1 -- ------------------------------------------------------
5 -- ------------------------------------------------------
9 -- ------------------------------------------------------
11 CREATE TABLE ldb_info AS
12 SELECT 'LDB' AS database_type,
15 CREATE TABLE ldb_distinguished_names
17 dn_id INTEGER PRIMARY KEY AUTOINCREMENT,
21 CREATE TABLE ldb_object_classes
23 class_name TEXT PRIMARY KEY,
28 CREATE TABLE ldb_dn_object_classes
30 dn_id INTEGER REFERENCES ldb_distinguished_names,
31 class_name TEXT REFERENCES ldb_object_classes
34 CREATE TABLE ldb_attributes
36 attr_name TEXT PRIMARY KEY,
37 case_insensitive_p BOOLEAN DEFAULT FALSE,
38 wildcard_p BOOLEAN DEFAULT FALSE,
39 hidden_p BOOLEAN DEFAULT FALSE,
40 integer_p BOOLEAN DEFAULT FALSE
43 CREATE TABLE ldb_attr_value_pairs
45 dn_id INTEGER REFERENCES ldb_distinguished_names,
46 attr_name TEXT, -- optionally REFERENCES ldb_attributes
49 UNIQUE (dn_id, attr_name, attr_value)
52 -- ------------------------------------------------------
54 CREATE TRIGGER ldb_distinguished_names_delete_tr
56 ON ldb_distinguished_names
59 DELETE FROM ldb_attr_value_pairs
60 WHERE dn_id = old.dn_id;
61 DELETE FROM ldb_dn_object_classes
62 WHERE dn_id = old.dn_id;
65 CREATE TRIGGER ldb_attr_value_pairs_insert_tr
67 ON ldb_attr_value_pairs
70 INSERT OR IGNORE INTO ldb_attributes
76 CREATE TRIGGER ldb_attr_value_pairs_delete_tr
78 ON ldb_attr_value_pairs
81 DELETE FROM ldb_attributes
82 WHERE (SELECT COUNT(*)
83 FROM ldb_attr_value_pairs
84 WHERE attr_name = old.attr_name) = 0
85 AND attr_name = old.attr_name;
88 -- ------------------------------------------------------
90 CREATE INDEX ldb_distinguished_names_dn_idx
91 ON ldb_distinguished_names (dn);
93 CREATE INDEX ldb_object_classes_tree_key_idx
94 ON ldb_object_classes (tree_key);
97 CREATE INDEX ldb_dn_object_classes_dn_id_idx
98 ON ldb_dn_object_classes (dn_id);
100 CREATE INDEX ldb_dn_object_classes_class_name_idx
101 ON ldb_dn_object_classes (class_name);
104 CREATE INDEX ldb_attr_value_pairs_dn_id_name_case_idx
105 ON ldb_attr_value_pairs (dn_id, attr_name);
107 CREATE INDEX ldb_attr_value_pairs_dn_id_name_nocase_idx
108 ON ldb_attr_value_pairs (dn_id, attr_name COLLATE NOCASE);
110 -- ------------------------------------------------------
112 /* all defaults for dn, initially */
113 INSERT INTO ldb_attributes (attr_name)
116 /* We need an implicit "top" level object class */
117 INSERT INTO ldb_object_classes (class_name, tree_key)
118 SELECT 'top', /* next_tree_key(NULL) */ '0001';
120 -- ------------------------------------------------------
124 -- ------------------------------------------------------
127 * dn: o=University of Michigan,c=US
128 * objectclass: organization
129 * objectclass: domainRelatedObject
132 INSERT INTO ldb_distinguished_names (dn_id, dn)
133 VALUES (1, 'o=University of Michigan,c=US');
136 INSERT OR IGNORE INTO ldb_object_classes (class_name, tree_key)
137 SELECT 'organization', /* next_tree_key(NULL) */ '0002';
139 INSERT OR IGNORE INTO ldb_object_classes (class_name, tree_key)
140 SELECT 'domainRelatedObject', /* next_tree_key(NULL) */ '0003';
143 INSERT OR IGNORE INTO ldb_dn_object_classes (dn_id, class_name)
144 VALUES (1, 'organization');
146 INSERT OR IGNORE INTO ldb_dn_object_classes (dn_id, class_name)
147 VALUES (1, 'domainRelatedObject');
150 * l: Ann Arbor, Michigan
152 * o: University of Michigan
157 * description: The University of Michigan at Ann Arbor
159 * postaladdress: University of Michigan $ 535 W. William St. $ Ann Arbor, MI 481
161 * telephonenumber: +1 313 764-1817
162 * associateddomain: example.com
165 INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value)
166 VALUES (1, 'l', 'Ann Arbor, Michigan');
167 INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value)
168 VALUES (1, 'st', 'Michigan');
169 INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value)
170 VALUES (1, 'o', 'University of Michigan');
171 INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value)
172 VALUES (1, 'o', 'UMICH');
173 INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value)
174 VALUES (1, 'o', 'UM');
175 INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value)
176 VALUES (1, 'o', 'U-M');
177 INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value)
178 VALUES (1, 'o', 'U of M');
179 INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value)
180 VALUES (1, 'description', 'The University of Michigan at Ann Arbor');
181 INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value)
182 VALUES (1, 'seeAlso', '');
183 INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value)
184 VALUES (1, 'postaladdress', 'University of Michigan $ 535 W. William St. $ Ann Arbor, MI 48109 $ US');
185 INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value)
186 VALUES (1, 'telephonenumber', '+1 313 764-1817');
187 INSERT INTO ldb_attr_value_pairs (dn_id, attr_name, attr_value)
188 VALUES (1, 'associateddomain', 'example.com');
190 -- ----------------------------------------------------------------------
194 * uid: CASE_INSENSITIVE WILDCARD
195 * cn: CASE_INSENSITIVE
196 * ou: CASE_INSENSITIVE
197 * dn: CASE_INSENSITIVE
200 INSERT OR REPLACE INTO ldb_attributes
201 (attr_name, case_insensitive_p, wildcard_p, hidden_p, integer_p)
202 VALUES ('uid', 1, 1, 0, 0);
203 INSERT OR REPLACE INTO ldb_attributes
204 (attr_name, case_insensitive_p, wildcard_p, hidden_p, integer_p)
205 VALUES ('cn', 1, 0, 0, 0);
206 INSERT OR REPLACE INTO ldb_attributes
207 (attr_name, case_insensitive_p, wildcard_p, hidden_p, integer_p)
208 VALUES ('ou', 1, 0, 0, 0);
209 INSERT OR REPLACE INTO ldb_attributes
210 (attr_name, case_insensitive_p, wildcard_p, hidden_p, integer_p)
211 VALUES ('dn', 1, 0, 0, 0);
213 -- ----------------------------------------------------------------------
220 * person: organizationalPerson
222 * organizationalPerson: user
223 * organizationalPerson: OpenLDAPperson
227 INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key)
228 SELECT 'domain', /* next_tree_key('top') */ '00010001';
229 INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key)
230 SELECT 'person', /* next_tree_key('top') */ '00010002';
231 INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key)
232 SELECT 'domainDNS', /* next_tree_key('domain') */ '000100010001';
233 INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key)
234 SELECT 'organizationalPerson', /* next_tree_key('person') */ '000100020001';
235 INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key)
236 SELECT 'fooPerson', /* next_tree_key('person') */ '000100020002';
237 INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key)
238 SELECT 'user', /* next_tree_key('organizationalPerson') */ '0001000200010001';
239 INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key)
240 SELECT 'OpenLDAPperson', /* next_tree_key('organizationPerson') */ '0001000200010002';
241 INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key)
242 SELECT 'computer', /* next_tree_key('user') */ '0001000200010001';