8000 `sqlite-utils transform` breaks DEFAULT string values and STRFTIME() · Issue #509 · simonw/sqlite-utils · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content
8000

sqlite-utils transform breaks DEFAULT string values and STRFTIME() #509

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
kennysong opened this issue Nov 2, 2022 · 0 comments · Fixed by #519
Closed

sqlite-utils transform breaks DEFAULT string values and STRFTIME() #509

kennysong opened this issue Nov 2, 2022 · 0 comments · Fixed by #519

Comments

@kennysong
Copy link
kennysong commented Nov 2, 2022 •
edited
Loading

Very nice library! Our team found sqlite-utils through @simonw's comment on the "Simple declarative schema migration for SQLite" article, and we were excited to use it, but unfortunately sqlite-utils transform seems to break our DB.

Running sqlite-utils transform to modify a column mangles their DEFAULT values:

  • Default string values are wrapped in extra single quotes
  • Function expressions such as STRFTIME() are turned into strings!

Here are steps to reproduce:

Original database

$ 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'))
)

Modified database after sqlite-utils

$ sqlite3 test.db "INSERT INTO mytable DEFAULT VALUES; SELECT * FROM mytable;"
foo|2022-11-02 02:26:58.038

$ 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'')'
)

$ sqlite3 test.db "INSERT INTO mytable DEFAULT VALUES; SELECT * FROM mytable;"
foo|2022-11-02 02:26:58.038
'foo'|STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')

(Related: #336)
rhoboro added a commit to rhoboro/sqlite-utils that referenced this issue Dec 17, 2022
@rhoboro
Fixes an issue simonw#509
8d1db47
rhoboro added a commit to rhoboro/sqlite-utils that referenced this issue Dec 17, 2022
@rhoboro
Fixes an issue simonw#509
5e5c262
@rhoboro rhoboro mentioned this issue Dec 21, 2022
Merged
simonw pushed a commit that referenced this issue May 8, 2023
@rhoboro
Transform no longer breaks non-string default values
6500fed 
Closes #509
simonw added a commit that referenced this issue May 8, 2023
@simonw
Release 3.31
b3b100d 
Refs #509, #517, #520, #525, #527, #532, #534, #536, #537, #538, #539
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Fixes breaking DEFAULT values rhoboro/sqlite-utils
1 participant
@kennysong
0