path: root/source4/lib/ldb/ldb_sqlite3/schema

                -- ------------------------------------------------------

                PRAGMA auto_vacuum=1;

                -- ------------------------------------------------------

                BEGIN EXCLUSIVE;

                -- ------------------------------------------------------

                CREATE TABLE ldb_info AS 
                  SELECT 'LDB' AS database_type, 
                         '1.0' AS version;

                /*
                 * Get the next USN value with:
                 *   BEGIN EXCLUSIVE;
                 *   UPDATE usn SET value = value + 1;
                 *   SELECT value FROM usn;
                 *   COMMIT;
                 */
                CREATE TABLE usn
                (
                  value           INTEGER
                );

                CREATE TABLE ldb_object
                (
                  /* tree_key is auto-generated by the insert trigger */
                  tree_key        TEXT PRIMARY KEY,

                  parent_tree_key TEXT,
                  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,
                  parent_tree_key       TEXT,

                  objectclass_p         BOOLEAN DEFAULT 0,

                  case_insensitive_p    BOOLEAN DEFAULT 0,
                  wildcard_p            BOOLEAN DEFAULT 0,
                  hidden_p              BOOLEAN DEFAULT 0,
                  integer_p             BOOLEAN DEFAULT 0,

                  /* tree_key is auto-generated by the insert trigger */
                  tree_key              TEXT, -- null if not a object/sub class
                                              -- level 1 if an objectclass
                                              -- level 1-n if a subclass
                  max_child_num         INTEGER
                );

                -- ------------------------------------------------------

                CREATE INDEX ldb_object_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
                      UPDATE ldb_object
                        SET max_child_num = max_child_num + 1
                        WHERE tree_key = new.parent_tree_key;
                      UPDATE usn SET value = value + 1;
                      UPDATE ldb_object
                        SET tree_key =
                              (SELECT
                                 new.tree_key ||
                                 base160(SELECT max_child_num
                                           FROM ldb_object
                                           WHERE tree_key =
                                                 new.parent_tree_key));
                            max_child_num = 0,
                            object_guid = random_guid(),
                            timestamp = strftime('%s', 'now'),
                            usn = (SELECT value FROM usn);
                        WHERE tree_key = new.tree_key;
                    END;

                CREATE TRIGGER ldb_object_update_tr
                  AFTER UPDATE
                  ON ldb_object
                  FOR EACH ROW
                    BEGIN
                      UPDATE usn SET value = value + 1;
                      UPDATE ldb_object
                        SET timestamp = strftime('%s', 'now'),
                            usn = (SELECT value FROM usn);
                        WHERE tree_key = new.tree_key;
                    END;

                CREATE TRIGGER ldb_attributes_insert_tr
                  AFTER INSERT
                  ON ldb_attributes
                  FOR EACH ROW
                    BEGIN
                      UPDATE ldb_attributes
                        SET max_child_num = max_child_num + 1
                        WHERE tree_key = new.parent_tree_key;
                      UPDATE ldb_attributes
                        SET tree_key =
                              (SELECT
                                 new.tree_key ||
                                 base160(SELECT max_child_num
                                           FROM ldb_attributes
                                           WHERE tree_key =
                                                 new.parent_tree_key));
                            max_child_num = 0
                        WHERE tree_key = new.tree_key;
                    END;


                -- ------------------------------------------------------

                /* Initialize usn */
                INSERT INTO usn (value) VALUES (0);

                /* Create root object */
                INSERT INTO ldb_object
                    (tree_key, parent_tree_key,
                     full_path,
                     object_type, max_child_num)
                  VALUES ('', NULL,
                          '',
                          1, 0);

                /* We need an implicit "top" level object class */
                INSERT INTO ldb_attributes (attr_name,
                                            parent_tree_key)
                  SELECT 'top', '';

                -- ------------------------------------------------------

                COMMIT;

                -- ------------------------------------------------------

/*
 * dn: o=University of Michigan,c=US
 * objectclass: organization
 * objectclass: domainRelatedObject
 */
-- newDN
BEGIN;

INSERT OR IGNORE INTO ldb_object
    (parent_tree_key
     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_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;


/*
 * dn: o=University of Michigan,c=US
 * l: Ann Arbor, Michigan
 * st: Michigan
 * o: University of Michigan
 * o: UMICH
 * seeAlso:
 * telephonenumber: +1 313 764-1817
 */
-- addAttrValuePair
BEGIN;

INSERT INTO ldb_object
    (parent_tree_key, 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;

-- ----------------------------------------------------------------------

/*
 * dn: @ATTRIBUTES
 * uid: CASE_INSENSITIVE WILDCARD
 * cn: CASE_INSENSITIVE
 * ou: CASE_INSENSITIVE
 * dn: CASE_INSENSITIVE
 */
-- newAttribute

BEGIN;

INSERT OR IGNORE INTO ldb_attributes
    (attr_name, parent_tree_key, objectclass_p)
  VALUES
    ('uid', '', 0);

UPDATE ldb_attributes
  SET case_insensitive_p = 1,
      wildcard_p = 1,
      hidden_p = 0,
      integer_p = 0
  WHERE attr_name = 'uid'

UPDATE ldb_attributes
  SET case_insensitive_p = 1,
      wildcard_p = 0,
      hidden_p = 0,
      integer_p = 0
  WHERE attr_name = 'cn'

UPDATE ldb_attributes
  SET case_insensitive_p = 1,
      wildcard_p = 0,
      hidden_p = 0,
      integer_p = 0
  WHERE attr_name = 'ou'

UPDATE ldb_attributes
  SET case_insensitive_p = 1,
      wildcard_p = 0,
      hidden_p = 0,
      integer_p = 0
  WHERE attr_name = 'dn'

-- ----------------------------------------------------------------------

/*
 * 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';