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] Very slow when query max(col) on empty set?

[Woody Wu]

On Friday, 9 May 2014, RSmith  wrote:

>
> On 2014/05/08 18:16, Woody Wu wrote:
>
>> On 2014年5月8日 GMT+08:00PM11:59:33, RSmith  wrote:
>>
>>> On 2014/05/08 14:13, Woody Wu wrote:
>>>
 Do you mean that my index for this case is already the best choice?

 And, as a general question, for a query in the form of
 select max(a) from table where a < InF and b=B and c=C.
 what's the best index in the case of (1) B is an existed one, or (2)

>>> B is not exists ?
>>>
>>> Imagine you have a table like this:
>>>
>>> A, B, C
>>> 1, 1, 1
>>> 2, 1, 2
>>> 3, 2, 1
>>> 4, 1, 3
>>> 5, 1, 1
>>> 6, 2, 1
>>> 7, 1, 2
>>>
>>> etc.
>>>
>>>
>> Thanks Smith, you explained quite clear in your way.  I understood that
>> things has to be that if sqlite could only work like that.
>>
>> What I was wondering is that why sqlite couldn't find out earlier that
>> b=8 c=1 denotes an empty set? If I run something like "select * from table
>> where b=8 and c=1", it could return very quickly. If so, the max(a) on an
>> empty set should alway be Null.  Am I thinking right?
>>
>>
>> -Woody Wu
>>
>
> It makes no sense if there isn't some index defined on b or c or both. If
> there is, then it is the same happy accident accident looked at in a
> different way. The reason that the query returns fast if you "Just" do
> WHERE b=8 AND c=1 is that there is either an automatic or explicit index on
> column b and/or c - but - as soon as you change the query to include
> grouping (i.e: max(a)) then that specific index is no longer the best one
> to use - and you cannot expect SQLite to first run some other arbitrary
> "test" query to see if it has no results, and only THEN try your whole
> query after it "knows" whether the other model query has actual results or
> not. Put another way, you CAN construct an Index that will be VERY fast for
> queries where there definitely is NO value in the list, consider again the
> table from earlier:
>
> A, B, C
> 1, 1, 1
> 2, 1, 2
> 3, 2, 1
> 4, 1, 3
> 5, 1, 1
> 6, 2, 1
> 7, 1, 2
>
> Now add Index (B, C, A) - a covering Index which first lists B and C, then
> A... now if you were to ask of it:
>
> SELECT max(a) from table where a
> It will start by scanning the new Index for values where b=8... and Not
> find any, so very quickly return with no results.
>
> but now let's say you change the query to:
>
> SELECT max(a) from table where a
> It starts (as before) scanning the table (assuming it uses the new index -
> this is not a given) and finds that the very first entry indeed b=1 and
> c=1.. so it checks a first check, max(a) requires we scan all other values to find the max of a,
> at least all values where b=1 and c=1.. of which there might be very very
> many meaning this query is a LOT slower now than NOT using that index (as
> you had it in your original post when you get very fast results).
>
> So the new Index is optimized to be quick when the b value DOESN'T exist,
> but slow when it DOES... and the original index is better to use if the b
> value DOES exist, but slow when it DOESN'T.  Again, there is no best index
> that will satisfy both those requirements. Simon's advice is still best re
> making Indices and then running Analyze to see which queries get used, and
> whether they have good times, then removing the bad ones.
>
> If there is a way you could tell before-hand whether a value exists or
> not, that would be better, and nothing stoppping you from actually running
> two queries every time, first check if it exists, then, if it does, run the
> second query that is faster (as the original). - Actually this might make
> your unique problem into the fastest possible results, but testing is
> needed to verify and it isn't a solution you can short-hand off to the
> Query planner... you need to do two queries.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

Simon,

After the explanation, I understand the problem so much better than before.
 I think I would to use the two queries solution.  Thanks a lot!



-- 
Life is the only flaw in an otherwise perfect nonexistence
-- Schopenhauer

narke
public key at http://subkeys.pgp.net:11371 (narkewo...@gmail.com)
___
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

[sqlite] Is sqlite case-sensitive?

[Baruch Burstein]

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)?

-- 
˙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