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

                PRAGMA auto_vacuum=1;

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

                BEGIN EXCLUSIVE;

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

                CREATE TABLE ldb_info AS 
                  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 on EID.
                 */
                CREATE TABLE ldb_entry
                (
                  -- 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
                );


                /*
                 * 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
                (
                  -- 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
                );

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


                -- ------------------------------------------------------
                -- Indexes


                -- ------------------------------------------------------
                -- Triggers

                CREATE TRIGGER ldb_entry_insert_tr
                  AFTER INSERT
                  ON ldb_entry
                  FOR EACH ROW
                    BEGIN
                      UPDATE ldb_entry
                        SET create_timestamp = strftime('%s', 'now'),
                            modify_timestamp = strftime('%s', 'now')
                        WHERE eid = new.eid;
                    END;

                CREATE TRIGGER ldb_entry_update_tr
                  AFTER UPDATE
                  ON ldb_entry
                  FOR EACH ROW
                    BEGIN
                      UPDATE ldb_entry
                        SET modify_timestamp = strftime('%s', 'now')
                        WHERE eid = old.eid;
                    END;

                -- ------------------------------------------------------
                -- Table initialization

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


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