Re: [sqlite] LIKE returns all rows

[Edward Macnaghten]

steve wrote:
Ah, that works just fine.
Is this noted someplace in the documentation that I missed?
If not:
- why does it work with single quotes and not double?  
- Shouldn't it be added?


Double quotes and single quotes have different meanings in SQLite (as 
defined in ANSI SQL too).

To produce string literals you MUST use single quotes.
Double quotes are used to specify case sensitive field/table/etc names, 
or variable names with spaces in them.  This is the same as Microsoft's 
SQLs [] characters, or MySQL backquote characters (neither are ANSI 
standard by the way).

Therefore "bob" means the field name bob, and 'bob' means the literal bob.
Eddy

RE: [sqlite] LIKE returns all rows

[steve]

Ah, that works just fine.
Is this noted someplace in the documentation that I missed?
If not:
- why does it work with single quotes and not double?  
- Shouldn't it be added?

This is all I found on the "Datatypes in SQLite Version 3" page:
"Values specified as literals as part of SQL statements are assigned storage
class TEXT if they are enclosed by single or double quotes, ..."

But it works, and that's what matters.
Thanks!
-> Steve

-Original Message-
From: Scott Leighton [mailto:[EMAIL PROTECTED] 
Sent: Saturday, April 23, 2005 7:28 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] LIKE returns all rows

On Saturday 23 April 2005 7:15 pm, steve wrote:
> Assume a database table named Good has a column named "bob".
> The following command will return ALL rows in the table regardless of 
> their
> content:
>
> SELECT * FROM Good WHERE bob LIKE "bob";
>
> Is this by design?  If so, is there a workaround for this other than 
> attempting to name all columns in a table to be so unique as to never 
> be "LIKEd"?
>

  Try  SELECT * FROM Good WHERE bob LIKE 'bob';

  Scott

--
POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/
Linux 2.6.11.4-20a-default x86_64

Re: [sqlite] LIKE returns all rows

[Scott Leighton]

On Saturday 23 April 2005 7:15 pm, steve wrote:
> Assume a database table named Good has a column named "bob".
> The following command will return ALL rows in the table regardless of their
> content:
>
> SELECT * FROM Good WHERE bob LIKE "bob";
>
> Is this by design?  If so, is there a workaround for this other than
> attempting to name all columns in a table to be so unique as to never be
> "LIKEd"?
>

 More to try

SELECT * from Good WHERE "bob" like 'bob';

SELECT * from Good WHERE 'bob' like 'bob';
  
SELECT * from Good WHERE 'bob' like "bob";

 See the pattern? 

   Double quotes are used for column names, single quotes
for values.

   Your SELECT * from Good WHERE bob like "bob";  is the
same as saying

SELECT * from Good WHERE 1 = 1;

  Scott



-- 
POPFile, the OpenSource EMail Classifier
http://popfile.sourceforge.net/
Linux 2.6.11.4-20a-default x86_64

Re: [sqlite] LIKE returns all rows

[Scott Leighton]

On Saturday 23 April 2005 7:15 pm, steve wrote:
> Assume a database table named Good has a column named "bob".
> The following command will return ALL rows in the table regardless of their
> content:
>
> SELECT * FROM Good WHERE bob LIKE "bob";
>
> Is this by design?  If so, is there a workaround for this other than
> attempting to name all columns in a table to be so unique as to never be
> "LIKEd"?
>

  Try  SELECT * FROM Good WHERE bob LIKE 'bob';

  Scott

-- 
POPFile, the OpenSource EMail Classifier
http://popfile.sourceforge.net/
Linux 2.6.11.4-20a-default x86_64

[sqlite] LIKE returns all rows

[steve]

Assume a database table named Good has a column named "bob".
The following command will return ALL rows in the table regardless of their
content:

SELECT * FROM Good WHERE bob LIKE "bob";

Is this by design?  If so, is there a workaround for this other than
attempting to name all columns in a table to be so unique as to never be
"LIKEd"?

-> Steve
PS. Sorry about the double post, I didn't know ctrl-enter sends an email!

[sqlite] LIKE returns all rows

[steve]

Assume a database table named Good has a column named "bob".
The following command will return ALL rows in the table regardless of their
content:

SELECT * FROM Good WHERE bob LIKE "bob";

Re: [sqlite] Performances problem with multi-table query ?

[Tom Poindexter]

On Sat, Apr 23, 2005 at 09:04:18AM -0400, D. Richard Hipp wrote:
> On Sat, 2005-04-23 at 14:25 +0200, Pierre D. wrote:
> > sqlite> SELECT p.name FROM packages p, files f WHERE f.pkgid=p.pkgid AND 
> > f.filename="/usr/bin/gcc";
> 
> Reverse the order of the tables in the FROM clause.  Like this:
> 
>SELECT p.name FROM files f, packages p WHERE ...


I'll add a plug for 'idxchk', my small program that reports index usage
on queries.  See the wiki page at:

http://www.sqlite.org/cvstrac/wiki?p=IdxChk

