[sqlite] SQLite3.exe console shell program run on Windows CE x86 platform

[ydlu]

I could run sqlite3 APIs on Windows CE application, but when I try build the
"shell.c" to the console program, I got "io.h" problem. Have anyone port it
to Windows CE console program?

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

Re: [sqlite] SQLIte Related

[Igor Tandetnik]

Kalyani Phadke
 wrote:
> How can I see time required to run the query using Sqlite3.exe command
> line utility?

There's timeit utility in Windows Resource Kit:

http://www.microsoft.com/downloads/details.aspx?FamilyID=9D467A69-57FF-4AE7-96EE-B18C4790CFFD=en

> Is there any way to check if indexes are being used
> while running the query

Run your query prepended with EXPLAIN QUERY PLAN

Igor Tandetnik 



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

[sqlite] Is it ok now to create a table with the same name as a table in an attached file?

[Gerry Snyder]

In the SQLite web page http://sqlite.org/lang_attach.html there is the 
statement:

"You cannot create a new table with the same name as a table in an 
attached database, but you can attach a database which contains tables 
whose names are duplicates of tables in the main database."

Is the the statement inoperative? Or does it just mean that if an 
attached file has table xxx and I want to create an xxx table in the 
main file I have to use the name main.xxx ? I can do the latter, and it 
seems to work, but I want to be sure there is not some subtle thing I am 
missing.

In the next paragraph there are the statemnts:

"When a database is attached, all of its tables which don't have 
duplicate names become the default table of that name. Any tables of 
that name attached afterwards require the database prefix."

Assuming my interpretation is correct, I think the second sentence 
should read "Any tables of that name attached /or created/ afterwards 
require the database prefix."

Thank you in advance,


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

[sqlite] SQLIte Related

[Kalyani Phadke]

How can I see time required to run the query using Sqlite3.exe command
line utility? Is there any way to check if indexes are being used while
running the query and its not doing table scan. I am trying to optimize
the following query 
SELECT ID, EventClassName, EventClassRef, TransitionTime, Message,
MonitoredRef, EventRef,ToState,Priority,Acked from Events WHERE
Events.NotificationTime >= {ts '2009-05-04 14:44:10'} Order By ID  DESC
LIMIT 100

I have indexes on ID and notificationtime (datatype timestamp).I have
2259207 records in table

 

 

 

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

Re: [sqlite] sqlite3_prepare returns SQLITE_MISUSE

[Beau Wilkinson]

I did some tests today and demonstrated that this problem goes away if the main 
thread is forced to sleep while the helper thread is running. Since the threads 
are operating on distinct databases, I don't think this should be necessary.

If I've misunderstood the level of thread safety provided by Sqlite, please 
tell me. Otherwise, I think this may be a Sqlite bug. Perhaps some confusion is 
resulting from the fact that my "distinct" databases actually share much in 
common (a schema, and one of the databases previously had the same name as the 
first but was renamed).

I'd appreciate everyone's thoughts on this... I don't really want either thread 
to have to wait and it doesn't seem like they should have to.

Also, I do plan to upgrade my version of Sqlite to whatever the latest is on 
the site (assuming I don't have the latest version already). I've only put that 
off because I'm dealing with a fairly brittle (and critical) app here, and I 
want to tread lightly.


From: Beau Wilkinson
Sent: Tuesday, May 05, 2009 9:02 AM
To: General Discussion of SQLite Database
Subject: RE: [sqlite] sqlite3_prepare returns SQLITE_MISUSE

Thanks for the reply. SQLITE_MISUSE would make sense in the scenario you 
describe. But it is difficult for me to see how I could be calling prepare with 
an unopened or closed connection. I am basically in the process of executing a 
series of statements against an open database that is exclusive to the thread. 
Everything succeeds until a random point. Is the database being closed somehow, 
and I am not realizing it? Or are my threads interacting in a way I've not 
considered?


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Igor Tandetnik [itandet...@mvps.org]
Sent: Monday, May 04, 2009 5:18 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] sqlite3_prepare returns SQLITE_MISUSE

Beau Wilkinson  wrote:
> Nevertheless, I am getting some very puzzling errors. In particular,
> there are cases where sqlite3_prepare() is the first call to cause an
> error, typically SQLITE_MISUSE.

You are passing a bad (never opened, already closed) connection handle
to sqlite3_prepare.

Igor Tandetnik



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

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_open_v2 and SQLITE_BUSY

[Igor Tandetnik]

Steven Fisher  wrote:
> I was looking over the requirements for sqlite3_open_v2(), and I'm not
> clear if this function can ever return SQLITE_BUSY.

I don't believe so. As far as I know, it doesn't actually touch the file 
at all, so it won't even return I/O errors (the file is physically 
opened and read when you prepare your first non-PRAGMA statement).

Igor Tandetnik 



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

[sqlite] sqlite3_open_v2 and SQLITE_BUSY

[Steven Fisher]

I was looking over the requirements for sqlite3_open_v2(), and I'm not  
clear if this function can ever return SQLITE_BUSY.

I initially wrote code to handle this case by sleeping and trying  
sqlite3_open_v2() again, but it is untested and I've spotted one bug  
in it already (I wasn't calling sqlite3_close in this case). If I can,  
I'd like to get rid of the code utterly.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] import / insert 120k records

[P Kishor]

On Thu, May 7, 2009 at 2:51 PM, Ben Marchbanks  wrote:
> That makes good sense - if my task were repetitive I would take that
> route - I opted to write
> a quick and dirty PHP script just to handle this one-off case and it
> worked like a charm.
>
> I can share the script as a jumping off point for anyone who is interested.


Add it to the wiki at sqlite.org under a suitable title. That way it
won't be forgotten, and it would be available to others.

>
>
> *Ben Marchbanks*
>
>
>
> John Stanton wrote:
>> Write a simple program..
>>
>> As I recall our programs which do this use the Expat parser and obey a 
>> protocol where the XML DTD represents the database tables and columns so 
>> that SQL can be generated directly from the XML.  It makes for a handy way 
>> to transport a database, or a part of a database in a universal format.  All 
>> that is needed is the program to scan the database and generate XML and one 
>> to receive the XML and transform it into SQL statements.
>>
>>
>> Ben Marchbanks wrote:
>>
>>> Whats the best way to import a large number of records ?
>>> 120K+ currently stored as an XML file.
>>>
>>>
>>>
>>
>> ___
>> 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
>



-- 
Puneet Kishor http://www.punkish.org/
Carbon Model http://carbonmodel.org/
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org/
Science Commons Fellow, Geospatial Data http://sciencecommons.org
Nelson Institute, UW-Madison http://www.nelson.wisc.edu/
---
collaborate, communicate, compete
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] import / insert 120k records

