lint CLI command

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

lint CLI command

Bernd Lehmkuhl-3
Good day altogether,

I suppose there might be a problem with reporting missing indexes on
foreign key columns in conjunction with the without rowid clause:

C:\Users\Bernd>sqlite3
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE t_parent (id text PRIMARY KEY NOT NULL);
sqlite> CREATE TABLE t_child_standard (id text PRIMARY KEY NOT NULL
REFERENCES t_parent (id) ON DELETE CASCADE, somethingelse text);
sqlite> CREATE TABLE t_child_wr (id text PRIMARY KEY NOT NULL REFERENCES
t_parent (id) ON DELETE CASCADE, somethingelse text) WITHOUT ROWID;
sqlite> .lint fkey-indexes
CREATE INDEX 't_child_wr_id' ON 't_child_wr'('id'); --> t_parent(id)
sqlite> .q

C:\Users\Bernd>

It looks like the primary key isn't realized as such when the without
rowid clause is used.

Thanks, Bernd
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: lint CLI command

Bernd Lehmkuhl-3
Am 14.08.2018 um 20:42 schrieb Bernd Lehmkuhl:

> Good day altogether,
>
> I suppose there might be a problem with reporting missing indexes on
> foreign key columns in conjunction with the without rowid clause:
>
> C:\Users\Bernd>sqlite3
> SQLite version 3.24.0 2018-06-04 19:24:41
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> CREATE TABLE t_parent (id text PRIMARY KEY NOT NULL);
> sqlite> CREATE TABLE t_child_standard (id text PRIMARY KEY NOT NULL
> REFERENCES t_parent (id) ON DELETE CASCADE, somethingelse text);
> sqlite> CREATE TABLE t_child_wr (id text PRIMARY KEY NOT NULL REFERENCES
> t_parent (id) ON DELETE CASCADE, somethingelse text) WITHOUT ROWID;
> sqlite> .lint fkey-indexes
> CREATE INDEX 't_child_wr_id' ON 't_child_wr'('id'); --> t_parent(id)
> sqlite> .q
>
> C:\Users\Bernd>
>
> It looks like the primary key isn't realized as such when the without
> rowid clause is used.
>
> Thanks, Bernd



Moreover, it'd be a bit more comfortable if the names of the index,
table and column of the resulting sql weren't put in single quotation
marks, but that's not so important.
But I consider it a bug - even though I know I have to be careful with
this word - because in both cases the primary keys are recognized as such:
sqlite> pragma index_list(t_child_standard);
0|sqlite_autoindex_t_child_standard_1|1|pk|0
sqlite> pragma index_list(t_child_wr);
0|sqlite_autoindex_t_child_wr_1|1|pk|0
sqlite>

Oh - just saw this in the change logs:
3.22.0
(10) Improvements to the command-line shell:
...
5. Enhance the ".lint fkey-indexes" command so that it works with
WITHOUT ROWID tables.
...

Regression? Nope. Same output in 3.22.0 ...
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users