From 26eff174ee8bf6b76ecf7f641286ad59c08f2672 Mon Sep 17 00:00:00 2001 From: Derrell Lipman Date: Tue, 24 May 2005 13:31:22 +0000 Subject: r6956: added start of ldb_sqlite3 work (This used to be commit ac396a4a53756f40ad5e1d45ca23e002f9c649e7) --- source4/lib/ldb/ldb_sqlite3/schema | 240 +++++++++++++++++++++++++++++++++++++ 1 file changed, 240 insertions(+) create mode 100644 source4/lib/ldb/ldb_sqlite3/schema (limited to 'source4/lib/ldb/ldb_sqlite3/schema') diff --git a/source4/lib/ldb/ldb_sqlite3/schema b/source4/lib/ldb/ldb_sqlite3/schema new file mode 100644 index 0000000000..2ba188c785 --- /dev/null +++ b/source4/lib/ldb/ldb_sqlite3/schema @@ -0,0 +1,240 @@ + -- ------------------------------------------------------ + + PRAGMA auto_vacuum=1; + + -- ------------------------------------------------------ + + BEGIN EXCLUSIVE; + + -- ------------------------------------------------------ + + CREATE TABLE ldb_info AS + SELECT 'LDB' AS database_type, + '1.0' AS version; + + CREATE TABLE ldb_distinguished_names + ( + dn_id INTEGER PRIMARY KEY AUTOINCREMENT, + dn TEXT UNIQUE + ); + + CREATE TABLE ldb_object_classes + ( + 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 + ); + + 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 + ); + + CREATE TABLE ldb_attr_value_pairs + ( + dn_id INTEGER REFERENCES ldb_distinguished_names, + attr_name TEXT REFERENCES ldb_attributes, + attr_value TEXT + ); + + -- ------------------------------------------------------ + + CREATE TRIGGER ldb_distinguished_names_delete_tr + AFTER DELETE + ON ldb_distinguished_names + 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; + END; + + CREATE TRIGGER ldb_attr_value_pairs_insert_tr + BEFORE INSERT + ON ldb_attr_value_pairs + FOR EACH ROW + BEGIN + INSERT OR IGNORE INTO ldb_attributes + (attr_name) + VALUES + (new.attr_name); + END; + + CREATE TRIGGER ldb_attr_value_pairs_delete_tr + AFTER DELETE + ON ldb_attr_value_pairs + 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; + 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'); + + /* We need an implicit "top" level object class */ + INSERT INTO ldb_object_classes (class_name, tree_key) + SELECT 'top', /* next_tree_key(NULL) */ '0001'; + + -- ------------------------------------------------------ + + COMMIT; + + -- ------------------------------------------------------ + +/* + * dn: o=University of Michigan,c=US + * objectclass: organization + * objectclass: domainRelatedObject + */ +-- newDN +INSERT INTO ldb_distinguished_names (dn_id, dn) + VALUES (1, 'o=University of Michigan,c=US'); + +-- 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'; + +-- 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'); + +/* + * 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'); + +-- ---------------------------------------------------------------------- + +/* + * dn: @ATTRIBUTES + * uid: CASE_INSENSITIVE WILDCARD + * cn: CASE_INSENSITIVE + * ou: CASE_INSENSITIVE + * 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); + +-- ---------------------------------------------------------------------- + +/* + * dn: @SUBCLASSES + * top: domain + * top: person + * domain: domainDNS + * person: organizationalPerson + * person: fooPerson + * organizationalPerson: user + * organizationalPerson: OpenLDAPperson + * user: computer + */ +-- insertSubclass +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) + SELECT 'person', /* next_tree_key('top') */ '00010002'; +INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key) + SELECT 'domainDNS', /* next_tree_key('domain') */ '000100010001'; +INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key) + SELECT 'organizationalPerson', /* next_tree_key('person') */ '000100020001'; +INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key) + SELECT 'fooPerson', /* next_tree_key('person') */ '000100020002'; +INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key) + SELECT 'user', /* next_tree_key('organizationalPerson') */ '0001000200010001'; +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'; -- cgit From a3972de8949f5d1c804c316b0be61c17e61d903b Mon Sep 17 00:00:00 2001 From: Derrell Lipman Date: Mon, 30 May 2005 16:46:54 +0000 Subject: r7116: work in progress (This used to be commit c860a4f9940c04021ecc859240c5f35c3d1c4bed) --- source4/lib/ldb/ldb_sqlite3/schema | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-) (limited to 'source4/lib/ldb/ldb_sqlite3/schema') diff --git a/source4/lib/ldb/ldb_sqlite3/schema b/source4/lib/ldb/ldb_sqlite3/schema index 2ba188c785..d06d7d0c34 100644 --- a/source4/lib/ldb/ldb_sqlite3/schema +++ b/source4/lib/ldb/ldb_sqlite3/schema @@ -43,8 +43,10 @@ CREATE TABLE ldb_attr_value_pairs ( dn_id INTEGER REFERENCES ldb_distinguished_names, - attr_name TEXT REFERENCES ldb_attributes, - attr_value TEXT + attr_name TEXT, -- optionally REFERENCES ldb_attributes + attr_value TEXT, + + UNIQUE (dn_id, attr_name, attr_value) ); -- ------------------------------------------------------ -- cgit From 913c19cd5b9b43f653652967edf9dc4d9816c79b Mon Sep 17 00:00:00 2001 From: Derrell Lipman Date: Tue, 31 May 2005 03:31:54 +0000 Subject: r7133: work in progress, including a HIGHLY revised and simplified schema (This used to be commit aa9bb6ad4cb7219a80a6589d406e47a15ac2c3a1) --- source4/lib/ldb/ldb_sqlite3/schema | 377 ++++++++++++++++++++++++------------- 1 file changed, 249 insertions(+), 128 deletions(-) (limited to 'source4/lib/ldb/ldb_sqlite3/schema') 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'; + -- cgit From 6ade5fc245a6f56b991b1b66742509270d65479d Mon Sep 17 00:00:00 2001 From: Derrell Lipman Date: Sat, 4 Jun 2005 02:31:45 +0000 Subject: r7260: save current schema before I blow it away to try something different (This used to be commit 676a9824934576056208d30ce34716cfb734ce58) --- source4/lib/ldb/ldb_sqlite3/schema | 28 ++++++++++++++-------------- 1 file changed, 14 insertions(+), 14 deletions(-) (limited to 'source4/lib/ldb/ldb_sqlite3/schema') diff --git a/source4/lib/ldb/ldb_sqlite3/schema b/source4/lib/ldb/ldb_sqlite3/schema index 45be5b578f..08dc50de08 100644 --- a/source4/lib/ldb/ldb_sqlite3/schema +++ b/source4/lib/ldb/ldb_sqlite3/schema @@ -30,7 +30,7 @@ tree_key TEXT PRIMARY KEY, parent_tree_key TEXT, - full_path TEXT, + dn TEXT, attr_name TEXT REFERENCES ldb_attributes, attr_value TEXT, @@ -85,8 +85,8 @@ -- ------------------------------------------------------ - CREATE INDEX ldb_object_full_path_idx - ON ldb_object (full_path); + CREATE INDEX ldb_object_dn_idx + ON ldb_object (dn); CREATE INDEX ldb_attributes_tree_key_ids ON ldb_attributes (tree_key); @@ -159,7 +159,7 @@ /* Create root object */ INSERT INTO ldb_object (tree_key, parent_tree_key, - full_path, + dn, object_type, max_child_num) VALUES ('', NULL, '', @@ -186,7 +186,7 @@ BEGIN; INSERT OR IGNORE INTO ldb_object (parent_tree_key - full_path, + dn, attr_name, attr_value, object_type, max_child_num) VALUES ('', 'c=US', @@ -194,7 +194,7 @@ INSERT OR IGNORE INTO ldb_object INSERT INTO ldb_object (parent_tree_key, - full_path, + dn, attr_name, attr_value, object_type, max_child_num) VALUES ('0001', 'o=University of Michigan,c=US', @@ -208,7 +208,7 @@ INSERT OR IGNORE INTO ldb_attributes INSERT INTO ldb_object (parent_tree_key, - full_path, + dn, attr_name, attr_value, object_type, max_child_num) VALUES ('00010001', NULL, @@ -221,7 +221,7 @@ INSERT OR IGNORE INTO ldb_attributes INSERT INTO ldb_object (parent_tree_key, - full_path, + dn, attr_name, attr_value, object_type, max_child_num) VALUES ('00010001', NULL, @@ -243,37 +243,37 @@ COMMIT; BEGIN; INSERT INTO ldb_object - (parent_tree_key, full_path, + (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, full_path, + (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, full_path, + (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, full_path, + (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, full_path, + (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, full_path, + (parent_tree_key, dn, attr_name, attr_value, object_type, max_child_num) VALUES ('00010001', NULL, 'telephonenumber', '+1 313 764-1817', 2, 0); -- cgit From a1ba224107fbcf6f8a9a3091f42cde2a0c47f85e Mon Sep 17 00:00:00 2001 From: Derrell Lipman Date: Sat, 4 Jun 2005 17:13:43 +0000 Subject: r7276: - moved static tdb function ltdb_dn_fold() into common/ so that it can be called from multiple backends. (ldb_sqlite3 needs it too.) Added parameter for a callback function that determines whether an attribute needs case folding. - begin to prepare for sqlite3 in build process - work-in-progress updates, on ldb_sqlite3 (This used to be commit a80bced0b96ffb655559a43cf7f4d7a34deb5a7d) --- source4/lib/ldb/ldb_sqlite3/schema | 207 +++++++++++++++---------------------- 1 file changed, 84 insertions(+), 123 deletions(-) (limited to 'source4/lib/ldb/ldb_sqlite3/schema') diff --git a/source4/lib/ldb/ldb_sqlite3/schema b/source4/lib/ldb/ldb_sqlite3/schema index 08dc50de08..78550985d4 100644 --- a/source4/lib/ldb/ldb_sqlite3/schema +++ b/source4/lib/ldb/ldb_sqlite3/schema @@ -12,158 +12,119 @@ SELECT 'LDB' AS database_type, '1.0' AS version; + -- ------------------------------------------------------ + -- Schema + /* - * Get the next USN value with: - * BEGIN EXCLUSIVE; - * UPDATE usn SET value = value + 1; - * SELECT value FROM usn; - * COMMIT; + * The entry table holds the information about an entry. This + * table is used to obtain the EID of the entry and to support + * scope="one" and scope="base". The parent and child table + * is included in the entry table since all the other + * attributes on EID. */ - CREATE TABLE usn + CREATE TABLE ldb_entry ( - value INTEGER + -- Unique identifier of this LDB entry + eid INTEGER PRIMARY KEY, + + -- Unique identifier of the parent LDB entry + peid INTEGER REFERENCES ldb_entry, + + -- Distinguished name of this entry + dn TEXT, + + -- Time when the entry was created + create_timestamp INTEGER, + + -- Time when the entry was last modified + modify_timestamp INTEGER, + + -- Attributes of this entry, in the form + -- attr\1value\0[attr\1value\0]*\0 + entry_data TEXT ); - CREATE TABLE ldb_object + + /* + * The purpose of the descendant table is to support the + * subtree search feature. For each LDB entry with a unique + * ID (AEID), this table contains the unique identifiers + * (DEID) of the descendant entries. + * + * For evern entry in the directory, a row exists in this + * table for each of its ancestors including itself. The size + * of the table depends on the depth of each entry. In the + * worst case, if all the entries were at the same depth, the + * number of rows in the table is O(nm) where n is the number + * of nodes in the directory and m is the depth of the tree. + */ + CREATE TABLE ldb_descendants ( - /* 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) + -- The unique identifier of the ancestor LDB entry + aeid INTEGER REFERENCES ldb_entry, + + -- The unique identifier of the descendant LDB entry + deid INTEGER REFERENCES ldb_entry ); - CREATE TABLE ldb_attributes - ( - attr_name TEXT PRIMARY KEY, - parent_tree_key TEXT, - objectclass_p BOOLEAN DEFAULT 0, + CREATE TABLE ldb_object_classes + ( + -- Object classes are inserted into this table to track + -- their class hierarchy. 'top' is the top-level class + -- of which all other classes are subclasses. + class_name TEXT PRIMARY KEY, + + -- tree_key tracks the position of the class in + -- the hierarchy + tree_key TEXT UNIQUE + ); - case_insensitive_p BOOLEAN DEFAULT 0, - wildcard_p BOOLEAN DEFAULT 0, - hidden_p BOOLEAN DEFAULT 0, - integer_p BOOLEAN DEFAULT 0, + /* + * There is one attribute table per searchable attribute. + */ +/* + CREATE TABLE ldb_attr_ATTRIBUTE_NAME + ( + -- The unique identifier of the LDB entry + eid INTEGER REFERENCES ldb_entry, - /* 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 + -- Normalized attribute value + attr_value TEXT ); +*/ - -- ------------------------------------------------------ - CREATE INDEX ldb_object_dn_idx - ON ldb_object (dn); + -- ------------------------------------------------------ + -- Indexes - CREATE INDEX ldb_attributes_tree_key_ids - ON ldb_attributes (tree_key); -- ------------------------------------------------------ + -- Triggers - /* Gifts for metze. Automatically updated meta-data */ - CREATE TRIGGER ldb_object_insert_tr + CREATE TRIGGER ldb_entry_insert_tr AFTER INSERT - ON ldb_object + ON ldb_entry 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; + UPDATE ldb_entry + SET create_timestamp = strftime('%s', 'now'), + modify_timestamp = strftime('%s', 'now') + WHERE eid = new.eid; END; - CREATE TRIGGER ldb_object_update_tr + CREATE TRIGGER ldb_entry_update_tr AFTER UPDATE - ON ldb_object + ON ldb_entry 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; + UPDATE ldb_entry + SET modify_timestamp = strftime('%s', 'now') + WHERE eid = old.eid; 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); + -- Table initialization /* We need an implicit "top" level object class */ INSERT INTO ldb_attributes (attr_name, -- cgit From 217a8cfe6677c0d435992f96996d5415824f598b Mon Sep 17 00:00:00 2001 From: Derrell Lipman Date: Sat, 4 Jun 2005 21:16:54 +0000 Subject: r7282: ldb_sqlite3 work in progress. (This used to be commit d934c42b00b68e8f4ac9d0583ac307818aeb494f) --- source4/lib/ldb/ldb_sqlite3/schema | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'source4/lib/ldb/ldb_sqlite3/schema') diff --git a/source4/lib/ldb/ldb_sqlite3/schema b/source4/lib/ldb/ldb_sqlite3/schema index 78550985d4..dddca8d48f 100644 --- a/source4/lib/ldb/ldb_sqlite3/schema +++ b/source4/lib/ldb/ldb_sqlite3/schema @@ -20,7 +20,7 @@ * table is used to obtain the EID of the entry and to support * scope="one" and scope="base". The parent and child table * is included in the entry table since all the other - * attributes on EID. + * attributes are dependent on EID. */ CREATE TABLE ldb_entry ( @@ -126,7 +126,7 @@ -- ------------------------------------------------------ -- Table initialization - /* We need an implicit "top" level object class */ + /* We need an implicit 'top' level object class */ INSERT INTO ldb_attributes (attr_name, parent_tree_key) SELECT 'top', ''; -- cgit From 2e1851e1acae4aba40fd38bc0f7b088599e9cba9 Mon Sep 17 00:00:00 2001 From: Derrell Lipman Date: Sun, 12 Jun 2005 03:07:10 +0000 Subject: r7498: ldb_sqlite3 work in progress (This used to be commit 797263330b9eada019e432ff201bf5c872e35b5d) --- source4/lib/ldb/ldb_sqlite3/schema | 19 ++++++++++++++----- 1 file changed, 14 insertions(+), 5 deletions(-) (limited to 'source4/lib/ldb/ldb_sqlite3/schema') diff --git a/source4/lib/ldb/ldb_sqlite3/schema b/source4/lib/ldb/ldb_sqlite3/schema index dddca8d48f..b02b806150 100644 --- a/source4/lib/ldb/ldb_sqlite3/schema +++ b/source4/lib/ldb/ldb_sqlite3/schema @@ -37,11 +37,7 @@ create_timestamp INTEGER, -- Time when the entry was last modified - modify_timestamp INTEGER, - - -- Attributes of this entry, in the form - -- attr\1value\0[attr\1value\0]*\0 - entry_data TEXT + modify_timestamp INTEGER ); @@ -80,6 +76,19 @@ tree_key TEXT UNIQUE ); + /* + * We keep a full listing of attribute/value pairs here + */ + CREATE TABLE ldb_attribute_values + ( + eid INTEGER REFERENCES ldb_entry, + + attr_name TEXT, -- see ldb_attr_ATTRIBUTE_NAME + + attr_value TEXT + ); + + /* * There is one attribute table per searchable attribute. */ -- cgit From c2747479e04761d86f0de4aa90b4d793856d0fb7 Mon Sep 17 00:00:00 2001 From: Derrell Lipman Date: Tue, 14 Jun 2005 21:52:35 +0000 Subject: r7586: ldb_sqlite3 making progress. add and search have indicated a willingness to operate properly on initial testing (This used to be commit 86ca8639e0ddc2525f8ed0ca9879d9f98c0cd00e) --- source4/lib/ldb/ldb_sqlite3/schema | 151 +------------------------------------ 1 file changed, 2 insertions(+), 149 deletions(-) (limited to 'source4/lib/ldb/ldb_sqlite3/schema') diff --git a/source4/lib/ldb/ldb_sqlite3/schema b/source4/lib/ldb/ldb_sqlite3/schema index b02b806150..c44351c543 100644 --- a/source4/lib/ldb/ldb_sqlite3/schema +++ b/source4/lib/ldb/ldb_sqlite3/schema @@ -146,58 +146,13 @@ -- ------------------------------------------------------ +/*** TESTS ***/ + /* * 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; /* @@ -209,48 +164,6 @@ COMMIT; * 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 @@ -259,44 +172,6 @@ COMMIT; * 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' - --- ---------------------------------------------------------------------- /* * dn: @SUBCLASSES @@ -309,25 +184,3 @@ UPDATE ldb_attributes * organizationalPerson: OpenLDAPperson * 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) - SELECT 'person', /* next_tree_key('top') */ '00010002'; -INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key) - SELECT 'domainDNS', /* next_tree_key('domain') */ '000100010001'; -INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key) - SELECT 'organizationalPerson', /* next_tree_key('person') */ '000100020001'; -INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key) - SELECT 'fooPerson', /* next_tree_key('person') */ '000100020002'; -INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key) - SELECT 'user', /* next_tree_key('organizationalPerson') */ '0001000200010001'; -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'; - -- cgit From fdc0450db25021eddcc65d032fd3fd8ca5976928 Mon Sep 17 00:00:00 2001 From: Derrell Lipman Date: Wed, 22 Jun 2005 02:39:07 +0000 Subject: r7828: Although there is still plenty to do, ldb_sqlite3 now passes the set of tests in tests/test-sqlite3.sh (tests/test-generic.sh). There are lots of optimizations still TBD, and some things are REALLY slow right now (e.g. each add() operation takes 1/3 - 1/2 second) but it's ready for interested parties to poke it and prod it and see how (un)reasonable it is. Play away. Still to be implemented or improved: - tdb specials (@MODULES, @SUBCLASSES, etc.) - all DNs are case-folded in their entirty right now (since doing otherwise would require @ATTRIBUTES to be implemented) - speed improvements and optimizations. I am quite confident that the excessively slow add() operation can be much improved, and other areas can be somewhat improved. (This used to be commit 1dd865005594671e7effe06fb088fa97fa08de0b) --- source4/lib/ldb/ldb_sqlite3/schema | 367 +++++++++++++++++++++++++++---------- 1 file changed, 272 insertions(+), 95 deletions(-) (limited to 'source4/lib/ldb/ldb_sqlite3/schema') diff --git a/source4/lib/ldb/ldb_sqlite3/schema b/source4/lib/ldb/ldb_sqlite3/schema index c44351c543..08dc50de08 100644 --- a/source4/lib/ldb/ldb_sqlite3/schema +++ b/source4/lib/ldb/ldb_sqlite3/schema @@ -12,130 +12,160 @@ SELECT 'LDB' AS database_type, '1.0' AS version; - -- ------------------------------------------------------ - -- Schema - /* - * The entry table holds the information about an entry. This - * table is used to obtain the EID of the entry and to support - * scope="one" and scope="base". The parent and child table - * is included in the entry table since all the other - * attributes are dependent on EID. + * Get the next USN value with: + * BEGIN EXCLUSIVE; + * UPDATE usn SET value = value + 1; + * SELECT value FROM usn; + * COMMIT; */ - CREATE TABLE ldb_entry + CREATE TABLE usn ( - -- Unique identifier of this LDB entry - eid INTEGER PRIMARY KEY, - - -- Unique identifier of the parent LDB entry - peid INTEGER REFERENCES ldb_entry, - - -- Distinguished name of this entry - dn TEXT, - - -- Time when the entry was created - create_timestamp INTEGER, - - -- Time when the entry was last modified - modify_timestamp INTEGER + value INTEGER ); - - /* - * The purpose of the descendant table is to support the - * subtree search feature. For each LDB entry with a unique - * ID (AEID), this table contains the unique identifiers - * (DEID) of the descendant entries. - * - * For evern entry in the directory, a row exists in this - * table for each of its ancestors including itself. The size - * of the table depends on the depth of each entry. In the - * worst case, if all the entries were at the same depth, the - * number of rows in the table is O(nm) where n is the number - * of nodes in the directory and m is the depth of the tree. - */ - CREATE TABLE ldb_descendants + CREATE TABLE ldb_object ( - -- The unique identifier of the ancestor LDB entry - aeid INTEGER REFERENCES ldb_entry, - - -- The unique identifier of the descendant LDB entry - deid INTEGER REFERENCES ldb_entry - ); - - - CREATE TABLE ldb_object_classes - ( - -- Object classes are inserted into this table to track - -- their class hierarchy. 'top' is the top-level class - -- of which all other classes are subclasses. - class_name TEXT PRIMARY KEY, - - -- tree_key tracks the position of the class in - -- the hierarchy - tree_key TEXT UNIQUE + /* 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) ); - /* - * We keep a full listing of attribute/value pairs here - */ - CREATE TABLE ldb_attribute_values + CREATE TABLE ldb_attributes ( - eid INTEGER REFERENCES ldb_entry, - - attr_name TEXT, -- see ldb_attr_ATTRIBUTE_NAME + attr_name TEXT PRIMARY KEY, + parent_tree_key TEXT, - 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, - /* - * There is one attribute table per searchable attribute. - */ -/* - CREATE TABLE ldb_attr_ATTRIBUTE_NAME - ( - -- The unique identifier of the LDB entry - eid INTEGER REFERENCES ldb_entry, - - -- Normalized attribute value - attr_value TEXT + /* 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 ); -*/ - -- ------------------------------------------------------ - -- Indexes + CREATE INDEX ldb_object_dn_idx + ON ldb_object (dn); + + CREATE INDEX ldb_attributes_tree_key_ids + ON ldb_attributes (tree_key); -- ------------------------------------------------------ - -- Triggers - CREATE TRIGGER ldb_entry_insert_tr + /* Gifts for metze. Automatically updated meta-data */ + CREATE TRIGGER ldb_object_insert_tr AFTER INSERT - ON ldb_entry + ON ldb_object FOR EACH ROW BEGIN - UPDATE ldb_entry - SET create_timestamp = strftime('%s', 'now'), - modify_timestamp = strftime('%s', 'now') - WHERE eid = new.eid; + 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_entry_update_tr + CREATE TRIGGER ldb_object_update_tr AFTER UPDATE - ON ldb_entry + ON ldb_object FOR EACH ROW BEGIN - UPDATE ldb_entry - SET modify_timestamp = strftime('%s', 'now') - WHERE eid = old.eid; + 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; + + -- ------------------------------------------------------ - -- Table initialization - /* We need an implicit 'top' level object class */ + /* 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', ''; @@ -146,13 +176,58 @@ -- ------------------------------------------------------ -/*** TESTS ***/ - /* * 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; /* @@ -164,6 +239,48 @@ * 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 @@ -172,6 +289,44 @@ * 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' + +-- ---------------------------------------------------------------------- /* * dn: @SUBCLASSES @@ -184,3 +339,25 @@ * organizationalPerson: OpenLDAPperson * 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) + SELECT 'person', /* next_tree_key('top') */ '00010002'; +INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key) + SELECT 'domainDNS', /* next_tree_key('domain') */ '000100010001'; +INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key) + SELECT 'organizationalPerson', /* next_tree_key('person') */ '000100020001'; +INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key) + SELECT 'fooPerson', /* next_tree_key('person') */ '000100020002'; +INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key) + SELECT 'user', /* next_tree_key('organizationalPerson') */ '0001000200010001'; +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'; + -- cgit From 5c6364ba0655316294833f192281d49a4de63b0c Mon Sep 17 00:00:00 2001 From: Andrew Bartlett Date: Mon, 18 Aug 2008 12:01:27 +1000 Subject: Remove references to the unused @SUBCLASS feature. This was removed from ldb_tdb a while ago Andrew Bartlett (This used to be commit fcb87e77860b449ac3483ccec5e6b5ed087540f2) --- source4/lib/ldb/ldb_sqlite3/schema | 35 ----------------------------------- 1 file changed, 35 deletions(-) (limited to 'source4/lib/ldb/ldb_sqlite3/schema') diff --git a/source4/lib/ldb/ldb_sqlite3/schema b/source4/lib/ldb/ldb_sqlite3/schema index 08dc50de08..ab7c5cc406 100644 --- a/source4/lib/ldb/ldb_sqlite3/schema +++ b/source4/lib/ldb/ldb_sqlite3/schema @@ -326,38 +326,3 @@ UPDATE ldb_attributes integer_p = 0 WHERE attr_name = 'dn' --- ---------------------------------------------------------------------- - -/* - * dn: @SUBCLASSES - * top: domain - * top: person - * domain: domainDNS - * person: organizationalPerson - * person: fooPerson - * organizationalPerson: user - * organizationalPerson: OpenLDAPperson - * 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) - SELECT 'person', /* next_tree_key('top') */ '00010002'; -INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key) - SELECT 'domainDNS', /* next_tree_key('domain') */ '000100010001'; -INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key) - SELECT 'organizationalPerson', /* next_tree_key('person') */ '000100020001'; -INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key) - SELECT 'fooPerson', /* next_tree_key('person') */ '000100020002'; -INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key) - SELECT 'user', /* next_tree_key('organizationalPerson') */ '0001000200010001'; -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'; - -- cgit