1
-- ------------------------------------------------------
5
-- ------------------------------------------------------
9
-- ------------------------------------------------------
11
CREATE TABLE ldb_info AS
12
SELECT 'LDB' AS database_type,
16
* Get the next USN value with:
18
* UPDATE usn SET value = value + 1;
19
* SELECT value FROM usn;
27
CREATE TABLE ldb_object
29
/* tree_key is auto-generated by the insert trigger */
30
tree_key TEXT PRIMARY KEY,
35
attr_name TEXT REFERENCES ldb_attributes,
39
* object_type can take on these values (to date):
40
* 1: object is a node of a DN
41
* 2: object is an attribute/value pair of its parent DN
46
* if object_type is 1, the node can have children.
47
* this tracks the maximum previously assigned child
48
* number so we can generate a new unique tree key for
49
* a new child object. note that this is always incremented,
50
* so if children are deleted, this will not represent
51
* the _number_ of children.
53
max_child_num INTEGER,
56
* Automatically maintained meta-data (a gift for metze)
58
object_guid TEXT UNIQUE,
59
timestamp INTEGER, -- originating_time
60
invoke_id TEXT, -- GUID: originating_invocation_id
61
usn INTEGER, -- hyper: originating_usn
63
/* do not allow duplicate name/value pairs */
64
UNIQUE (parent_tree_key, attr_name, attr_value, object_type)
67
CREATE TABLE ldb_attributes
69
attr_name TEXT PRIMARY KEY,
72
objectclass_p BOOLEAN DEFAULT 0,
74
case_insensitive_p BOOLEAN DEFAULT 0,
75
wildcard_p BOOLEAN DEFAULT 0,
76
hidden_p BOOLEAN DEFAULT 0,
77
integer_p BOOLEAN DEFAULT 0,
79
/* tree_key is auto-generated by the insert trigger */
80
tree_key TEXT, -- null if not a object/sub class
81
-- level 1 if an objectclass
82
-- level 1-n if a subclass
86
-- ------------------------------------------------------
88
CREATE INDEX ldb_object_dn_idx
91
CREATE INDEX ldb_attributes_tree_key_ids
92
ON ldb_attributes (tree_key);
94
-- ------------------------------------------------------
96
/* Gifts for metze. Automatically updated meta-data */
97
CREATE TRIGGER ldb_object_insert_tr
103
SET max_child_num = max_child_num + 1
104
WHERE tree_key = new.parent_tree_key;
105
UPDATE usn SET value = value + 1;
110
base160(SELECT max_child_num
113
new.parent_tree_key));
115
object_guid = random_guid(),
116
timestamp = strftime('%s', 'now'),
117
usn = (SELECT value FROM usn);
118
WHERE tree_key = new.tree_key;
121
CREATE TRIGGER ldb_object_update_tr
126
UPDATE usn SET value = value + 1;
128
SET timestamp = strftime('%s', 'now'),
129
usn = (SELECT value FROM usn);
130
WHERE tree_key = new.tree_key;
133
CREATE TRIGGER ldb_attributes_insert_tr
138
UPDATE ldb_attributes
139
SET max_child_num = max_child_num + 1
140
WHERE tree_key = new.parent_tree_key;
141
UPDATE ldb_attributes
145
base160(SELECT max_child_num
148
new.parent_tree_key));
150
WHERE tree_key = new.tree_key;
154
-- ------------------------------------------------------
157
INSERT INTO usn (value) VALUES (0);
159
/* Create root object */
160
INSERT INTO ldb_object
161
(tree_key, parent_tree_key,
163
object_type, max_child_num)
168
/* We need an implicit "top" level object class */
169
INSERT INTO ldb_attributes (attr_name,
173
-- ------------------------------------------------------
177
-- ------------------------------------------------------
180
* dn: o=University of Michigan,c=US
181
* objectclass: organization
182
* objectclass: domainRelatedObject
187
INSERT OR IGNORE INTO ldb_object
190
attr_name, attr_value, object_type, max_child_num)
195
INSERT INTO ldb_object
198
attr_name, attr_value, object_type, max_child_num)
200
'o=University of Michigan,c=US',
201
'o', 'University of Michigan', 1, 0);
204
INSERT OR IGNORE INTO ldb_attributes
205
(attr_name, parent_tree_key, objectclass_p)
207
('objectclass', '', 1);
209
INSERT INTO ldb_object
212
attr_name, attr_value, object_type, max_child_num)
215
'objectclass', 'organization', 2, 0);
217
INSERT OR IGNORE INTO ldb_attributes
218
(attr_name, parent_tree_key, objectclass_p)
220
('objectclass', '', 1);
222
INSERT INTO ldb_object
225
attr_name, attr_value, object_type, max_child_num)
228
'objectclass', 'domainRelatedObject', 2, 0);
234
* dn: o=University of Michigan,c=US
235
* l: Ann Arbor, Michigan
237
* o: University of Michigan
240
* telephonenumber: +1 313 764-1817
245
INSERT INTO ldb_object
246
(parent_tree_key, dn,
247
attr_name, attr_value, object_type, max_child_num)
248
VALUES ('00010001', NULL,
249
'l', 'Ann Arbor, Michigan', 2, 0);
251
INSERT INTO ldb_object
252
(parent_tree_key, dn,
253
attr_name, attr_value, object_type, max_child_num)
254
VALUES ('00010001', NULL,
255
'st', 'Michigan', 2, 0);
257
INSERT INTO ldb_object
258
(parent_tree_key, dn,
259
attr_name, attr_value, object_type, max_child_num)
260
VALUES ('00010001', NULL,
261
'o', 'University of Michigan', 2, 0);
263
INSERT INTO ldb_object
264
(parent_tree_key, dn,
265
attr_name, attr_value, object_type, max_child_num)
266
VALUES ('00010001', NULL,
269
INSERT INTO ldb_object
270
(parent_tree_key, dn,
271
attr_name, attr_value, object_type, max_child_num)
272
VALUES ('00010001', NULL,
273
'seeAlso', '', 2, 0);
275
INSERT INTO ldb_object
276
(parent_tree_key, dn,
277
attr_name, attr_value, object_type, max_child_num)
278
VALUES ('00010001', NULL,
279
'telephonenumber', '+1 313 764-1817', 2, 0);
283
-- ----------------------------------------------------------------------
287
* uid: CASE_INSENSITIVE WILDCARD
288
* cn: CASE_INSENSITIVE
289
* ou: CASE_INSENSITIVE
290
* dn: CASE_INSENSITIVE
296
INSERT OR IGNORE INTO ldb_attributes
297
(attr_name, parent_tree_key, objectclass_p)
301
UPDATE ldb_attributes
302
SET case_insensitive_p = 1,
306
WHERE attr_name = 'uid'
308
UPDATE ldb_attributes
309
SET case_insensitive_p = 1,
313
WHERE attr_name = 'cn'
315
UPDATE ldb_attributes
316
SET case_insensitive_p = 1,
320
WHERE attr_name = 'ou'
322
UPDATE ldb_attributes
323
SET case_insensitive_p = 1,
327
WHERE attr_name = 'dn'
329
-- ----------------------------------------------------------------------
336
* person: organizationalPerson
338
* organizationalPerson: user
339
* organizationalPerson: OpenLDAPperson
344
/* NOT YET UPDATED!!! *
347
INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key)
348
SELECT 'domain', /* next_tree_key('top') */ '00010001';
349
INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key)
350
SELECT 'person', /* next_tree_key('top') */ '00010002';
351
INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key)
352
SELECT 'domainDNS', /* next_tree_key('domain') */ '000100010001';
353
INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key)
354
SELECT 'organizationalPerson', /* next_tree_key('person') */ '000100020001';
355
INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key)
356
SELECT 'fooPerson', /* next_tree_key('person') */ '000100020002';
357
INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key)
358
SELECT 'user', /* next_tree_key('organizationalPerson') */ '0001000200010001';
359
INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key)
360
SELECT 'OpenLDAPperson', /* next_tree_key('organizationPerson') */ '0001000200010002';
361
INSERT OR REPLACE INTO ldb_object_classes (class_name, tree_key)
362
SELECT 'computer', /* next_tree_key('user') */ '0001000200010001';