wc-metadata.sql revision 262253
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); 111251881Speter 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, 153251881Speter 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 173251881Speter 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) */ 203251881Speter 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 556251881Speter /* 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 580262253Speter statistics ar 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 600262253SpeterINSERT OR REPLACE INTO sqlite_stat1(tbl, idx, stat) VALUES 601262253Speter ('NODES', 'sqlite_autoindex_NODES_1', '8000 8000 2 1'); 602262253SpeterINSERT OR REPLACE INTO sqlite_stat1(tbl, idx, stat) VALUES 603262253Speter ('NODES', 'I_NODES_PARENT', '8000 8000 10 2 1'); 604262253Speter/* Tell a lie: We ignore that 99.9% of all moved_to values are NULL */ 605262253SpeterINSERT OR REPLACE INTO sqlite_stat1(tbl, idx, stat) VALUES 606262253Speter ('NODES', 'I_NODES_MOVED', '8000 8000 1 1'); 607262253Speter 608262253SpeterINSERT OR REPLACE INTO sqlite_stat1(tbl, idx, stat) VALUES 609262253Speter ('ACTUAL_NODE', 'sqlite_autoindex_ACTUAL_NODE_1', '8000 8000 1'); 610262253SpeterINSERT OR REPLACE INTO sqlite_stat1(tbl, idx, stat) VALUES 611262253Speter ('ACTUAL_NODE', 'I_ACTUAL_PARENT', '8000 8000 10 1'); 612262253Speter 613262253SpeterINSERT OR REPLACE INTO sqlite_stat1(tbl, idx, stat) VALUES 614262253Speter ('LOCK', 'sqlite_autoindex_LOCK_1', '100 100 1'); 615262253Speter 616262253SpeterINSERT OR REPLACE INTO sqlite_stat1(tbl, idx, stat) VALUES 617262253Speter ('WC_LOCK', 'sqlite_autoindex_WC_LOCK_1', '100 100 1'); 618262253Speter 619262253Speter/* sqlite_autoindex_WORK_QUEUE_1 doesn't exist because WORK_QUEUE is 620262253Speter a INTEGER PRIMARY KEY AUTOINCREMENT table */ 621262253Speter 622262253SpeterANALYZE sqlite_master; /* Loads sqlite_stat1 data for query optimizer */ 623262253Speter/* ------------------------------------------------------------------------- */ 624262253Speter 625251881Speter/* Format 20 introduces NODES and removes BASE_NODE and WORKING_NODE */ 626251881Speter 627251881Speter-- STMT_UPGRADE_TO_20 628251881Speter 629251881SpeterUPDATE BASE_NODE SET checksum = (SELECT checksum FROM pristine 630251881Speter WHERE md5_checksum = BASE_NODE.checksum) 631251881SpeterWHERE EXISTS (SELECT 1 FROM pristine WHERE md5_checksum = BASE_NODE.checksum); 632251881Speter 633251881SpeterUPDATE WORKING_NODE SET checksum = (SELECT checksum FROM pristine 634251881Speter WHERE md5_checksum = WORKING_NODE.checksum) 635251881SpeterWHERE EXISTS (SELECT 1 FROM pristine 636251881Speter WHERE md5_checksum = WORKING_NODE.checksum); 637251881Speter 638251881SpeterINSERT INTO NODES ( 639251881Speter wc_id, local_relpath, op_depth, parent_relpath, 640251881Speter repos_id, repos_path, revision, 641251881Speter presence, depth, moved_here, moved_to, kind, 642251881Speter changed_revision, changed_date, changed_author, 643251881Speter checksum, properties, translated_size, last_mod_time, 644251881Speter dav_cache, symlink_target, file_external ) 645251881SpeterSELECT wc_id, local_relpath, 0 /*op_depth*/, parent_relpath, 646251881Speter repos_id, repos_relpath, revnum, 647251881Speter presence, depth, NULL /*moved_here*/, NULL /*moved_to*/, kind, 648251881Speter changed_rev, changed_date, changed_author, 649251881Speter checksum, properties, translated_size, last_mod_time, 650251881Speter dav_cache, symlink_target, file_external 651251881SpeterFROM BASE_NODE; 652251881SpeterINSERT INTO NODES ( 653251881Speter wc_id, local_relpath, op_depth, parent_relpath, 654251881Speter repos_id, repos_path, revision, 655251881Speter presence, depth, moved_here, moved_to, kind, 656251881Speter changed_revision, changed_date, changed_author, 657251881Speter checksum, properties, translated_size, last_mod_time, 658251881Speter dav_cache, symlink_target, file_external ) 659251881SpeterSELECT wc_id, local_relpath, 2 /*op_depth*/, parent_relpath, 660251881Speter copyfrom_repos_id, copyfrom_repos_path, copyfrom_revnum, 661251881Speter presence, depth, NULL /*moved_here*/, NULL /*moved_to*/, kind, 662251881Speter changed_rev, changed_date, changed_author, 663251881Speter checksum, properties, translated_size, last_mod_time, 664251881Speter NULL /*dav_cache*/, symlink_target, NULL /*file_external*/ 665251881SpeterFROM WORKING_NODE; 666251881Speter 667251881SpeterDROP TABLE BASE_NODE; 668251881SpeterDROP TABLE WORKING_NODE; 669251881Speter 670251881SpeterPRAGMA user_version = 20; 671251881Speter 672251881Speter 673251881Speter/* ------------------------------------------------------------------------- */ 674251881Speter 675251881Speter/* Format 21 involves no schema changes, it moves the tree conflict victim 676251881Speter information to victime nodes, rather than parents. */ 677251881Speter 678251881Speter-- STMT_UPGRADE_TO_21 679251881SpeterPRAGMA user_version = 21; 680251881Speter 681251881Speter/* For format 21 bump code */ 682251881Speter-- STMT_UPGRADE_21_SELECT_OLD_TREE_CONFLICT 683251881SpeterSELECT wc_id, local_relpath, tree_conflict_data 684251881SpeterFROM actual_node 685251881SpeterWHERE tree_conflict_data IS NOT NULL 686251881Speter 687251881Speter/* For format 21 bump code */ 688251881Speter-- STMT_UPGRADE_21_ERASE_OLD_CONFLICTS 689251881SpeterUPDATE actual_node SET tree_conflict_data = NULL 690251881Speter 691251881Speter/* ------------------------------------------------------------------------- */ 692251881Speter 693251881Speter/* Format 22 simply moves the tree conflict information from the conflict_data 694251881Speter column to the tree_conflict_data column. */ 695251881Speter 696251881Speter-- STMT_UPGRADE_TO_22 697251881SpeterUPDATE actual_node SET tree_conflict_data = conflict_data; 698251881SpeterUPDATE actual_node SET conflict_data = NULL; 699251881Speter 700251881SpeterPRAGMA user_version = 22; 701251881Speter 702251881Speter 703251881Speter/* ------------------------------------------------------------------------- */ 704251881Speter 705251881Speter/* Format 23 involves no schema changes, it introduces multi-layer 706251881Speter op-depth processing for NODES. */ 707251881Speter 708251881Speter-- STMT_UPGRADE_TO_23 709251881SpeterPRAGMA user_version = 23; 710251881Speter 711251881Speter-- STMT_UPGRADE_23_HAS_WORKING_NODES 712251881SpeterSELECT 1 FROM nodes WHERE op_depth > 0 713251881SpeterLIMIT 1 714251881Speter 715251881Speter/* ------------------------------------------------------------------------- */ 716251881Speter 717251881Speter/* Format 24 involves no schema changes; it starts using the pristine 718251881Speter table's refcount column correctly. */ 719251881Speter 720251881Speter-- STMT_UPGRADE_TO_24 721251881SpeterUPDATE pristine SET refcount = 722251881Speter (SELECT COUNT(*) FROM nodes 723251881Speter WHERE checksum = pristine.checksum /*OR checksum = pristine.md5_checksum*/); 724251881Speter 725251881SpeterPRAGMA user_version = 24; 726251881Speter 727251881Speter/* ------------------------------------------------------------------------- */ 728251881Speter 729251881Speter/* Format 25 introduces the NODES_CURRENT view. */ 730251881Speter 731251881Speter-- STMT_UPGRADE_TO_25 732251881SpeterDROP VIEW IF EXISTS NODES_CURRENT; 733251881SpeterCREATE VIEW NODES_CURRENT AS 734251881Speter SELECT * FROM nodes 735251881Speter JOIN (SELECT wc_id, local_relpath, MAX(op_depth) AS op_depth FROM nodes 736251881Speter GROUP BY wc_id, local_relpath) AS filter 737251881Speter ON nodes.wc_id = filter.wc_id 738251881Speter AND nodes.local_relpath = filter.local_relpath 739251881Speter AND nodes.op_depth = filter.op_depth; 740251881Speter 741251881SpeterPRAGMA user_version = 25; 742251881Speter 743251881Speter/* ------------------------------------------------------------------------- */ 744251881Speter 745251881Speter/* Format 26 introduces the NODES_BASE view. */ 746251881Speter 747251881Speter-- STMT_UPGRADE_TO_26 748251881SpeterDROP VIEW IF EXISTS NODES_BASE; 749251881SpeterCREATE VIEW NODES_BASE AS 750251881Speter SELECT * FROM nodes 751251881Speter WHERE op_depth = 0; 752251881Speter 753251881SpeterPRAGMA user_version = 26; 754251881Speter 755251881Speter/* ------------------------------------------------------------------------- */ 756251881Speter 757251881Speter/* Format 27 involves no schema changes, it introduces stores 758251881Speter conflict files as relpaths rather than names in ACTUAL_NODE. */ 759251881Speter 760251881Speter-- STMT_UPGRADE_TO_27 761251881SpeterPRAGMA user_version = 27; 762251881Speter 763251881Speter/* For format 27 bump code */ 764251881Speter-- STMT_UPGRADE_27_HAS_ACTUAL_NODES_CONFLICTS 765251881SpeterSELECT 1 FROM actual_node 766251881SpeterWHERE NOT ((prop_reject IS NULL) AND (conflict_old IS NULL) 767251881Speter AND (conflict_new IS NULL) AND (conflict_working IS NULL) 768251881Speter AND (tree_conflict_data IS NULL)) 769251881SpeterLIMIT 1 770251881Speter 771251881Speter 772251881Speter/* ------------------------------------------------------------------------- */ 773251881Speter 774251881Speter/* Format 28 involves no schema changes, it only converts MD5 pristine 775251881Speter references to SHA1. */ 776251881Speter 777251881Speter-- STMT_UPGRADE_TO_28 778251881Speter 779251881SpeterUPDATE NODES SET checksum = (SELECT checksum FROM pristine 780251881Speter WHERE md5_checksum = nodes.checksum) 781251881SpeterWHERE EXISTS (SELECT 1 FROM pristine WHERE md5_checksum = nodes.checksum); 782251881Speter 783251881SpeterPRAGMA user_version = 28; 784251881Speter 785251881Speter/* ------------------------------------------------------------------------- */ 786251881Speter 787251881Speter/* Format 29 introduces the EXTERNALS table (See STMT_CREATE_TRIGGERS) and 788251881Speter optimizes a few trigger definitions. ... */ 789251881Speter 790251881Speter-- STMT_UPGRADE_TO_29 791251881Speter 792251881SpeterDROP TRIGGER IF EXISTS nodes_update_checksum_trigger; 793251881SpeterDROP TRIGGER IF EXISTS nodes_insert_trigger; 794251881SpeterDROP TRIGGER IF EXISTS nodes_delete_trigger; 795251881Speter 796251881SpeterCREATE TRIGGER nodes_update_checksum_trigger 797251881SpeterAFTER UPDATE OF checksum ON nodes 798251881SpeterWHEN NEW.checksum IS NOT OLD.checksum 799251881Speter /* AND (NEW.checksum IS NOT NULL OR OLD.checksum IS NOT NULL) */ 800251881SpeterBEGIN 801251881Speter UPDATE pristine SET refcount = refcount + 1 802251881Speter WHERE checksum = NEW.checksum; 803251881Speter UPDATE pristine SET refcount = refcount - 1 804251881Speter WHERE checksum = OLD.checksum; 805251881SpeterEND; 806251881Speter 807251881SpeterCREATE TRIGGER nodes_insert_trigger 808251881SpeterAFTER INSERT ON nodes 809251881SpeterWHEN NEW.checksum IS NOT NULL 810251881SpeterBEGIN 811251881Speter UPDATE pristine SET refcount = refcount + 1 812251881Speter WHERE checksum = NEW.checksum; 813251881SpeterEND; 814251881Speter 815251881SpeterCREATE TRIGGER nodes_delete_trigger 816251881SpeterAFTER DELETE ON nodes 817251881SpeterWHEN OLD.checksum IS NOT NULL 818251881SpeterBEGIN 819251881Speter UPDATE pristine SET refcount = refcount - 1 820251881Speter WHERE checksum = OLD.checksum; 821251881SpeterEND; 822251881Speter 823251881SpeterPRAGMA user_version = 29; 824251881Speter 825251881Speter/* ------------------------------------------------------------------------- */ 826251881Speter 827251881Speter/* Format 30 creates a new NODES index for move information, and a new 828251881Speter PRISTINE index for the md5_checksum column. It also activates use of 829251881Speter skel-based conflict storage -- see notes/wc-ng/conflict-storage-2.0. 830251881Speter It also renames the "absent" presence to "server-excluded". */ 831251881Speter-- STMT_UPGRADE_TO_30 832251881SpeterCREATE UNIQUE INDEX IF NOT EXISTS I_NODES_MOVED 833251881SpeterON NODES (wc_id, moved_to, op_depth); 834251881Speter 835251881SpeterCREATE INDEX IF NOT EXISTS I_PRISTINE_MD5 ON PRISTINE (md5_checksum); 836251881Speter 837251881SpeterUPDATE nodes SET presence = "server-excluded" WHERE presence = "absent"; 838251881Speter 839251881Speter/* Just to be sure clear out file external skels from pre 1.7.0 development 840251881Speter working copies that were never updated by 1.7.0+ style clients */ 841251881SpeterUPDATE nodes SET file_external=1 WHERE file_external IS NOT NULL; 842251881Speter 843251881Speter-- STMT_UPGRADE_30_SELECT_CONFLICT_SEPARATE 844251881SpeterSELECT wc_id, local_relpath, 845251881Speter conflict_old, conflict_working, conflict_new, prop_reject, tree_conflict_data 846251881SpeterFROM actual_node 847251881SpeterWHERE conflict_old IS NOT NULL 848251881Speter OR conflict_working IS NOT NULL 849251881Speter OR conflict_new IS NOT NULL 850251881Speter OR prop_reject IS NOT NULL 851251881Speter OR tree_conflict_data IS NOT NULL 852251881SpeterORDER by wc_id, local_relpath 853251881Speter 854251881Speter-- STMT_UPGRADE_30_SET_CONFLICT 855251881SpeterUPDATE actual_node SET conflict_data = ?3, conflict_old = NULL, 856251881Speter conflict_working = NULL, conflict_new = NULL, prop_reject = NULL, 857251881Speter tree_conflict_data = NULL 858251881SpeterWHERE wc_id = ?1 and local_relpath = ?2 859251881Speter 860251881Speter/* ------------------------------------------------------------------------- */ 861251881Speter 862251881Speter/* Format 31 adds the inherited_props column to the NODES table. C code then 863251881Speter initializes the update/switch roots to make sure future updates fetch the 864251881Speter inherited properties */ 865251881Speter-- STMT_UPGRADE_TO_31_ALTER_TABLE 866251881SpeterALTER TABLE NODES ADD COLUMN inherited_props BLOB; 867251881Speter-- STMT_UPGRADE_TO_31_FINALIZE 868251881SpeterDROP INDEX IF EXISTS I_ACTUAL_CHANGELIST; 869251881SpeterDROP INDEX IF EXISTS I_EXTERNALS_PARENT; 870251881Speter 871251881SpeterDROP INDEX I_NODES_PARENT; 872251881SpeterCREATE UNIQUE INDEX I_NODES_PARENT ON NODES (wc_id, parent_relpath, 873251881Speter local_relpath, op_depth); 874251881Speter 875251881SpeterDROP INDEX I_ACTUAL_PARENT; 876251881SpeterCREATE UNIQUE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, parent_relpath, 877251881Speter local_relpath); 878251881Speter 879251881SpeterPRAGMA user_version = 31; 880251881Speter 881251881Speter-- STMT_UPGRADE_31_SELECT_WCROOT_NODES 882251881Speter/* Select all base nodes which are the root of a WC, including 883251881Speter switched subtrees, but excluding those which map to the root 884251881Speter of the repos. 885251881Speter 886251881Speter ### IPROPS: Is this query horribly inefficient? Quite likely, 887251881Speter ### but it only runs during an upgrade, so do we care? */ 888251881SpeterSELECT l.wc_id, l.local_relpath FROM nodes as l 889251881SpeterLEFT OUTER JOIN nodes as r 890251881SpeterON l.wc_id = r.wc_id 891251881Speter AND r.local_relpath = l.parent_relpath 892251881Speter AND r.op_depth = 0 893251881SpeterWHERE l.op_depth = 0 894251881Speter AND l.repos_path != '' 895251881Speter AND ((l.repos_id IS NOT r.repos_id) 896251881Speter OR (l.repos_path IS NOT RELPATH_SKIP_JOIN(r.local_relpath, r.repos_path, l.local_relpath))) 897251881Speter 898251881Speter 899251881Speter/* ------------------------------------------------------------------------- */ 900251881Speter/* Format 32 .... */ 901251881Speter-- STMT_UPGRADE_TO_32 902251881Speter 903251881Speter/* Drop old index. ### Remove this part from the upgrade to 31 once bumped */ 904251881SpeterDROP INDEX IF EXISTS I_ACTUAL_CHANGELIST; 905251881SpeterDROP INDEX IF EXISTS I_EXTERNALS_PARENT; 906251881SpeterCREATE INDEX I_EXTERNALS_PARENT ON EXTERNALS (wc_id, parent_relpath); 907251881Speter 908251881SpeterDROP INDEX I_NODES_PARENT; 909251881SpeterCREATE UNIQUE INDEX I_NODES_PARENT ON NODES (wc_id, parent_relpath, 910251881Speter local_relpath, op_depth); 911251881Speter 912251881SpeterDROP INDEX I_ACTUAL_PARENT; 913251881SpeterCREATE UNIQUE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, parent_relpath, 914251881Speter local_relpath); 915251881Speter 916251881Speter/* ------------------------------------------------------------------------- */ 917251881Speter 918251881Speter/* Format YYY introduces new handling for conflict information. */ 919251881Speter-- format: YYY 920251881Speter 921251881Speter 922251881Speter/* ------------------------------------------------------------------------- */ 923251881Speter 924251881Speter/* Format 99 drops all columns not needed due to previous format upgrades. 925251881Speter Before we release 1.7, these statements will be pulled into a format bump 926251881Speter and all the tables will be cleaned up. We don't know what that format 927251881Speter number will be, however, so we're just marking it as 99 for now. */ 928251881Speter-- format: 99 929251881Speter 930251881Speter/* TODO: Un-confuse *_revision column names in the EXTERNALS table to 931251881Speter "-r<operative> foo@<peg>", as suggested by the patch attached to 932251881Speter http://svn.haxx.se/dev/archive-2011-09/0478.shtml */ 933251881Speter/* TODO: Remove column parent_relpath from EXTERNALS. We're not using it and 934251881Speter never will. It's not interesting like in the NODES table: the external's 935251881Speter parent path may be *anything*: unversioned, "behind" a another WC... */ 936251881Speter 937251881Speter/* Now "drop" the tree_conflict_data column from actual_node. */ 938251881SpeterCREATE TABLE ACTUAL_NODE_BACKUP ( 939251881Speter wc_id INTEGER NOT NULL, 940251881Speter local_relpath TEXT NOT NULL, 941251881Speter parent_relpath TEXT, 942251881Speter properties BLOB, 943251881Speter conflict_old TEXT, 944251881Speter conflict_new TEXT, 945251881Speter conflict_working TEXT, 946251881Speter prop_reject TEXT, 947251881Speter changelist TEXT, 948251881Speter text_mod TEXT 949251881Speter ); 950251881Speter 951251881SpeterINSERT INTO ACTUAL_NODE_BACKUP SELECT 952251881Speter wc_id, local_relpath, parent_relpath, properties, conflict_old, 953251881Speter conflict_new, conflict_working, prop_reject, changelist, text_mod 954251881SpeterFROM ACTUAL_NODE; 955251881Speter 956251881SpeterDROP TABLE ACTUAL_NODE; 957251881Speter 958251881SpeterCREATE TABLE ACTUAL_NODE ( 959251881Speter wc_id INTEGER NOT NULL REFERENCES WCROOT (id), 960251881Speter local_relpath TEXT NOT NULL, 961251881Speter parent_relpath TEXT, 962251881Speter properties BLOB, 963251881Speter conflict_old TEXT, 964251881Speter conflict_new TEXT, 965251881Speter conflict_working TEXT, 966251881Speter prop_reject TEXT, 967251881Speter changelist TEXT, 968251881Speter text_mod TEXT, 969251881Speter 970251881Speter PRIMARY KEY (wc_id, local_relpath) 971251881Speter ); 972251881Speter 973251881SpeterCREATE UNIQUE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, parent_relpath, 974251881Speter local_relpath); 975251881Speter 976251881SpeterINSERT INTO ACTUAL_NODE SELECT 977251881Speter wc_id, local_relpath, parent_relpath, properties, conflict_old, 978251881Speter conflict_new, conflict_working, prop_reject, changelist, text_mod 979251881SpeterFROM ACTUAL_NODE_BACKUP; 980251881Speter 981251881SpeterDROP TABLE ACTUAL_NODE_BACKUP; 982251881Speter 983251881Speter/* Note: Other differences between the schemas of an upgraded and a 984251881Speter * fresh WC. 985251881Speter * 986251881Speter * While format 22 was current, "NOT NULL" was added to the 987251881Speter * columns PRISTINE.size and PRISTINE.md5_checksum. The format was not 988251881Speter * bumped because it is a forward- and backward-compatible change. 989251881Speter * 990251881Speter * While format 23 was current, "REFERENCES PRISTINE" was added to the 991251881Speter * columns ACTUAL_NODE.older_checksum, ACTUAL_NODE.left_checksum, 992251881Speter * ACTUAL_NODE.right_checksum, NODES.checksum. 993251881Speter * 994251881Speter * The "NODES_BASE" view was originally implemented with a more complex (but 995251881Speter * functionally equivalent) statement using a 'JOIN'. WCs that were created 996251881Speter * at or upgraded to format 26 before it was changed will still have the old 997251881Speter * version. 998251881Speter */ 999251881Speter 1000