Re: [sqlite] Is sqlite case-sensitive?

[Dominique Devienne]

On Sun, May 18, 2014 at 9:46 PM, James K. Lowden
 wrote:
> I can easily imagine uses for a generalized SQLite parser, one that
> returned an abstract syntax tree.  A program might use such a thing for
> all sorts of "server-side" functionality, for example to process a
> string provided to a virtual table function.

I very much wish for that in fact!

Many of our app's UI tables and trees are based on "model" SQL queries
that return the "raw" data, which are then "massaged", "wrapped",
etc... to derive new ("model" or "view") queries, and we've found
several situations where using the raw model queries as an inner query
is inefficient. We do get some info from prepared statements, by
looking at the VDBE bytecode, but ended up asking client code to
provide us a query by "pieces" (separate clauses, sometimes further
broken down) rather than a complete query, to work-around the lack of
AST. Doing our own parsing would likely be brittle and only work for
simpler queries, but really using the true full query AST is what we'd
need for a completely general solution, that is more API-friendly to
client code (they provide just full queries).

Ideally, there'd be two AST "levels":
1) A pure syntactic AST (which does not require a connection);
2) a resolved "semantically-analyzed" AST where all expressions are
resolved to known table.column "bindings" (or bind placeholders).

> But that's a very different sort of project. It wouldn't be easy to do
> in SQLite because AIUI the byte-code program produced by the prepare
> functions is concrete, comprising operations and references to actual
> database objects.

That would be my "resolved" AST. But I'm not sure you can get the
query AST from the VDBE program myself. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is sqlite case-sensitive?

[Jean-Christophe Deschamps]

At 22:36 18/05/2014, you wrote:


The more I
think of it, though, I think that the solution is as simple as converting
all letters to lower(/upper) case and converting all whitespace to a 
single

space each, except for within matching [ ], " ", ' ' or ` `. After that, I
can do a memcmp().


You're lucky that SQLite natively accepts Unicode schema names but is 
only case-insensitive over the 7-bit ASCII charset range, even when the 
ICU extension is built-in.


Requirement  R-26223-47623-19728-21792-34687-27643-09360-29816

Like other SQL identifiers, database names are case-insensitive. 
(source lang_naming.html, 
checked-by: tcl/e_resolve.test)


from http://www.sqlite.org/requirements.html is ambiguous and misleading.

CREATE TABLE ÉTUDE (a CHAR);  -- works
SELECT * FROM étude;  -- no such table étude
SELECT * FROM Étude;  -- works

(I hope my E and e with acute accents show up on your side)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is sqlite case-sensitive?

[Baruch Burstein]

On Sun, May 18, 2014 at 10:46 PM, James K. Lowden
wrote:

> On Sun, 18 May 2014 19:15:18 +0200
> RSmith  wrote:
>
> > > As Igor says, http://sqlite.org/c3ref/prepare.html would be
> > > appropriate. However, a database connection is required for this.
> >
> > But of course  What kind of syntactical correctness can you hope
> > to check without a connection?
>
> You could hope for the kind of syntactical correctness that conforms to
> the rules of the syntax.  No schema infomation is required for that.
>
> select A from T where W = 'foo';
>
> is valid syntax.  It might not execute correctly, expecially if there's
> no  table T with columns A and W, but that's not a syntax issue.
>

This is indeed what I was thinking of. A function that would mark the above
statement as 'valid', but mark

select from T where W='foo';
select name from from where 1;

as as 'invalid'.

I don't know if such a function would actually be useful, but I thought I
remembered that it already existed, and so I thought I would use it as a
basis for another function I wanted to write, which would compare 2 SQL
statements for equivalence, even if they don't memcmp() equal. The more I
think of it, though, I think that the solution is as simple as converting
all letters to lower(/upper) case and converting all whitespace to a single
space each, except for within matching [ ], " ", ' ' or ` `. After that, I
can do a memcmp().


-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is sqlite case-sensitive?

[James K. Lowden]

On Sun, 18 May 2014 19:15:18 +0200
RSmith  wrote:

