CLI .lint question

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

CLI .lint question

David Raymond
Looks like the .lint command has been in since 3.16.0, but this is my first time noticing it and trying it out. I ran the .lint fkey-indexes on one of my larger databases where I had thought I had indexed all the foreign keys ok, and it spat out a giant swath of text, basically for every foreign key. I'm pretty sure there's a bug in there triggered by collations. I'm also curious as to why it's putting identifiers in single ' quotes.

Here's a simplified example:

SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table a (id integer primary key);
Run Time: real 0.001 user 0.000000 sys 0.000000

sqlite> create table b (id integer primary key, b integer references a);
Run Time: real 0.001 user 0.000000 sys 0.000000

sqlite> .lint
Usage lint sub-command ?switches...?
Where sub-commands are:
    fkey-indexes

sqlite> .lint fkey-indexes
CREATE INDEX 'b_b' ON 'b'('b'); --> a(id)

sqlite> create index idx_b on b (b);
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> .lint fkey-indexes

sqlite> drop table b;
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> drop table a;
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> create table a (uuid text not null primary key collate nocase, a int, b text collate nocase);
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> create table b (uuid text not null primary key collate nocase, a int, b text collate nocase references a);
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> create index idx_b on b (b);
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> .lint fkey-indexes
CREATE INDEX 'b_b' ON 'b'('b' COLLATE BINARY); --> a(uuid)
_______________________________________________
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: CLI .lint question

Dan Kennedy-4
On 04/06/2017 02:58 AM, David Raymond wrote:
> Looks like the .lint command has been in since 3.16.0, but this is my first time noticing it and trying it out. I ran the .lint fkey-indexes on one of my larger databases where I had thought I had indexed all the foreign keys ok, and it spat out a giant swath of text, basically for every foreign key. I'm pretty sure there's a bug in there triggered by collations.

Thanks for reporting this. Should now be fixed here:

   http://www.sqlite.org/src/info/327eff25ba242026

Dan.




> I'm also curious as to why it's putting identifiers in single ' quotes.
>
> Here's a simplified example:
>
> SQLite version 3.18.0 2017-03-28 18:48:43
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
>
> sqlite> create table a (id integer primary key);
> Run Time: real 0.001 user 0.000000 sys 0.000000
>
> sqlite> create table b (id integer primary key, b integer references a);
> Run Time: real 0.001 user 0.000000 sys 0.000000
>
> sqlite> .lint
> Usage lint sub-command ?switches...?
> Where sub-commands are:
>      fkey-indexes
>
> sqlite> .lint fkey-indexes
> CREATE INDEX 'b_b' ON 'b'('b'); --> a(id)
>
> sqlite> create index idx_b on b (b);
> Run Time: real 0.000 user 0.000000 sys 0.000000
>
> sqlite> .lint fkey-indexes
>
> sqlite> drop table b;
> Run Time: real 0.000 user 0.000000 sys 0.000000
>
> sqlite> drop table a;
> Run Time: real 0.000 user 0.000000 sys 0.000000
>
> sqlite> create table a (uuid text not null primary key collate nocase, a int, b text collate nocase);
> Run Time: real 0.000 user 0.000000 sys 0.000000
>
> sqlite> create table b (uuid text not null primary key collate nocase, a int, b text collate nocase references a);
> Run Time: real 0.000 user 0.000000 sys 0.000000
>
> sqlite> create index idx_b on b (b);
> Run Time: real 0.000 user 0.000000 sys 0.000000
>
> sqlite> .lint fkey-indexes
> CREATE INDEX 'b_b' ON 'b'('b' COLLATE BINARY); --> a(uuid)
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users