Re: [sqlite] Valid characters for indentifiers

2017-11-14 Thread Clemens Ladisch
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

2017-11-14 Thread Simon Slavin


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

2017-11-13 Thread Clemens Ladisch
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

2017-11-13 Thread Simon Slavin


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

2017-11-13 Thread Jens Alfke


> 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

2017-11-12 Thread Kees Nuyt
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

2017-11-12 Thread Klaas Van B.
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

2017-11-12 Thread Clemens Ladisch
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

2017-11-11 Thread R Smith



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

2017-11-11 Thread Richard Hipp
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

2017-11-11 Thread Bart Smissaert
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

2017-11-11 Thread R Smith



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

2017-11-11 Thread R Smith

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

2017-11-11 Thread Simon Slavin


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

2017-11-11 Thread Kees Nuyt
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

2017-11-11 Thread Simon Slavin


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