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