date:20110628

On Tue, Jun 28, 2011 at 8:18 PM, Jan Hudec  wrote:

> > name TEXT
> >
> > DATA
> > file_id INTEGER REFERENCES files(resource_id) ON DELETE CASCADE,
> > data_type TEXT,
>
> If nothing else, you want to define integer identifiers for the data types
> and use integer here. That will save you some space (reading from flash is
> still performance bottleneck, especially if the flash is SD card) and some
> unnecessarily costly string comparisons. In code, you'd obviously use
> symbolic constants.
>

i made this change but did not see any performance improvements, but it
makes sense to keep it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help needed to optimize a query

On 29 Jun 2011, at 2:26am, Lazarus 101 wrote:

> the select statement is
>   SELECT * from files left join data on files.file_id=data.file_id;

So you read all the records for the correct file_id, and deal with each one as 
you find it, ignoring those you don't want.  Hmm.  I don't see why your app 
isn't far faster with the index.  Can an SQLite expert explain it ?

By the way, when SQLite does create the index automatically it deletes it when 
the SELECT finishes.  So in your real application it'll keep recreating the 
index for every SELECT.

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

Re: [sqlite] help needed to optimize a query

the select statement is
   SELECT * from files left join data on files.file_id=data.file_id;

to test the performance i'm only doing

long t1 = System.currentTimeMillis();

Cursor cursor = db.rawQuery(...);
while (cursor.moveToNext()) {
}
 android.util.Log.e(TAG, "loaded in: " + (System.currentTimeMillis() -t1));

and the results are
without index: 8143
with index:7039

this is for 1453 entries in resources and 7697 entries in data

the output for explain query plan (without the above index) is
0|0|TABLE files
1|1|TABLE data WITH INDEX sqlite_autoindex_data_1

so it looks like it creates an index automatically and that's why there is
no big improvement

On Wed, Jun 29, 2011 at 2:30 AM, Simon Slavin  wrote:

>
> On 28 Jun 2011, at 9:58pm, Lazarus 101 wrote:
>
> >> You should make an index on the columns 'file_id' and 'data_type' from
> the
> >> 'DATA' table.  This will allow it to be searched far more quickly.  Your
> >> command will be something like
> >>
> >> CREATE UNIQUE INDEX dfd ON data (file_id,data_type)
> >>
> >> Then do the above testing again.
> >
> > tried that and it didn't help much
>
> Something is wrong with your logic or programming.  Having this index
> should make a huge difference in the speed of lookup.  How are you finding
> the entries in the TABLE called 'data' ?
>
> Actually, as Jan posted, instead of the above index do this one:
>
> CREATE UNIQUE INDEX dfdv ON data (file_id,data_type, value)
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA bug in 3.7.7 (but fine in 3.7.6.3)

2011-06-28 Thread Greg Stein

I see that 3.7.7.1 has just been released with this bugfix.

Thanks!!

On Mon, Jun 27, 2011 at 14:01, Roger Binns  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 06/26/2011 03:52 PM, Richard Hipp wrote:
>> The bug is that it is returning
>> SQLITE_SCHEMA instead of SQLITE_OK.  If you wanted to, you could just ignore
>> the error and keep going and everything would work.
>
> Will there be a point release soon fixing this?
>
> If not I'll have to also add a workaround which is fairly difficult due to
> the use of a statement cache and the prepare and step code being very 
> separated.
>
> I use v1 prepare interface because v2 is worse - it gives different error
> semantics requiring manual reprepare anyway and saves another copy of the
> SQL (not useful since I have to reprepare again and already have a copy due
> to statement cache).
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAk4IxYMACgkQmOOfHg372QTbVwCcCqIxXJbcSRurvjDiXcczDQJP
> 7mYAn1tk+JQGHoNL9+wtVl2am+Grw5Zh
> =L68m
> -END PGP SIGNATURE-
> ___
> 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] help needed to optimize a query

On 28 Jun 2011, at 9:58pm, Lazarus 101 wrote:

>> You should make an index on the columns 'file_id' and 'data_type' from the
>> 'DATA' table.  This will allow it to be searched far more quickly.  Your
>> command will be something like
>> 
>> CREATE UNIQUE INDEX dfd ON data (file_id,data_type)
>> 
>> Then do the above testing again.
> 
> tried that and it didn't help much

Something is wrong with your logic or programming.  Having this index should 
make a huge difference in the speed of lookup.  How are you finding the entries 
in the TABLE called 'data' ?

Actually, as Jan posted, instead of the above index do this one:

CREATE UNIQUE INDEX dfdv ON data (file_id,data_type, value)

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

Re: [sqlite] help needed to optimize a query

On Tue, Jun 28, 2011 at 8:18 PM, Jan Hudec  wrote:

> On Tue, Jun 28, 2011 at 17:33:23 +0300, Lazarus 101 wrote:
> > Hi guys, i'm working on an Android app and using sqlite to store some
> data
> > and i need some help with a query.
> >
> > I have the following table structure:
> >
> > FILES
> > file_id INTEGER NOT NULL,
>    Do file_ids repeat? If not, it should be
> "integer
>  primary key". Than sqlite will alias the column to the "rowid"
>  column the table is internally ordered by for improved performance
>  looking up by file_id and saving some space (one fewer columns).
>

yes, that was already marked as primary key but it was at the end of the
create statement, that's why i forgot to mention it.


> > name TEXT
> >
> > DATA
> > file_id INTEGER REFERENCES files(resource_id) ON DELETE CASCADE,
> > data_type TEXT,
>
> If nothing else, you want to define integer identifiers for the data types
> and use integer here. That will save you some space (reading from flash is
> still performance bottleneck, especially if the flash is SD card) and some
> unnecessarily costly string comparisons. In code, you'd obviously use
> symbolic constants.
>
> > value TEXT
>
> Obviously you need index on DATA(file_id, data_type, value)
>
> Yes, all three columns. The first two will appear in your query, so have to
> be first two having the result also included in the index saves time,
> because
> now everything can be obtained from the index and the table itself does not
> have to be fetched.
>

I will try this, thanks.


>
> > data_type can be one of:
> > (title, description, date_taken, latitude,longitude, duration,
> > album,track_nr,genre, artist, year)
>
> Why this "hypernormalized" structure. When the set is fixed and there can
> be
> only one of each for each file, a big table with one column for each
> attribute will do you much better service.
>

it's not fixed, for photos for example it's possible that we would want to
add some other exif values, also some mp3 tags can have more then one value
(artist, genre).


>  Just create FILES with
>file_id integer primary key,
>name text,
>title text,
>description text,
>date_taken text, /* or integer if you decide to store timestamps instead
> */
>latitude number,
>longitude number,
>duration number,
>album text,
>track_nr integer,
>genre text,
>artist text,
>year integer
>

The files table has some other fields, I only mentioned those that I want in
the result (there is also a path, favorite flag, parent_id, size,state etc.
10 columns in total), so I don't think a table with 20+ columns is a good
idea, also there is the problem of adding new data types that would require
altering the files table structure. And most of the time when I display the
files list i don't need these specific media fields.



>
> > it's possible that a music from files does not have any data associated
> with
> > it, in this case i want the name to be in the result set
> >
> > This has to run as fast as possible. A left join between these tables is
> too
> > slow, for 10.000 entries it takes around 15 seconds just to navigate
> through
> > the cursor, if I add a where clause selecting only one kind of data then
> it
> > reduces to less than 5 seconds which is acceptable.
>
> Last but not least, "explain query plan" is your friend. If you prefix your
> query with "explain query plan" and run it against the database (you can
> use
> the command-line shell or some management tool), sqlite will tell you what
> tables it would read, in which order, using which indices and how big it
> expects the result set to be.
>
> So create various indices and experiment with tweaking the query and look
> what explain query plan tells you. Reading by primary key is fastest,
> followed by covering index, noncovering index and temporary index or linear
> search are worst (except join by temporary b-tree is near-optimal when you
> are not filtering out anything).
>
> Sqlite before 3.7.4 didn't support temporary indices and temporary b-tree
> joins, so it is much more critical to have good indices in older versions,
> because they did happily regress to quadratic or worse complexity and that
> would take ages to complete.
>
> Oh, and remember to remove the indices you end up not using to save space
> and time needed to keep them up to date.
>

On my Android device sqlite version is  3.7.2
Thanks a lot, i will try the "explain query plan"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help needed to optimize a query

On Tue, Jun 28, 2011 at 5:48 PM, Simon Slavin  wrote:

>
> On 28 Jun 2011, at 3:33pm, Lazarus 101 wrote:
>
> > FILES
> > file_id INTEGER NOT NULL,
> > name TEXT
>
> I assume that SQLite has identified 'file_id' as its own 'rowid' column and
> made in INDEX for it.
>

it's also marked as primary key

> You should make an index on the columns 'file_id' and 'data_type' from the
> 'DATA' table.  This will allow it to be searched far more quickly.  Your
> command will be something like
>
> CREATE UNIQUE INDEX dfd ON data (file_id,data_type)
>
> Then do the above testing again.
>

tried that and it didn't help much

> If it's still not fast enough, one possibility would be to create a TABLE
> which actually reflects your data.  Presumably one that reflects your layout
>
> file_id | name | duration | genre | artist | description | album | track_nr
> | year
>
> You could use TRIGGERs to make this table change whenever your DATA table
> changes.  Or you could do it in software.  Or you could abandon your DATA
> table entirely.

