Re: [sqlite] Is there always an index on ROWID?

[Jos van den Oever]

2008/2/21, Neville Franks <[EMAIL PROTECTED]>:
>  If I create a table with a Primary key on a TEXT clm, will there still
>  be an Index on the in-built ROWID clm. The reason for asking this is
>  that I need fast (indexed) lookup to rows by both ROWID and my TEXT
>  clm.

According to the documentation for 'CREATE TABLE' [1] you can even use
a custom name for ROWID by specifying:
 CREATE TABLE X(ID INTEGER PRIMARY KEY NOT NULL, CONTENT TEXT);

[1] http://www.sqlite.org/lang_createtable.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to Optimize query

[Manoj Marathayil]

Hi,
I am working with a project which uses SQLite as the database to store
collected data. I am
issuing the following query to retrieve data from the table. I noticed this
query is taking more
time as the number of records in the table increases. Is there a better way
to write this query?
any help is appreciated.

Query:
SELECT SUM(metric_diff) FROM snapshot_master WHERE snapshot_master.metric_id
= ? AND
snapshot_master.timestamp_id in (select timestamp_id from timestamp_master
where
server_timestamp >= ? AND server_timestamp <= ? and strftime(?,
server_timestamp, 'unixepoch') =
strftime(?, ?, 'unixepoch'));

Format string may vary depending on context.

Table schema:
CREATE TABLE [snapshot_master] (
[timestamp_id] INTEGER NOT NULL,
[metric_id] INTEGER NOT NULL,
[metric_now] TEXT,
[metric_diff] TEXT,
PRIMARY KEY (metric_id, timestamp_id))


CREATE TABLE [timestamp_master] (
[timestamp_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
[server_timestamp] INTEGER,
[server_start_time] INTEGER,
[server_uptime] INTEGER,
[server_uptime_diff] INTEGER,
[server_is_connected] INTEGER)

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

[sqlite] Checking that a row exists?

[Gilles Ganault]

Hello

Is this the standard way to check that a row exists in PHP/SQLite, or
is there a better syntax?


$query = "SELECT count(*) FROM contacts WHERE contacts_phones_tel GLOB
'%s*';
$query = sprintf($query,$row['calls_phones_tel']);
$query = $dbh->query($sql)->fetch();
if(!$query[0]) {
//Not found in table


Thank you.

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

Re: [sqlite] Is there always an index on ROWID?

[Neville Franks]

Hi Jeff,
Thanks, I thought that would be the case, but didn't want to start
creating tables etc. and find I was wrong.

Sorry about the spam-challenge you received. I didn't have it setup
right for this list.


Thursday, February 21, 2008, 11:30:51 AM, you wrote:

JH> The table data is stored in a b-tree keyed off of the rowid, so
JH> lookups based on rowid should always be fast.

JH> -Jeff

JH> On Wed, Feb 20, 2008 at 6:27 PM, Neville Franks <[EMAIL PROTECTED]> wrote:
>> Hi,
>>  If I create a table with a Primary key on a TEXT clm, will there still
>>  be an Index on the in-built ROWID clm. The reason for asking this is
>>  that I need fast (indexed) lookup to rows by both ROWID and my TEXT
>>  clm.
>>
>>  I am just starting out with SQLite and have almost finished Michael
>>  Owens book, but have been unable to find an answer to this.
>>
>>  Thanks.
>>
>>  --
>>  Best regards,
>>   Neville Franks, Author of Surfulater - Your off-line Digital Reference 
>> Library
>>   Soft As It Gets Pty Ltd,  http://www.surfulater.com - Download your copy 
>> now.
>>   Victoria, Australia   Blog: http://blog.surfulater.com
>>
>>
>>  ___
>>  sqlite-users mailing list
>>  sqlite-users@sqlite.org
>>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>


-- 
Best regards,
  Neville Franks, Author of Surfulater - Your off-line Digital Reference Library
  Soft As It Gets Pty Ltd,  http://www.surfulater.com - Download your copy now.
  Victoria, Australia   Blog: http://blog.surfulater.com 
 

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

Re: [sqlite] Is there always an index on ROWID?

[Jeff Hamilton]

The table data is stored in a b-tree keyed off of the rowid, so
lookups based on rowid should always be fast.

-Jeff

On Wed, Feb 20, 2008 at 6:27 PM, Neville Franks <[EMAIL PROTECTED]> wrote:
> Hi,
>  If I create a table with a Primary key on a TEXT clm, will there still
>  be an Index on the in-built ROWID clm. The reason for asking this is
>  that I need fast (indexed) lookup to rows by both ROWID and my TEXT
>  clm.
>
>  I am just starting out with SQLite and have almost finished Michael
>  Owens book, but have been unable to find an answer to this.
>
>  Thanks.
>
>  --
>  Best regards,
>   Neville Franks, Author of Surfulater - Your off-line Digital Reference 
> Library
>   Soft As It Gets Pty Ltd,  http://www.surfulater.com - Download your copy 
> now.
>   Victoria, Australia   Blog: http://blog.surfulater.com
>
>
>  ___
>  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] Is there always an index on ROWID?

[Neville Franks]

Hi,
If I create a table with a Primary key on a TEXT clm, will there still
be an Index on the in-built ROWID clm. The reason for asking this is
that I need fast (indexed) lookup to rows by both ROWID and my TEXT
clm.
  
I am just starting out with SQLite and have almost finished Michael
Owens book, but have been unable to find an answer to this.

Thanks.

-- 
Best regards,
  Neville Franks, Author of Surfulater - Your off-line Digital Reference Library
  Soft As It Gets Pty Ltd,  http://www.surfulater.com - Download your copy now.
  Victoria, Australia   Blog: http://blog.surfulater.com 
 

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

Re: [sqlite] Most widely deployed?

[Bogdan Eugen Daja]

The most important: free, slim, fast, easy to install, works on your 
pocket :)  this is SQLite
What  I looking for is oracle for ppc but i guess wont get enounght 
space to install.

Toby Roworth wrote:
> Bryan Oakley wrote:
>   
>> On Feb 20, 2008 7:41 AM, Toby Roworth <[EMAIL PROTECTED]> wrote:
>>   
>> 
>>> Oracle has deployment contracts in the govornments of X countries
>>> worldwide, each having Y department, which have Z number of databases
>>> each...
>>> 
>>>   
>> I would think the number of MacOSX installations would outnumber the
>> Oracle installations of the world, wouldn't it? I'm just guessing
>> here, but with the cost of Oracle being so high, and there being
>> millions of Macintoshes out in the wild...
>>
>> It wouldn't surpise me to learn sqlite was on the ipod or iphone too,
>> though I haven't heard definitely one way or the other.
>>
>> And of course there is Solaris. And symbian cell phones.
>>
>> And if you count a large percentage of windows systems in the wild
>> that use McAfee virus protection which reportedly uses sqlite and add
>> it all up, you're approaching a *very* large number of deployments.
>>
>> Do you really think Oracle can match all that?
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>   
>> 
> I'm not disputing whether it is the mist widely used SQL database - I 
> was merely disputing the fact that there are only 100 million other 
> databases in use worldwide. I completely agree with you that there is a 
> very large number of deployments, and that oracle probably has nowhere 
> near as many.
>
> I just wanted to point out this flaw in the logic, so that it could 
> possibly be rectified before someone in a better poition to criticise 
> the statement does so.
>
> Hope this makes sense
>
> Toby
> ___
> 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] Limit number of records to search in a table in SQlite

[P Kishor]

On 2/20/08, Kalyani Phadke <[EMAIL PROTECTED]> wrote:
>
>  Suppose I have 1280009 rows in table.
>  CREATE TABLE TableA
>  (
> ID  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
>column1 VARCHAR (50) NOT NULL,
>column2 VARCHAR (50)  NOT NULL,
>column3 TIMESTAMP NOT NULL DEFAULT  (CURRENT_TIMESTAMP),
>column4  VARCHAR (128) NULL,
>column5 VARCHAR (255)NULL,
>column6 VARCHAR ( 128 )   NULL,
>column7  TEXT NULL,
>column8  TEXT NULL
>  )
>
>  I have select query which looks like
>  select ID from TableA where column2 like '%test%'  or column4like
>  '%test%' or column5 like '%test%' or column6 like '%test%' or column7
>  like '%test%'  or column8 like '%test%' order by column3 desc limit
>  10;
>
>  So limit 10 will search only through 10 records in table or
>  gives (10)number of rows returned in the result.According to
>  documenation The LIMIT clause places an upper bound on the number of
>  rows returned in the result.
>
>  Instead of searching the entire table at once I would like to search
>  10 rows from table ,so that my search will be little faster.
>

right... but *which* 100,000 rows do you want to search out of the
1,280,009 you have? How can the db guess that for you unless you tell
it?

How about,

select ID
from TableA
where ID <= 10 AND
  column2 like '%test%'  or
  column4 like '%test%' or
  column5 like '%test%' or
  column6 like '%test%' or
  column7 like '%test%'  or
  column8 like '%test%'
order by column3 desc;

But, of course, then only the first 100k rows are searched. You could
repeat this in a loop in your application.


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit number of records to search in a table in SQlite

[Dennis Cote]

Kalyani Phadke wrote:
> 
> Any suggestions?
> 

Please stop hijacking message threads. 
http://en.wikipedia.org/wiki/Thread_hijacking

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

Re: [sqlite] Access to SQLite via two languages in one process

[Nicolas Williams]

On Wed, Feb 20, 2008 at 04:50:47PM +, [EMAIL PROTECTED] wrote:
> Private messages on this subject suggest that I have misunderstood
> the question.  What exactly do you mean by "instances" of SQLite?

Two instances of libsqlite3.a linked into different parts of the
process.

> If you create two or more connections (two or more calls to
> sqlite3_open()) within the same process, they all share the same
> st_dev hash table and hence they all know about each others locks.

Understood.  That's not the issue the poster had.

The poster has two Apache modules, one PHP, the other a home-grown
module, both accessing the same SQLite DB.

If the poster follows your advice and statically links libsqlite3 in
the relevant modules then they will have more than one instance of the
SQLite3 object code in the same process.

OTOH, I realize now that the linker should fail or warn about building
dynamic shared objects that are statically linked with libsqlite3.  So
in the case of the poster I think it isn't possible to have two
instances of SQLite in the same process.

> If you create connections to the same databases from different
> processes, then they don't share the same st_dev hash, but posix
> advisory locking works in that case.

Correct.

> In either case you are covered.
> 
> Even if you are accessing SQLite from two different languages,
> you should only be linked against a single instance of the SQLite
> library (otherwise you would get linkage errors) meaning that you
> only have a single copy of the hash table.  If you know of a way
> to link two copies of the same library into the same process at
> the same time, such that each copy of the library has its own
> set of static variables, then the conditions above break down
> and you cannot use SQLite safely.

If one has an application (an executable, not a library) that is
statically linked with SQLite *and* it uses an API with dynamic loading
of plug-ins (e.g., GSS-API, PAM, SASL, ...) *and* a plug-in for that API
uses SQLite, then you can have two instances in the same process.

But presumably those instances would be used to access different
databases.  The app, after all, wouldn't have knowledge of the internals
of any of those plug-ins.  I.e., should be no problem.

>But on the other hand, I don't
> know of much software that is likely to survive such a scenario.
> And I am unclear how such a scenario is even possible.

Agreed.

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

Re: [sqlite] Access to SQLite via two languages in one process

[drh]

Nicolas Williams <[EMAIL PROTECTED]> wrote:
> On Wed, Feb 20, 2008 at 04:23:29PM +, [EMAIL PROTECTED] wrote:
> > Nicolas Williams <[EMAIL PROTECTED]> wrote:
> > > does SQLite know about POSIX file locks on the same files from
> > > other instances of itself?
> > 
> > Yes.  That's the whole point of POSIX advisory locking.
> 
> src/os_unix.c says:
> 
> ** Here is the dirt on POSIX advisory locks:  ANSI STD 1003.1 (1996)
> ** section 6.5.2.2 lines 483 through 490 specify that when a process
> ** sets or clears a lock, that operation overrides any prior locks set
> ** by the same process.  It does not explicitly say so, but this implies
> ** that it overrides locks set by the same process using a different
> ** file descriptor.  Consider this test case:
> 
> ** To work around the problem, SQLite has to manage file locks internally
> ** on its own.  Whenever a new database is opened, we have to find the
> ** specific inode of the database file (the inode is determined by the
> ** st_dev and st_ino fields of the stat structure that fstat() fills in)
> ** and check for locks already existing on that inode.  When locks are
> ** created or removed, we have to look at our own internal record of the
> ** locks to see if another thread has previously set a lock on that same
> ** inode.
> 
> 
> Now, SQLite maintains an internal hash table indexed by st_dev and
> st_ino, but if there are two _distinct_ copies of SQLite in the same
> process, how can those two instances of SQLite share that hash table?
> 
> The hash table is a static, after all, so each instance of SQLite will
> have its own instance of that hash table, which will mean they won't
> know about each other's locks.
> 

Private messages on this subject suggest that I have misunderstood
the question.  What exactly do you mean by "instances" of SQLite?

If you create two or more connections (two or more calls to
sqlite3_open()) within the same process, they all share the same
st_dev hash table and hence they all know about each others locks.

If you create connections to the same databases from different
processes, then they don't share the same st_dev hash, but posix
advisory locking works in that case.

In either case you are covered.

Even if you are accessing SQLite from two different languages,
you should only be linked against a single instance of the SQLite
library (otherwise you would get linkage errors) meaning that you
only have a single copy of the hash table.  If you know of a way
to link two copies of the same library into the same process at
the same time, such that each copy of the library has its own
set of static variables, then the conditions above break down
and you cannot use SQLite safely.  But on the other hand, I don't
know of much software that is likely to survive such a scenario.
And I am unclear how such a scenario is even possible.
--
D. Richard Hipp <[EMAIL PROTECTED]>

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

Re: [sqlite] bit operations in select statements

[Ken]

 select key where value & '01011';

  Try: 
  select key where (value&176)  

you need to convert the bit string to an integer number.
Also be aware that sqlite interperts numbers as signed 64 bit integer and this 
can cause some issues with your bit mask number. 


HTH.




Jos van den Oever <[EMAIL PROTECTED]> wrote: Hi all,

Is it possible to use logic operations on fields, in particular on
integers and on fixed sized blobs (256 bits/32 bytes).
I'd like to do queries like this:
 select key where number_of_bits_set(value) = 10;
or
 select key where value & '01011';

What would be a good method of implementing this?

Best regards,
Jos
___
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] Access to SQLite via two languages in one process

[Nicolas Williams]

On Wed, Feb 20, 2008 at 04:23:29PM +, [EMAIL PROTECTED] wrote:
> Nicolas Williams <[EMAIL PROTECTED]> wrote:
> > does SQLite know about POSIX file locks on the same files from
> > other instances of itself?
> 
> Yes.  That's the whole point of POSIX advisory locking.

src/os_unix.c says:

** Here is the dirt on POSIX advisory locks:  ANSI STD 1003.1 (1996)
** section 6.5.2.2 lines 483 through 490 specify that when a process
** sets or clears a lock, that operation overrides any prior locks set
** by the same process.  It does not explicitly say so, but this implies
** that it overrides locks set by the same process using a different
** file descriptor.  Consider this test case:
...
** To work around the problem, SQLite has to manage file locks internally
** on its own.  Whenever a new database is opened, we have to find the
** specific inode of the database file (the inode is determined by the
** st_dev and st_ino fields of the stat structure that fstat() fills in)
** and check for locks already existing on that inode.  When locks are
** created or removed, we have to look at our own internal record of the
** locks to see if another thread has previously set a lock on that same
** inode.
...

Now, SQLite maintains an internal hash table indexed by st_dev and
st_ino, but if there are two _distinct_ copies of SQLite in the same
process, how can those two instances of SQLite share that hash table?

The hash table is a static, after all, so each instance of SQLite will
have its own instance of that hash table, which will mean they won't
know about each other's locks.

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

Re: [sqlite] Please test lastest CVS using WinCE

[Fin Springs]

> For that matter, is there anybody out there who would like
> to become the official wince maintainer for SQLite?  If you
> are able to compile, test, and debug SQLite for wince and
I could do that if you'd like Richard.

We develop our own Windows CE 5.0 based device so I have access to
Platform Builder at the platform level and all the Visual Studio 200x
stuff at the application level.

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

Re: [sqlite] Access to SQLite via two languages in one process

[drh]

Nicolas Williams <[EMAIL PROTECTED]> wrote:
> does SQLite know about POSIX file locks on the same files from
> other instances of itself?

Yes.  That's the whole point of POSIX advisory locking.

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

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

Re: [sqlite] Access to SQLite via two languages in one process

[Nicolas Williams]

On Wed, Feb 20, 2008 at 10:58:05AM -0500, Igor Tandetnik wrote:
> Sam Carleton <[EMAIL PROTECTED]> wrote:
> > I am working on a application that is currently access the SQLite
> > database via PHP that is running under Apache as a module.  The
> > application also has a custom written Apache module written in C.  Can
> > I access the the SQLite database from both PHP and the Apache module?
> 
> Yes, you can open multiple connections to the same database. Be prepared 
> to handle concurrency-related errors.

Even if the two lnaguage bindings are statically linked with libsqlite3?
I.e., does SQLite know about POSIX file locks on the same files from
other instances of itself?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Access to SQLite via two languages in one process

[Igor Tandetnik]

Sam Carleton <[EMAIL PROTECTED]> wrote:
> I am working on a application that is currently access the SQLite
> database via PHP that is running under Apache as a module.  The
> application also has a custom written Apache module written in C.  Can
> I access the the SQLite database from both PHP and the Apache module?

Yes, you can open multiple connections to the same database. Be prepared 
to handle concurrency-related errors.

Igor Tandetnik 



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

Re: [sqlite] Most widely deployed?

[Toby Roworth]

Bryan Oakley wrote:
> On Feb 20, 2008 7:41 AM, Toby Roworth <[EMAIL PROTECTED]> wrote:
>   
>> Oracle has deployment contracts in the govornments of X countries
>> worldwide, each having Y department, which have Z number of databases
>> each...
>> 
>
> I would think the number of MacOSX installations would outnumber the
> Oracle installations of the world, wouldn't it? I'm just guessing
> here, but with the cost of Oracle being so high, and there being
> millions of Macintoshes out in the wild...
>
> It wouldn't surpise me to learn sqlite was on the ipod or iphone too,
> though I haven't heard definitely one way or the other.
>
> And of course there is Solaris. And symbian cell phones.
>
> And if you count a large percentage of windows systems in the wild
> that use McAfee virus protection which reportedly uses sqlite and add
> it all up, you're approaching a *very* large number of deployments.
>
> Do you really think Oracle can match all that?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
I'm not disputing whether it is the mist widely used SQL database - I 
was merely disputing the fact that there are only 100 million other 
databases in use worldwide. I completely agree with you that there is a 
very large number of deployments, and that oracle probably has nowhere 
near as many.

I just wanted to point out this flaw in the logic, so that it could 
possibly be rectified before someone in a better poition to criticise 
the statement does so.

Hope this makes sense

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

Re: [sqlite] Optimization Question - multithread prepare ?

[John Stanton]

Mark Gilbert wrote:
>>  > - We don't currently prepare SQL statements in advance, would this
>>>  technique benefit from somehow preparing statements *before* that
>>>  thread gets the lock on the database ?  Can we have multiple threads
>>>  using the SAME database connection preparing SQL Queries at the same
>>>  time (so long as only one is RUNNING the query at once ?).
>> With the latest sqlite, I believe you can even have multiple threads 
>> access the same database connection. In this case, access will still 
>> be serialized.
>> However, you can also have each thread create it's own database 
>> connection, then access the database concurrently (well, with 
>> limitations - there can always only be one writer thread). In this
>> case you can actually execute several statements in parallel on
>> different threads (i.e. different database connections).
> 
> In fact much of our access is read-only, so being able to work 
> multiple concurrent connections sounds perfect.
> 
> Could you confirm that we can open and execute additional read only 
> connections to the database whilst we already have a Transaction open 
> for our write thread ?
> 
> - We BEGIN the transaction on the write thread, then leave that in 
> place and only COMIT the transactions once per  minute to coincide 
> with our flush.Can we open and close extra read connections to 
> the database on other threads whilst the write transaction is still 
> active (but idle) ?
> 
> Thanks for your insight
> 
> Cheers
> 
> Mark
>
First a performance suggestion.  You may be able to partition your data 
and users into multiple databases.  That would add concurrency.

