Re: [sqlite] ambiguous temporary trigger metadata

Hi Richard, Thank you for replying and for updating the documentation. I didn't realize that the trigger may be unexpectedly reattached to a different table when the schema changes. If this is the case then perhaps the creation of temp triggers on non-temp tables using non-qualified table names

Re: [sqlite] ambiguous temporary trigger metadata

On Sat, Oct 19, 2013 at 9:17 PM, Bogdan Ureche wrote: > What would be a good way to extract metadata information about temporary > triggers? If there are multiple tables with the same name in main and temp > databases (or even attached databases), I could not find a way to

[sqlite] ambiguous temporary trigger metadata

What would be a good way to extract metadata information about temporary triggers? If there are multiple tables with the same name in main and temp databases (or even attached databases), I could not find a way to determine on which table a temporary trigger was created by examining the available

Re: [sqlite] SQLite keeps on searching endlessly

On 19 Oct 2013, at 8:30pm, Raheel Gupta wrote: > Does SQLite support multi column primary keys ? Yes. > Also wouldnt primary keys actually slow down further inserts. I have > queries to insert nearly 1 rows in one second. With larger database > multi column primary

Re: [sqlite] migration from mysql to sqlite

On 19 Oct 2013, at 9:06pm, Mohsen Pahlevanzadeh wrote: > 1. how can define a unicode field and tables? In SQLite all text fields are unicode fields. Just make sure you are feeding them text which is in unicode. Making sure unicode fields are sorted in the order

Re: [sqlite] SQLite keeps on searching endlessly

On 10/19/13, Raheel Gupta wrote: > Does SQLite support multi column primary keys ? Yes. > Also wouldnt primary keys actually slow down further inserts. I have > queries to insert nearly 1 rows in one second. With larger database > multi column primary keys might slow

[sqlite] migration from mysql to sqlite

Dear all, I'm newbie in sqlite, and i migrated to sqlite from mysql. I have the folloiwng questions: 1. how can define a unicode field and tables? 2. how to define date type? I created my table such as: CREATE TABLE `buyers` ( `id` bigint(20) NOT NULL , `name` varchar(200) DEFAULT

Re: [sqlite] SQLite keeps on searching endlessly

Hi, >> First, consider if some combination of those columns constitute a primary key. That would be stronger than a simple index. Does SQLite support multi column primary keys ? Also wouldnt primary keys actually slow down further inserts. I have queries to insert nearly 1 rows in one second.

Re: [sqlite] SQLite keeps on searching endlessly

On Sat, 19 Oct 2013 21:21:44 +0530 Raheel Gupta wrote: > CREATE INDEX map_index ON map (n, s, d, c, b); > > The above table is having nearly 600 Million Records and is of size > 26 GB. The column 'n' is representing Numbers of Blocks on the file > system. 's' stands for

Re: [sqlite] SQLite keeps on searching endlessly

On 19 Oct 2013, at 7:29pm, Raheel Gupta wrote: > My current index is actually in the correct order of my query. > I use 'n' and 's' and they are the first in the query. This isn't how SQLite works. It's more clever than that. SQlite will analyze your WHERE clause and do

Re: [sqlite] SQLite keeps on searching endlessly

> I cannot definitely solve your problem but I can think of some things to > try. First, do these: > > ANALYZE; > CREATE INDEX map_dsn ON map (d, s, n); > CREATE INDEX map_dns ON map (d, n, s); > > then execute the same SELECT. Does it have the same problem ? Does the > EXPLAIN QUERY PLAN tell

Re: [sqlite] SQLite keeps on searching endlessly

On 19 Oct 2013, at 7:16pm, Fabian Büttner wrote: > Shouldn't ANALZYE be run _after_ creating the indexes? "The ANALYZE command gathers statistics about tables and indices" Whoops. Yes. Thanks. Simon. ___ sqlite-users

Re: [sqlite] SQLite keeps on searching endlessly

>> See if the situation changes if you drop all those single quotes around your constants. Why are you comparing integer values to string literals? Tried that and it doesnt change. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] SQLite keeps on searching endlessly

I cannot definitely solve your problem but I can think of some things to try. First, do these: ANALYZE; CREATE INDEX map_dsn ON map (d, s, n); CREATE INDEX map_dns ON map (d, n, s); then execute the same SELECT. Does it have the same problem ? Does the EXPLAIN QUERY PLAN tell you which

Re: [sqlite] SQLite keeps on searching endlessly

Raheel Gupta wrote: > > CREATE INDEX map_index ON map (n, s, d, c, b); > > > > SELECT n, c, b, s FROM map WHERE s <= '326' AND s NOT IN (0) AND d = '15' > > AND n >= '15591116' ORDER BY n ASC LIMIT 0, 32768 > > 0|0|0|SEARCH TABLE map USING COVERING INDEX map_index (n>?) (~4166 rows) >

Re: [sqlite] SQLite keeps on searching endlessly

You may want to put the columns with the highest selectivity first in your index. The device 15 has nearly 10 entries in the table while the remaining of the 600 Million records belong to another device. E.g., CREATE INDEX map_index ON map (d, ...); Also, you should run ANALYZE map so that

Re: [sqlite] SQLite keeps on searching endlessly