thanks, I will try this. Do you think that setting a trigger will have a
performance impact on the insert statements? I receive the file list from
the network in chunks of 1000 files and save them in the db and this also
has to be fast (it's currently less then 500 millis per insert), or is it
better to create this table after the initial sync and set the trigger only
for future changes?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compile sqlite3 with g++? Do I need a wrapper?

On Tue, Jun 28, 2011 at 8:55 PM, Charles Samuels wrote:

> However, be warned that if you use exceptions, you can't use sqlite3_exec,
> because then the exceptions can't make it through the C code. It's easy
> enough
> to roll your own sqlite3_exec and compile it as C++.
>

To expand upon that a bit...

It's generally considered bad practice to allow C++ exceptions to "cross
module boundaries", so if you're linking to sqlite3.dll/so and exec calls
your callback, it has cross such a boundary. As a general rule, one should
not allow any exceptions to go back across that boundary. If you do, you've
entered the world of undefined behaviour. In my experience it actually works
with 3rd-party C++ libraries on Linux systems, but it's not guaranteed to as
far as i understand. Then again, dlsym() (which plays are part in most
DLL-loading code on Unix-like system) relies on undefined behaviour (casting
a (void*) to a function and dereferencing it), and its use is still
prevelant (and works, despite The Standard). i.e. it works, but it's not
really guaranteed to. Kinda like email, i guess.

The legendary Herb Sutter wrote about it in "C++ Coding Standards", if i'm
not mistaken.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query

On Tue, Jun 28, 2011 at 8:50 PM, thilo  wrote:

> > use SINGLE quotes, not double quotes.
> bummer,  Thanks a lot
>

i PROMISE that you won't find such an obvious bug in sqlite3 ;).

sqlite uses single quotes because that's what ANSI SQL specifies. MySQL uses
(or can use) double quotes, but that is an unportable SQL extension.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compile sqlite3 with g++? Do I need a wrapper?

2011-06-28 Thread Charles Samuels

On Tuesday, June 28, 2011 9:36:22 f.h. Stephan Beal wrote:
> There is NOTHING wrong with mixing .c and .cpp files in one C++ project.
> Compile the C code with gcc and C++ code with g++, and then link them
> together as you would any other objects.

Compiling sqlite as C++ is "hopeless", so this is a good idea!

However, be warned that if you use exceptions, you can't use sqlite3_exec, 
because then the exceptions can't make it through the C code. It's easy enough 
to roll your own sqlite3_exec and compile it as C++.

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

Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query

2011-06-28 Thread thilo

On 6/28/2011 8:45 PM, Stephan Beal wrote:
> On Tue, Jun 28, 2011 at 8:42 PM, thilo  wrote:
>
>> sqlite> select (select v from t1 where n="a") wrong,*  from a1;
>>
>
> use SINGLE quotes, not double quotes.
bummer,  Thanks a lot

thilo


-- 
Dipl. Ing. Thilo Jeremias
Zur Rabenwiese 14
27239 Twistringen
T: +49 15782492240
T: +49 4243941633

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

Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query

2011-06-28 Thread Black, Michael (IS)

Use single quotes instead of double



sqlite> select (select v from t1 where n='a') wrong,*  from a1;
wrong|a|b
2000|123|456
2000|999|999



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 thilo [th...@nispuk.com]
Sent: Tuesday, June 28, 2011 1:42 PM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] Is this a BUG or am I wrong ? sub-select returns nothing 
when column id matches the inner query

Hello there!

I found a strange behavior while doing a
select with a sub select that has a where clause with a value (here
'a') which is the same as a column id:

What am I missing here ?


SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .mode column
sqlite> .header on

sqlite> select (select v from t1 where n="a") wrong,*  from a1;

wrong   a   b
--  --  --
123 456
999 999
sqlite>
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE a1 (a int,b int);
INSERT INTO "a1" VALUES(123,456);
INSERT INTO "a1" VALUES(999,999);
CREATE TABLE "t1" (n text primary key on conflict replace,v integer);
INSERT INTO "t1" VALUES('good',1000);
INSERT INTO "t1" VALUES('a',2000);
COMMIT;
sqlite>

Any light to the problem (especially if it is already fixed) is very
much appreciated.

My OS is NetBSD,

cheers thilo


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

Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query

2011-06-28 Thread Jim Morris

string literals are enclose in single quotes not double quotes"

select (select v from t1 where n='a') wrong,*  from a1;



On 6/28/2011 11:42 AM, thilo wrote:
> select (select v from t1 where n="a") wrong,*  from a1;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query

On Tue, Jun 28, 2011 at 8:42 PM, thilo  wrote:

> sqlite> select (select v from t1 where n="a") wrong,*  from a1;
>


use SINGLE quotes, not double quotes.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query

2011-06-28 Thread thilo

Hello there!

I found a strange behavior while doing a
select with a sub select that has a where clause with a value (here
'a') which is the same as a column id:

What am I missing here ?


SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .mode column
sqlite> .header on

sqlite> select (select v from t1 where n="a") wrong,*  from a1;

wrong   a   b
--  --  --
123 456  
999 999  
sqlite>
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE a1 (a int,b int);
INSERT INTO "a1" VALUES(123,456);
INSERT INTO "a1" VALUES(999,999);
CREATE TABLE "t1" (n text primary key on conflict replace,v integer);
INSERT INTO "t1" VALUES('good',1000);
INSERT INTO "t1" VALUES('a',2000);
COMMIT;
sqlite>

