Re: [sqlite] ANN: alpha DB sqlite util for MacOS X 10.3
At 11:22 PM -0500 12/13/04, Fernando Morgan wrote: For MacOS X 10.3 users; I was going to start to using sqlite in a project, but I need first to create a skeleton of a browser to add some of my data. If someone wants to use it (disclaimer: version 0.1 software), it's here: http://homepage.mac.com/fernandoluis/.cv/fernandoluis/Public/SquidSQL.zip-link.zip (368k) On starting, it open a file browser to open the db file. If this is cancel, it opens a Save File dialog and can create new db files. If this is also cancelled, well.. there's not much you can do besides Command-Q. What is working: the table browser; it allows for viewing the data and to insert/update and delete rows in the table. Table management (creating tables and dropping them) isn't working yet (some capability to create tables with text fields). Done with SQLite 3.0.8. Fernando Thanks for posting that! I'll be sure to find it useful. -- Darren Duncan
Re: [sqlite] absolute vs. relative path to database on Cygwin
Greg Miller wrote: You used a backslash, escaping the 'c' character. Notice that the error message refers to "c:cygwin" rather than "c:\cygwin", which isn't equivalent. Try "ls c:/cygwin" instead. I thought I had before posting... but you're right, this works! -Alan -- Alan Mead - [EMAIL PROTECTED] People often find it easier to be a result of the past than a cause of the future.
Re: [sqlite] absolute vs. relative path to database on Cygwin
amead wrote: Are you doing this at the Cygwin prompt or Window's command prompt? My installation of Cygwin doesn't recognize DOS style paths at all: $ ls c:\cygwin ls: c:cygwin: No such file or directory You used a backslash, escaping the 'c' character. Notice that the error message refers to "c:cygwin" rather than "c:\cygwin", which isn't equivalent. Try "ls c:/cygwin" instead. -- http://www.velocityvector.com/ | http://www.indie-games.com/ http://www.classic-games.com/ | http://glmiller.blogspot.com/ "If my forgeries looked as bad as the CBS documents, it would have been 'Catch Me In Two Days'" -- Frank Abagnale, Jr.
Re: [sqlite] Any way of using an implicit BTree ordering in SQLite?
> ...I'd like to use a table as a "pure" BTree ... If you mean a general multi-way B-Tree, I don't think there's any practical way in SQL. If you can use a binary tree, there are ways. The most convenient is Joe Celko's method, which he calls nested sets. The other design is usually called an adjacency list. There was a discussion of this topic on this mailing list about a year ago. You might search the list archive and also try a Google search on 'celko nested set' and 'adjacency list' -- or even just 'sql tree'. Regards
Re: [sqlite] Row count in database
On Tue, 14 Dec 2004, Christopher Petrilli wrote: On Tue, 14 Dec 2004 12:03:01 -0700 (MST), Ara.T.Howard <[EMAIL PROTECTED]> wrote: On Tue, 14 Dec 2004, Christopher Petrilli wrote: Has anyone had any experience in storing a million or more rows in a SQLite3 database? I've got a database that I've been building, which gets 250 inserts/second, roughly, and which has about 3M rows in it. At that point, the CPU load is huge. Note that I've got syncing turned off, because I'm willing to accept the risks. Thoughts? Chris -- | Christopher Petrilli | [EMAIL PROTECTED] on linux perhaps? cp ./db /dev/shm && a.out /dev/shm/db && mv /dev/shm/db ./db this will be fast. Right, but not really workable when total DB size is in gigabytes. :-) ya never know - it's hard to beat the kernel with regards to io... gigabytes of ram are cheap too compared to a couple days of a prgrammer's time. are you sure it's not YOUR 'building' code which is killing the cpu? can you gperf it? Yes, my code is using under 20% of the CPU. The rest is basically blocked up in sqlite3 code, and kernel time. In order to eliminate all possibility of my code being the issue, I actually built a rig that prebuilds 10,000 rows, and inserts them in sequence repeatedly putting new primary keys on them as its going alone. So the system basically just runs in a loop doing sqlite calls. this is probably a stupid question - but are the inserts inside of a transaction? -a -- === | EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov | PHONE :: 303.497.6469 | When you do something, you should burn yourself completely, like a good | bonfire, leaving no trace of yourself. --Shunryu Suzuki ===
[sqlite] Any way of using an implicit BTree ordering in SQLite?
Normally (to my understanding), a BTree of some sort is declared on a set of columns of a table when an index (or something which requires an index) is declared on that set of columns. However, I'd like to use a table as a "pure" BTree, i.e. one in which the order is not dependent on values in given columns, but on an implicit ordering of table elements, which is determined by inserting elements before or after other elements. This cannot be done efficiently (at least not elegantly) with explicit indexes. For example, let's say I'm using integer indexes to define the order, and I have a table with one million records. If I insert a new record halfway through the table, then I have to update the integer column for 500,000 elements--hardly O(log(n)) performance :-) Row IDs clearly won't do the trick, as they have no implicit ordering. However, I was wondering if the particular internal tree structure used by sqlite provides some sort of implicit btree structure which is accessible to insertions, deletions, and comparisons. The SQLite docs don't suggest any such possibility, but I thought it'd be worth asking. Thanks, Ken
[sqlite] [ANN] QuickLite 1.5.3 is available
Hello everybody, I'm pleased to announce that QuickLite 1.5.3 is now available. What’s New in this Version -- - In-cursor data matching - Set operations on cursors: union, minus and intersection - Support for attached databases - More convenience methods in all 3 classes - Several bug fixes - Updated examples - Better support for dates - Improved error reporting For more info, please check the Release Notes: http://www.webbotech.com/releasenotes.html For a brief intro to QuickLite and its main features: http://www.webbotech.com/quickliteoverview.html You can download QuickLite here: http://www.webbotech.com Regards, -- Tito
Re: [sqlite] absolute vs. relative path to database on Cygwin
Markus Hoenicka wrote: Well, that doesn't work, but the following does: sqlite c:/cygwin/usr/local/share/refdb/db/refdb i.e. absolute paths require a DOS-style drive letter. Are you doing this at the Cygwin prompt or Window's command prompt? My installation of Cygwin doesn't recognize DOS style paths at all: $ ls c:\cygwin ls: c:cygwin: No such file or directory -Alan -- Alan Mead - [EMAIL PROTECTED] People often find it easier to be a result of the past than a cause of the future.
Re: [sqlite] absolute vs. relative path to database on Cygwin
Markus Hoenicka writes: > amead writes: > > It's a bit of a long-shot, but have you tried the 'cygdrive' path syntax? > > > > $ sqite /cygdrive/c/cygwn/usr/local/share/refdb/db/refdb > > > > (assuming you installed Cygwin on C:\cygwin) > > > > Actually I did not try this yet. I'll do so tomorrow, as I don't have > any windoze stuff at home (fortunately). My gut feeling says it is > going to work. > Well, that doesn't work, but the following does: sqlite c:/cygwin/usr/local/share/refdb/db/refdb i.e. absolute paths require a DOS-style drive letter. regards, Markus -- Markus Hoenicka [EMAIL PROTECTED] (Spam-protected email: replace the quadrupeds with "mhoenicka") http://www.mhoenicka.de
Re: [sqlite] Row count in database
On Tue, 14 Dec 2004 13:54:35 -0500, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > Christopher Petrilli wrote: > > Has anyone had any experience in storing a million or more rows in a > > SQLite3 database? I've got a database that I've been building, which > > gets 250 inserts/second, roughly, and which has about 3M rows in it. > > At that point, the CPU load is huge. > > > > I just ran a test case inserting 3 million rows in a database. > Wall-clock time was 122 seconds for a rate just shy of 25000 inserts > per second. The final database size was 222428160. To check to see > if performance was falling off with increases size, I then inserted > an additional million rows. 41 seconds: 24390 inserts per second. > New file size 297440256. This is on three year old hardware. That was about my insert performance as well (AMD64), as it's basically disc limited any more. I found substantial differences between my SATA drive and my PATA drive though. > Inserts can be significantly slower if you have indices. The more > indices you have the slower things might go. (Depending on what > your indices and your data look like.) If possible, it is recommended > that you do all your inserts first, then do the CREATE INDEX statements > as a separate step afterwards. Alas, I think it is the indexing that's killing me. I'm contemplating shrinking the size of the database (i.e. partitioning the data into multiple databases, and doing some in-memory joining in my application), which means it'd be easier to add the indexes after the database has been "filled" if that makes sense. > What does your schema look like? Here's the schema, or mostly... I've removed a bunch of columns that aren't really public... think of it as another 8 INTEGER columns, without indexes. CREATE TABLE events ( event_id VARCHAR(32) PRIMARY KEY, sensor_ts INTEGER NOT NULL, my_ts INTEGER NOT NULL, sensor_id INTEGER NOT NULL, src_ip INTEGER NOT NULL, dst_ip INTEGER NOT NULL, event_class INTEGER NOT NULL, event_type INTEGER NOT NULL, user_name TEXT, info TEXT); CREATE INDEX events_sensor_ts_idx ON events(sensor_ts); CREATE INDEX events_conduit_ts_idx ON events(conduit_ts); CREATE INDEX events_src_ip_idx ON events(src_ip); CREATE INDEX events_dst_ip_idx ON events(dst_ip); -- | Christopher Petrilli | [EMAIL PROTECTED]
Re: [sqlite] Row count in database
On Tue, 14 Dec 2004 12:03:01 -0700 (MST), Ara.T.Howard <[EMAIL PROTECTED]> wrote: > On Tue, 14 Dec 2004, Christopher Petrilli wrote: > > > Has anyone had any experience in storing a million or more rows in a > > SQLite3 database? I've got a database that I've been building, which > > gets 250 inserts/second, roughly, and which has about 3M rows in it. > > At that point, the CPU load is huge. > > > > Note that I've got syncing turned off, because I'm willing to accept > > the risks. > > > > Thoughts? > > > > Chris > > > > -- > > | Christopher Petrilli > > | [EMAIL PROTECTED] > > on linux perhaps? > >cp ./db /dev/shm && a.out /dev/shm/db && mv /dev/shm/db ./db > > this will be fast. Right, but not really workable when total DB size is in gigabytes. :-) > are you sure it's not YOUR 'building' code which is killing the cpu? can you > gperf it? Yes, my code is using under 20% of the CPU. The rest is basically blocked up in sqlite3 code, and kernel time. In order to eliminate all possibility of my code being the issue, I actually built a rig that prebuilds 10,000 rows, and inserts them in sequence repeatedly putting new primary keys on them as its going alone. So the system basically just runs in a loop doing sqlite calls. Chris -- | Christopher Petrilli | [EMAIL PROTECTED]
RE: [sqlite] synchronising two databases
Thanks, I'll look into something like that. I think there must be a neat way of doing this synchronisation besides actually copying files, but this method should get me started. I'll keep you posted if I come up with any other method. Regards, Richard. -Original Message- From: Andrew Piskorski [mailto:[EMAIL PROTECTED] Sent: 13 December 2004 14:24 To: [EMAIL PROTECTED] Subject: Re: [sqlite] synchronising two databases On Mon, Dec 13, 2004 at 02:00:05PM -, Richard Boyd wrote: > My question is this: Is it possible for me to copy across the database file > and then append it to the end of the locally held database thus > synchronizing the two? Is there a better way to do this synchronization? If both databases are shut down, presumably you could use rsync to blindly synchronize the two at the binary level. Conceivably you could even integrate something like librsync into SQLite and thus do the rsync operation while the database is locked, with no other activity going on. http://librsync.sourceforge.net/ (This is all speculation on my part though, I have tried nothing of the sort. And of course, there might already be some better way to do it in SQLite.) -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/ --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.801 / Virus Database: 544 - Release Date: 24/11/04 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.813 / Virus Database: 553 - Release Date: 13/12/04
Re: [sqlite] Row count in database
On Tue, 14 Dec 2004, Christopher Petrilli wrote: Has anyone had any experience in storing a million or more rows in a SQLite3 database? I've got a database that I've been building, which gets 250 inserts/second, roughly, and which has about 3M rows in it. At that point, the CPU load is huge. Note that I've got syncing turned off, because I'm willing to accept the risks. Thoughts? Chris -- | Christopher Petrilli | [EMAIL PROTECTED] on linux perhaps? cp ./db /dev/shm && a.out /dev/shm/db && mv /dev/shm/db ./db this will be fast. are you sure it's not YOUR 'building' code which is killing the cpu? can you gperf it? -a -- === | EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov | PHONE :: 303.497.6469 | When you do something, you should burn yourself completely, like a good | bonfire, leaving no trace of yourself. --Shunryu Suzuki ===
Re: [sqlite] Row count in database
Christopher Petrilli wrote: Has anyone had any experience in storing a million or more rows in a SQLite3 database? I've got a database that I've been building, which gets 250 inserts/second, roughly, and which has about 3M rows in it. At that point, the CPU load is huge. I just ran a test case inserting 3 million rows in a database. Wall-clock time was 122 seconds for a rate just shy of 25000 inserts per second. The final database size was 222428160. To check to see if performance was falling off with increases size, I then inserted an additional million rows. 41 seconds: 24390 inserts per second. New file size 297440256. This is on three year old hardware. Inserts can be significantly slower if you have indices. The more indices you have the slower things might go. (Depending on what your indices and your data look like.) If possible, it is recommended that you do all your inserts first, then do the CREATE INDEX statements as a separate step afterwards. What does your schema look like? -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Row count in database
Christopher Petrilli wrote: Has anyone had any experience in storing a million or more rows in a SQLite3 database? I've got a database that I've been building, which gets 250 inserts/second, roughly, and which has about 3M rows in it. At that point, the CPU load is huge. Note that I've got syncing turned off, because I'm willing to accept the risks. Thoughts? Chris I have a database with almost 1.4 records in one table and over half a million in another. I treat them like they are read-only so I cannot advise you about write performance except to urge you to read the document on www.sqlite.org about performance... I do notice that doing anything like creating an index takes a while, as do joins. When I do, I hear my variable speed fan rev-up, so the load is definitely higher... Here's a silly idea: If you have enough RAM and you don't care about sync'ing then could you run things in memory... like on a RAM disk or buy one of those RAM hard-drives? -Alan -- Alan Mead - [EMAIL PROTECTED] People often find it easier to be a result of the past than a cause of the future.
[sqlite] Row count in database
Has anyone had any experience in storing a million or more rows in a SQLite3 database? I've got a database that I've been building, which gets 250 inserts/second, roughly, and which has about 3M rows in it. At that point, the CPU load is huge. Note that I've got syncing turned off, because I'm willing to accept the risks. Thoughts? Chris -- | Christopher Petrilli | [EMAIL PROTECTED]
Re: [sqlite] ODBC driver?
Here: http://www.ch-werner.de/sqliteodbc/ --- Jay <[EMAIL PROTECTED]> wrote: > Good morning all, > > Is there an odbc driver for Sqlite3? > It would be useful to be able to use existing data > tools... > > = > > - > > The Castles of Dereth Calendar: a tour of that art > and architecture of the online game world of > Asheron's Call > http://www.lulu.com/content/77264 > > > > __ > Do you Yahoo!? > Yahoo! Mail - 250MB free storage. Do more. Manage > less. > http://info.mail.yahoo.com/mail_250 > __ Do you Yahoo!? Dress up your holiday email, Hollywood style. Learn more. http://celebrity.mail.yahoo.com
RE: [sqlite] coding style
Also, the data types (in 2.8.15) do not appear to copy over into the newly created table... I had to manually create the table with SQL, then insert into it from the query. -Original Message- From: Jakub Adámek [mailto:[EMAIL PROTECTED] Sent: Monday, December 13, 2004 3:08 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] coding style Yes but then I must repeat the column names in every query in which I use the view. It is a bug in SQLite, isn't it? Jakub Brass Tilde ([EMAIL PROTECTED]) wrote*: > > > create view myview as select t1.a a from t1 inner join t2 on > > t1.a=t2.a; create table problem as select * from myview; > > Change this last line to: > > create table problem as select a as a from myview; > > That creates the problem table with just "a" as the field name. >
