sqlite .dump with CHECKS breaking

(1) By freshteapot on 2024-01-11 21:10:24 [source]

Hello :waves:

I have a database created before which used to allow CHECK to have typeof = "text". Now it is required to equal 'text'.

A correct table from scratch

CREATE TABLE IF NOT EXISTS alist_kv (
  uuid CHARACTER(36) not null primary key
  CHECK(
    typeof("uuid") = 'text' AND
    length("uuid") <= 36
  ),
  list_type CHARACTER(3)
  CHECK(
    typeof("list_type") = 'text' AND
    length("list_type") <= 3
  ),
  body text,
  user_uuid CHARACTER(36)
  CHECK(
    typeof("user_uuid") = 'text' AND
    length("user_uuid") <= 36
  ),
  created DATETIME not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
);

Table stored in "sqlite_master"

CREATE TABLE alist_kv (
  uuid CHARACTER(36) not null primary key
  CHECK(
    typeof("uuid") = "text" AND
    length("uuid") <= 36
  ),
  list_type CHARACTER(3)
  CHECK(
    typeof("list_type") = "text" AND
    length("list_type") <= 3
  ),
  body text,
  user_uuid CHARACTER(36)
  CHECK(
    typeof("user_uuid") = "text" AND
    length("user_uuid") <= 36
  ),
  created DATETIME not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
);

When I use

sqlite3 a.db .dump > a.sql
sqlite b.db < a.sql

It complains due to the schema being invalid.

Parse error near line 12481: no such column: text
  ot null primary key   CHECK(     typeof("uuid") = "text" AND     length("uuid"
                                      error here ---^

Is this user error?

sqlite version

sqlite3 -version
3.44.2 2023-11-24 11:41:44 ebead0e7230cd33bcec9f95d2183069565b9e709bf745c9b5db65cc0cbf92c0f (64-bit)

(2) By Spindrift (spindrift) on 2024-01-11 22:17:46 in reply to 1 [link] [source]

'Single quotes' around a string and "Double quotes" around a column name are standard SQL and now enforced in sqlite's cli.

(3) By Mark Lawrence (mark) on 2024-01-12 08:44:03 in reply to 1 [link] [source]

This does not look like a user error to me. I think your single quotes are appropriate for the text literals you are comparing against.

What version are you running? I don't see the same issue with the following:

SQLite 3.38.0 2022-02-14 01:12:46
c626cff7f99ec502ebd46f9cdafc3d088697544ef5d6559b6b3ae85679b4f9fa
zlib version 1.2.11
gcc-9.3.00

(4.1) By Spindrift (spindrift) on 2024-01-12 09:10:42 edited from 4.0 in reply to 3 [link] [source]

Really? This is "wrong", isn't it?

typeof("uuid") = "text"

(assuming use of cli which now defaults to the stricter parsing rules.)

Edit: And I see you're using a much older version of sqlite which precedes the default changes too - see my link in the first reply above.

(6) By Mark Lawrence (mark) on 2024-01-12 10:57:41 in reply to 4.1 [link] [source]

That is wrong, but that is not what was entered into the database. That is what SQLite has itself (wrongly) converted their schema into.

The cli may have stricter parsing today than before, but the poster's use was already correct and should not be impacted by that change.

(7.1) By Spindrift (spindrift) on 2024-01-12 11:18:53 edited from 7.0 in reply to 6 [link] [source]

Ah, I see what you mean. The existing schema is incorrect (though previously accepted), and not being updated with the (correct) new definition because the table already exists.

The easiest thing to do would be to correct the schema either by recreating the table and copying all the data over, or unwisely performing schema surgery directly on the internal structure tables (or using the dotconfig function to turn the quote checking off, which would be robust, compatible and avoid altering the database itself should that be a problem).

I don't think it's correct to assume that sqlite itself changed the quoting when storing the schema. It must have originally been specified with the quoting shown. But as it exists, it won't be altered by the OP's table creation attempt.

(9) By Richard Hipp (drh) on 2024-01-12 11:43:09 in reply to 7.1 [link] [source]

Try running these dot commands prior to importing the schema:

.dbconfig dqs_ddl on
.dbconfig dqs_dml on

That will put SQLite into the (legacy) MySQL-compatibiility mode where it accepts double-quoted strings as string literals if they cannot be resolved as identifiers.

(11) By freshteapot on 2024-01-12 21:19:31 in reply to 9 [link] [source]

I can confirm this imported.

sqlite3 b.db
.dbconfig dqs_ddl on
.dbconfig dqs_dml on
.read a.sql

I note that it kept the " syntax for CREATE. I learnt about the .dbconfig today :)

I was unable to use

pragma writable_schema = 1;

to make sqlite_master writable.

I dont have many tables to fix. So I think I am going to create new tables and fix the issue instead of remembering the need to handle this when I import.

Thank you for your helping and sharing the knowledge.

(5) By freshteapot on 2024-01-12 09:34:04 in reply to 1 [link] [source]

I feel you are both right.

The database is from before the "cli got strict". Which is why on 3.38 you can have it work with " and on 3.44 it wont.

I think my best solution here is to:

create a temp table which 3.44 is happy with
import the data via SELECT
Alter the table name back

I don't know how many people use CHECK, but this has the potential to trip a few people up if they .dump the db and import it elsewhere.

(8) By Mark Lawrence (mark) on 2024-01-12 11:18:23 in reply to 5 [link] [source]

You can modify the schema directly by setting the writable_schema PRAGMA, and using standard UPDATE against sqlite_master.

Alternatively, filter the dump output with your favourite tool:

sqlite3 a.db .dump > a.sql
perl -p -i -E "s/^(\s+typeof\(\"[a-z_A-Z]+\"\) = )\"text\" AND/\1'text' AND/" a.sql
sqlite b.db < a.sql

(10) By Dan Kennedy (dan) on 2024-01-12 13:54:54 in reply to 5 [link] [source]

Hi,

The 3.45 CLI will automatically turn on the DQS_DDL option (mysql compatibility mode) if it is executing a script from ".dump" against an empty database.

https://sqlite.org/src/info/f47a5f4e0ce078e6

Dan.