Any light to the problem (especially if it is already fixed) is very
much appreciated.

My OS is NetBSD,

cheers thilo


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

Re: [sqlite] Compile sqlite3 with g++? Do I need a wrapper?

On Tue, Jun 28, 2011 at 8:13 PM, Simon Slavin  wrote:

> That's clever.  And it allows quick regression testing in case something
> mysteriously stops working.
>

:-D

Here's the makefile code... it of course relies on other project details,
but you'll get the idea:


# Set up a local/embedded copy of sqlite3...
# i found out afterwards: http://www.sqlite.org/compile.html#omitfeatures
# The use of -DSQLITE_OMIT_xxx here isn't supposed to work, but it seems
# to do what i want (remove the requirement for -ldl).
ifeq (2,$(words $(wildcard ./sqlite3.c ./sqlite3.h)))
SQLITE3.O := sqlite3.o
CPDO_ENABLE_SQLITE3 := 1
#LDFLAGS_SQLITE3 := -L. -lsqlite3
LDFLAGS_SQLITE3 := sqlite3.o
sqlite3.c:
sqlite3.h:
$(SQLITE3.O): sqlite3.c sqlite3.h
$(SQLITE3.O): CPPFLAGS:=-I. \
$(CPPFLAGS) \
-DSQLITE_THREADSAFE=0 \
-DSQLITE_TEMP_STORE=3 \
-DSQLITE_OMIT_LOAD_EXTENSION=1 \
$(CFLAGS_C99)
libsqlite3.a: $(SQLITE3.O)
$(AR) crs $@ $(SQLITE3.O)
# C99: sqlite3.h does not compile cleanly in C89 mode
# due to its use of (long long).
#CPPFLAGS:=-I. $(filter-out -I.,$(CPPFLAGS))
#$(SQLITE3.O): CPPFLAGS:=-I.
cson_amalgamation.o: libsqlite3.a
cson_amalgamation.o: CPPFLAGS:=-I. $(CFLAGS_C99)# workaround for a bit of
sloppiness in sqlite3.h
endif
# /local sqlite3


ACHTUNG: that's a makefile and needs HARD TABS, which will almost certainly
be lost through the email path.
-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compile sqlite3 with g++? Do I need a wrapper?

On 28 Jun 2011, at 7:04pm, Stephan Beal wrote:

> in my latest
> sqlite3-using project i structured the build so that if sqlite3.[ch] are
> found in the build tree, that sqlite3 is used, otherwise we use whatever's
> on the system. i did that because when i launched my project on my web
> hoster i found that i couldn't compile there because they have an ancient
> (3.3?) sqlite3. So i added the option to add your own amalgamation to my
> tree.

That's clever.  And it allows quick regression testing in case something 
mysteriously stops working.

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

Re: [sqlite] Compile sqlite3 with g++? Do I need a wrapper?

On Tue, Jun 28, 2011 at 8:04 PM, Stephan Beal  wrote:

> You're both very right, and might i suggest a compromise: in my latest
>

Might i add that all involved machines were some flavour of Linux, which
favour's Simon's argument against relying on the system's sqlite3.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compile sqlite3 with g++? Do I need a wrapper?

On Tue, Jun 28, 2011 at 7:53 PM, Simon Slavin  wrote:

> On 28 Jun 2011, at 5:34pm, Jan Hudec wrote:
> > Let me express very, very strong disagreement with that. In Linux you
> should
> > *always* use system sqlite and specify minimal required version as
> desired
>

> This works only if you have some idea what your users will be running.
>  Linux
>

You're both very right, and might i suggest a compromise: in my latest
sqlite3-using project i structured the build so that if sqlite3.[ch] are
found in the build tree, that sqlite3 is used, otherwise we use whatever's
on the system. i did that because when i launched my project on my web
hoster i found that i couldn't compile there because they have an ancient
(3.3?) sqlite3. So i added the option to add your own amalgamation to my
tree.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compile sqlite3 with g++? Do I need a wrapper?

On 28 Jun 2011, at 5:34pm, Jan Hudec wrote:

> On Tue, Jun 28, 2011 at 11:07:50 +, Black, Michael (IS) wrote:
>> I'd recommend NOT relying on the system sqlite3.  That way you can control 
>> your changes.
> 
> Let me express very, very strong disagreement with that. In Linux you should
> *always* use system sqlite and specify minimal required version as desired.
> 
> That saves space (only one system-wide shared library with the code), memory
> (one shared library is only loaded once) and allows upgrading the library
> without having to recompile the application, which means when important fix
> is made in sqlite, you don't have to recompile all the applications and saves
> precious time of your friendly distribution package maintainer.

This works only if you have some idea what your users will be running.  Linux 
doesn't have one package maintainer it has many.  You don't know which one your 
users will be using.  Your users may be running other apps which use SQLite.  
Those apps may rely on the behaviour of one version of SQLite and you may rely 
on the behaviour of another.  If you both link to the system-wide shared 
library, one of you will be in trouble.

Your setup works fine if you're writing for yourself, or for an organisation 
where you have some contral over the computers.  Not when you're writing an app 
for other people.

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

[sqlite] Documentation for when/what exceptions are thrown?

2011-06-28 Thread Down, Jason

Wow, not sure how I missed that. Seems obvious now thanks.

I do have one more question though. While this does show great documentation, 
it still does not tell me what methods throw certain exceptions. For example, 
if I look at SqliteCommand.ExecuteNonQuery, it does not list that it can throw 
an exception (neither with Intellisense or in the actual help documentation). 
However, I do see that it implements IDbCommand.ExcecuteNonQuery. If I look up 
that method on MSDN, I see that it throws an InvalidOperationException if the 
connection is not open or does not exist.

So does this mean the only way I am going to know if a method throws an 
exception is to look up the base classes or interfaces on MSDN? What about 
SqliteException objects themselves? I see the object in the documentation, but 
I have no way of knowing which methods throw this type of exception.

-Jason-



This e-mail may contain privileged, private, proprietary or confidential 
information which is intended only for the intended recipient.  If you are not 
the intended recipient, or the person responsible for delivering this message 
to the intended recipient, you are notified that any dissemination, 
distribution or copying of this communication is strictly prohibited.  If you 
have received this communication in error, please reply with the notation 
"Received in Error" in the subject line and delete the e-mail from your 
computer.  

Ce courriel renferme des renseignements privilégiés, privés, exclusifs ou 
confidentiels et est destiné uniquement à l’usage de la personne à laquelle il 
est adressé. S’il ne vous est pas destiné ou si vous n’avez pas la 
responsabilité de le remettre à son destinataire, sachez que toute diffusion, 
distribution ou reproduction en est strictement interdite. Si vous avez reçu ce 
courriel par erreur, veuillez en informer l’auteur, en indiquant dans le champ 
«Objet :» la mention «Courriel reçu par erreur», et le supprimer immédiatement.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help needed to optimize a query

2011-06-28 Thread Jan Hudec

On Tue, Jun 28, 2011 at 17:33:23 +0300, Lazarus 101 wrote:
> Hi guys, i'm working on an Android app and using sqlite to store some data
> and i need some help with a query.
> 
> I have the following table structure:
> 
> FILES
> file_id INTEGER NOT NULL,
   Do file_ids repeat? If not, it should be "integer
  primary key". Than sqlite will alias the column to the "rowid"
  column the table is internally ordered by for improved performance
  looking up by file_id and saving some space (one fewer columns).
> name TEXT
> 
> DATA
> file_id INTEGER REFERENCES files(resource_id) ON DELETE CASCADE,
> data_type TEXT,

If nothing else, you want to define integer identifiers for the data types
and use integer here. That will save you some space (reading from flash is
still performance bottleneck, especially if the flash is SD card) and some
unnecessarily costly string comparisons. In code, you'd obviously use
symbolic constants.

> value TEXT

Obviously you need index on DATA(file_id, data_type, value)

Yes, all three columns. The first two will appear in your query, so have to
be first two having the result also included in the index saves time, because
now everything can be obtained from the index and the table itself does not
have to be fetched.

> data_type can be one of:
> (title, description, date_taken, latitude,longitude, duration,
> album,track_nr,genre, artist, year)

Why this "hypernormalized" structure. When the set is fixed and there can be
only one of each for each file, a big table with one column for each
attribute will do you much better service. Just create FILES with

file_id integer primary key,
name text,
title text,
description text,
date_taken text, /* or integer if you decide to store timestamps instead */
latitude number,
longitude number,
duration number,
album text,
track_nr integer,
genre text,
artist text,
year integer

Or you can split up to generic attributes, photograph attributes (date_taken,
latitude, longitude) and song attributes (duration, album, track_nr, genre,
artist, year), but I don't think you would save space (the extra row id and
foreign key will take more than the nulls) and you definitely wouldn't save
time.

You certainly want index on the name column as I suppose that will be your
initial input.

You can combine it with separate table for data with multiple values per
file. E.g. tags:

file_id integer references files(file_id) on delete cascade,
tag text,
value text

If it's user-defined tags, you want text, but if it's just additional
multi-valued entries defined by the application, use integer identifiers to
save space and unnecessarily costly string comparisons.

> Now, I want to select all music details in the following format
> file_id | name | duration | genre | artist | description | album | track_nr
> | year

Of course with the less normalized structure, that's result of simple select
from single table.

> it's possible that a music from files does not have any data associated with
> it, in this case i want the name to be in the result set
> 
> This has to run as fast as possible. A left join between these tables is too
> slow, for 10.000 entries it takes around 15 seconds just to navigate through
> the cursor, if I add a where clause selecting only one kind of data then it
> reduces to less than 5 seconds which is acceptable.

Last but not least, "explain query plan" is your friend. If you prefix your
query with "explain query plan" and run it against the database (you can use
the command-line shell or some management tool), sqlite will tell you what
tables it would read, in which order, using which indices and how big it
expects the result set to be.

So create various indices and experiment with tweaking the query and look
what explain query plan tells you. Reading by primary key is fastest,
followed by covering index, noncovering index and temporary index or linear
search are worst (except join by temporary b-tree is near-optimal when you
are not filtering out anything).

Sqlite before 3.7.4 didn't support temporary indices and temporary b-tree
joins, so it is much more critical to have good indices in older versions,
because they did happily regress to quadratic or worse complexity and that
would take ages to complete.

Oh, and remember to remove the indices you end up not using to save space
and time needed to keep them up to date.

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

Re: [sqlite] Compile sqlite3 with g++? Do I need a wrapper?

On Tue, Jun 28, 2011 at 6:45 AM, Phong Cao  wrote:

> However, the program was not compiled. I also read on some forums saying
> that sqlite3 must be compiled with gcc. But since I am using gtkmm and C++
> code for my project I wonder if there is anyway possible to compile sqlite3
> using g++? If so, please tell me how.
>

There is NOTHING wrong with mixing .c and .cpp files in one C++ project.
Compile the C code with gcc and C++ code with g++, and then link them
together as you would any other objects.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compile sqlite3 with g++? Do I need a wrapper?

2011-06-28 Thread Jan Hudec

On Tue, Jun 28, 2011 at 11:07:50 +, Black, Michael (IS) wrote:
> I'd recommend NOT relying on the system sqlite3.  That way you can control 
> your changes.

Let me express very, very strong disagreement with that. In Linux you should
*always* use system sqlite and specify minimal required version as desired.

That saves space (only one system-wide shared library with the code), memory
(one shared library is only loaded once) and allows upgrading the library
without having to recompile the application, which means when important fix
is made in sqlite, you don't have to recompile all the applications and saves
precious time of your friendly distribution package maintainer.

> Get the amalgamation and put sqlite3.c and sqlite3.h in your project.

That should only be done if you either need to modify it or if you are
building for platform without decent package manager.

> On Monday, June 27, 2011 11:45 PM, Phong Cao [phn...@gmail.com] wrote:
> > I am trying to use g++ to compile my C++ application, which uses sqlite3.
> > After googling for several hours this is what I tried:
> > 
> > g++ -g /home/phongcao/main.cc -o -lsqlite3 /home/phongcao/main `pkg-config
 ^ as already pointed out, -o needs an
   argument
> > --cflags --libs gtkmm-2.4`

(no, since sqlite is a shared library and pkg-config already lists all
necessary dependencies, you shouldn't need any extra libraries).

> > However, the program was not compiled.

Without also seeing the error message it produced, I can't really help you
(beyond the missing argument to -o, which might well be the main problem
though).

> > I also read on some forums saying that sqlite3 must be compiled with gcc.

You are not compiling sqlite3.

> > But since I am using gtkmm and C++ code for my project I wonder if there
> > is anyway possible to compile sqlite3 using g++? If so, please tell me
> > how.

Compilation of C and/or C++ program has two stages, compiling (from single .c
or .cc to .o) and linking (from bundch of .o to binary or .so). The gcc and
g++ are just wrappers that control both and just launch appropriate programs
for the stages (cpp, cc1 and as for compilation and ld for linking).

The only difference between gcc and g++ is the set of options they pass to
the lower level tools, the gcc being tuned to plain old C and g++ being tuned
to C++, which is superset of those for C.

Since you normally always compile one file at a time, you can compile the .c
sources with gcc and the .cc (or .cpp) sources with g++. If you produced some
of the objects with g++, you should also use g++ to control linking to get
appropraite default libraries (but you can use gcc or ld directly and pass
the necessary options manually).

Ok, what you are doing above is combining both steps in one g++ invocation.
Both gcc and g++ can do it, g++ can accept mixture of .c and .cc files in
this case. It's however not recommended for projects with more than one
source, because the compilation step takes quite long, especially for C++, so
there is a lot of time to be saved by recompiling only those sources that
changed and than linking it all together (using make to find out what needs
to be done).

As I said above, you are not compiling sqlite. It is already provided by the
system and you are only linking it and mixture of C and C++ objects is
correctly linked by g++.

If you decided to use the amalgamation (which I personally don't recommend
for Linux, but you may need it on some other system), you'd compile the
sqlite3.c to sqlite3.o with gcc and than list the sqlite3.o along with all
your other objects for the link. You don't want to list sqlite3.c there,
because you would be compiling each time you change your project when you
actually wont change sqlite3.c at least until next sqlite release.

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

Re: [sqlite] Documentation for when/what exceptions are thrown?

2011-06-28 Thread Alessandro Caliaro

you should find in "C:\Program Files\SQLite.NET\Doc\"

-Messaggio originale- 
From: Down, Jason
Sent: Tuesday, June 28, 2011 6:01 PM
To: 'sqlite-users@sqlite.org'
Subject: [sqlite] Documentation for when/what exceptions are thrown?

I'm having trouble finding documentation for the Sqlite.Net data provider 
(System.Data.Sqlite.dll - http://sqlite.phxsoftware.com/) (new development 
has forked here - 
http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki) that 
lists what exceptions are thrown by the various classes and their methods. I 
know there are SqliteExceptions that can be thrown, but when?

I have ensured I have the accompanying System.Data.Sqlite.xml documentation 
file, but it doesn't appear to list what exceptions are thrown by each 
method.

I don't want to wrap unnecessary code into try/catch blocks (or blindly 
catch a generic Exception object everywhere).

