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"`
}

    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) 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 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) 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 a 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 database, or a new revision of a 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