mantra 0.6.2

SQLite format 3

@  ;$.
;.r��$������	
x��h��/�o��I]7
indexsqlite_autoindex_UnrelatedTestCoverage_1UnrelatedTestCoverage�v%%
�/tableTestCoverageTestCoverageCREATE TABLE TestCoverage (
    req_id text not null references Requirements(id),
    test_run_name text not null,
    test_run_date text not null,
    test_name text not null,
    trace_filepath text not null,
    trace_line integer not null,
    primary key (req_id, test_run_name, test_run_date, test_name, trace_filepath, trace_line),
    foreign key (test_run_name, test_run_date, test_name) references Tests(test_run_name, test_run_date, name) on delete cascade,
    foreign key (req_id, trace_filepath, trace_line) references Traces(req_id, filepath, line) on delete cascade
)7K%
indexsqlite_autoindex_TestCoverage_1TestCoverage�%%
�KtableSkippedTestsSkippedTestsCREATE TABLE SkippedTests (
    test_run_name text not null,
    test_run_date text not null,
    name text not null,
    filepath text not null,
    line integer not null,
    reason text,
    primary key (test_run_name, test_run_date, name),
    foreign key (test_run_name, test_run_date) references TestRuns(name, date) on delete cascade
)7K%
indexsqlite_autoindex_SkippedTests_1SkippedTests�{
�UtableTestsTestsCREATE TABLE Tests (
    test_run_name text not null,
    test_run_date text not null,
    name text not null,
    filepath text not null,
    line integer not null,
    passed integer not null,
    primary key (test_run_name, test_run_date, name),
    foreign key (test_run_name, test_run_date) references TestRuns(name, date) on delete cascade
))=
indexsqlite_autoindex_Tests_1Tests�J

�gtableTestRunsTestRunsCREATE TABLE TestRuns (
    name text not null,
    date text not null,
    nr_of_tests integer not null,
    meta text,
    logs text,
    primary key (name, date)
)/C
indexsqlite_autoindex_TestRuns_1TestRuns�J++
�KtableUnrelatedTracesUnrelatedTracesCREATE TABLE UnrelatedTraces (
    req_id text not null,
    filepath text not null,
    line integer not null,
    primary key (req_id, filepath, line)
)=Q+
indexsqlite_autoindex_UnrelatedTraces_1UnrelatedTraces
�Z	!!
�tableTraceSpansTraceSpans
CREATE TABLE TraceSpans (
    req_id text not null,
    filepath text not null,
    line integer not null,
    start integer not null,
    end integer not null,
    primary key (req_id, filepath, line),
    foreign key (req_id, filepath, line) references Traces(req_id, filepath, line) on delete cascade
)3
G!
indexsqlite_autoindex_TraceSpans_1TraceSpans�
�YtableTracesTracesCREATE TABLE Traces (
    req_id text not null references Requirements(id) on delete cascade,
    generation integer not null,
    filepath text not null,
    line integer not null,
    primary key (req_id, filepath, line)
)+?
indexsqlite_autoindex_Traces_1Traces	�99
�YtableRequirementHierarchiesRequirementHierarchiesCREATE TABLE RequirementHierarchies (
    child_id text not null references Requirements(id) on delete cascade,
    parent_id text not null references Requirements(id) on delete cascade,
    primary key (child_id, parent_id)
)K_9
indexsqlite_autoindex_RequirementHierarchies_1RequirementHierarchies�%%
�KtableRequirementsRequirementsCREATE TABLE Requirements (
    id text not null primary key,
    generation integer not null,
    title text not null,
    link text not null,
    info text,
    manual bool not null,
    deprecated bool not null
)7K%
indexsqlite_autoindex_Requirements_1Requirements�)
--
�table_sqlx_migrations_sqlx_migrationsCREATE TABLE _sqlx_migrations (
    version BIGINT PRIMARY KEY,
    description TEXT NOT NULL,
    installed_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    success BOOLEAN NOT NULL,
    checksum BLOB NOT NULL,
    execution_time BIGINT NOT NULL
)?S-
indexsqlite_autoindex__sqlx_migrati#7"5!. *!

��[
	'3	lInitialSchema2024-07-01 16:20:21
��K��
�LI �#�$'�i6��D�k(N��ȱ��#H��ݐ�UG��qG�B�

��		
	�f ��@
�
f
�o�=
�
�
1	�	l	Q)
?Eid_from_schemaSome req added from JSON.local{"custom_field":"some data"}V=	kid.sub_id_3.sub_sub_id_2Sub-subhttps://github.com/mhatzl/mantra/tree/main/reqsk_	#kid.sub_id_3.sub_sub_id_1.sub_sub_sub_id_2Sub-sub-subhttps://github.com/mhatzl/mantra/tree/main/reqsk_	#k	id.sub_id_3.sub_sub_id_1.sub_sub_sub_id_1Sub-sub-subhttps://github.com/mhatzl/mantra/tree/main/reqsV=	kid.sub_id_3.sub_sub_id_1Sub-subhttps://github.com/mhatzl/mantra/tree/main/reqsE#	kid.sub_id_3Subhttps://github.com/mhatzl/mantra/tree/main/reqsV=	kid.sub_id_2.sub_sub_id_2Sub-subhttps://github.com/mhatzl/mantra/tree/main/reqsk
_	#kid.sub_id_2.sub_sub_id_1.sub_sub_sub_id_2Sub-sub-subhttps://github.com/mhatzl/mantra/tree/main/reqsk_	#kid.sub_id_2.sub_sub_id_1.sub_sub_sub_id_1Sub-sub-subhttps://github.com/mhatzl/mantra/tree/main/reqsV=	k	id.sub_id_2.sub_sub_id_1Sub-subhttps://github.com/mhatzl/mantra/tree/main/reqsE
#	kid.sub_id_2Subhttps://github.com/mhatzl/mantra/tree/main/reqsV	=	kid.sub_id_1.sub_sub_id_2Sub-subhttps://github.com/mhatzl/mantra/tree/main/reqsk_	#kid.sub_id_1.sub_sub_id_1.sub_sub_sub_id_2Sub-sub-subhttps://github.com/mhatzl/mantra/tree/main/reqsk_	#kid.sub_id_1.sub_sub_id_1.sub_sub_sub_id_1Sub-sub-subhttps://github.com/mhatzl/mantra/tree/main/reqsV=	k	id.sub_id_1.sub_sub_id_1Sub-subhttps://github.com/mhatzl/mantra/tree/main/reqsE#	kid.sub_id_1Subhttps://github.com/mhatzl/mantra/tree/main/reqs?	kidParenthttps://github.com/mhatzl/mantra/tree/main/reqsD	kother_idOtherhttps://github.com/mhatzl/mantra/tree/main/reqsT)	+kmain_id.sub_idSub-requirementhttps://github.com/mhatzl/mantra/tree/main/reqsB
	kmain_idMainhttps://github.com/mhatzl/mantra/tree/main/reqs

