[sqlite] sql optimization question

[Jay Sprenkle]

I'm deleting a tree of data stored in sqlite and was looking for the
most efficient way to do it.
I thought the best solution was to delete the row and then delete all
the orphaned rows the referenced it in a loop

delete from category where id = 5;

To delete the orphaned rows I repeat one of the following until
nothing more is deleted.
Which of these two forms would run faster?

delete from category where ParentId not in ( select distinct id from category );

or

delete from category A
 left join category B on b.id = a.parent.id
 where b.id is null;

Thanks


-- 
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

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

Re: [sqlite] SQLITE_MAX_EXPR_DEPTH

[Jerry Krinock]

On 2008 Jan, 18, at 19:34, [EMAIL PROTECTED] wrote:


But instead of all that trouble, why not just say:

  id IN (1,2,3,4,5,...,N)


Well, I guess the reason I did not say that is because: I had not yet  
had my SQL lesson for the day.


All is wonderful now.  Thank you for this, and the explanations!



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

Re: [sqlite] SQLITE_MAX_EXPR_DEPTH

[drh]

Jerry Krinock <[EMAIL PROTECTED]> wrote:
> My query:
> 
> DELETE FROM `table1` WHERE (`id`=1 OR `id`=2 OR `id`=3 OR ... OR `id`=N)
> 
> using the C API.  When N exceeds 999, I get an error stating that the  
> maximum depth of 1000 has been exceeded, and this is documented in 
> http://www.sqlite.org/limits.html 
> , item 5.
> 
> Of course, I could fix this by doing multiple queries when N>999, but  
> my code is very nicely encapsulated as is, and that change would make  
> it all yucky.  So I'd like other alternatives.

The WHERE expression is parsed as follows:

   (...(((id=1 OR id=2) OR id=3) OR id=4) OR ...) OR id=N)

If you draw this as a tree, you find that, indeed, it is N
levels deep.  But by explicit use of parentheses, you can
force a balanced tree with a depth of only logN.

   (...((id=1 OR id=2) OR (id=3 OR id=4)) OR (...)...)

But instead of all that trouble, why not just say:

   id IN (1,2,3,4,5,...,N)

The latter is not only more efficient, but easier for human
readers to understand as well.

> 
> 1.  I would describe my query as "1000 clauses wide".  I'm not nesting  
> anything "1000 levels deep".  Is there a way to rewrite my query and  
> make it work?
> 
> 2.  Documentation implies that I can change the parameter  
> SQLITE_MAX_EXPR_DEPTH from the default of 1000.  But I can't find  
> SQLITE_MAX_EXPR_DEPTH in sqlite3.h.  Seems to be neither a compiler  
> macro nor a global.  (Mac OS X 10.5, sqlite 3.4.0).  Where is it?
> 

Look in sqliteLimit.h

--
D. Richard Hipp <[EMAIL PROTECTED]>


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

[sqlite] SQLITE_MAX_EXPR_DEPTH

[Jerry Krinock]

My query:

DELETE FROM `table1` WHERE (`id`=1 OR `id`=2 OR `id`=3 OR ... OR `id`=N)

using the C API.  When N exceeds 999, I get an error stating that the  
maximum depth of 1000 has been exceeded, and this is documented in http://www.sqlite.org/limits.html 
, item 5.


Of course, I could fix this by doing multiple queries when N>999, but  
my code is very nicely encapsulated as is, and that change would make  
it all yucky.  So I'd like other alternatives.


1.  I would describe my query as "1000 clauses wide".  I'm not nesting  
anything "1000 levels deep".  Is there a way to rewrite my query and  
make it work?


2.  Documentation implies that I can change the parameter  
SQLITE_MAX_EXPR_DEPTH from the default of 1000.  But I can't find  
SQLITE_MAX_EXPR_DEPTH in sqlite3.h.  Seems to be neither a compiler  
macro nor a global.  (Mac OS X 10.5, sqlite 3.4.0).  Where is it?


Thanks,

Jerry Krinock



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

Re: [sqlite] Quick question about multithread and SQLITE_BUSY/SQLITE_LOCKED in 3.5.4

[[EMAIL PROTECTED]]

OK I figured out SQLITE_THREADSAFE=0 for the second question...
And it seems the answer for the first question is yes, but if you know
a simpler way please share it with us, thanks!

-- sword

On Sat, 19 Jan 2008 09:57:10 +0900
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:

> Hello all,
> 
> I've read http://www.sqlite.org/lockingv3.html but am still not sure about
> multithread and locking in 3.5.4. 
> 
> I have a multithread application that has a single connection to a single
> SQLite3 database. Since it's multithreaded, SQL statements are thrown to
> a single SQLite3 object concurrently. I'm using
> http://www.sqlite.org/sqlite-amalgamation-3_5_4.zip
> on VC8 + WindowsXP.
> 
> Prior to this version (I was using SQLite2) I'd serialized all these database 
> access
> using critical sections and didn't care about SQLITE_BUSY or SQLITE_LOCKED
> since they never happen. It was very simple as I didn't need to implement 
> access
> retry for a busy case.
> 
> However, I learned that SQLite 3.5 does mutexing by default. So I removed
> all synchronization stuff in my SQLite access code, and now it seems
> it's not working as I intended. Unfortunately I can't reproduce it in my
> development environment and I've not yet implemented logging to see
> if it's due to SQLITE_BUSY or SQLITE_LOCKED. I saw it's entering
> sqlite3_mutex_enter multiple times in the debugger though, so it's thread-safe
> at least.
> 
> My question is,
> 
> 1. Do I still have to synchronize all SQLite access in my client code not to
> encounter SQLITE_BUSY or SQLITE_LOCKED? (Or is there any better way?)
> 
> 2. If so, how can I turn off all these mutexes (critical sections) in SQLite 
> 3.5.4?
> They are needless if I serialize all SQLite access in the client code.
> 
> Regards,
> 
> -- sword
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -



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

[sqlite] sqlite3_step() returning SQLITE_DONE for query that should return results

[Joe Cosby]

I have a query that should return results and sqlite3_step is, sometimes, 
returning SQLITE_DONE instead of SQLITE_ROW.

I have a table, meta_data, with two columns

id INTEGER primary key,  Soundid INTEGER

which I know at the time of execution has the rows

ID  SOUNDID 
38  39  
39  40  

I can do the query at the console and get the right results.

The code is fairly simple,

