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

[Jim Wilcoxson]

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 order.  Does that also hold for
> multi-column indices (and not single-column indices transformed from two
> integers)?  I assume it's because we get more cache hits and fewer tree
> rebalances when we insert in key-order?

Yes, I'm pretty sure this applies to multi-column indices too.  So if
you do your inserts as:

a=0 b=0
a=1 b=0
...
a=0 b=1
a=1 b=1

Then the first set of rows with b=0 will be added in "more or less"
constant time.  When you start doing the second set of inserts, with
b=1, that will cause pain, because you will be modifying every index
record you created earlier and squeezing a new entry in between every
existing entry.  This will require a lot of journalling.

I think it would run faster (less journalling) to insert in order with:

a=0 b=0
a=0 b=1
a=1 b=0
a=1 b=1
etc.

Even if you load the data without indexes and add the index later, my
guess is that SQLite will still traverse the data in rowid order to
create the index.  So you are still better off inserting in the 2nd
order rather than the first.  The added advantage is that your index
pages will be clustered together.

Jim
-- 
HashBackup: easy onsite and offsite Unix backup
http://sites.google.com/site/hashbackup
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Noah Hart]

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 http://code.google.com/p/csharp-sqlite/

Please keep in mind the following:

* C#-SQLite is an independent reimplementation of the SQLite software
library
* This is not an official version of SQLite
* Bugs should not be reported to the SQLite.org ticket tracking system

SQLite® is a registered trademark of Hipp, Wyrick & Company, Inc


Enjoy,

Noah Hart

-- 
View this message in context: 
http://old.nabble.com/ANN%3A-C--SQLite-3.6.23-with-aes-256-encryption-tp28966276p28966276.html
Sent from the SQLite mailing list archive at Nabble.com.

___
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)

[Simon Slavin]

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)

[Eric Smith]

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 
> dark.  

As I was putting together the answer to this request, I decided to 
double-check the result by #ifdef'ing out the sqlite calls.

Turns out I *was* being stupid.

An old app-level error check ran after a hunk of data was inserted.
The check was supported by the PK definition, which I had just removed.
So sqlite was doing a table scan every batch.  Measurements were 
better with user-level indices because one of the indices was usable 
in the error check.  

*sigh* kill me.  Sorry for wasting your time there. :/

So the summary of this thread for those who follow is: 

1. Primary keys cause implicit indices to be defined.
2. If you insert data out of order (according to the index) then you
   have to read/write more pages per insertion.  This, among other
   things, means the journal file can grow with the starting db size,
   not just with the size of the transaction.
3. Consider reducing churn against OS-level caches (or the disk) 
   by increasing sqlite's cache_size.

Thanks again, everyone, for your help!

Eric 

-- 
Eric A. Smith

You made the fatal mistake of assuming that your friends are genuine.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] marking transaction boundaries

[b s]

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 and i said earlier, i cannot use transaction hooks.

thanks
brs


- Original Message 
From: Simon Slavin 
To: General Discussion of SQLite Database 
Sent: Tue, June 22, 2010 3:45:26 PM
Subject: 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 programming, 
processing time and storage.  It would be far less 'expensive' to mark your 
transactions with INTEGERs.

If you have your own description of what a transaction is, just use a trigger 
to maintain a 'last updated' field for every row, and make a table which lists 
your transactions and their start and stop times.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] marking transaction boundaries

[Simon Slavin]

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 programming, 
processing time and storage.  It would be far less 'expensive' to mark your 
transactions with INTEGERs.

If you have your own description of what a transaction is, just use a trigger 
to maintain a 'last updated' field for every row, and make a table which lists 
your transactions and their start and stop times.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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)
>{
>// set rc to something that will exit the while loop
>rc = SQLITE_OK;
>sqlite3_stmt * stmt = sqlite3_next_stmt(db, NULL);
> 
>if(stmt != NULL)
>{
>rc = sqlite3_finalize(stmt);
>if(rc == SQLITE_OK)
>{
>rc = sqlite3_close(db);
>}
>}
>}
>}
> }