> > As Igor says, http://sqlite.org/c3ref/prepare.html would be
> > appropriate. However, a database connection is required for this.
> 
> But of course  What kind of syntactical correctness can you hope
> to check without a connection?

You could hope for the kind of syntactical correctness that conforms to
the rules of the syntax.  No schema infomation is required for that.  

select A from T where W = 'foo';

is valid syntax.  It might not execute correctly, expecially if there's
no  table T with columns A and W, but that's not a syntax issue. 

Would a function that checks the syntax independent of schema be useful?
That depends on how it's defined. As things stand, SQLite might be able
to identify simple errors, 

sqlite> select 1a from T;
Error: unrecognized token: "1a"

That message is returned (with a connection, of course) from a database
with no table T.  It's a fine message, no objection here.  But I'm
having difficulty imagining how I might take advantage of it as a
programmer.  What's the difference between check-then-execute and
execute, if the error is the same?  

I can easily imagine uses for a generalized SQLite parser, one that
returned an abstract syntax tree.  A program might use such a thing for
all sorts of "server-side" functionality, for example to process a
string provided to a virtual table function.  

But that's a very different sort of project. It wouldn't be easy to do
in SQLite because AIUI the byte-code program produced by the prepare
functions is concrete, comprising operations and references to actual
database objects.  

--jkl


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is sqlite case-sensitive?

[RSmith]

On 2014/05/18 19:28, Wolfgang Enzinger wrote:

Completely agreed. I was just referring to the OP who asked for an "API


Indeed - my apologies too - I took your statement to imply that the solution (which Igor and yourself discussed) would not work for 
the OP precisely because of it needing a DB connection, which naturally caused the slightly lengthy digression - completely 
unnecessary since re-reading your post reveals it to be a simple aside and not really the fabric of my assumption.


I suppose looking on the bright side, if the OP did ponder in this vein, it's 
already answered! :)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is sqlite case-sensitive?

[Wolfgang Enzinger]

Am Sun, 18 May 2014 19:15:18 +0200 schrieb RSmith:

> But of course  What kind of syntactical correctness can you hope to
> check without a connection?

[...]

Completely agreed. I was just referring to the OP who asked for an "API to
validate a SQL statement, either in the context of the current connection
(validate also table/column/db names), or without context (just validate
syntax, e.g. that it can be parsed)". As you said, the latter couldn't be
more than keyword checking anyway.

Wolfgang

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is sqlite case-sensitive?

[RSmith]

On 2014/05/18 17:24, Wolfgang Enzinger wrote:


i _think_ what you want is:

http://sqlite.org/c3ref/complete.html

I don't think so, because this function essentially checks "if [the
statement] ends with a semicolon token". Furthermore, "these routines do
not parse the SQL statements thus will not detect syntactically incorrect
SQL".

As Igor says, http://sqlite.org/c3ref/prepare.html would be appropriate.
However, a database connection is required for this.


But of course  What kind of syntactical correctness can you hope to check 
without a connection?

I mean you can see if the SQL keywords used are valid keywords, i.e. SELECT, INSERT, CREATE, etc... but for that a simple Key-list 
look-up and white-space separation check would suffice. However to know whether the SQL itself is valid, that can never be done 
without a connection (i.e. knowledge of the DB Schema being available to the parser).


I mean the following will be valid SQL:
SELECT zxyyffCCgahajiklMM019101kkjdjhd12 FROM lkjsdndn23n2323 WHERE Patrick IS 
Dead;

But you would look far and wide to find an actual schema in existence for which that would be a valid statement. Simply having the 
words SELECT and FROM with anything in between might be fine in most cases.


A more sensible example would be the following statement:
CREATE TABLE xx (int INTEGER PRIMARY KEY) WITHOUT ROWID;

