Tables and SQL

Wiki

On this page

• A table is a property
• Column types
• Reading rows from Cypher
• What clients receive
• Storage and footprint
• Topic guides

A node property can hold a TABLE: typed rows with a primary key, indexes and constraints, read and changed with SQL. Orders, readings, line items and ledgers stay with the node they belong to, and a query moves between the graph and the rows in one statement. An order of five columns with a date index takes 63 bytes on disk as a table row, against 85 as a node of its own (100,000 orders across 1,000 customers, Galactus DB 2.1.0, Paged). From version 2.1.0. See it work in the Tables and SQL demo.

MATCH (c:Customer {id: 1})
SET c.orders = TABLE (
  id        INTEGER PRIMARY KEY,
  placed_at LOCAL DATETIME NOT NULL,
  sku       STRING NOT NULL,
  qty       INTEGER NOT NULL DEFAULT 1 CHECK (qty > 0),
  amount    DECIMAL(12, 2),
  INDEX by_sku (sku, placed_at)
);

MATCH (c:Customer {id: 1})
CALL (c) SQL {
  INSERT INTO c.orders (id, placed_at, sku, amount)
  VALUES (1, localdatetime(), 'SKU-00042', 129.50)
};

MATCH (c:Customer)
WHERE SQL { SELECT count(*) FROM c.orders WHERE amount > 100 } > 5
RETURN c.id;

A table is a property

• SET n.t = TABLE (…) gives one node an empty table. Every node can have any number of tables, each with its own columns; nothing is declared per label.
• Tables with the same definition share one stored schema, so 20,000 customers' orders tables cost one schema entry, and each row stores only its values.
• Properties are values: SET d.orders = c.orders copies the table, rows and indexes. REMOVE n.t or deleting the node drops it; its space is reclaimed once no reader's snapshot can see it.
• A table cannot be put in a list or map, compared with = or indexed as a property value.
• Reading a table needs MATCH (READ); changing one needs WRITE, as for any property.

Column types

Values convert where SQL does: an integer into a FLOAT or DECIMAL column, a decimal rounded to the column's scale. VARCHAR(n) counts characters. A table without a PRIMARY KEY is keyed by a hidden row number.

Reading rows from Cypher

SQL itself runs in SQL { } blocks: see SQL.

What clients receive

Returning a node, or the property itself, returns a small summary in place of the rows, whatever the table's size:

{"$gdbType": "table", "version": 1, "type": "Order", "rows": 1823,
 "columns": ["id", "placed_at", "sku", "qty", "amount"]}

Rows come back only when a query asks for them: CALL (n) SQL { SELECT … }, UNWIND, sql.page or a list form.

Storage and footprint

Rows live in the table tree of the database, on every storage engine: in pages on Paged, in memory and the commit log on Resident and Hybrid. A row stores its schema id, a null bitmap and its non-null values without type tags; an index entry stores the indexed values and the key.

• ADD COLUMN changes no row: rows written before read the column's default.
• Deleted rows and dropped tables give their space back. On Paged, a page left under a quarter full is joined with its neighbour as the change is flushed, and CALL gdb.compact() rewrites every page that holds dead space (Compacting a database).
• A database that never creates a table is unchanged on disk and stays readable by 2.0. The first table raises the oldest release that may read it to 2.1.0.

Topic guides

• SQL: SELECT, INSERT, UPDATE, DELETE, the SQL { } forms, parameters and the sql. procedures.
• Definitions: ALTER TABLE, CHECK constraints, indexes, table types and global indexes.