Re: [sqlite] This query seems clumsy - can anyone do better?

[Kai Peters]

On Thu, 3 Feb 2011 00:21:56 -0500, Igor Tandetnik wrote:
>
> select SUG.UserGrpID, SUG.GroupName,
>   coalesce(SUM.A_Link, 0), coalesce(SUM.B_Link, 0) from SecUserGroups SUG 
> left outer join
> SecUserMembers SUM on (SUM.UserGrpID = SUG.UserGrpID and SUM.UserID = 
> 'Jane Doe');

Brilliant - just what this newbie was hoping for - thanks!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[Patko Sándor]

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

Re: [sqlite] This query seems clumsy - can anyone do better?

[Igor Tandetnik]

Kai Peters  wrote:
> the query below (ready to paste & run in the editor of your choice) works but 
> I have a strong
> suspicion that it might be optimizable:
>
> selectSUG.UserGrpID, SUG.GroupName, SUM.A_Link, SUM.B_Link
> fromSecUserGroups SUG
> left outer join SecUserMembers SUM on SUM.UserGrpID = SUG.UserGrpID
> whereSUM.UserID = 'Jane Doe'
> union
> selectSUG.UserGrpID, SUG.GroupName, 0, 0
> fromSecUserGroups SUG
> whereUserGrpID not in (select UserGrpID from SecUserMembers where
> UserID = 'Jane Doe')

select SUG.UserGrpID, SUG.GroupName,
coalesce(SUM.A_Link, 0), coalesce(SUM.B_Link, 0)
from SecUserGroups SUG left outer join SecUserMembers SUM
on (SUM.UserGrpID = SUG.UserGrpID and SUM.UserID = 'Jane Doe');

-- 
Igor Tandetnik

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

[sqlite] This query seems clumsy - can anyone do better?

[Kai Peters]

Hi,

the query below (ready to paste & run in the editor of your choice) works but I 
have a strong 
suspicion that it might be optimizable:


DROP TABLE IF EXISTS SecUserMembers;
DROP TABLE IF EXISTS SecUserGroups;

CREATE TABLE SecUserMembers(
UserGrpID  integer NOT NULL,
UserID textNOT NULL,
Active integer NOT NULL,
A_Link integer NOT NULL,
B_Link integer NOT NULL
);

CREATE TABLE SecUserGroups(
UserGrpID  integer NOT NULL PRIMARY KEY AUTOINCREMENT,
GroupName  textNOT NULL
);

insert into SecUserGroups values (null, 'Alpha Group');
insert into SecUserGroups values (null, 'Beta Group');
insert into SecUserGroups values (null, 'Gamma Group');
insert into SecUserGroups values (null, 'Delta Group');

insert into SecUserMembers values (1, 'John Doe', 1, 0, 1);
insert into SecUserMembers values (2, 'John Doe', 1, 1, 0);
insert into SecUserMembers values (3, 'John Doe', 1, 1, 1);
insert into SecUserMembers values (1, 'Jane Doe', 1, 1, 0);
insert into SecUserMembers values (3, 'Jane Doe', 1, 0, 1);


selectSUG.UserGrpID, SUG.GroupName, SUM.A_Link, SUM.B_Link   
fromSecUserGroups SUG
left outer join SecUserMembers SUM on SUM.UserGrpID = SUG.UserGrpID
whereSUM.UserID = 'Jane Doe'
union
selectSUG.UserGrpID, SUG.GroupName, 0, 0
fromSecUserGroups SUG
whereUserGrpID not in (select UserGrpID from SecUserMembers where
UserID = 'Jane Doe')

Thanks in advance for any pointers,
Kai
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Will opening database in ReadOnly mode result in better query time

[Pavel Ivanov]

> Thanks. I understand this. But my file will be on a read-only medium.
> So no other connection opening for writing is not possible.

SQLite doesn't know if media is read-only, or is not accessible for
writing for current user, or whatever else. So sorry, locks will
persist and no speed-up for read-only files.


Pavel

On Wed, Feb 2, 2011 at 10:36 PM, Navaneeth.K.N  wrote:
>>
>> You may have opened the file as read-only, but someone else may open the
>> same file for writing. Thus, your connection still needs to maintain a
>> shared lock, just like any other reader.
>
> Thanks. I understand this. But my file will be on a read-only medium.
> So no other connection opening for writing is not possible.
>
> --
> -n
> ___
> 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] Optimizing Database Size

[Mohit Sindhwani]

Hi Simon,

>> 1. We are already using CEROD, so the final database is compressed.
>>
>> 2. We ran the sqlite_analyzer and identified the tables that take up a
>> lot of space and are now re-organizing the schema.  One thing I observed
>> is that we have a number of tables that have the same primary key
>> (o_id).  Each table has exactly the same number of entries and the a
>> record corresponding to a particular o_id is stored at the same _row_id_
>> in each table.  I was wondering if there is a way to use this
>> information to remove some of the indexes without affecting the query
>> speed much?
> The best way to save space on that would be to merge the tables into one.

Thanks for the reply.  We are a bit concerned that merging everything 
into a single table will increase the query times further... especially 
because we have blob fields in the tables too.

I guess we will need to:
* Merge all always-populated fields into a single table
* Separate sparsely populated fields into a separate table
* Separate blobs into different table(s)

Cheers,
Mohit.
3/2/2011 | 11:51 AM.

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

Re: [sqlite] Will opening database in ReadOnly mode result in better query time

[Simon Slavin]

Opening your database as read-only will not speed anything up.

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

Re: [sqlite] Will opening database in ReadOnly mode result in better query time

[Navaneeth.K.N]

>
> You may have opened the file as read-only, but someone else may open the
> same file for writing. Thus, your connection still needs to maintain a
> shared lock, just like any other reader.

