Re: [sqlite] help needed to optimize a query

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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:

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

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

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

[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

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:

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

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

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

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 >

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

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

Re: [sqlite] help needed to optimize a query

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

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

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

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

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

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

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

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.

Re: [sqlite] Using Unicode to display data?

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

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

[sqlite] Auto index with wrong number of entries

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

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

Re: [sqlite] help needed to optimize a query

> 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

[sqlite] help needed to optimize a query

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

Re: [sqlite] Using Unicode to display data?

Gilles Ganault wrote: > After importing DBF files into SQLite, it looks like data are in > Unicode Actually, it doesn't look like Unicode, but rather some ANSI codepage (my guess would be 1252, Western European). Show the output of this statement: select hex(name) from

Re: [sqlite] Using Unicode to display data?

On Tue, Jun 28, 2011 at 7:24 AM, Gilles Ganault wrote: > Hello > >After importing DBF files into SQLite, it looks like data are in > Unicode, so I get funny characters when running sqlite3.exe in a DOS > box on Windows: > SQLite uses only unicode (utf8 by default,

[sqlite] Using Unicode to display data?

Hello After importing DBF files into SQLite, it looks like data are in Unicode, so I get funny characters when running sqlite3.exe in a DOS box on Windows: sqlite> select * from varmod_stent2010 limit 5; A10|BE|Industrie manufacturiFre, industries extractives et autres

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

I'd recommend NOT relying on the system sqlite3. That way you can control your changes. Get the amalgamation and put sqlite3.c and sqlite3.h in your project. And, you forgot to put in the name for "-o" -- so you would get a file named "-lsqlite3" in your directory. And you'll probably

Re: [sqlite] Functions and index

On 2011-06-27 17:34 Simon Davies wrote: > select julianday( ( select max( day_date ) from days ) ); Of course I tried this, but with a single bracket I got a syntax error. With double bracket it works. Thanks! Adam ___