����sE(����rU'
�
�
����)
id_from_schema=
id.sub_id_3.sub_sub_id_2-_
id.sub_id_3.sub_sub_id_1.sub_sub_sub_id_2-_
id.sub_id_3.sub_sub_id_1.sub_sub_sub_id_1=
id.sub_id_3.sub_sub_id_1#
id.sub_id_3=
id.sub_id_2.sub_sub_id_2-_
id.sub_id_2.sub_sub_id_1.sub_sub_sub_id_2
-_
id.sub_id_2.sub_sub_id_1.sub_sub_sub_id_1=
id.sub_id_2.sub_sub_id_1#
id.sub_id_2
=
id.sub_id_1.sub_sub_id_2	-_
id.sub_id_1.sub_sub_id_1.sub_sub_sub_id_2-_
id.sub_id_1.sub_sub_id_1.sub_sub_sub_id_1=
id.sub_id_1.sub_sub_id_1#
id.sub_id_1
id
other_id)
main_id.sub_id
	main_id

���f ���x2

�
�
�
D
&=#id.sub_id_3.sub_sub_id_2id.sub_id_3D_=id.sub_id_3.sub_sub_id_1.sub_sub_sub_id_2id.sub_id_3.sub_sub_id_1D_=id.sub_id_3.sub_sub_id_1.sub_sub_sub_id_1id.sub_id_3.sub_sub_id_1&
=#id.sub_id_3.sub_sub_id_1id.sub_id_3#id.sub_id_3id&=#id.sub_id_2.sub_sub_id_2id.sub_id_2D
_=id.sub_id_2.sub_sub_id_1.sub_sub_sub_id_2id.sub_id_2.sub_sub_id_1D	_=id.sub_id_2.sub_sub_id_1.sub_sub_sub_id_1id.sub_id_2.sub_sub_id_1&=#id.sub_id_2.sub_sub_id_1id.sub_id_2#id.sub_id_2id&=#id.sub_id_1.sub_sub_id_2id.sub_id_1D_=id.sub_id_1.sub_sub_id_1.sub_sub_sub_id_2id.sub_id_1.sub_sub_id_1D_=id.sub_id_1.sub_sub_id_1.sub_sub_sub_id_1id.sub_id_1.sub_sub_id_1&=#id.sub_id_1.sub_sub_id_1id.sub_id_1#id.sub_id_1id
)main_id.sub_idmain_id


��c���p)
�
�
}
6

