Fixes breaking DEFAULT values

Fixes #509, Fixes #336

Thanks for the great library! I fixed a bug that sqlite-utils transform breaks DEFAULT values. All tests already present passed with no changes, and I added some tests for this PR.

In #509 case, fixed here.

$ sqlite3 test.db << EOF
CREATE TABLE mytable (
    col1 TEXT DEFAULT 'foo',
    col2 TEXT DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW'))
)
EOF

$ sqlite3 test.db "SELECT sql FROM sqlite_master WHERE name = 'mytable';"
CREATE TABLE mytable (
    col1 TEXT DEFAULT 'foo',
    col2 TEXT DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW'))
)

$ sqlite3 test.db "INSERT INTO mytable DEFAULT VALUES; SELECT * FROM mytable;"
foo|2022-12-21 01:15:39.669

$ sqlite-utils transform test.db mytable --rename col1 renamedcol1
$ sqlite3 test.db "SELECT sql FROM sqlite_master WHERE name = 'mytable';"
CREATE TABLE "mytable" (
   [renamedcol1] TEXT DEFAULT 'foo',
   [col2] TEXT DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW'))  # ← Non-String Value
)

$ sqlite3 test.db "INSERT INTO mytable DEFAULT VALUES; SELECT * FROM mytable;"
foo|2022-12-21 01:15:39.669
foo|2022-12-21 01:15:56.432

And #336 case also fixed. Special values are described here.

3.2. The DEFAULT clause ... A default value may also be one of the special case-independent keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.

$ echo 'create table bar (baz text, created_at timestamp default CURRENT_TIMESTAMP)' | sqlite3 foo.db
$ sqlite3 foo.db
SQLite version 3.39.5 2022-10-14 20:58:05
Enter ".help" for usage hints.
sqlite> .schema bar
CREATE TABLE bar (baz text, created_at timestamp default CURRENT_TIMESTAMP);
sqlite> .exit

$ sqlite-utils transform foo.db bar --column-order baz
$ sqlite3 foo.db
SQLite version 3.39.5 2022-10-14 20:58:05
Enter ".help" for usage hints.
sqlite> .schema bar
CREATE TABLE IF NOT EXISTS "bar" (
   [baz] TEXT,
   [created_at] FLOAT DEFAULT CURRENT_TIMESTAMP
);
sqlite> .exit

$ sqlite-utils transform foo.db bar --column-order baz
$ sqlite3 foo.db
SQLite version 3.39.5 2022-10-14 20:58:05
Enter ".help" for usage hints.
sqlite> .schema bar
CREATE TABLE IF NOT EXISTS "bar" (
   [baz] TEXT,
   [created_at] FLOAT DEFAULT CURRENT_TIMESTAMP  # ← Non-String Value
);

:books: Documentation preview :books:: https://sqlite-utils--519.org.readthedocs.build/en/519/

simonw / sqlite-utils

Fixes breaking DEFAULT values #519