[Ben Marchbanks]

That makes good sense - if my task were repetitive I would take that 
route - I opted to write
a quick and dirty PHP script just to handle this one-off case and it 
worked like a charm.

I can share the script as a jumping off point for anyone who is interested.


*Ben Marchbanks*



John Stanton wrote:
> Write a simple program..
>
> As I recall our programs which do this use the Expat parser and obey a 
> protocol where the XML DTD represents the database tables and columns so that 
> SQL can be generated directly from the XML.  It makes for a handy way to 
> transport a database, or a part of a database in a universal format.  All 
> that is needed is the program to scan the database and generate XML and one 
> to receive the XML and transform it into SQL statements.
>
>
> Ben Marchbanks wrote:
>   
>> Whats the best way to import a large number of records ?
>> 120K+ currently stored as an XML file.  
>>
>>   
>> 
>
> ___
> 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] import / insert 120k records

[John Stanton]

Write a simple program..

As I recall our programs which do this use the Expat parser and obey a protocol 
where the XML DTD represents the database tables and columns so that SQL can be 
generated directly from the XML.  It makes for a handy way to transport a 
database, or a part of a database in a universal format.  All that is needed is 
the program to scan the database and generate XML and one to receive the XML 
and transform it into SQL statements.


Ben Marchbanks wrote:
> Whats the best way to import a large number of records ?
> 120K+ currently stored as an XML file.  
>
>   

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

Re: [sqlite] Best way to do a date comparison?

[Radcon Entec]

Thanks again for your help, Igor.

RobR



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

Re: [sqlite] Best way to do a date comparison?

[Igor Tandetnik]

Radcon Entec 
wrote:
> So, since SQLite doesn't have a dedicated date or time type, what
> does the datetime() function return? Merely a string in a guaranteed
> format?

Yes.

> I'm sure I need to use the datetime() function on both sides
> of the comparison

Not necessarily, if your timestamps are already in the format that 
datetime() produces, in which case it's just an expensive no-op.

> But, just for my eduction, does the string in the call to the
> datetime() function have to formatted in the way I have it here?

The documentation at http://sqlite.org/lang_datefunc.html describes all 
acceptable formats.

Igor Tandetnik 



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

Re: [sqlite] Best way to do a date comparison?

[Radcon Entec]

Igor,

Thank you very much for your reply.

So, since SQLite doesn't have a dedicated date or time type, what does the 
datetime() function return?  Merely a string in a guaranteed format?  I'm sure 
I need to use the datetime() function on both sides of the comparison to ensure 
that I'm comparing apples to apples.  I also changed the format of the string 
my application generates.  The query is now:

