aquery

aquery compiles the arguments of a List request to SQL. It supports AIP-160 and CEL filters, AIP-132 ordering, AIP-158 page tokens and AIP-157 read masks.

aquery checks the text from the client against the schema of a resource. Then it gives SQL fragments and bound values. The SQL is portable ISO SQL. PostgreSQL, MySQL, SQL Server, SQLite and H2 run it. Databases shows how they are different. The design follows the aip160 and aip132 packages of LUCI.

Installation

aquery is published to GitHub Packages. Add the repository and the dependency to your pom.xml:

<repositories>
  <repository>
    <id>github-aquery</id>
    <url>https://maven.pkg.github.com/zZHorizonZz/aquery</url>
  </repository>
</repositories>

<dependencies>
  <dependency>
    <groupId>dev.horizon</groupId>
    <artifactId>aquery</artifactId>
    <version>0.3.1-SNAPSHOT</version>
  </dependency>
</dependencies>

For CEL filters, also add aquery-grammar-cel. It brings cel-java and its dependencies. cel-java has no module names, thus put it on the class path:

<dependency>
  <groupId>dev.horizon</groupId>
  <artifactId>aquery-grammar-cel</artifactId>
  <version>0.3.1-SNAPSHOT</version>
</dependency>

GitHub Packages needs a token even for public packages. Create a personal access token with the read:packages scope, and add it to ~/.m2/settings.xml under the same id:

<settings>
  <servers>
    <server>
      <id>github-aquery</id>
      <username>YOUR_GITHUB_USERNAME</username>
      <password>YOUR_TOKEN</password>
    </server>
  </servers>
</settings>

In GitHub Actions, use ${{ github.actor }} and ${{ secrets.GITHUB_TOKEN }} instead.

Snapshots of master are published to the same repository as 0.x.y-SNAPSHOT versions. Release notes are in CHANGELOG.md and on the releases page.

Example

DatabaseTable users = new DatabaseTable(
    new Field.Builder("username").backend(new StringColumn("username")).filterable().sortable().readable().build(),
    new Field.Builder("locked").backend(new BoolColumn("locked")).filterable().readable().build(),
    new Field.Builder("create_time").backend(new TimestampColumn("create_time")).filterable().sortable().build());

Parameters parameters = new Parameters(ParameterStyle.QUESTION_MARK);

FilterParser filters = new EbnfFilterParser();
Filter filter = filters.parse("username : \"dan\" NOT locked = true");
String where = WhereClause.of(users, filter, "T", parameters);
// ((T.username LIKE ? ESCAPE '!') AND (NOT (T.locked = ?)))
// parameters.values(): ["%dan%", true]

List<OrderBy> order = OrderByClause.mergeWithDefaultOrder(
    List.of(new OrderBy(new FieldPath("username"), false)), Order.parse("create_time desc"));
String orderBy = OrderByClause.of(users, order, "T");
// ORDER BY T.create_time DESC, T.username

SelectClause.Result select = SelectClause.of(users, ReadMask.parse("username"), "T");
// T.username

Filter languages

Each filter language has its own module and its own FilterParser:

Module	Parser	Language
aquery-grammar-ebnf	EbnfFilterParser	the AIP-160 EBNF. The aquery module includes it.
aquery-grammar-cel	CelFilterParser	CEL

The server makes the parser of the language that it accepts, and keeps it. All parsers give the same Filter tree, thus the schema, the backends and the SQL are the same:

Filter ebnf = new EbnfFilterParser().parse("username : \"dan\" NOT locked = true");
Filter cel = new CelFilterParser().parse("username.contains(\"dan\") && !locked");
// Both: ((T.username LIKE ? ESCAPE '!') AND (NOT (T.locked = ?)))

A parser is a TranslatingFilterParser: a SyntaxParser<T> reads the grammar into a syntax tree, and a FilterTranslator<T> changes that tree into the Filter tree. To add a language, implement the two interfaces.

The CEL parser supports the part of CEL that SQL can answer:

CEL	AIP-160
a && b, a || b, !a	a AND b, a OR b, NOT a
count >= 10, 10 <= count	count >= 10
locked	locked = true
name.contains("x")	name : "x"
name.startsWith("x"), name.endsWith("x")	none
status in [ACTIVE, PENDING]	status = ACTIVE OR status = PENDING
"prod" in tags	tags = "prod"
"site" in labels, has(labels.site)	labels:site
labels.site == "x", labels["site"] == "x"	labels.site = "x"
age > duration("1m30s")	age > 90s
create_time > timestamp("2012-04-21T11:30:00Z")	create_time > "2012-04-21T11:30:00Z"

A CEL string has no wildcards: name == "*.com" compares with =. The parser refuses arithmetic, matches(), size(), macros such as exists(), the conditional operator and null.

Parameters

