Re: [sqlite] File locking additions

[Ritesh Kapoor]

In case the designer knows that the DB file will be accessed by a single
program-thread throught the run - then there should be some sort of flag
indicating that SQLITE should execute minimum locking-related code.

We were facing problems with flock over NFS mounted file systems.  Since
we din't need any locking on our DB file I copy-pasted the return
statement in function 'sqlite3OsLock' to the beginning of the function,
so that it always returns SQLITE_OK.

Regards,
ritesh


On Wed, 2006-03-08 at 05:36, Adam Swift wrote:
> All,
> 
> In order to provide locking support for database files mounted from  
> remote file systems (NFS, SMB, AFP, etc) as well as provide  
> compatible locking support between database clients both local and  
> remote, I would like to propose some additions to the existing  
> database file locking behavior.
> 
> I have discussed this briefly with D. Richard Hipp and he has  
> expressed interest in pursuing it.  We would appreciate feedback/ 
> suggestions/concerns on the proposed solutions below.
> 
> 1. Support a variety of locking mechanisms, from the lowest  
> granularity (using a .lock file)  to the highest (the  
> existing advisory locks).  A preliminary list: .lock files, flock,  
> afp locks, posix advisory locks.
> 
> 2. Allow clients to specify type of locking (on database open)  
> manually or ask sqlite to automatically detect the best locking  
> supported by the file system hosting the database file (automatic  
> detection would not work in a mixed local/remote file system  
> situation, all clients of a single database file need to use the same  
> type of locking to avoid conflicts).
> 
> 3. Extend the sqlite3_open commands to support URI style path  
> references in order to specify the file system locking type (as  
> opposed to modifying the arguments list).  After a little poking  
> around on RFC 3986  I'm inclined  
> to specify the locking choice via the query part of the URI.  For  
> example:
> 
>   file:///mounts/myhost/dbfile.db?locktype=flock
>   file:///localdisk/otherdbfile.db?locktype=automatic
> 
> Thanks in advance for your input.
> 
> Adam Swift
> 

[sqlite] Optimization help requested

[Pam Greene]

Hi all,

I'm working on a system to add full-text indexing on top of SQLite in a
semi-automated way.  The general idea is that users will call an API to
"register" a document table for indexing, and the system will take care of
everything from there.

When a row is added to a registered document table, an SQLite trigger calls
a C function that parses the text for that document and saves it in a token
table, along with some meta-information that's used later by the querying
system to retrieve documents matching a given search.

Although all this is working, it's awfully slow.  I'm fairly new to SQLite,
and I'm hoping that some of the gurus out there can give me advice on
speeding it up a bit.

A token table looks like this:

CREATE TABLE IF NOT EXISTS Tokens (
Token TEXT NOT NULL,
DocID TEXT NOT NULL,
Position INTEGER NOT NULL,
Offset INTEGER NOT NULL,
DocumentTable TEXT NOT NULL,
DocumentColumn TEXT NOT NULL)

with two indexes:

CREATE INDEX IF NOT EXISTS Tokens_Index ON
  Tokens(Token, DocID, Position)


CREATE INDEX IF NOT EXISTS Tokens_DocIndex ON
  Tokens(DocID, DocumentTable,
DocumentColumn)

The INSERT trigger calls a function that eventually leads to one of these
for each word in the document's text:

INSERT INTO Tokens VALUES ("%s", '%q', %d, %d, '%q', '%q')

The DELETE trigger produces

DELETE FROM Tokens
  WHERE DocID='%q' AND DocumentTable='%q' AND DocumentColumn='%q'

The UPDATE trigger is approximately a combination of the two.

Finally, a complex search query like 'one two -three "four five"' might
produce a command like

SELECT t0.DocID, COUNT(t0.DocID) AS Rank
  FROM Tokens AS t0, Tokens AS t1, Tokens AS t3, Tokens AS t4
  USING (DocID)
  WHERE t0.Token="one" AND t1.Token="two" AND t3.Token="four" AND
t4.Token="five"

AND t4.Position=(t3.Position + 1)
AND NOT t0.DocID IN (SELECT DISTINCT DocID FROM Tokens WHERE
Token="three")
  GROUP BY t0.DocID
  ORDER BY Rank DESC

So, I'm sure that there are some simple things I can do to speed up adding
documents and performing searches (the most common operations), like
reordering the WHERE conditions.  I'd imagine that there are some more
complicated things, too, like splitting the "NOT" condition into a separate
query -- but I don't know which are likely to give a net speedup.  Any
advice?

Thanks,

- Pam

Re: [sqlite] sqlite performance with sizeable tables

[drh]

<[EMAIL PROTECTED]> wrote:
> Hi all
> 
> it seems that I am running in a problem with the way sqlite accesses the disk 
> when inserting rows of data in databases that are large in number of records 
> but not necessary big on disk (I am talking millions of records in files that 
> are in the order of a few hundred MBytes).
> 
> I reduced to this test case:
> 
> I have a table that I create with
> PRAGMA cache_size = 32000
> CREATE TABLE IF NOT EXISTS name2uid(fname VARCHAR(100) NOT NULL , lname 
> VARCHAR(32) NOT NULL, uid INTEGER NOT NULL,
>  PRIMARY KEY (fname, lname));
> 

SQLite inserts in records in primary key order.  (That is not
strictly true - but it is close enough to being true for the
purposes of what follows.)  So when you insert records that
are already in fname order, they can be appended to the file
and the insert goes relatively quickly. This is because the
file does not need to be reorganized to make space for the
new row (it is simply appended) and because nearby information
is already in cache and can be fetched quickly.  But if you 
insert with randomly ordered fnames, then records are constantly 
being inserted into different places in the middle of the file.
This takes more time because the file has to be reorganized 
to make space for the new record and because each record is 
in a different spot you are have no locality of reference 
and the cache is much less effective.