Thanks. I understand this. But my file will be on a read-only medium.
So no other connection opening for writing is not possible.

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

Re: [sqlite] Optimizing Database Size

[Simon Slavin]

On 3 Feb 2011, at 2:58am, Mohit Sindhwani wrote:

> 1. We are already using CEROD, so the final database is compressed.
> 
> 2. We ran the sqlite_analyzer and identified the tables that take up a 
> lot of space and are now re-organizing the schema.  One thing I observed 
> is that we have a number of tables that have the same primary key 
> (o_id).  Each table has exactly the same number of entries and the a 
> record corresponding to a particular o_id is stored at the same _row_id_ 
> in each table.  I was wondering if there is a way to use this 
> information to remove some of the indexes without affecting the query 
> speed much?

The best way to save space on that would be to merge the tables into one.

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

[sqlite] Optimizing Database Size

[Mohit Sindhwani]

Hi All,

I'm facing an issue where we want to make the database size smaller than 
it is.

1. We are already using CEROD, so the final database is compressed.

2. We ran the sqlite_analyzer and identified the tables that take up a 
lot of space and are now re-organizing the schema.  One thing I observed 
is that we have a number of tables that have the same primary key 
(o_id).  Each table has exactly the same number of entries and the a 
record corresponding to a particular o_id is stored at the same _row_id_ 
in each table.  I was wondering if there is a way to use this 
information to remove some of the indexes without affecting the query 
speed much?

3. We are also storing multiple indexes on the same table to accommodate 
the different queries that exist.  We are re-organizing the indexes 
again to ensure that we have indexes in the correct sequence to reduce 
the number of indexes we need to store.

4. Any other tips to keep the database smaller?

Best Regards,
Mohit.
3/2/2011 | 10:55 AM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Will opening database in ReadOnly mode result in better query time

[Igor Tandetnik]

On 2/2/2011 9:19 PM, Navaneeth.K.N wrote:
> I have an application that uses SQLite just for querying. Application
> will not write anything to the database. So I am wondering will I get
> a better query time if the database is opened with flag
> SQLITE_OPEN_READONLY? I am guessing on a read only database, SQLite
> doesn't have to do any kind of locking which will lead to performance
> improvement.

You may have opened the file as read-only, but someone else may open the 
same file for writing. Thus, your connection still needs to maintain a 
shared lock, just like any other reader.
-- 
Igor Tandetnik

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

[sqlite] Will opening database in ReadOnly mode result in better query time

[Navaneeth.K.N]

Hello,

I have an application that uses SQLite just for querying. Application
will not write anything to the database. So I am wondering will I get
a better query time if the database is opened with flag
SQLITE_OPEN_READONLY? I am guessing on a read only database, SQLite
doesn't have to do any kind of locking which will lead to performance
improvement.

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

Re: [sqlite] Question about database design

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/02/2011 11:48 AM, Bert Nelsen wrote:
> Because I felt so stupid about these mostly empty columns taking so much
> space, I tended to replace all the "phone" columns by a single column named
> "customerPhone".
> I stored the values into customerPhone like that:
> 
> 

Re: [sqlite] Attach to file in same directory

[Simon Slavin]

On 3 Feb 2011, at 1:03am, BareFeetWare wrote:

> How can I attach to a local file in the same directory, without specifying 
> the full absolute path?

No easy way.  Instead use operating system commands to retrieve the full path 
to the first file, then construct a full path to the second file.

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

Re: [sqlite] Attach to file in same directory

[Igor Tandetnik]

On 2/2/2011 8:03 PM, BareFeetWare wrote:
> I use the attach command to attach another SQLite database file that resides 
> in the same directory as my main file. I tried:
>
> attach 'Import.sqlitedb';
>
> But it fails to find the file. If I specify the full path:
>
> attach '/Users/tom/Documents/Work/Databases/Import.sqlitedb';
>
> Then it works.

Just as with any command line utility, relative paths are resolved 
relative to the current working directory. Just start sqlite3 in such a 
way that the directory where your database files reside is the current 
one. Read the fine manual for your shell of choice.

> How can I attach to a local file in the same directory, without specifying 
> the full absolute path?

How did you manage to open the main file in the first place? Specify the 
path to the second file in the same manner.
-- 
Igor Tandetnik

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

[sqlite] Attach to file in same directory

[BareFeetWare]

Hi All,

I use the attach command to attach another SQLite database file that resides in 
the same directory as my main file. I tried:

attach 'Import.sqlitedb';

But it fails to find the file. If I specify the full path:

attach '/Users/tom/Documents/Work/Databases/Import.sqlitedb';

Then it works. But I don't want to have to specify the full path because:

1. It's the same directory as the already open SQLite file which I'm asking to 
attach the second.

2. I move my directory of databases around so the absolute path changes but the 
relative path remains the same.

3. I move my databases and the procedure containing the attach to iPhones/iPads 
where the absolute path changes between devices.

How can I attach to a local file in the same directory, without specifying the 
full absolute path?

Thanks,
Tom
BareFeetWare

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

Re: [sqlite] Question about database design

[Nicolas Williams]

