Alex's blog

Simultaneous SQL and XML crimes

Athena Lilith Martin

Published on

New developments in the field of ungodly data format design have led to the following hybrid evilness.

-- SQLite schema.
CREATE TABLE xdocument (
  docid INTEGER PRIMARY KEY,
  name TEXT NULL,
  version TEXT NOT NULL DEFAULT '1.0',
  encoding TEXT NOT NULL DEFAULT 'UTF-8'
) STRICT;
CREATE INDEX docname ON xdocument(name);

CREATE TABLE xelement (
  elemid INTEGER PRIMARY KEY,
  docid INTEGER NOT NULL REFERENCES xdocument ON DELETE CASCADE ON UPDATE CASCADE,
  parent INTEGER NULL REFERENCES xelement ON DELETE CASCADE ON UPDATE CASCADE, -- NULL for root element. There can only be one root element.
  pos INTEGER NOT NULL CHECK(pos >= 0), -- Must be unique among pos values in records with the same element.parent or (xtext,xprocessing_instruction,xcomment).elemid value.
  nsid INTEGER NOT NULL REFERENCES xnamespace ON DELETE CASCADE ON UPDATE CASCADE, -- May only reference an xnamespace that is the first namespace along the xelement.parent chain with its nsprefix value.
  tag TEXT NOT NULL
) STRICT;
CREATE INDEX doc_tag ON xelement(docid, nsid, tag);
CREATE INDEX parent_tag ON xelement(parent, nsid, tag);

CREATE TABLE xnamespace (
  nsid INTEGER PRIMARY KEY,
  elemid INTEGER NOT NULL REFERENCES xelement ON DELETE CASCADE ON UPDATE CASCADE, -- Element namespace declared on.
  nsprefix TEXT NOT NULL,
  uri TEXT NOT NULL,
  UNIQUE (elemid, nsprefix)
) STRICT;
CREATE INDEX nsprefix ON xnamespace(nsprefix);
CREATE INDEX nsuri ON xnamespace(uri);

CREATE TABLE xattribute (
  attid INTEGER PRIMARY KEY,
  elemid INTEGER NOT NULL REFERENCES xelement ON DELETE CASCADE ON UPDATE CASCADE,
  nsid INTEGER NOT NULL REFERENCES xnamespace ON DELETE CASCADE ON UPDATE CASCADE, -- May only reference an xnamespace that is the first namespace along the xelement.parent chain (starting from elemid) with its nsprefix value.
  name TEXT NOT NULL,
  value TEXT NOT NULL
) STRICT;
CREATE INDEX att ON xattribute(elemid, nsid, name);

CREATE TABLE xtext (
  textid INTEGER PRIMARY KEY,
  elemid INTEGER NOT NULL REFERENCES xelement ON DELETE CASCADE ON UPDATE CASCADE,
  pos INTEGER NOT NULL CHECK(pos >= 0), -- Must be unique among pos values in records with the same element.parent or (xtext,xprocessing_instruction,xcomment).elemid value.
  content TEXT NOT NULL
) STRICT;

CREATE TABLE xprocessing_instruction (
  textid INTEGER PRIMARY KEY,
  docid INTEGER NOT NULL REFERENCES xdocument ON DELETE CASCADE ON UPDATE CASCADE,
  elemid INTEGER NULL REFERENCES xelement ON DELETE CASCADE ON UPDATE CASCADE, -- NULL for root-level PIs.
  pos INTEGER NOT NULL CHECK(pos >= 0), -- Must be unique among pos values in records with the same element.parent or (xtext,xprocessing_instruction,xcomment).elemid value.
  name TEXT NOT NULL,
  content TEXT NOT NULL
) STRICT;

CREATE TABLE xcomment (
  textid INTEGER PRIMARY KEY,
  docid INTEGER NOT NULL REFERENCES xdocument ON DELETE CASCADE ON UPDATE CASCADE,
  elemid INTEGER NULL REFERENCES xelement ON DELETE CASCADE ON UPDATE CASCADE, -- NULL for root-level comments.
  pos INTEGER NOT NULL CHECK(pos >= 0), -- Must be unique among pos values in records with the same element.parent or (xtext,xprocessing_instruction,xcomment).elemid value.
  content TEXT NOT NULL
) STRICT;

Tagged: