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