void CSQLiteDB::Close()
{
   if(m_db)
   {
rc = sqlite3_close(m_db);
while (rc == SQLITE_BUSY)   // maybe include _LOCKED
{
sqlite3_stmt *stmt = sqlite3_next_stmt(db, NULL);
if (stmt) sqlite3_finalize(stmt);   // don't trap, can't handle it 
anyway

msleep(1000 * .25); // give other threads time
rc = sqlite3_close(m_db);
}
if (rc != SQLITE_OK) reportSQLiteError(rc, "while closing connection");
}
}

Note: I have not tried the above code.

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)

[Eric Smith]

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 
> dark.  

I'll start doing that and reply here with an obfuscated schema.  In 
the mean time, where can I find version 3.6.18 (whom someone 
claimed definitely does constant-time insertions)?  

-- 
Eric A. Smith

You will never amount to much. 
-- Munich Schoolmaster, to Albert Einstein, age 10
___
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]

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 22, 2010 at 3:01 PM, Eric Smith  wrote:
> 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 arms vigorously, we can still
>> claim constant time per row.)  But if there are indices, each row must be
>> inserted in index order, which involves a b-tree search to find the right
>> spot and possible a rebalancing operation - O(logN) per row.
>
> Again, my observation is that you are *not* doing constant-time inserts
> when there are no indices.
>
> What do you mean, "ascending order"?
>
> The only constraint on the relevant table is a foreign-key ref to a tiny
> table.  But the asymptotic behavior is the same with pragma foreign_keys
> off or on.
>
> I double-checked sqlite_master and there are no indices (not even
> auto-indices) on the table.
>
> Inserts are *faster* at high row counts when there *are* indices.
>
> I am using 3.6.23.1.  I haven't tested earlier versions (waiting on a
> reply in another thread to find out where to get them).
>
> How vigorously are you waving?  Can you describe the real algorithm, or
> at least a second-order approximation?
>
> Eric
>
> --
> Eric A. Smith
>
> What the hell is it good for?
>    -- Robert Lloyd (engineer of the Advanced Computing Systems
>       Division of IBM), to colleagues who insisted that the
>       microprocessor was the wave of the future, c. 1968
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
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)

[Eric Smith]

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 they will be with an automatic ROWID) this should be 
> rare and cheap-- should should get more rare as the number of rows 
> increases.  
> 
> Many *filesystems* do not provide linear access times, however, 
> especially with larger files.  

Interesting.  But behavior is better (logarithmic) with indices 
defined.  

Right now I'm in 64-bit linux 2.6.18, rhel 5.4.  The fs is ext3.  Not 
sure if this particular box has a raid5 array like the other box did.  
But again, I think it's a moot point: even when I'm completely in the 
page cache behavior is linear, and it improves with indices.  This 
suggests a software algo issue.  

(Just got your corrections, I knew what you meant.:-)

Eric

-- 
Eric A. Smith

This non-pronunciation of initial _h_ is especially common among 
French and British people, who can't pronounce English very well.
-- Tim Pulju
___
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)

[Jay A. Kreibich]

   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, 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 for yourself.
> 
>   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 they will be with an automatic ROWID) this should be
>   rare and cheap-- should should get more rare as the number of rows

 and should...

>   increases.
> 
>   Many *filesystems* do not provide linear access times, however,
>   especially with larger files.

   ...constant access...  Many filesystems do not provide constant access.

> 
>-j
> 

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] handling sqlite3_close() == SQLITE_BUSY

[Sam Carleton]

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 SQLITE_BUSY issues
> since
> > all data is pretty small.
>
> No, this SQLITE_BUSY result is not related to database locking and so
> busy handler is not called. You can force connection closing in case
> of SQLITE_BUSY result by forcible finalizing of all statements. Use
> http://www.sqlite.org/c3ref/next_stmt.html for iterating all
> statements and sqlite3_finalize on each of them. After that
> sqlite3_close should complete successfully.
>

Pavel,

So would you agree with this as a solution to deal with the close being
busy:

void CSQLiteDB::Close()
{
if(m_db)
{
sqlite3 *db = m_db;
m_db = NULL;
int rc = sqlite3_close(db);

while( rc == SQLITE_BUSY)
{
// set rc to something that will exit the while loop
rc = SQLITE_OK;
sqlite3_stmt * stmt = sqlite3_next_stmt(db, NULL);

if(stmt != NULL)
{
rc = sqlite3_finalize(stmt);
if(rc == SQLITE_OK)
{
rc = sqlite3_close(db);
}
}
}
}
}

