sql-storage-adapter v0.7.9

Added

• autoIncrementPrimaryKey({ acceptExplicitIds: true }) on the dialects: on PostgreSQL an identity GENERATED BY DEFAULT, which takes an id an insert gives. The default stays GENERATED ALWAYS.
• syncIdentitySequence(table, column) on the dialects: the statement that moves an identity or serial column's sequence past the largest id (PostgreSQL setval; null on SQLite).
• ExportedData.dialect, and on each table's schema its primary key, the columns the engine fills or generates, and on SQLite the CREATE TABLE statement it keeps.
• importData and migrateAdapter options columnTypes (a column's target type) and sourceDialect (for an export without dialect), and warnings on the result.

Changed

• migrateAdapter and importData create the target engine's table: its types, the defaults it has a form of, NOT NULL and the primary key, with a column the source engine fills made an identity on PostgreSQL or an INTEGER PRIMARY KEY AUTOINCREMENT on SQLite. They copied the source's types and defaults as written and dropped the primary key, so a SQLite table failed on PostgreSQL (DATETIME, BLOB, datetime('now')) and a PostgreSQL one on SQLite (:: casts, nextval).
• SQLite to SQLite recreates each table from the statement SQLite keeps for it, with its keys, AUTOINCREMENT, constraints and generated columns. PostgreSQL to PostgreSQL keeps each exact type (character varying(20), integer[]), makes a serial an identity, and regenerates a generated column.
• On PostgreSQL, rows keep their ids in an identity GENERATED ALWAYS (OVERRIDING SYSTEM VALUE), and each identity or serial column that took ids has its sequence moved past them. Text without a zone goes into a timestamptz column as UTC. A source column matches a target column of another case.
• onConflict: 'ignore' and 'replace' write ON CONFLICT on PostgreSQL. They wrote SQLite's INSERT OR IGNORE and INSERT OR REPLACE, which failed there, and the failures were skipped without a word, so nothing was imported.
• rowsImported counts the rows written, leaving out those onConflict: 'ignore' skipped.
• exportData pages through each table in primary-key order (else rowid or ctid), so no row repeats or goes missing between pages, and leaves out the shadow tables of a SQLite virtual table.
• exportAsSQL writes the primary key and identity columns, leaves generated columns out of its inserts, and moves PostgreSQL sequences past the inserted ids. Its values load back on their engine: bytes as a blob or bytea literal (they were written as text), booleans as TRUE and FALSE on PostgreSQL, arrays as array literals in PostgreSQL array columns, and other objects as JSON (they were written as [object Object]).
• A column the target engine cannot fill (a PostgreSQL identity outside the primary key, on SQLite) is created nullable, so rows that leave it out can still be inserted.