Re: [sqlite] ambiguous temporary trigger metadata

[Bogdan Ureche]

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 should not be
allowed. Maybe this will be corrected in SQLite4?


> Who are your users that you allow them arbitrary control over the DDL and
> yet you feel the need to protect them from their own actions?
>

This is not about protecting users from their own actions, but rather about
providing them correct feedback as a result of these actions.

I am working on an administration tool for SQLite
 that
allows the execution of user queries and facilitates the table
restructure using a visual editor. Extracting complete metadata information
even for corner cases is vital for the correct execution of the program.
The users are sometimes reporting all kinds of bizarre situations in which
my tool does not display correct information, and I have come to realize
that telling them "don't do this" has the tendency of making them feel
uncomfortable. I have found that a different approach is generally
preferable - that the tool should be able to handle anything the users may
throw at it.

Thank you.

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

Re: [sqlite] ambiguous temporary trigger metadata

[Richard Hipp]

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 determine
> on which table a temporary trigger was created by examining the available
> information in sqlite_temp_master, unless the table name was qualified in
> the trigger creation SQL.
> ...
> And yes, I know these scenarios can be avoided simply by qualifying the
> table name in the trigger declaration - which would change the DDL to
> "CREATE TRIGGER tr1 after insert on .t1..." - but that is beside the
> point. It is not always possible to be in control of the queries the users
> may be executing.
>

The binding is undefined and may shift at unpredictable times.  In other
words: don't do that.  We have updated the documentation at (
http://www.sqlite.org/draft/lang_createtrigger.html#temptrig) to make this
clear.

Who are your users that you allow them arbitrary control over the DDL and
yet you feel the need to protect them from their own actions?
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ambiguous temporary trigger metadata

[Bogdan Ureche]

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
information in sqlite_temp_master, unless the table name was qualified in
the trigger creation SQL.

Example:

The main and temp databases both have a table called "t1".
Querying sqlite_temp_master by type 'trigger' returns a trigger called
"tr1" with the DDL:

CREATE TRIGGER tr1 after insert on t1 begin select 1, 2, 3; end

Was the trigger created on temp.t1 or on main.t1?

After experimenting different scenarios - and applying the changes in the
checkin [56dca4a65c ] without
which the first 2 test cases below are not even possible - I found out that
the table on which the trigger is created depends on the order of creation
of the objects. Consider the following test cases - the only difference
between them is the order of the first 3 lines:

Test case 1:

create table main.t1(c);
create temporary trigger tr1 after insert on t1 begin select raise(abort,
'error'); end;
create table temp.t1(c);
insert into main.t1(c) values(1); -- error is raised here, confirming the
trigger was created on main.t1
insert into temp.t1(c) values(1);

Note: without applying checkin
[56dca4a65c],
a "malformed database schema error" occurs at line 3.

Test case 2:

create table temp.t1(c);
create temporary trigger tr1 after insert on t1 begin select raise(abort,
'error'); end;
create table main.t1(c);
insert into main.t1(c) values(1);
insert into temp.t1(c) values(1); -- error is raised here, confirming the
trigger was created on temp.t1

Test case 3:

create table main.t1(c);
create table temp.t1(c);
create temporary trigger tr1 after insert on t1 begin select raise(abort,
'error'); end;
insert into main.t1(c) values(1);
insert into temp.t1(c) values(1); -- error is raised here, confirming the
trigger was created on temp.t1

However, all three cases above produce the same trigger DDL in
sqlite_temp_master:

typename tbl_name rootpage sql

---   
-

trigger tr1  t1  0 CREATE TRIGGER tr1 after insert on t1 begin
select raise(abort, 'error'); end

Unless I missed something, the information in sqlite_temp_master doesn't
seem to provide useful clues about the table the trigger was created on. Is
there another way?

And yes, I know these scenarios can be avoided simply by qualifying the
table name in the trigger declaration - which would change the DDL to
"CREATE TRIGGER tr1 after insert on .t1..." - but that is beside the
point. It is not always possible to be in control of the queries the users
may be executing.

Perhaps it's time to introduce a "pragma trigger_info()"? :)

Thank you for any insights on this issue.

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

