Re: [sqlite] sqlite3_open_v2 and SQLITE_BUSY
On May 8, 2009, at 4:37 AM, Igor Tandetnik wrote: > 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). The file is opened when sqlite3_open_v2() is called. And I think a couple of trivial methods are called - xSectorSize() and xDeviceCharacteristics(). But that's all, nothing is read, written or locked until later on. So you won't get SQLITE_BUSY back from sqlite3_open_v2(). You might manage an SQLITE_CANTOPEN if you tried to open a directory or, depending on the flags passed to open_v2(), a file that does not exist. Dan. > 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] SQLite3.exe console shell program run on Windows CE x86 platform
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
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&displaylang=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?
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
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
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
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
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
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
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
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?
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?
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?
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?
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
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?
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
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 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 > > >> asy
Re: [sqlite] Best way to do a date comparison?
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?
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
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 > > 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/
Re: [sqlite] SQLite version 3.6.14 and async vfs
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
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
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
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
--- 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
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
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
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
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
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
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
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
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
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
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
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
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
> 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
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
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
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