On Wed, Feb 02, 2011 at 04:45:16PM -0800, Jeff Rogers wrote:
> Nicolas Williams wrote:
> >On Wed, Feb 02, 2011 at 03:23:49PM -0800, Jeff Rogers wrote:
> >
> >>SQLite seems to do quite poorly performance-wise with fully-normalized
> >>attribute tables like this, when you want to query against multiple
> >>attributes.  My timing comparisons with postgres show sqlite to be as
> >>much as 10x-15x slower than pg.
> >>
> >>My timing code is at http://paste.tclers.tk/2346
> >
> >You need an index on props(id) -- you always need an index on columns
> >that form a foreign key.  SQLite3 requires that if you want to cascade
> >deletions.
> 
> Ok, that caught me by surprise, but it improved things a lot.  With
> that index sqlite is now significantly faster than pg on 3 of the
> tests, but still similar to or slower than pg on the remaining 2 -
> in my code, "sx" and "sx3".   (I can't be sure of the exact timings
> because I can't run sqlite and the pg server on the same machine)
> 
> Any idea why pg does ok on these queries without the extra index -
> Maybe they're created by default?  SQLIte doesn't create any indexes
> automatically on primary key fields or anything else, correct?

No, it doesn't.  Use EXPLAIN QUERY PLAN to see what SQLite3 is doing.

Also, see the followup about covering indexes.  Also, run ANALYZE.

Finally, you might denormalize somewhat by having a trigger to copy the
obj.name column to the props table and add that column to the _end_ of
the covering indexes.  Then SQLite3 might be able to do your joins using
a single index.

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

Re: [sqlite] Question about database design

[Simon Slavin]

On 3 Feb 2011, at 12:45am, Jeff Rogers wrote:

> Any idea why pg does ok on these queries without the extra index - 
> Maybe they're created by default?

Both PostgreSQL and SQLite will make indexes on the fly if that's the most 
efficient way of scanning the table.  However, PostgreSQL can keep the index 
around and reuse it any number of times, at the expense of using up disk space 
and memory to store the index.  SQLite is better designed for small light 
systems and has to place minimal load on disk space and memory, so it doesn't 
keep indexes around between uses.

What it comes down to is that if you use SQLite you have to work out what 
indexes are worthwhile for yourself whereas Postgres does some of the work for 
you but takes up more memory and disk space to do so.

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

Re: [sqlite] Question about database design

[Jeff Rogers]

Nicolas Williams wrote:
> On Wed, Feb 02, 2011 at 03:23:49PM -0800, Jeff Rogers wrote:
 >
>> SQLite seems to do quite poorly performance-wise with fully-normalized
>> attribute tables like this, when you want to query against multiple
>> attributes.  My timing comparisons with postgres show sqlite to be as
>> much as 10x-15x slower than pg.
>>
>> My timing code is at http://paste.tclers.tk/2346
>
> You need an index on props(id) -- you always need an index on columns
> that form a foreign key.  SQLite3 requires that if you want to cascade
> deletions.

Ok, that caught me by surprise, but it improved things a lot.  With that 
index sqlite is now significantly faster than pg on 3 of the tests, but 
still similar to or slower than pg on the remaining 2 - in my code, "sx" 
and "sx3".   (I can't be sure of the exact timings because I can't run 
sqlite and the pg server on the same machine)

Any idea why pg does ok on these queries without the extra index - 
Maybe they're created by default?  SQLIte doesn't create any indexes 
automatically on primary key fields or anything else, correct?

> Without that index your joins will use full table scans.
>
> Also, it's better to use FROM table1 t1 JOIN table2 t2 ON t1.col1 =
> t2.col2 than to put the join conditions in the WHERE clause, as that's
> much easier to read.  But I can see that you have JOINs using that
> un-indexed column.

I wrote this test code some time ago, I think originally against a 
database that didn't support JOIN syntax.  I would use that were I 
rewriting it now.  (I also don't remember exactly what conditions I was 
trying to exercise, but I think it was the case where two attributes 
each match a large set but the intersection of those sets is small)

Thanks,
-J

>
>> This is a synthetic test, but I ran across the issue in a real
>> application.  I'm not sure what else I can do do optimize the queries;
>> using a denormalized table is the only thing that seems to help.
>
> Add the missing index.
>
> Nico

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

Re: [sqlite] Question about database design

[Nicolas Williams]

On Wed, Feb 02, 2011 at 03:38:07PM -0800, Jim Morris wrote:
> I'd probably move the analyze out of the loop.
> 
> Since your joining on props.id a better index pind might be
> create index pind on props (id, pnam)

Yes, you probably want two covering or partially-covering indexes:

CREATE INDEX pind ON props (id, pnam, pval);
CREATE INDEX pind ON props (pval, pname, id);

The first is for when you need to join on 'id' and want to extract
attribute/values.  The second is for when you want to search by
attribute/values and need to get rowids back.

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

Re: [sqlite] Fast rowid selection

[Simon Slavin]

On 2 Feb 2011, at 11:28pm, Dave White wrote:

> Our old selection method queries indexes and returns the equivalent of a list 
> of rowids that we then use to access records as needed. I've managed to mimic 
> this behavior but it is very slow. My select statement is something like 
> "Select rowid from T01 where ref_uid=100". I then step through all rows, read 
> in each rowid, assemble them into an array, and forward the array to a client 
> which will then request the records by rowid one at a time.

Is there really any point in doing this ?  If you feed your second operation 
like this:

SELECT thisCol, thatCol FROM T01 WHERE ref_uid=100

you can skip the first stage entirely.  However, it's possible that whatever 
your second operation is doing will interfere with the SELECT so ...

> This takes about 10x longer than our old selections. It appears to me that 
> this is happening because each step to the next row is paging in data so the 
> rowid can be read. I expected index trees would store rowids and make data 
> reads unnecessary, but this doesn't seem to be the case.

If you have an index on the ref_uid column, then the SQL operations involved in 
this won't take too long. However you can dramatically reduce the number of SQL 
operations for this particular operation using an aggregate function:

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

So try something like

SELECT group_concat(rowid) FROM T01 WHERE ref_uid=100

should return just one row, with all the rowids concatenated into one long 
string, separated by commas.

Note: I just thought up the above code, I haven't actually tested it.  You 
should.

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

Re: [sqlite] Question about database design

[Jim Morris]

I'd probably move the analyze out of the loop.

Since your joining on props.id a better index pind might be
create index pind on props (id, pnam)

The name of column id in table props would be clearer as obj_id since it 
is not the id of the property but the id of the record in the obj table.

On 2/2/2011 3:23 PM, Jeff Rogers wrote:
> Andreas Kupries wrote:
>
>> It seems to me that you are looking for
>>  http://en.wikipedia.org/wiki/Database_normalization
>>
> SQLite seems to do quite poorly performance-wise with fully-normalized
> attribute tables like this, when you want to query against multiple
> attributes.  My timing comparisons with postgres show sqlite to be as
> much as 10x-15x slower than pg.
>
> My timing code is at http://paste.tclers.tk/2346
>
> This is a synthetic test, but I ran across the issue in a real
> application.  I'm not sure what else I can do do optimize the queries;
> using a denormalized table is the only thing that seems to help.
>
> -J
> ___
> 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] Question about database design

