[sqlite] Information Schema Views
Hello All, I have created a new wiki page off the main wiki page called Information Schema. Here is the link http://www.sqlite.org/cvstrac/wiki?p=InformationSchema The SQL standard INFORMATION_SCHEMA views are a standard way of retrieving schema information from any SQL standardised database. I thought it would be a great idea to create these views for SQLite and place the creation script on this page. People can then add them to there database as they need. Perhaps Richard could add them one day to the system one day. If anyone has any of these views perhaps you could add them to the page or send them to me. regards Greg O
Re: [sqlite] Does anybodu have tried TWS (http://www.hwaci.com/sw/tws)
borivoj wrote: Hello everybody, I would like to know is there anybody other willing to share experiences in TWS - all-in-one web server, tcl and sqlite combination provided by Richard on http://www.hwaci.com/sw/tws Since I am mostly on windows I've managed to find windows version of TWS, tested it and it looks very promising , at least for a small intranet server. what worries me is that it looks rather old: "TWS was released as beta on Feb 17, 2001." and that there is no mention in the sqlite groups since the beginning. (on yahoo groups). I found hard to believe that nobody else noticed TWS. Why? TWS never picked up much mindshare - everybody wanted to used LAMP. Even those who were not enamored with LAMP were only interested in algol-derived languages, not more powerful lisp-family languages like Tcl which TWS uses, so nobody paid TWS much mind. I, therefore, didn't maintain the website. But rest assured, TWS continues to be used. I continue to use the original TWS internally. Decendents of TWS have found their way into commercial products I've done for customers. Those products are currently in active use around the world. If enough people bug me about it, I'll go in and update the public code base for TWS with some more recent enhancements and bug fixes. Note: CVSTrac was originally a bunch of TWS scripts. Only later did rewrite CVSTrac in C so that it would work with apache. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Does anybodu have tried TWS (http://www.hwaci.com/sw/tws)
Hello everybody, I would like to know is there anybody other willing to share experiences in TWS - all-in-one web server, tcl and sqlite combination provided by Richard on http://www.hwaci.com/sw/tws Since I am mostly on windows I've managed to find windows version of TWS, tested it and it looks very promising , at least for a small intranet server. what worries me is that it looks rather old: "TWS was released as beta on Feb 17, 2001." and that there is no mention in the sqlite groups since the beginning. (on yahoo groups). I found hard to believe that nobody else noticed TWS. Why? regards Borivoj - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] X-Compiling SQLite using MinGW32 ... a quick question ...
Is anyone aware of problems in cross compiling the SQLite software for Windows on FreeBSD? Configure seems to pooch out in various incarnations, the most recent of which is: ../configure --target=mingw32 --prefix=/usr/local/mingw --host=i386-freebsd configure: error: unable to find a compiler for building build tools ../configure --build=i386-freebsd --target=i386-mingw32 --prefix=/usr/local/mingw --host=mingw32 gives a similar error. I can't seem to find the correct set of configure options to build the .dll and .a libraries for Windows from the MingW32. Does anyone have any data? I'd really like to build both the libSqlite.a AND sqlite.dll's if possible. Cheers, Rob. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] 16MB per row
SQLite by default limit the total amount of data in a single row to 1 megabyte. I need to increase this limit to 16 megabytes ... but I am wonder of some possible performance issues... Anyone can give more more details? Thank you Marco Bambini - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] sqlite not sharing page cache across processes?
On Mon, 22 Mar 2004 14:35:23 -0500, "Doug Currie" <[EMAIL PROTECTED]> said: > It has no way of knowing (save diffing the db) that "there have been > no write transactions." well, that should in principle be easy to accomplish, shouldn't it? For example by having any writer increment a counter held in the meta information in the first page of the file (PageOne or whatever). > > While I'm making suggestions despite near-total ignorance > > of sqlite :), have you measured what improvement would > > be obtained by using readonly mmap() for readers, rather > > read()? It would save some malloc churning as well as > > a memory copy. Performance could be additionally tweaked > > with madvise(SEQUENTIAL) when suitable. > > These functions are not in ANSI C. No, but they are POSIX. And there are lots of functions already in the sqlite code base that are neither ANSI nor POSIX (primarily for windows or mac). There are several operating systems, such as Solaris that implement their "cp" using mmap rather than read, and have done so for ages. So this is really not some piece of exotica. There probably are some embedded systems that do not offer mmap. There are circumstances when mmap will not perform as well as read. For example, the sqlite case of using a page only once is pessimal for mmap, because of TLB bookkeeping overhead on setup and unmapping. Use of mmap would probably make sqlite even more vulnerable to NFS than it is already. I would not suggest mmap as the only solution; as with web servers, I would suggest the strategy as a configurable option. Also, even I would hesitate over suggesting mmap for writers, without a lot of experimentation. On Mon, 22 Mar 2004 14:56:35 -0500, "D. Richard Hipp" <[EMAIL PROTECTED]> said: > Mark D. Anderson wrote: > > Have you measured what improvement would be obtained by using readonly mmap() > > for readers, rather read()? > > SQLite supports files that are larger than 4GB. I don't think > you can mmap() a file that big on a machine with a 32-bit > address space. You'd have to use mmap64 for that, if it was desirable to use mmap at all. -mda - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Feature of "FROM" clause
Hi, given the following tables: CREATE TABLE table_1(id INTEGER PRIMARY KEY,column VARCHAR(64)); CREATE TABLE table_2(id INTEGER PRIMARY KEY,column VARCHAR(64)); I realized that the following query isn't valid in SQLite: SELECT * FROM ( table_1 INNER JOIN ( SELECT * FROM table_2 WHERE column = 'whatever' ) AS inner_1 ON table_1.id = inner_1.id ); It returns error message: SQL error: near "table_1": syntax error But if I write it like that it works just fine: SELECT * FROM table_1 INNER JOIN ( SELECT * FROM table_2 WHERE column = 'whatever' ) AS inner_1 ON table_1.id = inner_1.id ; It also works fine if I write the query like that: SELECT * FROM ( select * from table_1 INNER JOIN ( SELECT * FROM table_2 WHERE column = 'whatever' ) AS inner_1 ON table_1.id = inner_1.id ); To me the first query should be valid. it seems the SQL engine desn't like having a table name right after an opening parenthesis. Are there any standards regarding parenthesis in SQL92? Thank you Simon B.
RE: [sqlite] Precompiled queries forcing db syncs?
> -Original Message- > From: Dan Thill [mailto:[EMAIL PROTECTED] > Sent: Monday, March 22, 2004 2:03 PM > To: Williams, Ken > Cc: [EMAIL PROTECTED] > Subject: Re: [sqlite] Precompiled queries forcing db syncs? > > > Ahh. The archives I was browsing must be a few days behind. Actually, I couldn't find that message from DRH in my list mailbox either, I only found the *reply* to it from [EMAIL PROTECTED] on 3/18. Maybe DRH accidentally only sent his message to Dave. It's an important point though, and I haven't seen it yet in the docs, though I might have overlooked it. -Ken - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Precompiled queries forcing db syncs?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 In short, when I switch to precompiled queries, according to gprof, it looks like the DB is being synched after every call, even though I've issued PRAGMA synchronous=OFF. As a result, using precompiled queries is an order of magnitude slower. Make sure you're compiling the queries *inside* your transaction boundaries. According to a message from DRH a few days ago, this is necessary to avoid syncing: Ahh. The archives I was browsing must be a few days behind. Worked like a charm. Also discovered that the mcount and _moncount are symbols inserted by gcc when profiling is enabled. Many thanks. I'd been stumped for a few days on this one. At least now I have a decent knowledge of the SQLite source :) - -dan -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (Darwin) iD8DBQFAX0aBU/zGB5xpbyoRAn/QAJ9O4FrBjPYqfqASXcZXRzTndYb+sQCeNK2c 4KxbYyRT2hYCsBc/CNTM7M0= =ugHG -END PGP SIGNATURE- - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] sqlite not sharing page cache across processes?
Mark D. Anderson wrote: > Have you measured what improvement would be obtained by using readonly mmap() > for readers, rather read()? SQLite supports files that are larger than 4GB. I don't think you can mmap() a file that big on a machine with a 32-bit address space. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] sqlite not sharing page cache across processes?
Monday, March 22, 2004, 1:46:00 PM, Mark wrote: >> I have run experiments to see how much faster SQLite might >> work if it didn't invalidate its cache so quickly. The >> answer is about 15%. So it is probably worth looking into >> someday. Note that such a change will be backwards but >> not forwards compatible. Suppose this change happened >> for version 2.9.0. Then a 2.9.0 library would be able >> to read and write a 2.8.13 database file, but a 2.8.13 >> library would not be able to read and write a 2.9.0 >> database file. Such a change it not to be made lightly >> and without careful consideration. > I don't follow. Why would this cause compatibility issues? Note that presently (<= 2.8.13), the only mechanism used to coordinate transactions is the file lock. > Presumably some benefit could be obtained just by > not throwing away the cache at the SQLITE_READLOCK to > SQLITE_UNLOCK transition, but waiting until the next > SQLITE_UNLOCK to SQLITE_READLOCK transition. > If at that time there have been no write transactions > since the cache was created, it can continue to use > it, otherwise it would then throw it away. It has no way of knowing (save diffing the db) that "there have been no write transactions." [...] > While I'm making suggestions despite near-total ignorance > of sqlite :), have you measured what improvement would > be obtained by using readonly mmap() for readers, rather > read()? It would save some malloc churning as well as > a memory copy. Performance could be additionally tweaked > with madvise(SEQUENTIAL) when suitable. These functions are not in ANSI C. e - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] sqlite not sharing page cache across processes?
> I have run experiments to see how much faster SQLite might > work if it didn't invalidate its cache so quickly. The > answer is about 15%. So it is probably worth looking into > someday. Note that such a change will be backwards but > not forwards compatible. Suppose this change happened > for version 2.9.0. Then a 2.9.0 library would be able > to read and write a 2.8.13 database file, but a 2.8.13 > library would not be able to read and write a 2.9.0 > database file. Such a change it not to be made lightly > and without careful consideration. I don't follow. Why would this cause compatibility issues? Presumably some benefit could be obtained just by not throwing away the cache at the SQLITE_READLOCK to SQLITE_UNLOCK transition, but waiting until the next SQLITE_UNLOCK to SQLITE_READLOCK transition. If at that time there have been no write transactions since the cache was created, it can continue to use it, otherwise it would then throw it away. Of course, that is just one example. If there was tracking of highest transaction on a per-table basis, and there was only one table's rows per page, then pages could be selectively invalidated on a per-page basis, not just the whole database. While I'm making suggestions despite near-total ignorance of sqlite :), have you measured what improvement would be obtained by using readonly mmap() for readers, rather read()? It would save some malloc churning as well as a memory copy. Performance could be additionally tweaked with madvise(SEQUENTIAL) when suitable. -mda - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] sqlite not sharing page cache across processes?
Mark D. Anderson wrote: My reading of the code has the readers flushing their cache often, and not using the journal at all to maintain consistency. My reading has the reader start with nothing in the cache, lock the database with a read lock, perform as many SQL queries as are in the single text string it's currently parsing, and flush its cache around the time it unlocks the database. So if the cache is thrown away after every transaction, does that mean that if I do "select * from mytable" twice in a row (in different transactions), that there will be the same number of read() system calls for the second one? Even if there are no intervening changes? That hardly seems optimal, even within the constraints of the sqlite architecture. Yes, that is what it means. You can make it reuse the cache by putting both SELECTs inside a BEGIN...COMMIT. But in so doing, you limit yourself to a single reader. I have run experiments to see how much faster SQLite might work if it didn't invalidate its cache so quickly. The answer is about 15%. So it is probably worth looking into someday. Note that such a change will be backwards but not forwards compatible. Suppose this change happened for version 2.9.0. Then a 2.9.0 library would be able to read and write a 2.8.13 database file, but a 2.8.13 library would not be able to read and write a 2.9.0 database file. Such a change it not to be made lightly and without careful consideration. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] sqlite not sharing page cache across processes?
> My reading of the code has the readers flushing their cache often, and > not > using the journal at all to maintain consistency. My reading has the > reader start with nothing in the cache, lock the database with a read > lock, perform as many SQL queries as are in the single text string it's > currently parsing, and flush its cache around the time it unlocks the > database. So if the cache is thrown away after every transaction, does that mean that if I do "select * from mytable" twice in a row (in different transactions), that there will be the same number of read() system calls for the second one? Even if there are no intervening changes? That hardly seems optimal, even within the constraints of the sqlite architecture. -mda - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] sqlite on vxworks or vrtx?
Hi, I'm new to this list, and have been given a task to investigate various alternatives for databases on embedded systems. I like what I have seen of sqlite so far! On the sqlite.org wiki under "when to use sqlite", there is the following: "# Embedded devices and applications Because an SQLite database requires little or no administration, SQLite is a good choice for devices or services that must work unattended and without human support. SQLite is a good fit for use in cellphones, PDAs, set-top boxes, and/or appliances. It also works well as an embedded database in downloadable consumer applications." Based on size, speed, and performance the above claim appears to be true. However, a lot of set-top boxes out there are not running Linux or WinCE, but are instead running vxworks or even vrtx. Has anyone ported/compiled sqlite to run on either of these OSes? Does anyone have a sense as to how hard it might or might not be if it doesn't already exist? Maybe it's not too hard if they're both POSIX compliant? I know that Berkeley DB from sleepy cat has a vxworks port, but I would really prefer to pursue sqlite. Any thoughts/hints welcomed! --Brett Granger - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Precompiled queries forcing db syncs?
> -Original Message- > From: Dan Thill [mailto:[EMAIL PROTECTED] > Sent: Monday, March 22, 2004 10:34 AM > To: [EMAIL PROTECTED] > Subject: [sqlite] Precompiled queries forcing db syncs? > > In short, when I switch to precompiled queries, according to gprof, it > looks like the DB is being synched after every call, even though I've > issued PRAGMA synchronous=OFF. As a result, using > precompiled queries is an order of magnitude slower. Make sure you're compiling the queries *inside* your transaction boundaries. According to a message from DRH a few days ago, this is necessary to avoid syncing: On Mar 18, 2004, at 12:14 PM, D. Richard Hipp wrote: > To run a query within a transaction, you also have to compile > it within a transaction. To run a query outside of a transaction > it should be compiled outside of the transaction. -Ken - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Precompiled queries forcing db syncs?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I'm running into a problem using the experimental precompiled query API. Yes, I know it's experimental, but this is pretty serious, and no one on this list, or google has posted anything similar. In short, when I switch to precompiled queries, according to gprof, it looks like the DB is being synched after every call, even though I've issued PRAGMA synchronous=OFF. As a result, using precompiled queries is an order of magnitude slower. I need to squeeze every last bit of performance I can, as I've only got two simple queries that I'm using (an insert or an update) and I'm going to be issuing billions of them. I only have one table with 6 columns numerical columns. The primary key is composed of two columns. However, the PRIMARY KEY() in the CREATE TABLE declaration doesn't have an effect, and neither does the presence or absence of indices. I'm running under OS X 10.3 (compiled with --disable-shared). Below are the top consumers in my gprof output. Anyone have any ideas? Especially the _moncount, mcount functions (which aren't part of SQLite, I realize)? The program runs below issue a total of 100k inserts. Thanks in advance, Dan With Precompiled Headers - - % cumulative self self total time seconds secondscalls ms/call ms/call name 18.7 4.98 4.98 _moncount (5175) 12.8 8.39 3.41 mcount (265) 4.9 9.70 1.3199103 0.01 0.12 _sqliteVdbeExec [6] 3.4 10.60 0.90 _szone_malloc [19] 3.2 11.44 0.84 1107279 0.00 0.00 _sqliteOsRead [22] 3.2 12.28 0.84 _szone_free [23] 3.0 13.08 0.80 1013033 0.00 0.00 _sqliteOsWrite [24] 2.8 13.83 0.75 1422484 0.00 0.00 _sqliteOsSeek [25] 1.7 14.29 0.46 14461558 0.00 0.00 _cellSize [32] 1.7 14.75 0.46 1981980 0.00 0.00 _initPage [21] 1.5 15.16 0.41 4714055 0.00 0.00 _fileBtreeKeyCompare [34] 1.5 15.55 0.39 2291775 0.00 0.00 _sqlitepager_get [9] 1.2 15.87 0.32 _szone_size [38] 1.2 16.18 0.31 _free [40] 1.1 16.48 0.3098903 0.00 0.01 _sqliteOsClose [28] 1.1 16.77 0.29 _malloc_zone_malloc [42] 1.1 17.05 0.28 395610 0.00 0.00 _sqliteOsReadLock [39] 1.0 17.32 0.27 _bcmp [46] 1.0 17.58 0.26 2756851 0.00 0.00 _sqlitepager_unref [29] 0.9 17.82 0.2498903 0.00 0.00 _sqliteOsDelete [49] 0.9 18.06 0.24 _small_free_list_add_ptr [50] 0.9 18.29 0.23 _access [52] 0.8 18.51 0.22 1637526 0.00 0.00 _allocateSpace [45] 0.7 18.70 0.19 296709 0.00 0.00 _sqliteOsUnlock [53] 0.7 18.88 0.18 _malloc_zone_free [55] 0.6 19.05 0.17 _malloc [58] 0.6 19.21 0.16 1284142 0.00 0.00 _moveToChild [10] 0.6 19.37 0.1698904 0.00 0.01 _sqliteOsOpenExclusive [31] Without Precompiled Queries - --- % cumulative self self total time seconds secondscalls ms/call ms/call name 24.0 6.12 6.12 _moncount (5171) 20.4 11.31 5.19 mcount (270) 3.6 12.22 0.91 _szone_malloc [15] 3.5 13.11 0.89 _szone_free [16] 2.1 13.64 0.5399103 0.01 0.04 _sqliteVdbeExec [8] 1.6 14.04 0.40 4712506 0.00 0.00 _fileBtreeKeyCompare [20] 1.5 14.42 0.38 7419421 0.00 0.00 _yy_find_shift_action [21] 1.5 14.79 0.37 7419418 0.00 0.00 _yy_shift [22] 1.4 15.15 0.36 __ZNSs9_M_mutateEmmm [23] 1.3 15.48 0.33 _szone_size [25] 1.2 15.79 0.31 3660038 0.00 0.00 _sqliteParser [9] 1.2 16.09 0.30 4154793 0.00 0.00 _sqliteGetToken [18] 1.1 16.37 0.28 _free [31] 1.1 16.64 0.27 3759386 0.00 0.00 _yy_reduce [10] 0.9 16.88 0.2498901 0.00 0.02 _sqliteInsert [12] 0.9 17.11 0.23 2777662 0.00 0.00 _sqlitepager_unref [32] 0.9 17.34 0.23 _malloc_zone_malloc [36] 0.8 17.55 0.2199103 0.00 0.04 _sqliteRunParser [6] 0.8 17.75 0.20 1657589 0.00
Re: [sqlite] Performance problem
> SQLite only uses a single index per table on any give query. > This is unlikely to change. Would it be able to use a multi-column query on ipnode + author? Hugh > Shi Elektronische Medien GmbH, Peter Spiske wrote: > > > > the following simple query is very slow: > > SELECT title FROM t1 WHERE ipnode='VZ' ORDER BY author; > > > > The database is about 250 MB in size and the table the query is run > > against > > has 12 cols and 120,000 rows. > > Every col has an index. > > The above query returns about 80% of the records. > > As soon as the ORDER BY statement is left away, the query ist fast. > > > > SQLite only uses a single index per table on any give query. > This is unlikely to change. > > Since your query is returning 80% of the rows in the table, > the use of an index to implement the WHERE clause is not > really helping you any. But the simple-minded query > optimizer of SQLite does not realize this. SQLite always > prefers to use an index to implement the WHERE clause when > it can. SQLite will also use that same index to implement > ORDER BY, if possible, or if no index was helpful for > implementing WHERE it will try to find an index to implement > ORDER BY. > > In your case, the best solution would be to trick sqlite into > not using the index on the WHERE clause. This will make it > use the index to implement ORDER BY and you should get much > better performance. I suggest trying this query: > > SELECT title FROM t1 WHERE ipnode||'x'='VZx' ORDER BY author; > > By appending the string 'x' onto the end of the ipnode column > prevents the query optimizer from use an index on ipnode. This > leave the optimizer free to use an index to implement ORDER BY. > > The other thing you would try is to DROP the index on the > ipnode column. > > > -- > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
