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 2007 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 \ 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 INDEX_IDMAP_CACHE_SID_W2U_v1 \ 126 "CREATE UNIQUE INDEX idmap_cache_sid_w2u ON idmap_cache" \ 127 " (sidprefix, rid, w2u)" 128 129 #define INDEX_IDMAP_CACHE_SID_W2U \ 130 "CREATE UNIQUE INDEX idmap_cache_sid_w2u ON idmap_cache" \ 131 " (sidprefix, rid, is_user, w2u)" 132 133 #define INDEX_IDMAP_CACHE_PID_U2W \ 134 "CREATE UNIQUE INDEX idmap_cache_pid_u2w ON idmap_cache" \ 135 " (pid, is_user, u2w)" 136 137 #define TRIGGER_IDMAP_CACHE_TOLOWER_INSERT \ 138 "CREATE TRIGGER idmap_cache_tolower_name_insert " \ 139 "AFTER INSERT ON idmap_cache " \ 140 "BEGIN " \ 141 " UPDATE idmap_cache SET winname = lower_utf8(canon_winname)" \ 142 " WHERE rowid = new.rowid;" \ 143 "END" 144 145 #define TRIGGER_IDMAP_CACHE_TOLOWER_UPDATE \ 146 "CREATE TRIGGER idmap_cache_tolower_name_update " \ 147 "AFTER UPDATE ON idmap_cache " \ 148 "BEGIN " \ 149 " UPDATE idmap_cache SET winname = lower_utf8(canon_winname)" \ 150 " WHERE rowid = new.rowid;" \ 151 "END" 152 153 #define TABLE_NAME_CACHE \ 154 "CREATE TABLE name_cache (" \ 155 " sidprefix TEXT," \ 156 " rid INTEGER," \ 157 " name TEXT," \ 158 " canon_name TEXT," \ 159 " domain TEXT," \ 160 " type INTEGER," \ 161 " expiration INTEGER" \ 162 ")" 163 164 #define TABLE_NAME_CACHE_v1 \ 165 "CREATE TABLE name_cache (" \ 166 " sidprefix TEXT," \ 167 " rid INTEGER," \ 168 " name TEXT," \ 169 " domain TEXT," \ 170 " type INTEGER," \ 171 " expiration INTEGER" \ 172 ")" 173 174 #define TRIGGER_NAME_CACHE_TOLOWER_INSERT \ 175 "CREATE TRIGGER name_cache_tolower_name_insert " \ 176 "AFTER INSERT ON name_cache " \ 177 "BEGIN " \ 178 " UPDATE name_cache SET name = lower_utf8(canon_name)" \ 179 " WHERE rowid = new.rowid;" \ 180 "END" 181 182 #define TRIGGER_NAME_CACHE_TOLOWER_UPDATE \ 183 "CREATE TRIGGER name_cache_tolower_name_update " \ 184 "AFTER UPDATE ON name_cache " \ 185 "BEGIN " \ 186 " UPDATE name_cache SET name = lower_utf8(canon_name)" \ 187 " WHERE rowid = new.rowid;" \ 188 "END" 189 190 #define INDEX_NAME_CACHE_SID \ 191 "CREATE UNIQUE INDEX name_cache_sid ON name_cache" \ 192 " (sidprefix, rid)" 193 194 #define INDEX_NAME_CACHE_NAME \ 195 "CREATE UNIQUE INDEX name_cache_name ON name_cache" \ 196 " (name, domain)" 197 198 #define CACHE_INSTALL_SQL \ 199 TABLE_IDMAP_CACHE ";" \ 200 INDEX_IDMAP_CACHE_SID_W2U ";" \ 201 INDEX_IDMAP_CACHE_PID_U2W ";" \ 202 TRIGGER_IDMAP_CACHE_TOLOWER_INSERT ";" \ 203 TRIGGER_IDMAP_CACHE_TOLOWER_UPDATE ";" \ 204 TABLE_NAME_CACHE ";" \ 205 INDEX_NAME_CACHE_SID ";" \ 206 INDEX_NAME_CACHE_NAME ";" \ 207 TRIGGER_NAME_CACHE_TOLOWER_INSERT ";" \ 208 TRIGGER_NAME_CACHE_TOLOWER_UPDATE ";" 209 210 #define CACHE_VERSION_SQL \ 211 "SELECT CASE (SELECT count(*) FROM sqlite_master) WHEN 0 THEN 0 ELSE " \ 212 "(CASE (SELECT count(*) FROM sqlite_master WHERE " \ 213 "sql = '" TABLE_IDMAP_CACHE_v1 "' OR " \ 214 "sql = '" INDEX_IDMAP_CACHE_SID_W2U_v1 "' OR " \ 215 "sql = '" INDEX_IDMAP_CACHE_PID_U2W "' OR " \ 216 "sql = '" TABLE_NAME_CACHE_v1 "' OR " \ 217 "sql = '" INDEX_NAME_CACHE_SID "') " \ 218 "WHEN 5 THEN 1 ELSE " \ 219 "(CASE (SELECT count(*) FROM sqlite_master WHERE " \ 220 "sql = '" TABLE_IDMAP_CACHE"' OR " \ 221 "sql = '" INDEX_IDMAP_CACHE_SID_W2U "' OR " \ 222 "sql = '" INDEX_IDMAP_CACHE_PID_U2W "' OR " \ 223 "sql = '" TRIGGER_IDMAP_CACHE_TOLOWER_INSERT "' OR " \ 224 "sql = '" TRIGGER_IDMAP_CACHE_TOLOWER_UPDATE "' OR " \ 225 "sql = '" TABLE_NAME_CACHE "' OR " \ 226 "sql = '" INDEX_NAME_CACHE_SID "' OR " \ 227 "sql = '" INDEX_NAME_CACHE_NAME "' OR " \ 228 "sql = '" TRIGGER_NAME_CACHE_TOLOWER_INSERT "' OR " \ 229 "sql = '" TRIGGER_NAME_CACHE_TOLOWER_UPDATE "') " \ 230 "WHEN 10 THEN 2 ELSE -1 END) END) END AS version;" 231 232 #define CACHE_UPGRADE_FROM_v1_SQL \ 233 "DROP TABLE idmap_cache;" \ 234 "DROP TABLE name_cache;" \ 235 CACHE_INSTALL_SQL 236 237 #define CACHE_VERSION 2 238 239 240 #define TABLE_NAMERULES_v1 \ 241 "CREATE TABLE namerules (" \ 242 " is_user INTEGER NOT NULL," \ 243 " windomain TEXT," \ 244 " winname TEXT NOT NULL," \ 245 " is_nt4 INTEGER NOT NULL," \ 246 " unixname NOT NULL," \ 247 " w2u_order INTEGER," \ 248 " u2w_order INTEGER" \ 249 ")" 250 251 #define TABLE_NAMERULES_BODY \ 252 "(" \ 253 " is_user INTEGER NOT NULL," \ 254 " is_wuser INTEGER NOT NULL," \ 255 " windomain TEXT," \ 256 " winname_display TEXT NOT NULL," \ 257 " winname TEXT," \ 258 " is_nt4 INTEGER NOT NULL," \ 259 " unixname NOT NULL," \ 260 " w2u_order INTEGER," \ 261 " u2w_order INTEGER" \ 262 ")" 263 264 #define TABLE_NAMERULES \ 265 "CREATE TABLE namerules " \ 266 TABLE_NAMERULES_BODY 267 268 #define INDEX_NAMERULES_W2U_v1 \ 269 "CREATE UNIQUE INDEX namerules_w2u ON namerules" \ 270 " (winname, windomain, is_user, w2u_order)" 271 272 #define INDEX_NAMERULES_W2U \ 273 "CREATE UNIQUE INDEX namerules_w2u ON namerules" \ 274 " (winname, windomain, is_user, is_wuser, w2u_order)" 275 276 #define INDEX_NAMERULES_U2W \ 277 "CREATE UNIQUE INDEX namerules_u2w ON namerules" \ 278 " (unixname, is_user, u2w_order)" 279 280 #define TRIGGER_NAMERULES_TOLOWER_BODY \ 281 "BEGIN " \ 282 " UPDATE namerules SET winname = lower_utf8(winname_display)" \ 283 " WHERE rowid = new.rowid;" \ 284 "END" 285 286 #define TRIGGER_NAMERULES_TOLOWER_INSERT \ 287 "CREATE TRIGGER namerules_tolower_name_insert " \ 288 "AFTER INSERT ON namerules " \ 289 TRIGGER_NAMERULES_TOLOWER_BODY 290 291 #define TRIGGER_NAMERULES_TOLOWER_UPDATE \ 292 "CREATE TRIGGER namerules_tolower_name_update " \ 293 "AFTER UPDATE ON namerules " \ 294 TRIGGER_NAMERULES_TOLOWER_BODY 295 296 #define TRIGGER_NAMERULES_UNIQUE_BODY \ 297 " SELECT CASE (SELECT count(*) FROM namerules AS n" \ 298 " WHERE n.unixname = NEW.unixname AND" \ 299 " n.is_user = NEW.is_user AND" \ 300 " (n.winname != lower(NEW.winname_display) OR" \ 301 " n.windomain != NEW.windomain ) AND" \ 302 " n.u2w_order = NEW.u2w_order AND" \ 303 " n.is_wuser != NEW.is_wuser) > 0" \ 304 " WHEN 1 THEN" \ 305 " raise(ROLLBACK, 'Conflicting w2u namerules')"\ 306 " END; " \ 307 "END" 308 309 #define TRIGGER_NAMERULES_UNIQUE_INSERT \ 310 "CREATE TRIGGER namerules_unique_insert " \ 311 "BEFORE INSERT ON namerules " \ 312 "BEGIN " \ 313 TRIGGER_NAMERULES_UNIQUE_BODY 314 315 #define TRIGGER_NAMERULES_UNIQUE_UPDATE \ 316 "CREATE TRIGGER namerules_unique_update " \ 317 "BEFORE INSERT ON namerules " \ 318 "BEGIN " \ 319 TRIGGER_NAMERULES_UNIQUE_BODY 320 321 #define DB_INSTALL_SQL \ 322 TABLE_NAMERULES ";" \ 323 INDEX_NAMERULES_W2U ";" \ 324 INDEX_NAMERULES_U2W ";" \ 325 TRIGGER_NAMERULES_TOLOWER_INSERT ";" \ 326 TRIGGER_NAMERULES_TOLOWER_UPDATE ";" \ 327 TRIGGER_NAMERULES_UNIQUE_INSERT ";" \ 328 TRIGGER_NAMERULES_UNIQUE_UPDATE ";" 329 330 #define DB_VERSION_SQL \ 331 "SELECT CASE (SELECT count(*) FROM sqlite_master) WHEN 0 THEN 0 ELSE " \ 332 "(CASE (SELECT count(*) FROM sqlite_master WHERE " \ 333 "sql = '" TABLE_NAMERULES_v1 "' OR " \ 334 "sql = '" INDEX_NAMERULES_W2U_v1 "' OR " \ 335 "sql = '" INDEX_NAMERULES_U2W "') " \ 336 "WHEN 3 THEN 1 ELSE "\ 337 "(CASE (SELECT count(*) FROM sqlite_master WHERE " \ 338 "sql = '" TABLE_NAMERULES "' OR " \ 339 "sql = '" INDEX_NAMERULES_W2U "' OR " \ 340 "sql = '" INDEX_NAMERULES_U2W "' OR " \ 341 "sql = '" TRIGGER_NAMERULES_TOLOWER_INSERT "' OR " \ 342 "sql = '" TRIGGER_NAMERULES_TOLOWER_UPDATE "' OR " \ 343 "sql = \"" TRIGGER_NAMERULES_UNIQUE_INSERT "\" OR " \ 344 "sql = \"" TRIGGER_NAMERULES_UNIQUE_UPDATE "\") " \ 345 "WHEN 7 THEN 2 ELSE -1 END) END) END AS version;" 346 347 /* SQL for upgrading an existing name rules DB. Includes DB_INSTALL_SQL */ 348 #define DB_UPGRADE_FROM_v1_SQL \ 349 "CREATE TABLE namerules_new " TABLE_NAMERULES_BODY ";" \ 350 "INSERT INTO namerules_new SELECT is_user, is_user, windomain, " \ 351 "winname, winname, is_nt4, unixname, w2u_order, u2w_order " \ 352 "FROM namerules;" \ 353 "DROP TABLE namerules;" \ 354 DB_INSTALL_SQL \ 355 "INSERT INTO namerules SELECT * FROM namerules_new;" \ 356 "DROP TABLE namerules_new;" 357 358 #define DB_VERSION 2 359 360 #ifdef __cplusplus 361 } 362 #endif 363 364 365 #endif /* _SCHEMA_H */ 366