Re: [sqlite] SQLite keeps on searching endlessly

[Simon Slavin]

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 keys might slow down right ?

Yes, but they allow the searches to be faster.  You are making it longer to do 
INSERT but shorter to do SELECT.  Which is best for you depends on your 
purposes.

>> create /* covering */ index ByDS on map(d, s, n);
> 
> Creating this index in my DB. It takes time. Its too big you know.

You only create an index once.

Again, you are making it longer to do INSERT but shorter to do SELECT.  If you 
want fast SELECTs, that's how you do it: you provide an appropriate index.

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

Re: [sqlite] migration from mysql to sqlite

[Simon Slavin]

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 you expect is far more 
complicated.  Try it and see if you get what you want.  If not, post again and 
tell us what wasn't happening the way you wanted.

> 2. how to define date type?

There are five (arguably four) data types in SQLite and you can read about them 
here:



Anything complicated you declare will be ignored.  For instance varchar(200) is 
interpreted as a straight text field with no limit on length.

> I can data entry from my program into mysql but i can't insert into
> sqlite...

What is your programming language ?  What are you doing ?  What result code is 
returned ?

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

Re: [sqlite] SQLite keeps on searching endlessly

[Zsbán Ambrus]

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 down right ?

Not really, not more than it would if you had an ordinary index on the
same columns.  A primary key is almost the same as a separate unique
index in sqlite.  Once you have an index, sqlite has to store inserted
records in the index anyway, and then if that index is unique it can
check the neighbouring entries in the index when it's doing the
insertion.

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

[sqlite] migration from mysql to sqlite

[Mohsen Pahlevanzadeh]

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 NULL,
  `name_type` tinyint(4) DEFAULT NULL,
  `addresses` text ,
  `telephones` text ,
  `emails` varchar(60)  DEFAULT NULL,
  `job_background` varchar(200)  DEFAULT NULL,
  `agent_first_name` varchar(200)  DEFAULT NULL,
  `agent_attributes` text ,
  `agent_last_name` varchar(200)  DEFAULT NULL,
  `agent_values` text ,
  PRIMARY KEY (`id`)
)
Or 
CREATE TABLE `materials` (
  `id` bigint(20) NOT NULL ,
  `name` varchar(200) DEFAULT NULL,
  `bought_price` bigint(20) DEFAULT NULL,
  `bought_date` date DEFAULT NULL,
  `minimum_bound` int(11) DEFAULT NULL,
  `stock` int(11) DEFAULT NULL,
  `imported_party_attributes` text ,
  `imported_party_values` text ,
  `measure_attributes` text ,
  `measure_values` text ,
  `sellers_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
)

I can data entry from my program into mysql but i can't insert into
sqlite...

Where's my problem?

Yours,
Mohsen


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

Re: [sqlite] SQLite keeps on searching endlessly

[Raheel Gupta]

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. With larger database
multi column primary keys might slow down right ?

>> create /* covering */ index ByDS on map(d, s, n);

Creating this index in my DB. It takes time. Its too big you know.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite keeps on searching endlessly

[James K. Lowden]

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 Snapshot ID.
> 'd' is device id
> 'c' is not used and contains 0 all the time.
> 'b' is Block ID which is in another table altogether.
> 
> Now I need to retrieve the block numbers in order for lets say d = 15
> and s <= 326.
> The device 15 has nearly 10 entries in the table while the
> remaining of the 600 Million records belong to another device.

First, consider if some combination of those columns constitute a
primary key.  That would be stronger than a simple index.  

Second, if you're searching for n based on d and s, don't put n at the
front of the index.  

create /* covering */ index ByDS on map(d, s, n);

and watch SQLite fetch your rows in O(log2 n) instead of O(n).  

Third, an unused column promotes confusion and inefficiency.  If you
need a column later, you can add a column later.  

Fourth (if I may), "map" is an unlovely name for a table.  Every row in
any table is a map of key to value.  You might as well call it map_table
(and you wouldn't be the first).  I suspect "device_blocks" might be a
better name.  Perhaps

CREATE TABLE devices (
snapshot integer NOT NULL, 
device integer NOT NULL,
block integer NOT NULL check (block >= 0),
nblocks integer NOT NULL, 
primary key(snapshot, device, block)
);

gives you a table that says what it is, prevents duplication, and lets
you easily fetch block counts by snapshot and device.  

HTH.  

--jkl

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

Re: [sqlite] SQLite keeps on searching endlessly

[Simon Slavin]

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 lots of clever chopping up and rearranging to try to 
find the best index for the fastest and most efficient operation.  The order 
that you specify your requirements in the WHERE clause is ignored.  What's 
important is that you provide an index which lets SQLite give you the results 
efficiently.

One way to figure out the best query is to make lots of indexes in different 
orders, then do an ANALYZE (thank you Fabien), then to use EXPLAIN QUERY PLAN 
to find which index SQLite prefers for your SELECT.  Once you know the one it 
likes you can delete the others to save space.

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

Re: [sqlite] SQLite keeps on searching endlessly

[Raheel Gupta]

> 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 of the (now) three indexes SQLite has
> chosen.
>
> Just in case you didn't know, you can download the SQLite command-line
> tool and execute SQL commands in it.  This means you don't have to change
> your own software.
>

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.
I am trying your suggestion to make the d first in the index as that is
what is the limiting factor in the WHERE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite keeps on searching endlessly

[Simon Slavin]

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 mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite keeps on searching endlessly

[Raheel Gupta]

>> 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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite keeps on searching endlessly

[Fabian Büttner]

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 of the (now) three indexes SQLite has chosen.


Shouldn't ANALZYE be run _after_ creating the indexes?

sqlite> create table t(x int, y int);
sqlite> insert into t values (1,1), (2,1), (3,3);
sqlite> analyze t;
sqlite> select * from sqlite_stat1;
t||3
sqlite> create index i on t(x,y);
sqlite> select * from sqlite_stat1;
t||3
sqlite> analyze t;
sqlite> select * from sqlite_stat1;
t|i|3 1 1

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

Re: [sqlite] SQLite keeps on searching endlessly

[Clemens Ladisch]

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)
> 0|0|0|EXECUTE LIST SUBQUERY 1
>
> It seems to be using the the covering index which I guess is the fastest
> way as Sqlite doesnt need to check the actual table. So why would it slow
> down and use so much CPU in my last query ?

In theory, a multi-column index can be used to speed up searches for
values in multiple columns.  However, an inequality (like n >= 15591116)
can be used only in the _last_ column to be searched in an index, so
this is the only column in this particular query.

Furthermore, this just speeds up searching for the _first_ record that
might match.  SQLite still has to go through all the records following
that in the index.  (On average, this is half of all records.)

> I am not sure how can I optimize this ?

You should have the column with the equality test first in the index,
i.e., "CREATE INDEX xxx ON map(d, n);".  Alternatively, an index on the
columns d and s might be more efficient.


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

Re: [sqlite] SQLite keeps on searching endlessly

[Fabian Büttner]

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 the query planner has accurate 
information about your index.


Fabian

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

Re: [sqlite] SQLite keeps on searching endlessly

[Simon Slavin]

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 this last result and not
> 1568 as per my last email.)
> 
> It seems to be using the the covering index which I guess is the fastest
> way as Sqlite doesnt need to check the actual table. So why would it slow
> down and use so much CPU in my last query ?

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 of the (now) three indexes SQLite has chosen.

Just in case you didn't know, you can download the SQLite command-line tool and 
execute SQL commands in it.  This means you don't have to change your own 
software.

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

Re: [sqlite] SQLite keeps on searching endlessly

[Igor Tandetnik]

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 to string literals?

--
Igor Tandetnik

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

Re: [sqlite] SQLite keeps on searching endlessly

[Raheel Gupta]

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 the covering index which I guess is the fastest
way as Sqlite doesnt need to check the actual table. So why would it slow
down and use so much CPU in my last query ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite keeps on searching endlessly

[Simon Slavin]

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 NULL DEFAULT 0
>);

By the way, SQLite has only one INTEGER type: INTEGER.  It's signed.  All the 
rest of that is ignored.

> 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.
> [snip]
> 
> 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
> 
> [snip]
> After this SQLITE goes into an endless search for some reasons unknown.

You might learn using EXPLAIN QUERY PLAN:



My guess is that SQLite has ended up sorting or constructing an index with many 
non-qualifying entries at the end of it.  For instance after all the entries 
you do want it might end up with a few million where d=15.  And SQLite has to 
look through them all before concluding that it has finished returning entries 
for your SELECT.

You might do better making an index more suited to your query.  I don't know 
which parts of the above are fixed and which aren't so I can't suggest one, but 
you might be able to figure one out and check, using EXPLAIN QUERY PLAN, 
whether SQLite prefers that one to map_index.

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

[sqlite] SQLite keeps on searching endlessly

[Raheel Gupta]

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 NOT NULL DEFAULT 0
);

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 Snapshot ID.
'd' is device id
'c' is not used and contains 0 all the time.
'b' is Block ID which is in another table altogether.

Now I need to retrieve the block numbers in order for lets say d = 15 and s
<= 326.
The device 15 has nearly 10 entries in the table while the remaining of
the 600 Million records belong to another device.

I retrieve the blocks in max limits of 32768 and my last query in the loop
is :

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

The query is executed immediately and it then starts to show the result and
the last rows are :
15731619|0|13359834|2
15731620|0|13359835|2
15731621|0|13359836|2
15731622|0|13359837|2
15731623|0|13359838|2
15731624|0|13359839|2
15731625|0|13359840|2
15731626|0|13359841|2
15731627|0|13359842|2
15731628|0|13359843|2
15731629|0|13359844|2
15731630|0|13359845|2
15731631|0|13359846|2
15731632|0|13359847|2
15731633|0|13359848|2
15731634|0|13359849|2
15731635|0|13359850|2
15731636|0|13359851|2
15731637|0|13359852|2
15731638|0|13359853|2
15731639|0|13359854|2

 After this SQLITE goes into an endless search for some reasons unknown. It
is doing something in the background (my guess its trying to search - I did
an strace) and keeps on doing it for nearly 10-15 minutes before it stops
and doesnt even give any new row after the above results.

The CPU shoots up during this and the following some of the strace (its
actually very large and I am just showing some results which show block
reads) :
lseek(3, 15185012736, SEEK_SET) = 15185012736
read(3,
"\n\0\0\0+\0c\0\0c\0y\0\216\0\244\0\271\0\317\0\344\0\372\1\17\1%\1:\1P"...,
1024) = 1024
lseek(3, 15185013760, SEEK_SET) = 15185013760
read(3,
"\n\0\0\0+\0c\0\0c\0y\0\216\0\244\0\271\0\317\0\344\0\372\1\17\1%\1:\1P"...,
1024) = 1024
lseek(3, 15185014784, SEEK_SET) = 15185014784
read(3,
"\n\0\0\0+\0c\0\0c\0y\0\216\0\244\0\271\0\317\0\344\0\372\1\17\1%\1:\1P"...,
1024) = 1024
lseek(3, 15185015808, SEEK_SET) = 15185015808
read(3,
"\n\0\0\0+\0c\0\0c\0y\0\216\0\244\0\271\0\317\0\344\0\372\1\17\1%\1:\1P"...,
1024) = 1024
lseek(3, 15185016832, SEEK_SET) = 15185016832
read(3,
"\n\0\0\0+\0c\0\0c\0y\0\216\0\244\0\271\0\317\0\344\0\372\1\17\1%\1:\1P"...,
1024) = 1024
lseek(3, 15185017856, SEEK_SET) = 15185017856
read(3,
"\n\0\0\0+\0c\0\0c\0y\0\216\0\244\0\271\0\317\0\344\0\372\1\17\1%\1:\1P"...,
1024) = 1024

Now, I dont understand why is this happening because I do have a full
fledged index for the entire table.
Also no new results are given after the whole search fiasco and the number
of results for this above query is 1568 rows.

Any ideas as to why this would be occuring.
I tried this similar query for the device which has nearly 500 Million
results and the last results of that query is pretty fast as usual.

Its for the smaller devices (e.g. with ID 15 above) the issue arises.

Any input will be appreciated.

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

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

[Bob Cochran]

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 both need to contain French language text.

To accomplish this, I think I need to take the following steps:

1. Compile and install the International Components for Unicode (ICU)
library ( http://site.icu-project.org/ ).
2. Compile the SQLite amalgamation with the SQLITE_ENABLE_ICU option.
3. Define the table so that it supports the French locale. I guess this
is the IANA language subtag "fr".


You only need all that if you want text in these columns to be sorted 
using French collation. You don't need to do anything special just to 
have SQLite store characters used in French, or indeed any Unicode 
characters.


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

Re: [sqlite] sqlite3_last_insert_rowid() problem

[Kevin Benson]

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:
> > >
> > > "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.
> > >
> >
> >
> >
> http://stackoverflow.com/questions/19018209/sqlite3-error-on-compilation/19018265#19018265
> >
>
> Is this solution for .m/.mm files?
> Because I'm writing plain old C++ code with cpp extension...
>
> Thank you.
>

Someone else will have to weigh in, then. I don't know. I just saw Xcode
mentioned in your post :-X

--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Kevin Benson]

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 guess the
> > function
> > gettimeofday() fails to add the time to the default EPOCH time.
>

Have you tried _matching_ the ARM  -mcpu=cortex-a15
compilation option _exactly_ into the  -mtune  option?
--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_last_insert_rowid() problem

[Igor Korot]

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 I build my program on Mac without such warning
> > and so that it will work properly.
> >
>
>
> http://stackoverflow.com/questions/19018209/sqlite3-error-on-compilation/19018265#19018265
>

Is this solution for .m/.mm files?
Because I'm writing plain old C++ code with cpp extension...

Thank you.


>
> --
>--
>   --
>  --Ô¿Ô--
> K e V i N
> ___
> 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] sqlite3_last_insert_rowid() problem

[Kevin Benson]

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

http://stackoverflow.com/questions/19018209/sqlite3-error-on-compilation/19018265#19018265

--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_last_insert_rowid() problem

[Igor Korot]

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 using XCode 4
configured
for 32 bit compilation.

On Windows everything compiles fine, 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.

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

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

[Drake Wilson]

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.

That sounds like it's not an SQLite-specific problem; if so, that's
leading off-topic.

Do other programs that use gettimeofday or clock_gettime also return
an incorrect time when executed in this environment?  Does the ARM
board actually _have_ real-time clock hardware to store the current
time in between resets?  If so, is it set properly?  If not, how is
the current time acquired by the OS?

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

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

[jitendar kumar]

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 guess the
function
gettimeofday() fails to add the time to the default EPOCH time.

I have also tried to use clock_gettime() but gets the same output on board.

Please provide your valuable suggestions to overcome this issue.

Thanks in Advance.

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

[sqlite] System.Data.SQLite Deployment Mystery

[Paul Bainter]

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 the
app would crash.  I then installed the file:
"sqlite-netFx45-setup-bundle-x86-2012-1.0.88.0.exe" (retrieved from your web
site) to this clean machine. I did NOT install it to the GAC which of course
also eliminated the Visual Studio Design stuff. At that point, I ran my app
again and it worked as expected.  I then went into the control panel and
uninstalled "System.Data.SQLite" and then tried running the application
again and it worked.  Quite a mystery there.

 

Seems like there is something that the
"sqlite-netFx45-setup-bundle-x86-2012-1.0.88.0.exe" installation file is
installing on the machine that is required for the database to be
recognized.  The application has the correct SQLite dll files which matched
the ones installed by the "System.Data.SQLite" installation file, so it's
not a missing dll, at least it's not missing "System.Data.SQLite.dll" or
"System.Data.SQLite.Linq.dll" as those are both put into the bin\debug
directory from the Visual Studio project.

 

I really enjoy working with the SQLite database, but I need it to work
without having to run the "System.Data.SQLite" installation.  I know that's
a mute-point because I don't believe that is what you guys intended either.
It's a pretty simple scenario, so hopefully I've given enough information to
help diagnose what is going on. If there is any other information that would
be helpful, please let me know.  Thank you very much for your help on this
issue.

 

Sincerely,

Paul Bainter

 

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