delete from trend_data where datetime(value_timestamp) < datetime('2009-04-07  
12:37:32')

It seems to be working.  

But, just for my eduction, does the string in the call to the datetime() 
function have to formatted in the way I have it here?  In SQLiteSpy, I tried 
"select datetime('04-07-2009  12:37:32')"  and "select datetime('2009/04/07  
12:37:32')".  Both of them returned nothing.  

Thanks again!

RobR



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

Re: [sqlite] Optimizing concurrency with sql query - locks?

[Jim Wilcoxson]

The original question was about 300 queries, which I took to mean
selects.  If the database is in memory, will 300 selects still cause
synchronous disk I/O?

Jim

On 5/6/09, John Stanton  wrote:
> Sqlite is an ACID database - it ensures that data is written to disk, so
> a database in memory still shares a single disk resource.
>
> Jim Wilcoxson wrote:
>> I'm not sure what you are considering a massive slowdown, but let's
>> assume that the entire database fits into memory and disk I/O isn't
>> the bottleneck.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.6.14

[Andreas Radke]

Am Wed, 6 May 2009 22:36:50 -0400
schrieb "D. Richard Hipp" :

> SQLite version 3.6.14 is now available on the SQLite website
> 
>  http://www.sqlite.org/
> 
> Version 3.6.14 contains performance enhances in the btree and pager  
> subsystems.  In addition, the query optimizer now knows how to take  
> advantage of OR and IN operators on columns of a virtual table.
> 
> A new optional extension is included that implements an asynchronous
> I/ O backend for SQLite on either windows or unix.  The asynchronous
> I/O backend processes all writes using a background thread.  This
> gives the appearance of faster response time at the cost of
> durability and additional memory usage.  See
> http://www.sqlite.org/asyncvfs.html for additional information.
> 
> This release also includes many small bug fixes and documentation  
> improvements.
> 
> As always, please let me know if you encounter any difficulties.
> 
> D. Richard Hipp
> d...@hwaci.com
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

==> Beginne build()...
configure: error: configure script is out of date:
 configure $PACKAGE_VERSION = 3.6.13
 top level VERSION file = 3.6.14
please regen with autoconf


worked with running autoreconf here.

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

Re: [sqlite] Best way to do a date comparison?

[Igor Tandetnik]

Radcon Entec 
wrote:
> I am trying to write a simple applicaton in C# that will remove all
> data older than 30 days from an SQLite table. However, my application
> is removing all data, not just the old data.
>
> Before I run my query, the value_timestamp field of my table contains:
>
> 2009-05-07 17:00:43
>
> My query is:
>
> delete from trend_data where datetime(value_timestamp) < '4/7/2009
> 12:37:32 PM'

Realize that SQLite doesn't have a dedicated type for dates or times. 
'4/7/2009 12:37:32 PM' is simply a string literal, and your query 
performs the usual string comparison. Since '2' is less than '4', all 
dates in your table satisfy the condition.

Now, if you represent your cut-off date as '2009-04-07 12:37:32' then 
string comparison would correctly order timestamps, too.

Igor Tandetnik 



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

Re: [sqlite] SQLite version 3.6.14 and async vfs

[Ken]

Obviously i had not looked at the code. I see now that the async code is 
registered as a VFS... And thanks to Dan there is only one background thread 
and queue.

One could implement the background thread and a function, mutex and condition 
variable so that the function would call the sqlite3async_control setting the 
SQLITEASYNC_HALT_IDLE. The function would then block on the condition awaiting 
the running thread to complete the work. The running thread would then signal 
the condition causing the "waiting" function to return.

 
--- On Thu, 5/7/09, Virgilio Alexandre Fornazin  
wrote:

> From: Virgilio Alexandre Fornazin 
> Subject: RE: [sqlite] SQLite version 3.6.14 and async vfs
> To: kennethinbox-sql...@yahoo.com, "'General Discussion of SQLite Database'" 
> , "'Pavel Ivanov'" 
> Date: Thursday, May 7, 2009, 12:04 PM
> This break purpose of VFS, all VFS
> should work in same way, you must not
> know if your VFS is asynchronous
> or not. VFS close method should wait for all file I/O on
> this database
> handle (not all databases) to 
> finalize before returning, providing compatibility with all
> other existing
> VFS implementations.
> 
> 
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org]
> On Behalf Of Ken
> Sent: quinta-feira, 7 de maio de 2009 13:47
> To: General Discussion of SQLite Database; Pavel Ivanov
> Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs
> 
> 
> I see the confusion with the word "Shutdown".
> 
> How about but a call that would block until the async
> thread completes all
> operations that are enqueued. Effectively a Close of the
> async thread/queue
> and db. The call could be sqlite3Async_close.
> 
> Hope that clarifies my intent. 
> 
> 
> --- On Thu, 5/7/09, Pavel Ivanov 
> wrote:
> 
> > From: Pavel Ivanov 
> > Subject: Re: [sqlite] SQLite version 3.6.14 and async
> vfs
> > To: kennethinbox-sql...@yahoo.com,
> "General Discussion of SQLite Database"
> 
> > Date: Thursday, May 7, 2009, 11:10 AM
> > Shutdown is not an option at all. I
> > need vfs to continue working on
> > other databases but to be notified (or have
> possibility to
> > check) when
> > one particular database is no longer opened.
> > 
> > Pavel
> > 
> > On Thu, May 7, 2009 at 12:00 PM, Ken 
> > wrote:
> > >
> > > --- On Thu, 5/7/09, Virgilio Alexandre Fornazin
> 
> > wrote:
> > >
> > >> From: Virgilio Alexandre Fornazin 
> > >> Subject: Re: [sqlite] SQLite version 3.6.14
> and
> > async vfs
> > >> To: "'General Discussion of SQLite
> Database'"
> > 
> > >> Date: Thursday, May 7, 2009, 10:50 AM
> > >> Close should wait for all file
> > >> operations complete to meet that needs.
> > >> I think asynchronous VFS should take care of
> > waiting in
> > >> sqlite3_close()
> > >> call.
> > >>
> > >> -Original Message-
> > >> From: sqlite-users-boun...@sqlite.org
> > >> [mailto:sqlite-users-boun...@sqlite.org]
> > >> On Behalf Of Pavel Ivanov
> > >> Sent: quinta-feira, 7 de maio de 2009 12:33
> > >> To: General Discussion of SQLite Database
> > >> Subject: Re: [sqlite] SQLite version 3.6.14
> and
> > async vfs
> > >>
> > >> Hi!
> > >>
> > >> It's great to hear about performance
> improvements
> > and
> > >> especially about
> > >> asynchronous I/O extension. Thank you very
> much
> > for your
> > >> work!
> > >>
> > >> I have one question though: taking quick look
> at
> > the
> > >> sources of async
> > >> vfs I've noticed that even closing the file
> is
> > just a task
> > >> in the
> > >> async queue and thus after closing sqlite
> > connection file
> > >> remains
> > >> opened for some time. It sounds pretty
> reasonable,
> > but here
> > >> stands the
> > >> question: what if I want to do something with
> the
> > database
> > >> file after
> > >> I close sqlite connection to it (e.g. move to
> the
> > archive
> > >> directory,
> > >> zip it etc.)? With sync vfs I could be sure
> that
> > after
> > >> closing
> > >> connection file is closed and I can do with
> it
> > whatever I
> > >> want. Is
> > >> there a way to catch the moment of actual
> file
> > closing with
> > >> async vfs?
> > >>
> > >> And another question just to be sure that I
> > understand it
> > >> correctly:
> > >> async vfs holds only one queue for all
> opened
> > database
> > >> files, right?
> > >>
> > >> Pavel
> > >>
> > >> On Wed, May 6, 2009 at 10:36 PM, D. Richard
> Hipp
> > 
> > >> wrote:
> > >> > SQLite version 3.6.14 is now available
> on the
> > SQLite
> > >> website
> > >> >
> > >> >     http://www.sqlite.org/
> > >> >
> > >> > Version 3.6.14 contains performance
> enhances
> > in the
> > >> btree and pager
> > >> > subsystems.  In addition, the query
> > optimizer 

Re: [sqlite] Best way to do a date comparison?

[Christopher Taylor]

Before I run my query, the value_timestamp field of my table contains:

2009-05-07 17:00:43

My query is:

delete from trend_data where datetime(value_timestamp) < '4/7/2009 12:37:32
PM'


Your times are in different formats - -mm-dd hh-mm-ss is what you state
is in the database.  You should use the same format in your query.

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

[sqlite] Best way to do a date comparison?

[Radcon Entec]

Greetings!

I am trying to write a simple applicaton in C# that will remove all data older 
than 30 days from an SQLite table.  However, my application is removing all 
data, not just the old data.

Before I run my query, the value_timestamp field of my table contains:

2009-05-07 17:00:43

My query is:

delete from trend_data where datetime(value_timestamp) < '4/7/2009 12:37:32 PM'

I'm sure I'm running into the fact that SQLite allows a user to store any type 
of data in any field.  So, SQLite has to decide whether to convert the left 
side to a string or the right side to a datetime.  It appears that it is 
choosing to convert the left side.  And, going by what I saw in SQLiteSpy, the 
first character of the conversion is '2', which is less than '4', so this row 
is deleted.

Do I merely have to use the datetime() method on the right side as well?

