Re: [sqlite] Confitional IF in triggers

[Drake Wilson]

Quoth Russell A , on 2010-10-07 22:24:23 -0700:
> This may be a really dumb question, but I've searched and can't find an 
> answer.
> Do SQLite triggers support any conditional expressions, like IF, or is there 
> only the WHEN statement? If the latter, does that mean that multiple 
> conditions must be in separate triggers?

Your answer is right in the docs, where the syntax diagrams at
http://sqlite.org/lang_createtrigger.html will demonstrate that the
body of a trigger is a sequence of UPDATE, INSERT, DELETE, and SELECT
statements.  There is no full procedural language.

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

[sqlite] Confitional IF in triggers

[Russell A]

This may be a really dumb question, but I've searched and can't find an answer.
Do SQLite triggers support any conditional expressions, like IF, or is there 
only the WHEN statement? If the latter, does that mean that multiple conditions 
must be in separate triggers?
Any help appreciated.Stopgap.


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

Re: [sqlite] SQLite database sync

[Alexander Spence]

Do you really require a local copy of the entire database?  Can you not just 
create a service that queries the database and gives you results on demand, 
caching the results?

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of David Haymond
Sent: Wednesday, October 06, 2010 7:30 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] SQLite database sync

Hi SQLite users,

I am completely new to SQLite (and SQL in general), and I am currently working 
on an iPhone app that uses the embedded SQLite engine to cache offline data. 
What is the best way to implement synchronization capabilities (change 
tracking, state tracking, etc.) in a separate table (such as meta), so that the 
app can sync to the server?

My database contains two tables: locations and trips. trips is the child of 
locations.

Any help would be greatly appreciated.

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

Please NOTE: This electronic message, including any attachments, may include 
privileged, confidential and/or inside information owned by Demand Media, Inc. 
Any distribution or use of this communication by anyone other than the intended 
recipient(s) is strictly prohibited and may be unlawful.  If you are not the 
intended recipient, please notify the sender by replying to this message and 
then delete it from your system. Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite and C++ in Linux

[Richard Hipp]

On Thu, Oct 7, 2010 at 5:47 PM, Dan Sabin  wrote:

> Hi,
>
> I'm trying to embed SQLite into a Linux application. It's written in C++
> and I'm having trouble getting the code to compile with the SQLite .c and .h
> files included. I get some of the following errors:
>
> ./MyProject/Debug/../src/sqlite3.c:16556: undefined reference to
> `pthread_mutexattr_init'
> ./MyProjec/Debug/../src/sqlite3.c:16557: undefined reference to
> `pthread_mutexattr_settype'
> ./MyProjec/Debug/../src/sqlite3.c:16559: undefined reference to
> `pthread_mutexattr_destroy'
>

Please add  -lpthread to the end of the gcc command-line.


>
> I looked and the  file is being included. I just don't know how
> sqlite.c can't find the functions. If you have any knowledge of where i can
> find out how to solve my problem it'd be appreciated. I've searched
> everywhere and i can't find any answers.
>
> Thank You,
> Dan
>



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

[sqlite] Porting SQLite 3.7.2 to vxWorks 6.7

[Scott A Mintz]

I searched through all the mail archives to see if I could find someone 
that has ported SQLite to vxWorks in kernel mode.  Apparently, there are a 
few folks attempting it.  And fewer succeeding at it.

I found an article published by ZhiHua Huang where he describes the mods 
he made to port SQLite 3.6.23.1 to vxWorks 6.5 
http://www.mail-archive.com/sqlite-users@sqlite.org/msg51531.html 

Using that as a starting point, I modified my files but I get the 
following errors:
"C:/CCViews/Mintz_NetlinxUCS_L7x/NetLinxUCS/SQLite3_LIB/sqlite3.c", line 
27262: error (dcc:1633): parse error  near 'struct'
"C:/CCViews/Mintz_NetlinxUCS_L7x/NetLinxUCS/SQLite3_LIB/sqlite3.c", line 
27262: error (dcc:1206): syntax error
"C:/CCViews/Mintz_NetlinxUCS_L7x/NetLinxUCS/SQLite3_LIB/sqlite3.c", line 
27262: fatal error (dcc:1340): can't recover from earlier errors

This is the code it's complaining about.  The "struct statfs fsInfo;" line 
is line 27262.

#ifdef FD_CLOEXEC
  fcntl(fd, F_SETFD, fcntl(fd, F_GETFD, 0) | FD_CLOEXEC);
#endif

  noLock = eType!=SQLITE_OPEN_MAIN_DB;

 
#if defined(__APPLE__) || SQLITE_ENABLE_LOCKING_STYLE
  struct statfs fsInfo;
  if( fstatfs(fd, ) == -1 ){
((unixFile*)pFile)->lastErrno = errno;
if( dirfd>=0 ) close(dirfd); /* silently leak if fail, in error */
close(fd); /* silently leak if fail, in error */
return SQLITE_IOERR_ACCESS;
  }
  if (0 == strncmp("msdos", fsInfo.f_fstypename, 5)) {
((unixFile*)pFile)->fsFlags |= SQLITE_FSFLAGS_IS_MSDOS;
  }
#endif

Any ideas?

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

Re: [sqlite] Involving in sqlite development

[P Kishor]

On Thu, Oct 7, 2010 at 4:26 PM, sjtirtha  wrote:
> Hi,
>
> I'm interested involving in sqlite development.
> How can I start it?


You really need to explain further and more clearly what you want to
do before anyone will be able to guide you.


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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Involving in sqlite development

[sjtirtha]

Hi,

I'm interested involving in sqlite development.
How can I start it?

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

[sqlite] how do I load a csv file or what is the simplest text file to load into sqlite3?

[joshua wojnas]

how do I load a csv file or what is the simplest text file to load into sqlite3?

what would be the best format to use I use text pad and open office
calc. Also open office base.

-- 
thanks for your time,
Joshua W
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Accented characters and ODBC - Fixed

[Greg Bryant]

I had the right idea, but the wrong code page.

So, going from MBCS project in visual c++, there is a magic ANSI to UTF8
conversion happening when the data is stored, but not one coming back out.
I could not find a direct UTF8 to ANSI code page conversion, presumably
since they are both "multibyte", but I found a function online that would
take the string from UTF8 to back to ANSI - by routing it through an extra
conversion. (First do a MultiByteToWideChar using the UTF8 code page 65001,
then WideCharToMultiByte using the ANSI code page, 1252).  Algorithm found
at http://www.chilkatsoft.com/p/p_348.asp.

Greg

On Thu, Oct 7, 2010 at 11:22 AM, Greg Bryant  wrote:

> Still hoping someone can help me with this.  I dug into it some with a hex
> editor, and it may be the app mode, which is multi-byte (and makes extensive
> use of libraries, so is not about to change).
>
> If I put the string Andé in, it is (41 6e 64 e9) in the debugger, but
> looking at the sqlite database file, it is (41 6e 64 c3 a9), with the c3
> appearing to be a UTF-8 "trigger" indicating a 2 byte characters.  So,
> somewhere along the way it went from mb to utf8, and it's just not being
> converted back out for the SELECT.  I tried a quick wide-to-multibyte
> conversion, but got real garbage then.  Next step is a manual mb to utf8
> conversion going in, but if anyone has any pointers, I'd sure love to have
> them, since I'm just guessing.
>
> Greg
>
>
> On Fri, Oct 1, 2010 at 12:51 PM, Greg Bryant  wrote:
>
>> Not sure if ODBC questions belong here, feel free to point me to a better
>> forum.
>>
>> I'm using current SQLite (3.7.2) via a visual c++ app.  We're connecting a
>> sqlite3 database via ODBC (driver from , also current version - 0.87).  If I
>> do either an insert or update using an accented character (e.g. André), it
>> works fine, and I can check it using anything that directly connects to the
>> database (I've been using SQLiteStudio and SQLiteAdmin) and it looks fine.
>> When I go to do a select via the ODBC connection, however, I get back
>> garbage for the accented character (specifically, I get André).
>>
>> I don't think the app settings are a problem, since the accent works fine
>> through the insert, and step into doesn't get me past the SQLFetch from
>> Microsoft's odbc layer, and I don't think it's sqlite, since it appears to
>> be correct when viewed through a direct connection, which just leaves the
>> odbc layer.  Does anyone have any pointers on where I can look to figure
>> this out?
>>
>> Thanks,
>> Greg
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite database sync

[Petite Abeille]

On Oct 7, 2010, at 9:35 PM, Nicolas Williams wrote:

> A general tool for hands-off bi-di synchronization of arbitrary DBs is
> not really feasible, not in a way that would satisfy most users.  

Perhaps we should ask the author of diffkit [1] to solve that hairy problem for 
the rest of us :))

In the meantime, perhaps something along these lines... 

.dump -> diff(1) -> patch(1) -> .load

... and vis-versa.

[1] http://code.google.com/p/diffkit/



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

Re: [sqlite] SQLite database sync

[Nicolas Williams]

On Thu, Oct 07, 2010 at 09:09:19PM +0200, Petite Abeille wrote:
> On Oct 6, 2010, at 5:50 PM, David Haymond wrote:
> > If I copy, I don't want to transfer EVERY record to the server each time I 
> > sync, because that would be a waste of bandwidth. What is the best way to 
> > copy only those records that have changed to the server?
> 
> Perhaps you could simply rsync the two files?

That only works for one-way synchronization, but for that rsync is
probably a very good idea.

For bi-directional synchronization things get messy because conflicts
can arise that must get resolved.  Since conflicts must get resolved
(i.e., you can't raise exceptions, can't rollback, can't reject) you
cannot handle them in the same way that you'd handle conflicts in the
case of normal transactions.  Dealing with uniqueness vilations here
requires generating new values for some conflicting records.  Dealing
with RESTRICT foreign key violations requires either un-DELETEing rows
or finding new rows to refer to, and so on.  Worse, the conflict
resolution must be done in such a way that the two DB copies end up
having the same contents when you're done, so the conflict resolution
must be deterministic regardless of where you're doing it.

A general tool for hands-off bi-di synchronization of arbitrary DBs is
not really feasible, not in a way that would satisfy most users.  Such a
tool would at minimum require plenty of schema-specific configuration to
be acceptbale.

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

Re: [sqlite] SQLite database sync

[Petite Abeille]

On Oct 6, 2010, at 5:50 PM, David Haymond wrote:

> If I copy, I don't want to transfer EVERY record to the server each time I 
> sync, because that would be a waste of bandwidth. What is the best way to 
> copy only those records that have changed to the server?

Perhaps you could simply rsync the two files?


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

Re: [sqlite] create View for four tables

[Petite Abeille]

On Oct 7, 2010, at 5:49 AM, Redhot wrote:

> I need to pull information from 4 different tables. I read taht using the
> "Create View" is that best way for this.

Non sequitur :)

> Can you let me now if my code is correct?  

http://en.wikipedia.org/wiki/Join_(SQL)

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

Re: [sqlite] Speed up DELETE of a lot of records

[Petite Abeille]

On Oct 7, 2010, at 6:05 PM, Michele Pradella wrote:

> I have a DB of about 3GB: the DB has about 23 millions of records. [..]
> the statement is trying to delete about 5 millions records and it takes 
> about 4-5minutes.
> Is there a way to try to speed up the DELETE?

Considering that you want to delete about a quarter of the records, perhaps it 
would be more efficient to recreate that table altogether, no?

Pseudocode:

create table new as select * from current where condition = keep;
create index on new;
drop table current;
alter table rename new to current;

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

Re: [sqlite] Speed up DELETE of a lot of records

[P Kishor]

On Thu, Oct 7, 2010 at 11:05 AM, Michele Pradella
 wrote:
>  Hi all, I have a question about how to speed up a DELETE statement.
> I have a DB of about 3GB: the DB has about 23 millions of records.
> The DB is indexed by a DateTime column (is a 64 bit integer), and
> suppose you want to delete all records before a date.
> Now I'm using a syntax like this (I try all the statement with the
> sqlite shell):
> suppose to use __int64 DateValue=the date limit you want to delete
>
> DELETE FROM table_name WHERE DateTime

What is the speed of

SELECT FROM table WHERE DateTime >= DateValue;

If the above speed is acceptable, then try the following

CREATE TABLE tmp AS SELECT FROM table WHERE DateTime >= DateValue;
DROP TABLE table;
ALTER TABLE tmp RENAME to table;


> the statement is trying to delete about 5 millions records and it takes
> about 4-5minutes.
> Is there a way to try to speed up the DELETE? I already try to put the
> DELETE statement between a BEGIN; COMMIT; statement, but same result.
> After the delete complete I have a -wal file of about 600MB: this file
> is not deleted even if I disconnect from the database.
> Is that the right behavior? I thought that when the last DB connection
> terminate the -wal file is reintegrated in the DB, but it's not.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed up DELETE of a lot of records

[Pavel Ivanov]

> But the rows he wants to delete are those with DateTime without an index on that column SQL can't find which rows to delete quickly !

"Quickly" is appropriate for one row. For several rows SQLite will
sequentially scan the index and for each rowid found there it will
traverse the table's b-tree structure from top to bottom searching for
the rowid and then delete that row. For 3 million rows it's well
likely that this process is slower than sequentially scanning the
table and marking as deleted all rows satisfying the condition.


Pavel

On Thu, Oct 7, 2010 at 12:52 PM, Simon Slavin  wrote:
>
> On 7 Oct 2010, at 5:36pm, Jay A. Kreibich wrote:
>
>> On Thu, Oct 07, 2010 at 05:22:19PM +0100, Simon Slavin scratched on the wall:
>>> On 7 Oct 2010, at 5:05pm, Michele Pradella wrote:
>>>
 The DB is indexed by a DateTime column (is a 64 bit integer)
>>>
>>> Do make sure that that column is declared as INTEGER and that there
>>> is an index on it.
>>
>>  When deleting 20 to 25% of the rows, an index is likely to slow
>>  things down.
>
> But the rows he wants to delete are those with DateTime without an index on that column SQL can't find which rows to delete quickly !
>
> Hmm.  If all rows are entered in DateTime order, and the table has an 
> AUTOINCREMENT primary key, then the values in that column will be in the same 
> order as the values in the primary key.  So perhaps he could do one SELECT to 
> find the appropriate primary key value, then use the primary key in his 
> DELETE command instead of the DateTime column.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed up DELETE of a lot of records

[Simon Slavin]

On 7 Oct 2010, at 5:36pm, Jay A. Kreibich wrote:

> On Thu, Oct 07, 2010 at 05:22:19PM +0100, Simon Slavin scratched on the wall:
>> On 7 Oct 2010, at 5:05pm, Michele Pradella wrote:
>> 
>>> The DB is indexed by a DateTime column (is a 64 bit integer)
>> 
>> Do make sure that that column is declared as INTEGER and that there
>> is an index on it.
> 
>  When deleting 20 to 25% of the rows, an index is likely to slow
>  things down.

But the rows he wants to delete are those with DateTimehttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed up DELETE of a lot of records

[Jay A. Kreibich]

On Thu, Oct 07, 2010 at 05:22:19PM +0100, Simon Slavin scratched on the wall:
> On 7 Oct 2010, at 5:05pm, Michele Pradella wrote:
> 
> > The DB is indexed by a DateTime column (is a 64 bit integer)
> 
> Do make sure that that column is declared as INTEGER and that there
> is an index on it.

  When deleting 20 to 25% of the rows, an index is likely to slow
  things down.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed up DELETE of a lot of records

[Simon Slavin]

On 7 Oct 2010, at 5:05pm, Michele Pradella wrote:

> The DB is indexed by a DateTime column (is a 64 bit integer)

Do make sure that that column is declared as INTEGER and that there is an index 
on it.

> DELETE FROM table_name WHERE DateTime 
> the statement is trying to delete about 5 millions records and it takes 
> about 4-5minutes.
> Is there a way to try to speed up the DELETE?

Do you have many indexes on that table ?  Or any complicated ones ?  It might 
be worth doing something like

BEGIN
DROP all indexes on the table apart from the one on DateTime
DELETE the records
CREATE all the dropped indexes again
COMMIT

On the other hand you say you're deleting about 5 million records out of 23, so 
that might not help.

> I already try to put the 
> DELETE statement between a BEGIN; COMMIT; statement, but same result.

Without declaring transactions, one statement (i.e. one DELETE command) is one 
transaction, no matter how many records it has an effect on.  As you've 
discovered.

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

Re: [sqlite] Accented characters and ODBC

[Greg Bryant]

Still hoping someone can help me with this.  I dug into it some with a hex
editor, and it may be the app mode, which is multi-byte (and makes extensive
use of libraries, so is not about to change).

If I put the string Andé in, it is (41 6e 64 e9) in the debugger, but
looking at the sqlite database file, it is (41 6e 64 c3 a9), with the c3
appearing to be a UTF-8 "trigger" indicating a 2 byte characters.  So,
somewhere along the way it went from mb to utf8, and it's just not being
converted back out for the SELECT.  I tried a quick wide-to-multibyte
conversion, but got real garbage then.  Next step is a manual mb to utf8
conversion going in, but if anyone has any pointers, I'd sure love to have
them, since I'm just guessing.

Greg

On Fri, Oct 1, 2010 at 12:51 PM, Greg Bryant  wrote:

> Not sure if ODBC questions belong here, feel free to point me to a better
> forum.
>
> I'm using current SQLite (3.7.2) via a visual c++ app.  We're connecting a
> sqlite3 database via ODBC (driver from , also current version - 0.87).  If I
> do either an insert or update using an accented character (e.g. André), it
> works fine, and I can check it using anything that directly connects to the
> database (I've been using SQLiteStudio and SQLiteAdmin) and it looks fine.
> When I go to do a select via the ODBC connection, however, I get back
> garbage for the accented character (specifically, I get André).
>
> I don't think the app settings are a problem, since the accent works fine
> through the insert, and step into doesn't get me past the SQLFetch from
> Microsoft's odbc layer, and I don't think it's sqlite, since it appears to
> be correct when viewed through a direct connection, which just leaves the
> odbc layer.  Does anyone have any pointers on where I can look to figure
> this out?
>
> Thanks,
> Greg
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Speed up DELETE of a lot of records

[Michele Pradella]

  Hi all, I have a question about how to speed up a DELETE statement.
I have a DB of about 3GB: the DB has about 23 millions of records.
The DB is indexed by a DateTime column (is a 64 bit integer), and 
suppose you want to delete all records before a date.
Now I'm using a syntax like this (I try all the statement with the 
sqlite shell):
suppose to use __int64 DateValue=the date limit you want to delete

DELETE FROM table_name WHERE DateTimehttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [BUG] JOIN subquery in FROM with FTS3 table

[Shopsland gmail]

Max,

Thank you for your fast answer.

I already knew the thread you suggest.

The issue here is that the same join works fine on 3.7.2 with a normal table:

SELECT news1.number, news2.title
FROM (SELECT number FROM news LIMIT 50) as news1, news2
WHERE news1.number=news2.docid

When you join with a FTS3 table (a virtual table) then you have a big
slowdown that did not happend in 3.6.23.

SELECT news1.number, fts_news.title
FROM (SELECT number FROM news LIMIT 50) as news1, fts_news
WHERE news1.number=fts_news.docid

Takes 8 seconds in 3.7.2 instead of 15ms in 3.6.23 or 3.7.2 with a
regular table.

Dan already suggested the LEFT JOIN workaround (Thanks Dan! :).

Thanks again! :)

Jochi Martínez
www.bfreenews.com



--

> Hi,
>
> Given this simple query with a subquery in FROM and a join with a FTS3
> table:
>
> SELECT news1.number, fts_news.title
> FROM (SELECT number FROM news LIMIT 50) as news1, fts_news
> WHERE news1.number=fts_news.docid
>
> The query runs in 15ms in 3.6.23. The same query runs in *8 seconds* in
> 3.7.2.
>
>

Jochi,
looks like this is intentional behavior of inner join optimizer, there was a
post recently, you can read about it here:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg55407.html
Richard suggested using ANALIZE, other workaround is to use LEFT JOIN
instead of INNER JOIN:

SELECT news1.number, fts_news.title
FROM (SELECT number FROM news LIMIT 50) as news1 LEFT JOIN  fts_news
ON news1.number=fts_news.docid

in the thread mentioned you can read very details explanation about the
logic introduced in v 3.7 up

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

Re: [sqlite] [BUG] JOIN subquery in FROM with FTS3 table

[Max Vlasov]

On Thu, Oct 7, 2010 at 4:05 PM, Richard Hipp  wrote:

> On Thu, Oct 7, 2010 at 8:00 AM, Dan Kennedy  wrote:
>
> > >
> > > It seems that something changed in the query optimizer.
> >
> > Thanks for the report. We think this has been fixed in
> > fossil already. 3.7.3 should be the same as 3.6.23 for
> > this query.
> >
>
> The 3.7.3 release will be Real Soon Now.  Please consider downloading a
> snapshot from http://www.sqlite.org/draft/download.html and giving it a
> try
> and letting us now (quickly!) if there are any remaining problems.
>
>
>
Richard, this is not the initial poster, but my tests shows that a similar
query for one of my fts bases now returned to be fast (30 ms). The same
query for 3.7.2 was real slow, even more than 8 seconds mentioned

Looks like I wasn't right about intentional behavior of the optimizer, more
like some side effect of the changes

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

Re: [sqlite] [BUG] JOIN subquery in FROM with FTS3 table

[Richard Hipp]

On Thu, Oct 7, 2010 at 8:00 AM, Dan Kennedy  wrote:

> >
> > It seems that something changed in the query optimizer.
>
> Thanks for the report. We think this has been fixed in
> fossil already. 3.7.3 should be the same as 3.6.23 for
> this query.
>

The 3.7.3 release will be Real Soon Now.  Please consider downloading a
snapshot from http://www.sqlite.org/draft/download.html and giving it a try
and letting us now (quickly!) if there are any remaining problems.



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



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

Re: [sqlite] [BUG] JOIN subquery in FROM with FTS3 table

[Dan Kennedy]

On Oct 7, 2010, at 5:50 PM, Shopsland gmail wrote:

> Hi,
>
> Given this simple query with a subquery in FROM and a join with a  
> FTS3 table:
>
> SELECT news1.number, fts_news.title
> FROM (SELECT number FROM news LIMIT 50) as news1, fts_news
> WHERE news1.number=fts_news.docid
>
> The query runs in 15ms in 3.6.23. The same query runs in *8 seconds*  
> in 3.7.2.
>
> If I do an explain query plan I get this:
>
> ###  3.6.23
> Order | From | detail
> 0 | 0 | Table News
> 0 | 0 | Table AS News1
> 1 | 1 | TABLE fts_news VIRTUAL TABLE INDEX 1:
>
> ### 3.7.2
> Order | From | detail
> 0 | 0 | Table News
> 0 | 1 | TABLE fts_news VIRTUAL TABLE INDEX 1:
> 1 | 0 | Table AS News1
>
> It seems that something changed in the query optimizer.

Thanks for the report. We think this has been fixed in
fossil already. 3.7.3 should be the same as 3.6.23 for
this query.

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

Re: [sqlite] [BUG] JOIN subquery in FROM with FTS3 table

[Max Vlasov]

On Thu, Oct 7, 2010 at 2:50 PM, Shopsland gmail  wrote:

> Hi,
>
> Given this simple query with a subquery in FROM and a join with a FTS3
> table:
>
> SELECT news1.number, fts_news.title
> FROM (SELECT number FROM news LIMIT 50) as news1, fts_news
> WHERE news1.number=fts_news.docid
>
> The query runs in 15ms in 3.6.23. The same query runs in *8 seconds* in
> 3.7.2.
>
>

Jochi,
looks like this is intentional behavior of inner join optimizer, there was a
post recently, you can read about it here:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg55407.html
Richard suggested using ANALIZE, other workaround is to use LEFT JOIN
instead of INNER JOIN:

SELECT news1.number, fts_news.title
FROM (SELECT number FROM news LIMIT 50) as news1 LEFT JOIN  fts_news
ON news1.number=fts_news.docid

in the thread mentioned you can read very details explanation about the
logic introduced in v 3.7 up

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

[sqlite] [BUG] JOIN subquery in FROM with FTS3 table

[BFreeNews.com]

Hi,

Given this simple query with a subquery in FROM and a join with a FTS3 table:

SELECT news1.number, fts_news.title
FROM (SELECT number FROM news LIMIT 50) as news1, fts_news
WHERE news1.number=fts_news.docid

The query runs in 15ms in 3.6.23. The same query runs in *8 seconds* in 3.7.2.

If I do an explain query plan I get this:

###  3.6.23
Order | From | detail
0 | 0 | Table News
0 | 0 | Table AS News1
1 | 1 | TABLE fts_news VIRTUAL TABLE INDEX 1:

### 3.7.2
Order | From | detail
0 | 0 | Table News
0 | 1 | TABLE fts_news VIRTUAL TABLE INDEX 1:
1 | 0 | Table AS News1

It seems that something changed in the query optimizer.

Keep up the good work! :-)

Jochi Martínez
www.bfreenews.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger Variables

[Pavel Ivanov]

> I'm porting an Interbase DB to SQLIte and wondered if there is any way to 
> store temporary values within a trigger?

No. You should use (temporary) tables created outside the trigger for
that. Or you can move the trigger logic into your application.


Pavel

On Thu, Oct 7, 2010 at 4:26 AM, Russell A  wrote:
> I'm porting an Interbase DB to SQLIte and wondered if there is any way to 
> store temporary values within a trigger?
> Any help appreciated.Slurcher.
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [BUG] JOIN subquery in FROM with FTS3 table

[Shopsland gmail]

Hi,

Given this simple query with a subquery in FROM and a join with a FTS3 table:

SELECT news1.number, fts_news.title
FROM (SELECT number FROM news LIMIT 50) as news1, fts_news
WHERE news1.number=fts_news.docid

The query runs in 15ms in 3.6.23. The same query runs in *8 seconds* in 3.7.2.

If I do an explain query plan I get this:

###  3.6.23
Order | From | detail
0 | 0 | Table News
0 | 0 | Table AS News1
1 | 1 | TABLE fts_news VIRTUAL TABLE INDEX 1:

### 3.7.2
Order | From | detail
0 | 0 | Table News
0 | 1 | TABLE fts_news VIRTUAL TABLE INDEX 1:
1 | 0 | Table AS News1

It seems that something changed in the query optimizer.

Keep up the good work! :-)

Jochi Martínez
www.bfreenews.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unable to open database file/Disc I/O error

[Max Vlasov]

On Thu, Oct 7, 2010 at 1:03 PM, Serena Lien  wrote:

>
> If you find any vista OS patches that solve this problem, please let
> me know, we are looking ourselves in the hopes that microsoft has
> resolved this issue..
>
>
Serena,

How about this patch :
http://support.microsoft.com/kb/935366/en-us(kb935366)? They mentioned
FoxPro and Access, but actually looks like a
Vista-related bug in SMB protocol used for network access in Windows.

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

Re: [sqlite] unable to open database file/Disc I/O error

[Serena Lien]

Hi,

Just wanted to say we have the same issue, using sqlite v3.6.15 (not
java) running on *some* windows vista machines accessing databases on
*some* networked drives. I don't think this is an sqlite problem,
because vista is obviously randomly locking files and this can be seen
when using microsoft word or excel. Occasionally these programs report
that the files are in use and open them as read-only, when in fact
they are not. Reopening the file always makes the errors go away.

With our sqlite program, we also see these errors occur - typically we
get SQLITE_BUSY (the file is locked) errors compiling or executing
statements, but as you said, the errors are random. Some statements
accessing a database will work, and then another statement accessing
the same database will fail a second later. Exiting our application
and restarting it makes the problem go away for a while.

If you find any vista OS patches that solve this problem, please let
me know, we are looking ourselves in the hopes that microsoft has
resolved this issue..

thanks, Serena.




-

Ferdinand wrote:

Hi,
we have a java application ( with sqlitejdbc-v056/windows vista)  which
runs only once on every PC to fill a new database.
On most machines everything works perfectly, but on some machines the
application fails with either "Unable to open database  file"
or " disk I/O error".
The database is of course there .
This errors occur on different locations in the code(with execute() or
executeBatch()), even when previous calls to this functions were
successfull..
In most cases the problems vanish when we run  the apllication again (but
this is not  really an option we have).
I searched the internet  and found that this errors might be caused by
locks on temporary files(we run Kaspersky Anti Virus on every machine)
Are there any other explanations for this behaviour?

Thanx


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

Re: [sqlite] Bug? LIMIT in compound statement with UNION ALL seems to affect only the first SELECT statement if it has ORDER BY

[Yuri G]

Yep, it turned out to be a bug (http://www.sqlite.org/src/info/38cb5df375).

Thanks for the info, Pavel! I didn't know that.

2010/10/6 Pavel Ivanov 

> I can't say anything about your particular issue with the LIMIT
> clause, maybe that's a bug. But
>
> > Another solution is to use UNION instead of UNION ALL. But I can't use
> that,
> > because UNION does not respect ORDER BY in sub-statements (not sure if
> it's
> > a correct behavior).
>
> Do you know that SELECT ... FROM (SELECT ... ORDER BY ...) doesn't
> have to respect your ORDER BY clause? UNION ALL doesn't have to
> respect your ORDER BY clause either. So the fact that your query
> behaves exactly that you want it to is a random coincidence and you
> shouldn't rely on it. It's best for you to execute first select,
> retrieve all rows and then execute the second select to retrieve the
> remaining rows you need.
>
>
> Pavel
>
> On Wed, Oct 6, 2010 at 12:22 PM, Yuri G  wrote:
> > Hi, everyone,
> >
> > This looks like a bug to me:
> >
> > --sql:
> >
> > CREATE TABLE t(a INTEGER);
> >
> > INSERT INTO "t" VALUES(1);
> > INSERT INTO "t" VALUES(2);
> > INSERT INTO "t" VALUES(3);
> > INSERT INTO "t" VALUES(4);
> >
> > SELECT * FROM (
> >  SELECT
> >a
> >  FROM t
> >  WHERE a<=2
> >  ORDER BY a)
> >
> > UNION ALL
> >
> > SELECT * FROM (
> >  SELECT
> >a
> >  FROM t
> >  WHERE a>2)
> >
> > LIMIT 1;
> >
> > --result:
> > 1
> > 3
> > 4
> >
> > --expected:
> > 1
> >
> > If I remove ORDER BY from the first SELECT, it gives the expected number
> of
> > rows. It looks like LIMIT limits only of the results of querying the
> first
> > SELECT statement because of ORDER BY.
> > Another solution is to use UNION instead of UNION ALL. But I can't use
> that,
> > because UNION does not respect ORDER BY in sub-statements (not sure if
> it's
> > a correct behavior).
> >
> > What I'm trying to do is get all names which match the search string. I
> need
> > to show all names starting with search string and then show all other
> > results which contain search string sorting results in each "group".
> > Something like this:
> >
> > SELECT * FROM
> > (
> > SELECT
> >  name
> > FROM names
> > WHERE name LIKE 'a%'
> > ORDER BY name
> > )
> >
> > UNION ALL
> >
> > SELECT * FROM
> > (
> > SELECT
> >  name
> > FROM all_patients
> > WHERE name LIKE '%a%' AND
> >  name NOT LIKE 'a%'
> > ORDER BY name
> > )
> > LIMIT 100
> >
> > In this case LIMIT does not work as expected.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug? LIMIT in compound statement with UNION ALL seems to affect only the first SELECT statement if it has ORDER BY

[Yuri G]

Thanks, Igor. It works like a charm now.

2010/10/6 Igor Tandetnik 

> Yuri G  wrote:
> > This looks like a bug to me:
> >
> > --sql:
> >
> > CREATE TABLE t(a INTEGER);
> >
> > INSERT INTO "t" VALUES(1);
> > INSERT INTO "t" VALUES(2);
> > INSERT INTO "t" VALUES(3);
> > INSERT INTO "t" VALUES(4);
> >
> > SELECT * FROM (
> >  SELECT
> >a
> >  FROM t
> >  WHERE a<=2
> >  ORDER BY a)
> >
> > UNION ALL
> >
> > SELECT * FROM (
> >  SELECT
> >a
> >  FROM t
> >  WHERE a>2)
> >
> > LIMIT 1;
> >
> > --result:
> > 1
> > 3
> > 4
> >
> > --expected:
> > 1
>
> Looks like a bug to me, too.
>
> > What I'm trying to do is get all names which match the search string. I
> need
> > to show all names starting with search string and then show all other
> > results which contain search string sorting results in each "group".
>
> As a workaround, try something like this:
>
> SELECT name FROM names
> WHERE name LIKE '%a%'
> ORDER BY name NOT LIKE 'a%', name;
>
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Trigger Variables

[Russell A]

I'm porting an Interbase DB to SQLIte and wondered if there is any way to store 
temporary values within a trigger?
Any help appreciated.Slurcher.



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