1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
|
-- ------------------------------------------------------
PRAGMA auto_vacuum=1;
-- ------------------------------------------------------
BEGIN EXCLUSIVE;
-- ------------------------------------------------------
CREATE TABLE ldb_info AS
SELECT 'LDB' AS database_type,
'1.0' AS version;
CREATE TABLE ldb_distinguished_names
(
dn_id INTEGER PRIMARY KEY AUTOINCREMENT,
dn TEXT UNIQUE
);
CREATE TABLE ldb_object_classes
(
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
);
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
);
CREATE TABLE ldb_attr_value_pairs
(
dn_id INTEGER REFERENCES ldb_distinguished_names,
attr_name TEXT, -- optionally REFERENCES ldb_attributes
attr_value TEXT,
UNIQUE (dn_id, attr_name, attr_value)
);
-- ------------------------------------------------------
CREATE TRIGGER ldb_distinguished_names_delete_tr
AFTER DELETE
ON ldb_distinguished_names
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;
END;
CREATE TRIGGER ldb_attr_value_pairs_insert_tr
BEFORE INSERT
ON ldb_attr_value_pairs
FOR EACH ROW
BEGIN
INSERT OR IGNORE INTO ldb_attributes
(attr_name)
VALUES
(new.attr_name);
END;
CREATE TRIGGER ldb_attr_value_pairs_delete_tr
AFTER DELETE
ON ldb_attr_value_pairs
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;
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');
/* We need an implicit "top" level object class */
INSERT INTO ldb_object_classes (class_name, tree_key)
SELECT 'top', /* next_tree_key(NULL) */ '0001';
-- ------------------------------------------------------
COMMIT;
-- ------------------------------------------------------
/*
* dn: o=University of Michigan,c=US
* objectclass: organization
* objectclass: domainRelatedObject
*/
-- newDN
INSERT INTO ldb_distinguished_names (dn_id, dn)
VALUES (1, 'o=University of Michigan,c=US');
-- 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';
-- 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');
/*
* 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');
-- ----------------------------------------------------------------------
/*
* dn: @ATTRIBUTES
* uid: CASE_INSENSITIVE WILDCARD
* cn: CASE_INSENSITIVE
* ou: CASE_INSENSITIVE
* 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);
-- ----------------------------------------------------------------------
/*
* dn: @SUBCLASSES
* top: domain
* top: person
* domain: domainDNS
* person: organizationalPerson
* person: fooPerson
* organizationalPerson: user
* organizationalPerson: OpenLDAPperson
* user: computer
*/
-- insertSubclass
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';
|