delete from trend_data where datetime(value_timestamp) < datetime('4/7/2009 
12:37:32 PM')

Or is there something else I should be doing?

Thanks very much!

RobR


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

Re: [sqlite] SQLite version 3.6.14 and async vfs

[Virgilio Alexandre Fornazin]

This break purpose of VFS, all VFS should work in same way, you must not
know if your VFS is asynchronous
or not. VFS close method should wait for all file I/O on this database
handle (not all databases) to 
finalize before returning, providing compatibility with all other existing
VFS implementations.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ken
Sent: quinta-feira, 7 de maio de 2009 13:47
To: General Discussion of SQLite Database; Pavel Ivanov
Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs


I see the confusion with the word "Shutdown".

How about but a call that would block until the async thread completes all
operations that are enqueued. Effectively a Close of the async thread/queue
and db. The call could be sqlite3Async_close.

Hope that clarifies my intent. 


--- On Thu, 5/7/09, Pavel Ivanov  wrote:

> From: Pavel Ivanov 
> Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs
> To: kennethinbox-sql...@yahoo.com, "General Discussion of SQLite Database"

> Date: Thursday, May 7, 2009, 11:10 AM
> Shutdown is not an option at all. I
> need vfs to continue working on
> other databases but to be notified (or have possibility to
> check) when
> one particular database is no longer opened.
> 
> Pavel
> 
> On Thu, May 7, 2009 at 12:00 PM, Ken 
> wrote:
> >
> > --- On Thu, 5/7/09, Virgilio Alexandre Fornazin

> wrote:
> >
> >> From: Virgilio Alexandre Fornazin 
> >> Subject: Re: [sqlite] SQLite version 3.6.14 and
> async vfs
> >> To: "'General Discussion of SQLite Database'"
> 
> >> Date: Thursday, May 7, 2009, 10:50 AM
> >> Close should wait for all file
> >> operations complete to meet that needs.
> >> I think asynchronous VFS should take care of
> waiting in
> >> sqlite3_close()
> >> call.
> >>
> >> -Original Message-
> >> From: sqlite-users-boun...@sqlite.org
> >> [mailto:sqlite-users-boun...@sqlite.org]
> >> On Behalf Of Pavel Ivanov
> >> Sent: quinta-feira, 7 de maio de 2009 12:33
> >> To: General Discussion of SQLite Database
> >> Subject: Re: [sqlite] SQLite version 3.6.14 and
> async vfs
> >>
> >> Hi!
> >>
> >> It's great to hear about performance improvements
> and
> >> especially about
> >> asynchronous I/O extension. Thank you very much
> for your
> >> work!
> >>
> >> I have one question though: taking quick look at
> the
> >> sources of async
> >> vfs I've noticed that even closing the file is
> just a task
> >> in the
> >> async queue and thus after closing sqlite
> connection file
> >> remains
> >> opened for some time. It sounds pretty reasonable,
> but here
> >> stands the
> >> question: what if I want to do something with the
> database
> >> file after
> >> I close sqlite connection to it (e.g. move to the
> archive
> >> directory,
> >> zip it etc.)? With sync vfs I could be sure that
> after
> >> closing
> >> connection file is closed and I can do with it
> whatever I
> >> want. Is
> >> there a way to catch the moment of actual file
> closing with
> >> async vfs?
> >>
> >> And another question just to be sure that I
> understand it
> >> correctly:
> >> async vfs holds only one queue for all opened
> database
> >> files, right?
> >>
> >> Pavel
> >>
> >> On Wed, May 6, 2009 at 10:36 PM, D. Richard Hipp
> 
> >> wrote:
> >> > SQLite version 3.6.14 is now available on the
> SQLite
> >> website
> >> >
> >> >     http://www.sqlite.org/
> >> >
> >> > Version 3.6.14 contains performance enhances
> in the
> >> btree and pager
> >> > subsystems.  In addition, the query
> optimizer now
> >> knows how to take
> >> > advantage of OR and IN operators on columns
> of a
> >> virtual table.
> >> >
> >> > A new optional extension is included that
> implements
> >> an asynchronous I/
> >> > O backend for SQLite on either windows or
> unix.  The
> >> asynchronous I/O
> >> > backend processes all writes using a
> background
> >> thread.  This gives
> >> > the appearance of faster response time at the
> cost of
> >> durability and
> >> > additional memory usage.  See http://www.sqlite.org/asyncvfs.html for
> >> > additional information.
> >> >
> >> > This release also includes many small bug
> fixes and
> >> documentation
> >> > improvements.
> >> >
> >> > As always, please let me know if you
> encounter any
> >> difficulties.
> >> >
> >> > D. Richard Hipp
> >> > d...@hwaci.com
> >> >
> >> >
> >
> > Without actually looking at the async code I think
> that instead of using the sqlite3_close to cause a block
> there should be a "shutdown" that would wait for the
> shutdown of the async thread to complete. So maybe a better
> name would be sqlite3Async_close or something similar.
> >
> > Ken
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > 

Re: [sqlite] SQLite version 3.6.14 and async vfs

[Pavel Ivanov]

According to the fact that queue in async vfs is one for all databases
and along with closing of one database there could be some writings to
another ones, method of catching the return from sqlite3async_run()
can be significantly delayed if work at all...

Ok, thank you for the extension anyway. I will think what can I do in
this situation.


Pavel

On Thu, May 7, 2009 at 12:54 PM, Dan  wrote:
>
> On May 7, 2009, at 10:33 PM, Pavel Ivanov wrote:
>
>> Hi!
>>
>> It's great to hear about performance improvements and especially about
>> asynchronous I/O extension. Thank you very much for your work!
>>
>> I have one question though: taking quick look at the sources of async
>> vfs I've noticed that even closing the file is just a task in the
>> async queue and thus after closing sqlite connection file remains
>> opened for some time. It sounds pretty reasonable, but here stands the
>> question: what if I want to do something with the database file after
>> I close sqlite connection to it (e.g. move to the archive directory,
>> zip it etc.)? With sync vfs I could be sure that after closing
>> connection file is closed and I can do with it whatever I want. Is
>> there a way to catch the moment of actual file closing with async vfs?
>
> Not easily. With the current code you could call sqlite3async_control()
> to configure the background thread to return when the write-queue is
> empty
> (SQLITEASYNC_HALT_IDLE). When the call to sqlite3async_run() returns you
> can be sure that the queue is empty and thus any close-file operation
> must
> have been flushed through.
>
>> And another question just to be sure that I understand it correctly:
>> async vfs holds only one queue for all opened database files, right?
>
> True statement.
>
> Dan.
>
>
> ___
> 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] SQLite version 3.6.14 and async vfs

[Dan]

On May 7, 2009, at 10:33 PM, Pavel Ivanov wrote:

> Hi!
>
> It's great to hear about performance improvements and especially about
> asynchronous I/O extension. Thank you very much for your work!
>
> I have one question though: taking quick look at the sources of async
> vfs I've noticed that even closing the file is just a task in the
> async queue and thus after closing sqlite connection file remains
> opened for some time. It sounds pretty reasonable, but here stands the
> question: what if I want to do something with the database file after
> I close sqlite connection to it (e.g. move to the archive directory,
> zip it etc.)? With sync vfs I could be sure that after closing
> connection file is closed and I can do with it whatever I want. Is
> there a way to catch the moment of actual file closing with async vfs?

