Hi All,
When I rename a table, SQLite seems to override the quote characters I
use, instead using single quote marks. Is this a bug?
I prefer to use double quotes or square brackets for entity and column
names (aka "user-defined objects"), obeying the SQLite documentation,
but SQLite overrides me and the documentation.
Here's the SQLite documentation at http://www.sqlite.org/lang_keywords.html
SQLite adds new keywords from time to time when it take on new
features. So to prevent your code from being broken by future
enhancements, you should normally quote any indentifier that is an
English language word, even if you do not have to.
If you want to use a keyword as a name, you need to quote it. There
are three ways of quoting keywords in SQLite:
'keyword'
A keyword in single quotes is interpreted as a literal string if it
occurs in a context where a string literal is allowed, otherwise it
is understood as an identifier.
"keyword"
A keyword in double-quotes is interpreted as an identifier if it
matches a known identifier. Otherwise it is interpreted as a string
literal.
[keyword]
A keyword enclosed in square brackets is always understood as an
identifier. This is not standard SQL. This quoting mechanism is used
by MS Access and SQL Server and is included in SQLite for
compatibility.
Here's an example of the problem:
create table [Old Table Square] ( Test );
create table 'Old Table Single' ( Test );
create table "Old Table Double" ( Test );
create trigger "Old Trigger Double"
after delete on "Old Table Double"
begin
delete from "Other Table";
end;
alter table [Old Table Square] rename to [New Table Square];
alter table 'Old Table Single' rename to 'New Table Single';
alter table "Old Table Double" rename to "New Table Double";
.dump
gives:
CREATE TABLE 'New Table Square' ( Test );
CREATE TABLE 'New Table Single' ( Test );
CREATE TABLE 'New Table Double' ( Test );
CREATE TRIGGER "Old Trigger Double"
after delete on 'New Table Double'
begin
delete from "Other Table";
end;
Notice that the quotes of the table names have all changed to single
quotes, in the create table and create trigger statements, after
renaming.
To me, this seems bad, because single quote marks are also interpreted
as enclosing a literal string.
Thanks,
Tom
-
To unsubscribe, send email to [EMAIL PROTECTED]
-