[Nicolas Williams]

On Wed, Feb 02, 2011 at 03:23:49PM -0800, Jeff Rogers wrote:
> Andreas Kupries wrote:
> 
> > It seems to me that you are looking for
> > http://en.wikipedia.org/wiki/Database_normalization
> >
> 
> SQLite seems to do quite poorly performance-wise with fully-normalized 
> attribute tables like this, when you want to query against multiple 
> attributes.  My timing comparisons with postgres show sqlite to be as 
> much as 10x-15x slower than pg.
> 
> My timing code is at http://paste.tclers.tk/2346

You need an index on props(id) -- you always need an index on columns
that form a foreign key.  SQLite3 requires that if you want to cascade
deletions.

Without that index your joins will use full table scans.

Also, it's better to use FROM table1 t1 JOIN table2 t2 ON t1.col1 =
t2.col2 than to put the join conditions in the WHERE clause, as that's
much easier to read.  But I can see that you have JOINs using that
un-indexed column.

> This is a synthetic test, but I ran across the issue in a real 
> application.  I'm not sure what else I can do do optimize the queries; 
> using a denormalized table is the only thing that seems to help.

Add the missing index.

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

Re: [sqlite] WAL for production use

[Dustin Sallings]

On Feb 2, 2011, at 8:14, Duquette, William H (318K) wrote:

> In SQLite 3.7.4/3.7.5, does WAL seem to be stable enough for production use?


I'm using it very, very heavily right now.

-- 
dustin sallings

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

[sqlite] Fast rowid selection

[Dave White]

We are currently migrating from a different type of database and I'm having a 
little trouble with performance. 

Our old selection method queries indexes and returns the equivalent of a list 
of rowids that we then use to access records as needed. I've managed to mimic 
this behavior but it is very slow. My select statement is something like 
"Select rowid from T01 where ref_uid=100". I then step through all rows, read 
in each rowid, assemble them into an array, and forward the array to a client 
which will then request the records by rowid one at a time.

This takes about 10x longer than our old selections. It appears to me that this 
is happening because each step to the next row is paging in data so the rowid 
can be read. I expected index trees would store rowids and make data reads 
unnecessary, but this doesn't seem to be the case.

Is there a way to speed this up, or a better approach? All I want is a list of 
all rowids that match my query.

Thanks
dw



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

Re: [sqlite] Question about database design

[Jeff Rogers]

Andreas Kupries wrote:

> It seems to me that you are looking for
>   http://en.wikipedia.org/wiki/Database_normalization
>

SQLite seems to do quite poorly performance-wise with fully-normalized 
attribute tables like this, when you want to query against multiple 
attributes.  My timing comparisons with postgres show sqlite to be as 
much as 10x-15x slower than pg.

My timing code is at http://paste.tclers.tk/2346

This is a synthetic test, but I ran across the issue in a real 
application.  I'm not sure what else I can do do optimize the queries; 
using a denormalized table is the only thing that seems to help.

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

Re: [sqlite] Question about database design

[Simon Slavin]

On 2 Feb 2011, at 7:48pm, Bert Nelsen wrote:

> customer_lastname
> customer_firstname
> customer_street
> customer_PhonePrivate (will almost always stay empty)
> customer_PhoneCompany
> customer_PhoneMobile
> customer_PhoneWAP (will almost always stay empty)
> customer_Phone1 (will almost always stay empty)
> customer_Phone2 (will almost always stay empty)
> 
> Because I felt so stupid about these mostly empty columns taking so much
> space, I tended to replace all the "phone" columns by a single column named
> "customerPhone".
> I stored the values into customerPhone like that:
> 
> 

Re: [sqlite] Query help

[Marco Bambini]

Thanks Igor and thanks Martin,
I need to add both the id and the other properties to an hash table (a Cocoa 
NSDictionary) so I needed a way to have a key, value representation that 
includes also the id.

I solved the problem with 2 queries and some Cocoa code.
I don't like complex queries and 2 simple queries is a better approach for the 
maintainability of the project.

Thanks a lot for your advices.
--
Marco Bambini
http://www.sqlabs.com






On Feb 2, 2011, at 8:11 PM, Igor Tandetnik wrote:

> On 2/2/2011 11:16 AM, Marco Bambini wrote:
>> your query returns 3 columns, but I need just two columns (key, value for 
>> example).
> 
> Why? You have all the information you need, just in a slightly different 
> (and, arguably, easier to use) form.
> 
>> The first row should be the label 'ID' and the id of the MKObjects followed 
>> by a SELECT prop_key, prop_value WHERE obj_id= MKObjects.id.
> 
> Why should it? Why exactly do you insist on this format?
> 
> What should happen, in your proposed representation, when there is more 
> than row in MKObjects, each with its own set of properties?
> -- 
> 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] Question about database design

[Andreas Kupries]

On 2/2/2011 11:48 AM, Bert Nelsen wrote:
> Hello!
>
> I have over 100 columns in a table. Most of the columns will stay empty.
> Just an example:
[example elided]

