Re: [sqlite] Bad query plan selection only with "LEFT JOIN"

[Richard Hipp]

On 1/5/17, Domingo Alvarez Duarte  wrote:
> Hello !
>
> Today I found this unexpected behavior when using sqlite3 trunk:
>
> When using views with joins sqlite3 is choosing expected plans except
> for "LEFT JOIN", bellow is the snippet that shows this unexpected behavior.
>
> ===
>
> create table if not exists a(id integer primary key, val text);
> create table if not exists b(id integer primary key, a_id integer not
> null, val text);
> create view if not exists b_view as select b.*, a.* from b left join a
> on b.a_id=a.id;
> create table if not exists c(id integer primary key, b_id integer not
> null, val text);
>
> select 'bad unexpected plan';
> explain query plan select c.*, b_view.* from c left join b_view on
> c.b_id=b_view.id;

Can you rewrite your query as:

  SELECT *
   FROM c LEFT JOIN b ON c.b_id=b.id
LEFT JOIN a ON b.id=a.id;
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs

[Richard Hipp]

On 1/5/17, Richard Hipp  wrote:
> But apparently, many people are looking at the result of
> sqlite3_column_count() and if it is zero, they never bother to
> sqlite3_finalize() their PRAGMA statements.

Or maybe not.

I just did an analysis of the sqlite3_column_count() changes between
3.15.2 and 3.16.0.  All changes involve PRAGMAs that used to return 0
but now return a positive number.

So if applications were using a zero sqlite3_column_count() return to
indicate that the pragma returned no rows, that still works.

In the cases where sqlite3_column_count() used to return 0 but now
returns positive, that would induce the application to call
sqlite3_step() on the pragma, which would then return SQLITE_DONE.

Everything should still work.

So now I don't really understand what is going wrong.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs

[Richard Hipp]

On 1/5/17, kk  wrote:
> All,
> Many thanks for all the replies. It seems that I had now need to run
> SQLite3Finalize against the PRAGMA stmts,

You should *always* run sqlite3_finalize() on every statement, before
you call sqlite3_close().  No exceptions.  Pragmas are no different
from any other statement in this respect.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs

[kk]

On 05/01/2017 23:35, Richard Hipp wrote:

On 1/5/17, Kyle  wrote:

Dear all,
Under version 3.16.1, SQLite3Close is returning SQLITE_BUSY when I open
a database, execute some PRAGMAS, (run some stmts) then attempt the
close. This did not occur under version 3.15.2, nor does it occur under
the new version if I omit the PRAGMAs.


Probably you are not talking to SQLite directly but are using some
wrapper software.  What is your wrapper software?


All,
Many thanks for all the replies. It seems that I had now need to run 
SQLite3Finalize against the PRAGMA stmts, and including this now 
corrects the issue.
Whilst the behaviour of PRAGMAs may change for release to release, IMO, 
it would be useful if this was documented in the release notes, since in 
this instance we see a behaviour change after upgrade.


BTW, I am using running the most excellent SQLiteForExcel wrapper to 
access SQLite from Excel.


Many thanks again,
K


Furthermore the PRAGMAs now seem to return a resultset whereas
previously they did not? Is this expected (or documented)?
Please advise,
Thanks,
K
--
PRAGMA encoding="UTF-8"
PRAGMA cache_size="-2" (hopefully a reasonable attempt to tell
SQLite that memory is more plentiful than default would suggest).

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






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

Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs

[Richard Hipp]

On 1/5/17, Jens Alfke  wrote:
>
> That would explain the SQLITE_BUSY error — if the pragma returns a non-empty
> list of rows, and you’re not reading the rows or resetting the statement,
> the statement stays busy and will block closing the database.
>

There has been a change in behavior of the sqlite3_column_count()
interface.  The behavior change was caused by the new pragma
table-valued functions.  For columns that return no rows,
sqlite3_column_count() is sometimes now returning a different number
of columns than it used to.  This should be of no consequence, since
zero rows times N columns is still zero regardless of the value of N.

If you check the documentation, it says "This routine returns 0 if
pStmt is an SQL statement that does not return data (for example an
UPDATE)."  Th3 sqlite3_column_count() interface is intended to tell
you how many columns are in the result set.  It was never intended to
tell you whether or not there is a result set.  I put in that sentence
about "returns 0" many many years ago.  I had completely forgotten
about it.  But apparently, many people are looking at the result of
sqlite3_column_count() and if it is zero, they never bother to
sqlite3_finalize() their PRAGMA statements.  Or something.  In other
words, they are using sqlite3_column_count() as an indicator about
whether or not the statement has content to return.

The idea sqlite3_column_count() could be used to determine if the
statement returns anything has never been true.  "SELECT * FROM table
WHERE 0" never returns any rows, but it will still give a non-zero
column count.  The column count is determined when the SQL is compiled
into bytecode, but we often do not know if the number of rows will be
zero until after the bytecode starts to execute.

We are trying to get the 3.16.2 patch release out the door to fix the
serious blunder described by
https://www.sqlite.org/src/info/30027b613b4 but now I'm thinking we
need to go back and make sure sqlite3_column_count() always returns
*exactly* the same value as it did in 3.15.2, regardless of how
inconsistent that value might have been, so as not to break
applications that were using sqlite3_column_count() in unintended
ways.

That means delaying 3.16.2 until next week sometime.

Maybe I should rollback the website to 3.15.2 temporarily until we can
get 3.16.2 ready...

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 Tutorial error

[Ken Wagner]

David,

Yes. That would be a big assist. I am new to using SQLite3 and found the 
GLOB function erratic in practice -- not on SQLite3 but on other web 
sites using SQLite. They yielded completely opposite results.


Second the motion.

Ken


On 01/05/2017 05:23 PM, dandl wrote:

From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin


They’re probably using the external function interface to override the internal 
globbing function.  And by the look of the results at least one of the 
programmers involved thinks that GLOB and REGEX do the same thing.

I think you're right. One of the contributing problems is that the behaviour of 
GLOB is not defined in the documentation. Here is all it says:

"The GLOB operator is similar to LIKE but uses the Unix file globbing syntax for its 
wildcards. Also, GLOB is case sensitive, unlike LIKE. Both GLOB and LIKE may be preceded 
by the NOT keyword to invert the sense of the test. The infix GLOB operator is 
implemented by calling the function glob(Y,X) and can be modified by overriding that 
function."

Unix globbing for Linux is defined here: 
http://man7.org/linux/man-pages/man7/glob.7.html. AFAICT Sqlite does not 
implement this behaviour.

Perhaps some accurate documentation for GLOB in Sqlite would help to clarify 
things?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org





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


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

Re: [sqlite] SQLite3 Tutorial error

[Ken Wagner]

Danap,

I thought so, too. But it is not the case.

I am cross-checking with the Unix/SQLite results, using Unix/SQLite as 
the base reference.


Ken


On 01/05/2017 01:30 PM, dmp wrote:

Message: 21
Date: Wed, 4 Jan 2017 22:10:59 -0600
From: Ken Wagner 
To: SQLite mailing list 
Subject: Re: [sqlite] SQLite3 Tutorial error
Yes, I am beginning to understand that. SQLite3 is its own GLOB standard.
I will abide by that.
It is just very confusing when 5 other apps using SQLite as their DB
engine all report the opposite.
  The SQLite versions they use are 3.9.2, 3.10.1, 3.11.0 and 3.13.0.
Example: the SQLite Manager in FireFox 50 uses SQLite 3.13.0 and
faithfully removes the names with digits in them.

I tried the example from R. Smith and got the exact same results
in my own SQLite Interface, Ajqvue, using the SQLITE JDBC.

I don't know what those other apps may be doing, but they should
just pass the query created by the user "DIRECTLY" to the SQLite DB
engine without any modification. Likewise the result should also
not be mucked with before presentation.

danap.

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


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

Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs

[Jens Alfke]

> On Jan 5, 2017, at 3:11 PM, Kyle  wrote:
> 
> Furthermore the PRAGMAs now seem to return a resultset whereas previously 
> they did not? Is this expected (or documented)?

That would explain the SQLITE_BUSY error — if the pragma returns a non-empty 
list of rows, and you’re not reading the rows or resetting the statement, the 
statement stays busy and will block closing the database.

For this reason, if you’re running a query it’s a good idea to reset the 
statement afterwards, even if you weren’t expecting it to return any rows.

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

Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs

[Simon Slavin]

On 5 Jan 2017, at 11:30pm, Kyle  wrote:

> no, the database is :memory:

You should not be getting SQLITE_BUSY when using a memory database.

So far my guess is the same as DRH’s.  Can you tell us how you are executing 
your SQLite functions ?  Are you using the C interface or a precompiled library 
?

You can also download the SQLite command-line tool and try executing the same 
commands with that.  If you get the same results with that they will be easier 
to investigate.

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

Re: [sqlite] SQLite3 Tutorial error

[Jens Alfke]

> On Jan 5, 2017, at 3:23 PM, dandl  wrote:
> 
> Perhaps some accurate documentation for GLOB in Sqlite would help to clarify 
> things?

+1. I for one was unaware that glob understands the “[…]” syntax for character 
classes (both in SQLite and in the Unix glob(3) function), despite having used 
SQLite for 12 years and Unix for several decades.

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

Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs

[Richard Hipp]

On 1/5/17, Kyle  wrote:
> Dear all,
> Under version 3.16.1, SQLite3Close is returning SQLITE_BUSY when I open
> a database, execute some PRAGMAS, (run some stmts) then attempt the
> close. This did not occur under version 3.15.2, nor does it occur under
> the new version if I omit the PRAGMAs.

Probably you are not talking to SQLite directly but are using some
wrapper software.  What is your wrapper software?


> Furthermore the PRAGMAs now seem to return a resultset whereas
> previously they did not? Is this expected (or documented)?
> Please advise,
> Thanks,
> K
> --
> PRAGMA encoding="UTF-8"
> PRAGMA cache_size="-2" (hopefully a reasonable attempt to tell
> SQLite that memory is more plentiful than default would suggest).
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs

[Kyle]

On 05/01/2017 23:14, Simon Slavin wrote:


On 5 Jan 2017, at 11:11pm, Kyle  wrote:


Under version 3.16.1, SQLite3Close is returning SQLITE_BUSY when I open a 
database, execute some PRAGMAS, (run some stmts) then attempt the close. This 
did not occur under version 3.15.2, nor does it occur under the new version if 
I omit the PRAGMAs.


Do other computers or processes have that database open at the same time as 
you’re doing this ?


Simon,
no, the database is :memory: but I think the same happens with on disk 
database. It is a change in behaviour with 3.16.1 vs 3.15.2.



Furthermore the PRAGMAs now seem to return a resultset whereas previously they 
did not? Is this expected (or documented)?


Some PRAGMAs are meant to return results.  Others are not.
Can you tell us one (or more) of the PRAGMAs which do this ?


Pls refer my original mail.


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



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

Re: [sqlite] SQLite3 Tutorial error

[dandl]

From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin

>>>They’re probably using the external function interface to override the 
>>>internal globbing function.  And by the look of the results at least one of 
>>>the programmers involved thinks that GLOB and REGEX do the same thing.

I think you're right. One of the contributing problems is that the behaviour of 
GLOB is not defined in the documentation. Here is all it says:

"The GLOB operator is similar to LIKE but uses the Unix file globbing syntax 
for its wildcards. Also, GLOB is case sensitive, unlike LIKE. Both GLOB and 
LIKE may be preceded by the NOT keyword to invert the sense of the test. The 
infix GLOB operator is implemented by calling the function glob(Y,X) and can be 
modified by overriding that function."

Unix globbing for Linux is defined here: 
http://man7.org/linux/man-pages/man7/glob.7.html. AFAICT Sqlite does not 
implement this behaviour.

Perhaps some accurate documentation for GLOB in Sqlite would help to clarify 
things?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org





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

Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs

[Simon Slavin]

On 5 Jan 2017, at 11:11pm, Kyle  wrote:

> Under version 3.16.1, SQLite3Close is returning SQLITE_BUSY when I open a 
> database, execute some PRAGMAS, (run some stmts) then attempt the close. This 
> did not occur under version 3.15.2, nor does it occur under the new version 
> if I omit the PRAGMAs.

Do other computers or processes have that database open at the same time as 
you’re doing this ?

> Furthermore the PRAGMAs now seem to return a resultset whereas previously 
> they did not? Is this expected (or documented)?

Some PRAGMAs are meant to return results.  Others are not.
Can you tell us one (or more) of the PRAGMAs which do this ?

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

[sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs

[Kyle]

Dear all,
Under version 3.16.1, SQLite3Close is returning SQLITE_BUSY when I open 
a database, execute some PRAGMAS, (run some stmts) then attempt the 
close. This did not occur under version 3.15.2, nor does it occur under 
the new version if I omit the PRAGMAs.
Furthermore the PRAGMAs now seem to return a resultset whereas 
previously they did not? Is this expected (or documented)?

Please advise,
Thanks,
K
--
PRAGMA encoding="UTF-8"
PRAGMA cache_size="-2" (hopefully a reasonable attempt to tell 
SQLite that memory is more plentiful than default would suggest).


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

Re: [sqlite] Quest for "scratch table" implementation in SQLite.

[Simon Slavin]

On 5 Jan 2017, at 7:26pm, Smith, Randall  wrote:

> I'm still trying to figure out a graceful way to implement a "scratch" 
> database table that has the following properties:
> 
>o Can be created on demand while a database connection is open.
>o Can include references to other material in the database to ensure 
> integrity.
>o Will be automatically deleted when the database is closed.

It’s not possible to combine the second and third requirements.  SQLite is 
designed as a multi-process multi-user database.  It would not be possible for 
one connection to figure out whether your temporary database should exist or 
not.

> TEMPORARY tables can't do the second bullet, as I understand it.

Correct.

I suggest that you just use a conventional table in the same database, creating 
it and deleting it when your program thinks it appropriate.

[later]

Yeah, what Donald Griggs wrote.

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

Re: [sqlite] SQLite3 Tutorial error

[Simon Slavin]

On 5 Jan 2017, at 7:30pm, dmp  wrote:

> I don't know what those other apps may be doing, but they should
> just pass the query created by the user "DIRECTLY" to the SQLite DB
> engine without any modification. Likewise the result should also
> not be mucked with before presentation.

They’re probably using the external function interface to override the internal 
globbing function.  And by the look of the results at least one of the 
programmers involved thinks that GLOB and REGEX do the same thing.

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

Re: [sqlite] Quest for "scratch table" implementation in SQLite.

[Donald Griggs]

On Thu, Jan 5, 2017 at 2:26 PM, Smith, Randall 
wrote:

> "I'm still trying to figure out a graceful way..."


I may well just be missing something important, but would the following not
be simple, unclunky, and not highly error-prone?

1) Before your program closes:
DROP TABLE scratchy-one;
DROP TABLE scratchy-two;   -- etc.

2) When your program begins:
 DROP TABLE IF EXISTS scratchy-one;
 DROP TABLE IF EXISTS scratchy-two;   -- etc.  Just in case your
program was previously interrupted and didn't drop them.

And 1) is even optional!  (at least for debugging)

Also -- do the "big name" databases handle this much better, and if so, is
that feature used widely?   (I know that, for example, Postgres disallows
foreign keys on temporary tables for the same reasons sqlite does.)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bad query plan selection only with "LEFT JOIN"

[Domingo Alvarez Duarte]

Hello !

Today I found this unexpected behavior when using sqlite3 trunk:

When using views with joins sqlite3 is choosing expected plans except 
for "LEFT JOIN", bellow is the snippet that shows this unexpected behavior.


===

create table if not exists a(id integer primary key, val text);
create table if not exists b(id integer primary key, a_id integer not 
null, val text);
create view if not exists b_view as select b.*, a.* from b left join a 
on b.a_id=a.id;
create table if not exists c(id integer primary key, b_id integer not 
null, val text);


select 'good expected plan';
explain query plan select c.*, b_view.* from c, b_view where 
c.b_id=b_view.id;


select 'good expected plan';
explain query plan select c.*, b_view.* from c join b_view on 
c.b_id=b_view.id;


select 'bad unexpected plan';
explain query plan select c.*, b_view.* from c left join b_view on 
c.b_id=b_view.id;


===

Output of sqlite3 < test-sqlte-bad-plan.sql :

===

good expected plan
0|0|0|SCAN TABLE c
0|1|1|SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE a USING INTEGER PRIMARY KEY (rowid=?)
good expected plan
0|0|0|SCAN TABLE c
0|1|1|SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE a USING INTEGER PRIMARY KEY (rowid=?)
bad unexpected plan
1|0|0|SCAN TABLE b
1|1|1|SEARCH TABLE a USING INTEGER PRIMARY KEY (rowid=?)
0|0|0|SCAN TABLE c
0|1|1|SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (id=?)

===

Cheers !

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

Re: [sqlite] Quest for "scratch table" implementation in SQLite.

[David Raymond]

The usual caveats apply of course
-Temporary tables and triggers are only visible by your own connection, so if 
anyone else came in and made updates then everything would get thrown out of 
synch and become dangerous. If it's all within a single transaction then it 
should be ok.

-Since triggers are always for each row, then there would be no such thing as a 
deferred "temporary fake reference"

-Umm, other limitations that my melted end-of-the-day brain can't think of 
right at the moment.



-Original Message-
From: David Raymond 
Sent: Thursday, January 05, 2017 3:19 PM
To: 'SQLite mailing list'
Subject: RE: Quest for "scratch table" implementation in SQLite.

Probably not the most elegant solution, but with enough triggers you can 
simulate close approximations to foreign keys.

create table permTable (pk integer primary key, t text);

create temp table tempTable (pk int /*references permTable on update cascade on 
delete set null*/);

create temp trigger trg_insert_check
before insert on tempTable
when new.pk is not null and not exists (select 1 from permTable where pk = 
new.pk)
begin select raise(abort, 'Key constraint 1'); end;

