Re: [sqlite] linear behavior on INSERT (was Re: unexpected large journal file)

On 6/22/10, Eric Smith wrote: > Jim Wilcoxson wrote: ... >> Did you see my earlier note about combining your two integers into the >> primary key? This will also give you constant insert times, if you >> insert items in the order: ... > Thanks also for the tip on insertion

[sqlite] ANN: C#-SQLite 3.6.23 with aes 256 encryption

C#-SQLite has been updated to release 3.6.23.1 and is now ready for review. The 6/21 release features: * SQL_HAS_CODEC compiler option * a AES256 crypto module * Silverlight support It now runs 35,028 of the tcl testharness tests without errors. The project is located at

Re: [sqlite] linear behavior on INSERT (was Re: unexpected large journal file)

On 23 Jun 2010, at 1:18am, Eric Smith wrote: > *sigh* kill me. Sorry for wasting your time there. :/ We've all done it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] linear behavior on INSERT (was Re: unexpected large journal file)

Scott Hess wrote: > You should reduce your demonstration case to something you'd be > willing to post the code for. Probably using synthetic data > (preferably generated data). There's something missing in the thread > right now, and it's unlikely to be exposed by random shots in the >

Re: [sqlite] marking transaction boundaries

Hi, Blob was just an example. My original email said hex(randomblob(16)) as a means of generating an uuid. It is not the important part. The question following your suggestion is how do i maintain transaction start and stop times? I have no control of knowing when a transaction starts and ends

Re: [sqlite] marking transaction boundaries

On 22 Jun 2010, at 10:25pm, b s wrote: > I want to mark that the following records inserted or updated > belong to a certain transaction. Transactions can be nested. In other words, an INSERT or UPDATE can belong to more than one transaction. BLOBs are hugely expensive in terms of

Re: [sqlite] handling sqlite3_close() == SQLITE_BUSY