On 19 Oct 2013, at 6:54pm, Raheel Gupta wrote: > Here is the output : > 0|0|0|SEARCH TABLE map USING COVERING INDEX map_index (n>?) (~4166 rows) > 0|0|0|EXECUTE LIST SUBQUERY 1 > > I am not sure how can I optimize this ? > (Also I checked again and there are 4166 rows in

Re: [sqlite] SQLite keeps on searching endlessly

On 10/19/2013 11:51 AM, Raheel Gupta wrote: SELECT n, c, b, s FROM map WHERE s <= '326' AND s NOT IN (0) AND d = '15' AND n >= '15591116' ORDER BY n ASC LIMIT 0, 32768 See if the situation changes if you drop all those single quotes around your constants. Why are you comparing integer values

Re: [sqlite] SQLite keeps on searching endlessly

Hi, Here is the output : 0|0|0|SEARCH TABLE map USING COVERING INDEX map_index (n>?) (~4166 rows) 0|0|0|EXECUTE LIST SUBQUERY 1 I am not sure how can I optimize this ? (Also I checked again and there are 4166 rows in this last result and not 1568 as per my last email.) It seems to be using the

Re: [sqlite] SQLite keeps on searching endlessly

On 19 Oct 2013, at 4:51pm, Raheel Gupta wrote: > CREATE TABLE map ( >n BIGINT NOT NULL DEFAULT 0, >s INT(5) NOT NULL DEFAULT 0, >d INT(5) NOT NULL DEFAULT 0, >c INT(1) NOT NULL DEFAULT 0, >b UNSIGNED BIGINT NOT

[sqlite] SQLite keeps on searching endlessly

Hi, I am facing a peculiar issue with SQLITE. The following is my table structure : CREATE TABLE map ( n BIGINT NOT NULL DEFAULT 0, s INT(5) NOT NULL DEFAULT 0, d INT(5) NOT NULL DEFAULT 0, c INT(1) NOT NULL DEFAULT 0, b UNSIGNED BIGINT

Re: [sqlite] Support For Table Columns In French Language Text

Thank you, I didn't understand that earlier. You saved me a lot of time! Bob On 10/18/13 10:30 PM, Igor Tandetnik wrote: On 10/18/2013 9:32 PM, Bob Cochran wrote: I want to create a table like this: glosskey of type text caption_text of type varchar or text The glosskey and caption_text

Re: [sqlite] sqlite3_last_insert_rowid() problem

On Sat, Oct 19, 2013 at 6:05 AM, Igor Korot wrote: > Hi, Kevin, > > > On Sat, Oct 19, 2013 at 2:59 AM, Kevin Benson >wrote: > > > On Sat, Oct 19, 2013 at 5:46 AM, Igor Korot wrote: > > > > > but on Mac I am getting the warning:

Re: [sqlite] Query "select date(\"now\")" show EPOCH time on ARM- Coertex A15 board

On Sat, Oct 19, 2013 at 5:32 AM, Drake Wilson wrote: > Quoth jitendar kumar , on 2013-10-19 14:48:46 +0530: > > but the same compiled with ARM - Cortex A15 cross compiler and the query > > executed on arm board it gives the output of EPOCH time. i

Re: [sqlite] sqlite3_last_insert_rowid() problem

Hi, Kevin, On Sat, Oct 19, 2013 at 2:59 AM, Kevin Benson wrote: > On Sat, Oct 19, 2013 at 5:46 AM, Igor Korot wrote: > > > but on Mac I am getting the warning: > > > > "Implicit conversion loses integer precision" > > > > So my question is: how do

Re: [sqlite] sqlite3_last_insert_rowid() problem

On Sat, Oct 19, 2013 at 5:46 AM, Igor Korot wrote: > but on Mac I am getting the warning: > > "Implicit conversion loses integer precision" > > So my question is: how do I build my program on Mac without such warning > and so that it will work properly. >

[sqlite] sqlite3_last_insert_rowid() problem

Hi, ALL, Looking at http://www.sqlite.org/c3ref/last_insert_rowid.html, I see that the function is declared as sqlite3_int64 sqlite3_last_insert_rowid(sqlite3*); I am trying to build my program in both MS Windows 7 64-bit using MSVC2010 for 32-bit solution and in Mac OSX Snow Leopard 10.6.8

Re: [sqlite] Query "select date(\"now\")" show EPOCH time on ARM- Coertex A15 board

Quoth jitendar kumar , on 2013-10-19 14:48:46 +0530: > but the same compiled with ARM - Cortex A15 cross compiler and the query > executed on arm board it gives the output of EPOCH time. i guess the > function > gettimeofday() fails to add the time to the default EPOCH time.

[sqlite] Query "select date(\"now\")" show EPOCH time on ARM- Coertex A15 board

Dear SQLITE users, I am facing issue with sqlite3 on arm board. when compiled with gcc and used on X86 PC , the query "select date(\"now\")" shows correct date. but the same compiled with ARM - Cortex A15 cross compiler and the query executed on arm board it gives the output of EPOCH time. i

[sqlite] System.Data.SQLite Deployment Mystery

I'm wondering if there is a bug somehow in the "System.Data.SQLite" dll file. When deploying my application to a clean Windows 7 x64 virtual machine (VMWare Workstation 10), I got the message "Failed to find or load the registered .NET Framework Data Provider" and of course with no database