Sqlite transactions have an intermediate locking mode which maintains 
read access right up to the point of the journal being committed.  That 
can enhance your concurrency.

> ___
> 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] Most widely deployed?

[Bryan Oakley]

On Feb 20, 2008 7:41 AM, Toby Roworth <[EMAIL PROTECTED]> wrote:
> Oracle has deployment contracts in the govornments of X countries
> worldwide, each having Y department, which have Z number of databases
> each...

I would think the number of MacOSX installations would outnumber the
Oracle installations of the world, wouldn't it? I'm just guessing
here, but with the cost of Oracle being so high, and there being
millions of Macintoshes out in the wild...

It wouldn't surpise me to learn sqlite was on the ipod or iphone too,
though I haven't heard definitely one way or the other.

And of course there is Solaris. And symbian cell phones.

And if you count a large percentage of windows systems in the wild
that use McAfee virus protection which reportedly uses sqlite and add
it all up, you're approaching a *very* large number of deployments.

Do you really think Oracle can match all that?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimization Question - multithread prepare ?

[Mark Gilbert]

>
>  > - We don't currently prepare SQL statements in advance, would this
>>  technique benefit from somehow preparing statements *before* that
>>  thread gets the lock on the database ?  Can we have multiple threads
>>  using the SAME database connection preparing SQL Queries at the same
>>  time (so long as only one is RUNNING the query at once ?).
>
>With the latest sqlite, I believe you can even have multiple threads 
>access the same database connection. In this case, access will still 
>be serialized.
>However, you can also have each thread create it's own database 
>connection, then access the database concurrently (well, with 
>limitations - there can always only be one writer thread). In this
>case you can actually execute several statements in parallel on
>different threads (i.e. different database connections).

In fact much of our access is read-only, so being able to work 
multiple concurrent connections sounds perfect.

Could you confirm that we can open and execute additional read only 
connections to the database whilst we already have a Transaction open 
for our write thread ?

- We BEGIN the transaction on the write thread, then leave that in 
place and only COMIT the transactions once per  minute to coincide 
with our flush.Can we open and close extra read connections to 
the database on other threads whilst the write transaction is still 
active (but idle) ?

Thanks for your insight

Cheers

Mark

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

[sqlite] Access to SQLite via two languages in one process

[Sam Carleton]

I am working on a application that is currently access the SQLite
database via PHP that is running under Apache as a module.  The
application also has a custom written Apache module written in C.  Can
I access the the SQLite database from both PHP and the Apache module?

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

Re: [sqlite] Most widely deployed?

[Toby Roworth]

Geoff Lane wrote:
> On Wed, Feb 20, 2008 at 01:41:50PM +, Toby Roworth wrote:
>   
>> I'm not trying to be picky here, or start telling you that you're wrong, 
>> but it could be worth changing - if an oracle developer (etc) were to 
>> look at the page, the could potentially have a field day when they can 
>> point out something like:
>> 
>
> It is just a list of estimates.
>   
Indeed, it does state that clearly. I just feel it could potentially 
damage the image of SQlite to have a fact stated on the front page that, 
when looked into, is based on what could be considered flawed logic, as 
it seems an unfair comparison. I've seen people get very aggressive on 
these matters (Adobe on the Prism page on mozilla labs, for example).
> The list does omit about 10 million Solaris 10 installations.  Solaris 10
> uses sqlite to manage parts of the package and patch installation process.
>
> I use sqlite in shell scripts (I guess many others also do so) and so sqlite
> will rapidly get installed on any new computer I look after, if it is not
> already there.  
> ___
> 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] Most widely deployed?

[Geoff Lane]

On Wed, Feb 20, 2008 at 01:41:50PM +, Toby Roworth wrote:
> I'm not trying to be picky here, or start telling you that you're wrong, 
> but it could be worth changing - if an oracle developer (etc) were to 
> look at the page, the could potentially have a field day when they can 
> point out something like:

It is just a list of estimates.

The list does omit about 10 million Solaris 10 installations.  Solaris 10
uses sqlite to manage parts of the package and patch installation process.

I use sqlite in shell scripts (I guess many others also do so) and so sqlite
will rapidly get installed on any new computer I look after, if it is not
already there.  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] bit operations in select statements

[Jens Miltner]

Am 20.02.2008 um 11:54 schrieb Jos van den Oever:

> Hi all,
>
> Is it possible to use logic operations on fields, in particular on
> integers and on fixed sized blobs (256 bits/32 bytes).
> I'd like to do queries like this:
> select key where number_of_bits_set(value) = 10;

