FTS - IF NOT EXISTS missing from sqlite_master

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

FTS - IF NOT EXISTS missing from sqlite_master

Daniel Santiago
Hi,

It seems that when creating a virtual table the 'IF NOT EXISTS' part is
missing from the sql column in sqlite_master for the recently created table
entry.

For example:
sqlite> CREATE VIRTUAL TABLE IF NOT EXISTS `Mail` USING FTS4(`subject`,
`body`);
sqlite> SELECT `sql` FROM `sqlite_master` WHERE `name` = 'Mail';
CREATE VIRTUAL TABLE `Mail` USING FTS4(`subject`, `body`)

Is this intended behaviour?

Thanks,
-Dany
_______________________________________________
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: FTS - IF NOT EXISTS missing from sqlite_master

Richard Hipp-3
On 8/19/18, Daniel Santiago <[hidden email]> wrote:

> Hi,
>
> It seems that when creating a virtual table the 'IF NOT EXISTS' part is
> missing from the sql column in sqlite_master for the recently created table
> entry.
>
> For example:
> sqlite> CREATE VIRTUAL TABLE IF NOT EXISTS `Mail` USING FTS4(`subject`,
> `body`);
> sqlite> SELECT `sql` FROM `sqlite_master` WHERE `name` = 'Mail';

Using a grave accent to quote a column or table name is a mysql-ism.
It only works in MySQL (and also in SQLite because SQLite tries to be
compatible with everybody). The SQL standard notation is double-quote:

    SELECT "sql" FROM "sqlite_master" WHERE "name" = 'Mail';


> CREATE VIRTUAL TABLE `Mail` USING FTS4(`subject`, `body`)
>
> Is this intended behaviour?

Yes, it works as intended.  The IF NOT EXISTS has already been
processed and found to be true before the text is inserted into the
sqlite_master table, so why keep it around?
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users