Sam
___
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)

[Jay A. Kreibich]

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 3.6.18.  
> 
> Definitely not constant.  Looks linear to me -- you saw the plot, you
> can decide for yourself.

  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 they will be with an automatic ROWID) this should be
  rare and cheap-- should should get more rare as the number of rows
  increases.

  Many *filesystems* do not provide linear access times, however,
  especially with larger files.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
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)

[Eric Smith]

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 arms vigorously, we can still 
> claim constant time per row.)  But if there are indices, each row must be 
> inserted in index order, which involves a b-tree search to find the right 
> spot and possible a rebalancing operation - O(logN) per row.  

Again, my observation is that you are *not* doing constant-time inserts 
when there are no indices.  

What do you mean, "ascending order"?  

The only constraint on the relevant table is a foreign-key ref to a tiny 
table.  But the asymptotic behavior is the same with pragma foreign_keys 
off or on.  

I double-checked sqlite_master and there are no indices (not even 
auto-indices) on the table.  

Inserts are *faster* at high row counts when there *are* indices.  

I am using 3.6.23.1.  I haven't tested earlier versions (waiting on a 
reply in another thread to find out where to get them).  

How vigorously are you waving?  Can you describe the real algorithm, or 
at least a second-order approximation?  

Eric 

-- 
Eric A. Smith

What the hell is it good for?
-- Robert Lloyd (engineer of the Advanced Computing Systems
   Division of IBM), to colleagues who insisted that the
   microprocessor was the wave of the future, c. 1968
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] marking transaction boundaries

[b s]

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
key/value table as 'tranid'/ and delete it at end of transaction.
this can be queried either by application or trigger. but i expect this
insertion to happen in a trigger like the original proposal.

brs


- Original Message 
From: Pavel Ivanov 
To: General Discussion of SQLite Database 
Sent: Tue, June 22, 2010 12:34:16 PM
Subject: 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 there something wrong with such
solutions?


Pavel

On Tue, Jun 22, 2010 at 11:35 AM, b s  wrote:
> 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 transaction boundaries from a user level.
>
> my requirement: on begin transaction, i would like to store
> a hex(randomblob(16)) in a table during begin transaction and
> put this value in all tables that were involved during that
> transaction.
>
> there is no way it can be done across all connections, unless
> a trigger level like facility is available. otherwise the hook functions
> all work only on a sqlite connection and i dont want to load modules.
>
> are there any other methods available to get the same result?
> or, as someone mentioned bring some Interbase like facility?
>
>
> thanks
> brs
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



  
___
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)

[Richard Hipp]

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 before I had created any user indices (and with no primary
> keys or unique indices defined).
>
> Can anyone explain this?
>

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 arms vigorously, we can still
claim constant time per row.)  But if there are indices, each row must be
inserted in index order, which involves a b-tree search to find the right
spot and possible a rebalancing operation - O(logN) per row.



>
> Eric
>
> >   time (minutes) to insert 2m records
> > 10
> ++--+--+---+--+---+-++
> >+   +  +   +  A   +   +
>+
> >  9
> ++..++
> >|  AAA A
>   |
> >  8
> ++..AAA..A..++
> >| A   
>   |
> >  7
> ++.A.A..AAA.++
> >|  
>|
> >  6
> ++..AA...A.A++
> >|AAA
>   |
> >  5
> ++A...AA++
> >  4
> ++.AA.AA..A.A...++
> >|   A AAA  AA
>|
> >  3
> ++..++
> >|   AAA A  A
>   |
> >  2
> ++.AA...++
> >| AA
>   |
> >  1
> ++...AAA++
> >AAA +  +   +  +   +
>+
> >  0
> ++--+--+---+--+---+-++
> >0  100200 300400 500
>  600
> >millions of existing records
>
> --
> Eric A. Smith
>
> A nickel ain't worth a dime anymore.
>-- Yogi Berra
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
-
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] where to get historical versions?

[Eric Smith]

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.  That's a start.
-- BJ Premore
___
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)

[Eric Smith]

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 for yourself.

I'm in SQLite 3.6.23.1.  How do I score an old version to test it?

> 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: 