Does anyone know where I may find such documentation? I know that 
SqliteConnection is derived from DbConnection 
(http://msdn.microsoft.com/en-us/library/system.data.common.dbconnection%28v=VS.90%29.aspx),

and SqliteCommand is derived from DbCommand 
(http://msdn.microsoft.com/en-us/library/system.data.common.dbcommand%28v=VS.90%29.aspx),

so if necessary I guess I can look at the documentation there. However, none 
of the base classes will list SqliteException, so when will those type of 
exceptions be thrown?

PS - I'm using version 1.0.64 (from 2009... can't upgrade at this time, but 
it's not outside the realm of possibility if necessary).

Thanks

Jason Down
HomeInfo/HAMR Development - Senior Programmer/Analyst
Home Hardware Stores Limited - Retail Applications
Phone: 519.664.4670
Fax: 519.664.3718
34 Henry St W
St. Jacobs, Ontario
N0B 2N0
jason.d...@homehardware.ca

*  Please consider the environment before printing this e-mail.

This e-mail may contain privileged, private, proprietary or confidential 
information which is intended only for the intended recipient.  If you are 
not the intended recipient, or the person responsible for delivering this 
message to the intended recipient, you are notified that any dissemination, 
distribution or copying of this communication is strictly prohibited.  If 
you have received this communication in error, please reply with the 
notation "Received in Error" in the subject line and delete the e-mail from 
your computer.

Ce courriel renferme des renseignements privilégiés, privés, exclusifs ou 
confidentiels et est destiné uniquement à l’usage de la personne à laquelle 
il est adressé. S’il ne vous est pas destiné ou si vous n’avez pas la 
responsabilité de le remettre à son destinataire, sachez que toute 
diffusion, distribution ou reproduction en est strictement interdite. Si 
vous avez reçu ce courriel par erreur, veuillez en informer l’auteur, en 
indiquant dans le champ «Objet :» la mention «Courriel reçu par erreur», et 
le supprimer immédiatement.
___
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] Documentation for when/what exceptions are thrown?

2011-06-28 Thread Down, Jason

I'm having trouble finding documentation for the Sqlite.Net data provider
(System.Data.Sqlite.dll - http://sqlite.phxsoftware.com/) (new development has
forked here -
http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki) that lists
what exceptions are thrown by the various classes and their methods. I know
there are SqliteExceptions that can be thrown, but when?

I have ensured I have the accompanying System.Data.Sqlite.xml documentation
file, but it doesn't appear to list what exceptions are thrown by each method.

I don't want to wrap unnecessary code into try/catch blocks (or blindly catch a
generic Exception object everywhere).