This will work on some versions of SQLite files and connections, but not on 
others. How would you know without a connection?
There is no universal correctness for SQL statements - though there are standards for how statements /SHOULD/ be composed and 
interpreted, but as experience on this list have taught us: No single SQL engine follows that to the letter - though I imagine you 
can get some software that might check universal SQL92 conformance (I've never seen any, but maybe James Lowden might know).


Either way, the SQL in SQLite will work mostly in SQLite but not necessarily elsewhere, and checking the validity of that would be 
insensible without a schema and file version - i.e. a connection to an actual file.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is sqlite case-sensitive?

[Wolfgang Enzinger]

>>> names), or without context (just validate syntax, e.g. that it can be
>>> parsed)?
>>>
>> I am asking about this API since I think I remember seeing it once, but
>> can't find it now
>>
> 
> i _think_ what you want is:
> 
> http://sqlite.org/c3ref/complete.html

I don't think so, because this function essentially checks "if [the
statement] ends with a semicolon token". Furthermore, "these routines do
not parse the SQL statements thus will not detect syntactically incorrect
SQL".

As Igor says, http://sqlite.org/c3ref/prepare.html would be appropriate.
However, a database connection is required for this.

Wolfgang

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is sqlite case-sensitive?

[Simon Slavin]

On 18 May 2014, at 3:32pm, Baruch Burstein  wrote:

> Sqlite is case-insensitive as far as table/column/db names. Is this
> documented as official behavior or it may change?

I would like to expand the scope of this question because I think an answer to 
just what Baruch asked may be over-specific.  According to the standards SQL is 
case insensitive for identifiers unless they're quoted.  So theoretically

CREATE TABLE mixedCaseTableName (id INTEGER)

SELECT * FROM mixedCaseTableName;
SELECT * FROM MIXEDCASETABLENAME

both succeed but

SELECT * FROM "mixedCaseTableName";
SELECT * FROM "MIXEDCASETABLENAME"

both fail.  Unfortunately

SELECT * FROM [mixedCaseTableName];
SELECT * FROM [MIXEDCASETABLENAME]

work differently in different implementations.  However my understanding is 
that technically [] should act the same as "".

But there's an exception.  If you quote the identifier when you create it you 
'fix' the case and you have to use that case whenever you use to it.  And in 
some cases you have to quote the identifier whenever you use it.  In other words

CREATE TABLE "quotedTableName" (id INTEGER);
SELECT * FROM quotedTableName

should fail if you accord directly to the standard.  However, most 
implementations will not make it fail.  Some ignore the quotes on the CREATE 
command, others ignore them when doing the SELECT.

All the above applies to all identifiers, I just used table names in my 
examples.

I hope now that someone familiar with SQLite specifically will post about this.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is sqlite case-sensitive?

[Igor Tandetnik]

On 5/18/2014 10:32 AM, Baruch Burstein wrote:

Sqlite is case-insensitive as far as table/column/db names. Is this
documented as official behavior or it may change?


It's highly unlikely to change, since that would break countless 
applications.



Also, is there a function in the API to validate a SQL statement, either in
the context of the current connection (validate also table/column/db
names), or without context (just validate syntax, e.g. that it can be
parsed)?


sqlite3_prepare (and its variations) for the former.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is sqlite case-sensitive?

[Stephan Beal]

On Sun, May 18, 2014 at 4:37 PM, Baruch Burstein wrote:

> > names), or without context (just validate syntax, e.g. that it can be
> > parsed)?
> >
> I am asking about this API since I think I remember seeing it once, but
> can't find it now
>

i _think_ what you want is:

http://sqlite.org/c3ref/complete.html


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is sqlite case-sensitive?

[Baruch Burstein]

On Sun, May 18, 2014 at 5:32 PM, Baruch Burstein wrote:

> Sqlite is case-insensitive as far as table/column/db names. Is this
> documented as official behavior or it may change?
>
> Also, is there a function in the API to validate a SQL statement, either
> in the context of the current connection (validate also table/column/db
> names), or without context (just validate syntax, e.g. that it can be
> parsed)?
>
I am asking about this API since I think I remember seeing it once, but
can't find it now


-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

RE: [sqlite] Is SQLite Case Sensitive?

[Lee Crain]

Dwight,

I had come to the same conclusion. 

The data has been manually typed, inconsistently, over the last 4 years. 

I've decided that during data importation, I'm going to force all
pertinent fields to lower case before they are written to the database.
That should solve the problem.

Thanks,

Lee Crain