FileIsInMeta(int trackIndex)
{
bool debugMe = false;
if(trackIndex == 40 || trackIndex == 39)
{
debugMe = true;
}
   int rc;
   sqlite3_stmt *pStmt;
   const char *pzTail= "";
   int alreadyInMeta = 0;
   string selectSql = "SELECT id FROM meta_data WHERE soundid = 
"+itos(trackIndex)+"";
   rc = sqlite3_prepare_v2(db,selectSql.c_str(),-1, &pStmt,&pzTail);

   if(rc == SQLITE_OK)
   {
  while(sqlite3_step(pStmt) == SQLITE_ROW)
  {
 const char *result = (const char *)sqlite3_column_text(pStmt, 0);
 alreadyInMeta = atoi(result);

(debugMe is just somewhere I could put a break point for debugging, itos() 
converts an integer to a string)

When I pass in 39 it works correctly and returns 38 for id, but when I pass in 
40 sqlite3_step() returns SQLITE_DONE.  (The particular numbers aren't 
important, I just include the details to show how the same query is being used 
both times but I get different results.)

I'm really not sure what I'm doing wrong.  I know it would be easier to help me 
if I could provide complete code to generate the issue, but it is intermittant 
(as above, the same query providing different results) and I'm not sure how to 
reproduce it in a briefer way.  The above is part of a very large project.

Although I say it's intermittant, it's also predictable.

The application first does an operation that adds 322 records to the table 
meta_data, in addition to a table sounds in the same database.

Then in a subsequent operation, it adds several thousand records to meta_data 
and sounds.  The SQLITE_DONE always happens at the same point, where trackIndex 
== 40.  If I recreate the database and do the same operations in the same 
order, the problem always happens at the same point.  (It is not just the one 
record where I get the problem though, there are several dozen, this is just 
one example).

I know this isn't much to work with, but I am not sure where to look next.

Things I have tried so far

 - Originally this occurred during a large transaction BEGIN/COMMIT section, 
removed the transaction, no change
 - Using a bind statement instead of a literal string for the query
 - Calling sqlite3_reset() on statement before calling sqlite3_step()
 - Added SQLITE_THREADSAFE=1 (This is running in an MFC worker thread, although 
it's designed so that there is no simultaneous access to the database at the 
time it's running;  only the executing thread)
 - Explicitly setting the maximum statement length, instead of using -1
 - Checking to see if there was anything returned into pzTail (no)
 - There is an index on meta_data.soundid (the index is just soundid), tried 
removing that, same results

Most of that was just guesswork based on similar problems I saw searching the 
archives for this mailing list.

I tried tracing into the SQLite code, to be honest I didn't know what I was 
looking for and got lost.  Does anybody know a likely place to look in the 
source code for what is going wrong?  Is there a way I could dereference a 
sqlite3_stmt to see if anything looks wrong with it?  Or a way to check that 
the statement which it has is still correct or something like that?

Several thousand records are inserted into meta_data before it reaches this 
point in the code, which is why I thought it might have something to do with 
the transaction or the thread.  But all the inserts should definitely be 
happening from the same thread.  Note though, there is the main application 
thread, which creates the db connection, and the worker thread, which does the 
heavy work.  Neither of them are accessing the db at the same time, but they 
are separate threads.

My best guess is it has something or other to do with that, somehow the state 
of the table meta_data isn't what it should be at the time I do the query and 
it has something to do with having inserted such a large number of records at 
once.  But, definitely the worker thread which did the inserts is the same one 
doing the query that I am having problems with.

Anyway ... I know it's difficult to help me without me being able to provide a 
complete reproducible example, but as I say this is part of a large complicated 
application, and I'm not sure how I could do it without a huge amount of 
dependant code;  an XML parser, a routine that scans the user's hard drive for 
records to add, etc.

Any ideas anybody could provide as to where to look next, or questions that it 
would help if I answered, would be greatly appreciated.  Sorry if I'm missing 
something obvious.

-- 
Joe Cosby
http://joecosby.com/ 
They laughed at Einstein. They l

[sqlite] Quick question about multithread and SQLITE_BUSY/SQLITE_LOCKED in 3.5.4

[[EMAIL PROTECTED]]

Hello all,

I've read http://www.sqlite.org/lockingv3.html but am still not sure about
multithread and locking in 3.5.4. 

I have a multithread application that has a single connection to a single
SQLite3 database. Since it's multithreaded, SQL statements are thrown to
a single SQLite3 object concurrently. I'm using
http://www.sqlite.org/sqlite-amalgamation-3_5_4.zip
on VC8 + WindowsXP.

Prior to this version (I was using SQLite2) I'd serialized all these database 
access
using critical sections and didn't care about SQLITE_BUSY or SQLITE_LOCKED
since they never happen. It was very simple as I didn't need to implement access
retry for a busy case.

However, I learned that SQLite 3.5 does mutexing by default. So I removed
all synchronization stuff in my SQLite access code, and now it seems
it's not working as I intended. Unfortunately I can't reproduce it in my
development environment and I've not yet implemented logging to see
if it's due to SQLITE_BUSY or SQLITE_LOCKED. I saw it's entering
sqlite3_mutex_enter multiple times in the debugger though, so it's thread-safe
at least.

My question is,

1. Do I still have to synchronize all SQLite access in my client code not to
encounter SQLITE_BUSY or SQLITE_LOCKED? (Or is there any better way?)

2. If so, how can I turn off all these mutexes (critical sections) in SQLite 
3.5.4?
They are needless if I serialize all SQLite access in the client code.

Regards,

-- sword



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

RE: [sqlite] SQLite character comparisons

[Evans, Mark (Tandem)]

Doh!  Wrong thread.  :) :) :)

> -Original Message-
> From: Zbigniew Baniewski [mailto:[EMAIL PROTECTED]
> Sent: Friday, January 18, 2008 5:14 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] SQLite character comparisons
>
> On Fri, Jan 18, 2008 at 11:04:09PM +, Evans, Mark (Tandem) wrote:
>
> > Think of NULL as "value is unknown".
> >
> > With zero length blob/text, value is known:  0-length blob/text
>
> OK, I will :)
> --
> pozdrawiam / regards
>
> Zbigniew Baniewski
>
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
>
>

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

Re: [sqlite] SQLite character comparisons

[Zbigniew Baniewski]

On Fri, Jan 18, 2008 at 11:04:09PM +, Evans, Mark (Tandem) wrote:

> Think of NULL as "value is unknown".
> 
> With zero length blob/text, value is known:  0-length blob/text

OK, I will :)
-- 
pozdrawiam / regards

Zbigniew Baniewski

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

[sqlite] Re: Is there a difference between NULL und zero byte length BLOB/TEXT?

[Igor Tandetnik]

Lothar Scholz
 wrote:

Is there a difference between NULL und zero byte length BLOB/TEXT?


Yes. "fieldName IS NULL" test will return true for the former and false 
for the latter, for one thing.


Igor Tandetnik 



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

RE: [sqlite] SQLite character comparisons

[Evans, Mark (Tandem)]

Think of NULL as "value is unknown".

With zero length blob/text, value is known:  0-length blob/text

Cheers,
Mark

> -Original Message-
> From: Zbigniew Baniewski [mailto:[EMAIL PROTECTED]
> Sent: Friday, January 18, 2008 4:51 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] SQLite character comparisons
>
> On Fri, Jan 18, 2008 at 02:13:51PM -0800, Darren Duncan wrote:
>
> > Don't read too much into that statement; I'm was not raising the
> > auto-trim thing.
>
> Yes, yes - I know... "Roma locuta"...
> --
> pozdrawiam / regards
>
> Zbigniew Baniewski
>
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
>
>

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

RE: [sqlite] Is there a difference between NULL und zero byte length BLOB/TEXT?

[Robert Simpson]

> -Original Message-
> From: Lothar Scholz [mailto:[EMAIL PROTECTED] 
> Sent: Friday, January 18, 2008 3:50 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Is there a difference between NULL und zero 
> byte length BLOB/TEXT?
> 
> Hello,
> 
> The last question for today:
> Is there a difference between NULL und zero byte length BLOB/TEXT?
> 
> Need this information for my SQLite GUI Frontend.

In my mind, yes.  An equality test against two empty blobs will result in a
match.  A test of two NULL's will not -- nor will a test of a NULL vs. empty
blob.  So there is a difference between NULL and an empty blob.

Robert



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

Re: [sqlite] SQLite character comparisons

[Zbigniew Baniewski]

On Fri, Jan 18, 2008 at 02:13:51PM -0800, Darren Duncan wrote:

> Don't read too much into that statement; I'm was not raising the 
> auto-trim thing.

Yes, yes - I know... "Roma locuta"...
-- 
pozdrawiam / regards

Zbigniew Baniewski

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

[sqlite] Is there a difference between NULL und zero byte length BLOB/TEXT?

[Lothar Scholz]

Hello,

The last question for today:
Is there a difference between NULL und zero byte length BLOB/TEXT?

Need this information for my SQLite GUI Frontend.

-- 
Best regards,
 Lothar Scholz  mailto:[EMAIL PROTECTED]


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

Re: [sqlite] SQLite character comparisons

[Darren Duncan]

At 10:57 PM +0100 1/18/08, Zbigniew Baniewski wrote:

On Fri, Jan 18, 2008 at 12:32:36PM -0800, Darren Duncan wrote:
 > If trailing spaces were supposed to be insignificant for an equality test,

 then it should not be possible to define a string value containing
 trailing spaces at all.


Yes, yes: quite right... the above reminds me something... ;)


Don't read too much into that statement; I'm was not raising the 
auto-trim thing.  See my previous email in this thread, which replied 
to Jeff Fowler, which addresses his own comment on my statement. -- 
Darren Duncan


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

Re: [sqlite] SQLite character comparisons

[Zbigniew Baniewski]

On Fri, Jan 18, 2008 at 12:32:36PM -0800, Darren Duncan wrote:

> If trailing spaces were supposed to be insignificant for an equality test,
> then it should not be possible to define a string value containing
> trailing spaces at all.

Yes, yes: quite right... the above reminds me something... ;)
-- 
pozdrawiam / regards

Zbigniew Baniewski

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

RE: [sqlite] SQLite character comparisons

[Darren Duncan]

At 4:11 PM -0500 1/18/08, Fowler, Jeff wrote:

"Better" depends on who you ask - I'd say it's worse, and I bet most
DBA's would agree. The ANSI standard is to ignore trailing spaces when
comparing character strings in a WHERE clause, a HAVING clause, or a
join. So I can take the exact same data, run the exact same query, yet
get a different answer from SQLite vs. Oracle or SQL Server. In fact, we
found this issue because we DID get a different answer.


And every SQL implementation already has many differences from every 
other one, even with fundamentals, making them all generally 
incompatible and proprietary, and this example is right in line with 
the kinds of differences they have.  Other implementations have 
distinct var-char and fixed-char types, while SQLite only has the 
former.


