Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Michael Black]

Is this some mental exercise? Why can't you do this in the calling code
rather than some funky SQL select?
Or add a custom function?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Mikael
Sent: Saturday, July 20, 2013 8:54 AM
To: General Discussion of SQLite Database; luu...@gmail.com
Subject: Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; "
possible whatsoever?

Ah I realize now I didn't write it out in the example, but by thing and
thing2 I just allegorically wanted to represent a *very complex* subselect,
so here we go more clearly:

Inlining this subselect's SQL expression in the "A / B" part would make it
need to execute once more, which would make it take double the time, which
would be really long.

So again,

SELECT
id,
(SELECT [very complex subselect here, that uses categories.id as input]) AS
a,
(SELECT [another very complex subselect here, that uses categories.id as
input]) AS b,
a / b AS c
FROM categories
ORDER BY c;


any way to do it whatsoever?

Thanks :))
Mikael


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

Re: [sqlite] Understanding how data is stored and the index is managed

[Michael Black]

One more test I would is first principles.
Load 1200 records and just do "select * from items" -- you aren't going to
get any faster than that.
Then add the index query.
You should find a performance knee as you add records (try adding them in
powers of 2).
To test I would use "select * from items where rowid%2==0" for 2400 records,
and rows%4 for 4800 records, etc.

Also, what happens if you don't encrypt?

Also, what if you turn off SQLite caching completely.  Let CE have a bit
more cache space?

You could also create 2 tables -- one for you frequent data and one for the
non-frequent.
That's 2 selects but might be noticeably faster if the frequent is small
enough.

Mike

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Mohit Sindhwani
Sent: Thursday, July 11, 2013 11:20 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Understanding how data is stored and the index is managed

Hi All,

We have a system in which there are around 3 million records in a 
particular table within SQLite3 on Windows CE.  There is a primary key 
index on the table.

We are selecting 1200 records from the table using a prepare - bind - 
step - reset approach.  We find the time seems unreasonably long given 
that the query is of the type select * from items where id = ? (id is 
the primary key).  On this device, it takes around 6seconds.

We think it's because the table is probably quite large, as may be the 
index but the cache is only 2MB.  If we are hitting the table at random 
places, it could be that every step actually takes us to a different 
part of the table, so the cache is not helping.

To test this hypothesis, we did the following:
* Forced the queries to be far apart (basically every query was to a 
record that 3million/ 1200 apart) - this would be like the worst case, 
and it was.  The time went up to 9 seconds (+3 seconds)
* Forced the queries to be within the first 15% of the space - we expect 
that this would increase the cache hit.  The time came down to around 4 
seconds (-2 seconds).

Given this, are there things that we can do?  We know that when we do 
the lookup, there is a high chance that a significant portion of the 
1200 results will always lie in within the same 15% of the database.  
Can we make use of that knowledge somehow?

We could try to renumber the IDs so that all the IDs are in sequence, 
but that is not the easiest thing to do.  Does insertion order have an 
impact on how the data is stored?  If we inserted the most frequently 
accessed records first, would it mean that they would be closer to each 
other in the table and the index and therefore, we could get a better 
performance?

The database is read only and we are using CEROD, so we don't have to 
worry about data changing.

(The page size is 4KB and that matches the file system block size).

Thanks for any thoughts.

Best Regards,
Mohit.
___
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] What number(2) means?

[Michael Black]

You're right that SQLite will ignore any field specifications...
But it will not "treat the value as real".  It will store whatever you give
it.  The system is typeless.
http://www.sqlite.org/datatypes.html
This is different than most other database systems.

SQLite version 3.7.16.2 2013-04-12 11:52:43
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t(a number(2));
sqlite> insert into t values('blah');
sqlite> insert into t values(2);
sqlite> insert into t values(2.1);
sqlite> select * from t;
blah
2
2.1
sqlite> select typeof(a) from t;
text
integer
real

Mike

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Wednesday, July 10, 2013 4:10 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] What number(2) means?


On 10 Jul 2013, at 8:44am, Woody Wu  wrote:

> I have an old dabase, some integer columns were defined as type of
> number(2).

It limits the number of digits after the decimal point to 2.  In other
words, it's what you might use if you wanted an amount of dollars
automatically truncated to cents as the value is stored.

It'll be ignored by SQLite, of course.  SQLite will treat the value as REAL
and store all the digits it can fit in a REAL.

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] GUI for SQLite

[Michael Black]

Free doesn't necessarily mean open source

http://www.valentina-db.com/en/get-free-studio


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paolo Bolzoni
Sent: Wednesday, June 26, 2013 10:26 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] GUI for SQLite

Are you sure it is free? I cannot find the code...


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

Re: [sqlite] First Day of Week Inconsistency

[Michael Black]

Oops...forgot to add what you want.

   %U The week number of the current year as a decimal number,
range  00  to  53,
  starting  with the first Sunday as the first day of week 01.
See also %V and
  %W.

And this one:
   %V The ISO 8601:1988 week number of the current year as a decimal
number, range
  01  to  53,  where  week 1 is the first week that has at least
4 days in the
  current year, and with Monday as the first day of the week.
See also %U  and
  %W. (SU)

But SQLite doesn't implement those.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Denis Burke
Sent: Wednesday, June 19, 2013 11:21 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] First Day of Week Inconsistency

The built-in function strftime properly (imho) responds to the '%w' command
for day of week with Sunday as day 0.

e.g. -  strftime ('%w','2013-06-19')
->  3

But the built-in function for returning week of the year treats weeks as
though they begin on Monday, not Sunday:
e.g.
strftime ('%W','2013-06-19')
-> 24
strftime ('%W','2013-06-17')
-> 24
strftime ('%W','2013-06-16')
-> 23


It seems to me these useful functions are inconsistent.  Is it possible to
modify %W to treat Sunday as the first day of the week?

Thanks,
Denis Burke
___
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] First Day of Week Inconsistency

[Michael Black]

Those are unrelated questions.day of week has nothing to with the start
of a week.

>From the standard strftime man page which has been around for decades.


   %w The  day  of  the week as a decimal, range 0 to 6, Sunday
being 0.  See also
  %u.
   %W The week number of the current year as a decimal number,
range  00  to  53,
  starting with the first Monday as the first day of week 01.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Denis Burke
Sent: Wednesday, June 19, 2013 11:21 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] First Day of Week Inconsistency

The built-in function strftime properly (imho) responds to the '%w' command
for day of week with Sunday as day 0.

e.g. -  strftime ('%w','2013-06-19')
->  3

But the built-in function for returning week of the year treats weeks as
though they begin on Monday, not Sunday:
e.g.
strftime ('%W','2013-06-19')
-> 24
strftime ('%W','2013-06-17')
-> 24
strftime ('%W','2013-06-16')
-> 23


It seems to me these useful functions are inconsistent.  Is it possible to
modify %W to treat Sunday as the first day of the week?

Thanks,
Denis Burke
___
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] Strange table behaviour after text import with sqlite3.exe

[Michael Black]

Or to get all the non-integer records.


select * from qqq where typeof(field1) <> 'integer';

Mike

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Bart Smissaert
Sent: Saturday, June 08, 2013 9:58 AM
To: rsm...@rsweb.co.za; General Discussion of SQLite Database
Subject: Re: [sqlite] Strange table behaviour after text import with
sqlite3.exe

Aaah, OK, that answers my question!
This is something I hadn't realised at all and good to know that one.
Thanks for clearing this up.

RBS



On Sat, Jun 8, 2013 at 3:48 PM, RSmith  wrote:

> Yes, FIELD1 values are formatted int he output to be displayed as 0 since
> it is a INTEGER field, but the real value of FIELD1 is "FIELD1" for the
0th
> record, since that is what was imported from the CSV. The formatted value
> is not always the same as the real value.
>
> try:
>
> select * from qqq where field1 = "FIELD1"
>
>
> It will pop out a record I'm sure.
>
>
>
> On 2013/06/08 16:42, Bart Smissaert wrote:
>
>> Have table defined like this:
>>
>> CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT)
>>
>> Table is empty, so has no records.
>>
>> Then I import a text file with this data:
>>
>> FIELD1,FIELD2
>> 1,ABC
>> 2,BCD
>> 3,CDE
>>
>> This is via sqlite3.exe with:
>>
>> .mode csv
>> .import textfilename QQQ
>>
>> Table will then be like this:
>>
>> FIELD1 FIELD2
>> --**---
>> 0 FIELD2
>> 1 ABC
>> 2 BCD
>> 3 CDE
>>
>> This is all fine and as expected.
>> However I am unable to produce any records when doing a select
>> with a where clause specifying field1 to be zero.
>>
>> Tried all:
>> select * from qqq where field1 = 0
>> select * from qqq where field1 = '0'
>> select * from qqq where field1 = ''
>> select * from qqq where field1 is null
>>
>> Nil producing a record.
>>
>> Any idea what is going on here or what I might be doing wrong?
>>
>>
>> RBS
>> __**_
>> 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-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] Strange table behaviour after text import with sqlite3.exe

[Michael Black]

What makes you think field1 gets turned into a zero?  Fields are really
typeless in SQLite3

Your .dump should look like this:

SQLite version 3.7.16.2 2013-04-12 11:52:43
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT);
sqlite> .mode csv
sqlite> .import qqq qqq
Error: cannot open "qqq"
sqlite> .import qqq.txt qqq
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT);
INSERT INTO "QQQ" VALUES('FIELD1','FIELD2');
INSERT INTO "QQQ" VALUES(1,'ABC');
INSERT INTO "QQQ" VALUES(2,'BCD');
INSERT INTO "QQQ" VALUES(3,'CDE');
COMMIT;



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Bart Smissaert
Sent: Saturday, June 08, 2013 9:43 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Strange table behaviour after text import with sqlite3.exe

Have table defined like this:

CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT)

Table is empty, so has no records.

Then I import a text file with this data:

FIELD1,FIELD2
1,ABC
2,BCD
3,CDE

This is via sqlite3.exe with:

.mode csv
.import textfilename QQQ

Table will then be like this:

FIELD1 FIELD2
-
0 FIELD2
1 ABC
2 BCD
3 CDE

This is all fine and as expected.
However I am unable to produce any records when doing a select
with a where clause specifying field1 to be zero.

Tried all:
select * from qqq where field1 = 0
select * from qqq where field1 = '0'
select * from qqq where field1 = ''
select * from qqq where field1 is null

Nil producing a record.

Any idea what is going on here or what I might be doing wrong?


RBS
___
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] Limit of attached databases

[Michael Black]

Oops...make that an unsigned int.

Change this declaration
#if SQLITE_MAX_ATTACHED>30
  typedef __uint128_t yDbMask;

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michael Black
Sent: Tuesday, June 04, 2013 9:51 AM
To: est...@gmail.com; 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Limit of attached databases

Gcc does have a __int128_t and __uint128_t available if you're on 64-bit and
have a current enough gcc (I'm using 4.4.4 and this works on Linux and
Windows)
Looks like a fairly easy change in the code.
Unless somebody already knows that this won't work?

main()
{
__uint128_t i128;
printf("i128=%d\n",sizeof(i128));
}
i128=16

Change this to 126
#ifndef SQLITE_MAX_ATTACHED
# define SQLITE_MAX_ATTACHED 126
#endif

Change this declaration
#if SQLITE_MAX_ATTACHED>30
  typedef __int128_t yDbMask;

Change this to 126 instead of 62
#if SQLITE_MAX_ATTACHED<0 || SQLITE_MAX_ATTACHED>62
# error SQLITE_MAX_ATTACHED must be between 0 and 62
#endif

And see if it works OK for you.  I have no way to test this but it does
compile for me.

I don't see why it wouldn't work.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Eleytherios
Stamatogiannakis
Sent: Tuesday, June 04, 2013 9:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Limit of attached databases

Hi,

During our work on a distributed processing system (which uses SQLite 
shards), we have hit the SQLITE_MAX_ATTACHED limit of attached DBs.

The way we use SQLite for distributed processing [*], is the following:
  - Each table is sharded into multiple SQLite DBs on different nodes of 
the cluster.
  - To process a query, we run on each shard a query which produces 
multiple sharded SQLite result DBs.
  - We redistribute in the cluster the result DBs, and the next set of 
cluster nodes, attaches all the input shard SQLite DBs, and it creates a 
temp view that unions all the input DB shards into a single view.
  - It then executes a query on the views that produces new result DB shards
  - and so on

We recently got access to a cluster of 64 nodes and it is very easy now 
to hit the SQLITE_MAX_ATTACHED limit (1 DB shard gets produced per node).

So the question that i have is:

Is there any way to go beyond the SQLITE_MAX_ATTACHED limit for *read 
only* attached DBs?

Also is there anyway for SQLite to create an automatic index on a view 
(or Virtual Table), without having to first materialize the view (or VT)?

Thanks in advance.

Lefteris Stamatogiannakis.

[*] The same processing ideas are used in hadapt:

http://hadapt.com/

which uses Postgres for the DB shards.
___
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit of attached databases

[Michael Black]

Gcc does have a __int128_t and __uint128_t available if you're on 64-bit and
have a current enough gcc (I'm using 4.4.4 and this works on Linux and
Windows)
Looks like a fairly easy change in the code.
Unless somebody already knows that this won't work?

main()
{
__uint128_t i128;
printf("i128=%d\n",sizeof(i128));
}
i128=16

Change this to 126
#ifndef SQLITE_MAX_ATTACHED
# define SQLITE_MAX_ATTACHED 126
#endif

Change this declaration
#if SQLITE_MAX_ATTACHED>30
  typedef __int128_t yDbMask;

Change this to 126 instead of 62
#if SQLITE_MAX_ATTACHED<0 || SQLITE_MAX_ATTACHED>62
# error SQLITE_MAX_ATTACHED must be between 0 and 62
#endif

And see if it works OK for you.  I have no way to test this but it does
compile for me.

I don't see why it wouldn't work.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Eleytherios
Stamatogiannakis
Sent: Tuesday, June 04, 2013 9:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Limit of attached databases

Hi,

During our work on a distributed processing system (which uses SQLite 
shards), we have hit the SQLITE_MAX_ATTACHED limit of attached DBs.

The way we use SQLite for distributed processing [*], is the following:
  - Each table is sharded into multiple SQLite DBs on different nodes of 
the cluster.
  - To process a query, we run on each shard a query which produces 
multiple sharded SQLite result DBs.
  - We redistribute in the cluster the result DBs, and the next set of 
cluster nodes, attaches all the input shard SQLite DBs, and it creates a 
temp view that unions all the input DB shards into a single view.
  - It then executes a query on the views that produces new result DB shards
  - and so on

We recently got access to a cluster of 64 nodes and it is very easy now 
to hit the SQLITE_MAX_ATTACHED limit (1 DB shard gets produced per node).

So the question that i have is:

Is there any way to go beyond the SQLITE_MAX_ATTACHED limit for *read 
only* attached DBs?

Also is there anyway for SQLite to create an automatic index on a view 
(or Virtual Table), without having to first materialize the view (or VT)?

Thanks in advance.

Lefteris Stamatogiannakis.

[*] The same processing ideas are used in hadapt:

http://hadapt.com/

which uses Postgres for the DB shards.
___
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] Row_number?

[Michael Black]

Or perhaps this is better since it is your example:


sqlite> create table people(id,name);
sqlite> insert into people values(5,'Chris');
sqlite> insert into people values(12,'Arthur');
sqlite> insert into people values(23,'Bill');
sqlite> insert into people values(34,'Ron');
sqlite> insert into people values(43,'William');
sqlite> select rowid,* from people;
1|5|Chris
2|12|Arthur
3|23|Bill
4|34|Ron
5|43|William
sqlite> create table mylist as select id,name from people order by name;
sqlite> select rowid,* from mylist;
1|12|Arthur
2|23|Bill
3|5|Chris
4|34|Ron
5|43|William


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paxdo
Sent: Tuesday, June 04, 2013 4:50 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Row_number?


Hello,

I have a problem and I do not find the solution with Sqlite. I need an
equivalent to ROW_NUMBER.

Here's the problem:

SELECT id, name FROM people ORDER BY name

On this, I would like to know the line number of the ID 34, how?

example:

SELECT id, name FROM people ORDER BY name
Result:

12 Arthur
23 Bill
5 Chris
34 Ron
43 William

The line number of the ID34 is 4. 
But how to know with sqlite?

Thank you very much!

Olivier
___
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] Row_number?

[Michael Black]

Do you want rowid perhaps for a guaranteed one-to-one mapping to the row
regardless of the query?  Or are you looking for a repeatable one-up counter
for the query results?

select rowid,id,name from people ORDER BY name;

If you want a one-up counter automagically you can create another table from
the query like this example too:


sqlite> create table a(b);
sqlite> insert into a values(1);
sqlite> insert into a values(2);
sqlite> insert into a values(3);
sqlite> insert into a values(4);
sqlite> insert into a values(5);
sqlite> create table c as select * from a where (b % 2)==0;
sqlite> select rowid,* from c;
1|2
2|4

Mike

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paxdo
Sent: Tuesday, June 04, 2013 4:50 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Row_number?


Hello,

I have a problem and I do not find the solution with Sqlite. I need an
equivalent to ROW_NUMBER.

Here's the problem:

SELECT id, name FROM people ORDER BY name

On this, I would like to know the line number of the ID 34, how?

example:

SELECT id, name FROM people ORDER BY name
Result:

12 Arthur
23 Bill
5 Chris
34 Ron
43 William

The line number of the ID34 is 4. 
But how to know with sqlite?

Thank you very much!

Olivier
___
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] numeric string quotation db2 to db3

[Michael Black]

So why don't you use SQLMaestro to import, then export it again.  I would
think that would quote it correctly.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Spora
Sent: Friday, May 31, 2013 1:01 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] numeric string quotation db2 to db3

But i see that sqlMaestro is able to do it.
How can he do?



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

Re: [sqlite] numeric string quotation

[Michael Black]

I think you showed us the insert you do and not the .dump result.
You need to ensure your inserted values are single-quoted.

sqlite> create table test(alfa char(5) not null default '');
sqlite> insert into test values(01000);
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE seqnumber(stepid);
INSERT INTO "seqnumber" VALUES(5);
CREATE TABLE test(alfa char(5) not null default '');
INSERT INTO "test" VALUES('1000');
COMMIT;
sqlite> insert into test values('01000');
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE seqnumber(stepid);
INSERT INTO "seqnumber" VALUES(5);
CREATE TABLE test(alfa char(5) not null default '');
INSERT INTO "test" VALUES('1000');
INSERT INTO "test" VALUES('01000');
COMMIT;

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Spora
Sent: Friday, May 31, 2013 11:52 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] numeric string quotation

I have table:

CREATE TABLE test (
   alfa char(5) NOT NULL default '',
);

when i .dump i obtain:

INSERT INTO test VALUES(01000);

but when i import in Mysql, i obtain

test = '1000'
and not
test = '01000'

because .dump create:
INSERT INTO test VALUES(01000);
and not:
INSERT INTO test VALUES('01000');

how to solve?
 
 
 --
 Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e SMTP
autenticato? GRATIS solo con Email.it http://www.email.it/f
 
 Sponsor:
 Ami l'arte e vuoi arredare casa con stile? Su MisterCupido.com puoi
acquistare le RIPRODUZIONI DEI QUADRI di: Van Gogh, Monet, Klimt,
Modigliani, Cezanne, Hayez, Michelangelo, Raffaello, ecc
 Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=12386=31-5
___
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] Concatenating literals with column values

[Michael Black]

Your statement doesn't even work in MySQLas || is a logical operator there.
And Oracle complains about your original query:
SQL> select stepid  ,'STEPID'||stepid  ,stepid+5
,'STEPID'||stepid+5,'STEPID'||5 from seqtable; 
select stepid  ,'STEPID'||stepid  ,stepid+5  ,'STEPID'||stepid+5,'STEPID'||5
from seqtable
   *
ERROR at line 1:
ORA-01722: invalid number

And Oracle works with parentheses just like SQLite does:
SQL> select stepid  ,'STEPID'||stepid  ,stepid+5
,'STEPID'||(stepid+5),'STEPID'||5 from seqtable; 

STEPID 'STEPID'||STEPID STEPID+5
-- -- --
'STEPID'||(STEPID+5)   'STEPID
-- ---
 5 STEPID510
STEPID10   STEPID5


So I'm not sure what "other" databases you're talking about.

What you're seeing is operator precedence.  || has the highest precedence so
you need the parentheses to override that.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dave Wellman
Sent: Friday, May 31, 2013 11:08 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Concatenating literals with column values

Hi Richard,
Many thanks, that works.

Why do I need the "()" around my calculation? (apart from 'because that
makes it work' !) I've used other dbms's and don't need them there.

Cheers,
Dave



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

Re: [sqlite] query help

[Michael Black]

I think this does what you want.  

create table tab (num1 int unique,num2 int);
insert into tab values(1,3);
insert into tab values(2,3);
insert into tab values(3,2);
insert into tab values(4,1);
insert into tab values(5,11);
insert into tab values(6,3);
insert into tab values(7,9);

sqlite> select t2.num1,t2.num2 from tab t2 where (select count(*) from tab
t1 where t2.num2 == t1.num1) == 0;
5|11
7|9

If you don't want to see num2 in the query answer:
sqlite> select num1 from (select t2.num1,t2.num2 from tab t2 where (select
count(*) from tab t1 where t2.num2 == t1.num1) == 0);
5
7





-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Sanderson
Sent: Monday, May 20, 2013 7:00 AM
To: General Discussion of SQLite Database
Subject: [sqlite] query help

I have a table of the form

create table tab (num int1 unique, num2, int)

for each row for num2 there is usually a matching num1. But not always.

I want to identify each row where num2 does not have a matching num1

example data might be

num1  num2
1  3
2  3
3  2
4  1
5  11
6  3
7  9

in this example my query would return rows 5 and 7 as there is no match on
num1 for 11 and 9

Any ideas, cheers.
___
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] Problem with index on multiple columns

[Michael Black]

Do you have to have an autoincrement column?
You can implement a non-primary key column in a trigger that fills itself
from the rowid after insert giving you the same thing.
Mike


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

Re: [sqlite] Problem with index on multiple columns

[Michael Black]

Indeed would seem so.  Remove the primary key and idx_test is used.
This is on 3.7.16.2

CREATE TABLE test(
  id INTEGER,
  class INTEGER NOT NULL);
CREATE INDEX idx_test ON TEST(class,id);
 EXPLAIN QUERY PLAN SELECT * FROM test WHERE id IN (0,1) AND class IN (3,4);
0|0|0|SEARCH TABLE test USING COVERING INDEX idx_test (class=? AND id=?)
(~36 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
0|0|0|EXECUTE LIST SUBQUERY 1

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
Sent: Friday, May 17, 2013 9:37 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Problem with index on multiple columns

On Fri, May 17, 2013 at 10:26 AM, Konstantinos Alogariastos <
marau...@gmail.com> wrote:

> I am already aware of the contents of the query planner documentation.
>
> As you say, only one b-tree index will be used. What I want to achieve
> is what is described in the documentation " *The second column is used
> to break ties in the left-most column* ".
>
> In my case, my custom index "idx_test(class,id)" should first search
> in the "class" column and if there are any ties there (which happens a
> lot in my case), the "id" column should be used to break them.
> However, as evidenced by the query plan output, only the "class"
> column is used for indexing.
>

Collating orders and affinities might be disqualifying the constraint on
"id" from being used with the index.


>
>
> Best regards,
>
> Kostas
>
>
> On May 17, 2013, at 10:54 AM, Konstantinos Alogariastos  gmail.com >
> wrote:
>
> >* Does this mean that one cannot use a index on two columns when in the
> query*>* both columns are used with "IN"?*
> You might want to read up on the query planner:
> http://www.sqlite.org/queryplanner.html#searching
>
> The short of it: only one btree index will be used per source table.
>
> >* Is this a limitation of SQLite or a bug?*
> Neither. But read up on Multi-Column Indices.
>
> There exist other types of indexes (for example bitmap indexes [1]),
> which have different properties and can be combined to resolve a
> query, but such structures are not supported by SQLite.
>
> [1] http://en.wikipedia.org/wiki/Bitmap_index
>
>
>
> 2013/5/17 Konstantinos Alogariastos 
>
> > Hi all,
> >
> > I am using SQLite 3.7.13 and I am experiencing a problem with using an
> > index on multiple columns.
> >
> > Let's assume the following example:
> > I have a simple table constructed as such:
> >
> > CREATE TABLE test(
> >   id INTEGER PRIMARY KEY AUTOINCREMENT,
> >   class INTEGER NOT NULL);
> >
> > and I insert some data to it.
> >
> > Next I create an index: CREATE INDEX idx_test ON TEST(class,id);
> >
> > If I try to use the index with a query similar to the following:
> > EXPLAIN QUERY PLAN
> >   SELECT *
> >  FROM test
> >WHERE id IN (0,1)
> >  AND class IN (3,4)
> >
> > I get the output:
> > 0|0|0|SEARCH TABLE test USING INTEGER PRIMARY KEY (rowid=?) (~2 rows)
> > 0|0|0|EXECUTE LIST SUBQUERY 1
> > 0|0|0|EXECUTE LIST SUBQUERY 1
> >
> > (which means my index is not used)
> >
> > If I add "INDEXED BY idx_test" on the above query, I get:
> > 0|0|0|SEARCH TABLE test USING COVERING INDEX idx_test (class=?) (~2
rows)
> > 0|0|0|EXECUTE LIST SUBQUERY 1
> > 0|0|0|EXECUTE LIST SUBQUERY 1
> >
> > which shows that only one column of the index is used for indexing. This
> > has the side effect of the query taking longer than it should be.
> > Modifying the order of the columns on the index or in the query didn't
> > help either.
> >
> > Does this mean that one cannot use a index on two columns when in the
> > query both columns are used with "IN"?
> > Is this a limitation of SQLite or a bug?
> >
> > Thanks in advance.
> >
> > Best regards,
> > Kostas
> >
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Possible bug in type conversion prior to comparison

[Michael Black]

May just be showing best representation
main()
{
float a=1,b=2,c=20;
printf("%g\n",a/b*c);
}
Answer: 10
For c=21
Answer: 10.5


SQL> insert into numtypes values(1,2,20);

1 row created.

SQL> select A/B*C from numtypes;

 A/B*C
--
  12.5
  13.5
  11.5
  10.5
10


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Monday, May 13, 2013 11:12 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Possible bug in type conversion prior to comparison


On 13 May 2013, at 5:08pm, Michael Black <mdblac...@yahoo.com> wrote:

> Would appear it's not doing any casting to promote values but just
promoting
> everything to float.

I should have asked you for (1,2,20) as well and we could see whether it
outputs '10' or '10.0'.  But yes, it would appear that in Oracle, NUMERIC
means FLOAT.

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] Possible bug in type conversion prior to comparison

[Michael Black]

Added that...
Would appear it's not doing any casting to promote values but just promoting
everything to float.

SQL> insert into numtypes values(1,2,21);
1 row created.

SQL> select A/B*C from numtypes;

 A/B*C
--
  12.5
  13.5
  11.5
  10.5

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Monday, May 13, 2013 11:01 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Possible bug in type conversion prior to comparison


On 13 May 2013, at 4:57pm, Michael Black <mdblac...@yahoo.com> wrote:

> Oracle gives the right answer too for example(contrary to what somebody
said
> earlier).
> 
> create table numtypes (A NUMERIC, B NUMERIC, C NUMERIC);
> insert into numtypes values (1, 2, 25.23);
> insert into numtypes values (1.0, 2, 27.17);
> insert into numtypes values (1.1, 2, 22.92);
> select A/B*C from numtypes;
> 
> A/B*C
> --
>  12.5
>  13.5
>  11.5

Please add to your INSERTs (1,2,21) and see whether Oracle is using integer
arithmetic or not.

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] Possible bug in type conversion prior to comparison

[Michael Black]

Seems to me the SQL standard makes no distinction between columns and
literals does it?
Why should literals be ignored?

Oracle gives the right answer too for example(contrary to what somebody said
earlier).

create table numtypes (A NUMERIC, B NUMERIC, C NUMERIC);
insert into numtypes values (1, 2, 25.23);
insert into numtypes values (1.0, 2, 27.17);
insert into numtypes values (1.1, 2, 22.92);
select A/B*C from numtypes;

 A/B*C
--
  12.5
  13.5
  11.5

SQL> desc numtypes;
 Name  Null?Type
 - 

 A  NUMBER(38)
 B  NUMBER(38)
 C  NUMBER(38)

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
Sent: Sunday, May 12, 2013 6:29 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Possible bug in type conversion prior to comparison

On Sun, May 12, 2013 at 7:55 AM, Tomasz Pawlak <
tomasz.paw...@cs.put.poznan.pl> wrote:

>
> So, type of '1' is 'text'.
>
> * If one operand has INTEGER, REAL or NUMERIC affinity and the other
> operand as TEXT or NONE affinity then NUMERIC affinity is applied to other
> operand. "
>
> So, if we compare 1 with '1'  (e.g. 1='1'), '1' should be converted to
> numeric, right?
>

No.  '1' has type 'text' but it has no affinity at all.  Likewise 1 has
type 'integer' but no affinity.  So no conversions take place, and the
answer is FALSE.

Affinity is only associated with table columns.  Literals never have
affinity.

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

Re: [sqlite] SQLite and integer division 1/2=0

[Michael Black]

PRAGMA INTEGER_DIVISION  would probably not have saved you this bug as you
would not have known to turn it on (default would have to be OFF for
backwards compatibility).
Changing to float math everywhere might hurt some embedded systems.
MYSQL was never designed to work on embedded systems.
So yeah...I doubt you'll get much support as there's not a good solution
which stops somebody from making this mistake.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul van Helden
Sent: Sunday, May 12, 2013 5:07 AM
To: General Discussion of SQLite Database
Subject: [sqlite] SQLite and integer division 1/2=0

Hi All,

I will probably get little support on this here, but I think it is
worthwhile documenting my complaint.

SELECT 1/2 returns 0 which to me is a little odd, but I see from this page:
http://www.sqlite.org/sqllogictest/wiki?name=Differences+Between+Engines
that most other engines do the same. (In my opinion MySQL does it right:
1/2=0.5; 1 div 2=0).

So be it, but it can really trip you up in SQLite if you have a table
create table numtypes (A NUMERIC, B NUMERIC, C NUMERIC);
insert into numtypes values (1, 2, 25.23);
insert into numtypes values (1.0, 2, 27.17);
insert into numtypes values (1.1, 2, 22.92);
select A, typeof(A), B, typeof(B), A/B*C from numtypes;

A,typeof(A),B,typeof(B),A/B*C
1,integer,2,integer,0,0
1,integer,2,integer,0,0
1.1,real,2,integer,0.12.606

Yes, I have discovered create table numtypes (A REAL, B REAL, C REAL)
solves the problem.

It just seems illogical to me that numbers are silently converted to
integer and then integer division is done on those. A client of mine just
got very bad answers from a simple calculation because some rows had
integers and others real. It is not intuitive that a NUMERIC column would
mix integer and float division. If you don't know this, as I'm sure most
regular users don't, it can really burn you. I don't mind the conversion to
integer, but then 1/2 should be 0.5.

On my wishlist: PRAGMA INTEGER_DIVISION = off;

I would use it all the time. Yes "feature creep" I can hear you type. :-)

Best regards,

Paul.
___
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] Is there a way to select a precision?

[Michael Black]

OK...this way then...it's stored correctly and the view should display
correctly in a 3rd party app.
Correctly gives the 99 cent discount.
Should work for any 2 decimal place number.


create table mine(a number(10,2));
create view v1 as select a/100.0 as a from mine;
create trigger t1 after insert on mine
for each row begin
update mine set a = cast(round(new.a*100) as integer) where
rowid=new.rowid;
end;
create trigger t2 after update on mine
for each row
begin
update mine set a=cast(round(a*100) as integer) where
typeof(a)!='integer' and rowid=new.rowid;
end;
insert into mine values(1.0/3.0);
select * from mine;
select a*3 from v1;
.99
update mine set a=1/3.0;
select a*3 from v1;
.99



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James K. Lowden
Sent: Monday, May 06, 2013 8:03 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is there a way to select a precision?

On Mon, 6 May 2013 07:42:43 -0500
"Michael Black" <mdblac...@yahoo.com> wrote:

> Yes...other databases do respect NUMBER(10,2) on SELECT's.
...
> Can you simply use round to do what you want?
> CREATE TABLE stuff(f number(10,2));
> INSERT INTO "stuff" VALUES(1.0/3.0);
> sqlite> select f from stuff;
> 0.333
> sqlite> select round(f,2) from stuff;
> 0.33

To be clear, Paul van Helden isn't talking about SELECT.  He's talking
about INSERT, about not storing more precision that the input actually
represents.  

Apply a 33% discount to a $3.00 purchase.  Is the bill $2.00, or
$2.01?  If you say $2.00, then apply a 33% discount to three $1
purchases in three separate months.  I imagine you'd agree the total
discount is just 99 cents.  

Whether or not SQLite ought to support exact decimal types is a
separate question; I don't think anyone is saying it should.  But it
isn't just a matter of presentation.  

--jkl




___
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] Anyone can decipher this so i can try to figure out how to debug it? Thanks

[Michael Black]

It's a lousy error message from some application which ay be using sqlite
underneath the covers.
Sqlite isn't even mentioned in the error so not sure why you think it's
involved.

It's getting an error on its own rename code and has nothing to do with
sqlite.

And it's not telling you what the real error is (poor coding).  My guess
would be either permissions or the target path does not exist.



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Clemens Ladisch
Sent: Monday, May 06, 2013 7:00 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Anyone can decipher this so i can try to figure out
how to debug it? Thanks

Mike wrote:
> May  3 16:29:56 syncd: [ERROR] db-api.cpp:3738 rename from
'/volume1/@tmp/jUH4Ti' -> '/volume1/@cloudstation/@sync/repo/d/0/V/.Z'

How is db-api.cpp related with SQLite?
___
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] Is there a way to select a precision?

[Michael Black]

What you are talking about would be feature creep for SQLite.

Yes...other databases do respect NUMBER(10,2) on SELECT's.

SQLite is "light weight" and does no such magic for you.
So it does take an extra step.
You'll note that SQLite does provide a GUI for you to play with.
If it did it would probably allow you to format columns.

This keeps the library small and lightweight.

Can you simply use round to do what you want?
CREATE TABLE stuff(f number(10,2));
INSERT INTO "stuff" VALUES(1.0/3.0);
sqlite> select f from stuff;
0.333
sqlite> select round(f,2) from stuff;
0.33

Doesn't work, of course, for generic "table edit" in some GUI.
For that you could create a view.

sqlite> create view fview as select round(f,2) from stuff;
sqlite> select * from fview;

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul van Helden
Sent: Monday, May 06, 2013 7:15 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is there a way to select a precision?

> A delared type of NUMBER(10,2) has NUMERIC affinity, which means that
> SQLite will attempt to store (string) values as integers first and floats
> second before giving up and storing strings.
>

This has nothing to do with my reply and I understand how it works.

>
> You do realize that there are decimal numbers that have infinite binary
> expansions?
>

I wouldn't store such numbers into a NUMBER(10,2), just a NUMBER (I know
they are the same in SQLite).

>
> You are also talking presentation (as in formatting) of numeric values as
> opposed to representation (as in storing/retrieving). The former is best
> handled in the user interface while the latter is the subject of database
> engines.
>

My point was about not storing binary junk - the part of a number that has
no meaning because the accuracy of the inputs is limited. When you have a
generic db manager that can show any table or if you are looking at the
results of your own SQL statement, it helps to reduce clutter on the
screen. The data also compresses better.

>
> Fatihful reproduction of formatting would be possible using TEXT affinity
> and calling sqlite3_bind_text. Performing arithmetic with these "numbers"
> would however be tricky, slow and would still not guarantee that
calculated
> values would conform to the desired formatting.
>
> Of course, but in most cases we don't need to go this far. My main point
is about rounding before binding; my secondary point that scale in a column
definition can be desirable to avoid it.
___
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] How people test db failures

[Michael Black]

There are several ways people handle errors.

#1 Path of least resistanceyou assume all is well and ignore thembad
idea but too common
#2 Catch as catch can...you put in error handling as you experience
themalso a bad idea as it's too similar to #1
#3 Catch with careful thought about what could happencan be difficult
but this is what experienced people do...can still miss things.
#4 Catch all...check all return codes and print out somethingthen do #3
for known error and graceful/proper handling...this is how you learn.
#5 Force all possible errors to occur...there's a lot.  I don't know anybody
that does that.

A combo of #3 and #4 is best IMHO.

If you want to be really conservative you could write a wrapper around every
sqlite function to randomly return one of its possible error codes.
That's would take a lot of time though and I've never heard of anybody doing
that.

My pet peeve is the lousy error message like Microsoft used to produce "dll
not found".  What DLL?  What error?  E.g. File not found or permission
problem?
Or the very common "cannot open file".  What file, why not?  Error messages
need to be explicit.
SQlite is pretty good about it.  Developers not so much.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Korot
Sent: Wednesday, May 01, 2013 2:00 PM
To: General Discussion of SQLite Database
Subject: [sqlite] How people test db failures

Hi, ALL,
I wrote a code that executes fine. There is no memory leaks and no
issues.

However, trying to check whether my error handling is written correctly, I
set
breakpoint before accessing db and when I hit it I manually changes the
value returned.
This is not how it should be done and I feel that the person I'm working
with will need to check it
at some point.

So, how people check whether db failure result in graceful program
termination/proper flow?

I'm working on the desktop application under Windows and am giving the
release version of the program
compiled under MSVC 2010.

Thank you.
___
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] How do I write this query

[Michael Black]

sqlite> CREATE TABLE foo( a integer, b integer);
sqlite> INSERT INTO foo VALUES(1,1);
sqlite> INSERT INTO foo VALUES(1,2);
sqlite> INSERT INTO foo VALUES(1,3);
sqlite> INSERT INTO foo VALUES(2,1);
sqlite> INSERT INTO foo VALUES(2,2);
sqlite> INSERT INTO foo VALUES(2,3);
sqlite> CREATE TABLE bar( a integer, b integer, c integer);
sqlite> INSERT INTO bar SELECT a,b,1 FROM foo;
sqlite> select * from bar;
1|1|1
1|2|1
1|3|1
2|1|1
2|2|1
2|3|1

Mike

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Korot
Sent: Monday, April 29, 2013 4:06 PM
To: General Discussion of SQLite Database
Subject: [sqlite] How do I write this query

Hi, ALL,

CREATE TABLE foo( a integer, b integer);
INSERT INTO foo VALUES( 1,1);
INSERT INTO foo VALUES( 1,2);
INSERT INTO foo VALUES( 1,3);
INSERT INTO foo VALUES( 2,1);
INSERT INTO foo VALUES( 2,2);
INSERT INTO foo VALUES( 2,3);

CREATE TABLE bar( a integer, b integer, c integer);

INSERT INTO bar VALUES((SELECT a, b FROM foo),1); // fails
INSERT INTO bar( a, b ) VALUES( (SELECT a, b FROM foo) ); //fails

What is the correct syntax?

Basically I need bar to have records from foo with field c to be 1, so:

SELECT * FROM bar;
1 1 1
1 2 1
1 3 1
2 1 1
2 2 1
2 3 1

Thank you.
___
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] Cygwin compilation error

[Michael Black]

What version gcc do you have?  Mine works.

D:\SQLite>gcc --version
gcc (GCC) 4.7.3 20121102 (prerelease)

D:\SQLite>gcc -o shgcc.exe shell.c sqlite3.c

D:\SQLite>shgcc
SQLite version 3.7.16.2 2013-04-12 11:52:43
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .quit

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Fulvio Esposito
Sent: Wednesday, April 17, 2013 9:06 AM
To: sqlite_us...@googlegroups.com
Subject: [sqlite] Cygwin compilation error

Hi all,
I'm trying to use sqlite (tried 3.6 and the latest) in a project using 
cygwin (the latest version), but I cannot manage to compile it. I also 
tried to compile the shell.c file but the same errors come up:

gcc shell.c sqlite3.c -lpthread -ldl