> This is kind of an xml design, but it works and it takes up less space.
> I "decompile" this weird expression at runtime to get the separate values
> again.
>
> Can I get an opinion on it?
> I have not found any good information on the number of columns and
> relationship between their count and speed and diskspace used.

It seems to me that you are looking for
http://en.wikipedia.org/wiki/Database_normalization

-- 
Andreas Kupries
Senior Tcl Developer
ActiveState, The Dynamic Language Experts

P: 778.786.1122
F: 778.786.1133
andre...@activestate.com
http://www.activestate.com
Get insights on Open Source and Dynamic Languages at www.activestate.com/blog
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about database design

[Duquette, William H (318K)]

On 2/2/11 11:48 AM, "Bert Nelsen"  wrote:

> Because I felt so stupid about these mostly empty columns taking so much
> space, I tended to replace all the "phone" columns by a single column named
> "customerPhone".
> I stored the values into customerPhone like that:
> 
> 

[sqlite] Question about database design

[Bert Nelsen]

Hello!

I have over 100 columns in a table. Most of the columns will stay empty.
Just an example:

customer_lastname
customer_firstname
customer_street
customer_PhonePrivate (will almost always stay empty)
customer_PhoneCompany
customer_PhoneMobile
customer_PhoneWAP (will almost always stay empty)
customer_Phone1 (will almost always stay empty)
customer_Phone2 (will almost always stay empty)

Because I felt so stupid about these mostly empty columns taking so much
space, I tended to replace all the "phone" columns by a single column named
"customerPhone".
I stored the values into customerPhone like that:

Re: [sqlite] how to get a reliable Integer from a Real?

[Oliver Peters]

Charles Samuels  writes:

> 
> On Wednesday, February 02, 2011 11:06:59 am Drake Wilson wrote:
> > Quoth Oliver Peters , on 2011-02-02 18:25:04 +:
> > > I'm on Win XP with sqlite 3.7.5 and trying to get cents from euro but
> > > sometimes
> > 
> > Don't do that.  ... Most people doing
> > currency calculations should be using integers and treating them as
> > fixed-point values.
> 
> Oliver,
> 
> This is very good advice.
> 
> :)
> 
> Charles

I simply have to work with what I get - so no way out in this case but I've
already learned the lesson a few weeks ago so I personally will never store
currency-values as floating point type.

Oliver




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

Re: [sqlite] how to get a reliable Integer from a Real?

[Charles Samuels]

On Wednesday, February 02, 2011 11:06:59 am Drake Wilson wrote:
> Quoth Oliver Peters , on 2011-02-02 18:25:04 +:
> > I'm on Win XP with sqlite 3.7.5 and trying to get cents from euro but
> > sometimes
> 
> Don't do that.  ... Most people doing
> currency calculations should be using integers and treating them as
> fixed-point values.

Oliver,

This is very good advice.

:)

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

Re: [sqlite] how to get a reliable Integer from a Real?

[Oliver Peters]

Igor Tandetnik  writes:

> 
> On 2/2/2011 1:25 PM, Oliver Peters wrote:
> > sqlite>  SELECT CAST(2.3*100 AS INTEGER);
> > 229<- S T R A N G E result
> 
> select cast(round(2.3*100) as integer);
> 

I knew you know it.

thx a lot you're still the greatest :-)

greetings to wherever you are
Oliver

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

Re: [sqlite] Query help

[Igor Tandetnik]

On 2/2/2011 11:16 AM, Marco Bambini wrote:
> your query returns 3 columns, but I need just two columns (key, value for 
> example).

Why? You have all the information you need, just in a slightly different 
(and, arguably, easier to use) form.

> The first row should be the label 'ID' and the id of the MKObjects followed 
> by a SELECT prop_key, prop_value WHERE obj_id= MKObjects.id.

Why should it? Why exactly do you insist on this format?

What should happen, in your proposed representation, when there is more 
than row in MKObjects, each with its own set of properties?
-- 
Igor Tandetnik

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

Re: [sqlite] how to get a reliable Integer from a Real?

[Stephan Wehner]

On Wed, Feb 2, 2011 at 10:25 AM, Oliver Peters  wrote:
> Hello,
>
> I'm on Win XP with sqlite 3.7.5 and trying to get cents from euro but 
> sometimes
> I get a strange result:
>
> SQLite version 3.7.5
> Enter ".help" for instructions
> Enter SQL statements terminated with a "
> sqlite> SELECT CAST(2.1*100 AS INTEGER);
> 210
> sqlite> SELECT CAST(2.2*100 AS INTEGER);
> 220
> sqlite> SELECT CAST(2.3*100 AS INTEGER);
> 229 <- S T R A N G E result
> sqlite> SELECT CAST(2.4*100 AS INTEGER);
> 240
> sqlite> SELECT CAST(2.5*100 AS INTEGER);
> 250
> sqlite> SELECT CAST(2.6*100 AS INTEGER);
> 260
> sqlite> SELECT CAST(2.7*100 AS INTEGER);
> 270
> sqlite> SELECT CAST(2.8*100 AS INTEGER);
> 280
> sqlite> SELECT CAST(2.9*100 AS INTEGER);
> 290
> sqlite> SELECT CAST(3.0*100 AS INTEGER);
> 300
> sqlite> SELECT CAST(3.1*100 AS INTEGER);
> 310
> sqlite> SELECT CAST(3.2*100 AS INTEGER);
> 320
> sqlite> SELECT CAST(3.3*100 AS INTEGER);
> 330
> sqlite> SELECT CAST(3.4*100 AS INTEGER);
> 340
> sqlite>

Can you store the euro's as cents? Then you'll have the opposite
problem of converting cents to euro's but that is much safer.

Quick reference for further reading,
http://stackoverflow.com/questions/618056/what-is-the-best-way-to-store-a-money-value-in-the-database

Stephan

>
> Is the result for 2.3 expected behaviour (strange floating point arithmetic
> reasons?) and is
>
> sqlite> SELECT CAST(2.3*1000 AS INTEGER)/10;
> 230
> sqlite>
>
> a reliable way to get rid of my problem (even if the Real has 2 digits behind
> the .)?
>
> greetings
> Oliver
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Stephan Wehner

-> http://stephan.sugarmotor.org (blog and homepage)
-> http://loggingit.com
-> http://www.thrackle.org
-> http://www.buckmaster.ca
-> http://www.trafficlife.com
-> http://stephansmap.org -- http://blog.stephansmap.org
-> http://twitter.com/stephanwehner / @stephanwehner
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to get a reliable Integer from a Real?

[Igor Tandetnik]

On 2/2/2011 1:25 PM, Oliver Peters wrote:
> sqlite>  SELECT CAST(2.3*100 AS INTEGER);
> 229<- S T R A N G E result

select cast(round(2.3*100) as integer);

-- 
Igor Tandetnik

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

Re: [sqlite] EXTERNAL: how to get a reliable Integer from a Real?

[Black, Michael (IS)]

Can't represent 2.3 correctly.

You can fix the whole thing by using 100.1 I think...

sqlite> select cast(2.3*100.1 as integer);
230

Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Oliver Peters [oliver@web.de]
Sent: Wednesday, February 02, 2011 12:25 PM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] how to get a reliable Integer from a Real?

Hello,

I'm on Win XP with sqlite 3.7.5 and trying to get cents from euro but sometimes
I get a strange result:

SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a "
sqlite> SELECT CAST(2.1*100 AS INTEGER);
210
sqlite> SELECT CAST(2.2*100 AS INTEGER);
220
sqlite> SELECT CAST(2.3*100 AS INTEGER);
229 <- S T R A N G E result
sqlite> SELECT CAST(2.4*100 AS INTEGER);
240
sqlite> SELECT CAST(2.5*100 AS INTEGER);
250
sqlite> SELECT CAST(2.6*100 AS INTEGER);
260
sqlite> SELECT CAST(2.7*100 AS INTEGER);
270
sqlite> SELECT CAST(2.8*100 AS INTEGER);
280
sqlite> SELECT CAST(2.9*100 AS INTEGER);
290
sqlite> SELECT CAST(3.0*100 AS INTEGER);
300
sqlite> SELECT CAST(3.1*100 AS INTEGER);
310
sqlite> SELECT CAST(3.2*100 AS INTEGER);
320
sqlite> SELECT CAST(3.3*100 AS INTEGER);
330
sqlite> SELECT CAST(3.4*100 AS INTEGER);
340
sqlite>

Is the result for 2.3 expected behaviour (strange floating point arithmetic
reasons?) and is

sqlite> SELECT CAST(2.3*1000 AS INTEGER)/10;
230
sqlite>

a reliable way to get rid of my problem (even if the Real has 2 digits behind
the .)?

greetings
Oliver

___
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 get a reliable Integer from a Real?

[Drake Wilson]

Quoth Oliver Peters , on 2011-02-02 18:25:04 +:
> I'm on Win XP with sqlite 3.7.5 and trying to get cents from euro but 
> sometimes
> I get a strange result:

Don't do that.  Read up on the Web about the properties of binary
floating-point representations.  They are not decimal and if you have
specific precision requirements you should carefully analyze how the
floating-point numbers will impact them first.  Most people doing
currency calculations should be using integers and treating them as
fixed-point values.  (The others are the ones who already know enough
to recognize exactly why their cases are different.)

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

[sqlite] how to get a reliable Integer from a Real?

[Oliver Peters]

Hello,

I'm on Win XP with sqlite 3.7.5 and trying to get cents from euro but sometimes
I get a strange result:

SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a "
sqlite> SELECT CAST(2.1*100 AS INTEGER);
210
sqlite> SELECT CAST(2.2*100 AS INTEGER);
220
sqlite> SELECT CAST(2.3*100 AS INTEGER);
229 <- S T R A N G E result
sqlite> SELECT CAST(2.4*100 AS INTEGER);
240
sqlite> SELECT CAST(2.5*100 AS INTEGER);
250
sqlite> SELECT CAST(2.6*100 AS INTEGER);
260
sqlite> SELECT CAST(2.7*100 AS INTEGER);
270
sqlite> SELECT CAST(2.8*100 AS INTEGER);
280
sqlite> SELECT CAST(2.9*100 AS INTEGER);
290
sqlite> SELECT CAST(3.0*100 AS INTEGER);
300
sqlite> SELECT CAST(3.1*100 AS INTEGER);
310
sqlite> SELECT CAST(3.2*100 AS INTEGER);
320
sqlite> SELECT CAST(3.3*100 AS INTEGER);
330
sqlite> SELECT CAST(3.4*100 AS INTEGER);
340
sqlite>

Is the result for 2.3 expected behaviour (strange floating point arithmetic
reasons?) and is

sqlite> SELECT CAST(2.3*1000 AS INTEGER)/10;
230
sqlite>

a reliable way to get rid of my problem (even if the Real has 2 digits behind
the .)?

greetings
Oliver

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

Re: [sqlite] Cant get PRAGMA temp_store = MEMORY to work

[Martin Engelschalk]

Hi,

i have something to add: SQLITE_DEFAULT_TEMP_CACHE_SIZE is not used 
anywhere in the sqlite.c file of the amalgamationand the comment above 
seems to be incomplete. Is this intended?

Martin