Not easily. With the current code you could call sqlite3async_control()
to configure the background thread to return when the write-queue is  
empty
(SQLITEASYNC_HALT_IDLE). When the call to sqlite3async_run() returns you
can be sure that the queue is empty and thus any close-file operation  
must
have been flushed through.

> And another question just to be sure that I understand it correctly:
> async vfs holds only one queue for all opened database files, right?

True statement.

Dan.


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

Re: [sqlite] SQLite version 3.6.14 and async vfs

[Ken]

I see the confusion with the word "Shutdown".

How about but a call that would block until the async thread completes all 
operations that are enqueued. Effectively a Close of the async thread/queue and 
db. The call could be sqlite3Async_close.

Hope that clarifies my intent. 


--- On Thu, 5/7/09, Pavel Ivanov  wrote:

> From: Pavel Ivanov 
> Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs
> To: kennethinbox-sql...@yahoo.com, "General Discussion of SQLite Database" 
> 
> Date: Thursday, May 7, 2009, 11:10 AM
> Shutdown is not an option at all. I
> need vfs to continue working on
> other databases but to be notified (or have possibility to
> check) when
> one particular database is no longer opened.
> 
> Pavel
> 
> On Thu, May 7, 2009 at 12:00 PM, Ken 
> wrote:
> >
> > --- On Thu, 5/7/09, Virgilio Alexandre Fornazin 
> wrote:
> >
> >> From: Virgilio Alexandre Fornazin 
> >> Subject: Re: [sqlite] SQLite version 3.6.14 and
> async vfs
> >> To: "'General Discussion of SQLite Database'"
> 
> >> Date: Thursday, May 7, 2009, 10:50 AM
> >> Close should wait for all file
> >> operations complete to meet that needs.
> >> I think asynchronous VFS should take care of
> waiting in
> >> sqlite3_close()
> >> call.
> >>
> >> -Original Message-
> >> From: sqlite-users-boun...@sqlite.org
> >> [mailto:sqlite-users-boun...@sqlite.org]
> >> On Behalf Of Pavel Ivanov
> >> Sent: quinta-feira, 7 de maio de 2009 12:33
> >> To: General Discussion of SQLite Database
> >> Subject: Re: [sqlite] SQLite version 3.6.14 and
> async vfs
> >>
> >> Hi!
> >>
> >> It's great to hear about performance improvements
> and
> >> especially about
> >> asynchronous I/O extension. Thank you very much
> for your
> >> work!
> >>
> >> I have one question though: taking quick look at
> the
> >> sources of async
> >> vfs I've noticed that even closing the file is
> just a task
> >> in the
> >> async queue and thus after closing sqlite
> connection file
> >> remains
> >> opened for some time. It sounds pretty reasonable,
> but here
> >> stands the
> >> question: what if I want to do something with the
> database
> >> file after
> >> I close sqlite connection to it (e.g. move to the
> archive
> >> directory,
> >> zip it etc.)? With sync vfs I could be sure that
> after
> >> closing
> >> connection file is closed and I can do with it
> whatever I
> >> want. Is
> >> there a way to catch the moment of actual file
> closing with
> >> async vfs?
> >>
> >> And another question just to be sure that I
> understand it
> >> correctly:
> >> async vfs holds only one queue for all opened
> database
> >> files, right?
> >>
> >> Pavel
> >>
> >> On Wed, May 6, 2009 at 10:36 PM, D. Richard Hipp
> 
> >> wrote:
> >> > SQLite version 3.6.14 is now available on the
> SQLite
> >> website
> >> >
> >> >     http://www.sqlite.org/
> >> >
> >> > Version 3.6.14 contains performance enhances
> in the
> >> btree and pager
> >> > subsystems.  In addition, the query
> optimizer now
> >> knows how to take
> >> > advantage of OR and IN operators on columns
> of a
> >> virtual table.
> >> >
> >> > A new optional extension is included that
> implements
> >> an asynchronous I/
> >> > O backend for SQLite on either windows or
> unix.  The
> >> asynchronous I/O
> >> > backend processes all writes using a
> background
> >> thread.  This gives
> >> > the appearance of faster response time at the
> cost of
> >> durability and
> >> > additional memory usage.  See http://www.sqlite.org/asyncvfs.html for
> >> > additional information.
> >> >
> >> > This release also includes many small bug
> fixes and
> >> documentation
> >> > improvements.
> >> >
> >> > As always, please let me know if you
> encounter any
> >> difficulties.
> >> >
> >> > D. Richard Hipp
> >> > d...@hwaci.com
> >> >
> >> >
> >
> > Without actually looking at the async code I think
> that instead of using the sqlite3_close to cause a block
> there should be a "shutdown" that would wait for the
> shutdown of the async thread to complete. So maybe a better
> name would be sqlite3Async_close or something similar.
> >
> > Ken
> >
> >
> > ___
> > 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] SQLite version 3.6.14 and async vfs

[Pavel Ivanov]

Shutdown is not an option at all. I need vfs to continue working on
other databases but to be notified (or have possibility to check) when
one particular database is no longer opened.

Pavel