Hey sorry, I didn't see that.  Cute idea, but my accessors are in Tcl, 
I don't want to do bit twiddling or query mangling on the read side from 
Tcl, and I don't want to re-write it in C.  Plus a host of other reasons
that would bore the SQLite community.  I'm actually rather happy without
any primary key definition right now.

Thanks also for the tip on insertion order.  Does that also hold for 
multi-column indices (and not single-column indices transformed from two 
integers)?  I assume it's because we get more cache hits and fewer tree 
rebalances when we insert in key-order?

Before I make any more adjustments, I want to understand why I'm linear
with no indices!

I'm pretty sure I'm not doing anything stupid, like setting evil 
compile-time options or whatever.  But then again most stupid people 
don't think their results come from being stupid.  

Eric 

-- 
Eric A. Smith

Aeropalmics (ayr o palm' iks), n.: 
The study of wind resistance conducted by holding a cupped 
hand out the car window.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] public domain

[Ned Fleming]

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 see, move along, thank you, but it's a bit like
driving by a car wreck: everybody slows down and gawks.

-- 

Ned


___
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)

[Jim Wilcoxson]

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 user indices (and with no primary
> keys or unique indices defined).
>
> Can anyone explain this?
>
> Eric

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.

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:

a=0, b=0
a=0, b=1
a=1, b=0
a=1, b=1
etc.

Jim
-- 
HashBackup: easy onsite and offsite Unix backup
http://sites.google.com/site/hashbackup
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] marking transaction boundaries

[Pavel Ivanov]

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 there something wrong with such
solutions?


Pavel

On Tue, Jun 22, 2010 at 11:35 AM, b s  wrote:
> 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 transaction boundaries from a user level.
>
> my requirement: on begin transaction, i would like to store
> a hex(randomblob(16)) in a table during begin transaction and
> put this value in all tables that were involved during that
> transaction.
>
> there is no way it can be done across all connections, unless
> a trigger level like facility is available. otherwise the hook functions
> all work only on a sqlite connection and i dont want to load modules.
>
> are there any other methods available to get the same result?
> or, as someone mentioned bring some Interbase like facility?
>
>
> thanks
> brs
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Eric Smith]

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 defined).

Can anyone explain this?

Eric

>   time (minutes) to insert 2m records
> 10 ++--+--+---+--+---+-++
>+   +  +   +  A   +   +  +
>  9 ++..++
>|  AAA A |
>  8 ++..AAA..A..++
>| A      |
>  7 ++.A.A..AAA.++
>|    |
>  6 ++..AA...A.A++
>|AAA |
>  5 ++A...AA++
>  4 ++.AA.AA..A.A...++
>|   A AAA  AA|
>  3 ++..++
>|   AAA A  A |
>  2 ++.AA...++
>| AA |
>  1 ++...AAA++
>AAA +  +   +  +   +  +
>  0 ++--+--+---+--+---+-++
>0  100200 300400 500600
>millions of existing records

--
Eric A. Smith

A nickel ain't worth a dime anymore.
-- Yogi Berra
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unexpected large journal file

[Jim Wilcoxson]

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 
> > you also reading it frequently ?  If not, then it might be worth making an 
> > index on that primary key only when you're about to need it.  
...
> I tried removing the PK definition as you both suggested, and the 
> journal stays fixed at less than 20Kb, even against a db size of (at 
> the moment) 37Gb.  My insert batch run times are improved by a factor of 
> ~2.5 and seem to be O(1).  
> 
> So, bingo.  :) 


Here's a trick that might help you:

Since your primary key is two integers, you can combine them into one integer
and use them as the primary key, without requiring a separate index:

create table x (
  pkey integer primary key,
  val  text);

a = (value 0-2M)
b = (32-bit integer)
pkey = a<<32 | b
insert into x values (pkey, data)

This shift assumes that b is a 32-bit integer.  Since a is limited to 2M, which
requires only 21 bits, b can be up to 43 bits, or 8796093022207.

Also, I think you mentioned that you are inserting records in this order:

a=0 b=0
a=1 b=0
...
a=2M b=0

then

a=0 b=1
a=1 b=1
...
a=2M b=1

To insert records in order, you should insert them as:

a=0 b=0
a=0 b=1
a=1 b=0
a=1 b=1

Jim
---
HashBackup: easy onsite and offsite Unix backup
http://sites.google.com/site/hashbackup




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Pavel Ivanov]

