Re: [sqlite] Valid characters for indentifiers
Simon Slavin wrote: > On 14 Nov 2017, at 7:56am, Clemens Ladisch wrote: >> The documentation claims ANSI SQL 1992 compatiblity; all identifiers >> conforming to the standard are supported. > > SQLite is case-insensitive for entity names. SQL92 says that case matters. What I meant to say: all characters valid in SQL92 identifiers are also valid in SQLite identifiers. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Valid characters for indentifiers
On 14 Nov 2017, at 7:56am, Clemens Ladisch wrote: > The documentation claims ANSI SQL 1992 compatiblity; all identifiers > conforming to the standard are supported. SQLite is case-insensitive for entity names. SQL92 says that case matters. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Valid characters for indentifiers
Jens Alfke wrote: > On Nov 11, 2017, at 6:31 AM, Simon Slavin wrote: >> There is no documentation for this. Which means that even if you find >> that, say, macrons are allowed in this version, they might not be allowed >> in the next version. > > Um, really? That sounds bad for compatibility. Why isn’t it documented, > or stable? The documentation claims ANSI SQL 1992 compatiblity; all identifiers conforming to the standard are supported. Extensions (e.g., dollar signs in the middle of identifiers; underscore at the start; Unicode characters that are not letters, syllables, or ideographs) are undocumented but will probably never be removed to avoid breaking backwards compatibility. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Valid characters for indentifiers
On 13 Nov 2017, at 7:33pm, Jens Alfke wrote: > On Nov 11, 2017, at 6:31 AM, Simon Slavin wrote: > >> There is no documentation for this. Which means that even if you find that, >> say, macrons are allowed in this version, they might not be allowed in the >> next version. > > Um, really? That sounds bad for compatibility. Why isn’t it documented, or > stable? One for the Dev team to answer. If it was documented, it would be stable, at least within what the documentation admits to. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Valid characters for indentifiers
> On Nov 11, 2017, at 6:31 AM, Simon Slavin wrote: > > There is no documentation for this. Which means that even if you find that, > say, macrons are allowed in this version, they might not be allowed in the > next version. Um, really? That sounds bad for compatibility. Why isn’t it documented, or stable? (My situation: my library generates SQL queries that sometimes use identifiers created by the library’s client, i.e. some 3rd party app. It’s very useful to be able to use these identifiers as-is by prefixing some sort of namespace (like “fts::”) and wrapping the token in quotes. (Of course I have to check that the identifier doesn’t itself contain an ASCII double-quote.)) —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Valid characters for indentifiers
On Sun, 12 Nov 2017 10:19:03 +0100, Clemens Ladisch wrote: > Kees Nuyt wrote: >> It conforms to the SQL standard, you can use the Postgresql docs >> as a reference. > > Actually, neither SQLite nor PostgreSQL conform to the SQL standard. > [...] Thanks for the heads-up! -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Valid characters for indentifiers
Bart Smissaert wrote on Sat, 11 Nov 2017 11:04:37>What are the exact rules for valid identifier names (tables, columns and indexes)? Enclosed in double quotes practically anything, but using special characters like /,@,;,#,+,*,% are not really recommended, just as using key-words ("TABLE", "COLUMN" etc see SQLite Query Language: SQLite Keywords ) | | | | SQLite Query Language: SQLite Keywords | | | More to it you may find on Stack Overflow | | | | | | | | | | | What SQLite column name can be/cannot be? Is there any rule for the SQLite's column name? Can it have characters like '/'? Can it be UTF-8? | | | Kind regards | Vriendelijke groeten | Cordiali saluti, Klaas `Z4us` van Buiten V, Experienced Freelance ICT-Guy https://www.linkedin.com/in/klaas-van-buiten-0325b2102 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Valid characters for indentifiers
Kees Nuyt wrote: > It conforms to the SQL standard, you can use the Postgresql docs > as a reference. Actually, neither SQLite nor PostgreSQL conform to the SQL standard. The SQL standard requires that delimited identifiers are case sensitive and can contain double quotes, and that undelimited identifiers are folded to upper case. SQLite's identifiers never are case sensitive. PostgreSQL folds undelimited identifiers to lower case, and does not allow double quotes inside a delimited identifier. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Valid characters for indentifiers
On 2017/11/11 7:23 PM, Bart Smissaert wrote: Yes, that this is mainly for SQLite and thanks for explaining and that is what I thought. I think square brackets are better than double quotes as it looks better and parsing SQL gets easier as the opening and closing character are not the same. But then for compatibility with other DB's double quotes are better. I agree, the square brackets can be very easy on the eyes when trying to decipher SQL from ASCII soup, and no doubt this was M$'s reasoning when introducing it, but these days with syntax highlighting available near everywhere, the point is a bit moot. The beauty of double-quotes is that it is the SQL standard and is acceptable by all 90% of DB engines (except of course notoriously where [ and ] is a must) which make multi-platform SQL easier if you keep to it - except, non-quoted identifiers IS actually accepted 100% universally (unless there's an edge case I am unaware of). I design everything to be non-Keyword, non-funny so that it NEVER needs quoting. The possible permutations of using only Alphanumerics is more than all the words you have ever spoken in your lifetime, if not more than all the words that have ever been spoken in totality... There is just no reason to have to go funny-char++ (unless perhaps you are programming in Russian/Chinese). That said, as Richard pointed out, it can be beneficial to use those quotes in protecting SQL code from all future SQLite Keyword additions, but even so, the chances of adding a keyword I've used as an identifier is infinitesimally small, so I'll be a Maverick and keep going lite. :) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Valid characters for indentifiers
On 11/11/17, R Smith wrote: > > Further to this, an Identifier can remain unquoted (plain text), except: > > - when it is the same as an SQLite Keyword. Correct. Unfortunately, we do occasionally add new keywords. The most recent example is version 3.8.3 (2014-02-03) when we added support for common table expressions, which required two new keywords: WITH and RECURSIVE. In order to make sure that new keywords do not break legacy applications that might be using those keywords as identifiers, the parser is rather forgiving of the misuse of keywords as identifiers. Whenever a keyword token is encounter in a context where an identifier would make sense but the keyword would be a syntax error, the token can be used as an identifier. This kind of thing is discouraged, since it can result in SQL that is confusing to human readers, but it does have the virtual of preserving backwards compatibility. So, for example, even though WITH and RECURSIVE are now keywords, you can still say: CREATE TABLE t1(with,recursive); SELECT with FROM t1 WHERE recursive=1; I repeat: Even though you can do this, you should not. I have observed that identifiers in SQLite databases on Macs and iPhones always start with the letter Z. There are no SQL keywords that begin with Z, so I'm guessing the initial Z in Mac/iOS identifiers is to avoid the possibility of any future keyword collisions. I also observe that many programmers familiar with SQL-Server put all identifiers inside [...]. As far as I know, SQL-Server and SQLite are the only database engines that support this syntax. Putting all identifiers inside [...] helps to prevent problems in the case that new keywords get added in the future, just alike prepending Z to all identifiers does on Mac/iOS. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Valid characters for indentifiers
Yes, that this is mainly for SQLite and thanks for explaining and that is what I thought. I think square brackets are better than double quotes as it looks better and parsing SQL gets easier as the opening and closing character are not the same. But then for compatibility with other DB's double quotes are better. RBS On Sat, Nov 11, 2017 at 4:52 PM, R Smith wrote: > On 2017/11/11 1:04 PM, Bart Smissaert wrote: > >> What are the exact rules for valid identifier names (tables, columns and >> indexes)? >> This is both for names enclosed in square brackets ([]) or double quotes >> (") and also for names >> that are not enclosed within square brackets or double quotes. >> > > The question is phrased quite generally, but if you perhaps meant w.r.t. > SQLite specifically - It's quite straight forward: > > An Identifier can be any text, including non-standard characters (Unicode > Chinese etc.) and including no text at all (empty string) as long as it is > quoted with Identifier-compatible quotation marks. > > Valid Identifier Quotation characters are: [, ], ` and " > > Further to this, an Identifier can remain unquoted (plain text), except: > - when it is empty text or > - when it contains any characters outside the ASCII Alpha-numeric > characters and Underscore, or > - when it starts with a number (digit), or (Suddenly not 100% sure of > this, may need to check it) > - when it is the same as an SQLite Keyword. > > Further to this, SQLite will be very forgiving if you use the wrong type > of quotes in some circumstances (where the intention is obvious), or have > an identifier that is the same as a keyword but you only use it in a phrase > where the keyword can't possibly be found. > > Read more about it here: > http://sqlite.org/lang_keywords.html > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Valid characters for indentifiers
On 2017/11/11 6:43 PM, Simon Slavin wrote: On 11 Nov 2017, at 4:40pm, Kees Nuyt wrote: It conforms to the SQL standard, you can use the Postgresql docs as a reference. https://www.postgresql.org/docs/7.1/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS "the SQL standard will not define a key word that contains digit" Does that mean that column names like "column1" shouldn’t work ? No, it simply means that the folks designing the SQL standard promised they would never make a new Keyword as part of the spec that will contain number digits and force it upon us. It has nothing to do with what you call your identifiers. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Valid characters for indentifiers
On 2017/11/11 1:04 PM, Bart Smissaert wrote: What are the exact rules for valid identifier names (tables, columns and indexes)? This is both for names enclosed in square brackets ([]) or double quotes (") and also for names that are not enclosed within square brackets or double quotes. The question is phrased quite generally, but if you perhaps meant w.r.t. SQLite specifically - It's quite straight forward: An Identifier can be any text, including non-standard characters (Unicode Chinese etc.) and including no text at all (empty string) as long as it is quoted with Identifier-compatible quotation marks. Valid Identifier Quotation characters are: [, ], ` and " Further to this, an Identifier can remain unquoted (plain text), except: - when it is empty text or - when it contains any characters outside the ASCII Alpha-numeric characters and Underscore, or - when it starts with a number (digit), or (Suddenly not 100% sure of this, may need to check it) - when it is the same as an SQLite Keyword. Further to this, SQLite will be very forgiving if you use the wrong type of quotes in some circumstances (where the intention is obvious), or have an identifier that is the same as a keyword but you only use it in a phrase where the keyword can't possibly be found. Read more about it here: http://sqlite.org/lang_keywords.html ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Valid characters for indentifiers
On 11 Nov 2017, at 4:40pm, Kees Nuyt wrote: > It conforms to the SQL standard, you can use the Postgresql docs > as a reference. > > https://www.postgresql.org/docs/7.1/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS "the SQL standard will not define a key word that contains digit" Does that mean that column names like "column1" shouldn’t work ? Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Valid characters for indentifiers
On Sat, 11 Nov 2017 11:04:37 +, Bart Smissaert wrote: > What are the exact rules for valid identifier names (tables, columns and > indexes)? > This is both for names enclosed in square brackets ([]) or double quotes > (") and also for names > that are not enclosed within square brackets or double quotes. > Had a good look for this, but couldn't find a clear answer. It conforms to the SQL standard, you can use the Postgresql docs as a reference. https://www.postgresql.org/docs/7.1/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS If you want to use a keyword (or an otherwise invalid word) as an identifier, you have to quote it. SQLite has its own lost of keywords. https://sqlite.org/lang_keywords.html -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Valid characters for indentifiers
On 11 Nov 2017, at 11:04am, Bart Smissaert wrote: > What are the exact rules for valid identifier names (tables, columns and > indexes)? There is no documentation for this. Which means that even if you find that, say, macrons are allowed in this version, they might not be allowed in the next version. > This is both for names enclosed in square brackets ([]) or double quotes > (") and also for names > that are not enclosed within square brackets or double quotes. Please note this distinction: A) The names Col1 and "Col1" are both allowed and refer to the same entity. B) The names Col1 and "Col1" are both allowed and refer to different entities. Some SQL engines do one, some do the other. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users