On Thu, May 7, 2009 at 12:00 PM, Ken  wrote:
>
> --- On Thu, 5/7/09, Virgilio Alexandre Fornazin  
> wrote:
>
>> From: Virgilio Alexandre Fornazin 
>> Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs
>> To: "'General Discussion of SQLite Database'" 
>> Date: Thursday, May 7, 2009, 10:50 AM
>> Close should wait for all file
>> operations complete to meet that needs.
>> I think asynchronous VFS should take care of waiting in
>> sqlite3_close()
>> call.
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org
>> [mailto:sqlite-users-boun...@sqlite.org]
>> On Behalf Of Pavel Ivanov
>> Sent: quinta-feira, 7 de maio de 2009 12:33
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs
>>
>> Hi!
>>
>> It's great to hear about performance improvements and
>> especially about
>> asynchronous I/O extension. Thank you very much for your
>> work!
>>
>> I have one question though: taking quick look at the
>> sources of async
>> vfs I've noticed that even closing the file is just a task
>> in the
>> async queue and thus after closing sqlite connection file
>> remains
>> opened for some time. It sounds pretty reasonable, but here
>> stands the
>> question: what if I want to do something with the database
>> file after
>> I close sqlite connection to it (e.g. move to the archive
>> directory,
>> zip it etc.)? With sync vfs I could be sure that after
>> closing
>> connection file is closed and I can do with it whatever I
>> want. Is
>> there a way to catch the moment of actual file closing with
>> async vfs?
>>
>> And another question just to be sure that I understand it
>> correctly:
>> async vfs holds only one queue for all opened database
>> files, right?
>>
>> Pavel
>>
>> On Wed, May 6, 2009 at 10:36 PM, D. Richard Hipp 
>> wrote:
>> > SQLite version 3.6.14 is now available on the SQLite
>> website
>> >
>> >     http://www.sqlite.org/
>> >
>> > Version 3.6.14 contains performance enhances in the
>> btree and pager
>> > subsystems.  In addition, the query optimizer now
>> knows how to take
>> > advantage of OR and IN operators on columns of a
>> virtual table.
>> >
>> > A new optional extension is included that implements
>> an asynchronous I/
>> > O backend for SQLite on either windows or unix.  The
>> asynchronous I/O
>> > backend processes all writes using a background
>> thread.  This gives
>> > the appearance of faster response time at the cost of
>> durability and
>> > additional memory usage.  See http://www.sqlite.org/asyncvfs.html for
>> > additional information.
>> >
>> > This release also includes many small bug fixes and
>> documentation
>> > improvements.
>> >
>> > As always, please let me know if you encounter any
>> difficulties.
>> >
>> > D. Richard Hipp
>> > d...@hwaci.com
>> >
>> >
>
> Without actually looking at the async code I think that instead of using the 
> sqlite3_close to cause a block there should be a "shutdown" that would wait 
> for the shutdown of the async thread to complete. So maybe a better name 
> would be sqlite3Async_close or something similar.
>
> Ken
>
>
> ___
> 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] SQLite version 3.6.14 and async vfs

[Ken]

--- On Thu, 5/7/09, Virgilio Alexandre Fornazin  
wrote:

> From: Virgilio Alexandre Fornazin 
> Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs
> To: "'General Discussion of SQLite Database'" 
> Date: Thursday, May 7, 2009, 10:50 AM
> Close should wait for all file
> operations complete to meet that needs.
> I think asynchronous VFS should take care of waiting in
> sqlite3_close()
> call.
> 
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org]
> On Behalf Of Pavel Ivanov
> Sent: quinta-feira, 7 de maio de 2009 12:33
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs
> 
> Hi!
> 
> It's great to hear about performance improvements and
> especially about
> asynchronous I/O extension. Thank you very much for your
> work!
> 
> I have one question though: taking quick look at the
> sources of async
> vfs I've noticed that even closing the file is just a task
> in the
> async queue and thus after closing sqlite connection file
> remains
> opened for some time. It sounds pretty reasonable, but here
> stands the
> question: what if I want to do something with the database
> file after
> I close sqlite connection to it (e.g. move to the archive
> directory,
> zip it etc.)? With sync vfs I could be sure that after
> closing
> connection file is closed and I can do with it whatever I
> want. Is
> there a way to catch the moment of actual file closing with
> async vfs?
> 
> And another question just to be sure that I understand it
> correctly:
> async vfs holds only one queue for all opened database
> files, right?
> 
> Pavel
> 
> On Wed, May 6, 2009 at 10:36 PM, D. Richard Hipp 
> wrote:
> > SQLite version 3.6.14 is now available on the SQLite
> website
> >
> >     http://www.sqlite.org/
> >
> > Version 3.6.14 contains performance enhances in the
> btree and pager
> > subsystems.  In addition, the query optimizer now
> knows how to take
> > advantage of OR and IN operators on columns of a
> virtual table.
> >
> > A new optional extension is included that implements
> an asynchronous I/
> > O backend for SQLite on either windows or unix.  The
> asynchronous I/O
> > backend processes all writes using a background
> thread.  This gives
> > the appearance of faster response time at the cost of
> durability and
> > additional memory usage.  See http://www.sqlite.org/asyncvfs.html for
> > additional information.
> >
> > This release also includes many small bug fixes and
> documentation
> > improvements.
> >
> > As always, please let me know if you encounter any
> difficulties.
> >
> > D. Richard Hipp
> > d...@hwaci.com
> >
> >

Without actually looking at the async code I think that instead of using the 
sqlite3_close to cause a block there should be a "shutdown" that would wait for 
the shutdown of the async thread to complete. So maybe a better name would be 
sqlite3Async_close or something similar.

Ken


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

Re: [sqlite] SQLite version 3.6.14 and async vfs

[Virgilio Alexandre Fornazin]

Close should wait for all file operations complete to meet that needs.
I think asynchronous VFS should take care of waiting in sqlite3_close()
call.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
Sent: quinta-feira, 7 de maio de 2009 12:33
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite version 3.6.14 and async vfs

Hi!

It's great to hear about performance improvements and especially about
asynchronous I/O extension. Thank you very much for your work!

I have one question though: taking quick look at the sources of async
vfs I've noticed that even closing the file is just a task in the
async queue and thus after closing sqlite connection file remains
opened for some time. It sounds pretty reasonable, but here stands the
question: what if I want to do something with the database file after
I close sqlite connection to it (e.g. move to the archive directory,
zip it etc.)? With sync vfs I could be sure that after closing
connection file is closed and I can do with it whatever I want. Is
there a way to catch the moment of actual file closing with async vfs?

And another question just to be sure that I understand it correctly:
async vfs holds only one queue for all opened database files, right?

Pavel

On Wed, May 6, 2009 at 10:36 PM, D. Richard Hipp  wrote:
> SQLite version 3.6.14 is now available on the SQLite website
>
>     http://www.sqlite.org/
>
> Version 3.6.14 contains performance enhances in the btree and pager
> subsystems.  In addition, the query optimizer now knows how to take
> advantage of OR and IN operators on columns of a virtual table.
>
> A new optional extension is included that implements an asynchronous I/
> O backend for SQLite on either windows or unix.  The asynchronous I/O
> backend processes all writes using a background thread.  This gives
> the appearance of faster response time at the cost of durability and
> additional memory usage.  See http://www.sqlite.org/asyncvfs.html for
> additional information.
>
> This release also includes many small bug fixes and documentation
> improvements.
>
> As always, please let me know if you encounter any difficulties.
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> 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] select performance with join

[Igor Tandetnik]

Andrea Galeazzi  wrote:
> but when I execute:
>
> SELECT S.id,title,artist,bpm,name
>
> FROM Song AS S
>
> LEFT JOIN Genre AS G ON (S.genre_id = G.id)
>
> WHERE name<= 'zUmM' AND (name< 'zUmM' OR S.id< 8122)
>
> ORDER BY name DESC, S.id DESC LIMIT 20;

