v0.45.0

Changed

• #335: Previously, sq didn't handle decimal values correctly. It basically shoved a decimal value into a float or string and hoped for the best. As is known, floats are imprecise, and so we saw unwanted behavior, e.g.

  db_type_test.go:194: 
        Error Trace:	D:/a/sq/sq/drivers/sqlite3/db_type_test.go:194
        Error:      	Not equal: 
                      expected: "77.77"
                      actual  : "77.77000000000001"

  Now, sq uses a dedicated Decimal type end-to-end. No precision is lost, and at the output end, the value is rendered with the correct precision.

  There is a proposal to add decimal support to the Go database/sql package. If that happens, sq will happily switch to that mechanism.

  • 👉 A side effect of decimal support is that some output formats may now render decimal values differently (i.e. correctly). In particular, Excel output should now render decimals as a number (as opposed to a string), and with the precision defined in the database. Previously, a database NUMERIC(10,5) value might have been rendered as 100.00, but will now accurately render 100.00000.

v0.44.0

Added

• New SLQ function rownum() that returns the one-indexed row number of the current record.

  $ sq '.actor | rownum(), .actor_id, .first_name | order_by(.first_name)'
  rownum()  actor_id  first_name
  1         71        ADAM
  2         132       ADAM
  3         165       AL

• sq inspect has two new flags:

  • --schemata: list the source's schemas

    $ sq inspect @sakila/pg12 --schemata -y
    - schema: information_schema
      catalog: sakila
      owner: sakila
    - schema: public
      catalog: sakila
      owner: sakila
      active: true

  • --catalogs: list the source's catalogs

    $ sq inspect @sakila/pg12 --catalogs
    CATALOG
    postgres
    sakila

Fixed

• sq version now honors option format.datetime when outputting build timestamp.
• Fixed a fairly nasty bug that prevented correct rendering of SLQ functions nested inside an expression.

Changed

• The --exec and --query flags for sq sql were removed in the preceding release (v0.43.1).

  That was probably a bit hasty, especially because it's possible those flags could be reintroduced when the query vs exec situation is figured out. So, those two flags are now restored, in that their use won't cause an error, but they've been hidden from command help, and remain no-op.

v0.43.1

Added

• Related to #270, the output of sq inspect now includes the source's catalog (in JSON and YAML output formats).

Fixed

• MySQL driver didn't populate all expected values for sq inspect --overview.

Changed

• ☢️ Removed unused --exec and --query flags from sq sql command.

v0.43.0

Added

• #270: Flag --src.schema permits switching the source's schema (and catalog) for the duration of the command. The flag is supported for the sq, sql and inspect commands.
• New SLQ functions catalog() and schema() return the catalog and schema of the DB connection.
• The SLQ unique function now has a synonym uniq.

Changed

• sq src --text now outputs only the handle of the active source. Previously it also printed the driver type and short location. Instead use sq src --text --verbose to see those details.

v0.42.1

Fixed

• #308: Fix to allow build on 32-bit systems. Thanks @icp.

v0.42.0

Added

• 🐥 #279: The SQLite driver now has initial support for several SQLite extensions baked in, including Virtual Table and FTS5. Note that this is an early access release of extensions support. Please open an issue if you discover something bad.

v0.41.1

Fixed

• sq version was missing a newline in its output.

v0.41.0

This release is heavily focused on improvements to Microsoft Excel support.

The underlying Excel library has been changed from tealeg/xlsx to qax-os/excelize, largely because tealeg/xlsx is no longer actively maintained.

Thus, both the XLSX output writer and the XLSX driver have been rewritten. There should be some performance improvements, but it's also possible that the rewrite introduced bugs. If you discover anything strange, please open an issue.

Added

• #99: The CSV and XLSX
  drivers can now handle duplicate header column names in the ingest data.
  For example, given a CSV file:

  actor_id,first_name,actor_id
  1,PENELOPE,1
  2,NICK,2
  

  The columns will be renamed to:

  actor_id,first_name,actor_id_1
  

  The renaming behavior is controlled by a new option ingest.column.rename
  This new option is effectively the ingest counterpart of the existing output option
  result.column.rename.

• #191: The XLSX driver now detects header rows, like
  the CSV driver already does. Thus, you now typically don't need to specify
  the --ingest.header flag for Excel files. However, the option remains available
  in case sq can't figure it out for a particular file.

• The Excel writer has three new config options for controlling date/time output.
  Note that these format strings are distinct from format.datetime
  and friends, because Excel has its own format string mechanism.

  • format.excel.datetime: Controls datetime format, e.g. 2023-08-03 16:07:01.
  • format.excel.date: Controls date-only format, e.g. 2023-08-03.
  • format.excel.time: Controls time-only format, e.g. 4:07 pm.

• The ingest kind detectors (e.g. for CSV or XLSX)
  now detect more date & time formats as kind.Datetime, kind.Date, and kind.Time.

• If an error occurs when the output format is text, a stack trace is printed
  to stderr when the command is executed with --verbose (-v).

• There's a new option error.format that controls error output format independent
  of the main format option . The error.format value must be one of text or json.

Changed

• ☢️ The default Excel date format has changed. Previously
  the format was 11/9/89, and now it is 1989-11-09. The same applies
  to datetimes, e.g. 11/9/1989 00:00:00 becomes 1989-11-09 00:00.

  This change is made to reduce ambiguity and confusion.
  sq uses a library
  to interact with Excel files, and it seems that the library chooses a particular format
  by default (11/9/89). There are several paths we could take here:

  1. Interrogate the OS, and use the OS locale date format.
  2. Stick with the library default 11/9/89.
  3. Pick a default other than 11/9/89.

  We pick the third option. The first option (locale-dependent)
  is excluded because, as a general rule, we want sq to produce the same
  output regardless of locale/system settings. We exclude the second option
  because month/day confuses most of the world. Thus, we're left with picking a
  default, and 1989-11-09 is the format used in
  RFC3339 and friends.

  Whether this is the correct (standard?) approach is still unclear, and
  feedback is welcome. However, the user can make use of the new config options
  (format.excel.datetime etc.)
  to customize the format as they see fit.

• The XLSX writer now outputs header rows in bold text.

• ☢️ The XLSX writer now outputs blob (bytes) cell data as a base64-encoded string,
  instead of raw bytes.

Fixed

• Fixed bug where source-specific config wasn't being propagated.

v0.40.0

This release features a complete overhaul of the join mechanism.

Added

• #277: A table selector can now have an alias. This in and of itself is not
  particularly useful, but it's a building block for multiple joins.

  $ sq '@sakila | .actor:a | .a.first_name'

• New option result.column.rename that exposes a template used to rename
  result set column names before display. The primary use case is to de-duplicate
  columns names on a SELECT * FROM tbl1 JOIN tbl2, where tbl1 and tbl2
  have clashing column names (docs).

• #157: Previously only join (INNER JOIN) was available: now the rest of
  the join types such as left_outer_join, cross_join, etc. are
  implemented (docs).

Changed

• ☢️ #12: The table join mechanism has been
  completely overhauled. Now there's support for multiple joins. See docs.

  # Previously, only a single join was possible
  $ sq '.actor, .film_actor | join(.actor_id)'
  
  # Now, an arbitrary number of joins
  $ sq '.actor | join(.film_actor, .actor_id) | join(.film, .film_id)'

• ☢️ The alias for --jsonl (JSON Lines) has been changed to -J.

Fixed

• Fixed bug where config options weren't being propagated correctly.

v0.39.1

Fixed

• Bug with sq version output on Windows.