PostgreSQL 17 has been released so we test against that. 12 is removed
from the tests, as it only has about a month of upstream support left
and to keep the test matrix somewhat manageable.
The relevant change in 0.11.0 is supports PostgreSQL pipelining
feature. This skips support for that feature. For now this doesn't
attempt to support the pipelining.
This changes the support for building ON CONFLICT portions of an
INSERT in a few key ways:
- Supports using an 'IndexBodyExpr' in addition to 'ConstraintName'
and 'Qualified ColumnName' for determining what to check for a
conflict.
- Is based more around 'RawSql', which allows for callers to inject
things not explicitly supported at this time. For example, we do not
have a notion of COLLATION, and the PostgreSQL documentation even
mentions it as usually unneeded. However this now allows for it.
- The support is also broken out to a separate module. This is more
about module size than anything else. For example, it is not likely
that we would reuse the support elsewhere. This implementation detail
is easy enough to change and should be reconsidered before the next
release.
I added the instance and a number of helper functions for using it,
including `Plan`s like `findAllWhereByMarshaller`.
PostgreSQL does not support the input of anonymous row values, and the
instance methods need to produce row `SqlValue`s and `ValueExpression`s.
This meant that I had to add support for row values at the `SqlValue`
level. The `SqlRowValue` constructor will produce a row of placeholders:
```sql
($1, $2, $3)
```
I added `Monoid` and `Semigroup` instances to `FromItemExpr` to support
writing queries with empty from lists with `mempty` or Cartesian
product from lists with `<>`.
I added an `Empty` constructor and an `appendWithCommaSpace` helper to
`RawSql` to facilitate the `Semigroup` instance, since we need to
generate different SQL if any of the `FromItemExpr`s being appended are
empty.
Added:
- Expr-level support for comments
- Functions for getting and setting comments on a `TableDefinition`
- `AutoMigration` support for adding, removing, and modifying table comments
Adds Expr-level support for create a resuable window clause.
This takes the form of @WINDOW window_name AS (window_definition)@.
Creating a window_definition is separate precisely because it is
reused from the above window clause and in selection as the portion
that comes after the @OVER@ keyword.
Building on the ability to express a window definition, is a window
function call. These functions have some additional requirements:
- They are only valid in select or order by contexts
- The use of the OVER keyword and a window definition is required.
Specific helpers for the following windowing functions are included:
- rank
- rowNumber
- dense_rank
- percent_rank
Adds support for "conditional expressions" as seen at
https://www.postgresql.org/docs/13/functions-conditional.html
These are not typical SQL functions, in that they must not be quoted
the same way. As such these cannot use the pre-existing support for
functions.
The module name of 'ConditionalExpr' is a bit odd in that there is no
type of the same name. However grouping these items does seem natural
as the PostgreSQL documentation groups them.
The pieces needed to represent @JOIN@ are now available at the 'expr' level.
- 'JoinType' represents the INNER/LEFT/RIGHT/LATERAL portion.
- 'JoinConstraint' represents the portion that would determine how to
limit the result set. Currently only a helper for 'ON expr' is added.
This is left general enough to support the 'USING(columnNames)' syntax
as well in a future exercise.
- 'JoinExpr' represent the above pieces put together so that they can
be easily folded together into a 'FromItemExpr'.
Perhaps the most controversial change included is renaming
'TableReferenceList' to 'FromItemExpr'. This change more closely
matches the name in the PostgreSQL documentation. Which hopefully aids
clarity as at least some users did not realize that the "Table" in
that name was _not_ necessarily a user defined table like a
'TableDefinition', but could be something like a @JOIN@ or a subquery.
These new options allow the user to control how many times Orville
will try to acquire the migration lock as well as the delay between
each attempt.
Note that this changes the default delay from the previous 10 milliseconds
to a randomized delay between 100 and 125 milliseconds. The default
max attempts remains set to 25.
This adds the ability to use a 'QueryExpr' as a subquery particularly
for one form of the expressions found at
https://www.postgresql.org/docs/12/functions-subquery.html.
In particular the following are added:
- 'EXISTS (subquery)', which results in a 'BooleanExpr'
- 'IN' subquery expression support of the form 'expression IN
(subquery)'.
- 'NOT IN' subquery expression support of the form 'expression NOT IN
(subquery)'.
- 'ANY' subquery expression support of the form 'expression operator
ANY (subquery)'. 'SOME' is an alias for 'ANY' supported by PostgreSQL,
but I left it out for simplicity.
- 'ALL' subquery expression support of the form 'expression operator
ALL (subquery)'.
Notably for 'IN', 'NOT IN', 'ANY', and 'ALL' there is a form that
works with a "row_constructor". That is not implemented here.
Also, there is a choice with the 'IN' and 'NOT IN' support. This
resuses the existing generation of those keywords but exposes separate
functions for building the 'BooleanExpr'. We could instead expose the
pieces and have users build the 'BooleanExpr' or we could implement
them as specializations of the 'anySubquery' and 'allSubquery'. That
specialization would look something like: 'inSubquery = anySubquery
equalsOp' and 'notInSubquery = allSubquery notEqualsOp'. Note those
would not generate the same literal SQL bytes. The path chosen here
feels like the best trade-off for users of the api at the cost of some
extra function definitions.
This allows for an alias concept to be used in more of the higher
level api, notably in the synthetic field.
The previous 'Alias' is renamed 'AliasExpr' to provide more
distinction between the two.
Issuing multiple commands concurrently on the same connection results in
one of those commands hanging indefinitely, possibly due to a dead-lock
somewhere in libpq.
This introduces an MVar that is used to serialize access to the
connection for utilization purposes. The existing semantics around closing
connections should be preserved by the use of a second MVar.
- Bump upper bounds on containers and time as reported by cabal outdated
- Minor version bumps in the 9.4 and 9.6 series
- Updates nightly to the latest, adding 9.8.2 to versions tested with
- Updates the cabal file with GHC versions tested against
- Removes a redundant constraint caught by newer versions of GHC.
The highlights:
- Moving to use 'Qualified ColumnName' in many/most places. This
allows for the aliasing to be used naturally in those places.
- Add an alias type and function to create a 'Qualified ColumnName'
with it. This signals that 'Alias' is in fact different than
'ColumnName' or 'TableName', and reusing them for aliasing is not
always appropriate.
- Adding a 'MarshallAlias' constructor to the 'SqlMarshaller' so that
an alias can be attached to all of the natural fields in a marshaller
in a single function call from users. Synthetic fields could be
refactored with this facility, but that was left out here to keep the
already big changeset smaller.
- Adds 'AliasedFieldDefinition' for holding a field that should be
referenced with an alias in SQL.
- Adds a 'SqlComparable' typeclass to allow for comparison in a
polymorphic way. Instances are provided for 'FieldDefinition',
'SyntheticField' and the new 'AliasedFieldDefinition'. Comparison
functions are exposed working with the class. The field definition
functions and operators for comparison are now type restricted
versions of the classy functions. In this way the "simple" case of
FieldDefinitions has the most specific functions, but
'AliasedFieldDefinition' and 'SyntheticField' can both be compared
against.
- A small number of helpers are added to make generation of bits of
SQL, such as a 'TableReferenceList' that includes the 'AS alias' piece.
At the highest level this does four things:
- Adds support for extensions, including automigration
- Adds a 'IfNotExistsExpr' for use in some extension support.
- Adds support for a subset of functionality provided by the pg_trgm
extension.
- Allows ordering to be done on a synthetic field.
Extension support is added at the expression level, as well as to the
'PgCatalog' tracking infrastructure to support 'AutoMigration'
for loading/unloading extensions.
Support for cascading the load of extensions supported and used
during automigration so that users can be sure to list the extension
they want and any dependencies will be loaded as well.
To be used in conjection with the extension support is some
functionality from pg_trgm. The ability to add both GIN and GIST
indexes are included, as well as overriding the GIST index parameter,
'siglen'. Further, the similarity, word_similarity, and
strict_word_similarity functions are exposed to compare value
expressions. Higher level synthetic fields that compare a column with
a value are also included. This hopefully covers a broad range of
usage such as LIKE queries, column similarity, and the flexibility to
use the function value in other ways.
Support for ordering by a synthetic field is added precisely be able
sort by similarity.
pg_trgm has a few things **not** included here:
- The notion of a similarity threshold for each of the three
similarity functions. Including the ability to show and set the
values.
- Operators comparing a pair of values that each, implicitly use a
corresponding threshold resulting in a boolean.
- Operators that compute "distance", defined as 1 - similarity, for
each of the three similarities.
