Database Queries

The AQA software uses an SQL database. This user guide provides sample queries to simplify users' experience. If you need a query that is not listed, please contact the site administrator.

To perform these queries a database account is required, which is different from the account used to access the web. To obtain a database account, contact the site administrator. Note that database accounts provide read-only access to the database to protect against accidental modification of data.

Examples include the LIMIT 10 statement which limits the number of rows returned to 10 to make it more appropriate while becoming familiar with the data. Remove or change this to get more data.

The database contains more information in addition to these examples, but much of it is unlikely to be useful clinically or for research.

The AQA software is compatible with both PostgreSQL and Microsoft SQL server databases. The Automated Quality Assurance site uses PostgreSQL version 12.5 R5.

Refer to the PostgreSQL documentation for more information.

There is an open source GUI-based administrative interface for PostgreSQL that can help users navigate databases. It can be downloaded from pgadmin.

---

Phase 2

• MetadataCheck
• Collimator Centering
• Center Dose
• Collimator Position
• Wedge
• Symmetry Flatness Constancy
• Leaf Position / Picket Fence
• VMAT

LOC

• Leaf Offset Correction
• Leaf Transmission Fraction
• R²
• Difference from Baseline Open
• Difference from Baseline Transmission

Daily QA

• BB by CBCT
• BB by EPID
• BB by EPID Composite

AQA

• Maintenance Record

---

Database Conventions

All tables use a synthetic key (also called a surrogate key) as their primary database key. The primary key name is the same as the table name, appended with 'PK'. For example, the primary key of the collimatorCentering table is collimatorCenteringPK. The same name is used when it is referenced as a foreign key.

To indicate units, column names are appended with _units. For example, "leafPosition"."offset_mm" indicates a value in millimeters.

---

Restricting Queries

Below are WHERE clauses that can be added to queries to focus on data of interest. It may also be desirable to limit the size of data returned by queries for efficient use of resources.

Restrict results to data created after a given date

         
        AND "output.dataDate" > '2019-10-25 16:00:00.00'
    

Restrict results to a single machine

         
        AND "machine"."id" = 'MACH_5'
    

 

Restrict results to a single institution

         
        AND "machine"."id" = 'INST_8'
    

Limit the number of results to 10, starting at the 40th qualifying entry by adding this to the end of a query

         
        LIMIT 10 OFFSET 40;
    

---

Database Schema

The database tables can be listed with:

         
        SELECT *
          FROM pg_catalog.pg_tables
          WHERE schemaname != 'pg_catalog' AND
              schemaname != 'information_schema';
    

The columns for a database table can be listed with the following:

         
        SELECT
          table_name,
          column_name,
          data_type
        FROM
          information_schema.columns
        WHERE
          -- where 'leafPosition' is the name of the table
          table_name = 'leafPosition';