Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Added comments to the sql commands in the integrity checks.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:727f370c2927c0788f0adc64f16d67258b237c34
User & Date: aku 2008-01-27 20:38:17
Context
2008-01-27
20:40
Fixed bug in the code detaching a vendor branch. Left link to removed parent dangling, and killed connection to first on branch. No more. check-in: 0adaabf1 user: aku tags: trunk
20:38
Added comments to the sql commands in the integrity checks. check-in: 727f370c user: aku tags: trunk
2008-01-19
06:07
More comments on sql statements. check-in: 6809145e user: aku tags: trunk
Changes

Changes to tools/cvs2fossil/lib/c2f_integrity.tcl.

76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
...
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
...
346
347
348
349
350
351
352

353
354
355
356


357
358
359
360
361
362
363
364
365
366
367
...
384
385
386
387
388
389
390
391


392
393
394
395


396

397
398
399
400
401
402
403
...
407
408
409
410
411
412
413
414


415
416
417
418
419


420

421
422
423
424
425
426
427
428
...
432
433
434
435
436
437
438

439
440


441
442
443
444
445

446

447
448
449
450

451
452
453


454
455
456
457
458
459
460
461
...
496
497
498
499
500
501
502

503
504
505
506

507
508
509
510
511
512
513
514
515
516
517
518
...
554
555
556
557
558
559
560
561


562
563
564
565
566


567

568
569
570
571
572
573
574
575
...
578
579
580
581
582
583
584

585
586


587
588
589
590
591

592

593
594
595
596

597
598
599


600
601
602
603
604
605
606
607
...
626
627
628
629
630
631
632
633

634
635
636
637
638
639
640
...
643
644
645
646
647
648
649

650
651
652
653

654
655
656
657
658
659
660
661
662
663
664
665
...
717
718
719
720
721
722
723
724


725
726
727
728
729


730

731
732
733
734
735
736
737
738
...
742
743
744
745
746
747
748

749
750


751
752
753
754
755

756

757
758
759
760

761
762
763