I believe for this kind of query you'll have to implement a user  
function - see  for details.

>
> or
> select key where value & '01011';

You can use bit-wise AND and OR operations on integers. Not sure about  
BLOBs, though... If it doesn't work out of the box, implement a user  
function.

HTH,


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

[sqlite] Most widely deployed?

[Toby Roworth]

I was just reading the page about SQlite being the most widely deployed 
SQL database engine (http://sqlite.org/mostdeployed.html), and noticed 
that it does not seem to take into account non-website non-sqlite 
database engines in the arbitrary 100million other database engines.

I'm not trying to be picky here, or start telling you that you're wrong, 
but it could be worth changing - if an oracle developer (etc) were to 
look at the page, the could potentially have a field day when they can 
point out something like:

Oracle has deployment contracts in the govornments of X countries 
worldwide, each having Y department, which have Z number of databases 
each...

Like I said, *I* am not trying to dispute this claim, but merely point 
out a place where others may, and thus should maybe be looked at.

I'm not sure if this was the right place to post this, but it would be 
interesting to hear people's thoughts on the matter.

Toby

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

Re: [sqlite] Optimization Question - multithread prepare ?

[Jens Miltner]

Am 20.02.2008 um 14:03 schrieb Mark Gilbert:

> Folks.
>
> Our application uses SQlite on Mac OSX.  It is a central data hub for
> a larger infrastructure and manages data coming in from some clients,
> and requests for data from other clients, using our own XML based
> protocols via TCPIP.
>
> Its somewhat like a web server with a backend system supplying data
> presented to web clients.
>
> All client access requests in and out of the system are made on
> individual threads.  Our approach for SQLite has been to open the
> database on our main thread, begin a transaction, then using a
> locking mechanism, hand the database connection to threads in turn,
> then once every 60 seconds we flush the connection by ending the
> transaction then closing the DB(and making a backup copy) and finally
> reopening the database.
>
> Whilst this approach seems to work reliably, I am concerned that it
> may not be the most efficient model, and in some cases we are seeing
> very heavy load on our application, where this technique could
> benefit from some optimisations, since the database access becomes a
> bottleneck.
>
> I am interested to learn from other SQLite developers:
>
> - Is there a faster way to flush to disk and backup the database ?

I don't think you'll get any faster flushing than just closing the  
transaction, however for backup you don't have to close the database  
connection, just start an immediate transaction to lock database write  
access, then backup the database file and rollback the transaction:

sqlite3_exec("BEGIN IMMEDIATE");
backup_database();
sqlite3_exec("ROLLBACK");

However, don't expect too much improvement - opening and closing  
database connections are done in a fraction of seconds, so this will  
not a major speed boost if you do this once every minute ;-)


>
> - We don't currently prepare SQL statements in advance, would this
> technique benefit from somehow preparing statements *before* that
> thread gets the lock on the database ?  Can we have multiple threads
> using the SAME database connection preparing SQL Queries at the same
> time (so long as only one is RUNNING the query at once ?).

With the latest sqlite, I believe you can even have multiple threads  
access the same database connection. In this case, access will still  
be serialized.
However, you can also have each thread create it's own database  
connection, then access the database concurrently (well, with  
limitations - there can always only be one writer thread). In this  
case you can actually execute several statements in parallel on  
different threads (i.e. different database connections).

See  and 
 for more details.

>
> Its tempting to consider using a multi-user database instead, but for
> the most part SQLite is doing a great job, and we don't really think
> the complexity of something like mySQL is warranted.  All access to
> the database is made from multiple threads in one application.
>
> Any other suggestions on ways we could optimize the way we interact
> with SQLite ?

If the majority of your requests are read requests, I'd go with the  
multiple connections in parallel approach. In that case you may have  
to manually serialize the write access or handle SQLITE_BUSY errors  
correctly.

If the majority of requests are write requests, parallelization won't  
help a lot due to sqlite's limitation of a single writer.


HTH,


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

[sqlite] Time to rewrite DBMS, says Ingres founder

[Ion Silvestru]

http://www.regdeveloper.co.uk/2008/02/18/stonebraker_dbms_outdated/

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

[sqlite] Virtual Tables performance

[Eric Grange]

We've begun implementing some virtual tables with latest SQLite build
and run into a few questions performance-wise, all these questions
revolve around the same issue (join performance).

1) Is there a way to know which fields are going to be potentially
requested by xColumn? For instance if I have a virtual table with 10
columns, but in the query only 2 of them are referred, is there a way to
know which these are? (without parsing the SQL independantly from SQLite)
I'm asking because the virtual tables we've been considering would be
aggregates, with potentially hundreds of virtual columns, and knowing
which xColumn calls may happen after an xFilter would be quite helpful.

2) Since SQLite uses loops for joins, this can result in a lot of
xFilter calls, and when the actual data is in an other DB server, this
means a lot of actual queries, which are slow, even when xFilter queries
for a specific primary key/index. To reduce this load, we've used
temporary tables with good success (selecting from the virtual table
into the temp table, then joining against the temp table).
Would there be a way to use sub-selects to that purpose? (ie. without
manually creating the temp table)

