Skip to content

Latest commit

 

History

History
132 lines (107 loc) · 5.87 KB

README.md

File metadata and controls

132 lines (107 loc) · 5.87 KB
Raw

SQL array comprehensions in Go. GoDoc Build Status Coverage Status Donate

  • Great for complex in-memory caches, or in-memory joins across various datastores
  • Development faster than in-memory DBs: no table creation, inserts, IPC, CGO or de/serialization
  • Sort Easily: "ORDER BY last_name, importance DESC"
  • Every struct is a table row. A (slice OR chan) of them is a table. -- chan tables will cache to a slice as needed. (Today to avoid caching, it needs to be the first table & no subsequent Right join)
  • Pass in & use any func. time.Time is your time format.
  • Use any types you want, just provide & use the functions you needed.

Example:

// Lets thank our 10 biggest customers this month:
var results []struct{
  Name string
  Email string
  MonthlyTotal float64
}
err := nodb.Inline(&results, 
    "SELECT name, email, SUM(o.total) AS monthlyTotal",
    "FROM", customer,"AS cust JOIN", order,"AS o ON cust.id=o.custID",
    "WHERE o.whenCompleted > ", time.Now().Add(-30*24*time.Hour),
    "GROUP BY cust.id",
    "ORDER BY monthlyTotal DESC",
    "LIMIT 10")
// If no error: the results' structs will contain shallow-copies of their elements.

DB Driver method:

  import "github.com/snadrus/nodb"
  ...
  // Once:
  nodb.Add("myTable", []ABC{{1, "hello", 1.0}, {2, "db", 2.0}, {3, "world", 3.0}})  
  connection := sqlx.MustConnect("nodb", "cache")         

  // As-Needed:           
  var results []ABC
  err := connection.Select(&results, "SELECT * FROM myTable WHERE C > 1.5")
  fmt.Println(results) // []ABC{{2, "db", 2.0}, {3, "world", 3.0}}

Benefitting:

  • no-Join policy scenarios (like Cassandra): ACID your way.
  • SQL in caches
  • Building your own DB.

FAQ:

  • Dual License
    • BSD or commercial license.
  • Why are nodb.Do results not saved?
    • Results reach the destination by name. Use an "AS" to select the destination field.
  • How compatible?
    • Common parts of ANSI SQL. It's right or will error. Case-insensitive query of public members. See TODOs for omissions
  • How extensible?
    • Add functions per query Obj & use them anywhere in the query.
  • How can I help?
    • Open a bug in github.org/snadrus/nodb and send a merge request.
  • Types?
    • Are GoLang types. Pass-in functions: func Hour(t time.Time) int { return t.Hour() }
    • Time renders as UnixNano() for comparison/arithmatic/etc including time.Duration
  • Where did this come from?
    • It's the personal efforts of Andrew Jackson who also had the idea.
  • How fast really?
    • It is in-memory & pipelined for multicore. Correctness first, query planning second.
    • 30-year-optimized DBs mostly win with on-disk layout & permanent indexes. Neither makes sense here.
    • Lots of work is possible in query planner stage to make use of special circumstances.
  • Memory Risk:
    • Object copying isn't light. GROUPBY/DISTINCT/ORDER_BY is even heavier.

Design: (the first?) 100% Go SQL engine Go libs for SQL parsing and interface{} evaluating. Native GoLang libs for all else: reflect, sort, template (functions) Process:

  • Rich "Rows" are formed of all joined, renamed, calculated data for a source row
  • Processing occurs on these.
  • SELECT rows (just requested data) are formed.
  • The SELECT rows are mapped back to the destination by name Closures are the greatest! The setups return functions that have context.

Recently Added:

  • Subqueries
  • chan (struct) Tables. If it's the first table, it also won't cache
  • SELECT count(distinct __)
  • DISTINCT (whole SELECT row)

TODO:

  • Functions in WHERE clause -- Needs parser upgrades to be used in WHERE clauses

  • Perf: -- WHERE clause per-table first IF this table is involved in it. -- MAPS for ON relation (presume unique, work if not unique)

  • Parentheses joins. Build a joinElement without a left, but keep its append order

  • NULL (nil) support is wonky at best. Avoid if possible.

  • TODOs in the code.

  • Functions on objects: Hour(t) --> t.Hour() -- Needs parser upgrades to be used in WHERE clauses

  • DB Proxy (Cassandra or a variety at once) -- SubQueries + mmap for JOIN/GROUP intermediaries

  • OPTIMIZATION: Per-table elimination of rows without all data available: -- THEN: make errored expr.E returns include partial eval. <-- bad idea. No way to combine. Overhead

  • OPTIMIZATION: RowMap["1Prototype"] pointing to previous map. Reduces join effort. BETTER: have a .GetValue("name") function that works on all the various types.

  • OPTIMIZATION: index idea: zeroed index will represent position offset to real table values, so all zeroes is "this order". Then we are free to sort using govaluate to build an index on-the-fly without painful/risky reordering. Indexes are great for groupby fields: walking them in sorted order means no memalloc

  • OPTIMIZATION: expr needs return a rollup of what tables a subexpression uses.

    • Inv of constant. Use to determine if needs re-eval
    • Aides planner in slicing-up processing
    • OPTIMIZATION: pre-determine the table rows that match the WHERE query.

  • Planner v2: run short-circuit expr, then recurse. Medium: just left-align those with indexes. leftist for 1 channel

  • Joiner: hard-version:

    • if you're an inner loop, consider marking those you skip
    • if unsorted & "equals" join, map or sort

  • MEM exhaustion risk: group/distinct/orderby mmaped hashes. (MEDIUM)