Using the ClearDDTS SQL Interface
To retrieve information from the database, you construct a query that requests the information you want to see or work with. Each query helps you perform an action, such as select records to view, insert new records, update existing records with new information, or delete records.
In ClearDDTS, queries are written using a subset of the standard Structured Query Language (SQL). SQL is very simple to learn but is also very powerful. This section introduces some of the most common SQL statements; however, with SQL you can create far more complex queries than those described here.
Note: Although they may be shown in uppercase in this manual, SQL commands are not case-sensitive; only actual data is case-sensitive.
ddtssqlYou should now see the line prompt:
1>You can now enter your query, pressing return to move to a new line. To execute the query, enter go (on a new line) or a semi-colon (;) at the end of the query or on a new line. For example:
1> SELECT engineer 2> FROM defects 3> go 1> SELECT engineer from defects;The result of your query displays and the first line prompt (1>) returns so you can enter a new query. To repeat a query, enter two exclamation points (!!).
To see what tables are available, use the help command and press return. For example:
1> help table name -------------------- defects enclosures change_historyTo see the details about a specific table, enter help and the name of the table, then press return. For example, to see the details about the enclosures table, do the following:
1> help enclosures fieldname size type -------------------------------------- identifier 10 char name 32 char operation 16 char op_date 11 datetime engineer 16 char text 0 long
SELECT * FROM defectsThis command will display (select) everything (all columns, indicated by the *) from the table, defects. However, in many cases this is far more information than you are interested in seeing. To restrict the output to certain records, you can indicate a condition. For example, if you only want to see a summary of the defects associated with the project QTMS, you could enter:
SELECT identifier, headline FROM defects WHERE project = 'QTMS' ;This command displays the identifier and headline columns from the defects table for the defects linked to the project QTMS (the column project has the value QTMS). Note that the strings you search for are always enclosed in single quotation marks. The order you list the columns in is the order in which they are displayed. You can continue combining conditions or restricting the output until you get exactly the result you want. For example:
SELECT identifier, headline, severity FROM defects WHERE status = 'A' AND severity < 3
Note: Be sure to always use a comma to separate column names in the SELECT statement. If you forget the comma, your query may produce unexpected results.In addition, it is possible to control the order in which the results of a query are displayed. For example, to organize the results of a query so that it is displayed alphabetically by engineer's name:
SELECT identifier, severity, engineer FROM defects WHERE status = 'A' ORDER BY engineer
SELECT to_char (est_fix_date, `rrmmdd') FROM defects WHERE est_fix_date > to_date (`000101',`rrmmdd') ;
Note: For more information on Oracle format models see your Oracle documentation.
1> SELECT identifier, engineer 2> FROM defects 3> go identifier engineer ------------ --------- QTKqa00001 fred QTKqa00002 fred QTKqa00032 jones QTKqa00034 jones QTKqa00062 fred (5 rows selected)Notice the extra space between the ID and the engineer fields. By default, ddtssql pads each field with spaces so that it is the width defined in the database schema (schema_file). The format command can be used to override this behavior. The format command allows you to specify a printf-style format to control how the output is formatted. See the printf(3) man page for a complete description.
For example, to execute the previous query with the first column left justified with a maximum width of 10 characters and with the second column allowed to extend as wide as needed:
1> format %-10.10s %s 2> go identifier engineer ---------- --------- QTKqa00001 fred QTKqa00002 fred QTKqa00032 jones QTKqa00034 jones QTKqa00062 fred (5 rows selected)Note that the formatting instructions are applied to all subsequent queries and that only the columns that have a format will be displayed in the output. For example, if you add the severity column to the above query, the query would still produce the same results.
Note: You can use either vi or emacs to edit queries in ddtssql.
#!/bin/sh # Lookup the owner of <defect id>. # # Usage: owner <defect id> # defectid=$1 engineer=\Qddtssql -f - -noheader <<_E_O_F format %s select engineer from defects where identifier = '$defectid' ; _E_O_F\Q echo "$engineer"To quit ddtssql, enter quit or exit at the line prompt. Now that you are somewhat familiar with the ddtssql interface and some of its features, you are ready to practice writing queries.
defects.engineerFor example:
SELECT defects.identifier,defects.project,
enclosures.name
FROM defects, enclosures
WHERE defects.identifier = enclosures.identifier
WHERE defects.severity = 1
This query displays the defect id, associated project, and related file for all defects with a severity level of 1.Now that you have a general understanding of how to use the SELECT statement to retrieve information from your database, you are ready to begin writing more advanced queries.
date_convert(field, format)For example, to have the submitted-on date in month/day/year (such as 11/21/97) format, you would use:
date_convert(submitted_on, '%m/%d/%y')For a complete list of formatting descriptors, see the UNIX man page for strftime.
SELECT assigned=COUNT(status = 'A'), resolved=COUNT(status = 'R') FROM defects WHERE engineer = 'jones'This query would return something like this:
assigned resolved -------- -------- 30 18By combining aggregate comparisons, you can perform multiple tasks and calculations using a single SELECT statement. For example, the following query illustrates how you can calculate the percentage of resolved defects per engineer:
FORMAT %-10.10s %-10.10s %-8.8s %-4.4s %-8.8s %-5.5s %-4.4s
SELECT project, engineer,
Assigned = COUNT(status='A'),
Open = COUNT(status='O'),
Resolved = COUNT(status='R'),
Total = COUNT(status),
Pct = Resolved * 100/Total
FROM defects
WHERE project = 'X-Graph'
GROUP BY project, engineer
ORDER BY Total desc ;
project engineer Assigned Open Resolved Total Pct
---------- --------- -------- ---- -------- ----- ----
X-Graph saxon 70 3 183 274 66.7
X-Graph mcannon 5 0 174 205 84.8
X-Graph joeb 2 0 151 162 93.2
X-Graph connell 30 2 67 143 46.8
X-Graph NULL 0 0 0 91 0
X-Graph rico 0 0 44 81 54.3
X-Graph stuart 17 2 37 59 62.7
X-Graph rex 8 0 2 10 20
X-Graph patd 0 0 0 8 0
X-Graph nwong 6 0 0 6 0
X-Graph mjm 0 0 5 5 100
X-Graph mgr 0 0 2 4 50
X-Graph chris 0 0 1 2 50
X-Graph djg 0 0 1 1 100
(14 rows selected)
To create an alias (or correlation name) for a table, enter the alias after the table name in the FROM clause. You can then use the alias when selecting columns. For example:
SELECT status,
last_mod,
name
FROM defects D, enclosures E
WHERE D.identifier = E.identifier
AND D.last_mod > 950101
In this query, D is used as an alias for the defects table and E is used as an alias for the enclosures table. You can also use a table alias when specifying a column before actually defining the table alias. For example:
SELECT D.identifier,
D.last_mod,
E.name
FROM defects D, enclosures E
WHERE D.identifier = E.identifier
AND D.last_mod > 950101
By default, ClearDDTS supplies column headings for your query results using the column names entered in the query. However, you can change these column headings to make your results more readable by using column aliases. For example
SELECT DefectID = identifier, /* column alias */
os_version Platform, /* column alias */
when_found Phase_detected, /* column alias */
status, severity
FROM defects
WHERE project = 'Demo'
AND submitted_on > 970101 ;
This query would provide a result similar to the following:
DefectID Platform Phase_detected st se ------------ ----------- ---------------- -- -- QTKqa04315 SunOS4.1.3 beta test A 1 QTKqa05033 HP UX 9.05 post-release A 2 QTKqa04318 Sun 4.1.3 post-release A 3 QTKqa04263 Sun 4.1.3 post-release A 3 QTKqa04299 Sun 4.1.2 post-release A 3 QTKqa04342 AIX 1.4 beta test A 2 QTKqa04343 AIX 3.2.5 alpha test A 3 QTKqa04246 Sol 2.x beta test A 3 QTKqa04248 HP-UX 9.0 beta test A 3 QTKqa04268 integration A 3 QTKqa04331 ALL post-release N 2 QTKqa04330 AIX 3.2.5 functional test N 3 QTKqa04344 AIX beta test N 3 QTKqa04313 4.1.2 functional test R 1 QTKqa04307 4.1.3 post-release R 1 QTKqa04308 4.1.3 post-release R 1 QTKqa04317 alpha test R 1 QTKqa04327 Solaris post-release R 2 (18 rows selected)As shown in this example, ClearDDTS supports both the <alias> = <column> syntax and the <column> <alias> syntax.
Note: The INSERT, UPDATE, and DELETE statements are supported, but they should never be used directly. ClearDDTS user interfaces use these statements in conjunction with code to update the data in the allbugs directory and the SQL database in such a way as to keep the information synchronized. If you use these statements to modify the data in the SQL database directly, your changes will be overwritten by the data in the allbugs file the next time anyone updates the defect using a standard ClearDDTS interface (such as webddts or xddts).