Re: [sqlite] .mode column .width .separator

[Don V Nielsen]

Thanks John. I had a feeling you would have encountered this sort of stuff.

And thanks for your time, Simon.

All is appreciated.

And thanks in advance, Dr. Hipp, if you act on this...allowing a \0 row
separator in column mode.  It would simplify the life of us mainframers.

dvn

On Thu, Oct 13, 2016 at 3:31 PM, John McKown 
wrote:

> On Thu, Oct 13, 2016 at 2:42 PM, Don V Nielsen 
> wrote:
>
> > Thanks, but it appears that ".mode column" triggers it, and .separator
> does
> > not appear to have any influence in that mode.
> >
> > Out of curiosity, it appears that the row separator (in windows) is a
> > single character. Do you know to specify as the row separator?
> Everything
> > I attempt is taken as literal characters.  I've attempted: 0x0D0x0A,
> > 0x0D0A, 0Dx0Ax. Nothing appears to work.
> >
>
> ​The row separator is specified using the 2nd parameter of the .separator
> command. Example transcript:
>
>
> SQLite version 3.11.0 2016-02-15 17:29:24
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .mode column
> sqlite> .separator | -
> sqlite> create table a(one text,two text);
> sqlite> .width 10 10
> sqlite> insert into a values('1a','2a');
> sqlite> insert into a values('1b','2b');
> sqlite> select * from a;
> 1a  2a-1b  2b-sqlite>
>
> Note that the specified column separator is ignored in .mode column, but
> the row separator is not. Also, for fun, note what happens with negative
> widths
>
> sqlite> .width -10 -10
> sqlite> select * from a;
> 1a  2a-1b  2b-sqlite>
>
> Also, I have looked at the current sqlite3.c source code. In .mode column,
> the space separator character is "hard coded" and so cannot be set to any
> other character.​
>
> Lastly, you can specify a "control" character by using a C language escape.
> E.g. (continuing from above examples)
>
> sqlite> .separator - \n
> sqlite> select * from a;
> 1a  2a
> 1b  2b
>
> To address your desire, it would be necessary for the column separator
> character to be honored in .mode column mode and the separator be made a
> 0x00, or \0. If Dr. Hipp were to do this, this would eliminate the column
> separator entirely because \0 would result in "no" character between the
> columns. This appears, to me, to be a rather simple change in shell.c.
>
>
> > dvn
> >
> >
>
> --
> Heisenberg may have been here.
>
> Unicode: http://xkcd.com/1726/
>
> Maranatha! <><
> John McKown
> ___
> 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] .mode column .width .separator

[John McKown]

On Thu, Oct 13, 2016 at 2:42 PM, Don V Nielsen 
wrote:

> Thanks, but it appears that ".mode column" triggers it, and .separator does
> not appear to have any influence in that mode.
>
> Out of curiosity, it appears that the row separator (in windows) is a
> single character. Do you know to specify as the row separator?  Everything
> I attempt is taken as literal characters.  I've attempted: 0x0D0x0A,
> 0x0D0A, 0Dx0Ax. Nothing appears to work.
>

​The row separator is specified using the 2nd parameter of the .separator
command. Example transcript:


SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode column
sqlite> .separator | -
sqlite> create table a(one text,two text);
sqlite> .width 10 10
sqlite> insert into a values('1a','2a');
sqlite> insert into a values('1b','2b');
sqlite> select * from a;
1a  2a-1b  2b-sqlite>

Note that the specified column separator is ignored in .mode column, but
the row separator is not. Also, for fun, note what happens with negative
widths

sqlite> .width -10 -10
sqlite> select * from a;
1a  2a-1b  2b-sqlite>

Also, I have looked at the current sqlite3.c source code. In .mode column,
the space separator character is "hard coded" and so cannot be set to any
other character.​

Lastly, you can specify a "control" character by using a C language escape.
E.g. (continuing from above examples)

sqlite> .separator - \n
sqlite> select * from a;
1a  2a
1b  2b

