dbhub

package module
Version: v0.1.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Mar 26, 2023 License: MIT Imports: 12 Imported by: 0

README

GoDoc Go Report Card

A Go library for accessing and using SQLite databases stored remotely on DBHub.io

This is an early stage work in progress

What works now

  • Run read-only queries (eg SELECT statements) on databases, returning the results as JSON
  • Upload and download your databases
  • List the databases in your account
  • List the tables, views, and indexes present in a database
  • List the columns in a table, view or index, along with their details
  • List the branches, releases, tags, and commits for a database
  • Generate diffs between two databases, or database revisions
  • Download the database metadata (size, branches, commit list, etc.)
  • Retrieve the web page URL of a database

Still to do

  • Have the backend server correctly use the incoming branch, release, and tag information
  • Tests for each function
  • Investigate what would be needed for this to work through the Go SQL API
    • Probably need to improve the Query approach, to at least support placeholders and argument parameters
  • Anything else people suggest and seems like a good idea 😄

Requirements

  • Go version 1.14.x
    • Older Go releases should be ok, but only Go 1.14.x has been tested (so far).
  • A DBHub.io API key
    • These can be generated in your Settings page, when logged in.

Example code

Create a new DBHub.io API object

db, err := dbhub.New("YOUR_API_KEY_HERE")
if err != nil {
    log.Fatal(err)
}

Retrieve the list of tables in a remote database

// Run the `Tables()` function on the new API object
tables, err := db.Tables("justinclift", "Join Testing.sqlite", dbhub.Identifier{Branch: "master"})
if err != nil {
    log.Fatal(err)
}

// Display the retrieved list of tables
fmt.Println("Tables:")
for _, j := range tables {
    fmt.Printf("  * %s\n", j)
}
Output
Tables:
  * table1
  * table2

Run a SQL query on a remote database

// Do we want to display BLOBs as base64?
showBlobs := false

// Run the query
result, err := db.Query("justinclift", "Join Testing.sqlite",
    dbhub.Identifier{ Branch: "master" }, showBlobs,
    `SELECT table1.Name, table2.value
    FROM table1 JOIN table2
    USING (id)
    ORDER BY table1.id`)
if err != nil {
    log.Fatal(err)
}
fmt.Printf("Query results (JSON):\n\t%v\n", result)
fmt.Println()
Output
Query results (JSON):
        {[{[Foo 5]} {[Bar 10]} {[Baz 15]} {[Blumph 12.5000]} {[Blargo 8]} {[Batty 3]}]}

Generate and display the difference between two commits of a remote database

// The databases we want to see differences for
db1Owner := "justinclift"
db1Name := "Join Testing.sqlite"
db1Commit := dbhub.Identifier{
    CommitID: "c82ba65add364427e9af3f540be8bf98e8cd6bdb825b07c334858e816c983db0" }
db2Owner := ""
db2Name := ""
db2Commit := dbhub.Identifier{
    CommitID: "adf78104254ece17ff40dab80ae800574fa5d429a4869792a64dcf2027cd9cd9" }

// Create the diff
diffs, err := db.Diff(db1Owner, db1Name, db1Commit, db2Owner, db2Name, db2Commit,
    dbhub.PreservePkMerge)
if err != nil {
    log.Fatal(err)
}

// Display the diff
fmt.Printf("SQL statements for turning the first commit into the second:\n")
for _, i := range diffs.Diff {
    if i.Schema != nil {
        fmt.Printf("%s\n", i.Schema.Sql)
    }
    for _, j := range i.Data {
        fmt.Printf("%s\n", j.Sql)
    }
}
Output
SQL statements for turning the first commit into the second:
CREATE VIEW joinedView AS
SELECT table1.Name, table2.value
FROM table1 JOIN table2
USING (id)
ORDER BY table1.id;

Further examples

Please try it out, submits PRs to extend or fix things, and report any weirdness or bugs you encounter. 😄

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

This section is empty.

Types

type Connection 

type Connection struct {
	APIKey           string `json:"api_key"`
	Server           string `json:"server"`
	VerifyServerCert bool   `json:"verify_certificate"`
}

Connection is a simple container holding the API key and address of the DBHub.io server

func New 

func New(key string) (Connection, error)

New creates a new DBHub.io connection object. It doesn't connect to DBHub.io to do this. Connection only occurs when subsequent functions (eg Query()) are called.

func (Connection) Branches 

func (c Connection) Branches(dbOwner, dbName string) (branches map[string]com.BranchEntry, defaultBranch string, err error)

Branches returns a list of all available branches of a database along with the name of the default branch

func (*Connection) ChangeAPIKey 

func (c *Connection) ChangeAPIKey(k string)

ChangeAPIKey updates the API key used for authenticating with DBHub.io.

func (*Connection) ChangeServer 

func (c *Connection) ChangeServer(s string)

ChangeServer changes the address for communicating with DBHub.io. Useful for testing and development.

func (*Connection) ChangeVerifyServerCert 

func (c *Connection) ChangeVerifyServerCert(b bool)

ChangeVerifyServerCert changes whether to verify the server provided https certificate. Useful for testing and development.

func (Connection) Columns 

func (c Connection) Columns(dbOwner, dbName string, ident Identifier, table string) (columns []com.APIJSONColumn, err error)

Columns returns the column information for a given table or view

func (Connection) Commits 

func (c Connection) Commits(dbOwner, dbName string) (commits map[string]com.CommitEntry, err error)

Commits returns the details of all commits for a database

func (Connection) Databases 

func (c Connection) Databases() (databases []string, err error)

Databases returns the list of standard databases in your account

func (Connection) DatabasesLive 

