summaryrefslogtreecommitdiffstats
path: root/lib/ldb/ldb_sqlite3/schema
diff options
context:
space:
mode:
Diffstat (limited to 'lib/ldb/ldb_sqlite3/schema')
-rw-r--r--lib/ldb/ldb_sqlite3/schema328
1 files changed, 328 insertions, 0 deletions
diff --git a/lib/ldb/ldb_sqlite3/schema b/lib/ldb/ldb_sqlite3/schema
new file mode 100644
index 0000000..ab7c5cc
--- /dev/null
+++ b/lib/ldb/ldb_sqlite3/schema
@@ -0,0 +1,328 @@
+ -- ------------------------------------------------------
+
+ PRAGMA auto_vacuum=1;
+
+ -- ------------------------------------------------------
+
+ BEGIN EXCLUSIVE;
+
+ -- ------------------------------------------------------
+
+ CREATE TABLE ldb_info AS
+ SELECT 'LDB' AS database_type,
+ '1.0' AS version;
+
+ /*
+ * Get the next USN value with:
+ * BEGIN EXCLUSIVE;
+ * UPDATE usn SET value = value + 1;
+ * SELECT value FROM usn;
+ * COMMIT;
+ */
+ CREATE TABLE usn
+ (
+ value INTEGER
+ );
+
+ CREATE TABLE ldb_object
+ (
+ /* tree_key is auto-generated by the insert trigger */
+ tree_key TEXT PRIMARY KEY,
+
+ parent_tree_key TEXT,
+ dn TEXT,
+
+ attr_name TEXT REFERENCES ldb_attributes,
+ attr_value TEXT,
+
+ /*
+ * object_type can take on these values (to date):
+ * 1: object is a node of a DN
+ * 2: object is an attribute/value pair of its parent DN
+ */
+ object_type INTEGER,
+
+ /*
+ * if object_type is 1, the node can have children.
+ * this tracks the maximum previously assigned child
+ * number so we can generate a new unique tree key for
+ * a new child object. note that this is always incremented,
+ * so if children are deleted, this will not represent
+ * the _number_ of children.
+ */
+ max_child_num INTEGER,
+
+ /*
+ * Automatically maintained meta-data (a gift for metze)
+ */
+ object_guid TEXT UNIQUE,
+ timestamp INTEGER, -- originating_time
+ invoke_id TEXT, -- GUID: originating_invocation_id
+ usn INTEGER, -- hyper: originating_usn
+
+ /* do not allow duplicate name/value pairs */
+ UNIQUE (parent_tree_key, attr_name, attr_value, object_type)
+ );
+
+ CREATE TABLE ldb_attributes
+ (
+ attr_name TEXT PRIMARY KEY,
+ parent_tree_key TEXT,
+
+ objectclass_p BOOLEAN DEFAULT 0,
+
+ case_insensitive_p BOOLEAN DEFAULT 0,
+ wildcard_p BOOLEAN DEFAULT 0,
+ hidden_p BOOLEAN DEFAULT 0,
+ integer_p BOOLEAN DEFAULT 0,
+
+ /* tree_key is auto-generated by the insert trigger */
+ tree_key TEXT, -- null if not a object/sub class
+ -- level 1 if an objectclass
+ -- level 1-n if a subclass
+ max_child_num INTEGER
+ );
+
+ -- ------------------------------------------------------
+
+ CREATE INDEX ldb_object_dn_idx
+ ON ldb_object (dn);
+
+ CREATE INDEX ldb_attributes_tree_key_ids
+ ON ldb_attributes (tree_key);
+
+ -- ------------------------------------------------------
+
+ /* Gifts for metze. Automatically updated meta-data */
+ CREATE TRIGGER ldb_object_insert_tr
+ AFTER INSERT
+ ON ldb_object
+ FOR EACH ROW
+ BEGIN
+ UPDATE ldb_object
+ SET max_child_num = max_child_num + 1
+ WHERE tree_key = new.parent_tree_key;
+ UPDATE usn SET value = value + 1;
+ UPDATE ldb_object
+ SET tree_key =
+ (SELECT
+ new.tree_key ||
+ base160(SELECT max_child_num
+ FROM ldb_object
+ WHERE tree_key =
+ new.parent_tree_key));
+ max_child_num = 0,
+ object_guid = random_guid(),
+ timestamp = strftime('%s', 'now'),
+ usn = (SELECT value FROM usn);
+ WHERE tree_key = new.tree_key;
+ END;
+
+ CREATE TRIGGER ldb_object_update_tr
+ AFTER UPDATE
+ ON ldb_object
+ FOR EACH ROW
+ BEGIN
+ UPDATE usn SET value = value + 1;
+ UPDATE ldb_object
+ SET timestamp = strftime('%s', 'now'),
+ usn = (SELECT value FROM usn);
+ WHERE tree_key = new.tree_key;
+ END;
+
+ CREATE TRIGGER ldb_attributes_insert_tr
+ AFTER INSERT
+ ON ldb_attributes
+ FOR EACH ROW
+ BEGIN
+ UPDATE ldb_attributes
+ SET max_child_num = max_child_num + 1
+ WHERE tree_key = new.parent_tree_key;
+ UPDATE ldb_attributes
+ SET tree_key =
+ (SELECT
+ new.tree_key ||
+ base160(SELECT max_child_num
+ FROM ldb_attributes
+ WHERE tree_key =
+ new.parent_tree_key));
+ max_child_num = 0
+ WHERE tree_key = new.tree_key;
+ END;
+
+
+ -- ------------------------------------------------------
+
+ /* Initialize usn */
+ INSERT INTO usn (value) VALUES (0);
+
+ /* Create root object */
+ INSERT INTO ldb_object
+ (tree_key, parent_tree_key,
+ dn,
+ object_type, max_child_num)
+ VALUES ('', NULL,
+ '',
+ 1, 0);
+
+ /* We need an implicit "top" level object class */
+ INSERT INTO ldb_attributes (attr_name,
+ parent_tree_key)
+ SELECT 'top', '';
+
+ -- ------------------------------------------------------
+
+ COMMIT;
+
+ -- ------------------------------------------------------
+
+/*
+ * dn: o=University of Michigan,c=US
+ * objectclass: organization
+ * objectclass: domainRelatedObject
+ */
+-- newDN
+BEGIN;
+
+INSERT OR IGNORE INTO ldb_object
+ (parent_tree_key
+ dn,
+ attr_name, attr_value, object_type, max_child_num)
+ VALUES ('',
+ 'c=US',
+ 'c', 'US', 1, 0);
+
+INSERT INTO ldb_object
+ (parent_tree_key,
+ dn,
+ attr_name, attr_value, object_type, max_child_num)
+ VALUES ('0001',
+ 'o=University of Michigan,c=US',
+ 'o', 'University of Michigan', 1, 0);
+
+-- newObjectClass
+INSERT OR IGNORE INTO ldb_attributes
+ (attr_name, parent_tree_key, objectclass_p)
+ VALUES
+ ('objectclass', '', 1);
+
+INSERT INTO ldb_object
+ (parent_tree_key,
+ dn,
+ attr_name, attr_value, object_type, max_child_num)
+ VALUES ('00010001',
+ NULL,
+ 'objectclass', 'organization', 2, 0);
+
+INSERT OR IGNORE INTO ldb_attributes
+ (attr_name, parent_tree_key, objectclass_p)
+ VALUES
+ ('objectclass', '', 1);
+
+INSERT INTO ldb_object
+ (parent_tree_key,
+ dn,
+ attr_name, attr_value, object_type, max_child_num)
+ VALUES ('00010001',
+ NULL,
+ 'objectclass', 'domainRelatedObject', 2, 0);
+
+COMMIT;
+
+
+/*
+ * dn: o=University of Michigan,c=US
+ * l: Ann Arbor, Michigan
+ * st: Michigan
+ * o: University of Michigan
+ * o: UMICH
+ * seeAlso:
+ * telephonenumber: +1 313 764-1817
+ */
+-- addAttrValuePair
+BEGIN;
+
+INSERT INTO ldb_object
+ (parent_tree_key, dn,
+ attr_name, attr_value, object_type, max_child_num)
+ VALUES ('00010001', NULL,
+ 'l', 'Ann Arbor, Michigan', 2, 0);
+
+INSERT INTO ldb_object
+ (parent_tree_key, dn,
+ attr_name, attr_value, object_type, max_child_num)
+ VALUES ('00010001', NULL,
+ 'st', 'Michigan', 2, 0);
+
+INSERT INTO ldb_object
+ (parent_tree_key, dn,
+ attr_name, attr_value, object_type, max_child_num)
+ VALUES ('00010001', NULL,
+ 'o', 'University of Michigan', 2, 0);
+
+INSERT INTO ldb_object
+ (parent_tree_key, dn,
+ attr_name, attr_value, object_type, max_child_num)
+ VALUES ('00010001', NULL,
+ 'o', 'UMICH', 2, 0);
+
+INSERT INTO ldb_object
+ (parent_tree_key, dn,
+ attr_name, attr_value, object_type, max_child_num)
+ VALUES ('00010001', NULL,
+ 'seeAlso', '', 2, 0);
+
+INSERT INTO ldb_object
+ (parent_tree_key, dn,
+ attr_name, attr_value, object_type, max_child_num)
+ VALUES ('00010001', NULL,
+ 'telephonenumber', '+1 313 764-1817', 2, 0);
+
+COMMIT;
+
+-- ----------------------------------------------------------------------
+
+/*
+ * dn: @ATTRIBUTES
+ * uid: CASE_INSENSITIVE WILDCARD
+ * cn: CASE_INSENSITIVE
+ * ou: CASE_INSENSITIVE
+ * dn: CASE_INSENSITIVE
+ */
+-- newAttribute
+
+BEGIN;
+
+INSERT OR IGNORE INTO ldb_attributes
+ (attr_name, parent_tree_key, objectclass_p)
+ VALUES
+ ('uid', '', 0);
+
+UPDATE ldb_attributes
+ SET case_insensitive_p = 1,
+ wildcard_p = 1,
+ hidden_p = 0,
+ integer_p = 0
+ WHERE attr_name = 'uid'
+
+UPDATE ldb_attributes
+ SET case_insensitive_p = 1,
+ wildcard_p = 0,
+ hidden_p = 0,
+ integer_p = 0
+ WHERE attr_name = 'cn'
+
+UPDATE ldb_attributes
+ SET case_insensitive_p = 1,
+ wildcard_p = 0,
+ hidden_p = 0,
+ integer_p = 0
+ WHERE attr_name = 'ou'
+
+UPDATE ldb_attributes
+ SET case_insensitive_p = 1,
+ wildcard_p = 0,
+ hidden_p = 0,
+ integer_p = 0
+ WHERE attr_name = 'dn'
+