diff options
Diffstat (limited to 'source4/lib/ldb/ldb_sqlite3/schema')
| -rw-r--r-- | source4/lib/ldb/ldb_sqlite3/schema | 377 |
1 files changed, 249 insertions, 128 deletions
diff --git a/source4/lib/ldb/ldb_sqlite3/schema b/source4/lib/ldb/ldb_sqlite3/schema index d06d7d0c34..45be5b578f 100644 --- a/source4/lib/ldb/ldb_sqlite3/schema +++ b/source4/lib/ldb/ldb_sqlite3/schema @@ -12,110 +12,163 @@ SELECT 'LDB' AS database_type, '1.0' AS version; - CREATE TABLE ldb_distinguished_names + /* + * Get the next USN value with: + * BEGIN EXCLUSIVE; + * UPDATE usn SET value = value + 1; + * SELECT value FROM usn; + * COMMIT; + */ + CREATE TABLE usn ( - dn_id INTEGER PRIMARY KEY AUTOINCREMENT, - dn TEXT UNIQUE + value INTEGER ); - CREATE TABLE ldb_object_classes + CREATE TABLE ldb_object ( - class_name TEXT PRIMARY KEY, - tree_key TEXT, - max_child_num INTEGER - ); - - CREATE TABLE ldb_dn_object_classes - ( - dn_id INTEGER REFERENCES ldb_distinguished_names, - class_name TEXT REFERENCES ldb_object_classes + /* tree_key is auto-generated by the insert trigger */ + tree_key TEXT PRIMARY KEY, + + parent_tree_key TEXT, + full_path 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, - case_insensitive_p BOOLEAN DEFAULT FALSE, - wildcard_p BOOLEAN DEFAULT FALSE, - hidden_p BOOLEAN DEFAULT FALSE, - integer_p BOOLEAN DEFAULT FALSE - ); + parent_tree_key TEXT, - CREATE TABLE ldb_attr_value_pairs - ( - dn_id INTEGER REFERENCES ldb_distinguished_names, - attr_name TEXT, -- optionally REFERENCES ldb_attributes - attr_value 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, - UNIQUE (dn_id, attr_name, attr_value) + /* 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 TRIGGER ldb_distinguished_names_delete_tr - AFTER DELETE - ON ldb_distinguished_names + CREATE INDEX ldb_object_full_path_idx + ON ldb_object (full_path); + + 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 - DELETE FROM ldb_attr_value_pairs - WHERE dn_id = old.dn_id; - DELETE FROM ldb_dn_object_classes - WHERE dn_id = old.dn_id; + 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_attr_value_pairs_insert_tr - BEFORE INSERT - ON ldb_attr_value_pairs + CREATE TRIGGER ldb_object_update_tr + AFTER UPDATE + ON ldb_object FOR EACH ROW BEGIN - INSERT OR IGNORE INTO ldb_attributes - (attr_name) - VALUES - (new.attr_name); + 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_attr_value_pairs_delete_tr - AFTER DELETE - ON ldb_attr_value_pairs + CREATE TRIGGER ldb_attributes_insert_tr + AFTER INSERT + ON ldb_attributes FOR EACH ROW BEGIN - DELETE FROM ldb_attributes - WHERE (SELECT COUNT(*) - FROM ldb_attr_value_pairs - WHERE attr_name = old.attr_name) = 0 - AND attr_name = old.attr_name; + 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; - -- ------------------------------------------------------ - - CREATE INDEX ldb_distinguished_names_dn_idx - ON ldb_distinguished_names (dn); - - CREATE INDEX ldb_object_classes_tree_key_idx - ON ldb_object_classes (tree_key); - - - CREATE INDEX ldb_dn_object_classes_dn_id_idx - ON ldb_dn_object_classes (dn_id); - - CREATE INDEX ldb_dn_object_classes_class_name_idx - ON ldb_dn_object_classes (class_name); - - - CREATE INDEX ldb_attr_value_pairs_dn_id_name_case_idx - ON ldb_attr_value_pairs (dn_id, attr_name); - - CREATE INDEX ldb_attr_value_pairs_dn_id_name_nocase_idx - ON ldb_attr_value_pairs (dn_id, attr_name COLLATE NOCASE); -- ------------------------------------------------------ - /* all defaults for dn, initially */ - INSERT INTO ldb_attributes (attr_name) - VALUES ('dn'); + /* Initialize usn */ + INSERT INTO usn (value) VALUES (0); + + /* Create root object */ + INSERT INTO ldb_object + (tree_key, parent_tree_key, + full_path, + object_type, max_child_num) + VALUES ('', NULL, + '', + 1, 0); /* We need an implicit "top" level object class */ - INSERT INTO ldb_object_classes (class_name, tree_key) - SELECT 'top', /* next_tree_key(NULL) */ '0001'; + INSERT INTO ldb_attributes (attr_name, + parent_tree_key) + SELECT 'top', ''; -- ------------------------------------------------------ @@ -129,63 +182,103 @@ * objectclass: domainRelatedObject */ -- newDN -INSERT INTO ldb_distinguished_names (dn_id, dn) - VALUES (1, 'o=University of Michigan,c=US'); +BEGIN; + +INSERT OR IGNORE INTO ldb_object + (parent_tree_key + full_path, + attr_name, attr_value, object_type, max_child_num) + VALUES ('', + 'c=US', + 'c', 'US', 1, 0); + +INSERT INTO ldb_object + (parent_tree_key, + full_path, + 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_object_classes (class_name, tree_key) - SELECT 'organization', /* next_tree_key(NULL) */ '0002'; - -INSERT OR IGNORE INTO ldb_object_classes (class_name, tree_key) - SELECT 'domainRelatedObject', /* next_tree_key(NULL) */ '0003'; +INSERT OR IGNORE INTO ldb_attributes + (attr_name, parent_tree_key, objectclass_p) + VALUES + ('objectclass', '', 1); + +INSERT INTO ldb_object + (parent_tree_key, + full_path, + 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, + full_path, + attr_name, attr_value, object_type, max_child_num) + VALUES ('00010001', + NULL, + 'objectclass', 'domainRelatedObject', 2, 0); + +COMMIT; --- assignObjectClass -INSERT OR IGNORE INTO ldb_dn_object_classes (dn_id, class_name) - VALUES (1, 'organization'); - -INSERT OR IGNORE INTO ldb_dn_object_classes (dn_id, class_name) - VALUES (1, 'domainRelatedObject'); /* + * dn: o=University of Michigan,c=US * l: Ann Arbor, Michigan * st: Michigan * o: University of Michigan * o: UMICH - * o: UM - * o: U-M - * o: U of M - * description: The University of Michigan at Ann Arbor * seeAlso: - * postaladdress: University of Michigan $ 535 W. William St. $ Ann Arbor, MI 481 - * 09 $ US * telephonenumber: +1 313 764-1817 - * associateddomain: example.com */ -- addAttrValuePair -INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value) - VALUES (1, 'l', 'Ann Arbor, Michigan'); -INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value) - VALUES (1, 'st', 'Michigan'); -INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value) - VALUES (1, 'o', 'University of Michigan'); -INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value) - VALUES (1, 'o', 'UMICH'); -INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value) - VALUES (1, 'o', 'UM'); -INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value) - VALUES (1, 'o', 'U-M'); -INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value) - VALUES (1, 'o', 'U of M'); -INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value) - VALUES (1, 'description', 'The University of Michigan at Ann Arbor'); -INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value) - VALUES (1, 'seeAlso', ''); -INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value) - VALUES (1, 'postaladdress', 'University of Michigan $ 535 W. William St. $ Ann Arbor, MI 48109 $ US'); -INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value) - VALUES (1, 'telephonenumber', '+1 313 764-1817'); -INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value) - VALUES (1, 'associateddomain', 'example.com'); +BEGIN; + +INSERT INTO ldb_object + (parent_tree_key, full_path, + 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, full_path, + attr_name, attr_value, object_type, max_child_num) + VALUES ('00010001', NULL, + 'st', 'Michigan', 2, 0); + +INSERT INTO ldb_object + (parent_tree_key, full_path, + 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, full_path, + attr_name, attr_value, object_type, max_child_num) + VALUES ('00010001', NULL, + 'o', 'UMICH', 2, 0); + +INSERT INTO ldb_object + (parent_tree_key, full_path, + attr_name, attr_value, object_type, max_child_num) + VALUES ('00010001', NULL, + 'seeAlso', '', 2, 0); + +INSERT INTO ldb_object + (parent_tree_key, full_path, + attr_name, attr_value, object_type, max_child_num) + VALUES ('00010001', NULL, + 'telephonenumber', '+1 313 764-1817', 2, 0); + +COMMIT; -- ---------------------------------------------------------------------- @@ -197,18 +290,41 @@ INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value) * dn: CASE_INSENSITIVE */ -- newAttribute -INSERT OR REPLACE INTO ldb_attributes - (attr_name, case_insensitive_p, wildcard_p, hidden_p, integer_p) - VALUES ('uid', 1, 1, 0, 0); -INSERT OR REPLACE INTO ldb_attributes - (attr_name, case_insensitive_p, wildcard_p, hidden_p, integer_p) - VALUES ('cn', 1, 0, 0, 0); -INSERT OR REPLACE INTO ldb_attributes - (attr_name, case_insensitive_p, wildcard_p, hidden_p, integer_p) - VALUES ('ou', 1, 0, 0, 0); -INSERT OR REPLACE INTO ldb_attributes - (attr_name, case_insensitive_p, wildcard_p, hidden_p, integer_p) - VALUES ('dn', 1, 0, 0, 0); + +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' -- ---------------------------------------------------------------------- @@ -224,6 +340,10 @@ INSERT OR REPLACE INTO ldb_attributes * user: computer */ -- insertSubclass + +/* NOT YET UPDATED!!! * + + INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key) SELECT 'domain', /* next_tree_key('top') */ '00010001'; INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key) @@ -240,3 +360,4 @@ INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key) SELECT 'OpenLDAPperson', /* next_tree_key('organizationPerson') */ '0001000200010002'; INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key) SELECT 'computer', /* next_tree_key('user') */ '0001000200010001'; + |