764
765
766
767
768
769
770
771
...
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
	# Find all revisions which disagree with their line of
	# development about the project they are owned by.
	CheckRev \
	    {Revisions and their LODs have to be in the same project} \
	    {disagrees with its LOD about owning project} {
		SELECT F.name, R.rev
		FROM revision R, file F, symbol S
		WHERE R.fid = F.fid
		AND   R.lod = S.sid
		AND   F.pid != S.pid
		;
	    }
	# Find all revisions which disgree with their meta data about
	# the project they are owned by.
	CheckRev \
	    {Revisions and their meta data have to be in the same project} \
	    {disagrees with its meta data about owning project} {
		SELECT F.name, R.rev
		FROM revision R, file F, meta M
		WHERE R.fid = F.fid
		AND   R.mid = M.mid
		AND   F.pid != M.pid
		;
	    }
	# Find all revisions with a primary child which disagrees
	# about the file they belong to.
	CheckRev \
	    {Revisions and their primary children have to be in the same file} \
	    {disagrees with its primary child about the owning file} {
		SELECT F.name, R.rev
		FROM revision R, revision C, file F
		WHERE R.fid = F.fid
		AND   R.child IS NOT NULL
		AND   R.child = C.rid
		AND   C.fid != R.fid
		;
	    }

	# Find all revisions with a branch parent symbol whose parent
	# disagrees about the file they belong to.
	CheckRev \
	    {Revisions and their branch children have to be in the same file} \
	    {at the beginning of its branch and its parent disagree about the owning file} {
		SELECT F.name, R.rev
		FROM revision R, revision P, file F
		WHERE R.fid = F.fid
		AND   R.bparent IS NOT NULL
		AND   R.parent = P.rid
		AND   R.fid != P.fid
		;
	    }
	# Find all revisions with a non-NTDB child which disagrees
	# about the file they belong to.
	CheckRev \
	    {Revisions and their non-NTDB children have to be in the same file} \
	    {disagrees with its non-NTDB child about the owning file} {
		SELECT F.name, R.rev
		FROM revision R, revision C, file F
		WHERE R.fid = F.fid
		AND   R.dbchild IS NOT NULL
		AND   R.dbchild = C.rid
		AND   C.fid != R.fid
		;
	    }
	# Find all revisions which have a primary child, but the child
	# does not have them as parent.
	CheckRev \
	    {Revisions have to be parents of their primary children} \
	    {is not the parent of its primary child} {
		SELECT F.name, R.rev
		FROM revision R, revision C, file F
		WHERE R.fid = F.fid
		AND   R.child IS NOT NULL
		AND   R.child = C.rid
		AND   C.parent != R.rid
		;
	    }
	# Find all revisions which have a primrary child, but the
	# child has a branch parent symbol making them brach starters.
	CheckRev \
	    {Primary children of revisions must not start branches} \
	    {is parent of a primary child which is the beginning of a branch} {
		SELECT F.name, R.rev
		FROM revision R, revision C, file F
		WHERE R.fid = F.fid
		AND   R.child IS NOT NULL
		AND   R.child = C.rid
		AND   C.bparent IS NOT NULL
		;
	    }
	# Find all revisions without branch parent symbol which have a
	# parent, but the parent does not have them as primary child.
	CheckRev \
	    {Revisions have to be primary children of their parents, if any} \
	    {is not the child of its parent} {
		SELECT F.name, R.rev
		FROM revision R, revision P, file F
		WHERE R.fid = F.fid
		AND   R.bparent IS NULL
		AND   R.parent IS NOT NULL
		AND   R.parent = P.rid
		AND   P.child != R.rid
		;
	    }
	# Find all revisions with a branch parent symbol which do not
	# have a parent.
	CheckRev \
	    {Branch starting revisions have to have a parent, if not detached} \
	    {at the beginning of its branch has no parent, but its branch has} {
		SELECT F.name, R.rev
		FROM revision R, file F, branch B
		WHERE R.fid = F.fid
		AND   R.bparent IS NOT NULL
		AND   R.parent  IS NULL
		AND   B.sid = R.bparent
		AND   B.fid = R.fid
		AND   B.root    IS NOT NULL
		;
	    }
	# Find all revisions with a branch parent symbol whose parent
	# has them as primary child.
	CheckRev \
	    {Branch starting revisions must not be primary children of their parents} \
	    {at the beginning of its branch is the primary child of its parent} {
		SELECT F.name, R.rev
		FROM revision R, revision P, file F
		WHERE R.fid = F.fid
		AND   R.bparent IS NOT NULL
		AND   R.parent IS NOT NULL
		AND   R.parent = P.rid
		AND   P.child = R.rid
		;
	    }
	# Find all revisions with a non-NTDB child which are not on
	# the NTDB.
	CheckRev \
	    {NTDB to trunk transition has to begin on NTDB} \
	    {has a non-NTDB child, yet is not on the NTDB} {
		SELECT F.name, R.rev
		FROM revision R, file F
		WHERE R.fid = F.fid
		AND   R.dbchild IS NOT NULL
		AND   NOT R.isdefault
		;
	    }
	# Find all revisions with a NTDB parent which are on the NTDB.
	CheckRev \
	    {NTDB to trunk transition has to end on non-NTDB} \
	    {has a NTDB parent, yet is on the NTDB} {
		SELECT F.name, R.rev
		FROM revision R, file F
		WHERE R.fid = F.fid
		AND   R.dbparent IS NOT NULL
		AND   R.isdefault
		;
	    }
	# Find all revisions with a child which disagrees about the
	# line of development they belong to.
	CheckRev \
	    {Revisions and their primary children have to be in the same LOD} \
	    {and its primary child disagree about their LOD} {
		SELECT F.name, R.rev
		FROM revision R, revision C, file F
		WHERE R.fid = F.fid
		AND   R.child IS NOT NULL
		AND   R.child = C.rid
		AND   C.lod != R.lod
		;
	    }
	# Find all revisions with a non-NTDB child which agrees about
	# the line of development they belong to.
	CheckRev \
	    {NTDB and trunk revisions have to be in different LODs} \
	    {on NTDB and its non-NTDB child wrongly agree about their LOD} {
		SELECT F.name, R.rev
		FROM revision R, revision C, file F
		WHERE R.fid = F.fid
		AND   R.dbchild IS NOT NULL
		AND   R.dbchild = C.rid
		AND   C.lod = R.lod
		;
	    }
	# Find all revisions with a branch parent symbol which is not
	# their LOD.
	CheckRev \
	    {Branch starting revisions have to have their LOD as branch parent symbol} \
	    {at the beginning of its branch does not have the branch symbol as its LOD} {
		SELECT F.name, R.rev
		FROM revision R, file F
		WHERE R.fid = F.fid
		AND   R.bparent IS NOT NULL
		AND   R.lod != R.bparent
		;
	    }
	# Find all revisions with a branch parent symbol whose parent
	# is in the same line of development.
	CheckRev \
	    {Revisions and their branch children have to be in different LODs} \
	    {at the beginning of its branch and its parent wrongly agree about their LOD} {
		SELECT F.name, R.rev
		FROM revision R, revision P, file F
		WHERE R.fid = F.fid
		AND   R.bparent IS NOT NULL
		AND   R.parent = P.rid
		AND   R.lod = P.lod
		;
	    }
	return
    }

    proc Meta {} {
	# This code performs a number of paranoid checks of the
................................................................................
	# Find all revisions which disgree with their meta data about
	# the branch/line of development they belong to.
	CheckRev \
	    {Revisions and their meta data have to be in the same LOD} \
	    {disagrees with its meta data about owning LOD} {
		SELECT F.name, R.rev
		FROM revision R, meta M, file F
		WHERE R.mid = M.mid
		AND   R.lod != M.bid
		AND   R.fid = F.fid
		;
	    }
	return
    }

    proc RevisionChangesets {} {
	# This code performs a number of paranoid checks of the
................................................................................
                -- revision to aggregate the changeset, counting
                -- them. From the resulting revision/count table
                -- select those with more than one user, and get their
                -- associated file (name) for display.

		SELECT F.name, R.rev
		FROM revision R, file F,

		     (SELECT CI.iid AS rid, count(CI.cid) AS count
		      FROM csitem CI, changeset C
		      WHERE C.type = 0
		      AND   C.cid = CI.cid


		      GROUP BY CI.iid) AS U
		WHERE U.count > 1
		AND R.rid = U.rid
		AND R.fid = F.fid
	    }
	# All revisions have to refer to the same meta information as
	# their changeset.
	CheckRevCS \
	    {All revisions have to agree with their changeset about the used meta information} \
	    {disagrees with its changeset @ about the meta information} {
		SELECT CT.name, C.cid, F.name, R.rev
................................................................................
	# for the changesets found thusly.
	CheckCS \
	    {All revisions in a changeset have to belong to the same LOD} \
	    {: Its revisions disagree about the LOD they belong to} {
		SELECT T.name, C.cid
		FROM   changeset C, cstype T
		WHERE  C.cid IN (SELECT U.cid
				 FROM (SELECT DISTINCT CI.cid AS cid, R.lod AS lod


				       FROM   csitem CI, changeset C, revision R
				       WHERE  CI.iid = R.rid
				       AND    C.cid = CI.cid
				       AND    C.type = 0) AS U


				 GROUP BY U.cid HAVING COUNT(U.lod) > 1)

		AND    T.tid = C.type
	    }
	# All revisions have to agree on the project their changeset
	# belongs to. In other words, all revisions in a changeset
	# have to refer to the same project.
	#
	# Instead of looking at all pairs of revisions in all
................................................................................
	# for the changesets found thusly.
	CheckCS \
	    {All revisions in a changeset have to belong to the same project} \
	    {: Its revisions disagree about the project they belong to} {
		SELECT T.name, C.cid
		FROM   changeset C, cstype T
		WHERE  C.cid IN (SELECT U.cid
				 FROM (SELECT DISTINCT CI.cid AS cid, F.pid AS pid


				       FROM   csitem CI, changeset C, revision R, file F
				       WHERE  CI.iid = R.rid
				       AND    C.cid = CI.cid
				       AND    C.type = 0
				       AND    F.fid  = R.fid) AS U


				 GROUP BY U.cid HAVING COUNT(U.pid) > 1)

		AND    T.tid = C.type
	    }
	# All revisions in a single changeset have to belong to
	# different files. Conversely: No two revisions of a single
	# file are allowed to be in the same changeset.
	#
	# Instead of looking at all pairs of revisions in all
	# changesets we generate the distinct set of all files
................................................................................
	# changesets found thusly.
	CheckCS \
	    {All revisions in a changeset have to belong to different files} \
	    {: Its revisions share files} {
		SELECT T.name, C.cid
		FROM   changeset C, cstype T
		WHERE  C.cid IN (SELECT VV.cid

				 FROM (SELECT U.cid as cid, COUNT (U.fid) AS fcount
				       FROM (SELECT DISTINCT CI.cid AS cid, R.fid AS fid


					     FROM   csitem CI, changeset C, revision R
					     WHERE  CI.iid = R.rid
					     AND    C.cid = CI.cid
					     AND    C.type = 0
					     ) AS U

				       GROUP BY U.cid) AS UU,

				      (SELECT V.cid AS cid, COUNT (V.iid) AS rcount
				       FROM   csitem V, changeset X
				       WHERE  X.cid = V.cid
				       AND    X.type = 0

				       GROUP BY V.cid) AS VV
				 WHERE VV.cid = UU.cid
				 AND   UU.fcount < VV.rcount)


		AND    T.tid = C.type
	    }
	return
    }

    proc TagChangesets {} {
	# This code performs a number of paranoid checks of the
	# database, searching for inconsistent changeset/revision
................................................................................
                -- the changeset, counting them. From the resulting
                -- tag/count table select those with more than one
                -- user, and get their associated file (name) for
                -- display.

		SELECT P.name, S.name
		FROM tag T, project P, symbol S,

		     (SELECT CI.iid AS iid, count(CI.cid) AS count
		      FROM csitem CI, changeset C
		      WHERE C.type = 1
		      AND   C.cid = CI.cid

		      GROUP BY CI.iid) AS U
		WHERE U.count > 1
		AND   T.tid = U.iid
		AND   S.sid = T.sid               -- get symbol of tag
		AND   P.pid = S.pid               -- get project of symbol
	    }
	if 0 {
	    # This check is disabled for the moment. Apparently tags
	    # can cross lines of development, at least if the involved
	    # LODs are the trunk, and the NTDB. That makes sense, as
	    # the NTDB revisions are initially logically a part of the
	    # trunk. The standard check below however does not capture
................................................................................
	# thusly.
	CheckCS \
	    {All tags in a changeset have to belong to the same project} \
	    {: Its tags disagree about the project they belong to} {
		SELECT T.name, C.cid
		FROM   changeset C, cstype T
		WHERE  C.cid IN (SELECT U.cid
				 FROM (SELECT DISTINCT CI.cid AS cid, F.pid AS pid


				       FROM   csitem CI, changeset C, tag T, file F
				       WHERE  CI.iid = T.tid
				       AND    C.cid = CI.cid
				       AND    C.type = 1
				       AND    F.fid  = T.fid) AS U


				 GROUP BY U.cid HAVING COUNT(U.pid) > 1)

		AND    T.tid = C.type
	    }
	# All tags in a single changeset have to belong to different
	# files. Conversely: No two tags of a single file are allowed
	# to be in the same changeset.
	#
	# Instead of looking at all pairs of tags in all changesets we
	# generate the distinct set of all files referenced by the
................................................................................
	# information for the changesets found thusly.
	CheckCS \
	    {All tags in a changeset have to belong to different files} \
	    {: Its tags share files} {
		SELECT T.name, C.cid
		FROM   changeset C, cstype T
		WHERE  C.cid IN (SELECT VV.cid

				 FROM (SELECT U.cid as cid, COUNT (U.fid) AS fcount
				       FROM (SELECT DISTINCT CI.cid AS cid, T.fid AS fid


					     FROM   csitem CI, changeset C, tag T
					     WHERE  CI.iid = T.tid
					     AND    C.cid = CI.cid
					     AND    C.type = 1
					     ) AS U

				       GROUP BY U.cid) AS UU,

				      (SELECT V.cid AS cid, COUNT (V.iid) AS rcount
				       FROM   csitem V, changeset X
				       WHERE  X.cid = V.cid
				       AND    X.type = 1

				       GROUP BY V.cid) AS VV
				 WHERE VV.cid = UU.cid
				 AND   UU.fcount < VV.rcount)


		AND    T.tid = C.type
	    }
	return
    }

    proc BranchChangesets {} {
	# This code performs a number of paranoid checks of the
	# database, searching for inconsistent changeset/revision
................................................................................
		FROM project P, branch B, symbol S
		WHERE B.bid IN (SELECT bid                    -- All branches
				FROM   branch
				EXCEPT                        -- subtract
				SELECT CI.iid                 -- branches used
				FROM   csitem CI, changeset C
				WHERE  C.cid = CI.cid         -- by any branch
				AND    C.type = 2)            -- changeset

		AND   S.sid = B.sid               -- get symbol of branch
		AND   P.pid = S.pid               -- get project of symbol
	    }
	# Find all branches which are used by more than one changeset.
	CheckRev \
	    {All branches have to be used by at most one changeset} \
	    {is used by multiple changesets} {
................................................................................
                -- aggregate the changeset, counting them. From the
                -- resulting branch/count table select those with more
                -- than one user, and get their associated file (name)
                -- for display.

		SELECT P.name, S.name
		FROM branch B, project P, symbol S,

		     (SELECT CI.iid AS iid, count(CI.cid) AS count
		      FROM csitem CI, changeset C
		      WHERE C.type = 2
		      AND   C.cid = CI.cid

		      GROUP BY CI.iid ) AS U
		WHERE U.count > 1
		AND   B.bid = U.iid
		AND   S.sid = B.sid               -- get symbol of branch
		AND   P.pid = S.pid               -- get project of symbol
	    }
	if 0 {
	    # This check has been disabled. When the converter was run
	    # on the Tcl CVS several branches tripped this
	    # constraint. One of them was a free-floating branch, and
	    # its handling has been fixed by now. The others however
	    # seem semi-legitimate, in the sense that they show
................................................................................
	# for the changesets found thusly.
	CheckCS \
	    {All branches in a changeset have to belong to the same project} \
	    {: Its branches disagree about the project they belong to} {
		SELECT T.name, C.cid
		FROM   changeset C, cstype T
		WHERE  C.cid IN (SELECT U.cid
				 FROM (SELECT DISTINCT CI.cid AS cid, F.pid AS pid


				       FROM   csitem CI, changeset C, branch B, file F
				       WHERE  CI.iid = B.bid
				       AND    C.cid = CI.cid
				       AND    C.type = 2
				       AND    F.fid  = B.fid) AS U


				 GROUP BY U.cid HAVING COUNT(U.pid) > 1)

		AND    T.tid = C.type
	    }
	# All branches in a single changeset have to belong to
	# different files. Conversely: No two branches of a single
	# file are allowed to be in the same changeset.
	#
	# Instead of looking at all pairs of branches in all
	# changesets we generate the distinct set of all files
................................................................................
	# changesets found thusly.
	CheckCS \
	    {All branches in a changeset have to belong to different files} \
	    {: Its branches share files} {
		SELECT T.name, C.cid
		FROM   changeset C, cstype T
		WHERE  C.cid IN (SELECT VV.cid

				 FROM (SELECT U.cid as cid, COUNT (U.fid) AS fcount
				       FROM (SELECT DISTINCT CI.cid AS cid, B.fid AS fid


					     FROM   csitem CI, changeset C, branch B
					     WHERE  CI.iid = B.bid
					     AND    C.cid = CI.cid
					     AND    C.type = 2
					     ) AS U

				       GROUP BY U.cid) AS UU,

				      (SELECT V.cid AS cid, COUNT (V.iid) AS rcount
				       FROM   csitem V, changeset X
				       WHERE  X.cid = V.cid
				       AND    X.type = 2

				       GROUP BY V.cid) AS VV
				 WHERE VV.cid = UU.cid
				 AND   UU.fcount < VV.rcount)


		AND    T.tid = C.type
	    }
	return
    }

    proc ___UnusedChangesetChecks___ {} {
	# This code performs a number of paranoid checks of the
	# database, searching for inconsistent changeset/revision
................................................................................
	# changeset's tag associated with them.
	CheckRevCS \
	    {All revisions used by tag symbol changesets have to have the changeset's tag attached to them} \
	    {does not have the tag of its symbol changeset @ attached to it} {
		SELECT CT.name, C.cid, F.name, R.rev
		FROM   changeset C, cstype CT, revision R, file F, csitem CI, tag T
		WHERE  C.type = 1       -- symbol changesets only
		AND    C.src  = T.sid   -- tag only, linked by symbol id 
		AND    C.cid  = CI.cid  -- changeset --> its revisions
		AND    R.rid  = CI.iid  -- look at the revisions
		-- and look for the tag among the attached ones.
		AND    T.sid NOT IN (SELECT TB.sid
				     FROM   tag TB
				     WHERE  TB.rev = R.rid)
		AND    R.fid = F.fid    -- get file of revision







|
|
|









|
|
|









|
|
|
|










|
|
|
|









|
|
|
|









|
|
|
|









|
|
|
|









|
|
|
|
|









|
|
|
|
|
|









|
|
|
|
|









|
|
|








|
|
|









|
|
|
|









|
|
|
|









|
|
|









|
|
|
|







 







|
|
|







 







>
|

|
<
>
>
|
|
|
|







 







|
>
>

|
|
|
>
>
|
>







 







|
>
>

|
|
|
|
>
>
|
>
|







 







>
|
|
>
>

|
|
|

>
|
>
|

|
|
>
|
|
|
>
>
|







 







>
|

|
|
>
|
|
|
|
|







 







|
>
>

|
|
|
|
>
>
|
>
|







 







>
|
|
>
>

|
|
|

>
|
>
|

|
|
>
|
|
|
>
>
|







 







|
>







 







>
|

|
|
>
|
|
|
|
|







 







|
>
>

|
|
|
|
>
>
|
>
|







 







>
|
|
>
>

|
|
|

>
|
>
|

|
|
>
|
|
|
>
>
|







 







|







76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
...
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
...
346
347
348
349
350
351
352
353
354
355
356

357
358
359
360
361
362
363
364
365
366
367
368
369
...
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
...
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
...
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
...
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
...
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
...
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
...
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
...
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
...
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
...
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
...
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
	# Find all revisions which disagree with their line of
	# development about the project they are owned by.
	CheckRev \
	    {Revisions and their LODs have to be in the same project} \
	    {disagrees with its LOD about owning project} {
		SELECT F.name, R.rev
		FROM revision R, file F, symbol S
		WHERE R.fid = F.fid   -- get file of rev
		AND   R.lod = S.sid   -- get symbol of its lod
		AND   F.pid != S.pid  -- disagreement about the owning project
		;
	    }
	# Find all revisions which disgree with their meta data about
	# the project they are owned by.
	CheckRev \
	    {Revisions and their meta data have to be in the same project} \
	    {disagrees with its meta data about owning project} {
		SELECT F.name, R.rev
		FROM revision R, file F, meta M
		WHERE R.fid = F.fid   -- get file of rev
		AND   R.mid = M.mid   -- get meta of rev
		AND   F.pid != M.pid  -- disagreement about owning project
		;
	    }
	# Find all revisions with a primary child which disagrees
	# about the file they belong to.
	CheckRev \
	    {Revisions and their primary children have to be in the same file} \
	    {disagrees with its primary child about the owning file} {
		SELECT F.name, R.rev
		FROM revision R, revision C, file F
		WHERE R.fid = F.fid       -- get file of rev
		AND   R.child IS NOT NULL -- get all with primary children
		AND   R.child = C.rid     -- get primary child
		AND   C.fid != R.fid      -- wrongly in different file
		;
	    }

	# Find all revisions with a branch parent symbol whose parent
	# disagrees about the file they belong to.
	CheckRev \
	    {Revisions and their branch children have to be in the same file} \
	    {at the beginning of its branch and its parent disagree about the owning file} {
		SELECT F.name, R.rev
		FROM revision R, revision P, file F
		WHERE R.fid = F.fid         -- get file of rev
		AND   R.bparent IS NOT NULL -- get first-of-branch revisions
		AND   R.parent = P.rid      -- get out-of-branch parent
		AND   R.fid != P.fid        -- wrongly in different file
		;
	    }
	# Find all revisions with a non-NTDB child which disagrees
	# about the file they belong to.
	CheckRev \
	    {Revisions and their non-NTDB children have to be in the same file} \
	    {disagrees with its non-NTDB child about the owning file} {
		SELECT F.name, R.rev
		FROM revision R, revision C, file F
		WHERE R.fid = F.fid         -- get file of rev
		AND   R.dbchild IS NOT NULL -- get last NTDB revisions
		AND   R.dbchild = C.rid     -- get their child
		AND   C.fid != R.fid        -- wrongly in different file
		;
	    }
	# Find all revisions which have a primary child, but the child
	# does not have them as parent.
	CheckRev \
	    {Revisions have to be parents of their primary children} \
	    {is not the parent of its primary child} {
		SELECT F.name, R.rev
		FROM revision R, revision C, file F
		WHERE R.fid = F.fid         -- get file of rev
		AND   R.child IS NOT NULL   -- get all with primary children
		AND   R.child = C.rid       -- get primary child
		AND   C.parent != R.rid     -- child's parent wrongly not us
		;
	    }
	# Find all revisions which have a primrary child, but the
	# child has a branch parent symbol making them brach starters.
	CheckRev \
	    {Primary children of revisions must not start branches} \
	    {is parent of a primary child which is the beginning of a branch} {
		SELECT F.name, R.rev
		FROM revision R, revision C, file F
		WHERE R.fid = F.fid         -- get file of rev
		AND   R.child IS NOT NULL   -- get all with primary children
		AND   R.child = C.rid       -- get primary child
		AND   C.bparent IS NOT NULL -- but indicates to be on branch
		;
	    }
	# Find all revisions without branch parent symbol which have a
	# parent, but the parent does not have them as primary child.
	CheckRev \
	    {Revisions have to be primary children of their parents, if any} \
	    {is not the child of its parent} {
		SELECT F.name, R.rev
		FROM revision R, revision P, file F
		WHERE R.fid = F.fid        -- get file of revision
		AND   R.bparent IS NULL    -- exclude all first-on-branch revisions
		AND   R.parent IS NOT NULL -- which are not root of their line
		AND   R.parent = P.rid     -- get in-lod parent
		AND   P.child != R.rid     -- but does not have rev as primary child
		;
	    }
	# Find all revisions with a branch parent symbol which do not
	# have a parent.
	CheckRev \
	    {Branch starting revisions have to have a parent, if not detached} \
	    {at the beginning of its branch has no parent, but its branch has} {
		SELECT F.name, R.rev
		FROM revision R, file F, branch B
		WHERE R.fid = F.fid         -- get file of revision
		AND   R.bparent IS NOT NULL -- limit to first-on-branch revisions
		AND   R.parent  IS NULL     -- which are detached
		AND   B.sid = R.bparent     -- get branch governing the rev
		AND   B.fid = R.fid         -- in the revision's file
		AND   B.root    IS NOT NULL -- but says that branch is attached
		;
	    }
	# Find all revisions with a branch parent symbol whose parent
	# has them as primary child.
	CheckRev \
	    {Branch starting revisions must not be primary children of their parents} \
	    {at the beginning of its branch is the primary child of its parent} {
		SELECT F.name, R.rev
		FROM revision R, revision P, file F
		WHERE R.fid = F.fid         -- get file of revision
		AND   R.bparent IS NOT NULL -- limit to first-on-branch revisions
		AND   R.parent IS NOT NULL  -- which are attached
		AND   R.parent = P.rid      -- get out-of-branch parent
		AND   P.child = R.rid       -- wrongly has rev as primary child
		;
	    }
	# Find all revisions with a non-NTDB child which are not on
	# the NTDB.
	CheckRev \
	    {NTDB to trunk transition has to begin on NTDB} \
	    {has a non-NTDB child, yet is not on the NTDB} {
		SELECT F.name, R.rev
		FROM revision R, file F
		WHERE R.fid = F.fid         -- get file of revision
		AND   R.dbchild IS NOT NULL -- limit to last NTDB revision
		AND   NOT R.isdefault       -- but signals not-NTDB
		;
	    }
	# Find all revisions with a NTDB parent which are on the NTDB.
	CheckRev \
	    {NTDB to trunk transition has to end on non-NTDB} \
	    {has a NTDB parent, yet is on the NTDB} {
		SELECT F.name, R.rev
		FROM revision R, file F
		WHERE R.fid = F.fid          -- get file of revision
		AND   R.dbparent IS NOT NULL -- limit to roots of non-NTDB
		AND   R.isdefault            -- but signals to be NTDB
		;
	    }
	# Find all revisions with a child which disagrees about the
	# line of development they belong to.
	CheckRev \
	    {Revisions and their primary children have to be in the same LOD} \
	    {and its primary child disagree about their LOD} {
		SELECT F.name, R.rev
		FROM revision R, revision C, file F
		WHERE R.fid = F.fid       -- get file of revision
		AND   R.child IS NOT NULL -- revision has a primary child
		AND   R.child = C.rid     -- get that child
		AND   C.lod != R.lod      -- child wrongly disagrees with lod
		;
	    }
	# Find all revisions with a non-NTDB child which agrees about
	# the line of development they belong to.
	CheckRev \
	    {NTDB and trunk revisions have to be in different LODs} \
	    {on NTDB and its non-NTDB child wrongly agree about their LOD} {
		SELECT F.name, R.rev
		FROM revision R, revision C, file F
		WHERE R.fid = F.fid         -- get file of revision
		AND   R.dbchild IS NOT NULL -- limit to last NTDB revision
		AND   R.dbchild = C.rid     -- get non-NTDB child
		AND   C.lod = R.lod         -- child wrongly has same lod
		;
	    }
	# Find all revisions with a branch parent symbol which is not
	# their LOD.
	CheckRev \
	    {Branch starting revisions have to have their LOD as branch parent symbol} \
	    {at the beginning of its branch does not have the branch symbol as its LOD} {
		SELECT F.name, R.rev
		FROM revision R, file F
		WHERE R.fid = F.fid         -- get file of revision
		AND   R.bparent IS NOT NULL -- limit to branch-first revisions
		AND   R.lod != R.bparent    -- out-of-branch parent wrongly is not the lod
		;
	    }
	# Find all revisions with a branch parent symbol whose parent
	# is in the same line of development.
	CheckRev \
	    {Revisions and their branch children have to be in different LODs} \
	    {at the beginning of its branch and its parent wrongly agree about their LOD} {
		SELECT F.name, R.rev
		FROM revision R, revision P, file F
		WHERE R.fid = F.fid          -- get file of revision
		AND   R.bparent IS NOT NULL  -- limit to branch-first revisions
		AND   R.parent = P.rid       -- get out-of-branch parent of revision
		AND   R.lod = P.lod          -- rev and parent wrongly agree on lod
		;
	    }
	return
    }

    proc Meta {} {
	# This code performs a number of paranoid checks of the
................................................................................
	# Find all revisions which disgree with their meta data about
	# the branch/line of development they belong to.
	CheckRev \
	    {Revisions and their meta data have to be in the same LOD} \
	    {disagrees with its meta data about owning LOD} {
		SELECT F.name, R.rev
		FROM revision R, meta M, file F
		WHERE R.mid = M.mid   -- get meta data of revision
		AND   R.lod != M.bid  -- rev wrongly disagrees with meta about lod
		AND   R.fid = F.fid   -- get file of revision
		;
	    }
	return
    }

    proc RevisionChangesets {} {
	# This code performs a number of paranoid checks of the
................................................................................
                -- revision to aggregate the changeset, counting
                -- them. From the resulting revision/count table
                -- select those with more than one user, and get their
                -- associated file (name) for display.

		SELECT F.name, R.rev
		FROM revision R, file F,
		     (SELECT CI.iid        AS rid,  -- revision item
		             count(CI.cid) AS count -- number of csets using item
		      FROM csitem CI, changeset C
		      WHERE C.type = 0            -- limit to revision csets

		      AND   C.cid  = CI.cid       -- get item in changeset
		      GROUP BY CI.iid             -- aggregate by item, count csets/item
		     ) AS U
		WHERE U.count > 1    -- limit to item with multiple users
		AND   R.rid = U.rid  -- get revision of item
		AND   R.fid = F.fid  -- get file of revision
	    }
	# All revisions have to refer to the same meta information as
	# their changeset.
	CheckRevCS \
	    {All revisions have to agree with their changeset about the used meta information} \
	    {disagrees with its changeset @ about the meta information} {
		SELECT CT.name, C.cid, F.name, R.rev
................................................................................
	# for the changesets found thusly.
	CheckCS \
	    {All revisions in a changeset have to belong to the same LOD} \
	    {: Its revisions disagree about the LOD they belong to} {
		SELECT T.name, C.cid
		FROM   changeset C, cstype T
		WHERE  C.cid IN (SELECT U.cid
				 FROM (SELECT DISTINCT       -- unique cset/lod pairs
				              CI.cid AS cid, -- revision cset
				              R.lod  AS lod  -- lod of item in cset
				       FROM   csitem CI, changeset C, revision R
				       WHERE  CI.iid = R.rid  -- get rev of item in cset
				       AND    C.cid  = CI.cid -- get changeset of item
				       AND    C.type = 0      -- limit to rev csets
				      ) AS U
				 GROUP BY U.cid          -- aggregate by cset, count lods/cset
				 HAVING COUNT(U.lod) > 1 -- find csets with multiple lods
				)
		AND    T.tid = C.type
	    }
	# All revisions have to agree on the project their changeset
	# belongs to. In other words, all revisions in a changeset
	# have to refer to the same project.
	#
	# Instead of looking at all pairs of revisions in all
................................................................................
	# for the changesets found thusly.
	CheckCS \
	    {All revisions in a changeset have to belong to the same project} \
	    {: Its revisions disagree about the project they belong to} {
		SELECT T.name, C.cid
		FROM   changeset C, cstype T
		WHERE  C.cid IN (SELECT U.cid
				 FROM (SELECT DISTINCT       -- unique cset/proj pairs
				              CI.cid AS cid, -- rev cset
				              F.pid  AS pid  -- project of item in cset
				       FROM   csitem CI, changeset C, revision R, file F
				       WHERE  CI.iid = R.rid  -- get rev of item in cset
				       AND    C.cid  = CI.cid -- get changeset of item
				       AND    C.type = 0      -- limit to rev changesets
				       AND    F.fid  = R.fid  -- get file of revision
				      ) AS U
				 GROUP BY U.cid          -- aggregate by csets, count proj/cset
				 HAVING COUNT(U.pid) > 1 -- find csets with multiple projects
				)
		AND    T.tid = C.type -- get readable changeset type
	    }
	# All revisions in a single changeset have to belong to
	# different files. Conversely: No two revisions of a single
	# file are allowed to be in the same changeset.
	#
	# Instead of looking at all pairs of revisions in all
	# changesets we generate the distinct set of all files
................................................................................
	# changesets found thusly.
	CheckCS \
	    {All revisions in a changeset have to belong to different files} \
	    {: Its revisions share files} {
		SELECT T.name, C.cid
		FROM   changeset C, cstype T
		WHERE  C.cid IN (SELECT VV.cid
				 FROM (SELECT U.cid         AS cid,   -- rev changeset
				              COUNT (U.fid) AS fcount -- number of files by items
				       FROM (SELECT DISTINCT       -- unique cset/file pairs
					            CI.cid AS cid, -- rev changeset
					            R.fid AS fid   -- file of item in changeset
					     FROM   csitem CI, changeset C, revision R
					     WHERE  CI.iid = R.rid  -- get rev of item in changeset
					     AND    C.cid  = CI.cid -- get changeset of item
					     AND    C.type = 0      -- limit to rev csets
					     ) AS U
				       GROUP BY U.cid -- aggregate by csets, count files/cset
				      ) AS UU,
				      (SELECT V.cid         AS cid,   -- rev changeset
				              COUNT (V.iid) AS rcount -- number of items
				       FROM   csitem V, changeset X
				       WHERE  X.cid  = V.cid  -- get changeset of item
				       AND    X.type = 0      -- limit to rev csets
				       GROUP BY V.cid         -- aggregate by csets, count items/cset
				      ) AS VV
				 WHERE VV.cid = UU.cid        -- sync #items/cset with #files/cset
				 AND   UU.fcount < VV.rcount  -- less files than items
				                              -- => items belong to the same file.
				)
		AND    T.tid = C.type -- get readable changeset type
	    }
	return
    }

    proc TagChangesets {} {
	# This code performs a number of paranoid checks of the
	# database, searching for inconsistent changeset/revision
................................................................................
                -- the changeset, counting them. From the resulting
                -- tag/count table select those with more than one
                -- user, and get their associated file (name) for
                -- display.

		SELECT P.name, S.name
		FROM tag T, project P, symbol S,
		     (SELECT CI.iid        AS iid,  -- item
		             count(CI.cid) AS count -- number of csets using item
		      FROM csitem CI, changeset C
		      WHERE C.type = 1       -- limit to tag csets
		      AND   C.cid  = CI.cid  -- get items of cset
		      GROUP BY CI.iid        -- aggregate by item, count csets/item
		     ) AS U
		WHERE U.count > 1            -- find tag item used multiple times
		AND   T.tid = U.iid          -- get tag of item
		AND   S.sid = T.sid          -- get symbol of tag
		AND   P.pid = S.pid          -- get project of symbol
	    }
	if 0 {
	    # This check is disabled for the moment. Apparently tags
	    # can cross lines of development, at least if the involved
	    # LODs are the trunk, and the NTDB. That makes sense, as
	    # the NTDB revisions are initially logically a part of the
	    # trunk. The standard check below however does not capture
................................................................................
	# thusly.
	CheckCS \
	    {All tags in a changeset have to belong to the same project} \
	    {: Its tags disagree about the project they belong to} {
		SELECT T.name, C.cid
		FROM   changeset C, cstype T
		WHERE  C.cid IN (SELECT U.cid
				 FROM (SELECT DISTINCT       -- unique cset/proj pairs
				              CI.cid AS cid, -- tag cset
				              F.pid  AS pid  -- project of item in cset
				       FROM   csitem CI, changeset C, tag T, file F
				       WHERE  CI.iid = T.tid  -- get tag of item in cset
				       AND    C.cid  = CI.cid -- get changeset of item
				       AND    C.type = 1      -- limit to tag changesets
				       AND    F.fid  = T.fid  -- get file of tag
                                      ) AS U
				 GROUP BY U.cid           -- aggregate by csets, count proj/cset
				 HAVING COUNT(U.pid) > 1  -- find csets with multiple projects
		                )
		AND    T.tid = C.type -- get readable changeset type
	    }
	# All tags in a single changeset have to belong to different
	# files. Conversely: No two tags of a single file are allowed
	# to be in the same changeset.
	#
	# Instead of looking at all pairs of tags in all changesets we
	# generate the distinct set of all files referenced by the
................................................................................
	# information for the changesets found thusly.
	CheckCS \
	    {All tags in a changeset have to belong to different files} \
	    {: Its tags share files} {
		SELECT T.name, C.cid
		FROM   changeset C, cstype T
		WHERE  C.cid IN (SELECT VV.cid
				 FROM (SELECT U.cid         AS cid,   -- changeset
				              COUNT (U.fid) AS fcount -- number of files by items
				       FROM (SELECT DISTINCT       -- unique cset/file pairs
					            CI.cid AS cid, -- tag changeset
					            T.fid  AS fid  -- file of item in changeset
					     FROM   csitem CI, changeset C, tag T
					     WHERE  CI.iid = T.tid -- get tag of item in changeset
					     AND    C.cid = CI.cid -- get changeset of item
					     AND    C.type = 1     -- limit to tag changesets
					     ) AS U
				       GROUP BY U.cid -- aggregate by csets, count files/cset
                                      ) AS UU,
				      (SELECT V.cid         AS cid,   -- changeset
				              COUNT (V.iid) AS rcount -- number of items in cset
				       FROM   csitem V, changeset X
				       WHERE  X.cid  = V.cid -- get changeset of item
				       AND    X.type = 1     -- limit to tag changesets
				       GROUP BY V.cid        -- aggregate by csets, count items/cset
                                      ) AS VV
				 WHERE VV.cid = UU.cid       -- sync #items/cset with #files/cset
				 AND   UU.fcount < VV.rcount -- less files than items
				                             -- => items belong to the same file.
				)
		AND    T.tid = C.type -- get readable changeset type
	    }
	return
    }

    proc BranchChangesets {} {
	# This code performs a number of paranoid checks of the
	# database, searching for inconsistent changeset/revision
................................................................................
		FROM project P, branch B, symbol S
		WHERE B.bid IN (SELECT bid                    -- All branches
				FROM   branch
				EXCEPT                        -- subtract
				SELECT CI.iid                 -- branches used
				FROM   csitem CI, changeset C
				WHERE  C.cid = CI.cid         -- by any branch
				AND    C.type = 2             -- changeset
			       )
		AND   S.sid = B.sid               -- get symbol of branch
		AND   P.pid = S.pid               -- get project of symbol
	    }
	# Find all branches which are used by more than one changeset.
	CheckRev \
	    {All branches have to be used by at most one changeset} \
	    {is used by multiple changesets} {
................................................................................
                -- aggregate the changeset, counting them. From the
                -- resulting branch/count table select those with more
                -- than one user, and get their associated file (name)
                -- for display.

		SELECT P.name, S.name
		FROM branch B, project P, symbol S,
		     (SELECT CI.iid        AS iid,  -- item
                             count(CI.cid) AS count -- number of csets for item
		      FROM csitem CI, changeset C
		      WHERE C.type = 2        -- limit to branch changesets,
		      AND   C.cid = CI.cid    -- get the items they contain,
		      GROUP BY CI.iid         -- aggregate by items, count csets/item (x)
                     ) AS U
		WHERE U.count > 1             -- find items used multiple times
		AND   B.bid = U.iid           -- get the users (branch changesets)
		AND   S.sid = B.sid           -- get symbol of branch
		AND   P.pid = S.pid           -- get project of symbol
	    }
	if 0 {
	    # This check has been disabled. When the converter was run
	    # on the Tcl CVS several branches tripped this
	    # constraint. One of them was a free-floating branch, and
	    # its handling has been fixed by now. The others however
	    # seem semi-legitimate, in the sense that they show
................................................................................
	# for the changesets found thusly.
	CheckCS \
	    {All branches in a changeset have to belong to the same project} \
	    {: Its branches disagree about the project they belong to} {
		SELECT T.name, C.cid
		FROM   changeset C, cstype T
		WHERE  C.cid IN (SELECT U.cid
				 FROM (SELECT DISTINCT        -- Unique cset/proj pairs
				              CI.cid AS cid,  -- Branch cset
				              F.pid  AS pid   -- Project of item in cset
				       FROM   csitem CI, changeset C, branch B, file F
				       WHERE  CI.iid = B.bid  -- get branch of item in cset
				       AND    C.cid  = CI.cid -- get changeset of item
				       AND    C.type = 2      -- limit to branch changesets
				       AND    F.fid  = B.fid  -- get file of branch
                                      ) AS U
				 GROUP BY U.cid          -- aggregate by csets, count proj/cset
				 HAVING COUNT(U.pid) > 1 -- find cset with multiple projects
				)
		AND    T.tid = C.type -- get readable changeset type
	    }
	# All branches in a single changeset have to belong to
	# different files. Conversely: No two branches of a single
	# file are allowed to be in the same changeset.
	#
	# Instead of looking at all pairs of branches in all
	# changesets we generate the distinct set of all files
................................................................................
	# changesets found thusly.
	CheckCS \
	    {All branches in a changeset have to belong to different files} \
	    {: Its branches share files} {
		SELECT T.name, C.cid
		FROM   changeset C, cstype T
		WHERE  C.cid IN (SELECT VV.cid
				 FROM (SELECT U.cid         AS cid,   -- changeset
				              COUNT (U.fid) AS fcount -- number of files by items
				       FROM (SELECT DISTINCT       -- unique cset/file pairs
					            CI.cid AS cid, -- Branch changeset
					            B.fid  AS fid  -- File of item in changeset
					     FROM   csitem CI, changeset C, branch B
					     WHERE  CI.iid = B.bid  -- get tag of item in changeset
					     AND    C.cid  = CI.cid -- get changeset of item
					     AND    C.type = 2      -- limit to branch changesets
					     ) AS U
				       GROUP BY U.cid -- aggregate by csets, count files/cset
				      ) AS UU,
				      (SELECT V.cid         AS cid,   -- changeset
				              COUNT (V.iid) AS rcount -- number of items in cset
				       FROM   csitem V, changeset X
				       WHERE  X.cid  = V.cid -- get changeset of item
				       AND    X.type = 2     -- limit to branch changesets
				       GROUP BY V.cid	     -- aggregate by csets, count items/cset
				      ) AS VV
				 WHERE VV.cid = UU.cid       -- sync #items/cset with #files/cset
				 AND   UU.fcount < VV.rcount -- less files than items
							     -- => items belong to the same file.
				)
		AND    T.tid = C.type -- get readable changeset type
	    }
	return
    }

    proc ___UnusedChangesetChecks___ {} {
	# This code performs a number of paranoid checks of the
	# database, searching for inconsistent changeset/revision
................................................................................
	# changeset's tag associated with them.
	CheckRevCS \
	    {All revisions used by tag symbol changesets have to have the changeset's tag attached to them} \
	    {does not have the tag of its symbol changeset @ attached to it} {
		SELECT CT.name, C.cid, F.name, R.rev
		FROM   changeset C, cstype CT, revision R, file F, csitem CI, tag T
		WHERE  C.type = 1       -- symbol changesets only
		AND    C.src  = T.sid   -- tag only, linked by symbol id
		AND    C.cid  = CI.cid  -- changeset --> its revisions
		AND    R.rid  = CI.iid  -- look at the revisions
		-- and look for the tag among the attached ones.
		AND    T.sid NOT IN (SELECT TB.sid
				     FROM   tag TB
				     WHERE  TB.rev = R.rid)
		AND    R.fid = F.fid    -- get file of revision