SQL/JSON-Query — Query Specifications

The Query Group Specification - QueryGroupSpec

The structure that you supply to the query generator should conform to the QueryGroupSpec interface, and it is usually defined in a file called query-specs.ts. This structure contains specifications for the queries to be generated and allows setting some options that apply to all of the queries.

interface QueryGroupSpec
{
  defaultSchema?: string;
  propertyNameDefault?: PropertyNameDefault;
  generateUnqualifiedNamesForSchemas: string[];
  querySpecs: QuerySpec[];
}

QueryGroupSpec Properties

Usage example for QueryGroupSpec

If using the ready-made project dropin to add query generation capability to a project, then you define your single QueryGroupSpec object in file <query-gen-folder>/query-specs.ts, and export it from that module as queryGroupSpec.

// (file query-specs.ts)
export const queryGroupSpec: QueryGroupSpec = {
   defaultSchema: "foos",
   generateUnqualifiedNamesForSchemas: ["foos"],
   propertyNameDefault: "CAMELCASE",
   querySpecs: [
     // ...
   ]
};

Query Specification

A query specification describes a single query as a hierarchy of data to be fetched. It usually defines the generation of a single SQL file, but may specify multiple resultRepresenations in which case multiple SQL files will be generated for the query. In all cases it yields at most one TypeScript module or Java class representing the result types for the query. A query specification is described via the QuerySpec interface.

interface QuerySpec
{
  queryName: string;
  tableJson: TableJsonSpec;
  resultRepresentations?: ResultRepr[];
  generateResultTypes?: boolean;
  generateSource?: boolean;
  propertyNameDefault?: PropertyNameDefault;
  orderBy?: string;
  forUpdate?: boolean;
  typesFileHeader?: string;
}

QuerySpec Properties

Table JSON Specification

The TableJsonSpec structure describes how selected parts of or expressions involving the data content of a given table and related tables should be represented in JSON format. TableJsonSpec is the heart of the query specification, where it describes how to form JSON output for the top table which is the subject of the query. The structure also describes how data for related tables is to be included via nested TableJsonSpec in the parentTables and childTables members of the TableJsonSpec, and so on recursively to any level of nesting required through related tables.

interface TableJsonSpec
{
  table: string;
  fieldExpressions?: (string | TableFieldExpr)[];
  parentTables?: ParentSpec[]; // (ParentSpec extends TableJsonSpec)
  childTables?: ChildSpec[];   // (ChildSpec extends TableJsonSpec)
  recordCondition?: RecordCondition;
}

TableJsonSpec Properties

Parent Table Specification

Parent table specifications appear in the parentTables member of any TableJsonSpec — meaning either in the top level tableJson, or within an item in parentTables or childTables properties at some level of nesting under tableJson. A parent table specification describes the contributions to the JSON output from a table that is a parent of the table named in table. The contribution from a single parent table is described by interface ParentSpec which derives from TableJsonSpec. The subtype adds a few optional members to describe the join, which are only occasionally necessary, and also adds a member controlling whether the fields from the parent should be included inline with the child table’s own field expressions, or else wrapped via a single object reference property.

  interface ParentSpec extends TableJsonSpec
  {
    referenceName?: string | undefined;
    alias?: string | undefined;
    customMatchCondition?: CustomMatchCondition;
    viaForeignKeyFields?: string[];
  }

Child Table Specification

Child table specifications appear in the childTables member of any TableJsonSpec — meaning either in the top level tableJson, or within an item in parentTables or childTables properties at some level of nesting under tableJson. They describe the collection properties which are contributed to the JSON output from child tables of the parent table named in table. The contribution from a single child table is described by interface ChildSpec which derives from TableJsonSpec. The subtype adds a collection name property and additional optional properties to describe the join between parent and child, which are only occasionally necessary. It also adds members controlling filtering and ordering, and whether child tables exporting only a single property should have those property values “unwrapped” in the collection instead of being wrapped in JSON objects.

interface ChildSpec extends TableJsonSpec
{
  collectionName: string;
  foreignKeyFields?: string[];
  customMatchCondition?: CustomMatchCondition;
  unwrap?: boolean;
  filter?: string;
  orderBy?: string;
}

Including Data from Many to Many Relationships

So far it’s been described how to bring in information from parent tables reached via a many-to-one or -zero/one relationship, and from child tables via a one-to-many relationship. But we haven’t yet described how to include data from a many-to-many relationship.

In fact we don’t need any new constructs to handle the many-to-many case. We start by including the intermediate/intersection table as a child table collection within our initial table which represents one side of the relationship. Then within that child table specification we include whatever parts we want from the intersection table’s other parent as an “inlined” parent specification, by not specifying referenceName in the ParentSpec.

Many-Many Relationship Example

As an example let’s say that we have a table drug which has a many-many relationship to a table reference representing published literature references about the drug. An intermediate table drug_reference establishes the many-to-many relationship. Within the JSON output for our drug table, we’d like to include a collection of literature references, and to also have their priorities from the intermediate table.

drug references diagram

We accomplish this with the following specification:

const drugsQuery: QuerySpec = {
  queryName: 'drugs query',
  tableJson: {
    table: "drug",
    fieldExpressions: ["id", "name"],
    childTables: [
      {
        collectionName: "prioritizedReferences",
        table: "drug_reference",        // The intermediate/intersection table.
        fieldExpressions: ["priority"], // Include any properties wanted from intermediate table here.
        parentTables: [
          {
            // No "referenceName" property is given, so contents are "inlined" into the intermediate table output.
            table: "reference",         // The "other" entity in the many-many relationship
            fieldExpressions: ["publication"]
          }
        ],
        orderBy: "priority asc" // Sort the intermediate table records by priority, ascending.
      }
     ]
  }
}

In summary, we included the intermediate/intersection table drug_reference as we would do with any child table. And then we included the reference table as a parent within the intermediate table but without specifying a referenceName property, so its contents are inlined with those properties (if any) from the intermediate table itself. Here we included the priority column from the intermediate table, but often we may not incude any properties from the intermediate table itself.