1 /*- 2 * Copyright (c) 2011-2019 Baptiste Daroussin <bapt@FreeBSD.org> 3 * Copyright (c) 2011-2012 Julien Laffaye <jlaffaye@FreeBSD.org> 4 * Copyright (c) 2013 Matthew Seaman <matthew@FreeBSD.org> 5 * Copyright (c) 2013-2014 Vsevolod Stakhov <vsevolod@FreeBSD.org> 6 * All rights reserved. 7 * 8 * Redistribution and use in source and binary forms, with or without 9 * modification, are permitted provided that the following conditions 10 * are met: 11 * 1. Redistributions of source code must retain the above copyright 12 * notice, this list of conditions and the following disclaimer 13 * in this position and unchanged. 14 * 2. Redistributions in binary form must reproduce the above copyright 15 * notice, this list of conditions and the following disclaimer in the 16 * documentation and/or other materials provided with the distribution. 17 * 18 * THIS SOFTWARE IS PROVIDED BY THE AUTHOR(S) ``AS IS'' AND ANY EXPRESS OR 19 * IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES 20 * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. 21 * IN NO EVENT SHALL THE AUTHOR(S) BE LIABLE FOR ANY DIRECT, INDIRECT, 22 * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT 23 * NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, 24 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY 25 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT 26 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF 27 * THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 28 */ 29 30 #ifndef _DB_UPGRADES 31 #define _DB_UPGRADES 32 33 static struct db_upgrades { 34 int version; 35 const char *sql; 36 } db_upgrades[] = { 37 {1, 38 "CREATE TABLE licenses (" 39 "id INTEGER PRIMARY KEY, " 40 "license TEXT NOT NULL UNIQUE " 41 ");" 42 "CREATE TABLE pkg_licenses_assoc (" 43 "package_id INTEGER REFERENCES packages(id) ON DELETE CASCADE" 44 " ON UPDATE CASCADE, " 45 "license_id INTEGER REFERENCES licenses(id) ON DELETE RESTRICT" 46 " ON UPDATE RESTRICT, " 47 "PRIMARY KEY (package_id, license_id)" 48 ");" 49 "CREATE VIEW pkg_licenses AS SELECT origin, license FROM packages " 50 "INNER JOIN pkg_licenses_assoc ON packages.id = pkg_licenses_assoc.package_id " 51 "INNER JOIN licenses ON pkg_licenses_assoc.license_id = licenses.id;" 52 "CREATE TRIGGER license_insert INSTEAD OF INSERT ON pkg_licenses " 53 "FOR EACH ROW BEGIN " 54 "INSERT OR IGNORE INTO licenses(license) values (NEW.license);" 55 "INSERT INTO pkg_licenses_assoc(package_id, license_id) VALUES " 56 "((SELECT id FROM packages where origin = NEW.origin), " 57 "(SELECT id FROM categories WHERE name = NEW.name));" 58 "END;" 59 }, 60 61 {2, 62 "ALTER TABLE packages ADD licenselogic INTEGER NOT NULL DEFAULT(1);" 63 }, 64 65 {3, 66 "DROP VIEW pkg_licenses;" 67 "DROP TRIGGER license_insert;" 68 "ALTER TABLE licenses RENAME TO todelete;" 69 "CREATE TABLE licenses (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);" 70 "INSERT INTO licenses(id, name) SELECT id, license FROM todelete;" 71 "CREATE VIEW pkg_licenses AS SELECT origin, licenses.name FROM packages " 72 "INNER JOIN pkg_licenses_assoc ON packages.id = pkg_licenses_assoc.package_id " 73 "INNER JOIN licenses ON pkg_licenses_assoc.license_id = licenses.id;" 74 "CREATE TRIGGER license_insert INSTEAD OF INSERT ON pkg_licenses " 75 "FOR EACH ROW BEGIN " 76 "INSERT OR IGNORE INTO licenses(name) values (NEW.name);" 77 "INSERT INTO pkg_licenses_assoc(package_id, license_id) VALUES " 78 "((SELECT id FROM packages where origin = NEW.origin), " 79 "(SELECT id FROM licenses WHERE name = NEW.name));" 80 "END;" 81 "DROP VIEW pkg_mtree;" 82 "CREATE VIEW pkg_mtree AS " 83 "SELECT origin, name, version, comment, desc, mtree.content AS " 84 "mtree, message, arch, osversion, maintainer, www, prefix, " 85 "flatsize, automatic, licenselogic, pkg_format_version " 86 "FROM packages " 87 "INNER JOIN mtree ON packages.mtree_id = mtree.id;" 88 "DROP TRIGGER pkg_insert;" 89 "CREATE TRIGGER pkg_insert INSTEAD OF INSERT ON pkg_mtree " 90 "FOR EACH ROW BEGIN " 91 "INSERT OR IGNORE INTO mtree (content) VALUES (NEW.mtree);" 92 "INSERT OR REPLACE INTO packages(origin, name, version, comment, desc, mtree_id, " 93 "message, arch, osversion, maintainer, www, prefix, flatsize, automatic, licenselogic) " 94 "VALUES (NEW.origin, NEW.name, NEW.version, NEW.comment, NEW.desc, " 95 "(SELECT id FROM mtree WHERE content = NEW.mtree), " 96 "NEW.message, NEW.arch, NEW.osversion, NEW.maintainer, NEW.www, NEW.prefix, " 97 "NEW.flatsize, NEW.automatic, NEW.licenselogic);" 98 "END;" 99 "DROP TABLE todelete;" 100 }, 101 {4, 102 "DROP VIEW pkg_mtree;" 103 "DROP TRIGGER CLEAN_MTREE;" 104 "DROP TRIGGER pkg_insert;" 105 "DROP VIEW pkg_dirs;" 106 "DROP TRIGGER dir_insert;" 107 "ALTER TABLE pkg_dirs_assoc RENAME TO pkg_directories;" 108 "DROP VIEW pkg_categories;" 109 "DROP TRIGGER category_insert;" 110 "ALTER TABLE pkg_categories_assoc RENAME TO pkg_categories;" 111 "DROP VIEW pkg_licenses;" 112 "DROP TRIGGER licenses_insert;" 113 "ALTER TABLE pkg_licenses_assoc RENAME TO pkg_licenses;" 114 }, 115 {5, 116 "CREATE TABLE users (" 117 "id INTEGER PRIMARY KEY, " 118 "name TEXT NOT NULL UNIQUE " 119 ");" 120 "CREATE TABLE pkg_users (" 121 "package_id INTEGER REFERENCES packages(id) ON DELETE CASCADE" 122 " ON UPDATE CASCADE, " 123 "user_id INTEGER REFERENCES users(id) ON DELETE RESTRICT" 124 " ON UPDATE RESTRICT, " 125 "UNIQUE(package_id, user_id)" 126 ");" 127 "CREATE TABLE groups (" 128 "id INTEGER PRIMARY KEY, " 129 "name TEXT NOT NULL UNIQUE " 130 ");" 131 "CREATE TABLE pkg_groups (" 132 "package_id INTEGER REFERENCES packages(id) ON DELETE CASCADE" 133 " ON UPDATE CASCADE, " 134 "group_id INTEGER REFERENCES groups(id) ON DELETE RESTRICT" 135 " ON UPDATE RESTRICT, " 136 "UNIQUE(package_id, group_id)" 137 ");" 138 }, 139 {6, 140 "ALTER TABLE pkg_directories ADD try INTEGER;" 141 "UPDATE pkg_directories SET try = 1;" 142 }, 143 {7, 144 "CREATE INDEX deporigini on deps(origin);" 145 }, 146 {8, 147 "DROP TABLE conflicts;" 148 }, 149 {9, 150 "CREATE TABLE shlibs (" 151 "id INTEGER PRIMARY KEY," 152 "name TEXT NOT NULL UNIQUE" 153 ");" 154 "CREATE TABLE pkg_shlibs (" 155 "package_id INTEGER REFERENCES packages(id) ON DELETE CASCADE" 156 " ON UPDATE CASCADE," 157 "shlib_id INTEGER REFERENCES shlibs(id) ON DELETE RESTRICT" 158 " ON UPDATE RESTRICT," 159 "PRIMARY KEY (package_id, shlib_id)" 160 ");" 161 }, 162 {10, 163 "ALTER TABLE packages RENAME TO oldpkgs;" 164 "UPDATE oldpkgs set arch=myarch();" 165 "CREATE TABLE packages (" 166 "id INTEGER PRIMARY KEY," 167 "origin TEXT UNIQUE NOT NULL," 168 "name TEXT NOT NULL," 169 "version TEXT NOT NULL," 170 "comment TEXT NOT NULL," 171 "desc TEXT NOT NULL," 172 "mtree_id INTEGER REFERENCES mtree(id) ON DELETE RESTRICT" 173 " ON UPDATE CASCADE," 174 "message TEXT," 175 "arch TEXT NOT NULL, " 176 "maintainer TEXT NOT NULL, " 177 "www TEXT," 178 "prefix TEXT NOT NULL, " 179 "flatsize INTEGER NOT NULL," 180 "automatic INTEGER NOT NULL," 181 "licenselogic INTEGER NOT NULL," 182 "pkg_format_version INTEGER " 183 ");" 184 "INSERT INTO packages (id, origin, name, version, comment, desc, " 185 "mtree_id, message, arch, maintainer, www, prefix, flatsize, " 186 "automatic, licenselogic, pkg_format_version) " 187 "SELECT oldpkgs.id, origin, name, version, comment, desc, mtree_id, " 188 "message, arch, maintainer, www, prefix, flatsize, automatic, " 189 "licenselogic, pkg_format_version FROM oldpkgs;" 190 "DROP TABLE oldpkgs;" 191 }, 192 {11, 193 "ALTER TABLE packages RENAME TO oldpkgs;" 194 "CREATE TABLE packages (" 195 "id INTEGER PRIMARY KEY," 196 "origin TEXT UNIQUE NOT NULL," 197 "name TEXT NOT NULL," 198 "version TEXT NOT NULL," 199 "comment TEXT NOT NULL," 200 "desc TEXT NOT NULL," 201 "mtree_id INTEGER REFERENCES mtree(id) ON DELETE RESTRICT" 202 " ON UPDATE CASCADE," 203 "message TEXT," 204 "arch TEXT NOT NULL," 205 "maintainer TEXT NOT NULL, " 206 "www TEXT," 207 "prefix TEXT NOT NULL," 208 "flatsize INTEGER NOT NULL," 209 "automatic INTEGER NOT NULL," 210 "licenselogic INTEGER NOT NULL," 211 "infos TEXT, " 212 "time INTEGER," 213 "pkg_format_version INTEGER" 214 ");" 215 "INSERT INTO packages (id, origin, name, version, comment, desc, " 216 "mtree_id, message, arch, maintainer, www, prefix, flatsize, " 217 "automatic, licenselogic, time, pkg_format_version) " 218 "SELECT id, origin, name, version, comment, desc, " 219 "mtree_id, message, arch, maintainer, www, prefix, flatsize, " 220 "automatic, licenselogic, time, pkg_format_version " 221 "FROM oldpkgs;" 222 "DROP TABLE oldpkgs;" 223 }, 224 {12, 225 "CREATE INDEX scripts_package_id ON scripts (package_id);" 226 "CREATE INDEX options_package_id ON options (package_id);" 227 "CREATE INDEX deps_package_id ON deps (package_id);" 228 "CREATE INDEX files_package_id ON files (package_id);" 229 "CREATE INDEX pkg_directories_package_id ON pkg_directories (package_id);" 230 "CREATE INDEX pkg_categories_package_id ON pkg_categories (package_id);" 231 "CREATE INDEX pkg_licenses_package_id ON pkg_licenses (package_id);" 232 "CREATE INDEX pkg_users_package_id ON pkg_users (package_id);" 233 "CREATE INDEX pkg_groups_package_id ON pkg_groups (package_id);" 234 "CREATE INDEX pkg_shlibs_package_id ON pkg_shlibs (package_id);" 235 "CREATE INDEX pkg_directories_directory_id ON pkg_directories (directory_id);" 236 }, 237 {13, 238 "ALTER TABLE packages RENAME TO oldpkgs;" 239 "CREATE TABLE packages (" 240 "id INTEGER PRIMARY KEY," 241 "origin TEXT UNIQUE NOT NULL," 242 "name TEXT NOT NULL," 243 "version TEXT NOT NULL," 244 "comment TEXT NOT NULL," 245 "desc TEXT NOT NULL," 246 "mtree_id INTEGER REFERENCES mtree(id) ON DELETE RESTRICT" 247 " ON UPDATE CASCADE," 248 "message TEXT," 249 "arch TEXT NOT NULL," 250 "maintainer TEXT NOT NULL, " 251 "www TEXT," 252 "prefix TEXT NOT NULL," 253 "flatsize INTEGER NOT NULL," 254 "automatic INTEGER NOT NULL," 255 "locked INTEGER NOT NULL DEFAULT 0," 256 "licenselogic INTEGER NOT NULL," 257 "infos TEXT, " 258 "time INTEGER," 259 "pkg_format_version INTEGER" 260 ");" 261 "INSERT INTO packages (id, origin, name, version, comment, desc, " 262 "mtree_id, message, arch, maintainer, www, prefix, flatsize, " 263 "automatic, licenselogic, time, pkg_format_version) " 264 "SELECT id, origin, name, version, comment, desc, " 265 "mtree_id, message, arch, maintainer, www, prefix, flatsize, " 266 "automatic, licenselogic, time, pkg_format_version " 267 "FROM oldpkgs;" 268 "DROP TABLE oldpkgs;" 269 }, 270 {14, 271 "CREATE TABLE pkg_shlibs_required (" 272 "package_id INTEGER NOT NULL REFERENCES packages(id)" 273 " ON DELETE CASCADE ON UPDATE CASCADE," 274 "shlib_id INTEGER NOT NULL REFERENCES shlibs(id)" 275 " ON DELETE RESTRICT ON UPDATE RESTRICT," 276 "UNIQUE (package_id, shlib_id)" 277 ");" 278 "CREATE TABLE pkg_shlibs_provided (" 279 "package_id INTEGER NOT NULL REFERENCES packages(id)" 280 " ON DELETE CASCADE ON UPDATE CASCADE," 281 "shlib_id INTEGER NOT NULL REFERENCES shlibs(id)" 282 " ON DELETE RESTRICT ON UPDATE RESTRICT," 283 "UNIQUE (package_id, shlib_id)" 284 ");" 285 "INSERT INTO pkg_shlibs_required (package_id, shlib_id)" 286 " SELECT package_id, shlib_id FROM pkg_shlibs;" 287 "CREATE INDEX pkg_shlibs_required_package_id ON pkg_shlibs_required (package_id);" 288 "CREATE INDEX pkg_shlibs_provided_package_id ON pkg_shlibs_provided (package_id);" 289 "DROP INDEX pkg_shlibs_package_id;" 290 "DROP TABLE pkg_shlibs;" 291 }, 292 {15, 293 "CREATE TABLE abstract (" 294 "abstract_id INTEGER PRIMARY KEY," 295 "abstract TEXT NOT NULL UNIQUE" 296 ");" 297 "CREATE TABLE pkg_abstract (" 298 "package_id INTEGER REFERENCES packages(id)" 299 " ON DELETE CASCADE ON UPDATE RESTRICT," 300 "key_id INTEGER NOT NULL REFERENCES abstract(abstract_id)" 301 " ON DELETE CASCADE ON UPDATE RESTRICT," 302 "value_id INTEGER NOT NULL REFERENCES abstract(abstract_id)" 303 " ON DELETE CASCADE ON UPDATE RESTRICT" 304 ");" 305 "CREATE INDEX pkg_abstract_package_id ON pkg_abstract(package_id);" 306 }, 307 {16, 308 "ALTER TABLE packages ADD COLUMN manifestdigest TEXT NULL;" 309 "CREATE INDEX IF NOT EXISTS pkg_digest_id ON packages(origin, manifestdigest);" 310 }, 311 {17, 312 "CREATE TABLE annotation (" 313 "annotation_id INTEGER PRIMARY KEY," 314 "annotation TEXT NOT NULL UNIQUE" 315 ");" 316 "CREATE TABLE pkg_annotation (" 317 "package_id INTEGER REFERENCES packages(id)" 318 " ON DELETE CASCADE ON UPDATE RESTRICT," 319 "tag_id INTEGER NOT NULL REFERENCES annotation(annotation_id)" 320 " ON DELETE CASCADE ON UPDATE RESTRICT," 321 "value_id INTEGER NOT NULL REFERENCES annotation(annotation_id)" 322 " ON DELETE CASCADE ON UPDATE RESTRICT," 323 "UNIQUE(package_id, tag_id)" 324 ");" 325 "CREATE INDEX pkg_annotation_package_id ON pkg_annotation(package_id);" 326 "INSERT INTO annotation (annotation_id, annotation)" 327 "SELECT abstract_id, abstract FROM abstract;" 328 "INSERT INTO pkg_annotation (package_id,tag_id,value_id)" 329 "SELECT package_id,key_id,value_id FROM pkg_abstract;" 330 "DROP INDEX pkg_abstract_package_id;" 331 "DROP TABLE pkg_abstract;" 332 "DROP TABLE abstract;" 333 }, 334 {18, 335 "CREATE VIEW pkg_shlibs AS SELECT * FROM pkg_shlibs_required;" 336 "CREATE TRIGGER pkg_shlibs_update " 337 "INSTEAD OF UPDATE ON pkg_shlibs " 338 "FOR EACH ROW BEGIN " 339 "UPDATE pkg_shlibs_required " 340 "SET package_id = new.package_id, " 341 "shlib_id = new.shlib_id " 342 "WHERE shlib_id = old.shlib_id " 343 "AND package_id = old.package_id; " 344 "END;" 345 "CREATE TRIGGER pkg_shlibs_insert " 346 "INSTEAD OF INSERT ON pkg_shlibs " 347 "FOR EACH ROW BEGIN " 348 "INSERT INTO pkg_shlibs_required (shlib_id, package_id) " 349 "VALUES (new.shlib_id, new.package_id); " 350 "END;" 351 "CREATE TRIGGER pkg_shlibs_delete " 352 "INSTEAD OF DELETE ON pkg_shlibs " 353 "FOR EACH ROW BEGIN " 354 "DELETE FROM pkg_shlibs_required " 355 "WHERE shlib_id = old.shlib_id " 356 "AND package_id = old.package_id; " 357 "END;" 358 }, 359 {19, 360 "INSERT OR IGNORE INTO annotation(annotation) VALUES ('_INFOS_');" 361 "INSERT OR IGNORE INTO annotation(annotation) SELECT DISTINCT infos" 362 " FROM packages;" 363 "INSERT OR IGNORE INTO pkg_annotation(package_id, tag_id, value_id)" 364 " SELECT p.id, (SELECT annotation_id FROM annotation" 365 " WHERE annotation = '_INFOS_'), a.annotation_id" 366 " FROM packages p JOIN annotation a" 367 " ON (p.infos = a.annotation);" 368 "DELETE FROM annotation WHERE " 369 "annotation_id NOT IN ( SELECT DISTINCT tag_id FROM " 370 " pkg_annotation) AND " 371 "annotation_id NOT IN ( SELECT DISTINCT value_id FROM " 372 " pkg_annotation);" 373 "ALTER TABLE packages RENAME TO oldpkgs;" 374 "CREATE TABLE packages (" 375 "id INTEGER PRIMARY KEY," 376 "origin TEXT UNIQUE NOT NULL," 377 "name TEXT NOT NULL," 378 "version TEXT NOT NULL," 379 "comment TEXT NOT NULL," 380 "desc TEXT NOT NULL," 381 "mtree_id INTEGER REFERENCES mtree(id) ON DELETE RESTRICT" 382 " ON UPDATE CASCADE," 383 "message TEXT," 384 "arch TEXT NOT NULL," 385 "maintainer TEXT NOT NULL, " 386 "www TEXT," 387 "prefix TEXT NOT NULL," 388 "flatsize INTEGER NOT NULL," 389 "automatic INTEGER NOT NULL," 390 "locked INTEGER NOT NULL DEFAULT 0," 391 "licenselogic INTEGER NOT NULL," 392 "time INTEGER, " 393 "manifestdigest TEXT NULL, " 394 "pkg_format_version INTEGER" 395 ");" 396 "INSERT INTO packages (id, origin, name, version, comment, desc, " 397 "mtree_id, message, arch, maintainer, www, prefix, flatsize, " 398 "automatic, locked, licenselogic, time, manifestdigest, " 399 "pkg_format_version) " 400 "SELECT id, origin, name, version, comment, desc, " 401 "mtree_id, message, arch, maintainer, www, prefix, flatsize, " 402 "automatic, locked, licenselogic, time, manifestdigest, " 403 "pkg_format_version " 404 "FROM oldpkgs;" 405 "DROP TABLE oldpkgs;" 406 }, 407 {20, 408 "CREATE TABLE pkg_script (" 409 "package_id INTEGER REFERENCES packages(id) ON DELETE CASCADE" 410 " ON UPDATE CASCADE," 411 "type INTEGER," 412 "script_id INTEGER REFERENCES script(script_id)" 413 " ON DELETE RESTRICT ON UPDATE CASCADE," 414 "PRIMARY KEY (package_id, type)" 415 ");" 416 "CREATE TABLE script (" 417 "script_id INTEGER PRIMARY KEY," 418 "script TEXT NOT NULL UNIQUE" 419 ");" 420 "INSERT INTO script(script)" 421 " SELECT DISTINCT script FROM scripts;" 422 "INSERT INTO pkg_script(package_id,type,script_id)" 423 " SELECT package_id, type, script_id FROM" 424 " script s JOIN scripts ss ON (s.script = ss.script);" 425 "CREATE INDEX pkg_script_package_id ON pkg_script(package_id);" 426 "DROP TABLE scripts;" 427 "CREATE VIEW scripts AS SELECT package_id, script, type" 428 " FROM pkg_script ps JOIN script s" 429 " ON (ps.script_id = s.script_id);" 430 "CREATE TRIGGER scripts_update" 431 " INSTEAD OF UPDATE ON scripts " 432 "FOR EACH ROW BEGIN" 433 " INSERT OR IGNORE INTO script(script)" 434 " VALUES(new.script);" 435 " UPDATE pkg_script" 436 " SET package_id = new.package_id," 437 " type = new.type," 438 " script_id = ( SELECT script_id" 439 " FROM script WHERE script = new.script )" 440 " WHERE package_id = old.package_id" 441 " AND type = old.type;" 442 "END;" 443 "CREATE TRIGGER scripts_insert" 444 " INSTEAD OF INSERT ON scripts " 445 "FOR EACH ROW BEGIN" 446 " INSERT OR IGNORE INTO script(script)" 447 " VALUES(new.script);" 448 " INSERT INTO pkg_script(package_id, type, script_id) " 449 " SELECT new.package_id, new.type, s.script_id" 450 " FROM script s WHERE new.script = s.script;" 451 "END;" 452 "CREATE TRIGGER scripts_delete" 453 " INSTEAD OF DELETE ON scripts " 454 "FOR EACH ROW BEGIN" 455 " DELETE FROM pkg_script" 456 " WHERE package_id = old.package_id" 457 " AND type = old.type;" 458 " DELETE FROM script" 459 " WHERE script_id NOT IN" 460 " (SELECT DISTINCT script_id FROM pkg_script);" 461 "END;" 462 }, 463 {21, 464 "CREATE TABLE option (" 465 "option_id INTEGER PRIMARY KEY," 466 "option TEXT NOT NULL UNIQUE" 467 ");" 468 "CREATE TABLE option_desc (" 469 "option_desc_id INTEGER PRIMARY KEY," 470 "option_desc TEXT NOT NULL UNIQUE" 471 ");" 472 "CREATE TABLE pkg_option (" 473 "package_id INTEGER NOT NULL REFERENCES packages(id) " 474 "ON DELETE CASCADE ON UPDATE CASCADE," 475 "option_id INTEGER NOT NULL REFERENCES option(option_id) " 476 "ON DELETE RESTRICT ON UPDATE CASCADE," 477 "value TEXT NOT NULL," 478 "PRIMARY KEY(package_id, option_id)" 479 ");" 480 "CREATE TABLE pkg_option_desc (" 481 "package_id INTEGER NOT NULL REFERENCES packages(id) " 482 "ON DELETE CASCADE ON UPDATE CASCADE," 483 "option_id INTEGER NOT NULL REFERENCES option(option_id) " 484 "ON DELETE RESTRICT ON UPDATE CASCADE," 485 "option_desc_id INTEGER NOT NULL " 486 "REFERENCES option_desc(option_desc_id) " 487 "ON DELETE RESTRICT ON UPDATE CASCADE," 488 "PRIMARY KEY(package_id, option_id)" 489 ");" 490 "CREATE TABLE pkg_option_default (" 491 "package_id INTEGER NOT NULL REFERENCES packages(id) " 492 "ON DELETE CASCADE ON UPDATE CASCADE," 493 "option_id INTEGER NOT NULL REFERENCES option(option_id) " 494 "ON DELETE RESTRICT ON UPDATE CASCADE," 495 "default_value TEXT NOT NULL," 496 "PRIMARY KEY(package_id, option_id)" 497 ");" 498 "INSERT INTO option(option) " 499 "SELECT DISTINCT option FROM options;" 500 "INSERT INTO pkg_option(package_id, option_id, value) " 501 "SELECT package_id, option_id, value " 502 "FROM options oo JOIN option o ON (oo.option = o.option);" 503 "DROP TABLE options;" 504 "CREATE VIEW options AS " 505 "SELECT package_id, option, value " 506 "FROM pkg_option JOIN option USING(option_id);" 507 "CREATE TRIGGER options_update " 508 "INSTEAD OF UPDATE ON options " 509 "FOR EACH ROW BEGIN " 510 "UPDATE pkg_option " 511 "SET value = new.value " 512 "WHERE package_id = old.package_id AND " 513 "option_id = ( SELECT option_id FROM option " 514 "WHERE option = old.option );" 515 "END;" 516 "CREATE TRIGGER options_insert " 517 "INSTEAD OF INSERT ON options " 518 "FOR EACH ROW BEGIN " 519 "INSERT OR IGNORE INTO option(option) " 520 "VALUES(new.option);" 521 "INSERT INTO pkg_option(package_id, option_id, value) " 522 "VALUES (new.package_id, " 523 "(SELECT option_id FROM option " 524 "WHERE option = new.option), " 525 "new.value);" 526 "END;" 527 "CREATE TRIGGER options_delete " 528 "INSTEAD OF DELETE ON options " 529 "FOR EACH ROW BEGIN " 530 "DELETE FROM pkg_option " 531 "WHERE package_id = old.package_id AND " 532 "option_id = ( SELECT option_id FROM option " 533 "WHERE option = old.option );" 534 "DELETE FROM option " 535 "WHERE option_id NOT IN " 536 "( SELECT DISTINCT option_id FROM pkg_option );" 537 "END;" 538 }, 539 {22, 540 "CREATE TABLE pkg_conflicts (" 541 "package_id INTEGER NOT NULL REFERENCES packages(id)" 542 " ON DELETE CASCADE ON UPDATE CASCADE," 543 "conflict_id INTEGER NOT NULL," 544 "UNIQUE(package_id, conflict_id)" 545 ");" 546 "CREATE TABLE provides(" 547 " id INTEGER PRIMARY KEY," 548 " provide TEXT NOT NULL" 549 ");" 550 "CREATE TABLE pkg_provides (" 551 "package_id INTEGER NOT NULL REFERENCES packages(id)" 552 " ON DELETE CASCADE ON UPDATE CASCADE," 553 "provide_id INTEGER NOT NULL REFERENCES provides(id)" 554 " ON DELETE RESTRICT ON UPDATE RESTRICT," 555 "UNIQUE(package_id, provide_id)" 556 ");" 557 }, 558 {23, 559 "CREATE VIRTUAL TABLE pkg_search USING fts4(id, name, origin);" 560 "INSERT INTO pkg_search SELECT id, name || '-' || version, origin FROM packages;" 561 "CREATE INDEX packages_origin ON packages(origin COLLATE NOCASE);" 562 "CREATE INDEX packages_name ON packages(name COLLATE NOCASE);" 563 }, 564 /* pkg_lock existed during 1.3 dev cycle before moving to schema */ 565 {24, 566 "CREATE TABLE IF NOT EXISTS pkg_lock (" 567 "exclusive INTEGER(1)," 568 "advisory INTEGER(1)," 569 "read INTEGER(8)" 570 ");" 571 "CREATE TABLE IF NOT EXISTS pkg_lock_pid (" 572 "pid INTEGER PRIMARY KEY" 573 ");" 574 "DELETE FROM pkg_lock;" 575 "DELETE FROM pkg_lock_pid;" 576 "INSERT INTO pkg_lock VALUES(0,0,0);" 577 }, 578 /* Move uniqueness outside of tables into indexes to simplify evolution */ 579 {25, 580 "ALTER TABLE packages RENAME TO oldpkgs;" 581 "CREATE TABLE packages (" 582 "id INTEGER PRIMARY KEY," 583 "origin TEXT NOT NULL," 584 "name TEXT NOT NULL," 585 "version TEXT NOT NULL," 586 "comment TEXT NOT NULL," 587 "desc TEXT NOT NULL," 588 "mtree_id INTEGER REFERENCES mtree(id) ON DELETE RESTRICT" 589 " ON UPDATE CASCADE," 590 "message TEXT," 591 "arch TEXT NOT NULL," 592 "maintainer TEXT NOT NULL, " 593 "www TEXT," 594 "prefix TEXT NOT NULL," 595 "flatsize INTEGER NOT NULL," 596 "automatic INTEGER NOT NULL," 597 "locked INTEGER NOT NULL DEFAULT 0," 598 "licenselogic INTEGER NOT NULL," 599 "infos TEXT, " 600 "time INTEGER," 601 "pkg_format_version INTEGER" 602 ");" 603 "CREATE UNIQUE INDEX packages_unique ON packages(origin, name);" 604 "INSERT INTO packages (id, origin, name, version, comment, desc, " 605 "mtree_id, message, arch, maintainer, www, prefix, flatsize, " 606 "automatic, licenselogic, time, pkg_format_version) " 607 "SELECT id, origin, name, version, comment, desc, " 608 "mtree_id, message, arch, maintainer, www, prefix, flatsize, " 609 "automatic, licenselogic, time, pkg_format_version " 610 "FROM oldpkgs;" 611 "DROP TABLE oldpkgs;" 612 "ALTER TABLE deps RENAME TO olddeps;" 613 "CREATE TABLE deps (" 614 "origin TEXT NOT NULL," 615 "name TEXT NOT NULL," 616 "version TEXT NOT NULL," 617 "package_id INTEGER REFERENCES packages(id) ON DELETE CASCADE" 618 " ON UPDATE CASCADE" 619 ");" 620 "CREATE UNIQUE INDEX deps_unique ON deps(origin, version, package_id);" 621 "INSERT INTO deps (origin, name, version, package_id) " 622 "SELECT origin, name, version, package_id " 623 "FROM olddeps;" 624 "DROP TABLE olddeps;" 625 }, 626 {26, 627 "ALTER TABLE packages ADD COLUMN manifestdigest TEXT NULL;" 628 "CREATE INDEX IF NOT EXISTS pkg_digest_id ON packages(origin, manifestdigest);" 629 }, 630 {27, 631 "CREATE INDEX IF NOT EXISTS packages_origin ON packages(origin COLLATE NOCASE);" 632 "CREATE INDEX IF NOT EXISTS packages_name ON packages(name COLLATE NOCASE);" 633 "CREATE INDEX IF NOT EXISTS packages_uid_nocase ON packages(name COLLATE NOCASE, origin COLLATE NOCASE);" 634 "CREATE INDEX IF NOT EXISTS packages_version_nocase ON packages(name COLLATE NOCASE, version);" 635 "CREATE INDEX IF NOT EXISTS packages_uid ON packages(name, origin COLLATE NOCASE);" 636 "CREATE INDEX IF NOT EXISTS packages_version ON packages(name, version);" 637 }, 638 {28, 639 "CREATE TABLE config_files (" 640 "path TEXT NOT NULL UNIQUE, " 641 "content TEXT, " 642 "package_id INTEGER REFERENCES packages(id) ON DELETE CASCADE" 643 " ON UPDATE CASCADE" 644 ");" 645 }, 646 {29, 647 "DROP INDEX packages_unique;" 648 "UPDATE packages SET name= name || \"~pkg-renamed~\" || hex(randomblob(2)) " 649 "WHERE name IN (" 650 "SELECT name FROM packages GROUP BY name HAVING count(name) > 1 " 651 ");" 652 "CREATE UNIQUE INDEX packages_unique ON packages(name);" 653 }, 654 {30, 655 "DROP INDEX deps_unique;" 656 "CREATE UNIQUE INDEX deps_unique ON deps(name, version, package_id);" 657 }, 658 {31, 659 "CREATE TABLE requires(" 660 " id INTEGER PRIMARY KEY," 661 " require TEXT NOT NULL" 662 ");" 663 "CREATE TABLE pkg_requires (" 664 "package_id INTEGER NOT NULL REFERENCES packages(id)" 665 " ON DELETE CASCADE ON UPDATE CASCADE," 666 "require_id INTEGER NOT NULL REFERENCES requires(id)" 667 " ON DELETE RESTRICT ON UPDATE RESTRICT," 668 "UNIQUE(package_id, require_id)" 669 ");" 670 }, 671 {32, 672 "ALTER TABLE packages ADD COLUMN dep_formula TEXT NULL;" 673 }, 674 {33, 675 "ALTER TABLE packages ADD COLUMN vital INTEGER NOT NULL DEFAULT 0;" 676 }, 677 {34, 678 "DROP TABLE pkg_search;" 679 }, 680 {35, 681 "CREATE TABLE lua_script(" 682 " lua_script_id INTEGER PRIMARY KEY," 683 " lua_script TEXT NOT NULL UNIQUE" 684 ");" 685 "CREATE TABLE pkg_lua_script (" 686 "package_id INTEGER NOT NULL REFERENCES packages(id)" 687 " ON DELETE CASCADE ON UPDATE CASCADE," 688 "lua_script_id INTEGER NOT NULL REFERENCES lua_script(lua_script_id)" 689 " ON DELETE RESTRICT ON UPDATE RESTRICT," 690 "type INTEGER," 691 "UNIQUE(package_id, lua_script_id)" 692 ");" 693 "CREATE VIEW lua_scripts AS " 694 "SELECT package_id, lua_script, type " 695 "FROM pkg_lua_script JOIN lua_script USING(lua_script_id);" 696 "CREATE TRIGGER lua_script_update " 697 "INSTEAD OF UPDATE ON lua_scripts " 698 "FOR EACH ROW BEGIN " 699 "UPDATE pkg_lua_script " 700 "SET type = new.type " 701 "WHERE package_id = old.package_id AND " 702 "lua_script_id = (SELECT lua_script_id FROM lua_script " 703 "WHERE lua_script = old.lua_script );" 704 "END;" 705 "CREATE TRIGGER lua_script_insert " 706 "INSTEAD OF INSERT ON lua_scripts " 707 "FOR EACH ROW BEGIN " 708 "INSERT OR IGNORE INTO lua_script(lua_script) " 709 "VALUES(new.lua_script);" 710 "INSERT INTO pkg_lua_script(package_id, lua_script_id, type) " 711 "VALUES (new.package_id, " 712 "(SELECT lua_script_id FROM lua_script " 713 "WHERE lua_script = new.lua_script), " 714 "new.type);" 715 "END;" 716 "CREATE TRIGGER lua_script_delete " 717 "INSTEAD OF DELETE ON lua_scripts " 718 "FOR EACH ROW BEGIN " 719 "DELETE FROM pkg_lua_script " 720 "WHERE package_id = old.package_id AND " 721 "lua_script_id = ( SELECT lua_script_id FROM lua_script " 722 "WHERE lua_script = old.lua_script );" 723 "DELETE FROM lua_script " 724 "WHERE lua_script_id NOT IN " 725 "( SELECT DISTINCT lua_script_id from lua_script );" 726 "END;" 727 }, 728 /* Mark the end of the array */ 729 { -1, NULL } 730 731 }; 732 733 #endif 734