Part of the problem here is that the ANSI standard is itself flawed.

(Maybe this particular case of trailing spaces is an exception, but 
many parts of ANSI SQL, AFAIK, are more focused on what the syntax of 
SQL is, and they say that the semantics, even of some fundamental 
operations, is left up to the implementation to decide for itself. 
So what good does it do you if SQL of the same syntax will compile on 
different DBMSs if it behaves differently in each one?  And the 
standard considers this valid.)


How many other programming langauges besides ANSI SQL treat trailing 
spaces as insignificant.



Regarding whether by extension it should be impossible to create strings
with trailing spaces; I side with the SQLite developers who say it isn't
the engine's job to trim blanks in data. Most other engines I've used do
not trim spaces either, even if the field is a varchar.


And rightly so, you should not trim spaces, because spaces are significant.

Herein lies a wider part of the problem.  The ANSI SQL is 
inconsistent in how it treats trailing spaces in strings.  On one 
hand it wants to preserve them, but on the other hand it wants to 
ignore them in its most fundamental operation other than preserving.


(With my "it should not be possible" sentence, I was not saying that 
spaces should not be trimmed in the fictional scenario where a 
character string does by definition not contain trailing spaces, but 
that code specifying them should produce an error rather than 
succeed.  The matter is analagous to what would happen if you write 
code that tries to treat the character string literal 'foo' as a 
number.)



But - whether ANSI compliance is considered "bloat" is not really my
place to comment upon.  I guess it's really is up to the SQLite team.


Yes it is up to the developers.  And they have already demonstrated 
willingness to do some things differently than ANSI SQL because they 
considered the differences to be improvements, or alternately 
reasonable feature cutting.



Purely from a business usability standpoint (not a programming one), I
would say there's no question that it's far more useful to do
comparisons the ANSI way.


And why is it more useful to ignore trailing spaces than respect 
them.  And if ignoring them is more useful, why do most programming 
languages (AFAIK) respect them?



 If for some reason I truly want to compare &
respect trailing spaces, I can still do that using a function such as
HEX(A) = HEX(B) or something better.


I would argue that it is rediculous to do such ugly things in order 
to do something that should be fundamental, and is simple and 
fundamental in any other language.  Better for basic '=' comparison 
to test that the values are the same, and have some other operator or 
function like 'equal_when_trimmed( v1, v2 )' when you want various 
exceptional comparisons.


-- Darren Duncan

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

[sqlite] Assertion for Virtual Table INSERT

[Evans, Mark (Tandem)]

Hi all,

SQLite version 3.5.3 - with custom virtual table module.

I am having a problem with assertion failure following processing of an INSERT 
statement relative to a virtual table.  Everything works through the call for 
VUpdate, then it asserts in vdbe.o in leg for Dup.  I'm wondering if this is a 
bug in SQLite vdbe code generation.  What is the purpose of Dup following 
VUpdate below?  I have duplicated the problem on two different architectures.


