Re: [sqlite] Many many tables... performance wise?

[Andrew Davison]

On 16/10/2010 12:01 PM, Dustin Sallings wrote:
>
>   ...but there will also be a unique index on rowid, which will get large 
> and need to be maintained.  I'm concerned that this alone could be limiting 
> me somewhat.
>
>   I have a similar application with a single table that I'd like to split 
> into more based on an identifier that appears in the table.  All of my 
> operations are limited to one of these identifiers (though it's not indexed, 
> the lookup is always by rowid).  Occasionally, I want to delete all records 
> based on an ID.
>
>   Bobby Tables is not relevant to my application as I know how to do my 
> bindings properly and have no confusion with data types (this is an integer) 
> or user data vs. executable code.
>
>   As a single table, I can easily have many tens of millions of rows.  
> Splitting it into 1,024 tables by a specific ID, I'd expect the each index to 
> be smaller and (at the very least), I'll have a far easier time deleting a 
> large chunk all at once.
>
>   I do intend to do some experimentation here, though it'd be helpful to 
> have some more detailed pointers as to why the intuition is wrong here.
>

Very similar to me. So far it is proving a good solution. But proof 
would be scaling from a dozen dynamic tables so far to hundreds+. If you 
do some testing on that please post.


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

Re: [sqlite] Many many tables... performance wise?

[Andrew Davison]

This is basically where I am coming from, but deletion (of possibly 
millions of entries) is slow and ties up the system (see previous thread 
by someone some days back). In experimentation having dynamic tables and 
doing a drop table is proving a big winner, though so far only using 
about a dozen tables.

On 16/10/2010 10:32 AM, Scott Hess wrote:
> Having a table with an owner_id, key, and value, with a unique index
> on (owner_id, key) will probably be more efficient than having a
> separate table per owner.  Also, it will be easier to code safely,
> because bind parameters don't work on table names (I'm assuming you're
> using dynamic table names in this case - if so, watch out for johny
> drop tables).
>
> -scott
>
>
> On Fri, Oct 15, 2010 at 4:26 PM, Andrew Davison
>   wrote:
>> There are no schemas per se. Just key+blob. They are backup datasets.
>> Nothing fancy databasey. Just wondering about the actual impact of
>> having many tables.
>>
>> On 15/10/2010 6:54 PM, Simon Slavin wrote:
>>>
>>> On 15 Oct 2010, at 7:36am, Andrew Davison wrote:
>>>
 What's the take on having hundreds of tables in a database?
>>>
>>> Generally not.  A database should be designed.  By a human.  I don't know 
>>> about you, but I can't hold hundreds of schema in my head at the same time. 
>>>  Rather than have two or more tables with the same schema, it's usually 
>>> better to have one table with an extra column to mark what kind of data 
>>> each record is.  There are exceptions to this but it's a good design 
>>> principle.
>>>
Any likely
 performance problems apart from first time a table is accessed? Does it
 affect the cache?
>>>
>>> SQLite keeps data from each table (and each index) in different pages of 
>>> filespace.  So each time you switch from one table to another you're 
>>> switching to another page of the file.  And if you have 100 tables in a 
>>> file you have 200 pages of space, reserved for only one kind of data that 
>>> can't be used for anything else.  That's an argument for fewer but bigger 
>>> tables.
>>>
>>> I understand why you asked the question but I think that an SQLite newbie 
>>> can only figure it out from experience.  My advice is to design stuff 
>>> whatever way makes it simplest for you to do your programming.  Worry about 
>>> performance only if it turns out to be too slow or too unwieldy or annoying 
>>> in some other way.
>>>
>>> Simon.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> 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] Problem with aggregate query

[Germán Herrera]

Hi all

I found the following strange behavior. It's rather easy to stumble upon
with:

sqlite> create table a(id,name);
sqlite> insert into a values (1,'name1');
sqlite> insert into a values (2,'name2');
sqlite> select * from a;
1|name1
2|name2
sqlite> select count(*), name from a;
2|name2
sqlite>

As you may know, both MySQL and SQL Server engines would refuse to run
the last query, indicating an error because not all columns come from
aggregate functions and there is no "group by" clause..

Is this left on purpose?, can this behavior be switched? (already
searched in the Documentation, and in the list of pragmas and couldn't
find anything).

I'm kind of new in SQlite so please bare with my ignorance (and my poor
english ;-)).

Thank you all for your help, and best wishes!
German
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many many tables... performance wise?

[Dustin Sallings]

On Oct 15, 2010, at 17:32, Scott Hess wrote:

> Having a table with an owner_id, key, and value, with a unique index
> on (owner_id, key) will probably be more efficient than having a
> separate table per owner.  Also, it will be easier to code safely,
> because bind parameters don't work on table names (I'm assuming you're
> using dynamic table names in this case - if so, watch out for johny
> drop tables).


...but there will also be a unique index on rowid, which will get large 
and need to be maintained.  I'm concerned that this alone could be limiting me 
somewhat.

I have a similar application with a single table that I'd like to split 
into more based on an identifier that appears in the table.  All of my 
operations are limited to one of these identifiers (though it's not indexed, 
the lookup is always by rowid).  Occasionally, I want to delete all records 
based on an ID.

Bobby Tables is not relevant to my application as I know how to do my 
bindings properly and have no confusion with data types (this is an integer) or 
user data vs. executable code.

As a single table, I can easily have many tens of millions of rows.  
Splitting it into 1,024 tables by a specific ID, I'd expect the each index to 
be smaller and (at the very least), I'll have a far easier time deleting a 
large chunk all at once.

I do intend to do some experimentation here, though it'd be helpful to 
have some more detailed pointers as to why the intuition is wrong here.

-- 
Dustin Sallings

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

Re: [sqlite] Many many tables... performance wise?

[Scott Hess]

Having a table with an owner_id, key, and value, with a unique index
on (owner_id, key) will probably be more efficient than having a
separate table per owner.  Also, it will be easier to code safely,
because bind parameters don't work on table names (I'm assuming you're
using dynamic table names in this case - if so, watch out for johny
drop tables).

-scott


On Fri, Oct 15, 2010 at 4:26 PM, Andrew Davison
 wrote:
> There are no schemas per se. Just key+blob. They are backup datasets.
> Nothing fancy databasey. Just wondering about the actual impact of
> having many tables.
>
> On 15/10/2010 6:54 PM, Simon Slavin wrote:
>>
>> On 15 Oct 2010, at 7:36am, Andrew Davison wrote:
>>
>>> What's the take on having hundreds of tables in a database?
>>
>> Generally not.  A database should be designed.  By a human.  I don't know 
>> about you, but I can't hold hundreds of schema in my head at the same time.  
>> Rather than have two or more tables with the same schema, it's usually 
>> better to have one table with an extra column to mark what kind of data each 
>> record is.  There are exceptions to this but it's a good design principle.
>>
>>>   Any likely
>>> performance problems apart from first time a table is accessed? Does it
>>> affect the cache?
>>
>> SQLite keeps data from each table (and each index) in different pages of 
>> filespace.  So each time you switch from one table to another you're 
>> switching to another page of the file.  And if you have 100 tables in a file 
>> you have 200 pages of space, reserved for only one kind of data that can't 
>> be used for anything else.  That's an argument for fewer but bigger tables.
>>
>> I understand why you asked the question but I think that an SQLite newbie 
>> can only figure it out from experience.  My advice is to design stuff 
>> whatever way makes it simplest for you to do your programming.  Worry about 
>> performance only if it turns out to be too slow or too unwieldy or annoying 
>> in some other way.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many many tables... performance wise?

[Andrew Davison]

There are no schemas per se. Just key+blob. They are backup datasets. 
Nothing fancy databasey. Just wondering about the actual impact of 
having many tables.

On 15/10/2010 6:54 PM, Simon Slavin wrote:
>
> On 15 Oct 2010, at 7:36am, Andrew Davison wrote:
>
>> What's the take on having hundreds of tables in a database?
>
> Generally not.  A database should be designed.  By a human.  I don't know 
> about you, but I can't hold hundreds of schema in my head at the same time.  
> Rather than have two or more tables with the same schema, it's usually better 
> to have one table with an extra column to mark what kind of data each record 
> is.  There are exceptions to this but it's a good design principle.
>
>>   Any likely
>> performance problems apart from first time a table is accessed? Does it
>> affect the cache?
>
> SQLite keeps data from each table (and each index) in different pages of 
> filespace.  So each time you switch from one table to another you're 
> switching to another page of the file.  And if you have 100 tables in a file 
> you have 200 pages of space, reserved for only one kind of data that can't be 
> used for anything else.  That's an argument for fewer but bigger tables.
>
> I understand why you asked the question but I think that an SQLite newbie can 
> only figure it out from experience.  My advice is to design stuff whatever 
> way makes it simplest for you to do your programming.  Worry about 
> performance only if it turns out to be too slow or too unwieldy or annoying 
> in some other way.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] Comma-delimited field to rows (Once again)

[Max Vlasov]

On Sat, Oct 16, 2010 at 2:47 AM, Jim Morris  wrote:

>  Not much help but this removes the multiplication:
> SELECT B1.B + B2.B + B3.B + B4.B FROM
> (SELECT 0 AS B UNION SELECT 1 AS B) AS B1,
> (SELECT 0 AS B UNION SELECT 2 AS B) AS B2,
> (SELECT 0 AS B UNION SELECT 4 AS B) AS B3,
> (SELECT 0 AS B UNION SELECT 8 AS B) AS B4
>
>
thanks, Jim, thinking "binary" sometimes makes things more complicated than
it could be :)

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

Re: [sqlite] Comma-delimited field to rows (Once again)

[Jim Morris]

  Not much help but this removes the multiplication:
SELECT B1.B + B2.B + B3.B + B4.B FROM
(SELECT 0 AS B UNION SELECT 1 AS B) AS B1,
(SELECT 0 AS B UNION SELECT 2 AS B) AS B2,
(SELECT 0 AS B UNION SELECT 4 AS B) AS B3,
(SELECT 0 AS B UNION SELECT 8 AS B) AS B4

On 10/15/2010 3:00 PM, Max Vlasov wrote:
> Hi,
> from time to time I try to solve well-known task of making rows from a
> comma-delimited list and the best I could do was this:
> - create a user-function returning zero-based Nth item from the list (let's
> call it GetItemFromSet)
> - make a complex query like this (this one allows up to 16 elements in the
> list, can be expanded with similar selects)
>
> SELECT Trim(GetItemFromSet(Value, '23, 14, 1, 7, 9')) Item FROM
> (SELECT B1.B*1 + B2.B*2 + B3.B*4 + B4.B*8 AS VALUE FROM
> (SELECT 0 AS B UNION SELECT 1 AS B) AS B1,
> (SELECT 0 AS B UNION SELECT 1 AS B) AS B2,
> (SELECT 0 AS B UNION SELECT 1 AS B) AS B3,
> (SELECT 0 AS B UNION SELECT 1 AS B) AS B4,
> ) WHERE NOT (Item Is Null)
>
> Is there a way to implement something more elegant or at least to improve
> this approach. For example, the query for 16 bit limit will have 16 selects
> and bigger expression and also will iterate through all cross join output
> when we actually only have 5 items in the list.
>
> Max
> ___
> 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] error 14 : unable to open database file

[Simon]

Thanks man...  I found I had forgot to fclose() one file after each
iteration, so I'm assuming I was reaching some limit of max files
opened by the same process (though it was the same file each time it
still stacked up).

Ill let it run for a while and see...  Iterations were originally set
to 10 minutes, now at 1 minute I should see faster if it crashes while
still having decent load on the network/source.

Thanks a lot!
  Simon

On 10/15/10, Simon Slavin  wrote:
>
> On 15 Oct 2010, at 8:11pm, Simon wrote:
>
>> The program is meant to loop forever, grab information from the
>> internet and store it in the database.  The error happens after 12-24
>> hours of execution.  The database file is opened at the beginning of
>> execution and is never closed (it would if the loop was not
>> while(1){...}).
>
> Does your application have a leak of some kind ?  Does it get slower, or
> request more memory, or open more file handle, the longer you leave it
> running ?  Use your OS's utilities to monitor it and see if it looks
> different in the 12th hour to the way it looks in the 1st hour.
>
>> This transaction is done every 60 seconds and there is only one thread
>> working.  The transaction contains 1 or 2 dozen INSERTs only...  And
>> my program has no memory leaks, the system has been running very
>> stably for 96 days so far...
>
> Hmm.  Just for testing, make it go crazy and do things ten times as often or
> insert ten copies of every record.  Does the error happen at the same time,
> or ten times sooner ?
>
>> Should I also close the database
>> after doing my transaction and re-open it every iteration of my loop?
>
> Try it.  If it fixes your problem then you have a serious bug, either in
> your code or in one of the support libraries you're using (or possibly your
> OS !).
>
> Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Comma-delimited field to rows (Once again)

[Max Vlasov]

Hi,
from time to time I try to solve well-known task of making rows from a
comma-delimited list and the best I could do was this:
- create a user-function returning zero-based Nth item from the list (let's
call it GetItemFromSet)
- make a complex query like this (this one allows up to 16 elements in the
list, can be expanded with similar selects)

SELECT Trim(GetItemFromSet(Value, '23, 14, 1, 7, 9')) Item FROM
(SELECT B1.B*1 + B2.B*2 + B3.B*4 + B4.B*8 AS VALUE FROM
(SELECT 0 AS B UNION SELECT 1 AS B) AS B1,
(SELECT 0 AS B UNION SELECT 1 AS B) AS B2,
(SELECT 0 AS B UNION SELECT 1 AS B) AS B3,
(SELECT 0 AS B UNION SELECT 1 AS B) AS B4,
) WHERE NOT (Item Is Null)

Is there a way to implement something more elegant or at least to improve
this approach. For example, the query for 16 bit limit will have 16 selects
and bigger expression and also will iterate through all cross join output
when we actually only have 5 items in the list.

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

Re: [sqlite] error 14 : unable to open database file

[Simon Slavin]

On 15 Oct 2010, at 8:11pm, Simon wrote:

> The program is meant to loop forever, grab information from the
> internet and store it in the database.  The error happens after 12-24
> hours of execution.  The database file is opened at the beginning of
> execution and is never closed (it would if the loop was not
> while(1){...}).

Does your application have a leak of some kind ?  Does it get slower, or 
request more memory, or open more file handle, the longer you leave it running 
?  Use your OS's utilities to monitor it and see if it looks different in the 
12th hour to the way it looks in the 1st hour.

> This transaction is done every 60 seconds and there is only one thread
> working.  The transaction contains 1 or 2 dozen INSERTs only...  And
> my program has no memory leaks, the system has been running very
> stably for 96 days so far...

Hmm.  Just for testing, make it go crazy and do things ten times as often or 
insert ten copies of every record.  Does the error happen at the same time, or 
ten times sooner ?

> Should I also close the database
> after doing my transaction and re-open it every iteration of my loop?

Try it.  If it fixes your problem then you have a serious bug, either in your 
code or in one of the support libraries you're using (or possibly your OS !).

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

Re: [sqlite] Duplicate results for a given primary key/row

[Simon Slavin]

On 15 Oct 2010, at 6:43pm, Jeff Flanigan wrote:

> Cool, that definitely tells me the db is corrupt. Is there any way to recover 
> a corrupted db, or is it completely borked?

http://www.sqlite.org/sqlite.html

Use the command-line tool to dump the database as a text file (a long list of 
SQL commands).  Then try to read that again.  It might work.  If it produces 
the wrong results or error messages you might be able to fix the problem by 
editing the file before you import it.

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

[sqlite] error 14 : unable to open database file

[Simon]

Hi guys,
  first time I see this, not much docs on the topic, or I couldn't
find much on this one...

  The program is meant to loop forever, grab information from the
internet and store it in the database.  The error happens after 12-24
hours of execution.  The database file is opened at the beginning of
execution and is never closed (it would if the loop was not
while(1){...}).  The bug happens during my own "query" function,
meaning one of the prepare, step, finalize functions cause this (I
will try to fine grain my log to help on this).  The query is made of
text only, no parameters are bound, the query's text is perfectly sane
and the error happens on one INSERT of a transaction (ie, not at begin
or commit transaction).

  I'm not sure how to interpret this as the DB has been opened all
along.  It is the only process that is accessing the database file.
The file itself is in a writable directory, the file and any other
(-journal, etc) are writable, plenty of disk space, plenty of inodes
left, no quotas, plenty of ram available, the /tmp has disk space
available too...  And I can't reproduce it, except with lots of
patience...  also the query contains sane data, within a transaction.
This transaction is done every 60 seconds and there is only one thread
working.  The transaction contains 1 or 2 dozen INSERTs only...  And
my program has no memory leaks, the system has been running very
stably for 96 days so far...

Running on Linux (a Linode, actually a virtual private server), distro
is Gentoo, kernel is 2.6.35-rc3 with aufs2 compiled in but not used,
filesystem is ext3 (it has journaling and has options
"barrier=0,data=writeback").  The database path is relative to the
process (ie:  "someDir/theDBfile") and has no substitution of any
sort.  The file is local not networked.  The DB itself is setup with
all default options.  Using sqlite-3.6.23.1

  As I said, I will fine-grain my debug log and I will try to
reinforce this query function...  Should I also close the database
after doing my transaction and re-open it every iteration of my loop?
Or perhaps close and reopen every hour or so?  I don't think so, but
let me know what you think...

  If anyone has had experience like this, or knows something, or has
any idea at all, I'd be very happy to hear about it!
  Oh and if you guys need me to include some more technical info, let
me know what you need (and maybe how I can get it just in case I don't
know)...

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

Re: [sqlite] Duplicate results for a given primary key/row

[Pavel Ivanov]

This is most probably a corruption where index have some rowids not
present in table. If nothing else is corrupted you can just drop the
index and recreate it again.

Pavel

On Fri, Oct 15, 2010 at 1:43 PM, Jeff Flanigan  wrote:
> Cool, that definitely tells me the db is corrupt. Is there any way to recover 
> a corrupted db, or is it completely borked?
>
> - Original Message -
> From: "Simon Slavin" 
> To: "General Discussion of SQLite Database" 
> Sent: Friday, October 15, 2010 1:30:34 PM
> Subject: Re: [sqlite] Duplicate results for a given primary key/row
>
>
> On 15 Oct 2010, at 6:11pm, Jeff Flanigan wrote:
>
>> My initial guess is this is due to some sort of database corruption,
>
> http://www.sqlite.org/pragma.html#pragma_integrity_check
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Duplicate results for a given primary key/row

[Jeff Flanigan]

Cool, that definitely tells me the db is corrupt. Is there any way to recover a 
corrupted db, or is it completely borked?

- Original Message -
From: "Simon Slavin" 
To: "General Discussion of SQLite Database" 
Sent: Friday, October 15, 2010 1:30:34 PM
Subject: Re: [sqlite] Duplicate results for a given primary key/row


On 15 Oct 2010, at 6:11pm, Jeff Flanigan wrote:

> My initial guess is this is due to some sort of database corruption,

http://www.sqlite.org/pragma.html#pragma_integrity_check

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

Re: [sqlite] Duplicate results for a given primary key/row

[Simon Slavin]

On 15 Oct 2010, at 6:11pm, Jeff Flanigan wrote:

> My initial guess is this is due to some sort of database corruption,

http://www.sqlite.org/pragma.html#pragma_integrity_check

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

Re: [sqlite] GUI for data entry

[Graham Smith]

Gabor,

> If the purpose of this is teaching with R then the R package sqldf
> lets you query all R data frames in your session using sql as if they
> were one big giant database.

No, nothing to do with teaching R, but still a useful point in terms
of broadening the use of SQL with R.

Thanks,

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

[sqlite] Duplicate results for a given primary key/row

[Jeff Flanigan]

I am receiving duplicate entries for a given 'row' in a select result. It looks 
like the select is finding multiple rows with different rowid (the built-in 
hidden column) for a given primary key. My initial guess is this is due to some 
sort of database corruption, but any insight would be helpful.


Selecting by id returns only the expected single row:

sqlite> select * from mail_item where id=714474;
714474|714463

sqlite> select rowid,id from mail_item where id=714474;
193254|714474


Selecting by parent_id returns multiple records:

sqlite> select id from mail_item where parent_id=714463;
714474
714474
714474
714474
714474
714474

sqlite> select rowid,id from mail_item where parent_id=714463;
193249|714474
193250|714474
193251|714474
193252|714474
193253|714474
193254|714474


Here is a simplified version of the table. There are other columns which have 
been omitted for brevity.

CREATE TABLE foo (
   idINTEGER UNSIGNED NOT NULL PRIMARY KEY,
   parent_id INTEGER UNSIGNED,
   CONSTRAINT fk_mail_item_parent_id FOREIGN KEY (parent_id) REFERENCES foo(id) 
ON UPDATE CASCADE
);
CREATE INDEX i_mail_item_parent_id ON mail_item(parent_id);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GUI for data entry

[Gabor Grothendieck]

On Fri, Oct 15, 2010 at 12:54 PM, Graham Smith  wrote:
> Tom,
>
> Thanks for this.
>
> My main reason for asking is because I am trying to encourage my
> students and indeed clients to think "database" rather than
> "spreadsheet". Most of the time these aren't big or complex data sets
> (normally records in the hundreds, sometimes the thousands) but still
> big enough to create major problems for themselves and me, just
> because the spreadsheet gives them the freedom to really screw things
> up.
>
> While far from perfect, I could live with a single table in a database
> that could be queried from R .  But it needs to be user friendly and
> run on Linux, Windows and Macs.
>

If the purpose of this is teaching with R then the R package sqldf
lets you query all R data frames in your session using sql as if they
were one big giant database.  If you stick with the few dozen data
frames that ship with R or ones you create yourself using various R
facilities then you don't have to enter anything in the first place.
See the sqldf home page at: http://sqldf.googlecode.com

-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attach on disk database to memory database

[Schoinya]

Dan Kennedy-4 wrote:
> 
> 
> On Oct 15, 2010, at 11:40 PM, Schoinya wrote:
> 
>>
>> Hello everybody
>>
>> I'm trying to attach on disk database to in memory database.
>>
>> But I get the strange error : SQLite error unrecognized token: ":"
>>
>> The following is the code:
>>
>>SQLiteConnection connInMemory = new  
>> SQLiteConnection("Data
>> Source=:memory:");
>>connInMemory.Open();
>>SQLiteCommand commandInMemory = new  
>> SQLiteCommand(@"ATTACH "
>> + Application.StartupPath + "\\Northwind.sl3" + " AS  
>> disk",
>> connInMemory);
>>commandInMemory.ExecuteNonQuery();
>>
>> I'm using System.Data.SQLite, in Visual Studio 2008, c#.
>> I could connect and query the same database without an error using :
>>
>>SQLiteConnection connOnDisk = new  
>> SQLiteConnection("Data
>> Source=" +
>>Application.StartupPath +
>> "\\Northwind.sl3;CacheSize=0");
>>connOnDisk.Open();
>>SQLiteCommand commandOnDisk = new  
>> SQLiteCommand(connOnDisk);
>>commandOnDisk.CommandText = "select count(OrderID) from
>> Orders";
>>commandOnDisk.ExecuteNonQuery();
>>
>> What is the problem?
> 
> Put quotes around the filename.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

Thanks so much Dan. It works now

-- 
View this message in context: 
http://old.nabble.com/Attach-on-disk-database-to-memory-database-tp29973365p29973478.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] GUI for data entry

[Graham Smith]

Tom,

Thanks for this.

My main reason for asking is because I am trying to encourage my
students and indeed clients to think "database" rather than
"spreadsheet". Most of the time these aren't big or complex data sets
(normally records in the hundreds, sometimes the thousands) but still
big enough to create major problems for themselves and me, just
because the spreadsheet gives them the freedom to really screw things
up.

While far from perfect, I could live with a single table in a database
that could be queried from R .  But it needs to be user friendly and
run on Linux, Windows and Macs.

And of course, I would like to introduce other aspects of database
design, but a lot of the time the idea of developing an "application",
when there may only ever be one or two users is a bit of an overkill.

So I suppose I have two objectives 1) to convince people that using  a
"simple" database isn't as difficult as they think, so they give them
a go and 2) to  develop enough understanding of databases so they
know they need to hire someone, or develop high level database skills
if they have a project that is working with lots of data.

I should say that these are biology/ecology students, or professional
ecologists, so this isn't a core subject, and no one but me seems
particularly concerned about the use of spreadsheets, even for very
large data sets  (tens of thousands of records).

So, I am currently exploring how I can drive some enthusiasm for
databases, as well as learn more about them myself.

I will have a look at the table of editors and see how I get on.

BTW when I said SQLite Manager, I meant the firefox addin, from your
table I see that SQLiteManager is a different program.

Thanks again,

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

Re: [sqlite] Attach on disk database to memory database

[Dan Kennedy]

On Oct 15, 2010, at 11:40 PM, Schoinya wrote:

>
> Hello everybody
>
> I'm trying to attach on disk database to in memory database.
>
> But I get the strange error : SQLite error unrecognized token: ":"
>
> The following is the code:
>
>SQLiteConnection connInMemory = new  
> SQLiteConnection("Data
> Source=:memory:");
>connInMemory.Open();
>SQLiteCommand commandInMemory = new  
> SQLiteCommand(@"ATTACH "
> + Application.StartupPath + "\\Northwind.sl3" + " AS  
> disk",
> connInMemory);
>commandInMemory.ExecuteNonQuery();
>
> I'm using System.Data.SQLite, in Visual Studio 2008, c#.
> I could connect and query the same database without an error using :
>
>SQLiteConnection connOnDisk = new  
> SQLiteConnection("Data
> Source=" +
>Application.StartupPath +
> "\\Northwind.sl3;CacheSize=0");
>connOnDisk.Open();
>SQLiteCommand commandOnDisk = new  
> SQLiteCommand(connOnDisk);
>commandOnDisk.CommandText = "select count(OrderID) from
> Orders";
>commandOnDisk.ExecuteNonQuery();
>
> What is the problem?

Put quotes around the filename.

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

[sqlite] Attach on disk database to memory database

[Schoinya]

Hello everybody

I'm trying to attach on disk database to in memory database.

But I get the strange error : SQLite error unrecognized token: ":"

The following is the code:

SQLiteConnection connInMemory = new SQLiteConnection("Data
Source=:memory:");
connInMemory.Open();
SQLiteCommand commandInMemory = new SQLiteCommand(@"ATTACH "
+ Application.StartupPath + "\\Northwind.sl3" + " AS disk",
connInMemory);
commandInMemory.ExecuteNonQuery();

I'm using System.Data.SQLite, in Visual Studio 2008, c#.
I could connect and query the same database without an error using :

SQLiteConnection connOnDisk = new SQLiteConnection("Data
Source=" +
Application.StartupPath +
"\\Northwind.sl3;CacheSize=0");
connOnDisk.Open();
SQLiteCommand commandOnDisk = new SQLiteCommand(connOnDisk);
commandOnDisk.CommandText = "select count(OrderID) from
Orders";
commandOnDisk.ExecuteNonQuery();

What is the problem?



-- 
View this message in context: 
http://old.nabble.com/Attach-on-disk-database-to-memory-database-tp29973365p29973365.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] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

[Black, Michael (IS)]

I also see where you can set the behavior using procnto -- I'll bet "procnto 
~i" will make sqlite behave correctly.  Though this is a global change.  
Anybody who depends on this zeroing though is nuts...
 
http://www.qnx.com/developers/docs/6.4.0/neutrino/lib_ref/m/munmap_flags.html
 

There are some interactions of the flags argument with the MAP_NOINIT flag of 
the mmap()   
function as well as procnto 
 , as 
detailed below: 

*   The mmap() function has a new flag, MAP_NOINIT. When specified, the 
POSIX requirement that the memory be zeroed is relaxed. The physical memory 
being used for this allocation must have been previously freed with 
UNMAP_INIT_OPTIONAL for this flag to have any effect. 
*   The procnto command line now has an -mmemmgr_configuration option. The 
memmgr_configuration string has a sequence of configuration options to enable 
(or if preceeded with a ~ character, disable) memory-manager aspects. The 
configuation options are: 

i 
munmap_flags() (with flags parameter as zero) acts as if 
UNMAP_INIT_REQUIRED were specified (the default). 
~i 
munmap_flags() (with flags parameter as zero) acts as if 
UNMAP_INIT_OPTIONAL were specified. 

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 

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

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

[Black, Michael (IS)]

The problem is the lack of the unmap call before the 2nd mmap.
It's redundant on most systems but apparently needed to make QNX happy 
(including the flags we discussed before on both unmap and mmap).
 
So we need
 
   ftruncate(fd, 32*1024);
   mmap(0, 32*1024, PROT_READ|PROT_WRITE, MAP_SHARED, fd, 0);
   
   ftruncate(fd, 64*1024);
#if QNX0..
   munmap_flags(0,32*1024,UNMAP_INIT_OPTIONAL);
   munmap(0,32*1024);
   mmap(0, 32*1024, PROT_READ|PROT_WRITE, MAP_SHARED|MAP_NOINIT, fd, 32*1024);
#else
   map(0, 32*1024, PROT_READ|PROT_WRITE, MAP_SHARED, fd, 32*1024);
#endif


Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Dan Kennedy
Sent: Fri 10/15/2010 10:37 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS




On Oct 15, 2010, at 10:24 PM, Black, Michael (IS) wrote:

> I'm not sure but I suspect sqlite is not calling unmap before 
> extending the area.
> That would explain why it still gets zeroed out even with the flags.
>
> Put a break point in the unixShmUnmap call and see if it gets called 
> before mmap.


We're doing this:

   ftruncate(fd, 32*1024);
   mmap(0, 32*1024, PROT_READ|PROT_WRITE, MAP_SHARED, fd, 0);
   
   ftruncate(fd, 64*1024);
   mmap(0, 32*1024, PROT_READ|PROT_WRITE, MAP_SHARED, fd, 32*1024);

No unmap calls.

Praveen's investigations suggest that the second mmap() call is
zeroing the memory mapped by the first mmap() call. Which is,
as you might expect, confusing SQLite.

I guess it could also be the second ftruncate() call that is
zeroing our mapped memory. That would be even odder though...

Dan.



> May just need some QNX logic that says "if we're extending an area 
> unmap it first with flags".
>
> I think the mmap should honor the NOINIT flag when extending an area 
> but apparently it doesn't -- only unmap can set the flag to make it 
> honor it.  QNX must be about the only one that does this.
>
> QNX mmap claims to be POSIX 1003.1 compliant but I don't see this 
> behavior defined in there.
>
> The only zero-fill reference I see in POSIX is
> "The system shall always zero-fill any partial page at the end of an 
> object"
> which doesn't fit this behavior at all.
>
> Perhaps you should report this as a bug or non-desirable/non-
> compliant behavior compared to every other OS in the world (and the 
> POSIX standard which doesn't call for this behavior).
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Raj, Praveen
> Sent: Fri 10/15/2010 9:55 AM
> To: General Discussion of SQLite Database
> Subject: EXTERNAL:Re: [sqlite] TestFixture 3.7.2 - Some WAL tests 
> fail on QNX OS
>
>
>
> Hi Michael,
>
> Yes I added the "MAP_NOINIT" to mmap() and "UNMAP_INIT_OPTIONAL" 
> flag to munmap_flags() call. Don't know where i might be going wrong 
> in SQLite.
>
> As you suggested, I wrote a small application to check if this 
> works. Fortunately it worked as desired (as given below).
>
> MAP_NOINIT
> When specified, the POSIX requirement that the memory be zeroed is 
> relaxed. The physical memory being used for this allocation must 
> have been previously freed with UNMAP_INIT_OPTIONAL for this flag to 
> have any effect.
>
> _
> int main(int argc, char *argv[]) {
>printf("Welcome to the QNX Momentics IDE\n");
>
>//Open a file
>int fd = open("/tmp/mmaptest",O_RDWR|O_CREAT, 0777);
>
>//Truncate the file
>int size = ftruncate(fd, 2048);
>
>const char buff[]="Testing the mmap API";
>write(fd,buff,sizeof(buff));
>
>//Mapping the disk file to memory
>void *pMem = mmap(0, 2048, PROT_READ|PROT_WRITE,MAP_SHARED, fd, 0);
>
>//Check the mmapped memory contents
>printf("Memory Mapped Data: %s\n", pMem);
>
>//Set all mmapped locations to 'A'
>memset(pMem,'A',2048);
>
>//Unmap the memory
>//munmap(pMem, 2048);
>munmap_flags(pMem, 2048, UNMAP_INIT_OPTIONAL);
>
>//Buffer to read disk data
>char disk_read[2048];
>
>//Read the data from the file to check if its synced
>read(fd,disk_read,sizeof(disk_read));
>printf("Reading disk file data:%s\n",disk_read);
>
>//Resetting the contents of disk file
>size = ftruncate(fd, 0);
>close(fd);
>
>//Open the file again to check to re-map the memory
>fd = open("/tmp/mmaptest",O_RDWR, 0777);
>size = ftruncate(fd, 2048);
>
>//Map the memory region again
>//int pMem1 = mmap(0, 2048, PROT_READ|PROT_WRITE,MAP_SHARED, fd, 
> 0);
>int pMem1 = mmap(0, 2048, PROT_READ|PROT_WRITE,MAP_SHARED|
> MAP_NOINIT, fd, 0);
>
>//Print the re-mapped region
>printf("New Memory Mapped Data: %

[sqlite] Re : restore function

[Roger Martinez]

thank you



De : Igor Tandetnik 
À : sqlite-users@sqlite.org
Envoyé le : Jeu 14 octobre 2010, 2h 28min 18s
Objet : Re: [sqlite] restore function

Roger MARTINEZ  wrote:
> restore function is not ok for me .
> Before I make a backup with
> /usr/local/sqlite-3.7.2/bin/sqlite3 spip.sqlite
> sqlite>.backup main spip.save
> ...
> /usr/local/sqlite-3.7.2/bin/sqlite3 spip.save
> sqlite>.database
> seq name file
> ---  
>-
> 0 main /home/roger/htdocs/essai/spip/config/bases/spip.save
> ...
> 
> sqlite>.restore spip.sqlite spip.save
> Error: unknown database spip.sqlite

The first parameter of .restore is not the file name. It's the database name, 
e.g. "main" or a name you passed to ATTACH DATABASE. You got it right for 
.backup.
-- 
Igor Tandetnik


___
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] Re : restore function

[Roger Martinez]

thank you for answer Igor 
At once , i didn't knew if i could use restore function with sqlite corrupt 
database file .It seems that if i use corrupt file or empty file, restore 
function failed . With database file and table dropped it's ok
Regards 
Roger





De : Igor Tandetnik 
À : sqlite-users@sqlite.org
Envoyé le : Jeu 14 octobre 2010, 2h 28min 18s
Objet : Re: [sqlite] restore function

Roger MARTINEZ  wrote:
> restore function is not ok for me .
> Before I make a backup with
> /usr/local/sqlite-3.7.2/bin/sqlite3 spip.sqlite
> sqlite>.backup main spip.save
> ...
> /usr/local/sqlite-3.7.2/bin/sqlite3 spip.save
> sqlite>.database
> seq name file
> ---  
>-
> 0 main /home/roger/htdocs/essai/spip/config/bases/spip.save
> ...
> 
> sqlite>.restore spip.sqlite spip.save
> Error: unknown database spip.sqlite

The first parameter of .restore is not the file name. It's the database name, 
e.g. "main" or a name you passed to ATTACH DATABASE. You got it right for 
.backup.
-- 
Igor Tandetnik


___
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] GUI for data entry

[BareFeetWare]

Hi Graham,

>> You don't need to create special tables for data entry. You can create views 
>> instead, coupled with "instead of" triggers.
> 
>> I'll see if I can put together some SQL with a few examples of how a view 
>> updates several >related tables.
> 
> As the OP, I would be very interested in this.

OK, I'll put something together and post it here. Do you have a schema you've 
been working on? I might be able to adapt it for you.

> I did try using Views, as there is no forms option in SQLiteManager

Try clicking the pencil button to show "Record Editor". I could only get it to 
work sometimes for views.

> but the "add" button is
> greyed out when using a View.

SQLiteManager seems to support updates (changing of existing rows) but not 
inserts (adding a new row) in views.

>> Comparison of SQLite GUI tools:
>> http://www.barefeetware.com/sqlite/compare/?ml
> 
> This link is currently giving an internal server error.

Ah, thanks for the heads up. I've been moving my website to another hosting 
service. They don't seem to like some permissions on the PHP files. 
Incidentally, this page is actually a PHP file that grabs the data from an 
SQLite database. This database provides a view for the PHP to read and for a 
data entry operator (well, that's most often me) to read when using a GUI. That 
same view has "instead of" triggers so I can enter changes back into that same 
view, which propagates back to the underlying tables.

One of the features compared on the SQLite GUI comparison web page (which is 
now publicly viewable again), is "Data Entry -> Entry in views (with "instead 
of" triggers)". This will tell you what SQLite GUI editors I found that support 
editing of data in views.

Until today, I hadn't updated this comparison matrix for over a year, and now 
realise that some apps have even disappeared. It needs updating. If anyone is 
familiar with another SQLite GUI editor, please provide values for the 
comparison table and I will add it to the web page. Or let me know of any 
changes needed to existing data, or extra features you've compared.

Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

[Dan Kennedy]

On Oct 15, 2010, at 10:24 PM, Black, Michael (IS) wrote:

> I'm not sure but I suspect sqlite is not calling unmap before  
> extending the area.
> That would explain why it still gets zeroed out even with the flags.
>
> Put a break point in the unixShmUnmap call and see if it gets called  
> before mmap.


We're doing this:

   ftruncate(fd, 32*1024);
   mmap(0, 32*1024, PROT_READ|PROT_WRITE, MAP_SHARED, fd, 0);
   
   ftruncate(fd, 64*1024);
   mmap(0, 32*1024, PROT_READ|PROT_WRITE, MAP_SHARED, fd, 32*1024);

No unmap calls.

Praveen's investigations suggest that the second mmap() call is
zeroing the memory mapped by the first mmap() call. Which is,
as you might expect, confusing SQLite.

I guess it could also be the second ftruncate() call that is
zeroing our mapped memory. That would be even odder though...

Dan.



> May just need some QNX logic that says "if we're extending an area  
> unmap it first with flags".
>
> I think the mmap should honor the NOINIT flag when extending an area  
> but apparently it doesn't -- only unmap can set the flag to make it  
> honor it.  QNX must be about the only one that does this.
>
> QNX mmap claims to be POSIX 1003.1 compliant but I don't see this  
> behavior defined in there.
>
> The only zero-fill reference I see in POSIX is
> "The system shall always zero-fill any partial page at the end of an  
> object"
> which doesn't fit this behavior at all.
>
> Perhaps you should report this as a bug or non-desirable/non- 
> compliant behavior compared to every other OS in the world (and the  
> POSIX standard which doesn't call for this behavior).
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Raj, Praveen
> Sent: Fri 10/15/2010 9:55 AM
> To: General Discussion of SQLite Database
> Subject: EXTERNAL:Re: [sqlite] TestFixture 3.7.2 - Some WAL tests  
> fail on QNX OS
>
>
>
> Hi Michael,
>
> Yes I added the "MAP_NOINIT" to mmap() and "UNMAP_INIT_OPTIONAL"  
> flag to munmap_flags() call. Don't know where i might be going wrong  
> in SQLite.
>
> As you suggested, I wrote a small application to check if this  
> works. Fortunately it worked as desired (as given below).
>
> MAP_NOINIT
> When specified, the POSIX requirement that the memory be zeroed is  
> relaxed. The physical memory being used for this allocation must  
> have been previously freed with UNMAP_INIT_OPTIONAL for this flag to  
> have any effect.
>
> _
> int main(int argc, char *argv[]) {
>printf("Welcome to the QNX Momentics IDE\n");
>
>//Open a file
>int fd = open("/tmp/mmaptest",O_RDWR|O_CREAT, 0777);
>
>//Truncate the file
>int size = ftruncate(fd, 2048);
>
>const char buff[]="Testing the mmap API";
>write(fd,buff,sizeof(buff));
>
>//Mapping the disk file to memory
>void *pMem = mmap(0, 2048, PROT_READ|PROT_WRITE,MAP_SHARED, fd, 0);
>
>//Check the mmapped memory contents
>printf("Memory Mapped Data: %s\n", pMem);
>
>//Set all mmapped locations to 'A'
>memset(pMem,'A',2048);
>
>//Unmap the memory
>//munmap(pMem, 2048);
>munmap_flags(pMem, 2048, UNMAP_INIT_OPTIONAL);
>
>//Buffer to read disk data
>char disk_read[2048];
>
>//Read the data from the file to check if its synced
>read(fd,disk_read,sizeof(disk_read));
>printf("Reading disk file data:%s\n",disk_read);
>
>//Resetting the contents of disk file
>size = ftruncate(fd, 0);
>close(fd);
>
>//Open the file again to check to re-map the memory
>fd = open("/tmp/mmaptest",O_RDWR, 0777);
>size = ftruncate(fd, 2048);
>
>//Map the memory region again
>//int pMem1 = mmap(0, 2048, PROT_READ|PROT_WRITE,MAP_SHARED, fd,  
> 0);
>int pMem1 = mmap(0, 2048, PROT_READ|PROT_WRITE,MAP_SHARED| 
> MAP_NOINIT, fd, 0);
>
>//Print the re-mapped region
>printf("New Memory Mapped Data: %s\n", pMem1);
>
>//Read the disk contents again to check if "MAP_NOINIT" works
>read(fd,disk_read,sizeof(disk_read));
>printf("Disk file data during re-map: %s\n", disk_read);
>
>//Close the disk file
>close(fd);
>
>return EXIT_SUCCESS;
> }
>
> ---
> Output:
>
> Welcome to the QNX Momentics IDE
> Memory Mapped Data: Testing the mmap API
> Reading disk file data:AA [2048 times]
> New Memory Mapped Data: A [2048 times]
> Disk file data during re-map:
> ---
>
> Also found that the mmapped data is written back to disk file on  
> munmap()/munmap_flags() call.
>
> Maybe i need to look into Sqlite amalgamation file in detail to  
> analyze what's happening here during unmapping of memory.
> I believe we can use these flags und

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

[Black, Michael (IS)]

I'm not sure but I suspect sqlite is not calling unmap before extending the 
area.
That would explain why it still gets zeroed out even with the flags.
 
Put a break point in the unixShmUnmap call and see if it gets called before 
mmap.
 
May just need some QNX logic that says "if we're extending an area unmap it 
first with flags".
 
I think the mmap should honor the NOINIT flag when extending an area but 
apparently it doesn't -- only unmap can set the flag to make it honor it.  QNX 
must be about the only one that does this.
 
QNX mmap claims to be POSIX 1003.1 compliant but I don't see this behavior 
defined in there.
 
The only zero-fill reference I see in POSIX is 
"The system shall always zero-fill any partial page at the end of an object"
which doesn't fit this behavior at all.
 
Perhaps you should report this as a bug or non-desirable/non-compliant behavior 
compared to every other OS in the world (and the POSIX standard which doesn't 
call for this behavior).
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Raj, Praveen
Sent: Fri 10/15/2010 9:55 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS



Hi Michael,

Yes I added the "MAP_NOINIT" to mmap() and "UNMAP_INIT_OPTIONAL" flag to 
munmap_flags() call. Don't know where i might be going wrong in SQLite.

As you suggested, I wrote a small application to check if this works. 
Fortunately it worked as desired (as given below).

MAP_NOINIT
When specified, the POSIX requirement that the memory be zeroed is relaxed. The 
physical memory being used for this allocation must have been previously freed 
with UNMAP_INIT_OPTIONAL for this flag to have any effect.

_
int main(int argc, char *argv[]) {
printf("Welcome to the QNX Momentics IDE\n");

//Open a file
int fd = open("/tmp/mmaptest",O_RDWR|O_CREAT, 0777);

//Truncate the file
int size = ftruncate(fd, 2048);

const char buff[]="Testing the mmap API";
write(fd,buff,sizeof(buff));

//Mapping the disk file to memory
void *pMem = mmap(0, 2048, PROT_READ|PROT_WRITE,MAP_SHARED, fd, 0);

//Check the mmapped memory contents
printf("Memory Mapped Data: %s\n", pMem);

//Set all mmapped locations to 'A'
memset(pMem,'A',2048);

//Unmap the memory
//munmap(pMem, 2048);
munmap_flags(pMem, 2048, UNMAP_INIT_OPTIONAL);

//Buffer to read disk data
char disk_read[2048];

//Read the data from the file to check if its synced
read(fd,disk_read,sizeof(disk_read));
printf("Reading disk file data:%s\n",disk_read);

//Resetting the contents of disk file
size = ftruncate(fd, 0);
close(fd);

//Open the file again to check to re-map the memory
fd = open("/tmp/mmaptest",O_RDWR, 0777);
size = ftruncate(fd, 2048);

//Map the memory region again
//int pMem1 = mmap(0, 2048, PROT_READ|PROT_WRITE,MAP_SHARED, fd, 0);
int pMem1 = mmap(0, 2048, PROT_READ|PROT_WRITE,MAP_SHARED|MAP_NOINIT, fd, 
0);

//Print the re-mapped region
printf("New Memory Mapped Data: %s\n", pMem1);

//Read the disk contents again to check if "MAP_NOINIT" works
read(fd,disk_read,sizeof(disk_read));
printf("Disk file data during re-map: %s\n", disk_read);

//Close the disk file
close(fd);

return EXIT_SUCCESS;
}

---
Output:

Welcome to the QNX Momentics IDE
Memory Mapped Data: Testing the mmap API
Reading disk file data:AA [2048 times]
New Memory Mapped Data: A [2048 times]
Disk file data during re-map:
---

Also found that the mmapped data is written back to disk file on 
munmap()/munmap_flags() call.

Maybe i need to look into Sqlite amalgamation file in detail to analyze what's 
happening here during unmapping of memory.
I believe we can use these flags under all scenarios without any 
pre-conditions. Not sure if POSIX (on QNX) have some limitations on their usage.

Thanks,
Praveen
__

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Black, Michael (IS)
Sent: Thursday, October 14, 2010 9:22 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

Did you also add the MAP_NOINIT to the mmap() call?

It sounds like exactly the behavior you're seeing.

Did you try writing  a stand-alone app to test this idea?

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman

Re: [sqlite] EXTERNAL:Re: How to optimize a multi-condition query

[Pavel Ivanov]

There are several conditions that should be met for walking the index
to be faster than walking the table.

1) The most important one: index b-tree structure should be organized
in such way that one can walk directly from one leaf to another thus
traversing all leaves without touching interior pages.
2) All selected data should exist in the index or there's a condition
with good selectiveness containing only data in the index.
3) Index size should be much smaller than table size.
4) Either both index and table pages should be in the cache or table
pages should be out of the cache. If table pages are in the cache and
index pages are not then probably walking the table will be faster.

I'm afraid that the first condition is a show stopper and it's not
implemented in SQLite.


Pavel

On Fri, Oct 15, 2010 at 10:18 AM, Black, Michael (IS)
 wrote:
> I see the difference now...
>
> So I take it that it's faster just to walk the table once rather than walk 
> the index?
>
> Couldn't you just walk the index once?  Smaller data space (quite likely), 
> better caching?  It might be a wash or worse with the potential of having to 
> retreive other fields from the table I suppose.  But in the case where you're 
> just retrieving the same fields as the index wouldn't that generally be 
> faster?
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov
> Sent: Fri 10/15/2010 8:27 AM
> To: General Discussion of SQLite Database
> Subject: EXTERNAL:Re: [sqlite] How to optimize a multi-condition query
>
>
>
>> sqlite> create table c(achr char,bchr char);
>> sqlite> create index c_chr on c(achr,bchr);
>> sqlite> explain query plan select achr,bchr from c where achr=bchr;
>> 0|0|TABLE c
>>
>> Why no use of the index in this case?
>
> How do you think it should be used here? It's not that rows with the
> same values of achr and bchr stored together in the index - they are
> spread all over the place. And thus using index it will have to make
> full scan of it which is less efficient than full scan of the table.
> Or should I say it's more efficient than full scan of the table in
> very rare situations, so that I'm not sure if SQLite implements it at
> all.
>
>
> Pavel
>
>
>
>
> ___
> 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] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