> 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 to be aware of SQLITE_BUSY code that can be returned from
SQLite functions, so be sure that you process it properly.


Pavel

On Tue, Jun 22, 2010 at 12:44 PM, Ian Hardingham  wrote:
> 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-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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, you still need to worry about locking.  SQLite locks the entire 
database, not individual tables or rows.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Jay A. Kreibich]

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 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
> > > sqlite3_close.
> >
> > Wow, if you really relied on that then you did too few of
> > mutli-threaded programming. 

> Yea, I know it isn't as safe as it should be, but in this case it is one
> class per thread.  Why I am doing it, I don't know, old habit that I just
> cannot seem to break.  In light of your next statement, I think I will
> change it;)

  The practice is justified, as "get it done quickly" has nothing
  to do with threads.  It is a defensive programming style that
  is based off invariants:
  .

  In this case, the one absolute thing that must happen in ::Close() is
  that m_db needs to get set to NULL.  It would be a good idea to also
  close and properly destroy the database connection, but that isn't as
  critical. If the database connection isn't actually closed, the
  application will leak memory and resources.  That's bad, but there
  is a good chance the application will continue to run-- likely until
  the natural termination of the process, which will clean everything
  up anyways.  However, if m_db is not set to NULL there is, as we
  saw, a good chance the application will just crash outright.  Most
  people consider this worse, especially if it happens on a customer's
  machine (the common case for most code execution).

  So the very first thing the function does is set m_db to NULL.  It
  then uses a locally scoped copy of the database handle to actually
  try to get some work done (like call sqlite3_close()).  But since
  this variable is locally scoped, we know it will go out of scope at
  the end of this function.

  This means that, one way or another, no matter how the function
  exits, neither the class nor any other variable in the application
  will have a stale reference to the database handle.  This greatly
  reduces the possibility of a crash.  In fact, the only stale pointer
  that ever exists is the locally scoped copy, which has a very short
  and confined lifetime.  If the function does something wrong, the
  database connection might not actually be closed, but that's true
  regardless of how you structure the function.

  In a function this small it might seem a bit silly, but consider a
  100 line function that has the "m_db = NULL;" statement at the
  bottom.  All it takes is someone to come back to code three months
  later, do a quick scan, and add a "return;" half way through the 
  function to deal with a hopeless error condition (this being called
  from a distructor makes any serious action difficult).  The same general
  ideas apply to called client functions (e.g. stuff like sqlite3_close()) 
  that might throw an exception, kill the thread, or otherwise disrupt
  the normal code flow.  To defend against this, we do the important
  stuff "quickly," before any other code (and, in specific, external
  functions) has a chance to screw something up-- not because of
  concurrency, but because we don't trust the code (and the functions
  it calls) that may run next.

  You can say that this just points to bigger structure and error
  handling issues, and you might be right.  This is just a safety net,
  not an end-all solution.  But this is still a really good habit,
  especially for situations similar to COM code that is likely
  wrappering other code or libraries not under the control of the
  developer writing this code.  This style provides a line of defense
  against poorly written libraries, as well as future-proofing against
  libraries that may be changed or updated independently of the
  wrapper code.

  There are those that might say crashing is better than silently failing,
  as it is more "in your face" and demands attention (and a fix).
  That's only true while in development, however, where there is a
  chance to notice it, analyze it, and fix it.  A customer crash is
  almost always a worst-case situation.

  Even in-development, if you're using tools like Valgrind, the any 
  issue with program logic or flow control is likely to show up.  A
  Valgrind report should put up just as big a red flag as a crash-- and
  is likely to be easier to debug.



  The biggest problem with invariant style programming is that, quite
  frankly, it is very hard.  You also need very strict and specific
  requirements for each function.   Unless you've been doing it for
  some time, it might seem odd that setting the pointer to NULL is
  an absolute requirement of the ::Close() method, while actually
  closing the database connection is not.  

[sqlite] Accessing an sqlite db from two different programs

[Ian Hardingham]

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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] marking transaction boundaries

[b s]

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 transaction boundaries from a user level.

my requirement: on begin transaction, i would like to store
a hex(randomblob(16)) in a table during begin transaction and
put this value in all tables that were involved during that 
transaction.

there is no way it can be done across all connections, unless
a trigger level like facility is available. otherwise the hook functions
all work only on a sqlite connection and i dont want to load modules.