create temp trigger trg_update_check
before update of pk on tempTable
when new.pk is not null and not exists (select 1 from permTable where pk = 
new.pk)
begin select raise(abort, 'Key constraint 2'); end;

create temp trigger trg_update_cascade
after update of pk on permTable
begin update tempTable set pk = new.pk where pk = old.pk; end;

create temp trigger trg_delete_null
after delete on permTable
begin update tempTable set pk = null where pk = old.pk; end;


And to test it:
insert into permTable values (1, 'one'), (2, 'two');
insert into tempTable values (3);
insert into tempTable values (2);
select * from tempTable;
update tempTable set pk = 3 where pk = 2;
update permTable set pk = 3 where pk = 2;
select * from tempTable;
delete from permTable where pk = 3;
select * from tempTable;



sqlite> insert into permTable values (1, 'one'), (2, 'two');

sqlite> insert into tempTable values (3);
Error: Key constraint 1

sqlite> insert into tempTable values (2);

sqlite> select * from tempTable;
pk
2

sqlite> update tempTable set pk = 3 where pk = 2;
Error: Key constraint 2

sqlite> update permTable set pk = 3 where pk = 2;

sqlite> select * from tempTable;
pk
3
sqlite> delete from permTable where pk = 3;

sqlite> select * from tempTable;
pk
NULL


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Smith, Randall
Sent: Thursday, January 05, 2017 2:26 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Quest for "scratch table" implementation in SQLite.

I'm still trying to figure out a graceful way to implement a "scratch" database 
table that has the following properties:

o Can be created on demand while a database connection is open.
o Can include references to other material in the database to ensure 
integrity.
o Will be automatically deleted when the database is closed.

TEMPORARY tables can't do the second bullet, as I understand it.

Right now I have application code that manages such tables, but it seems clunky 
and error-prone.  Am I missing any SQLite feature that would let me do this 
more easily?

Thanks for any suggestions.

Randall.




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

Re: [sqlite] Quest for "scratch table" implementation in SQLite.

[David Raymond]

Probably not the most elegant solution, but with enough triggers you can 
simulate close approximations to foreign keys.

create table permTable (pk integer primary key, t text);

create temp table tempTable (pk int /*references permTable on update cascade on 
delete set null*/);

create temp trigger trg_insert_check
before insert on tempTable
when new.pk is not null and not exists (select 1 from permTable where pk = 
new.pk)
begin select raise(abort, 'Key constraint 1'); end;

create temp trigger trg_update_check
before update of pk on tempTable
when new.pk is not null and not exists (select 1 from permTable where pk = 
new.pk)
begin select raise(abort, 'Key constraint 2'); end;

create temp trigger trg_update_cascade
after update of pk on permTable
begin update tempTable set pk = new.pk where pk = old.pk; end;

create temp trigger trg_delete_null
after delete on permTable
begin update tempTable set pk = null where pk = old.pk; end;


And to test it:
insert into permTable values (1, 'one'), (2, 'two');
insert into tempTable values (3);
insert into tempTable values (2);
select * from tempTable;
update tempTable set pk = 3 where pk = 2;
update permTable set pk = 3 where pk = 2;
select * from tempTable;
delete from permTable where pk = 3;
select * from tempTable;



sqlite> insert into permTable values (1, 'one'), (2, 'two');

sqlite> insert into tempTable values (3);
Error: Key constraint 1

sqlite> insert into tempTable values (2);

sqlite> select * from tempTable;
pk
2

sqlite> update tempTable set pk = 3 where pk = 2;
Error: Key constraint 2

sqlite> update permTable set pk = 3 where pk = 2;

sqlite> select * from tempTable;
pk
3
sqlite> delete from permTable where pk = 3;

sqlite> select * from tempTable;
pk
NULL


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Smith, Randall
Sent: Thursday, January 05, 2017 2:26 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Quest for "scratch table" implementation in SQLite.

I'm still trying to figure out a graceful way to implement a "scratch" database 
table that has the following properties:

o Can be created on demand while a database connection is open.
o Can include references to other material in the database to ensure 
integrity.
o Will be automatically deleted when the database is closed.

TEMPORARY tables can't do the second bullet, as I understand it.

Right now I have application code that manages such tables, but it seems clunky 
and error-prone.  Am I missing any SQLite feature that would let me do this 
more easily?

Thanks for any suggestions.

Randall.




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

Re: [sqlite] new user seeking help

[Chen, Hui]

Well, my question is how do I get connections open with [STAThread] attribute 
on Main method.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Kevin Benson
Sent: January-05-17 2:49 PM
To: SQLite mailing list 
Subject: Re: [sqlite] new user seeking help

 On Thu, Jan 5, 2017 at 1:26 PM, Chen, Hui  wrote:

> Hi there,
>
> I just recently started integrating System.Data.SQLite.dll into my 
> project, I started with a very simple winform application.
>
> I have a very simple method to create a connection, txtSqliteFile is 
> textbox for db file location.
> SQLiteConnection GetConnection()
> {
> string connectionString = "Data Source=" + txtSqliteFile.Text + 
> ";Version=3;datetimeformat=CurrentCulture";
> SQLiteConnection conn = new SQLiteConnection(connectionString);
> return conn;
> }
>
> Then in a button handler I have these to open a connection.
>
> SQLiteConnection conn = GetConnection(); conn.Open();
>
> whenever Open method is called, Following exception thrown Attempted 
> to read or write protected memory. This is often an indication that 
> other memory is corrupt.
>
> If I remove [STAThread] attribute from Main, it opens connections 
> without problem, but my OpenFileDialgue blows up, I have to have 
> [STAThread] on Main method.
> SQLite documentation says that I can change the threading model, but 
> doesn't say how this can be achieved in C#. all examples are in C/C++
>
> Can anyone give me a hint?
>

https://richnewman.wordpress.com/2007/04/08/top-level-exception-handling-in-windows-forms-applications-part-1/

--
   --
  --
 --Ô¿Ô--
K e V i N


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

Re: [sqlite] new user seeking help

[Kevin Benson]

 On Thu, Jan 5, 2017 at 1:26 PM, Chen, Hui  wrote:

> Hi there,
>
> I just recently started integrating System.Data.SQLite.dll into my
> project, I started with a very simple winform application.
>
> I have a very simple method to create a connection, txtSqliteFile is
> textbox for db file location.
> SQLiteConnection GetConnection()
> {
> string connectionString = "Data Source=" + txtSqliteFile.Text +
> ";Version=3;datetimeformat=CurrentCulture";
> SQLiteConnection conn = new SQLiteConnection(connectionString);
> return conn;
> }
>
> Then in a button handler I have these to open a connection.
>
> SQLiteConnection conn = GetConnection();
> conn.Open();
>
> whenever Open method is called, Following exception thrown
> Attempted to read or write protected memory. This is often an indication
> that other memory is corrupt.
>
> If I remove [STAThread] attribute from Main, it opens connections without
> problem, but my OpenFileDialgue blows up, I have to have [STAThread] on
> Main method.
> SQLite documentation says that I can change the threading model, but
> doesn't say how this can be achieved in C#. all examples are in C/C++
>
> Can anyone give me a hint?
>

https://richnewman.wordpress.com/2007/04/08/top-level-exception-handling-in-windows-forms-applications-part-1/

--
   --
  --
 --Ô¿Ô--
K e V i N


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

Re: [sqlite] SQLite3 Tutorial error

[dmp]

> Message: 21
> Date: Wed, 4 Jan 2017 22:10:59 -0600
> From: Ken Wagner 
> To: SQLite mailing list 
> Subject: Re: [sqlite] SQLite3 Tutorial error

> Yes, I am beginning to understand that. SQLite3 is its own GLOB standard.

> I will abide by that.

> It is just very confusing when 5 other apps using SQLite as their DB
> engine all report the opposite.
>  The SQLite versions they use are 3.9.2, 3.10.1, 3.11.0 and 3.13.0.

> Example: the SQLite Manager in FireFox 50 uses SQLite 3.13.0 and
> faithfully removes the names with digits in them.

I tried the example from R. Smith and got the exact same results
in my own SQLite Interface, Ajqvue, using the SQLITE JDBC.

I don't know what those other apps may be doing, but they should
just pass the query created by the user "DIRECTLY" to the SQLite DB
engine without any modification. Likewise the result should also
not be mucked with before presentation.

danap.

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

[sqlite] Quest for "scratch table" implementation in SQLite.

[Smith, Randall]

I'm still trying to figure out a graceful way to implement a "scratch" database 
table that has the following properties:

o Can be created on demand while a database connection is open.
o Can include references to other material in the database to ensure 
integrity.
o Will be automatically deleted when the database is closed.

TEMPORARY tables can't do the second bullet, as I understand it.

Right now I have application code that manages such tables, but it seems clunky 
and error-prone.  Am I missing any SQLite feature that would let me do this 
more easily?

Thanks for any suggestions.

Randall.




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

Re: [sqlite] Commit ad601c7962 degrade performance in a client program

[Don V Nielsen]

I loved this remark posted in the bugzilla chain:   "(... Fossil?
where do people find these version control systems?)"

On Thu, Jan 5, 2017 at 2:09 AM, Richard Hipp  wrote:
> On 1/3/17, Jianxun Zhang  wrote:
>> I am working in Yocto project. We have a fake-root program “pseudo” that
>> uses sqlite3 at a step when building Yocto images. We found a 2% increase of
>> the whole build time, and my bisecting shows the ad601c7962 in sqlite3 is
>> the root cause.
>
> That change was backed out by
> https://www.sqlite.org/src/timeline?c=9675518b33e8d407 and so your
> code should return to its old speed, or be faster.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance degradation of in-memory database with high row count and overall design discussion of my project

[Dan Kennedy]

On 01/06/2017 12:56 AM, Simone Mosciatti wrote:

Hi all,

I am writing a small Redis module that embeds SQLite [1]

I am facing some performance issues and I wonder if the behaviour I am 
seeing is expected or if I am doing something completely wrong.



However I am finding some issues on the performance of the in memory 
database, and I see the insert performance decrease, quite fast, as 
more and more tuple are added. I run some test and I would like to 
understand if this behaviour is expected.


I collect the result of my benchmark on a github issues [2] where you 
can see that the performance decrease in a more than linear way from 
roughly 24.000 inserts per second to stabilize at roughly 4.000 
inserts per second.


The actual number of inserts per second is pretty pointless, it 
depends on the hardware, on the resource of the machine, etc... what 
leaves me wonder is the fact that I was expecting a logarithmic 
decrease in performance and not a (at least) linear decrease.


Can somebody confirm that this is normal?


Can you describe the performance test you are running?

If I create a table in an in-memory database with:

  CREATE TABLE test (a INT, b INT, c INT);

Then run this:

  INSERT INTO test VALUES(random(), random(), random())

10,000,000 times, I get a fairly stable 330,000 inserts per second or 
so. Tcl code to do this below. What is your test doing differently?


Dan.


## START TCL CODE
   package require sqlite3

   sqlite3 db :memory:
   db eval { CREATE TABLE test (a INT, b INT, c INT); }

   proc insert_n_rows {n} {
 for {set i 0} {$i<$n} {incr i} {
   db eval { INSERT INTO test VALUES(random(), random(), random()) }
 }
   }

   set nStep 10

   for {set i 0} {$i < 100} {incr i} {
 set us [lindex [time { insert_n_rows $nStep }] 0]
 puts "[expr $i*$nStep] [format %.2f [expr (100.0 * $nStep) / 
$us]]/sec"

   }
## END TCL CODE

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

[sqlite] new user seeking help

[Chen, Hui]

Hi there,

I just recently started integrating System.Data.SQLite.dll into my project, I 
started with a very simple winform application.

I have a very simple method to create a connection, txtSqliteFile is textbox 
for db file location.
SQLiteConnection GetConnection()
{
string connectionString = "Data Source=" + txtSqliteFile.Text + 
";Version=3;datetimeformat=CurrentCulture";
SQLiteConnection conn = new SQLiteConnection(connectionString);
return conn;
}

Then in a button handler I have these to open a connection.

SQLiteConnection conn = GetConnection();
conn.Open();

whenever Open method is called, Following exception thrown
Attempted to read or write protected memory. This is often an indication that 
other memory is corrupt.

If I remove [STAThread] attribute from Main, it opens connections without 
problem, but my OpenFileDialgue blows up, I have to have [STAThread] on Main 
method.
SQLite documentation says that I can change the threading model, but doesn't 
say how this can be achieved in C#. all examples are in C/C++

Can anyone give me a hint?

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

[sqlite] Performance degradation of in-memory database with high row count and overall design discussion of my project

[Simone Mosciatti]

Hi all,

I am writing a small Redis module that embeds SQLite [1]

I am facing some performance issues and I wonder if the behaviour I am 
seeing is expected or if I am doing something completely wrong.


I would provide some context on what I am doing, hoping that you may see 
some flaw in my reasoning and point me in a better direction, I am sorry 
for the long email though.


The main problem I am trying to solve is that SQLite when writing on 
disk is slow; this completely expected, I am using a spinning disk and 
we need to hit the disk for every commit (at least with the default 
PRAGMA setting).


However I am trying to figure out how to make it faster.

The very first thing that comes to mind is to avoid doing a lot of 
transaction, I could store in some buffer some SQL statement and I could 
execute all of them in one single transaction.


This is perfect from the performance point of view, but two problem arise:

1) I need to differentiate between SELECT operation and 
DELETE/INSERT/UPDATE operations, I could manage this part.


2) I need some way to indicate if an error occurs during a write 
operation, which is a problem I am not able to solve in a reasonable 
bound of complexity.



The second option I am considering is to let Redis takes care of the 
persistence. Use SQLite as in-memory and periodically write a snapshot 
on the RDB file (the RDB file is a snapshot of the whole internal state 
of Redis).


This approach would works fine because I would be fast enough to COMMIT 
every operation, so I could provide immediate feedback in the case of an 
error, but at the same time I could provide enough throughput and the 
data will be written on disk "reasonably" often.


However I am finding some issues on the performance of the in memory 
database, and I see the insert performance decrease, quite fast, as more 
and more tuple are added. I run some test and I would like to understand 
if this behaviour is expected.


I collect the result of my benchmark on a github issues [2] where you 
can see that the performance decrease in a more than linear way from 
roughly 24.000 inserts per second to stabilize at roughly 4.000 inserts 
per second.


The actual number of inserts per second is pretty pointless, it depends 
on the hardware, on the resource of the machine, etc... what leaves me 
wonder is the fact that I was expecting a logarithmic decrease in 
performance and not a (at least) linear decrease.


Can somebody confirm that this is normal?

Given the background I provide, could you suggest a better way to 
achieve reasonable performance and reasonable data safeness?



[1]: https://github.com/RedBeardLab/rediSQL

[2]: https://github.com/RedBeardLab/rediSQL/issues/13#issuecomment-270449852

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

Re: [sqlite] date-2.2c tests fail for sqlite-3.16.1 on Fedora / i686

[Don V Nielsen]

Graham, you may be on to something: "I don't know what the tests are
doing, but could it be connected with the fact that a leap-second was
added as we changed from 2016 to 2017". I noticed a whole bunch of
rspec tests (my projects completely unrelated to sqlite) failing with
microsecond differences. My issue is likely the result of the leap_sec
shift. Thanks!

On Thu, Jan 5, 2017 at 4:08 AM, Graham Holden  wrote:
> I don't know what the tests are doing, but could it be connected with the 
> fact that a leap-second was added as we changed from 2016 to 2017 and one of 
> expected/got is taking this into account and the other isn't?
> Graham
>  Original message From: Richard Hipp  Date: 
> 05/01/2017  08:12  (GMT+00:00) To: SQLite mailing list 
>  Subject: Re: [sqlite] date-2.2c tests 
> fail for sqlite-3.16.1 on Fedora / i686
> On 1/4/17, Jakub Dorňák  wrote:
>> Example output:
>>
>> ...
>> ! date-2.2c-1 expected: [06:28:00.001]
>> ! date-2.2c-1 got:  [06:28:00.000]
>> ! date-2.2c-4 expected: [06:28:00.004]
>> ! date-2.2c-4 got:  [06:28:00.003]
>> ! date-2.2c-7 expected: [06:28:00.007]
>> ! date-2.2c-7 got:  [06:28:00.006]
>> ! date-2.2c-8 expected: [06:28:00.008]
>> ! date-2.2c-8 got:  [06:28:00.007]
>> ...
>
> This is probably a function of the underlying floating-point hardware.
> What CPU is this running on?
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Need help with System.Data.SQLite.SQLiteDataReader and NUMERIC(5, 5) column

[Burtsev , Dmitriy]

Hello

I got this error:
Exception calling "WriteToServer" with "1" argument(s): "The given value of 
type SqlDecimal from the data source cannot be converted to type decimal of the 
specified target column."

How to reproduce.

1.   On SQLite execute this statements.
CREATE TABLE FromNum (
id INT,
Num NUMERIC(5,5) NULL);
INSERT INTO FromNum (id, Num) VALUES (1, .0);

2.  On MS SQL Server 2014 (SP2) execute this statement


CREATE TABLE dbo.ToNum (
id INT NOT NULL ,
Num NUMERIC(5,5) NULL);



I can't copy my PowerShell script in this e-mail. Got error "The message's 
content type was not explicitly allowed". I will try pseudocode.



Add-Type -Path '\\SERVER01\SQL\SQLite\netFx46\System.Data.SQLite.dll'

Create System.Data.SQLite.SQLiteConnection

Connection CreateCommand()

CommandText = "SELECT * FROM FromNum"

System.Data.SQLite.SQLiteDataReader = Command.ExecuteReader()

Create System.Data.SqlClient.SqlConnection
Create System.Data.SqlClient.SqlBulkCopy with 
[System.Data.SqlClient.SqlBulkCopyOptions]::TableLock
bulkCopy.BatchSize = 5000
bulkCopy.DestinationTableName = "ToNum"
bulkcopy.EnableStreaming = true