3) As soon as there are "or" or "in" terms in the SQL, xBestIndex isn't
solicited, and xFilter requests everything unfiltered, the solution to
that we found was to "union" the selects, which doesn't exactly improve
readability. Is there a better solution?
In our case, merely having a range constraint would already be enough to
drastically improve the row count of xFilter (ie. "field in (40, 50,
70)" being presented as a "field>=40 and field<=70" constraint)

Thanks,

Eric

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

Re: [sqlite] SQLite slow on WindowsXP

[Michał Bartyzel]

really works!
thanks a lot

Nuno Lucas pisze:
> On Feb 20, 2008 11:06 AM, Michał Bartyzel <[EMAIL PROTECTED]> wrote:
>   
>> Hi,
>>
>> I use the newest version of SQLite - 3.5.6.
>> I have 9 MB file with insert statements to ".read" into the data base.
>> When I run it under Linux it takes up to 20 minutes, but when I run it
>> under WndowsXP it takes about 2 hours!
>> Does anybody know why?
>> 
>
> Use transactions, that is, start with a "BEGIN" and finish with an "END".
> You will see that all your inserts will drop to just seconds instead
> of minutes (in Linux AND Windows).
>
>
> Regards,
> ~Nuno Lucas
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   

-- 
Michał Bartyzel
Projektant - Programista

Zespół ds. Projektów IT Equilibrium
FPL Sp. z o.o.

Rejestracja i dokumentacja / Registration & documentation:
Sąd Rejonowy dla Łodzi-Śródmieścia,
XX Wydział KRS, nr 075078;
kapitał zakładowy 100 170 PLN;
NIP 725-00-41-469.

Siedziba / Head office: Łódź 90-254, ul. Piramowicza 9
Kamienica Pod Aniołami
tel.  (042) 6 300 900  fax (042) 6 303 993

Biuro / Branch office:
Warszawa 02-703, ul. Bukowińska 24A
Rezydencja Pod Orłem
tel.  (022) 8 522 201  fax (022) 8 521 360

[EMAIL PROTECTED]
http://equilibrium.fpl.pl

Uwaga! Wiadomość ta przeznaczona jest jedynie dla osoby lub podmiotu,
który jest jej adresatem i zakazane
jest jej wszelkie przeglądanie, rozpowszechnianie czy wykorzystywanie
przez inne osoby czy podmioty niż adresat.
Jeśli otrzymałeś tę wiadomość przez pomyłkę, prosimy zawiadomić nadawcę
wysyłając informację zwrotną. Dziękujemy.

Note! This e-mail transmission is intended only for the use of the
individual or entity named above and may contain
information that is confidential,privileged, and exempt from disclosure
under applicable law. If you are not the intended
recipient, you are hereby notified that any disclosure, copying,
distribution, or use of any of the information contained in
this transmission is strictly PROHIBITED. If you have received this
transmission in error, please immediately notify me by
e-mail at the above address. Thank you.


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

Re: [sqlite] Easy question concerning C++ & sqlite3

[Igor Tandetnik]

"vl.pavlov" <[EMAIL PROTECTED]> wrote
in message news:[EMAIL PROTECTED]
> there is expression:
>
> select count(number) from words where word = bla
>
> which, if i am right, searches through whole table

Not if you have an index on "word". And you need a unique index anyway 
for "insert or replace" part to work (replace only kicks in if insert 
would have violated a uniqueness constraint).

Oh, and the subselect should use "select number ...", not "select 
count(number) ...".

Igor Tandetnik



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

Re: [sqlite] SQLite slow on WindowsXP

[Nuno Lucas]

On Feb 20, 2008 11:06 AM, Michał Bartyzel <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I use the newest version of SQLite - 3.5.6.
> I have 9 MB file with insert statements to ".read" into the data base.
> When I run it under Linux it takes up to 20 minutes, but when I run it
> under WndowsXP it takes about 2 hours!
> Does anybody know why?

Use transactions, that is, start with a "BEGIN" and finish with an "END".
You will see that all your inserts will drop to just seconds instead
of minutes (in Linux AND Windows).


Regards,
~Nuno Lucas
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite slow on WindowsXP

[Michał Bartyzel]

Hi,

I use the newest version of SQLite - 3.5.6.
I have 9 MB file with insert statements to ".read" into the data base.
When I run it under Linux it takes up to 20 minutes, but when I run it
under WndowsXP it takes about 2 hours!
Does anybody know why?

thanks for help
mike

-- 
Michał Bartyzel
Projektant - Programista

Zespół ds. Projektów IT Equilibrium
FPL Sp. z o.o.

Rejestracja i dokumentacja / Registration & documentation:
Sąd Rejonowy dla Łodzi-Śródmieścia,
XX Wydział KRS, nr 075078;
kapitał zakładowy 100 170 PLN;
NIP 725-00-41-469.

Siedziba / Head office: Łódź 90-254, ul. Piramowicza 9
Kamienica Pod Aniołami
tel.  (042) 6 300 900  fax (042) 6 303 993

Biuro / Branch office:
Warszawa 02-703, ul. Bukowińska 24A
Rezydencja Pod Orłem
tel.  (022) 8 522 201  fax (022) 8 521 360

