r7133: work in progress, including a HIGHLY revised and simplified schema
[kai/samba.git] / source4 / lib / ldb / ldb_sqlite3 / schema
index d06d7d0c3435b5525ad8eef002854e9c23fa1078..45be5b578f4b0a01a06c5703a37984ae0a1546bc 100644 (file)
                   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', '';
 
                 -- ------------------------------------------------------
 
  * 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';