issues: 1505568103

This data as json

id node_id number title user state locked assignee milestone comments created_at updated_at closed_at author_association pull_request body repo type active_lock_reason performed_via_github_app reactions draft state_reason
1505568103 PR_kwDOCGYnMM5F609a 519 Fixes breaking DEFAULT values 13819005 closed 0     1 2022-12-21T01:27:52Z 2023-05-08T21:13:37Z 2023-05-08T21:13:37Z CONTRIBUTOR simonw/sqlite-utils/pulls/519

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.

```shell $ 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.

```shell $ 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/

 140912432 pull     
{
    "url": "https://api.github.com/repos/simonw/sqlite-utils/issues/519/reactions",
    "total_count": 3,
    "+1": 3,
    "-1": 0,
    "laugh": 0,
    "hooray": 0,
    "confused": 0,
    "heart": 0,
    "rocket": 0,
    "eyes": 0
}
 0  

Links from other tables