[sqlite] JOIN problem (works in postgres)

Hello, The following query does not work in Sqlite: SELECT i.user, ia.key, ia.value FROM invite AS i JOIN (invite AS j JOIN users AS u ON j.user = u.id AND u.canonical_username='ludde') ON i.parent = j.id LEFT JOIN inviteattr as ia ON ia.invite = i.id; It complains about "no such

[sqlite] Auto Vaccum and fragmentation?

Assuming I have an autovacuum database that primarily stores 32k blobs. If I add/remove lots of rows, will this lead to excessive fragmentation of the overflow chains, or does Sqlite do anything to try to unfragment the pages belonging to a single row? Thanks, Ludvig

Re: [sqlite] Non-amalgamized version

3.3.15.tar.gz Everything you need to build Sqlite on a variety of platforms is there. Well commented open source makes it simple to extend or modify. Ludvig Strigeus wrote: > Hi, > > I want a non-amalgamized version. I.e. I want to have the file structure > intact, and not everythin

Re: [sqlite] Non-amalgamized version

Hi, I want a non-amalgamized version. I.e. I want to have the file structure intact, and not everything in the same file. Thanks, Ludvig On 4/15/07, Jens Miltner <[EMAIL PROTECTED]> wrote: Am 15.4.07 um 14:00 schrieb Ludvig Strigeus: > Hi, > > Is it still possible to find a

[sqlite] Non-amalgamized version

Hi, Is it still possible to find a non-amalgamized zip file suitable for use to build sqlite on windows? (I.e. I need the "preprocessed" files where the unix tools have already been run). I would like the non-amalgamized files because I would like to modify it more easily. Can I just split the

Re: [sqlite] Supporting databases larger than 2GB on FAT filesystems?

Alright thanks! I will look into that. /Ludvig On 4/14/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "Ludvig Strigeus" <[EMAIL PROTECTED]> wrote: > Does Sqlite support databases larger than 2GB on FAT filesystems? SQLite supports large databases just f

[sqlite] Rollback journal bitmap size

I read this on Sqlite's webpage (http://www.sqlite.org/whentouse.html): When you start a transaction in SQLite (which happens automatically before any write operation that is not within an explicit BEGIN...COMMIT) the engine has to allocate a bitmap of dirty pages in the disk file to help it

Re: [sqlite] Supporting databases larger than 2GB on FAT filesystems?

I would like to have a single table larger than 2GB...though. /Ludvig On 4/13/07, John Stanton <[EMAIL PROTECTED]> wrote: It is limited by the maximum file size on your OS. You can make a multiple file database by ATTACHing more than one database. Ludvig Strigeus wrote: > Do

[sqlite] Supporting databases larger than 2GB on FAT filesystems?

Does Sqlite support databases larger than 2GB on FAT filesystems? If not, how hard would it be to add so it uses additional files for the pages that don't fit in the first file? Thanks Ludvig

[sqlite] Using the Sqlite Btree Backend directly

Hi, I'm looking at using Sqlite as a storage backend for a program. Using SQL is a little bit overkill and much more than we need. How complicated would it be to interface to the btree subsystem directly? Sqlite seems very modular from the looks of it, but has anyone attempted anything like this

[sqlite] Lemon grammar question

With Bison, you can do something like this (not quite bison syntax): myrule: TYPE IDENT {DoSomethingRightAfterIdent($1,$2); } LP more_rules RP; {DoSomethingAfterEverything($1,$2,$5); } I.e. you have a chunk of C code that's called in the middle of the processing of the production. (In the above

[sqlite] Lemon question

I'm using lemon to make a simple parser.I want to get more descriptive error messages. Right now, it prints Syntax error near '123'. Is there some way to make it print Syntax error near '123', expecting ';' or something similar? /Ludvig

Re: [sqlite] Slow SQL statements

Why doesn't SQL provide a utility function: maxv Then you could (almost) write it like this: SELECT maxv(sb, playerid) FROM batting WHERE playerid IN (SELECT player FROM fielding WHERE pos='3B' AND lgid='NL')); The semantics of maxv(arg, value) would be that it finds the maximum of arg,

[sqlite] Behavior on corrupt database

If I corrupt my database in certain ways, I can make Sqlite crash. Is this by design, or is it a bug? /Ludvig

[sqlite] unit tests

How do I run the unit tests in Linux? I've managed to build "tclsqlite3", but where do I go from there? /Ludvig

[sqlite] Slow SQL statements

Hello, Can someone come up with some slow SQL statements (that execute in like 2 seconds) that are not disk bound but CPU bound. Preferably single liners. I'm playing around with a profiler and trying to find bottlenecks in sqlite and optimizing them. /Ludvig

[sqlite] What does this code do?

In sqlite3VdbeRecordCompare() /* Read the serial types for the next element in each key. */ idx1 += sqlite3GetVarint32([idx1], _type1); if( d1>=nKey1 && sqlite3VdbeSerialTypeLen(serial_type1)>0 ) break; idx2 += sqlite3GetVarint32([idx2], _type2); if( d2>=nKey2 &&

[sqlite] Answer to Linux vs Windows performance issues

Stuff below relates to IDE drives. On Linux, the fsync() call doesn't actually force that the data reaches the physical disk platters. It just makes sure that the data is sent to the cache on the disk. On Windows, FlushFileBuffers() forces the disk to actually write the data to the physical

Re: Re: [sqlite] More on FlushFileBuffers / Windows performance

Christian Smith wrote: > No, because *every single* write to that handle will involve a sync to the > underlying device! That would decimate performance. > Using a single FlushFileBuffers batches multiple write's in a single sync > operation. > That this hurts performance on Windows says more

[sqlite] More on FlushFileBuffers / Windows performance

Link: http://searchstorage.techtarget.com/tip/1,289483,sid5_gci920473,00.html Quote: "FlushFileBuffers is an API call that forces all data for an open file handle to be flushed from the system cache and also sends a command to the disk to flush its cache (contrary to the name, this call

[sqlite] Possible alternative to FlushFileBuffers on Windows

How about using the FILE_FLAG_WRITE_THROUGH to CreateFile on Windows? Description: Instructs the system to write through any intermediate cache and go directly to disk. The system can still cache write operations, but cannot lazily flush them. I guess you can remove a few of the calls to

Re: [sqlite] Sqlite2 vs Sqlite3 benchmark

Dan Kennedy <[EMAIL PROTECTED]> wrote: > For SQLite 3, the default value of the 'synchronous' pragma > changed from "NORMAL" to "FULL". IIRC this means the disk is > synced 3 times instead of 2 for a small transaction. So this > might be what you're seeing. That is indeed the case. The sqlite

[sqlite] InnoDB Transactions

Quote: InnoDB must flush the log to disk at each transaction commit, if that transaction made modifications to the database. Since the rotation speed of a disk is typically at most 167 revolutions/second, that constrains the number of commits to the same 167/second if the disk does not fool

[sqlite] Small journal optimization?

Hello. I noticed that the sqlite code looks like below. On windows this will result in 5 system calls instead of 1 each time the journal is made. Why not batch it all together into a single write? /Ludvig rc = sqlite3OsWrite(>jfd, aJournalMagic, sizeof(aJournalMagic)); if( rc==SQLITE_OK ){ /*