To address your desire, it would be necessary for the column separator
character to be honored in .mode column mode and the separator be made a
0x00, or \0. If Dr. Hipp were to do this, this would eliminate the column
separator entirely because \0 would result in "no" character between the
columns. This appears, to me, to be a rather simple change in shell.c.


> dvn
>
>

-- 
Heisenberg may have been here.

Unicode: http://xkcd.com/1726/

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Header Change

[Simon Slavin]

On 13 Oct 2016, at 8:48pm, Matías Badin  wrote:

> I have a problem with my sqilte3 database. It works great for
> days/weeks/months but suddenly the error "file is encrypted or is not a
> database".
> Looking for the problem I realised that the header was changed. So I can
> recover it with an hexa editor, but I don´t know why this happens.

Do any of these seem likely ?



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

[sqlite] Header Change

[Matías Badin]

Hi everyone;
I have a problem with my sqilte3 database. It works great for
days/weeks/months but suddenly the error "file is encrypted or is not a
database".
Looking for the problem I realised that the header was changed. So I can
recover it with an hexa editor, but I don´t know why this happens.


Thanks all for your help!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .mode column .width .separator

[Don V Nielsen]

Thanks, but it appears that ".mode column" triggers it, and .separator does
not appear to have any influence in that mode.

Out of curiosity, it appears that the row separator (in windows) is a
single character. Do you know to specify as the row separator?  Everything
I attempt is taken as literal characters.  I've attempted: 0x0D0x0A,
0x0D0A, 0Dx0Ax. Nothing appears to work.

dvn

On Thu, Oct 13, 2016 at 2:11 PM, Simon Slavin  wrote:

>
> On 13 Oct 2016, at 7:51pm, Don V Nielsen  wrote:
>
> > The .separator command does not provide any
> > mechanism for turning it off. Is there a way?
>
> Can't try it now but does
>
> .separator ""
>
> do what you want ?
>
> 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] .mode column .width .separator

[Simon Slavin]

On 13 Oct 2016, at 7:51pm, Don V Nielsen  wrote:

> The .separator command does not provide any
> mechanism for turning it off. Is there a way?

Can't try it now but does

.separator ""

do what you want ?

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

Re: [sqlite] .mode column .width .separator

[Don V Nielsen]

I can do this, which works. But the redundancy bothers me, and is prone to
finger-check errors.

with pre_process as (
  select
recid,
z_num,
zip,
zip4,
dpbc,
case when piecerate in ('AF','RF') and version_id = '81' then '81' else
segment end as segment,
  ... blah blah ...
  from address_txt
)
select
printf(
'%-1s%-15s%-5s%-4s%-2s%-1s%-4s%-4s%-1s%-5s%-1s%-10s%-10s%-10s%-1s%-20s%-20s%-15s%-1s%-10s%-20s%-20s%-50s%-50s%-50s%-50s%-50s%-25s%-2s%-8s%-003s%-006s%-009s',
recid,
z_num,
zip,
zip4,
dpbc,
segment
  ... blah blah ...
)
from pre_process
limit 10
;


On Thu, Oct 13, 2016 at 2:02 PM, Don V Nielsen 
wrote:

> These are simply blanks, 0x20, use to create separation of the output
> columns. I'm assuming this is an inherent behavior for readability. If the
> output was not being directed to the output file, it would be directed to
> the display.
>
> I'm trying to avoid pre processing (creating a table or view of the
> preprocessed data) and post processing (having to pass 10g of text data to
> removed blanks -- which is dangerous on its own.)
>
> dvn
>
> On Thu, Oct 13, 2016 at 1:56 PM, Simon Slavin 
> wrote:
>
>>
>> On 13 Oct 2016, at 7:51pm, Don V Nielsen  wrote:
>>
>> > Unfortunately, there are two blanks separating each column
>>
>> Can you tell what characters these are ?  Perhaps use a hexdump facility.
>>
>> My guess at this point is that you should continue with the file you have
>> already developed and then post-process it to remove the blank characters.
>> If those characters are used only in the blanks you don't want, it should
>> be possible to use a simple find/replace utility to do it.
>>
>> 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] .mode column .width .separator

[Don V Nielsen]

These are simply blanks, 0x20, use to create separation of the output
columns. I'm assuming this is an inherent behavior for readability. If the
output was not being directed to the output file, it would be directed to
the display.

I'm trying to avoid pre processing (creating a table or view of the
preprocessed data) and post processing (having to pass 10g of text data to
removed blanks -- which is dangerous on its own.)

dvn

On Thu, Oct 13, 2016 at 1:56 PM, Simon Slavin  wrote:

>
> On 13 Oct 2016, at 7:51pm, Don V Nielsen  wrote:
>
> > Unfortunately, there are two blanks separating each column
>
> Can you tell what characters these are ?  Perhaps use a hexdump facility.
>
> My guess at this point is that you should continue with the file you have
> already developed and then post-process it to remove the blank characters.
> If those characters are used only in the blanks you don't want, it should
> be possible to use a simple find/replace utility to do it.
>
> 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] .mode column .width .separator

[Simon Slavin]

On 13 Oct 2016, at 7:51pm, Don V Nielsen  wrote:

> Unfortunately, there are two blanks separating each column

Can you tell what characters these are ?  Perhaps use a hexdump facility.

My guess at this point is that you should continue with the file you have 
already developed and then post-process it to remove the blank characters.  If 
those characters are used only in the blanks you don't want, it should be 
possible to use a simple find/replace utility to do it.

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

[sqlite] .mode column .width .separator

[Don V Nielsen]

I am using the command line shell SQLite 3.14.2 2016-09-12. I'm working
with mainframe data in a fixed format.

I would like to use .mode column to create my output text file in a fixed
layout. I set all my column widths using .width. I then output my data to a
file. Unfortunately, there are two blanks separating each column, space I
don't want to be there. The .separator command does not provide any
mechanism for turning it off. Is there a way?

I realize there is a printf function available. However, it appears that
output values must come from a table column, where as below, I could use
case statements in the sql select of the data.

Any suggestions? I think I am overlooking a .separator option that says
"don't put spaces between output columns". I assume that John McKown has
faced this already, given his mainframe pedigree.

Thanks for your time,
dvn



Sample output:
"H  0NZOX0001687395  83501  5827  "...

.mode column
.width 1 15 5 4 2 1 4 4 1 5 1 10 10 10 1 20 20 15 1 10 20 20 50 50 50 50 50
25 2 8 003 006 009
.output vo_pwprep.txt
select
  recid,
  z_num,
  zip,
  zip4,
  dpbc,
  ckdig,
  cart,
  lot,
  lot_order,
  walk_seq,
  walk_seq_bic,
  case when piecerate in ('AF','RF') and version_id = '81' then '81' else
segment end as segment,
  version_id,
  message,
  seed,
  seed_id,
  seed_key,
  planet,
  ocr_acr,
  priority,
  keycode,
  custno,
  name,
  title,
  firm_id,
  addr1,
  addr2,
  city,
  state,
  seq_number,
  srvc_type,
  imb_mid,
  imb_serial
from address_txt
;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger WHEN condition, comparing with null

[Daniel Polski]

Replace != with IS NOT.


Regards,
Clemens



Thanks! Exactly what I looked for.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger WHEN condition, comparing with null

[rhuijben]

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of R Smith
> Sent: donderdag 13 oktober 2016 17:37
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Trigger WHEN condition, comparing with null
> 
> 
> 
> On 2016/10/13 1:09 PM, Hick Gunter wrote:
> > (new.value != old.value) or (new.value IS NULL) or (old.value IS NULL)

I'm guessing
   (new.value IS NOT old.value)
is the check the OP wants.

(This was already noted in another mail... but it may not be obvious that you 
can just perform the comparison this way instead of only checking for NULL/NOT 
NULL)

Bert

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

Re: [sqlite] Trigger WHEN condition, comparing with null

[R Smith]

On 2016/10/13 1:09 PM, Hick Gunter wrote:

(new.value != old.value) or (new.value IS NULL) or (old.value IS NULL)


This will test true for a condition where new.value and old.value are 
both NULL (so technically not different), which may or may not be what 
the OP wanted.




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

