wc-checks.sql revision 302408
1100894Srwatson/* wc-checks.sql -- trigger-based checks for the wc-metadata database. 2100894Srwatson * This is intended for use with SQLite 3 3100894Srwatson * 4100894Srwatson * ==================================================================== 5100894Srwatson * Licensed to the Apache Software Foundation (ASF) under one 6100894Srwatson * or more contributor license agreements. See the NOTICE file 7100894Srwatson * distributed with this work for additional information 8100894Srwatson * regarding copyright ownership. The ASF licenses this file 9100894Srwatson * to you under the Apache License, Version 2.0 (the 10100894Srwatson * "License"); you may not use this file except in compliance 11100894Srwatson * with the License. You may obtain a copy of the License at 12100894Srwatson * 13100894Srwatson * http://www.apache.org/licenses/LICENSE-2.0 14100894Srwatson * 15100894Srwatson * Unless required by applicable law or agreed to in writing, 16100894Srwatson * software distributed under the License is distributed on an 17100894Srwatson * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 18100894Srwatson * KIND, either express or implied. See the License for the 19100894Srwatson * specific language governing permissions and limitations 20100894Srwatson * under the License. 21100894Srwatson * ==================================================================== 22100894Srwatson */ 23100894Srwatson 24100894Srwatson 25100894Srwatson-- STMT_VERIFICATION_TRIGGERS 26100894Srwatson 27100894Srwatson/* ------------------------------------------------------------------------- */ 28100894Srwatson 29100894SrwatsonCREATE TEMPORARY TRIGGER no_repository_updates BEFORE UPDATE ON repository 30100894SrwatsonBEGIN 31100894Srwatson SELECT RAISE(FAIL, 'Updates to REPOSITORY are not allowed.'); 32100894SrwatsonEND; 33100894Srwatson 34100894Srwatson/* ------------------------------------------------------------------------- */ 35100894Srwatson 36100894Srwatson/* Verify: on every NODES row: parent_relpath is parent of local_relpath */ 37100894SrwatsonCREATE TEMPORARY TRIGGER validation_01 BEFORE INSERT ON nodes 38100894SrwatsonWHEN NOT ((new.local_relpath = '' AND new.parent_relpath IS NULL) 39100894Srwatson OR (relpath_depth(new.local_relpath) 40100894Srwatson = relpath_depth(new.parent_relpath) + 1)) 41100894SrwatsonBEGIN 42100894Srwatson SELECT RAISE(FAIL, 'WC DB validity check 01 failed'); 43100894SrwatsonEND; 44100894Srwatson 45100894Srwatson/* Verify: on every NODES row: its op-depth <= its own depth */ 46100894SrwatsonCREATE TEMPORARY TRIGGER validation_02 BEFORE INSERT ON nodes 47100894SrwatsonWHEN NOT new.op_depth <= relpath_depth(new.local_relpath) 48100894SrwatsonBEGIN 49101173Srwatson SELECT RAISE(FAIL, 'WC DB validity check 02 failed'); 50100894SrwatsonEND; 51100979Srwatson 52100979Srwatson/* Verify: on every NODES row: it is an op-root or it has a parent with the 53100979Srwatson sames op-depth. (Except when the node is a file external) */ 54102949SbdeCREATE TEMPORARY TRIGGER validation_03 BEFORE INSERT ON nodes 55100979SrwatsonWHEN NOT ( 56100979Srwatson (new.op_depth = relpath_depth(new.local_relpath)) 57101712Srwatson OR 58100979Srwatson (EXISTS (SELECT 1 FROM nodes 59100979Srwatson WHERE wc_id = new.wc_id AND op_depth = new.op_depth 60100894Srwatson AND local_relpath = new.parent_relpath)) 61100894Srwatson ) 62100979Srwatson AND NOT (new.file_external IS NOT NULL AND new.op_depth = 0) 63100979SrwatsonBEGIN 64100979Srwatson SELECT RAISE(FAIL, 'WC DB validity check 03 failed'); 65100979SrwatsonEND; 66100979Srwatson 67100979Srwatson/* Verify: on every ACTUAL row (except root): a NODES row exists at its 68100979Srwatson * parent path. */ 69100979SrwatsonCREATE TEMPORARY TRIGGER validation_04 BEFORE INSERT ON actual_node 70100894SrwatsonWHEN NOT (new.local_relpath = '' 71100979Srwatson OR EXISTS (SELECT 1 FROM nodes 72100979Srwatson WHERE wc_id = new.wc_id 73100979Srwatson AND local_relpath = new.parent_relpath)) 74100979SrwatsonBEGIN 75100979Srwatson SELECT RAISE(FAIL, 'WC DB validity check 04 failed'); 76100979SrwatsonEND; 77100979Srwatson 78100979Srwatson-- STMT_STATIC_VERIFY 79100979SrwatsonSELECT local_relpath, op_depth, 1, 'Invalid parent relpath set in NODES' 80100979SrwatsonFROM nodes n WHERE local_relpath != '' 81100979Srwatson AND (parent_relpath IS NULL 82100979Srwatson OR NOT IS_STRICT_DESCENDANT_OF(local_relpath, parent_relpath) 83100979Srwatson OR relpath_depth(local_relpath) != relpath_depth(parent_relpath)+1) 84100979Srwatson 85100979SrwatsonUNION ALL 86100979Srwatson 87100979SrwatsonSELECT local_relpath, -1, 2, 'Invalid parent relpath set in ACTUAL' 88100979SrwatsonFROM actual_node a WHERE local_relpath != '' 89101712Srwatson AND (parent_relpath IS NULL 90101712Srwatson OR NOT IS_STRICT_DESCENDANT_OF(local_relpath, parent_relpath) 91101712Srwatson OR relpath_depth(local_relpath) != relpath_depth(parent_relpath)+1) 92101712Srwatson 93101712SrwatsonUNION ALL 94101712Srwatson 95101712Srwatson/* All ACTUAL nodes must have an equivalent NODE in NODES 96100979Srwatson or be only one level deep (delete-delete tc) */ 97100979SrwatsonSELECT local_relpath, -1, 10, 'No ancestor in ACTUAL' 98100979SrwatsonFROM actual_node a WHERE local_relpath != '' 99100979Srwatson AND NOT EXISTS(SELECT 1 from nodes i 100100979Srwatson WHERE i.wc_id=a.wc_id 101100979Srwatson AND i.local_relpath=a.parent_relpath) 102100979Srwatson AND NOT EXISTS(SELECT 1 from nodes i 103100979Srwatson WHERE i.wc_id=a.wc_id 104100979Srwatson AND i.local_relpath=a.local_relpath) 105100979Srwatson 106100979SrwatsonUNION ALL 107100979Srwatson/* Verify if the ACTUAL data makes sense for the related node. 108100979Srwatson Only conflict data is valid if there is none */ 109100979SrwatsonSELECT a.local_relpath, -1, 11, 'Bad or Unneeded actual data' 110100979SrwatsonFROM actual_node a 111100979SrwatsonLEFT JOIN nodes n on n.wc_id = a.wc_id AND n.local_relpath = a.local_relpath 112100979Srwatson AND n.op_depth = (SELECT MAX(op_depth) from nodes i 113100979Srwatson WHERE i.wc_id=a.wc_id AND i.local_relpath=a.local_relpath) 114100979SrwatsonWHERE (a.properties IS NOT NULL 115100979Srwatson AND (n.presence IS NULL 116100979Srwatson OR n.presence NOT IN (MAP_NORMAL, MAP_INCOMPLETE))) 117100979Srwatson OR (a.changelist IS NOT NULL AND (n.kind IS NOT NULL AND n.kind != MAP_FILE)) 118100979Srwatson OR (a.conflict_data IS NULL AND a.properties IS NULL AND a.changelist IS NULL) 119100979Srwatson AND NOT EXISTS(SELECT 1 from nodes i 120100979Srwatson WHERE i.wc_id=a.wc_id 121100979Srwatson AND i.local_relpath=a.parent_relpath) 122100979Srwatson 123103513SrwatsonUNION ALL 124103513Srwatson 125103513Srwatson/* A parent node must exist for every normal node except the root. 126104236Srwatson That node must exist at a lower or equal op-depth */ 127103513SrwatsonSELECT local_relpath, op_depth, 20, 'No ancestor in NODES' 128100979SrwatsonFROM nodes n WHERE local_relpath != '' 129100979Srwatson AND file_external IS NULL 130100979Srwatson AND NOT EXISTS(SELECT 1 from nodes i 131100979Srwatson WHERE i.wc_id=n.wc_id 132100979Srwatson AND i.local_relpath=n.parent_relpath 133100979Srwatson AND i.op_depth <= n.op_depth) 134100979Srwatson 135100979SrwatsonUNION ALL 136100979Srwatson/* If a node is not present in the working copy (normal, add, copy) it doesn't 137100979Srwatson have revision details stored on this record */ 138103514SrwatsonSELECT local_relpath, op_depth, 21, 'Unneeded node data' 139103514SrwatsonFROM nodes 140103514SrwatsonWHERE presence NOT IN (MAP_NORMAL, MAP_INCOMPLETE) 141104236SrwatsonAND (properties IS NOT NULL 142103514Srwatson OR checksum IS NOT NULL 143100979Srwatson OR depth IS NOT NULL 144100979Srwatson OR symlink_target IS NOT NULL 145100979Srwatson OR changed_revision IS NOT NULL 146100979Srwatson OR (changed_date IS NOT NULL AND changed_date != 0) 147100979Srwatson OR changed_author IS NOT NULL 148100979Srwatson OR translated_size IS NOT NULL 149100979Srwatson OR last_mod_time IS NOT NULL 150100979Srwatson OR dav_cache IS NOT NULL 151100979Srwatson OR file_external IS NOT NULL 152100979Srwatson OR inherited_props IS NOT NULL) 153100979Srwatson 154100979SrwatsonUNION ALL 155100979Srwatson/* base-deleted nodes don't have a repository location. They are just 156100979Srwatson shadowing without a replacement */ 157100979SrwatsonSELECT local_relpath, op_depth, 22, 'Unneeded base-deleted node data' 158100979SrwatsonFROM nodes 159103136SrwatsonWHERE presence IN (MAP_BASE_DELETED) 160103136SrwatsonAND (repos_id IS NOT NULL 161103136Srwatson OR repos_path IS NOT NULL 162103136Srwatson OR revision IS NOT NULL) 163103136Srwatson 164101892SrwatsonUNION ALL 165100979Srwatson/* Verify if type specific data is set (or not set for wrong type) */ 166100979SrwatsonSELECT local_relpath, op_depth, 23, 'Kind specific data invalid on normal' 167100979SrwatsonFROM nodes 168100979SrwatsonWHERE presence IN (MAP_NORMAL, MAP_INCOMPLETE) 169101988SrwatsonAND (kind IS NULL 170104268Srwatson OR (repos_path IS NULL 171104268Srwatson AND (properties IS NOT NULL 172104268Srwatson OR changed_revision IS NOT NULL 173104268Srwatson OR changed_author IS NOT NULL 174104268Srwatson OR (changed_date IS NOT NULL AND changed_date != 0))) 175104268Srwatson OR (CASE WHEN kind = MAP_FILE AND repos_path IS NOT NULL 176104268Srwatson THEN checksum IS NULL 177104268Srwatson ELSE checksum IS NOT NULL END) 178104268Srwatson OR (CASE WHEN kind = MAP_DIR THEN depth IS NULL 179104268Srwatson ELSE depth IS NOT NULL END) 180100979Srwatson OR (CASE WHEN kind = MAP_SYMLINK THEN symlink_target IS NULL 181100979Srwatson ELSE symlink_target IS NOT NULL END)) 182100979Srwatson 183100979SrwatsonUNION ALL 184100979Srwatson/* Local-adds are always their own operation (read: they don't have 185100979Srwatson op-depth descendants, nor are op-depth descendants */ 186100979SrwatsonSELECT local_relpath, op_depth, 24, 'Invalid op-depth for local add' 187100979SrwatsonFROM nodes 188100979SrwatsonWHERE presence IN (MAP_NORMAL, MAP_INCOMPLETE) 189100979Srwatson AND repos_path IS NULL 190100979Srwatson AND op_depth != relpath_depth(local_relpath) 191100979Srwatson 192100979SrwatsonUNION ALL 193100979Srwatson/* op-depth descendants are only valid if they have a direct parent 194100979Srwatson node at the same op-depth. Only certain types allow further 195100979Srwatson descendants */ 196100979SrwatsonSELECT local_relpath, op_depth, 25, 'Node missing op-depth ancestor' 197100979SrwatsonFROM nodes n 198100979SrwatsonWHERE op_depth < relpath_depth(local_relpath) 199100979Srwatson AND file_external IS NULL 200100979Srwatson AND NOT EXISTS(SELECT 1 FROM nodes p 201100979Srwatson WHERE p.wc_id=n.wc_id AND p.local_relpath=n.parent_relpath 202100979Srwatson AND p.op_depth=n.op_depth 203100979Srwatson AND (p.presence IN (MAP_NORMAL, MAP_INCOMPLETE) 204100979Srwatson OR (p.presence IN (MAP_BASE_DELETED, MAP_NOT_PRESENT) 205101988Srwatson AND n.presence = MAP_BASE_DELETED))) 206100979Srwatson 207100979SrwatsonUNION ALL 208100979Srwatson/* Present op-depth descendants have the repository location implied by their 209100979Srwatson ancestor */ 210100979SrwatsonSELECT n.local_relpath, n.op_depth, 26, 'Copied descendant mismatch' 211100979SrwatsonFROM nodes n 212100979SrwatsonJOIN nodes p 213100979Srwatson ON p.wc_id=n.wc_id AND p.local_relpath=n.parent_relpath 214100979Srwatson AND n.op_depth=p.op_depth 215100979SrwatsonWHERE n.op_depth > 0 AND n.presence IN (MAP_NORMAL, MAP_INCOMPLETE) 216100979Srwatson AND (n.repos_id != p.repos_id 217100979Srwatson OR n.repos_path != 218100979Srwatson RELPATH_SKIP_JOIN(n.parent_relpath, p.repos_path, n.local_relpath) 219100979Srwatson OR n.revision != p.revision 220100979Srwatson OR p.kind != MAP_DIR 221100979Srwatson OR n.moved_here IS NOT p.moved_here) 222100979Srwatson 223100979SrwatsonUNION ALL 224100979Srwatson/* Only certain presence values are valid as op-root. 225100979Srwatson Note that the wc-root always has presence normal or incomplete */ 226100979SrwatsonSELECT n.local_relpath, n.op_depth, 27, 'Invalid op-root presence' 227100979SrwatsonFROM nodes n 228100979SrwatsonWHERE n.op_depth = relpath_depth(local_relpath) 229100979Srwatson AND presence NOT IN (MAP_NORMAL, MAP_INCOMPLETE, MAP_BASE_DELETED) 230100979Srwatson 231100979SrwatsonUNION ALL 232100979Srwatson/* If a node is shadowed, all its present op-depth descendants 233100979Srwatson must be shadowed at the same op-depth as well */ 234100979SrwatsonSELECT n.local_relpath, s.op_depth, 28, 'Incomplete shadowing' 235100979SrwatsonFROM nodes n 236100979SrwatsonJOIN nodes s ON s.wc_id=n.wc_id AND s.local_relpath=n.local_relpath 237100979Srwatson AND s.op_depth = relpath_depth(s.local_relpath) 238100979Srwatson AND s.op_depth = (SELECT MIN(op_depth) FROM nodes d 239100979Srwatson WHERE d.wc_id=s.wc_id AND d.local_relpath=s.local_relpath 240100979Srwatson AND d.op_depth > n.op_depth) 241100979SrwatsonWHERE n.presence IN (MAP_NORMAL, MAP_INCOMPLETE) 242100979Srwatson AND EXISTS(SELECT 1 243100979Srwatson FROM nodes dn 244100979Srwatson WHERE dn.wc_id=n.wc_id AND dn.op_depth=n.op_depth 245100979Srwatson AND dn.presence IN (MAP_NORMAL, MAP_INCOMPLETE) 246100979Srwatson AND IS_STRICT_DESCENDANT_OF(dn.local_relpath, n.local_relpath) 247100979Srwatson AND dn.file_external IS NULL 248100979Srwatson AND NOT EXISTS(SELECT 1 249100979Srwatson FROM nodes ds 250100979Srwatson WHERE ds.wc_id=n.wc_id AND ds.op_depth=s.op_depth 251100979Srwatson AND ds.local_relpath=dn.local_relpath)) 252100979Srwatson 253100979SrwatsonUNION ALL 254100979Srwatson/* A base-delete is only valid if it directly deletes a present node */ 255100979SrwatsonSELECT s.local_relpath, s.op_depth, 29, 'Invalid base-delete' 256100979SrwatsonFROM nodes s 257100979SrwatsonLEFT JOIN nodes n ON n.wc_id=s.wc_id AND n.local_relpath=s.local_relpath 258100979Srwatson AND n.op_depth = (SELECT MAX(op_depth) FROM nodes d 259100979Srwatson WHERE d.wc_id=s.wc_id AND d.local_relpath=s.local_relpath 260100979Srwatson AND d.op_depth < s.op_depth) 261100979SrwatsonWHERE s.presence = MAP_BASE_DELETED 262100979Srwatson AND (n.presence IS NULL 263100979Srwatson OR n.presence NOT IN (MAP_NORMAL, MAP_INCOMPLETE) 264100979Srwatson /*OR n.kind != s.kind*/) 265100979Srwatson 266100979SrwatsonUNION ALL 267100979Srwatson/* Moves are stored in the working layers, not in BASE */ 268100979SrwatsonSELECT n.local_relpath, n.op_depth, 30, 'Invalid data for BASE' 269100979SrwatsonFROM nodes n 270100979SrwatsonWHERE n.op_depth = 0 271100979Srwatson AND (n.moved_to IS NOT NULL 272100979Srwatson OR n.moved_here IS NOT NULL) 273100979Srwatson 274100979SrwatsonUNION ALL 275100979Srwatson/* If moved_here is set on an op-root, there must be a proper moved_to */ 276100979SrwatsonSELECT d.local_relpath, d.op_depth, 60, 'Moved here without origin' 277100979SrwatsonFROM nodes d 278100979SrwatsonWHERE d.op_depth = relpath_depth(d.local_relpath) 279100979Srwatson AND d.moved_here IS NOT NULL 280100979Srwatson AND NOT EXISTS(SELECT 1 FROM nodes s 281100979Srwatson WHERE s.wc_id = d.wc_id AND s.moved_to = d.local_relpath) 282100979Srwatson 283100979SrwatsonUNION ALL 284100979Srwatson/* If moved_to is set there should be an moved op root at the target */ 285100979SrwatsonSELECT s.local_relpath, s.op_depth, 61, 'Moved to without target' 286100979SrwatsonFROM nodes s 287100979SrwatsonWHERE s.moved_to IS NOT NULL 288100979Srwatson AND NOT EXISTS(SELECT 1 FROM nodes d 289100979Srwatson WHERE d.wc_id = s.wc_id AND d.local_relpath = s.moved_to 290100979Srwatson AND d.op_depth = relpath_depth(d.local_relpath) 291100979Srwatson AND d.moved_here =1 AND d.repos_path IS NOT NULL) 292100979Srwatson