1251881Speter/* wc-metadata.sql -- schema used in the wc-metadata SQLite database 2251881Speter * This is intended for use with SQLite 3 3251881Speter * 4251881Speter * ==================================================================== 5251881Speter * Licensed to the Apache Software Foundation (ASF) under one 6251881Speter * or more contributor license agreements. See the NOTICE file 7251881Speter * distributed with this work for additional information 8251881Speter * regarding copyright ownership. The ASF licenses this file 9251881Speter * to you under the Apache License, Version 2.0 (the 10251881Speter * "License"); you may not use this file except in compliance 11251881Speter * with the License. You may obtain a copy of the License at 12251881Speter * 13251881Speter * http://www.apache.org/licenses/LICENSE-2.0 14251881Speter * 15251881Speter * Unless required by applicable law or agreed to in writing, 16251881Speter * software distributed under the License is distributed on an 17251881Speter * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 18251881Speter * KIND, either express or implied. See the License for the 19251881Speter * specific language governing permissions and limitations 20251881Speter * under the License. 21251881Speter * ==================================================================== 22251881Speter */ 23251881Speter 24251881Speter/* 25251881Speter * the KIND column in these tables has one of the following values 26251881Speter * (documented in the corresponding C type #svn_kind_t): 27251881Speter * "file" 28251881Speter * "dir" 29251881Speter * "symlink" 30251881Speter * "unknown" 31251881Speter * 32251881Speter * the PRESENCE column in these tables has one of the following values 33251881Speter * (see also the C type #svn_wc__db_status_t): 34251881Speter * "normal" 35251881Speter * "server-excluded" -- server has declared it excluded (ie. authz failure) 36251881Speter * "excluded" -- administratively excluded (ie. sparse WC) 37251881Speter * "not-present" -- node not present at this REV 38251881Speter * "incomplete" -- state hasn't been filled in 39251881Speter * "base-deleted" -- node represents a delete of a BASE node 40251881Speter */ 41251881Speter 42251881Speter/* One big list of statements to create our (current) schema. */ 43251881Speter-- STMT_CREATE_SCHEMA 44251881Speter 45251881Speter/* ------------------------------------------------------------------------- */ 46251881Speter 47251881SpeterCREATE TABLE REPOSITORY ( 48251881Speter id INTEGER PRIMARY KEY AUTOINCREMENT, 49251881Speter 50251881Speter /* The root URL of the repository. This value is URI-encoded. */ 51251881Speter root TEXT UNIQUE NOT NULL, 52251881Speter 53251881Speter /* the UUID of the repository */ 54251881Speter uuid TEXT NOT NULL 55251881Speter ); 56251881Speter 57251881Speter/* Note: a repository (identified by its UUID) may appear at multiple URLs. 58251881Speter For example, http://example.com/repos/ and https://example.com/repos/. */ 59251881SpeterCREATE INDEX I_UUID ON REPOSITORY (uuid); 60251881SpeterCREATE INDEX I_ROOT ON REPOSITORY (root); 61251881Speter 62251881Speter 63251881Speter/* ------------------------------------------------------------------------- */ 64251881Speter 65251881SpeterCREATE TABLE WCROOT ( 66251881Speter id INTEGER PRIMARY KEY AUTOINCREMENT, 67251881Speter 68251881Speter /* absolute path in the local filesystem. NULL if storing metadata in 69251881Speter the wcroot itself. */ 70251881Speter local_abspath TEXT UNIQUE 71251881Speter ); 72251881Speter 73251881SpeterCREATE UNIQUE INDEX I_LOCAL_ABSPATH ON WCROOT (local_abspath); 74251881Speter 75251881Speter 76251881Speter/* ------------------------------------------------------------------------- */ 77251881Speter 78251881Speter/* The PRISTINE table keeps track of pristine texts. Each row describes a 79251881Speter single pristine text. The text itself is stored in a file whose name is 80251881Speter derived from the 'checksum' column. Each pristine text is referenced by 81251881Speter any number of rows in the NODES and ACTUAL_NODE tables. 82251881Speter 83251881Speter In future, the pristine text file may be compressed. 84251881Speter */ 85251881SpeterCREATE TABLE PRISTINE ( 86251881Speter /* The SHA-1 checksum of the pristine text. This is a unique key. The 87251881Speter SHA-1 checksum of a pristine text is assumed to be unique among all 88251881Speter pristine texts referenced from this database. */ 89251881Speter checksum TEXT NOT NULL PRIMARY KEY, 90251881Speter 91251881Speter /* Enumerated values specifying type of compression. The only value 92251881Speter supported so far is NULL, meaning that no compression has been applied 93251881Speter and the pristine text is stored verbatim in the file. */ 94251881Speter compression INTEGER, 95251881Speter 96251881Speter /* The size in bytes of the file in which the pristine text is stored. 97251881Speter Used to verify the pristine file is "proper". */ 98251881Speter size INTEGER NOT NULL, 99251881Speter 100251881Speter /* The number of rows in the NODES table that have a 'checksum' column 101251881Speter value that refers to this row. (References in other places, such as 102251881Speter in the ACTUAL_NODE table, are not counted.) */ 103251881Speter refcount INTEGER NOT NULL, 104251881Speter 105251881Speter /* Alternative MD5 checksum used for communicating with older 106251881Speter repositories. Not strictly guaranteed to be unique among table rows. */ 107251881Speter md5_checksum TEXT NOT NULL 108251881Speter ); 109251881Speter 110251881SpeterCREATE INDEX I_PRISTINE_MD5 ON PRISTINE (md5_checksum); 111299742Sdim 112251881Speter/* ------------------------------------------------------------------------- */ 113251881Speter 114251881Speter/* The ACTUAL_NODE table describes text changes and property changes 115251881Speter on each node in the WC, relative to the NODES table row for the 116251881Speter same path. (A NODES row must exist if this node exists, but an 117251881Speter ACTUAL_NODE row can exist on its own if it is just recording info 118251881Speter on a non-present node - a tree conflict or a changelist, for 119251881Speter example.) 120251881Speter 121251881Speter The ACTUAL_NODE table row for a given path exists if the node at that 122251881Speter path is known to have text or property changes relative to its 123251881Speter NODES row. ("Is known" because a text change on disk may not yet 124251881Speter have been discovered and recorded here.) 125251881Speter 126251881Speter The ACTUAL_NODE table row for a given path may also exist in other cases, 127251881Speter including if the "changelist" or any of the conflict columns have a 128251881Speter non-null value. 129251881Speter */ 130251881SpeterCREATE TABLE ACTUAL_NODE ( 131251881Speter /* specifies the location of this node in the local filesystem */ 132251881Speter wc_id INTEGER NOT NULL REFERENCES WCROOT (id), 133251881Speter local_relpath TEXT NOT NULL, 134251881Speter 135251881Speter /* parent's local_relpath for aggregating children of a given parent. 136251881Speter this will be "" if the parent is the wcroot. NULL if this is the 137251881Speter wcroot node. */ 138251881Speter parent_relpath TEXT, 139251881Speter 140251881Speter /* serialized skel of this node's properties. NULL implies no change to 141251881Speter the properties, relative to WORKING/BASE as appropriate. */ 142251881Speter properties BLOB, 143251881Speter 144251881Speter /* relpaths of the conflict files. */ 145251881Speter /* ### These columns will eventually be merged into conflict_data below. */ 146251881Speter conflict_old TEXT, 147251881Speter conflict_new TEXT, 148251881Speter conflict_working TEXT, 149251881Speter prop_reject TEXT, 150251881Speter 151251881Speter /* if not NULL, this node is part of a changelist. */ 152251881Speter changelist TEXT, 153299742Sdim 154251881Speter /* ### need to determine values. "unknown" (no info), "admin" (they 155251881Speter ### used something like 'svn edit'), "noticed" (saw a mod while 156251881Speter ### scanning the filesystem). */ 157251881Speter text_mod TEXT, 158251881Speter 159251881Speter /* if a directory, serialized data for all of tree conflicts therein. 160251881Speter ### This column will eventually be merged into the conflict_data column, 161251881Speter ### but within the ACTUAL node of the tree conflict victim itself, rather 162251881Speter ### than the node of the tree conflict victim's parent directory. */ 163251881Speter tree_conflict_data TEXT, 164251881Speter 165251881Speter /* A skel containing the conflict details. */ 166251881Speter conflict_data BLOB, 167251881Speter 168251881Speter /* Three columns containing the checksums of older, left and right conflict 169251881Speter texts. Stored in a column to allow storing them in the pristine store */ 170251881Speter /* stsp: This is meant for text conflicts, right? What about property 171251881Speter conflicts? Why do we need these in a column to refer to the 172251881Speter pristine store? Can't we just parse the checksums from 173299742Sdim conflict_data as well? 174251881Speter rhuijben: Because that won't allow triggers to handle refcounts. 175251881Speter We would have to scan all conflict skels before cleaning up the 176251881Speter a single file from the pristine stor */ 177251881Speter older_checksum TEXT REFERENCES PRISTINE (checksum), 178251881Speter left_checksum TEXT REFERENCES PRISTINE (checksum), 179251881Speter right_checksum TEXT REFERENCES PRISTINE (checksum), 180251881Speter 181251881Speter PRIMARY KEY (wc_id, local_relpath) 182251881Speter ); 183251881Speter 184251881SpeterCREATE UNIQUE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, parent_relpath, 185251881Speter local_relpath); 186251881Speter 187251881Speter 188251881Speter/* ------------------------------------------------------------------------- */ 189251881Speter 190251881Speter/* This table is a cache of information about repository locks. */ 191251881SpeterCREATE TABLE LOCK ( 192251881Speter /* what repository location is locked */ 193251881Speter repos_id INTEGER NOT NULL REFERENCES REPOSITORY (id), 194251881Speter repos_relpath TEXT NOT NULL, 195251881Speter 196251881Speter /* Information about the lock. Note: these values are just caches from 197251881Speter the server, and are not authoritative. */ 198251881Speter lock_token TEXT NOT NULL, 199251881Speter /* ### make the following fields NOT NULL ? */ 200251881Speter lock_owner TEXT, 201251881Speter lock_comment TEXT, 202251881Speter lock_date INTEGER, /* an APR date/time (usec since 1970) */ 203299742Sdim 204251881Speter PRIMARY KEY (repos_id, repos_relpath) 205251881Speter ); 206251881Speter 207251881Speter 208251881Speter/* ------------------------------------------------------------------------- */ 209251881Speter 210251881SpeterCREATE TABLE WORK_QUEUE ( 211251881Speter /* Work items are identified by this value. */ 212251881Speter id INTEGER PRIMARY KEY AUTOINCREMENT, 213251881Speter 214251881Speter /* A serialized skel specifying the work item. */ 215251881Speter work BLOB NOT NULL 216251881Speter ); 217251881Speter 218251881Speter 219251881Speter/* ------------------------------------------------------------------------- */ 220251881Speter 221251881SpeterCREATE TABLE WC_LOCK ( 222251881Speter /* specifies the location of this node in the local filesystem */ 223251881Speter wc_id INTEGER NOT NULL REFERENCES WCROOT (id), 224251881Speter local_dir_relpath TEXT NOT NULL, 225251881Speter 226251881Speter locked_levels INTEGER NOT NULL DEFAULT -1, 227251881Speter 228251881Speter PRIMARY KEY (wc_id, local_dir_relpath) 229251881Speter ); 230251881Speter 231251881Speter 232251881SpeterPRAGMA user_version = 233251881Speter-- define: SVN_WC__VERSION 234251881Speter; 235251881Speter 236251881Speter 237251881Speter/* ------------------------------------------------------------------------- */ 238251881Speter 239251881Speter/* The NODES table describes the way WORKING nodes are layered on top of 240251881Speter BASE nodes and on top of other WORKING nodes, due to nested tree structure 241251881Speter changes. The layers are modelled using the "op_depth" column. 242251881Speter 243251881Speter An 'operation depth' refers to the number of directory levels down from 244251881Speter the WC root at which a tree-change operation (delete, add?, copy, move) 245251881Speter was performed. A row's 'op_depth' does NOT refer to the depth of its own 246251881Speter 'local_relpath', but rather to the depth of the nearest tree change that 247251881Speter affects that node. 248251881Speter 249251881Speter The row with op_depth=0 for any given local relpath represents the "base" 250251881Speter node that is created and updated by checkout, update, switch and commit 251251881Speter post-processing. The row with the highest op_depth for a particular 252251881Speter local_relpath represents the working version. Any rows with intermediate 253251881Speter op_depth values are not normally visible to the user but may become 254251881Speter visible after reverting local changes. 255251881Speter 256251881Speter This table contains full node descriptions for nodes in either the BASE 257251881Speter or WORKING trees as described in notes/wc-ng/design. Fields relate 258251881Speter both to BASE and WORKING trees, unless documented otherwise. 259251881Speter 260251881Speter For illustration, with a scenario like this: 261251881Speter 262251881Speter # (0) 263251881Speter svn rm foo 264251881Speter svn cp ^/moo foo # (1) 265251881Speter svn rm foo/bar 266251881Speter touch foo/bar 267251881Speter svn add foo/bar # (2) 268251881Speter 269251881Speter , these are the NODES table rows for the path foo/bar: 270251881Speter 271251881Speter (0) "BASE" ---> NODES (op_depth == 0) 272251881Speter (1) NODES (op_depth == 1) 273251881Speter (2) NODES (op_depth == 2) 274251881Speter 275251881Speter 0 is the original data for foo/bar before 'svn rm foo' (if it existed). 276251881Speter 1 is the data for foo/bar copied in from ^/moo/bar. 277251881Speter 2 is the to-be-committed data for foo/bar, created by 'svn add foo/bar'. 278251881Speter 279251881Speter An 'svn revert foo/bar' would remove the NODES of (2). 280251881Speter 281251881Speter */ 282251881Speter-- STMT_CREATE_NODES 283251881SpeterCREATE TABLE NODES ( 284251881Speter /* Working copy location related fields */ 285251881Speter 286251881Speter wc_id INTEGER NOT NULL REFERENCES WCROOT (id), 287251881Speter local_relpath TEXT NOT NULL, 288251881Speter 289251881Speter /* Contains the depth (= number of path segments) of the operation 290251881Speter modifying the working copy tree structure. All nodes below the root 291251881Speter of the operation (aka operation root, aka oproot) affected by the 292251881Speter operation will be assigned the same op_depth. 293251881Speter 294251881Speter op_depth == 0 designates the initial checkout; the BASE tree. 295251881Speter 296251881Speter */ 297251881Speter op_depth INTEGER NOT NULL, 298251881Speter 299251881Speter /* parent's local_relpath for aggregating children of a given parent. 300251881Speter this will be "" if the parent is the wcroot. Since a wcroot will 301251881Speter never have a WORKING node the parent_relpath will never be null, 302251881Speter except when op_depth == 0 and the node is a wcroot. */ 303251881Speter parent_relpath TEXT, 304251881Speter 305251881Speter 306251881Speter /* Repository location fields */ 307251881Speter 308251881Speter /* When op_depth == 0, these fields refer to the repository location of the 309251881Speter BASE node, the location of the initial checkout. 310251881Speter 311251881Speter When op_depth != 0, they indicate where this node was copied/moved from. 312251881Speter In this case, the fields are set for the root of the operation and for all 313251881Speter children. */ 314251881Speter repos_id INTEGER REFERENCES REPOSITORY (id), 315251881Speter repos_path TEXT, 316251881Speter revision INTEGER, 317251881Speter 318251881Speter 319251881Speter /* WC state fields */ 320251881Speter 321251881Speter /* The tree state of the node. 322251881Speter 323251881Speter In case 'op_depth' is equal to 0, this node is part of the 'BASE' 324251881Speter tree. The 'BASE' represents pristine nodes that are in the 325251881Speter repository; it is obtained and modified by commands such as 326251881Speter checkout/update/switch. 327251881Speter 328251881Speter In case 'op_depth' is greater than 0, this node is part of a 329251881Speter layer of working nodes. The 'WORKING' tree is obtained and 330251881Speter modified by commands like delete/copy/revert. 331251881Speter 332251881Speter The 'BASE' and 'WORKING' trees use the same literal values for 333251881Speter the 'presence' but the meaning of each value can vary depending 334251881Speter on the tree. 335251881Speter 336251881Speter normal: in the 'BASE' tree this is an ordinary node for which we 337251881Speter have full information. In the 'WORKING' tree it's an added or 338251881Speter copied node for which we have full information. 339251881Speter 340251881Speter not-present: in the 'BASE' tree this is a node that is implied to 341251881Speter exist by the parent node, but is not present in the working 342251881Speter copy. Typically obtained by delete/commit, or by update to 343251881Speter revision in which the node does not exist. In the 'WORKING' 344251881Speter tree this is a copy of a 'not-present' node from the 'BASE' 345251881Speter tree, and it will be deleted on commit. Such a node cannot be 346251881Speter copied directly, but can be copied as a descendant. 347251881Speter 348251881Speter incomplete: in the 'BASE' tree this is an ordinary node for which 349251881Speter we do not have full information. Only the name is guaranteed; 350251881Speter we may not have all its children, we may not have its checksum, 351251881Speter etc. In the 'WORKING' tree this is a copied node for which we 352251881Speter do not have the full information. This state is generally 353251881Speter obtained when an operation was interrupted. 354251881Speter 355251881Speter base-deleted: not valid in 'BASE' tree. In the 'WORKING' tree 356251881Speter this represents a node that is deleted from the tree below the 357251881Speter current 'op_depth'. This state is badly named, it should be 358251881Speter something like 'deleted'. 359251881Speter 360251881Speter server-excluded: in the 'BASE' tree this is a node that is excluded by 361251881Speter authz. The name of the node is known from the parent, but no 362251881Speter other information is available. Not valid in the 'WORKING' 363251881Speter tree as there is no way to commit such a node. 364251881Speter 365251881Speter excluded: in the 'BASE' tree this node is administratively 366251881Speter excluded by the user (sparse WC). In the 'WORKING' tree this 367251881Speter is a copy of an excluded node from the 'BASE' tree. Such a 368251881Speter node cannot be copied directly but can be copied as a 369251881Speter descendant. */ 370251881Speter 371251881Speter presence TEXT NOT NULL, 372251881Speter 373251881Speter /* ### JF: For an old-style move, "copyfrom" info stores its source, but a 374251881Speter new WC-NG "move" is intended to be a "true rename" so its copyfrom 375251881Speter revision is implicit, being in effect (new head - 1) at commit time. 376251881Speter For a (new) move, we need to store or deduce the copyfrom local-relpath; 377251881Speter perhaps add a column called "moved_from". */ 378251881Speter 379251881Speter /* Boolean value, specifying if this node was moved here (rather than just 380251881Speter copied). This is set on all the nodes in the moved tree. The source of 381251881Speter the move is implied by a different node with a moved_to column pointing 382251881Speter at the root node of the moved tree. */ 383251881Speter moved_here INTEGER, 384251881Speter 385251881Speter /* If the underlying node was moved away (rather than just deleted), this 386251881Speter specifies the local_relpath of where the node was moved to. 387251881Speter This is set only on the root of a move, and is NULL for all children. 388251881Speter 389251881Speter The op-depth of the moved-to node is not recorded. A moved_to path 390251881Speter always points at a node within the highest op-depth layer at the 391251881Speter destination. This invariant must be maintained by operations which 392251881Speter change existing move information. */ 393251881Speter moved_to TEXT, 394251881Speter 395251881Speter 396251881Speter /* Content fields */ 397251881Speter 398251881Speter /* the kind of the new node. may be "unknown" if the node is not present. */ 399251881Speter kind TEXT NOT NULL, 400251881Speter 401251881Speter /* serialized skel of this node's properties (when presence is 'normal' or 402251881Speter 'incomplete'); an empty skel or NULL indicates no properties. NULL if 403251881Speter we have no information about the properties (any other presence). 404251881Speter TODO: Choose & require a single representation for 'no properties'. 405251881Speter */ 406251881Speter properties BLOB, 407251881Speter 408251881Speter /* NULL depth means "default" (typically svn_depth_infinity) */ 409251881Speter /* ### depth on WORKING? seems this is a BASE-only concept. how do 410251881Speter ### you do "files" on an added-directory? can't really ignore 411251881Speter ### the subdirs! */ 412251881Speter /* ### maybe a WC-to-WC copy can retain a depth? */ 413251881Speter depth TEXT, 414251881Speter 415251881Speter /* The SHA-1 checksum of the pristine text, if this node is a file and was 416251881Speter moved here or copied here, else NULL. */ 417251881Speter checksum TEXT REFERENCES PRISTINE (checksum), 418251881Speter 419251881Speter /* for kind==symlink, this specifies the target. */ 420251881Speter symlink_target TEXT, 421251881Speter 422251881Speter 423251881Speter /* Last-Change fields */ 424251881Speter 425251881Speter /* If this node was moved here or copied here, then the following fields may 426251881Speter have information about their source node. changed_rev must be not-null 427251881Speter if this node has presence=="normal". changed_date and changed_author may 428251881Speter be null if the corresponding revprops are missing. 429251881Speter 430251881Speter For an added or not-present node, these are null. */ 431251881Speter changed_revision INTEGER, 432251881Speter changed_date INTEGER, /* an APR date/time (usec since 1970) */ 433251881Speter changed_author TEXT, 434251881Speter 435251881Speter 436251881Speter /* Various cache fields */ 437251881Speter 438251881Speter /* The size in bytes of the working file when it had no local text 439251881Speter modifications. This means the size of the text when translated from 440251881Speter repository-normal format to working copy format with EOL style 441251881Speter translated and keywords expanded according to the properties in the 442251881Speter "properties" column of this row. 443251881Speter 444251881Speter NULL if this node is not a file or if the size has not (yet) been 445251881Speter computed. */ 446251881Speter translated_size INTEGER, 447251881Speter 448251881Speter /* The mod-time of the working file when it was last determined to be 449251881Speter logically unmodified relative to its base, taking account of keywords 450251881Speter and EOL style. This value is used in the change detection heuristic 451251881Speter used by the status command. 452251881Speter 453251881Speter NULL if this node is not a file or if this info has not yet been 454251881Speter determined. 455251881Speter */ 456251881Speter last_mod_time INTEGER, /* an APR date/time (usec since 1970) */ 457251881Speter 458251881Speter /* serialized skel of this node's dav-cache. could be NULL if the 459251881Speter node does not have any dav-cache. */ 460251881Speter dav_cache BLOB, 461251881Speter 462251881Speter /* Is there a file external in this location. NULL if there 463251881Speter is no file external, otherwise '1' */ 464251881Speter /* ### Originally we had a wc-1.0 like skel in this place, so we 465251881Speter ### check for NULL. 466251881Speter ### In Subversion 1.7 we defined this column as TEXT, but Sqlite 467251881Speter ### only uses this information for deciding how to optimize 468251881Speter ### anyway. */ 469251881Speter file_external INTEGER, 470251881Speter 471251881Speter /* serialized skel of this node's inherited properties. NULL if this 472251881Speter is not the BASE of a WC root node. */ 473251881Speter inherited_props BLOB, 474251881Speter 475251881Speter PRIMARY KEY (wc_id, local_relpath, op_depth) 476251881Speter 477251881Speter ); 478251881Speter 479251881SpeterCREATE UNIQUE INDEX I_NODES_PARENT ON NODES (wc_id, parent_relpath, 480251881Speter local_relpath, op_depth); 481251881Speter/* I_NODES_MOVED is introduced in format 30 */ 482251881SpeterCREATE UNIQUE INDEX I_NODES_MOVED ON NODES (wc_id, moved_to, op_depth); 483251881Speter 484251881Speter/* Many queries have to filter the nodes table to pick only that version 485251881Speter of each node with the highest (most "current") op_depth. This view 486251881Speter does the heavy lifting for such queries. 487251881Speter 488251881Speter Note that this view includes a row for each and every path that is known 489251881Speter in the WC, including, for example, paths that were children of a base- or 490251881Speter lower-op-depth directory that has been replaced by something else in the 491251881Speter current view. 492251881Speter */ 493251881SpeterCREATE VIEW NODES_CURRENT AS 494251881Speter SELECT * FROM nodes AS n 495251881Speter WHERE op_depth = (SELECT MAX(op_depth) FROM nodes AS n2 496251881Speter WHERE n2.wc_id = n.wc_id 497251881Speter AND n2.local_relpath = n.local_relpath); 498251881Speter 499251881Speter/* Many queries have to filter the nodes table to pick only that version 500251881Speter of each node with the BASE ("as checked out") op_depth. This view 501251881Speter does the heavy lifting for such queries. */ 502251881SpeterCREATE VIEW NODES_BASE AS 503251881Speter SELECT * FROM nodes 504251881Speter WHERE op_depth = 0; 505251881Speter 506251881Speter-- STMT_CREATE_NODES_TRIGGERS 507251881Speter 508251881SpeterCREATE TRIGGER nodes_insert_trigger 509251881SpeterAFTER INSERT ON nodes 510251881SpeterWHEN NEW.checksum IS NOT NULL 511251881SpeterBEGIN 512251881Speter UPDATE pristine SET refcount = refcount + 1 513251881Speter WHERE checksum = NEW.checksum; 514251881SpeterEND; 515251881Speter 516251881SpeterCREATE TRIGGER nodes_delete_trigger 517251881SpeterAFTER DELETE ON nodes 518251881SpeterWHEN OLD.checksum IS NOT NULL 519251881SpeterBEGIN 520251881Speter UPDATE pristine SET refcount = refcount - 1 521251881Speter WHERE checksum = OLD.checksum; 522251881SpeterEND; 523251881Speter 524251881SpeterCREATE TRIGGER nodes_update_checksum_trigger 525251881SpeterAFTER UPDATE OF checksum ON nodes 526251881SpeterWHEN NEW.checksum IS NOT OLD.checksum 527251881Speter /* AND (NEW.checksum IS NOT NULL OR OLD.checksum IS NOT NULL) */ 528251881SpeterBEGIN 529251881Speter UPDATE pristine SET refcount = refcount + 1 530251881Speter WHERE checksum = NEW.checksum; 531251881Speter UPDATE pristine SET refcount = refcount - 1 532251881Speter WHERE checksum = OLD.checksum; 533251881SpeterEND; 534251881Speter 535251881Speter-- STMT_CREATE_EXTERNALS 536251881Speter 537251881SpeterCREATE TABLE EXTERNALS ( 538251881Speter /* Working copy location related fields (like NODES)*/ 539251881Speter 540251881Speter wc_id INTEGER NOT NULL REFERENCES WCROOT (id), 541251881Speter local_relpath TEXT NOT NULL, 542251881Speter 543251881Speter /* The working copy root can't be recorded as an external in itself 544251881Speter so this will never be NULL. ### ATM only inserted, never queried */ 545251881Speter parent_relpath TEXT NOT NULL, 546251881Speter 547251881Speter /* Repository location fields */ 548251881Speter repos_id INTEGER NOT NULL REFERENCES REPOSITORY (id), 549251881Speter 550251881Speter /* Either MAP_NORMAL or MAP_EXCLUDED */ 551251881Speter presence TEXT NOT NULL, 552251881Speter 553251881Speter /* the kind of the external. */ 554251881Speter kind TEXT NOT NULL, 555251881Speter 556299742Sdim /* The local relpath of the directory NODE defining this external 557251881Speter (Defaults to the parent directory of the file external after upgrade) */ 558251881Speter def_local_relpath TEXT NOT NULL, 559251881Speter 560251881Speter /* The url of the external as used in the definition */ 561251881Speter def_repos_relpath TEXT NOT NULL, 562251881Speter 563251881Speter /* The operational (peg) and node revision if this is a revision fixed 564251881Speter external; otherwise NULL. (Usually these will both have the same value) */ 565251881Speter def_operational_revision TEXT, 566251881Speter def_revision TEXT, 567251881Speter 568251881Speter PRIMARY KEY (wc_id, local_relpath) 569251881Speter); 570251881Speter 571251881SpeterCREATE UNIQUE INDEX I_EXTERNALS_DEFINED ON EXTERNALS (wc_id, 572251881Speter def_local_relpath, 573251881Speter local_relpath); 574251881Speter 575251881Speter/* ------------------------------------------------------------------------- */ 576262253Speter/* This statement provides SQLite with the necessary information about our 577262253Speter indexes to make better decisions in the query planner. 578251881Speter 579262253Speter For every interesting index this contains a number of rows where the 580299742Sdim statistics are calculated for and then for every column in the index the 581262253Speter average number of rows with the same value in all columns left of this 582262253Speter column including the column itself. 583262253Speter 584262253Speter See http://www.sqlite.org/fileformat2.html#stat1tab for more details. 585262253Speter 586262253Speter The important thing here is that this tells Sqlite that the wc_id column 587262253Speter of the NODES and ACTUAL_NODE table is usually a single value, so queries 588262253Speter should use more than one column for index usage. 589262253Speter 590262253Speter The current hints describe NODES+ACTUAL_NODE as a working copy with 591262253Speter 8000 nodes in 1 a single working copy(=wc_id), 10 nodes per directory 592262253Speter and an average of 2 op-depth layers per node. 593262253Speter 594262253Speter The number of integers must be number of index columns + 1, which is 595262253Speter verified via the test_schema_statistics() test. 596262253Speter */ 597262253Speter-- STMT_INSTALL_SCHEMA_STATISTICS 598262253SpeterANALYZE sqlite_master; /* Creates empty sqlite_stat1 if necessary */ 599262253Speter 600269847SpeterDELETE FROM sqlite_stat1 601289166SpeterWHERE tbl in ('NODES', 'ACTUAL_NODE', 'LOCK', 'WC_LOCK', 'EXTERNALS'); 602269847Speter 603289166SpeterINSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 604262253Speter ('NODES', 'sqlite_autoindex_NODES_1', '8000 8000 2 1'); 605289166SpeterINSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 606262253Speter ('NODES', 'I_NODES_PARENT', '8000 8000 10 2 1'); 607262253Speter/* Tell a lie: We ignore that 99.9% of all moved_to values are NULL */ 608289166SpeterINSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 609262253Speter ('NODES', 'I_NODES_MOVED', '8000 8000 1 1'); 610262253Speter 611289166SpeterINSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 612262253Speter ('ACTUAL_NODE', 'sqlite_autoindex_ACTUAL_NODE_1', '8000 8000 1'); 613289166SpeterINSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 614262253Speter ('ACTUAL_NODE', 'I_ACTUAL_PARENT', '8000 8000 10 1'); 615262253Speter 616289166SpeterINSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 617262253Speter ('LOCK', 'sqlite_autoindex_LOCK_1', '100 100 1'); 618262253Speter 619289166SpeterINSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 620262253Speter ('WC_LOCK', 'sqlite_autoindex_WC_LOCK_1', '100 100 1'); 621262253Speter 622289166SpeterINSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 623289166Speter ('EXTERNALS','sqlite_autoindex_EXTERNALS_1', '100 100 1'); 624289166SpeterINSERT INTO sqlite_stat1(tbl, idx, stat) VALUES 625289166Speter ('EXTERNALS','I_EXTERNALS_DEFINED', '100 100 3 1'); 626289166Speter 627262253Speter/* sqlite_autoindex_WORK_QUEUE_1 doesn't exist because WORK_QUEUE is 628262253Speter a INTEGER PRIMARY KEY AUTOINCREMENT table */ 629262253Speter 630262253SpeterANALYZE sqlite_master; /* Loads sqlite_stat1 data for query optimizer */ 631262253Speter/* ------------------------------------------------------------------------- */ 632262253Speter 633251881Speter/* Format 20 introduces NODES and removes BASE_NODE and WORKING_NODE */ 634251881Speter 635251881Speter-- STMT_UPGRADE_TO_20 636251881Speter 637251881SpeterUPDATE BASE_NODE SET checksum = (SELECT checksum FROM pristine 638251881Speter WHERE md5_checksum = BASE_NODE.checksum) 639251881SpeterWHERE EXISTS (SELECT 1 FROM pristine WHERE md5_checksum = BASE_NODE.checksum); 640251881Speter 641251881SpeterUPDATE WORKING_NODE SET checksum = (SELECT checksum FROM pristine 642251881Speter WHERE md5_checksum = WORKING_NODE.checksum) 643251881SpeterWHERE EXISTS (SELECT 1 FROM pristine 644251881Speter WHERE md5_checksum = WORKING_NODE.checksum); 645251881Speter 646251881SpeterINSERT INTO NODES ( 647251881Speter wc_id, local_relpath, op_depth, parent_relpath, 648251881Speter repos_id, repos_path, revision, 649251881Speter presence, depth, moved_here, moved_to, kind, 650251881Speter changed_revision, changed_date, changed_author, 651251881Speter checksum, properties, translated_size, last_mod_time, 652251881Speter dav_cache, symlink_target, file_external ) 653251881SpeterSELECT wc_id, local_relpath, 0 /*op_depth*/, parent_relpath, 654251881Speter repos_id, repos_relpath, revnum, 655251881Speter presence, depth, NULL /*moved_here*/, NULL /*moved_to*/, kind, 656251881Speter changed_rev, changed_date, changed_author, 657251881Speter checksum, properties, translated_size, last_mod_time, 658251881Speter dav_cache, symlink_target, file_external 659251881SpeterFROM BASE_NODE; 660251881SpeterINSERT INTO NODES ( 661251881Speter wc_id, local_relpath, op_depth, parent_relpath, 662251881Speter repos_id, repos_path, revision, 663251881Speter presence, depth, moved_here, moved_to, kind, 664251881Speter changed_revision, changed_date, changed_author, 665251881Speter checksum, properties, translated_size, last_mod_time, 666251881Speter dav_cache, symlink_target, file_external ) 667251881SpeterSELECT wc_id, local_relpath, 2 /*op_depth*/, parent_relpath, 668251881Speter copyfrom_repos_id, copyfrom_repos_path, copyfrom_revnum, 669251881Speter presence, depth, NULL /*moved_here*/, NULL /*moved_to*/, kind, 670251881Speter changed_rev, changed_date, changed_author, 671251881Speter checksum, properties, translated_size, last_mod_time, 672251881Speter NULL /*dav_cache*/, symlink_target, NULL /*file_external*/ 673251881SpeterFROM WORKING_NODE; 674251881Speter 675251881SpeterDROP TABLE BASE_NODE; 676251881SpeterDROP TABLE WORKING_NODE; 677251881Speter 678251881SpeterPRAGMA user_version = 20; 679251881Speter 680251881Speter 681251881Speter/* ------------------------------------------------------------------------- */ 682251881Speter 683251881Speter/* Format 21 involves no schema changes, it moves the tree conflict victim 684251881Speter information to victime nodes, rather than parents. */ 685251881Speter 686251881Speter-- STMT_UPGRADE_TO_21 687251881SpeterPRAGMA user_version = 21; 688251881Speter 689251881Speter/* For format 21 bump code */ 690251881Speter-- STMT_UPGRADE_21_SELECT_OLD_TREE_CONFLICT 691251881SpeterSELECT wc_id, local_relpath, tree_conflict_data 692251881SpeterFROM actual_node 693251881SpeterWHERE tree_conflict_data IS NOT NULL 694251881Speter 695251881Speter/* For format 21 bump code */ 696251881Speter-- STMT_UPGRADE_21_ERASE_OLD_CONFLICTS 697251881SpeterUPDATE actual_node SET tree_conflict_data = NULL 698251881Speter 699251881Speter/* ------------------------------------------------------------------------- */ 700251881Speter 701251881Speter/* Format 22 simply moves the tree conflict information from the conflict_data 702251881Speter column to the tree_conflict_data column. */ 703251881Speter 704251881Speter-- STMT_UPGRADE_TO_22 705251881SpeterUPDATE actual_node SET tree_conflict_data = conflict_data; 706251881SpeterUPDATE actual_node SET conflict_data = NULL; 707251881Speter 708251881SpeterPRAGMA user_version = 22; 709251881Speter 710251881Speter 711251881Speter/* ------------------------------------------------------------------------- */ 712251881Speter 713251881Speter/* Format 23 involves no schema changes, it introduces multi-layer 714251881Speter op-depth processing for NODES. */ 715251881Speter 716251881Speter-- STMT_UPGRADE_TO_23 717251881SpeterPRAGMA user_version = 23; 718251881Speter 719251881Speter-- STMT_UPGRADE_23_HAS_WORKING_NODES 720251881SpeterSELECT 1 FROM nodes WHERE op_depth > 0 721251881SpeterLIMIT 1 722251881Speter 723251881Speter/* ------------------------------------------------------------------------- */ 724251881Speter 725251881Speter/* Format 24 involves no schema changes; it starts using the pristine 726251881Speter table's refcount column correctly. */ 727251881Speter 728251881Speter-- STMT_UPGRADE_TO_24 729251881SpeterUPDATE pristine SET refcount = 730251881Speter (SELECT COUNT(*) FROM nodes 731251881Speter WHERE checksum = pristine.checksum /*OR checksum = pristine.md5_checksum*/); 732251881Speter 733251881SpeterPRAGMA user_version = 24; 734251881Speter 735251881Speter/* ------------------------------------------------------------------------- */ 736251881Speter 737251881Speter/* Format 25 introduces the NODES_CURRENT view. */ 738251881Speter 739251881Speter-- STMT_UPGRADE_TO_25 740251881SpeterDROP VIEW IF EXISTS NODES_CURRENT; 741251881SpeterCREATE VIEW NODES_CURRENT AS 742251881Speter SELECT * FROM nodes 743251881Speter JOIN (SELECT wc_id, local_relpath, MAX(op_depth) AS op_depth FROM nodes 744251881Speter GROUP BY wc_id, local_relpath) AS filter 745251881Speter ON nodes.wc_id = filter.wc_id 746251881Speter AND nodes.local_relpath = filter.local_relpath 747251881Speter AND nodes.op_depth = filter.op_depth; 748251881Speter 749251881SpeterPRAGMA user_version = 25; 750251881Speter 751251881Speter/* ------------------------------------------------------------------------- */ 752251881Speter 753251881Speter/* Format 26 introduces the NODES_BASE view. */ 754251881Speter 755251881Speter-- STMT_UPGRADE_TO_26 756251881SpeterDROP VIEW IF EXISTS NODES_BASE; 757251881SpeterCREATE VIEW NODES_BASE AS 758251881Speter SELECT * FROM nodes 759251881Speter WHERE op_depth = 0; 760251881Speter 761251881SpeterPRAGMA user_version = 26; 762251881Speter 763251881Speter/* ------------------------------------------------------------------------- */ 764251881Speter 765251881Speter/* Format 27 involves no schema changes, it introduces stores 766251881Speter conflict files as relpaths rather than names in ACTUAL_NODE. */ 767251881Speter 768251881Speter-- STMT_UPGRADE_TO_27 769251881SpeterPRAGMA user_version = 27; 770251881Speter 771251881Speter/* For format 27 bump code */ 772251881Speter-- STMT_UPGRADE_27_HAS_ACTUAL_NODES_CONFLICTS 773251881SpeterSELECT 1 FROM actual_node 774251881SpeterWHERE NOT ((prop_reject IS NULL) AND (conflict_old IS NULL) 775251881Speter AND (conflict_new IS NULL) AND (conflict_working IS NULL) 776251881Speter AND (tree_conflict_data IS NULL)) 777251881SpeterLIMIT 1 778251881Speter 779251881Speter 780251881Speter/* ------------------------------------------------------------------------- */ 781251881Speter 782299742Sdim/* Format 28 involves no schema changes, it only converts MD5 pristine 783251881Speter references to SHA1. */ 784251881Speter 785251881Speter-- STMT_UPGRADE_TO_28 786251881Speter 787251881SpeterUPDATE NODES SET checksum = (SELECT checksum FROM pristine 788251881Speter WHERE md5_checksum = nodes.checksum) 789251881SpeterWHERE EXISTS (SELECT 1 FROM pristine WHERE md5_checksum = nodes.checksum); 790251881Speter 791251881SpeterPRAGMA user_version = 28; 792251881Speter 793251881Speter/* ------------------------------------------------------------------------- */ 794251881Speter 795251881Speter/* Format 29 introduces the EXTERNALS table (See STMT_CREATE_TRIGGERS) and 796251881Speter optimizes a few trigger definitions. ... */ 797251881Speter 798251881Speter-- STMT_UPGRADE_TO_29 799251881Speter 800251881SpeterDROP TRIGGER IF EXISTS nodes_update_checksum_trigger; 801251881SpeterDROP TRIGGER IF EXISTS nodes_insert_trigger; 802251881SpeterDROP TRIGGER IF EXISTS nodes_delete_trigger; 803251881Speter 804251881SpeterCREATE TRIGGER nodes_update_checksum_trigger 805251881SpeterAFTER UPDATE OF checksum ON nodes 806251881SpeterWHEN NEW.checksum IS NOT OLD.checksum 807251881Speter /* AND (NEW.checksum IS NOT NULL OR OLD.checksum IS NOT NULL) */ 808251881SpeterBEGIN 809251881Speter UPDATE pristine SET refcount = refcount + 1 810251881Speter WHERE checksum = NEW.checksum; 811251881Speter UPDATE pristine SET refcount = refcount - 1 812251881Speter WHERE checksum = OLD.checksum; 813251881SpeterEND; 814251881Speter 815251881SpeterCREATE TRIGGER nodes_insert_trigger 816251881SpeterAFTER INSERT ON nodes 817251881SpeterWHEN NEW.checksum IS NOT NULL 818251881SpeterBEGIN 819251881Speter UPDATE pristine SET refcount = refcount + 1 820251881Speter WHERE checksum = NEW.checksum; 821251881SpeterEND; 822251881Speter 823251881SpeterCREATE TRIGGER nodes_delete_trigger 824251881SpeterAFTER DELETE ON nodes 825251881SpeterWHEN OLD.checksum IS NOT NULL 826251881SpeterBEGIN 827251881Speter UPDATE pristine SET refcount = refcount - 1 828251881Speter WHERE checksum = OLD.checksum; 829251881SpeterEND; 830251881Speter 831251881SpeterPRAGMA user_version = 29; 832251881Speter 833251881Speter/* ------------------------------------------------------------------------- */ 834251881Speter 835251881Speter/* Format 30 creates a new NODES index for move information, and a new 836251881Speter PRISTINE index for the md5_checksum column. It also activates use of 837251881Speter skel-based conflict storage -- see notes/wc-ng/conflict-storage-2.0. 838251881Speter It also renames the "absent" presence to "server-excluded". */ 839251881Speter-- STMT_UPGRADE_TO_30 840251881SpeterCREATE UNIQUE INDEX IF NOT EXISTS I_NODES_MOVED 841251881SpeterON NODES (wc_id, moved_to, op_depth); 842251881Speter 843251881SpeterCREATE INDEX IF NOT EXISTS I_PRISTINE_MD5 ON PRISTINE (md5_checksum); 844251881Speter 845251881SpeterUPDATE nodes SET presence = "server-excluded" WHERE presence = "absent"; 846251881Speter 847251881Speter/* Just to be sure clear out file external skels from pre 1.7.0 development 848251881Speter working copies that were never updated by 1.7.0+ style clients */ 849251881SpeterUPDATE nodes SET file_external=1 WHERE file_external IS NOT NULL; 850251881Speter 851251881Speter-- STMT_UPGRADE_30_SELECT_CONFLICT_SEPARATE 852251881SpeterSELECT wc_id, local_relpath, 853251881Speter conflict_old, conflict_working, conflict_new, prop_reject, tree_conflict_data 854251881SpeterFROM actual_node 855251881SpeterWHERE conflict_old IS NOT NULL 856251881Speter OR conflict_working IS NOT NULL 857251881Speter OR conflict_new IS NOT NULL 858251881Speter OR prop_reject IS NOT NULL 859251881Speter OR tree_conflict_data IS NOT NULL 860251881SpeterORDER by wc_id, local_relpath 861251881Speter 862251881Speter-- STMT_UPGRADE_30_SET_CONFLICT 863251881SpeterUPDATE actual_node SET conflict_data = ?3, conflict_old = NULL, 864251881Speter conflict_working = NULL, conflict_new = NULL, prop_reject = NULL, 865251881Speter tree_conflict_data = NULL 866251881SpeterWHERE wc_id = ?1 and local_relpath = ?2 867251881Speter 868251881Speter/* ------------------------------------------------------------------------- */ 869251881Speter 870251881Speter/* Format 31 adds the inherited_props column to the NODES table. C code then 871251881Speter initializes the update/switch roots to make sure future updates fetch the 872251881Speter inherited properties */ 873251881Speter-- STMT_UPGRADE_TO_31_ALTER_TABLE 874251881SpeterALTER TABLE NODES ADD COLUMN inherited_props BLOB; 875251881Speter-- STMT_UPGRADE_TO_31_FINALIZE 876251881SpeterDROP INDEX IF EXISTS I_ACTUAL_CHANGELIST; 877251881SpeterDROP INDEX IF EXISTS I_EXTERNALS_PARENT; 878251881Speter 879251881SpeterDROP INDEX I_NODES_PARENT; 880251881SpeterCREATE UNIQUE INDEX I_NODES_PARENT ON NODES (wc_id, parent_relpath, 881251881Speter local_relpath, op_depth); 882251881Speter 883251881SpeterDROP INDEX I_ACTUAL_PARENT; 884251881SpeterCREATE UNIQUE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, parent_relpath, 885251881Speter local_relpath); 886251881Speter 887251881SpeterPRAGMA user_version = 31; 888251881Speter 889251881Speter-- STMT_UPGRADE_31_SELECT_WCROOT_NODES 890251881Speter/* Select all base nodes which are the root of a WC, including 891251881Speter switched subtrees, but excluding those which map to the root 892251881Speter of the repos. 893251881Speter 894251881Speter ### IPROPS: Is this query horribly inefficient? Quite likely, 895251881Speter ### but it only runs during an upgrade, so do we care? */ 896251881SpeterSELECT l.wc_id, l.local_relpath FROM nodes as l 897251881SpeterLEFT OUTER JOIN nodes as r 898251881SpeterON l.wc_id = r.wc_id 899251881Speter AND r.local_relpath = l.parent_relpath 900251881Speter AND r.op_depth = 0 901251881SpeterWHERE l.op_depth = 0 902251881Speter AND l.repos_path != '' 903251881Speter AND ((l.repos_id IS NOT r.repos_id) 904251881Speter OR (l.repos_path IS NOT RELPATH_SKIP_JOIN(r.local_relpath, r.repos_path, l.local_relpath))) 905251881Speter 906251881Speter 907251881Speter/* ------------------------------------------------------------------------- */ 908251881Speter/* Format 32 .... */ 909251881Speter-- STMT_UPGRADE_TO_32 910251881Speter 911251881Speter/* Drop old index. ### Remove this part from the upgrade to 31 once bumped */ 912251881SpeterDROP INDEX IF EXISTS I_ACTUAL_CHANGELIST; 913251881SpeterDROP INDEX IF EXISTS I_EXTERNALS_PARENT; 914251881SpeterCREATE INDEX I_EXTERNALS_PARENT ON EXTERNALS (wc_id, parent_relpath); 915251881Speter 916251881SpeterDROP INDEX I_NODES_PARENT; 917251881SpeterCREATE UNIQUE INDEX I_NODES_PARENT ON NODES (wc_id, parent_relpath, 918251881Speter local_relpath, op_depth); 919251881Speter 920251881SpeterDROP INDEX I_ACTUAL_PARENT; 921251881SpeterCREATE UNIQUE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, parent_relpath, 922251881Speter local_relpath); 923251881Speter 924251881Speter/* ------------------------------------------------------------------------- */ 925251881Speter 926251881Speter/* Format YYY introduces new handling for conflict information. */ 927251881Speter-- format: YYY 928251881Speter 929251881Speter 930251881Speter/* ------------------------------------------------------------------------- */ 931251881Speter 932251881Speter/* Format 99 drops all columns not needed due to previous format upgrades. 933251881Speter Before we release 1.7, these statements will be pulled into a format bump 934251881Speter and all the tables will be cleaned up. We don't know what that format 935251881Speter number will be, however, so we're just marking it as 99 for now. */ 936251881Speter-- format: 99 937251881Speter 938251881Speter/* TODO: Un-confuse *_revision column names in the EXTERNALS table to 939251881Speter "-r<operative> foo@<peg>", as suggested by the patch attached to 940251881Speter http://svn.haxx.se/dev/archive-2011-09/0478.shtml */ 941251881Speter/* TODO: Remove column parent_relpath from EXTERNALS. We're not using it and 942251881Speter never will. It's not interesting like in the NODES table: the external's 943251881Speter parent path may be *anything*: unversioned, "behind" a another WC... */ 944251881Speter 945251881Speter/* Now "drop" the tree_conflict_data column from actual_node. */ 946251881SpeterCREATE TABLE ACTUAL_NODE_BACKUP ( 947251881Speter wc_id INTEGER NOT NULL, 948251881Speter local_relpath TEXT NOT NULL, 949251881Speter parent_relpath TEXT, 950251881Speter properties BLOB, 951251881Speter conflict_old TEXT, 952251881Speter conflict_new TEXT, 953251881Speter conflict_working TEXT, 954251881Speter prop_reject TEXT, 955251881Speter changelist TEXT, 956251881Speter text_mod TEXT 957251881Speter ); 958251881Speter 959251881SpeterINSERT INTO ACTUAL_NODE_BACKUP SELECT 960251881Speter wc_id, local_relpath, parent_relpath, properties, conflict_old, 961251881Speter conflict_new, conflict_working, prop_reject, changelist, text_mod 962251881SpeterFROM ACTUAL_NODE; 963251881Speter 964251881SpeterDROP TABLE ACTUAL_NODE; 965251881Speter 966251881SpeterCREATE TABLE ACTUAL_NODE ( 967251881Speter wc_id INTEGER NOT NULL REFERENCES WCROOT (id), 968251881Speter local_relpath TEXT NOT NULL, 969251881Speter parent_relpath TEXT, 970251881Speter properties BLOB, 971251881Speter conflict_old TEXT, 972251881Speter conflict_new TEXT, 973251881Speter conflict_working TEXT, 974251881Speter prop_reject TEXT, 975251881Speter changelist TEXT, 976251881Speter text_mod TEXT, 977251881Speter 978251881Speter PRIMARY KEY (wc_id, local_relpath) 979251881Speter ); 980251881Speter 981251881SpeterCREATE UNIQUE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, parent_relpath, 982251881Speter local_relpath); 983251881Speter 984251881SpeterINSERT INTO ACTUAL_NODE SELECT 985251881Speter wc_id, local_relpath, parent_relpath, properties, conflict_old, 986251881Speter conflict_new, conflict_working, prop_reject, changelist, text_mod 987251881SpeterFROM ACTUAL_NODE_BACKUP; 988251881Speter 989251881SpeterDROP TABLE ACTUAL_NODE_BACKUP; 990251881Speter 991251881Speter/* Note: Other differences between the schemas of an upgraded and a 992251881Speter * fresh WC. 993251881Speter * 994251881Speter * While format 22 was current, "NOT NULL" was added to the 995251881Speter * columns PRISTINE.size and PRISTINE.md5_checksum. The format was not 996251881Speter * bumped because it is a forward- and backward-compatible change. 997251881Speter * 998251881Speter * While format 23 was current, "REFERENCES PRISTINE" was added to the 999251881Speter * columns ACTUAL_NODE.older_checksum, ACTUAL_NODE.left_checksum, 1000251881Speter * ACTUAL_NODE.right_checksum, NODES.checksum. 1001251881Speter * 1002251881Speter * The "NODES_BASE" view was originally implemented with a more complex (but 1003251881Speter * functionally equivalent) statement using a 'JOIN'. WCs that were created 1004251881Speter * at or upgraded to format 26 before it was changed will still have the old 1005251881Speter * version. 1006251881Speter */ 1007251881Speter 1008