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));
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
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
Marco Bambini ma...@sqlabs.net 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,
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
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
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
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.
Thanks, Richard!
Will
On 2/2/11 8:22 AM, Richard Hipp d...@sqlite.org 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
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
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
Quoth Oliver Peters oliver@web.de, 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
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:
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
On Wed, Feb 2, 2011 at 10:25 AM, Oliver Peters oliver@web.de 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
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
Igor Tandetnik itandetnik@... 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
On Wednesday, February 02, 2011 11:06:59 am Drake Wilson wrote:
Quoth Oliver Peters oliver@web.de, 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
Charles Samuels charles@... writes:
On Wednesday, February 02, 2011 11:06:59 am Drake Wilson wrote:
Quoth Oliver Peters oliver.pet@..., 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
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)
On 2/2/11 11:48 AM, Bert Nelsen bert.nel...@googlemail.com 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:
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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.
-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
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
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
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
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
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
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
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
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
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
Kai Peters kpet...@otaksoft.com 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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
50 matches
Mail list logo