An SQL database engine is not required to insert records in
primary key order.  (In fact, SQLite version 1.0 didn't).  But
if they don't then queries that involve inequalities on the
primary key are much less efficient.  Most people would rather
have slower inserts in exchange for faster queries.

Some database engines have non-standard extensions that let
the programmer specify how entries are stored.  That way the
application designer can choose which method works best for
each application.  If you need fast inserts and never do
inequality comparisons on the primary key, then a hashing-style
table will work better than SQLite's b-tree tables.  But
adding such options bloats the database engine and is contrary
to the goals of SQLite.  So SQLite chooses a single algorithm
that might not be the fastest for every special case, but it
fast enough in the general case that it usually doesn't matter.

--
D. Richard Hipp   <[EMAIL PROTECTED]>

RE: [sqlite] File locking additions

[Marian Olteanu]

I would say that the best way to access a sqlite database mounted from a
remote file server, concurrently with other processes is through a database
server. My opinion is that the overhead of file sync and file locking for a
remote file system is higher than simple TCP/IP communication overhead. The
server would be able to use also the same cache, would have very fast access
to the file (local file), etc.

Building a server for sqlite is not a very complicated task. It can take as
few as a couple hours.


-Original Message-
From: Adam Swift [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, March 07, 2006 6:07 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] File locking additions

All,

In order to provide locking support for database files mounted from  
remote file systems (NFS, SMB, AFP, etc) as well as provide  
compatible locking support between database clients both local and  
remote, I would like to propose some additions to the existing  
database file locking behavior.

I have discussed this briefly with D. Richard Hipp and he has  
expressed interest in pursuing it.  We would appreciate feedback/ 
suggestions/concerns on the proposed solutions below.

1. Support a variety of locking mechanisms, from the lowest  
granularity (using a .lock file)  to the highest (the  
existing advisory locks).  A preliminary list: .lock files, flock,  
afp locks, posix advisory locks.

2. Allow clients to specify type of locking (on database open)  
manually or ask sqlite to automatically detect the best locking  
supported by the file system hosting the database file (automatic  
detection would not work in a mixed local/remote file system  
situation, all clients of a single database file need to use the same  
type of locking to avoid conflicts).

3. Extend the sqlite3_open commands to support URI style path  
references in order to specify the file system locking type (as  
opposed to modifying the arguments list).  After a little poking  
around on RFC 3986  I'm inclined  
to specify the locking choice via the query part of the URI.  For  
example:

file:///mounts/myhost/dbfile.db?locktype=flock
file:///localdisk/otherdbfile.db?locktype=automatic

Thanks in advance for your input.

Adam Swift

[sqlite] File locking additions

[Adam Swift]

All,

In order to provide locking support for database files mounted from  
remote file systems (NFS, SMB, AFP, etc) as well as provide  
compatible locking support between database clients both local and  
remote, I would like to propose some additions to the existing  
database file locking behavior.


I have discussed this briefly with D. Richard Hipp and he has  
expressed interest in pursuing it.  We would appreciate feedback/ 
suggestions/concerns on the proposed solutions below.


1. Support a variety of locking mechanisms, from the lowest  
granularity (using a .lock file)  to the highest (the  
existing advisory locks).  A preliminary list: .lock files, flock,  
afp locks, posix advisory locks.


2. Allow clients to specify type of locking (on database open)  
manually or ask sqlite to automatically detect the best locking  
supported by the file system hosting the database file (automatic  
detection would not work in a mixed local/remote file system  
situation, all clients of a single database file need to use the same  
type of locking to avoid conflicts).


3. Extend the sqlite3_open commands to support URI style path  
references in order to specify the file system locking type (as  
opposed to modifying the arguments list).  After a little poking  
around on RFC 3986  I'm inclined  
to specify the locking choice via the query part of the URI.  For  
example:


file:///mounts/myhost/dbfile.db?locktype=flock
file:///localdisk/otherdbfile.db?locktype=automatic

Thanks in advance for your input.

Adam Swift

[sqlite] sqlite performance with sizeable tables

[spaminos-sqlite]

Hi all

it seems that I am running in a problem with the way sqlite accesses the disk 
when inserting rows of data in databases that are large in number of records 
but not necessary big on disk (I am talking millions of records in files that 
are in the order of a few hundred MBytes).

I reduced to this test case:

I have a table that I create with
PRAGMA cache_size = 32000
CREATE TABLE IF NOT EXISTS name2uid(fname VARCHAR(100) NOT NULL , lname 
VARCHAR(32) NOT NULL, uid INTEGER NOT NULL,
 PRIMARY KEY (fname, lname));

then for my test, I do inserts that look like this:
REPLACE INTO name2uid (fname, lname, uid) VALUES ('%s','SMITH',%d)
where I set fname as U%xser (%x replaced by the uid that I generate myself).

I create transactions of 1 replace at a time.

I am running on Linux Fedora Core 3 on a Opteron 146 (2GHz), with 2GB RAM, SATA 
drive with a partition reserved for the sqlite database.

IMPORTANT: I umount/mount the partition between tests to clear the disk caches 
from the OS.

Test 1:
>From an empty DB.
I loop so that the uids are consecutive numbers from 1 to 1 million.
at this point, each transaction takes less than 1 second to execute. The whole 
1M inserts (100 transactions) take 74 seconds ie 13000 inserts/second.

Test 2:
with the DB from Test 1, I run the exact same sequence of inserts.
the 1M inserts take 103 seconds to execute that's still 9700 inserts/second.
First transaction is 8 seconds then about 1 second.
At this point everything is OK.

Test 3:
from the DB from Test 2, I run 1 million inserts where the uid is selected 
randomly between 1 and 1 million.
At this point, the performance is pretty bad:
the first 1 insert transaction takes 31 seconds to run, the next ones take 
over 5 seconds, for a total run time of 623 seconds.
That's 1600 inserts/second (6 times slower than the ordered case).

It seems that the performance degrades pretty badly with the number of records:
this is still a relatively small dataset (especially given the simplicity of 
the table).
To me, it looks like there is a problem in the way the files are accessed.

I tried to partition the dataset by creating separate databases or tables, but 
creating separate databases make things slower (expected, as the problem is 
disk I/O seeks probably), and partitioning tables give me only a 20% speed gain 
on those 1M insert tests.

Things get really slow afterwards, for example moving to 2M records (137 MB on 
disk):
Test 1 w/ 2M is 138 seconds, about 2 times slower than the 1M case (normal)
Test 2 w/ 2M is 192 seconds, 2 times slower (normal).
Test 3 w/ 2M is 2390 seconds 4 times slower than in the 1M case (12 times 
slower than the ordered case).

I didn't try these tests with bigger sets (my original app was on a DB of about 
8M records, and things were very very slow).

