gloriousfutureio / sql2kv Goto Github PK

Document in #9 after we're done

Add this early on so we benefit from it over time. This project pushes a lot of data from network to disk, and holds things in memory to reshape them. Profiling seems worth it.

We get column types after scanning information_schema. The first place we map those to a go type is here

There are a lot of types to map. We might gather them under a function like:

for _, t := range ts.Columns {
  val, err := mapMySQLType(ts.DataType)
  // check err ...
  newRow = append(newRow, val)
}
rows.Scan(newRow...)

If we encapsulate the different DB's mappings into functions, it will be easier to swap the implementation.

The core function is to dump data, but we might persist additional metadata on a special key path:

/{schema}/{table}/metadata/{something}

Rather than forcing the person querying out k-v store to map a complicated data structure that contains all metadata, we might provide arrays of strings for some things

/{schema}/{table}/metadata/columns - ["id", "name", "age"]
/{schema}/{table}/metadata/types - ["numeric", "string", "numeric"]
//{schema}/{table}/metadata/primary-key - "id"

Travis is free for open source. Does Circle do the same thing?

The JSON serializer knows what to do with a flat map[string]interface{}. We need to switch over the pointer types and dereference the pointers to get values.

Note that we need column names and (optionally) the primary key column name turned into the special __pk key.

Proposed signature:

func derefToMap(cols []string, row []interface{}, pkCol string) map[string]interface{} { /* ... */ }

We have a chain of conversions from SQL to Go types to Key + JSON representation.

Nullables

Most regular types, if nullable, will need to map to a custom Go struct that implements the Valuer interface: https://golang.org/pkg/database/sql/driver/#Valuer . There might also be libraries that provide these types.

Edge cases

• tables without primary keys: what do we do here?

Still working on the POC.

Take the user table

+----+---------------+-------+
| id | name          | age   |
+----+---------------+-------+
|  1 | Farhan        |    32 |
|  2 | Coleman       |    32 |
|  3 | Jeff May      |    27 |
|  4 | Mr&MissGophie |     1 |
|  5 | Dogs          |   200 |
|  6 | GopherThing   |    20 |
|  7 | Linus Torval  |   100 |
|  8 | Rob Pike      |   100 |
|  9 | NinjaMan      | 23232 |
| 10 | Zack          |    34 |
+----+---------------+-------+

Into a leveldb with something like

<schema>!<table>!<PrimaryKEY>! JSON

We need an open source license and a README stubbed out.

We can ship tools under cmd like

sql2kv/cmd/sql2kv  # "real" command line app
sql2kv/cmd/sql2kv-test  # an standalone CLI for benchmarking

Everybody loves the cli framework.