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
|
-- ------------------------------------------------------
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 are dependent 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
);
/*
* 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
);
/*
* We keep a full listing of attribute/value pairs here
*/
CREATE TABLE ldb_attribute_values
(
eid INTEGER REFERENCES ldb_entry,
attr_name TEXT, -- see ldb_attr_ATTRIBUTE_NAME
attr_value TEXT
);
/*
* 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;
-- ------------------------------------------------------
/*** TESTS ***/
/*
* dn: o=University of Michigan,c=US
* objectclass: organization
* objectclass: domainRelatedObject
*/
/*
* 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
*/
/*
* dn: @ATTRIBUTES
* uid: CASE_INSENSITIVE WILDCARD
* cn: CASE_INSENSITIVE
* ou: CASE_INSENSITIVE
* dn: CASE_INSENSITIVE
*/
/*
* dn: @SUBCLASSES
* top: domain
* top: person
* domain: domainDNS
* person: organizationalPerson
* person: fooPerson
* organizationalPerson: user
* organizationalPerson: OpenLDAPperson
* user: computer
*/
|