Note that LEFT JOIN is pointless here, since any record with G.name=NULL 
won't make it past the WHERE clause. Replace it with plain JOIN, you 
should see an improvement.

Igor Tandetnik 



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

Re: [sqlite] SQLite version 3.6.14 and async vfs

[Pavel Ivanov]

Hi!

It's great to hear about performance improvements and especially about
asynchronous I/O extension. Thank you very much for your work!

I have one question though: taking quick look at the sources of async
vfs I've noticed that even closing the file is just a task in the
async queue and thus after closing sqlite connection file remains
opened for some time. It sounds pretty reasonable, but here stands the
question: what if I want to do something with the database file after
I close sqlite connection to it (e.g. move to the archive directory,
zip it etc.)? With sync vfs I could be sure that after closing
connection file is closed and I can do with it whatever I want. Is
there a way to catch the moment of actual file closing with async vfs?

And another question just to be sure that I understand it correctly:
async vfs holds only one queue for all opened database files, right?

Pavel

On Wed, May 6, 2009 at 10:36 PM, D. Richard Hipp  wrote:
> SQLite version 3.6.14 is now available on the SQLite website
>
>     http://www.sqlite.org/
>
> Version 3.6.14 contains performance enhances in the btree and pager
> subsystems.  In addition, the query optimizer now knows how to take
> advantage of OR and IN operators on columns of a virtual table.
>
> A new optional extension is included that implements an asynchronous I/
> O backend for SQLite on either windows or unix.  The asynchronous I/O
> backend processes all writes using a background thread.  This gives
> the appearance of faster response time at the cost of durability and
> additional memory usage.  See http://www.sqlite.org/asyncvfs.html for
> additional information.
>
> This release also includes many small bug fixes and documentation
> improvements.
>
> As always, please let me know if you encounter any difficulties.
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> 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] select performance with join

[Andrea Galeazzi]

Hi guys,

I've got a big problem about select performance on an left join. I have 
two tables:

CREATE TABLE Song (

id INTEGER NOT NULL UNIQUE,

title VARCHAR(40) NULL COLLATE NOCASE,

artist VARCHAR(40) NULL COLLATE NOCASE,

bpm INT NULL,

genre_id INT NULL,

PRIMARY KEY (id),

CONSTRAINT fk_Genre FOREIGN KEY (genre_id)

REFERENCES Song (id)

ON DELETE SET NULL

ON UPDATE CASCADE);

-- Indeces

CREATE INDEX Song_title_idx ON Song(title);

CREATE INDEX Song_artist_idx ON Song(artist);

CREATE INDEX Song_bpm_idx ON Song(bpm);

CREATE INDEX Song_genre_idx ON Song(genre_id);


CREATE TABLE Genre (

id INTEGER NOT NULL UNIQUE,

name VARCHAR(20) NOT NULL COLLATE NOCASE,

image_id INT NOT NULL,

PRIMARY KEY (id),

CONSTRAINT fk_Image2 FOREIGN KEY (image_id)

REFERENCES Genre (id)

ON DELETE SET NULL

ON UPDATE CASCADE);


-- Indeces

CREATE INDEX Genre_name_idx ON Genre(name);

-

Now when I execute this query:

SELECT S.id,title,artist,bpm,name

FROM Song AS S

LEFT JOIN Genre AS G ON (S.genre_id = G.id)

WHERE title<= 'zzX_5238' AND (title< 'zzX_5238' OR S.id< 5238)

ORDER BY title DESC, S.id DESC LIMIT 20;

it takes only 200 ms

but when I execute:

SELECT S.id,title,artist,bpm,name

FROM Song AS S

LEFT JOIN Genre AS G ON (S.genre_id = G.id)

WHERE name<= 'zUmM' AND (name< 'zUmM' OR S.id< 8122)

ORDER BY name DESC, S.id DESC LIMIT 20;


it takes 8100! It's a huge time for our application!

I also noticed that the Genre_name_idx it's useless. Song has 1 
records, Genre has 100 records.

Does anyone have any ideas about how to improve the previous query?

Thanks

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

Re: [sqlite] Combining .output and .read in a batch file

[Leo Freitag]

Slightly modified it works:

=== batchfile
REM the read-statement with quotes
sqlite3 test.db ".read r1.sql"

=== r1.sql
REM the following line without quotes
.output o1.txt
select * from table1;

Thanks a lot.
Leo

Simon Davies schrieb:
> 2009/5/7 Leo Freitag :
>   
>> Hallo,
>>
>> I try to get run the following in a batch file
>>
>> Open database "test.db"
>> Set output to "o.txt"
>> Read sql-statement form "r.sql"
>>
>> === r.sql - Start ===
>> select * from table1;
>> === r.sql - End ===
>>
>> Thinks like the following didn't work:
>> sqlite3 test.db .output o.txt .read r.sql
>> 
>
> Only 1 dot command can be supplied in the first command string.
>
> Make the first line of r.sql ".output o.txt"
>
> Then:
>sqlite3 test.db ".read r.sql"
>
>   
>> Thanks for your help.
>> Leo
>>
>> 
>
> Rgds,
> 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] import / insert 120k records

[Swithun Crowe]

Hello

BM Whats the best way to import a large number of records ?
BM 120K+ currently stored as an XML file.

If your XML data is data-centric, then it should fit into one or more 
tables quite easily.

If it can fit into one table, then you could use XSLT to convert the XML 
to CSV and .import that into the table you've created.

If you need several tables, or because your number of records is large, 
you could use a SAX implementation (e.g. in Python or PHP) and parse the 
XML, and bind the values you get from it to placeholders in prepared 
statements.

If the XML is more document-centric, then "adjacency model" and "nested 
set" were suggested recently as things to investigate for turning a tree 
structure into something tabular. 

There are probably lots of other ways to do it, depending on what 
languages you want to employ.

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

Re: [sqlite] import / insert 120k records

[Alexander A. Gnatyna]

On Thursday 07 May 2009 15:42:52 Ben Marchbanks wrote:
> Whats the best way to import a large number of records ?
> 120K+ currently stored as an XML file.

Parse XML with perl using for example XML::Twig and insert into database( with 
transactions of course).

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

[sqlite] import / insert 120k records

[Ben Marchbanks]

Whats the best way to import a large number of records ?
120K+ currently stored as an XML file.

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

Re: [sqlite] Combining .output and .read in a batch file

[Leo Freitag]

Yes, that's what I wanted to do. But even with that syntax there is no 
output.
Any other ideas?

Leo