Given the size of the DB on disk, I would think that the speed could be much 
more consistant than that (especially considering that it should cache a lot of 
the information quickly).

Any idea?

Nicolas


PS: I use the c api for running those tests.

[sqlite] Sankara Narayanan is out of the office.

[Sankara Narayanan]

I will be out of the office starting  2006-03-08 and will not return until
2006-03-12.

I am on vacation from Wed 8th March to Friday 10th March. I will not be
having access to the mails in this time.  I will respond to your message
when I return on Monday 13th March.

Re: [sqlite] SQLite open via a file HANDLE

[drh]

Christian Tacke <[EMAIL PROTECTED]> wrote:
> On Tue, Mar 07, 2006 at 03:52:35PM -0500, [EMAIL PROTECTED] wrote:
> > SQLite must know the name of the file so that it can
> > create a rollback journal with a name that is based on the
> > database filename.  The rollback journal filename must be
> > computable from the database filename so that other applications
> > can locate the rollback journal in order to rollback uncommitted
> > changes the first time the database is opened following a crash.
> 
> So having hard- or symlinks to a db file is bad idea...
> 

Yes.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] SQLite open via a file HANDLE

[Christian Tacke]

On Tue, Mar 07, 2006 at 03:52:35PM -0500, [EMAIL PROTECTED] wrote:
> "Marvin K. Bellamy" <[EMAIL PROTECTED]> wrote:
> > Is there a safe way to open a database on Windows by passing in a file 
> > HANDLE instead of a file path?
> > 
> 
> No.  SQLite must know the name of the file so that it can
> create a rollback journal with a name that is based on the
> database filename.  The rollback journal filename must be
> computable from the database filename so that other applications
> can locate the rollback journal in order to rollback uncommitted
> changes the first time the database is opened following a crash.

So having hard- or symlinks to a db file is bad idea...


Elrond

Re: [sqlite] SQLite open via a file HANDLE

[drh]

"Marvin K. Bellamy" <[EMAIL PROTECTED]> wrote:
> Is there a safe way to open a database on Windows by passing in a file 
> HANDLE instead of a file path?
> 

No.  SQLite must know the name of the file so that it can
create a rollback journal with a name that is based on the
database filename.  The rollback journal filename must be
computable from the database filename so that other applications
can locate the rollback journal in order to rollback uncommitted
changes the first time the database is opened following a crash.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Compatability issues with DBD::SQLite and SQLite v3.3.3 and 3.3.4

[Steve Green]

Thanks, that seems to have fixed the problem...

Steve

Eric Bohlman wrote:


Nathan Kurz wrote:


On Mon, Mar 06, 2006 at 06:24:13PM -0800, Steve Green wrote:


Hi,

I'm hoping that someone can shed some light on the following issue that
I'm seeing.  When I attempt to create a temp table using DBD::SQLite 
(v1.11)

and either SQLite v3.3.3 or v3.3.4, I get the following error:

DBD::SQLite::db do failed: not an error(21) at dbdimp.c line 398 at 



Same failures here on Linux 2.4.29.  I didn't have time to debug it,
so I decided just to downgrade to a copy of 3.2.7 that I had around,
and things were fine thereafter.  I presume that some recent change to
sqlite is not yet reflected in DBD::SQLite.  'make test' for
DBD::SQLite is another fine test program that fails.



You'll need to go into dbdimp.c and change the two calls to 
sqlite3_prepare() so that the third argument is -1 rather than zero. 
This is due to the change in check-in 3047.


--
Steve Green
SAVVIS
Transforming Information Technology SM

This message contains information which may be confidential and/or
privileged.  Unless you are the intended recipient (or authorized
to receive for the intended recipient), you may not read, use,
copy or disclose to anyone the message or any information contained
in the message. If you have received the message in error, please
advise the sender by reply e-mail at [EMAIL PROTECTED] and
delete the message and any attachment(s) thereto without retaining
any copies.

[sqlite] SQLite open via a file HANDLE

[Marvin K. Bellamy]

Is there a safe way to open a database on Windows by passing in a file 
HANDLE instead of a file path?


--
Marvin Keith Bellamy
Software Engineer
Innovision Corporation
913.438.3200

Re: [sqlite] Busy management

[John Stanton]

It depends upon your application.  What are you trying to do?

Ludovic Ferrandis wrote:

Thanks for your answers,

