Query Documents
Query documents are a special type of document where the content of the document is made by quering the data of other requirements documents and work items.
The following data sources are supported:
- Work items
- Document sheet data
- Document links data
- Document entities data
The document query language is SQL (structured query language) with extension functions.
Query Data Source
Work Items Source
The work items source extracts a list of work items from the live database and populates a table that can be queried.
-
Source name
The name of the query table the source will be imported into.
-
Filters
The filters pre-applied to the work items. Pre filtering the data will reduce the amount of data loaded into the query.
-
Select
The name and format of fields to be loaded into the query table.
The formatting options for work item fields is the same as advanced work item reports.
Work Item Field Source
The work items field source extracts any multi-line string field from a list of work items, splits the list by line, and populates a table that can be queried for a specific line in the work item field.
-
Source name
The name of the query table the source will be imported into.
-
Filters
The filters pre-applied to the work items. Pre filtering the data will reduce the amount of data loaded into the query.
-
Select
The name of the fields that will be split by line break and imported.
The following additional fields can be queried for work item field sources.
| Field | Type | Description |
|---|---|---|
| $id | snowflake | The built in snowflake for the work item |
| id | string | The formatted work item id |
Document Sheet Source
-
Source name
The name of the query table the source will be imported into.
The following additional fields can be queried for document sheet sources.
| Field | Type | Description |
|---|---|---|
| $id | snowflake | The built in snowflake for the clause |
| $index | number | The index of the clause in the sheet |
| $number | number | The number part of the clause id |
| id | string | The formatted clause id |
| number | string | The paragraph number |
| $level | number | The indentation level of the clause |
| $heading | bool | Indicates of the clause is formatted as a heading |
| $order | number | The index of the clause in the sheet |
Document fields have the type field. References to fields can be extracted from the table as a byte array representing the hash of the field.
Document Links Source
-
Source name
The name of the query table the source will be imported into.
The following table fields can be queried.
| Field | Type | Description |
|---|---|---|
| $clause1_id | snowflake | The built in snowflake for the clause that owns the link (outgoing) |
| clause1_id | string | The clause id for the clause that owns the link (outgoing) |
| $clause2_id | snowflake | The built in snowflake for the clause that is linked to (incoming) |
| clause2_id | string | The clause id for the clause that is linked to (incoming) |
| $link_type | link type | The type of the link |
| $link_verb | link verb | The verb of the link |
Document Query Language
The document query language is an extension of the sqlite variant of SQL with extension functions to handle document field data.
FIELD_INCLUDES(field, value)
FIELD_INCLUDES(input : field, value : string) : bool
The FIELD_INCLUDES function returns true if any value in the field input to search is the specified value.
FIELD_INCLUDES_LINE
FIELD_INCLUDES_LINE(input : field, value : string) : bool
The FIELD_INCLUDES_LINE function returns true if any line of any value in the field input match the specified value.
FIELD_TOSTRING
FIELD_TOSTRING(input : field) : string
The FIELD_TOSTRING function returns a unicode plain text string representation of a document field. Any formatting included in the field is
flattened to plain text.
TOFIELD
TOFIELD(input : string) : field
The TOFIELD function returns a field from a string input.
FIELD_DISTINCT
FIELD_DISTINCT(input : field) : field
The FIELD_DISTINCT function returns only distinct values in a field.
FIELD_TRANSPOSE
FIELD_TRANSPOSE(input : field) : field
The FIELD_TRANSPOSE function transposes the rows and columns in a field.
INCLUDES_LINE
INCLUDES_LINE(input : string) : bool
The INCLUDES_LINE function returns true if any line in the string input match the specified value.
CONCAT
CONCAT(input : string, group : string, separator : string) : string
The CONCAT function concatenates all the string values of the named group for the row, separated by the separator. Use with GROUP BY.
FIELD_CONCAT
FIELD_CONCAT(input : field, group : string, separator : string) : field
The FIELD_CONCAT function concatenates all the field values of the named group for the row separated by the separator. Use with GROUP BY.
All field values will be converted to string.
FIELD_JOIN
FIELD_JOIN(input : field, group : string) : field
The FIELD_CONCAT function concatenates all the field values of the named group for the row. Use with GROUP BY.
FAQ
Q: How do I use columns with spaces or special characters in the name
A: Standard SQLite syntax can be used by quoting source and field names with double quotes
SELECT "source 1"."field 1" FROM "source 1";
If the source name contains the double quote character, repeating the double quote will escape the double quote. If we have a source source "1" then the same query would be written:
SELECT "source ""1"""."field 1" FROM "source ""1""";
Q: How does string concatenation work
A: Standard SQLite string contatenation works out of the box. The string concatenation operator is ||.
SELECT prefix || number AS formatted FROM source;
There is a caveat here, if any of the strings to be concatenated are NULL then the entire string is cast as NULL.
| prefix | number |
|---|---|
| PFX- | 1 |
| NULL | 2 |
The output from this query will be:
| formatted |
|---|
| PFX-1 |
| NULL |
The NULL value can be cast to an empty string by using the COALESCE function.
SELECT COALESCE(prefix, '') || number AS formatted FROM source;
The output from this query will now be:
| formatted |
|---|
| PFX-1 |
| 2 |
Document fields must first be converted to a string using the FIELD_TOSTRING before being used in string contatenation.
Q: What is the difference between $id and id columns in a source
A: The $id column is the internal snowflake id used by V-Raptor and the id column is the id presented to the user. Generally you will want to use id in SELECT statements.
Q: I want to combine several sources as a single source
A: Combining sources can be achieved by creating an SQL view. Insert each source as you normally would. The UNION ALL syntax can be used to combine the sources. The SELECT query for each source being unioned needs the same column names.
CREATE TEMP VIEW IF NOT EXISTS combined
SELECT id, body FROM "source 1"
UNION ALL
SELECT id, body FROM "source 2";
Once you have created a view, you can use it as a source in a SELECT query.
SELECT id, body FROM combined;
Q: How do I join data between several sources to create a traceability matrix
SQL has several types of join, each one has different use cases.
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
Requirements
| id | requirement |
|---|---|
| 1 | One |
| 2 | Two |
| 3 | Three |
Verification
| id | verification | requirement id |
|---|---|---|
| 1 | Un | 1 |
| 2 | Deux | 2 |
| 3 | Trois | 2 |
| 4 | Quatre | 4 |
The INNER JOIN is a joining of the select source and the join source returning only the select source rows that matches a join source row. In the following example, only requirements with verification are in the result set.
SELECT
"requirements".*,
"verification".*
FROM "requirements"
INNER JOIN "verification" ON "requirements".id = "verification"."requirement id";
| requirements.id | requirement | verification.id | verification | requirement id |
|---|---|---|---|---|
| 1 | One | 1 | Un | 1 |
| 2 | Two | 2 | Deux | 2 |
| 2 | Two | 2 | Trois | 2 |
The LEFT JOIN is a joining of the select source and the join source excluding records in the join source that do not match the select source.
SELECT
"requirements".*,
"verification".*
FROM "requirements"
LEFT JOIN "verification" ON "requirements".id = "verification"."requirement id";
| requirements.id | requirement | verification.id | verification | requirement id |
|---|---|---|---|---|
| 1 | One | 1 | Un | 1 |
| 2 | Two | 2 | Deux | 2 |
| 2 | Two | 2 | Trois | 2 |
| 3 | Three | NULL | NULL | NULL |
The RIGHT JOIN is a joining of the select source and the join source excluding records in the select source that do not match the join source.
SELECT
"requirements".*,
"verification".*
FROM "requirements"
RIGHT JOIN "verification" ON "requirements".id = "verification"."requirement id";
| requirements.id | requirement | verification.id | verification | requirement id |
|---|---|---|---|---|
| 1 | One | 1 | Un | 1 |
| 2 | Two | 2 | Deux | 2 |
| 2 | Two | 3 | Trois | 2 |
| NULL | NULL | 4 | Quatre | 4 |
The FULL JOIN is a joining of the select source and the join source including records in the select source that do not match the join source and records in the join source that do not match the select source.
SELECT
"requirements".*,
"verification".*
FROM "requirements"
RIGHT JOIN "verification" ON "requirements".id = "verification"."requirement id";
| requirements.id | requirement | verification.id | verification | requirement id |
|---|---|---|---|---|
| 1 | One | 1 | Un | 1 |
| 2 | Two | 2 | Deux | 2 |
| 2 | Two | 2 | Trois | 2 |
| 3 | Three | NULL | NULL | NULL |
| NULL | NULL | 3 | Quatre | 4 |