wc-metadata.sql revision 289166
1/* wc-metadata.sql -- schema used in the wc-metadata SQLite database 2 * This is intended for use with SQLite 3 3 * 4 * ==================================================================== 5 * Licensed to the Apache Software Foundation (ASF) under one 6 * or more contributor license agreements. See the NOTICE file 7 * distributed with this work for additional information 8 * regarding copyright ownership. The ASF licenses this file 9 * to you under the Apache License, Version 2.0 (the 10 * "License"); you may not use this file except in compliance 11 * with the License. You may obtain a copy of the License at 12 * 13 * http://www.apache.org/licenses/LICENSE-2.0 14 * 15 * Unless required by applicable law or agreed to in writing, 16 * software distributed under the License is distributed on an 17 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 18 * KIND, either express or implied. See the License for the 19 * specific language governing permissions and limitations 20 * under the License. 21 * ==================================================================== 22 */ 23 24/* 25 * the KIND column in these tables has one of the following values 26 * (documented in the corresponding C type #svn_kind_t): 27 * "file" 28 * "dir" 29 * "symlink" 30 * "unknown" 31 * 32 * the PRESENCE column in these tables has one of the following values 33 * (see also the C type #svn_wc__db_status_t): 34 * "normal" 35 * "server-excluded" -- server has declared it excluded (ie. authz failure) 36 * "excluded" -- administratively excluded (ie. sparse WC) 37 * "not-present" -- node not present at this REV 38 * "incomplete" -- state hasn't been filled in 39 * "base-deleted" -- node represents a delete of a BASE node 40 */ 41 42/* One big list of statements to create our (current) schema. */ 43-- STMT_CREATE_SCHEMA 44 45/* ------------------------------------------------------------------------- */ 46 47CREATE TABLE REPOSITORY ( 48 id INTEGER PRIMARY KEY AUTOINCREMENT, 49 50 /* The root URL of the repository. This value is URI-encoded. */ 51 root TEXT UNIQUE NOT NULL, 52 53 /* the UUID of the repository */ 54 uuid TEXT NOT NULL 55 ); 56 57/* Note: a repository (identified by its UUID) may appear at multiple URLs. 58 For example, http://example.com/repos/ and https://example.com/repos/. */ 59CREATE INDEX I_UUID ON REPOSITORY (uuid); 60CREATE INDEX I_ROOT ON REPOSITORY (root); 61 62 63/* ------------------------------------------------------------------------- */ 64 65CREATE TABLE WCROOT ( 66 id INTEGER PRIMARY KEY AUTOINCREMENT, 67 68 /* absolute path in the local filesystem. NULL if storing metadata in 69 the wcroot itself. */ 70 local_abspath TEXT UNIQUE 71 ); 72 73CREATE UNIQUE INDEX I_LOCAL_ABSPATH ON WCROOT (local_abspath); 74 75 76/* ------------------------------------------------------------------------- */ 77 78/* The PRISTINE table keeps track of pristine texts. Each row describes a 79 single pristine text. The text itself is stored in a file whose name is 80 derived from the 'checksum' column. Each pristine text is referenced by 81 any number of rows in the NODES and ACTUAL_NODE tables. 82 83 In future, the pristine text file may be compressed. 84 */ 85CREATE TABLE PRISTINE ( 86 /* The SHA-1 checksum of the pristine text. This is a unique key. The 87 SHA-1 checksum of a pristine text is assumed to be unique among all 88 pristine texts referenced from this database. */ 89 checksum TEXT NOT NULL PRIMARY KEY, 90 91 /* Enumerated values specifying type of compression. The only value 92 supported so far is NULL, meaning that no compression has been applied 93 and the pristine text is stored verbatim in the file. */ 94 compression INTEGER, 95 96 /* The size in bytes of the file in which the pristine text is stored. 97 Used to verify the pristine file is "proper". */ 98 size INTEGER NOT NULL, 99 100 /* The number of rows in the NODES table that have a 'checksum' column 101 value that refers to this row. (References in other places, such as 102 in the ACTUAL_NODE table, are not counted.) */ 103 refcount INTEGER NOT NULL, 104 105 /* Alternative MD5 checksum used for communicating with older 106 repositories. Not strictly guaranteed to be unique among table rows. */ 107 md5_checksum TEXT NOT NULL 108 ); 109 110CREATE INDEX I_PRISTINE_MD5 ON PRISTINE (md5_checksum); 111 112/* ------------------------------------------------------------------------- */ 113 114/* The ACTUAL_NODE table describes text changes and property changes 115 on each node in the WC, relative to the NODES table row for the 116 same path. (A NODES row must exist if this node exists, but an 117 ACTUAL_NODE row can exist on its own if it is just recording info 118 on a non-present node - a tree conflict or a changelist, for 119 example.) 120 121 The ACTUAL_NODE table row for a given path exists if the node at that 122 path is known to have text or property changes relative to its 123 NODES row. ("Is known" because a text change on disk may not yet 124 have been discovered and recorded here.) 125 126 The ACTUAL_NODE table row for a given path may also exist in other cases, 127 including if the "changelist" or any of the conflict columns have a 128 non-null value. 129 */ 130CREATE TABLE ACTUAL_NODE ( 131 /* specifies the location of this node in the local filesystem */ 132 wc_id INTEGER NOT NULL REFERENCES WCROOT (id), 133 local_relpath TEXT NOT NULL, 134 135 /* parent's local_relpath for aggregating children of a given parent. 136 this will be "" if the parent is the wcroot. NULL if this is the 137 wcroot node. */ 138 parent_relpath TEXT, 139 140 /* serialized skel of this node's properties. NULL implies no change to 141 the properties, relative to WORKING/BASE as appropriate. */ 142 properties BLOB, 143 144 /* relpaths of the conflict files. */ 145 /* ### These columns will eventually be merged into conflict_data below. */ 146 conflict_old TEXT, 147 conflict_new TEXT, 148 conflict_working TEXT, 149 prop_reject TEXT, 150 151 /* if not NULL, this node is part of a changelist. */ 152 changelist TEXT, 153 154 /* ### need to determine values. "unknown" (no info), "admin" (they 155 ### used something like 'svn edit'), "noticed" (saw a mod while 156 ### scanning the filesystem). */ 157 text_mod TEXT, 158 159 /* if a directory, serialized data for all of tree conflicts therein. 160 ### This column will eventually be merged into the conflict_data column, 161 ### but within the ACTUAL node of the tree conflict victim itself, rather 162 ### than the node of the tree conflict victim's parent directory. */ 163 tree_conflict_data TEXT, 164 165 /* A skel containing the conflict details. */ 166 conflict_data BLOB, 167 168 /* Three columns containing the checksums of older, left and right conflict 169 texts. Stored in a column to allow storing them in the pristine store */ 170 /* stsp: This is meant for text conflicts, right? What about property 171 conflicts? Why do we need these in a column to refer to the 172 pristine store? Can't we just parse the checksums from 173 conflict_data as well? 174 rhuijben: Because that won't allow triggers to handle refcounts. 175 We would have to scan all conflict skels before cleaning up the 176 a single file from the pristine stor */ 177 older_checksum TEXT REFERENCES PRISTINE (checksum), 178 left_checksum TEXT REFERENCES PRISTINE (checksum), 179 right_checksum TEXT REFERENCES PRISTINE (checksum), 180 181 PRIMARY KEY (wc_id, local_relpath) 182 ); 183 184CREATE UNIQUE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, parent_relpath, 185 local_relpath); 186 187 188/* ------------------------------------------------------------------------- */ 189 190/* This table is a cache of information about repository locks. */ 191CREATE TABLE LOCK ( 192 /* what repository location is locked */ 193 repos_id INTEGER NOT NULL REFERENCES REPOSITORY (id), 194 repos_relpath TEXT NOT NULL, 195 196 /* Information about the lock. Note: these values are just caches from 197 the server, and are not authoritative. */ 198 lock_token TEXT NOT NULL, 199 /* ### make the following fields NOT NULL ? */ 200 lock_owner TEXT, 201 lock_comment TEXT, 202 lock_date INTEGER, /* an APR date/time (usec since 1970) */ 203 204 PRIMARY KEY (repos_id, repos_relpath) 205 ); 206 207 208/* ------------------------------------------------------------------------- */ 209 210CREATE TABLE WORK_QUEUE ( 211 /* Work items are identified by this value. */ 212 id INTEGER PRIMARY KEY AUTOINCREMENT, 213 214 /* A serialized skel specifying the work item. */ 215 work BLOB NOT NULL 216 ); 217 218 219/* ------------------------------------------------------------------------- */ 220 221CREATE TABLE WC_LOCK ( 222 /* specifies the location of this node in the local filesystem */ 223 wc_id INTEGER NOT NULL REFERENCES WCROOT (id), 224 local_dir_relpath TEXT NOT NULL, 225 226 locked_levels INTEGER NOT NULL DEFAULT -1, 227 228 PRIMARY KEY (wc_id, local_dir_relpath) 229 ); 230 231 232PRAGMA user_version = 233-- define: SVN_WC__VERSION 234; 235 236 237/* ------------------------------------------------------------------------- */ 238 239/* The NODES table describes the way WORKING nodes are layered on top of 240 BASE nodes and on top of other WORKING nodes, due to nested tree structure 241 changes. The layers are modelled using the "op_depth" column. 242 243 An 'operation depth' refers to the number of directory levels down from 244 the WC root at which a tree-change operation (delete, add?, copy, move) 245 was performed. A row's 'op_depth' does NOT refer to the depth of its own 246 'local_relpath', but rather to the depth of the nearest tree change that 247 affects that node. 248 249 The row with op_depth=0 for any given local relpath represents the "base" 250 node that is created and updated by checkout, update, switch and commit 251 post-processing. The row with the highest op_depth for a particular 252 local_relpath represents the working version. Any rows with intermediate 253 op_depth values are not normally visible to the user but may become 254 visible after reverting local changes. 255 256 This table contains full node descriptions for nodes in either the BASE 257 or WORKING trees as described in notes/wc-ng/design. Fields relate 258 both to BASE and WORKING trees, unless documented otherwise. 259 260 For illustration, with a scenario like this: 261 262 # (0) 263 svn rm foo 264 svn cp ^/moo foo # (1) 265 svn rm foo/bar 266 touch foo/bar 267 svn add foo/bar # (2) 268 269 , these are the NODES table rows for the path foo/bar: 270 271 (0) "BASE" ---> NODES (op_depth == 0) 272 (1) NODES (op_depth == 1) 273 (2) NODES (op_depth == 2) 274 275 0 is the original data for foo/bar before 'svn rm foo' (if it existed). 276 1 is the data for foo/bar copied in from ^/moo/bar. 277 2 is the to-be-committed data for foo/bar, created by 'svn add foo/bar'. 278 279 An 'svn revert foo/bar' would remove the NODES of (2). 280 281 */ 282-- STMT_CREATE_NODES 283CREATE TABLE NODES ( 284 /* Working copy location related fields */ 285 286 wc_id INTEGER NOT NULL REFERENCES WCROOT (id), 287 local_relpath TEXT NOT NULL, 288 289 /* Contains the depth (= number of path segments) of the operation 290 modifying the working copy tree structure. All nodes below the root 291 of the operation (aka operation root, aka oproot) affected by the 292 operation will be assigned the same op_depth. 293 294 op_depth == 0 designates the initial checkout; the BASE tree. 295 296 */ 297 op_depth INTEGER NOT NULL, 298 299 /* parent's local_relpath for aggregating children of a given parent. 300 this will be "" if the parent is the wcroot. Since a wcroot will 301 never have a WORKING node the parent_relpath will never be null, 302 except when op_depth == 0 and the node is a wcroot. */ 303 parent_relpath TEXT, 304 305 306 /* Repository location fields */ 307 308 /* When op_depth == 0, these fields refer to the repository location of the 309 BASE node, the location of the initial checkout. 310 311 When op_depth != 0, they indicate where this node was copied/moved from. 312 In this case, the fields are set for the root of the operation and for all 313 children. */ 314 repos_id INTEGER REFERENCES REPOSITORY (id), 315 repos_path TEXT, 316 revision INTEGER, 317 318 319 /* WC state fields */ 320 321 /* The tree state of the node. 322 323 In case 'op_depth' is equal to 0, this node is part of the 'BASE' 324 tree. The 'BASE' represents pristine nodes that are in the 325 repository; it is obtained and modified by commands such as 326 checkout/update/switch. 327 328 In case 'op_depth' is greater than 0, this node is part of a 329 layer of working nodes. The 'WORKING' tree is obtained and 330 modified by commands like delete/copy/revert. 331 332 The 'BASE' and 'WORKING' trees use the same literal values for 333 the 'presence' but the meaning of each value can vary depending 334 on the tree. 335 336 normal: in the 'BASE' tree this is an ordinary node for which we 337 have full information. In the 'WORKING' tree it's an added or 338 copied node for which we have full information. 339 340 not-present: in the 'BASE' tree this is a node that is implied to 341 exist by the parent node, but is not present in the working 342 copy. Typically obtained by delete/commit, or by update to 343 revision in which the node does not exist. In the 'WORKING' 344 tree this is a copy of a 'not-present' node from the 'BASE' 345 tree, and it will be deleted on commit. Such a node cannot be 346 copied directly, but can be copied as a descendant. 347 348 incomplete: in the 'BASE' tree this is an ordinary node for which 349 we do not have full information. Only the name is guaranteed; 350 we may not have all its children, we may not have its checksum, 351 etc. In the 'WORKING' tree this is a copied node for which we 352 do not have the full information. This state is generally 353 obtained when an operation was interrupted. 354 355 base-deleted: not valid in 'BASE' tree. In the 'WORKING' tree 356 this represents a node that is deleted from the tree below the 357 current 'op_depth'. This state is badly named, it should be 358 something like 'deleted'. 359 360 server-excluded: in the 'BASE' tree this is a node that is excluded by 361 authz. The name of the node is known from the parent, but no 362 other information is available. Not valid in the 'WORKING' 363 tree as there is no way to commit such a node. 364 365 excluded: in the 'BASE' tree this node is administratively 366 excluded by the user (sparse WC). In the 'WORKING' tree this 367 is a copy of an excluded node from the 'BASE' tree. Such a 368 node cannot be copied directly but can be copied as a 369 descendant. */ 370 371 presence TEXT NOT NULL, 372 373 /* ### JF: For an old-style move, "copyfrom" info stores its source, but a 374 new WC-NG "move" is intended to be a "true rename" so its copyfrom 375 revision is implicit, being in effect (new head - 1) at commit time. 376 For a (new) move, we need to store or deduce the copyfrom local-relpath; 377 perhaps add a column called "moved_from". */ 378 379 /* Boolean value, specifying if this node was moved here (rather than just 380 copied). This is set on all the nodes in the moved tree. The source of 381 the move is implied by a different node with a moved_to column pointing 382 at the root node of the moved tree. */ 383 moved_here INTEGER, 384 385 /* If the underlying node was moved away (rather than just deleted), this 386 specifies the local_relpath of where the node was moved to. 387 This is set only on the root of a move, and is NULL for all children. 388 389 The op-depth of the moved-to node is not recorded. A moved_to path 390 always points at a node within the highest op-depth layer at the 391 destination. This invariant must be maintained by operations which 392 change existing move information. */ 393 moved_to TEXT, 394 395 396 /* Content fields */ 397 398 /* the kind of the new node. may be "unknown" if the node is not present. */ 399 kind TEXT NOT NULL, 400 401 /* serialized skel of this node's properties (when presence is 'normal' or 402 'incomplete'); an empty skel or NULL indicates no properties. NULL if 403 we have no information about the properties (any other presence). 404 TODO: Choose & require a single representation for 'no properties'. 405 */ 406 properties BLOB, 407 408 /* NULL depth means "default" (typically svn_depth_infinity) */ 409 /* ### depth on WORKING? seems this is a BASE-only concept. how do 410 ### you do "files" on an added-directory? can't really ignore 411 ### the subdirs! */ 412 /* ### maybe a WC-to-WC copy can retain a depth? */ 413 depth TEXT, 414 415 /* The SHA-1 checksum of the pristine text, if this node is a file and was 416 moved here or copied here, else NULL. */ 417 checksum TEXT REFERENCES PRISTINE (checksum), 418 419 /* for kind==symlink, this specifies the target. */ 420 symlink_target TEXT, 421 422 423 /* Last-Change fields */ 424 425 /* If this node was moved here or copied here, then the following fields may 426 have information about their source node. changed_rev must be not-null 427 if this node has presence=="normal". changed_date and changed_author may 428 be null if the corresponding revprops are missing. 429 430 For an added or not-present node, these are null. */ 431 changed_revision INTEGER, 432 changed_date INTEGER, /* an APR date/time (usec since 1970) */ 433 changed_author TEXT, 434 435 436 /* Various cache fields */ 437 438 /* The size in bytes of the working file when it had no local text 439 modifications. This means the size of the text when translated from 440 repository-normal format to working copy format with EOL style 441 translated and keywords expanded according to the properties in the 442 "properties" column of this row. 443 444 NULL if this node is not a file or if the size has not (yet) been 445 computed. */ 446 translated_size INTEGER, 447 448 /* The mod-time of the working file when it was last determined to be 449 logically unmodified relative to its base, taking account of keywords 450 and EOL style. This value is used in the change detection heuristic 451 used by the status command. 452 453 NULL if this node is not a file or if this info has not yet been 454 determined. 455 */ 456 last_mod_time INTEGER, /* an APR date/time (usec since 1970) */ 457 458 /* serialized skel of this node's dav-cache. could be NULL if the 459 node does not have any dav-cache. */ 460 dav_cache BLOB, 461 462 /* Is there a file external in this location. NULL if there 463 is no file external, otherwise '1' */ 464 /* ### Originally we had a wc-1.0 like skel in this place, so we 465 ### check for NULL. 466 ### In Subversion 1.7 we defined this column as TEXT, but Sqlite 467 ### only uses this information for deciding how to optimize 468 ### anyway. */ 469 file_external INTEGER, 470 471 /* serialized skel of this node's inherited properties. NULL if this 472 is not the BASE of a WC root node. */ 473 inherited_props BLOB, 474 475 PRIMARY KEY (wc_id, local_relpath, op_depth) 476 477 ); 478 479CREATE UNIQUE INDEX I_NODES_PARENT ON NODES (wc_id, parent_relpath, 480 local_relpath, op_depth); 481/* I_NODES_MOVED is introduced in format 30 */ 482CREATE UNIQUE INDEX I_NODES_MOVED ON NODES (wc_id, moved_to, op_depth); 483 484/* Many queries have to filter the nodes table to pick only that version 485 of each node with the highest (most "current") op_depth. This view 486 does the heavy lifting for such queries. 487 488 Note that this view includes a row for each and every path that is known 489 in the WC, including, for example, paths that were children of a base- or 490 lower-op-depth directory that has been replaced by something else in the 491 current view. 492 */ 493CREATE VIEW NODES_CURRENT AS 494 SELECT * FROM nodes AS n 495 WHERE op_depth = (SELECT MAX(op_depth) FROM nodes AS n2 496 WHERE n2.wc_id = n.wc_id 497 AND n2.local_relpath = n.local_relpath); 498 499/* Many queries have to filter the nodes table to pick only that version 500 of each node with the BASE ("as checked out") op_depth. This view 501 does the heavy lifting for such queries. */ 502CREATE VIEW NODES_BASE AS 503 SELECT * FROM nodes 504 WHERE op_depth = 0; 505 506-- STMT_CREATE_NODES_TRIGGERS 507 508CREATE TRIGGER nodes_insert_trigger 509AFTER INSERT ON nodes 510WHEN NEW.checksum IS NOT NULL 511BEGIN 512 UPDATE pristine SET refcount = refcount + 1 513 WHERE checksum = NEW.checksum; 514END; 515 516CREATE TRIGGER nodes_delete_trigger 517AFTER DELETE ON nodes 518WHEN OLD.checksum IS NOT NULL 519BEGIN 520 UPDATE pristine SET refcount = refcount - 1 521 WHERE checksum = OLD.checksum; 522END; 523 524CREATE TRIGGER nodes_update_checksum_trigger 525AFTER UPDATE OF checksum ON nodes 526WHEN NEW.checksum IS NOT OLD.checksum 527 /* AND (NEW.checksum IS NOT NULL OR OLD.checksum IS NOT NULL) */ 528BEGIN 529 UPDATE pristine SET refcount = refcount + 1 530 WHERE checksum = NEW.checksum; 531 UPDATE pristine SET refcount = refcount - 1 532 WHERE checksum = OLD.checksum; 533END; 534 535-- STMT_CREATE_EXTERNALS 536 537CREATE TABLE EXTERNALS ( 538 /* Working copy location related fields (like NODES)*/ 539 540 wc_id INTEGER NOT NULL REFERENCES WCROOT (id), 541 local_relpath TEXT NOT NULL, 542 543 /* The working copy root can't be recorded as an external in itself 544 so this will never be NULL. ### ATM only inserted, never queried */ 545 parent_relpath TEXT NOT NULL, 546 547 /* Repository location fields */ 548 repos_id INTEGER NOT NULL REFERENCES REPOSITORY (id), 549 550 /* Either MAP_NORMAL or MAP_EXCLUDED */ 551 presence TEXT NOT NULL, 552 553 /* the kind of the external. */ 554 kind TEXT NOT NULL, 555 556 /* The local relpath of the directory NODE defining this external 557 (Defaults to the parent directory of the file external after upgrade) */ 558 def_local_relpath TEXT NOT NULL, 559 560 /* The url of the external as used in the definition */ 561 def_repos_relpath TEXT NOT NULL, 562 563 /* The operational (peg) and node revision if this is a revision fixed 564 external; otherwise NULL. (Usually these will both have the same value) */ 565 def_operational_revision TEXT, 566 def_revision TEXT, 567 568 PRIMARY KEY (wc_id, local_relpath) 569); 570 571CREATE UNIQUE INDEX I_EXTERNALS_DEFINED ON EXTERNALS (wc_id, 572 def_local_relpath, 573 local_relpath); 574 575/* ------------------------------------------------------------------------- */ 576/* This statement provides SQLite with the necessary information about our 577 indexes to make better decisions in the query planner. 578 579 For every interesting index this contains a number of rows where the 580 statistics ar calculated for and then for every column in the index the 581 average number of rows with the same value in all columns left of this 582 column including the column itself. 583 584 See http://www.sqlite.org/fileformat2.html#stat1tab for more details. 585 586 The important thing here is that this tells Sqlite that the wc_id column 587 of the NODES and ACTUAL_NODE table is usually a single value, so queries 588 should use more than one column for index usage. 589 590 The current hints describe NODES+ACTUAL_NODE as a working copy with 591 8000 nodes in 1 a single working copy(=wc_id), 10 nodes per directory 592 and an average of 2 op-depth layers per node. 593 594 The number of integers must be number of index columns + 1, which is 595 verified via the test_schema_statistics() test. 596 */ 597-- STMT_INSTALL_SCHEMA_STATISTICS 598ANALYZE sqlite_master; /* Creates empty sqlite_stat1 if necessary */ 599 600DELETE FROM sqlite_stat1 601WHERE tbl in ('NODES', 'ACTUAL_NODE', 'LOCK', 'WC_LOCK', 'EXTERNALS'); 602 603INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 604 ('NODES', 'sqlite_autoindex_NODES_1', '8000 8000 2 1'); 605INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 606 ('NODES', 'I_NODES_PARENT', '8000 8000 10 2 1'); 607/* Tell a lie: We ignore that 99.9% of all moved_to values are NULL */ 608INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 609 ('NODES', 'I_NODES_MOVED', '8000 8000 1 1'); 610 611INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 612 ('ACTUAL_NODE', 'sqlite_autoindex_ACTUAL_NODE_1', '8000 8000 1'); 613INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 614 ('ACTUAL_NODE', 'I_ACTUAL_PARENT', '8000 8000 10 1'); 615 616INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 617 ('LOCK', 'sqlite_autoindex_LOCK_1', '100 100 1'); 618 619INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 620 ('WC_LOCK', 'sqlite_autoindex_WC_LOCK_1', '100 100 1'); 621 622INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 623 ('EXTERNALS','sqlite_autoindex_EXTERNALS_1', '100 100 1'); 624INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 625 ('EXTERNALS','I_EXTERNALS_DEFINED', '100 100 3 1'); 626 627/* sqlite_autoindex_WORK_QUEUE_1 doesn't exist because WORK_QUEUE is 628 a INTEGER PRIMARY KEY AUTOINCREMENT table */ 629 630ANALYZE sqlite_master; /* Loads sqlite_stat1 data for query optimizer */ 631/* ------------------------------------------------------------------------- */ 632 633/* Format 20 introduces NODES and removes BASE_NODE and WORKING_NODE */ 634 635-- STMT_UPGRADE_TO_20 636 637UPDATE BASE_NODE SET checksum = (SELECT checksum FROM pristine 638 WHERE md5_checksum = BASE_NODE.checksum) 639WHERE EXISTS (SELECT 1 FROM pristine WHERE md5_checksum = BASE_NODE.checksum); 640 641UPDATE WORKING_NODE SET checksum = (SELECT checksum FROM pristine 642 WHERE md5_checksum = WORKING_NODE.checksum) 643WHERE EXISTS (SELECT 1 FROM pristine 644 WHERE md5_checksum = WORKING_NODE.checksum); 645 646INSERT INTO NODES ( 647 wc_id, local_relpath, op_depth, parent_relpath, 648 repos_id, repos_path, revision, 649 presence, depth, moved_here, moved_to, kind, 650 changed_revision, changed_date, changed_author, 651 checksum, properties, translated_size, last_mod_time, 652 dav_cache, symlink_target, file_external ) 653SELECT wc_id, local_relpath, 0 /*op_depth*/, parent_relpath, 654 repos_id, repos_relpath, revnum, 655 presence, depth, NULL /*moved_here*/, NULL /*moved_to*/, kind, 656 changed_rev, changed_date, changed_author, 657 checksum, properties, translated_size, last_mod_time, 658 dav_cache, symlink_target, file_external 659FROM BASE_NODE; 660INSERT INTO NODES ( 661 wc_id, local_relpath, op_depth, parent_relpath, 662 repos_id, repos_path, revision, 663 presence, depth, moved_here, moved_to, kind, 664 changed_revision, changed_date, changed_author, 665 checksum, properties, translated_size, last_mod_time, 666 dav_cache, symlink_target, file_external ) 667SELECT wc_id, local_relpath, 2 /*op_depth*/, parent_relpath, 668 copyfrom_repos_id, copyfrom_repos_path, copyfrom_revnum, 669 presence, depth, NULL /*moved_here*/, NULL /*moved_to*/, kind, 670 changed_rev, changed_date, changed_author, 671 checksum, properties, translated_size, last_mod_time, 672 NULL /*dav_cache*/, symlink_target, NULL /*file_external*/ 673FROM WORKING_NODE; 674 675DROP TABLE BASE_NODE; 676DROP TABLE WORKING_NODE; 677 678PRAGMA user_version = 20; 679 680 681/* ------------------------------------------------------------------------- */ 682 683/* Format 21 involves no schema changes, it moves the tree conflict victim 684 information to victime nodes, rather than parents. */ 685 686-- STMT_UPGRADE_TO_21 687PRAGMA user_version = 21; 688 689/* For format 21 bump code */ 690-- STMT_UPGRADE_21_SELECT_OLD_TREE_CONFLICT 691SELECT wc_id, local_relpath, tree_conflict_data 692FROM actual_node 693WHERE tree_conflict_data IS NOT NULL 694 695/* For format 21 bump code */ 696-- STMT_UPGRADE_21_ERASE_OLD_CONFLICTS 697UPDATE actual_node SET tree_conflict_data = NULL 698 699/* ------------------------------------------------------------------------- */ 700 701/* Format 22 simply moves the tree conflict information from the conflict_data 702 column to the tree_conflict_data column. */ 703 704-- STMT_UPGRADE_TO_22 705UPDATE actual_node SET tree_conflict_data = conflict_data; 706UPDATE actual_node SET conflict_data = NULL; 707 708PRAGMA user_version = 22; 709 710 711/* ------------------------------------------------------------------------- */ 712 713/* Format 23 involves no schema changes, it introduces multi-layer 714 op-depth processing for NODES. */ 715 716-- STMT_UPGRADE_TO_23 717PRAGMA user_version = 23; 718 719-- STMT_UPGRADE_23_HAS_WORKING_NODES 720SELECT 1 FROM nodes WHERE op_depth > 0 721LIMIT 1 722 723/* ------------------------------------------------------------------------- */ 724 725/* Format 24 involves no schema changes; it starts using the pristine 726 table's refcount column correctly. */ 727 728-- STMT_UPGRADE_TO_24 729UPDATE pristine SET refcount = 730 (SELECT COUNT(*) FROM nodes 731 WHERE checksum = pristine.checksum /*OR checksum = pristine.md5_checksum*/); 732 733PRAGMA user_version = 24; 734 735/* ------------------------------------------------------------------------- */ 736 737/* Format 25 introduces the NODES_CURRENT view. */ 738 739-- STMT_UPGRADE_TO_25 740DROP VIEW IF EXISTS NODES_CURRENT; 741CREATE VIEW NODES_CURRENT AS 742 SELECT * FROM nodes 743 JOIN (SELECT wc_id, local_relpath, MAX(op_depth) AS op_depth FROM nodes 744 GROUP BY wc_id, local_relpath) AS filter 745 ON nodes.wc_id = filter.wc_id 746 AND nodes.local_relpath = filter.local_relpath 747 AND nodes.op_depth = filter.op_depth; 748 749PRAGMA user_version = 25; 750 751/* ------------------------------------------------------------------------- */ 752 753/* Format 26 introduces the NODES_BASE view. */ 754 755-- STMT_UPGRADE_TO_26 756DROP VIEW IF EXISTS NODES_BASE; 757CREATE VIEW NODES_BASE AS 758 SELECT * FROM nodes 759 WHERE op_depth = 0; 760 761PRAGMA user_version = 26; 762 763/* ------------------------------------------------------------------------- */ 764 765/* Format 27 involves no schema changes, it introduces stores 766 conflict files as relpaths rather than names in ACTUAL_NODE. */ 767 768-- STMT_UPGRADE_TO_27 769PRAGMA user_version = 27; 770 771/* For format 27 bump code */ 772-- STMT_UPGRADE_27_HAS_ACTUAL_NODES_CONFLICTS 773SELECT 1 FROM actual_node 774WHERE NOT ((prop_reject IS NULL) AND (conflict_old IS NULL) 775 AND (conflict_new IS NULL) AND (conflict_working IS NULL) 776 AND (tree_conflict_data IS NULL)) 777LIMIT 1 778 779 780/* ------------------------------------------------------------------------- */ 781 782/* Format 28 involves no schema changes, it only converts MD5 pristine 783 references to SHA1. */ 784 785-- STMT_UPGRADE_TO_28 786 787UPDATE NODES SET checksum = (SELECT checksum FROM pristine 788 WHERE md5_checksum = nodes.checksum) 789WHERE EXISTS (SELECT 1 FROM pristine WHERE md5_checksum = nodes.checksum); 790 791PRAGMA user_version = 28; 792 793/* ------------------------------------------------------------------------- */ 794 795/* Format 29 introduces the EXTERNALS table (See STMT_CREATE_TRIGGERS) and 796 optimizes a few trigger definitions. ... */ 797 798-- STMT_UPGRADE_TO_29 799 800DROP TRIGGER IF EXISTS nodes_update_checksum_trigger; 801DROP TRIGGER IF EXISTS nodes_insert_trigger; 802DROP TRIGGER IF EXISTS nodes_delete_trigger; 803 804CREATE TRIGGER nodes_update_checksum_trigger 805AFTER UPDATE OF checksum ON nodes 806WHEN NEW.checksum IS NOT OLD.checksum 807 /* AND (NEW.checksum IS NOT NULL OR OLD.checksum IS NOT NULL) */ 808BEGIN 809 UPDATE pristine SET refcount = refcount + 1 810 WHERE checksum = NEW.checksum; 811 UPDATE pristine SET refcount = refcount - 1 812 WHERE checksum = OLD.checksum; 813END; 814 815CREATE TRIGGER nodes_insert_trigger 816AFTER INSERT ON nodes 817WHEN NEW.checksum IS NOT NULL 818BEGIN 819 UPDATE pristine SET refcount = refcount + 1 820 WHERE checksum = NEW.checksum; 821END; 822 823CREATE TRIGGER nodes_delete_trigger 824AFTER DELETE ON nodes 825WHEN OLD.checksum IS NOT NULL 826BEGIN 827 UPDATE pristine SET refcount = refcount - 1 828 WHERE checksum = OLD.checksum; 829END; 830 831PRAGMA user_version = 29; 832 833/* ------------------------------------------------------------------------- */ 834 835/* Format 30 creates a new NODES index for move information, and a new 836 PRISTINE index for the md5_checksum column. It also activates use of 837 skel-based conflict storage -- see notes/wc-ng/conflict-storage-2.0. 838 It also renames the "absent" presence to "server-excluded". */ 839-- STMT_UPGRADE_TO_30 840CREATE UNIQUE INDEX IF NOT EXISTS I_NODES_MOVED 841ON NODES (wc_id, moved_to, op_depth); 842 843CREATE INDEX IF NOT EXISTS I_PRISTINE_MD5 ON PRISTINE (md5_checksum); 844 845UPDATE nodes SET presence = "server-excluded" WHERE presence = "absent"; 846 847/* Just to be sure clear out file external skels from pre 1.7.0 development 848 working copies that were never updated by 1.7.0+ style clients */ 849UPDATE nodes SET file_external=1 WHERE file_external IS NOT NULL; 850 851-- STMT_UPGRADE_30_SELECT_CONFLICT_SEPARATE 852SELECT wc_id, local_relpath, 853 conflict_old, conflict_working, conflict_new, prop_reject, tree_conflict_data 854FROM actual_node 855WHERE conflict_old IS NOT NULL 856 OR conflict_working IS NOT NULL 857 OR conflict_new IS NOT NULL 858 OR prop_reject IS NOT NULL 859 OR tree_conflict_data IS NOT NULL 860ORDER by wc_id, local_relpath 861 862-- STMT_UPGRADE_30_SET_CONFLICT 863UPDATE actual_node SET conflict_data = ?3, conflict_old = NULL, 864 conflict_working = NULL, conflict_new = NULL, prop_reject = NULL, 865 tree_conflict_data = NULL 866WHERE wc_id = ?1 and local_relpath = ?2 867 868/* ------------------------------------------------------------------------- */ 869 870/* Format 31 adds the inherited_props column to the NODES table. C code then 871 initializes the update/switch roots to make sure future updates fetch the 872 inherited properties */ 873-- STMT_UPGRADE_TO_31_ALTER_TABLE 874ALTER TABLE NODES ADD COLUMN inherited_props BLOB; 875-- STMT_UPGRADE_TO_31_FINALIZE 876DROP INDEX IF EXISTS I_ACTUAL_CHANGELIST; 877DROP INDEX IF EXISTS I_EXTERNALS_PARENT; 878 879DROP INDEX I_NODES_PARENT; 880CREATE UNIQUE INDEX I_NODES_PARENT ON NODES (wc_id, parent_relpath, 881 local_relpath, op_depth); 882 883DROP INDEX I_ACTUAL_PARENT; 884CREATE UNIQUE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, parent_relpath, 885 local_relpath); 886 887PRAGMA user_version = 31; 888 889-- STMT_UPGRADE_31_SELECT_WCROOT_NODES 890/* Select all base nodes which are the root of a WC, including 891 switched subtrees, but excluding those which map to the root 892 of the repos. 893 894 ### IPROPS: Is this query horribly inefficient? Quite likely, 895 ### but it only runs during an upgrade, so do we care? */ 896SELECT l.wc_id, l.local_relpath FROM nodes as l 897LEFT OUTER JOIN nodes as r 898ON l.wc_id = r.wc_id 899 AND r.local_relpath = l.parent_relpath 900 AND r.op_depth = 0 901WHERE l.op_depth = 0 902 AND l.repos_path != '' 903 AND ((l.repos_id IS NOT r.repos_id) 904 OR (l.repos_path IS NOT RELPATH_SKIP_JOIN(r.local_relpath, r.repos_path, l.local_relpath))) 905 906 907/* ------------------------------------------------------------------------- */ 908/* Format 32 .... */ 909-- STMT_UPGRADE_TO_32 910 911/* Drop old index. ### Remove this part from the upgrade to 31 once bumped */ 912DROP INDEX IF EXISTS I_ACTUAL_CHANGELIST; 913DROP INDEX IF EXISTS I_EXTERNALS_PARENT; 914CREATE INDEX I_EXTERNALS_PARENT ON EXTERNALS (wc_id, parent_relpath); 915 916DROP INDEX I_NODES_PARENT; 917CREATE UNIQUE INDEX I_NODES_PARENT ON NODES (wc_id, parent_relpath, 918 local_relpath, op_depth); 919 920DROP INDEX I_ACTUAL_PARENT; 921CREATE UNIQUE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, parent_relpath, 922 local_relpath); 923 924/* ------------------------------------------------------------------------- */ 925 926/* Format YYY introduces new handling for conflict information. */ 927-- format: YYY 928 929 930/* ------------------------------------------------------------------------- */ 931 932/* Format 99 drops all columns not needed due to previous format upgrades. 933 Before we release 1.7, these statements will be pulled into a format bump 934 and all the tables will be cleaned up. We don't know what that format 935 number will be, however, so we're just marking it as 99 for now. */ 936-- format: 99 937 938/* TODO: Un-confuse *_revision column names in the EXTERNALS table to 939 "-r<operative> foo@<peg>", as suggested by the patch attached to 940 http://svn.haxx.se/dev/archive-2011-09/0478.shtml */ 941/* TODO: Remove column parent_relpath from EXTERNALS. We're not using it and 942 never will. It's not interesting like in the NODES table: the external's 943 parent path may be *anything*: unversioned, "behind" a another WC... */ 944 945/* Now "drop" the tree_conflict_data column from actual_node. */ 946CREATE TABLE ACTUAL_NODE_BACKUP ( 947 wc_id INTEGER NOT NULL, 948 local_relpath TEXT NOT NULL, 949 parent_relpath TEXT, 950 properties BLOB, 951 conflict_old TEXT, 952 conflict_new TEXT, 953 conflict_working TEXT, 954 prop_reject TEXT, 955 changelist TEXT, 956 text_mod TEXT 957 ); 958 959INSERT INTO ACTUAL_NODE_BACKUP SELECT 960 wc_id, local_relpath, parent_relpath, properties, conflict_old, 961 conflict_new, conflict_working, prop_reject, changelist, text_mod 962FROM ACTUAL_NODE; 963 964DROP TABLE ACTUAL_NODE; 965 966CREATE TABLE ACTUAL_NODE ( 967 wc_id INTEGER NOT NULL REFERENCES WCROOT (id), 968 local_relpath TEXT NOT NULL, 969 parent_relpath TEXT, 970 properties BLOB, 971 conflict_old TEXT, 972 conflict_new TEXT, 973 conflict_working TEXT, 974 prop_reject TEXT, 975 changelist TEXT, 976 text_mod TEXT, 977 978 PRIMARY KEY (wc_id, local_relpath) 979 ); 980 981CREATE UNIQUE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, parent_relpath, 982 local_relpath); 983 984INSERT INTO ACTUAL_NODE SELECT 985 wc_id, local_relpath, parent_relpath, properties, conflict_old, 986 conflict_new, conflict_working, prop_reject, changelist, text_mod 987FROM ACTUAL_NODE_BACKUP; 988 989DROP TABLE ACTUAL_NODE_BACKUP; 990 991/* Note: Other differences between the schemas of an upgraded and a 992 * fresh WC. 993 * 994 * While format 22 was current, "NOT NULL" was added to the 995 * columns PRISTINE.size and PRISTINE.md5_checksum. The format was not 996 * bumped because it is a forward- and backward-compatible change. 997 * 998 * While format 23 was current, "REFERENCES PRISTINE" was added to the 999 * columns ACTUAL_NODE.older_checksum, ACTUAL_NODE.left_checksum, 1000 * ACTUAL_NODE.right_checksum, NODES.checksum. 1001 * 1002 * The "NODES_BASE" view was originally implemented with a more complex (but 1003 * functionally equivalent) statement using a 'JOIN'. WCs that were created 1004 * at or upgraded to format 26 before it was changed will still have the old 1005 * version. 1006 */ 1007 1008