but that still don't explain me which method is the best to manage Busy
statement, to do a loop or to use a busy callback. And as I'm very curious,
especially why one method is better than the other. :)

Thanks,

Ludovic

On 3/5/06, John Stanton <[EMAIL PROTECTED]> wrote:


I have a several of suggestions for that busy logic.
1.  Perform a "yield()" or Win sleep(0) to give up the current
timeslice and thus have a busy wait with as little system impact
as possible but fastest turnaround.  An upper limit of 100 probes
before failing as deadlocked seems to be reasonable with that
approach.
2.  Launch a "reset" call to restart the sqlite3_step on finding
a BUSY to make sure that deadlock situations are broken by
brute force, with all but one process yielding control.
3.  If the contention comes from competing threads within a process
or competing processes in an application, define a semaphore or
similar appropriate lock to synchronize each database and avoid the
possibility of ever having a BUSY.  In windows it looks like this
 sqlite3_prep
 sqlite3_bind
 WaitOnSingleEvent
   sqlite3_step
   
 SetEvent
This is a lower overhead, higher performance and generally more
elegant approach than polling.  It does however assume that there
are co-operating threads and processes.
JS

Jay Sprenkle wrote:


On 3/3/06, Ludovic Ferrandis <[EMAIL PROTECTED]> wrote:



I want to manage the SQLITE_BUSY error like this: If it fails, sleep X
ms then try the command Y times. I found 2 ways to do it:



I do it using sqlite3_step(); Using bound variables and step eliminates


the need


for escaping string data and prevents SQL injection attacks. I retry
the statement
up to 10 times in case another process has locked the database.
Psuedo code looks like this:

   // open database
   dbOpen();

   // Get configuration information for this website instance
   string sql = "SELECT blah"
" FROM Setup"
;

   // prepare statement instead of building it to avoid sql injection


attacks


   if ( ! dbPrep( sql ) )
 throw ConException( string("Cannot prepare sql: ") + sql +
string(", ") +  + sqlite3_errmsg(db) );

   bool loop = true;
   for ( int i = 0; ( i < 10 ) && ( loop ); i++ )
 switch ( dbStep() )
   {
 // if database busy wait for a short time
 // to see if it becomes available
 case SQLITE_BUSY:
 case SQLITE_LOCKED:
   break;
 case SQLITE_ROW:
   // get results ( 0 based index!!! )
   blah  = dbColumn( 0 );
   break;
 case SQLITE_DONE:
   if ( CookieUser.empty() )
 throw ConException( string("Invalid configuration") );
   loop = false;
   break;
 default:
   throw ConException( string("Cannot execute sql: ") + sql );
   break;
   }

   // clean up when finished
   dbFinalize();
   dbClose();

Re: [sqlite] sqlite reporting an unexpected version number

[Chuck Lima]

Thanks everyone for your prompt responses.

Here is what I found.  I was running the correct executable, however 
I had some stale .so files hanging around down in /usr/lib, while 
the ones that I wanted to use where in /usr/local/lib .


I blew away the ones down in /usr/lib, reran ldconfig and all is 
right with the world.  I'll wait to see what breaks when trying to 
access those other libraries.


Gracias,
-Chuck


Pam Greene wrote:

On 3/7/06, Chuck Lima <[EMAIL PROTECTED]> wrote:

Hi,

I just compiled and installed sqlite-3.3.4 on FC4 and it seems to be
running just fine, except for a few things.

sqlite3 -version returns 3.1.4

This in itself is not a problem, but I can't seem to get constraints
to work and the documentation indicates that I need version >= 3.3 .
  Is the -version switch merely misreporting the real version number
or does the sqlite-3.3.4.tar.gz file actually contain the 3.1.4 files?



There's another possibility.  Have you ever had another version of sqlite
installed?  Try writing a tiny program with the following, or its
equivalent, in it:

 fprintf(stdout, "library info: %d %d\n",
  SQLITE_VERSION_NUMBER,
  sqlite3_libversion_number());

The first is the constant defined in the sqlite3.h file your program is
using.  The second is the value of that constant that was compiled into the
library you're using.  If they don't match, or if they don't match what
sqlite3 -version is reporting, then something's wrong with your installation
-- probably you have two copies of sqlite in different places.

- Pam

Re: [sqlite] sqlite reporting an unexpected version number

[drh]

Chuck Lima <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> I just compiled and installed sqlite-3.3.4 on FC4 and it seems to be 
> running just fine, except for a few things.
> 
> sqlite3 -version returns 3.1.4
> 
> This in itself is not a problem, but I can't seem to get constraints 
> to work and the documentation indicates that I need version >= 3.3 . 
>   Is the -version switch merely misreporting the real version number 
> or does the sqlite-3.3.4.tar.gz file actually contain the 3.1.4 files?
> 
> I need to know if I should continue to test/debug my constraint 
> syntax or get the right version of sqlite.
> 

The most likely problem is that you already have 3.1.4 installed
on your system in some place that is earlier on your PATH than
where you are installing 3.3.4.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] sqlite reporting an unexpected version number

[Dennis Cote]

Chuck Lima wrote:


Hi,

I just compiled and installed sqlite-3.3.4 on FC4 and it seems to be 
running just fine, except for a few things.


sqlite3 -version returns 3.1.4

This in itself is not a problem, but I can't seem to get constraints 
to work and the documentation indicates that I need version >= 3.3 . 
 Is the -version switch merely misreporting the real version number or 
does the sqlite-3.3.4.tar.gz file actually contain the 3.1.4 files?


I need to know if I should continue to test/debug my constraint syntax 
or get the right version of sqlite.


I also compiled and installed from a CVS checkout with the same results.

Thanks in advance for any help.

-Chuck


Chuck

