sqljson-query v1.11.2
SQL/JSON-Query
Overview
This is a tool to be used as part of an application's build process to generate SQL/JSON nested data queries and matching result type declarations.
As a developer, your job in the process is to supply a file query-specs.ts
which provides a list of query specifications, where each specification describes a
hierarchy of data to be fetched for a query. From these query specifications and a
database metadata file (generated from a database via included tool), the tool
generates:
- SQL/JSON nested data queries for Oracle or Postgres to fetch the data,
- Result type declarations in TypeScript or Java which match the structure of the generated SQL and to which the query results can be directly deserialized.
- Column-level metadata for all relations (tables/views) in TypeScript or Java, which can be used to refer to tables/columns in INSERT/UPDATE/DELETE statements in an error-resistant way.
When generating queries, database metadata is used to verify all tables, fields, and foreign key relationships used in the queries, failing with an error if any referenced database items are not found in database metadata. A self-contained tool to fetch this database metadata for Oracle and PostgresSQL databases is included.
The query generation work is all done at build time, and there is no run-time component
needed to use the generated SQL queries and generated result types. The SQL can be
executed by any preferred means, and deserialized to the generated result types using
any library suitable for the purpose, such as Jackson in Java, or via JSON.parse()
in TypeScript.
Query Group Specification
The structure that you supply to the query generator, usually in a file called query-specs.ts
,
must conform to the QueryGroupSpec
interface. This structure contains the queries to be
generated and also allows setting some options that apply to all the queries.
interface QueryGroupSpec
{
defaultSchema?: string;
propertyNameDefault?: PropertyNameDefault;
generateUnqualifiedNamesForSchemas: string[];
querySpecs: QuerySpec[];
}
QueryGroupSpec Properties
defaultSchema
Any unqualified table or view referenced in a query specification is assumed to belong to the default schema specified here. This allows table/view references to be unqualified in query definitions for convenience and readability, while also allowing the query generator to precisely find the relation in the database metadata, where relations are stored in fully qualified form.
propertyNameDefault
The property name default describes how json property names are to be derived from database field names, when the property name is not specified directly in the query specification. Valid values are:
AS_IN_DB
: JSON property name will equal the database field name exactly. A database fieldaccount_number
would yield a JSON property name ofaccount_number
with this setting.CAMELCASE
: JSON property name will be the camelcase form of the database field name. A database fieldaccount_number
would yield a JSON property name ofaccountNumber
with this setting.
generateUnqualifiedNamesForSchemas
This setting determines which relation names are qualified in generated SQL. Any relation belonging to a schema in this list will be referred to in unqualified form in generated SQL. Your ability to use unqualified names in generated SQL depends on how your application connects to your database to execute the generated SQL. Generally if you connect to a schema directly (as with Oracle) or have it on a schema search path (if supported by your database such as with Postgres), then you should be able to add your schema in this list to generated unqualified names in your SQL. If you don't care how the generated SQL looks then it's always safe to leave this list empty.
querySpecs
These are the specifications for the SQL/JSON nested data queries to be generated for your application. Result types in either TypeScript or Java are also generated from these. The specifics of the query specifications are described in detail below.
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>/queries/query-specs.ts
, and export it from that module
as queryGroupSpec
.
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 and Java class representing 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
queryName
This required field defines a name of the given query, which can be several words separated by spaces, dashes, or underscores, such as "drugs with cautions and functional categories query". The query name should be unique in the
QueryGroupSpec
and is used to form the names of the generated SQL files, as well as the module name or class name for generated TypeScript or Java sources representing the query result types. SQL file names and TypeScript module names are based on the dashed form of the tokenized query name, for exampledrugs-with-cautions-and-functional-categories-query.sql/ts
for the query name above. The Java class name containing result type definitions is the camelcase form of the query name, for exampleDrugsWithCautionsAndFunctionalCategoriesQuery
.
tableJson
This required field is the heart of the query definition, specifying a top table and nested related tables, recursively, with details about how to convert each to JSON format. This structure is described in detail below.
resultRepresentations
Contains from one to three SQL variants to be generated for the query. Each variant will be written to its own SQL file, with variant type appended to the query name if multiple variants are selected. The result representation variants differ in how the results are presented in database result sets. Any subset of the following variants may be chosen.
JSON_OBJECT_ROWS
(default): The result set will consist of multiple rows, with one row per result row from the top table in the hierarchy of results. Each row consists of exactly one column, which contains the JSON object representation of the hierarchy of data for the top-level table row and its related data from related tables.JSON_ARRAY_ROW
: In this result style the query will yield only a single row, which consists of only one column. The entire result set in this case is represented as a single JSON array value, the elements of which are JSON objects representing the result rows from the top table and data related to the row from the related tables.MULTI_COLUMN_ROWS
: The result set will consist of multiple rows, with one row per result row from the top table in the hierarchy of results. Top level rows are presented in multi-column form as in the top level table itself. The column values themselves may be either database types coming directly from a table or JSON objects representing data collected from related tables.
generateResultTypes
Controls whether Java or TypeScript source code representing result types for the query should be generated. The default value is
true
.
generateSource
If
false
, no SQL nor Java or TypeScript source will be generated. The default value istrue
.
propertyNameDefault
Controls how JSON property names are derived from database field names for this specific query, overriding any setting at the level of the
QueryGroupSpec
. See the description of the field of the same name inQueryGroupSpec
for details. Defaults toCAMELCASE
if defined in neither theQuerySpec
nor theQueryGroupSpec
.
orderBy
This optional property is a SQL expression controlling the ordering of the results from the top-level table in the query.
forUpdate
If
true
, afor update
clause will be added to the generated SQL, causing the fetched rows to be locked for the duration of the transaction in which the query occurs.
typesFileHeader
Optional text which will be included above the generated Java or TypeScript result type definitions. This text is not interpreted by the tool and will be included verbatim in the generated source code, just below standard imports if any. Often this is used to add imports for types referred to in customized field result types.
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
table
The name of the top-level table for which this structure describes JSON output. Data from other, related tables may be described via the members
parentTables
andchildTables
as described below. The table name may appear unqualified if adefaultSchema
has been specified for the enclosingQueryGroupSpec
. The table name may be entered with quotes in which case it will be used in quoted form exactly as entered in query generation, or without quotes in which case the database's default name case folding will be applied automatically (to uppercase for Oracle and lowercase for Postgres).
fieldExpressions
Lists the properties that come from fields of the top-level table named in
table
, or from expressions formed from those fields. Each entry can either be a simplestring
, in which case it should equal a column name of the top level table, or else a structure of typeTableFieldExpr
which allows additional options to be specified for the output property.interface TableFieldExpr { field?: string; expression?: string; jsonProperty?: string; // Required if value is not a simple field name. fieldTypeInGeneratedSource?: string | {[srcLang: string]: string}; withTableAliasAs?: string; // Table alias escape sequence which may be used in value (default '$$'). }
- A simple
string
value is interpreted as aTableFieldExpr
with thefield
property of that value and no other properties provided. Exactly one of
field
andexpression
should be specified. In thefield
case the value comes directly from a database column, while anexpression
may be any SQL expression involving the database fields in scope. The expression should be entered just as it should appear in generated SQL, with the exception that$$
will be replaced with an alias to the table named intable
. Usually qualification with$$
is not necessary.If
expression
is specified, thenfieldTypeInGeneratedSource
is required to assign a type to the expression in generated source code. If only one target language (Java or TypeScript) is in use, then the result type can be entered as a simplestring
. If multiple target languages are in use then the result type can be supplied as an object mapping the target language to the result type declaration for the expression in that language. For example:{'TS': 'string', 'Java': 'String'}
.If
jsonProperty
is provided, it determines the name of the property in the JSON object representing the table row. If not specified, then the output property name is determined by thepropertyNameDefault
value of the enclosingQuerySpec
orQueryGroupSpec
as described above, defaulting toCAMELCASE
if neither is found.Field Expressions Example
fieldExpressions: [ 'account_num', // equiv to { field: 'account_num', jsonProperty: 'accountNum' } under default CAMELCASE property naming {field: 'id', jsonProperty: 'accountId'}, // specify custom property name {expression: 'select name from state where code = $$.state_code', fieldTypeInGeneratedSource: 'String'} ]
- A simple
parentTables
The
parentTables
member ofTableJsonSpec
scribes the contributions from parent tables of the table named intable
to the JSON output. Each element of the array is aTableJsonSpec
, and may also include additional options particular to the parent/child relationship. A parent specification can describe an inline parent table, whose fields are merged into the child table's, or else a referenced parent table whose row value is included as a JSON object via a property in the child. Additional members are provided for controlling how the parent table is joined with the child table. See the Parent Table Specification section below for details.
childTables
Each item of the
childTables
member ofTableJsonSpec
describes a property to be added to the parent JSON to hold a collection of records from a single child table. Each element of the array is aTableJsonSpec
, with additional options particular to the child/parent relationship. See the Child Table Specification section below for details.
recordCondition
The optional
recordCondition
member can hold an arbitrary SQL predicate (suitable for inclusion in a SQLWHERE
clause) to filter records in the subject table of theTableJsonSpec
as named intable
. The record condition should conform to theRecordCondition
interface.interface RecordCondition { sql: string; paramNames?: string[]; withTableAliasAs?: string; }
The
sql
property holds the SQL predicate expression, just as it would appear in a SQLWHERE
clause. Within the expression, usages of$$
will be expanded to be the alias of the subject table in the generated query, unless the alias placeholder has been customized via thewithTableAlias
property in which case that character sequence will be expanded to the table alias instead. The SQL expression may contain parameters such as:myParam
or?
, in which case it is the responsibility of the program executing the SQL to set the parameters properly where the SQL is finally executed. Complex expressions involving boolean conjunctions and parentheses to nest conditions are allowed here.The
paramNames
member, if provided, informs the query generator of any parameters that are used in thesql
member, in which case it will helpfully add members or defined constants to the Java or TypeScript source code containing the parameter names. This is a convenience for the users of the SQL to assist with auto-completion and to prevent usage of wrong or missing parameter names, and is in no case required to properly execute the SQL.The
withTableAlias
member controls the text that will be expanded to the subject table alias in the SQL expression in thesql
property. Defaults to$$
. Usually unqualified field names are unambiguous so no table alias is necessary.
Parent Table Specification
Parent table specifications appear in the parentTables
member of the TableJsonSpec
, where they
describe the contributions from parent tables of the table named in table
to the JSON output.
The contribution from a single parent table is described by interface ParentSpec
which derives from
TableJsonSpec
. The subtype adds a few additional 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;
customJoinCondition?: CustomJoinCondition;
viaForeignKeyFields?: string[];
}
- If
referenceName
is provided for a parent table, then the value for a row of the parent will be wrapped as a JSON object which is referenced via a property of this name in the child's JSON object value. If areferenceName
is not provided, then the contents from the parent table's JSON output will be included directly, inlined, into in the referencing table's JSON output. In the inlined fields case, ie. whenreferenceName
is not provided, the parent's own fields, child collections, and fields from its own parents will all appear directly among the referencing table's own fields, child collections, and content from other parent tables.
- The
alias
property allows assigning a custom alias for the parent table. This is not often necessary, but it may be used in expressions provided in propertyrecordCondition
which is described further below as a way of filtering records via custom conditions.
- At most one of
customJoinCondition
andviaForeignKeyFields
can be specified, in both cases to control how the join to the parent is accomplished from the table named intable
. Normally neither of these properties is needed, for the common case that there is one and only one foreign key between the table named intable
and the parent table as found in the database metadata. If neither property is provided and no suitable foreign key is found, or more than one such foreign key is found, then query generation will fail with an error.
- The
viaForeignKeyFields
option allows disambiguating when multiple foreign keys exist between the child table (named intable
) and the parent table, by listing the names of the foreign key fields to be matched. The order doesn't matter for the foreign key fields here, but they must exist as foreign key fields in the database metadata or else query generation will fail with an error.
The
customJoinCondition
allows matching via fields that do not have a foreign key constraint defined in the database (metadata). The structure is a list of field pairs between the child and parent tables that should be equated to form the join condition:interface CustomJoinCondition { equatedFields: FieldPair[]; } interface FieldPair { childField: string; parentPrimaryKeyField: string; }
For example:
parentTables: [ { table: 'foo', // ... customJoinCondition: {equatedFields: [{childField: 'fooId', parentPrimaryKeyField: 'id'}]} } ]
Child Table Specification
TODO
interface ChildSpec extends TableJsonSpec
{
collectionName: string;
foreignKeyFields?: string[];
customJoinCondition?: CustomJoinCondition;
filter?: string;
unwrap?: boolean;
orderBy?: string;
}
The
customJoinCondition
allows matching via fields that do not have a foreign key constraint defined in the database (metadata). The structure is a list of field pairs between the child and parent tables that should be equated to form the join condition:interface CustomJoinCondition { equatedFields: FieldPair[]; } interface FieldPair { childField: string; parentPrimaryKeyField: string; }
For example:
{ table: 'bar', // ... childTables: [ { collectionName: "foos", table: 'foo', // ... // (This custom join condition is not necessary if a proper foreign key constraint is defined on barId.) customJoinCondition: {equatedFields: [{childField: 'barId', parentPrimaryKeyField: 'id'}]} } ] }
TODO: Explain how to use a child table with inline parent to represent data from a many-many relationship.
Setup
Refer to sqljson-query-dropin project docs, or copy them here.
6 months ago
6 months ago
7 months ago
7 months ago
7 months ago
6 months ago
9 months ago
10 months ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago