[sqlite] Using collation instead a virtual table

[Constantine Yannakopoulos]

On Wed, Sep 9, 2015 at 9:47 PM, R.Smith  wrote:

>
> On 2015-09-09 05:19 PM, Constantine Yannakopoulos wrote:
>
>> On Wed, Sep 9, 2015 at 4:54 PM, Igor Tandetnik 
>> wrote:
>>
>> ?Out of curiosity, doesn't this also apply also to numeric (real number)
>> comparisons since SQLite3 uses IEEE floating point arithmetic??
>>
>
> IEEE Float comparisons do not work this way - you are more likely to find
> the opposite:  two numbers that seem to be near perfectly equal might fail
> an equality test.
>

?That is the problem. There are cases where two numbers that come out of
different calculations? (especially if a division is included) are expected
to be equal but they fail the equality test. A classic case is when you
distribute an amount (e.g. a total) among several rows using a certain
column as weight and you expect the sum of the distributed amounts to be
exactly equal to the original total but it is not.


> On Wed, Sep 9, 2015 at 7:47 PM, Igor Tandetnik  wrote:
> What aspect of IEEE floating point arithmetic makes comparisons unsafe, in
> your opinion? Given two IEEE numbers (NaNs and INFs excepted), the
> comparison would only ever declare them equal if their representations are
> bit-for-bit identical; it doesn't play "close enough" games. What again
> seems to be the problem?
>

?OK, I was under the impression that SQLite ?used an epsilon comparison to
avoid the aforementioned case. Obviously I was wrong. Sorry. As I said, no
problem, just curiosity.

[sqlite] Using collation instead a virtual table

[R.Smith]

On 2015-09-09 09:02 PM, Constantine Yannakopoulos wrote:
> On Wed, Sep 9, 2015 at 9:47 PM, R.Smith  wrote:
>
>> On 2015-09-09 05:19 PM, Constantine Yannakopoulos wrote:
>>
>>> On Wed, Sep 9, 2015 at 4:54 PM, Igor Tandetnik 
>>> wrote:
>>>
>>> ?Out of curiosity, doesn't this also apply also to numeric (real number)
>>> comparisons since SQLite3 uses IEEE floating point arithmetic??
>>>
>> IEEE Float comparisons do not work this way - you are more likely to find
>> the opposite:  two numbers that seem to be near perfectly equal might fail
>> an equality test.
>>
> ?That is the problem. There are cases where two numbers that come out of
> different calculations? (especially if a division is included) are expected
> to be equal but they fail the equality test. A classic case is when you
> distribute an amount (e.g. a total) among several rows using a certain
> column as weight and you expect the sum of the distributed amounts to be
> exactly equal to the original total but it is not.

Indeed - but you can always avoid it when comparing values using your 
own epsilon difference test, something like:

WHERE abs(x-y) < 0.1
(Or whatever small value seems adequate to satisfy your equality 
specification).


While on the subject - found this re-print and edited version of an old 
paper by David Goldberg highlighting all the IEEE pitfalls in computing 
- a valuable read to any programmer:

https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html

[sqlite] FTS: Escaping MATCH expressions

[Dan Kennedy]

On 09/09/2015 07:56 PM, Lohmann, Niels, Dr. (CQTN) wrote:
> Hi there,
>   
> I have a question regarding the expressions that are valid after MATCH: Is 
> there a way to escape a string str such that I can safely bind it to variable 
> @var in a statement like "SELECT * FROM myFtsTable WHERE myFtsTable MATCH 
> @var;"?
>   
> In particular, I encountered error messages with strings like "TEST.*" or 
> "TEST'*".

I don't think there is a foolproof way to do that with FTS4. Enclosing 
the text in double-quotes might help, but then there is no way to escape 
embedded double quotes.

In FTS5 you can enclose tokens in double quotes and escape embeded quote 
characters in the usual SQL way (by doubling them). i.e.

   ... MATCH '"TEST.*"'

Or for {TEST"*}:

   ... MATCH '"TEST""*"'

Dan.




>   
> All the best
> Niels
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Using collation instead a virtual table

[R.Smith]

On 2015-09-09 05:19 PM, Constantine Yannakopoulos wrote:
> On Wed, Sep 9, 2015 at 4:54 PM, Igor Tandetnik  wrote:
>
>> A comparison like this would not generally be a proper collation. The
>> equivalence relation it induces is not transitive - it's possible to have A
>> == B and B == C but A != C (when A is "close enough" to B and B is "close
>> enough" to C, but A and C are just far enough from each other).
>>
> ?Out of curiosity, doesn't this also apply also to numeric (real number)
> comparisons since SQLite3 uses IEEE floating point arithmetic??

IEEE Float comparisons do not work this way - you are more likely to 
find the opposite:  two numbers that seem to be near perfectly equal 
might fail an equality test.

Such confusion might be caused by statements such as:
...WHERE (5.6 - 3.1) = 2.5
...WHERE (14 * 0.4) = 5.6

Which might return false if two or more of the constants cannot be 
precisely represented. (The second one is a known problem value).

Nothing however would "seem" equal to the processor if they are not 
exactly equal in binary form - no "almost" matching happens.

BTW: In strict Math it can be shown that 0.999...  (repeating) is 
exactly equal to 1 but in IEEE floats they are not, but that is just 
because an 8-byte (64b) float lacks the capacity to render the repeating 
nines to sufficiently wide a representation to find the one-ness of it.

https://en.wikipedia.org/wiki/0.999...

IEEE fun in C#:

Testing 1/3:
  f = 0.333
  d = 0.333
  m = 0.
  f*3 = 1
  d*3 = 1
  m*3 = 0.
  (double)f*3 = 1.0002980232
  (decimal)f*3 = 0.999
  (decimal)d*3 = 0.999
  (double)((float)i/3)*3 = 1
Testing 2/3:
  f = 0.667
  d = 0.667
  m = 0.6667
  f*3 = 2
  d*3 = 2
  m*3 = 2.0001
  (double)f*3 = 2.0005960464
  (decimal)f*3 = 2.001
  (decimal)d*3 = 2.001
  (double)((float)i/3)*3 = 2

Cheers,
Ryan

[sqlite] Using collation instead a virtual table

[Constantine Yannakopoulos]

On Wed, Sep 9, 2015 at 4:54 PM, Igor Tandetnik  wrote:

> A comparison like this would not generally be a proper collation. The
> equivalence relation it induces is not transitive - it's possible to have A
> == B and B == C but A != C (when A is "close enough" to B and B is "close
> enough" to C, but A and C are just far enough from each other).
>

?Out of curiosity, doesn't this also apply also to numeric (real number)
comparisons since SQLite3 uses IEEE floating point arithmetic??

[sqlite] Closure.c extension

[Sairam Gaddam]

Is it necessary to have AVL tree in closure tables method and does it
result in speed improvements?

On Wed, Sep 9, 2015 at 4:59 PM, Richard Hipp  wrote:

> On 9/9/15, Sairam Gaddam  wrote:
> > I know that one of the method to compute transitive closure is closure
> > tables method. But I didn't understand why AVL tree implementation is
> > present in the closure.c extension in order to compute transitive closure
> > because the result can be directly queried from the Virtual table(closure
> > table).
> >
> > Can anyone kindly explain what is the significance of AVL tree to compute
> > transitive closure ???
>
> It is used to implement a priority queue.
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Does PRIMARY KEY imply NOT NULL?

[John McKown]

On Wed, Sep 9, 2015 at 3:56 PM, Baruch Burstein 
wrote:

> Question in the subject
>
> --
>
>
?Answered on the SQLite web site: http://sqlite.org/lang_createtable.html


According to the SQL standard, PRIMARY KEY should always imply NOT NULL.
Unfortunately, due to a bug in some early versions, this is not the case in
SQLite. Unless the column is an INTEGER PRIMARY KEY
 or the table is a WITHOUT
ROWID  table or the column is declared
NOT NULL, SQLite allows NULL values in a PRIMARY KEY column. SQLite could
be fixed to conform to the standard, but doing so might break legacy
applications. Hence, it has been decided to merely document the fact that
SQLite allowing NULLs in most PRIMARY KEY columns.
?



-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] FTS: Escaping MATCH expressions

[Martin Kucej]

FTS3/4 replaces non-alphanumeric characters with spaces. I do the same
for strings that I match in my applications. Something like:

preg_replace('/[^\*\da-z\x{0080}-\x{}]/ui', ' ', $string);

On Wed, Sep 9, 2015 at 7:56 AM, Lohmann, Niels, Dr. (CQTN)
 wrote:
> Hi there,
>
> I have a question regarding the expressions that are valid after MATCH: Is 
> there a way to escape a string str such that I can safely bind it to variable 
> @var in a statement like "SELECT * FROM myFtsTable WHERE myFtsTable MATCH 
> @var;"?
>
> In particular, I encountered error messages with strings like "TEST.*" or 
> "TEST'*".
>
> All the best
> Niels
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Using collation instead a virtual table

[Scott Doctor]

Best practice when dealing with floating point is to normalize 
and Chop.

The best practice for dealing with floating point operations is 
to normalize your data sets before proceeding. All numbers 
should be -1.0<=x<=1.0. Done properly, after calculations are 
complete, the data set is easily returned to its original range 
and domain by reversing the normalization process.

Chop is basically a rounding to a specified number of digits. 
Often 4 to 8 digits is adequate but each application is 
different. When dealing with matrix convolutions, after each row 
operation, chop each number in the matrix. Mathematically it can 
be shown that the final result is more accurate than allowing 
the floating point resolution error to propagate. Numbers such 
as 2.9 should round to 3. before doing the next round of 
calculations. This is especially important for results near 
zero. Typically numbers less than, for instance 0.1 should 
be set to zero. This is especially important in matrix operations.

All floating point math libraries have round and/or chop 
functions. In fact, not chopping when doing large data sets will 
ultimately result in significantly wrong results due to 
propagating floating point resolution errors. Chopping corrects 
for these errors.

If you remember your High School chemistry or biology class, one 
of the first lessons is about significant digits. How to 
determine the proper number of significant digits depends on 
your application and field of study. By using proper number of 
significant digits throughout your calculations, the result will 
be more correct than not doing so.


Scott Doctor
scott at scottdoctor.com
--

On 9/9/2015 11:47 AM, R.Smith wrote:
>
>
> On 2015-09-09 05:19 PM, Constantine Yannakopoulos wrote:
>> On Wed, Sep 9, 2015 at 4:54 PM, Igor Tandetnik 
>>  wrote:
>>
>>> A comparison like this would not generally be a proper 
>>> collation. The
>>> equivalence relation it induces is not transitive - it's 
>>> possible to have A
>>> == B and B == C but A != C (when A is "close enough" to B 
>>> and B is "close
>>> enough" to C, but A and C are just far enough from each other).
>>>
>> ?Out of curiosity, doesn't this also apply also to numeric 
>> (real number)
>> comparisons since SQLite3 uses IEEE floating point arithmetic??
>
> IEEE Float comparisons do not work this way - you are more 
> likely to find the opposite:  two numbers that seem to be near 
> perfectly equal might fail an equality test.
>
> Such confusion might be caused by statements such as:
> ...WHERE (5.6 - 3.1) = 2.5
> ...WHERE (14 * 0.4) = 5.6
>
> Which might return false if two or more of the constants 
> cannot be precisely represented. (The second one is a known 
> problem value).
>
> Nothing however would "seem" equal to the processor if they 
> are not exactly equal in binary form - no "almost" matching 
> happens.
>
> BTW: In strict Math it can be shown that 0.999...  (repeating) 
> is exactly equal to 1 but in IEEE floats they are not, but 
> that is just because an 8-byte (64b) float lacks the capacity 
> to render the repeating nines to sufficiently wide a 
> representation to find the one-ness of it.
>
> https://en.wikipedia.org/wiki/0.999...
>
> IEEE fun in C#:
>
> Testing 1/3:
>  f = 0.333
>  d = 0.333
>  m = 0.
>  f*3 = 1
>  d*3 = 1
>  m*3 = 0.
>  (double)f*3 = 1.0002980232
>  (decimal)f*3 = 0.999
>  (decimal)d*3 = 0.999
>  (double)((float)i/3)*3 = 1
> Testing 2/3:
>  f = 0.667
>  d = 0.667
>  m = 0.6667
>  f*3 = 2
>  d*3 = 2
>  m*3 = 2.0001
>  (double)f*3 = 2.0005960464
>  (decimal)f*3 = 2.001
>  (decimal)d*3 = 2.001
>  (double)((float)i/3)*3 = 2
>
> Cheers,
> Ryan
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 
>

[sqlite] FTS: Escaping MATCH expressions

[Lohmann, Niels, Dr. (CQTN)]

Hi there,
?
I have a question regarding the expressions that are valid after MATCH: Is 
there a way to escape a string str such that I can safely bind it to variable 
@var in a statement like "SELECT * FROM myFtsTable WHERE myFtsTable MATCH 
@var;"?
?
In particular, I encountered error messages with strings like "TEST.*" or 
"TEST'*".
?
All the best
Niels

[sqlite] Using collation instead a virtual table

[Eduardo Morras]

On Tue, 8 Sep 2015 15:42:28 -0400
Richard Hipp  wrote:

> On 9/8/15, Eduardo Morras  wrote:
> >
> >
> > Hello,
> >
> > I have a virtual table that implements query perceptual hashing data
> > [1]. Now I'm thinking about converting the virtual table
> > implementation in a collation on a normal sqlite3 table, but
> > collation requieres that '=','<' and '>' be well defined by obeying
> > the rules cited on create_collation() page[2]. Sometimes, rule 2
> > may not be true, but I always query for '=',
> 
> Yes, but under the hood, SQLlite never does an == query on the b-trees
> even if you ask for a == query in the SQL.  Instead, the b-trees are
> queried using one of >, >=, <, or <=.  A query of the form:
> 
>  x=$value
> 
> Gets translated (at the b-tree layer) into
> 
>  x>=$value AND x<=$value
> 
> So it is *very* important that the comparison operators all work
> correctly on your collating sequence function.  If they don't, then
> SQLite will give incorrect answers.

Yes, the comparison operators work correctly and the b-tree binary search 
should give correct answers, the only tweak is in xCompare, that returns 0 when 
left(x) and rigth($value) expressions distance is lower than a threshold. I 
begin with the implementation and test cases, I expect it be faster than 
virtual table.

Thanks

---   ---
Eduardo Morras 

[sqlite] Using collation instead a virtual table

[Igor Tandetnik]

On 9/9/2015 11:19 AM, Constantine Yannakopoulos wrote:
> On Wed, Sep 9, 2015 at 4:54 PM, Igor Tandetnik  wrote:
>
>> A comparison like this would not generally be a proper collation. The
>> equivalence relation it induces is not transitive - it's possible to have A
>> == B and B == C but A != C (when A is "close enough" to B and B is "close
>> enough" to C, but A and C are just far enough from each other).
>>
>
> ?Out of curiosity, doesn't this also apply also to numeric (real number)
> comparisons since SQLite3 uses IEEE floating point arithmetic??

What aspect of IEEE floating point arithmetic makes comparisons unsafe, 
in your opinion? Given two IEEE numbers (NaNs and INFs excepted), the 
comparison would only ever declare them equal if their representations 
are bit-for-bit identical; it doesn't play "close enough" games. What 
again seems to be the problem?
-- 
Igor Tandetnik

[sqlite] Disk-image malformed

[Klaas V]

Dear fellow users and developers of SQLite,

sqlite> create table dual(dummy blob);
sqlite> insert into dual values ('SQLite version 3.8.11.1 2015-07-29 20:00:57');
sqlite> create table z4usm1 as select (unicode(substr(type,2,1)) - 97) / 4 "zk",
   ...>name "zn", rootpage "zp", tbl_name "zt" from 
sqlite_master; 
Error: database disk image is malformed

-- relevant part of .dbinfo
number of tables:1
number of indexes:   0
number of triggers:  0
number of views: 0
schema size: 29

I found a work-around by creating a view (actually my first intention, because 
that is what it actually is), but 
  if you really want to create a table this way how to achieve that?

sqlite> pragma page_size=512;
sqlite> create table dual(dummy blob);
sqlite> insert into dual values ('SQLite version 3.8.11.1 2015-07-29 20:00:57');
sqlite> create view z4usm0 as select (unicode(substr(type,2,1)) - 97) / 4 "zk",
   ...>name "zn", rootpage "zp", tbl_name "zt" from 
sqlite_master; 
sqlite> .he on
sqlite> select * from z4usm0;
zk|zn|zp|zt
0|dual|2|dual
2|z4usm0|0|z4usm0

-- relevant part of .dbinfo
number of tables:1
number of indexes:   0
number of triggers:  0
number of views: 1
schema size: 182

sqlite> .sh ls -lg m?.sq?
-rw-r--r--  1 staff  1024  9 Set 11:20 m0.sqb
-rw-r--r--  1 staff  2048  9 Set 11:10 m1.sqb


Another question: When can I see type 'meta' (would result in zk=1) [ exists 
according to https://www.sqlite.org/cli.html#fileio ] ?


Kind regards |?Cordiali saluti | Vriendelijke groeten | Freundliche Gr?sse,
Klaas `Z4us` V ?- OrcID -0001-7190-2544

[sqlite] Third test of json and index expressions, now it works

[Domingo Alvarez Duarte]

Hello !  

This fix the example I gave on the other email it was "r <> r" but it's more
easy to see the problem with "r = r".

 Nice explanation ! 

 With your knowledge could you give your view about how evaluation of
 calculated/function columns should be done to have correct results. 

 Like another example given on other thread: 

 _ 

 CREATE TABLE a(b); 

 INSERT INTO a(b) VALUES(1),(2),(3); 

 SELECT a, random() as r FROM a WHERE r = r;

 _

[sqlite] Closure.c extension

[Sairam Gaddam]

I know that one of the method to compute transitive closure is closure
tables method. But I didn't understand why AVL tree implementation is
present in the closure.c extension in order to compute transitive closure
because the result can be directly queried from the Virtual table(closure
table).

Can anyone kindly explain what is the significance of AVL tree to compute
transitive closure ???

[sqlite] Third test of json and index expressions, now it works

[Simon Slavin]

On 9 Sep 2015, at 8:56am, Domingo Alvarez Duarte  
wrote:

> SELECT a, random() as r FROM a WHERE r <> r; 

This is not valid SQL.  You won't find any official SQL standard saying that 
'r' can be referred to in the WHERE clause.

You may find implementations of SQL where which will accept that command, and 
what those do is entirely up to whoever wrote them.  If they have some sort of 
'strict' option and you turn it on, they should instead produce an error 
message.

Simon.

[sqlite] Third test of json and index expressions, now it works

[Domingo Alvarez Duarte]

Hello !  

Nice explanation !  

With your knowledge could you give your view about how evaluation of
calculated/function columns should be done to have correct results.  

Like another example given on other thread:  

_  

CREATE TABLE a(b);  

INSERT INTO a(b) VALUES(1),(2),(3);  

SELECT a, random() as r FROM a WHERE r <> r;  

_  
>  Wed Sep 09 2015 4:34:48 am CEST CEST from "James K. Lowden"
>  Subject: Re: [sqlite] Third test of json and
>index expressions, now it works
>
>  On Sat, 5 Sep 2015 09:07:11 -0700
> Darko Volaric  wrote:
> 
>  
>>I'm asking why the SQL standard restricts the use of aliases in this
>> way and what the benefit of this restriction is.
>> 

>  Rationales in SQL are hard to come by. The language was promulgated by
> a private firm, and the standard evolved under the aegis of what was,
> for all intents and purposes, a private club. Rationales for most
> warts in the language boil down to "because we say so". 
> 
> Nonetheless, there is a good reason! 
> 
> There are no aliases in SQL, Horatio. 
> 
> In every SQL database, column names are unique and unambiguous. If you
> know the name of a table and a column, you've identified it. A query
> can name *new* columns, but it can't create aliases for existing
> ones. Consider, 
> 
>  
>>SELECT a+b AS x FROM t1 WHERE x=99;
>> 

>  Here, "x" is a new column, the product of the SELECT. By the rules of
> SQL, it's *not* a macro for a+b, and it's not an alias. It's the name
> of the column formed by computing a+b, a new column of a new table. 
> 
> Now consider, 
> 
> SELECT x from (
> SELECT a+b AS x FROM t1 
> ) as TEETH_GNASHER
> WHERE x=99;
> 
> The outer query does not refer to t1, and thus not to "a" nor "b". It
> sees only the new table, with its sole column, "x". And, although it's
> a bit verbose, it also satisfies the sacred DRY criterion. 
> 
> Is that good? The "no aliases" rule has one thing going for it: it's
> consistent. It's easy to understand and remember, and it reduces
> opportunities for ambiguity. SQL is a logic-based language, and
> ambiguity in logic is anathema because it's too easy to form
> syntactically valid constructions that produce incorrect (and
> unintended) results. 
> 
> Nearly every SQL programmer uses some other programming language as the
> "real" language for his application. There's a temptation to make
> informal, sometimes unwitting assumptions about the rules of SQL drawn
> from that other language. The best way to understand any language
> though, including SQL, is on its own terms. So double-quotes denote
> identifiers, single-quotes strings, "||" contatenation, and there are
> no aliases. It's not easy to slough off unwarranted associations with
> other languages, but once that's done, SQL is impossible to
> misconstrue. 
> 
> --jkl
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Using collation instead a virtual table

[Igor Tandetnik]

On 9/9/2015 6:54 AM, Eduardo Morras wrote:
> Yes, the comparison operators work correctly and the b-tree binary search 
> should give correct answers, the only tweak is in xCompare, that returns 0 
> when left(x) and rigth($value) expressions distance is lower than a threshold.

A comparison like this would not generally be a proper collation. The 
equivalence relation it induces is not transitive - it's possible to 
have A == B and B == C but A != C (when A is "close enough" to B and B 
is "close enough" to C, but A and C are just far enough from each other).
-- 
Igor Tandetnik

[sqlite] SQLITE_READONLY_ROLLBACK due to expected race?

[Matthew Flatt]

The documentation for SQLITE_READONLY_ROLLBACK suggests that it will
only happen as a result of a previous crash or power failure, where a
hot journal is left behind. I'm seeing that error without those events
and with a small number of concurrent readers and writers.

In the implementation of SQLite, comments in hasHotJournal() mention
the possibility of a false positive (referencing ticket #3883, which I
cannot find) and how it will be handled in the playback mechanism after
obtaining an exclusive lock. The check for a read-only connection after
hasHotJournal() is called, however, happens (and must happen?) before
that exclusive lock is acquired. So, it seems like the race-provoked
false positive could trigger a false SQLITE_READONLY_ROLLBACK error.

If that's right, then for my application's purposes, it works to detect
SQLITE_READONLY_ROLLBACK and just try again. I'd like to make sure I'm
not missing some other problem in my system, though.

Thanks!
Matthew

[sqlite] Closure.c extension

[Richard Hipp]

On 9/9/15, Sairam Gaddam  wrote:
> I know that one of the method to compute transitive closure is closure
> tables method. But I didn't understand why AVL tree implementation is
> present in the closure.c extension in order to compute transitive closure
> because the result can be directly queried from the Virtual table(closure
> table).
>
> Can anyone kindly explain what is the significance of AVL tree to compute
> transitive closure ???

It is used to implement a priority queue.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Command line sqlite3 program bug

[Domingo Alvarez Duarte]

Hello !  

Every day is a day to learn something new !  

It also have the command line -bail for my example "sqlite3 -bail a.db <
the.sql".  

Interesting that how easy I can miss the basics "when everything else fail,
read the manual !".  

Thank you !  

Cheers !  
>  Tue Sep 08 2015 9:40:37 pm CEST CEST from "Gerry Snyder"
>  Subject: Re: [sqlite] Command line sqlite3 
>program
>bug
>
>  The Command Line Interface has the command:
> 
> ..bail on
> 
> which will do what you want.
> 
> HTH,
> 
> Gerry Snyder
> ---
> On 9/8/2015 9:54 AM, Domingo Alvarez Duarte wrote:
>  
>>Hello !
>> 
>> After seem several emails from a user asking about how to use sqlite3
>>through
>> shell scripts, I remember my experiences with sqlite3 but didn't mind to
>> report it, but now I think that it's worth mention it because it'll hurt
>> several users.
>> 
>> The bug/problem is that the sqlite3 command line when feed with a sql
>>script
>> with commands wrapped by a transaction if there is any error in the middle
>>of
>> it sqlite3 reports the error but do not stop/abort the transaction and the
>> database end up in a dirty state.
>> 
>> __example to show the bug/problem
>> 
>> BEGIN;
>> 
>> DROP TABLE IF EXISTS a; --to allow run more than once
>> 
>> CREATE TABLE a(b); --after the next line error this should be rolled back
>> 
>> INSERT INTO a(c) VALUES(3); -- intentional error and no stop/rollback
>> 
>> INSERT INTO a(b) values(4);
>> 
>> COMMIT;
>> 
>> __
>> 
>> __blank database after been feed by the above sql script
>> 
>> PRAGMA foreign_keys=OFF;
>> BEGIN TRANSACTION;
>> CREATE TABLE a(b);
>> INSERT INTO "a" VALUES(4);
>> COMMIT;
>> 
>> __
>> 
>> Cheers !
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 

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



?