__

-Original Message-
From: Dwight Ingersoll [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 08, 2007 5:11 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is SQLite Case Sensitive? 

--- Lee Crain <[EMAIL PROTECTED]> wrote:

> I am working on an application where I am importing
> data for which great care has NOT been taken to
> ensure uppercase and lowercase letters have been 
> entered appropriately.

Just a suggestion:  This sounds like it's a candidate
for some data scrubbing and cleanup rather than trying
to code for a lot of 'what if' scenarios, especially
since you indicate that the data is pretty freeform. 
It will probably save a lot of development time and
make future debugging easier if your data is in a
known consistent state rather than the currently
somewhat random state you implied.


   
__
__
Sick sense of humor? Visit Yahoo! TV's 
Comedy with an Edge to see what's on, when. 
http://tv.yahoo.com/collections/222

--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Is SQLite Case Sensitive?

[Dwight Ingersoll]

--- Lee Crain <[EMAIL PROTECTED]> wrote:

> I am working on an application where I am importing
> data for which great care has NOT been taken to
> ensure uppercase and lowercase letters have been 
> entered appropriately.

Just a suggestion:  This sounds like it's a candidate
for some data scrubbing and cleanup rather than trying
to code for a lot of 'what if' scenarios, especially
since you indicate that the data is pretty freeform. 
It will probably save a lot of development time and
make future debugging easier if your data is in a
known consistent state rather than the currently
somewhat random state you implied.


   

Sick sense of humor? Visit Yahoo! TV's 
Comedy with an Edge to see what's on, when. 
http://tv.yahoo.com/collections/222

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Is SQLite Case Sensitive?

[Samuel R. Neff]

Use of either "OR" or "Lower/Upper" will bypass any index and force a full
table scan.  Much better to use COLLATE NOCASE instead or a custom collation
if you need internationalized comparisons.

Sam


---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P Kishor
Sent: Tuesday, August 07, 2007 6:15 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is SQLite Case Sensitive?

SQL is not case-sensitive, but SQL comparisons are.

Use the following

SELECT * FROM table WHERE field1 = 'a' OR field1 = 'A'

you can also use

WHERE Lower(field1) = 'a'

or

WHERE Upper(field1) = 'A'



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Is SQLite Case Sensitive?

[Cory Nelson]

On 8/7/07, Lee Crain <[EMAIL PROTECTED]> wrote:
> I am working on an application where I am importing data for which great
> care has NOT been taken to ensure uppercase and lowercase letters have
> been entered appropriately.
>
>
> Would a search for an 'a' return a different result than a search for an
> 'A'?

Yes.  SQLite is case-sensitive.  If you are only using ASCII, you can
create an index using COLLATE NOCASE - otherwise you'll have to add
your own collation.

>
> SELECT * FROM table WHERE field1 = 'a';
>
> Vs.
>
> SELECT * FROM table WHERE field1 = 'A';
>
>
> If SQLite is case sensitive, is there an easy override for this to enforce
> all lowercase letters?
>
> Thanks,
>
> Lee Crain
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>


-- 
Cory Nelson
http://www.int64.org

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Is SQLite Case Sensitive?

[P Kishor]

SQL is not case-sensitive, but SQL comparisons are.

Use the following

SELECT * FROM table WHERE field1 = 'a' OR field1 = 'A'

you can also use

WHERE Lower(field1) = 'a'

or

WHERE Upper(field1) = 'A'



On 8/7/07, Lee Crain <[EMAIL PROTECTED]> wrote:
> I am working on an application where I am importing data for which great
> care has NOT been taken to ensure uppercase and lowercase letters have
> been entered appropriately.
>
>
> Would a search for an 'a' return a different result than a search for an
> 'A'?
>
>
> SELECT * FROM table WHERE field1 = 'a';
>
> Vs.
>
> SELECT * FROM table WHERE field1 = 'A';
>
>
> If SQLite is case sensitive, is there an easy override for this to enforce
> all lowercase letters?
>
> Thanks,
>
> Lee Crain
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
S Policy Fellow, National Academy of Sciences http://www.nas.edu/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-