are there any other methods available to get the same result?
or, as someone mentioned bring some Interbase like facility?


thanks
brs



  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Pavel Ivanov]

> 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 related to database locking and so
busy handler is not called. You can force connection closing in case
of SQLITE_BUSY result by forcible finalizing of all statements. Use
http://www.sqlite.org/c3ref/next_stmt.html for iterating all
statements and sqlite3_finalize on each of them. After that
sqlite3_close should complete successfully.


Pavel

On Tue, Jun 22, 2010 at 9:48 AM, Sam Carleton
 wrote:
> 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
>> > sqlite3_close, the second thread finds m_db null and will not call
>> > sqlite3_close.
>>
>> Wow, if you really relied on that then you did too few of
>> mutli-threaded programming. The rule of thumb is: no matter how fast
>> you think 2 different operations execute there always will be a
>> situation when another thread executes something between those 2
>> operations (and that "something" can be a very big chunk of code). So
>> you must protect it with some mutex or you can use atomic swaps (but
>> they are platform- and compiler-dependent).
>>
>
> Yea, I know it isn't as safe as it should be, but in this case it is one
> class per thread.  Why I am doing it, I don't know, old habit that I just
> cannot seem to break.  In light of your next statement, I think I will
> change it;)
>
>
>> Another problem with this code (which is not related to the crash): do
>> you know that sqlite3_close can return SQLITE_BUSY when there are some
>> statements or BLOB handles still open and not finalized?
>>
>
> 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.
>
> Sam
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Sam Carleton]

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
> > sqlite3_close, the second thread finds m_db null and will not call
> > sqlite3_close.
>
> Wow, if you really relied on that then you did too few of
> mutli-threaded programming. The rule of thumb is: no matter how fast
> you think 2 different operations execute there always will be a
> situation when another thread executes something between those 2
> operations (and that "something" can be a very big chunk of code). So
> you must protect it with some mutex or you can use atomic swaps (but
> they are platform- and compiler-dependent).
>

Yea, I know it isn't as safe as it should be, but in this case it is one
class per thread.  Why I am doing it, I don't know, old habit that I just
cannot seem to break.  In light of your next statement, I think I will
change it;)


> Another problem with this code (which is not related to the crash): do
> you know that sqlite3_close can return SQLITE_BUSY when there are some
> statements or BLOB handles still open and not finalized?
>

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.

Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how often to open the db

[Pavel Ivanov]

> 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 db, and then return the result to the user. At least, that
> is how I do it.

And if you open and close connection several times for one Apache
request it will just hurt your performance. But if you do that only
2-3 times per request then you probably won't notice it until load on
Apache server grows up to the certain level.


Pavel

On Mon, Jun 21, 2010 at 11:54 PM, P Kishor  wrote:
> On Mon, Jun 21, 2010 at 10:44 PM, Sam Carleton
>  wrote:
>> I have asked this Q a number of times over the last year and NEVER gotten
>> ANYONE to even comment on it.  I am wondering why:
>>
>> Am I opening the DB too much?
>
> what is too much? I mean, the computer is not going to get tired. But,
> are you experiencing speed bottlenecks?
>
>>
>> My usage of SQLite is in an Apache module that opens the DB each time it
>> needs info from the DB:  For authentication it is open/closed, for the
>> initialization of the request the db is open/closed, for the processing of
>> the request, the db is opened and closed.
>
> 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 db, and then return the result to the user. At least, that
> is how I do it.
>
> In fact, once the Apache process ends, the db connection should close
> automatically, although it doesn't hurt to close it explicitly.
>
>
>
>
>>
>> Is this OK, or should I open it once for the whole request and close it when
>> the whole request is finished?
>>
>> Sam
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Pavel Ivanov]

> 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
> sqlite3_close.

Wow, if you really relied on that then you did too few of
mutli-threaded programming. The rule of thumb is: no matter how fast
you think 2 different operations execute there always will be a
situation when another thread executes something between those 2
operations (and that "something" can be a very big chunk of code). So
you must protect it with some mutex or you can use atomic swaps (but
they are platform- and compiler-dependent).

Another problem with this code (which is not related to the crash): do
you know that sqlite3_close can return SQLITE_BUSY when there are some
statements or BLOB handles still open and not finalized?


Pavel

On Tue, Jun 22, 2010 at 3:27 AM, Sam Carleton
 wrote:
> 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 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
> sqlite3_close.  Of course it would help if I actually NULLed out m_db,
> rather than did a compare on it!  I shake my head at myself sometimes.
> Thanks a million Jay, I really appreciate it!
>
> Sam
>
> On Tue, Jun 22, 2010 at 12:06 AM, Jay A. Kreibich  wrote:
>
>> On Mon, Jun 21, 2010 at 11:56:09PM -0400, Sam Carleton scratched on the
>> wall:
>>
>> > I am NEVER capturing anything in the log file, so it looks like
>> everything
>> > is returning the correct. I am a bit stumped right now, so if it is
>> alright,
>> > I am going to post my code:
>>
>>   If you're doing a double close, it won't log an error, it will just
>> crash.
>>
>> > void CSQLiteDB::Close()
>> > {
>> >     if(m_db)
>> >     {
>> >         sqlite3 *db = m_db;
>> >         m_db == NULL;
>>
>>   No.
>>
>> >         sqlite3_close(db);
>> >     }
>> > }
>>
>>  If you're manually calling ::Close() anywhere, when the object is
>>  destroyed it will do a double close.
>>
>>   -j
>>
>> --
>> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>>
>> "Intelligence is like underwear: it is important that you have it,
>>  but showing it to the wrong people has the tendency to make them
>>  feel uncomfortable." -- Angela Johnson
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] max blob size without overflow page

[Jan Asselman]

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 'column_n' being BLOB nor TEXT columns.

What would be the maximal allowed size of the blob in order to make a
single record fit onto a single sqlite page? I want to avoid overflow
pages as I can make the BLOBs any size I want (they are part of a
continuous data stream).

I took a look at the sqlite file format documentation and I have
deducted the following formula:


MAX_BLOB_SIZE = 

SQLITE_PAGE_SIZE [32K] - DATABASE_HEADER_BYTE_OFFSET_20 [0] - 35

- RECORD_SIZE [VARINT]

- KEY VALUE [VARINT]

- RECORD_HEADER_SIZE [VARINT]

- N * (HEADER_TYPE [SINGLE_BYTE_VARINT] + RECORD_DATA [VARINT])

- BLOB_HEADER_TYPE [VARINT]


with

[VARINT] = 9
[SINGLE_BYTE_VARINT] = 1


Any comments or improvements would be appreciated.

Thanks in advance,

Jan Asselman
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Oracle joins the SQLite Consortium

[Greg Burd]

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 go well together without 
damaging either one.  I am also the guy responsible for most of the messaging 
on the Oracle.com website (with a lot of editing oversight and marketing 
input), so if you want to question something there please just email me.

We here in the Berkeley DB team within Oracle's Embedded Database group are 
thrilled to have Oracle join the SQLite Consortium.  Today and in the past our 
goal with open source collaborations has been to work closely together, help 
each other out, keep things informal-yet-formal, and give credit where credit 
is due.  The SQLite product is excellent, we don't want or need to fork it.  
The SQLite3 ANSI C API is like the BDB ANSI C key/value API, de-facto standards 
in their respective spaces.  From our view this combination is like chocolate 
and peanut butter, two great products that go well together.  Some will like 
this combo and find value in it, others won't.  That's okay, in fact it's the 
way it should be.  We are thrilled to be joining this community, we're not the 
enemy or the competition.

Clearly there are going to be many questions, I'm here to help answer them as 
best I can.


License: Oracle Berkeley DB is not licensed under the GPL.  Berkeley DB is 
released under the terms of the Sleepycat License.  The Sleepycat License is 
significantly different from the GPL, take a look.  
http://en.wikipedia.org/wiki/Sleepycat_License

Compatibility: "... [the] application-level behavior of the two products is 
identical..."  Okay, this is a bit of an overstatement at this point and I 
freely admit that.  This is our long-term goal, so I think it's fair to put 
have it on our site.  Basically we're telling people that we'd like to be as 
close to 100% drop-in compatible as possible while still providing the unique 
value of Berkeley DB as a btree storage engine.  For our first release, I think 
you'll have to admit that we are very close to the mark.  We're already nearing 
a patch release and it is even closer.  This will evolve, both SQLite and BDB's 
SQL will benefit along the way.