�(=#
id.sub_id_3.sub_sub_id_2id.sub_id_3F_=
id.sub_id_3.sub_sub_id_1.sub_sub_sub_id_2id.sub_id_3.sub_sub_id_1F_=
id.sub_id_3.sub_sub_id_1.sub_sub_sub_id_1id.sub_id_3.sub_sub_id_1(=#
id.sub_id_3.sub_sub_id_1id.sub_id_3
#
id.sub_id_3id(=#
id.sub_id_2.sub_sub_id_2id.sub_id_2F_=
id.sub_id_2.sub_sub_id_1.sub_sub_sub_id_2id.sub_id_2.sub_sub_id_1
F_=
id.sub_id_2.sub_sub_id_1.sub_sub_sub_id_1id.sub_id_2.sub_sub_id_1	(=#
id.sub_id_2.sub_sub_id_1id.sub_id_2#
id.sub_id_2id(=#
id.sub_id_1.sub_sub_id_2id.sub_id_1F_=
id.sub_id_1.sub_sub_id_1.sub_sub_sub_id_2id.sub_id_1.sub_sub_id_1F_=
id.sub_id_1.sub_sub_id_1.sub_sub_sub_id_1id.sub_id_1.sub_sub_id_1(=#
id.sub_id_1.sub_sub_id_1id.sub_id_1#
id.sub_id_1id)	main_id.sub_idmain_id
��r;����	+	other_idsrc/traces.yaml	'
other_idsrc/traces.rs	'	main_idsrc/traces.rs(#	;
id.sub_id_3src/hierarchy_test.yaml5=	;
id.sub_id_2.sub_sub_id_2src/hierarchy_test.yaml5=	;
id.sub_id_2.sub_sub_id_1src/hierarchy_test.yamlE_	;	id.sub_id_1.sub_sub_id_1.sub_sub_sub_id_2src/hierarchy_test.yamlE
_	;	id.sub_id_1.sub_sub_id_1.sub_sub_sub_id_1src/hierarchy_test.yaml
��s<����+	
other_idsrc/traces.yaml'

other_idsrc/traces.rs'	
main_idsrc/traces.rs)#;

id.sub_id_3src/hierarchy_test.yaml6=;

id.sub_id_2.sub_sub_id_2src/hierarchy_test.yaml6=;

id.sub_id_2.sub_sub_id_1src/hierarchy_test.yamlF_;	
id.sub_id_1.sub_sub_id_1.sub_sub_sub_id_2src/hierarchy_test.yamlE_;		id.sub_id_1.sub_sub_id_1.sub_sub_sub_id_1src/hierarchy_test.yaml
���'


other_idsrc/traces.rs

'	

main_idsrc/traces.rs
���'

other_idsrc/traces.rs'		main_idsrc/traces.rs

��
'
bad_idsrc/traces.rs

��'
	bad_idsrc/traces.rs

��M
!E	O
usage-test2024-06-11T13:07:43.3463928Z{"binary":"target\\debug\\usage"}

��*!E	usage-test2024-06-11T13:07:43.3463928Z

��P
!E7'
	usage-test2024-06-11T13:07:43.3463928Ztraces::test::test_fnsrc/traces.rs

��@!E7	usage-test2024-06-11T13:07:43.3463928Ztraces::test::test_fn


N�NV!E7'	main_idusage-test2024-06-11T13:07:43.3463928Ztraces::test::test_fnsrc/traces.rsX
!E7'
other_idusage-test2024-06-11T13:07:43.3463928Ztraces::test::test_fnsrc/traces.rs
MM�X!E7'	
main_idusage-test2024-06-11T13:07:43.3463928Ztraces::test::test_fnsrc/traces.rsY!E7'
	other_idusage-test2024-06-11T13:07:43.3463928Ztraces::test::test_fnsrc/traces.rs

��V
!E7'
bad_idusage-test2024-06-11T13:07:43.3463928Ztraces::test::test_fnsrc/traces.rs

��W!E7'
	bad_idusage-test2024-06-11T13:07:43.3463928Ztraces::test::test_fnsrc/traces.rs
�
/��
KR��
��	
x���h��
/o��I]7
indexsqlite_autoindex_UnrelatedTestCoverage_1UnrelatedTestCoverage�vI]7
indexsqlite_autoindex_UnrelatedTestCoverage_1UnrelatedTestCoverage�v%%
�/tableTestCoverageTestCoverageCREATE TABLE Test�v%%
�/tableTestCoverageTestCoverageCREATE TABLE TestCoverage (
    req_id text not null references Requirements(id),
    test_run_name text not null,
    test_run_date text not null,
    test_name text not null,
    trace_filepath text not null,
    trace_line integer not null,
    primary key (req_id, test_run_name, test_run_date, test_name, trace_filepath, trace_line),
    foreign key (test_run_name, test_run_date, test_name) references Tests(test_run_name, test_run_date, name) on delete cascade,
    foreign key (req_id, trace_filepath, trace_line) references Traces(req_id, filepath, line) on delete cascade
)7K%
indexsqlite_autoindex_SkippedTests_1SkippedTests�%%
�KtableSkippedTestsSkippedTestsCREATE TABLE SkippedTests (
    test_run_name text not null,
    test_run_date text not null,
    name text not null,
    filepath text not null,
    line integer not null,
    reason text,
    primary key (test_run_name, test_run_date, name),
    foreign key (test_run_name, test_run_date) references TestRuns(name, date) on delete cascade
))=
indexsqlite_autoindex_Tests_1Tests�{
�UtableTestsTestsCREATE TABLE Tests (
    test_run_name text not null,
    test_run_date text not null,
    name text not null,
    filepath text not null,
    line integer not null,
    passed integer not null,
    primary key (test_run_name, test_run_date, name),
    foreign key (test_run_name, test_run_date) references TestRuns(name, date) on delete cascade
)�J

�gtableTestRunsTestRunsCREATE TABLE TestRuns (
    name text not null,
    date text not null,
    nr_of_tests integer not null,
    meta text,
    logs text,
    primary key (name, date)
)/C
indexsqlite_autoindex_TestRuns_1TestRuns�J++
�KtableUnrelatedTracesUnrelatedTracesCREATE TABLE UnrelatedTraces (
    req_id text not null,
    filepath text not null,
    line integer not null,
    primary key (req_id, filepath, line)
)=Q+
indexsqlite_autoindex_UnrelatedTraces_1UnrelatedTraces
�Z	!!
�tableTraceSpansTraceSpans
CREATE TABLE TraceSpans (
    req_id text not null,
    filepath text not null,
    line integer not null,
    start integer not null,
    end integer not null,
    primary key (req_id, filepath, line),
    foreign key (req_id, filepath, line) references Traces(req_id, filepath, line) on delete cascade
)3
G!
indexsqlite_autoindex_TraceSpans_1TraceSpans�
�YtableTracesTracesCREATE TABLE Traces (
    req_id text not null references Requirements(id) on delete cascade,
    generation integer not null,
    filepath text not null,
    line integer not null,
    primary key (req_id, filepath, line)
)+?
indexsqlite_autoindex_Traces_1Traces	�99
�YtableRequirementHierarchiesRequirementHierarchiesCREATE TABLE RequirementHierarchies (
    child_id text not null references Requirements(id) on delete cascade,
    parent_id text not null references Requirements(id) on delete cascade,
    primary key (child_id, parent_id)
)K_9
indexsqlite_autoindex_RequirementHierarchies_1RequirementHierarchies�%%
�KtableRequirementsRequirementsCREATE TABLE Requirements (
    id text not null primary key,
    generation integer not null,
    title text not null,
    link text not null,
    info text,
    manual bool not null,
    deprecated bool not null
)7K%
indexsqlite_autoindex_Requirements_1Requirements�)
--
�table_sqlx_migrations_sqlx_migrationsCREATE TABLE _sqlx_migrations (
    version BIGINT PRIMARY KEY,
    description TEXT NOT NULL,
    installed_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    success BOOLEAN NOT NULL,
    checksum BLOB NOT NULL,
    execution_time BIGINT NOT NULL
)?S-
indexsqlite_autoindex__sqlx_migrations_1_sqlx_migrations
\�	��dv-�5�^
j�b
��
�\\�(--�viewLeafRequirementsLeafRequirementsCREATE VIEW LeafRequirements as
select id
from Requirements
where id not in (select parent_id from RequirementHierarchies)�33�+viewRequirementChildrenRequirementChildrenCREATE VIEW RequirementChildren as
with recursive TransitiveChildren(id, child_id) as
(
    select parent_id, child_id from RequirementHierarchies
    union all
    select tc.id, rh.child_id from RequirementHierarchies rh, TransitiveChildren tc
    where tc.child_id = rh.parent_id
)
select id, child_id from TransitiveChildren�n??
�ktableUnrelatedManuallyVerifiedUnrelatedManuallyVerifiedCREATE TABLE UnrelatedManuallyVerified (
    req_id text not null,
    review_name text not null,    
    review_date text not null,
    comment text,
    primary key (req_id, review_name, review_date),
    foreign key (review_name, review_date) references Reviews(name, date) on delete cascade
)Qe?
indexsqlite_autoindex_UnrelatedManuallyVerified_1UnrelatedManuallyVerified�
--
�5tableManuallyVerifiedManuallyVerifiedCREATE TABLE ManuallyVerified (
    req_id text not null references Requirements(id) on delete cascade,
    review_name text not null,    
    review_date text not null,
    comment text,
    primary key (req_id, review_name, review_date),
    foreign key (review_name, review_date) references Reviews(name, date) on delete cascade
)?S-
indexsqlite_autoindex_ManuallyVerified_1ManuallyVerified�4
�?tableReviewsReviewsCREATE TABLE Reviews (
    name text not null,
    date text not null,
    reviewer text not null,
    comment text,
    primary key (name, date)
)-A
indexsqlite_autoindex_Reviews_1Reviews	�I]7
indexsqlite_autoindex_UnrelatedTestCoverage_1UnrelatedTestCoverage�77
�#tableUnrelatedTestCoverageUnrelatedTestCoverageCREATE TABLE UnrelatedTestCoverage (
    req_id text not null,
    test_run_name text not null,
    test_run_date text not null,
    test_name text not null,
    trace_filepath text not null,
    trace_line integer not null,
    primary key (req_id, test_run_name, test_run_date, test_name, trace_filepath, trace_line),
    foreign key (test_run_name, test_run_date, test_name) references Tests(test_run_name, test_run_date, name) on delete cascade
)7K%
indexsqlite_autoindex_TestCoverage_1TestCoverage
�%%
�/tableTestCoverageTestCoverageCREATE TABLE TestCoverage (
    req_id text not null references Requirements(id),
    test_run_name text not null,
    test_run_date text not null,
    test_name text not null,
    trace_filepath text not null,
    trace_line �,"AA�gviewDirectlyTracedRequirementsDirectlyTracedRequirementsCREATE VIEW DirectlyTracedRequirements as
select r.id from Requirements r, Traces tr
where r.id = tr.req_id�U!11�YviewManualRequirementsManualRequirementsCREATE VIEW ManualRequirements as
with MarkedManual(id) as (
    select id from Requirements
    where manual = true
),
ParentMarkedManual(id) as (
    select rc.child_id
    from RequirementChildren rc, MarkedManual md
    where rc.id = md.id
),
Manual(id) as (
    select id from MarkedManual
    union
    select id from ParentMarkedManual
)
select r.id
from Requirements r, Manual d 
where r.id = d.id�[ 99�UviewDeprecatedRequirementsDeprecatedRequirementsCREATE VIEW DeprecatedRequirements as
with MarkedDeprecated(id) as (
    select id from Requirements
    where deprecated = true
),
ParentMarkedDeprecated(id) as (
    select rc.child_id
    from RequirementChildren rc, MarkedDeprecated md
    where rc.id = md.id
),
Deprecated(id) as (
    select id from MarkedDeprecated
    union
    select id from ParentMarkedDeprecated
)
select id
from Deprecated d�33�aviewNonLeafRequirementsNonLeafRequirementsCREATE VIEW NonLeafRequirements as
select id
from Requirements
except
select id
from LeafRequirements

��G
#7%9Some Review2024-05-25 22:00:00.0Manuel HatzlThis is a test review.

��$#7	Some Review2024-05-25 22:00:00.0
�kN_#7id.sub_id_3.sub_sub_id_1.sub_sub_sub_id_1Some Review2024-05-25 22:00:00.0P=#73id.sub_id_1.sub_sub_id_1Some Review2024-05-25 22:00:00.0This one was tough.A
#75other_idSome Review2024-05-25 22:00:00.0What could go wrong?
C�C�O_#7
id.sub_id_3.sub_sub_id_1.sub_sub_sub_id_1Some Review2024-05-25 22:00:00.0>=#7
id.sub_id_1.sub_sub_id_1Some Review2024-05-25 22:00:00.0-#7	other_idSome Review2024-05-25 22:00:00.0


J	��y��_�
�
�
�*�()33�{viewInvalidRequirementsInvalidRequirementsCREATE VIEW InvalidRequirements as
select d.id
from DeprecatedRequirements d, TracedRequirements t
where d.id = t.id�(55�cviewUntracedRequirementsUntracedRequirementsCREATE VIEW UntracedRequirements as
select id from Requirements
except
select id from TracedRequirements�Q';;�=viewFullyTracedRequirementsFullyTracedRequirementsCREATE VIEW FullyTracedRequirements as
with HasUntracedLeaf(id) as (
    select rc.id
    from RequirementChildren rc, LeafRequirements lr, UntracedRequirements ur
    where rc.child_id = lr.id and lr.id = ur.id
)
select lr.id
from LeafRequirements lr, DirectlyTracedRequirements dr
where lr.id = dr.id
union all
select id
from NonLeafRequirements
where id not in (select id from HasUntracedLeaf)�/&11�
viewTracedRequirementsTracedRequirementsCREATE VIEW TracedRequirements as
select id from DirectlyTracedRequirements
union
select id from IndirectlyTracedRequirements�^%//�oviewIndirectTraceTreeIndirectTraceTreeCREATE VIEW IndirectTraceTree as
with CompactTraceEntry(id, traced_id, trace) as (
	select id, traced_id, json_object('filepath', filepath, 'line', line)
	from IndirectRequirementTraces
), GroupedTraceEntry(id, traced_id, trace_list) as (
	select id, traced_id, '[' || group_concat(json(trace)) || ']'
	from CompactTraceEntry
	group by id, traced_id
)
select id, traced_id, json(trace_list) as traces
from GroupedTraceEntry�$??�=viewIndirectRequirementTracesIndirectRequirementTracesCREATE VIEW IndirectRequirementTraces as
select ir.id, c.child_id as traced_id, t.filepath, t.line
from IndirectlyTracedRequirements ir, RequirementChildren c, Traces t
where ir.id = c.id and c.child_id = t.req_id�#EE�viewIndirectlyTracedRequirementsIndirectlyTracedRequirementsCREATE VIEW IndirectlyTracedRequirements as
with recursive IsIndirectlyUntraced(id) as (
    -- Leaf requirements cannot be traced indirectly
    select id
    from LeafRequirements
    where id not in (select id from DirectlyTracedRequirements)
    union all
    -- Recursively get requirements that are not indirectly traced
    select r.id
    from NonLeafRequirements r, RequirementHierarchies rh, IsIndirectlyUntraced u
    where r.id = rh.parent_id
    and rh.child_id = u.id
),
-- Neither directly or indirectly traced requirements
IsUntraced(id) as (
    select id from IsIndirectlyUntraced
    except
    select id from DirectlyTracedRequirements
),
HasUntracedChild(id) as (
    select rh.parent_id
    from RequirementHierarchies rh, IsUntraced u
    where rh.child_id = u.id
)
-- Only non-leaf requirements can be indirectly traced
select distinct id
from NonLeafRequirements
where id not in (select id from HasUntracedChild)�,"AA�gviewDirectlyTracedRequirementsDirectlyTracedRequirementsCREATE VIEW DirectlyTracedRequirements as
select r.id from Requirements r, Traces tr
where r.id = tr.req_id
�11�YviewManualRequirementsManualRequirementsCREATE VIEW ManualRequirements as
with MarkedManual(id) as (
    select id from Requirements
    where manual = true
),
ParentMarkedManual(id) as (
    select rc.child_id
    from RequirementChildren rc, MarkedManual md
    where rc.id = md.id
),
Manual(id) as (
    select id from MarkedManual
    union
    sele,�y+??�viewDirectRequirementCoverageDirectReq�7*CC�yviewDirectlyCoveredRequirementsDirectlyCoveredRequirementsCREATE VIEW DirectlyCoveredRequirements as
select id from Requirements
where id in (select req_id from TestCoverage)�()33�{viewInvalidRequirementsInvalidRequirementsCREATE VIEW InvalidRequirements as
select d.id
from DeprecatedRequirements d, TracedRequirements t
where d.id = t.id�(55�cviewUntracedRequirementsUntracedRequirementsCREATE VIEW UntracedRequirements as
select id from Requirements
except
select id from TracedRequirements
�~��
�
�
�/�.KK�{viewIndirectRequirementTestCoverageIndirectRequirementTestCoverageCREATE VIEW IndirectRequirementTestCoverage as
select r.id, c.child_id as covered_id,
v.test_run_name, v.test_run_date, v.test_name,
v.trace_filepath, v.trace_line,
coalesce(t.passed, 0) as test_passed
from IndirectlyCoveredRequirements r, RequirementChildren c, TestCoverage v, Tests t
where r.id = c.id and c.child_id = v.req_id
and v.test_run_name = t.test_run_name and v.test_run_date = t.test_run_date
and v.test_name = t.name�-GG�5viewIndirectlyCoveredRequirementsIndirectlyCoveredRequirementsCREATE VIEW IndirectlyCoveredRequirements as
with recursive IsIndirectlyUncovered(id) as (
    -- Leaf requirements cannot be covered indirectly
    select id
    from LeafRequirements
    where id not in (select id from DirectlyCoveredRequirements)
    union all
    -- Recursively get requirements that are not indirectly covered
    select r.id
    from NonLeafRequirements r, RequirementHierarchies rh, IsIndirectlyUncovered u
    where r.id = rh.parent_id
    and rh.child_id = u.id
),
-- Neither directly or indirectly covered requirements
IsUncovered(id) as (
    select id from IsIndirectlyUncovered
    except
    select id from DirectlyCoveredRequirements
),
HasUncoveredChild(id) as (
    select rh.parent_id
    from RequirementHierarchies rh, IsUncovered u
    where rh.child_id = u.id
)
-- Only non-leaf requirements can be indirectly uncovered
select distinct id
from NonLeafRequirements
where id not in (select id from HasUncoveredChild)�*,11�viewDirectCoverageTreeDirectCoverageTreeCREATE VIEW DirectCoverageTree as
with CompactTraceEntry(id, test_run_name, test_run_date, test_name, test_passed, trace) as (
	select id, test_run_name, test_run_date, test_name, test_passed,
	json_object('filepath', trace_filepath, 'line', trace_line)
	from DirectRequirementCoverage
), GroupedTraceEntry(id, test_run_name, test_run_date, test_name, test_passed, trace_list) as (
	select id, test_run_name, test_run_date, test_name, test_passed, '[' || group_concat(trace) || ']'
	from CompactTraceEntry
	group by id, test_run_name, test_run_date, test_name
), CompactTestEntry(id, test_run_name, test_run_date, test) as (
	select id, test_run_name, test_run_date,
	json_object('name', test_name, 'passed', case when test_passed = 1 then json('true') else json('false') end, 'traces', json(trace_list))
	from GroupedTraceEntry
), GroupedTestEntry(id, test_run_name, test_run_date, test_list) as (
	select id, test_run_name, test_run_date, '[' || group_concat(test) || ']'
	from CompactTestEntry
	group by id, test_run_name, test_run_date
)
select id, test_run_name, test_run_date, json(test_list) as tests from GroupedTestEntry�y+??�viewDirectRequirementCoverageDirectRequirementCoverageCREATE VIEW DirectRequirementCoverage as
select v.req_id as id, v.test_run_name, v.test_run_date, v.test_name,
v.trace_filepath, v.trace_line, coalesce(t.passed, 0) as test_passed
from TestCoverage v, Tests t
where v.test_run_name = t.test_run_name and v.test_run_date = t.test_run_date
and v.test_name = t.name�.KK�{viewIndirectRequirementTestCoverageIndirectRequirementTestCoverageCREATE VIEW IndirectRequirementTestCoverage as
select r.id, c.child_id as covered_id,
v.test_run_name, v.test_run_date, v.test_name,
v.trace_filepath, v.trace_line,
coalesce(t.passed, 0) as test_passed
from IndirectlyCoveredRequirements r, RequirementChildren c, TestCoverage v, Tests t
where r.id = c.id and c.child_id = v.req_id
and v.test_run_name = t.test_run_name and v.test_run_date = t.test_run_date
and v.test_name = t.naments
�	]�
I��6�5==�/viewFullyCoveredRequirementsFullyCoveredRequirementsCREATE VIEW FullyCoveredRequirements as
with HasUncoveredOrFailedLeaf(id) as (
    select rc.id
    from RequirementChildren rc, LeafRequirements lr, UncoveredRequirements ur
    where rc.child_id = lr.id and lr.id = ur.id
    union all
    select rc.id
    from RequirementChildren rc, LeafRequirements lr, FailedCoveredRequirements fr
    where rc.child_id = lr.id and lr.id = fr.id
)
select lr.id
from LeafRequirements lr, PassedCoveredRequirements pr
where lr.id = pr.id
union all
select id
from NonLeafRequirements
where id not in (select id from HasUncoveredOrFailedLeaf)�;4??�	viewPassedCoveredRequirementsPassedCoveredRequirementsCREATE VIEW PassedCoveredRequirements as
select id from CoveredRequirements
except
select id from FailedCoveredRequirements�{3??�	viewFailedRequirementCoverageFailedRequirementCoverageCREATE VIEW FailedRequirementCoverage as
select fr.id, null as covered_id, fc.test_run_name, fc.test_run_date, fc.test_name, fc.filepath, fc.line
from FailedCoveredRequirements fr, FailedTestCoverage fc
where fr.id = fc.req_id
union all
select fr.id, fr.covered_id as covered_id, fc.test_run_name, fc.test_run_date, fc.test_name, fc.filepath, fc.line
from FailedCoveredRequirements fr, FailedTestCoverage fc
where fr.covered_id = fc.req_id�y2??�viewFailedCoveredRequirementsFailedCoveredRequirementsCREATE VIEW FailedCoveredRequirements as
with HasFailedChild(id, covered_id) as (
    select r.id, rc.child_id from Requirements r, RequirementChildren rc, FailedTestCoverage f
    where r.id = rc.id and rc.child_id = f.req_id
)
select c.id, hf.covered_id
from CoveredRequirements c, HasFailedChild hf
where c.id = hf.id
union all
select c.id, null as covered_id
from CoveredRequirements c, FailedTestCoverage f
where c.id = f.req_id�"177�gviewUncoveredRequirementsUncoveredRequirementsCREATE VIEW UncoveredRequirements as
select id from Requirements
except
select id from CoveredRequirements�4033�viewCoveredRequirementsCoveredRequirementsCREATE VIEW CoveredRequirements as
select id from DirectlyCoveredRequirements
union
select id from IndirectlyCoveredRequirements� /==�WviewIndirectTestCoverageTreeIndirectTestCoverageTreeCREATE VIEW IndirectTestCoverageTree as
with CompactTraceEntry(id, covered_id, test_run_name, test_run_date, test_name, test_passed, trace) as (
	select id, covered_id, test_run_name, test_run_date, test_name, test_passed,
	json_object('filepath', trace_filepath, 'line', trace_line)
	from IndirectRequirementTestCoverage
), GroupedTraceEntry(id, covered_id, test_run_name, test_run_date, test_name, test_passed, trace_list) as (
	select id, covered_id, test_run_name, test_run_date, test_name, test_passed, '[' || group_concat(trace) || ']'
	from CompactTraceEntry
	group by id, covered_id, test_run_name, test_run_date, test_name
), CompactTestEntry(id, covered_id, test_run_name, test_run_date, test) as (
	select id, covered_id, test_run_name, test_run_date,
	json_object('name', test_name, 'passed', case when test_passed = 1 then json('true') else json('false') end, 'traces', json(trace_list))
	from GroupedTraceEntry
), GroupedTestEntry(id, covered_id, test_run_name, test_run_date, test_list) as (
	select id, covered_id, test_run_name, test_run_date, '[' || group_concat(test) || ']'
	from CompactTestEntry
	group by id, covered_id, test_run_name, test_run_date
), CompactTestRunEntry(id, covered_id, test_run) as (
	select id, covered_id,
	json_object('name', test_run_name, 'date', test_run_date, 'tests', json(test_list))
	from GroupedTestEntry
), GroupedTestRunEntry(id, covered_id, test_run_list) as (
	select id, covered_id, '[' || group_concat(test_run) || ']'
	from CompactTestRunEntry
	group by id, covered_id
)
select id, covered_id, json(test_run_list) as test_runs from GroupedTestRunEntry
�
��0��:�m911�	viewFailedTestCoverageFailedTestCoverageCREATE VIEW FailedTestCoverage as
select tc.req_id, tc.test_run_name, tc.test_run_date, tc.test_name, tc.trace_filepath, tc.trace_line
from TestCoverage tc, Tests t
where tc.test_run_name = t.test_run_name and tc.test_run_date = t.test_run_date
    and tc.test_name = t.name and (t.passed <> 1 or t.passed is null)�8##�sviewPassedTestsPassedTestsCREATE VIEW PassedTests as
select test_run_name, test_run_date, name, filepath, line
from Tests
where passed = 1�j7//�viewLeafChildOverviewLeafChildOverviewCREATE VIEW LeafChildOverview as
with NrLeafs(id, cnt) as (
    select rc.id, count(*)
    from RequirementChildren rc, LeafRequirements lr
    where rc.child_id = lr.id
    group by rc.id
), NrTracedLeafs(id, cnt) as (
    select rc.id, count(*)
    from RequirementChildren rc, LeafRequirements lr, DirectlyTracedRequirements dt
    where rc.child_id = lr.id and lr.id = dt.id
    group by rc.id
), NrCoveredLeafs(id, cnt) as (
    select rc.id, count(*)
    from RequirementChildren rc, LeafRequirements lr, DirectlyCoveredRequirements dc
    where rc.child_id = lr.id and lr.id = dc.id
    group by rc.id
), NrPassedCoveredLeafs(id, cnt) as (
    select rc.id, count(*)
    from RequirementChildren rc, LeafRequirements lr, PassedCoveredRequirements pc
    where rc.child_id = lr.id and lr.id = pc.id
    group by rc.id
)
select id, sum(leaf_cnt) as leaf_cnt,
sum(traced_leaf_cnt) as traced_leaf_cnt, case when sum(leaf_cnt) = 0 then 0.0 else (sum(traced_leaf_cnt)  * 1.0 / sum(leaf_cnt)) end as traced_leaf_ratio,
sum(covered_leaf_cnt) as covered_leaf_cnt, case when sum(leaf_cnt) = 0 then 0.0 else (sum(covered_leaf_cnt) * 1.0 / sum(leaf_cnt)) end as covered_leaf_ratio,
sum(passed_covered_leaf_cnt) as passed_covered_leaf_cnt, case when sum(leaf_cnt) = 0 then 0.0 else (sum(passed_covered_leaf_cnt) * 1.0 / sum(leaf_cnt)) end as passed_covered_leaf_ratio
from (
    select id, cnt as leaf_cnt, 0 as traced_leaf_cnt, 0 as covered_leaf_cnt, 0 as passed_covered_leaf_cnt
    from NrLeafs
    union all
    select id, 0 as leaf_cnt, cnt as traced_leaf_cnt, 0 as covered_leaf_cnt, 0 as passed_covered_leaf_cnt
    from NrTracedLeafs
    union all
    select id, 0 as leaf_cnt, 0 as traced_leaf_cnt, cnt as covered_leaf_cnt, 0 as passed_covered_leaf_cnt
    from NrCoveredLeafs
    union all
    select id, 0 as leaf_cnt, 0 as traced_leaf_cnt, 0 as covered_leaf_cnt, cnt as passed_covered_leaf_cnt
    from NrPassedCoveredLeafs
)
group by id�I6CC�viewRequirementCoverageOverviewRequirementCoverageOverviewCREATE VIEW RequirementCoverageOverview as
with NrRequirements(cnt) as (select count(*) from Requirements),
NrTraced(cnt) as (select count(*) from TracedRequirements),
NrCovered(cnt) as (select count(*) from CoveredRequirements),
NrPassed(cnt) as (select count(*) from PassedCoveredRequirements),
VerifiedOverview(cnt, ratio) as (
    -- Only consider manual requirements for verified cnt and ratio
    select 
        case when m.nr_manuals = 0 then null else c.cnt end as cnt,
        case when m.nr_manuals = 0 then 0.0 else (c.cnt * 1.0 / m.nr_manuals) end as ratio
    from (
        select count(*) as cnt
        from ManuallyVerifiedRequirements m, ManualRequirements r
        where m.req_id = r.id
    ) as c, (
        select count(*) as nr_manuals
        from ManualRequirements
    ) as m
)
select r.cnt as req_cnt, t.cnt as traced_cnt, case when r.cnt = 0 then 0.0 else (t.cnt * 1.0 / r.cnt) end as traced_ratio,
    c.cnt as covered_cnt, case when r.cnt = 0 then 0.0 else (c.cnt * 1.0 / r.cnt) end as covered_ratio,
    p.cnt as passed_cnt, case when r.cnt = 0 then 0.0 else (p.cnt * 1.0 / r.cnt) end as passed_ratio,
    v.cnt as verified_cnt, v.ratio as verified_ratio
from NrRequirements r, NrTraced t, NrCovered c, NrPassed p, VerifiedOverview v
�i
�

O��<EE�-viewManuallyVerifiedRequirementsManuallyVerifiedRequirementsCREATE VIEW ManuallyVerifiedRequirements as
select req_id from ManuallyVerified�/;33�	viewOverallTestOverviewOverallTestOverviewCREATE VIEW OverallTestOverview as
select sum(test_cnt) as test_cnt,
    sum(ran_cnt) as ran_cnt, case when sum(test_cnt) = 0 then 0.0 else (sum(ran_cnt) * 1.0 / sum(test_cnt)) end as ran_ratio,
    sum(passed_cnt) as passed_cnt, case when sum(test_cnt) = 0 then 0.0 else (sum(passed_cnt) * 1.0 / sum(test_cnt)) end as passed_ratio,
    sum(failed_cnt) as failed_cnt, case when sum(test_cnt) = 0 then 0.0 else (sum(failed_cnt) * 1.0 / sum(test_cnt)) end as failed_ratio,
    sum(skipped_cnt) as skipped_cnt, case when sum(test_cnt) = 0 then 0.0 else (sum(skipped_cnt) * 1.0 / sum(test_cnt)) end as skipped_ratio
from TestRunOverview�u:++�%viewTestRunOverviewTestRunOverviewCREATE VIEW TestRunOverview as
with NrTests(name, date, cnt) as
(
    select tr.name, tr.date, tr.nr_of_tests
    from TestRuns tr
),
NrRanTests(name, date, cnt) as
(
    select tr.name, tr.date, count(*)
    from TestRuns tr, Tests t
    where tr.name = t.test_run_name and tr.date = t.test_run_date
    group by tr.name, tr.date
),
NrPassed(name, date, cnt) as
(
    select tr.name, tr.date, count(*)
    from TestRuns tr, PassedTests t
    where tr.name = t.test_run_name and tr.date = t.test_run_date
    group by tr.name, tr.date
),
NrFailed(name, date, cnt) as
(
    select tr.name, tr.date, count(*)
    from TestRuns tr, Tests t
    where tr.name = t.test_run_name and tr.date = t.test_run_date
        and (t.passed <> 1 or t.passed is null)
    group by tr.name, tr.date
),
NrSkipped(name, date, cnt) as
(
    select tr.name, tr.date, count(*)
    from TestRuns tr, SkippedTests t
    where tr.name = t.test_run_name and tr.date = t.test_run_date
    group by tr.name, tr.date
),
TestRunCnts(name, date, test_cnt, ran_cnt, passed_cnt, failed_cnt, skipped_cnt) as
(
    select name, date, sum(test_cnt), sum(ran_cnt), sum(passed_cnt), sum(failed_cnt), sum(skipped_cnt)
    from (
        select name, date, cnt as test_cnt, 0 as ran_cnt, 0 as passed_cnt, 0 as failed_cnt, 0 as skipped_cnt
        from NrTests
        union all
        select name, date, 0 as test_cnt, cnt as ran_cnt, 0 as passed_cnt, 0 as failed_cnt, 0 as skipped_cnt
        from NrRanTests
        union all
        select name, date, 0 as test_cnt, 0 as ran_cnt, cnt as passed_cnt, 0 as failed_cnt, 0 as skipped_cnt
        from NrPassed
        union all
        select name, date, 0 as test_cnt, 0 as ran_cnt, 0 as passed_cnt, cnt as failed_cnt, 0 as skipped_cnt
        from NrFailed
        union all
        select name, date, 0 as test_cnt, 0 as ran_cnt, 0 as passed_cnt, 0 as failed_cnt, cnt as skipped_cnt
        from NrSkipped
    )
    where name not null and date not null
    group by name, date
)
select name, date, test_cnt,
    ran_cnt, case when test_cnt = 0 then 0.0 else (ran_cnt * 1.0 / test_cnt) end as ran_ratio,
    passed_cnt, case when test_cnt = 0 then 0.0 else (passed_cnt * 1.0 / test_cnt) end as passed_ratio,
    failed_cnt, case when test_cnt = 0 then 0.0 else (failed_cnt * 1.0 / test_cnt) end as failed_ratio,
    skipped_cnt, case when test_cnt = 0 then 0.0 else (skipped_cnt * 1.0 / test_cnt) end as skipped_ratio
from TestRunCnts�m911�	viewFailedTestCoverageFailedTestCoverageCREATE VIEW FailedTestCoverage as
select tc.req_id, tc.test_run_name, tc.test_run_date, tc.test_name, tc.trace_filepath, tc.trace_line
from TestCoverage tc, Tests t
where tc.test_run_name = t.test_run_name and tc.test_run_date = t.test_run_date
    and tc.test_name = t.name and (t.passed <> 1 or t.passed is null)�8##�sviewPassedTestsPassedTestsCREATE VIEW PassedTests as
select test_run_name, test_run_date, name, filepath, line
from Tests
where passed = 1