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 PRAGMA user_version =
233 -- define: SVN_WC__VERSION
237 /* ------------------------------------------------------------------------- */
239 /* The NODES table describes the way WORKING nodes are layered on top of
240 BASE nodes and on top of other WORKING nodes, due to nested tree structure
241 changes. The layers are modelled using the "op_depth" column.
243 An 'operation depth' refers to the number of directory levels down from
244 the WC root at which a tree-change operation (delete, add?, copy, move)
245 was performed. A row's 'op_depth' does NOT refer to the depth of its own
246 'local_relpath', but rather to the depth of the nearest tree change that
249 The row with op_depth=0 for any given local relpath represents the "base"
250 node that is created and updated by checkout, update, switch and commit
251 post-processing. The row with the highest op_depth for a particular
252 local_relpath represents the working version. Any rows with intermediate
253 op_depth values are not normally visible to the user but may become
254 visible after reverting local changes.
256 This table contains full node descriptions for nodes in either the BASE
257 or WORKING trees as described in notes/wc-ng/design. Fields relate
258 both to BASE and WORKING trees, unless documented otherwise.
260 For illustration, with a scenario like this:
264 svn cp ^/moo foo # (1)
267 svn add foo/bar # (2)
269 , these are the NODES table rows for the path foo/bar:
271 (0) "BASE" ---> NODES (op_depth == 0)
272 (1) NODES (op_depth == 1)
273 (2) NODES (op_depth == 2)
275 0 is the original data for foo/bar before 'svn rm foo' (if it existed).
276 1 is the data for foo/bar copied in from ^/moo/bar.
277 2 is the to-be-committed data for foo/bar, created by 'svn add foo/bar'.
279 An 'svn revert foo/bar' would remove the NODES of (2).
284 /* Working copy location related fields */
286 wc_id INTEGER NOT NULL REFERENCES WCROOT (id),
287 local_relpath TEXT NOT NULL,
289 /* Contains the depth (= number of path segments) of the operation
290 modifying the working copy tree structure. All nodes below the root
291 of the operation (aka operation root, aka oproot) affected by the
292 operation will be assigned the same op_depth.
294 op_depth == 0 designates the initial checkout; the BASE tree.
297 op_depth INTEGER NOT NULL,
299 /* parent's local_relpath for aggregating children of a given parent.
300 this will be "" if the parent is the wcroot. Since a wcroot will
301 never have a WORKING node the parent_relpath will never be null,
302 except when op_depth == 0 and the node is a wcroot. */
306 /* Repository location fields */
308 /* When op_depth == 0, these fields refer to the repository location of the
309 BASE node, the location of the initial checkout.
311 When op_depth != 0, they indicate where this node was copied/moved from.
312 In this case, the fields are set for the root of the operation and for all
314 repos_id INTEGER REFERENCES REPOSITORY (id),
319 /* WC state fields */
321 /* The tree state of the node.
323 In case 'op_depth' is equal to 0, this node is part of the 'BASE'
324 tree. The 'BASE' represents pristine nodes that are in the
325 repository; it is obtained and modified by commands such as
326 checkout/update/switch.
328 In case 'op_depth' is greater than 0, this node is part of a
329 layer of working nodes. The 'WORKING' tree is obtained and
330 modified by commands like delete/copy/revert.
332 The 'BASE' and 'WORKING' trees use the same literal values for
333 the 'presence' but the meaning of each value can vary depending
336 normal: in the 'BASE' tree this is an ordinary node for which we
337 have full information. In the 'WORKING' tree it's an added or
338 copied node for which we have full information.
340 not-present: in the 'BASE' tree this is a node that is implied to
341 exist by the parent node, but is not present in the working
342 copy. Typically obtained by delete/commit, or by update to
343 revision in which the node does not exist. In the 'WORKING'
344 tree this is a copy of a 'not-present' node from the 'BASE'
345 tree, and it will be deleted on commit. Such a node cannot be
346 copied directly, but can be copied as a descendant.
348 incomplete: in the 'BASE' tree this is an ordinary node for which
349 we do not have full information. Only the name is guaranteed;
350 we may not have all its children, we may not have its checksum,
351 etc. In the 'WORKING' tree this is a copied node for which we
352 do not have the full information. This state is generally
353 obtained when an operation was interrupted.
355 base-deleted: not valid in 'BASE' tree. In the 'WORKING' tree
356 this represents a node that is deleted from the tree below the
357 current 'op_depth'. This state is badly named, it should be
358 something like 'deleted'.
360 server-excluded: in the 'BASE' tree this is a node that is excluded by
361 authz. The name of the node is known from the parent, but no
362 other information is available. Not valid in the 'WORKING'
363 tree as there is no way to commit such a node.
365 excluded: in the 'BASE' tree this node is administratively
366 excluded by the user (sparse WC). In the 'WORKING' tree this
367 is a copy of an excluded node from the 'BASE' tree. Such a
368 node cannot be copied directly but can be copied as a
371 presence TEXT NOT NULL,
373 /* ### JF: For an old-style move, "copyfrom" info stores its source, but a
374 new WC-NG "move" is intended to be a "true rename" so its copyfrom
375 revision is implicit, being in effect (new head - 1) at commit time.
376 For a (new) move, we need to store or deduce the copyfrom local-relpath;
377 perhaps add a column called "moved_from". */
379 /* Boolean value, specifying if this node was moved here (rather than just
380 copied). This is set on all the nodes in the moved tree. The source of
381 the move is implied by a different node with a moved_to column pointing
382 at the root node of the moved tree. */
385 /* If the underlying node was moved away (rather than just deleted), this
386 specifies the local_relpath of where the node was moved to.
387 This is set only on the root of a move, and is NULL for all children.
389 The op-depth of the moved-to node is not recorded. A moved_to path
390 always points at a node within the highest op-depth layer at the
391 destination. This invariant must be maintained by operations which
392 change existing move information. */
398 /* the kind of the new node. may be "unknown" if the node is not present. */
401 /* serialized skel of this node's properties (when presence is 'normal' or
402 'incomplete'); an empty skel or NULL indicates no properties. NULL if
403 we have no information about the properties (any other presence).
404 TODO: Choose & require a single representation for 'no properties'.
408 /* NULL depth means "default" (typically svn_depth_infinity) */
409 /* ### depth on WORKING? seems this is a BASE-only concept. how do
410 ### you do "files" on an added-directory? can't really ignore
412 /* ### maybe a WC-to-WC copy can retain a depth? */
415 /* The SHA-1 checksum of the pristine text, if this node is a file and was
416 moved here or copied here, else NULL. */
417 checksum TEXT REFERENCES PRISTINE (checksum),
419 /* for kind==symlink, this specifies the target. */
423 /* Last-Change fields */
425 /* If this node was moved here or copied here, then the following fields may
426 have information about their source node. changed_rev must be not-null
427 if this node has presence=="normal". changed_date and changed_author may
428 be null if the corresponding revprops are missing.
430 For an added or not-present node, these are null. */
431 changed_revision INTEGER,
432 changed_date INTEGER, /* an APR date/time (usec since 1970) */
436 /* Various cache fields */
438 /* The size in bytes of the working file when it had no local text
439 modifications. This means the size of the text when translated from
440 repository-normal format to working copy format with EOL style
441 translated and keywords expanded according to the properties in the
442 "properties" column of this row.
444 NULL if this node is not a file or if the size has not (yet) been
446 translated_size INTEGER,
448 /* The mod-time of the working file when it was last determined to be
449 logically unmodified relative to its base, taking account of keywords
450 and EOL style. This value is used in the change detection heuristic
451 used by the status command.
453 NULL if this node is not a file or if this info has not yet been
456 last_mod_time INTEGER, /* an APR date/time (usec since 1970) */
458 /* serialized skel of this node's dav-cache. could be NULL if the
459 node does not have any dav-cache. */
462 /* Is there a file external in this location. NULL if there
463 is no file external, otherwise '1' */
464 /* ### Originally we had a wc-1.0 like skel in this place, so we
466 ### In Subversion 1.7 we defined this column as TEXT, but Sqlite
467 ### only uses this information for deciding how to optimize
469 file_external INTEGER,
471 /* serialized skel of this node's inherited properties. NULL if this
472 is not the BASE of a WC root node. */
473 inherited_props BLOB,
475 PRIMARY KEY (wc_id, local_relpath, op_depth)
479 CREATE UNIQUE INDEX I_NODES_PARENT ON NODES (wc_id, parent_relpath,
480 local_relpath, op_depth);
481 /* I_NODES_MOVED is introduced in format 30 */
482 CREATE UNIQUE INDEX I_NODES_MOVED ON NODES (wc_id, moved_to, op_depth);
484 /* Many queries have to filter the nodes table to pick only that version
485 of each node with the highest (most "current") op_depth. This view
486 does the heavy lifting for such queries.
488 Note that this view includes a row for each and every path that is known
489 in the WC, including, for example, paths that were children of a base- or
490 lower-op-depth directory that has been replaced by something else in the
493 CREATE VIEW NODES_CURRENT AS
494 SELECT * FROM nodes AS n
495 WHERE op_depth = (SELECT MAX(op_depth) FROM nodes AS n2
496 WHERE n2.wc_id = n.wc_id
497 AND n2.local_relpath = n.local_relpath);
499 /* Many queries have to filter the nodes table to pick only that version
500 of each node with the BASE ("as checked out") op_depth. This view
501 does the heavy lifting for such queries. */
502 CREATE VIEW NODES_BASE AS
506 -- STMT_CREATE_NODES_TRIGGERS
508 CREATE TRIGGER nodes_insert_trigger
509 AFTER INSERT ON nodes
510 WHEN NEW.checksum IS NOT NULL
512 UPDATE pristine SET refcount = refcount + 1
513 WHERE checksum = NEW.checksum;
516 CREATE TRIGGER nodes_delete_trigger
517 AFTER DELETE ON nodes
518 WHEN OLD.checksum IS NOT NULL
520 UPDATE pristine SET refcount = refcount - 1
521 WHERE checksum = OLD.checksum;
524 CREATE TRIGGER nodes_update_checksum_trigger
525 AFTER UPDATE OF checksum ON nodes
526 WHEN NEW.checksum IS NOT OLD.checksum
527 /* AND (NEW.checksum IS NOT NULL OR OLD.checksum IS NOT NULL) */
529 UPDATE pristine SET refcount = refcount + 1
530 WHERE checksum = NEW.checksum;
531 UPDATE pristine SET refcount = refcount - 1
532 WHERE checksum = OLD.checksum;
535 -- STMT_CREATE_EXTERNALS
537 CREATE TABLE EXTERNALS (
538 /* Working copy location related fields (like NODES)*/
540 wc_id INTEGER NOT NULL REFERENCES WCROOT (id),
541 local_relpath TEXT NOT NULL,
543 /* The working copy root can't be recorded as an external in itself
544 so this will never be NULL. ### ATM only inserted, never queried */
545 parent_relpath TEXT NOT NULL,
547 /* Repository location fields */
548 repos_id INTEGER NOT NULL REFERENCES REPOSITORY (id),
550 /* Either MAP_NORMAL or MAP_EXCLUDED */
551 presence TEXT NOT NULL,
553 /* the kind of the external. */
556 /* The local relpath of the directory NODE defining this external
557 (Defaults to the parent directory of the file external after upgrade) */
558 def_local_relpath TEXT NOT NULL,
560 /* The url of the external as used in the definition */
561 def_repos_relpath TEXT NOT NULL,
563 /* The operational (peg) and node revision if this is a revision fixed
564 external; otherwise NULL. (Usually these will both have the same value) */
565 def_operational_revision TEXT,
568 PRIMARY KEY (wc_id, local_relpath)
571 CREATE UNIQUE INDEX I_EXTERNALS_DEFINED ON EXTERNALS (wc_id,
575 /* ------------------------------------------------------------------------- */
576 /* This statement provides SQLite with the necessary information about our
577 indexes to make better decisions in the query planner.
579 For every interesting index this contains a number of rows where the
580 statistics are calculated for and then for every column in the index the
581 average number of rows with the same value in all columns left of this
582 column including the column itself.
584 See http://www.sqlite.org/fileformat2.html#stat1tab for more details.
586 The important thing here is that this tells Sqlite that the wc_id column
587 of the NODES and ACTUAL_NODE table is usually a single value, so queries
588 should use more than one column for index usage.
590 The current hints describe NODES+ACTUAL_NODE as a working copy with
591 8000 nodes in 1 a single working copy(=wc_id), 10 nodes per directory
592 and an average of 2 op-depth layers per node.
594 The number of integers must be number of index columns + 1, which is
595 verified via the test_schema_statistics() test.
597 -- STMT_INSTALL_SCHEMA_STATISTICS
598 ANALYZE sqlite_master; /* Creates empty sqlite_stat1 if necessary */
600 DELETE FROM sqlite_stat1
601 WHERE tbl in ('NODES', 'ACTUAL_NODE', 'LOCK', 'WC_LOCK', 'EXTERNALS');
603 INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES
604 ('NODES', 'sqlite_autoindex_NODES_1', '8000 8000 2 1');
605 INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES
606 ('NODES', 'I_NODES_PARENT', '8000 8000 10 2 1');
607 /* Tell a lie: We ignore that 99.9% of all moved_to values are NULL */
608 INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES
609 ('NODES', 'I_NODES_MOVED', '8000 8000 1 1');
611 INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES
612 ('ACTUAL_NODE', 'sqlite_autoindex_ACTUAL_NODE_1', '8000 8000 1');
613 INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES
614 ('ACTUAL_NODE', 'I_ACTUAL_PARENT', '8000 8000 10 1');
616 INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES
617 ('LOCK', 'sqlite_autoindex_LOCK_1', '100 100 1');
619 INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES
620 ('WC_LOCK', 'sqlite_autoindex_WC_LOCK_1', '100 100 1');
622 INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES
623 ('EXTERNALS','sqlite_autoindex_EXTERNALS_1', '100 100 1');
624 INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES
625 ('EXTERNALS','I_EXTERNALS_DEFINED', '100 100 3 1');
627 /* sqlite_autoindex_WORK_QUEUE_1 doesn't exist because WORK_QUEUE is
628 a INTEGER PRIMARY KEY AUTOINCREMENT table */
630 ANALYZE sqlite_master; /* Loads sqlite_stat1 data for query optimizer */
631 /* ------------------------------------------------------------------------- */
633 /* Format 20 introduces NODES and removes BASE_NODE and WORKING_NODE */
635 -- STMT_UPGRADE_TO_20
637 UPDATE BASE_NODE SET checksum = (SELECT checksum FROM pristine
638 WHERE md5_checksum = BASE_NODE.checksum)
639 WHERE EXISTS (SELECT 1 FROM pristine WHERE md5_checksum = BASE_NODE.checksum);
641 UPDATE WORKING_NODE SET checksum = (SELECT checksum FROM pristine
642 WHERE md5_checksum = WORKING_NODE.checksum)
643 WHERE EXISTS (SELECT 1 FROM pristine
644 WHERE md5_checksum = WORKING_NODE.checksum);
647 wc_id, local_relpath, op_depth, parent_relpath,
648 repos_id, repos_path, revision,
649 presence, depth, moved_here, moved_to, kind,
650 changed_revision, changed_date, changed_author,
651 checksum, properties, translated_size, last_mod_time,
652 dav_cache, symlink_target, file_external )
653 SELECT wc_id, local_relpath, 0 /*op_depth*/, parent_relpath,
654 repos_id, repos_relpath, revnum,
655 presence, depth, NULL /*moved_here*/, NULL /*moved_to*/, kind,
656 changed_rev, changed_date, changed_author,
657 checksum, properties, translated_size, last_mod_time,
658 dav_cache, symlink_target, file_external
661 wc_id, local_relpath, op_depth, parent_relpath,
662 repos_id, repos_path, revision,
663 presence, depth, moved_here, moved_to, kind,
664 changed_revision, changed_date, changed_author,
665 checksum, properties, translated_size, last_mod_time,
666 dav_cache, symlink_target, file_external )
667 SELECT wc_id, local_relpath, 2 /*op_depth*/, parent_relpath,
668 copyfrom_repos_id, copyfrom_repos_path, copyfrom_revnum,
669 presence, depth, NULL /*moved_here*/, NULL /*moved_to*/, kind,
670 changed_rev, changed_date, changed_author,
671 checksum, properties, translated_size, last_mod_time,
672 NULL /*dav_cache*/, symlink_target, NULL /*file_external*/
675 DROP TABLE BASE_NODE;
676 DROP TABLE WORKING_NODE;
678 PRAGMA user_version = 20;
681 /* ------------------------------------------------------------------------- */
683 /* Format 21 involves no schema changes, it moves the tree conflict victim
684 information to victime nodes, rather than parents. */
686 -- STMT_UPGRADE_TO_21
687 PRAGMA user_version = 21;
689 /* For format 21 bump code */
690 -- STMT_UPGRADE_21_SELECT_OLD_TREE_CONFLICT
691 SELECT wc_id, local_relpath, tree_conflict_data
693 WHERE tree_conflict_data IS NOT NULL
695 /* For format 21 bump code */
696 -- STMT_UPGRADE_21_ERASE_OLD_CONFLICTS
697 UPDATE actual_node SET tree_conflict_data = NULL
699 /* ------------------------------------------------------------------------- */
701 /* Format 22 simply moves the tree conflict information from the conflict_data
702 column to the tree_conflict_data column. */
704 -- STMT_UPGRADE_TO_22
705 UPDATE actual_node SET tree_conflict_data = conflict_data;
706 UPDATE actual_node SET conflict_data = NULL;
708 PRAGMA user_version = 22;
711 /* ------------------------------------------------------------------------- */
713 /* Format 23 involves no schema changes, it introduces multi-layer
714 op-depth processing for NODES. */
716 -- STMT_UPGRADE_TO_23
717 PRAGMA user_version = 23;
719 -- STMT_UPGRADE_23_HAS_WORKING_NODES
720 SELECT 1 FROM nodes WHERE op_depth > 0
723 /* ------------------------------------------------------------------------- */
725 /* Format 24 involves no schema changes; it starts using the pristine
726 table's refcount column correctly. */
728 -- STMT_UPGRADE_TO_24
729 UPDATE pristine SET refcount =
730 (SELECT COUNT(*) FROM nodes
731 WHERE checksum = pristine.checksum /*OR checksum = pristine.md5_checksum*/);
733 PRAGMA user_version = 24;
735 /* ------------------------------------------------------------------------- */
737 /* Format 25 introduces the NODES_CURRENT view. */
739 -- STMT_UPGRADE_TO_25
740 DROP VIEW IF EXISTS NODES_CURRENT;
741 CREATE VIEW NODES_CURRENT AS
743 JOIN (SELECT wc_id, local_relpath, MAX(op_depth) AS op_depth FROM nodes
744 GROUP BY wc_id, local_relpath) AS filter
745 ON nodes.wc_id = filter.wc_id
746 AND nodes.local_relpath = filter.local_relpath
747 AND nodes.op_depth = filter.op_depth;
749 PRAGMA user_version = 25;
751 /* ------------------------------------------------------------------------- */
753 /* Format 26 introduces the NODES_BASE view. */
755 -- STMT_UPGRADE_TO_26
756 DROP VIEW IF EXISTS NODES_BASE;
757 CREATE VIEW NODES_BASE AS
761 PRAGMA user_version = 26;
763 /* ------------------------------------------------------------------------- */
765 /* Format 27 involves no schema changes, it introduces stores
766 conflict files as relpaths rather than names in ACTUAL_NODE. */
768 -- STMT_UPGRADE_TO_27
769 PRAGMA user_version = 27;
771 /* For format 27 bump code */
772 -- STMT_UPGRADE_27_HAS_ACTUAL_NODES_CONFLICTS
773 SELECT 1 FROM actual_node
774 WHERE NOT ((prop_reject IS NULL) AND (conflict_old IS NULL)
775 AND (conflict_new IS NULL) AND (conflict_working IS NULL)
776 AND (tree_conflict_data IS NULL))
780 /* ------------------------------------------------------------------------- */
782 /* Format 28 involves no schema changes, it only converts MD5 pristine
783 references to SHA1. */
785 -- STMT_UPGRADE_TO_28
787 UPDATE NODES SET checksum = (SELECT checksum FROM pristine
788 WHERE md5_checksum = nodes.checksum)
789 WHERE EXISTS (SELECT 1 FROM pristine WHERE md5_checksum = nodes.checksum);
791 PRAGMA user_version = 28;
793 /* ------------------------------------------------------------------------- */
795 /* Format 29 introduces the EXTERNALS table (See STMT_CREATE_TRIGGERS) and
796 optimizes a few trigger definitions. ... */
798 -- STMT_UPGRADE_TO_29
800 DROP TRIGGER IF EXISTS nodes_update_checksum_trigger;
801 DROP TRIGGER IF EXISTS nodes_insert_trigger;
802 DROP TRIGGER IF EXISTS nodes_delete_trigger;
804 CREATE TRIGGER nodes_update_checksum_trigger
805 AFTER UPDATE OF checksum ON nodes
806 WHEN NEW.checksum IS NOT OLD.checksum
807 /* AND (NEW.checksum IS NOT NULL OR OLD.checksum IS NOT NULL) */
809 UPDATE pristine SET refcount = refcount + 1
810 WHERE checksum = NEW.checksum;
811 UPDATE pristine SET refcount = refcount - 1
812 WHERE checksum = OLD.checksum;
815 CREATE TRIGGER nodes_insert_trigger
816 AFTER INSERT ON nodes
817 WHEN NEW.checksum IS NOT NULL
819 UPDATE pristine SET refcount = refcount + 1
820 WHERE checksum = NEW.checksum;
823 CREATE TRIGGER nodes_delete_trigger
824 AFTER DELETE ON nodes
825 WHEN OLD.checksum IS NOT NULL
827 UPDATE pristine SET refcount = refcount - 1
828 WHERE checksum = OLD.checksum;
831 PRAGMA user_version = 29;
833 /* ------------------------------------------------------------------------- */
835 /* Format 30 creates a new NODES index for move information, and a new
836 PRISTINE index for the md5_checksum column. It also activates use of
837 skel-based conflict storage -- see notes/wc-ng/conflict-storage-2.0.
838 It also renames the "absent" presence to "server-excluded". */
839 -- STMT_UPGRADE_TO_30
840 CREATE UNIQUE INDEX IF NOT EXISTS I_NODES_MOVED
841 ON NODES (wc_id, moved_to, op_depth);
843 CREATE INDEX IF NOT EXISTS I_PRISTINE_MD5 ON PRISTINE (md5_checksum);
845 UPDATE nodes SET presence = "server-excluded" WHERE presence = "absent";
847 /* Just to be sure clear out file external skels from pre 1.7.0 development
848 working copies that were never updated by 1.7.0+ style clients */
849 UPDATE nodes SET file_external=1 WHERE file_external IS NOT NULL;
851 -- STMT_UPGRADE_30_SELECT_CONFLICT_SEPARATE
852 SELECT wc_id, local_relpath,
853 conflict_old, conflict_working, conflict_new, prop_reject, tree_conflict_data
855 WHERE conflict_old IS NOT NULL
856 OR conflict_working IS NOT NULL
857 OR conflict_new IS NOT NULL
858 OR prop_reject IS NOT NULL
859 OR tree_conflict_data IS NOT NULL
860 ORDER by wc_id, local_relpath
862 -- STMT_UPGRADE_30_SET_CONFLICT
863 UPDATE actual_node SET conflict_data = ?3, conflict_old = NULL,
864 conflict_working = NULL, conflict_new = NULL, prop_reject = NULL,
865 tree_conflict_data = NULL
866 WHERE wc_id = ?1 and local_relpath = ?2
868 /* ------------------------------------------------------------------------- */
870 /* Format 31 adds the inherited_props column to the NODES table. C code then
871 initializes the update/switch roots to make sure future updates fetch the
872 inherited properties */
873 -- STMT_UPGRADE_TO_31_ALTER_TABLE
874 ALTER TABLE NODES ADD COLUMN inherited_props BLOB;
875 -- STMT_UPGRADE_TO_31_FINALIZE
876 DROP INDEX IF EXISTS I_ACTUAL_CHANGELIST;
877 DROP INDEX IF EXISTS I_EXTERNALS_PARENT;
879 DROP INDEX I_NODES_PARENT;
880 CREATE UNIQUE INDEX I_NODES_PARENT ON NODES (wc_id, parent_relpath,
881 local_relpath, op_depth);
883 DROP INDEX I_ACTUAL_PARENT;
884 CREATE UNIQUE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, parent_relpath,
887 PRAGMA user_version = 31;
889 -- STMT_UPGRADE_31_SELECT_WCROOT_NODES
890 /* Select all base nodes which are the root of a WC, including
891 switched subtrees, but excluding those which map to the root
894 ### IPROPS: Is this query horribly inefficient? Quite likely,
895 ### but it only runs during an upgrade, so do we care? */
896 SELECT l.wc_id, l.local_relpath FROM nodes as l
897 LEFT OUTER JOIN nodes as r
899 AND r.local_relpath = l.parent_relpath
902 AND l.repos_path != ''
903 AND ((l.repos_id IS NOT r.repos_id)
904 OR (l.repos_path IS NOT RELPATH_SKIP_JOIN(r.local_relpath, r.repos_path, l.local_relpath)))
907 /* ------------------------------------------------------------------------- */
909 -- STMT_UPGRADE_TO_32
911 /* Drop old index. ### Remove this part from the upgrade to 31 once bumped */
912 DROP INDEX IF EXISTS I_ACTUAL_CHANGELIST;
913 DROP INDEX IF EXISTS I_EXTERNALS_PARENT;
914 CREATE INDEX I_EXTERNALS_PARENT ON EXTERNALS (wc_id, parent_relpath);
916 DROP INDEX I_NODES_PARENT;
917 CREATE UNIQUE INDEX I_NODES_PARENT ON NODES (wc_id, parent_relpath,
918 local_relpath, op_depth);
920 DROP INDEX I_ACTUAL_PARENT;
921 CREATE UNIQUE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, parent_relpath,
924 /* ------------------------------------------------------------------------- */
926 /* Format YYY introduces new handling for conflict information. */
930 /* ------------------------------------------------------------------------- */
932 /* Format 99 drops all columns not needed due to previous format upgrades.
933 Before we release 1.7, these statements will be pulled into a format bump
934 and all the tables will be cleaned up. We don't know what that format
935 number will be, however, so we're just marking it as 99 for now. */
938 /* TODO: Un-confuse *_revision column names in the EXTERNALS table to
939 "-r<operative> foo@<peg>", as suggested by the patch attached to
940 http://svn.haxx.se/dev/archive-2011-09/0478.shtml */
941 /* TODO: Remove column parent_relpath from EXTERNALS. We're not using it and
942 never will. It's not interesting like in the NODES table: the external's
943 parent path may be *anything*: unversioned, "behind" a another WC... */
945 /* Now "drop" the tree_conflict_data column from actual_node. */
946 CREATE TABLE ACTUAL_NODE_BACKUP (
947 wc_id INTEGER NOT NULL,
948 local_relpath TEXT NOT NULL,
953 conflict_working TEXT,
959 INSERT INTO ACTUAL_NODE_BACKUP SELECT
960 wc_id, local_relpath, parent_relpath, properties, conflict_old,
961 conflict_new, conflict_working, prop_reject, changelist, text_mod
964 DROP TABLE ACTUAL_NODE;
966 CREATE TABLE ACTUAL_NODE (
967 wc_id INTEGER NOT NULL REFERENCES WCROOT (id),
968 local_relpath TEXT NOT NULL,
973 conflict_working TEXT,
978 PRIMARY KEY (wc_id, local_relpath)
981 CREATE UNIQUE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, parent_relpath,
984 INSERT INTO ACTUAL_NODE SELECT
985 wc_id, local_relpath, parent_relpath, properties, conflict_old,
986 conflict_new, conflict_working, prop_reject, changelist, text_mod
987 FROM ACTUAL_NODE_BACKUP;
989 DROP TABLE ACTUAL_NODE_BACKUP;
991 /* Note: Other differences between the schemas of an upgraded and a
994 * While format 22 was current, "NOT NULL" was added to the
995 * columns PRISTINE.size and PRISTINE.md5_checksum. The format was not
996 * bumped because it is a forward- and backward-compatible change.
998 * While format 23 was current, "REFERENCES PRISTINE" was added to the
999 * columns ACTUAL_NODE.older_checksum, ACTUAL_NODE.left_checksum,
1000 * ACTUAL_NODE.right_checksum, NODES.checksum.
1002 * The "NODES_BASE" view was originally implemented with a more complex (but
1003 * functionally equivalent) statement using a 'JOIN'. WCs that were created
1004 * at or upgraded to format 26 before it was changed will still have the old