Re: [sqlite] Parallel access to read only in memory database

[Howard Chu]

Daniel Meyer wrote:

We are interested in using sqlite as a read only, in memory, parallel
access database.  We have database files that are on the order of 100GB
that we are loading into memory.  We have found great performance when
reading from a single thread.  We need to scale up to have many parallel
reader threads.  Once the DB is created it never needs to be modified.  How
can we allow many reader threads on an in memory, write once read many
times database and achieve multi-core performance?  Is this possible with
sqlite?


Thanks for all the helpful responses.  I have moved forward experimenting
with using parallel readers on an in memory sqlite database.  I have found
that I get true concurrency (multi-core speed up) when I create a new
connection to my database file on disk in every thread.  I've verified this
by running a single long query and then running the same query in several
threads and ensuring the net time is the same as the single thread query
time.

In order to get parallel readers on an in memory database I first loaded
the file into memory with:
rc = sqlite3_open_v2("file::memory:?cache=shared", &db, SQLITE_OPEN_URI |
SQLITE_OPEN_READWRITE, NULL);
I hold onto the db reference in the main thread after loading the data and
don't close that connection until all the worker threads are done
attempting to run my long query.

I then spawn N threads, each creating their own connection like so:
rc = sqlite3_open_v2("file::memory:?cache=shared", &db, SQLITE_OPEN_URI |
SQLITE_OPEN_READONLY | SQLITE_OPEN_NOMUTEX, NULL);
I have experimented with the various flags; however, everything I do gives
me serial performance.  There is some kind of mutex locking the database so
that running my query say two times takes twice as long as running it once
whereas with the disk based approach using this connection string in each
thread:
rc = sqlite3_open("data.sl3", &db);
makes the total time to run the query twice the same as running it just
once.

I am hypothesizing that since we are using 'cache=shared' and hence each
thread is sharing the same cache each read requires locking the database.
What I would like is to get the same kind of behavior as we get with "
file::memory:?cache=shared" wherein every time I open a new connection that
connection points to the same memory; however, does not actually involve
sharing the cache so no global mutex locks the database on every read.

I have put my test code in a gist.
My C code is here:
https://gist.github.com/danielrmeyer/fae54d5993f2800626c616e72782b5eb
I generate the 1.5GB test database with this python 3.4 script:
https://gist.github.com/danielrmeyer/bfa415256502471d1512f2155e76adc2

I compiled the C code on my system with the following command:
gcc -std=gnu99 test.c -o test -lsqlite3 -lpthread (I did download the
amalgamation and copied the sqlite.h and sqlite.o files into my cwd after
building)

I apologize if the C code is not as clean as it could be.  I'm primarily a
Python programmer but figured i'd be more likely to get help with a C test
case so I did my best to hack this together.  The Python GIL was confusing
the situation in any case.

A little background on what I am doing:  I have several large datasets that
I wish to serve up to customers to generate custom reports based on unique
slices of the data.  I am using a cluster of machines with .5TB of memory
each so loading all the data into memory is reasonable in my case.  I've
found that against my production work load I get massive speedups in single
threaded tests against the in memory database relative to the disk
version.  In fact I have found that the single threaded sqlite in memory
tests are faster than all the other database solutions i've looked at so I
am very excited about using sqlite, nevertheless I really need to scale to
many cores.  Also, my work load is highly random so cache is not much
help.  I really want the data in memory.  Any help is greatly appreciated.
I have started experimenting with memory mapped io; however, I have not had
much luck so far.


Use LMDB in SQLightning. Since LMDB reads acquire no locks, they scale 
perfectly linearly across arbitrarily many CPUs. No other DB engine will do 
what you're looking for. Everything else based on locking will bottleneck as 
soon as you extend beyond a single CPU socket.



I would not mind creating a fork of sqlite on github and hacking the code
if someone could give me pointers on what needs to be modified to get this
working.  Certainly if there is an extra flag or URI I need to use to get
concurrent in memory read access that would be great, but I'm willing to
try and modify the source code and sharing with the community if I can
figure out how to get this going.


--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list