I have a small tutorial of rewriting SELECT statements to achieve better
performance.  The 'idxchk' program is available from:

http://sqlite.org/contrib


-- 
Tom Poindexter
[EMAIL PROTECTED]
http://www.nyx.net/~tpoindex/

Re: [sqlite] Performances problem with multi-table query ?

[D. Richard Hipp]

On Sat, 2005-04-23 at 14:25 +0200, Pierre D. wrote:
> I'm trying some "simple" query. The first query is "Whose file is it ?"
> Here is my first SQL query for that (ran with the sqlite3 command) :
> sqlite> SELECT p.name FROM packages p, files f WHERE f.pkgid=p.pkgid AND 
> f.filename="/usr/bin/gcc";

Reverse the order of the tables in the FROM clause.  Like this:

   SELECT p.name FROM files f, packages p WHERE ...

Then make sure you have indices on files.filename and
packages.pkgid.  If you do those two things, the query above
should run in O(logN) time.  Without those changes, the
execution time should be O(N^2).
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Performances problem with multi-table query ?

[Pierre D.]

Le Samedi 23 Avril 2005 14:50, Tobias Rundström a écrit :
> [EMAIL PROTECTED] wrote:
> > Le Samedi 23 Avril 2005 14:34, Tobias Rundström a écrit :
> >>select p.name from packages p join files f on f.pkgid = p.pkgid where
> >>f.filename="/usr/bin/gcc";
> >
> > It is as slow as the previous query :(
>
> Poor indexes? make sure that you have a index on pkgid in both tables
> and one filename.
After the creation of an index on the pkgid column of the table files, it 
works far faster : less than one second too :)
Thanks...

Re: [sqlite] Performances problem with multi-table query ?

[Tobias Rundström]

[EMAIL PROTECTED] wrote:
Le Samedi 23 Avril 2005 14:34, Tobias Rundström a écrit :
select p.name from packages p join files f on f.pkgid = p.pkgid where
f.filename="/usr/bin/gcc";
It is as slow as the previous query :(
Poor indexes? make sure that you have a index on pkgid in both tables 
and one filename.

-- Tobias

Re: [sqlite] Performances problem with multi-table query ?

[pinaraf]

Le Samedi 23 Avril 2005 14:34, Tobias Rundström a écrit :
> Pierre D. wrote:
> > Hi
> >
> > I'm currently developing a package manager (for linux) (yes I know, yet
> > another, useless...) and I'm using XML files for the database. But the
> > problem of that way is the slowdown and the memory cost of xml files +
> > XPath query
> > So I'm exploring other ways to store the database.
> > The first other way I want to try is sqlite, because it has perfect
> > bindings for python (my favorite scripting language) allowing the quick
> > creation (less than one hour, including tests, RTFM...) of a convertion
> > tool XML => sqlite After the convertion, I get a 8,6MB database, with a
> > table files containing about 14 records, a packages table with about
> > 440 records... The draft of the database is here (a picture showing the
> > relations between tables) : http://pinaraf.robertlan.eu.org/schemadb.png
> > (you'll notice some differences)
> > I'm trying some "simple" query. The first query is "Whose file is it ?"
> > Here is my first SQL query for that (ran with the sqlite3 command) :
> > sqlite> SELECT p.name FROM packages p, files f WHERE f.pkgid=p.pkgid AND
> > f.filename="/usr/bin/gcc";
> > gcc-core
>
> try a join instead.
>
> select p.name from packages p join files f on f.pkgid = p.pkgid where
> f.filename="/usr/bin/gcc";
It is as slow as the previous query :(

Re: [sqlite] Performances problem with multi-table query ?

[Tobias Rundström]

Pierre D. wrote:
Hi
I'm currently developing a package manager (for linux) (yes I know, yet 
another, useless...) and I'm using XML files for the database. But the 
problem of that way is the slowdown and the memory cost of xml files + XPath 
query
So I'm exploring other ways to store the database.
The first other way I want to try is sqlite, because it has perfect bindings 
for python (my favorite scripting language) allowing the quick creation (less 
than one hour, including tests, RTFM...) of a convertion tool XML => sqlite
After the convertion, I get a 8,6MB database, with a table files containing 
about 14 records, a packages table with about 440 records...
The draft of the database is here (a picture showing the relations between 
tables) : http://pinaraf.robertlan.eu.org/schemadb.png (you'll notice some 
differences)
I'm trying some "simple" query. The first query is "Whose file is it ?"
Here is my first SQL query for that (ran with the sqlite3 command) :
sqlite> SELECT p.name FROM packages p, files f WHERE f.pkgid=p.pkgid AND 
f.filename="/usr/bin/gcc";
gcc-core

try a join instead.
select p.name from packages p join files f on f.pkgid = p.pkgid where 
f.filename="/usr/bin/gcc";

-- Tobias

[sqlite] Performances problem with multi-table query ?

[Pierre D.]

Hi

