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 232/* ------------------------------------------------------------------------- */ 233 234/* The NODES table describes the way WORKING nodes are layered on top of 235 BASE nodes and on top of other WORKING nodes, due to nested tree structure 236 changes. The layers are modelled using the "op_depth" column. 237 238 An 'operation depth' refers to the number of directory levels down from 239 the WC root at which a tree-change operation (delete, add?, copy, move) 240 was performed. A row's 'op_depth' does NOT refer to the depth of its own 241 'local_relpath', but rather to the depth of the nearest tree change that 242 affects that node. 243 244 The row with op_depth=0 for any given local relpath represents the "base" 245 node that is created and updated by checkout, update, switch and commit 246 post-processing. The row with the highest op_depth for a particular 247 local_relpath represents the working version. Any rows with intermediate 248 op_depth values are not normally visible to the user but may become 249 visible after reverting local changes. 250 251 This table contains full node descriptions for nodes in either the BASE 252 or WORKING trees as described in notes/wc-ng/design. Fields relate 253 both to BASE and WORKING trees, unless documented otherwise. 254 255 For illustration, with a scenario like this: 256 257 # (0) 258 svn rm foo 259 svn cp ^/moo foo # (1) 260 svn rm foo/bar 261 touch foo/bar 262 svn add foo/bar # (2) 263 264 , these are the NODES table rows for the path foo/bar: 265 266 (0) "BASE" ---> NODES (op_depth == 0) 267 (1) NODES (op_depth == 1) 268 (2) NODES (op_depth == 2) 269 270 0 is the original data for foo/bar before 'svn rm foo' (if it existed). 271 1 is the data for foo/bar copied in from ^/moo/bar. 272 2 is the to-be-committed data for foo/bar, created by 'svn add foo/bar'. 273 274 An 'svn revert foo/bar' would remove the NODES of (2). 275 276 */ 277CREATE TABLE NODES ( 278 /* Working copy location related fields */ 279 280 wc_id INTEGER NOT NULL REFERENCES WCROOT (id), 281 local_relpath TEXT NOT NULL, 282 283 /* Contains the depth (= number of path segments) of the operation 284 modifying the working copy tree structure. All nodes below the root 285 of the operation (aka operation root, aka oproot) affected by the 286 operation will be assigned the same op_depth. 287 288 op_depth == 0 designates the initial checkout; the BASE tree. 289 290 */ 291 op_depth INTEGER NOT NULL, 292 293 /* parent's local_relpath for aggregating children of a given parent. 294 this will be "" if the parent is the wcroot. Since a wcroot will 295 never have a WORKING node the parent_relpath will never be null, 296 except when op_depth == 0 and the node is a wcroot. */ 297 parent_relpath TEXT, 298 299 300 /* Repository location fields */ 301 302 /* When op_depth == 0, these fields refer to the repository location of the 303 BASE node, the location of the initial checkout. 304 305 When op_depth != 0, they indicate where this node was copied/moved from. 306 In this case, the fields are set for the root of the operation and for all 307 children. */ 308 repos_id INTEGER REFERENCES REPOSITORY (id), 309 repos_path TEXT, 310 revision INTEGER, 311 312 313 /* WC state fields */ 314 315 /* The tree state of the node. 316 317 In case 'op_depth' is equal to 0, this node is part of the 'BASE' 318 tree. The 'BASE' represents pristine nodes that are in the 319 repository; it is obtained and modified by commands such as 320 checkout/update/switch. 321 322 In case 'op_depth' is greater than 0, this node is part of a 323 layer of working nodes. The 'WORKING' tree is obtained and 324 modified by commands like delete/copy/revert. 325 326 The 'BASE' and 'WORKING' trees use the same literal values for 327 the 'presence' but the meaning of each value can vary depending 328 on the tree. 329 330 normal: in the 'BASE' tree this is an ordinary node for which we 331 have full information. In the 'WORKING' tree it's an added or 332 copied node for which we have full information. 333 334 not-present: in the 'BASE' tree this is a node that is implied to 335 exist by the parent node, but is not present in the working 336 copy. Typically obtained by delete/commit, or by update to 337 revision in which the node does not exist. In the 'WORKING' 338 tree this is a copy of a 'not-present' node from the 'BASE' 339 tree, and it will be deleted on commit. Such a node cannot be 340 copied directly, but can be copied as a descendant. 341 342 incomplete: in the 'BASE' tree this is an ordinary node for which 343 we do not have full information. Only the name is guaranteed; 344 we may not have all its children, we may not have its checksum, 345 etc. In the 'WORKING' tree this is a copied node for which we 346 do not have the full information. This state is generally 347 obtained when an operation was interrupted. 348 349 base-deleted: not valid in 'BASE' tree. In the 'WORKING' tree 350 this represents a node that is deleted from the tree below the 351 current 'op_depth'. This state is badly named, it should be 352 something like 'deleted'. 353 354 server-excluded: in the 'BASE' tree this is a node that is excluded by 355 authz. The name of the node is known from the parent, but no 356 other information is available. Not valid in the 'WORKING' 357 tree as there is no way to commit such a node. 358 359 excluded: in the 'BASE' tree this node is administratively 360 excluded by the user (sparse WC). In the 'WORKING' tree this 361 is a copy of an excluded node from the 'BASE' tree. Such a 362 node cannot be copied directly but can be copied as a 363 descendant. */ 364 365 presence TEXT NOT NULL, 366 367 /* ### JF: For an old-style move, "copyfrom" info stores its source, but a 368 new WC-NG "move" is intended to be a "true rename" so its copyfrom 369 revision is implicit, being in effect (new head - 1) at commit time. 370 For a (new) move, we need to store or deduce the copyfrom local-relpath; 371 perhaps add a column called "moved_from". */ 372 373 /* Boolean value, specifying if this node was moved here (rather than just 374 copied). This is set on all the nodes in the moved tree. The source of 375 the move is implied by a different node with a moved_to column pointing 376 at the root node of the moved tree. */ 377 moved_here INTEGER, 378 379 /* If the underlying node was moved away (rather than just deleted), this 380 specifies the local_relpath of where the node was moved to. 381 This is set only on the root of a move, and is NULL for all children. 382 383 The op-depth of the moved-to node is not recorded. A moved_to path 384 always points at a node within the highest op-depth layer at the 385 destination. This invariant must be maintained by operations which 386 change existing move information. */ 387 moved_to TEXT, 388 389 390 /* Content fields */ 391 392 /* the kind of the new node. may be "unknown" if the node is not present. */ 393 kind TEXT NOT NULL, 394 395 /* serialized skel of this node's properties (when presence is 'normal' or 396 'incomplete'); an empty skel or NULL indicates no properties. NULL if 397 we have no information about the properties (any other presence). 398 TODO: Choose & require a single representation for 'no properties'. 399 */ 400 properties BLOB, 401 402 /* NULL depth means "default" (typically svn_depth_infinity) */ 403 /* ### depth on WORKING? seems this is a BASE-only concept. how do 404 ### you do "files" on an added-directory? can't really ignore 405 ### the subdirs! */ 406 /* ### maybe a WC-to-WC copy can retain a depth? */ 407 depth TEXT, 408 409 /* The SHA-1 checksum of the pristine text, if this node is a file and was 410 moved here or copied here, else NULL. */ 411 checksum TEXT REFERENCES PRISTINE (checksum), 412 413 /* for kind==symlink, this specifies the target. */ 414 symlink_target TEXT, 415 416 417 /* Last-Change fields */ 418 419 /* If this node was moved here or copied here, then the following fields may 420 have information about their source node. changed_rev must be not-null 421 if this node has presence=="normal". changed_date and changed_author may 422 be null if the corresponding revprops are missing. 423 424 For an added or not-present node, these are null. */ 425 changed_revision INTEGER, 426 changed_date INTEGER, /* an APR date/time (usec since 1970) */ 427 changed_author TEXT, 428 429 430 /* Various cache fields */ 431 432 /* The size in bytes of the working file when it had no local text 433 modifications. This means the size of the text when translated from 434 repository-normal format to working copy format with EOL style 435 translated and keywords expanded according to the properties in the 436 "properties" column of this row. 437 438 NULL if this node is not a file or if the size has not (yet) been 439 computed. */ 440 translated_size INTEGER, 441 442 /* The mod-time of the working file when it was last determined to be 443 logically unmodified relative to its base, taking account of keywords 444 and EOL style. This value is used in the change detection heuristic 445 used by the status command. 446 447 NULL if this node is not a file or if this info has not yet been 448 determined. 449 */ 450 last_mod_time INTEGER, /* an APR date/time (usec since 1970) */ 451 452 /* serialized skel of this node's dav-cache. could be NULL if the 453 node does not have any dav-cache. */ 454 dav_cache BLOB, 455 456 /* Is there a file external in this location. NULL if there 457 is no file external, otherwise '1' */ 458 /* ### Originally we had a wc-1.0 like skel in this place, so we 459 ### check for NULL. 460 ### In Subversion 1.7 we defined this column as TEXT, but Sqlite 461 ### only uses this information for deciding how to optimize 462 ### anyway. */ 463 file_external INTEGER, 464 465 /* serialized skel of this node's inherited properties. NULL if this 466 is not the BASE of a WC root node. */ 467 inherited_props BLOB, 468 469 PRIMARY KEY (wc_id, local_relpath, op_depth) 470 471 ); 472 473CREATE UNIQUE INDEX I_NODES_PARENT ON NODES (wc_id, parent_relpath, 474 local_relpath, op_depth); 475/* I_NODES_MOVED is introduced in format 30 */ 476CREATE UNIQUE INDEX I_NODES_MOVED ON NODES (wc_id, moved_to, op_depth); 477 478/* Many queries have to filter the nodes table to pick only that version 479 of each node with the highest (most "current") op_depth. This view 480 does the heavy lifting for such queries. 481 482 Note that this view includes a row for each and every path that is known 483 in the WC, including, for example, paths that were children of a base- or 484 lower-op-depth directory that has been replaced by something else in the 485 current view. 486 */ 487CREATE VIEW NODES_CURRENT AS 488 SELECT * FROM nodes AS n 489 WHERE op_depth = (SELECT MAX(op_depth) FROM nodes AS n2 490 WHERE n2.wc_id = n.wc_id 491 AND n2.local_relpath = n.local_relpath); 492 493/* Many queries have to filter the nodes table to pick only that version 494 of each node with the BASE ("as checked out") op_depth. This view 495 does the heavy lifting for such queries. */ 496CREATE VIEW NODES_BASE AS 497 SELECT * FROM nodes 498 WHERE op_depth = 0; 499 500CREATE TRIGGER nodes_insert_trigger 501AFTER INSERT ON nodes 502WHEN NEW.checksum IS NOT NULL 503BEGIN 504 UPDATE pristine SET refcount = refcount + 1 505 WHERE checksum = NEW.checksum; 506END; 507 508CREATE TRIGGER nodes_delete_trigger 509AFTER DELETE ON nodes 510WHEN OLD.checksum IS NOT NULL 511BEGIN 512 UPDATE pristine SET refcount = refcount - 1 513 WHERE checksum = OLD.checksum; 514END; 515 516CREATE TRIGGER nodes_update_checksum_trigger 517AFTER UPDATE OF checksum ON nodes 518WHEN NEW.checksum IS NOT OLD.checksum 519 /* AND (NEW.checksum IS NOT NULL OR OLD.checksum IS NOT NULL) */ 520BEGIN 521 UPDATE pristine SET refcount = refcount + 1 522 WHERE checksum = NEW.checksum; 523 UPDATE pristine SET refcount = refcount - 1 524 WHERE checksum = OLD.checksum; 525END; 526 527CREATE TABLE EXTERNALS ( 528 /* Working copy location related fields (like NODES)*/ 529 530 wc_id INTEGER NOT NULL REFERENCES WCROOT (id), 531 local_relpath TEXT NOT NULL, 532 533 /* The working copy root can't be recorded as an external in itself 534 so this will never be NULL. ### ATM only inserted, never queried */ 535 parent_relpath TEXT NOT NULL, 536 537 /* Repository location fields */ 538 repos_id INTEGER NOT NULL REFERENCES REPOSITORY (id), 539 540 /* Either MAP_NORMAL or MAP_EXCLUDED */ 541 presence TEXT NOT NULL, 542 543 /* the kind of the external. */ 544 kind TEXT NOT NULL, 545 546 /* The local relpath of the directory NODE defining this external 547 (Defaults to the parent directory of the file external after upgrade) */ 548 def_local_relpath TEXT NOT NULL, 549 550 /* The url of the external as used in the definition */ 551 def_repos_relpath TEXT NOT NULL, 552 553 /* The operational (peg) and node revision if this is a revision fixed 554 external; otherwise NULL. (Usually these will both have the same value) */ 555 def_operational_revision TEXT, 556 def_revision TEXT, 557 558 PRIMARY KEY (wc_id, local_relpath) 559); 560 561CREATE UNIQUE INDEX I_EXTERNALS_DEFINED ON EXTERNALS (wc_id, 562 def_local_relpath, 563 local_relpath); 564 565 566PRAGMA user_version = 567-- define: SVN_WC__VERSION 568; 569 570 571/* ------------------------------------------------------------------------- */ 572/* This statement provides SQLite with the necessary information about our 573 indexes to make better decisions in the query planner. 574 575 For every interesting index this contains a number of rows where the 576 statistics are calculated for and then for every column in the index the 577 average number of rows with the same value in all columns left of this 578 column including the column itself. 579 580 See http://www.sqlite.org/fileformat2.html#stat1tab for more details. 581 582 The important thing here is that this tells Sqlite that the wc_id column 583 of the NODES and ACTUAL_NODE table is usually a single value, so queries 584 should use more than one column for index usage. 585 586 The current hints describe NODES+ACTUAL_NODE as a working copy with 587 8000 nodes in 1 a single working copy(=wc_id), 10 nodes per directory 588 and an average of 2 op-depth layers per node. 589 590 The number of integers must be number of index columns + 1, which is 591 verified via the test_schema_statistics() test. 592 */ 593-- STMT_INSTALL_SCHEMA_STATISTICS 594ANALYZE sqlite_master; /* Creates empty sqlite_stat1 if necessary */ 595 596DELETE FROM sqlite_stat1 597WHERE tbl in ('NODES', 'ACTUAL_NODE', 'LOCK', 'WC_LOCK', 'EXTERNALS'); 598 599INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 600 ('NODES', 'sqlite_autoindex_NODES_1', '8000 8000 2 1'); 601INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 602 ('NODES', 'I_NODES_PARENT', '8000 8000 10 2 1'); 603/* Tell a lie: We ignore that 99.9% of all moved_to values are NULL */ 604INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 605 ('NODES', 'I_NODES_MOVED', '8000 8000 1 1'); 606 607INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 608 ('ACTUAL_NODE', 'sqlite_autoindex_ACTUAL_NODE_1', '8000 8000 1'); 609INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 610 ('ACTUAL_NODE', 'I_ACTUAL_PARENT', '8000 8000 10 1'); 611 612INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 613 ('LOCK', 'sqlite_autoindex_LOCK_1', '100 100 1'); 614 615INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 616 ('WC_LOCK', 'sqlite_autoindex_WC_LOCK_1', '100 100 1'); 617 618INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 619 ('EXTERNALS','sqlite_autoindex_EXTERNALS_1', '100 100 1'); 620INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 621 ('EXTERNALS','I_EXTERNALS_DEFINED', '100 100 3 1'); 622 623/* sqlite_autoindex_WORK_QUEUE_1 doesn't exist because WORK_QUEUE is 624 a INTEGER PRIMARY KEY AUTOINCREMENT table */ 625 626ANALYZE sqlite_master; /* Loads sqlite_stat1 data for query optimizer */ 627/* ------------------------------------------------------------------------- */ 628 629/* Format 30 creates a new NODES index for move information, and a new 630 PRISTINE index for the md5_checksum column. It also activates use of 631 skel-based conflict storage -- see notes/wc-ng/conflict-storage-2.0. 632 It also renames the "absent" presence to "server-excluded". */ 633-- STMT_UPGRADE_TO_30 634CREATE UNIQUE INDEX IF NOT EXISTS I_NODES_MOVED 635ON NODES (wc_id, moved_to, op_depth); 636 637CREATE INDEX IF NOT EXISTS I_PRISTINE_MD5 ON PRISTINE (md5_checksum); 638 639UPDATE nodes SET presence = 'server-excluded' WHERE presence = 'absent'; 640 641/* Just to be sure clear out file external skels from pre 1.7.0 development 642 working copies that were never updated by 1.7.0+ style clients */ 643UPDATE nodes SET file_external=1 WHERE file_external IS NOT NULL; 644 645-- STMT_UPGRADE_30_SELECT_CONFLICT_SEPARATE 646SELECT wc_id, local_relpath, 647 conflict_old, conflict_working, conflict_new, prop_reject, tree_conflict_data 648FROM actual_node 649WHERE conflict_old IS NOT NULL 650 OR conflict_working IS NOT NULL 651 OR conflict_new IS NOT NULL 652 OR prop_reject IS NOT NULL 653 OR tree_conflict_data IS NOT NULL 654ORDER by wc_id, local_relpath 655 656-- STMT_UPGRADE_30_SET_CONFLICT 657UPDATE actual_node SET conflict_data = ?3, conflict_old = NULL, 658 conflict_working = NULL, conflict_new = NULL, prop_reject = NULL, 659 tree_conflict_data = NULL 660WHERE wc_id = ?1 and local_relpath = ?2 661 662/* ------------------------------------------------------------------------- */ 663 664/* Format 31 adds the inherited_props column to the NODES table. C code then 665 initializes the update/switch roots to make sure future updates fetch the 666 inherited properties */ 667-- STMT_UPGRADE_TO_31 668ALTER TABLE NODES ADD COLUMN inherited_props BLOB; 669 670DROP INDEX IF EXISTS I_ACTUAL_CHANGELIST; 671DROP INDEX IF EXISTS I_EXTERNALS_PARENT; 672 673DROP INDEX I_NODES_PARENT; 674CREATE UNIQUE INDEX I_NODES_PARENT ON NODES (wc_id, parent_relpath, 675 local_relpath, op_depth); 676 677DROP INDEX I_ACTUAL_PARENT; 678CREATE UNIQUE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, parent_relpath, 679 local_relpath); 680 681PRAGMA user_version = 31; 682 683-- STMT_UPGRADE_31_SELECT_WCROOT_NODES 684/* Select all base nodes which are the root of a WC, including 685 switched subtrees, but excluding those which map to the root 686 of the repos. 687 688 ### IPROPS: Is this query horribly inefficient? Quite likely, 689 ### but it only runs during an upgrade, so do we care? */ 690SELECT l.wc_id, l.local_relpath FROM nodes as l 691LEFT OUTER JOIN nodes as r 692ON l.wc_id = r.wc_id 693 AND r.local_relpath = l.parent_relpath 694 AND r.op_depth = 0 695WHERE l.op_depth = 0 696 AND l.repos_path != '' 697 AND ((l.repos_id IS NOT r.repos_id) 698 OR (l.repos_path IS NOT RELPATH_SKIP_JOIN(r.local_relpath, r.repos_path, l.local_relpath))) 699 700 701/* ------------------------------------------------------------------------- */ 702/* Format 32 .... */ 703/* -- STMT_UPGRADE_TO_32 704PRAGMA user_version = 32; */ 705 706 707/* ------------------------------------------------------------------------- */ 708 709/* Format 99 drops all columns not needed due to previous format upgrades. 710 Before we release 1.7, these statements will be pulled into a format bump 711 and all the tables will be cleaned up. We don't know what that format 712 number will be, however, so we're just marking it as 99 for now. */ 713-- format: 99 714 715/* TODO: Un-confuse *_revision column names in the EXTERNALS table to 716 "-r<operative> foo@<peg>", as suggested by the patch attached to 717 http://svn.haxx.se/dev/archive-2011-09/0478.shtml */ 718/* TODO: Remove column parent_relpath from EXTERNALS. We're not using it and 719 never will. It's not interesting like in the NODES table: the external's 720 parent path may be *anything*: unversioned, "behind" a another WC... */ 721 722/* Now "drop" the tree_conflict_data column from actual_node. */ 723CREATE TABLE ACTUAL_NODE_BACKUP ( 724 wc_id INTEGER NOT NULL, 725 local_relpath TEXT NOT NULL, 726 parent_relpath TEXT, 727 properties BLOB, 728 conflict_old TEXT, 729 conflict_new TEXT, 730 conflict_working TEXT, 731 prop_reject TEXT, 732 changelist TEXT, 733 text_mod TEXT 734 ); 735 736INSERT INTO ACTUAL_NODE_BACKUP SELECT 737 wc_id, local_relpath, parent_relpath, properties, conflict_old, 738 conflict_new, conflict_working, prop_reject, changelist, text_mod 739FROM ACTUAL_NODE; 740 741DROP TABLE ACTUAL_NODE; 742 743CREATE TABLE ACTUAL_NODE ( 744 wc_id INTEGER NOT NULL REFERENCES WCROOT (id), 745 local_relpath TEXT NOT NULL, 746 parent_relpath TEXT, 747 properties BLOB, 748 conflict_old TEXT, 749 conflict_new TEXT, 750 conflict_working TEXT, 751 prop_reject TEXT, 752 changelist TEXT, 753 text_mod TEXT, 754 755 PRIMARY KEY (wc_id, local_relpath) 756 ); 757 758CREATE UNIQUE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, parent_relpath, 759 local_relpath); 760 761INSERT INTO ACTUAL_NODE SELECT 762 wc_id, local_relpath, parent_relpath, properties, conflict_old, 763 conflict_new, conflict_working, prop_reject, changelist, text_mod 764FROM ACTUAL_NODE_BACKUP; 765 766DROP TABLE ACTUAL_NODE_BACKUP; 767 768/* Note: Other differences between the schemas of an upgraded and a 769 * fresh WC. 770 * 771 * While format 22 was current, "NOT NULL" was added to the 772 * columns PRISTINE.size and PRISTINE.md5_checksum. The format was not 773 * bumped because it is a forward- and backward-compatible change. 774 * 775 * While format 23 was current, "REFERENCES PRISTINE" was added to the 776 * columns ACTUAL_NODE.older_checksum, ACTUAL_NODE.left_checksum, 777 * ACTUAL_NODE.right_checksum, NODES.checksum. 778 * 779 * The "NODES_BASE" view was originally implemented with a more complex (but 780 * functionally equivalent) statement using a 'JOIN'. WCs that were created 781 * at or upgraded to format 26 before it was changed will still have the old 782 * version. 783 */ 784 785