On 22 Jun 2010, at 11:18pm, Sam Carleton wrote: > void CSQLiteDB::Close() > { >if(m_db) >{ >sqlite3 *db = m_db; >m_db = NULL; Does not do what you are trying to do. Just use m_db. >int rc = sqlite3_close(db); > >while( rc == SQLITE_BUSY) >{ >

Re: [sqlite] linear behavior on INSERT (was Re: unexpected large journal file)

Scott Hess wrote: > You should reduce your demonstration case to something you'd be > willing to post the code for. Probably using synthetic data > (preferably generated data). There's something missing in the thread > right now, and it's unlikely to be exposed by random shots in the >

Re: [sqlite] linear behavior on INSERT (was Re: unexpected large journal file)

Eric, You should reduce your demonstration case to something you'd be willing to post the code for. Probably using synthetic data (preferably generated data). There's something missing in the thread right now, and it's unlikely to be exposed by random shots in the dark. -scott On Tue, Jun

Re: [sqlite] linear behavior on INSERT (was Re: unexpected large journal file)

Jay A. Kreibich wrote: > What OS/filesystem are you using? > > SQL inserts should be near-constant, assuming the table does not > have an INTEGER PRIMARY KEY with explicit values. The table's root > B-Tree needs to re-balance every now and then, but if the inserts are > in-order (which

Re: [sqlite] linear behavior on INSERT (was Re: unexpected large journal file)

Uggg On Tue, Jun 22, 2010 at 05:12:38PM -0500, Jay A. Kreibich scratched on the wall: > On Tue, Jun 22, 2010 at 04:16:42PM -0400, Eric Smith scratched on the wall: > > Jim Wilcoxson wrote: > > > > > Insert times should be constant for the 2nd case: no primary key, no > > > indexes; ie,

[sqlite] handling sqlite3_close() == SQLITE_BUSY

On Tue, Jun 22, 2010 at 10:13 AM, Pavel Ivanov wrote: > > No, I did not. I am not storing any blobs right now, but... Is the busy > > handler going to kick in? I know the busy handler is not the sole answer > to > > the problem, but it does seem to catch most of my

Re: [sqlite] linear behavior on INSERT (was Re: unexpected large journal file)

On Tue, Jun 22, 2010 at 04:16:42PM -0400, Eric Smith scratched on the wall: > Jim Wilcoxson wrote: > > > Insert times should be constant for the 2nd case: no primary key, no > > indexes; ie, it doesn't matter how many records are already in the > > database. I confirmed this with SQLite

Re: [sqlite] linear behavior on INSERT (was Re: unexpected large journal file)

Richard Hipp wrote: > When there are no indices, SQLite can put the rows into the database in > any order it wants, and it chooses to put them in ascending order. Thus, > each insert becomes a constant-time append. (Approximately - the truth is > a little more complicated, but by waving our

Re: [sqlite] marking transaction boundaries

Hi, I want to mark that the following records inserted or updated belong to a certain transaction. Unless i know the explicit begin/end, i cannot use just sticking a blob in a column, because it will change for every call to blob. what i want is during begin transaction store this value in a

Re: [sqlite] linear behavior on INSERT (was Re: unexpected large journal file)

On Tue, Jun 22, 2010 at 3:08 PM, Eric Smith wrote: > I have confirmed that INSERT times are roughly logarithmic in > the number of existing records after creating my specific user > indices. > > But INSERT times appeared to be *linear* in the number of existing > records

[sqlite] where to get historical versions?

In another thread in this forum, someone says they noticed a behavior in sqlite version 3.6.18 different (better) than what I've observed in 3.6.23.1. Where can I find version 3.6.18 (or, more generally, any old version) for testing? Eric -- Eric A. Smith It's up. It sorta works.

Re: [sqlite] linear behavior on INSERT (was Re: unexpected large journal file)

Jim Wilcoxson wrote: > Insert times should be constant for the 2nd case: no primary key, no > indexes; ie, it doesn't matter how many records are already in the > database. I confirmed this with SQLite 3.6.18. Definitely not constant. Looks linear to me -- you saw the plot, you can decide

[sqlite] public domain

IANAL. I am glad IANAL. This is a biased review of an appeals court ruling about removing content from public domain because of "substantial or important governmental interest" and placing it back under copyright. http://www.techdirt.com/articles/20100621/2320049908.shtml Probably nothing to

Re: [sqlite] linear behavior on INSERT (was Re: unexpected large journal file)

On 6/22/10, Eric Smith wrote: > I have confirmed that INSERT times are roughly logarithmic in > the number of existing records after creating my specific user > indices. > > But INSERT times appeared to be *linear* in the number of existing > records before I had created any

Re: [sqlite] marking transaction boundaries

Do you want to put that blob into all tables using a trigger or changing all applications using database? If changing applications then you can also change them to create that blob value. If using a trigger then you can insert into that trigger creation of the blob value if it doesn't exist. Is

[sqlite] linear behavior on INSERT (was Re: unexpected large journal file)

I have confirmed that INSERT times are roughly logarithmic in the number of existing records after creating my specific user indices. But INSERT times appeared to be *linear* in the number of existing records before I had created any user indices (and with no primary keys or unique indices

Re: [sqlite] unexpected large journal file

Eric Smith writes: > > Jay A. Kreibich wrote: > > > Try getting rid of the PK definition and see how much that buys you. > > It might be worth it, or it might not. > > and Simon Slavin wrote: > > > We know you are doing a huge amount of writing to this database. Are > >

Re: [sqlite] Accessing an sqlite db from two different programs

> do I need to do a lot of locking? You don't need to do it, SQLite will do that for you. And it doesn't matter whether both applications write to the same table or different ones - as long as they write to the same file those writes will be serialized by locking made inside SQLite. But you have

Re: [sqlite] Accessing an sqlite db from two different programs

On 22 Jun 2010, at 5:44pm, Ian Hardingham wrote: > If I have program 1 and program 2 which both open the same db file, but > they never write to the same table (but might be reading one written by > another), do I need to do a lot of locking? I'm not worried about race > conditions. Sorry,

Re: [sqlite] When to close a db, take 2...

On Tue, Jun 22, 2010 at 09:48:29AM -0400, Sam Carleton scratched on the wall: > On Tue, Jun 22, 2010 at 9:15 AM, Pavel Ivanov wrote: > > > > The idea is that the copy and nulling happens very quickly where the > > > sqlite3_close is more expensive, do the copy/null very

[sqlite] Accessing an sqlite db from two different programs

Hey guys, If I have program 1 and program 2 which both open the same db file, but they never write to the same table (but might be reading one written by another), do I need to do a lot of locking? I'm not worried about race conditions. Thanks, Ian

[sqlite] marking transaction boundaries

hi, long ago, drh had proposed a trigger like mechanism that can be invoked at the begin/end of a transaction. http://osdir.com/ml/db.sqlite.general/2003-04/msg00137.html the general consensus was there is no use other than up'ng a counter. however, i have a requirement where i would like to mark

Re: [sqlite] When to close a db, take 2...

> No, I did not.  I am not storing any blobs right now, but...  Is the busy > handler going to kick in?  I know the busy handler is not the sole answer to > the problem, but it does seem to catch most of my SQLITE_BUSY issues since > all data is pretty small. No, this SQLITE_BUSY result is not

Re: [sqlite] When to close a db, take 2...

On Tue, Jun 22, 2010 at 9:15 AM, Pavel Ivanov wrote: > > The idea is that the copy and nulling happens very quickly where the > > sqlite3_close is more expensive, do the copy/null very quickly so that if > > another thread calls Close during the first threads execution of > >

Re: [sqlite] how often to open the db

> If all of the above is happening within one logical transaction (and, > I don't mean, a db transaction -- since you mentioned Apache, I mean, > a "job," a "click" from the user's point of view), you certainly can > and should open a single db connection, do everything you want to do, > close the

Re: [sqlite] When to close a db, take 2...

> The idea is that the copy and nulling happens very quickly where the > sqlite3_close is more expensive, do the copy/null very quickly so that if > another thread calls Close during the first threads execution of > sqlite3_close, the second thread finds m_db null and will not call >

[sqlite] max blob size without overflow page

Hello sqlite list, Given the following table with large page size (32 KB): CREATE TABLE table ( column_0 INTEGER PRIMARY KEY, column_1 INTEGER, column_2 REAL, ... column_n INTEGER, column_blob BLOB ); With 'column_1' to

Re: [sqlite] Oracle joins the SQLite Consortium

Hello, My name is Greg, I'm one of the product managers within Oracle working on the Berkeley DB products. I joined Oracle when Sleepycat was acquired but I've been working on BDB for nearly nine years now. I was the one who pushed hard to integrate SQLite and BDB, I think the two products

Re: [sqlite] When to close a db, take 2...

En/na Sam Carleton ha escrit: > The idea is that the copy and nulling happens very quickly where the > sqlite3_close is more expensive, do the copy/null very quickly so that if > another thread calls Close during the first threads execution of > sqlite3_close, the second thread finds m_db null

Re: [sqlite] Insert Using Tcl Array?

You can use simple wrapper like as proc ::dataset::update {table fields} { array set info $fields foreach {key value} $fields { if {$key eq {id}} continue if {$key eq {*}} continue lappend sql_pairs $key=:info($key) }

Re: [sqlite] When to close a db, take 2...

Jay, Thank you! I believe you pointed me at the correct direction. The logic I am using of copying the m_db to a temp var, setting the m_db to null prior to calling the close method comes from my days of working with Microsoft COM. The idea is that the copy and nulling happens very quickly