peachy/assets/.lua/dbidents.lua.txt

local re = require "re"

--[[
	Instead of describing node and field types as tuples, as in Drupal, I'll describe them in the DB schema itself.
	If I have a node of type "blogpost" with fields "title" and "body", I'll store it like this:
	CREATE TABLE "node:blogpost" (id INTEGER PRIMARY KEY, "title" TEXT NOT NULL, "body" TEXT NOT NULL);

	Complex fields will use a similar system. Here is a field type called "mood", and a node type that uses it called "moody blogpost".
	CREATE TABLE "field:mood" (id INTEGER PRIMARY KEY, "description" TEXT NOT NULL, "intensity" INT NOT NULL);
	CREATE TABLE "node:moody blogpost" (
		id INTEGER PRIMARY KEY,
		"today's mood:mood" INTEGER NOT NULL REFERENCES "field:mood"(id)
	);

	Complex fields can be arbitrarily nested.

	TODO Primitive date/time types
	TODO Lists
	TODO Variants
]]

-- type path = [string]

local function path_to_tablename(path)
	-- We join path segments with ':', so escape ':' to [[\:]]' and [[\]] to [[\\]].
	local pattern = [=[[:\]]=]
	local escaped_path = {}
	for _, segment in ipairs(path) do
		local escaped_segment = segment:gsub(pattern, [[\%0]])
		table.insert(escaped_path, escaped_segment)
	end
	return table.concat(escaped, ':')
end

local function tablename_to_path(path)
	-- Reverse of path_to_tablename.
	-- Then write a test of the property that ∀s:string, s == path_to_tablename(tablename_to_path(s)).
end





--[[


Node
Field


The website schema needs to be discoverable. The sqlite_schema table (or the table_list pragma) and the table_info pragma are essential.
The following facts need to be discoverable:
- The name of each node type.
- The fields in each node type.
- The type of each field in a node.
- The subfields in each field type.
- The type of each subfield in a field.
- Whether a field or subfield is repeated, optional, or single.
- Whether a field has an application-primitive, but non-sqlite-primitive, type, such as datetime.
- Discriminated union fields.

Field types need to be acyclic.
	Do they really?

Field values need to be deleted when the node that contains them is deleted.

If field types are namespaced to a node type, then SQLite schema object naming will get harder.
	Maybe just let them be shared.

(node blogpost)
(composite-field car)
(node "car review")
(field cars)
(field car)
node:blogpost
field:


(: road-review node)
(: car composite-field)
-- Entity names must be qualified by their type/kind, or there can't be e.g. a node type and field type that share a name.
-- I think qualification is best, because then SQLite will prevent duplicates by the requirement that table names be unique.
(list (: car composite-field))


create table "car:composite-field" (
	id integer primary key,
	"Make" text not null,
	"Model" text not null,
	"Year" text not null,
	"Test distance" int not null,
	"Test date:date" text not null,
);

create table "road review.cars:(list-field (road review:node))" (
	"Road review:node" int primary key references "road review:node",
	"Car:composite-field" int primary key references "car:composite-field",
);

create table "Road review:node" (
	id integer primary key,
	"Title" text not null,
	"Cars:(list car)"
);

(field car)
(node review)
(list (field car) (node review)) -- If we parameterize each list relation by both the listed field and the containing node or field, then we've basically monomorphized it.
-- However, that won't cut it if a node or field contains multiple lists of the same type. We also need to parameterize list relations by the field name in the containing node or field.
primitive-field-type := int | text | date | 
record-field-type := (record <field-type-name> ((<field-name> <field-type>) ...))
variant-field-type := (variant <field-type-name> ((<field-name> <field-type>) ...))
field-type := (field <field-type-name>)
node-type := (node <node-type-name>)
parent-object-type := <field-type> | <node-type> 
list := (list <element-type> <parent-object-type> <field-name>)




(record car)
	(id integer)
	(make text)
	(model text)
	(year text)
	(test-distance integer)
	(test-date date)

(list car)
	(node road-review)
	(car car)

(node road-review)
	(id integer)
	(title text)
	(cars (list car))


(node <node-type-name>)
	id integer primary key
	(<composite-type-name> <field-name>) <db-type> ...
(list <composite-type-name>)
	id
(list-item <composite-type-name>)
	list-id references (list <composite-type-name>) primary key
	sequence-number primary key
	item-id references (composite-value <composite-type-name>)
(composite-value <composite-type-name>)
	id
	-- This indirection is so that SQLite will prevent duplicate field type names in table names.
	<record|variant>-value-id references (<record|variant>-value <composite-type-name>)
(record-value <composite-type-name>)
	id integer primary key
	(<composite-type-name> <field-name>) <db-type> ...
(variant-value <composite-type-name>)
	id integer primary key
	(<composite-type-name> <field-name>) <db-type> ...

]]