One Parameters keeps the bound values of one statement. Give the same instance to each clause of the statement. Then the placeholders do not collide, and numbered placeholders continue from one clause to the next. Each placeholder in the SQL binds its own value, in the sequence of the text. Thus ? works.

Style	Placeholders	Driver
ParameterStyle.QUESTION_MARK	?	JDBC
ParameterStyle.DOLLAR	$1, $2	PostgreSQL protocol
ParameterStyle.AT_P	@p1, @p2	SQL Server
ParameterStyle.COLON	:1, :2	other drivers

For a different driver, write your own ParameterStyle. A style that numbers its values is a lambda, for example index -> "?" + (index + 1).

Some libraries bind values by name. A style for them also overrides name. Its placeholder contains the name. Then valuesByName() gives the values by name:

ParameterStyle named = new ParameterStyle() {
  @Override
  public String name(int index) {
    return "arg" + (index + 1);
  }

  @Override
  public String placeholder(int index) {
    return ":" + name(index);
  }
};

Parameters parameters = new Parameters(named);
String where = WhereClause.of(users, filter, "T", parameters);
// where: (T.name = :arg1), parameters.valuesByName(): {arg1=dan}

Page tokens

Continue a list with a page token:

PageTokenCodec tokens = new PageTokenCodec(secretKey); // 16, 24 or 32 bytes
String fingerprint = PageToken.fingerprintOf(filter, order);

Optional<PageToken> token = tokens.decode(request.pageToken(), fingerprint);
String resume = token.map(t -> new Keyset(users, order).after(t.cursor(), "T", parameters)).orElse("(1 = 1)");
// ((T.create_time < ?) OR (T.create_time = ? AND T.username > ?))
// parameters.values(): [..., 2012-04-21T15:30Z, 2012-04-21T15:30Z, "dan"]

List<String> cursor = List.of(
    users.fields().get(2).backend().cursorText(lastCreateTime),
    users.fields().get(0).backend().cursorText(lastUsername));
String next = tokens.encode(new PageToken(cursor, fingerprint));

The backend of each field writes the text of its cursor value with cursorText, and reads it again with cursorValue. The keyset binds the typed value, for example an OffsetDateTime for a timestamp.

Fields and backends

A field declares a FieldBackend that writes its SQL. The library has these backends:

Backend	Column	Bound value
StringColumn	VARCHAR	String
OpaqueStringColumn	VARCHAR with encoded values	String
BoolColumn	BOOLEAN	Boolean
IntegerColumn	BIGINT	Long
DurationColumn	BIGINT with nanoseconds	Long
TimestampColumn	TIMESTAMP WITH TIME ZONE	OffsetDateTime in UTC
EnumColumn	integer or VARCHAR, see EnumColumn.Storage	Long or String
UuidColumn	UUID	UUID
RepeatedStringColumn	array of VARCHAR	String
KeyValueColumn	array of key:value strings, or a child table	String
SimpleColumn	any, sort only	String cursor

LIKE patterns use ! as the escape character, as in T.name LIKE ? ESCAPE '!'.

RepeatedStringColumn and the STRING_ARRAY form of KeyValueColumn need array columns. Of the engines in Databases, only PostgreSQL runs their SQL. H2 has array columns, but it cannot run their UNNEST subquery. A child table works on all engines:

new Field.Builder("labels")
    .backend(new KeyValueColumn(new KeyValueColumn.ChildTable("user_labels", "user_id", "label_key", "label_value", "id")))
    .filterable()
    .build();
// labels.site = "pilsen":
// (EXISTS (SELECT 1 FROM user_labels aquery_kv WHERE aquery_kv.user_id = T.id AND aquery_kv.label_key = ? AND aquery_kv.label_value = ?))

To add a new type of field, write a new implementation of FieldBackend.

Text from the client goes into the statement only through bound values. Column names come only from the schema. Thus the output is safe against SQL injection. Table aliases that start with aquery_ are reserved for the SQL that the library writes.

Databases

These databases run the SQL. They are different in these points:

Database	Array fields	LIKE and = ignore case	Convert before you bind
PostgreSQL 17	yes	no	nothing
SQL Server 2022	no	yes, with the default collation	nothing
MySQL 8.4	no	yes, with the default collation	UUID to text
SQLite	no	only LIKE, only for ASCII letters	UUID to text, OffsetDateTime to text
H2	no	no	nothing

The MySQL JDBC driver writes a UUID with Java serialization. Bind uuid.toString() to a CHAR(36) column.

SQLite has no timestamp type. Its driver writes an OffsetDateTime as text, and the length of that text changes. Bind the timestamp as UTC text with a fixed length, for example 2024-01-01T00:00:00.500000000Z. Then text comparison gives the same result as timestamp comparison. Read the column as text and parse it. Read a UUID column as text too.

Requirements

Java 25 or later.

License

Apache License 2.0. The design comes from LUCI (infra/luci-go), The LUCI Authors. See NOTICE.