Does anyone know where I may find such documentation? I know that
SqliteConnection is derived from DbConnection
(http://msdn.microsoft.com/en-us/library/system.data.common.dbconnection%28v=VS.90%29.aspx),
and SqliteCommand is derived from DbCommand
(http://msdn.microsoft.com/en-us/library/system.data.common.dbcommand%28v=VS.90%29.aspx),
so if necessary I guess I can look at the documentation there. However, none
of the base classes will list SqliteException, so when will those type of
exceptions be thrown?

PS - I'm using version 1.0.64 (from 2009... can't upgrade at this time, but
it's not outside the realm of possibility if necessary).

Thanks

Jason Down
HomeInfo/HAMR Development - Senior Programmer/Analyst
Home Hardware Stores Limited - Retail Applications
Phone: 519.664.4670
Fax: 519.664.3718
34 Henry St W
St. Jacobs, Ontario
N0B 2N0
jason.d...@homehardware.ca

* Please consider the environment before printing this e-mail.

This e-mail may contain privileged, private, proprietary or confidential
information which is intended only for the intended recipient. If you are not
the intended recipient, or the person responsible for delivering this message
to the intended recipient, you are notified that any dissemination,
distribution or copying of this communication is strictly prohibited. If you
have received this communication in error, please reply with the notation
"Received in Error" in the subject line and delete the e-mail from your
computer.

Ce courriel renferme des renseignements privilégiés, privés, exclusifs ou
confidentiels et est destiné uniquement à l’usage de la personne à laquelle il
est adressé. S’il ne vous est pas destiné ou si vous n’avez pas la
responsabilité de le remettre à son destinataire, sachez que toute diffusion,
distribution ou reproduction en est strictement interdite. Si vous avez reçu ce
courriel par erreur, veuillez en informer l’auteur, en indiquant dans le champ
«Objet :» la mention «Courriel reçu par erreur», et le supprimer immédiatement.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Selecting indexes to use & NOT INDEXED

2011-06-28 Thread Mohit Sindhwani

Hi Igor and Puneet,

On 27/6/2011 11:47 PM, Igor Tandetnik wrote:
> You can suppress the index on CAT with a unary plus operator, like this:
> ... AND +CAT=25;

Thanks for the suggestions.  I'll try these :)

Best Regards,
Mohit.
28/6/2011 | 11:50 PM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using Unicode to display data?

2011-06-28 Thread Gilles Ganault

On Tue, 28 Jun 2011 07:48:09 -0400, Richard Hipp
 wrote:
>SQLite uses only unicode (utf8 by default, but it also works with utf16).
>Probably your DBF file was exported as MBCS.  You need to convert the MBCS
>from the export into utf8 or utf16 prior to import into SQLite.

Thanks for the tip. I'll check if the DBD-to-SQLite converter can
export in Unicode.

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

Re: [sqlite] Auto index with wrong number of entries


On 28 Jun 2011, at 4:22pm, Adam DeVita wrote:

> I can see the data that I want to export.   How do I fix these indexes?

Use the sqlite3 command-line shell to dump the database to SQL commands, then 
create a new database by reading it back in.

While the data is in the SQL command file, you can take a look and make sure 
those records are present.

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

[sqlite] Auto index with wrong number of entries

2011-06-28 Thread Adam DeVita

Good day,

Following a data collection & reporting error from a workstation, I have
found that

pragma integrity_check


reported that 2 of my tables have a few thousand entries missing in their
auto indexes.
wrong number of entries in index sqlite_auto_index_tablename_1
rowid 87973 missing from ... table above.


I can see the data that I want to export.   How do I fix these indexes?

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

Re: [sqlite] help needed to optimize a query

On 28 Jun 2011, at 3:33pm, Lazarus 101 wrote:

> FILES
> file_id INTEGER NOT NULL,
> name TEXT

I assume that SQLite has identified 'file_id' as its own 'rowid' column and 
made in INDEX for it.

> DATA
> file_id INTEGER REFERENCES files(resource_id) ON DELETE CASCADE,
> data_type TEXT,
> value TEXT
> 
> data_type can be one of:
> (title, description, date_taken, latitude,longitude, duration,
> album,track_nr,genre, artist, year)
> 
> as you've probably guessed this is used to store information about some
> media files.
> 
> Now, I want to select all music details in the following format
> file_id | name | duration | genre | artist | description | album | track_nr
> | year
> 
> it's possible that a music from files does not have any data associated with
> it, in this case i want the name to be in the result set
> 
> This has to run as fast as possible. A left join between these tables is too
> slow, for 10.000 entries it takes around 15 seconds just to navigate through
> the cursor

You should make an index on the columns 'file_id' and 'data_type' from the 
'DATA' table.  This will allow it to be searched far more quickly.  Your 
command will be something like

CREATE UNIQUE INDEX dfd ON data (file_id,data_type)

Then do the above testing again.

If it's still not fast enough, one possibility would be to create a TABLE which 
actually reflects your data.  Presumably one that reflects your layout

file_id | name | duration | genre | artist | description | album | track_nr | 
year

You could use TRIGGERs to make this table change whenever your DATA table 
changes.  Or you could do it in software.  Or you could abandon your DATA table 
entirely.

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

Re: [sqlite] help needed to optimize a query

2011-06-28 Thread Pavel Ivanov

> This has to run as fast as possible. A left join between these tables is too
> slow, for 10.000 entries it takes around 15 seconds just to navigate through
> the cursor, if I add a where clause selecting only one kind of data then it
> reduces to less than 5 seconds which is acceptable.

What kind of selects did you do here?

Generally in my opinion if you want to get all data as one table with
one file per row you shouldn't do that using just SELECT statement.
You should get raw data with SELECT and then transform it into the
necessary table format in your application.


Pavel


On Tue, Jun 28, 2011 at 10:33 AM, Lazarus 101  wrote:
> Hi guys, i'm working on an Android app and using sqlite to store some data
> and i need some help with a query.
>
> I have the following table structure:
>
> FILES
> file_id INTEGER NOT NULL,
> name TEXT
>
> DATA
> file_id INTEGER REFERENCES files(resource_id) ON DELETE CASCADE,
> data_type TEXT,
> value TEXT
>
> data_type can be one of:
> (title, description, date_taken, latitude,longitude, duration,
> album,track_nr,genre, artist, year)
>
> as you've probably guessed this is used to store information about some
> media files.
>
> Now, I want to select all music details in the following format
> file_id | name | duration | genre | artist | description | album | track_nr
> | year
>
> it's possible that a music from files does not have any data associated with
> it, in this case i want the name to be in the result set
>
> This has to run as fast as possible. A left join between these tables is too
> slow, for 10.000 entries it takes around 15 seconds just to navigate through
> the cursor, if I add a where clause selecting only one kind of data then it
> reduces to less than 5 seconds which is acceptable.
>
> Thanks a lot for any input.
> ___
> 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] help needed to optimize a query