bulkCopy.WriteToServer(SQLiteDataReader) <-- Got error here

Note: I did run it with System.Data.SqlClient.SqlDataReader as a source. It 
works with NUMERIC(5,5) column.
I did run it with System.Data.SQLite.SQLiteDataReader but without NUMERIC(5,5) 
column. It works.
The problem appears only when I run SQLiteDataReader as a sourse and table have 
NUMERIC(5,5) column.

Powershell version 5, 64 bit
System.Data.SQLite.dll version 1.0.103.0  64 bit
.NET Framework 4.6.1

Thank you
Dmitriy Burtsev

This message, and any of its attachments, is for the intended recipient(s) 
only, and it may contain information that is privileged, confidential, and/or 
proprietary and subject to important terms and conditions available at 
http://www.stifel.com/disclosures/emaildisclaimers/. If you are not the 
intended recipient, please delete this message and immediately notify the 
sender. No confidentiality, privilege, or property rights are waived or lost by 
any errors in transmission.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 Tutorial error

[Ken Wagner]

Ryan,

Thanks. I have saved the Unix GLOB reference.

When I inferred that other versions of SQLite gave the other results, it 
was thru the other SQLite GUI tools and the version of SQLite that they 
used.


Thanks for helping to make clear what was going on.

Ken



On 01/05/2017 02:53 AM, R Smith wrote:


On 2017/01/05 9:04 AM, Ken Wagner wrote:

Keith,

It appears that the folks at SQLiteTutorial.net have a coding anomaly.

They are not following the UNIX / SQLite3 GLOB patterns. And so, too, 
do the other guys.


I am adjusting my usage accordingly. I will advise users of the other 
products to NOT use the GLOB "*[^1-9]*" pattern. SQLiteTutorial.net 
has already been advised. (No reply, thus far in 2 days.)




What the 3rd party people do is likely override the GLOB and REGEXP 
functions, either when compiling their SQLite versions or at runtime 
using the user-function api. People add their own GLOB sometimes 
purely because they add REGEXP functions (which is not defined by 
default in SQLite) and the new REGEXP comes with a GLOB sibling that 
gets added and they perhaps prefer the outcome as it happens in SQLite 
tutorial and DB Browser etc.  These alternate globbing methodologies 
may even be preferred by you or your customers.


The similarity between the different tools suggest to me they probably 
use the same added code / library which someone made to "improve" over 
the standard Unix file globbing. I wouldn't want or expect the SQLite 
tutorial people to change how they implement the globbing, but they 
should at least notify learners of the difference.


What you and your customers need to know is:
  - SQLite uses Unix file globbing exactly
  - 3rd party tools which implement SQLite engine may override this 
(and seemingly mostly do so)
  - You can expect different GLOB returns via different tools, and 
these need to be understood during use. (Hopefully most tools are 
consistent in their alteration)
  - SQLite engine as used in the SQLite CLI will always return the 
exact same Unix file globbing results in queries.


The big question here was on the last point where you suggested you 
saw different results using some version of the SQLite CLI than 
another - but I think Keith is correct in assuming it's simply a 
mistake, however, if you DO find a difference in any version, please 
let us know - that would be a bug (or a concern at the very least).


Thanks,
Ryan

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


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

Re: [sqlite] date-2.2c tests fail for sqlite-3.16.1 on Fedora / i686

[Jakub Dorňák]

Since the difference is in the fraction part, i'd vote for the
floating-point problem.
I'have encountered this issue with all machines I've tried, however, both
my laptop and koji build systems are natively 64bit.
(my cpuinfo attached)

2017-01-05 11:08 GMT+01:00 Graham Holden :

> I don't know what the tests are doing, but could it be connected with the
> fact that a leap-second was added as we changed from 2016 to 2017 and one
> of expected/got is taking this into account and the other isn't?
> Graham
>  Original message From: Richard Hipp 
> Date: 05/01/2017  08:12  (GMT+00:00) To: SQLite mailing list <
> sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] date-2.2c
> tests fail for sqlite-3.16.1 on Fedora / i686
> On 1/4/17, Jakub Dorňák  wrote:
> > Example output:
> >
> > ...
> > ! date-2.2c-1 expected: [06:28:00.001]
> > ! date-2.2c-1 got:  [06:28:00.000]
> > ! date-2.2c-4 expected: [06:28:00.004]
> > ! date-2.2c-4 got:  [06:28:00.003]
> > ! date-2.2c-7 expected: [06:28:00.007]
> > ! date-2.2c-7 got:  [06:28:00.006]
> > ! date-2.2c-8 expected: [06:28:00.008]
> > ! date-2.2c-8 got:  [06:28:00.007]
> > ...
>
> This is probably a function of the underlying floating-point hardware.
> What CPU is this running on?
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Jakub Dorňák,

Prostřední Poříčí 19, 679 62
Telefon: 728 808 795
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] date-2.2c tests fail for sqlite-3.16.1 on Fedora / i686

[Graham Holden]

I don't know what the tests are doing, but could it be connected with the fact 
that a leap-second was added as we changed from 2016 to 2017 and one of 
expected/got is taking this into account and the other isn't?
Graham
 Original message From: Richard Hipp  Date: 
05/01/2017  08:12  (GMT+00:00) To: SQLite mailing list 
 Subject: Re: [sqlite] date-2.2c tests 
fail for sqlite-3.16.1 on Fedora / i686 
On 1/4/17, Jakub Dorňák  wrote:
> Example output:
>
> ...
> ! date-2.2c-1 expected: [06:28:00.001]
> ! date-2.2c-1 got:  [06:28:00.000]
> ! date-2.2c-4 expected: [06:28:00.004]
> ! date-2.2c-4 got:  [06:28:00.003]
> ! date-2.2c-7 expected: [06:28:00.007]
> ! date-2.2c-7 got:  [06:28:00.006]
> ! date-2.2c-8 expected: [06:28:00.008]
> ! date-2.2c-8 got:  [06:28:00.007]
> ...

This is probably a function of the underlying floating-point hardware.
What CPU is this running on?
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 Tutorial error