func (c Connection) DatabasesLive() (databases []string, err error)

DatabasesLive returns the list of Live databases in your account

func (Connection) Delete 

func (c Connection) Delete(dbName string) (err error)

Delete deletes a database in your account

func (Connection) Diff 

func (c Connection) Diff(dbOwnerA, dbNameA string, identA Identifier, dbOwnerB, dbNameB string, identB Identifier, merge MergeStrategy) (diffs com.Diffs, err error)

Diff returns the differences between two commits of two databases, or if the details on the second database are left empty, between two commits of the same database. You can also specify the merge strategy used for the generated SQL statements.

func (Connection) Download 

func (c Connection) Download(dbOwner, dbName string, ident Identifier) (db io.ReadCloser, err error)

Download returns the database file

func (Connection) Execute 

func (c Connection) Execute(dbOwner, dbName string, sql string) (rowsChanged int, err error)

Execute executes a SQL statement (INSERT, UPDATE, DELETE) on the chosen database.

func (Connection) Indexes 

func (c Connection) Indexes(dbOwner, dbName string, ident Identifier) (idx []com.APIJSONIndex, err error)

Indexes returns the list of indexes present in the database, along with the table they belong to

func (Connection) Metadata 

func (c Connection) Metadata(dbOwner, dbName string) (meta com.MetadataResponseContainer, err error)

Metadata returns the metadata (branches, releases, tags, commits, etc) for the database

func (Connection) PrepareVals 

func (c Connection) PrepareVals(dbOwner, dbName string, ident Identifier) (data url.Values)

PrepareVals creates an url.Values container holding the API key, database owner, name, and database identifier. The url.Values container is then used for the requests to DBHub.io.

func (Connection) Query 

func (c Connection) Query(dbOwner, dbName string, ident Identifier, blobBase64 bool, sql string) (out Results, err error)

Query runs a SQL query (SELECT only) on the chosen database, returning the results. The "blobBase64" boolean specifies whether BLOB data fields should be base64 encoded in the output, or just skipped using an empty string as a placeholder.

func (Connection) Releases 

func (c Connection) Releases(dbOwner, dbName string) (releases map[string]com.ReleaseEntry, err error)

Releases returns the details of all releases for a database

func (Connection) Tables 

func (c Connection) Tables(dbOwner, dbName string, ident Identifier) (tbl []string, err error)

Tables returns the list of tables in the database

func (Connection) Tags 

func (c Connection) Tags(dbOwner, dbName string) (tags map[string]com.TagEntry, err error)

Tags returns the details of all tags for a database

func (Connection) Upload 

func (c Connection) Upload(dbName string, info UploadInformation, dbBytes *[]byte) (err error)

Upload uploads a new standard database, or a new revision of a database

func (Connection) UploadLive 

func (c Connection) UploadLive(dbName string, dbBytes *[]byte) (err error)

UploadLive uploads a new Live database

func (Connection) Views 

func (c Connection) Views(dbOwner, dbName string, ident Identifier) (views []string, err error)

Views returns the list of views in the database

func (Connection) Webpage 

func (c Connection) Webpage(dbOwner, dbName string) (webPage com.WebpageResponseContainer, err error)

Webpage returns the URL of the database file in the webUI. eg. for web browsers

type Identifier 

type Identifier struct {
	Branch   string `json:"branch"`
	CommitID string `json:"commit_id"`
	Release  string `json:"release"`
	Tag      string `json:"tag"`
}

Identifier holds information used to identify a specific commit, tag, release, or the head of a specific branch

type JSONError 

type JSONError struct {
	Msg string `json:"error"`
}

JSONError holds information about an error condition, in a useful JSON format

type MergeStrategy 

type MergeStrategy int

MergeStrategy specifies the type of SQL statements included in the diff results. The SQL statements can be used for merging databases and depending on whether and how you want to merge you should choose your merge strategy. 

const (
	// NoMerge removes any SQL statements for merging from the diff results
	NoMerge MergeStrategy = iota

	// PreservePkMerge produces SQL statements which preserve the values of the primary key columns.
	// Executing these statements on the first database produces a database similar to the second.
	PreservePkMerge

	// NewPkMerge produces SQL statements which generate new values for the primary key columns when
	// executed. This avoids a couple of possible conflicts and allows merging more distant databases.
	NewPkMerge
)

type ResultRow 

type ResultRow struct {
	Fields []string
}

ResultRow is used for returning the results of a SQL query as a slice of strings

type Results 

type Results struct {
	Rows []ResultRow
}

Results is used for returning the results of a SQL query as a slice of strings

type UploadInformation 

type UploadInformation struct {
	Ident           Identifier `json:"identifier"`
	CommitMsg       string     `json:"commitmsg"`
	SourceURL       string     `json:"sourceurl"`
	LastModified    time.Time  `json:"lastmodified"`
	Licence         string     `json:"licence"`
	Public          string     `json:"public"`
	Force           bool       `json:"force"`
	CommitTimestamp time.Time  `json:"committimestamp"`
	AuthorName      string     `json:"authorname"`
	AuthorEmail     string     `json:"authoremail"`
	CommitterName   string     `json:"committername"`
	CommitterEmail  string     `json:"committeremail"`
	OtherParents    string     `json:"otherparents"`
	ShaSum          string     `json:"dbshasum"`
}

UploadInformation holds information used when uploading

Source Files

View all

Directories

Path Synopsis
examples
column_details
delete_database
diff_commits
download_database
list_branches
list_commits
list_databases
list_indexes
list_releases
list_tables
list_tags
list_views
metadata
sql_query
upload
webpage

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL