RE: [sqlite] RFC Beginning digit in column name
Dr. Hipp isn't showing his hand here, but the timeline indicates that he
added support for MySQL-style backquote quoting on Saturday:
http://www.sqlite.org/cvstrac/chngview?cn=2591
--Ned.
http://nedbatchelder.com
-Original Message-
From: Marcus Welz [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 10 August, 2005 4:23 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] RFC Beginning digit in column name
Hello there,
I think that adhering to standards is a Very Good Thing(tm). They are
standards for a reason. Deviations can introduce ambiguity, confusion,
complexity, vendor lock-ins and all sorts of other headaches.
That said, however, I believe that "no, because it's not the standard" isn't
exactly what a customer wants to hear. Then again "no" isn't what a customer
wants to ever hear in almost any context, but I digress..
Customers / users may wonder "Well, they (e.g. MySQL) can do it, why can't
you?" And I think in a fair number of situations, if possible to implement
at a reasonable amount of cost the answer should be "We can, but be warned,
it is NOT the standard."
Some proprietary extensions are rather handy, too, and may want be be
considered. I don't really think "`" and "[]" are useful nor pretty, but I
also don't think that it would be unethical to support it. On the other
hand, MySQL's "SQL_CALC_FOUND_ROWS" keyword and the "FOUND_ROWS()" function
for instance have made my life easy in the past. (SQL_CALC_FOUND_ROWS in a
query counts how many rows there would have been returned with the LIMIT
clause omitted, great for pagination if you want to display something like
"Rows 1 - 15, 400 total" all while running a complex query (with a whole
slew of JOINS and WHERE conditions) only once.
The short of it all: I don't know much about SQLite's internal architecture,
besides glossing over the web site documentation, but my suggestion is to
add a new PRAGMA statement ("PRAGMA tolerate_broken_sql = 1" :-), which
should be _disabled_ by default, but when enabled would honor proprietary
extensions such as "[]" and "`" for quotation, digits in column names, and
who knows, maybe even some of the other extensions and vendor specific
functions, etc.
It would allow numerous projects to more easily migrate away from MySQL to
SQLite without needing to clean up all their broken SQL instantly, and it
would allow projects that want to stick with MySQL to also offer a SQLite
version with little effort.
How off the wall am I with this?
-m
-Original Message-
From: Austin Ziegler [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 10, 2005 2:44 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] RFC Beginning digit in column name
On 8/10/05, ender <[EMAIL PROTECTED]> wrote:
> Austin Ziegler wrote:
>>On 8/9/05, ender <[EMAIL PROTECTED]> wrote:
>>> So my simple feature request would be: allow '`' as a quoting symbol -
>>> as mySQL does. Or - what would be as helpful as the other idea - allow
>>> unquoted column names with leading digits - as mySQL does.
>>> Also see ticket # 1337 http://www.sqlite.org/cvstrac/tktview?tn=1337
>> I think that supporting '`' (backquote) would be a bad idea. Then
>> again, MySQL ignores so much of the SQL standard that I think that
>> importing any idea from MySQL is a bad idea.
> What are your reasons for thinking supporting '`' is a bad idea?
1. It's not part of the standard.
2. It's not part of the standard.
3. MySQL does it. Therefore, no one else should do it, because:
4. It's not part of the standard (and most of what MySQL does is not
part of the standard).
If you've learned "SQL" on MySQL, you haven't learned SQL. You've
learned MySQL. The same applies to people who want to do [tablename]
-- it's a Microsoftism that (IMO) should be *de-supported* in SQLite
because it's ugly and wrong.
The only quotes that SQL92 supports are ' and "; ' indicates data, "
indicates literal interpretation of table and column names. Not `, not
[].
If MySQL does it and it's not part of the standard, I'm opposed to it.
-austin
--
Austin Ziegler * [EMAIL PROTECTED]
* Alternate: [EMAIL PROTECTED]
Re: [sqlite] RFC Beginning digit in column name
Marcus Welz wrote:
Hello there,
I think that adhering to standards is a Very Good Thing(tm). They are
standards for a reason. Deviations can introduce ambiguity, confusion,
complexity, vendor lock-ins and all sorts of other headaches.
That said, however, I believe that "no, because it's not the standard" isn't
exactly what a customer wants to hear. Then again "no" isn't what a customer
wants to ever hear in almost any context, but I digress..
Customers / users may wonder "Well, they (e.g. MySQL) can do it, why can't
you?" And I think in a fair number of situations, if possible to implement
at a reasonable amount of cost the answer should be "We can, but be warned,
it is NOT the standard."
Some proprietary extensions are rather handy, too, and may want be be
considered. I don't really think "`" and "[]" are useful nor pretty, but I
also don't think that it would be unethical to support it. On the other
hand, MySQL's "SQL_CALC_FOUND_ROWS" keyword and the "FOUND_ROWS()" function
for instance have made my life easy in the past. (SQL_CALC_FOUND_ROWS in a
query counts how many rows there would have been returned with the LIMIT
clause omitted, great for pagination if you want to display something like
"Rows 1 - 15, 400 total" all while running a complex query (with a whole
slew of JOINS and WHERE conditions) only once.
The short of it all: I don't know much about SQLite's internal architecture,
besides glossing over the web site documentation, but my suggestion is to
add a new PRAGMA statement ("PRAGMA tolerate_broken_sql = 1" :-), which
should be _disabled_ by default, but when enabled would honor proprietary
extensions such as "[]" and "`" for quotation, digits in column names, and
who knows, maybe even some of the other extensions and vendor specific
functions, etc.
I think that isn't a bad idea necessarily, but in general, more pragmas
means more configuration, means
more mysterious settings that need to be
explained/documented/maintained. SQLite is against requiring
configuration.
It would allow numerous projects to more easily migrate away from MySQL to
SQLite without needing to clean up all their broken SQL instantly, and it
But if it's supported in sqlite, they'll never migrate away from it.
Supporting something that's
non-standard because it makes it easier to migrate code isn't, to me,
enough justification. Supporting
something that's non-standard because it makes it easier to do something
important can be a good
idea. Fixing your sql to be standard is something you need to do
whenever you switch away from
mysql, no matter what you're migrating to.
would allow projects that want to stick with MySQL to also offer a SQLite
version with little effort.
How off the wall am I with this?
-m
John LeSueur
RE: [sqlite] RFC Beginning digit in column name
Hello there,
I think that adhering to standards is a Very Good Thing(tm). They are
standards for a reason. Deviations can introduce ambiguity, confusion,
complexity, vendor lock-ins and all sorts of other headaches.
That said, however, I believe that "no, because it's not the standard" isn't
exactly what a customer wants to hear. Then again "no" isn't what a customer
wants to ever hear in almost any context, but I digress..
Customers / users may wonder "Well, they (e.g. MySQL) can do it, why can't
you?" And I think in a fair number of situations, if possible to implement
at a reasonable amount of cost the answer should be "We can, but be warned,
it is NOT the standard."
Some proprietary extensions are rather handy, too, and may want be be
considered. I don't really think "`" and "[]" are useful nor pretty, but I
also don't think that it would be unethical to support it. On the other
hand, MySQL's "SQL_CALC_FOUND_ROWS" keyword and the "FOUND_ROWS()" function
for instance have made my life easy in the past. (SQL_CALC_FOUND_ROWS in a
query counts how many rows there would have been returned with the LIMIT
clause omitted, great for pagination if you want to display something like
"Rows 1 - 15, 400 total" all while running a complex query (with a whole
slew of JOINS and WHERE conditions) only once.
The short of it all: I don't know much about SQLite's internal architecture,
besides glossing over the web site documentation, but my suggestion is to
add a new PRAGMA statement ("PRAGMA tolerate_broken_sql = 1" :-), which
should be _disabled_ by default, but when enabled would honor proprietary
extensions such as "[]" and "`" for quotation, digits in column names, and
who knows, maybe even some of the other extensions and vendor specific
functions, etc.
It would allow numerous projects to more easily migrate away from MySQL to
SQLite without needing to clean up all their broken SQL instantly, and it
would allow projects that want to stick with MySQL to also offer a SQLite
version with little effort.
How off the wall am I with this?
-m
-----Original Message-----
From: Austin Ziegler [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 10, 2005 2:44 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] RFC Beginning digit in column name
On 8/10/05, ender <[EMAIL PROTECTED]> wrote:
> Austin Ziegler wrote:
>>On 8/9/05, ender <[EMAIL PROTECTED]> wrote:
>>> So my simple feature request would be: allow '`' as a quoting symbol -
>>> as mySQL does. Or - what would be as helpful as the other idea - allow
>>> unquoted column names with leading digits - as mySQL does.
>>> Also see ticket # 1337 http://www.sqlite.org/cvstrac/tktview?tn=1337
>> I think that supporting '`' (backquote) would be a bad idea. Then
>> again, MySQL ignores so much of the SQL standard that I think that
>> importing any idea from MySQL is a bad idea.
> What are your reasons for thinking supporting '`' is a bad idea?
1. It's not part of the standard.
2. It's not part of the standard.
3. MySQL does it. Therefore, no one else should do it, because:
4. It's not part of the standard (and most of what MySQL does is not
part of the standard).
If you've learned "SQL" on MySQL, you haven't learned SQL. You've
learned MySQL. The same applies to people who want to do [tablename]
-- it's a Microsoftism that (IMO) should be *de-supported* in SQLite
because it's ugly and wrong.
The only quotes that SQL92 supports are ' and "; ' indicates data, "
indicates literal interpretation of table and column names. Not `, not
[].
If MySQL does it and it's not part of the standard, I'm opposed to it.
-austin
--
Austin Ziegler * [EMAIL PROTECTED]
* Alternate: [EMAIL PROTECTED]
Re: [sqlite] RFC Beginning digit in column name
On 8/10/05, Austin Ziegler <[EMAIL PROTECTED]> wrote: > > What are your reasons for thinking supporting '`' is a bad idea? > > 1. It's not part of the standard. LOL! Standards are great! Everybody should have several. ;)
Re: [sqlite] RFC Beginning digit in column name
On 8/10/05, ender <[EMAIL PROTECTED]> wrote: > Austin Ziegler wrote: >>On 8/9/05, ender <[EMAIL PROTECTED]> wrote: >>> So my simple feature request would be: allow '`' as a quoting symbol - >>> as mySQL does. Or - what would be as helpful as the other idea - allow >>> unquoted column names with leading digits - as mySQL does. >>> Also see ticket # 1337 http://www.sqlite.org/cvstrac/tktview?tn=1337 >> I think that supporting '`' (backquote) would be a bad idea. Then >> again, MySQL ignores so much of the SQL standard that I think that >> importing any idea from MySQL is a bad idea. > What are your reasons for thinking supporting '`' is a bad idea? 1. It's not part of the standard. 2. It's not part of the standard. 3. MySQL does it. Therefore, no one else should do it, because: 4. It's not part of the standard (and most of what MySQL does is not part of the standard). If you've learned "SQL" on MySQL, you haven't learned SQL. You've learned MySQL. The same applies to people who want to do [tablename] -- it's a Microsoftism that (IMO) should be *de-supported* in SQLite because it's ugly and wrong. The only quotes that SQL92 supports are ' and "; ' indicates data, " indicates literal interpretation of table and column names. Not `, not []. If MySQL does it and it's not part of the standard, I'm opposed to it. -austin -- Austin Ziegler * [EMAIL PROTECTED] * Alternate: [EMAIL PROTECTED]
Re: [sqlite] RFC Beginning digit in column name
Austin Ziegler wrote: On 8/9/05, ender <[EMAIL PROTECTED]> wrote: So my simple feature request would be: allow '`' as a quoting symbol - as mySQL does. Or - what would be as helpful as the other idea - allow unquoted column names with leading digits - as mySQL does. Also see ticket # 1337 http://www.sqlite.org/cvstrac/tktview?tn=1337 I think that supporting '`' (backquote) would be a bad idea. Then again, MySQL ignores so much of the SQL standard that I think that importing any idea from MySQL is a bad idea. -austin What are your reasons for thinking supporting '`' is a bad idea?
Re: [sqlite] RFC Beginning digit in column name
> Does MySQL allows [0_xy] to specify column names? > > If I remember correctly, that is the standard SQL way to have columns > with spaces in the middle, and sqlite allows columns starting with a > digit in this way... It's not standard; it's a Microsoft extension. Regards
Re: [sqlite] RFC Beginning digit in column name
On 8/9/05, Nuno Lucas <[EMAIL PROTECTED]> wrote:
> [09-08-2005 19:05, ender escreveu]
> [...]
>> So my simple feature request would be: allow '`' as a quoting
>> symbol - as mySQL does. Or - what would be as helpful as the
>> other idea - allow unquoted column names with leading digits - as
>> mySQL does.
> Does MySQL allows [0_xy] to specify column names?
I don't think so.
> If I remember correctly, that is the standard SQL way to have
> columns with spaces in the middle, and sqlite allows columns
> starting with a digit in this way...
I'm not sure that it's a standard. I know Microsoft SQL Server and
Access DB use it, but from what I recall of the SQL92 standard, only
double quotes ("0_xy") are permitted for case-sensitive or otherwise
abnormal table and column names. Tables specified without this are
to be treated as uppercase only.
-austin
--
Austin Ziegler * [EMAIL PROTECTED]
* Alternate: [EMAIL PROTECTED]
Re: [sqlite] RFC Beginning digit in column name
At 8:46 PM +0100 8/9/05, Nuno Lucas wrote:
[09-08-2005 19:05, ender escreveu]
[...]
So my simple feature request would be: allow '`' as a quoting symbol -
as mySQL does. Or - what would be as helpful as the other idea - allow
unquoted column names with leading digits - as mySQL does.
Does MySQL allows [0_xy] to specify column names?
If I remember correctly, that is the standard SQL way to have columns
with spaces in the middle, and sqlite allows columns starting with a
digit in this way...
Regards,
~Nuno Lucas
The SQL standard has both delimited and bareword identifiers.
Bareword identifiers can only be letters, numbers, and underscore,
and start with a letter, to my knowledge; they are also
case-insensitive.
Delimited identifiers can have any characters in them, including
spaces, and they are delimited with double-quotes (") usually.
The standard may allow other delimiting characters, but I'm not sure.
I imagine that back-ticks (`) may be safe to support if they aren't
already used by SQL in some other way. You certainly don't want to
use single-quotes (') as those are always literal string delimiters.
You also don't want to use brackets ([]) as those are used by the
standard for array indices.
I suggest for simplicity that SQLite simply support single-quotes for
string delimiters and double-quotes for identifiers; clearly distinct
and simple.
-- Darren Duncan
Re: [sqlite] RFC Beginning digit in column name
[09-08-2005 19:05, ender escreveu] [...] > So my simple feature request would be: allow '`' as a quoting symbol - > as mySQL does. Or - what would be as helpful as the other idea - allow > unquoted column names with leading digits - as mySQL does. Does MySQL allows [0_xy] to specify column names? If I remember correctly, that is the standard SQL way to have columns with spaces in the middle, and sqlite allows columns starting with a digit in this way... Regards, ~Nuno Lucas
Re: [sqlite] RFC Beginning digit in column name
On 8/9/05, ender <[EMAIL PROTECTED]> wrote: > > Dear developers, > > a while back, our project decided to switch from mysql to usage of > various SQL backends, among this sqlite3 as our favorite. Now recently > we encountered a problem, just a tiny one, but one we are not able to > solve with all used SQl backends in a common sense. > > The problem arised within a table that had a column called '0_uid'. As > you might guess, the problem comes from the beginning digit in the > column name. I looked arround quite a while and learned, that it was > pure luck that we haven't had problems with that so far - as we relied > upon an SQL extension that mySQL offered. After some reading and trying > I found out, that sqlite3 also was able to handle such columns, but the > column name has to be quoted. Ok, that sounds fair - BUT I had to learn > one sad thing... mySQL and sqlite3 don't agree upon a common subset of > symbols to quote. Whilest the one accepts ' and ", the other relies upon `. > > So my simple feature request would be: allow '`' as a quoting symbol - > as mySQL does. Or - what would be as helpful as the other idea - allow > unquoted column names with leading digits - as mySQL does. > Also see ticket # 1337 http://www.sqlite.org/cvstrac/tktview?tn=1337 I think that supporting '`' (backquote) would be a bad idea. Then again, MySQL ignores so much of the SQL standard that I think that importing any idea from MySQL is a bad idea. -austin -- Austin Ziegler * [EMAIL PROTECTED] * Alternate: [EMAIL PROTECTED]
[sqlite] RFC Beginning digit in column name
Dear developers, a while back, our project decided to switch from mysql to usage of various SQL backends, among this sqlite3 as our favorite. Now recently we encountered a problem, just a tiny one, but one we are not able to solve with all used SQl backends in a common sense. The problem arised within a table that had a column called '0_uid'. As you might guess, the problem comes from the beginning digit in the column name. I looked arround quite a while and learned, that it was pure luck that we haven't had problems with that so far - as we relied upon an SQL extension that mySQL offered. After some reading and trying I found out, that sqlite3 also was able to handle such columns, but the column name has to be quoted. Ok, that sounds fair - BUT I had to learn one sad thing... mySQL and sqlite3 don't agree upon a common subset of symbols to quote. Whilest the one accepts ' and ", the other relies upon `. So my simple feature request would be: allow '`' as a quoting symbol - as mySQL does. Or - what would be as helpful as the other idea - allow unquoted column names with leading digits - as mySQL does. Also see ticket # 1337 http://www.sqlite.org/cvstrac/tktview?tn=1337
