Keys

Keyed storage. Declared columns are the composite key, in order, and may only be int or string. Inserts must supply every declared key field with the declared type; the physical key is an order-preserving encoding of the key columns, so scans return rows in key order. Keyless tables keep surrogate ids.

Whole Objects

A keyless table stores and returns whole objects.

SQL

create table t;

insert into t ({x: 1, y: 2, z: 3});

select * from t;

Result

[
  { "x": 1, "y": 2, "z": 3 }
]

Ones X

A keyless table accepts any object, including ones with no x.

SQL

create table t;

insert into t ({y: 2, z: 3});

select * from t;

Result

[
  { "y": 2, "z": 3 }
]

Surrogate Ids

Surrogate ids increment, so rows come back in insertion order.

SQL

create table t;

insert into t ({x: 3}, {x: 1}, {x: 2});

select * from t;

Result

[
  { "x": 3 },
  { "x": 1 },
  { "x": 2 }
]

Int Key

Int key with payload round-trips whole object.

SQL

create table t (x int);

insert into t ({x: 1, z: 9});

select * from t;

Result

[
  { "x": 1, "z": 9 }
]

Rows Inserted

Rows inserted out of order come back sorted by the int key.

SQL

create table t (x int);

insert into t ({x: 3}, {x: 1}, {x: 2});

select * from t;

Result

[
  { "x": 1 },
  { "x": 2 },
  { "x": 3 }
]

Negative Ints

Negative ints sort before zero and positives (sign-flip encoding).

SQL

create table t (x int);

insert into t ({x: 1}, {x: -5}, {x: 0}, {x: -1});

select * from t;

Result

[
  { "x": -5 },
  { "x": -1 },
  { "x": 0 },
  { "x": 1 }
]

Re-inserting The

Re-inserting the same key overwrites (last write wins).

SQL

create table t (x int);

insert into t ({x: 1, v: 100});

insert into t ({x: 1, v: 200});

select * from t;

Result

[
  { "x": 1, "v": 200 }
]

Inserting Without

Inserting without the key field is a schema error.

SQL

create table t (x int);

insert into t ({z: 9});

Expected error: schema

Wrong Type

A string where an int key is declared is a schema error.

SQL

create table t (x int);

insert into t ({x: "a"});

Expected error: schema

Non Integral

A non-integral number for an int key is a schema error.

SQL

create table t (x int);

insert into t ({x: 1.5});

Expected error: schema

String Key

String key with payload round-trips whole object.

SQL

create table t (x string);

insert into t ({x: "a", z: 9});

select * from t;

Result

[
  { "x": "a", "z": 9 }
]

Rows Come

Rows come back in lexicographic key order.

SQL

create table t (x string);

insert into t ({x: "c"}, {x: "a"}, {x: "b"});

select * from t;

Result

[
  { "x": "a" },
  { "x": "b" },
  { "x": "c" }
]

Inserting Without

Inserting without the key field is a schema error.

SQL

create table t (x string);

insert into t ({z: 9});

Expected error: schema

Wrong Type

A number where a string key is declared is a schema error.

SQL

create table t (x string);

insert into t ({x: 1});

Expected error: schema

Composite (int,

Composite (int, string) key round-trips whole object.

SQL

create table t (a int, b string);

insert into t ({a: 1, b: "x", z: 9});

select * from t;

Result

[
  { "a": 1, "b": "x", "z": 9 }
]

Sort By

Sort by first component, tie-break on the second.

SQL

create table t (a int, b string);

insert into t ({a: 2, b: "a"}, {a: 1, b: "y"}, {a: 1, b: "x"});

select * from t;

Result

[
  { "a": 1, "b": "x" },
  { "a": 1, "b": "y" },
  { "a": 2, "b": "a" }
]

Missing The

Missing the first key field is a schema error.

SQL

create table t (a int, b string);

insert into t ({b: "x"});

Expected error: schema

Missing The

Missing the second key field is a schema error.

SQL

create table t (a int, b string);

insert into t ({a: 1});

Expected error: schema

Wrong Type

Wrong type for the first key field is a schema error.

SQL

create table t (a int, b string);

insert into t ({a: "q", b: "x"});

Expected error: schema

Wrong Type

Wrong type for the second key field is a schema error.

SQL

create table t (a int, b string);

insert into t ({a: 1, b: 2});

Expected error: schema

Composite (string,

Composite (string, int) key round-trips whole object.

SQL

create table t (a string, b int);

insert into t ({a: "x", b: 1, z: 9});

select * from t;

Result

[
  { "a": "x", "b": 1, "z": 9 }
]

Sort By

Sort by string first, tie-break on the int.

SQL

create table t (a string, b int);

insert into t ({a: "b", b: 1}, {a: "a", b: 2}, {a: "a", b: 1});

select * from t;

Result

[
  { "a": "a", "b": 1 },
  { "a": "a", "b": 2 },
  { "a": "b", "b": 1 }
]

Missing The

Missing the int component is a schema error.

SQL

create table t (a string, b int);

insert into t ({a: "x"});

Expected error: schema

Type Second

A string where the int component is declared is a schema error.

SQL

create table t (a string, b int);

insert into t ({a: "x", b: "y"});

Expected error: schema

Composite (int,

Composite (int, int) key round-trips whole object.

SQL

create table t (a int, b int);

insert into t ({a: 1, b: 2, z: 9});

select * from t;

Result

[
  { "a": 1, "b": 2, "z": 9 }
]

Sort By

Sort by first int, tie-break on the second int.

SQL

create table t (a int, b int);

insert into t ({a: 2, b: 1}, {a: 1, b: 2}, {a: 1, b: 1});

select * from t;

Result

[
  { "a": 1, "b": 1 },
  { "a": 1, "b": 2 },
  { "a": 2, "b": 1 }
]

Missing A

Missing a key component is a schema error.

SQL

create table t (a int, b int);

insert into t ({a: 1});

Expected error: schema

Composite (string,

Composite (string, string) key round-trips whole object.

SQL

create table t (a string, b string);

insert into t ({a: "x", b: "y", z: 9});

select * from t;

Result

[
  { "a": "x", "b": "y", "z": 9 }
]

Sort By

Sort by first string, tie-break on the second.

SQL

create table t (a string, b string);

insert into t ({a: "b", b: "a"}, {a: "a", b: "b"}, {a: "a", b: "a"});

select * from t;

Result

[
  { "a": "a", "b": "a" },
  { "a": "a", "b": "b" },
  { "a": "b", "b": "a" }
]

Before Ab

A shorter first component sorts before a longer one that shares its prefix, regardless of the second component — proves the string terminator. ("a","z") must sort before ("ab","a").

SQL

create table t (a string, b string);

insert into t ({a: "ab", b: "a"}, {a: "a", b: "z"});

select * from t;

Result

[
  { "a": "a", "b": "z" },
  { "a": "ab", "b": "a" }
]

Missing A

Missing a key component is a schema error.

SQL

create table t (a string, b string);

insert into t ({a: "x"});

Expected error: schema

Float Key

A float key column is rejected at create.

SQL

create table t (x float);

Expected error: static

Bool Key

A bool key column is rejected at create.

SQL

create table t (x bool);

Expected error: static