Re: [sqlite] Static Analysis on sqlite codebase

[Richard Hipp]

Attachments are stripped from this mailing list.  Please send your
attachments directly to me.  As we are working toward the scheduled
release of 3.15.0 tomorrow, please send your attachments as soon as
possible.  Thanks.

On 10/11/16, Miroslav Franc  wrote:
> Hello, as a side effect of what I do I ran static analysis on sqlite
> codebase and found 4 defects.  Two potential overflows and two unchecked
> calls.  Not sure what is the best way to handle those, but please see the
> attached patch.
>
> Best regards,
> Miroslav Franc
> ___
> 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] Parallel access to read only in memory database

[Daniel Meyer]

>We are interested in using sqlite as a read only, in memory, parallel
>access database.  We have database files that are on the order of 100GB
>that we are loading into memory.  We have found great performance when
>reading from a single thread.  We need to scale up to have many parallel
>reader threads.  Once the DB is created it never needs to be modified.  How
>can we allow many reader threads on an in memory, write once read many
>times database and achieve multi-core performance?  Is this possible with
>sqlite?

Thanks for all the helpful responses.  I have moved forward experimenting
with using parallel readers on an in memory sqlite database.  I have found
that I get true concurrency (multi-core speed up) when I create a new
connection to my database file on disk in every thread.  I've verified this
by running a single long query and then running the same query in several
threads and ensuring the net time is the same as the single thread query
time.

In order to get parallel readers on an in memory database I first loaded
the file into memory with:
rc = sqlite3_open_v2("file::memory:?cache=shared", &db, SQLITE_OPEN_URI |
SQLITE_OPEN_READWRITE, NULL);
I hold onto the db reference in the main thread after loading the data and
don't close that connection until all the worker threads are done
attempting to run my long query.

I then spawn N threads, each creating their own connection like so:
rc = sqlite3_open_v2("file::memory:?cache=shared", &db, SQLITE_OPEN_URI |
SQLITE_OPEN_READONLY | SQLITE_OPEN_NOMUTEX, NULL);
I have experimented with the various flags; however, everything I do gives
me serial performance.  There is some kind of mutex locking the database so
that running my query say two times takes twice as long as running it once
whereas with the disk based approach using this connection string in each
thread:
rc = sqlite3_open("data.sl3", &db);
makes the total time to run the query twice the same as running it just
once.

I am hypothesizing that since we are using 'cache=shared' and hence each
thread is sharing the same cache each read requires locking the database.
What I would like is to get the same kind of behavior as we get with "
file::memory:?cache=shared" wherein every time I open a new connection that
connection points to the same memory; however, does not actually involve
sharing the cache so no global mutex locks the database on every read.

I have put my test code in a gist.
My C code is here:
https://gist.github.com/danielrmeyer/fae54d5993f2800626c616e72782b5eb
I generate the 1.5GB test database with this python 3.4 script:
https://gist.github.com/danielrmeyer/bfa415256502471d1512f2155e76adc2

I compiled the C code on my system with the following command:
gcc -std=gnu99 test.c -o test -lsqlite3 -lpthread (I did download the
amalgamation and copied the sqlite.h and sqlite.o files into my cwd after
building)

I apologize if the C code is not as clean as it could be.  I'm primarily a
Python programmer but figured i'd be more likely to get help with a C test
case so I did my best to hack this together.  The Python GIL was confusing
the situation in any case.

A little background on what I am doing:  I have several large datasets that
I wish to serve up to customers to generate custom reports based on unique
slices of the data.  I am using a cluster of machines with .5TB of memory
each so loading all the data into memory is reasonable in my case.  I've
found that against my production work load I get massive speedups in single
threaded tests against the in memory database relative to the disk
version.  In fact I have found that the single threaded sqlite in memory
tests are faster than all the other database solutions i've looked at so I
am very excited about using sqlite, nevertheless I really need to scale to
many cores.  Also, my work load is highly random so cache is not much
help.  I really want the data in memory.  Any help is greatly appreciated.
I have started experimenting with memory mapped io; however, I have not had
much luck so far.

