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

2009-05-07 Thread 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

Re: [sqlite] SQLIte Related

2009-05-07 Thread 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

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

2009-05-07 Thread 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

[sqlite] SQLIte Related

2009-05-07 Thread 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,

Re: [sqlite] sqlite3_prepare returns SQLITE_MISUSE

2009-05-07 Thread 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

Re: [sqlite] sqlite3_open_v2 and SQLITE_BUSY

2009-05-07 Thread 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

[sqlite] sqlite3_open_v2 and SQLITE_BUSY

2009-05-07 Thread 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

Re: [sqlite] import / insert 120k records

2009-05-07 Thread 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

Re: [sqlite] import / insert 120k records

2009-05-07 Thread 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

Re: [sqlite] import / insert 120k records

2009-05-07 Thread 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

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

2009-05-07 Thread 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?

2009-05-07 Thread 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

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

2009-05-07 Thread 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

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

2009-05-07 Thread 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

Re: [sqlite] SQLite version 3.6.14

2009-05-07 Thread 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

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

2009-05-07 Thread 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

Re: [sqlite] SQLite version 3.6.14 and async vfs

2009-05-07 Thread 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

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

2009-05-07 Thread 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

[sqlite] Best way to do a date comparison?

2009-05-07 Thread 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

Re: [sqlite] SQLite version 3.6.14 and async vfs

2009-05-07 Thread 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

Re: [sqlite] SQLite version 3.6.14 and async vfs

2009-05-07 Thread 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.

Re: [sqlite] SQLite version 3.6.14 and async vfs

2009-05-07 Thread 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

Re: [sqlite] SQLite version 3.6.14 and async vfs

2009-05-07 Thread 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,

Re: [sqlite] SQLite version 3.6.14 and async vfs

2009-05-07 Thread 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,

Re: [sqlite] SQLite version 3.6.14 and async vfs

2009-05-07 Thread 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:

Re: [sqlite] SQLite version 3.6.14 and async vfs

2009-05-07 Thread 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:

Re: [sqlite] select performance with join

2009-05-07 Thread 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

Re: [sqlite] SQLite version 3.6.14 and async vfs

2009-05-07 Thread 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

[sqlite] select performance with join

2009-05-07 Thread 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

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

2009-05-07 Thread 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 : > >>

Re: [sqlite] import / insert 120k records

2009-05-07 Thread 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

Re: [sqlite] import / insert 120k records

2009-05-07 Thread 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] import / insert 120k records

2009-05-07 Thread 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

2009-05-07 Thread 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: > >>

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

2009-05-07 Thread 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

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

2009-05-07 Thread 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"

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

2009-05-07 Thread 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

Re: [sqlite] SQLite version 3.6.14

2009-05-07 Thread 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

Re: [sqlite] SQLite version 3.6.14

2009-05-07 Thread 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

Re: [sqlite] SQLite version 3.6.14

2009-05-07 Thread 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

Re: [sqlite] group_concat bug

2009-05-07 Thread 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