[Raj, Praveen]

Hi Michael,

Yes I added the "MAP_NOINIT" to mmap() and "UNMAP_INIT_OPTIONAL" flag to 
munmap_flags() call. Don't know where i might be going wrong in SQLite.

As you suggested, I wrote a small application to check if this works. 
Fortunately it worked as desired (as given below).

MAP_NOINIT
When specified, the POSIX requirement that the memory be zeroed is relaxed. The 
physical memory being used for this allocation must have been previously freed 
with UNMAP_INIT_OPTIONAL for this flag to have any effect.

_
int main(int argc, char *argv[]) {
printf("Welcome to the QNX Momentics IDE\n");

//Open a file
int fd = open("/tmp/mmaptest",O_RDWR|O_CREAT, 0777);

//Truncate the file
int size = ftruncate(fd, 2048);

const char buff[]="Testing the mmap API";
write(fd,buff,sizeof(buff));

//Mapping the disk file to memory
void *pMem = mmap(0, 2048, PROT_READ|PROT_WRITE,MAP_SHARED, fd, 0);

//Check the mmapped memory contents
printf("Memory Mapped Data: %s\n", pMem);

//Set all mmapped locations to 'A'
memset(pMem,'A',2048);

//Unmap the memory
//munmap(pMem, 2048);
munmap_flags(pMem, 2048, UNMAP_INIT_OPTIONAL);

//Buffer to read disk data
char disk_read[2048];

//Read the data from the file to check if its synced
read(fd,disk_read,sizeof(disk_read));
printf("Reading disk file data:%s\n",disk_read);

//Resetting the contents of disk file
size = ftruncate(fd, 0);
close(fd);

//Open the file again to check to re-map the memory 
fd = open("/tmp/mmaptest",O_RDWR, 0777);
size = ftruncate(fd, 2048);

//Map the memory region again
//int pMem1 = mmap(0, 2048, PROT_READ|PROT_WRITE,MAP_SHARED, fd, 0);
int pMem1 = mmap(0, 2048, PROT_READ|PROT_WRITE,MAP_SHARED|MAP_NOINIT, fd, 
0);

//Print the re-mapped region
printf("New Memory Mapped Data: %s\n", pMem1);

//Read the disk contents again to check if "MAP_NOINIT" works
read(fd,disk_read,sizeof(disk_read));
printf("Disk file data during re-map: %s\n", disk_read);

//Close the disk file
close(fd);

return EXIT_SUCCESS;
}

---
Output:

Welcome to the QNX Momentics IDE
Memory Mapped Data: Testing the mmap API
Reading disk file data:AA [2048 times]
New Memory Mapped Data: A [2048 times]
Disk file data during re-map:
---

Also found that the mmapped data is written back to disk file on 
munmap()/munmap_flags() call.

Maybe i need to look into Sqlite amalgamation file in detail to analyze what's 
happening here during unmapping of memory. 
I believe we can use these flags under all scenarios without any 
pre-conditions. Not sure if POSIX (on QNX) have some limitations on their usage.

Thanks,
Praveen
__

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Black, Michael (IS)
Sent: Thursday, October 14, 2010 9:22 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

Did you also add the MAP_NOINIT to the mmap() call?
 
It sounds like exactly the behavior you're seeing. 
 
Did you try writing  a stand-alone app to test this idea?
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Raj, Praveen
Sent: Thu 10/14/2010 10:44 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS



Hi Micheal,

Thanks Dan and Michael for all your inputs.

I tried this approach as well, but didn't find any success.
During unmapping i used the API munmap_flags() with "UNMAP_INIT_OPTIONAL" flag 
to avoid the zero initialization during the next mmaping.

Another thought I have here is that the old mmapped regions may not be 
initialized with zeros, but instead the regions are getting synced with the 
data in disk file (which is full of zeros). Not sure if can happen with mmap() 
API though?


Thanks,
Praveen

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Black, Michael (IS)
Sent: Thursday, October 14, 2010 5:21 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

I sent this before...have you tried this?

According to the QNX mmap page
http://www.qnx.com/developers/docs/6.3.0SP3/neutrino/lib_ref/m/mmap.html 

Re: [sqlite] How to optimize a multi-condition query

[Igor Tandetnik]

Hilmar Berger  wrote:
> I used EXPLAIN QUERY PLAN on the query and it looks like it does not use
> any index on b at all, only if I use hardcoded conditions like b > 0.
> 
> It appears that the real problem is that SQlite does not use indices for
> both tables

For your problem, there's no way to use indices on both tables. An index is 
only helpful if it allows one to look at only some, but not all, rows in the 
table. In your query, one has to look at all records in A and find suitable 
matching records in B, or else look at all records in B and find matching 
records in A. You have to look at all records in at least one table, there's no 
way around that.
-- 
Igor Tandetnik

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

Re: [sqlite] How to optimize a multi-condition query

[Igor Tandetnik]

Hilmar Berger  wrote:
> Indexes has been created for all fields in A and B (e.g. create index
> name on A(chr, start, stop, strand));
> 
> The query is as follows:
> 
> select * from
> a, b
> where a.chr = b.chr and a.strand = b.strand and a.start <= b.start
> and  a.stop >= b.stop  and b.start <= a.stop and a.start <= b.stop ;

Drop those indexes you created, change the query to

select * from a, b
 where a.chr = b.chr and a.strand = b.strand
 and b.start between a.start and a.stop
 and b.stop <= a.stop;

and create a single index on b(chr, strand, start) or b(chr, strand, start, 
stop), listing fields in this exact order  (adding stop to the index may or may 
not result in marginal performance improvement - experiment).
-- 
Igor Tandetnik

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

Re: [sqlite] How to optimize a multi-condition query

[Igor Tandetnik]

Black, Michael (IS)  wrote:
> I love simple examples like this can help people with understanding 
> things...so I tried this which I thought would do what Hilmar
> wants...but alaswhat concept am I missing? 
> 
> SQLite version 3.7.2
> sqlite> create table c(achr char,bchr char);
> sqlite> create index c_chr on c(achr,bchr);
> sqlite> explain query plan select achr,bchr from c where achr=bchr;
> 0|0|TABLE c
> 
> Why no use of the index in this case?

Try this:

select t.achr, t.bchr
from (select distinct achr from c) as alphabet
join c as t on (t.achr=alphabet.achr and t.bchr=alphabet.achr);

Under some circumstances - achr only has a small number of distinct values, the 
number of rows where achr=bchr is a small percentage of all rows in the table - 
this may be significantly faster than full table scan.
-- 
Igor Tandetnik

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

Re: [sqlite] Multiple prepared statements

[Andrew Davison]

Yup, my bad. Fixed.

On 16/10/2010 12:03 AM, Andrew Davison wrote:
> On 15/10/2010 11:49 PM, Pavel Ivanov wrote:
>>> Now I decide that I want a second type of insert, so I try to use a
>>> prepared statement for that as well. However it always fails. As long as
>>> the other prepared statement is hanging round I can't prepare a new one.
>>> Does this seem right or am I really soing something wrong?
>>
>> You are doing something wrong. I always keep like 10 prepared
>> statements for each connection and it works perfectly. I work in C++
>> so it's a direct SQLite feature. That's what prepared statements are
>> for.
>>
>
> That's what I thought, so something is wrong.
> Thanks.
>
>
> ___
> 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] EXTERNAL:Re: How to optimize a multi-condition query

[Black, Michael (IS)]

I see the difference now...
 
So I take it that it's faster just to walk the table once rather than walk the 
index?
 
Couldn't you just walk the index once?  Smaller data space (quite likely), 
better caching?  It might be a wash or worse with the potential of having to 
retreive other fields from the table I suppose.  But in the case where you're 
just retrieving the same fields as the index wouldn't that generally be faster?
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov
Sent: Fri 10/15/2010 8:27 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] How to optimize a multi-condition query



> sqlite> create table c(achr char,bchr char);
> sqlite> create index c_chr on c(achr,bchr);
> sqlite> explain query plan select achr,bchr from c where achr=bchr;
> 0|0|TABLE c
>
> Why no use of the index in this case?

How do you think it should be used here? It's not that rows with the
same values of achr and bchr stored together in the index - they are
spread all over the place. And thus using index it will have to make
full scan of it which is less efficient than full scan of the table.
Or should I say it's more efficient than full scan of the table in
very rare situations, so that I'm not sure if SQLite implements it at
all.


Pavel



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

Re: [sqlite] Multiple prepared statements

[Andrew Davison]