sqlite3.c:30467: error: initializer element is not constant
sqlite3.c:30467: error: (near initialization for `aSyscall[0].pCurrent')
sqlite3.c:30467: error: initializer element is not constant
sqlite3.c:30467: error: (near initialization for `aSyscall[0]')
sqlite3.c:30479: error: initializer element is not constant
sqlite3.c:30479: error: (near initialization for `aSyscall[1]')
sqlite3.c:30487: error: initializer element is not constant
[...]

and it goes the same until aSyscall[73]

Any insight about why I'm getting those errors?


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

Re: [sqlite] sqlite3_column_blob does not return the size of the blob?

[Michael Black]

http://www.sqlite.org/c3ref/column_blob.html

sqlite3_column_bytes should do what you need.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paolo Bolzoni
Sent: Thursday, April 18, 2013 7:02 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] sqlite3_column_blob does not return the size of the blob?

I need to save some blob (arbitrary precision numbers representations) on my
sqlite3 database.

And confused by the function:

const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);

I would expect an output parameter (example type size_t*) to get the size of
the blob, but it is not there.

How can I know the length of the blob to read it correctly?

Thanks,
Paolo
___
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] RTree Documentation Error?

[Michael Black]

It would also be nice if 3.3 showed the "expected output" too.
Would be a bit easier for the beginner to ensure they are sane.
Also would've prevented this GIGO error me thinkst.

Michael Black


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

Re: [sqlite] RTree Documentation Error?

[Michael Black]

Correct -- no results for the "real" 2nd query ...you got no results for it
either.\
Then 2nd query is this and produces no results...nor should it with the data
given.
SELECT id FROM demo_index
 WHERE minX>=-81.08 AND maxX<=-80.58
   AND minY>=35.00  AND maxY<=35.44;

I think the data point you have for SQL HQ is supposed to be:
1|-80.77490234375|-80.7746963500977|35.3775978088379|35.3778038024902
You have "33" for miny which puts HQ outside of the query since the
query doesn't do intersections.

Change 33 to 35 and the query will work.  I don't think HQ is really 2
degrees N/S :-)

SQLite version 3.7.16.2 2013-04-12 11:52:43
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE VIRTUAL TABLE demo_index USING rtree(
   ...>id,  -- Integer primary key
   ...>minX, maxX,  -- Minimum and maximum X coordinate
   ...>minY, maxY   -- Minimum and maximum Y coordinate
   ...> );
sqlite> INSERT INTO demo_index VALUES(
   ...> 1,   -- Primary key
   ...> -80.7749, -80.7747,  -- Longitude range
   ...> 33.3776, 35.3778 -- Latitude range
   ...> );
sqlite> INSERT INTO demo_index VALUES(
   ...> 2,
   ...> -81.0, -79.6,
   ...> 35.0, 36.2
   ...> );
sqlite> SELECT * FROM demo_index WHERE id=1;
1|-80.77490234375|-80.7746963500977|33.3775978088379|35.3778038024902
sqlite> SELECT id FROM demo_index
   ...>  WHERE minX>=-81.08 AND maxX<=-80.58
   ...>AND minY>=35.00  AND maxY<=35.44;
Note that there's no output here  And there shouldn't
beminY is 33.377 for ID#1
So let's put in some correct numbers
sqlite> delete from demo_index where id=1;
sqlite> INSERT INTO demo_index VALUES(
   ...> 1,   -- Primary key
   ...> -80.7749, -80.7747,  -- Longitude range
   ...> 35.3776, 35.3778 -- Latitude range
   ...> );
sqlite> SELECT id FROM demo_index
   ...>  WHERE minX>=-81.08 AND maxX<=-80.58
   ...>AND minY>=35.00  AND maxY<=35.44;
1
sqlite> SELECT id FROM demo_index
   ...>  WHERE maxY>=35.0  AND minY<=35.0;
2

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
Sent: Wednesday, April 17, 2013 8:07 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] RTree Documentation Error?

On Wed, Apr 17, 2013 at 8:41 AM, Michael Black <mdblac...@yahoo.com> wrote:

> I think the point is that this query that produces nothing:
> SELECT id FROM demo_index WHERE minX>=-81.08 AND maxX<=-80.58 AND
> minY>=35.00  AND maxY<=35.44;
>
> Should produce something as the directions imply.
>

And my point is that the query DOES produce a result.  Are y'all saying you
are getting an empty result for the second query?  What version of SQLite
are you running?  What platform?

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

Re: [sqlite] RTree Documentation Error?

[Michael Black]

I think the point is that this query that produces nothing:
SELECT id FROM demo_index WHERE minX>=-81.08 AND maxX<=-80.58 AND
minY>=35.00  AND maxY<=35.44;

Should produce something as the directions imply.

It's just that SQL HQ is not actually inside the city limits apparently
(mailing addresses are not restricted to city boundaries).
So add another data point of something downtown.
Like the Carolina Panthers stadium
-80.854201,-80.851489, 35.224511, 35.226925

sqlite> insert into demo_index
values(3,-80.854201,-80.851489,35.224511,35.226925);
sqlite> SELECT id FROM demo_index WHERE minX>=-81.08 AND maxX<=-80.58 AND
minY>=35.00  AND maxY<=35.44;
id
3

Michael Black



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

Re: [sqlite] Help speed up query

[Michael Black]

Have you tried increase sqlite cache size and page size?

pragma cache_size;
pragma page_size;

Try making page_size=4096

Then make cache_size*page_size as big as your database file.




-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of ven...@intouchmi.com
Sent: Tuesday, April 16, 2013 1:10 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Help speed up query

I tried transfering the database to an in memory copy and running the
queries.  It is actually a little slower but not much.  I'm sure there are
tables that are not being accessed and so loading them into memory would add
to the time.

Vance

on Apr 16, 2013, ven...@intouchmi.com wrote:
>
>Yes, the DBs are on my local disk.  The quoted times are after the first
run so mostly
>in cache.  It takes about twice the time the first run for both SQLite and
MS Access.
>
>Vance
>
>on Apr 16, 2013, Simon Slavin  wrote:
>>
>>
>>On 16 Apr 2013, at 2:32pm, ven...@intouchmi.com wrote:
>>
>>> It was about 380 ms.  Now it is taking about 610 ms.
>>
>>Can I check with you that the database file is stored on a disk of the
computer which
>>is executing the SQLite commands ?  In other words, that this is a local
hard disk
>>and not one shared over a network.
>>
>>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
>
___
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] Beginning database question

[Michael Black]

I assume when you say "discrete" you actually mean "bracketed" as there are
lots more than 10 heights and weights.

I don't know what Excel has to do with this unless you programmed the whole
thing in Excel already?

Is speed really a concern?  This does sound like a fairly trivial
programming effort in most any language.

The database approach would be nice if you have bracketed weights as a
simple SQL query like this should work:

Create table teams(heightlow,heighthigh,weightlow,weighthigh,team);
Insert into hw values(100,110,60,62,1);  (100-110lbs 60-62", Team#1)

Select team from teams where h > = heightlow and h < heighthigh and w >=
weightlow and w < weighthigh;

Do you want a GUI around this too?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Carl Gross
Sent: Tuesday, April 16, 2013 3:28 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Beginning database question

Hi All,

I'm an amateur programmer who has never worked with databases before.  I
*think* that getting started with SQLite may help with some of my projects,
but I'm having trouble determining exactly how.  I'm hoping someone in this
list may be able to point me in the right direction.

My simplified situation is this.  There are 10 discrete heights' and 10
discrete weights,' and each height/weight combination corresponds to one of
two 'teams.'  All of this information is hardcoded into my program.  My
program asks a user to enter his own height and weight, and the program
will output the user's corresponding team based on the hardcoded data.

My SQLite question is:  Can using SQLite instead of Microsoft Excel be
beneficial in terms of coding efficiency, processing speed, memory usage,
or any other aspect?  If so, how?

Sorry for the long, broad, vague question.  I'm not looking for someone to
tell me how to do something.  I'm looking for someone to assure me that
SQLite (rather than a simple spreadsheet) is right for me, and to hopefully
steer me towards some documentation that may be beneficial to me.

Thanks,

Carl
___
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] correlated subquery in LIMIT/OFFSET?

[Michael Black]

I think you need a more complete example with the output you expect to get
better help.  Although I do understand what you're getting at.

Mike

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Clemens Ladisch
Sent: Thursday, April 11, 2013 8:39 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] correlated subquery in LIMIT/OFFSET?

Michael Black wrote:
> > However, it appears that SQLite does not allow correlated subqueries
> > in the LIMIT/OFFSET clauses of a scalar subquery:
> >
> >   sqlite> create table t(x);
> >   sqlite> select (select 42 limit 1 offset (select t.x)) from t;
> >   Error: no such column: t.x
>
> Instead of "select t.x" don't you really want "select x from t" ??

No, I want it to be a *correlated* subquery.

> I assume you just want the offset to come from a single-row table?

It should come (or be derived) from the current row in the outer query.


Regards,
Clemens
___
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] correlated subquery in LIMIT/OFFSET?

[Michael Black]

Instead of "select t.x" don't you really want "select x from t" ??

That seems to work at least syntactically.  I assume you just want the
offset to come from a single-row table?
Mike


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Clemens Ladisch
Sent: Thursday, April 11, 2013 8:15 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] correlated subquery in LIMIT/OFFSET?

Hi,

for computing the median of a group, it would be useful to be able to
use a correlated subquery as the expression in a OFFSET clause.

However, it appears that SQLite does not allow correlated subqueries
in the LIMIT/OFFSET clauses of a scalar subquery:

  sqlite> create table t(x);
  sqlite> select (select 42 limit 1 offset (select t.x)) from t;
  Error: no such column: t.x

Is this behaviour by design?

The documentation says that the LIMIT clause of a scalar subquery is
ignored and gets replaced with 1, but this shouldn't disallow the syntax
above, and should not apply to the OFFSET value in any case.


Regards,
Clemens
___
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] 64bit compatibility warnings

[Michael Black]

Which is why...IMHOto avoid all the repeats of this question in the future 
(and from the past)one should simply to do the cast to int and put a 
comment on the line that says " % int always fits in an int".

Too bad one can't cast to the type of a variable though in C.

And if IBuf is an int do you really want that to be a 64-bit int on a 64-bit 
compiler?
Would IBuf ever get that big or need be that big?  Yes...I know I sound like 
IBM now...:-)



-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Monday, April 08, 2013 6:41 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] 64bit compatibility warnings

On Sun, Apr 7, 2013 at 1:06 PM, Alexandr Němec  wrote:

>
> Line 6766   u.bc.r.flags = (u16)(UNPACKED_INCRKEY * (1 & (u.bc.oc -
> OP_SeekLt)));  WARNING: conversion from 'u16' to 'u8', possible
> loss of data
> Line 71133 iBuf = p->iReadOff % p->nBuffer;
> WARNING: conversion from 'i64' to 'int', possible loss of data
> Line 71209 iBuf = p->iReadOff % p->nBuffer;
>  WARNING: conversion from 'i64' to 'int', possible loss of data
> Line 71286 iBuf = iStart % nBuf;
> WARNING: conversion from 'i64' to 'int', possible loss of data
> Line 71574 p->iBufEnd = p->iBufStart = (iStart % nBuf); WARNING:
> conversion from 'i64' to 'int', possible loss of data
>

The first warning is harmless and results from a prior datatype change.
Dan has already fixed that one.  The other four appear to be due to an MSVC
compiler bug, since every (i64%int) operation will always yield a value
that can fit in an int, no?
-- 
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Logic error or missing database

[Michael Black]

How about just posting your COMPLETE code example for your console app.
Some of us can't import a VS2012 project
I just sent you a complete example that works.  Are you saying this doesn't
work for you?

#include 
#include "sqlite3.h"

main()
{
  sqlite3 *oDatabase;
  int returnValue;
  returnValue = sqlite3_open_v2("D:/SQlite/testing.db", ,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, NULL);
  if (returnValue != SQLITE_OK )
  {
printf("%d: %s\n",returnValue,sqlite3_errmsg(oDatabase));
  }
  else {
printf("Got it\n");
  }
}

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Collie
Sent: Friday, April 05, 2013 9:47 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQL Logic error or missing database

Nothing seems to be overly weird, and the console app is a fresh project,
with very little changed.

I'm compiling under 32-bit in VS2012 using the amalgamation files. I can
attach my test project if needed. I'm really rather curious to know what I
broke.


On Fri, Apr 5, 2013 at 4:28 PM, Noel Frankinet
wrote:

> it's time to check your compiler setting, anything weird ? Are you sure
you
> have the source code of sqlite for windows ? 32 or 64 bits settings ??
>
>
> On 5 April 2013 16:24, Rob Collie  wrote:
>
> > Same problem, I'm afraid. I've tried just about every combination
> suggested
> > in http://www.sqlite.org/c3ref/open.html
> >
> >
> > On Fri, Apr 5, 2013 at 4:18 PM, Kevin Benson  > >wrote:
> >
> > > On Fri, Apr 5, 2013 at 10:08 AM, Rob Collie 
> > wrote:
> > >
> > > > Yeap, I'm on Visual Studio 2012. I've created a console app:
> > > >
> > > >
> > > >  sqlite3 *oDatabase;
> > > >  int returnValue;
> > > >  returnValue = sqlite3_open_v2("file://C:/Newfolder/testing.db",
> > > > & oDatabase, SQLITE_OPEN_CREATE, NULL);
> > > >  if (returnValue != SQLITE_OK )
> > > >  {
> > > >   //sqlite3_close(oDatabase);
> > > >   return returnValue ;
> > > >  }
> > > >  int anyKey;
> > > >  return 0;
> > > >
> > > > It returns 21. Checking the other project, the open actually does
> > return
> > > 21
> > > > too.
> > > >
> > >
> > >
> > > Three(3) forward slashes for the Internet path style in Windows,
maybe?
> > >
> > > file:///C:/Newfolder/testing.db
> > > --
> > >--
> > >   --
> > >  --Ô¿Ô--
> > > 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
> >
>
>
>
> --
> Noël Frankinet
> Strategis sprl
> 0478/90.92.54
> ___
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Logic error or missing database

[Michael Black]

This works for me under Visual Studio 2010.  I couldn't seem to get a file
uri to work at all either.

#include 
#include "sqlite3.h"

main()
{
  sqlite3 *oDatabase;
  int returnValue;
  returnValue = sqlite3_open_v2("D:/SQlite/testing.db", ,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, NULL);
  if (returnValue != SQLITE_OK )
  {
printf("%d: %s\n",returnValue,sqlite3_errmsg(oDatabase));
  }
  else {
printf("Got it\n");
  }
}

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Collie
Sent: Friday, April 05, 2013 9:25 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQL Logic error or missing database

Same problem, I'm afraid. I've tried just about every combination suggested
in http://www.sqlite.org/c3ref/open.html



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

Re: [sqlite] SQL Logic error or missing database

[Michael Black]

Also change the last arg of open to NULL instead of "".


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Collie
Sent: Friday, April 05, 2013 7:54 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] SQL Logic error or missing database

Hello there,

For my sins, I'm trying to create a library allowing our legacy fortran
code to work with SQL.

Calling this from fortran...

CALL EXECUTESQL('dbTest'//CHAR(0), cQuery, iReturnValue)
...runs the following code, and yet the error returned is 'SQL Logic error
or missing database'. No file is ever created. Is there something dumb I'm
missing here?


extern "C"
{

 void EXECUTESQL(char *dataBase, char *query, int returnValue)
 {

  // Checking the incoming data from FORTRAN
  CStringW wName(dataBase);
  MessageBoxW( NULL, wName, L"Name: ", MB_OK );

  // Create the object
  sqlite3 *oDatabase;

  // Create the error objects
  char *sErrorMessage;
  // Open/create the table, if required
  returnValue = sqlite3_open_v2(dataBase, ,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, "");
  if (returnValue != SQLITE_OK )
  {
   sqlite3_close(oDatabase);
   MessageBoxA(NULL, sqlite3_errstr(returnValue), "SQL Open Error", MB_OK);
   return;
  }


Rob.
___
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] SQL Logic error or missing database

[Michael Black]

Sqlitge3_close() might be your problem that's masking the real error.
You can't close what never got opened.



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Collie
Sent: Friday, April 05, 2013 7:54 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] SQL Logic error or missing database

Hello there,

For my sins, I'm trying to create a library allowing our legacy fortran
code to work with SQL.

Calling this from fortran...

CALL EXECUTESQL('dbTest'//CHAR(0), cQuery, iReturnValue)
...runs the following code, and yet the error returned is 'SQL Logic error
or missing database'. No file is ever created. Is there something dumb I'm
missing here?


extern "C"
{

 void EXECUTESQL(char *dataBase, char *query, int returnValue)
 {

  // Checking the incoming data from FORTRAN
  CStringW wName(dataBase);
  MessageBoxW( NULL, wName, L"Name: ", MB_OK );

  // Create the object
  sqlite3 *oDatabase;

  // Create the error objects
  char *sErrorMessage;
  // Open/create the table, if required
  returnValue = sqlite3_open_v2(dataBase, ,
SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, "");
  if (returnValue != SQLITE_OK )
  {
   sqlite3_close(oDatabase);
   MessageBoxA(NULL, sqlite3_errstr(returnValue), "SQL Open Error", MB_OK);
   return;
  }


Rob.
___
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] TCL Test failures on ARM

[Michael Black]

What's the ulimit for file sizes on your system?  You might be hitting that.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Bk
Sent: Monday, April 01, 2013 1:16 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] TCL Test failures on ARM

I have updated make file with "TCC = armv7l-timesys-linux-gnueabi-gcc   -g
-DSQLITE_OS_UNIX=1 -DSQLITE_DISABLE_LFS=1 -I. -I${TOP}/src
-I${TOP}/ext/rtree"


The test still fails with DSQLITE_DISABLE_LFS=1 with same error

sysfault-2.setup... Ok
ftruncate: : File too large
ftruncate: : File too large
ftruncate: : File too large
ftruncate: : File too large
sysfault-2.1-vfsfault-transient.1...
Expected: [0 ok]
 Got: [1 {nfail=1 rc=1 result=disk I/O error}]


Below is the debugger screen with value of "sizeof(off_t)" . The values
seems different 4 in ts_ftruncate and 8 in robust_ftruncate. I hope this is
what you asked me to check 

(gdb) break ts_ftruncate
Cannot access memory at address 0x0
Breakpoint 1 at 0x455e0: file ./src/test_syscall.c, line 273.
(gdb) continue
Continuing.

Breakpoint 1, ts_ftruncate (fd=5, n=284108) at ./src/test_syscall.c:273
273   if( tsIsFailErrno("ftruncate") ){
(gdb) p sizeof(off_t)
$1 = 4
(gdb) break robust_ftruncate
Breakpoint 2 at 0x6371c: file sqlite3.c, line 23440.
(gdb) continue
Continuing.

Breakpoint 2, robust_ftruncate (h=7, sz=0) at sqlite3.c:23440
23440 rc = osFtruncate(h,sz);
(gdb) p sizeof(off_t)
$2 = 8
(gdb) continue
Continuing.

Breakpoint 1, ts_ftruncate (fd=7, n=284108) at ./src/test_syscall.c:273
273   if( tsIsFailErrno("ftruncate") ){
(gdb) p sizeof(off_t)
$3 = 4
(gdb) continue
Continuing.

Breakpoint 2, robust_ftruncate (h=8, sz=0) at sqlite3.c:23440
23440 rc = osFtruncate(h,sz);
(gdb) p sizeof(off_t)
$4 = 8
(gdb) continue
Continuing.

Breakpoint 1, ts_ftruncate (fd=8, n=284108) at ./src/test_syscall.c:273
273   if( tsIsFailErrno("ftruncate") ){
(gdb) p sizeof(off_t)
$5 = 4
(gdb) continue
Continuing.

Breakpoint 2, robust_ftruncate (h=7, sz=0) at sqlite3.c:23440
23440 rc = osFtruncate(h,sz);
(gdb) p sizeof(off_t)
$6 = 8
(gdb) continue
Continuing.

Breakpoint 1, ts_ftruncate (fd=7, n=284108) at ./src/test_syscall.c:273
273   if( tsIsFailErrno("ftruncate") ){
(gdb) p sizeof(off_t)
$7 = 4
(gdb) continue
Continuing.

Breakpoint 2, robust_ftruncate (h=5, sz=24576) at sqlite3.c:23440
23440 rc = osFtruncate(h,sz);
(gdb) p sizeof(off_t)
$8 = 8
(gdb) 




--
View this message in context:
http://sqlite.1065341.n5.nabble.com/TCL-Test-failures-on-ARM-tp67612p67984.h
tml
Sent from the SQLite mailing list archive at Nabble.com.
___
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] How to achieve fastest possible write performance for a strange and limited case

[Michael Black]

I think many people would tell you not to store your images in your
database.
Just store a filepath to them.
That will speed things up quite a bit and even possibly prevent having to
use an SSD.

With the filepath your processing apps can use file locking too if you need
it.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jeff Archer
Sent: Friday, March 29, 2013 2:18 PM
To: SQLite-user.org
Subject: [sqlite] How to achieve fastest possible write performance for a
strange and limited case

I have previously made an apparently bad assumption about this so now I
would like to go back to the beginning of the problem and ask the most
basic question first without any preconceived ideas.

This use case is from an image processing application.  I have a large
amount of intermediate data (way exceeds physical memory on my 24GB
machine).  So, I need to store it temporarily on disk until getting to next
phase of processing.  I am planning to use a large SSD dedicated to holding
this temporary data.  I do not need any recoverability in case of hardware,
power or other failure.   Each item to be stored is 9 DWORDs, 4 doubles and
2 variable sized BLOBS which are images.

I could write directly to a file myself.  But I would need to provide some
minimal indexing, some amount of housekeeping to manage variable
sized BLOBS and some minimal synchronization so that multiple instances of
the same application could operate simultaneously on a single set of data.

So, then I though that SQLite could manage these things nicely for me so
that I don't have to write and debug indexing and housekeeping code that
already exists in SQLite.

So, question is:  What is the way to get the fastest possible performance
from SQLite when I am willing to give up all recoverability guarantees?
Or, is it simple that I should just write directly to file myself?
___
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] [EDIT]What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE?

[Michael Black]

I can tell you the explain plan is notably different between those two
inserts.  This is with 3.7.14.1
If only I knew more about the details of what the plan meansone thing I
noted is that keyinfo is opened twice for write on Test#2
Test 1:
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Goto   0 45000
2 OpenWrite  1 2 0 5  00
3 OpenWrite  2 3 0 keyinfo(1,BINARY)  00
4 NewRowid   1 2 000
5 String80 3 0 Braintree Road  00
6 Null   0 4 000
7 Null   0 5 000
8 Once   0 25000
9 Null   0 8 000
10Integer1 9 000
11OpenRead   0 2 0 4  00
12OpenRead   3 3 0 keyinfo(1,BINARY)  00
13String80 100 Braintree Road  00
14SeekGe 3 23101  00
15IdxGE  3 23101  01
16IdxRowid   3 11000
17Seek   0 11000
18Column 0 3 11   00
19Integer1 13000
20Add131112   00
21Move   128 100
22IfZero 9 23-1   00
23Close  0 0 000
24Close  3 0 000
25SCopy  8 6 000
26NotNull6 28000
27Integer1 6 000
28Integer147 000
29SCopy  3 14000
30SCopy  2 15000
31MakeRecord 142 1 ad 00
32SCopy  2 11000
33IsUnique   2 391114 00
34NotExists  1 3911   00
35Rowid  1 15000
36Column 1 0 14   00
37IdxDelete  2 14200
38Delete 1 0 000
39IdxInsert  2 1 000
40MakeRecord 3 5 11aeadd  00
41Insert 1 112 FREQMARY   0b
42Close  1 0 000
43Close  2 0 000
44Halt   0 0 000
45Transaction0 1 000
46VerifyCookie   0 4 000
47TableLock  0 2 1 FREQMARY   00
48Goto   0 2 000
Test 2:
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Goto   0 64000
2 OpenWrite  1 4 0 5  00
3 OpenWrite  2 6 0 keyinfo(1,BINARY)  00
4 OpenWrite  3 5 0 keyinfo(1,BINARY)  00
5 NewRowid   1 3 000
6 String80 4 0 Braintree Road  00
7 Null   0 5 000
8 Null   0 6 000
9 Once   0 26000
10Null   0 9 000
11Integer1 10000
12OpenRead   0 4 0 4  00
13OpenRead   4 6 0 keyinfo(1,BINARY)  00
14String80 110 Braintree Road  00
15SeekGe 4 24111  00
16IdxGE  4 24111  01
17IdxRowid   4 12000
18Seek   0 12000
19Column 0 3 12   00
20Integer1 14000
21Add141213   00
22Move   139 100
23IfZero 1024-1   00
24Close  0 0 000
25Close  4 0 000
26SCopy  9 7 000
27NotNull7 29000
28Integer1 7 000

Re: [sqlite] Efficient way to store counters

[Michael Black]

You're simply missing the where clause on your update so you're updating the
entire database every time you do an insert.
update_counter(k1,k2 count=count+1,expires=now+count*1day) where field1=k1
and field2=k2;

And a (k1,k2) index would help that update a lot.



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

Re: [sqlite] Windows-Specific 2-c-files Amalgamation?

[Michael Black]

Can't you just wait to fix things until "patch Tuesday"??? :-)
Sheesh...we can't handle things this quickly...

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
Sent: Monday, March 11, 2013 12:37 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Windows-Specific 2-c-files Amalgamation?

On Mon, Mar 11, 2013 at 1:33 PM, Michael Black <mdblac...@yahoo.com> wrote:

> Richard...he split is missing the 5 split files. I thought the small size
> was suspicious.
>

Fixed.

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

Re: [sqlite] Windows-Specific 2-c-files Amalgamation?

[Michael Black]

Richard...he split is missing the 5 split files. I thought the small size
was suspicious.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
Sent: Monday, March 11, 2013 11:42 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Windows-Specific 2-c-files Amalgamation?

On Mon, Mar 11, 2013 at 12:17 PM, Dominique Devienne
wrote:

>  Perhaps someone knows
> a trick or two to work-around that MS debugging issue?
>

Run:  "make sqlite3-all.c" to build an amalgamation composed of smaller
files.

See the http://www.sqlite.org/draft/download.html for a "32K" amalgamation
of the latest 3.7.16 beta.


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

Re: [sqlite] Bug on real operations

[Michael Black]

I don't see your "problem". 22.35 cannot be accurately represented in
floating point.
It's just some display systems will hide it from you.

What exactly is it that you can't do or that is coming out wrong?

Stop trying to make sense out of comparing floats -- it's a bad idea.
main()
{
float f=(5.45f+16.9f);
double d=(5.45+16.9);
printf("%g\n",f);
printf("%g\n",d);
printf("%15e\n",f);
printf("%15e\n",d);
}

(gdb) p f
$1 = 22.3499985
(gdb) p d
$2 = 22.348
 (gdb) cont
Continuing.
22.35
22.35
  2.235000e+001
  2.235000e+001


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Israel Lins
Albuquerque
Sent: Friday, March 08, 2013 12:07 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Bug on real operations

The problem is not comparisons the problem is when I do something like this:
CREATE TABLE tb (a REAL);
INSERT INTO tb (a) VALUES(0);
UPDATE tb SET a = a + 5.45;
UPDATE tb SET a = a + 16.9;

SELECT a FROM tb; 

Gives visually right answer: 22.35
But putting on a double variable gives me 22.3499948593433 (something like
that)
and when declaring double a = 22.35 => gdb give me 22.34999 

Em 08/03/2013, às 14:11, Michael Black <mdblac...@yahoo.com> escreveu:

> Nobody should expect float comparisons like that to work.
> If they do they're asking for trouble.
> All you're seeing is what the database is letting you see.  Their "0" is
not
> really "0".
> 
> Try this in your friendly C compiler
> main()
> {
>   double d = 22.35-(5.45+16.9);
>   printf("%f\n",d);
>   printf("%g\n",d);
>   printf("%e\n",d);
> }
> On both MS Visual C and gcc on a Linux system:
> 0.00
> 3.55271e-015
> 3.552714e-015
> 
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Israel Lins
> Albuquerque
> Sent: Thursday, March 07, 2013 12:04 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Bug on real operations
> 
> An example speaks more than words:
> 
> Execute this:
> SELECT 22.35 - (5.45 + 16.9), 22.35 = (5.45 + 16.9), (5.45 + 16.9) -
22.35;
> 
> The expected result on almost databases is:
> 0.0, true or 1, 0.0
> 
> But in sqlite for some reason they are:
> 3.5527136788005e-15, 0, -3.5527136788005e-15
> 
> I thing this can be a bug on calculation of doubles.
> 
> Regards,
> Israel Lins Albuquerque
> ___
> 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-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] Bug on real operations

[Michael Black]

And didn't we go through this a while ago...

Sqlite3's precision is 14...sometimes 15
sqlite> select round(22.35-(5.45+16.9),15);
4.0e-15
sqlite> select round(22.35-(5.45+16.9),14);
0.0

So this also works:
sqlite> select round(22.35 - (5.45 + 16.9),14), 22.35 = round((5.45 +
16.9),14), round((5.45 + 16.9) - 22.35,14);
0.0|1|0.0



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Israel Lins
Albuquerque
Sent: Thursday, March 07, 2013 12:04 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Bug on real operations

An example speaks more than words:

Execute this:
SELECT 22.35 - (5.45 + 16.9), 22.35 = (5.45 + 16.9), (5.45 + 16.9) - 22.35;

The expected result on almost databases is:
0.0, true or 1, 0.0

But in sqlite for some reason they are:
3.5527136788005e-15, 0, -3.5527136788005e-15

I thing this can be a bug on calculation of doubles.

Regards,
Israel Lins Albuquerque
___
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] Bug on real operations

[Michael Black]

And...the right way to do this which should work on all databases...rounding
any answers to the max precision you asked for.

sqlite> SELECT round(22.35 - (5.45 + 16.9),2), 22.35 = round((5.45 +
16.9),2), round((5.45 + 16.9) - 22.35,2);
0.0|1|0.0


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Israel Lins
Albuquerque
Sent: Thursday, March 07, 2013 12:04 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Bug on real operations

An example speaks more than words:

Execute this:
SELECT 22.35 - (5.45 + 16.9), 22.35 = (5.45 + 16.9), (5.45 + 16.9) - 22.35;

The expected result on almost databases is:
0.0, true or 1, 0.0

But in sqlite for some reason they are:
3.5527136788005e-15, 0, -3.5527136788005e-15

I thing this can be a bug on calculation of doubles.

Regards,
Israel Lins Albuquerque
___
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] Bug on real operations

[Michael Black]

Nobody should expect float comparisons like that to work.
If they do they're asking for trouble.
All you're seeing is what the database is letting you see.  Their "0" is not
really "0".

Try this in your friendly C compiler
main()
{
double d = 22.35-(5.45+16.9);
printf("%f\n",d);
printf("%g\n",d);
printf("%e\n",d);
}
On both MS Visual C and gcc on a Linux system:
0.00
3.55271e-015
3.552714e-015

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Israel Lins
Albuquerque
Sent: Thursday, March 07, 2013 12:04 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Bug on real operations

An example speaks more than words:

Execute this:
SELECT 22.35 - (5.45 + 16.9), 22.35 = (5.45 + 16.9), (5.45 + 16.9) - 22.35;

The expected result on almost databases is:
0.0, true or 1, 0.0

But in sqlite for some reason they are:
3.5527136788005e-15, 0, -3.5527136788005e-15

I thing this can be a bug on calculation of doubles.

Regards,
Israel Lins Albuquerque
___
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] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Michael Black]

Personally I think this behavior is horrid.  Is there some scenario where
this wouldn't be a latent bug?
Here's a patch against 3.7.14.1 which behaves the way it should IMHO
Though I think it should actually throw an error when not parseable
correctly.
Why is this behavior allowed now?

Old:
select cast('2' as integer);
2
select cast('2a' as integer);
2
select cast('2.1' as integer);
2
select cast('2.6' as integer);
2
select cast('2.6f' as integer);
2
select cast('2.6' as float);
2.6
Select cast('2.6f' as float);
2.6

New:
select cast('2' as integer);
2
select cast('2a' as integer);
0
select cast('2.1' as integer);
0
select cast('2.6' as integer);
0
select cast('2.6f' as integer);
0
select cast('2.6' as float);
2.6
select cast('2.6f' as float);
0

*** sqlite3.old Thu Mar 07 07:06:32 2013
--- sqlite3.c   Thu Mar 07 07:26:59 2013
***
*** 21199,21205 
int nDigits = 0;

*pResult = 0.0;   /* Default return value, in case of an error */
-
if( enc==SQLITE_UTF16BE ) z++;

/* skip leading spaces */
--- 21199,21204 
***
*** 21239,21244 
--- 21238,21249 
  }
  /* skip non-significant digits */
  while( z=zEnd ) goto do_atof_calc;

***
*** 21465,21470 
--- 21470,21476 
sqlite_int64 v = 0;
int i, c;
int neg = 0;
+   printf("sqlite3GetInt32\n");
if( zNum[0]=='-' ){
  neg = 1;
  zNum++;
***
*** 21474,21479 
--- 21480,21488 
while( zNum[0]=='0' ) zNum++;
for(i=0; i<11 && (c = zNum[i] - '0')>=0 && c<=9; i++){
  v = v*10 + c;
+   }
+   if (zNum[i] != 0) {
+ return 0;
}

/* The longest decimal representation of a 32 bit integer is 10 digits:

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Petite Abeille
Sent: Wednesday, March 06, 2013 4:21 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join
inconsistency]


On Mar 6, 2013, at 10:49 PM, Nico Williams  wrote:

> Ah, your confusion comes from the fact that type conversion still
> happens when the INSERT gets around to making the record.  The CHECK
> constraint happens before the record is made.  See the vdbe that gets
> generated.

All good. Small sanity check though:


select 2 = cast( '2' as integer );
> 1

Ok. '2' is can be casted to 2. great...


select 2 = cast( 'a2' as integer );
> 0

Ok. 'a2' cannot really be casted to an integer. cool...


select 2 = cast( '2.1' as integer );
> 1

Hmmm..


select 2 = cast( '2abc' as integer );
> 1

What?!? Oh. "When casting a TEXT value to INTEGER, the longest possible
prefix of the value that can be interpreted as an integer number is
extracted from the TEXT value and the remainder ignored. ". ah. ok. weird.

So.

select cast( 'abc' as integer );
> 0

o k i d o k i . . .







___
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] Break on cumulative sum

[Michael Black]

One thing I'll point out...the trigger solution is almost linear performance
and constant memory where the single SQL statement will suffer notably as
time goes on in both those measures.  Also the single SQL statement doesn't
seem like a good idea if the data is coming in real time.
He never mentioned how big his table is going to be.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James K. Lowden
Sent: Sunday, March 03, 2013 7:10 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Break on cumulative sum

On Sun, 3 Mar 2013 11:46:26 +0100
Gert Van Assche  wrote:

> My SQL knowledge is not yet good enough to understand what you
> proposed, but I hope one day that I will be able to understand and
> replicate myself the code you wrote. I started of with a join on the
> table itself as well, but I got stuck. I'll study your code and I'll
> grow.

Happy to be service, Gert.  

The trick is always the same: get your ducks in a row and shoot.  Figure
out the formula that computes the answer you want, and put the
constituent parts on the row, then compute.  

The first order of business in your case was a running sum.  That's
done by adding up everything "before" the current row, where "before"
means "less than" something, however ordered.   

select   a.*, sum(b.ValueField) as total
from Test as a join Test as b
on a.TextField >= b.TextField
group by a.TextField
, a.ValueField
, a.CumulativeValue
, a.BreakPoint

For each row in "a", get all the rows in "b" whose TextField is less
than (or equal to, here) to a's.  Here it is, live and in color: 

$ sqlite3 -echo db < self.sql 
select   a.TextField
, a.ValueField
, a.BreakPoint
 , sum(b.ValueField) as total
from Test as a join Test as b
on a.TextField >= b.TextField
group by a.TextField
, a.ValueField
, a.CumulativeValue
, a.BreakPoint
;
TextField   ValueField  BreakPoint  total 
--  --  --  --
A   2   10  2 
B   3   10  5 
C   2   10  7 
D   4   10  11
E   5   10  16
F   1   10  17
G   1   10  18
H   5   10  23
I   11  10  34
J   8   10  42
K   2   10  44

That formed my building block.  

Your formula is almost

GN = 1 + total / Breakpoint

By using integer arithmetic, I could divide "total" by BreakPoint to
get a multiple, which is almost what you call a GroupName, except for
that "one past" rule I remarked on, where for example 11 is part of
the first group.  But by joining my block to itself, offset by one, we
can slide the multiplier down a row.  

For CumulativeValue, your formula is 

CV = total - [max total of previous GN]

That requires computing the GN, grouping on it, and finding the maximum
total for it, and joining that result back to the main one, where it
can be subtracted from the main total.  

So, yes, there are a lot of joins.  But I didn't write the query
top-down, and you shouldn't read it that way.  Read it inside-out.
Each piece can be separately evaluated, and each operand used in the
calculation can be exposed in the SELECT clause.  

There's some cruft, too.  I was only demonstrating that it could be
done.  If you find a way to simplify it, you'll know you understand
it.  

Regards, 

--jkl
___
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] Break on cumulative sum

[Michael Black]

And I just noticed that should probably be

when new.BreakPoint <=


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Gert Van Assche
Sent: Saturday, March 02, 2013 10:49 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Break on cumulative sum

Michael, this is working perfectly!
I learned a lot with your code.
Thanks a lot for your help.

gert

2013/3/2 Michael Black <mdblac...@yahoo.com>

> I think your "K" row was a typo on the CumulativeValue?
>
> CREATE TABLE [Test] (TextField, ValueField, CumulativeValue,  BreakPoint
> DEFAULT 10, GroupName);
> CREATE TABLE [MyGroup](GroupName);
> insert into [MyGroup] values(1);
> create trigger trig1 before insert on [Test]
> when 10 <= (select sum(ValueField) from [Test] where GroupName=(select
> GroupName from [MyGroup]))
> begin
> update [MyGroup] set GroupName = (select GroupName+1 from
> [MyGroup]);
> end;
> create trigger trig2 after insert on [Test]
> begin
> update [Test] set GroupName = (select GroupName from [MyGroup])
> where
>  rowid=new.rowid;
> update [Test] set CumulativeValue = (select sum(ValueField) from
> [Test] where GroupName=(select GroupName from [MyGroup])) where
> rowid=new.rowid;
> end;
> insert into [Test] values('A', '2', null, '10',(select GroupName from
> MyGroup));
> insert into [Test] values('B', '3', null, '10',(select GroupName from
> MyGroup));
> insert into [Test] values('C', '2', null, '10',(select GroupName from
> MyGroup));
> insert into [Test] values('D', '4', null, '10',(select GroupName from
> MyGroup));
> insert into [Test] values('E', '5', null, '10',(select GroupName from
> MyGroup));
> insert into [Test] values('F', '1', null, '10',(select GroupName from
> MyGroup));
> insert into [Test] values('G', '1', null, '10',(select GroupName from
> MyGroup));
> insert into [Test] values('H', '5', null, '10',(select GroupName from
> MyGroup));
> insert into [Test] values('I', '11', null, '10',(select GroupName from
> MyGroup));
> insert into [Test] values('J', '8', null, '10',(select GroupName from
> MyGroup));
> insert into [Test] values('K', '2', null, '10',(select GroupName from
> MyGroup));
> select * from [Test];
> A|2|2|10|1
> B|3|5|10|1
> C|2|7|10|1
> D|4|11|10|1
> E|5|5|10|2
> F|1|6|10|2
> G|1|7|10|2
> H|5|12|10|2
> I|11|11|10|3
> J|8|8|10|4
> K|2|10|10|4
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Gert Van Assche
> Sent: Saturday, March 02, 2013 7:47 AM
> To: sqlite-users
> Subject: [sqlite] Break on cumulative sum
>
> All, I don't know how to achieve this: I need to put the cumulative sum in
> a field, and create a group as soon as that cumulative sum is over a
> breakpoint value (10).
>
> This is an example table:
>
> CREATE TABLE [Test] (TextField, ValueField, CumulativeValue,  BreakPoint
> DEFAULT 10, GroupName);
> insert into [Test] values('A', '2', null, '10');
> insert into [Test] values('B', '3', null, '10');
> insert into [Test] values('C', '2', null, '10');
> insert into [Test] values('D', '4', null, '10');
> insert into [Test] values('E', '5', null, '10');
> insert into [Test] values('F', '1', null, '10');
> insert into [Test] values('G', '1', null, '10');
> insert into [Test] values('H', '5', null, '10');
> insert into [Test] values('I', '11', null, '10');
> insert into [Test] values('J', '8', null, '10');
> insert into [Test] values('K', '2', null, '10');
>
> I'd like to end up with a table that looks like this:
>
> TextField ValueField CumulativeValue BreakPoint GroupName
> A 2 2 10 1
> B 3 5 10 1
> C 2 7 10 1
> D 4 11 10 1
> E 5 5 10 2
> F 1 6 10 2
> G 1 7 10 2
> H 5 12 10 2
> I 11 11 10 3
> J 8 8 10 4
> K 2 2 10 4
>
> I spent hours trying to update the CumulativeValue field untill the
> BreakPoint value is crossed, and restarting the cumulative counter, but I
> have too little sql knowledge to do this.
>
> Could anyone help me?
>
> thanks
>
> gert
> ___
> 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-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] Break on cumulative sum

[Michael Black]

I think your "K" row was a typo on the CumulativeValue?

CREATE TABLE [Test] (TextField, ValueField, CumulativeValue,  BreakPoint 
DEFAULT 10, GroupName);
CREATE TABLE [MyGroup](GroupName);
insert into [MyGroup] values(1);
create trigger trig1 before insert on [Test]
when 10 <= (select sum(ValueField) from [Test] where GroupName=(select 
GroupName from [MyGroup]))
begin
update [MyGroup] set GroupName = (select GroupName+1 from [MyGroup]);
end;
create trigger trig2 after insert on [Test]
begin
update [Test] set GroupName = (select GroupName from [MyGroup]) where
 rowid=new.rowid;
update [Test] set CumulativeValue = (select sum(ValueField) from [Test] 
where GroupName=(select GroupName from [MyGroup])) where rowid=new.rowid;
end;
insert into [Test] values('A', '2', null, '10',(select GroupName from MyGroup));
insert into [Test] values('B', '3', null, '10',(select GroupName from MyGroup));
insert into [Test] values('C', '2', null, '10',(select GroupName from MyGroup));
insert into [Test] values('D', '4', null, '10',(select GroupName from MyGroup));
insert into [Test] values('E', '5', null, '10',(select GroupName from MyGroup));
insert into [Test] values('F', '1', null, '10',(select GroupName from MyGroup));
insert into [Test] values('G', '1', null, '10',(select GroupName from MyGroup));
insert into [Test] values('H', '5', null, '10',(select GroupName from MyGroup));
insert into [Test] values('I', '11', null, '10',(select GroupName from 
MyGroup));
insert into [Test] values('J', '8', null, '10',(select GroupName from MyGroup));
insert into [Test] values('K', '2', null, '10',(select GroupName from MyGroup));
select * from [Test];
A|2|2|10|1
B|3|5|10|1
C|2|7|10|1
D|4|11|10|1
E|5|5|10|2
F|1|6|10|2
G|1|7|10|2
H|5|12|10|2
I|11|11|10|3
J|8|8|10|4
K|2|10|10|4


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Gert Van Assche
Sent: Saturday, March 02, 2013 7:47 AM
To: sqlite-users
Subject: [sqlite] Break on cumulative sum

All, I don't know how to achieve this: I need to put the cumulative sum in
a field, and create a group as soon as that cumulative sum is over a
breakpoint value (10).

This is an example table:

CREATE TABLE [Test] (TextField, ValueField, CumulativeValue,  BreakPoint
DEFAULT 10, GroupName);
insert into [Test] values('A', '2', null, '10');
insert into [Test] values('B', '3', null, '10');
insert into [Test] values('C', '2', null, '10');
insert into [Test] values('D', '4', null, '10');
insert into [Test] values('E', '5', null, '10');
insert into [Test] values('F', '1', null, '10');
insert into [Test] values('G', '1', null, '10');
insert into [Test] values('H', '5', null, '10');
insert into [Test] values('I', '11', null, '10');
insert into [Test] values('J', '8', null, '10');
insert into [Test] values('K', '2', null, '10');

I'd like to end up with a table that looks like this:

TextField ValueField CumulativeValue BreakPoint GroupName
A 2 2 10 1
B 3 5 10 1
C 2 7 10 1
D 4 11 10 1
E 5 5 10 2
F 1 6 10 2
G 1 7 10 2
H 5 12 10 2
I 11 11 10 3
J 8 8 10 4
K 2 2 10 4

I spent hours trying to update the CumulativeValue field untill the
BreakPoint value is crossed, and restarting the cumulative counter, but I
have too little sql knowledge to do this.

Could anyone help me?

thanks

gert
___
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] locked database returning SQLITE_IOERR, not SQLITE_BUSY

[Michael Black]

This was covered a few days ago...the error handling is not thread safe
apparently(why not???) so you need to wrap the call and error check with a
mutex.

  int rc=SQLITE_OK;
  char *errmsg=NULL;
  sqlite3_mutex_enter(sqlite_db_mutex(db));
  rc=sqlite3_blobopen(.);
  if(rc!=SQLITE_OK) {
 rc=sqlite3_extended_errcode(db);
 errmsg=strdup(sqlite3_errmsg(db);
  }
  sqlite3_mutex_leave(sqlite_db_mutex(db));
  // now you can safely use rc and errmsg

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Greg Janée
Sent: Tuesday, February 26, 2013 11:50 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] locked database returning SQLITE_IOERR, not
SQLITE_BUSY

I've instrumented the SQLite source and have found that the error is  
occurring at the fcntl call near the end of function unixLock (in  
SQLite version 3.7.0.1, this is line 23592 of sqlite3.c).  The  
relevant code snippet is below.  fnctl is returning -1, and errno=2  
(ENOENT).  From my reading of the fcntl man page, it wouldn't seem to  
be possible for fcntl to even return ENOENT.

SQLite is being used from a multi-threaded application in my case, and  
I don't know if it's a possibility that some other thread is  
overwriting errno.  But then, if that's even a possibility, wouldn't  
that seem to preclude using SQLite in a multithreaded application at  
all?

   }else{
 /* The request was for a RESERVED or EXCLUSIVE lock.  It is
 ** assumed that there is a SHARED or greater lock on the file
 ** already.
 */
 assert( 0!=pFile->eFileLock );
 lock.l_type = F_WRLCK;
 switch( eFileLock ){
   case RESERVED_LOCK:
 lock.l_start = RESERVED_BYTE;
 break;
   case EXCLUSIVE_LOCK:
 lock.l_start = SHARED_FIRST;
 lock.l_len = SHARED_SIZE;
 break;
   default:
 assert(0);
 }
 s = fcntl(pFile->h, F_SETLK, );
 if( s==(-1) ){
   tErrno = errno;
   rc = sqliteErrorFromPosixError(tErrno, SQLITE_IOERR_LOCK);
   if( IS_LOCK_ERROR(rc) ){
 pFile->lastErrno = tErrno;
   }
 }
   }

On Feb 26, 2013, at 9:13 AM, Dan Kennedy wrote:

> On 02/27/2013 12:00 AM, Greg Janée wrote:
>> errno=2 (ENOENT)
>> What could not be existing?
>
> Strange. Could the value of errno have been clobbered before you
> read it?
>
> What can you see if you run the app under "truss -tfcntl"?

___
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] Thread sync issue

[Michael Black]

Are you misreading that lineit's not worded very well.

To rephrase in the positive sense instead of the negative.

"provided every thread has its own db connection"


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ashok Pitambar
Sent: Sunday, February 24, 2013 7:44 AM
To: Roger Binns; General Discussion of SQLite Database
Subject: Re: [sqlite] Thread sync issue

Read point 2 multithread carefully it says "provided no single db
connection is  used simultaneously in two or more threads".  I am
creating one db connection and it is accessed by multiple threads.
  I am facing while reading the count from tables. There is
mismatch due sync issue.

Regards,
Ashok

- Reply message -
From: "Roger Binns" 
To: "General Discussion of SQLite Database" 
Subject: [sqlite] Thread sync issue
Date: Sun, Feb 24, 2013 1:57 pm



-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 24/02/13 00:14, Roger Binns wrote:
> On 23/02/13 21:23, Ashok Pitambar wrote:
>> Sqlite doc says it thread safe ...
>
> http://www.sqlite.org/threadsafe.html
>
> What makes you think the doc is wrong?

One big gotcha: errors - if you use one connection per thread then you are
fine.  If you use the same connection across threads then read on:

The one thing not mentioned is that retrieving SQLite errors is not
threadsafe.  The operating system does errors as thread specific (eg
errno, GetLastError).  SQLite does errors as connection wide (I really
wish this was fixed).

That means getting integer error codes is a race condition as a different
thread can change it before your thread has a chance to retrieve it.  If
you intend to retrieve the error string then your program *could crash*
since the pointer you get could end up with invalid memory access when you
treat it as a string.

Consequently the *only* safe way to make API calls where you intend to
look at the error code or string is to add yet another level of locking.
You can use sqlite3_db_mutex to get the mutex for a connection, and
sqlite3_mutex_enter/leave to keep it locked while you extract codes and
messages.  For example to safely call a single function (sqlite3_blobopen
in this example but it applies to every sqlite3 call that returns an error):

  int rc=SQLITE_OK;
  char *errmsg=NULL;
  sqlite3_mutex_enter(sqlite_db_mutex(db));
  rc=sqlite3_blobopen(.);
  if(rc!=SQLITE_OK) {
 rc=sqlite3_extended_errcode(db);
 errmsg=strdup(sqlite3_errmsg(db);
  }
  sqlite3_mutex_leave(sqlite_db_mutex(db));
  // now you can safely use rc and errmsg

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlEpztEACgkQmOOfHg372QTfLgCfVzKDib6ExfEhUO7StM5XYWT1
YSAAnRUWckgS95dF+7Rvq1RtU+DS8U7X
=LB1g
-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

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

Re: [sqlite] import TXT file

[Michael Black]

I wrote an import util a while ago which might help you out as it's a bit
more picky and gives more intelligent error messages than .import does.
http://www.mail-archive.com/sqlite-users@sqlite.org/msg70182.html

Show us some example lines and we can help a lot better.

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

Re: [sqlite] got selected items ok but can't update using them

[Michael Black]

You need the selects in each = expression to replace the variables there.
Something like this if I translated your query correctly.

UPDATE stmnts SET itm=(SELECT itm FROM std_itms where ID = std_id) where
ID=(SELECT alias_id from alias_itms);

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of e-mail mgbg25171
Sent: Wednesday, February 20, 2013 6:46 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] got selected items ok but can't update using them

Thanks Simon...
Your's works a treat
Re your question about my error

Here's my query

UPDATE stmnts SET itm=n where ID=i
(
SELECT alias_id i,
(SELECT orig_itm FROM stmnts where ID = alias_id) o,
(SELECT itm FROM std_itms where ID = std_id) n
FROM
(SELECT std_id, alias_id FROM alias_itms)
)



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

Re: [sqlite] SQLite error near "16": syntax error

[Michael Black]

You're inserting a lot of stringsso you have to put single quotes around
all the string fields on your insert.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of mikkelzuuu
Sent: Tuesday, February 19, 2013 7:16 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite error near "16": syntax error

1-2-2013 16:58|H2|NL-Radio 2|2013-01-03T00:00:00.000Z|172806528

An example of the output that I have to do. I see the 16 s on the first cell
and the time, but I wouldn't know why its giving me an error there. Would I
have to change the setup of the Cell in my SQLite Database?



--
View this message in context:
http://sqlite.1065341.n5.nabble.com/SQLite-error-near-16-syntax-error-tp6708
6p67091.html
Sent from the SQLite mailing list archive at Nabble.com.
___
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] Is it possible to use SQLITE to calculate the streakedness of numeric data?

[Michael Black]

Does this get you started?  It calculates a running standard deviation over
a window.
With a bit more effort you can add a running average and other calculations
on the window.
Do you have a reference for this streakedness measurement?  I couldn't find
one.

I used this library extension from http://www.sqlite.org/contrib

http://www.sqlite.org/contrib/download/extension-functions.c?get=25


Assumes window size of 10 but you could put that value in the data (if it's
variable) and use it from there too if you want.
FYI...a linear sequence like this has a constant standard deviation.

select load_extension('./libsqlitefunctions.so');
create table a(n,deviation);
create table window(n);
create trigger trig1 after insert on a begin
delete from window where rowid%10 = new.rowid%10;
insert into window values(new.n);
update a set deviation = (select sqrt(sum(square(n-(select sum(n)/10 from
window)))/10) from window) where new.rowid >=10 and rowid=new.rowid;
end;
insert into a(n) values(1.0);
insert into a(n) values(2.0);
insert into a(n) values(3.0);
insert into a(n) values(4.0);
insert into a(n) values(5.0);
insert into a(n) values(6.0);
insert into a(n) values(7.0);
insert into a(n) values(8.0);
insert into a(n) values(9.0);
insert into a(n) values(10.0);
insert into a(n) values(11.0);
insert into a(n) values(12.0);
insert into a(n) values(13.0);
insert into a(n) values(14.0);
insert into a(n) values(15.0);
insert into a(n) values(16.0);
insert into a(n) values(17.0);
insert into a(n) values(18.0);
insert into a(n) values(19.0);
insert into a(n) values(20.0);
select * from a;
1.0|
2.0|
3.0|
4.0|
5.0|
6.0|
7.0|
8.0|
9.0|
10.0|2.87228132326901
11.0|2.87228132326901
12.0|2.87228132326901
13.0|2.87228132326901
14.0|2.87228132326901
15.0|2.87228132326901
16.0|2.87228132326901
17.0|2.87228132326901
18.0|2.87228132326901
19.0|2.87228132326901
20.0|2.87228132326901



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Frank Chang
Sent: Monday, February 18, 2013 9:50 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Is it possible to use SQLITE to calculate the streakedness
of numeric data?

Would anyone know how to use SQLITE to calculate the streakedness of data?
The definition of streakedness is how many deviations away from the
mean(i.e running average a numerical data streak is Thank you for your help.

A variable R can be used to indicate how many deviations away from the mean
a particular streak is. According to the disclosed embodiment, the level of
a streak can be defined not just in (integer*deviation) distances from the
mean but also as (integer*fraction_of_deviation) distances. To accomplish
this, a variable R-factor can be used. The R-factor indicates the
separation between two successive R-levels in terms of a fraction of the
deviation. By varying the R-factor, streaks can be ranked as required.
However, the "credibility" of the streak should also be considered, and
included in a ranking mechanism. The deviation within the streak is an
obvious measure of how staggered the data is within the streak. A good
streak should be less staggered, or in other words, have less deviation.
For this reason, a very high level streak is considered to be good, even if
its deviation is more than what would normally be desired. Thus, while the
level R influences the ranking positively, the deviation within the streak
influences it negatively.
___
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] MIN(x), MAX(x) confusion

[Michael Black]

Probably because you're storing your numbers as text.  How are you inserting
them?
Do a .dump from the shell and you'll see it in the record dump.

So '7' > '20' but 7 < 20



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Stephan Beal
Sent: Monday, February 11, 2013 7:47 AM
To: General Discussion of SQLite Database
Subject: [sqlite] MIN(x), MAX(x) confusion

Hiho,

Summary: i'm getting an incorrect/unexpected MIN()/MAX() results and have
no clue why. After running these results by a colleague, we're both
confused, and thus this post. Here's my data (JMeter output) and results:

SQLite version 3.7.16 2013-01-03 19:34:46
(built from sources updated an hour or so ago)
...
sqlite> .header on
sqlite> select * from res where label='access service';
ts,elapsed,label,rc,msg,thread,dataType,success,bytes,latency
1360586493370,27,access service,200,OK,Thread Group 1-1,text,true,4379,27
1360586494947,20,access service,200,OK,Thread Group 1-2,text,true,4378,20
1360586496927,6,access service,200,OK,Thread Group 1-3,text,true,4378,6
1360586499102,5,access service,200,OK,Thread Group 1-4,text,true,4378,5
1360586501135,6,access service,200,OK,Thread Group 1-5,text,true,4378,6
1360586503112,5,access service,200,OK,Thread Group 1-6,text,true,4378,5
1360586505116,7,access service,200,OK,Thread Group 1-7,text,true,4378,7
1360586507133,6,access service,200,OK,Thread Group 1-8,text,true,4378,6
1360586509333,6,access service,200,OK,Thread Group 1-9,text,true,4377,6
1360586511238,6,access service,200,OK,Thread Group 1-10,text,true,4378,6
1360586513321,6,access service,200,OK,Thread Group 1-11,text,true,4378,6
1360586515244,7,access service,200,OK,Thread Group 1-12,text,true,4378,7
1360586517295,6,access service,200,OK,Thread Group 1-13,text,true,4378,6
1360586519255,6,access service,200,OK,Thread Group 1-14,text,true,4378,6
1360586521261,5,access service,200,OK,Thread Group 1-15,text,true,4378,5
1360586523329,6,access service,200,OK,Thread Group 1-16,text,true,4378,6
1360586525316,6,access service,200,OK,Thread Group 1-17,text,true,4377,6
1360586527325,6,access service,200,OK,Thread Group 1-18,text,true,4378,6
1360586529301,6,access service,200,OK,Thread Group 1-19,text,true,4378,6
1360586531308,6,access service,200,OK,Thread Group 1-20,text,true,4378,6


Notice that the min/max "elapsed" values are 5 and 27, respectively. Now my
results (which i cannot explain)...

sqlite>  SELECT label, count(*), MIN(elapsed), AVG(elapsed), MAX(elapsed)
FROM res where label='access service';
label,count(*),MIN(elapsed),AVG(elapsed),MAX(elapsed)
access service,20,20,7.7,7

sqlite>  SELECT label, count(*), MIN(elapsed), AVG(elapsed), MAX(elapsed)
FROM res where label='access service' GROUP BY label;
label,count(*),MIN(elapsed),AVG(elapsed),MAX(elapsed)
access service,20,20,7.7,7

sqlite> select count(*) from res where label='access service';
count(*)
20

sqlite> select MIN(elapsed) from res where label='access service';
MIN(elapsed)
20

sqlite> select AVG(elapsed) from res where label='access service';
AVG(elapsed)
7.7

sqlite> select MAX(elapsed) from res where label='access service';
MAX(elapsed)
7

sqlite> select label, MAX(elapsed) from res group by label;
label,MAX(elapsed)
access service,7
autocomplete: choose LeadSource,70
autocomplete: choose LeadType,79
...

sqlite> select MIN(elapsed), MAX(elapsed) from res where label='access
service';
MIN(elapsed) MAX(elapsed)
20 7


i'm very confused.  Surely i'm doing something wrong here (as i refuse to
believe that this is an sqlite3 bug)?

:-?

PS: i'm not an sql guru.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
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] ranking in a view

[Michael Black]

http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2013-February/04
4367.html


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jeff Archer
Sent: Saturday, February 09, 2013 7:01 AM
To: SQLite-user.org
Subject: [sqlite] ranking in a view

There was recently a post where someone was trying to update the ranking of
records in a table and someone else presented a clever view which did the
ranking and thus required no table update.  I wanted to try and understand
how this view worked but when I have looked back to find it, I can't.
Could someone please repost that view or help me find it in some way?

Thanks in advance,

Jeff
___
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] Strange eviction from Linux page cache

[Michael Black]

I re-ran my test with a 33MB database.  Using the shell to .dump the file
doesn't fill the cache.
But my testro program does.  If you open the database with the shell it
clears the cache again (it's opening it read/write).

ls -l insert.db
-rw-r--r-- 1 mblack users 35016704 Feb  7 10:54 insert.db
vmtouch insert.db
   Files: 1
 Directories: 0
  Resident Pages: 0/8549  0/33M  0%
 Elapsed: 0.000372 seconds
sqlite3 insert.db .dump >/dev/null
vmtouch insert.db
   Files: 1
 Directories: 0
  Resident Pages: 0/8549  0/33M  0%
 Elapsed: 0.002608 seconds
./testro insert.db
vmtouch insert.db
   Files: 1
 Directories: 0
  Resident Pages: 8549/8549  33M/33M  100%
 Elapsed: 0.001311 seconds

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James Vanns
Sent: Thursday, February 07, 2013 9:31 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Strange eviction from Linux page cache

I fear I must correct myself. SQLite appears to 2nd guess/avoid the Linux
kernel page cache both when the file is local and when it is remote. I'd
wager that it's own internal cache (an LRU of somesort?) only ever ensures
that there are n pages in RAM and therefore it is only these pages that
Linux itself will cache. In fact, this is easy to confirm;

a) Write local DB file
b) Use vmtouch to monitor the file state in the OS page cache
c) Use SQLite to read local DB file
d) Observe

Only 16MB of the file resides in cache after the processes terminate.

a) Write local DB file
b) Use vmtouch to monitor the file state in the OS page cache
c) cat/dd the file to /dev/null - read pages reside in RAM
d) Use SQLite to read local DB file
e) Observe

All 200MB of the file resides in cache after processes terminate.

This behaviour seems almost identical for NFS with the addition that SQLite
will evict all the pages from the OS cache entirely.

I shall ask on the developer list why this is and if I can just prevent
SQLite
trying to do the job of the page cache. I understand that it may have to do
this for small, mobile devices or for a platform that doesn't have a page
cache,
but it shouldn't for normal Linux/UNIX/Windows workstations, servers etc.

Jim

- Original Message -
From: "James Vanns" 
To: "General Discussion of SQLite Database" 
Sent: Thursday, 7 February, 2013 2:52:30 PM
Subject: Re: [sqlite] Strange eviction from Linux page cache

> I would be interested to know if handing a sequential file over the
> same NFS connection shows the same behaviour.  This would use
> fread() which should trigger any caching that the operating system
> and file system implement for that type of connection.  You could
> test this using a text editor and a very long text file.

Already tested that and as expected, pages remain in the cache. I basically
did cat /nfs/machine/location/file.txt (a file of around 5GB) 1>
/tmp/foobar.

I can see using both xosview and vmtouch that the pages aren't evicted -
until 
a process needs RAM of course.

In fact, if I 'dd if=' over NFS then the pages are cached as
expected.
It is only when SQLite itself opens the file are the pages immediately
evicted.

Jim

> I haven't looked at the code for SQLite.  As far as I know, even
> though you can tell SQLite that /you/ aren't going to make changes
> to the file, there's no way to tell it that nobody else is going to
> make changes between your SELECT commands.  Consequently there's no
> way to force it to use the cache.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

-- 
Jim Vanns
Senior Software Developer
Framestore

-- 
Jim Vanns
Senior Software Developer
Framestore
___
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] Strange eviction from Linux page cache

[Michael Black]

Would it be any use to you to have a separate process which mmaps the file?
Seems to me that would probably keep all the pages in cache constantly.


I just did a local test on my NFS setup and the file appears to cache just
fine.
Does yours behave differently?

#include 
#include 

int main(int argc, char *argv[])
{
  sqlite3 *db;
  int rc;
  if (argc != 2) {
printf("Usage: %s dbfile\n",argv[0]);
return 1;
  }
  rc = sqlite3_open_v2(argv[1],,SQLITE_OPEN_READONLY,NULL);
  if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
return 1;
  }
  rc=sqlite3_exec(db,"select * from t",NULL,NULL,NULL);
  if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
return 1;
  }
  sleep(60);
  return 0;
}
I had an 8.7MB database I used this on

vmtouch insert.db
   Files: 1
 Directories: 0
  Resident Pages: 0/2128  0/8M  0%
 Elapsed: 0.000394 seconds
./testro insert.db&
[1] 26598
sqlite]$ vmtouch insert.db
   Files: 1
 Directories: 0
  Resident Pages: 2128/2128  8M/8M  100%
 Elapsed: 0.000556 seconds


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

Re: [sqlite] Strange eviction from Linux page cache

[Michael Black]

Nothing to do with SQLite.  NFS won't use cache by default.  You have to
mount it with the "fsc" option.
https://access.redhat.com/knowledge/docs/en-US/Red_Hat_Enterprise_Linux/6/ht
ml/Storage_Administration_Guide/fscachenfs.html



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James Vanns
Sent: Thursday, February 07, 2013 3:56 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Strange eviction from Linux page cache

Hello list. I'd like to ask someone with more SQLite experience than me a
simple question. First, some background;

Distribution: Scientific Linux 6.3
Kernel: 2.6.32-279.9.1.el6.x86_64
SQLite version: 3.6.20

We have a single process that, given some data, does some processing and
writes it all to a single SQLite DB file. This is a write-once process. When
this task is finished, the file itself is marked as read only (0444).

This file exists on an NFS share for multiple users to read - nothing
further is ever written to it. The problem we're seeing is that when this DB
file is read from (over NFS) none of the pages are cached (despite ~12GB
free for page cache use) or at least immediately evicted. This is quite
detrimental to performance because our resulting data files (SQLite DB
files) are between 100 to 400 MB in size. We *want* it to be cached - the
whole thing. The page cache would do this nicely for us and allow multiple
processes on the same machine to share that data without any complication.

I understand that SQLite implements it's own internal page cache but why, on
a standard desktop machine, will it not use the page cache. Is there anyway
of forcing it or bypassing the internal page cache in favour of the job that
Linux already does? I cannot find any reference to O_DIRECT or madvise() or
favdise() etc. in the code. The following PRAGMAs don't help either;

PRAGMA writable_schema = OFF
PRAGMA journal_mode = OFF
PRAGMA synchronous = OFF

PRAGMA cache_size = -

Obviously that last one works - but only for a single process and for the
lifetime of that process. We want the pages to reside in RAM afterwards.

Anyone out there know how to correct this undesirable behaviour?

Regards,

Jim Vanns

-- 
Jim Vanns
Senior Software Developer
Framestore
___
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] Deletion slow?

[Michael Black]

If you don't put a COMMIT on your mysql example I don't think you're
comparing apples-to-apples.

I'm guessing your Rasberry PI sdcard isn't exactly a high-speed performer
http://jalada.co.uk/2012/05/20/raspberry-pi-sd-card-benchmark.html

How long does it take you to import your database for example?



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jason Gauthier
Sent: Tuesday, February 05, 2013 6:44 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Deletion slow?

There were a few responses to this, so I will put them all below:

Igor:
>The difference between select and delete is that the former only reads from
the file, while the latter also writes to it. What kind of hardware does
your >system have? Is there any reason why it may be unusually slow writing
to disk (or whatever device you are storing files on)? 1.5 sec to delete 200
records >does sound excessive (for a regular PC with database file stored on
a hard drive), even considering that three indexes need to be updated.

The system is a raspberry pi.  ARM processor running around 700Mhz. 256MB of
memory, and an sdcard filesystem.

Dominique:
>Well, you're paying for the maintenance of the indexes, 4 of them. Try the
delete with fewer indexes, and you'll see the delete time improve.
>There's not much you can do about it I'm afraid.

>BTW, tell us the total count(*) and .db file size, and perhaps your DB page
size as well. --DD

I dropped and recreated the table leaving only 2 indexes. The primary, and
icur_time.
I'm down to ~1s.

# time sqlite3 trip.db 'delete from trip where key<=600'
real0m0.911s
user0m0.020s
sys 0m0.020s
# time sqlite3 trip.db 'delete from trip where key<=800'
real0m0.952s
user0m0.000s
sys 0m0.040s

Total count of the table is about 40k records.   Not sure how to retrieve DB
page size.

> Load the same data into another RDBMS you're familiar with, and see how it
compares perf-wise.

I built the table on the same system with mysql. I loaded the same 40k
records and ran the same deletion.
Here are my results:
mysql> delete from trip where id<=84540;
Query OK, 201 rows affected (0.09 sec)
mysql> delete from trip where id<=84740;
Query OK, 200 rows affected (0.15 sec)

It definitely performs better, but I really do not want to use such a large
piece of software for this.

Thanks for help so far.  I really appreciate all the responses.

Jason



___
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] Deletion slow?

[Michael Black]

I made a test database using your table and this
main()
{
int i;
for(i=0;i<1;++i) {
printf("insert into trip(key) values(%d);\n",i);
  }
}

The deleted all keys < 200.
time sqlite3 trip.db 'delete from trip where key < 200'

real0m0.004s
user0m0.001s
sys 0m0.003s

I assume you're running on your "not quite" machine?  Are you disk-based?
What's the speed of that?

Can you generate that same database like this and we can then actually
compare speed?

Otherwise you're in a 1-off situation which is not very useful for
comparison.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jason Gauthier
Sent: Tuesday, February 05, 2013 6:54 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Deletion slow?

Hey Everyone,

 I am a fairly new user of sqlite, but not particularly new to SQL
principles.  I am developing an application that will run on a low end
system.
Not quite embedded, but not quite a PC.  In my application, I do frequent
table deletes.  My results have been poor, and I am looking for someone to
tell me "I'm doing it wrong", or maybe "that's the best you're going to
get", etc.

Anyway, my table is create as such:

create table trip (
key integer primary key, 
vin varchar(17), 
ts int, 
cur_time int, 
caps varchar(20), 
cmdid int, 
value real, 
longitude real, 
latitude real);

create index ivin on trip (vin); 
create index icaps on trip (caps); 
create index icur_time on trip (cur_time);

sqlite> .indices
icaps
icur_time
ivin

I understand that a primary key index is created automatically, so it won't
be listed here.

Now, I can execute a queries very quickly:
---
time sqlite3 trip.db 'select count(*) from trip where key<=1400'
200

real0m0.026s
user0m0.020s
sys 0m0.000s
---
Notice there are only 200 rows that match this query!
---
time sqlite3 trip.db 'select * from trip where key<=1400'
real0m0.205s
user0m0.030s
sys 0m0.070s
---
200ms is not bad. 
---
time sqlite3 trip.db 'delete from trip where key<=1400'

real0m1.532s
user0m0.050s
sys 0m0.020s
---
The deletion takes 1.5 seconds.  This is actually fast, it usually takes
closer 2 seconds.

Any thoughts on why this may be so slow, or what I can do to improve it?

Thanks,

Jason

___
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] SQL query

[Michael Black]

You need to start showing your testinga .dump of your tables might help
plus show what you execute in the shell.
This works just fine and appears to produce what you want.
I don't know what you want #1 in your 1st question as the hash matches #0
which is what you said you want to exclude.

SQLite version 3.7.14.1 2012-10-04 19:37:12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table files(file,setid,hash);
sqlite> insert into files
values('file1',0,'8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279');
sqlite> insert into files
values('file1',1,'8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279');
sqlite> insert into files
values('file1',2,'8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279');
sqlite> insert into files
values('file1',3,'B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5');
sqlite> insert into files
values('file1',4,'0546-4667-5A69-6478-FC97-6F27-840D-7D62');
sqlite> insert into files
values('file1',5,'0546-4667-5A69-6478-FC97-6F27-840D-7D62');
sqlite> insert into files
values('file1',6,'0546-4667-5A69-6478-FC97-6F27-840D-7D62');
sqlite> insert into files
values('file1',7,'01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29');
sqlite> insert into files
values('file1',8,'01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29');
sqlite> SELECT * FROM files WHERE hash NOT IN (SELECT hash FROM files WHERE
setid=0);
file1|3|B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5
file1|4|0546-4667-5A69-6478-FC97-6F27-840D-7D62
file1|5|0546-4667-5A69-6478-FC97-6F27-840D-7D62
file1|6|0546-4667-5A69-6478-FC97-6F27-840D-7D62
file1|7|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
file1|8|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
sqlite> SELECT * FROM files WHERE hash NOT IN (SELECT hash FROM files WHERE
setid=0) group by hash;
file1|8|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
file1|6|0546-4667-5A69-6478-FC97-6F27-840D-7D62
file1|3|B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5
sqlite> delete from files where setid=0;
sqlite> SELECT * FROM files WHERE hash NOT IN (SELECT hash FROM files WHERE
setid=0);
file1|1|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
file1|2|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
file1|3|B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5
file1|4|0546-4667-5A69-6478-FC97-6F27-840D-7D62
file1|5|0546-4667-5A69-6478-FC97-6F27-840D-7D62
file1|6|0546-4667-5A69-6478-FC97-6F27-840D-7D62
file1|7|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
file1|8|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
sqlite> SELECT * FROM files WHERE hash NOT IN (SELECT hash FROM files WHERE
setid=0) group by hash;
file1|8|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
file1|6|0546-4667-5A69-6478-FC97-6F27-840D-7D62
file1|2|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
file1|3|B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Sanderson
Sent: Wednesday, January 30, 2013 4:33 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQL query

Thanks - replace set with setid - query is the same (it was badly
simplified sorry)

Another real world example using the sql query

SELECT * FROM files WHERE hash NOT IN (SELECT hash FROM files WHERE
setid=0);

file1 08465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
file1 18465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
file1 28465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
file1 3B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5
file1 40546-4667-5A69-6478-FC97-6F27-840D-7D62
file1 50546-4667-5A69-6478-FC97-6F27-840D-7D62
file1 60546-4667-5A69-6478-FC97-6F27-840D-7D62
file1 701EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
file1 801EE-7E2E-2242-E734-B125-D02F-A7F0-DC29

I get an empty data set again, what I want is something like this

file1 18465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
file1 3B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5
file1 60546-4667-5A69-6478-FC97-6F27-840D-7D62
file1 801EE-7E2E-2242-E734-B125-D02F-A7F0-DC29


also there could be instances where  there is no setid=0 row, as below, in
this case I would want the same dataset as returned above

file1 18465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
file1 28465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
file1 3B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5
file1 40546-4667-5A69-6478-FC97-6F27-840D-7D62
file1 50546-4667-5A69-6478-FC97-6F27-840D-7D62
file1 60546-4667-5A69-6478-FC97-6F27-840D-7D62
file1 701EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
file1 801EE-7E2E-2242-E734-B125-D02F-A7F0-DC29



On 30 January 2013 22:14, Michael Black <mdblac...@yahoo.com> wrote:

> 'set' is a reserved word.  I get an error running your select statement.
>
> Change it.
>
>
> SQLite version 3.7.14.1 2012-10-04 19:37:12
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table files(file,setid,hash);
> sqlite> insert into files
> values('1.jpg',0,'5DA4-CD3A-62DE-2F9D-4BD7-6E24-EACE-936D');
> sqlite> insert into files
>

Re: [sqlite] SQL query

[Michael Black]

'set' is a reserved word.  I get an error running your select statement.

Change it.


SQLite version 3.7.14.1 2012-10-04 19:37:12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table files(file,setid,hash);
sqlite> insert into files
values('1.jpg',0,'5DA4-CD3A-62DE-2F9D-4BD7-6E24-EACE-936D');
sqlite> insert into files
values('1.jpg',1,'5DA4-CD3A-62DE-2F9D-4BD7-6E24-EACE-936D');
sqlite> insert into files
values('1.jpg',2,'5DA4-CD3A-62DE-2F9D-4BD7-6E24-EACE-936D');
sqlite> insert into files
values('1.jpg',3,'5DA4-CD3A-62DE-2F9D-4BD7-6E24-EACE-936D');
sqlite> insert into files
values('1.jpg',4,'890B-4533-447E-6461-070E-FDB7-799E-1FB8');
sqlite> SELECT * FROM files WHERE hash NOT IN (SELECT hash FROM files WHERE
setid=0);
1.jpg|4|890B-4533-447E-6461-070E-FDB7-799E-1FB8


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Sanderson
Sent: Wednesday, January 30, 2013 4:05 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQL query

I have a test set with the following real data

1.jpg05DA4-CD3A-62DE-2F9D-4BD7-6E24-EACE-936D
1.jpg15DA4-CD3A-62DE-2F9D-4BD7-6E24-EACE-936D
1.jpg25DA4-CD3A-62DE-2F9D-4BD7-6E24-EACE-936D
1.jpg35DA4-CD3A-62DE-2F9D-4BD7-6E24-EACE-936D
1.jpg4890B-4533-447E-6461-070E-FDB7-799E-1FB8

SELECT * FROM files WHERE hash NOT IN (SELECT hash FROM files WHERE set=0)

returns an empty data set, but should return the item from set 4

The following does work

select * from files where set > 0 and not exists (select * from files a
where hash=files.hash and set=0);

which is great and solves my problem, but I cant see why the first query
doesn't work.


On 30 January 2013 21:37, Paul Sanderson
wrote:

>
> Thanks All - duplicated means the content is the same as well as the name,
> different is the filename is the same but the content is different.
>
> I need to refine my query to produce only one copy of any that is not in
> set 0
>
> file10ABCD
> file11ABCD
> file13EF01
> file20BCE2
> file22BCE2
> file35EE34
> file40EE22
> file41FF34
> file43FF34
> file44FF34
>
>
> My query would return
>
> file13EF01
> file35EE34
> file41FF34, or file43FF34, or file44FF34
>
> Thanks
>
>
>


-- 
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
___
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] SQL query

[Michael Black]

sqlite> create table t(name,num,ref);
sqlite> insert into t values('file1',0,'ABCD');
sqlite> insert into t values('file1',1,'ABCD');
sqlite> insert into t values('file1',3,'EF01');
sqlite> insert into t values('file2',0,'BCE2');
sqlite> insert into t values('file2',2,'BCE2');
sqlite> insert into t values('file3',5,'EE34');
sqlite> insert into t values('file4',0,'EE22');
sqlite> insert into t values('file4',1,'FF34');
sqlite> insert into t values('file4',3,'FF34');
sqlite> insert into t values('file4',4,'FF34');
sqlite> select distinct(ref) from t where ref not in (select ref from t
where num=0);
EF01
EE34
FF34

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Sanderson
Sent: Wednesday, January 30, 2013 3:37 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQL query

Thanks All - duplicated means the content is the same as well as the name,
different is the filename is the same but the content is different.

I need to refine my query to produce only one copy of any that is not in
set 0

file10ABCD
file11ABCD
file13EF01
file20BCE2
file22BCE2
file35EE34
file40EE22
file41FF34
file43FF34
file44FF34

My query would return

file13EF01
file35EE34
file41FF34, or file43FF34, or file44FF34

Thanks
___
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] Sqlite ubuntu 12.10 compile/install

[Michael Black]

Use the amalgamation:

cc -O -c sqlite3.c

Then link it into your program.  Most of us recommend avoiding shared
libraries.

If you need special features you may have to set some flags.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of David Clark
Sent: Wednesday, January 30, 2013 9:06 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Sqlite ubuntu 12.10 compile/install

Ok I have used sqlite in windows software no problems.  
I am now trying to use it in software I am porting to ubuntu 12.10 and I am
finding that the source code I used under windows has compile issues.  And
when I downloaded the .gz file and ran ./configure on that fileset I got
errors
on that.  

I know I not being specific on the errors I got.  But my question here is...
basically under ubuntu what should my procedure be for installing and
compiling sqlite?  Yes I am new to ubuntu so the obvious is not so obvious 
on that OS.  

Thank you,

David Clark
___
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] faster query - help

[Michael Black]

The real answer is try both and see which is faster.

My guess is #1 is probably faster since I don't think there's an easy way to
limit the left-hand side of a left-join operation to do #2 without touching
all the music_file records, is there?  The join would have to match on
file_id so would hit the entire music_files against .  I'm sure somebody
will correct me if I'm wrong here...I'm not confident that the join isn't
smarter than that.

Is the join done BEFORE the order by and limit?  Or after?  I would think
it's done before.  Or will the order by and limit apply to the left-hand
side first before the join when it can?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of moti lahiani
Sent: Friday, January 25, 2013 8:45 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] faster query - help

Hi

I have a data base with the following tables
1) files - each file in the system have his information in this table. that
table include 12 different columns like file_id as integer primary key
autoincrement, date_uploaded as integer, file_type as integer and so..
2) music_files - hold more information on file in case the file is music
file. columns like file_id as integer, artist, genre, album_name and more

I need to query those 2 tables (with both all information) according to
some filters/rule that I have, order the results according to date_uploaded
and return the top(10) as final result

my question is what is faster and have the best performance: (the table
files can have more than 150 rows)

option 1: select the file_id and date_uploaded, order by date_uploaded and
get the top(10) file_id and than select all information from both(files and
music_files) tables according to those top(10) results
option 2: select all information from both(files+music files) tables, order
by date_uploaded and return the top(10) as results


Thanks
___
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] bug report: out of memory error for most operations on a large database

[Michael Black]

How much free disk space do you have?  Your temp tables might be exceeding
capacity.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of ammon_lymphater
Sent: Thursday, January 24, 2013 1:41 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] bug report: out of memory error for most operations on a
large database

Summary: except for  select count(*) , all operations on a large table (500m
rows/80gb) result in out-of-memory error

 

Details:

a.   Using SQLite3 ver 3.7.15.2, Windows 7  64 bit AMD(but the error
also in 3.6.xx and on other Windows platforms)

b.  Created a table (schema attached), imported data from a csv file
using sqlite3 (no problems)

c.  "select Name5, count(*) from LargeData group by name5 order by name5
results" in Error: out of memory (the cardinality of Name5 is 12)

d.  "sqlite> select count(*) from StorageHourlyBySIDCL2;" gives 587608348,
as it should

e.  The above independent of cache_size (from 0.3 to 1.5gb); happens for
page_size 32kb and 64kb (others not tested)

 

(personally not urgent for me - just testing the limits -  but may be useful
for others)

 

   table schema & sqlite3_analyzer output

CREATE TABLE largedata (

  name1 smalldatetime

 , Name2 uniqueidentifier

 , Name3 varchar (16)

 , Name4 varchar (8)

 , Name5 varchar (80)

 , Name6 real

 , Name7 real

 , Name8 real

 , Name9 real

 , Name10 real

 , Name11 real

 , Name12 real

 , Name13 real

 , Name14 smallint

 , Name15 tinyint

 , Name16 tinyint

 , Name17 smalldatetime

 , Name18 real

 , Name19 tinyint

);

 

-- SQLITE3_ANALYZER output

/** Disk-Space Utilization Report For h:\temp\convert\import2.db

 

Page size in bytes 32768

Pages in the whole file (measured) 2578119

Pages in the whole file (calculated).. 2578118

Pages that store data. 2578118100.000%

Pages on the freelist (per header) 00.0%

Pages on the freelist (calculated) 10.0%

Pages of auto-vacuum overhead. 00.0%

Number of tables in the database.. 11

Number of indices. 0

Number of named indices... 0

Automatically generated indices... 0

Size of the file in bytes. 84479803392

Bytes of user payload stored.. 79293861071  93.9%

 

*** Page counts for all tables with their indices 

 

LargeData. 2578108100.000%

smalldata 10.0%

(zero-length tables omitted)

 

*** All tables ***

 

Percentage of total database.. 100.000%

Number of entries. 587608358

Bytes of storage consumed. 84479770624

Bytes of payload.. 79293871126  93.9%

Average payload per entry. 134.94

Average unused bytes per entry 0.32

Average fanout 2716.00

Fragmentation.   0.11%

Maximum payload per entry. 1933

Entries that use overflow. 00.0%

Index pages used.. 949

Primary pages used 2577169

Overflow pages used... 0

Total pages used.. 2578118

Unused bytes on index pages... 3904523 12.6%

Unused bytes on primary pages. 1841229100.22%

Unused bytes on overflow pages 0

Unused bytes on all pages. 1880274330.22%

 

--$ removing unused tables

*** Table STORAGEHOURLYBYSIDCL2 **

 

Percentage of total database.. 100.000%

Number of entries. 587608348

Bytes of storage consumed. 84479442944

Bytes of payload.. 79293861071  93.9%

Average payload per entry. 134.94

Average unused bytes per entry 0.32

Average fanout 2716.00

Fragmentation.   0.11%

Maximum payload per entry. 183

Entries that use overflow. 00.0%

Index pages used.. 949

Primary pages used 2577159

Overflow pages used... 0

Total pages used.. 2578108

Unused bytes on index pages... 3904523 12.6%

Unused bytes on primary pages. 1838055150.22%

Unused bytes on overflow pages 0

Unused bytes on all pages. 1877100380.22%

 

 

*** Table SQLITE_MASTER **

 

Percentage of total database..   0.0%

Number of entries. 10

Bytes of storage consumed. 32768

Bytes of payload.. 10055   30.7%

Average payload per entry. 1005.50

Average unused bytes per 

Re: [sqlite] Sum of various rows

[Michael Black]

create table test (id,invoice,transfer,price);
insert into test values(10,500,200,0);
insert into test values(200,300,300,200);
insert into test values(334,3000,200,3000);
select sum(invoice),sum(transfer),sum(price) from test where id in
(10,200,334);
3800|700|3200


Michael Black



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

Re: [sqlite] Facing "SQLite ERROR : unable to open database file" error

[Michael Black]

Is there some reason there's the rather poor "unable to open" message
without the actual error message?

Strerror coming to mind if it hasn't disappeared under foot at those places?

I see these questions on the list fairly often and they would all be
answered with an intelligent error messagefrequently permission
oriented.

All such errors should say the file they errored on and the system error
along with it.

At an absolute minimum they should say the filename which would still allow
much better diagnosis rather than trying to guess what's going on.





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

Re: [sqlite] Need help with query

[Michael Black]

You're structure is bad for future growth (i.e. multiple languages) as the
query gets really nasty really quickly.

You should normalize this data and your query will never change.


pragma foreign_keys=ON;
CREATE TABLE buttons (
ID integer primary key autoincrement,
Key1 varchar not null,
Key2 varchar not null
);
insert into buttons(key1,key2) values('FORM1','SAVE_BUTTON');
insert into buttons(key1,key2) values('FORM1','HELP_BUTTON');

CREATE TABLE masterlanguages(
ID integer primary key autoincrement,
ISOCode varchar not null
);
insert into masterlanguages (ISOCode) values ('ENG');
insert into masterlanguages(ISOCode) values ('DEU');

CREATE TABLE buttontext (
ID integer primary key autoincrement,
Description varchar not null,
masterlanguage integer,
button integer,
foreign key(masterlanguage) references masterlanguages(id),
foreign key(button) references buttons(id)
);

insert into buttontext (Description,masterlanguage,button)
values('Save',1,1);
insert into buttontext (Description,masterlanguage,button)
values('Help',1,2);
insert into buttontext (Description,masterlanguage,button)
values('Speichern',2,1);
insert into buttontext (Description,masterlanguage,button)
values('Hilfe',2,2);

All you need to do0 is specify what language you want in the query.

sqlite> select * from buttons b1 join buttontext b2 on b1.id=b2.button and
b2.masterlanguage=(select id from masterlanguages where ISOCode='ENG');
ID|Key1|Key2|ID|Description|masterlanguage|button
1|FORM1|SAVE_BUTTON|1|Save|1|1
2|FORM1|HELP_BUTTON|2|Help|1|2
sqlite> select * from buttons b1 join buttontext b2 on b1.id=b2.button and
b2.masterlanguage=(select id from masterlanguages where ISOCode='DEU');
ID|Key1|Key2|ID|Description|masterlanguage|button
1|FORM1|SAVE_BUTTON|3|Speichern|2|1
2|FORM1|HELP_BUTTON|4|Hilfe|2|2

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kai Peters
Sent: Monday, January 14, 2013 11:50 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Need help with query

Hi all,

given

CREATE TABLE masterlanguages (
  ID  integer primary key autoincrement,
  Key1varchar not null, 
  Key2varchar not null,
  ISOCode varchar not null,
  Description varchar not null,
  MaxCharsinteger default 0
);


insert into masterlanguages values (null, 'FORM1', 'SAVE_BUTTON', 'ENG',
'Save', 0);
insert into masterlanguages values (null, 'FORM1', 'HELP_BUTTON', 'ENG',
'Help', 0);
insert into masterlanguages values (null, 'FORM1', 'SAVE_BUTTON', 'DEU',
'Speichern', 0);
insert into masterlanguages values (null, 'FORM1', 'HELP_BUTTON', 'DEU',
'Hilfe', 0);



In addition to the data from 

SELECT * FROM MASTERLANGUAGES WHERE ISOCode = 'DEU' 

I also need the Description field for the corresponding record (based on
Key1 + Key2) in English so 
that I can display the original English description as well as its German
translation.


How can I achieve this?

TIA,
Kai
___
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] Concurrent read performance

[Michael Black]

What about using 2 or more databases?

Wayne Bradney  wrote:

>>>All access in SQLite is serialized. Apologies if I'm missing something 
>>>fundamental here, but that's not what I'm seeing with a file-backed database 
>>>when shared cache is OFF.My test has a single table with 1M rows, and four 
>>>queries that each yield 100K different rows. I run them two ways: 1. All 
>>>queries in a loop on the same thread in the same connection.2. Each query in 
>>>parallel on separate threads, each with its own connection. If all access 
>>>were serialized, I would expect these two tests to take about the same 
>>>amount of time overall, wouldn't I?In fact, with a file-backed database and 
>>>shared cache OFF, the second run takes about 70% less time.With shared cache 
>>>ON, they're the same. As to your second point, I probably should have made 
>>>it clear that this isn't an internal project, it's a software product, and 
>>>we don't control where it runs. I understand what an SSD is and why it's 
>>>better than a spindle drive, but my question wasn't really meant to solicit 
>>>suggestions for performan
 c
> e improvements outside the proposal at hand, which was to retire our existing 
> home-grown in-memory cache implementation (which is very fast for concurrent 
> reads, but is extremely limited in how it can be queried), and replace it 
> with a SQL-capable, relational store and still get roughly the same 
> performance. Our expectation was that we could achieve this with SQLite, but 
> were surprised by the apparent lack of read-concurrency, and wanted to get 
> some input on what our options might be in terms of SQLite configuration of 
> memory-backed databases. > From: slav...@bigfraud.org
>> Date: Sat, 12 Jan 2013 17:48:56 +
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] Concurrent read performance
>> 
>> 
>> On 12 Jan 2013, at 5:38pm, Wayne Bradney  wrote:
>> 
>> > "mode=memory=shared"
>> 
>> 
>> > 1. when shared cache is enabled, all reads are serialized, and
>> 
>> All access in SQLite is serialised.  All transactions require locking the 
>> entire database.  SQLite is very simple -- 'lite' -- so queries run 
>> extremely quickly, so you don't normally realise that any locking has taken 
>> place.
>> 
>> > 2. there doesn't seem to be any way to have a memory-backed database that 
>> > can be accessed by multiple connections without using a shared cache,  
>> > then I guess I MUST use a file-backed database to get concurrent reads, 
>> > even though I don't need the persistence and don't want to take the I/O 
>> > hit. Am I making any sense? Anything I'm missing? 
>> 
>> You are putting programming effort into making your code fast, and this is 
>> costing you (or your employer) programmer time.  Have you tried doing this 
>> using an SSD instead of a spinning disk ?  A great deal of the time taken 
>> for on-disk SQLite is waiting for the disk to spin to the right place.  With 
>> an SSD all this time vanishes and access is nearly as fast as for in-memory 
>> databases.  The advantage is that you don't spend your time on clever 
>> optimal programming or have to do any of the things required for 
>> 'mode=memory'.  In fact it works very quickly without any special modes or 
>> PRAGMAs at all.  Though I don't know your setup in detail and it may not be 
>> of such a great advantage to you.
>> 
>> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrent read performance

[Michael Black]

Also...does increasing cache_size help?
Are you able to use a RAM disk?


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Wayne Bradney
Sent: Saturday, January 12, 2013 11:39 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Concurrent read performance

I have a requirement for which I'm proposing SQLite as a solution, and would
appreciate some feedback to clarify my thinking. The application is a shared
service (.NET/WCF) that caches relational data in-memory and serves it up
when (read-only) requests come in (via operation contracts) from multiple
clients. Such client requests are high-level, not relational, and could
result in several (potentially many) individual queries to the cache itself.
These individual cache queries are dynamically generated and could be
arbitrarily complex, but are all essentially relational in nature. The
service itself will periodically load data from an external data source,
transform it and update the cache. There's no requirement currently for the
cache to ever be persisted - it can be reloaded from the external source if
necessary, but performance (especially read performance) is critical. The
amount of data/indexes to cache potentially could be quite large (of the
order of several gigabytes, let's 
 say). I've already worked an initial implementation that uses an in-memory
SQLite database via System.Data.SQLite (1.0.82.0). The service maintains a
"mode=memory=shared" database, and each SQL query happens on its own
connection, and in its own thread. Some observations of our read performance
(when the cache is fully populated and there are no writers): [Experiment 1:
Memory-backed, single query]
For simple client requests that only result in a single (albeit complex) SQL
query to the database, performance is excellent, and I'm very happy to get
the maintenance benefits of using a flexible query language against the
cache. [Experiment 2: Memory-backed, concurrent queries]
For any client request that results in multiple simultaneous SQL queries to
the database, those queries seem to be serialized rather than concurrent,
and the whole request actually performs much worse than the old
home-grown-but-horribly-limited caching/querying mechanism that was in place
beforehand, and I'm sad. [Experiment 3: File-backed, concurrent queries,
with shared cache]
I switched to a file-backed database (but still "cache=shared") and it
appears that the queries are still being serialized, and is overall about
15% slower than Experiment 2. [Experiment 4: File-backed, concurrent
queries, without shared cache]
I switched to a file-backed database without a shared cache, and performance
improved dramatically (about 70% faster than Experiment 3). It appears that
the queries are now truly happening concurrently. So it appears that, since:
1. when shared cache is enabled, all reads are serialized, and
2. there doesn't seem to be any way to have a memory-backed database that
can be accessed by multiple connections without using a shared cache,  then
I guess I MUST use a file-backed database to get concurrent reads, even
though I don't need the persistence and don't want to take the I/O hit. Am I
making any sense? Anything I'm missing?

___
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] Concurrent read performance

[Michael Black]

Did you try read-uncommitted?
Sounds promising...

2.2.1
http://www.sqlite.org/sharedcache.html

PRAGMA read_uncommitted = ;


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Wayne Bradney
Sent: Saturday, January 12, 2013 11:39 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Concurrent read performance

I have a requirement for which I'm proposing SQLite as a solution, and would
appreciate some feedback to clarify my thinking. The application is a shared
service (.NET/WCF) that caches relational data in-memory and serves it up
when (read-only) requests come in (via operation contracts) from multiple
clients. Such client requests are high-level, not relational, and could
result in several (potentially many) individual queries to the cache itself.
These individual cache queries are dynamically generated and could be
arbitrarily complex, but are all essentially relational in nature. The
service itself will periodically load data from an external data source,
transform it and update the cache. There's no requirement currently for the
cache to ever be persisted - it can be reloaded from the external source if
necessary, but performance (especially read performance) is critical. The
amount of data/indexes to cache potentially could be quite large (of the
order of several gigabytes, let's 
 say). I've already worked an initial implementation that uses an in-memory
SQLite database via System.Data.SQLite (1.0.82.0). The service maintains a
"mode=memory=shared" database, and each SQL query happens on its own
connection, and in its own thread. Some observations of our read performance
(when the cache is fully populated and there are no writers): [Experiment 1:
Memory-backed, single query]
For simple client requests that only result in a single (albeit complex) SQL
query to the database, performance is excellent, and I'm very happy to get
the maintenance benefits of using a flexible query language against the
cache. [Experiment 2: Memory-backed, concurrent queries]
For any client request that results in multiple simultaneous SQL queries to
the database, those queries seem to be serialized rather than concurrent,
and the whole request actually performs much worse than the old
home-grown-but-horribly-limited caching/querying mechanism that was in place
beforehand, and I'm sad. [Experiment 3: File-backed, concurrent queries,
with shared cache]
I switched to a file-backed database (but still "cache=shared") and it
appears that the queries are still being serialized, and is overall about
15% slower than Experiment 2. [Experiment 4: File-backed, concurrent
queries, without shared cache]
I switched to a file-backed database without a shared cache, and performance
improved dramatically (about 70% faster than Experiment 3). It appears that
the queries are now truly happening concurrently. So it appears that, since:
1. when shared cache is enabled, all reads are serialized, and
2. there doesn't seem to be any way to have a memory-backed database that
can be accessed by multiple connections without using a shared cache,  then
I guess I MUST use a file-backed database to get concurrent reads, even
though I don't need the persistence and don't want to take the I/O hit. Am I
making any sense? Anything I'm missing?

___
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] FTS questions

[Michael Black]

Test it yourself:

create virtual test using fts4(context text);
insert into test values ('c:\folders\video\עברית');
select * from test where context match 'עברית';
If you want a partial match add a wildcard
select * from test where context match 'עברית*';

I don't have the codepage running so I can't test it but it sure works for 
English.  Don't see why it wouldn't work for other languages.



-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of moti lahiani
Sent: Saturday, January 12, 2013 10:56 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] FTS questions

Thanks for your reply
Why I care the language: according to the documentation:
"A term is a contiguous sequence of eligible characters, where eligible
characters are all alphanumeric characters and all characters with Unicode
codepoint values greater than or equal to 128. All other characters are
discarded when splitting a document into terms. Their only contribution is
to separate adjacent terms."
if the path include folder or file name in France or Hebrew or Arabic  and
the user what to search that word according to above the FTS will not found
it. Am I wrong?

about the backslash/slash if I have a path like this and the user search
for עברית
did the FTS will find it?
c:\folders\video\עברית

about the creation I was confuse it not temporary its virtual so ignore my
question
Thanks




On Sat, Jan 12, 2013 at 5:58 PM, Michael Black <mdblac...@yahoo.com> wrote:

> I'm not sure I understand your problem.
> Why do you care what language it is?  Aren't you just wanting to tokenize
> on
> backslash?
>
> Simple way is to replace all spaces in the path with another char (e.g.
> '_')
> then replace all backslashes with a space.
> Then you can just use the default tokenizer and make the same changes on
> any
> user queries.  So you map the user query to your storage format.
>
> Not sure why you would want to use some stem tokenizer on paths.
>
> And, of course, you're reinventing the wheel unless you have some special
> purpose in mind.
>
> http://locate32.cogit.net/
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of moti lahiani
> Sent: Saturday, January 12, 2013 4:37 AM
> To: sqlite-users@sqlite.org
> Cc: Moti LAHIANI
> Subject: [sqlite] FTS questions
>
> Hello all
>
> I'm new with sqlite3 and sql.
>
> I have data base that include path columns (file system path like c:\bla
> bla\myFiles\1.txt)
>
> On that columns I need to do
>
> 1)  search for patterns in case the user want to find a file or
> directory
>
> 2)  search for prefix path in case the user rename a file or directory
>
> the Database include about 1.5-2.5 million records and to use the "LIKE" is
> not possible because the result time.
>
> As an alternative I want to use FTS3 or FTS4 but I think I have a problems
> with what I read here: http://www.sqlite.org/fts3.html#section_1_4
>
> And here: http://www.sqlite.org/fts3.html#section_6_3
>
> I need to specify the language to FTS to use it as tokenize but the path
> can include multi languages how can I configure the FTS table to use all
> languages
>
> How can I tell to FTS to token the path only according to the character "\"
> ?
>
>
>
> More than that when creating FTS table it creates with the TEMPORARY  key
> word. My question is: do I need to create this table each time I run the
> data base   (because the temporary word) or for each connections (in case
> of multiconnections) or this is a table like all tables I declared and it
> stay in the data base even if I restart my PC
>
>
>
> Thanks a lot
> ___
> 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-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] FTS questions

[Michael Black]

I'm not sure I understand your problem.
Why do you care what language it is?  Aren't you just wanting to tokenize on
backslash?

Simple way is to replace all spaces in the path with another char (e.g. '_')
then replace all backslashes with a space.
Then you can just use the default tokenizer and make the same changes on any
user queries.  So you map the user query to your storage format.

Not sure why you would want to use some stem tokenizer on paths.

And, of course, you're reinventing the wheel unless you have some special
purpose in mind.

http://locate32.cogit.net/

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of moti lahiani
Sent: Saturday, January 12, 2013 4:37 AM
To: sqlite-users@sqlite.org
Cc: Moti LAHIANI
Subject: [sqlite] FTS questions

Hello all

I'm new with sqlite3 and sql.

I have data base that include path columns (file system path like c:\bla
bla\myFiles\1.txt)

On that columns I need to do

1)  search for patterns in case the user want to find a file or
directory

2)  search for prefix path in case the user rename a file or directory

the Database include about 1.5-2.5 million records and to use the "LIKE" is
not possible because the result time.

As an alternative I want to use FTS3 or FTS4 but I think I have a problems
with what I read here: http://www.sqlite.org/fts3.html#section_1_4

And here: http://www.sqlite.org/fts3.html#section_6_3

I need to specify the language to FTS to use it as tokenize but the path
can include multi languages how can I configure the FTS table to use all
languages

How can I tell to FTS to token the path only according to the character "\"
?



More than that when creating FTS table it creates with the TEMPORARY  key
word. My question is: do I need to create this table each time I run the
data base   (because the temporary word) or for each connections (in case
of multiconnections) or this is a table like all tables I declared and it
stay in the data base even if I restart my PC



Thanks a lot
___
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] How to decrease IO usage

[Michael Black]

Increase your cache size?  Default is 2000*page_size
http://www.sqlite.org/pragma.html#pragma_cache_size



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Bebel
Sent: Wednesday, January 09, 2013 5:00 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] How to decrease IO usage

Hi there,

Let me explain you my problem.

I'm working with a lot of sqlite base (many million), but they are very
small, 2000 entries for the bigger. In this base, I create 6 tables, but I
mostly work on just one of them.

My structure are very simple, I stored varchar and blob.

My amount of insert is not important regarding to my amount of select
action.

Now my problem is that my limitation for increase my latency is on the
amount of IO ops perform by the databases. In fact, sqlite make a lot of
random IO on my disks (many hundred) and this increase my disk latency.

I made a test on a empty database, on which I insert a value and then read
it. This two sqlite operations made more than 40 IO operation.

Then, do you know a way to optimize my usage ?

NB : Data integrity is very important on my environment.

Thanks for help, and happy new year !



--
View this message in context:
http://sqlite.1065341.n5.nabble.com/How-to-decrease-IO-usage-tp66474.html
Sent from the SQLite mailing list archive at Nabble.com.
___
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] Archive SQLite Database?

[Michael Black]

You can also just copy the entire file (best to do when app is not running).
So you'd only be down for as long as it takes to copy it.
If your database is updated frequently .backup may never finish.

Then you can archive from the copy and delete the archived rows from the
active DB in a non-interference way (i.e. small batches).

I still think you may benefit greatly from some indexes but we need more
info.

Somebody will eventually tell you to run EXPLAIN on your queries so we can
see what they are doing.
http://www.sqlite.org/eqp.html






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

Re: [sqlite] Archive SQLite Database?

[Michael Black]

Tou haven't said what "really slow" means.
Could be multiple solutions to that problem.

30min for 3-5MB sounds a bit ridiculous.

What kind of select are you doing to archive and what indexes are on the
tables?


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jimmy Martin
Sent: Tuesday, January 08, 2013 8:05 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Archive SQLite Database?

Hello,

I have recently taken over a position that uses a SQLite
database.  The database is currently 24GB and running extremely slow.
Is there a quick way to archive data and free-up some space?  Also, are
there any risks to running the "vacuum" command...such as losing data?
I would like to archive a year's worth of data.  Archiving the data via
the application is taking roughly 30min for every 3-5MB.  Unfortunately
our application cannot be down for such an extended time to Archive the
years worth of data we need.

 

SQLite Version:  3.6.23.1

 

 

Thanks,

 

Jimmy Martin

___
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] SQLite4 Performance

[Michael Black]

Thanks...progress...now we get a SIGBUS later on...

Program received signal SIGBUS, Bus error.
0x0046ce4c in treeShmalloc (pDb=0x6c9728, bAlign=0, nByte=25,
pRc=0x7fffd784) at src/lsm_tree.c:668
668 pNext->iNext = 0;

pRc looks suspiciously like it's ready to overflow

#include 
#include 
#include 
#include 
#include "sqlite4/sqlite4.h"

#define NRECORDS 5000

time_t base_seconds;
suseconds_t base_useconds;

double tic() {
  struct timeval tv;
  double f;
  gettimeofday(,NULL);
  base_seconds=tv.tv_sec;
  base_useconds=tv.tv_usec;
  f= base_seconds + base_useconds/1.0e6;
  return f;
}

// returns time in seconds since tic() was called
double toc() {
  struct timeval tv;
  gettimeofday(,NULL);
  double mark=(tv.tv_sec-base_seconds)+(tv.tv_usec-base_useconds)/1.0e6;
  return mark;
}

void checkrc(sqlite4 *db,int rc,int checkrc,int flag,char *msg,char *str) {
  if (rc != checkrc) {
fprintf(stderr,msg,str);
fprintf(stderr,"%s\n",sqlite4_errmsg(db));
if (flag) { // then fatal
  exit(1);
}
  }
}

int main(int argc, char *argv[]) {
  int rc;
  long i;
  double startTime,stopTime;
  char *sql,*errmsg=NULL;
  char *databaseName="data.db";
  sqlite4 *db;
  sqlite4_env *env=NULL;
  sqlite4_stmt *stmt1,*stmt2;
  remove(databaseName);
  rc =
sqlite4_open(env,"data.db",,SQLITE4_OPEN_READWRITE|SQLITE4_OPEN_CREATE,NU
LL);
  checkrc(db,SQLITE4_OK,rc,1,"Error opening database '%s': ",databaseName);
  //sql = "create table if not exists t_foo (key binary(16) primary key,
value binary(16))";
  sql = "create table if not exists t_foo (key binary(16) , value
binary(16))";
  rc=sqlite4_prepare(db,sql,-1,,NULL);
  checkrc(db,SQLITE4_OK,rc,1,"Error preparing statement '%s': ",sql);
  rc=sqlite4_step(stmt1);
  checkrc(db,SQLITE4_DONE,rc,1,"Error executing statement '%s': ",sql);
  rc=sqlite4_finalize(stmt1);
  checkrc(db,SQLITE4_OK,rc,1,"Error finalizing statement '%s': ",sql);
  rc=sqlite4_exec(db, "PRAGMA journal_mode=wal",NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error on WAL mode statement '%s': ",sql);
  rc=sqlite4_exec(db, "PRAGMA synchronous=OFF",NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error on synchronous mode statement '%s':
",sql);
  rc=sqlite4_exec(db, "PRAGMA cache_size=8000",NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error on synchronous mode statement '%s':
",sql);
  rc=sqlite4_exec(db, "PRAGMA page_size=4096",NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error on synchronous mode statement '%s':
",sql);
  sql="BEGIN";
  rc=sqlite4_exec(db,sql,NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error preparing statement '%s': ",sql);
  sql = "insert or replace into t_foo(key,value) values(?,?)";
  rc=sqlite4_prepare(db,sql,-1,,NULL);
  checkrc(db,SQLITE4_OK,rc,1,"Error preparing statement '%s': ",sql);
  startTime=tic();
  for(i=0; i<=NRECORDS; ++i) {
char key[16],value[16];
long number = random();
memcpy(key,,8);
memcpy([8],,8);
memcpy(value,,8);
rc=sqlite4_bind_blob(stmt2,1,key,16,SQLITE4_STATIC);
checkrc(db,SQLITE4_OK,rc,1,"Error bind1 statement '%s': ",sql);
rc=sqlite4_bind_blob(stmt2,2,value,16,SQLITE4_STATIC);
checkrc(db,SQLITE4_OK,rc,1,"Error bind2 statement '%s': ",sql);
rc=sqlite4_step(stmt2);
checkrc(db,SQLITE4_DONE,rc,1,"Error finalizing statement '%s': ",sql);
rc=sqlite4_reset(stmt2);
checkrc(db,SQLITE4_OK,rc,1,"Error resetting statement '%s': ",sql);
#if 0
if  (i>0&&(i % 100)==0) {
  sql="COMMIT";
  rc=sqlite4_exec(db,sql,NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error executing statement '%s': ",errmsg);
  sql="BEGIN";
  rc=sqlite4_exec(db,sql,NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error executing statement '%s': ",errmsg);
}
#endif
if (i>0 && (i % 10) == 0) {
  //printf("%ld,%g \n",i,10/toc());
  printf("%g \n",10/toc());
  fflush(stdout);
  tic();
}
  }
  rc=sqlite4_finalize(stmt2);
  checkrc(db,SQLITE4_OK,rc,1,"Error finalizing statement '%s': ",sql);
  rc=sqlite4_close(db);
  checkrc(db,SQLITE4_OK,rc,1,"Error closing database'%s': ",databaseName);
  stopTime=tic();
  printf("avg %.0f inserts/sec\n",NRECORDS/(stopTime-startTime));
  return 0;
}

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

Re: [sqlite] 1.1GB database - 7.8 million records

[Michael Black]

I turned journalling off in my test program and sqlite3 is running about
3000/inserts per second right now at around 6M records.  Lousy performance
compared to WAL mode.
journal=memory behaved the same way.

Journaling off might work if you don't have any indexes.

Taking the primary key off of my test program cranks up to 374,000
inserts/sec average over 50M records with journal_mode=WAL - pretty decent
throughput I'd say.

Interestingly enough sqlite4 is slower in that case (without the random
primary key)running around 80,000 inserts/sec

So sqlite4 is faster when inserting random numbers in the index but slower
when not (i.e. only the rowid index).



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns
Sent: Monday, December 31, 2012 1:59 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] 1.1GB database - 7.8 million records

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 31/12/12 10:35, Michael Black wrote:
> One transaction like you did is best.
> 
> I recently ran a test which ran pretty well with a commit every 1M
> records. Doing every 100,000 records slowed things down dramatically.

If you are creating the initial database then you can turn journalling etc
off until the database, indices etc are fully created.  This will get you
a little more speed too.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlDh7mkACgkQmOOfHg372QR/dwCfVhcMaYJIr6pTFKsL1LbaFiVJ
xk8An3lyoOv/LLmi9lWh8ZFEFJdCGfZO
=ie9C
-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] 1.1GB database - 7.8 million records

[Michael Black]

One transaction like you did is best.

I recently ran a test which ran pretty well with a commit every 1M records.
Doing every 100,000 records slowed things down dramatically.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of
joe.fis...@tanguaylab.com
Sent: Monday, December 31, 2012 12:32 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] 1.1GB database - 7.8 million records

Very impressive. With SQLite 3.7.14.1
Took 4 minutes to load a 1.5GB MySQL dump with 7.8 million records.
Count(*) takes 5 seconds. Even runs on a USB key. Wow!
Also loaded a smaller one (33MB database [30 tables/dumps] in 10 
seconds, largest file had 200,000 records).

I wrapped the 7.8 million records in one [BEGIN TRANSACTION;] [COMMIT 
TRANSACTION;] block.
Had to use VIM to edit the file.
Using the Transaction is significantly faster with a large number of 
inserts.
What's the rule of thumb on how many records per transaction?
Does it matter how many are used, is one transaction OK?

Joe Fisher
___
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] SQLite4 Performance

[Michael Black]

Do we still get to report bugs?
I checked out the fossil repository
fossil clone http://www.sqlite.org/src4 sqlite4.fossil


I tried my insert test and ran into a problem.
I'm running Redhat 5.7 gcc 4.4.4

This program dies (showing inserts/sec)
123536 
113110 
110154 
107018 
105489 
100335 
100165 
100382 
100086 
99336.9 
insert4: src/lsm_shared.c:996: lsmReadlock: Assertion
`(((u32)iShmMax-(u32)iShmMin) < (1<<30))' failed.
Aborted
It's the COMMIT that causes it.  If I take the commit out it keeps on
running but eventually gets a "Bus error".  Shm file is about 2G at that
point.


And sqlite4 does appear faster than sqlite3...same program but in sqlite3..
135052 
113865 
104801 
77650 
64325.1 
56964.1 
54297.1 
50751.4 
49402.3 
47852.7
so version 4 is >2X faster at least this far.  Version 4 was running at 76K
inserts/sec at 14.9M records when it died so it looks very promising on
speed.


Is shared mem really limited to 32 bits?  Both 3 and 4 versions below...

===SQLITE4===
#include 
#include 
#include 
#include "sqlite4/sqlite4.h"
  
time_t base_seconds;
suseconds_t base_useconds;
  
void tic() {
  struct timeval tv;
  gettimeofday(,NULL);
  base_seconds=tv.tv_sec;
  base_useconds=tv.tv_usec;
} 
  
// returns time in seconds since tic() was called
double toc() {
  struct timeval tv;
  gettimeofday(,NULL);
  double mark=(tv.tv_sec-base_seconds)+(tv.tv_usec-base_useconds)/1.0e6;
  return mark;
}   

void checkrc(sqlite4 *db,int rc,int checkrc,int flag,char *msg,char *str) {
  if (rc != checkrc) {
fprintf(stderr,msg,str);
fprintf(stderr,"%s\n",sqlite4_errmsg(db));
if (flag) { // then fatal
  exit(1);
}
  }
}

int main(int argc, char *argv[]) {
  int rc;
  long i;
  char *sql,*errmsg=NULL;
  char *databaseName="data.db";
  sqlite4 *db;
  sqlite4_env *env=NULL;
  sqlite4_stmt *stmt1,*stmt2;
  remove(databaseName);
  //rc =
sqlite4_open_v2(databaseName,,SQLITE4_OPEN_READWRITE|SQLITE4_OPEN_CREATE,
NULL);
  rc =
sqlite4_open(env,"data.db",,SQLITE4_OPEN_READWRITE|SQLITE4_OPEN_CREATE,NU
LL);
  checkrc(db,SQLITE4_OK,rc,1,"Error opening database '%s': ",databaseName);
  sql = "create table if not exists t_foo (key binary(16) primary key, value
binary(16))";
  rc=sqlite4_prepare(db,sql,-1,,NULL);
  checkrc(db,SQLITE4_OK,rc,1,"Error preparing statement '%s': ",sql);
  rc=sqlite4_step(stmt1);
  checkrc(db,SQLITE4_DONE,rc,1,"Error executing statement '%s': ",sql);
  rc=sqlite4_finalize(stmt1);
  checkrc(db,SQLITE4_OK,rc,1,"Error finalizing statement '%s': ",sql);
  rc=sqlite4_exec(db, "PRAGMA journal_mode=WAL",NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error on WAL mode statement '%s': ",sql);
  rc=sqlite4_exec(db, "PRAGMA synchronous=OFF",NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error on synchronous mode statement '%s':
",sql);
  rc=sqlite4_exec(db, "PRAGMA cache_size=8000",NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error on synchronous mode statement '%s':
",sql);
  rc=sqlite4_exec(db, "PRAGMA page_size=4096",NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error on synchronous mode statement '%s':
",sql);
  sql="BEGIN";
  rc=sqlite4_exec(db,sql,NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error preparing statement '%s': ",sql);
  sql = "insert or replace into t_foo(key,value) values(?,?)";
  rc=sqlite4_prepare(db,sql,-1,,NULL);
  checkrc(db,SQLITE4_OK,rc,1,"Error preparing statement '%s': ",sql);
  tic();
  for(i=0; i<5000; ++i) {
char key[16],value[16];
long number = random();
if (i>0 && (i % 10) == 0) {
  //printf("%ld,%g \n",i,10/toc());
  printf("%g \n",10/toc());
  fflush(stdout);
  tic();
}
#if 1 // undef to get bus error
if  (i>0&&(i % 100)==0) {
  sql="COMMIT";
  rc=sqlite4_exec(db,sql,NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error executing statement '%s': ",errmsg);
  sql="BEGIN";
  rc=sqlite4_exec(db,sql,NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error executing statement '%s': ",errmsg);
}
#endif
memcpy(key,,8);
memcpy([8],,8);
memcpy(value,,8);
rc=sqlite4_bind_blob(stmt2,1,key,16,SQLITE4_STATIC);
checkrc(db,SQLITE4_OK,rc,1,"Error bind1 statement '%s': ",sql);
rc=sqlite4_bind_blob(stmt2,2,value,16,SQLITE4_STATIC);
checkrc(db,SQLITE4_OK,rc,1,"Error bind2 statement '%s': ",sql);
rc=sqlite4_step(stmt2);
checkrc(db,SQLITE4_DONE,rc,1,"Error finalizing statement '%s': ",sql);
rc=sqlite4_reset(stmt2);
checkrc(db,SQLITE4_OK,rc,1,"Error resetting statement '%s': ",sql);
  }
  return 0;
}

==SQLITE3==
#include 
#include 
#include 
#include 
#include "sqlite3.h"

time_t base_seconds;
suseconds_t base_useconds;

void tic() {
  struct timeval tv;
  gettimeofday(,NULL);
  base_seconds=tv.tv_sec;
  base_useconds=tv.tv_usec;
}

// returns time in seconds since tic() was called
double toc() {
  struct timeval tv;
  gettimeofday(,NULL);
  double mark=(tv.tv_sec-base_seconds)+(tv.tv_usec-base_useconds)/1.0e6;
  return mark;
}

void checkrc(sqlite3 *db,int 

Re: [sqlite] Write performance question for 3.7.15

[Michael Black]

Referencing the C program I sent earlierI've found a COMMIT every 1M
records does best.  I had an extra zero on my 100,000 which gives the EKG
appearance.
I averaged 25,000 inserts/sec over 50M records with no big knees in the
performance (there is a noticeable knee on the commit though around 12M
records).  But the average performance curve is pretty smooth.
Less than that and you're flushing out the index too often which causes an
awful lot of disk thrashing it would seem.
During the 1M commit the CPU drops to a couple % and the disk I/O is pretty
constant...albeit slow

P.S. I'm using 3.7.15.1


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

Re: [sqlite] Write performance question for 3.7.15

[Michael Black]

I wrote a C program doing your thing (with random data so each key is
unique)

I see some small knees at 20M and 23M -- but nothing like what you're seeing
as long as I don't do the COMMIT.
Seems the COMMIT is what's causing the sudden slowdown.
When doing the COMMIT I see your dramatic slowdown (an order of magnitude)
at around 5M records...regardless of cache sizeso cache size isn't the
problem.
I'm guessing the COMMIT is paging out the index which starts thrashing the
disk.
Increasing the COMMIT to every 100,000 seems to help a lot.  The plot looks
almost like an EKG then with regular slowdowns.


And...when not doing the commit is it normal for memory usage to increase
like the WAL file does?


#include 
#include 
#include 
#include 
#include "sqlite3.h"
  
time_t base_seconds;
suseconds_t base_useconds;
  
void tic() {
  struct timeval tv;
  gettimeofday(,NULL);
  base_seconds=tv.tv_sec;
  base_useconds=tv.tv_usec;
} 
  
// returns time in seconds since tic() was called
double toc() {
  struct timeval tv;
  gettimeofday(,NULL);
  double mark=(tv.tv_sec-base_seconds)+(tv.tv_usec-base_useconds)/1.0e6;
  return mark;
}

void checkrc(sqlite3 *db,int rc,int checkrc,int flag,char *msg,char *str) {
  if (rc != checkrc) {
fprintf(stderr,msg,str);
fprintf(stderr,"%s\n",sqlite3_errmsg(db));
if (flag) { // then fatal
  exit(1);
}
  }
}   

int main(int argc, char *argv[]) {
  int rc;
  long i;
  char *sql,*errmsg=NULL;
  char *databaseName="data.db";
  sqlite3 *db;
  sqlite3_stmt *stmt1,*stmt2;
  remove(databaseName);
  rc =
sqlite3_open_v2(databaseName,,SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE,NU
LL);
  checkrc(db,SQLITE_OK,rc,1,"Error opening database '%s': ",databaseName);
  sql = "create table if not exists t_foo (key binary(16) primary key, value
binary(16))";
  rc=sqlite3_prepare_v2(db,sql,-1,,NULL);
  checkrc(db,SQLITE_OK,rc,1,"Error preparing statement '%s': ",sql);
  rc=sqlite3_step(stmt1);
  checkrc(db,SQLITE_DONE,rc,1,"Error executing statement '%s': ",sql);
  rc=sqlite3_step(stmt1);
  checkrc(db,SQLITE_DONE,rc,1,"Error executing statement '%s': ",sql);
  rc=sqlite3_finalize(stmt1);
  checkrc(db,SQLITE_OK,rc,1,"Error finalizing statement '%s': ",sql);
  rc=sqlite3_exec(db, "PRAGMA journal_mode=WAL",NULL,NULL,);
  checkrc(db,SQLITE_OK,rc,1,"Error on WAL mode statement '%s': ",sql);
  rc=sqlite3_exec(db, "PRAGMA synchronous=OFF",NULL,NULL,);
  checkrc(db,SQLITE_OK,rc,1,"Error on synchronous mode statement '%s':
",sql);
  rc=sqlite3_exec(db, "PRAGMA cache_size=10",NULL,NULL,);
  checkrc(db,SQLITE_OK,rc,1,"Error on cache size statement '%s': ",sql);
  sql="BEGIN";
  rc=sqlite3_exec(db,sql,NULL,NULL,);
  checkrc(db,SQLITE_OK,rc,1,"Error preparing statement '%s': ",sql);
  sql = "insert or replace into t_foo(key,value) values(?,?)";
  rc=sqlite3_prepare_v2(db,sql,-1,,NULL);
  checkrc(db,SQLITE_OK,rc,1,"Error preparing statement '%s': ",sql);
  tic();
  for(i=0; i<5000; ++i) {
char key[16],value[16];
long number = random();
if (i>0 && (i % 10) == 0) {
  printf("%ld,%g \n",i,10/toc());
  tic();
}
#if 0 // COMMIT?
if  (i>0&&(i % 1000)==0) { // try 100,000 
  sql="COMMIT";
  rc=sqlite3_exec(db,sql,NULL,NULL,);
  checkrc(db,SQLITE_OK,rc,1,"Error executing statement '%s': ",errmsg);
  sql="BEGIN";
  rc=sqlite3_exec(db,sql,NULL,NULL,);
  checkrc(db,SQLITE_OK,rc,1,"Error executing statement '%s': ",errmsg);
}
#endif
memcpy(key,,8);
memcpy([8],,8);
memcpy(value,,8);
rc=sqlite3_bind_blob(stmt2,1,key,16,SQLITE_STATIC);
checkrc(db,SQLITE_OK,rc,1,"Error bind1 statement '%s': ",sql);
rc=sqlite3_bind_blob(stmt2,2,value,16,SQLITE_STATIC);
checkrc(db,SQLITE_OK,rc,1,"Error bind2 statement '%s': ",sql);
rc=sqlite3_step(stmt2);
checkrc(db,SQLITE_DONE,rc,1,"Error finalizing statement '%s': ",sql);
rc=sqlite3_reset(stmt2);
checkrc(db,SQLITE_OK,rc,1,"Error resetting statement '%s': ",sql);
  }
  return 0;
}


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Saturday, December 29, 2012 8:19 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Write performance question for 3.7.15


On 29 Dec 2012, at 12:37pm, Stephen Chrzanowski  wrote:

> My guess would be the OS slowing things down with write caching.  The
> system will hold so much data in memory as a cache to write to the disk,
> and when the cache gets full, the OS slows down and waits on the HDD.  Try
> doing a [dd] to a few gig worth of random data and see if you get the same
> kind of slow down.

Makes sense.  A revealing of how much memory the operating system is using
for caching.  Once you hit 30M rows you exceed the amount of memory the
system is using for caching, and it has to start reading or writing disk for
every operation which is far slower.  Or it's the amount of memory that the
operating system is 

Re: [sqlite] Fwd: Write performance question for 3.7.15

[Michael Black]

Perhaps the rowid index cache gets too big?  I assume you don't have any
indexes of your own?

Does the knee change if you say, double your cache_size?

Default should be 2000;

pragma cache_size=4000;


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Frankowski
Sent: Friday, December 28, 2012 3:34 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Fwd: Write performance question for 3.7.15

I am running a benchmark of inserting 100 million (100M) items into a
table. I am seeing performance I don't understand. Graph:
http://imgur.com/hH1Jr. Can anyone explain:

1. Why does write speed (writes/second) slow down dramatically around 28M
items?
2. Are there parameters (perhaps related to table size) that would change
this write performance?

===

Create and insert statements:

create table if not exists t_foo (
  key binary(16) primary key,
  value binary(16));

insert or replace into t_foo (key, value) values (?, ?)

key and value are each 16-byte arrays.

I turn auto-commit off and commit every 1000 inserts.
I set synchronous mode to OFF and journaling mode to WAL (write-ahead log).

I am using sqlite 3.7.15 through the Xerial JDBC driver (see
https://bitbucket.org/xerial/sqlite-jdbc). I built it myself, due to a
glibc incompatibility (see
https://groups.google.com/d/msg/Xerial/F9roGuUjH6c/6RuxqmG6UK4J).

I am running on Gentoo. Output of uname -a:

Linux mymachine 3.2.1-c42.31 #1 SMP Mon Apr 30 10:55:12 CDT 2012 x86_64
Quad-Core AMD Opteron(tm) Processor 1381 AuthenticAMD GNU/Linux

It has 8G of memory.
___
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] malloc failures on ubuntu

[Michael Black]

Of course that doesn't guarantee you're executing the same code.

Only a static binary or a MD5Sum of all code in the libraries involved can
ensure that.

So the question is,  what's different?

Maybe if somebody could post a static binary for him to test that might help
narrow things down to see if it's his hardware or software setup.

I'm on RedHat but this is how to check what you're executing

ldd -v sqlite3
linux-vdso.so.1 =>  (0x7fffb7d3e000)
libreadline.so.5 => /usr/lib64/libreadline.so.5 (0x003d2ca0)
libncurses.so.5 => /usr/lib64/libncurses.so.5 (0x003d3d40)
libpthread.so.0 => /lib64/libpthread.so.0 (0x003d2ae0)
libdl.so.2 => /lib64/libdl.so.2 (0x003d2aa0)
libc.so.6 => /lib64/libc.so.6 (0x003d2a20)
/lib64/ld-linux-x86-64.so.2 (0x003d29e0)


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
Sent: Friday, December 14, 2012 7:13 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] malloc failures on ubuntu

On Fri, Dec 14, 2012 at 2:37 AM, bkk  wrote:

> Below are the steps i followed in my Ubuntu PC:
>
> 1. From the site : http://www.sqlite.org/download.html
> 2. connected to http://www.sqlite.org/cgi/src (Dallas)
> 3. clicked  [52e755943f] Leaf ( which was on the top)
> 4. clicked ZIP archive
> 5. saved the file
> 6. copied the zip file(SQLite-52e755943f87354f.zip) and extracted
> 7. in the terminal navigated to the extracted directory
> 8. Commanded for ./configure and then maketest
>

I get "0 errors out of 119415 test" when I follow the steps above on my
Ubuntu desktop.


>
> Please correct, if something am doing wrong here.
>
>
>
> --
> View this message in context:
>
http://sqlite.1065341.n5.nabble.com/malloc-failures-on-ubuntu-tp65936p66136.
html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] table backup

[Michael Black]

I don't see in the thread where you say what journal mode you're running in
or your synchronous setting.
That may explain your problem and be easily fixable.

The other thing is to compile your system with stack protection and see if
that can trap the problem.

If it's just stack corruption you should be able to trigger the error
locally much more quickly with stack protection on then in a random system.





-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of dd
Sent: Tuesday, December 11, 2012 1:00 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] table backup

Hi Roger,

  I don't have any clue. Two databases are corrupted. First one, while
inserting 20,000 records suddenly sqlite thrown disk io error at 4,000
record. No clue.

  Second database corrupted when my application crashed. But that time,
second database was not opened.

  So, I am planning to choose backup solution instead of investigating
corruption.

  I discussed this issue in my previous post.

  Any sample application for virtual table option.

Best Regards,
d


On Mon, Dec 10, 2012 at 9:09 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 09/12/12 21:44, dd wrote:
> > Sometimes, sqlite databse corrupts.
>
> That is the problem you need to fix.  If you have a system that is
> unreliable then it will also corrupt your backups.
>
> http://www.sqlite.org/lockingv3.html#how_to_corrupt
> http://www.sqlite.org/howtocorrupt.html
>
> > So, I want to take online backup of specific table. Not entire
> > database.
>
> Do you need to take a backup on every change, as part of the change or is
> it acceptable to make backups periodically and possibly lose intermediate
> versions of the data?
>
> For a periodic backup you can iterate over the table contents and output
> them in a convenient format for you, such as CSV or SQL statements.
>
> For saving all data you can use triggers to save historical values in a
> second table and then do a periodic backup.
>
> If it must be immediate then the only choice available is to use a virtual
> table and do the backup during writes/sync.
>
> This is all considerably more work than figuring out why you are getting
> corruption in the first place.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAlDGF2IACgkQmOOfHg372QTo9gCfSQQwreSvsa9lrV/wj0YC2Fvj
> LT0AmwdZSaNvVJJuic3gLYmQfn9YX6x3
> =Gx1r
> -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

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

Re: [sqlite] Subject: Re: Simple SQLite-based spreadsheet?

[Michael Black]

You're finding out that "simple" and "complete" are frequently mutually
exclusiveespecially when defined by you.
Chances are that if what you want doesn't exist there's a good reason for
it.either not practical or not useful or doable by other means already.
To make a powerful, idiot proof system is quite difficult.

By the time you get to DB functionality you will lose most users.

Do you perhaps want a web-based version?
http://www.debianhelp.co.uk/sqliteweb.htm

Generally speaking database and spreadsheet functionality are not similar
enough to combine.
Why do you think Excel and Access are two separate products?  Just like all
other office suites?
They narrow their comp ability problems by importing/exporting to each
other.

The only concept they have in common is rows/columns/tables -- and
spreadsheets didn't have tables at first (i.e. tabs).  But the spreadsheet
rows/columns is not really db rows/columns.  The spreadsheet version is
transposable for example making the row/column definition very loose.

P.S. I've changed my sqlite mail subscription to my private email now.
Found out that Microsquish Exchange doesn't put the REFERENCES tag back in
replies.  So that was breaking the topic threading. 

Michael Black


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Gilles Ganault
Sent: Sunday, December 09, 2012 5:14 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Subject: Re: Simple SQLite-based spreadsheet?

On Sun, 09 Dec 2012 00:04:40 +0100, Olaf Schmidt
<s...@online.de> wrote:
>If no such special Formatting is needed, then the
>term "DataGrid" is the more common one, since
>"real SpreadSheet functionality" is usually associated
>with the extended requirements (at individual cell-level)
>I've listed above.

Thanks for the input. Indeed, a datagrid looks more like what I had in
mind, since people using Excel just to build lists probably don't need
that much control.

OTOH, whoever writes that application could always provide two
version: Basic (datagrid) and Pro (spreadsheet).

SQLite being such a great tool, I just find it sad/odd that no one has
come up with a datagrid/spreadsheet for non-techies that saves data in
an SQLite DB. Currently, it's either Excel for most people although
it's not a DB, or Access for the few (or Libre/OpenOffice + ODBC/JDBC,
which is just as hard or harder than Access).

Thank you.

___
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