Try using the which command to determine which copy of sqlite ou are 
actually running.


   which sqlite3

It will return the path to the binary it is executing. I suspect you 
have another version (3.1.4) of sqlite installed that is being located 
first.


HTH
Dennis Cote

Re: [sqlite] sqlite reporting an unexpected version number

[Pam Greene]

On 3/7/06, Chuck Lima <[EMAIL PROTECTED]> wrote:
>
> Hi,
>
> I just compiled and installed sqlite-3.3.4 on FC4 and it seems to be
> running just fine, except for a few things.
>
> sqlite3 -version returns 3.1.4
>
> This in itself is not a problem, but I can't seem to get constraints
> to work and the documentation indicates that I need version >= 3.3 .
>   Is the -version switch merely misreporting the real version number
> or does the sqlite-3.3.4.tar.gz file actually contain the 3.1.4 files?


There's another possibility.  Have you ever had another version of sqlite
installed?  Try writing a tiny program with the following, or its
equivalent, in it:

 fprintf(stdout, "library info: %d %d\n",
  SQLITE_VERSION_NUMBER,
  sqlite3_libversion_number());

The first is the constant defined in the sqlite3.h file your program is
using.  The second is the value of that constant that was compiled into the
library you're using.  If they don't match, or if they don't match what
sqlite3 -version is reporting, then something's wrong with your installation
-- probably you have two copies of sqlite in different places.

- Pam

[sqlite] sqlite reporting an unexpected version number

[Chuck Lima]

Hi,

I just compiled and installed sqlite-3.3.4 on FC4 and it seems to be 
running just fine, except for a few things.


sqlite3 -version returns 3.1.4

This in itself is not a problem, but I can't seem to get constraints 
to work and the documentation indicates that I need version >= 3.3 . 
 Is the -version switch merely misreporting the real version number 
or does the sqlite-3.3.4.tar.gz file actually contain the 3.1.4 files?


I need to know if I should continue to test/debug my constraint 
syntax or get the right version of sqlite.


I also compiled and installed from a CVS checkout with the same results.

Thanks in advance for any help.

-Chuck

Re: [sqlite] Merging of like databases to a single master database

[Christian Smith]

On Mon, 6 Mar 2006, Dale Qualls wrote:

>Okay, here's a strange one.  I have sqlite dbs for every day of the week.
>Every db is the same (contains 4 tables) as far as structure goes.
>
>Is there a method from the command line (on a win32 box) that I can use
>the sqlite.exe to merge 10 days (so 10 dbs) worth of data into a single
>"master" database?  I was accomplishing this with 40 separate queries (4
>for each of the 10 days) within Access 2k but it takes an ungodly amount
>of time.
>
>Just wondering if it was possible from the cmd line to speed it up.  A
>vacuum takes a large amount of time as well, each of the database files
>range from 800-1200MB.
>
>This is 2.x by the way.


You can generate a file of inserts using a script. Something like this in
a file script:

.mode insert table1 table2 table3 table4
select * from table1;
 ...
select * from table4;


For each database, run:
C:\dir> sqlite.exe day1.db < script > day1.inserts
 ...
C:\dir> sqlite.exe day10.db < script > day10.inserts
C:\dir> type day1.inserts ... day10.inserts | sqlite.exe master.db

You'd want to change the last command to include a "BEGIN;"  before the
inserts and a "COMMIT;" after the inserts, but my windows command line
knowledge is limited.

If you want a single invocation of sqlite.exe, you can "ATTACH" the
different days and transfer data from each directly:

ATTACH "day1.db" AS day;
BEGIN;
INSERT INTO table1 SELECT * FROM day.table1;
 ...
INSERT INTO table4 SELECT * FROM day.table4;
COMMIT;
DETACH day;
 ...
ATTACH "day10.db" AS day;
BEGIN;
INSERT INTO table1 SELECT * FROM day.table1;
 ...
INSERT INTO table4 SELECT * FROM day.table4;
COMMIT;
DETACH day;

With scripting, this script can be generated from a list of files, and fed
to sqlite.exe using a pipe. It should be as quick as it gets.


>
>Any help would be most appreciated.  The archives didn't lend much help.
>
>ciao!
>


Christian


-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

Re: [sqlite] Merging of like databases to a single master database

[Dale Qualls]

Great, I'll give it a shot!
 
Thanks much!

>>> [EMAIL PROTECTED] 3/7/2006 9:43:05 AM >>>

Dale Qualls wrote:

>Okay, here's a strange one.  I have sqlite dbs for every day of the week.  
>Every db is the same (contains 4 tables) as far as structure goes.
> 
>Is there a method from the command line (on a win32 box) that I can use the 
>sqlite.exe to merge 10 days (so 10 dbs) worth of data into a single "master" 
>database?  I was accomplishing this with 40 separate queries (4 for each of 
>the 10 days) within Access 2k but it takes an ungodly amount of time.
> 
>Just wondering if it was possible from the cmd line to speed it up.  A vacuum 
>takes a large amount of time as well, each of the database files range from 
>800-1200MB.
> 
>This is 2.x by the way.
> 
>Any help would be most appreciated.  The archives didn't lend much help.
> 
>ciao!
>
>
>
>
>
>
>**
>The preceding message and any attachments may contain confidential 
>information protected by the attorney-client or other privilege.  If you 
>believe that it has been sent to you in error, please reply to the sender 
>that you received the message in error and then delete it.  Nothing in 
>this email message, including the typed name of the sender and/or this 
>signature block, is intended to constitute an electronic signature unless 
>a specific statement to the contrary is included in the message.
>**
>
>  
>
Dale