I'm currently developing a package manager (for linux) (yes I know, yet 
another, useless...) and I'm using XML files for the database. But the 
problem of that way is the slowdown and the memory cost of xml files + XPath 
query
So I'm exploring other ways to store the database.
The first other way I want to try is sqlite, because it has perfect bindings 
for python (my favorite scripting language) allowing the quick creation (less 
than one hour, including tests, RTFM...) of a convertion tool XML => sqlite
After the convertion, I get a 8,6MB database, with a table files containing 
about 14 records, a packages table with about 440 records...
The draft of the database is here (a picture showing the relations between 
tables) : http://pinaraf.robertlan.eu.org/schemadb.png (you'll notice some 
differences)
I'm trying some "simple" query. The first query is "Whose file is it ?"
Here is my first SQL query for that (ran with the sqlite3 command) :
sqlite> SELECT p.name FROM packages p, files f WHERE f.pkgid=p.pkgid AND 
f.filename="/usr/bin/gcc";
gcc-core

But it took about 1 minute to give the answer, while the following query took 
less than one second :
sqlite> SELECT name FROM packages WHERE pkgid = (SELECT pkgid FROM files WHERE 
filename="/usr/bin/gcc");
gcc-core


Is it a known bug of sqlite ? Did I do something wrong ?
You can get the database here : 
http://pinaraf.robertlan.eu.org/database.db.bz2 (1,4MB compressed file)
I'm using sqlite 3.2.1, from ubuntu (breezy) packages...


Thanks for reading me...

Re: [sqlite] Where are the tables being saved?

[Witold Czarnecki]

Run:
sqlite -help
...and see the sqlite's command line syntax. You should give then db 
filename as an parameter. Otherwise DB will be created in the memory and not 
saved.

Best regards,
Witold
- Original Message - 
From: <[EMAIL PROTECTED]>
To: 
Sent: Saturday, April 23, 2005 12:15 PM
Subject: [sqlite] Where are the tables being saved?


Hello,
I'm using SQLite 2.8.15 on SUSE Pro 9.2.  The executable is in /usr/bin.
I've been having some problems using/learning sqlite.  I have the Newman 
book and
have been trying to find where the tables/databases are being saved.

Using the book, I create a couple of tables, and insert data into them. 
When I
exit the sqlite command line (I invoke it from terminal) and go back into 
later,
I can't reload the tables.

What am I doing wrong here?  I've looked up what I can in the book but I 
can't find
a place to direct sqlite to save the tables to a specific location.

Mark

[sqlite] Where are the tables being saved?

[phantom21]

Hello,

I'm using SQLite 2.8.15 on SUSE Pro 9.2.  The executable is in /usr/bin.

I've been having some problems using/learning sqlite.  I have the Newman book 
and
have been trying to find where the tables/databases are being saved.

Using the book, I create a couple of tables, and insert data into them.  When I 
exit the sqlite command line (I invoke it from terminal) and go back into later,
I can't reload the tables.

What am I doing wrong here?  I've looked up what I can in the book but I can't 
find
a place to direct sqlite to save the tables to a specific location.

Mark

Re: [sqlite] strftime and the %f option

[Will Leshner]

On 4/22/05, Kurt Welgehausen <[EMAIL PROTECTED]> wrote:

> Is that always true, or just when the date is 'now'?
> I suspect that 'now' is producing an integer.


Aha. I bet you are right. I wish I had thought to test that. Thanks.

[sqlite] strftime and the %f option

[Will Leshner]

It would appear that for a sqlite library built with CodeWarrior on
the Mac, the %f option to strftime only ever retuns '000' for the
milliseconds portion of the time. I'm wondering if this could be a
problem with CodeWarrior, or if that's just the way things are.

Re: [sqlite] strftime and the %f option

[Kurt Welgehausen]

> ... %f option to strftime ... retu[r]ns '000' ...

Is that always true, or just when the date is 'now'?
I suspect that 'now' is producing an integer.

sqlite> select strftime('%f', 'now');
strftime('%f', 'now')
-
52.000   
sqlite> select strftime('%f', 'now');
strftime('%f', 'now')
-
59.000   
sqlite> select strftime('%f', '10:11:22.33');
strftime('%f', '10:11:22.33')
-
22.329   


Regards

Re: [sqlite] timestamp how to ?

[Ken & Deb Allen]

Having used other databases extensively, and discovering that SQLITE 
does not have a native DATETIME data structure, I have elected to store 
the Date/Time value from the operating system (which is either a 32-bit 
or 64-bit value) directly into an INT field and then translate it into 
a string on retrieval.

I did some performance tests, and it seemed that converting a date/time 
string into the 32-bit or 64-bit value in my code and comparing integer 
values in a query were orders of magnitude faster than using the SQLITE 
functions for date/time comparisons on the fly. They are fine for 
formatting the data for display, but where I need to manipulate the 
data I still retrieve the 'raw' integer value and convert it as I 
require.

-ken
On 22-Apr-05, at 9:03 AM, [EMAIL PROTECTED] wrote:
"msaka msaka" <[EMAIL PROTECTED]> writes:
how can i use timestamp value in sqlite
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions