[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));

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 >

[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

Re: [sqlite] Query help

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,

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

[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

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

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

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

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

[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

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

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:

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

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

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 " >

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

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

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

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

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

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.

[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)

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: >

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

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

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

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

[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

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

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

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

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

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

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

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

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

[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

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

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.

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

[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

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

[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

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

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

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] 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

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

[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

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

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

[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?

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 -