On 15/10/2010 11:49 PM, Pavel Ivanov wrote:
>> Now I decide that I want a second type of insert, so I try to use a
>> prepared statement for that as well. However it always fails. As long as
>> the other prepared statement is hanging round I can't prepare a new one.
>> Does this seem right or am I really soing something wrong?
>
> You are doing something wrong. I always keep like 10 prepared
> statements for each connection and it works perfectly. I work in C++
> so it's a direct SQLite feature. That's what prepared statements are
> for.
>

That's what I thought, so something is wrong.
Thanks.


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

Re: [sqlite] Multiple prepared statements

[Pavel Ivanov]

> Now I decide that I want a second type of insert, so I try to use a
> prepared statement for that as well. However it always fails. As long as
> the other prepared statement is hanging round I can't prepare a new one.
> Does this seem right or am I really soing something wrong?

You are doing something wrong. I always keep like 10 prepared
statements for each connection and it works perfectly. I work in C++
so it's a direct SQLite feature. That's what prepared statements are
for.


Pavel

On Fri, Oct 15, 2010 at 9:43 AM, Andrew Davison
 wrote:
> In my database I do lots of inserts, of exactly the same nature so I use
> a prepared statement, which I cache, always reseting after use. Works fine.
>
> Now I decide that I want a second type of insert, so I try to use a
> prepared statement for that as well. However it always fails. As long as
> the other prepared statement is hanging round I can't prepare a new one.
> Does this seem right or am I really soing something wrong?
>
> Can I not have multiple prepared statements created?
>
> Regards.
>
>
> ___
> 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] Multiple prepared statements

[P Kishor]

On Fri, Oct 15, 2010 at 8:43 AM, Andrew Davison
 wrote:
> In my database I do lots of inserts, of exactly the same nature so I use
> a prepared statement, which I cache, always reseting after use. Works fine.
>
> Now I decide that I want a second type of insert, so I try to use a
> prepared statement for that as well. However it always fails. As long as
> the other prepared statement is hanging round I can't prepare a new one.
> Does this seem right or am I really soing something wrong?
>

Which language?

> Can I not have multiple prepared statements created?
>

At least with Perl DBI I can have as many prepared statements as I
want or care. Don't know if that is a Perl capability or sqlite
capability.


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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multiple prepared statements

[Andrew Davison]

In my database I do lots of inserts, of exactly the same nature so I use 
a prepared statement, which I cache, always reseting after use. Works fine.

Now I decide that I want a second type of insert, so I try to use a 
prepared statement for that as well. However it always fails. As long as 
the other prepared statement is hanging round I can't prepare a new one. 
Does this seem right or am I really soing something wrong?

Can I not have multiple prepared statements created?

Regards.


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

Re: [sqlite] How to optimize a multi-condition query

[Pavel Ivanov]

> sqlite> create table c(achr char,bchr char);
> sqlite> create index c_chr on c(achr,bchr);
> sqlite> explain query plan select achr,bchr from c where achr=bchr;
> 0|0|TABLE c
>
> Why no use of the index in this case?

How do you think it should be used here? It's not that rows with the
same values of achr and bchr stored together in the index - they are
spread all over the place. And thus using index it will have to make
full scan of it which is less efficient than full scan of the table.
Or should I say it's more efficient than full scan of the table in
very rare situations, so that I'm not sure if SQLite implements it at
all.


Pavel

On Fri, Oct 15, 2010 at 8:34 AM, Black, Michael (IS)
 wrote:
> I love simple examples like this can help people with understanding 
> things...so I tried this which I thought would do what Hilmar wants...but 
> alaswhat concept am I missing?
>
> SQLite version 3.7.2
> sqlite> create table c(achr char,bchr char);
> sqlite> create index c_chr on c(achr,bchr);
> sqlite> explain query plan select achr,bchr from c where achr=bchr;
> 0|0|TABLE c
>
> Why no use of the index in this case?
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Simon Davies
> Sent: Fri 10/15/2010 5:16 AM
> To: General Discussion of SQLite Database
> Subject: EXTERNAL:Re: [sqlite] How to optimize a multi-condition query
>
>
>
> On 15 October 2010 10:43, Hilmar Berger  wrote:
>>  Thanks !
>>
>> However, I tried what you suggested and there was no change.
>>
>> I used EXPLAIN QUERY PLAN on the query and it looks like it does not use
>> any index on b at all, only if I use hardcoded conditions like b > 0.
>>
>> It appears that the real problem is that SQlite does not use indices for
>> both tables, e.g.:
>>
>> CREATE INDEX a_chr on a(chr)
>> CREATE INDEX b_chr on b(chr)
>>
>> explain query plan
>>   select a.chr, b.chr
>>     from b, a
>>     where b.chr = a.chr;
>>
>> Output:
>> 0    0    TABLE b
>> 1    1    TABLE a WITH INDEX a_chr
>
> There is no benefit in using an index on b for this query.
>
> Change the query to:
>  select a.chr, b.chr
>    from b, a
>    where b.chr = a.chr and b.chr>0;
>
> and the query plan becomes:
> 0|0|TABLE b WITH INDEX b_chr
> 1|1|TABLE a WITH INDEX a_chr
>
>>
>>
>> There is an example on how to use multiple indices on the same table
>> here: http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning
>> However, I'm not sure how to extend this to joined tables.
>>
>> Thanks !
>>
>> Best regards,
>> Hilmar
>>
>
> Regarding
>
> "select * from
>    a, b
>    where a.chr = b.chr and a.strand = b.strand and
> a.start <= b.start and  a.stop >= b.stop  and
> b.start <= a.stop and a.start <= b.stop ;"
>
> surely the last 2 conditions are redundant assuming a.start and b.start
>
> Regards,
> Simon
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to optimize a multi-condition query

[Martin.Engelschalk]

  Hi,

the condition in your query

select achr,bchr from c where achr=bchr

involves 2 columns of the table. In this case, an index is useless.
If you do

select achr,bchr from c where achr='foo'

then the index will be used.

Martin



Am 15.10.2010 15:09, schrieb Black, Michael (IS):
> Ok then... I added 67,600 records like this and still no index use.
>
> SQLite version 3.7.2
> sqlite>  select count(*) from c;
> 67600
> sqlite>  explain query plan select achr,bchr from c where achr=bchr;
> 0|0|TABLE c
> sqlite>  create index c_chr on c(achr,bchr);
> sqlite>  explain query plan select achr,bchr from c where achr=bchr;
> 0|0|TABLE c
>
> Here's my record add:
> #include
> #include
> #include "sqlite3.h"
> int main()
> {
>   sqlite3 *db;
>   char *errmsg=NULL;
>   int rc;
>   int i,j,k;
>   sqlite3_open("test.db",&db);
>   rc=sqlite3_exec(db, "CREATE TABLE c (achr char, bchr 
> char)",NULL,NULL,&errmsg);
>   if (rc != SQLITE_OK) {
>puts(errmsg);
>sqlite3_free(errmsg);
>   }
>   sqlite3_exec(db,"BEGIN",NULL,NULL,&errmsg);
>   for(k=0;k<100;k++) {
>for(i=0;i<26;i++) {
> char sql[4096];
> for(j=0;j<26;j++) {
>  sprintf(sql,"INSERT INTO c VALUES ('%c','%c')",'a'+i,'a'+j);
>  rc=sqlite3_exec(db, sql,NULL,NULL,&errmsg);
>  if (rc != SQLITE_OK) {
>   puts(sql);
>   puts(errmsg);
>   sqlite3_free(errmsg);
>   exit(-1);
>  }
> }
>}
>   }
>   sqlite3_exec(db,"COMMIT",NULL,NULL,&errmsg);
>   sqlite3_close(db);
>   return 0;
> }
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of luuk34
> Sent: Fri 10/15/2010 7:40 AM
> To: General Discussion of SQLite Database
> Subject: EXTERNAL:Re: [sqlite] How to optimize a multi-condition query
>
>
>
>
>On 15-10-10 14:34, Black, Michael (IS) wrote:
>> I love simple examples like this can help people with understanding 
>> things...so I tried this which I thought would do what Hilmar wants...but 
>> alaswhat concept am I missing?
>>
>> SQLite version 3.7.2
>> sqlite>   create table c(achr char,bchr char);
>> sqlite>   create index c_chr on c(achr,bchr);
>> sqlite>   explain query plan select achr,bchr from c where achr=bchr;
>> 0|0|TABLE c
>>
>> Why no use of the index in this case?
>>
> because there are no records in the database,
> so its quicker to read just all records,
> than to read all record in the order of the index...
>
> --
> Luuk
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to optimize a multi-condition query

[Black, Michael (IS)]

Ok then... I added 67,600 records like this and still no index use.  
 
SQLite version 3.7.2
sqlite> select count(*) from c;
67600
sqlite> explain query plan select achr,bchr from c where achr=bchr;
0|0|TABLE c
sqlite> create index c_chr on c(achr,bchr);
sqlite> explain query plan select achr,bchr from c where achr=bchr;
0|0|TABLE c
 
