v0.14.3

FunSQL v0.14.3

Diff since v0.14.2

• Fix MySQL reflection. Thanks to Alexander Plavin.

Merged pull requests:

• fix MySQL reflection (#71) (@aplavin)

v0.14.2

FunSQL v0.14.2

Diff since v0.14.1

• Add DuckDB support. Thanks to Alexander Plavin.

Merged pull requests:

• support duckdb (#69) (@aplavin)

v0.14.1

FunSQL v0.14.1

Diff since v0.14.0

• Fix Join incorrectly collapsing an outer branch when it may transform a NULL
  to a non-NULL value.

• Make @funsql macro support operators ≥, ≤, ≢, ≡, ≠, ∉, ∈
  as aliases for >=, <=, IS DISTINCT FROM, IS NOT DISTINCT FROM, <>,
  IN, NOT IN, thanks to Ashlin Harris.

Merged pull requests:

• Add definitions for ≥ and ≤ (#67) (@AshlinHarris)
• Fix unsafe branch collapsing of an outer JOIN (#68) (@xitology)

v0.14.0

FunSQL v0.14.0

Diff since v0.13.2

• Define: add parameters before and after for specifying position of the defined columns.
• Introduce the SQLColumn type to represent table columns. The type of SQLTable.columns is changed from Vector{Symbol} to OrderedDict{Symbol, SQLColumn}.
• Make SQLTable an AbstractDict{Symbol, SQLColumn}.
• Add DataAPI-compatible metadata to catalog objects SQLCatalog, SQLTable, and SQLColumn.
• Add a field SQLString.columns with an optional Vector{SQLColumn} representing output columns of the SQL query.
• Support docstrings in @funsql notation.
• Remove support for const and variable assignment syntax from @funsql notation.
• Use a simpler and more consistent rule for collapsing JOIN branches (fixes #60).

Merged pull requests:

• Define: add 'before' and 'after' parameters (#62) (@xitology)
• Support docstrings in @funsql notation (#63) (@xitology)
• Metadata interface for catalog objects (#64) (@xitology)

Closed issues:

• Adding multiple qualifiers to table results in joins being subqueries (#60)

v0.13.2

FunSQL v0.13.2

Diff since v0.13.1

• Wrap a branch of UNION ALL in a subquery if it contains ORDER BY or
  LIMIT clause.

v0.13.1

• Add support for grouping sets, which are used in SQL to calculate totals
  and subtotals. The Group() node accepts an optional parameter sets,
  which is either a grouping mode indicator :cube or :rollup, or
  a collection of grouping key sets Vector{Vector{Symbol}}. Examples:

  From(:person) |> Group(:year_of_birth, sets = :cube)
  
  From(:person) |> Group(:year_of_birth, :month_of_birth, sets = :rollup)
  
  From(:person) |> Group(:year_of_birth, :gender_concept_id,
                         sets = [[:year_of_birth], [:gender_concept_id]])

v0.13.0

This release introduces some backward-incompatible changes. Before upgrading,
please review these notes.

• Type resolution has been refactored to allow assembling query fragments based
  on the type information.

• Type checking is now more strict. A Define field or an optional Join will
  be validated even when they are to be elided.

• Resolution of ambiguous column names in Join has been changed in favor of
  the right branch. Previously, an ambiguous name would cause an error.

• Node-bound references are no longer supported. The following query will
  fail:

  qₚ = From(:person)
  qₗ = From(:location)
  q = qₚ |>
      Join(qₗ, on = qₚ.location_id .== qₗ.location_id) |>
      Select(qₚ.person_id, qₗ.state)

  Use nested references instead:

  q = @funsql begin
      from(person)
      join(
          location => from(location),
          location_id == location.location_id)
      select(person_id, location.state)
  end

v0.12.0

This release introduces some backward-incompatible changes. Before upgrading,
please review these notes.

• The SQLTable constructor drops the schema parameter. Instead, it now
  accepts an optional vector of qualifiers.
• Bare Append(p, q) now produces p UNION ALL q. Previously, bare
  Append(p, q) would be interpreted as Select() |> Append(p, q).
• Add Spark dialect.
• Update nodes Group() and Partition() to accept an optional parameter
  name, which speficies the field that holds the group data.
• Add Over() node such that p |> Over(q) is equivalent to q |> With(p).
• Add the @funsql macro, described below.

This release introduces @funsql macro, which provides a new, concise notation
for building FunSQL queries. Example:

using FunSQL

q = @funsql from(person).filter(year_of_birth > 1950).select(person_id)

This is equivalent to the following query:

using FunSQL: From, Get, Select, Where

q = From(:person) |> Where(Get.year_of_birth .> 1950) |> Select(Get.person_id)

The @funsql notation reduces syntactic noise, making queries prettier and
faster to write. Semantically, any query that can be constructed with @funsql
could also be constructed without the macro, and vice versa. Moreover, these
two notations could be freely mixed:

@funsql from(person).$(Where(Get.year_of_birth .> 1950)).select(person_id)

From(:person) |>
@funsql(filter(year_of_birth > 1950)) |>
Select(Get.person_id)

In @funsql notation, the chain operator (|>) is replaced either with
period (.) or with block syntax:

@funsql begin
    from(person)
    filter(year_of_birth > 1950)
    select(person_id)
end

Names that are not valid identifiers should be wrapped in backticks:

@funsql begin
    from(person)
    define(`Patient's age` => 2024 - year_of_birth)
    filter(`Patient's age` >= 16)
end

Many SQL functions and operators are available out of the box:

@funsql from(location).define(city_state => concat(city, ", ", state))

@funsql from(person).filter(year_of_birth < 1900 || year_of_birth > 2024)

Comparison chaining is supported:

@funsql from(person).filter(1950 < year_of_birth < 2000)

The if statement and the ternary ? : operator are converted to a CASE
expression:

@funsql begin
    from(person)
    define(
        generation =>
            year_of_birth <= 1964 ? "Baby Boomer" :
            year_of_birth <= 1980 ? "Generation X" :
            "Millenial")
end

A @funsql query can invoke any SQL function or even an arbitrary scalar
SQL expression:

@funsql from(location).select(fun(`SUBSTRING(? FROM ? FOR ?)`, zip, 1, 3))

Aggregate and window functions are supported:

@funsql begin
    from(person)
    group()
    select(
        count(),
        min(year_of_birth),
        max(year_of_birth, filter = gender_concept_id == 8507),
        median =>
            agg(`(percentile_cont(0.5) WITHIN GROUP (ORDER BY ?))`, year_of_birth))
end

@funsql begin
    from(visit_occurrence)
    partition(person_id, order_by = [visit_start_date])
    filter(row_number() <= 1)
end

Custom scalar and aggregate functions can be integrated to @funsql notation:

const funsql_substring = FunSQL.FunClosure("SUBSTRING(? FROM ? FOR ?)")

@funsql from(location).select(substring(zip, 1, 3))

const funsql_median = FunSQL.AggClosure("(percentile_cont(0.5) WITHIN GROUP (ORDER BY ?))")

@funsql from(person).group().select(median(year_of_birth))

In general, any Julia function with a name funsql_f() can be invoked as f()
within @funsql macro. For example:

funsql_concept(v, cs...) =
    @funsql from(concept).filter(vocabulary_id == $v && in(concept_id, $cs...))

funsql_ICD10CM(cs...) =
    @funsql concept("ICD10CM", $cs...)

For convenience, @funsql macro can wrap such function definitions:

@funsql concept(v, cs...) = begin
    from(concept)
    filter(vocabulary_id == $v && in(concept_id, $cs...))
end

@funsql ICD10CM(cs...) =
    concept("ICD10CM", $cs...)

Or even:

@funsql begin

concept(v, cs...) = begin
    from(concept)
    filter(vocabulary_id == $v && in(concept_id, $cs...))
end

ICD10CM(cs...) =
    concept("ICD10CM", $cs...)

end

Here are some other SQL features expressed in @funsql notation. JOIN and
GROUP BY:

@funsql begin
    from(person)
    filter(between(year_of_birth, 1930, 1940))
    join(
        location => from(location).filter(state == "IL"),
        on = location_id == location.location_id)
    left_join(
        visit_group => begin
            from(visit_occurrence)
            group(person_id)
        end,
        on = person_id == visit_group.person_id)
    select(
        person_id,
        latest_visit_date => visit_group.max(visit_start_date))
end

ORDER BY and LIMIT:

@funsql from(person).order(year_of_birth.desc()).limit(10)

UNION ALL:

@funsql begin
    append(
        from(measurement).define(date => measurement_date),
        from(observation).define(date => observation_date))
end

Recursive queries:

@funsql begin
    define(n => 1, f => 1)
    iterate(define(n => n + 1, f => f * (n + 1)).filter(n <= 10))
end

Query parameters:

@funsql from(person).filter(year_of_birth >= :THRESHOLD)

v0.11.2

• Fix a number of problems with serializing Order() and Limit() for
  MS SQL Server.
• Add a column alias to the dummy NULL when generating zero-column output.

v0.11.1

No changes since the last release, enable Zenodo integration.