Pavel Ivanov schrieb:
> Maybe you want to do
> sqlite3 test.db ".output o.txt; .read r.sql"
>
> Pavel
>
> On Thu, May 7, 2009 at 6:44 AM, Leo Freitag  wrote:
>   
>> Hallo,
>>
>> I try to get run the following in a batch file
>>
>> Open database "test.db"
>> Set output to "o.txt"
>> Read sql-statement form "r.sql"
>>
>> === r.sql - Start ===
>> select * from table1;
>> === r.sql - End ===
>>
>> Thinks like the following didn't work:
>> sqlite3 test.db .output o.txt .read r.sql
>>
>>
>> Thanks for your help.
>> Leo
>>
>>
>>
>> ___
>> 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] Combining .output and .read in a batch file

[Simon Davies]

2009/5/7 Leo Freitag :
> Hallo,
>
> I try to get run the following in a batch file
>
> Open database "test.db"
> Set output to "o.txt"
> Read sql-statement form "r.sql"
>
> === r.sql - Start ===
> select * from table1;
> === r.sql - End ===
>
> Thinks like the following didn't work:
> sqlite3 test.db .output o.txt .read r.sql

Only 1 dot command can be supplied in the first command string.

Make the first line of r.sql ".output o.txt"

Then:
   sqlite3 test.db ".read r.sql"

>
>
> Thanks for your help.
> Leo
>

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

Re: [sqlite] Combining .output and .read in a batch file

[Pavel Ivanov]

Maybe you want to do
sqlite3 test.db ".output o.txt; .read r.sql"

Pavel

On Thu, May 7, 2009 at 6:44 AM, Leo Freitag  wrote:
> Hallo,
>
> I try to get run the following in a batch file
>
> Open database "test.db"
> Set output to "o.txt"
> Read sql-statement form "r.sql"
>
> === r.sql - Start ===
> select * from table1;
> === r.sql - End ===
>
> Thinks like the following didn't work:
> sqlite3 test.db .output o.txt .read r.sql
>
>
> Thanks for your help.
> Leo
>
>
>
> ___
> 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] Combining .output and .read in a batch file

[Leo Freitag]

Hallo,

I try to get run the following in a batch file

Open database "test.db"
Set output to "o.txt"
Read sql-statement form "r.sql"

=== r.sql - Start ===
select * from table1;
=== r.sql - End ===

Thinks like the following didn't work:
sqlite3 test.db .output o.txt .read r.sql


Thanks for your help.
Leo



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

Re: [sqlite] SQLite version 3.6.14

[Lukas Gebauer]

> A new optional extension is included that implements an asynchronous
> I/ O backend for SQLite on either windows or unix.  The asynchronous
> I/O  backend processes all writes using a background thread.  This
> gives  the appearance of faster response time at the cost of
> durability and  additional memory usage.  See
> http://www.sqlite.org/asyncvfs.html for  additional information.

Is this extension compiled in your Win32 DLL binary?

Thanks!


-- 
Lukas Gebauer.

E-mail: gebau...@mlp.cz
http://synapse.ararat.cz/ - Ararat Synapse - TCP/IP Lib.

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

Re: [sqlite] SQLite version 3.6.14

[Alexey Pechnikov]

Hello!

On Thursday 07 May 2009 06:36:50 D. Richard Hipp wrote:
> See http://www.sqlite.org/asyncvfs.html for  
> additional information.

Can you add some diagramms of async I/O database usage?
And usage scenarios may be very useful. Now I'm don't
undertand when can applications to get help from this.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.6.14

[Jules Colding]

On 07/05/2009, at 04.36, D. Richard Hipp wrote:

> SQLite version 3.6.14 is now available on the SQLite website
>
> http://www.sqlite.org/
>
> Version 3.6.14 contains performance enhances in the btree and pager
> subsystems.  In addition, the query optimizer now knows how to take
> advantage of OR and IN operators on columns of a virtual table.
>
> A new optional extension is included that implements an asynchronous  
> I/
> O backend for SQLite on either windows or unix.  The asynchronous I/O
> backend processes all writes using a background thread.  This gives
> the appearance of faster response time at the cost of durability and
> additional memory usage.  See http://www.sqlite.org/asyncvfs.html for
> additional information.
>
> This release also includes many small bug fixes and documentation
> improvements.
>
> As always, please let me know if you encounter any difficulties.

Only a compile warning:

sqlite3.m: In function 'proxyGetLockPath':
sqlite3.m:25276: warning: unused variable 'err'


Easily fixed with:


Index: sqlite3.m
===
--- sqlite3.m   (revision 581)
+++ sqlite3.m   (working copy)
@@ -25273,8 +25273,8 @@
  len = strlcat(lPath, "sqliteplocks", maxLen);
  if( mkdir(lPath, SQLITE_DEFAULT_PROXYDIR_PERMISSIONS) ){
/* if mkdir fails, handle as lock file creation failure */
+#  ifdef SQLITE_DEBUG
int err = errno;
-#  ifdef SQLITE_DEBUG
if( err!=EEXIST ){
  fprintf(stderr, "proxyGetLockPath: mkdir(%s,0%o) error %d %s 
\n", lPath,
  SQLITE_DEFAULT_PROXYDIR_PERMISSIONS, err,  
strerror(err));


Thanks,
   jules

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

Re: [sqlite] group_concat bug

[Ralf Junker]

Confirmed and created ticket http://www.sqlite.org/cvstrac/tktview?tn=3841.

Ralf

At 03:10 07.05.2009, Steve Bauer wrote:
>The following example demonstrates what seems to be a bug in group_concat.  
>With the latest version of CVS:
>
>CREATE TABLE example (id INTEGER, x TEXT);
>CREATE TABLE table2 (key TEXT, x TEXT);
>CREATE TABLE list (key TEXT, value TEXT);
>
>INSERT INTO example VALUES (1, "a");
>INSERT INTO table2 VALUES ("a", "alist");
>INSERT INTO table2 VALUES ("b", "blist");
>INSERT INTO list VALUES ("a", 1);
>INSERT INTO list VALUES ("a", 2);
>INSERT INTO list VALUES ("a", 3);
>INSERT INTO list VALUES ("b", 4);
>INSERT INTO list VALUES ("b", 5);
>INSERT INTO list VALUES ("b", 6);
>
>SELECT example.id,
>   table2.x,
>   (SELECT group_concat(list.value) FROM list WHERE list.key = table2.key)
>FROM example, table2;
>
>Output:
>
>1|alist|1,2,3
>1|blist|,4,5,6
>
>I expected:
>
>1|alist|1,2,3
>1|blist|4,5,6

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