xref: /illumos-gate/usr/src/cmd/idmap/idmapd/schema.h (revision 2a8bcb4efb45d99ac41c94a75c396b362c414f7f)
1 /*
2  * CDDL HEADER START
3  *
4  * The contents of this file are subject to the terms of the
5  * Common Development and Distribution License (the "License").
6  * You may not use this file except in compliance with the License.
7  *
8  * You can obtain a copy of the license at usr/src/OPENSOLARIS.LICENSE
9  * or http://www.opensolaris.org/os/licensing.
10  * See the License for the specific language governing permissions
11  * and limitations under the License.
12  *
13  * When distributing Covered Code, include this CDDL HEADER in each
14  * file and include the License file at usr/src/OPENSOLARIS.LICENSE.
15  * If applicable, add the following below this CDDL HEADER, with the
16  * fields enclosed by brackets "[]" replaced with your own identifying
17  * information: Portions Copyright [yyyy] [name of copyright owner]
18  *
19  * CDDL HEADER END
20  */
21 /*
22  * Copyright 2008 Sun Microsystems, Inc.  All rights reserved.
23  * Use is subject to license terms.
24  */
25 
26 #ifndef _SCHEMA_H
27 #define	_SCHEMA_H
28 
29 #ifdef __cplusplus
30 extern "C" {
31 #endif
32 
33 /*
34  * Various macros (constant strings) containing:
35  *
36  *  - CREATE TABLE/INDEX/TRIGGER/VIEW SQL
37  *  - old versions of schema items that have changed
38  *  - SQL to detect the version currently installed in a db
39  *  - SQL to upgrade the schema from any older version to the current
40  *     - the SQL to install the current version of the schema on a
41  *       freshly created db is the SQL used to "upgrade" from "version 0"
42  *
43  * There is one set of such macros for the cache DB (CACHE_*) and
44  * another set for the persistent DB (DB_*).  The macros ending in _SQL
45  * are used in arguments to init_db_instance().
46  *
47  * Schema version detection SQL has the following form:
48  *
49  * SELECT CASE (SELECT count(*) FROM sqlite_master) WHEN 0 THEN 0 ELSE
50  * (CASE (SELECT count(*) FROM sqlite_master WHERE sql = <original schema> ...)
51  * WHEN <correct count> THEN 1 ELSE (CASE (<v2 schema>) WHEN ... THEN 2
52  * ELSE -1 END) END AS version;
53  *
54  * That is, check that there is no schema else that the current schema
55  * sql matches the original schema, else the next version, ... and
56  * return an integer identifying the schema.  Version numbers returned:
57  *
58  * -1 -> unknown schema  (shouldn't happen)
59  *  0 -> no schema       (brand new DB, install latest schema)
60  *  1 -> original schema (if != latest, then upgrade)
61  *  . -> ...             (if != latest, then upgrade)
62  *  n -> latest schema   (nothing to do)
63  *
64  * Upgrade SQL for the cache DB is simple: drop everything, create
65  * latest schema.  This means losing ephemeral mappings, so idmapd must
66  * tell the kernel about that in its registration call.
67  *
68  * Upgrade SQL for the persistent DB is simple: drop the indexes, create
69  * temporary tables with the latest schema, insert into those from the
70  * old tables (transforming the data in the process), then drop the old
71  * tables, create the latest schema, restore the data from the temp.
72  * tables and drop the temp tables.
73  *
74  * Complex, but it avoids all sorts of packaging install/upgrade
75  * complexity, requiring reboots on patch.
76  *
77  * Conventions:
78  * - each TABLE/INDEX gets its own macro, and the SQL therein must not
79  *   end in a semi-colon (';)
80  * - macros are named * TABLE_* for tables, INDEX_* for indexes,
81  *   *_VERSION_SQL for SQL for determining version number,
82  *   *_UPGRADE_FROM_v<version>_SQL for SQL for upgrading from some
83  *   schema, *_LATEST_SQL for SQL for installing the latest schema.
84  * - some macros nest expansions of other macros
85  *
86  * The latest schema has two columns for Windows user/group name in
87  * tables where there used to be one.  One of those columns contains the
88  * name as it came from the user or from AD, the other is set via a
89  * TRIGGER to be the lower-case version of the first, and we always
90  * search (and index) by the latter.  This is for case-insensitivity.
91  */
92 #define	TABLE_IDMAP_CACHE_v1 \
93 	"CREATE TABLE idmap_cache (" \
94 	"	sidprefix TEXT," \
95 	"	rid INTEGER," \
96 	"	windomain TEXT," \
97 	"	winname TEXT," \
98 	"	pid INTEGER," \
99 	"	unixname TEXT," \
100 	"	is_user INTEGER," \
101 	"	w2u INTEGER," \
102 	"	u2w INTEGER," \
103 	"	expiration INTEGER" \
104 	")"
105 
106 #define	TABLE_IDMAP_CACHE_v2 \
107 	"CREATE TABLE idmap_cache " \
108 	"(" \
109 	"	sidprefix TEXT," \
110 	"	rid INTEGER," \
111 	"	windomain TEXT," \
112 	"	canon_winname TEXT," \
113 	"	winname TEXT," \
114 	"	pid INTEGER," \
115 	"	unixname TEXT," \
116 	"	is_user INTEGER," \
117 	"	is_wuser INTEGER," \
118 	"	w2u INTEGER," \
119 	"	u2w INTEGER," \
120 	"	expiration INTEGER" \
121 	")"
122 
123 #define	TABLE_IDMAP_CACHE \
124 	"CREATE TABLE idmap_cache " \
125 	"(" \
126 	"	sidprefix TEXT," \
127 	"	rid INTEGER," \
128 	"	windomain TEXT," \
129 	"	canon_winname TEXT," \
130 	"	winname TEXT," \
131 	"	pid INTEGER," \
132 	"	unixname TEXT," \
133 	"	is_user INTEGER," \
134 	"	is_wuser INTEGER," \
135 	"	w2u INTEGER," \
136 	"	u2w INTEGER," \
137 	"	map_type INTEGER," \
138 	"	map_dn TEXT, "\
139 	"	map_attr TEXT, "\
140 	"	map_value TEXT, "\
141 	"	map_windomain TEXT, "\
142 	"	map_winname TEXT, "\
143 	"	map_unixname TEXT, "\
144 	"	map_is_nt4 INTEGER, "\
145 	"	expiration INTEGER" \
146 	")"
147 
148 #define	INDEX_IDMAP_CACHE_SID_W2U_v1 \
149 	"CREATE UNIQUE INDEX idmap_cache_sid_w2u ON idmap_cache" \
150 	"		(sidprefix, rid, w2u)"
151 
152 #define	INDEX_IDMAP_CACHE_SID_W2U \
153 	"CREATE UNIQUE INDEX idmap_cache_sid_w2u ON idmap_cache" \
154 	"		(sidprefix, rid, is_user, w2u)"
155 
156 #define	INDEX_IDMAP_CACHE_PID_U2W \
157 	"CREATE UNIQUE INDEX idmap_cache_pid_u2w ON idmap_cache" \
158 	"		(pid, is_user, u2w)"
159 
160 #define	TRIGGER_IDMAP_CACHE_TOLOWER_INSERT \
161 	"CREATE TRIGGER idmap_cache_tolower_name_insert " \
162 	"AFTER INSERT ON idmap_cache " \
163 	"BEGIN " \
164 	"	UPDATE idmap_cache SET winname = lower_utf8(canon_winname)" \
165 	"		WHERE rowid = new.rowid;" \
166 	"END"
167 
168 #define	TRIGGER_IDMAP_CACHE_TOLOWER_UPDATE \
169 	"CREATE TRIGGER idmap_cache_tolower_name_update " \
170 	"AFTER UPDATE ON idmap_cache " \
171 	"BEGIN " \
172 	"	UPDATE idmap_cache SET winname = lower_utf8(canon_winname)" \
173 	"		WHERE rowid = new.rowid;" \
174 	"END"
175 
176 #define	TABLE_NAME_CACHE \
177 	"CREATE TABLE name_cache (" \
178 	"	sidprefix TEXT," \
179 	"	rid INTEGER," \
180 	"	name TEXT," \
181 	"	canon_name TEXT," \
182 	"	domain TEXT," \
183 	"	type INTEGER," \
184 	"	expiration INTEGER" \
185 	")"
186 
187 #define	TABLE_NAME_CACHE_v1 \
188 	"CREATE TABLE name_cache (" \
189 	"	sidprefix TEXT," \
190 	"	rid INTEGER," \
191 	"	name TEXT," \
192 	"	domain TEXT," \
193 	"	type INTEGER," \
194 	"	expiration INTEGER" \
195 	")"
196 
197 #define	TRIGGER_NAME_CACHE_TOLOWER_INSERT \
198 	"CREATE TRIGGER name_cache_tolower_name_insert " \
199 	"AFTER INSERT ON name_cache " \
200 	"BEGIN " \
201 	"	UPDATE name_cache SET name = lower_utf8(canon_name)" \
202 	"		WHERE rowid = new.rowid;" \
203 	"END"
204 
205 #define	TRIGGER_NAME_CACHE_TOLOWER_UPDATE \
206 	"CREATE TRIGGER name_cache_tolower_name_update " \
207 	"AFTER UPDATE ON name_cache " \
208 	"BEGIN " \
209 	"	UPDATE name_cache SET name = lower_utf8(canon_name)" \
210 	"		WHERE rowid = new.rowid;" \
211 	"END"
212 
213 #define	INDEX_NAME_CACHE_SID \
214 	"CREATE UNIQUE INDEX name_cache_sid ON name_cache" \
215 	"		(sidprefix, rid)"
216 
217 #define	INDEX_NAME_CACHE_NAME \
218 	"CREATE UNIQUE INDEX name_cache_name ON name_cache" \
219 	"		(name, domain)"
220 
221 #define	CACHE_INSTALL_SQL \
222 	TABLE_IDMAP_CACHE ";" \
223 	INDEX_IDMAP_CACHE_SID_W2U ";" \
224 	INDEX_IDMAP_CACHE_PID_U2W ";" \
225 	TRIGGER_IDMAP_CACHE_TOLOWER_INSERT ";" \
226 	TRIGGER_IDMAP_CACHE_TOLOWER_UPDATE ";" \
227 	TABLE_NAME_CACHE ";" \
228 	INDEX_NAME_CACHE_SID ";" \
229 	INDEX_NAME_CACHE_NAME ";" \
230 	TRIGGER_NAME_CACHE_TOLOWER_INSERT ";" \
231 	TRIGGER_NAME_CACHE_TOLOWER_UPDATE ";"
232 
233 #define	CACHE_VERSION_SQL \
234 	"SELECT CASE (SELECT count(*) FROM sqlite_master) WHEN 0 THEN 0 ELSE " \
235 	"(CASE (SELECT count(*) FROM sqlite_master WHERE " \
236 	"sql = '" TABLE_IDMAP_CACHE_v1 "' OR " \
237 	"sql = '" INDEX_IDMAP_CACHE_SID_W2U_v1 "' OR " \
238 	"sql = '" INDEX_IDMAP_CACHE_PID_U2W "' OR " \
239 	"sql = '" TABLE_NAME_CACHE_v1 "' OR " \
240 	"sql = '" INDEX_NAME_CACHE_SID "') " \
241 	"WHEN 5 THEN 1 ELSE " \
242 	"(CASE (SELECT count(*) FROM sqlite_master WHERE " \
243 	"sql = '" TABLE_IDMAP_CACHE_v2"' OR " \
244 	"sql = '" INDEX_IDMAP_CACHE_SID_W2U "' OR " \
245 	"sql = '" INDEX_IDMAP_CACHE_PID_U2W "' OR " \
246 	"sql = '" TRIGGER_IDMAP_CACHE_TOLOWER_INSERT "' OR " \
247 	"sql = '" TRIGGER_IDMAP_CACHE_TOLOWER_UPDATE "' OR " \
248 	"sql = '" TABLE_NAME_CACHE "' OR " \
249 	"sql = '" INDEX_NAME_CACHE_SID "' OR " \
250 	"sql = '" INDEX_NAME_CACHE_NAME "' OR " \
251 	"sql = '" TRIGGER_NAME_CACHE_TOLOWER_INSERT "' OR " \
252 	"sql = '" TRIGGER_NAME_CACHE_TOLOWER_UPDATE "') " \
253 	"WHEN 10 THEN 2 ELSE " \
254 	"(CASE (SELECT count(*) FROM sqlite_master WHERE " \
255 	"sql = '" TABLE_IDMAP_CACHE"' OR " \
256 	"sql = '" INDEX_IDMAP_CACHE_SID_W2U "' OR " \
257 	"sql = '" INDEX_IDMAP_CACHE_PID_U2W "' OR " \
258 	"sql = '" TRIGGER_IDMAP_CACHE_TOLOWER_INSERT "' OR " \
259 	"sql = '" TRIGGER_IDMAP_CACHE_TOLOWER_UPDATE "' OR " \
260 	"sql = '" TABLE_NAME_CACHE "' OR " \
261 	"sql = '" INDEX_NAME_CACHE_SID "' OR " \
262 	"sql = '" INDEX_NAME_CACHE_NAME "' OR " \
263 	"sql = '" TRIGGER_NAME_CACHE_TOLOWER_INSERT "' OR " \
264 	"sql = '" TRIGGER_NAME_CACHE_TOLOWER_UPDATE "') " \
265 	"WHEN 10 THEN 3 ELSE -1 END) END) END) END AS version;"
266 
267 #define	CACHE_UPGRADE_FROM_v1_SQL \
268 	"DROP TABLE idmap_cache;" \
269 	"DROP TABLE name_cache;" \
270 	CACHE_INSTALL_SQL
271 
272 #define	CACHE_UPGRADE_FROM_v2_SQL \
273 	"DROP TABLE idmap_cache;" \
274 	"DROP TABLE name_cache;" \
275 	CACHE_INSTALL_SQL
276 
277 #define	CACHE_VERSION	3
278 
279 
280 #define	TABLE_NAMERULES_v1 \
281 	"CREATE TABLE namerules (" \
282 	"	is_user INTEGER NOT NULL," \
283 	"	windomain TEXT," \
284 	"	winname TEXT NOT NULL," \
285 	"	is_nt4 INTEGER NOT NULL," \
286 	"	unixname NOT NULL," \
287 	"	w2u_order INTEGER," \
288 	"	u2w_order INTEGER" \
289 	")"
290 
291 #define	TABLE_NAMERULES_BODY \
292 	"(" \
293 	"	is_user INTEGER NOT NULL," \
294 	"	is_wuser INTEGER NOT NULL," \
295 	"	windomain TEXT," \
296 	"	winname_display TEXT NOT NULL," \
297 	"	winname TEXT," \
298 	"	is_nt4 INTEGER NOT NULL," \
299 	"	unixname NOT NULL," \
300 	"	w2u_order INTEGER," \
301 	"	u2w_order INTEGER" \
302 	")"
303 
304 #define	TABLE_NAMERULES \
305 	"CREATE TABLE namerules " \
306 	TABLE_NAMERULES_BODY
307 
308 #define	INDEX_NAMERULES_W2U_v1 \
309 	"CREATE UNIQUE INDEX namerules_w2u ON namerules" \
310 	"		(winname, windomain, is_user, w2u_order)"
311 
312 #define	INDEX_NAMERULES_W2U \
313 	"CREATE UNIQUE INDEX namerules_w2u ON namerules" \
314 	"		(winname, windomain, is_user, is_wuser, w2u_order)"
315 
316 #define	INDEX_NAMERULES_U2W \
317 	"CREATE UNIQUE INDEX namerules_u2w ON namerules" \
318 	"		(unixname, is_user, u2w_order)"
319 
320 #define	TRIGGER_NAMERULES_TOLOWER_BODY \
321 	"BEGIN " \
322 	"	UPDATE namerules SET winname = lower_utf8(winname_display)" \
323 	"		WHERE rowid = new.rowid;" \
324 	"END"
325 
326 #define	TRIGGER_NAMERULES_TOLOWER_INSERT \
327 	"CREATE TRIGGER namerules_tolower_name_insert " \
328 	"AFTER INSERT ON namerules " \
329 	TRIGGER_NAMERULES_TOLOWER_BODY
330 
331 #define	TRIGGER_NAMERULES_TOLOWER_UPDATE \
332 	"CREATE TRIGGER namerules_tolower_name_update " \
333 	"AFTER UPDATE ON namerules " \
334 	TRIGGER_NAMERULES_TOLOWER_BODY
335 
336 #define	TRIGGER_NAMERULES_UNIQUE_BODY \
337 	"	SELECT CASE (SELECT count(*) FROM namerules AS n" \
338 	"		WHERE n.unixname = NEW.unixname AND" \
339 	"		n.is_user = NEW.is_user AND" \
340 	"		(n.winname != lower(NEW.winname_display) OR" \
341 	"		n.windomain != NEW.windomain ) AND" \
342 	"		n.u2w_order = NEW.u2w_order AND" \
343 	"		n.is_wuser != NEW.is_wuser) > 0" \
344 	"	WHEN 1 THEN" \
345 	"		raise(ROLLBACK, 'Conflicting w2u namerules')"\
346 	"	END; " \
347 	"END"
348 
349 #define	TRIGGER_NAMERULES_UNIQUE_INSERT \
350 	"CREATE TRIGGER namerules_unique_insert " \
351 	"BEFORE INSERT ON namerules " \
352 	"BEGIN " \
353 	TRIGGER_NAMERULES_UNIQUE_BODY
354 
355 #define	TRIGGER_NAMERULES_UNIQUE_UPDATE \
356 	"CREATE TRIGGER namerules_unique_update " \
357 	"BEFORE INSERT ON namerules " \
358 	"BEGIN " \
359 	TRIGGER_NAMERULES_UNIQUE_BODY
360 
361 #define	DB_INSTALL_SQL \
362 	TABLE_NAMERULES ";" \
363 	INDEX_NAMERULES_W2U ";" \
364 	INDEX_NAMERULES_U2W ";" \
365 	TRIGGER_NAMERULES_TOLOWER_INSERT ";" \
366 	TRIGGER_NAMERULES_TOLOWER_UPDATE ";" \
367 	TRIGGER_NAMERULES_UNIQUE_INSERT ";" \
368 	TRIGGER_NAMERULES_UNIQUE_UPDATE ";"
369 
370 #define	DB_VERSION_SQL \
371 	"SELECT CASE (SELECT count(*) FROM sqlite_master) WHEN 0 THEN 0 ELSE " \
372 	"(CASE (SELECT count(*) FROM sqlite_master WHERE " \
373 	"sql = '" TABLE_NAMERULES_v1 "' OR " \
374 	"sql = '" INDEX_NAMERULES_W2U_v1 "' OR " \
375 	"sql = '" INDEX_NAMERULES_U2W "') " \
376 	"WHEN 3 THEN 1 ELSE "\
377 	"(CASE (SELECT count(*) FROM sqlite_master WHERE " \
378 	"sql = '" TABLE_NAMERULES "' OR " \
379 	"sql = '" INDEX_NAMERULES_W2U "' OR " \
380 	"sql = '" INDEX_NAMERULES_U2W "' OR " \
381 	"sql = '" TRIGGER_NAMERULES_TOLOWER_INSERT "' OR " \
382 	"sql = '" TRIGGER_NAMERULES_TOLOWER_UPDATE "' OR " \
383 	"sql = \"" TRIGGER_NAMERULES_UNIQUE_INSERT "\" OR " \
384 	"sql = \"" TRIGGER_NAMERULES_UNIQUE_UPDATE "\") " \
385 	"WHEN 7 THEN 2 ELSE -1 END) END) END AS version;"
386 
387 /* SQL for upgrading an existing name rules DB.  Includes DB_INSTALL_SQL */
388 #define	DB_UPGRADE_FROM_v1_SQL \
389 	"CREATE TABLE namerules_new " TABLE_NAMERULES_BODY ";" \
390 	"INSERT INTO namerules_new SELECT is_user, is_user, windomain, " \
391 	"winname, winname, is_nt4, unixname, w2u_order, u2w_order " \
392 	"FROM namerules;" \
393 	"DROP TABLE namerules;" \
394 	DB_INSTALL_SQL \
395 	"INSERT INTO namerules SELECT * FROM namerules_new;" \
396 	"DROP TABLE namerules_new;"
397 
398 #define	DB_VERSION	2
399 
400 #ifdef __cplusplus
401 }
402 #endif
403 
404 
405 #endif	/* _SCHEMA_H */
406