You should be able to do this with a simple sql script file. Take the 
commands below, change the declarations of the tables to match your four 
tables, and the names of the database files to match your files, then 
save the result as "merge.sql". These sql commands attach to each of 
your daily databases, drop and recreate each of your four tables, then 
populate the tables in the master database with all the records from the 
daily databases.

attach database 'day1.db' as day1;
attach database 'day2.db' as day2;
attach database 'day3.db' as day3;
attach database 'day4.db' as day4;
attach database 'day5.db' as day5;
attach database 'day6.db' as day6;
attach database 'day7.db' as day7;
attach database 'day8.db' as day8;
attach database 'day9.db' as day9;
attach database 'day10.db' as day10;

drop table if exists tab1;
drop table if exists tab2;
drop table if exists tab3;
drop table if exists tab4;

create table tab1(...);
create table tab2(...);
create table tab3(...);
create table tab4(...);

insert into tab1
select * from day1.tab1
union select * from day2.tab1
union select * from day3.tab1
union select * from day4.tab1
union select * from day5.tab1
union select * from day6.tab1
union select * from day7.tab1
union select * from day8.tab1
union select * from day9.tab1
union select * from day10.tab1;

insert into tab2
select * from day1.tab2
union select * from day2.tab2
union select * from day3.tab2
union select * from day4.tab2
union select * from day5.tab2
union select * from day6.tab2
union select * from day7.tab2
union select * from day8.tab2
union select * from day9.tab2
union select * from day10.tab2;

insert into tab3
select * from day1.tab3
union select * from day2.tab3
union select * from day3.tab3
union select * from day4.tab3
union select * from day5.tab3
union select * from day6.tab3
union select * from day7.tab3
union select * from day8.tab3
union select * from day9.tab3
union select * from day10.tab3;

insert into tab4
select * from day1.tab4
union select * from day2.tab4
union select * from day3.tab4
union select * from day4.tab4
union select * from day5.tab4
union select * from day6.tab4
union select * from day7.tab4
union select * from day8.tab4
union select * from day9.tab4
union select * from day10.tab4;

Now at the command prompt you can open your master database and execute 
the merge with the following command.

sqlite master.db '.read merge.sql'

HTH
Dennis Cote







**
The preceding message and any attachments may contain confidential
information protected by the attorney-client or other privilege.  If you
believe that it has been sent to you in error, please reply to the sender
that you received the message in error and then delete it.  Nothing in
this email message, including the typed name of the sender and/or this
signature block, is intended to constitute an electronic signature unless
a specific statement to the contrary is included in the message.
*

Re: [sqlite] Merging of like databases to a single master database

[Dennis Cote]

Dale Qualls wrote:


Okay, here's a strange one.  I have sqlite dbs for every day of the week.  
Every db is the same (contains 4 tables) as far as structure goes.

Is there a method from the command line (on a win32 box) that I can use the sqlite.exe to 
merge 10 days (so 10 dbs) worth of data into a single "master" database?  I was 
accomplishing this with 40 separate queries (4 for each of the 10 days) within Access 2k 
but it takes an ungodly amount of time.

Just wondering if it was possible from the cmd line to speed it up.  A vacuum 
takes a large amount of time as well, each of the database files range from 
800-1200MB.

This is 2.x by the way.

Any help would be most appreciated.  The archives didn't lend much help.

ciao!






**
The preceding message and any attachments may contain confidential 
information protected by the attorney-client or other privilege.  If you 
believe that it has been sent to you in error, please reply to the sender 
that you received the message in error and then delete it.  Nothing in 
this email message, including the typed name of the sender and/or this 
signature block, is intended to constitute an electronic signature unless 
a specific statement to the contrary is included in the message.

**

 


Dale

You should be able to do this with a simple sql script file. Take the 
commands below, change the declarations of the tables to match your four 
tables, and the names of the database files to match your files, then 
save the result as "merge.sql". These sql commands attach to each of 
your daily databases, drop and recreate each of your four tables, then 
populate the tables in the master database with all the records from the 
daily databases.


   attach database 'day1.db' as day1;
   attach database 'day2.db' as day2;
   attach database 'day3.db' as day3;
   attach database 'day4.db' as day4;
   attach database 'day5.db' as day5;
   attach database 'day6.db' as day6;
   attach database 'day7.db' as day7;
   attach database 'day8.db' as day8;
   attach database 'day9.db' as day9;
   attach database 'day10.db' as day10;

   drop table if exists tab1;
   drop table if exists tab2;
   drop table if exists tab3;
   drop table if exists tab4;

   create table tab1(...);
   create table tab2(...);
   create table tab3(...);
   create table tab4(...);

   insert into tab1
   select * from day1.tab1
   union select * from day2.tab1
   union select * from day3.tab1
   union select * from day4.tab1
   union select * from day5.tab1
   union select * from day6.tab1
   union select * from day7.tab1
   union select * from day8.tab1
   union select * from day9.tab1
   union select * from day10.tab1;

   insert into tab2
   select * from day1.tab2
   union select * from day2.tab2
   union select * from day3.tab2
   union select * from day4.tab2
   union select * from day5.tab2
   union select * from day6.tab2
   union select * from day7.tab2
   union select * from day8.tab2
   union select * from day9.tab2
   union select * from day10.tab2;

   insert into tab3
   select * from day1.tab3
   union select * from day2.tab3
   union select * from day3.tab3
   union select * from day4.tab3
   union select * from day5.tab3
   union select * from day6.tab3
   union select * from day7.tab3
   union select * from day8.tab3
   union select * from day9.tab3
   union select * from day10.tab3;

   insert into tab4
   select * from day1.tab4
   union select * from day2.tab4
   union select * from day3.tab4
   union select * from day4.tab4
   union select * from day5.tab4
   union select * from day6.tab4
   union select * from day7.tab4
   union select * from day8.tab4
   union select * from day9.tab4
   union select * from day10.tab4;