[EMAIL PROTECTED]
http://equilibrium.fpl.pl

Uwaga! Wiadomość ta przeznaczona jest jedynie dla osoby lub podmiotu,
który jest jej adresatem i zakazane
jest jej wszelkie przeglądanie, rozpowszechnianie czy wykorzystywanie
przez inne osoby czy podmioty niż adresat.
Jeśli otrzymałeś tę wiadomość przez pomyłkę, prosimy zawiadomić nadawcę
wysyłając informację zwrotną. Dziękujemy.

Note! This e-mail transmission is intended only for the use of the
individual or entity named above and may contain
information that is confidential,privileged, and exempt from disclosure
under applicable law. If you are not the intended
recipient, you are hereby notified that any disclosure, copying,
distribution, or use of any of the information contained in
this transmission is strictly PROHIBITED. If you have received this
transmission in error, please immediately notify me by
e-mail at the above address. Thank you.


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

[sqlite] bit operations in select statements

[Jos van den Oever]

Hi all,

Is it possible to use logic operations on fields, in particular on
integers and on fixed sized blobs (256 bits/32 bytes).
I'd like to do queries like this:
 select key where number_of_bits_set(value) = 10;
or
 select key where value & '01011';

What would be a good method of implementing this?

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

Re: [sqlite] Easy question concerning C++ & sqlite3

[vl.pavlov]

hi again,

i wander in this expression:


* table words  *
* word | number *


insert or replace into words values(bla', coalesce((select count(number)
from words where word = bla)+1, 1));

there is expression:

select count(number) from words where word = bla

which, if i am right, searches through whole table -- i wander is there a
way to use some other function?



Dennis Cote wrote:
> 
> vl.pavlov wrote:
>> 
>> C++ aplication should put words into sqlite3 database, but if word that
>> should be put into the dbase is already in the table of the dbase it
>> should
>> increase the number which is in the second column of the same row. how?
>> 
>> eg:
>> coffee | 3
>> milk | 1
>> 
>> ...
>> 
>> i use standard functions from sqlite3.h
>> 
> 
> You can try this:
> 
> create table words (word text primary key, count integer);
> 
> insert or replace into words values(:word,
>   coalesce((select count from words where word = :word) + 1, 1));
> 
> It will replace the row with a new one that has the count incremented if 
> the word already exists. It will insert the word with a count of 1 if not.
> 
> HTH
> Dennis Cote
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Easy-question-concerning-C%2B%2B---sqlite3-tp15561319p15585117.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] Easy question concerning C++ & sqlite3

[vl.pavlov]

hello again & thx 4 reply

i'll try with this. i am very new 2 sql and sqlite so i would like 2 find
the fastest way to do the operation i explained in the first post

thank you once more


Dennis Cote wrote:
> 
> vl.pavlov wrote:
>> 
>> C++ aplication should put words into sqlite3 database, but if word that
>> should be put into the dbase is already in the table of the dbase it
>> should
>> increase the number which is in the second column of the same row. how?
>> 
>> eg:
>> coffee | 3
>> milk | 1
>> 
>> ...
>> 
>> i use standard functions from sqlite3.h
>> 
> 
> You can try this:
> 
> create table words (word text primary key, count integer);
> 
> insert or replace into words values(:word,
>   coalesce((select count from words where word = :word) + 1, 1));
> 
> It will replace the row with a new one that has the count incremented if 
> the word already exists. It will insert the word with a count of 1 if not.
> 
> HTH
> Dennis Cote
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Easy-question-concerning-C%2B%2B---sqlite3-tp15561319p15584651.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] Please test lastest CVS using WinCE

[noel frankinet]

[EMAIL PROTECTED] a écrit :
> Can somebody with the ability to compile and test for wince
> please test check-in [4802] for me.
>
>http://www.sqlite.org/cvstrac/chngview?cn=4802
>
> For that matter, is there anybody out there who would like
> to become the official wince maintainer for SQLite?  If you
> are able to compile, test, and debug SQLite for wince and
> would like to take on this task, we will be happy to welcome
> you to the official SQLite development team.  Benefits include:
>
>*  CVS check-in privileges
>*  Access to the private SQLite developers chatroom
>*  A prestigious [EMAIL PROTECTED] email alias
>*  Praise and admiration from your peers
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>   

Hello,

I am ready to help if you want.
I develop a GIS package on pocketPC and windows, It use sqlite quite well.

Best regards
Noël Frankinet

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

[sqlite] Database Question

[Jay Young]

I am using a sqlite3 database in a pda application that i am writing,
the reason i am using it is so when our employees are out in the field
collecting data the application can do data checking in the field to make
sure the data the field personel are checking
exist back in the main database in the office, the the PDA database needs to
be refreshed on a daily basis
and transfered to the pda before the field personnel leave for the day.

is there away to populate the sqlite3 database with data from an Oracle
database from inside of a visual basic application?
So i was thinking that if i can pull the information from the oracle tables
into a dynaset and then load that into sqlite3 using VB?

I don't know if this makes since but i am looking for away to get this done
thanks for any advise




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