I would not mind creating a fork of sqlite on github and hacking the code
if someone could give me pointers on what needs to be modified to get this
working.  Certainly if there is an extra flag or URI I need to use to get
concurrent in memory read access that would be great, but I'm willing to
try and modify the source code and sharing with the community if I can
figure out how to get this going.

On Sat, Oct 8, 2016 at 5:00 AM, <
sqlite-users-requ...@mailinglists.sqlite.org> wrote:

> Send sqlite-users mailing list submissions to
> sqlite-users@mailinglists.sqlite.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/
> sqlite-users
> or, via email, send a message with subject or body 'help' to
> sqlite-users-requ...@mailinglists.sqlite.org
>
> You can reach the person managing the list at
> sqlite-users-ow...@mailinglists.sqlite.org
>
> When

[sqlite] Bug in lempar.c leads to compiler crashes

[Benjamin Franksen]

Hello

it's me again, the guy who uses the lemon parser generator in his
project (http://www-csr.bessy.de/control/SoftDist/sequencer/).

I have found another problem in lempar.c. This one leads to crashes
(assertion failures) in my compiler, at least on 64 bit systems
(observed on Windows and Linux).

The failed assertion is at line 397 in lempar.c which reads:

  assert( pParser->yytos > pParser->yystack );

This is is function yy_pop_parser_stack. I searched for where this
function is used and found that in all cases except one there is a
condition that guarantees the above precondition is actually fulfilled.
The exception is at line 881 in function Parse where it says

while( yypParser->yytos >= yypParser->yystack
&& yymx != YYERRORSYMBOL
&& (yyact = yy_find_reduce_action(
yypParser->yytos->stateno,
YYERRORSYMBOL)) >= YY_MIN_REDUCE
){
  yy_pop_parser_stack(yypParser);
}
if( yypParser->yytos < yypParser->yystack || yymajor==0 ){
  yy_destructor(yypParser,(YYCODETYPE)yymajor,&yyminorunion);
  yy_parse_failed(yypParser);
  ...

Here, the loop condition is '>=', not '>'. I think the loop condition
should have '>'. Also, the if condition after the loop is dubious: I
think there should be an assertion after the loop:

assert( yypParser->yytos >= yypParser->yystack );

since that appears to be an invariant; and the if-condition (that seems
to test whether the stack is now empty) should have '==':

if( yypParser->yytos == yypParser->yystack || yymajor==0 ){

I attached a patch file that makes these changes. With the patch
applied, I get no more assertion failures (on Linux, awaiting
confirmation for Windows in the next days...).

BTW, it seems the bug only manifests when parsing fails, though not in
all such cases.

Cheers
Ben

PS: Please include me in any replies as I am not subscribed to the list.
-- 
"Make it so they have to reboot after every typo." ― Scott Adams


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Static Analysis on sqlite codebase

[Miroslav Franc]

Hello, as a side effect of what I do I ran static analysis on sqlite codebase 
and found 4 defects.  Two potential overflows and two unchecked calls.  Not 
sure what is the best way to handle those, but please see the attached patch.

Best regards,
Miroslav Franc
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger WHEN condition, comparing with null

[Hick Gunter]

(new.value != old.value) or (new.value IS NULL) or (old.value IS NULL)

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Daniel Polski
Gesendet: Donnerstag, 13. Oktober 2016 12:03
An: SQLite mailing list 
Betreff: Re: [sqlite] Trigger WHEN condition, comparing with null

Thank you, I see.
Any suggestion how to get the trigger to fire if (and only if) the values are 
different, including if one of the "sides" of old/new are null?

Den 2016-10-13 kl. 11:56, skrev Kees Nuyt:
> On Thu, 13 Oct 2016 11:45:14 +0200, Daniel Polski
>  wrote:
>
> That's because the expression in the WHEN clause
>   (new.value != old.value)
> is not valid when one of them is NULL.
>
> HTH
>

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] Trigger WHEN condition, comparing with null

[Clemens Ladisch]

Daniel Polski wrote:
> Any suggestion how to get the trigger to fire if (and only if) the
> values are different, including if one of the "sides" of old/new are
> null?

Replace != with IS NOT.


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

Re: [sqlite] Trigger WHEN condition, comparing with null

[Daniel Polski]

Thank you, I see.
Any suggestion how to get the trigger to fire if (and only if) the 
values are different, including if one of the "sides" of old/new are null?


Den 2016-10-13 kl. 11:56, skrev Kees Nuyt:

On Thu, 13 Oct 2016 11:45:14 +0200, Daniel Polski
 wrote:

That's because the expression in the WHEN clause
(new.value != old.value)
is not valid when one of them is NULL.

HTH



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

Re: [sqlite] Trigger WHEN condition, comparing with null

[Kees Nuyt]

On Thu, 13 Oct 2016 11:45:14 +0200, Daniel Polski
 wrote:

That's because the expression in the WHEN clause
(new.value != old.value)
is not valid when one of them is NULL.

HTH

-- 
Regards,
Kees Nuyt



>Hello,
>I guess there is something I'm missing when trying to synchronize some 
>data with a trigger.
>How do I get the trigger to fire when the comparison in either new.x or 
>old.x is null?
>
>The below tested with 3.8.6 sqlite command line shell:
>
>CREATE TABLE table1(
> idINTEGER PRIMARY KEY,
> valueINT
>);
>
>CREATE TABLE table1_mirror(
> idINT,
> valueINT
>);
>
>CREATE TRIGGER trigger_1
>AFTER UPDATE OF value ON table1
>WHEN (new.value != old.value)
>BEGIN
> UPDATE table1_mirror
> SET value = new.value
> WHERE id = new.id
> ;
>END;
>
>INSERT INTO table1 VALUES(1, 1);
>INSERT INTO table1_mirror VALUES(1,1);
>
>UPDATE table1 SET value = 2 WHERE id = 1;
>SELECT value from table1; -- 2
>SELECT value from table1_mirror; -- 2
>
>UPDATE table1 SET value = 3 WHERE id = 1;
>SELECT value from table1; -- 3
>SELECT value from table1_mirror; -- 3
>
>UPDATE table1 SET value = null WHERE id = 1;
>SELECT value from table1; -- null
>SELECT value from table1_mirror; -- still 3 , why not null?
>
>UPDATE table1 SET value = 4 WHERE id = 1;
>SELECT value from table1; -- 4
>SELECT value from table1_mirror; -- still 3 , why not 4?
>
>UPDATE table1 SET value = 5 WHERE id = 1;
>SELECT value from table1; --5
>SELECT value from table1_mirror; --5
>
>
>___
>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] Trigger WHEN condition, comparing with null

[Daniel Polski]

Hello,
I guess there is something I'm missing when trying to synchronize some 
data with a trigger.
How do I get the trigger to fire when the comparison in either new.x or 
old.x is null?


The below tested with 3.8.6 sqlite command line shell:

CREATE TABLE table1(
idINTEGER PRIMARY KEY,
valueINT
);

CREATE TABLE table1_mirror(
idINT,
valueINT
);

CREATE TRIGGER trigger_1
AFTER UPDATE OF value ON table1
WHEN (new.value != old.value)
BEGIN
UPDATE table1_mirror
SET value = new.value
WHERE id = new.id
;
END;

INSERT INTO table1 VALUES(1, 1);
INSERT INTO table1_mirror VALUES(1,1);

UPDATE table1 SET value = 2 WHERE id = 1;
SELECT value from table1; -- 2
SELECT value from table1_mirror; -- 2

UPDATE table1 SET value = 3 WHERE id = 1;
SELECT value from table1; -- 3
SELECT value from table1_mirror; -- 3

UPDATE table1 SET value = null WHERE id = 1;
SELECT value from table1; -- null
SELECT value from table1_mirror; -- still 3 , why not null?

UPDATE table1 SET value = 4 WHERE id = 1;
SELECT value from table1; -- 4
SELECT value from table1_mirror; -- still 3 , why not 4?

UPDATE table1 SET value = 5 WHERE id = 1;
SELECT value from table1; --5
SELECT value from table1_mirror; --5


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