Re: [sqlite] This query seems clumsy - can anyone do better?
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)
___ 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?
Kai Peterswrote: > 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?
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
> 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.Nwrote: >> >> 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
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
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
> > 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
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
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
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
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
-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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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?
Charles Samuelswrites: > > 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?
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?
Igor Tandetnikwrites: > > 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
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?
On Wed, Feb 2, 2011 at 10:25 AM, Oliver Peterswrote: > 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?
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?
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?
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?
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
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
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
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
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 Bambiniwrote: >> 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
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
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
Marco Bambiniwrote: > 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
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
> 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
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