sqlite> explain insert into t1 values (0,'foo', 0);
0|Goto|0|16|
1|Null|0|0|
2|Null|0|0|
3|Integer|0|0|
4|String8|0|0|foo
5|Integer|0|0|
6|VUpdate|1|5|vtab:9E002B8:FD0A60
7|Dup|2|1|
8|NotNull|1|10|
9|Halt|19|2|T1.X may not be NULL
10|Dup|0|1|
11|NotNull|1|13|
12|Halt|19|2|T1.Z may not be NULL
13|MakeRecord|3|0|dad
14|Insert|0|3|T1
15|Halt|0|0|
16|Transaction|0|1|
17|VerifyCookie|0|2|
18|VBegin|0|0|vtab:9E002B8:FD0A60
19|Goto|0|1|
20|Noop|0|0|
sqlite> insert into t1 values (0,'foo', 0);
sqlite3: ../../../../Source/sqlite-3.5.3-hp/src/vdbe.c:893: sqlite3VdbeExec: 
Assertion `pFrom<=pTos && pFrom>=p->aStack' failed.
Abort (core dumped)
[EMAIL PROTECTED] MSE]$

Many thanks,
Mark

Re: [sqlite] Re: Re: Why are strings in hexadecimal notation always blobs?

[Nicolas Williams]

On Sat, Jan 19, 2008 at 03:23:32AM +0700, Lothar Scholz wrote:
> IT>Does this really make sense to you?
> 
> Yes the only reason left for a BLOB would be a containing zero byte and
> any illegal UTF8 sequence of bytes.

Or wanting to avoid collations that are aware of, say, Unicode
normalization, Unicode case transformations, ...

Really, a blob of bytes either is TEXT or not, and this distinction can
make a huge difference for some operations.

> For me it looks like the introduction of the current logic is just for
> backward compatibility that embedded 0 characters are not allowed in a
> string.

For me it looks like a form of string content encoding tagging, with a
one-bit content type tag: Unicode vs. arbitrary binary content types.
(SQLite also has a collation tag.)

Additional typing of octet strings could be really useful, or not.  I
don't know.  Perhaps some users have strings in many different codesets
and could use TEXT type variants that include codeset information.  But
such users can always add such tags as columns to existing tables, or
they can convert to UTF-8 (or UTF-16) and live with any lossiness.

> IMHO it would be cleaner if we conceptionally only have BLOBS
> and check for other datatypes on demand maybe with some caching.
> Exactly what TCL is doing it when it assumes everything is a string and
> (since version 7.X )we got the cached integer or double values.

That would only be true if the <, >, =, <=, >=, <=>, LIKE, GLOB and
other such operators behaved exactly the same for all strings regardless
of whether they were Unicode strings or blobs.  But they don't (or at
least, I don't want them to).

SQLite does not have a normalization-insensitive string comparison
operation/function today, but it might eventually, in which case even
the basic string equality/inequality comparison operation will behave
differently given TEXT inputs vs. BLOB inputs.

Please keep TEXT and BLOB as distinct types,

Nico
-- 

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

[sqlite] Slow query on one machine

[RB Smissaert]

Trying to figure out why (with one particular customer) some queries have
very different timings on one machine compared to another machine.
It has to do with updating one particular SQLite table with more recent data
from an Interbase table.

I give the database (S for SQLite and I for Interbase), SQL query and time
in seconds of slow machine and fast machine:

DB|Query|slow PC|fast PC
-
S|SELECT MAX(ENTRY_ID) FROM ENTRY_ATTRIBUTES|0.26|8.46
I|SELECT ENTRY_ID, ATTRIBUTE_TYPE, TYPE_SPECIFIC_INFO,
NUMERIC_VALUE FROM ENTRY_ATTRIBUTE WHERE ENTRY_ID > 15085882|0.05|0.07
S|INSERT OR IGNORE INTO ENTRY_ATTRIBUTES VALUES(?,?,?,?)|7.51|0.14
S|analyze ENTRY_ATTRIBUTES|431.96|0.03

All I can think of is that somehow there is something wrong with the SQLite
table ENTRY_ATTRIBUTES on the slow machine and I suggested dropping that
table and making a new one, but that made no difference. The application
that runs this is exactly the same on both machines. The slow machine is
actually slightly slower specification wise, but that can't explain the huge
differences in timings.

I think the only way for me to find is to get hold of that SQLite file, but
for now having some trouble getting hold of this file.
Any ideas?


RBS





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

RE: [sqlite] SQLite character comparisons

[Fowler, Jeff]

"Better" depends on who you ask - I'd say it's worse, and I bet most
DBA's would agree. The ANSI standard is to ignore trailing spaces when
comparing character strings in a WHERE clause, a HAVING clause, or a
join. So I can take the exact same data, run the exact same query, yet
get a different answer from SQLite vs. Oracle or SQL Server. In fact, we
found this issue because we DID get a different answer.

Regarding whether by extension it should be impossible to create strings
with trailing spaces; I side with the SQLite developers who say it isn't
the engine's job to trim blanks in data. Most other engines I've used do
not trim spaces either, even if the field is a varchar.

But - whether ANSI compliance is considered "bloat" is not really my
place to comment upon. I guess it's really is up to the SQLite team.
Purely from a business usability standpoint (not a programming one), I
would say there's no question that it's far more useful to do
comparisons the ANSI way. If for some reason I truly want to compare &
respect trailing spaces, I can still do that using a function such as
HEX(A) = HEX(B) or something better.

- Jeff Fowler

 

-Original Message-
From: Darren Duncan [mailto:[EMAIL PROTECTED] 
Sent: Friday, January 18, 2008 3:33 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite character comparisons

At 10:43 AM -0500 1/17/08, Fowler, Jeff wrote:
>Hello All,
>I've used SQL Server for over 15 years, Oracle off & on when I have no 
>choice, but SQLite for a couple weeks. I've just learned (today) that 
>SQLite respects trailing spaces when comparing two character fields.
>I.e. 'SQLITE' <> 'SQLITE '
>Is this behavior intentional? Neither SQL Server nor Oracle do this.
>Just curious as to why it works this way.

Because respecting the actual contents of the string is the better way
to do things.

The strings 'SQLITE' and 'SQLITE ' are not the same string.  Just as the
strings 'SQLITE' and 'sqlite' are not the same string.  A computer
language is more logical, predictable, and easy to use when a test for
equality or inequality actually treats every distinct value as distinct.
If trailing spaces were supposed to be insignificant for an equality
test, then it should not be possible to define a string value containing
trailing spaces at all.

Moreover, treating 'SQLITE' and 'SQLITE ' as not being the same string
also is consistent with the SQLite philosophy, because it means SQLite
has fewer exceptions to be concerned with in a simpler set of rules, and
also not having to check lengths and space pad before each compare also
makes the code simpler, and less buggy, and it saves CPU cycles.  A
value equality test is a very common and fundamental thing to do in a
DBMS, and bloating that will have a big performance impact.

-- Darren Duncan


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

Re: [sqlite] SQLite character comparisons

[Darren Duncan]

At 10:43 AM -0500 1/17/08, Fowler, Jeff wrote:

Hello All,
I've used SQL Server for over 15 years, Oracle off & on when I have no
choice, but SQLite for a couple weeks. I've just learned (today) that
SQLite respects trailing spaces when comparing two character fields.
I.e. 'SQLITE' <> 'SQLITE '
Is this behavior intentional? Neither SQL Server nor Oracle do this.
Just curious as to why it works this way.


Because respecting the actual contents of the string is the better 
way to do things.


The strings 'SQLITE' and 'SQLITE ' are not the same string.  Just as 
the strings 'SQLITE' and 'sqlite' are not the same string.  A 
computer language is more logical, predictable, and easy to use when 
a test for equality or inequality actually treats every distinct 
value as distinct.  If trailing spaces were supposed to be 
insignificant for an equality test, then it should not be possible to 
define a string value containing trailing spaces at all.


Moreover, treating 'SQLITE' and 'SQLITE ' as not being the same 
string also is consistent with the SQLite philosophy, because it 
means SQLite has fewer exceptions to be concerned with in a simpler 
set of rules, and also not having to check lengths and space pad 
before each compare also makes the code simpler, and less buggy, and 
it saves CPU cycles.  A value equality test is a very common and 
fundamental thing to do in a DBMS, and bloating that will have a big 
performance impact.


-- Darren Duncan

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

Re: [sqlite] Re: Re: Why are strings in hexadecimal notation always blobs?

[Lothar Scholz]

Hello Igor,

Saturday, January 19, 2008, 12:02:15 AM, you wrote:

IT> You misunderstand the dynamics of datatypes then.

Yes maybe. With the current implementation i really do not understand
the point anyway neither with my understanding nor with yours.

IT> Wait a minute. Didn't you just say that you _want_ text strings to be 
IT> able to contain control characters? So what's left for the BLOB then?

IT> Suppose I want to insert, say, a bitmap image into the database - as a
IT> BLOB, naturally. You are saying that, if it doesn't just happen to 
IT> contain at least one zero byte, it will have to go in as a string. So if
IT> it has a black pixel, it's a BLOB. If it doesn't have any black pixels,
IT> it's a string. Does this really make sense to you?

Yes the only reason left for a BLOB would be a containing zero byte and
any illegal UTF8 sequence of bytes.

For me it looks like the introduction of the current logic is just for
backward compatibility that embedded 0 characters are not allowed in a
string.

IMHO it would be cleaner if we conceptionally only have BLOBS
and check for other datatypes on demand maybe with some caching.
Exactly what TCL is doing it when it assumes everything is a string and
(since version 7.X )we got the cached integer or double values.

But well i can code around this like usual but i will bring up the
topic again if there is a discussion about a 4.0 release.


-- 
Best regards,
 Lothar Scholzmailto:[EMAIL PROTECTED]


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

Re: [sqlite] Variable substitution (TCL & SQLite)

[Zbigniew Baniewski]

On Fri, Jan 18, 2008 at 04:41:12PM +, Simon Davies wrote:

> Thus the results are from executing the SQL
> SELECT 'column1, column2, column3' FROM some_table;
> which I believe tallies with the results you see.

Thanks: it's probably the best picture, what is exactly going on there.
-- 
pozdrawiam / regards

Zbigniew Baniewski

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

Re: [sqlite] sqlite3 performace

[John Stanton]

John Stanton wrote:

Philip Nick wrote:
I tracked down the problem. I was linking an old version of sqlite3 
(3.0.8).
That was the old version we used. I apparently doesn't cache between 
calls.


My code now performs IO very similar to the CLI. Each subsequent query 
once

the db is open does not require re-reading the whole db.

My next task is to solve keeping the database open for longer. The basic
application is an rpc server. So its spins up a thread does some work 
sends

reply and closes the thread. Ideally we want to open the db when we start
the server and then close it when we shutdown. And then pass the 
connection

into each thread, so we don't have to keep opening the db. In the past we
had lots of issues doing this, hence the open for each query model.

Any advice would be appreciated.

Phil

On Jan 18, 2008 8:46 AM, Jay Sprenkle <[EMAIL PROTECTED]> wrote:


On Jan 17, 2008 3:48 PM, Philip Nick <[EMAIL PROTECTED]> wrote:

Greetings,

Currently I am using sqlite3 in a multi-process/multi-threaded server
setting.

opening a file is very slow. You need to move it out of your loop.


I have noticed my program basically reads the whole database every time

the

query is run. The IO Read bytes increases by 900k for every query we

run. We

also chew a good chunch of the cpu  I have tried keeping the database
connection open and open/closing for every query. But there was no

change.


if I launch the CLI and run the query it runs instantly and monitoring

the

IO read bytes is see only ~20 bytes of read to execute the query, when

my
code is using over 900k for every call. I have been looking into the 
CLI

source to see what is done differently, but was hoping someone on here

might

have some insight.

The operating system will cache files it reads in memory.
If your process reads the file and then you open the CLI
it will still be in memory from before and will not give you
comparable times.

If your process reads the whole database for every call you didn't
optimize
your sql. You need to create indexes to optimize your query

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite 



Cthulhu Bucks!
http://www.cthulhubucks.com


- 


To unsubscribe, send email to [EMAIL PROTECTED]

- 







We have an RPC server.  When it gets a request it looks at a pool of 
threads and selects the most recently opened on the basis that it is 
still in cache.  If the pool is empty it launches a thread and opens an 
Sqlite connection to it.  Each thread has an Sqlite context and an open 
Sqlite connection.


Dormant threads wait on an event and are signalled to start execution.

The performance is quite good with RPC's executing in about 40mS as 
recorded by the browser.  There is no churning from opening and closing 
connections and creatiing and destroying threads.


On shutdown the Sqlite connections are closed and the threads destroyed.

A note to add to this.  This is for pre 3.5.4. Sqlite which does not 
share connections.  It can be improved by using the latest sqlite and 
sharing a connection and cache.


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

Re: [sqlite] sqlite3 performace

[John Stanton]

Philip Nick wrote:

I tracked down the problem. I was linking an old version of sqlite3 (3.0.8).
That was the old version we used. I apparently doesn't cache between calls.

My code now performs IO very similar to the CLI. Each subsequent query once
the db is open does not require re-reading the whole db.

My next task is to solve keeping the database open for longer. The basic
application is an rpc server. So its spins up a thread does some work sends
reply and closes the thread. Ideally we want to open the db when we start
the server and then close it when we shutdown. And then pass the connection
into each thread, so we don't have to keep opening the db. In the past we
had lots of issues doing this, hence the open for each query model.

Any advice would be appreciated.

Phil

On Jan 18, 2008 8:46 AM, Jay Sprenkle <[EMAIL PROTECTED]> wrote:


On Jan 17, 2008 3:48 PM, Philip Nick <[EMAIL PROTECTED]> wrote:

Greetings,

Currently I am using sqlite3 in a multi-process/multi-threaded server
setting.

opening a file is very slow. You need to move it out of your loop.


I have noticed my program basically reads the whole database every time

the

query is run. The IO Read bytes increases by 900k for every query we

run. We

also chew a good chunch of the cpu  I have tried keeping the database
connection open and open/closing for every query. But there was no

change.


if I launch the CLI and run the query it runs instantly and monitoring

the

IO read bytes is see only ~20 bytes of read to execute the query, when

my

code is using over 900k for every call. I have been looking into the CLI
source to see what is done differently, but was hoping someone on here

might

have some insight.

The operating system will cache files it reads in memory.
If your process reads the file and then you open the CLI
it will still be in memory from before and will not give you
comparable times.

If your process reads the whole database for every call you didn't
optimize
your sql. You need to create indexes to optimize your query

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com


-
To unsubscribe, send email to [EMAIL PROTECTED]

-





We have an RPC server.  When it gets a request it looks at a pool of 
threads and selects the most recently opened on the basis that it is 
still in cache.  If the pool is empty it launches a thread and opens an 
Sqlite connection to it.  Each thread has an Sqlite context and an open 
Sqlite connection.


Dormant threads wait on an event and are signalled to start execution.

The performance is quite good with RPC's executing in about 40mS as 
recorded by the browser.  There is no churning from opening and closing 
connections and creatiing and destroying threads.


On shutdown the Sqlite connections are closed and the threads destroyed.

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

[sqlite] Re: Re: What is the precise definition of an identifier?

[Igor Tandetnik]

Lothar Scholz
 wrote:

Subject: Re: Re: What is the precise definition of an identifier?

Further, SQLite also interprets strings enclosed in square brackets
as identifiers (for compatibility with MS Access, I believe):


Okay thanks. Do [ and " literals have any escaping for example "" in
the same way the single quote string literal is doing it.


Double quotes are escaped similarly to single quotes:

create table "a""b" (x);
select * from [a"b];

I don't know of any way to escape a closing bracket in bracket-delimited 
identifier (which doesn't mean there's none). An opening bracket is not 
a problem - [a[b] works as an identifier.



And just to ask it before i miss something: The double single quotes
'' is the only escaped character in SQLite string literals. Correct?


Yes, as far as I know.

Igor Tandetnik 



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

Re: [sqlite] Re: What is the precise definition of an identifier?

[Lothar Scholz]

Hello Igor,

Saturday, January 19, 2008, 1:26:26 AM, you wrote:

IT> In addition to that, in SQL any string enclosed in double quotes is also
IT> an identifier. E.g. these are valid statements:

IT> Further, SQLite also interprets strings enclosed in square brackets as
IT> identifiers (for compatibility with MS Access, I believe):

Okay thanks. Do [ and " literals have any escaping for example "" in
the same way the single quote string literal is doing it.

And just to ask it before i miss something: The double single quotes
'' is the only escaped character in SQLite string literals. Correct?

-- 
Best regards,
 Lothar Scholzmailto:[EMAIL PROTECTED]


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

Re: [sqlite] No way to dump a database with large BLOB's ?

[drh]

Lothar Scholz <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> Dumping a database with images i run into the 1 million byte per
> SQL statement limit. I thought that the usual way to backup a database
> is the sqlite.exe and dump and eval method.
> 
> Shouldn't this limit be dynamic instead of hard wired into a compile
> constant?
> 

The bytes/statement limit is removed by default beginning with
check-in [4636] on 2007-12-17.

--
D. Richard Hipp <[EMAIL PROTECTED]>


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

[sqlite] No way to dump a database with large BLOB's ?

[Lothar Scholz]

Hello,

Dumping a database with images i run into the 1 million byte per
SQL statement limit. I thought that the usual way to backup a database
is the sqlite.exe and dump and eval method.

Shouldn't this limit be dynamic instead of hard wired into a compile
constant?

-- 
Best regards,
 Lothar Scholz  mailto:[EMAIL PROTECTED]


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

[sqlite] Re: What is the precise definition of an identifier?

[Igor Tandetnik]

Lothar Scholz
 wrote:

I have to write an sqlite syntax highligher for an editor
and at the moment i use the following token BNF syntax.

ident := '_' | letter ( letter | '_' | digit )*


I believe dollar sign is allowed in the middle (as the first character, 
it denotes a named parameter).


In addition to that, in SQL any string enclosed in double quotes is also 
an identifier. E.g. these are valid statements:


create table "Hi there!" (a int, b int);
select * from "Hi there!";

Further, SQLite also interprets strings enclosed in square brackets as 
identifiers (for compatibility with MS Access, I believe):


create table [Hi there!] (a int, b int);
-- quotes and brackets are interchangeable
select * from "Hi there!";

To make matters even more complicated, SQLite allows string literals to 
be enclosed in double quotes (this is an extension: standard SQL 
requires string literals to be enclosed in single quotes). When 
encountering a string in double quotes, SQLite tries to resolve it as an 
identifier. If it can't, then it treats it as a string literal. E.g.


create table X(a);

select "a", "b" from [X];
-- same as
select X.a, 'b' from X;

Igor Tandetnik 



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

Re: [sqlite] What is the precise definition of an identifier?

[drh]

Lothar Scholz <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> I have to write an sqlite syntax highligher for an editor
> and at the moment i use the following token BNF syntax.
> 
> ident := '_' | letter ( letter | '_' | digit )*
> 
> Is this correct?

This is correct depending on your definition of "letter"
and "digit".  SQLite understands letter to be any unicode
charater in the range 65-90, 97-122, or 128 and greater.
SQLite understands digit (in this context only) to be
unicode characters 48-57 and also the character 36.
See ticket number #1066 for information about the use
of character 36 as a digit.

--
D. Richard Hipp <[EMAIL PROTECTED]>


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

[sqlite] What is the precise definition of an identifier?

[Lothar Scholz]

Hello,

I have to write an sqlite syntax highligher for an editor
and at the moment i use the following token BNF syntax.

ident := '_' | letter ( letter | '_' | digit )*

Is this correct?

I would appreciate an additional section in the documentation
that defines the few missing low level productions.

-- 
Best regards,
 Lothar Scholz  mailto:[EMAIL PROTECTED]


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

[sqlite] Re: Re: Why are strings in hexadecimal notation always blobs?

[Igor Tandetnik]

Lothar Scholz
 wrote:

Friday, January 18, 2008, 8:09:02 PM, you wrote:

Lothar Scholz

wrote:

it seems that "Lothar" is stored as a TEXT value but when i store
X'4C6F74686172' it is a BLOB.
What is the reason for it?



Same reason 1 is an integer literal but '1' is a string literal.
X'4C6F74686172' is a blob literal.


Sorry as far as i understand the dynamics of datatypes they should
depend on the bytes that are passed but not on the literal that
is used for notation inside a textual SQL statement.


You misunderstand the dynamics of datatypes then.

How do you plan to determine, just looking at a sequence of bytes, 
whether it looks like a string or not? Especially given your claim below 
that you want to support control characters in a string. In your 
hypothetical implementation, how would I construct a BLOB literal that 
would _not_ be interpreted as a string, but inserted as a BLOB as I 
intended?



Another question, how would you realiable represent contrl characters
in the range 1-31 in a string? It is not really good to add them as
plain code in text files and SQLite does not have C like backslash
quoting. Especially the automatic %R%N->%N conversions might be a
huge
problem. And i don't think we should restrict the TEXT data type to
anything more then non zero bytes.


What do you mean, data content? How is it supposed to know that a
particular sequence of bytes is supposed to represent a string,
without
the help of mind-reading hardware? After all, you don't expect the
number 48 to be magically interpreted as a string '0'. You don't,
right?


Well if it looks like a number it is a number. If it does not look
like a number it is either a TEXT or if it contains zero (or maybe
non text
control characters others then usually defined \f \v \r \n) it is a
BLOB. This would make sense for me.


Wait a minute. Didn't you just say that you _want_ text strings to be 
able to contain control characters? So what's left for the BLOB then?


Suppose I want to insert, say, a bitmap image into the database - as a 
BLOB, naturally. You are saying that, if it doesn't just happen to 
contain at least one zero byte, it will have to go in as a string. So if 
it has a black pixel, it's a BLOB. If it doesn't have any black pixels, 
it's a string. Does this really make sense to you?


Igor Tandetnik 



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

Re: [sqlite] sqlite3 performace

[Ken]

Upgrading to 3.5.4 will be bennificial. You should be able to pass the 
connection around to the various threads with no problems.

Just make sure you compile (.configure --enable-threadsafe) and you should be 
good. No need to add your own mutex as its done internally by the sqlite code.

Definately keep the DB open at startup. Close it at shutdown.

The most important thing I know to do from a performance stand point is to use 
transactions and mostly I Use transactions of the form "BEGIN IMMEDIATE" So 
that a lock is taken immediately at the start of the transaction. This allows 
for a much simpler sqlite_busy/sqlite_locked error handling.

Regards,
Ken


Philip Nick <[EMAIL PROTECTED]> wrote: I tracked down the problem. I was 
linking an old version of sqlite3 (3.0.8).
That was the old version we used. I apparently doesn't cache between calls.

My code now performs IO very similar to the CLI. Each subsequent query once
the db is open does not require re-reading the whole db.

My next task is to solve keeping the database open for longer. The basic
application is an rpc server. So its spins up a thread does some work sends
reply and closes the thread. Ideally we want to open the db when we start
the server and then close it when we shutdown. And then pass the connection
into each thread, so we don't have to keep opening the db. In the past we
had lots of issues doing this, hence the open for each query model.

Any advice would be appreciated.

Phil

On Jan 18, 2008 8:46 AM, Jay Sprenkle  wrote:

> On Jan 17, 2008 3:48 PM, Philip Nick 
 wrote:
> > Greetings,
> >
> > Currently I am using sqlite3 in a multi-process/multi-threaded server
> > setting.
>
> opening a file is very slow. You need to move it out of your loop.
>
> > I have noticed my program basically reads the whole database every time
> the
> > query is run. The IO Read bytes increases by 900k for every query we
> run. We
> > also chew a good chunch of the cpu  I have tried keeping the database
> > connection open and open/closing for every query. But there was no
> change.
>
> > if I launch the CLI and run the query it runs instantly and monitoring
> the
> > IO read bytes is see only ~20 bytes of read to execute the query, when
> my
> > code is using over 900k for every call. I have been looking into the CLI
> > source to see what is done differently, but was hoping someone on here
> might
> > have some insight.
>
> The operating system will cache files it reads in memory.
> If your process reads the file and then you open the CLI
> it will still be in memory from before and will not give you
> comparable times.
>
> If your process reads the whole database for every call you didn't
> optimize
> your sql. You need to create indexes to optimize your query
>
> --
> The PixAddixImage Collector suite:
> http://groups-beta.google.com/group/pixaddix
>
> SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
> http://www.reddawn.net/~jsprenkl/Sqlite
>
> Cthulhu Bucks!
> http://www.cthulhubucks.com
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>


-- 
Philip Nick
E-Mail: [EMAIL PROTECTED]
Cell: 352-262-9067
Web: http://www.ruffasdagut.com

Re: [sqlite] Variable substitution (TCL & SQLite)

[Simon Davies]

On 18/01/2008, Zbigniew Baniewski <[EMAIL PROTECTED]> wrote:
> ...and now the contents of $columns (SQL variable) in the statement above,
> has been replaced with the contents of $columns (TCL variable) - because the
> variable names are "compatible". So - that was my assumption - we've got now:
>
> { SELECT column1, column2, column3 FROM some_table }
>
> ...which seems to be quite legal SQL statement.
>
>
> It seems, there's something I'm still missing(?).

Hi Zbigniew,

Parameter binding is not the same as substitution into a raw sql string.

SQLite prepares an SQL string akin to "SELECT ? FROM some_table;",
then satisfies the place holder by having the text "column1, column2,
column3" ($columns ) bound to the prepared statement. Thus the results
are from executing the SQL
SELECT 'column1, column2, column3' FROM some_table;
which I believe tallies with the results you see.

Rgds,
Simon

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

Re: [sqlite] Date arithmetic question

[John Elrick]

[EMAIL PROTECTED] wrote:

"Virgilio Fornazin" <[EMAIL PROTECTED]> wrote:
  

DATEDIFF should compute the difference by arithmetic subtracting M/Y in
month case, if I'm not wrong

ex:

DateDiff (month, 1-1-2007, 3-30-2007) will return 2

Its that right ?



So datediff('month', '2008-02-01 23:59:59','2008-01-31 00:00:00') should
return 1 even though the difference is really only 1 second?  Seems
goofy to me
  


Weighing in for a second...

Given the context of "month", I suppose it is no more goofy than saying 
"see you next year" to someone one second before midnight on New Years 
Eve.  I'd guess that's why most debt instruments define what they mean 
by "month" as a specific, if arbitrary, day in the month.



John

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

Re: [sqlite] Date arithmetic question

[Rob Sciuk]

On Thu, 17 Jan 2008, [EMAIL PROTECTED] wrote:


"Virgilio Fornazin" <[EMAIL PROTECTED]> wrote:

DATEDIFF should compute the difference by arithmetic subtracting M/Y in
month case, if I'm not wrong

ex:

DateDiff (month, 1-1-2007, 3-30-2007) will return 2

Its that right ?


So datediff('month', '2008-02-01 23:59:59','2008-01-31 00:00:00') should
return 1 even though the difference is really only 1 second?  Seems
goofy to me

--
D. Richard Hipp <[EMAIL PROTECTED]>


The timestamp is a very useful thing, but IMHO, dates are dates, and times 
are times.  Date arithmentic is very easy given a true Julian date, 
calculated as the number of *DAYS* since an "epoch" (Oct 15, 1583?), and 
times can be the number of seconds, tenths of seconds, milliseconds or 
whatever in a day, but stored as separate variables.


There is much subjective processing in time differences when you start 
adding +- n months, and much of it can be avoided by not using the system
type timestamps to store dates, and keeping a date variable and time 
variable (both integers).


I wrote a Tcl binding for a Julian date and time library I've used for 
years, and made it available, both as C library (BSD/Linux/Windows), and
Tcl Extension (BSD/Windows).  There is a unix style man page for 
documentation.


http://www.controlq.com/OpenSource/Tcl_Julian.tgz

HTH,
Rob Sciuk



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

RE: [sqlite] Date arithmetic question

[Tom Briggs]

   Writing, adding and using your own functions within SQLite is pretty
easy.  That's probably your best bet to solve this problem.

   -T

> -Original Message-
> From: Fowler, Jeff [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, January 17, 2008 11:59 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Date arithmetic question
> 
> Guys,
>  
> I guess I'm the newest SQLite person on this email list and I 
> know I'm definitely the dumbest. It seems like a lot of you 
> are trying to justify why two dates that are one minute apart 
> can have a function say they're one month apart. Don't look 
> at it that way. Back when mainframes and dinosaurs ruled the 
> world I used to be a fairly decent programmer, now I run a 
> small company, so today I'm coming from a business standpoint 
> not a programmer one. 
>  
> To give some background, we're in the process of embedding 
> SQLite into our application, and yes it's an awesome product. 
> Our software sits on top of huge data warehouses (hundreds of 
> millions of rows) which are typically either Oracle or SQL 
> Server. We run queries against these databases and store the 
> result sets in SQLite, where we run subsequent queries to 
> filter & format the output.
>  
> A huge number of businesses are date driven. Publishers want 
> to renew subscriptions before they expire. Insurance 
> companies need to renew policies. Our largest client sells 
> service contracts which have a start & end  date. So it's an 
> everyday occurrence for a manager to want to know how many 
> customers will expire within the next three months, or what 
> an average contract length is in months.
>  
> My request was for a new date function that returns the 
> difference, or "calendar interval" if you prefer, between two 
> dates. Without such a function we must say:
> WHERE (strftime('%Y', LaborEndDate)*12+strftime('%m', 
> LaborEndDate)) - (strftime('%Y', 
> LaborStartDate)*12+strftime('%m', LaborStartDate)) < 3
>  
> Wow. This is quite a mouthfull for something that's so 
> commonly needed, and it's harder to generate SQL 
> automatically when a user clicks a checkbox. Clearly it's far 
> simpler (and easier to program) if we could say:
> WHERE datediff('month', LaborEndDate, LaborStartDate) < 3
>  
> Datediff also supports years, weeks, days, hours, seconds, 
> and milliseconds. It's just a quick & easy way to make life 
> easier. But.. of course I know the SQLite team can't slap in 
> every enhancement that somebody suggests; so I just wanted to 
> explain why this would be useful for some of us. We do have a 
> vested interest in the product!
>  
> - Jeff
> 
> 
> 
> 
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Thu 1/17/2008 9:21 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Date arithmetic question
> 
> 
> 
> Gerry Snyder <[EMAIL PROTECTED]> wrote:
> > [EMAIL PROTECTED] wrote:
> > >
> > > So datediff('month', '2008-02-01 23:59:59','2008-01-31 
> 00:00:00') should
> > > return 1 even though the difference is really only 1 
> second?  Seems
> > > goofy to me
> > >
> > >  
> >
> > I have been staring at this until I'm getting goofy.
> >
> > Written as it is, isn't the time interval 1 second short of 
> two days?
> >
> > If you want an interval of 1 second shouldn't it be
> >
> > datediff('month', '2008-02-01 00:00:00','2008-01-31 23:59:59')
> >
> > ?
> >
> >
> > Gerry, more confused than usual (as usual)
> >
> 
> Yeah.  I got it backwards.  Sorry.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 
> 
> 
> 
> 

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

Re: [sqlite] Why are strings in hexadecimal notation always blobs?

[drh]

Lothar Scholz <[EMAIL PROTECTED]> wrote:
> 
> Another question, how would you realiable represent contrl characters
> in the range 1-31 in a string? It is not really good to add them as
> plain code in text files and SQLite does not have C like backslash
> quoting. Especially the automatic %R%N->%N conversions might be a huge
> problem. And i don't think we should restrict the TEXT data type to
> anything more then non zero bytes.
> 

  CAST(x'0102030405' AS text)

--
D. Richard Hipp <[EMAIL PROTECTED]>


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

Re: [sqlite] Re: Why are strings in hexadecimal notation always blobs?

[Lothar Scholz]

Hello Igor,

Friday, January 18, 2008, 8:09:02 PM, you wrote:

IT> Lothar Scholz
IT>  wrote:
>> it seems that "Lothar" is stored as a TEXT value but when i store
>> X'4C6F74686172' it is a BLOB.
>> What is the reason for it?

IT> Same reason 1 is an integer literal but '1' is a string literal. 
IT> X'4C6F74686172' is a blob literal.

Sorry as far as i understand the dynamics of datatypes they should
depend on the bytes that are passed but not on the literal that
is used for notation inside a textual SQL statement.

Another question, how would you realiable represent contrl characters
in the range 1-31 in a string? It is not really good to add them as
plain code in text files and SQLite does not have C like backslash
quoting. Especially the automatic %R%N->%N conversions might be a huge
problem. And i don't think we should restrict the TEXT data type to
anything more then non zero bytes.

IT> What do you mean, data content? How is it supposed to know that a 
IT> particular sequence of bytes is supposed to represent a string, without
IT> the help of mind-reading hardware? After all, you don't expect the 
IT> number 48 to be magically interpreted as a string '0'. You don't, right?

Well if it looks like a number it is a number. If it does not look
like a number it is either a TEXT or if it contains zero (or maybe non text
control characters others then usually defined \f \v \r \n) it is a
BLOB. This would make sense for me.

IT> Use parameterized queries and approprite sqlite3_bind_* calls. This way
IT> you unambiguously control the type of the value.

I have to use for portability reasons UTF-8 sql text files. So i can't
use a programming langauge API.

-- 
Best regards,
 Lothar Scholzmailto:[EMAIL PROTECTED]


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

Re: [sqlite] Variable substitution (TCL & SQLite)

[Zbigniew Baniewski]

On Fri, Jan 18, 2008 at 02:54:40PM +0100, Michael Schlenker wrote:

> Not really true.
> If the part is wrapped in {} then for Tcl the $column is just an ordinary 
> string with no other meaning than foobar, and NO substitution takes place 
> before the string is passed to SQLite.

Yes, as I wrote already: I agree.

> SQLite then interprets the string again, like some Tcl commands do 
> themselfes and defines the semantics for its argument as:
> 
> $name is an application variable if it appears in a place where an 
> application variable is valid.
> [..]
> Your usage fails, because the select list is no valid place to use 
> application variables, so SQLite does expand it there.

How is the definition of the "valid place"?
-- 
pozdrawiam / regards

Zbigniew Baniewski

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

Re: [sqlite] Variable substitution (TCL & SQLite)

[Michael Schlenker]

Zbigniew Baniewski schrieb:

On Fri, Jan 18, 2008 at 02:54:40PM +0100, Michael Schlenker wrote:


$name is an application variable if it appears in a place where an 
application variable is valid.

[..]
Your usage fails, because the select list is no valid place to use 
application variables, so SQLite does expand it there.


How is the definition of the "valid place"?


See the link i provided in my last message, it explains it. Probably drh can 
provide more details, or just look at the BNF used by the lemon parser, it 
should contain all the gory details.


Michael


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

Re: [sqlite] sqlite3 performace

[Philip Nick]

I tracked down the problem. I was linking an old version of sqlite3 (3.0.8).
That was the old version we used. I apparently doesn't cache between calls.

My code now performs IO very similar to the CLI. Each subsequent query once
the db is open does not require re-reading the whole db.

My next task is to solve keeping the database open for longer. The basic
application is an rpc server. So its spins up a thread does some work sends
reply and closes the thread. Ideally we want to open the db when we start
the server and then close it when we shutdown. And then pass the connection
into each thread, so we don't have to keep opening the db. In the past we
had lots of issues doing this, hence the open for each query model.

Any advice would be appreciated.

Phil

On Jan 18, 2008 8:46 AM, Jay Sprenkle <[EMAIL PROTECTED]> wrote:

> On Jan 17, 2008 3:48 PM, Philip Nick <[EMAIL PROTECTED]> wrote:
> > Greetings,
> >
> > Currently I am using sqlite3 in a multi-process/multi-threaded server
> > setting.
>
> opening a file is very slow. You need to move it out of your loop.
>
> > I have noticed my program basically reads the whole database every time
> the
> > query is run. The IO Read bytes increases by 900k for every query we
> run. We
> > also chew a good chunch of the cpu  I have tried keeping the database
> > connection open and open/closing for every query. But there was no
> change.
>
> > if I launch the CLI and run the query it runs instantly and monitoring
> the
> > IO read bytes is see only ~20 bytes of read to execute the query, when
> my
> > code is using over 900k for every call. I have been looking into the CLI
> > source to see what is done differently, but was hoping someone on here
> might
> > have some insight.
>
> The operating system will cache files it reads in memory.
> If your process reads the file and then you open the CLI
> it will still be in memory from before and will not give you
> comparable times.
>
> If your process reads the whole database for every call you didn't
> optimize
> your sql. You need to create indexes to optimize your query
>
> --
> The PixAddixImage Collector suite:
> http://groups-beta.google.com/group/pixaddix
>
> SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
> http://www.reddawn.net/~jsprenkl/Sqlite
>
> Cthulhu Bucks!
> http://www.cthulhubucks.com
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>


-- 
Philip Nick
E-Mail: [EMAIL PROTECTED]
Cell: 352-262-9067
Web: http://www.ruffasdagut.com

Re: [sqlite] Variable substitution (TCL & SQLite)

[Michael Schlenker]

Zbigniew Baniewski schrieb:

On Thu, Jan 17, 2008 at 11:13:59PM -0500, D. Richard Hipp wrote:


The rules of TCL parsing are that text within {...} gets passed into
its command exactly as written with the outermost {...} removed. [..]
In other words, the $columns was *not* expanded by TCL.  It got
passed down into SQLite.


Yes, I agree. But the following is not quite clear to me:


SQLite sees the $columns and thinks
you are dealing with an application variable.  Just like a "?" or a
":abc" or "@xyz".  Sqlite3_prepare() runs and treats the $columns
token as it would any other SQL variable.


So, TCL sees a variable "columns", whose contents ($columns) - is 
"column1, column2, column3".


SQLite sees statement { SELECT $columns FROM some_table }, where $columns is
just "a token", which _can be_ an application variable, if TCL confirms
this.


Not really true.
If the part is wrapped in {} then for Tcl the $column is just an ordinary 
string with no other meaning than foobar, and NO substitution takes place 
before the string is passed to SQLite.
SQLite then interprets the string again, like some Tcl commands do 
themselfes and defines the semantics for its argument as:


$name is an application variable if it appears in a place where an 
application variable is valid.


This is then prepared as a statement and then values are bound by asking the 
surrounding Tcl stackframe for the values of the Variables used.


Your usage fails, because the select list is no valid place to use 
application variables, so SQLite does expand it there.


http://sqlite.org/c3ref/bind_blob.html has the details for the C side, only 
literals can be replaced with application variables, and column names in the 
select list are not really literals.


Michael

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

Re: [sqlite] sqlite3 performace

[Jay Sprenkle]

On Jan 17, 2008 3:48 PM, Philip Nick <[EMAIL PROTECTED]> wrote:
> Greetings,
>
> Currently I am using sqlite3 in a multi-process/multi-threaded server
> setting.

opening a file is very slow. You need to move it out of your loop.

> I have noticed my program basically reads the whole database every time the
> query is run. The IO Read bytes increases by 900k for every query we run. We
> also chew a good chunch of the cpu  I have tried keeping the database
> connection open and open/closing for every query. But there was no change.

> if I launch the CLI and run the query it runs instantly and monitoring the
> IO read bytes is see only ~20 bytes of read to execute the query, when my
> code is using over 900k for every call. I have been looking into the CLI
> source to see what is done differently, but was hoping someone on here might
> have some insight.

The operating system will cache files it reads in memory.
If your process reads the file and then you open the CLI
it will still be in memory from before and will not give you
comparable times.

If your process reads the whole database for every call you didn't optimize
your sql. You need to create indexes to optimize your query

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

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

Re: [sqlite] Variable substitution (TCL & SQLite)

[Zbigniew Baniewski]

On Thu, Jan 17, 2008 at 11:13:59PM -0500, D. Richard Hipp wrote:

> The rules of TCL parsing are that text within {...} gets passed into
> its command exactly as written with the outermost {...} removed. [..]
> In other words, the $columns was *not* expanded by TCL.  It got
> passed down into SQLite.

Yes, I agree. But the following is not quite clear to me:

> SQLite sees the $columns and thinks
> you are dealing with an application variable.  Just like a "?" or a
> ":abc" or "@xyz".  Sqlite3_prepare() runs and treats the $columns
> token as it would any other SQL variable.

So, TCL sees a variable "columns", whose contents ($columns) - is 
"column1, column2, column3".

SQLite sees statement { SELECT $columns FROM some_table }, where $columns is
just "a token", which _can be_ an application variable, if TCL confirms
this.

> After the statement is prepared.  TCL asks the statement: "What
> application variables do you have, and what are their names?"
> The statement tells TCL that it has a variable named "$columns".
> TCL says "I have a variable by that name", and so then TCL
> then calls sqlite3_bind_text() to stick the value of the $columns
> TCL variable into the SQLite variable.  TCL then calls sqlite3_step()
> to run the statement.

...and now the contents of $columns (SQL variable) in the statement above,
has been replaced with the contents of $columns (TCL variable) - because the
variable names are "compatible". So - that was my assumption - we've got now:

{ SELECT column1, column2, column3 FROM some_table }

...which seems to be quite legal SQL statement.


It seems, there's something I'm still missing(?).
-- 
pozdrawiam / regards

Zbigniew Baniewski

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

[sqlite] Re: Why are strings in hexadecimal notation always blobs?

[Igor Tandetnik]

Lothar Scholz
 wrote:

it seems that "Lothar" is stored as a TEXT value but when i store
X'4C6F74686172' it is a BLOB.
What is the reason for it?


Same reason 1 is an integer literal but '1' is a string literal. 
X'4C6F74686172' is a blob literal.



The dynamic typing should look at the
data content and not the literal form. So i would consider this a bug.


What do you mean, data content? How is it supposed to know that a 
particular sequence of bytes is supposed to represent a string, without 
the help of mind-reading hardware? After all, you don't expect the 
number 48 to be magically interpreted as a string '0'. You don't, right?



For convenience i tried to always use hex quotes no matter what data
is inside.


Use parameterized queries and approprite sqlite3_bind_* calls. This way 
you unambiguously control the type of the value.


Igor Tandetnik 



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

RE: [sqlite] Date arithmetic question

[Doug]

Perhaps some enterprising soul could write the datediff function and put it
in the wiki for everyone to use.  In fact a separate area just for
user-written functions might be quite helpful.

For what it's worth, I have one that truncates a time (stored in time_t
format) down to the start of an hour:

void GetStartOfHourUTF16(sqlite3_context* context, int numArgs,
sqlite3_value** args)
{
_ASSERT(1 == numArgs);
__int64 timeVal = 0;

switch( sqlite3_value_type(args[0]) )
{
default:
case SQLITE_NULL: 
case SQLITE_BLOB: _ASSERT(0); break;
case SQLITE_FLOAT: timeVal = (__int64)sqlite3_value_double(args[0]);
break;
case SQLITE_TEXT: timeVal =
_wtoi64((wchar_t*)sqlite3_value_text16(args[0])); break;
case SQLITE_INTEGER: timeVal = sqlite3_value_int64(args[0]); break;
}
timeVal /= 3600; //take it down to hours
timeVal *= 3600; //have now stripped of minutes and seconds
sqlite3_result_int64(context, timeVal);
}


> -Original Message-
> From: Fowler, Jeff [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 17, 2008 10:59 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Date arithmetic question
> 
> Guys,
> 
> I guess I'm the newest SQLite person on this email list and I know I'm
definitely the
> dumbest. It seems like a lot of you are trying to justify why two dates
that are one
> minute apart can have a function say they're one month apart. Don't look
at it that
> way. Back when mainframes and dinosaurs ruled the world I used to be a
fairly
> decent programmer, now I run a small company, so today I'm coming from a
> business standpoint not a programmer one.
> 
> To give some background, we're in the process of embedding SQLite into our
> application, and yes it's an awesome product. Our software sits on top of
huge data
> warehouses (hundreds of millions of rows) which are typically either
Oracle or SQL
> Server. We run queries against these databases and store the result sets
in SQLite,
> where we run subsequent queries to filter & format the output.
> 
> A huge number of businesses are date driven. Publishers want to renew
> subscriptions before they expire. Insurance companies need to renew
policies. Our
> largest client sells service contracts which have a start & end  date. So
it's an
> everyday occurrence for a manager to want to know how many customers will
> expire within the next three months, or what an average contract length is
in
> months.
> 
> My request was for a new date function that returns the difference, or
"calendar
> interval" if you prefer, between two dates. Without such a function we
must say:
> WHERE (strftime('%Y', LaborEndDate)*12+strftime('%m', LaborEndDate)) -
> (strftime('%Y', LaborStartDate)*12+strftime('%m', LaborStartDate)) < 3
> 
> Wow. This is quite a mouthfull for something that's so commonly needed,
and it's
> harder to generate SQL automatically when a user clicks a checkbox.
Clearly it's far
> simpler (and easier to program) if we could say:
> WHERE datediff('month', LaborEndDate, LaborStartDate) < 3
> 
> Datediff also supports years, weeks, days, hours, seconds, and
milliseconds. It's just
> a quick & easy way to make life easier. But.. of course I know the SQLite
team can't
> slap in every enhancement that somebody suggests; so I just wanted to
explain
> why this would be useful for some of us. We do have a vested interest in
the
> product!
> 
> - Jeff
> 
> 
> 
> 
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Thu 1/17/2008 9:21 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Date arithmetic question
> 
> 
> 
> Gerry Snyder <[EMAIL PROTECTED]> wrote:
> > [EMAIL PROTECTED] wrote:
> > >
> > > So datediff('month', '2008-02-01 23:59:59','2008-01-31 00:00:00')
> > > should return 1 even though the difference is really only 1 second?
> > > Seems goofy to me
> > >
> > >
> >
> > I have been staring at this until I'm getting goofy.
> >
> > Written as it is, isn't the time interval 1 second short of two days?
> >
> > If you want an interval of 1 second shouldn't it be
> >
> > datediff('month', '2008-02-01 00:00:00','2008-01-31 23:59:59')
> >
> > ?
> >
> >
> > Gerry, more confused than usual (as usual)
> >
> 
> Yeah.  I got it backwards.  Sorry.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 
>

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

-
> 
> 
> 



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

[sqlite] Why are strings in hexadecimal notation always blobs?

[Lothar Scholz]

Hello,

it seems that "Lothar" is stored as a TEXT value but when i store
X'4C6F74686172' it is a BLOB.

What is the reason for it? The dynamic typing should look at the
data content and not the literal form. So i would consider this a bug.

For convenience i tried to always use hex quotes no matter what data
is inside. But this is bad for a GUI Frontend i've written which is
displaying data differently if 'sqlite3_column_type' returns 4.

-- 
Best regards,
 Lothar Scholz  mailto:[EMAIL PROTECTED]


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

Re: [sqlite] Date arithmetic question

[Paolo Vernazza]

Fowler, Jeff wrote:

Hello All,
 
SQLite newbie here. I've looked through the email archives and website

trying to find out how to compute the difference in months between two
given dates. Each date is in -MM-DD HH:MM:SS format.
 
The best I've been able to come up with seems rather ugly:
 
SELECT (strftime( '%Y', date1)*12+strftime('%m',date1)) -

(strftime('%Y',date1)*12+strftime('%m',date1))
 
Am I on the right track, or is there something obvious I'm missing?
 
Thanks in advance,
 
Jeff Fowler

For the other query you reported in a later mail

WHERE (strftime('%Y', LaborEndDate)*12+strftime('%m', LaborEndDate)) - 
(strftime('%Y', LaborStartDate)*12+strftime('%m', LaborStartDate)) < 3


I'm using something like this:
WHERE (date(date1, '+3 months') > date(date2))

Paolo

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