Re: [sqlite] Will a read-only transaction cause writes to the WAL?
Just curious why you wrap your SELECT statement into a 'TRANSACTION' ... -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Martin C. Sent: quinta-feira, 16 de setembro de 2010 14:16 To: sqlite-users@sqlite.org Subject: [sqlite] Will a read-only transaction cause writes to the WAL? Hi, I am using SQLite on a NAND based embedded system, and to ensure the lifetime of the NAND I need to optimize writes to the NAND. Can you tell me, if a read-only transaction, by which I mean BEGIN TRANSACTION SELECT * from SomeTable; COMMIT will cause any writes to the WAL? What about the -shm file, is it likely that the transaction will cause a write to the NAND in this case? Is there any statistics about actual writes I can query to analyze write-behavior of my software? Thanks in advance! Best regards, Martin ___ 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] Query critique
Calling UPPER() for each field/value while querying is a pain, if you can assume the names are all in uppercase (forcing this in your program before passing them to SQlite), you could speed up things a lot. But you need to analyze what´s happening inside SQlite to check if indices are used, etc. SQlite command line app has a command that does this (does not remember if it´s this: ".explain ON|OFFTurn output mode suitable for EXPLAIN on or off.\n") 'ANALYZE' SQlite command also performs some kind of prefetching of indexes and loads results in a internal table, can help also to speed up. Also, try to avoid JOINS like this 'SELECT a.* FROM globalRankingTable a, friendTable b WHERE upper(b.player) = upper('?') AND upper(b.friend) = upper(a.name))' It´s better to explicit use LEFT JOIN / INNER JOIN always. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns Sent: sábado, 10 de julho de 2010 17:39 To: General Discussion of SQLite Database Subject: Re: [sqlite] Query critique -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/09/2010 01:54 AM, Ian Hardingham wrote: > And here is my query (written in a script language): Are you actually getting correct answers from this? > upper('?') Unless your script language includes a SQL parser, that does not do what you think it does. It is seeing if the value matches upper cased question mark. It looks like you wanted to supply bound parameters so the question mark should not be in quotes. I'd also suggest using a better wrapper or scripting language since you are providing three bindings but the query is only using one of them. It should complain. > - yes I know I'm an idiot with the upper stuff, a refactor is needed on > that - I know all of my "name" fields should really be integers. Or look into indices and collations. In any event you should always post your exact actual query so we don't have to figure out issues like the question marks are intentional or transcription errors. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkw42kcACgkQmOOfHg372QSgxQCfTtgfYFMZ9a4nHtbJn8gI7XEK 7wYAn3ghSgc3wPgl0bFGLq8oA2moTJcs =aCUo -END PGP SIGNATURE- ___ 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] Null character problem
>> My question came purely from a mild curiosity. I was wondering about >> the behavior of sqlite call sqlite3_bind_text when it is passed a range >> of BYTES that includes nulls. See this snipper for documentation: "The third argument is the value to bind to the parameter. In those routines that have a fourth argument, its value is the number of bytes in the parameter. To be clear: the value is the number of bytes in the value, not the number of characters. If the fourth parameter is negative, the length of the string is the number of bytes up to the first zero terminator. The fifth argument to sqlite3_bind_blob(), sqlite3_bind_text(), and sqlite3_bind_text16() is a destructor used to dispose of the BLOB or string after SQLite has finished with it. If the fifth argument is the special value SQLITE_STATIC, then SQLite assumes that the information is in static, unmanaged space and does not need to be freed. If the fifth argument has the value SQLITE_TRANSIENT, then SQLite makes its own private copy of the data immediately, before the sqlite3_bind_*() routine returns." http://sqlite.org/c3ref/bind_blob.html -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Eric Smith Sent: sexta-feira, 9 de julho de 2010 14:54 To: General Discussion of SQLite Database Subject: Re: [sqlite] Null character problem Simo Slavin wrote: > (according to your earlier post) I'm not OP. I'm Eric. OP was someone else. In this context, I don't care about blobs or about the right way of doing anything. > Read the documentation for memset(). I know quite well how memset works. I know character!=byte. These matters are irrelevant to my question. My question came purely from a mild curiosity. I was wondering about the behavior of sqlite call sqlite3_bind_text when it is passed a range of BYTES that includes nulls. -- Eric A. Smith It's up. It sorta works. That's a start. -- BJ Premore ___ 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] is this a bug?
It´s good to try to reproduce all conditions that this problem happens, to help with creating test-cases and with bug fix. []'s -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor Sent: sábado, 19 de dezembro de 2009 00:38 To: General Discussion of SQLite Database Subject: Re: [sqlite] is this a bug? On Fri, Dec 18, 2009 at 8:23 PM, Valerio Aimale wrote: > Hello all, > > I've run into an interesting situation; when duplicating parenthesis > around a 'in ()' subquery, only the first row is returned. > > This is not my real-life query, but a test that replicates the problem. > > Thanks, > > Valerio > > $ sqlite3 --version > 3.6.16 > > prepare some dummy data: > > create table test ( id INT ); > insert into test VALUES(1); > insert into test VALUES(2); > insert into test VALUES(3); > insert into test VALUES(4); > insert into test VALUES(5); > insert into test VALUES(6); > insert into test VALUES(7); > insert into test VALUES(8); > insert into test VALUES(9); > insert into test VALUES(10); > > sqlite> select id from test where (id > 5); > 6 > 7 > 8 > 9 > 10 > [Good] > > sqlite> select id from test where id in (select id from test where (id > > 5)); > 6 > 7 > 8 > 9 > 10 > [Still Good] > > Now let's duplicate parenthesis around the subquery: > > sqlite> select id from test where id in ((select id from test where (id > > 5))); > 6 > > Why only one value returned when parenthesis are duplicated? > > Same with triple parenthesis enclosing: > > sqlite> select id from test where id in (((select id from test where (id > > 5; > 6 > Given the above table sqlite> SELECT id FROM test WHERE id IN (6, 7, 8, 9, 10); id -- 6 7 8 9 10 sqlite> SELECT id FROM test WHERE id IN ((6, 7, 8, 9, 10)); SQL error: near ",": syntax error sqlite> Seems like IN expects a comma separated list, and nothing else within a single set of parens. -- Puneet Kishor ___ 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] is this a bug?
This is probably a bug in SQL parser, that doesn't reduced to one parenthesis, causing the side effect in VDBE engine, that doesn't returned all ID´s to IN evaluator in first select. This should be easily reproduced, but the fix you must wait for Richard or other that have knowledge on VDBE instruction debugging. I've tested the same thing on SQL Server 2008, correct results appeared: --- create database x go use x go create table test (id int not null, primary key(id)) go insert into test values (1) insert into test values (2) insert into test values (3) insert into test values (4) insert into test values (5) insert into test values (6) insert into test values (7) insert into test values (8) insert into test values (9) insert into test values (10) go select id from test where id in (select id from test where id > 5) go id --- 6 7 8 9 10 (5 row(s) affected) select id from test where id in (select id from test where (id > 5)) go id --- 6 7 8 9 10 (5 row(s) affected) select id from test where id in ((select id from test where (id > 5))) go id --- 6 7 8 9 10 (5 row(s) affected) []'s -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Valerio Aimale Sent: sábado, 19 de dezembro de 2009 00:23 To: sqlite-users@sqlite.org Subject: [sqlite] is this a bug? Hello all, I've run into an interesting situation; when duplicating parenthesis around a 'in ()' subquery, only the first row is returned. This is not my real-life query, but a test that replicates the problem. Thanks, Valerio $ sqlite3 --version 3.6.16 prepare some dummy data: create table test ( id INT ); insert into test VALUES(1); insert into test VALUES(2); insert into test VALUES(3); insert into test VALUES(4); insert into test VALUES(5); insert into test VALUES(6); insert into test VALUES(7); insert into test VALUES(8); insert into test VALUES(9); insert into test VALUES(10); sqlite> select id from test where (id > 5); 6 7 8 9 10 [Good] sqlite> select id from test where id in (select id from test where (id > 5)); 6 7 8 9 10 [Still Good] Now let's duplicate parenthesis around the subquery: sqlite> select id from test where id in ((select id from test where (id > 5))); 6 Why only one value returned when parenthesis are duplicated? Same with triple parenthesis enclosing: sqlite> select id from test where id in (((select id from test where (id > 5; 6 ___ 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] [ANN] SQLJet 1.0.0 released
Question 1 is a very good question, why pay for a partial copy if you can use the full version for free? Also, what is the sense of using SQLite database without SQL support? (this remember Clipper/dBase GOTO LOCATE APPEND...) I can't get the point... if you can't use a native SQLite in your platform, why do not use another pure-java DB? Just my 2 cents... -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kosenko Max Sent: quinta-feira, 17 de setembro de 2009 11:14 To: sqlite-users@sqlite.org Subject: Re: [sqlite] [ANN] SQLJet 1.0.0 released Alexander Kitaev-3 wrote: > > We're glad to announce that SQLJet 1.0.0 has been released and available > for download at http://sqljet.com/ web site. > Hi. Several questions: 1. Are there any real reasons for having dual-licensed commercial partial reimplementation of SQLite in Java? Any examples? 2. When do you expecting to have SQL API? 3. Are you targeting for having 100% SQLite unit-tests passes? 4. Have you ever thought about collaborating with and contributing to http://code.google.com/p/csharp-sqlite/ since it's easier to port C#->Java than C->Java? 5. Any benchmarks comparing to native? Thanks. Max. - Best Regards. Max Kosenko. -- View this message in context: http://www.nabble.com/-ANN--SQLJet-1.0.0-released-tp25458690p25491910.html Sent from the SQLite mailing list archive at Nabble.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
Re: [sqlite] Is it Possible in SQL...
'Not using SQL directly' means you create a series data access interface like an ISeries interface that browse in a cursor-style through your series data, then implement it as a concrete class like DatabaseSeries that does the SQL job for you. Talking about scaling issues means that you could do some kind of data manipulation best on processing by itself instead of executing SQL statements (example: an data analysis study that take a series and output 3 new series with heterogeneous values, based on first data series, you can calculate the resulting 3 series fetching data one time when you iterate through you ISeries interface; executing 3 SQL statements will perform 3 read cycles, you can perform fast and also stop wasting CPU resources). []'s -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rick Ratchford Sent: sexta-feira, 10 de julho de 2009 23:10 To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Is it Possible in SQL... #>I was trying to figuring out if you are doing something of #>graph data analysis, I do it almost everyday in our Stock #>Trader applications... #>I never did this way (direct SQL), cause our graph series #>data sources are implement throught a common interface, that #>could be a SQL query, a stream, a XML, whatever. #> #>Just a tip: implementing specific and well designed #>interfaces for series data manipulation should be the right #>way for you, avoid scaling issues cause of possible SQL #>limitations in the ways those series can/should be #>manipulated in some cases. This specific application requires very little user input. The user selects a market (stock/futures) from a list of available data files and that is it. The program then performs all kinds of different things on the dataset selected and for the most part presents its results in the form of values. There is one procedure, however, that will produce a 'graph' if the user clicks on a button. That's pretty much it. When you said you never use 'direct SQL', are you saying that you never use SQL that is hard coded in your program? If so, perhaps in the case of my application requiring virtually no interaction that it is acceptable for some of the internal procedures? What are "scaling issues"? Thanks. :-) Rick ___ 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] Is it Possible in SQL...
I was trying to figuring out if you are doing something of graph data analysis, I do it almost everyday in our Stock Trader applications... I never did this way (direct SQL), cause our graph series data sources are implement throught a common interface, that could be a SQL query, a stream, a XML, whatever. Just a tip: implementing specific and well designed interfaces for series data manipulation should be the right way for you, avoid scaling issues cause of possible SQL limitations in the ways those series can/should be manipulated in some cases. Just my 2 cents... []'s -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rick Ratchford Sent: sexta-feira, 10 de julho de 2009 17:32 To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Is it Possible in SQL... Seems there was a question in your reply I didn't catch the first time. > #>What do you mean by "previous one"? Records in a table don't #>have any implicit ordering. Do you have some kind of a #>timestamp field that imposes the order? The table, each time, has been in order from oldest Date to latest Date. There is also the Primary ID field that starts from 1 to ... The table is created by loading in a Metastock data file. The table name is that of the market loaded. Records are never deleted or inserted within this table, as is simply houses the complete price data loaded for that specific market. So the order stays as when first created. It is never modified. From oldest date to last price data date. For simplicity, call this MarketTable. What I've been working on are the recordsets created from MarketTable, or a temp table (TmpTable) that was created from a recordset derived from the MarketTable. #>update taxTable set DIRECTION = #>(select case when taxTable.TAX < prevTax then 'down' when #>taxTable.TAX > prevTax then 'up' else null end from (select #>t2.TAX as prevTax from taxTable t2 where t2.Date < #>taxTable.Date order by Date desc limit 1)); #> #>Igor Tandetnik After examining the above, it appears that what this does is modify the table itself. So I suppose then that it is not possible to create a recordset instead that meets what I'm trying to do. If this is the case, I'll have to make a copy of this table first as I don't want to modify the original. Here's what is going on: Most of my functions and procedures works off a table called TmpTable. This was created from a recordset derived from the main MarketTable. TmpTable was created because it needed to be somewhat modified from the original price data table (MarketTable). The current procedure that prompted my original question needs only to determine the DIRECTION of values from one record to the next. Once this procedure has completed this task and plotted (graphically), it is no longer needed. So modifying TmpTable, which will still be needed for other procedures, is not preferred. It would be great if a recordset could be derived from it instead that contains the DIRECTION results. Once the procedure exits, the recordset would just go away. My original plan was to create the recordset from TmpTable, with the added DIRECTION column. Ex: 0 as Direction FROM TmpTable Then, either loop through the recordset doing the comparisons and filling in DIRECTION, or make an array copy of the recordset and then fill in the info. Of course, it is preferred that it be already accomplished by way of the query. I hope this makes sense. I won't be surprised if it does not. :-b Thanks. Rick ___ 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] GROUPY BY alias backward incompatibility
Curious... even it does not make sense, it pass also on oracle 11g (sql server actively refused to run) SQL Server 2008: SELECT (SELECT COUNT(*) FROM T t_inner GROUP BY t_outer.c) FROM T t_outer -- Msg 164, Level 15, State 1, Line 1 -- Each GROUP BY expression must contain at least one column that is not an outer reference. SELECT (SELECT COUNT(*) FROM T t_inner GROUP BY t_inner.c) FROM T t_outer --- (0 row(s) affected) Oracle 11g: SQL> create table t (c int); Table created SQL> select (select count(*) from t t_inner group by t_inner.c) from t t_outer; No rows selected SQL> select (select count(*) from t t_inner group by t_outer.c) from t t_outer; No rows selected -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ralf Junker Sent: quinta-feira, 28 de maio de 2009 11:30 To: General Discussion of SQLite Database Subject: Re: [sqlite] GROUPY BY alias backward incompatibility At 15:37 28.05.2009, D. Richard Hipp wrote: >Have you tried these two queries on other SQL database engines besides >SQLite? What do PostgreSQL and MySQL make of them? MySQL (5.0.21) reports no erros on either of both queries: select (select count(*) from t t_inner group by t_outer.c) -- t_outer !!! from t t_outer; select (select count(*) from t t_inner group by t_inner.c) -- t_inner !!! from t t_outer; I do not have access to PostgreSQL right now. The SQLite help [1] says: "The expressions in the GROUP BY clause do not have to be expressions that appear in the result." Reading this, I'd expect that both queries should run - even if the 1st one does not make much sense. Opinions? Ralf [1] http://www.sqlite.org/lang_select.html ___ 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
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 > >> > additi
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] Large SQLite3 Database Memory Usage
Try to run those queries on sqlite3 program. Then compare memory working sets between your IIS Process and sqlite3 shell. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke Sent: terça-feira, 5 de maio de 2009 16:25 To: General Discussion of SQLite Database Subject: Re: [sqlite] Large SQLite3 Database Memory Usage I am using SQLite ODBC Driver. How can I find if its leak in my provider??? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Virgilio Alexandre Fornazin Sent: Tuesday, May 05, 2009 12:23 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Large SQLite3 Database Memory Usage Are you using ADO or plain sqlite dll ? May this be a leak in your provider ? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke Sent: terça-feira, 5 de maio de 2009 16:13 To: General Discussion of SQLite Database Subject: Re: [sqlite] Large SQLite3 Database Memory Usage I am running the query with CursorLocation = 2, CursorType = 2, LockType = 3 to handle 610MB database with 2259207 records in table. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke Sent: Tuesday, May 05, 2009 11:45 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Large SQLite3 Database Memory Usage How much RAM is needed to handle 610MB database with 2259207 records in table.Initially I was running Windows web server 2008 with 512 MB RAM,now I upgraded to 1GB RAM but still the same ..If I run my application once the query returns results in 80 sec and if run it again , memory usage starts growing and reaches nearly 100% ..then I get more hard page faults , the application responds slow, or system just hangs. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke Sent: Tuesday, May 05, 2009 11:25 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Large SQLite3 Database Memory Usage Thanks for your response. The query runs faster than mine but still facing problem after running the page multiple times.So I think I should try with 1GB of RAM. Thanks, -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of BareFeet Sent: Tuesday, May 05, 2009 9:57 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Large SQLite3 Database Memory Usage Hi Kalyani, > I have 2259207 records in table using SQLite3 database. I am running > the select query to retrive records from DB > > 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 > > If I run my application once the query returns results in 80 sec and > if run it again , memory usage starts growing and reaches nearly 100% > ..then I get more hard page faults , the application responds slow, or > system just hangs. I am running my application on Windows web server > 2008 with 512 MB RAM. How can I optimize the query ? I have indexes on > ID and notificationtime (datatype timestamp) Using this is very inefficient: order by ID desc limit 100 since SQLite still has to build and sort the entire result table before limiting it. You are better off to filter using the search (ie where). For instance, if ID is the integer primary key and you don't expect deletes, you could use: SELECT ID, EventClassName, EventClassRef, TransitionTime, Message, MonitoredRef, EventRef, ToState, Priority, Acked from Events where Events.ID >= (select max(ID) from Events) - 100 and Events.NotificationTime >= {ts '2009-05-04 14:44:10'} order by ID desc HTH, Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/?ml ___ 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 ___ 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] Large SQLite3 Database Memory Usage
Are you using ADO or plain sqlite dll ? May this be a leak in your provider ? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke Sent: terça-feira, 5 de maio de 2009 16:13 To: General Discussion of SQLite Database Subject: Re: [sqlite] Large SQLite3 Database Memory Usage I am running the query with CursorLocation = 2, CursorType = 2, LockType = 3 to handle 610MB database with 2259207 records in table. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke Sent: Tuesday, May 05, 2009 11:45 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Large SQLite3 Database Memory Usage How much RAM is needed to handle 610MB database with 2259207 records in table.Initially I was running Windows web server 2008 with 512 MB RAM,now I upgraded to 1GB RAM but still the same ..If I run my application once the query returns results in 80 sec and if run it again , memory usage starts growing and reaches nearly 100% ..then I get more hard page faults , the application responds slow, or system just hangs. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kalyani Phadke Sent: Tuesday, May 05, 2009 11:25 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Large SQLite3 Database Memory Usage Thanks for your response. The query runs faster than mine but still facing problem after running the page multiple times.So I think I should try with 1GB of RAM. Thanks, -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of BareFeet Sent: Tuesday, May 05, 2009 9:57 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Large SQLite3 Database Memory Usage Hi Kalyani, > I have 2259207 records in table using SQLite3 database. I am running > the select query to retrive records from DB > > 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 > > If I run my application once the query returns results in 80 sec and > if run it again , memory usage starts growing and reaches nearly 100% > ..then I get more hard page faults , the application responds slow, or > system just hangs. I am running my application on Windows web server > 2008 with 512 MB RAM. How can I optimize the query ? I have indexes on > ID and notificationtime (datatype timestamp) Using this is very inefficient: order by ID desc limit 100 since SQLite still has to build and sort the entire result table before limiting it. You are better off to filter using the search (ie where). For instance, if ID is the integer primary key and you don't expect deletes, you could use: SELECT ID, EventClassName, EventClassRef, TransitionTime, Message, MonitoredRef, EventRef, ToState, Priority, Acked from Events where Events.ID >= (select max(ID) from Events) - 100 and Events.NotificationTime >= {ts '2009-05-04 14:44:10'} order by ID desc HTH, Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/?ml ___ 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 ___ 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] A memvfs for loading/saving database from buffer
Where we can get the code ? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of stephen liu Sent: terça-feira, 28 de abril de 2009 22:24 To: sqlite-users@sqlite.org Subject: [sqlite] A memvfs for loading/saving database from buffer Hi, The attachment is a memvfs implementation for sqlite. With the memvfs, we can loading/saving sqlite database from buffer. There also includes a demo to show how to use it. Cheers, Stephen Liu ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using SQLite3 on On-Time RTOS ...
Maybe RTOS kernel does not implement Wide-Char functions... Windows VFS must guard them with a SQLITE_WINDOWS_NO_UNICODE macro or something like that at compile time, but you should do it yourself... then you can contribute it back to SQLite. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kent Dahl Sent: segunda-feira, 27 de abril de 2009 09:58 To: General Discussion of SQLite Database Subject: [sqlite] Using SQLite3 on On-Time RTOS ... Hi. I wondered whether anyone is using SQLite3 on the On-Time RTOS platform, and if so, if there are any patches or tips to help others along? We've been giving the SQLite 3.6.10 amalgamation source code for Windows a try against RTOS 5.14 and it compiled out of the box. However, we ran into some linker and run-time errors. After a fair amount of experimenting, we got it up and running, but only towards an in-memory database. We still have problems opening existing or creating database files. I'll outline what we ran into and what we've tried so far. The linker errors were primarily towards wide char APIs that didn't exist on RTOS. Most of these could easily be ifdef'd away. There was alternate Win95/98/ME code we could trigger if we changed "isNT" to 0, which helped a bit. Some, like AreFileApisANSI we just defined to 1. We then ran into a crasher caused by convertUtf8Filename returning NULL, so we changed that to use a fallback. Just strdup-ing the input string if the conversion failed. Debugging this took a little while, because using the amalgamation source meant that the RTOS compiler crossed a 64k symbols limit, meaning breakpoints and backtraces broke badly. Finally we had something that linked and ran, but only towards in-memory database. When we tried to open an existing database (or create a new one) using the sqlite3_open_v2 API, it kept returning SQLITE_NOMEM(7). When I tried debugging this, I got as far as the sqlite3BtreeFactory call, but because of the breakpointing problems I didn't get much further. So, my questions then are: * Is anyone using or have used SQLite3 on RTOS? * Are the older Win95/98/ME code paths still actively used, tested and found to be working? (Or should I expect a few inches of legacy dust and bugs in them?) * Is there a good way to get more debug information? (SQLITE_DEBUG seems more targetted at debugging SQL statements.) * Am I barking up any of the wrong trees? Hope someone out there has some ideas or pointers to help me get motivated enough to give another stab at it. :) = Additional information = === Linker errors === Example linker error: "Error: DLL dependency in CDP.EXE: KERNEL32.dll.DeleteFileW" These APIs also gave linker errors: - LockFileEx - GetTempPathW - GetFullPathNameW - GetDiskFreeSpaceW - AreFileApisANSI === References === * On-Time RTOS - http://www.on-time.com/ -- Mvh/Regards, Kent Dahl Software Developer Industrial Control Design AS Phone: +47 93 07 32 30 Breivika Industriveg 63 N-6018 Ålesund Norway k...@icd.no www.icd.no The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the System Manager i...@icd.no and delete the material from any computer. ___ 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 replication
SQLite is file-based (no server behind DB), you must provide your own synchronization (copying file, executing SQL on both databases, etc). I don't know if there´s an application/library that does it for you automatically. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wade Williams Sent: quinta-feira, 11 de dezembro de 2008 14:32 To: sqlite-users@sqlite.org Subject: [sqlite] Sqlite replication I'm looking for an honest assessment from someone that may have made this decision in the past. I'm considering using an embedded database for an upcoming application. Operation rate is high 20,000-60,000 per day. (Those will mostly be selects, but some smaller percentage will be inserts). Our choices appear to be SQLite or Berkley DB. An RDBMS isn't really an option due to the administrative cost. My first inclination was to use SQLite. From what I've seen of the performance numbers, it should be able to support that rate without much trouble. However, a key feature is disaster recovery. If the primary machine goes down we've got to quickly switch to another machine (quickly meaning within minutes if not seconds). In my research it appears SQLite may not be a good option, since the only replication appears to be "lock the database and copy the file to the new machine." Berkeley DB seems to have the advantage of having replication built-in. However, I have no idea how useful the replication is and of course the API is much more inscrutable. I've also certainly heard all the Berkley DB corruption horror stories. I'm OK with stepping off the deep end into Berkeley DB, but I'd prefer SQLite. However, I'm certainly not looking to shoot myself in the foot. I'd appreciate input from anyone on this subject, especially tales from replication projects. Thanks, Wade ___ 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] Vista frustrations
Sure. I just tell to do this test to check if the bug is related to this component, since it debuted on Vista. Virgilio Alexandre Fornazin High performance and realtime systems development Rua Brigadeiro Vicente Faria Lima, 268 Bela VistaLeme-SPCEP 13611-485 Phone: +55 19 3571-5573 Cell: +55 19 8111-4053 +55 11 8357 1491 Mail: [EMAIL PROTECTED] Web: http://www.fornazinconsultoria.com.br -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeffrey Becker Sent: quarta-feira, 17 de setembro de 2008 13:41 To: General Discussion of SQLite Database Subject: Re: [sqlite] Vista frustrations SQLite in general and the .Net provider in particular are most often shipped as components of other applications. I dont think having developers tell their end users to disable superfetch is a viable solution. As much as I hate to propose this maybe a runtime check is in order to see what the OS version is and not use the flag where it's known to be problematic. On Wed, Sep 17, 2008 at 12:14 PM, Virgilio Alexandre Fornazin <[EMAIL PROTECTED]> wrote: > Could not this bug be related with Vista feature called 'Superfetch' ? > It tries to keep in memory the most accessed files for user, avoiding > disk for read access. > > If you disable (or stop) this service, the problem remains or not ? > > > > > Virgilio Alexandre Fornazin > High performance and realtime systems development > > Rua Brigadeiro Vicente Faria Lima, 268 > Bela VistaLeme-SPCEP 13611-485 > Phone: +55 19 3571-5573 > Cell: +55 19 8111-4053 > +55 11 8357 1491 > Mail: [EMAIL PROTECTED] > Web: http://www.fornazinconsultoria.com.br > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Jay A. Kreibich > Sent: quarta-feira, 17 de setembro de 2008 13:01 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Vista frustrations > > On Wed, Sep 17, 2008 at 01:17:51AM -0700, Roger Binns scratched on the wall: >> Robert Simpson wrote: >> > To me this seems like an obvious bug in Vista, >> >> Actually I'd argue that it is behaving as designed. > > You could argue it is behaving as designed, but I'd still argue it is > behaving poorly. > > Further, if a system component who's sole purpose is to increase > performance-- as all cache systems are-- has the overall effect of > decreasing performance, not only of the process it is trying to speed > up, but of the whole system, it is pretty easy to argue that's a serious > functional bug. > > > Given the speed of most storage systems, filesystem cache management > is an important component of overall system performance. However, if > the cache system is grabbing so much physical memory (and, apparently, > refusing to let go of it) that processes are forced to aggressively > page and the net result is a massive performance loss, then something > isn't right. > > As with so many things, cache management (and, indeed, the whole > concept of caches) tends to be a huge web of compromises. It is > extremely difficult, if not impossible, to cover all cases. But > these things are not exactly new, and it should be easy enough to > never get in a situation where things are actually made worse-- > especially that they're not made worse for the whole system. > >> Generally >> filesystem code will try to detect what is going on under the hood. In >> particular if it looks like you are doing sequential access(*) then they >> will start doing read ahead, whereas read ahead is a waste for random >> access. > > Not to get into a whole argument about cache strategies, but this > often not true. If we assume free memory isn't a big concern, > when a process opens a file for random-access we can either > read-ahead the whole thing or we can read blocks here and there until > (if the process touches the majority of the file) we have the whole > thing in memory. Both systems, in the end, will result in the same > memory usage. > > However, if I'm going to be doing random access on a file of moderate > or smaller size, it is much cheaper for the OS to just suck the whole > thing into memory via one bulk read operation than it is to grab it > piecemeal. > > The whole trick is defining "moderate" both in terms of first-return > read times (time to return the block the process actually asked for, > which might not be the first block pulled off disk) vs how likely the > process is to touch the majority of file blocks (something that is > somewhat less likely as the file gets bigger). > > As the file gets larger, there is also the real-world issue of how
Re: [sqlite] Vista frustrations
Note that Windows Server 2008 use the same 'core' as Windows Vista. If you´re detecting and redirecting by using GetVersion() or other approach you might test for Server 2008 too. Virgilio Alexandre Fornazin High performance and realtime systems development Rua Brigadeiro Vicente Faria Lima, 268 Bela VistaLeme-SPCEP 13611-485 Phone: +55 19 3571-5573 Cell: +55 19 8111-4053 +55 11 8357 1491 Mail: [EMAIL PROTECTED] Web: http://www.fornazinconsultoria.com.br -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Robert Simpson Sent: quarta-feira, 17 de setembro de 2008 13:30 To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Vista frustrations I've run the tests with superfetch and prefetch disabled and enabled. Results are consistent with or without these running. The only thing that has any affect is the FILE_FLAG_RANDOM_ACCESS flag. And only on Vista. For now I'm thinking of overriding the default Windows VFS and redirecting the open function just for Vista so it doesn't use that flag. Robert -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Virgilio Alexandre Fornazin Sent: Wednesday, September 17, 2008 9:14 AM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Vista frustrations Could not this bug be related with Vista feature called 'Superfetch' ? It tries to keep in memory the most accessed files for user, avoiding disk for read access. If you disable (or stop) this service, the problem remains or not ? Virgilio Alexandre Fornazin High performance and realtime systems development Rua Brigadeiro Vicente Faria Lima, 268 Bela VistaLeme-SPCEP 13611-485 Phone: +55 19 3571-5573 Cell: +55 19 8111-4053 +55 11 8357 1491 Mail: [EMAIL PROTECTED] Web: http://www.fornazinconsultoria.com.br -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jay A. Kreibich Sent: quarta-feira, 17 de setembro de 2008 13:01 To: General Discussion of SQLite Database Subject: Re: [sqlite] Vista frustrations On Wed, Sep 17, 2008 at 01:17:51AM -0700, Roger Binns scratched on the wall: > Robert Simpson wrote: > > To me this seems like an obvious bug in Vista, > > Actually I'd argue that it is behaving as designed. You could argue it is behaving as designed, but I'd still argue it is behaving poorly. Further, if a system component who's sole purpose is to increase performance-- as all cache systems are-- has the overall effect of decreasing performance, not only of the process it is trying to speed up, but of the whole system, it is pretty easy to argue that's a serious functional bug. Given the speed of most storage systems, filesystem cache management is an important component of overall system performance. However, if the cache system is grabbing so much physical memory (and, apparently, refusing to let go of it) that processes are forced to aggressively page and the net result is a massive performance loss, then something isn't right. As with so many things, cache management (and, indeed, the whole concept of caches) tends to be a huge web of compromises. It is extremely difficult, if not impossible, to cover all cases. But these things are not exactly new, and it should be easy enough to never get in a situation where things are actually made worse-- especially that they're not made worse for the whole system. > Generally > filesystem code will try to detect what is going on under the hood. In > particular if it looks like you are doing sequential access(*) then they > will start doing read ahead, whereas read ahead is a waste for random > access. Not to get into a whole argument about cache strategies, but this often not true. If we assume free memory isn't a big concern, when a process opens a file for random-access we can either read-ahead the whole thing or we can read blocks here and there until (if the process touches the majority of the file) we have the whole thing in memory. Both systems, in the end, will result in the same memory usage. However, if I'm going to be doing random access on a file of moderate or smaller size, it is much cheaper for the OS to just suck the whole thing into memory via one bulk read operation than it is to grab it piecemeal. The whole trick is defining "moderate" both in terms of first-return read times (time to return the block the process actually asked for, which might not be the first block pulled off disk) vs how likely the process is to touch the majority of file blocks (something that is somewhat less likely as the file gets bigger). As the file gets larger, there is also the real-world issue of how much RAM the system has, and how much of it is actually in-use with process a
Re: [sqlite] Vista frustrations
Could not this bug be related with Vista feature called 'Superfetch' ? It tries to keep in memory the most accessed files for user, avoiding disk for read access. If you disable (or stop) this service, the problem remains or not ? Virgilio Alexandre Fornazin High performance and realtime systems development Rua Brigadeiro Vicente Faria Lima, 268 Bela VistaLeme-SPCEP 13611-485 Phone: +55 19 3571-5573 Cell: +55 19 8111-4053 +55 11 8357 1491 Mail: [EMAIL PROTECTED] Web: http://www.fornazinconsultoria.com.br -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jay A. Kreibich Sent: quarta-feira, 17 de setembro de 2008 13:01 To: General Discussion of SQLite Database Subject: Re: [sqlite] Vista frustrations On Wed, Sep 17, 2008 at 01:17:51AM -0700, Roger Binns scratched on the wall: > Robert Simpson wrote: > > To me this seems like an obvious bug in Vista, > > Actually I'd argue that it is behaving as designed. You could argue it is behaving as designed, but I'd still argue it is behaving poorly. Further, if a system component who's sole purpose is to increase performance-- as all cache systems are-- has the overall effect of decreasing performance, not only of the process it is trying to speed up, but of the whole system, it is pretty easy to argue that's a serious functional bug. Given the speed of most storage systems, filesystem cache management is an important component of overall system performance. However, if the cache system is grabbing so much physical memory (and, apparently, refusing to let go of it) that processes are forced to aggressively page and the net result is a massive performance loss, then something isn't right. As with so many things, cache management (and, indeed, the whole concept of caches) tends to be a huge web of compromises. It is extremely difficult, if not impossible, to cover all cases. But these things are not exactly new, and it should be easy enough to never get in a situation where things are actually made worse-- especially that they're not made worse for the whole system. > Generally > filesystem code will try to detect what is going on under the hood. In > particular if it looks like you are doing sequential access(*) then they > will start doing read ahead, whereas read ahead is a waste for random > access. Not to get into a whole argument about cache strategies, but this often not true. If we assume free memory isn't a big concern, when a process opens a file for random-access we can either read-ahead the whole thing or we can read blocks here and there until (if the process touches the majority of the file) we have the whole thing in memory. Both systems, in the end, will result in the same memory usage. However, if I'm going to be doing random access on a file of moderate or smaller size, it is much cheaper for the OS to just suck the whole thing into memory via one bulk read operation than it is to grab it piecemeal. The whole trick is defining "moderate" both in terms of first-return read times (time to return the block the process actually asked for, which might not be the first block pulled off disk) vs how likely the process is to touch the majority of file blocks (something that is somewhat less likely as the file gets bigger). As the file gets larger, there is also the real-world issue of how much RAM the system has, and how much of it is actually in-use with process and OS pages. This is true of both sequential AND random access, although memory usage is generally easier to control in sequential patterns. This is where Vista appears to be breaking down and making very poor decisions. It seems to be giving cache pages more priority than process and OS system pages, and generally that should never happen. If we're correctly understanding what is going on, Vista might very well be paging out SQLite's internal page cache to fit a few extra file blocks in RAM. How much sense does that make? > By using the sequential or random flags you are explicitly > telling the filesystem to ignore its heuristics and do as you say only. Even if that's true (most APIs present the flags as "hints" not absolute truths), the worst an incorrect flag should do is hurt the file access performance of the process that provided the hint. Even then, the lower end should be the same performance one would expect if there was no cache (e.g. constant misses). A poor or incorrect flag is no excuse to be overly aggressive with holding pages in RAM and killing the whole system. Even if a flag alters the read-ahead policy or cache replacement strategy, a flag should never override the decisions the cache system has to face when the system starts to run thin on free physical RAM-- e
Re: [sqlite] SQLite and Threadsafety (again)
I'm a bit curious why it wouldn't work. I use the same approach right here to have a exclusive access to a database table in the same model I told you. Also, I never used other locking mode that exclusive, because if want to write to the database the write lock should be granted exclusively by a single thread (similar to pthreads multiple reader single writer lock scheme). Also, DRH told in other answer in this thread the problem of using only one connection shared with many threads: you cannot guarantee that last_insert_rowid() is correct, because someone other can started a transaction and write a record to any table, modifying the last_insert_rowid() result, which lead you to produce bugs if you depend on this feature. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Shawn Wilsher Sent: quarta-feira, 21 de maio de 2008 14:15 To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite and Threadsafety (again) The problem with the approach you suggest as that that does not work when PRAGMA locking_mode = EXCLUSIVE, which we use in many places to improve performance. Additionally, it's my understanding that when using the shared cache, that transactions are grouped across connection objects (it's possible I misread a past e-mail though). We use the shared cache for every database connection (although, I've been wondering as of late if it's really worthwhile). It is unfortunate that we'll lose the ability to do multiple reads at the same time, however. I'm open to suggestions on a better way to fix this problem. Cheers, Shawn On Wed, May 21, 2008 at 1:05 PM, Virgilio Alexandre Fornazin <[EMAIL PROTECTED]> wrote: > Not to putting flame in question, but why not use any connection per thread > ? At this way you can guarantee: > > - Correct transaction processing; > - Avoid waiting on R/W locks, allowing more than one read to run > concurrently; > > We also use this model with ODBC / ADO database layers. > > You don't need to take care if your database drivers provides thread safety, > handle multiple active result sets > (client-side cursors), last insert row id concurrency, etc. > > We tried to use a single connection per process, but after changed to one > connection per thread model, the > gains we got avoiding synchronization was bigger than we imaginated. > > To get this changes working best, we created a database connection pool, > that we use to get the connections by their > ID´s (yes, it´s a key-value dictionary). The pool also 'recycle' connections > that will not be used by any thread to > avoid resource leaking. > > Is this case, assuming that the unique ID of the database is the file name > (SQLite database file name), you can get > this behaviour to work transparently for your consumers (I assume you´re not > using directly the sqlite3_* calls inside > your program, you have some kind of high-level abstraction to use them). > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Shawn Wilsher > Sent: quarta-feira, 21 de maio de 2008 13:08 > To: General Discussion of SQLite Database > Subject: [sqlite] SQLite and Threadsafety (again) > > Hey all, > > I've come to the sad realization that we need to make our sqlite > wrapper threadsafe so it can be used on multiple threads without > consumers having to worry about threadsafety themselves. So, I wanted > to make sure all my assumptions about sqlite data structures are > correct so I don't introduce issues before undertaking this task. > > First, I know that the sqlite3 object can be accessed on multiple > threads, but it must only be used by one thread of control at a time. > It is also my understanding that this same constraint applies to > sqlite3_stmt objects - they can only be used by one thread of control > at a time but accessed on multiple ones. What I am not so sure about, > however, is if I have to protect the sqlite3 object that "owns" the > statement when I'm calling methods on it such as sqlite3_bind_* > interfaces, sqlite3_step, sqlite3_reset, and sqlite3_finalize. > Conservatively, I'm assuming yes on all of the above, but I hope I'm > wrong for at least some of those. I would, however, expect to have to > protect the sqlite3 object when calling sqlite3_prepare_v2. > > Clarification on this would be greatly appreciated. > > Cheers, > > Shawn Wilsher > Mozilla Developer > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list >
Re: [sqlite] SQLite and Threadsafety (again)
Not to putting flame in question, but why not use any connection per thread ? At this way you can guarantee: - Correct transaction processing; - Avoid waiting on R/W locks, allowing more than one read to run concurrently; We also use this model with ODBC / ADO database layers. You don't need to take care if your database drivers provides thread safety, handle multiple active result sets (client-side cursors), last insert row id concurrency, etc. We tried to use a single connection per process, but after changed to one connection per thread model, the gains we got avoiding synchronization was bigger than we imaginated. To get this changes working best, we created a database connection pool, that we use to get the connections by their ID´s (yes, it´s a key-value dictionary). The pool also 'recycle' connections that will not be used by any thread to avoid resource leaking. Is this case, assuming that the unique ID of the database is the file name (SQLite database file name), you can get this behaviour to work transparently for your consumers (I assume you´re not using directly the sqlite3_* calls inside your program, you have some kind of high-level abstraction to use them). -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Shawn Wilsher Sent: quarta-feira, 21 de maio de 2008 13:08 To: General Discussion of SQLite Database Subject: [sqlite] SQLite and Threadsafety (again) Hey all, I've come to the sad realization that we need to make our sqlite wrapper threadsafe so it can be used on multiple threads without consumers having to worry about threadsafety themselves. So, I wanted to make sure all my assumptions about sqlite data structures are correct so I don't introduce issues before undertaking this task. First, I know that the sqlite3 object can be accessed on multiple threads, but it must only be used by one thread of control at a time. It is also my understanding that this same constraint applies to sqlite3_stmt objects - they can only be used by one thread of control at a time but accessed on multiple ones. What I am not so sure about, however, is if I have to protect the sqlite3 object that "owns" the statement when I'm calling methods on it such as sqlite3_bind_* interfaces, sqlite3_step, sqlite3_reset, and sqlite3_finalize. Conservatively, I'm assuming yes on all of the above, but I hope I'm wrong for at least some of those. I would, however, expect to have to protect the sqlite3 object when calling sqlite3_prepare_v2. Clarification on this would be greatly appreciated. Cheers, Shawn Wilsher Mozilla Developer ___ 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] Proposed SQLite C/C++ interface behavior change.
A good and new safe could be a sqlite3_close_v2() call prototyped like int sqlite3_close_v2(sqlite3 * db, int closePendingStatements); and current sqlite3_close() call could become int sqlite3_close(sqlite3 * db) { return sqlite3_close_v2(db, 0); } In this way, current running code does not need to be changed, and developers that use v2 interface with close = 1 are aware of what they are doing. This not only mantains backward compatibility but also give more control of sqlite behaviour. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: terça-feira, 13 de maio de 2008 20:51 To: General Discussion of SQLite Database Subject: [sqlite] Proposed SQLite C/C++ interface behavior change. The currently documented behavior of sqlite3_close() is that when it called on a database connection that has unfinalized prepared statements is to return SQLITE_BUSY and fail to close the connection. The rational is that we did not want a call to sqlite3_close() to destroy sqlite3_stmt* pointers out from under other subsystems. But for version 3.6.0 we are considering a behavior change in which a call to sqlite3_close() will silently and automatically call sqlite3_finalize() on all outstanding prepared statements. This is, technically, an incompatible change and we strive to avoid incompatible changes. But we think it unlikely that this change will cause any problems, and in fact we suspect it will likely fix more bugs than it will induce. But before we move forward, it seems good to submit the idea to the community of SQLite users and programmers. Does anybody have any thoughts on this proposed behavior changes for the sqlite3_close() interface? D. Richard Hipp [EMAIL PROTECTED] ___ 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] Distributed transaction best practices
The best you can do actually with SQLite is a 'mirror-replicating' mode engine that works like Microsoft Windows Active Directory Database, to build a king of High Availability / Load Balancing server. You have a farm of servers (or workstations, etc) receiving SQL commands by a channel (socket, pipe, whatever). They force a 'election' do decide what´s the best servers to become master, then they agree on that and transactions (all commands that modify the database) are first applied to the master server then 'mirrored' to slave servers. In this design, if a 'child' server cannot complete the transaction that are completed by master and any other server, there is a critical problem in that slave node, and you must consider it offline or some kind of state that you need to check if by hand or by one tool you might develop for this. In this scenario, you can do a 'load balance' in SELECT´s, distributing querying belong all servers, creating affinities for tables, buffering most used tables in a memory database, etc. I´m currently implementing services for finantial stock exchanging services that works in the way I told you, if you are planning something that we can have in the way SQLite is (not tied to any kind of restrictive license), we can share knowledge and implement a solution like that. (PS: I don't have full time to work on it, but I can help in free hours) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Griggs, Donald Sent: segunda-feira, 12 de maio de 2008 10:47 To: General Discussion of SQLite Database Subject: Re: [sqlite] Distributed transaction best practices Hi David, Regarding: "What are the recommended "best practices" around using SQLite in a distributed scenario?" [two-phase commit, etc.] I trust that someone with some actual relevant knowledge will reply to your query later, but I imagine that many would say the the "recommend best practice" is *not* to use sqlite, since sqlite was designed to be an elegant embedded database -- without even one server -- let alone multiple synchronized ones. I take it you have strong reasons for rejecting, say, Postgres, which now implements two-phase commmit right out of the box? http://www.postgresql.org/docs/current/static/sql-prepare-transaction.ht ml You may already know everything in articles such as this one http://en.wikipedia.org/wiki/Two_phase_commit#Distributed_two-phase_comm it_protocol And its references (I don't claim to), but I'm listing it here just it case it's helpful to you. On the other hand, if you *do* develop a solid "distributed sqlite" implementation, I'm sure others would be interested. Regards, Donald Griggs This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. ___ 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] property/config file for SQLite
The database contains all pragmas commands you had executed. Why keep storing in a configuration file, since it´s not a database server? Please keep in mind that SQlite is a general purpose 'embedded' database engine, not a DBA-managed database server. Also WHY SQlite should have a configuration file? What kind of configuration this file will hold? Also, SQlite has a concept of VFS (Virtual File System), SQlite does not access any file direct without this interface (you can wrap direct memory access, another kind of custom storage, etc). I really can´t see what a configuration file will help (?) SQlite. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: sábado, 3 de maio de 2008 14:27 To: sqlite-users@sqlite.org Subject: Re: [sqlite] property/config file for SQLite But the question is who the target audience for SQLite is? Is it software developers or application users? If programmers can customize SQLite by changing source code, then they are probably smart enough to change property files. I am not complaining about the lack of a config file, just wondering why not Shibu Narayanan Consultant, PrimeSourcing Division, Investment Banking Group Tel.Office: 91-80-2208-6270 or 91-80-6659-6270 e-mail: [EMAIL PROTECTED] The answer is 42. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of John Stanton Sent: Saturday, May 03, 2008 9:19 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] property/config file for SQLite Not only is it a feature, it is a blessing. One of the most endearing features of Sqlite is that one file encapsulates everything. The simplicity is no accident but rather the reward of plenty of design discipline. I can attest from experience to the improvement in software reliability achieved by removing .ini files and similar configuration add-ons. For example support call rates drop enormously. D. Richard Hipp wrote: > On May 2, 2008, at 2:50 PM, [EMAIL PROTECTED] wrote: >> Is there a way to configure SQLite using a property/config file? > > SQLite has no configuration file. This is by design. SQLite is > intended to be a "zero-configuration" database. See http://www.sqlite.org/zeroconf.html > . The lack of a configuration file is a feature, not a bug. > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > 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 DISCLAIMER: This message contains privileged and confidential information and is intended only for an individual named. If you are not the intended recipient, you should not disseminate, distribute, store, print, copy or deliver this message. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete or contain viruses. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required, please request a hard-copy version. ___ 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] multiple writers for in-memory datastore
Imagine the following cenario (I assume you know c++ stdlib) A map of strings (filenames) to in-memory file handlers (the objects that will handle the shared memory or heap files). These files handlers will exists until the process exists and do not receive a delelefile() vfs call. File handlers can synchronize RW-Locks using internal mutex/criticat sections/semaphores/spin locks, etc. When you create a new file in vfs, a new handler is created and assigned to that filename and registered in this map. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of James Gregurich Sent: sábado, 19 de abril de 2008 17:02 To: General Discussion of SQLite Database Subject: Re: [sqlite] multiple writers for in-memory datastore I don't immediately see how that would solve the problem. The limitation of interest here (based on my perhaps limited understanding) is that locking has file-level granularity. I don't immediately see how a VST implementation would allow for changing the locking granularity of the overall system. -James On Apr 19, 2008, at 12:03 PM, Virgilio Fornazin wrote: > what about creating a VFS for such task ? Can be accomplished in > many ways, > using heap memory, shared memory... not so easy to do, but not much > complicated too... locking can be provided by multiple-readers > single-writers locks strategies, etc... > > On Sat, Apr 19, 2008 at 2:29 PM, James Gregurich <[EMAIL PROTECTED]> > wrote: > >> >> oh good! That isn't the version that ships with Leopard, but I can >> live with deploying my own version as part of my app. >> >> Will l get the writer parallelism I'm after as long as each thread >> writes exclusively into its own attached db? >> >> >> in other wordstwo bulk insert operations going on simultaneously >> on the same connection but each insert operation going into a >> different attached in-memory db. >> >> >> On Apr 19, 2008, at 9:20 AM, Dan wrote: >> >>> >>> On Apr 19, 2008, at 6:06 AM, James Gregurich wrote: >>> I'll ask this question. The answer is probably "no," but I'll ask it for the sake of completeness. Suppose I created an in-memory db. I use the attach command to associate an additional in-memory db. Suppose I assign the main db to thread 1 and the associated db to thread 2. Can I share the connection across the 2 threads if each thread works exclusively in its own db? I am aware that the connection is generally not threadsafe, but will it work if the two threads don't operate on the same db at the same time? >>> >>> As of 3.5, sqlite connections are threadsafe by default. With >>> earlier versions, this trick will not work. >>> >>> 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 >> > ___ > 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] segmentation fault
You must have to do a run inside gdb to get sqlite shell working then you can get your segfault -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of dark0s dark0s Sent: domingo, 13 de abril de 2008 11:00 To: sqlite-users@sqlite.org Subject: [sqlite] segmentation fault How must I interpret this output: bash-3.1# gcc -O0 -g -shared labsinf.c -o soundex.so bash-3.1# gdb sqlite3 GNU gdb 6.6 Copyright (C) 2006 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type "show copying" to see the conditions. There is absolutely no warranty for GDB. Type "show warranty" for details. This GDB was configured as "i486-slackware-linux"... Using host libthread_db library "/lib/libthread_db.so.1". (gdb) select load_extension('/root/soundex.so'); No symbol "load_extension" in current context. (gdb) select soundex('saverio'); No symbol "soundex" in current context. (gdb) - Inviato da Yahoo! Mail. La casella di posta intelligente. ___ 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] segmentation fault
gdb is your friend here. compile with: gcc -O0 -g -shared labsinf.c -o soundex.so then run sqlite with gdb gdb sqlite3 () gdb> run then you can get the backtrace of your exception -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of dark0s dark0s Sent: domingo, 13 de abril de 2008 10:46 To: sqlite-users@sqlite.org Subject: [sqlite] segmentation fault This is last suggestion that I tell, where is the problem now for segmentation fault: bash-3.1# gcc -shared labsinf.c -o soundex.so bash-3.1# sqlite3 SQLite version 3.5.7 Enter ".help" for instructions sqlite> select load_extension('/root/soundex.so'); sqlite> select soundex('saverio'); S010 Segmentation fault bash-3.1# - Inviato da Yahoo! Mail. La casella di posta intelligente. ___ 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] algorithm for adding columns to a table
Yes. I did this in my custom version of SQLite. If statement is ALTER TABLE, and SQLite returns error, I check if it´s ALTER TABLE (t) MODIFY COLUMN ou DROP COLUMN, doing the exact flow you did. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Derrell Lipman Sent: terça-feira, 18 de março de 2008 11:26 To: General Discussion of SQLite Database Subject: [sqlite] algorithm for adding columns to a table I could use another set of eyes or three to verify that this algorithm makes sense. I have legacy sqlite2 databases for which I need a generic function to add columns to tables. This is the pseudocode for the function. Am I missing anything? In this pseudocode, the parameters are: :tableName: The table name being altered :newColumnDefinition: The complete text of the new column description, as if it were in a CREATE TABLE command, e.g. "t TIMESTAMP DEFAULT '2008-03-18 10:08:47'" :newColumnValue: The value to insert into the new column as we add the new column to the table. Of course, all queries need error checking which is not included in the pseudocode. Errors cause an immediate rollback. Pseudocode follows... // If anything fails, ensure we can get back to our original query("begin;") // Get the sql to generate the table tableDef = query( "SELECT sql FROM sqlite_master WHERE tbl_name == :tableName: AND type = 'table';" ) // Get the indexes associated with this table, excluding automatic indexes indexes = query( "SELECT sql FROM sqlite_master WHERE tbl_name == :tableName: AND type = 'index' AND length(sql) > 0;" ) // Get the triggers associated with this table triggers = query( "SELECT sql FROM sqlite_master WHERE tbl_name == :tableName: AND type = 'trigger' AND length(sql) > 0;" ) // Copy all of the data to a temporary table query("CREATE TEMPORARY TABLE __t AS SELECT * FROM :tableName:;") // Drop the table being altered query("DROP TABLE :tableName:;") // Copy the original table definition so we can modify it sql = tableDef.sql; // Find the trailing right parenthesis in the original table definition p = strrchr(sql, ')'); // Where the right parenthesis was, append a comma and new column definition *p++ = ','; strcpy(p, :newColumnDefinition:); strcat(p, ");"); // Recreate the table using the new definition query(sql); // Copy the data from our temporary table back into this table. query("INSERT INTO :tableName: SELECT *, :newColumnValue: FROM __t;") // We don't need the temporary table anymore query("DROP TABLE __t;") // Recreate the indexes foreach index in indexes { query(index.sql) } // Recreate the triggers (after having copied the data back to the table!) foreach trigger in triggers { query(trigger.sql) } query("commit;") Thanks for any comments you can provide! Derrell ___ 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] Proposed sqlite3_initialize() interface
gcc support this, msvc++ and other compilers does not. -Original Message- From: Russell Leighton [mailto:[EMAIL PROTECTED] Sent: terça-feira, 30 de outubro de 2007 23:32 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Proposed sqlite3_initialize() interface On Oct 30, 2007, at 10:18 AM, [EMAIL PROTECTED] wrote: > > To accomodate this need, we are considering an incompatible > API change to SQLite. We are thinking of requiring that an > application invoke: > > int sqlite3_initialize(...); > I am not sure about the systems that you are trying to support, but for gnu tool chain you can do: gcc -shared -Wl,-init=sqlite3_initialize ... which will run the function at library load time and for static linking ( I think you can use this for dynamic linking too but I am not sure): __attribute__((constructor)) void sqlite3_initialize(void) So the init function would not need to be a public function and no API change would be needed (assuming the target platforms have similar capability). - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] how to get file handle from sqlite3 object?
There is a better workaround: get the code from the .dump command of sqlite3 utility and use it... it creates a fresh copy of your database like using sqlite3 dbold .dump | sqlite3 newdb (not sure about the syntax, there´s a example of this case on internet) -Original Message- From: Ronny Dierckx [mailto:[EMAIL PROTECTED] Sent: quarta-feira, 10 de outubro de 2007 13:55 To: sqlite-users@sqlite.org Subject: Re: [sqlite] how to get file handle from sqlite3 object? I think a possible solution is to lock the database with a "BEGIN EXCLUSIVE" statement and then copy the database file. - Original Message - From: "Cyrus Durgin" <[EMAIL PROTECTED]> To: Sent: Wednesday, October 10, 2007 6:41 PM Subject: Re: [sqlite] how to get file handle from sqlite3 object? > Agreed - it seems like this would be useful enough functionality that I'm > not sure everyone who needs it should be reinventing the wheel... > > So is it fair to say that the sqlite3_file API methods are not useful for > this purpose? The docs are a bit sparse regarding their intended > purposes. > > On 10/10/07, John Stanton <[EMAIL PROTECTED]> wrote: >> >> There is a good case to have an Sqlite API call to take a snapshot of a >> database. It would integrate with the locking logic and secure an >> exclusive lock before taking the snapshot. That is a safer and handier >> approach than extracting a file descriptor and perhaps creating mayhem. >> >> Cyrus Durgin wrote: >> > Maybe it would help to state my use case: without this functionality, >> what >> > is the proper way to copy a database using the C API without >> > introducing >> a >> > race condition? >> > >> > On 10/9/07, Robert Simpson <[EMAIL PROTECTED]> wrote: >> > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] how to iterate on SELECT query results
SQLite use the 'cursor' style like SQL Server / ORACLE in stored procedures: you fetch in a unidirectional (forward only) way. To achieve bi-directional support you must have to store the results in memory as you fetch them (not so difficult to accomplish if you have enough memory to do it, a bit complicated if not). -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: terça-feira, 4 de setembro de 2007 17:17 To: sqlite-users@sqlite.org Subject: Re: [sqlite] how to iterate on SELECT query results Use sqlite3_step to read each row in sequence. Babu, Lokesh wrote: > Dear all, > > After doing some SELECT operation on a TABLE, say we get 100 result > items out of 1000 records, Is there any way where I can iterate > through this result set. i.e., Get N items out of 100, say get > previous 10, get next 10, etc, > > This should be done without creating a temporary table or virtual > tables. As there is overhead of space and time. > > If it is possible to use VIEWs then how can I? As I think VIEWs are > little better than temp table or virtual table. Please correct me if > I'm wrong. > > please reply, thanks in advance. > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] VFS in upcoming 3.5.0
You can create a 'shared memory VFS' to share a memory database against other thread / processes, and you can also 'copy' the RAW bytes of your memory with memcpy from/to another storage to accomplish the serialize / load you want to wire transfer SQLite memory databases. But this is not a simple code, may be after some development, it could be give to public domain and merged into SQLite if it prove to be robust and safe enough. -Original Message- From: Olaf Schmidt [mailto:[EMAIL PROTECTED] Sent: sábado, 1 de setembro de 2007 12:15 To: sqlite-users@sqlite.org Subject: [sqlite] VFS in upcoming 3.5.0 Hi, first - congrats to the planned changes in the new, upcoming version of SQLite. The new shared-cache behaviour sounds promising and also the new VFS-option. A few questions to VFS. 1. As known, an InMemory-DB is currently not (much) faster than working against a File. With the new VFS I think, that much faster InMemory- DB-Handling should be possible, is that right? 2. If so, is it planned, to automatically instantiate an appropriate (already built in) InMemory-VFS, if one sets the Filename-Param to ':memory:' in an Open-Call, so that InMemory DBs work against this implicite created MemVFS - meaning that the "Default-SQLite- engine" already implements such an "InMemory-VFS" for us "Wrapper- developers" (because you know best, how to do it in the fastest possible way and because of my following "feature-request" below)? ;-) 3. If you plan something like this, it would be very nice, if you could include (now that many new interfaces are coming in either way) an additional API-enhancement, wich would allow, to get the current "Byte-Content" of an InMemory-DB, wich makes use of this new (built in) InMemory-VFS? The background for these questions is, that we use SQLite behind an Appserver wich is currently able, to get Resultset- Objects at the serverside (done over our wrapper) and after retrieving such an Resultset, to serialize its "Query-Content" into a ByteArray, wich is then transferred over sockets back to the client. At the clientside we are able, to deserialize the Bytes and "materialize" a new Resultset-Object appropriately. With a built in InMemory-VFS (and its new "Dump-Interface") we could achieve many nice things in only one roundtrip. At the serverside we could attach an empty InMemory-DB (implicitely using the new MemVFS) to an already pooled SQLite-Connection and perform a bunch of "Insert Into-Queries", to create a small snapshot of e.g. a midsized "Master-Detail- Scenario". Now we could dump not only a "single-query-content" (as with our Resultsets currently), but could write a complete, related scenario (containing the prepared "InMemory-Tables") to a ByteArray and transfer *this* to the client. Now it would be great, if we could use the new MemVFS- interface, to create *and* initialize a new InMemory-DB at the clientside with the received ByteContent. This way, we could perform related queries (Joins, Filters, etc.) against the InMemory-DB (containing the midsize Master-Detail-Set) without doing any extra-roundtrips over the server. An already builtin InMemory-VFS would ease the burden of all wrapper-developers, to implement such kind of animal themselfes - and maybe such an implementation would help to cleanup (and speedup) the already contained InMemory-DB-Handling of the sqlite-engine too. What's your opinion on this feature-request? Best regards, Olaf Schmidt (developer of dhSQLite and dhRPCServer) -- View this message in context: http://www.nabble.com/VFS-in-upcoming-3.5.0-tf4364818.html#a12441170 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Table locked - why?
Did you closed the cursor opened at 'select *...' ? Thats probably the reason you have getting a 'table is locked' error. -Original Message- From: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] Sent: quinta-feira, 30 de agosto de 2007 19:24 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Table locked - why? Pls see if u hv an open sqlite3 terminal.sometimes this can also cause a prob with begin tx and just kept it open. regrads ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - Original Message - From: Yves Goergen <[EMAIL PROTECTED]> Date: Friday, August 31, 2007 3:41 am Subject: Re: [sqlite] Table locked - why? > On 30.08.2007 23:00 CE(S)T, [EMAIL PROTECTED] wrote: > > Probably another thread or process is reading from the database > > and does not want you to drop the table out from under it. > > Surely not. This database is there for testing and development > purposesonly and my application is the only one that opens it. It > doesn't use > multiple threads so there also can't be another thread locking it. > > -- > Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> > Visit my web laboratory at http://beta.unclassified.de > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] In memory database question
I'm thinking in a more generic way... using /dev/shm works on linux, but not on windows. Also, r/w support is a must have, so this approach cannot solve the problem. Since we have 'drivers' for windows / linux / etc, a 'memory' driver would be enought, simulating file opening / closing / deleting, creating the possibility of managing various in-memory databases at same time usable by the other threads of the process. There´s some planning to support a thing like this? It´s a bit crazy but a lot interesting. -Original Message- From: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] Sent: terça-feira, 28 de agosto de 2007 12:56 To: sqlite-users@sqlite.org Subject: Re: [sqlite] In memory database question Hi, I did tried something crazy like this (it worked, for read only DB only). I changed pread to preadCustom api.Then in preadCustom maintained a static fdArray. if fd is not listed then i mmap the whole file and the use the memcpy to return the data. There was significant performance again as there was no context switch. U can overload all the os wrappers to your custom impl and then share it with multiple threads. I had to do this as even mounting the file on /dev/shm there was no significant performance gain. Just a possible direction, regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - Original Message - From: Virgilio Alexandre Fornazin <[EMAIL PROTECTED]> Date: Tuesday, August 28, 2007 8:42 pm Subject: [sqlite] In memory database question > Hi > > > > There´s possible to share a sqlite3 handle to a memory database in all > threads of application? > > Or there´s a way to ?duplicate? the handle (sqlite_open() or > something like > that)? > > > > > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] In memory database question
Hi There´s possible to share a sqlite3 handle to a memory database in all threads of application? Or there´s a way to duplicate the handle (sqlite_open() or something like that)?