Here's my record add:
#include 
#include 
#include "sqlite3.h"
int main()
{
 sqlite3 *db;
 char *errmsg=NULL;
 int rc;
 int i,j,k;
 sqlite3_open("test.db",&db);
 rc=sqlite3_exec(db, "CREATE TABLE c (achr char, bchr char)",NULL,NULL,&errmsg);
 if (rc != SQLITE_OK) {
  puts(errmsg);
  sqlite3_free(errmsg);
 }
 sqlite3_exec(db,"BEGIN",NULL,NULL,&errmsg);
 for(k=0;k<100;k++) {
  for(i=0;i<26;i++) {
   char sql[4096];
   for(j=0;j<26;j++) {
sprintf(sql,"INSERT INTO c VALUES ('%c','%c')",'a'+i,'a'+j);
rc=sqlite3_exec(db, sql,NULL,NULL,&errmsg);
if (rc != SQLITE_OK) {
 puts(sql);
 puts(errmsg);
 sqlite3_free(errmsg);
 exit(-1);
}
   }
  }
 }
 sqlite3_exec(db,"COMMIT",NULL,NULL,&errmsg);
 sqlite3_close(db);
 return 0;
}

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of luuk34
Sent: Fri 10/15/2010 7:40 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] How to optimize a multi-condition query




  On 15-10-10 14:34, Black, Michael (IS) wrote:
> I love simple examples like this can help people with understanding 
> things...so I tried this which I thought would do what Hilmar wants...but 
> alaswhat concept am I missing?
>
> SQLite version 3.7.2
> sqlite>  create table c(achr char,bchr char);
> sqlite>  create index c_chr on c(achr,bchr);
> sqlite>  explain query plan select achr,bchr from c where achr=bchr;
> 0|0|TABLE c
>
> Why no use of the index in this case?
>

because there are no records in the database,
so its quicker to read just all records,
than to read all record in the order of the index...

--
Luuk
___
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] How to optimize a multi-condition query

[luuk34]

  On 15-10-10 14:34, Black, Michael (IS) wrote:
> I love simple examples like this can help people with understanding 
> things...so I tried this which I thought would do what Hilmar wants...but 
> alaswhat concept am I missing?
>
> SQLite version 3.7.2
> sqlite>  create table c(achr char,bchr char);
> sqlite>  create index c_chr on c(achr,bchr);
> sqlite>  explain query plan select achr,bchr from c where achr=bchr;
> 0|0|TABLE c
>
> Why no use of the index in this case?
>

because there are no records in the database,
so its quicker to read just all records,
than to read all record in the order of the index...

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

Re: [sqlite] How to optimize a multi-condition query

[Black, Michael (IS)]

I love simple examples like this can help people with understanding things...so 
I tried this which I thought would do what Hilmar wants...but alaswhat 
concept am I missing?  
 
SQLite version 3.7.2
sqlite> create table c(achr char,bchr char);
sqlite> create index c_chr on c(achr,bchr);
sqlite> explain query plan select achr,bchr from c where achr=bchr;
0|0|TABLE c
 
Why no use of the index in this case?  
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Simon Davies
Sent: Fri 10/15/2010 5:16 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] How to optimize a multi-condition query



On 15 October 2010 10:43, Hilmar Berger  wrote:
>  Thanks !
>
> However, I tried what you suggested and there was no change.
>
> I used EXPLAIN QUERY PLAN on the query and it looks like it does not use
> any index on b at all, only if I use hardcoded conditions like b > 0.
>
> It appears that the real problem is that SQlite does not use indices for
> both tables, e.g.:
>
> CREATE INDEX a_chr on a(chr)
> CREATE INDEX b_chr on b(chr)
>
> explain query plan
>   select a.chr, b.chr
> from b, a
> where b.chr = a.chr;
>
> Output:
> 00TABLE b
> 11TABLE a WITH INDEX a_chr

There is no benefit in using an index on b for this query.

Change the query to:
  select a.chr, b.chr
from b, a
where b.chr = a.chr and b.chr>0;

and the query plan becomes:
0|0|TABLE b WITH INDEX b_chr
1|1|TABLE a WITH INDEX a_chr

>
>
> There is an example on how to use multiple indices on the same table
> here: http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning
> However, I'm not sure how to extend this to joined tables.
>
> Thanks !
>
> Best regards,
> Hilmar
>

Regarding

"select * from
a, b
where a.chr = b.chr and a.strand = b.strand and
a.start <= b.start and  a.stop >= b.stop  and
b.start <= a.stop and a.start <= b.stop ;"

surely the last 2 conditions are redundant assuming a.starthttp://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] How can I get the first 10 rows in sqlite db

[Simon Davies]

On 14 October 2010 16:42, Kavita Raghunathan
 wrote:
> Hello,
> I’ve been adding and deleting rows from the sqlite database. Now the primary 
> ID is non-sequential.
>
>
>  1.  How do I get the nth entry in the database
>  2.  AND, How do I get the first n rows from the database ?

http://old.nabble.com/Nth-row-of-on-sqlite-DB-to29963848.html

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

Re: [sqlite] How to optimize a multi-condition query

[Simon Davies]

On 15 October 2010 10:43, Hilmar Berger  wrote:
>  Thanks !
>
> However, I tried what you suggested and there was no change.
>
> I used EXPLAIN QUERY PLAN on the query and it looks like it does not use
> any index on b at all, only if I use hardcoded conditions like b > 0.
>
> It appears that the real problem is that SQlite does not use indices for
> both tables, e.g.:
>
> CREATE INDEX a_chr on a(chr)
> CREATE INDEX b_chr on b(chr)
>
> explain query plan
>   select a.chr, b.chr
>     from b, a
>     where b.chr = a.chr;
>
> Output:
> 0    0    TABLE b
> 1    1    TABLE a WITH INDEX a_chr

There is no benefit in using an index on b for this query.

Change the query to:
  select a.chr, b.chr
from b, a
where b.chr = a.chr and b.chr>0;

and the query plan becomes:
0|0|TABLE b WITH INDEX b_chr
1|1|TABLE a WITH INDEX a_chr

>
>
> There is an example on how to use multiple indices on the same table
> here: http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning
> However, I'm not sure how to extend this to joined tables.
>
> Thanks !
>
> Best regards,
> Hilmar
>

Regarding

"select * from
a, b
where a.chr = b.chr and a.strand = b.strand and
a.start <= b.start and  a.stop >= b.stop  and
b.start <= a.stop and a.start <= b.stop ;"

surely the last 2 conditions are redundant assuming a.starthttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How can I get the first 10 rows in sqlite db

[Kavita Raghunathan]

Hello,
I’ve been adding and deleting rows from the sqlite database. Now the primary ID 
is non-sequential.


 1.  How do I get the nth entry in the database
 2.  AND, How do I get the first n rows from the database ?


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

Re: [sqlite] How to optimize a multi-condition query

[Hilmar Berger]

  Thanks !

However, I tried what you suggested and there was no change.

I used EXPLAIN QUERY PLAN on the query and it looks like it does not use 
any index on b at all, only if I use hardcoded conditions like b > 0.

It appears that the real problem is that SQlite does not use indices for 
both tables, e.g.:

CREATE INDEX a_chr on a(chr)
CREATE INDEX b_chr on b(chr)

explain query plan
   select a.chr, b.chr
 from b, a
 where b.chr = a.chr;

Output:
00TABLE b
11TABLE a WITH INDEX a_chr


There is an example on how to use multiple indices on the same table 
here: http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning
However, I'm not sure how to extend this to joined tables.

Thanks !

Best regards,
Hilmar

El 10/14/2010 7:12 PM, Simon Slavin escribió:
> On 14 Oct 2010, at 5:46pm, Hilmar Berger wrote:
>
>> The query is as follows:
>>
>> select * from
>>  a, b
>>  where a.chr = b.chr and a.strand = b.strand and a.start<= b.start
>> and  a.stop>= b.stop  and b.start<= a.stop and a.start<= b.stop ;
>> Indexes has been created for all fields in A and B (e.g.
>> create index name on A(chr, start, stop, strand));
> I not certain from your phrasing, but this may not do what you think.  It is 
> different matter to do
>
> create index Achr on A (chr)
> create index Astart on A (start)
> create index Astop on A (stop)
> ...
>
> to what you did above.  One creates one index on the sequence of four 
> variables, the other creates four indexes each on one variable.
>
> However, to make this SELECT go fast,
> CREAT INDEX Bkey ON B (chr,strand)
> CREAT INDEX Bstart ON B (start)
> CREAT INDEX Bstop ON B (stop)
>
> May help.
>
> Also I recommend doing this:
>
> http://www.sqlite.org/lang_analyze.html
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many many tables... performance wise?

[Simon Slavin]

On 15 Oct 2010, at 7:36am, Andrew Davison wrote:

> What's the take on having hundreds of tables in a database?

Generally not.  A database should be designed.  By a human.  I don't know about 
you, but I can't hold hundreds of schema in my head at the same time.  Rather 
than have two or more tables with the same schema, it's usually better to have 
one table with an extra column to mark what kind of data each record is.  There 
are exceptions to this but it's a good design principle.

>  Any likely 
> performance problems apart from first time a table is accessed? Does it 
> affect the cache?

SQLite keeps data from each table (and each index) in different pages of 
filespace.  So each time you switch from one table to another you're switching 
to another page of the file.  And if you have 100 tables in a file you have 200 
pages of space, reserved for only one kind of data that can't be used for 
anything else.  That's an argument for fewer but bigger tables.

I understand why you asked the question but I think that an SQLite newbie can 
only figure it out from experience.  My advice is to design stuff whatever way 
makes it simplest for you to do your programming.  Worry about performance only 
if it turns out to be too slow or too unwieldy or annoying in some other way.

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

Re: [sqlite] GUI for data entry

[Graham Smith]

Tom

> You don't need to create special tables for data entry. You can create views 
> instead, >coupled with "instead of" triggers.

> I'll see if I can put together some SQL with a few examples of how a view 
> updates several >related tables.

As the OP, I would be very interested in this. I did try using Views,
as there is no forms option in SQLiteManager, but the "add" button is
greyed out when using a View.

> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml

This link is currently giving an internal server error.

Thanks.

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