summaryrefslogtreecommitdiff
path: root/source4/lib/ldb/ldb_sqlite3/schema
diff options
context:
space:
mode:
Diffstat (limited to 'source4/lib/ldb/ldb_sqlite3/schema')
-rw-r--r--source4/lib/ldb/ldb_sqlite3/schema377
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';
+