Am 02.02.2011 13:38, schrieb Martin Engelschalk:
> Hi list,
>
> I have a problem getting PRAGMA temp_store = MEMORY to work.
>
> I do a select joining two tables with an order by for which no index
> esists. This selects all the data in my database, 1 million records.
> The database size is 196 MB.
>
> When using version 3.2.5 (the amalgamation, no special pragmas or
> #defines), my executable consumes 345 MBs of memory, does not write any
> temporary files, and finishes in about 100 seconds.
>
> With version 3.7.5, downloaded today, i have to do some things to get
> sqlite to work like before. I want to avoid temporary files, which are
> created by default for the temporary index.
> Even if i set PRAGMA temp_store = MEMORY after opening the database, a
> temporary file gets written, and the memory usage is 72  MB.
>
> I read the docs and compiled the amalgamation with various values of
> SQLITE_DEFAULT_TEMP_CACHE_SIZE, and after opening the file set PRAGMA
> cache_size to different values.
> However, even if memory usage rises up to 318 MB, a temorary file is
> written which is larger than the database itself.
>
> This is on Windows XP, with 1 GB of freee memory.
>
> What am i missing?
>
> Thank you,
> Martin
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

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

Re: [sqlite] WAL for production use

[Duquette, William H (318K)]

Thanks, Richard!

Will


On 2/2/11 8:22 AM, "Richard Hipp"  wrote:

On Wed, Feb 2, 2011 at 11:14 AM, Duquette, William H (318K) <
william.h.duque...@jpl.nasa.gov> wrote:

> Howdy!
>
> In SQLite 3.7.4/3.7.5, does WAL seem to be stable enough for production
> use?
>

Mass-market consumer products are shipping with WAL now, I am told.


>
> And then, an architecture question.  I have an app that occasionally needs
> to do significant background processing.  I'd like to keep the GUI awake and
> looking at the current data set while the app is computing the updated data
> in the background.  The obvious notion is to ask a secondary process or
> thread to do the background processing.  However, both the background task
> and the GUI need to access the same DB.
>

Please use a background process, not a thread   :-)


>
> WAL seems perfectly suited to this architecture.  While the background
> processing is going on, only the background task can write to it; but the
> GUI can read the database all it likes, seeing only the data committed
> before the background task started its current transaction.
>

WAL does exactly what you want, Will.  As long as you only have a single
writer, it will work great.


>
> Does this make sense?  Is there an easier way to do it that doesn't involve
> WAL?
>
> Thanks very much!
>
> Will
>
> --
> Will Duquette -- william.h.duque...@jpl.nasa.gov
> Athena Development Lead -- Jet Propulsion Laboratory
> "It's amazing what you can do with the right tools."
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."

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

Re: [sqlite] WAL for production use

[Richard Hipp]

On Wed, Feb 2, 2011 at 11:14 AM, Duquette, William H (318K) <
william.h.duque...@jpl.nasa.gov> wrote:

> Howdy!
>
> In SQLite 3.7.4/3.7.5, does WAL seem to be stable enough for production
> use?
>

Mass-market consumer products are shipping with WAL now, I am told.


>
> And then, an architecture question.  I have an app that occasionally needs
> to do significant background processing.  I'd like to keep the GUI awake and
> looking at the current data set while the app is computing the updated data
> in the background.  The obvious notion is to ask a secondary process or
> thread to do the background processing.  However, both the background task
> and the GUI need to access the same DB.
>

Please use a background process, not a thread   :-)


>
> WAL seems perfectly suited to this architecture.  While the background
> processing is going on, only the background task can write to it; but the
> GUI can read the database all it likes, seeing only the data committed
> before the background task started its current transaction.
>

WAL does exactly what you want, Will.  As long as you only have a single
writer, it will work great.


>
> Does this make sense?  Is there an easier way to do it that doesn't involve
> WAL?
>
> Thanks very much!
>
> Will
>
> --
> Will Duquette -- william.h.duque...@jpl.nasa.gov
> Athena Development Lead -- Jet Propulsion Laboratory
> "It's amazing what you can do with the right tools."
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Query help

[Marco Bambini]

Hello Igor,
your query returns 3 columns, but I need just two columns (key, value for 
example).

The first row should be the label 'ID' and the id of the MKObjects followed by 
a SELECT prop_key, prop_value WHERE obj_id= MKObjects.id.

For example MKObjects contains (1,IPHONE,PANEL,0)
and MKProperties contains (1,1,NAME,About Box) and (2,1,WIDTH,200)

the result of the query should should be:
col1col2
'ID'1
'NAME'  'About Box'
'WIDTH', '200'
--
Marco Bambini
http://www.sqlabs.com






On Feb 2, 2011, at 1:43 PM, Igor Tandetnik wrote:

> Marco Bambini  wrote:
>> Hello, I have two tables defined as:
>> 
>> CREATE TABLE MKObjects (id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT, 
>> type TEXT, parent_id INTEGER DEFAULT 0);
>> CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id 
>> INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id,
>> prop_key)); 
>> 
>> I need to create a query that returns 2 columns key, value (column names are 
>> not important) where the first row is the label 'ID'
>> with value id from MKObjects and the other rows are the columns prop_key, 
>> prop_value from MKProperties where obj_id= MKObjects.id
>> satisfying a WHERE condition.  
> 
> You don't really need, or want, to create a query like that. It goes against 
> the grain of SQL. You want this:
> 
> select o.id,  prop_key, prop_value
> from MKObjects o join MKProperties p on (o.id = p.obj_id)
> where type='PANEL' AND platform='IPHONE'
> order by o.id;
> 
> When formatting your report, output a section heading whenever id column 
> changes from previous row.
> -- 
> 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] WAL for production use

[Duquette, William H (318K)]

Howdy!

In SQLite 3.7.4/3.7.5, does WAL seem to be stable enough for production use?