[R Smith]

On 2017/01/05 9:04 AM, Ken Wagner wrote:

Keith,

It appears that the folks at SQLiteTutorial.net have a coding anomaly.

They are not following the UNIX / SQLite3 GLOB patterns. And so, too, 
do the other guys.


I am adjusting my usage accordingly. I will advise users of the other 
products to NOT use the GLOB "*[^1-9]*" pattern. SQLiteTutorial.net 
has already been advised. (No reply, thus far in 2 days.)




What the 3rd party people do is likely override the GLOB and REGEXP 
functions, either when compiling their SQLite versions or at runtime 
using the user-function api. People add their own GLOB sometimes purely 
because they add REGEXP functions (which is not defined by default in 
SQLite) and the new REGEXP comes with a GLOB sibling that gets added and 
they perhaps prefer the outcome as it happens in SQLite tutorial and DB 
Browser etc.  These alternate globbing methodologies may even be 
preferred by you or your customers.


The similarity between the different tools suggest to me they probably 
use the same added code / library which someone made to "improve" over 
the standard Unix file globbing. I wouldn't want or expect the SQLite 
tutorial people to change how they implement the globbing, but they 
should at least notify learners of the difference.


What you and your customers need to know is:
  - SQLite uses Unix file globbing exactly
  - 3rd party tools which implement SQLite engine may override this 
(and seemingly mostly do so)
  - You can expect different GLOB returns via different tools, and 
these need to be understood during use. (Hopefully most tools are 
consistent in their alteration)
  - SQLite engine as used in the SQLite CLI will always return the 
exact same Unix file globbing results in queries.


The big question here was on the last point where you suggested you saw 
different results using some version of the SQLite CLI than another - 
but I think Keith is correct in assuming it's simply a mistake, however, 
if you DO find a difference in any version, please let us know - that 
would be a bug (or a concern at the very least).


Thanks,
Ryan

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

Re: [sqlite] date-2.2c tests fail for sqlite-3.16.1 on Fedora / i686

[Richard Hipp]

On 1/4/17, Jakub Dorňák  wrote:
> Example output:
>
> ...
> ! date-2.2c-1 expected: [06:28:00.001]
> ! date-2.2c-1 got:  [06:28:00.000]
> ! date-2.2c-4 expected: [06:28:00.004]
> ! date-2.2c-4 got:  [06:28:00.003]
> ! date-2.2c-7 expected: [06:28:00.007]
> ! date-2.2c-7 got:  [06:28:00.006]
> ! date-2.2c-8 expected: [06:28:00.008]
> ! date-2.2c-8 got:  [06:28:00.007]
> ...

This is probably a function of the underlying floating-point hardware.
What CPU is this running on?
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Commit ad601c7962 degrade performance in a client program

[Richard Hipp]

On 1/3/17, Jianxun Zhang  wrote:
> I am working in Yocto project. We have a fake-root program “pseudo” that
> uses sqlite3 at a step when building Yocto images. We found a 2% increase of
> the whole build time, and my bisecting shows the ad601c7962 in sqlite3 is
> the root cause.

That change was backed out by
https://www.sqlite.org/src/timeline?c=9675518b33e8d407 and so your
code should return to its old speed, or be faster.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] date-2.2c tests fail for sqlite-3.16.1 on Fedora / i686

[Jakub Dorňák]

Example output:

...
! date-2.2c-1 expected: [06:28:00.001]
! date-2.2c-1 got:  [06:28:00.000]
! date-2.2c-4 expected: [06:28:00.004]
! date-2.2c-4 got:  [06:28:00.003]
! date-2.2c-7 expected: [06:28:00.007]
! date-2.2c-7 got:  [06:28:00.006]
! date-2.2c-8 expected: [06:28:00.008]
! date-2.2c-8 got:  [06:28:00.007]
...

The whole build log:
https://kojipkgs.fedoraproject.org//work/tasks/695/17160695/build.log)

The same results with both Fedora 25 and Fedora rawhide.

Jakub Dorňák,

Prostřední Poříčí 19, 679 62
Telefon: 728 808 795
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Commit ad601c7962 degrade performance in a client program

[Patrick Ohly]

On Tue, 2017-01-03 at 17:49 -0800, Jianxun Zhang wrote:
> I am working in Yocto project. We have a fake-root program “pseudo”
> that uses sqlite3 at a step when building Yocto images. We found a 2%
> increase of the whole build time, and my bisecting shows the
> ad601c7962 in sqlite3 is the root cause.

That was 2% on average. In certain long-running parts of a build that
made heavy use of pseudo, that particular commit caused a slowdown of
326% - see
https://www.mail-archive.com/openembedded-core@lists.openembedded.org/msg88109.html

-- 
Best Regards, Patrick Ohly

The content of this message is my personal opinion only and although
I am an employee of Intel, the statements I make here in no way
represent Intel's position on the issue, nor am I authorized to speak
on behalf of Intel on this matter.



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

[sqlite] Commit ad601c7962 degrade performance in a client program

[Jianxun Zhang]

I am working in Yocto project. We have a fake-root program “pseudo” that uses 
sqlite3 at a step when building Yocto images. We found a 2% increase of the 
whole build time, and my bisecting shows the ad601c7962 in sqlite3 is the root 
cause.

Basically, the sqlite3 library is built first from a released amalgamation 
package of sqlite3, then it is linked to the pseudo program. The build system 
uses the pseudo tool to perform some tasks like installation and packaging. A 
slower pseudo program will lead to a longer build time.

Could any experts in Sqlite3 have a look the concerned commit and provide some 
initial thoughts?

I understand my question may not be a “smart” one due to the lack of some 
necessary background information. Unfortunately, I am not the expert in the 
pseudo either so I add more Yocto people in the loop too. And we will try our 
best to provide more needed information based on any feedbacks.

The issue and bisect result are at 
https://bugzilla.yoctoproject.org/show_bug.cgi?id=10367#c17

The pseudo component: http://git.yoctoproject.org/cgit/cgit.cgi/pseudo/

The attached is a log from compiling sqlite3. (I removed some paths)

Thanks lot!

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