[sqlite] Separate INTEGER and REAL affinity ?

I read in the "changes" page of the SQLite site that version 3.3.x of SQLite features "Separate INTEGER and REAL affinity". What does this exactly mean ? How is SQLite 3.3.x different from 2.8.x with respect to column affinity ? Thank you, bye

Re: [sqlite] Versioning in SQL database?

Hi, I acnnot get "IF EXISTS" to work for "DROP TABLE IF EXISTS tablename"; ver 3.2.8; I see "IF NOT EXISTS" started in ver 3.3.0, but "IF EXISTS" has been in the syntax page for at least months; am I doing something wrong? Randall

Re: [sqlite] Versioning in SQL database?

- Original Message - From: "Jim C. Nasby" <[EMAIL PROTECTED]> To: Sent: Wednesday, February 08, 2006 8:58 AM Subject: Re: [sqlite] Versioning in SQL database? On Tue, Feb 07, 2006 at 10:55:27AM -0500, Paul Tomblin wrote: I am putting together something

Re: [sqlite] Versioning in SQL database?

Paul Tomblin wrote: > > I am putting together something that will act like a Wiki for structured > data (in this case, airport and navigation aid data like id, location, > runways, etc). I currently store the data in an SQL databasee, but only > the "current" version. I want to allow people to

Re: [sqlite] Fedora Core 4 RPM Version of SQLite And A Sad Tale of PHP Configure Error In v3.3.3

Dan Kennedy wrote: configure:79872: checking for sqlite_open in -lsqlite It might be looking for sqlite version 2, not 3. Grep the configure script for the string "sqlite3_open", and then "sqlite_open". If you find the latter and not the former, it's version 2 you need to install.

Re: [sqlite] aynchronous loading

chetana bhargav wrote: Does sqlite provides asynchronous loading of data. Basically if I have something around 3000 records and want to do some query, instead of returning the result in one single query is it possible for me to relinquish the control to other apps so that I wont get a time out

Re: [sqlite] Versioning in SQL database?

On Tue, Feb 07, 2006 at 10:55:27AM -0500, Paul Tomblin wrote: > I am putting together something that will act like a Wiki for structured > data (in this case, airport and navigation aid data like id, location, > runways, etc). I currently store the data in an SQL databasee, but only > the

RE: [sqlite] How to create a queue using Sqlite

Thanks this helps a lot! I'll get on it right away. Kind regards, Lodewijk -Original Message- From: Christian Smith [mailto:[EMAIL PROTECTED] Sent: Monday, February 06, 2006 9:34 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to create a queue using Sqlite On Thu, 2 Feb

Re: [sqlite] More benchmarks

On Tue, Feb 07, 2006 at 07:54:19PM +0100, Nemanja Corlija wrote: > On 2/7/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote: > > You might want to put a legend on your results tables so it's clear > > what the numbers represent. I assume these are times but I didn't see > > anything so far that said. >

Re: [sqlite] More benchmarks

On Tue, Feb 07, 2006 at 08:07:03PM +0100, Nemanja Corlija wrote: > On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > AFAIK MySQL ships with a few different config files, so presumably > > choosing the appropriate one would be equivalent to what I provided for > > PostgreSQL. > Yes, and I

Re: [sqlite] More benchmarks

On Tue, Feb 07, 2006 at 11:51:22AM -0500, [EMAIL PROTECTED] wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: > > On Tue, Feb 07, 2006 at 09:54:22AM -0600, Jim C. Nasby wrote: > > > Hrm, that's rather odd. What does top show when it's running through > > > psql? Are the test scripts available for

RE: [sqlite] disk locality (and delta storage)