And then, an architecture question.  I have an app that occasionally needs to 
do significant background processing.  I'd like to keep the GUI awake and 
looking at the current data set while the app is computing the updated data in 
the background.  The obvious notion is to ask a secondary process or thread to 
do the background processing.  However, both the background task and the GUI 
need to access the same DB.

WAL seems perfectly suited to this architecture.  While the background 
processing is going on, only the background task can write to it; but the GUI 
can read the database all it likes, seeing only the data committed before the 
background task started its current transaction.

Does this make sense?  Is there an easier way to do it that doesn't involve WAL?

Thanks very much!

Will

--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."

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

Re: [sqlite] Query help

[Martin Engelschalk]

Hello Marco,

As far as i can see, the union is necessary. However, the second select 
in the union can be rewritten as a join:

SELECT 'ID', id
   FROM MKObjects
  WHERE type='PANEL' AND platform='IPHONE'
UNION
  SELECT prop_key, prop_value
FROM MKProperties
JOIN MKObjects on MKProperties.obj_id = MKObjects.id
   WHERE MKObjects.type='PANEL' AND MKObjects.platform='IPHONE'


I am not sure if i understood your question correctly - perhaps you want to add 
some examples.

Martin


Am 02.02.2011 10:04, schrieb Marco Bambini:
> Hello, I have two tables defined as:
>
> CREATE TABLE MKObjects (id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT, 
> type TEXT, parent_id INTEGER DEFAULT 0);
> CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id 
> INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id, prop_key));
>
> I need to create a query that returns 2 columns key, value (column names are 
> not important) where the first row is the label 'ID' with value id from 
> MKObjects and the other rows are the columns prop_key, prop_value from 
> MKProperties where obj_id= MKObjects.id satisfying a WHERE condition.
>
> So far I am using a query like:
> SELECT 'ID', id FROM MKObjects WHERE type='PANEL' AND platform='IPHONE' UNION 
> SELECT prop_key, prop_value FROM MKProperties WHERE obj_id=(SELECT id FROM 
> MKObjects WHERE type='PANEL' AND platform='IPHONE' ORDER BY id);
>
> but I am wondering if there is a better way (without using 3 select 
> statements).
> Thanks a lot.
> --
> Marco Bambini
> http://www.sqlabs.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

Re: [sqlite] Query help

[Igor Tandetnik]

Marco Bambini  wrote:
> Hello, I have two tables defined as:
> 
> CREATE TABLE MKObjects (id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT, 
> type TEXT, parent_id INTEGER DEFAULT 0);
> CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id 
> INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id,
> prop_key)); 
> 
> I need to create a query that returns 2 columns key, value (column names are 
> not important) where the first row is the label 'ID'
> with value id from MKObjects and the other rows are the columns prop_key, 
> prop_value from MKProperties where obj_id= MKObjects.id
> satisfying a WHERE condition.  

You don't really need, or want, to create a query like that. It goes against 
the grain of SQL. You want this:

select o.id,  prop_key, prop_value
from MKObjects o join MKProperties p on (o.id = p.obj_id)
where type='PANEL' AND platform='IPHONE'
order by o.id;

When formatting your report, output a section heading whenever id column 
changes from previous row.
-- 
Igor Tandetnik

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

[sqlite] Cant get PRAGMA temp_store = MEMORY to work

[Martin Engelschalk]

Hi list,

I have a problem getting PRAGMA temp_store = MEMORY to work.

I do a select joining two tables with an order by for which no index 
esists. This selects all the data in my database, 1 million records.
The database size is 196 MB.

When using version 3.2.5 (the amalgamation, no special pragmas or 
#defines), my executable consumes 345 MBs of memory, does not write any 
temporary files, and finishes in about 100 seconds.

With version 3.7.5, downloaded today, i have to do some things to get 
sqlite to work like before. I want to avoid temporary files, which are 
created by default for the temporary index.
Even if i set PRAGMA temp_store = MEMORY after opening the database, a 
temporary file gets written, and the memory usage is 72  MB.

I read the docs and compiled the amalgamation with various values of 
SQLITE_DEFAULT_TEMP_CACHE_SIZE, and after opening the file set PRAGMA 
cache_size to different values.
However, even if memory usage rises up to 318 MB, a temorary file is 
written which is larger than the database itself.

This is on Windows XP, with 1 GB of freee memory.

What am i missing?

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

Re: [sqlite] SQLite version 3.7.5

[Michael Knigge]

> As of this release, the popular ADO.NET provider for SQLite by Robert
> Simpson, System.Data.SQLite, is hosted on the SQLite website.  See
> http://System.Data.SQLite.org/ for additional information.  Release builds
> of System.Data.SQLite will appears on the SQLite download page over the
> course of the next week.


EESS


Finally we get up-to-date versions of this ADO.NET Provider!

THANKS! THANKS! THANKS!


Bye,
Michael

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

[sqlite] Query help

[Marco Bambini]

Hello, I have two tables defined as:

CREATE TABLE MKObjects (id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT, 
type TEXT, parent_id INTEGER DEFAULT 0);
CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id 
INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id, prop_key));

I need to create a query that returns 2 columns key, value (column names are 
not important) where the first row is the label 'ID' with value id from 
MKObjects and the other rows are the columns prop_key, prop_value from 
MKProperties where obj_id= MKObjects.id satisfying a WHERE condition.

So far I am using a query like:
SELECT 'ID', id FROM MKObjects WHERE type='PANEL' AND platform='IPHONE' UNION 
SELECT prop_key, prop_value FROM MKProperties WHERE obj_id=(SELECT id FROM 
MKObjects WHERE type='PANEL' AND platform='IPHONE' ORDER BY id);

but I am wondering if there is a better way (without using 3 select statements).
Thanks a lot.
--
Marco Bambini
http://www.sqlabs.com






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