CLI .lint question

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

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
|  
Report Content as Inappropriate

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
Loading...