Now at the command prompt you can open your master database and execute 
the merge with the following command.


   sqlite master.db '.read merge.sql'

HTH
Dennis Cote

Re: [sqlite] using Expressions/CASE/Lookups!!?

[Roger]

Thanks a lot Jay, that seems to work.

On Tue, 2006-03-07 at 09:14 -0600, Jay Sprenkle wrote:

> On 3/7/06, Roger <[EMAIL PROTECTED]> wrote:
> 
> > What i would like to do is to write for my reports with a column called
> > "Advertising Type" which when either one or more of the above fields has
> > 1, it should show for instance if :
> >
> > Classified_Advertising = 1
> > Display_Advertising = 1
> > Sponsored_Editorial = 0
> > Recruitment_Advertising = 0
> >
> > The resultant query should return a column Advertising Types with the
> > following result:
> >
> > Advertising Types
> > Classified Adverting, Display Advertising
> >
> > How can i achieve this best, using especially the CASE; or whatever tool
> > is available.
> 
> Use select case to generate a string from each column, then
> concatenate the strings.
> 
> http://www.craigsmullins.com/ssu_0899.htm

Re: [sqlite] using Expressions/CASE/Lookups!!?

[Jay Sprenkle]

On 3/7/06, Roger <[EMAIL PROTECTED]> wrote:

> What i would like to do is to write for my reports with a column called
> "Advertising Type" which when either one or more of the above fields has
> 1, it should show for instance if :
>
> Classified_Advertising = 1
> Display_Advertising = 1
> Sponsored_Editorial = 0
> Recruitment_Advertising = 0
>
> The resultant query should return a column Advertising Types with the
> following result:
>
> Advertising Types
> Classified Adverting, Display Advertising
>
> How can i achieve this best, using especially the CASE; or whatever tool
> is available.

Use select case to generate a string from each column, then
concatenate the strings.

http://www.craigsmullins.com/ssu_0899.htm

Re: [sqlite] Busy management

[drh]

"Ludovic Ferrandis" <[EMAIL PROTECTED]> wrote:
> I was wondering if there was limitations for one of the 2 methods. If they
> are identicals, I know which one I will use.
> 

They are essentially different interfaces to the same underlying
mechanism.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] cannot commit transaction - SQL statements in progress

[Markus Kolb]

Markus Kolb wrote:
> Hello,
> 
> I open one connection to a sqlitedb and do a few successful autocommited
> sqlite3_exec() calls.
> Then I want to use the db handle to call another sqlite3_exec() with SQL
>   code for a transaction.
> Before I close the connection I call again sqlite3_exec() with SQL
> COMMIT to end the transaction.
> This produces the sqlite error: cannot commit transaction - SQL
> statements in progress
> 
> I don't use statements and/or threading in my code but sqlite is
> compiled with threading enabled.
> 
> I don't understand why there are statements in progress?

Thanks for your "very numerous" help ;)
I know it in the meantime. The transaction stuff got called by accident
in a callback function. The callbacks are threaded by sqlite?!

Bye

Re: [sqlite] Busy management

[Ludovic Ferrandis]

I was wondering if there was limitations for one of the 2 methods. If they
are identicals, I know which one I will use.

Thanks a lot.

On 3/7/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
> "Ludovic Ferrandis" <[EMAIL PROTECTED]> wrote:
> > Thanks for your answers,
> >
> > but that still don't explain me which method is the best to manage Busy
> > statement, to do a loop or to use a busy callback. And as I'm very
> curious,
> > especially why one method is better than the other. :)
> >
>
> That is a value judgement that only you - the application designer -
> can make.
> --
> D. Richard Hipp   <[EMAIL PROTECTED]>
>
>

Re: [sqlite] Busy management

[drh]

"Ludovic Ferrandis" <[EMAIL PROTECTED]> wrote:
> Thanks for your answers,
> 
> but that still don't explain me which method is the best to manage Busy
> statement, to do a loop or to use a busy callback. And as I'm very curious,
> especially why one method is better than the other. :)
> 

That is a value judgement that only you - the application designer -
can make.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

[sqlite] Re: sqlite3.3.4 '@' parameter

[drh]

"angshuman" <[EMAIL PROTECTED]> wrote:
> Hi,
> I have downloaded the binaries and the command line program for sqlite
> 3.3.4.
> I fired the following query
> 
> "SELECT @BrandID=Brandid from M_SetOfCodes WHERE SetOFCodesID = 74;
> SELECT BrandName FROM M_Brands WHERE BrandID = @BrandID; "
> 
> This SQL SERVER style query did not throw any error nor it showed any
> result. 
> 
> can someone help in this regard.
> Thx in advance
> 

Re: [sqlite] Busy management

[Ludovic Ferrandis]

Thanks for your answers,

but that still don't explain me which method is the best to manage Busy
statement, to do a loop or to use a busy callback. And as I'm very curious,
especially why one method is better than the other. :)

Thanks,

Ludovic

