1 /* wc-metadata.sql -- schema used in the wc-metadata SQLite database
2 * This is intended for use with SQLite 3
4 * ====================================================================
5 * Licensed to the Apache Software Foundation (ASF) under one
6 * or more contributor license agreements. See the NOTICE file
7 * distributed with this work for additional information
8 * regarding copyright ownership. The ASF licenses this file
9 * to you under the Apache License, Version 2.0 (the
10 * "License"); you may not use this file except in compliance
11 * with the License. You may obtain a copy of the License at
13 * http://www.apache.org/licenses/LICENSE-2.0
15 * Unless required by applicable law or agreed to in writing,
16 * software distributed under the License is distributed on an
17 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
18 * KIND, either express or implied. See the License for the
19 * specific language governing permissions and limitations
21 * ====================================================================
25 * the KIND column in these tables has one of the following values
26 * (documented in the corresponding C type #svn_kind_t):
32 * the PRESENCE column in these tables has one of the following values
33 * (see also the C type #svn_wc__db_status_t):
35 * "server-excluded" -- server has declared it excluded (ie. authz failure)
36 * "excluded" -- administratively excluded (ie. sparse WC)
37 * "not-present" -- node not present at this REV
38 * "incomplete" -- state hasn't been filled in
39 * "base-deleted" -- node represents a delete of a BASE node
42 /* One big list of statements to create our (current) schema. */
45 /* ------------------------------------------------------------------------- */
47 CREATE TABLE REPOSITORY (
48 id INTEGER PRIMARY KEY AUTOINCREMENT,
50 /* The root URL of the repository. This value is URI-encoded. */
51 root TEXT UNIQUE NOT NULL,
53 /* the UUID of the repository */
57 /* Note: a repository (identified by its UUID) may appear at multiple URLs.
58 For example, http://example.com/repos/ and https://example.com/repos/. */
59 CREATE INDEX I_UUID ON REPOSITORY (uuid);
60 CREATE INDEX I_ROOT ON REPOSITORY (root);
63 /* ------------------------------------------------------------------------- */
66 id INTEGER PRIMARY KEY AUTOINCREMENT,
68 /* absolute path in the local filesystem. NULL if storing metadata in
70 local_abspath TEXT UNIQUE
73 CREATE UNIQUE INDEX I_LOCAL_ABSPATH ON WCROOT (local_abspath);
76 /* ------------------------------------------------------------------------- */
78 /* The PRISTINE table keeps track of pristine texts. Each row describes a
79 single pristine text. The text itself is stored in a file whose name is
80 derived from the 'checksum' column. Each pristine text is referenced by
81 any number of rows in the NODES and ACTUAL_NODE tables.
83 In future, the pristine text file may be compressed.
85 CREATE TABLE PRISTINE (
86 /* The SHA-1 checksum of the pristine text. This is a unique key. The
87 SHA-1 checksum of a pristine text is assumed to be unique among all
88 pristine texts referenced from this database. */
89 checksum TEXT NOT NULL PRIMARY KEY,
91 /* Enumerated values specifying type of compression. The only value
92 supported so far is NULL, meaning that no compression has been applied
93 and the pristine text is stored verbatim in the file. */
96 /* The size in bytes of the file in which the pristine text is stored.
97 Used to verify the pristine file is "proper". */
98 size INTEGER NOT NULL,
100 /* The number of rows in the NODES table that have a 'checksum' column
101 value that refers to this row. (References in other places, such as
102 in the ACTUAL_NODE table, are not counted.) */
103 refcount INTEGER NOT NULL,
105 /* Alternative MD5 checksum used for communicating with older
106 repositories. Not strictly guaranteed to be unique among table rows. */
107 md5_checksum TEXT NOT NULL
110 CREATE INDEX I_PRISTINE_MD5 ON PRISTINE (md5_checksum);
112 /* ------------------------------------------------------------------------- */
114 /* The ACTUAL_NODE table describes text changes and property changes
115 on each node in the WC, relative to the NODES table row for the
116 same path. (A NODES row must exist if this node exists, but an
117 ACTUAL_NODE row can exist on its own if it is just recording info
118 on a non-present node - a tree conflict or a changelist, for
121 The ACTUAL_NODE table row for a given path exists if the node at that
122 path is known to have text or property changes relative to its
123 NODES row. ("Is known" because a text change on disk may not yet
124 have been discovered and recorded here.)
126 The ACTUAL_NODE table row for a given path may also exist in other cases,
127 including if the "changelist" or any of the conflict columns have a
130 CREATE TABLE ACTUAL_NODE (
131 /* specifies the location of this node in the local filesystem */
132 wc_id INTEGER NOT NULL REFERENCES WCROOT (id),
133 local_relpath TEXT NOT NULL,
135 /* parent's local_relpath for aggregating children of a given parent.
136 this will be "" if the parent is the wcroot. NULL if this is the
140 /* serialized skel of this node's properties. NULL implies no change to
141 the properties, relative to WORKING/BASE as appropriate. */
144 /* relpaths of the conflict files. */
145 /* ### These columns will eventually be merged into conflict_data below. */
148 conflict_working TEXT,
151 /* if not NULL, this node is part of a changelist. */
154 /* ### need to determine values. "unknown" (no info), "admin" (they
155 ### used something like 'svn edit'), "noticed" (saw a mod while
156 ### scanning the filesystem). */
159 /* if a directory, serialized data for all of tree conflicts therein.
160 ### This column will eventually be merged into the conflict_data column,
161 ### but within the ACTUAL node of the tree conflict victim itself, rather
162 ### than the node of the tree conflict victim's parent directory. */
163 tree_conflict_data TEXT,
165 /* A skel containing the conflict details. */
168 /* Three columns containing the checksums of older, left and right conflict
169 texts. Stored in a column to allow storing them in the pristine store */
170 /* stsp: This is meant for text conflicts, right? What about property
171 conflicts? Why do we need these in a column to refer to the
172 pristine store? Can't we just parse the checksums from
173 conflict_data as well?
174 rhuijben: Because that won't allow triggers to handle refcounts.
175 We would have to scan all conflict skels before cleaning up the
176 a single file from the pristine stor */
177 older_checksum TEXT REFERENCES PRISTINE (checksum),
178 left_checksum TEXT REFERENCES PRISTINE (checksum),
179 right_checksum TEXT REFERENCES PRISTINE (checksum),
181 PRIMARY KEY (wc_id, local_relpath)
184 CREATE UNIQUE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, parent_relpath,
188 /* ------------------------------------------------------------------------- */
190 /* This table is a cache of information about repository locks. */
192 /* what repository location is locked */
193 repos_id INTEGER NOT NULL REFERENCES REPOSITORY (id),
194 repos_relpath TEXT NOT NULL,
196 /* Information about the lock. Note: these values are just caches from
197 the server, and are not authoritative. */
198 lock_token TEXT NOT NULL,
199 /* ### make the following fields NOT NULL ? */
202 lock_date INTEGER, /* an APR date/time (usec since 1970) */
204 PRIMARY KEY (repos_id, repos_relpath)
208 /* ------------------------------------------------------------------------- */
210 CREATE TABLE WORK_QUEUE (
211 /* Work items are identified by this value. */
212 id INTEGER PRIMARY KEY AUTOINCREMENT,
214 /* A serialized skel specifying the work item. */
219 /* ------------------------------------------------------------------------- */
221 CREATE TABLE WC_LOCK (
222 /* specifies the location of this node in the local filesystem */
223 wc_id INTEGER NOT NULL REFERENCES WCROOT (id),
224 local_dir_relpath TEXT NOT NULL,
226 locked_levels INTEGER NOT NULL DEFAULT -1,
228 PRIMARY KEY (wc_id, local_dir_relpath)
232 /* ------------------------------------------------------------------------- */
234 /* The NODES table describes the way WORKING nodes are layered on top of
235 BASE nodes and on top of other WORKING nodes, due to nested tree structure
236 changes. The layers are modelled using the "op_depth" column.
238 An 'operation depth' refers to the number of directory levels down from
239 the WC root at which a tree-change operation (delete, add?, copy, move)
240 was performed. A row's 'op_depth' does NOT refer to the depth of its own
241 'local_relpath', but rather to the depth of the nearest tree change that
244 The row with op_depth=0 for any given local relpath represents the "base"
245 node that is created and updated by checkout, update, switch and commit
246 post-processing. The row with the highest op_depth for a particular
247 local_relpath represents the working version. Any rows with intermediate
248 op_depth values are not normally visible to the user but may become
249 visible after reverting local changes.
251 This table contains full node descriptions for nodes in either the BASE
252 or WORKING trees as described in notes/wc-ng/design. Fields relate
253 both to BASE and WORKING trees, unless documented otherwise.
255 For illustration, with a scenario like this:
259 svn cp ^/moo foo # (1)
262 svn add foo/bar # (2)
264 , these are the NODES table rows for the path foo/bar:
266 (0) "BASE" ---> NODES (op_depth == 0)
267 (1) NODES (op_depth == 1)
268 (2) NODES (op_depth == 2)
270 0 is the original data for foo/bar before 'svn rm foo' (if it existed).
271 1 is the data for foo/bar copied in from ^/moo/bar.
272 2 is the to-be-committed data for foo/bar, created by 'svn add foo/bar'.
274 An 'svn revert foo/bar' would remove the NODES of (2).
278 /* Working copy location related fields */
280 wc_id INTEGER NOT NULL REFERENCES WCROOT (id),
281 local_relpath TEXT NOT NULL,
283 /* Contains the depth (= number of path segments) of the operation
284 modifying the working copy tree structure. All nodes below the root
285 of the operation (aka operation root, aka oproot) affected by the
286 operation will be assigned the same op_depth.
288 op_depth == 0 designates the initial checkout; the BASE tree.
291 op_depth INTEGER NOT NULL,
293 /* parent's local_relpath for aggregating children of a given parent.
294 this will be "" if the parent is the wcroot. Since a wcroot will
295 never have a WORKING node the parent_relpath will never be null,
296 except when op_depth == 0 and the node is a wcroot. */
300 /* Repository location fields */
302 /* When op_depth == 0, these fields refer to the repository location of the
303 BASE node, the location of the initial checkout.
305 When op_depth != 0, they indicate where this node was copied/moved from.
306 In this case, the fields are set for the root of the operation and for all
308 repos_id INTEGER REFERENCES REPOSITORY (id),
313 /* WC state fields */
315 /* The tree state of the node.
317 In case 'op_depth' is equal to 0, this node is part of the 'BASE'
318 tree. The 'BASE' represents pristine nodes that are in the
319 repository; it is obtained and modified by commands such as
320 checkout/update/switch.
322 In case 'op_depth' is greater than 0, this node is part of a
323 layer of working nodes. The 'WORKING' tree is obtained and
324 modified by commands like delete/copy/revert.
326 The 'BASE' and 'WORKING' trees use the same literal values for
327 the 'presence' but the meaning of each value can vary depending
330 normal: in the 'BASE' tree this is an ordinary node for which we
331 have full information. In the 'WORKING' tree it's an added or
332 copied node for which we have full information.
334 not-present: in the 'BASE' tree this is a node that is implied to
335 exist by the parent node, but is not present in the working
336 copy. Typically obtained by delete/commit, or by update to
337 revision in which the node does not exist. In the 'WORKING'
338 tree this is a copy of a 'not-present' node from the 'BASE'
339 tree, and it will be deleted on commit. Such a node cannot be
340 copied directly, but can be copied as a descendant.
342 incomplete: in the 'BASE' tree this is an ordinary node for which
343 we do not have full information. Only the name is guaranteed;
344 we may not have all its children, we may not have its checksum,
345 etc. In the 'WORKING' tree this is a copied node for which we
346 do not have the full information. This state is generally
347 obtained when an operation was interrupted.
349 base-deleted: not valid in 'BASE' tree. In the 'WORKING' tree
350 this represents a node that is deleted from the tree below the
351 current 'op_depth'. This state is badly named, it should be
352 something like 'deleted'.
354 server-excluded: in the 'BASE' tree this is a node that is excluded by
355 authz. The name of the node is known from the parent, but no
356 other information is available. Not valid in the 'WORKING'
357 tree as there is no way to commit such a node.
359 excluded: in the 'BASE' tree this node is administratively
360 excluded by the user (sparse WC). In the 'WORKING' tree this
361 is a copy of an excluded node from the 'BASE' tree. Such a
362 node cannot be copied directly but can be copied as a
365 presence TEXT NOT NULL,
367 /* ### JF: For an old-style move, "copyfrom" info stores its source, but a
368 new WC-NG "move" is intended to be a "true rename" so its copyfrom
369 revision is implicit, being in effect (new head - 1) at commit time.
370 For a (new) move, we need to store or deduce the copyfrom local-relpath;
371 perhaps add a column called "moved_from". */
373 /* Boolean value, specifying if this node was moved here (rather than just
374 copied). This is set on all the nodes in the moved tree. The source of
375 the move is implied by a different node with a moved_to column pointing
376 at the root node of the moved tree. */
379 /* If the underlying node was moved away (rather than just deleted), this
380 specifies the local_relpath of where the node was moved to.
381 This is set only on the root of a move, and is NULL for all children.
383 The op-depth of the moved-to node is not recorded. A moved_to path
384 always points at a node within the highest op-depth layer at the
385 destination. This invariant must be maintained by operations which
386 change existing move information. */
392 /* the kind of the new node. may be "unknown" if the node is not present. */
395 /* serialized skel of this node's properties (when presence is 'normal' or
396 'incomplete'); an empty skel or NULL indicates no properties. NULL if
397 we have no information about the properties (any other presence).
398 TODO: Choose & require a single representation for 'no properties'.
402 /* NULL depth means "default" (typically svn_depth_infinity) */
403 /* ### depth on WORKING? seems this is a BASE-only concept. how do
404 ### you do "files" on an added-directory? can't really ignore
406 /* ### maybe a WC-to-WC copy can retain a depth? */
409 /* The SHA-1 checksum of the pristine text, if this node is a file and was
410 moved here or copied here, else NULL. */
411 checksum TEXT REFERENCES PRISTINE (checksum),
413 /* for kind==symlink, this specifies the target. */
417 /* Last-Change fields */
419 /* If this node was moved here or copied here, then the following fields may
420 have information about their source node. changed_rev must be not-null
421 if this node has presence=="normal". changed_date and changed_author may
422 be null if the corresponding revprops are missing.
424 For an added or not-present node, these are null. */
425 changed_revision INTEGER,
426 changed_date INTEGER, /* an APR date/time (usec since 1970) */
430 /* Various cache fields */
432 /* The size in bytes of the working file when it had no local text
433 modifications. This means the size of the text when translated from
434 repository-normal format to working copy format with EOL style
435 translated and keywords expanded according to the properties in the
436 "properties" column of this row.
438 NULL if this node is not a file or if the size has not (yet) been
440 translated_size INTEGER,
442 /* The mod-time of the working file when it was last determined to be
443 logically unmodified relative to its base, taking account of keywords
444 and EOL style. This value is used in the change detection heuristic
445 used by the status command.
447 NULL if this node is not a file or if this info has not yet been
450 last_mod_time INTEGER, /* an APR date/time (usec since 1970) */
452 /* serialized skel of this node's dav-cache. could be NULL if the
453 node does not have any dav-cache. */
456 /* Is there a file external in this location. NULL if there
457 is no file external, otherwise '1' */
458 /* ### Originally we had a wc-1.0 like skel in this place, so we
460 ### In Subversion 1.7 we defined this column as TEXT, but Sqlite
461 ### only uses this information for deciding how to optimize
463 file_external INTEGER,
465 /* serialized skel of this node's inherited properties. NULL if this
466 is not the BASE of a WC root node. */
467 inherited_props BLOB,
469 PRIMARY KEY (wc_id, local_relpath, op_depth)
473 CREATE UNIQUE INDEX I_NODES_PARENT ON NODES (wc_id, parent_relpath,
474 local_relpath, op_depth);
475 /* I_NODES_MOVED is introduced in format 30 */
476 CREATE UNIQUE INDEX I_NODES_MOVED ON NODES (wc_id, moved_to, op_depth);
478 /* Many queries have to filter the nodes table to pick only that version
479 of each node with the highest (most "current") op_depth. This view
480 does the heavy lifting for such queries.
482 Note that this view includes a row for each and every path that is known
483 in the WC, including, for example, paths that were children of a base- or
484 lower-op-depth directory that has been replaced by something else in the
487 CREATE VIEW NODES_CURRENT AS
488 SELECT * FROM nodes AS n
489 WHERE op_depth = (SELECT MAX(op_depth) FROM nodes AS n2
490 WHERE n2.wc_id = n.wc_id
491 AND n2.local_relpath = n.local_relpath);
493 /* Many queries have to filter the nodes table to pick only that version
494 of each node with the BASE ("as checked out") op_depth. This view
495 does the heavy lifting for such queries. */
496 CREATE VIEW NODES_BASE AS
500 CREATE TRIGGER nodes_insert_trigger
501 AFTER INSERT ON nodes
502 WHEN NEW.checksum IS NOT NULL
504 UPDATE pristine SET refcount = refcount + 1
505 WHERE checksum = NEW.checksum;
508 CREATE TRIGGER nodes_delete_trigger
509 AFTER DELETE ON nodes
510 WHEN OLD.checksum IS NOT NULL
512 UPDATE pristine SET refcount = refcount - 1
513 WHERE checksum = OLD.checksum;
516 CREATE TRIGGER nodes_update_checksum_trigger
517 AFTER UPDATE OF checksum ON nodes
518 WHEN NEW.checksum IS NOT OLD.checksum
519 /* AND (NEW.checksum IS NOT NULL OR OLD.checksum IS NOT NULL) */
521 UPDATE pristine SET refcount = refcount + 1
522 WHERE checksum = NEW.checksum;
523 UPDATE pristine SET refcount = refcount - 1
524 WHERE checksum = OLD.checksum;
527 CREATE TABLE EXTERNALS (
528 /* Working copy location related fields (like NODES)*/
530 wc_id INTEGER NOT NULL REFERENCES WCROOT (id),
531 local_relpath TEXT NOT NULL,
533 /* The working copy root can't be recorded as an external in itself
534 so this will never be NULL. ### ATM only inserted, never queried */
535 parent_relpath TEXT NOT NULL,
537 /* Repository location fields */
538 repos_id INTEGER NOT NULL REFERENCES REPOSITORY (id),
540 /* Either MAP_NORMAL or MAP_EXCLUDED */
541 presence TEXT NOT NULL,
543 /* the kind of the external. */
546 /* The local relpath of the directory NODE defining this external
547 (Defaults to the parent directory of the file external after upgrade) */
548 def_local_relpath TEXT NOT NULL,
550 /* The url of the external as used in the definition */
551 def_repos_relpath TEXT NOT NULL,
553 /* The operational (peg) and node revision if this is a revision fixed
554 external; otherwise NULL. (Usually these will both have the same value) */
555 def_operational_revision TEXT,
558 PRIMARY KEY (wc_id, local_relpath)
561 CREATE UNIQUE INDEX I_EXTERNALS_DEFINED ON EXTERNALS (wc_id,
566 PRAGMA user_version =
567 -- define: SVN_WC__VERSION
571 /* ------------------------------------------------------------------------- */
572 /* This statement provides SQLite with the necessary information about our
573 indexes to make better decisions in the query planner.
575 For every interesting index this contains a number of rows where the
576 statistics are calculated for and then for every column in the index the
577 average number of rows with the same value in all columns left of this
578 column including the column itself.
580 See http://www.sqlite.org/fileformat2.html#stat1tab for more details.
582 The important thing here is that this tells Sqlite that the wc_id column
583 of the NODES and ACTUAL_NODE table is usually a single value, so queries
584 should use more than one column for index usage.
586 The current hints describe NODES+ACTUAL_NODE as a working copy with
587 8000 nodes in 1 a single working copy(=wc_id), 10 nodes per directory
588 and an average of 2 op-depth layers per node.
590 The number of integers must be number of index columns + 1, which is
591 verified via the test_schema_statistics() test.
593 -- STMT_INSTALL_SCHEMA_STATISTICS
594 ANALYZE sqlite_master; /* Creates empty sqlite_stat1 if necessary */
596 DELETE FROM sqlite_stat1
597 WHERE tbl in ('NODES', 'ACTUAL_NODE', 'LOCK', 'WC_LOCK', 'EXTERNALS');
599 INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES
600 ('NODES', 'sqlite_autoindex_NODES_1', '8000 8000 2 1');
601 INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES
602 ('NODES', 'I_NODES_PARENT', '8000 8000 10 2 1');
603 /* Tell a lie: We ignore that 99.9% of all moved_to values are NULL */
604 INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES
605 ('NODES', 'I_NODES_MOVED', '8000 8000 1 1');
607 INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES
608 ('ACTUAL_NODE', 'sqlite_autoindex_ACTUAL_NODE_1', '8000 8000 1');
609 INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES
610 ('ACTUAL_NODE', 'I_ACTUAL_PARENT', '8000 8000 10 1');
612 INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES
613 ('LOCK', 'sqlite_autoindex_LOCK_1', '100 100 1');
615 INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES
616 ('WC_LOCK', 'sqlite_autoindex_WC_LOCK_1', '100 100 1');
618 INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES
619 ('EXTERNALS','sqlite_autoindex_EXTERNALS_1', '100 100 1');
620 INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES
621 ('EXTERNALS','I_EXTERNALS_DEFINED', '100 100 3 1');
623 /* sqlite_autoindex_WORK_QUEUE_1 doesn't exist because WORK_QUEUE is
624 a INTEGER PRIMARY KEY AUTOINCREMENT table */
626 ANALYZE sqlite_master; /* Loads sqlite_stat1 data for query optimizer */
627 /* ------------------------------------------------------------------------- */
629 /* Format 30 creates a new NODES index for move information, and a new
630 PRISTINE index for the md5_checksum column. It also activates use of
631 skel-based conflict storage -- see notes/wc-ng/conflict-storage-2.0.
632 It also renames the "absent" presence to "server-excluded". */
633 -- STMT_UPGRADE_TO_30
634 CREATE UNIQUE INDEX IF NOT EXISTS I_NODES_MOVED
635 ON NODES (wc_id, moved_to, op_depth);
637 CREATE INDEX IF NOT EXISTS I_PRISTINE_MD5 ON PRISTINE (md5_checksum);
639 UPDATE nodes SET presence = "server-excluded" WHERE presence = "absent";
641 /* Just to be sure clear out file external skels from pre 1.7.0 development
642 working copies that were never updated by 1.7.0+ style clients */
643 UPDATE nodes SET file_external=1 WHERE file_external IS NOT NULL;
645 -- STMT_UPGRADE_30_SELECT_CONFLICT_SEPARATE
646 SELECT wc_id, local_relpath,
647 conflict_old, conflict_working, conflict_new, prop_reject, tree_conflict_data
649 WHERE conflict_old IS NOT NULL
650 OR conflict_working IS NOT NULL
651 OR conflict_new IS NOT NULL
652 OR prop_reject IS NOT NULL
653 OR tree_conflict_data IS NOT NULL
654 ORDER by wc_id, local_relpath
656 -- STMT_UPGRADE_30_SET_CONFLICT
657 UPDATE actual_node SET conflict_data = ?3, conflict_old = NULL,
658 conflict_working = NULL, conflict_new = NULL, prop_reject = NULL,
659 tree_conflict_data = NULL
660 WHERE wc_id = ?1 and local_relpath = ?2
662 /* ------------------------------------------------------------------------- */
664 /* Format 31 adds the inherited_props column to the NODES table. C code then
665 initializes the update/switch roots to make sure future updates fetch the
666 inherited properties */
667 -- STMT_UPGRADE_TO_31
668 ALTER TABLE NODES ADD COLUMN inherited_props BLOB;
670 DROP INDEX IF EXISTS I_ACTUAL_CHANGELIST;
671 DROP INDEX IF EXISTS I_EXTERNALS_PARENT;
673 DROP INDEX I_NODES_PARENT;
674 CREATE UNIQUE INDEX I_NODES_PARENT ON NODES (wc_id, parent_relpath,
675 local_relpath, op_depth);
677 DROP INDEX I_ACTUAL_PARENT;
678 CREATE UNIQUE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, parent_relpath,
681 PRAGMA user_version = 31;
683 -- STMT_UPGRADE_31_SELECT_WCROOT_NODES
684 /* Select all base nodes which are the root of a WC, including
685 switched subtrees, but excluding those which map to the root
688 ### IPROPS: Is this query horribly inefficient? Quite likely,
689 ### but it only runs during an upgrade, so do we care? */
690 SELECT l.wc_id, l.local_relpath FROM nodes as l
691 LEFT OUTER JOIN nodes as r
693 AND r.local_relpath = l.parent_relpath
696 AND l.repos_path != ''
697 AND ((l.repos_id IS NOT r.repos_id)
698 OR (l.repos_path IS NOT RELPATH_SKIP_JOIN(r.local_relpath, r.repos_path, l.local_relpath)))
701 /* ------------------------------------------------------------------------- */
703 /* -- STMT_UPGRADE_TO_32
704 PRAGMA user_version = 32; */
707 /* ------------------------------------------------------------------------- */
709 /* Format 99 drops all columns not needed due to previous format upgrades.
710 Before we release 1.7, these statements will be pulled into a format bump
711 and all the tables will be cleaned up. We don't know what that format
712 number will be, however, so we're just marking it as 99 for now. */
715 /* TODO: Un-confuse *_revision column names in the EXTERNALS table to
716 "-r<operative> foo@<peg>", as suggested by the patch attached to
717 http://svn.haxx.se/dev/archive-2011-09/0478.shtml */
718 /* TODO: Remove column parent_relpath from EXTERNALS. We're not using it and
719 never will. It's not interesting like in the NODES table: the external's
720 parent path may be *anything*: unversioned, "behind" a another WC... */
722 /* Now "drop" the tree_conflict_data column from actual_node. */
723 CREATE TABLE ACTUAL_NODE_BACKUP (
724 wc_id INTEGER NOT NULL,
725 local_relpath TEXT NOT NULL,
730 conflict_working TEXT,
736 INSERT INTO ACTUAL_NODE_BACKUP SELECT
737 wc_id, local_relpath, parent_relpath, properties, conflict_old,
738 conflict_new, conflict_working, prop_reject, changelist, text_mod
741 DROP TABLE ACTUAL_NODE;
743 CREATE TABLE ACTUAL_NODE (
744 wc_id INTEGER NOT NULL REFERENCES WCROOT (id),
745 local_relpath TEXT NOT NULL,
750 conflict_working TEXT,
755 PRIMARY KEY (wc_id, local_relpath)
758 CREATE UNIQUE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, parent_relpath,
761 INSERT INTO ACTUAL_NODE SELECT
762 wc_id, local_relpath, parent_relpath, properties, conflict_old,
763 conflict_new, conflict_working, prop_reject, changelist, text_mod
764 FROM ACTUAL_NODE_BACKUP;
766 DROP TABLE ACTUAL_NODE_BACKUP;
768 /* Note: Other differences between the schemas of an upgraded and a
771 * While format 22 was current, "NOT NULL" was added to the
772 * columns PRISTINE.size and PRISTINE.md5_checksum. The format was not
773 * bumped because it is a forward- and backward-compatible change.
775 * While format 23 was current, "REFERENCES PRISTINE" was added to the
776 * columns ACTUAL_NODE.older_checksum, ACTUAL_NODE.left_checksum,
777 * ACTUAL_NODE.right_checksum, NODES.checksum.
779 * The "NODES_BASE" view was originally implemented with a more complex (but
780 * functionally equivalent) statement using a 'JOIN'. WCs that were created
781 * at or upgraded to format 26 before it was changed will still have the old