Trac (http://projects.edgewall.com/trac) is a VCS (Subversion) + Wiki + Ticketing... And is built on top of a sqlite db. It has a fair amount of installation pre-requisites but is a very clean interface and trys to adhere to KISS for their feature set. Its of course not usable if your still

Re: [sqlite] More benchmarks

On Tue, Feb 07, 2006 at 08:11:02PM +0100, Nemanja Corlija wrote: > On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > I see theh tcl now... is TCL piping into psql, or are there a set of raw > > files you could post or send me? > TCL generates files and then I'm piping those files to all

Re: [sqlite] Prepared statements

On Tue, 7 Feb 2006, [EMAIL PROTECTED] wrote: Thank you for your answer! Thanks the rest of you that gave me an answer to my problem! Marian Olteanu <[EMAIL PROTECTED]> wrote: Is there a way in SQLite to use real prepared statements? Statements with variables, that you fill after you compile

Re: [sqlite] SQLite to MySQL

Jim C. Nasby wrote: On Mon, Feb 06, 2006 at 05:30:39PM +0100, Laurent Goussard wrote: Hi there, I use SQLite on my website for 2 years now. I do like SQLite a lot and will use it for a lot of new web projects but, because I got more and more traffic, I consider to move this one to MySQL in

Re: [sqlite] More benchmarks

On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > I see theh tcl now... is TCL piping into psql, or are there a set of raw > files you could post or send me? TCL generates files and then I'm piping those files to all databases except Firebird, which doesn't accept commands from stdin so I'm

Re: [sqlite] More benchmarks

On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > AFAIK MySQL ships with a few different config files, so presumably > choosing the appropriate one would be equivalent to what I provided for > PostgreSQL. Yes, and I installed it as a developer machine. That should be the least performant one of

Re: [sqlite] More benchmarks

> > You might want to put a legend on your results tables so it's clear > > what the numbers represent. I assume these are times but I didn't see > > anything so far that said. > Its time in seconds. > "sync" in case of SQLite is PRAGMA synchronous=FULL; while in case of > MySQL it signifies usage

Re: [sqlite] More benchmarks

Jim C. Nasby said: > Finally, and luckily this applies to SQLite as well so this doesn't get > too off topic :), PLEASE seek help/advice BEFORE spending a bunch of > money on a big server! All too often I see people who spend a load of $$ > on equipment they didn't need or won't be able to

Re: [sqlite] More benchmarks

On 2/7/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote: > You might want to put a legend on your results tables so it's clear > what the numbers represent. I assume these are times but I didn't see > anything so far that said. Its time in seconds. "sync" in case of SQLite is PRAGMA synchronous=FULL;

Re: [sqlite] More benchmarks

On 2/7/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Looks like I need to work on Test 6 some, huh? Your suggestion > that the servers are creating a temporary index to do the join > was my first throught too. I wonder if I should look into teaching > that trick to SQLite. Do you think you

[sqlite] New User With Basic Install/Configure Questions

  I am a new user with some basic very questions.  My specific SQLite questions are in regard to the installation/building of SQLite using configure, release 3.3.2, the “Optional Features” cited when “./configure –-help” is invoke from the command line shell.  I have attached a file with

Re: [sqlite] Prepared statements

Marian Olteanu wrote: Is there a way in SQLite to use real prepared statements? Statements with variables, that you fill after you compile the query and reuse then reuse? I imagine something like: prepared_statement ps = db.prepare( "select * from tbl where c = %q' ); for( int i = 0 ; i <

Re: [sqlite] More benchmarks

On Tue, Feb 07, 2006 at 10:04:43AM -0500, Clay Dowling wrote: > > Jim C. Nasby said: > > > Well, that certainly won't help things... at a minimum, on your machine, > > you should change the following: > > shared_buffers=1 > > effective_cache_size=10 > > > > The following should also

[sqlite] Re: Prepared statements

Marian Olteanu wrote: Is there a way in SQLite to use real prepared statements? Statements with variables, that you fill after you compile the query and reuse then reuse? See sqlite3_prepare, sqlite3_bind*, sqlite3_step, sqlite3_reset Igor Tandetnik

Re: [sqlite] Prepared statements

Marian Olteanu wrote: Is there a way in SQLite to use real prepared statements? Statements with variables, that you fill after you compile the query and reuse then reuse? [...] SQLite 3 introduced prepared statements. Read http://www.sqlite.org/capi3.html for enlightenment. -- Gerhard

Re: [sqlite] More benchmarks

On Tue, Feb 07, 2006 at 09:54:22AM -0600, Jim C. Nasby wrote: > Hrm, that's rather odd. What does top show when it's running through > psql? Are the test scripts available for download? I'll try this on my > machine as well... I see theh tcl now... is TCL piping into psql, or are there a set of

Re: [sqlite] More benchmarks

On Tue, Feb 07, 2006 at 08:07:53AM -0500, [EMAIL PROTECTED] wrote: > It is also interesting to note that PostgreSQL get significantly > slower in Test 13 (join with an index) versus Test 6 (the same > join without an index). What is that about? Firebird shows the > same effect, but less

[sqlite] Prepared statements

Is there a way in SQLite to use real prepared statements? Statements with variables, that you fill after you compile the query and reuse then reuse? I imagine something like: prepared_statement ps = db.prepare( "select * from tbl where c = %q' ); for( int i = 0 ; i < 100 ; i++ ) {

[sqlite] RE: [RBL] Re[2]: [sqlite] R: [sqlite] Snapshot database creation performance

Hi Teg, Presumably you have a transaction in place around the whole of your inserts and that you have the PRAGMA synchronous = OFF; set. Have you looked at perhaps not creating the database on the server, but merely creating the INSERT statements in one big file that you compress and send down

[sqlite] Versioning in SQL database?

I am putting together something that will act like a Wiki for structured data (in this case, airport and navigation aid data like id, location, runways, etc). I currently store the data in an SQL databasee, but only the "current" version. I want to allow people to edit that data, but that means

Re: [sqlite] More benchmarks

On Tue, Feb 07, 2006 at 10:08:23AM +0100, Nemanja Corlija wrote: > On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > On Tue, Feb 07, 2006 at 07:31:50AM +0100, Nemanja Corlija wrote: > > > > Well, that certainly won't help things... at a minimum, on your machine, > > > > you should change the

Re[2]: [sqlite] R: [sqlite] Snapshot database creation performance

Hello Andrew, My purpose is primarily disk storage savings, the data's mainly text so it's highly compressible. 500K on disk chunks of data decompress out to about 8 megabytes of text. What compression scheme do they use? I might consider trading some disk space for faster

Re: [sqlite] More benchmarks

On 2/6/06, Nemanja Corlija <[EMAIL PROTECTED]> wrote: > I've posted some benchmarks between SQLite, PostgreSQL, MySQL and FirebirdSQL. > > Details at http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison You might want to put a legend on your results tables so it's clear what the numbers

Re: [sqlite] R: [sqlite] Snapshot database creation performance

On Tue, Feb 07, 2006 at 08:51:43AM -0500, Teg wrote: > My application uses compressed data (gzip) but, the tradeoff to small > data files is exceptionally heavy CPU usage when the data is > decompressed/compressed. Incidentally, the MonetDB folks have done research on that sort of thing. In

Re: [sqlite] disk locality (and delta storage)

Nathaniel Smith <[EMAIL PROTECTED]> wrote: > > So and rows are basically written to the file in the same order > that the INSERT statements are executed? Right. If there are no free pages in the database file (which is the usual case for Monotone, I expect) then new pages are allocated from the

Re: [sqlite] New benchmark comparisons

- Original Message - From: "Brandon, Nicholas (UK)" <[EMAIL PROTECTED]> Just had a quick look and noticed a peculiarity. SQLite seems to do pretty well across the board except in the graph for "INSERT 1 Row Test (implicit transaction)". It seemly does well except when using the SD

Re: [sqlite] New benchmark comparisons

- Original Message - From: "Denis Sbragion" <[EMAIL PROTECTED]> Hello Robert, At 06.36 06/02/2006, you wrote: ... Also coming up next week are comparisons vs. Sql Server Mobile, which is where SQLite is really racking up some wins recently. interesting but, be careful! Last time I

[sqlite] Snapshot database creation performance

Hallo! I'm not using compressed data while SQLite is creating the database. I create it in the standard way, and AFTER it is created and closed I compress the just generated file and I transfer it. The compression is used only for performance reasons during the file transfer. I cannot use a

[sqlite] Snapshot database creation performance

I already tried. I could see no particular effect or, at least, not so dramatic. -Messaggio originale- Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Inviato: martedì, 7. febbraio 2006 14:22 A: sqlite-users@sqlite.org Oggetto: Re: [sqlite] Snapshot database creation performance

Re: [sqlite] R: [sqlite] Snapshot database creation performance

Hello Michele, Perhaps replacing the "os_win.c" (or whatever your OS) with functions the simulate disk IO through your compressed stream functions. The biggest problem I see is random access. Typically there is no quick seeking within compressed data, you just have to "Read" to the point you want

Re: [sqlite] More benchmarks

Jim C. Nasby said: > Well, that certainly won't help things... at a minimum, on your machine, > you should change the following: > shared_buffers=1 > effective_cache_size=10 > > The following should also help: > work_mem=1 > vacuum_cost_delay=50 > autovacuum=on >

Re: [sqlite] disk locality

On Wed, Feb 01, 2006 at 08:56:37PM -0800, Joe Wilson wrote: > Another question... Does Monotone still Base64 encode all its data before > putting it into blobs? > If so, using raw binary SQLite blobs would likely give Monotone a 33% speedup > and smaller > database. It does, actually, but

Re: [sqlite] disk locality (and delta storage)

Thanks for the helpful reply. Sorry I've taken so long to get back to this; I've had some hardware trouble and am only catching up on email now... On Wed, Feb 01, 2006 at 07:27:06AM -0500, [EMAIL PROTECTED] wrote: > Nathaniel Smith <[EMAIL PROTECTED]> wrote: > > I was wondering if there were any

Re: [sqlite] Snapshot database creation performance

"Clinco, Michele" <[EMAIL PROTECTED]> wrote: > > This operation is quite heavy and takes several seconds (20 sec on my > laptop, generating a 1700k data file) > > Since the file is absolutely temporary, I installed a ramdisk driver and > I tried to create the file in this driver instead that in

[sqlite] aynchronous loading

Hi, Does sqlite provides asynchronous loading of data. Basically if I have something around 3000 records and want to do some query, instead of returning the result in one single query is it possible for me to relinquish the control to other apps so that I wont get a time out error. and

[sqlite] R: [sqlite] Snapshot database creation performance

You understood perfectly. The ramdisk is not the goal, but just a mean to obtain the 'best performance' possible goal. I don't need the ramdisk at all, just a 'all in memory processing'. The solution to this problem should help many users because I think we are many that use a SQLite database

Re: [sqlite] More benchmarks

Nemanja Corlija <[EMAIL PROTECTED]> wrote: > I've posted some benchmarks between SQLite, PostgreSQL, MySQL and FirebirdS= > QL. > > Details at http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison > Thanks for your hard work, Nemanja! This is useful information. Looks like I need to work on

Re: [sqlite] Snapshot database creation performance

On Tue, Feb 07, 2006 at 07:43:44AM -0500, Andrew Piskorski wrote: > On Tue, Feb 07, 2006 at 12:52:08PM +0100, Clinco, Michele wrote: > > My program is written in .Net and the compression routines I'm using are > > stream based, so I need to create a memory stream from the internal > > buffers

Re: [sqlite] Snapshot database creation performance

On Tue, Feb 07, 2006 at 12:52:08PM +0100, Clinco, Michele wrote: > The question is: > Is there a way that allows to create the database in memory (this is > present: use :memory: as file name) and to use the allocated memory > before it is deallocated when database is closed? I'm confused, why

RE: [sqlite] Problem with complex query

Scrap that... I think I found the problem and its nowhere near SQLite... > -Original Message- > From: Bielik, Robert > Sent: Tuesday, February 07, 2006 1:22 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Problem with complex query > > > Well, it isn't that complex really. I have

RE: [sqlite] New benchmark comparisons

>It did pretty well against Sql Server Mobile as well. I just posted those >benchmarks here: >http://sqlite.phxsoftware.com/forums/623/ShowPost.aspx Just had a quick look and noticed a peculiarity. SQLite seems to do pretty well across the board except in the graph for "INSERT 1 Row Test

[sqlite] Problem with complex query

Well, it isn't that complex really. I have a database structure with T_ITEM possibly having T_ITEM children (connected by T_MAP (childid, parentid)). Also connected to a T_ITEM (possibly) is a T_DATA (id integer, size integer, data blob). I wan't to retrieve all T_ITEM children of a T_ITEM

[sqlite] Snapshot database creation performance

Hallo. I have an application that uses a central database (Oracle or SQL server) and creates a copy of the data on an SQLite database file. This file contains a read only copy of more or less all the central database tables and contains about 50 tables and may contain up to 100k records spread

Re: [sqlite] More benchmarks

On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > On Tue, Feb 07, 2006 at 07:31:50AM +0100, Nemanja Corlija wrote: > > > Well, that certainly won't help things... at a minimum, on your machine, > > > you should change the following: > > > shared_buffers=1 > > > effective_cache_size=10 >

Re: [sqlite] More benchmarks

On Tue, Feb 07, 2006 at 07:06:26AM +0100, Nemanja Corlija wrote: > On 2/7/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > Did you happen to do an analyze? > Nope. All databases are run as default as possible. And, they all get > same scripts to execute. Then your results for PostgreSQL are

Re: [sqlite] Indexing

On 2/7/06, chetana bhargav <[EMAIL PROTECTED]> wrote: > I am sure that this question would have been asked many times earlier also. > I am new to > this list, can any one point me about some info on indexing in SQLite. The > time efficiency > and space it requires. Some Do's and Dont's about

Re: [sqlite] New benchmark comparisons

Hello Robert, At 06.36 06/02/2006, you wrote: ... Also coming up next week are comparisons vs. Sql Server Mobile, which is where SQLite is really racking up some wins recently. interesting but, be careful! Last time I checked benchmarking was explicitely forbidden by the SQL Server end user

[sqlite] Indexing

Hi, I am sure that this question would have been asked many times earlier also. I am new to this list, can any one point me about some info on indexing in SQLite. The time efficiency and space it requires. Some Do's and Dont's about indexing. Thanks for the help in advance.