Comparison: "... improved performance, concurrency, scalability, and 
reliability."  Fundamentally, we are faster because we don't lock the entire 
database on writes as SQLite's btree does.  BDB is designed for concurrent 
multi-process/thread access, this gives us a speed advantage when there is any 
concurrency in the system.  Single-threaded performance is a more 
apples-to-apples comparison and this is more evenly matched.  The product is 
evolving fast, we're constantly finding ways to use advanced features in BDB 
for special cases in SQLite.  Again, we're only just in release 1 of the 
combined product and we're already in very good shape to be faster in general.

MVCC: We're going to add in support for MVCC (snapshot isolation), it's not 
there in the first release.  This will continue to help speed up concurrent 
access and prevent deadlocks.

HA: Clearly we're going to integrate (in a SQLite-friendly way) support for 
HA/replication.  It's not there in this release.  If you have ideas for how to 
properly make this fit into the product let us know!  Should it be a PRAGMA?  
Should it be C-functions?  Something else?  Speak up now.

Compaction: We punted on compaction in the first release because we wanted to 
do it using BDB's built-in compaction code (which can compact the database and 
optimize the btree while it's being used, it can even do this a little bit at a 
time so as not to be overly disruptive).  We didn't get this into the code line 
in time for the first release, it's coming very soon.

Compression: BDB has support for compression of things stored in the database, 
this is something we hope to integrate into the SQL API very soon.

Encryption: Again, we are hard at work on this.  BDB already supports encrypted 
databases, so it won't be hard to do.


We are also working on a comparison paper with Mike Owens (of "The Definitive 
Guide to SQLite" fame).  We hope to get this finished very soon and make it 
available for comment.  Mike is doing a great job of really providing a 
critical view of our combined product, which is exactly what we want.

There is a lot more we're working on, we're focused on a number of improvements 
in BDB itself and in our adaptor layer between BDB and SQLite.  What's great 
about this is that for fifteen years we had avoided SQL and just focused on 
being the best transactional storage engine around and I'd say we did a pretty 
good job of that.  Now we can happily join forces with a great community 
focused on the SQL piece.  SQLite does an amazing job of providing the features 
of a relational database and SQL92 in a 

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

[Luca Olivetti]

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 and will not call
> sqlite3_close.

Unless the second thread is called between the copy and the null assignment.
I think that these concurrent accesses should be protected by a critical 
section.

Bye
-- 
Luca Olivetti
Wetron Automatización S.A. http://www.wetron.es/
Tel. +34 93 5883004 (Ext.133)  Fax +34 93 5883007
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert Using Tcl Array?

[Alexey Pechnikov]

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)
}
set sql_pairs [join $sql_pairs ,]
set query "update $table set $sql_pairs where id=$info(id)"
db eval $query
}


2010/5/30 Ross Hayden :
> If SQLite can return to me a Tcl array from SELECT, why does the feature not
> exist to INSERT, UPDATE, or DELETE using a properly formed Tcl array as an
> argument?
>
> Seems nice to have, but perhaps I'm not aware of possible dangers in such a
> feature.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Sam Carleton]

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 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
sqlite3_close.  Of course it would help if I actually NULLed out m_db,
rather than did a compare on it!  I shake my head at myself sometimes.
Thanks a million Jay, I really appreciate it!

Sam

On Tue, Jun 22, 2010 at 12:06 AM, Jay A. Kreibich  wrote:

> On Mon, Jun 21, 2010 at 11:56:09PM -0400, Sam Carleton scratched on the
> wall:
>
> > I am NEVER capturing anything in the log file, so it looks like
> everything
> > is returning the correct. I am a bit stumped right now, so if it is
> alright,
> > I am going to post my code:
>
>   If you're doing a double close, it won't log an error, it will just
> crash.
>
> > void CSQLiteDB::Close()
> > {
> > if(m_db)
> > {
> > sqlite3 *db = m_db;
> > m_db == NULL;
>
>   No.
>
> > sqlite3_close(db);
> > }
> > }
>
>  If you're manually calling ::Close() anywhere, when the object is
>  destroyed it will do a double close.
>
>   -j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it,
>  but showing it to the wrong people has the tendency to make them
>  feel uncomfortable." -- Angela Johnson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users