On 3/5/06, John Stanton <[EMAIL PROTECTED]> wrote:
>
> I have a several of suggestions for that busy logic.
> 1.  Perform a "yield()" or Win sleep(0) to give up the current
>  timeslice and thus have a busy wait with as little system impact
>  as possible but fastest turnaround.  An upper limit of 100 probes
>  before failing as deadlocked seems to be reasonable with that
>  approach.
> 2.  Launch a "reset" call to restart the sqlite3_step on finding
>  a BUSY to make sure that deadlock situations are broken by
>  brute force, with all but one process yielding control.
> 3.  If the contention comes from competing threads within a process
>  or competing processes in an application, define a semaphore or
>  similar appropriate lock to synchronize each database and avoid the
>  possibility of ever having a BUSY.  In windows it looks like this
>   sqlite3_prep
>   sqlite3_bind
>   WaitOnSingleEvent
> sqlite3_step
> 
>   SetEvent
>  This is a lower overhead, higher performance and generally more
>  elegant approach than polling.  It does however assume that there
>  are co-operating threads and processes.
> JS
>
> Jay Sprenkle wrote:
> > On 3/3/06, Ludovic Ferrandis <[EMAIL PROTECTED]> wrote:
> >
> >>I want to manage the SQLITE_BUSY error like this: If it fails, sleep X
> >>ms then try the command Y times. I found 2 ways to do it:
> >
> >
> > I do it using sqlite3_step(); Using bound variables and step eliminates
> the need
> > for escaping string data and prevents SQL injection attacks. I retry
> > the statement
> > up to 10 times in case another process has locked the database.
> > Psuedo code looks like this:
> >
> > // open database
> > dbOpen();
> >
> > // Get configuration information for this website instance
> > string sql = "SELECT blah"
> >  " FROM Setup"
> >  ;
> >
> > // prepare statement instead of building it to avoid sql injection
> attacks
> > if ( ! dbPrep( sql ) )
> >   throw ConException( string("Cannot prepare sql: ") + sql +
> > string(", ") +  + sqlite3_errmsg(db) );
> >
> > bool loop = true;
> > for ( int i = 0; ( i < 10 ) && ( loop ); i++ )
> >   switch ( dbStep() )
> > {
> >   // if database busy wait for a short time
> >   // to see if it becomes available
> >   case SQLITE_BUSY:
> >   case SQLITE_LOCKED:
> > break;
> >   case SQLITE_ROW:
> > // get results ( 0 based index!!! )
> > blah  = dbColumn( 0 );
> > break;
> >   case SQLITE_DONE:
> > if ( CookieUser.empty() )
> >   throw ConException( string("Invalid configuration") );
> > loop = false;
> > break;
> >   default:
> > throw ConException( string("Cannot execute sql: ") + sql );
> > break;
> > }
> >
> > // clean up when finished
> > dbFinalize();
> > dbClose();
>
>

[sqlite] using Expressions/CASE/Lookups!!?

[Roger]

Can someone please help

I have the following fields in my database

Classified_Advertising
Display_Advertising
Sponsored_Editorial
Recruitment_Advertising

Each of this fields can store either 0 or 1 with one indicating that the
mentioned advertising is available, i use this in my web application as
checkboxes to show that the type of said advertising is available.

What i would like to do is to write for my reports with a column called
"Advertising Type" which when either one or more of the above fields has
1, it should show for instance if :

Classified_Advertising = 1
Display_Advertising = 1
Sponsored_Editorial = 0
Recruitment_Advertising = 0

The resultant query should return a column Advertising Types with the
following result:

Advertising Types
Classified Adverting, Display Advertising

How can i achieve this best, using especially the CASE; or whatever tool
is available.

Re: [sqlite] recompiled source code, make test failed

[Roger]

an someone please help!

I have the following fields in my database

Recruitment_Advertising
Advertorial
Sponsored_Editorial

Re: [sqlite] Database locks up on AMD64/PHP

[Firman Wandayandi]

On 3/7/06, Stefan de Konink <[EMAIL PROTECTED]> wrote:
> On Tue, 7 Mar 2006, Firman Wandayandi wrote:
>
> > On 3/7/06, Stefan de Konink <[EMAIL PROTECTED]> wrote:
> > > Hello,
> > >
> > >
> > > We are running SQLite 2.8.16 together with PHP5 on Linux. And it runs
> > > our TV Newspaper System. It was running on a PIII-1GHz but due some
> > > shifts for rendering performance increase (Inkscape SVG -> PNG) we moved
> > > the complete application to an AMD64.
> > >
> > > The last weeks it seems the database got locked very often, and made the
> > > apache/php system 'hang', not on query but on insert. Restarting Apache
> > > solves the problem making me wonder what I can do to visualize the
> > > problem or even fix it.
> > >
> > > As temporary fix I already tried to decrease the database size (now at
> > > 7MB). But this only increase speed (for now).
> > >
> >
> > What's this meant?
>
> What part isn't clear?
>

Nothing, it's just like annoucement. Well, sorry I didn't read
carefully the second paragraph. I have no idea it could be happened,
seems something has vacummed your databases, well not sure.

--
Firman Wandayandi, Tarzilla Foundation.
Never Dreamt Before: http://firman.dotgeek.org/
Wishlist: http://www.amazon.com/gp/registry/1AAN8NZBHW2W9

Re: [sqlite] Database locks up on AMD64/PHP

[Stefan de Konink]

On Tue, 7 Mar 2006, Firman Wandayandi wrote:

> On 3/7/06, Stefan de Konink <[EMAIL PROTECTED]> wrote:
> > Hello,
> >
> >
> > We are running SQLite 2.8.16 together with PHP5 on Linux. And it runs
> > our TV Newspaper System. It was running on a PIII-1GHz but due some
> > shifts for rendering performance increase (Inkscape SVG -> PNG) we moved
> > the complete application to an AMD64.
> >
> > The last weeks it seems the database got locked very often, and made the
> > apache/php system 'hang', not on query but on insert. Restarting Apache
> > solves the problem making me wonder what I can do to visualize the
> > problem or even fix it.
> >
> > As temporary fix I already tried to decrease the database size (now at
> > 7MB). But this only increase speed (for now).
> >
>
> What's this meant?

What part isn't clear?

Stefan