[sqlite] Lemon-generated parser gives an assertion failure

[Kelvin Sherlock]

The test case can be simplified to:

bug.lemon:
---

%include {
#include 
#include 
#include 
#include "bug.h"
}
%code {
int main(void) {

void *pParser;

pParser = ParseAlloc(malloc);
if (!pParser) {
printf("out of memory\n");
exit(1);
}

ParseTrace(stderr, "Debug: ");

Parse(pParser, X, 0);
Parse(pParser, 0, 0);
ParseFree(pParser, free);
return 0;
}
}


main ::= decls.
decls ::= . 
decls ::= decls decl.

decl ::= X

—

which generates this code:

  default:
  /* (0) main ::= decls */ yytestcase(yyruleno==0);
  /* (1) decls ::= (OPTIMIZED OUT) */ assert(yyruleno!=1);
break;

I believe the issue is line 4164 which should be … = LEMON_TRUE.  Currently, 
when i=0, all rules will erroneously be optimized out.

  4157/* Mark rules that are actually used for reduce actions after all
  4158** optimizations have been applied
  4159*/
  4160for(rp=lemp->rule; rp; rp=rp->next) rp->doesReduce = LEMON_FALSE;
  4161for(i=0; inxstate; i++){
  4162  for(ap=lemp->sorted[i]->ap; ap; ap=ap->next){
  4163if( ap->type==REDUCE || ap->type==SHIFTREDUCE ){
  4164  ap->x.rp->doesReduce = i;
  4165}
  4166  }
  4167}

Kelvin Sherlock

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

Re: [sqlite] Lemon-generated parser gives an assertion failure

[Richard Hipp]

On 4/25/17, Kelvin Sherlock  wrote:
>
> I believe the issue is line 4164 which should be … = LEMON_TRUE.  Currently,
> when i=0, all rules will erroneously be optimized out.
>
>   4157/* Mark rules that are actually used for reduce actions after all
>   4158** optimizations have been applied
>   4159*/
>   4160for(rp=lemp->rule; rp; rp=rp->next) rp->doesReduce = LEMON_FALSE;
>   4161for(i=0; inxstate; i++){
>   4162  for(ap=lemp->sorted[i]->ap; ap; ap=ap->next){
>   4163if( ap->type==REDUCE || ap->type==SHIFTREDUCE ){
>   4164  ap->x.rp->doesReduce = i;
>   4165}
>   4166  }
>   4167}
>

Well done.  I fear that you have had to learn more about Lemon and
LALR(1) parsing than perhaps you really ever wanted to know.  Your
efforts are greatly appreciated.
-- 
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] RTree and foreign keys

[David Raymond]

Try as I might, I can't seem to get RTrees and foreign keys to play nicely with 
each other. With a little digging it looks like this is a general issue with 
the virtual nature of virtual tables in general.

Are foreign keys at all possible with virtual tables like this?

And if so, might it be a potential future enhancement for RTree to be able to 
specify that?

Something like

create table bulky_geometry_table(
uuid primary key,
indexID integer unique,
bulkyGeoData blob
);
create virtual table geo_index using rtree(
geo_id references bulky_geometry_table (indexID)
on delete cascade,
minLat, maxLat, minLon, maxLon
);

Or would that be considered not worth the effort?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RTree and foreign keys

[Richard Hipp]

On 4/25/17, David Raymond  wrote:
>
> Are foreign keys at all possible with virtual tables like this?
>

Foreign key constraints are possible on virtual tables, but it is up
to the various virtual table implementations to implement them, and
R-Tree does not.
-- 
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] Performances and Foreign keys

[Bubu Bubu]

Ok, I'll test with and without FKs. As you said, since one can turn them on
and off, maybe there is something to do there.

Thanks for your answer

2017-04-24 14:52 GMT+02:00 Dominique Devienne :

> On Mon, Apr 24, 2017 at 2:31 PM, Bubu Bubu  wrote:
>
> > Foreign keys have been implemented in sqlite since 3.6.19. My boss has
> > always been reluctant to use this mechanism in our development under the
> > pretext of performance loss. He told me he read that somewhere once, but
> he
> > can't recall precisely the reasons that lead him think that.
> >
> > I've read 3.6.19 release note and try to find info that cover that issue
> > without finding any.
> >
> > Can someone tell me if there can really be performance issues when one
> uses
> > foreign keys in their database?
> >
>
> There's always a cost to FKs, since inserting a child row implies checking
> the PK
> of the parent row(s) existence, a check that's not necessary if FKs are
> disabled.
>
> You can't make it faster than not doing it at all. That overhead really
> depends on
> what you value most, performance at all cost, or enforced data integrity.
>
> SQLite is flexible, with FKs disabled by default. So always design with
> FKs, but
> do not necessarily always turn them ON. You get your as-fast-as-possible
> inserts,
> when it's OFF, but you can at least check integrity "after-the-fact" via
> pragmas.
>
> I often decide myself data integrity is more important than speed, but
> that's
> a personal trade-off. Measure your perf with FKs both ON and OFF, and see
> for yourself.
>
> One caveats of FKs ON is with deletes, if you don't index your child FK
> columns,
> and you have ON DELETE CASCADE, because that implies a FULL SCAN of
> child tables (to locate child rows that need deleting when the parent gets
> deleted).
> But there's a new way to find those, in the official shell I think.
> (or is that part of the new optimize pragma?). --DD
> ___
> 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] Build error in SQLite 3.18 if SQLITE_UNTESTABLE is defined

[Priebe, Sebastian]

Hello,

shell.c fails to build with:
shell.c: In function 'do_meta_command':
shell.c:6442:50: error: 'sql_trace_callback' undeclared (first use in this 
function)
   sqlite3_trace_v2(p->db, SQLITE_TRACE_STMT, 
sql_trace_callback,p->traceOut);
  ^
shell.c:6442:50: note: each undeclared identifier is reported only once for 
each function it appears in
make: *** [sqlite3-shell.o] Error 1

Looking at the source code: the function sql_trace_callback is only available 
if SQLITE_UNTESTABLE is not defined, but at the place it is used there is no 
such restiction.

Greetings
Sebastian




--
 +++ CADCON ist Mitglied der SII Group +++
Mehr auf unserer neuen Homepage > www.cadcon.de
---

CADCON
Ingenieurgesellschaft mbH & Co. KG
Geschaeftsfuehrer: Robert Bauer
Sitz der Gesellschaft: 86368 Gersthofen
Registergericht: Amtsgericht Augsburg HRA 14521

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

[sqlite] SQLITE AIX Driver

[Periasamy, Rathnakumar (Larsen & Toubro Infotech Ltd.)]

Team,

I would like to know whether do we have a sqlite ODBC driver for AIX which has 
level 2 or above. We like to use the driver to connect the sqlite through 
Informatica.


I have searched across the net but I don't fine one for AIX. I appreciates your 
reply.

Thanks & Regards
Rathnakumar P
Cell: +1 918 815 3229
Phone: 918 977 5190

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

Re: [sqlite] Performances and Foreign keys

[Bubu Bubu]

All of your feedbacks are really insightful, I deeply thank you guys, I'm
meeting with my boss later with all that stuff to discuss about. Great
community and thanks again

2017-04-24 18:58 GMT+02:00 David Raymond :

> Another situational space/speed helper I found for when you have a
> sparsely populated foreign key field is that the child key index can be a
> partial index and still work.
>
> create table child (id primary key, parentID references parent,
> stuff);--parentID is nullable
>
> I had cases where parentID would be populated only about 10% of the time
> or less and didn't like knowing I had an index 90% full of nulls. We found
> out that a partial index still works:
>
> create index childOfParentNotNull on child (parentID) where parentID is
> not null;
> --still used by the foreign key checker, 10% the size, quicker lookups etc.
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of John Found
> Sent: Monday, April 24, 2017 12:24 PM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Performances and Foreign keys
>
> Simply make some tests?
>
> The experiment is the only measure for the truth.
>
> Personally I am using SQLite for my project AsmBB (web forum software in
> assembly language). The project widely uses FK in order to provide DB
> consistency.
>
> It was tested during several "slashdot effect" kind of events and
> demonstrated very high performance.
>
> So, in my experience using foreign keys does not degrade the performance
> noticeably.
>
> Regards.
>
>
> On Mon, 24 Apr 2017 14:31:32 +0200
> Bubu Bubu  wrote:
>
> > Hi everyone,
> >
> > Foreign keys have been implemented in sqlite since 3.6.19. My boss has
> > always been reluctant to use this mechanism in our development under the
> > pretext of performance loss. He told me he read that somewhere once, but
> he
> > can't recall precisely the reasons that lead him think that.
> >
> > I've read 3.6.19 release note and try to find info that cover that issue
> > without finding any.
> >
> > Can someone tell me if there can really be performance issues when one
> uses
> > foreign keys in their database?
> >
> > Thanks a lot
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> --
> http://fresh.flatassembler.net
> http://asm32.info
> John Found 
> ___
> 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

Re: [sqlite] Build error in SQLite 3.18 if SQLITE_UNTESTABLE is defined

[Richard Hipp]

I'm curious:  Why do you want to make SQLite untestable?

On 4/24/17, Priebe, Sebastian  wrote:
> Hello,
>
> shell.c fails to build with:
> shell.c: In function 'do_meta_command':
> shell.c:6442:50: error: 'sql_trace_callback' undeclared (first use in this
> function)
>sqlite3_trace_v2(p->db, SQLITE_TRACE_STMT,
> sql_trace_callback,p->traceOut);
>   ^
> shell.c:6442:50: note: each undeclared identifier is reported only once for
> each function it appears in
> make: *** [sqlite3-shell.o] Error 1
>
> Looking at the source code: the function sql_trace_callback is only
> available if SQLITE_UNTESTABLE is not defined, but at the place it is used
> there is no such restiction.
>
> Greetings
> Sebastian
>
>
>
>
> --
>  +++ CADCON ist Mitglied der SII Group +++
> Mehr auf unserer neuen Homepage > www.cadcon.de
> ---
>
> CADCON
> Ingenieurgesellschaft mbH & Co. KG
> Geschaeftsfuehrer: Robert Bauer
> Sitz der Gesellschaft: 86368 Gersthofen
> Registergericht: Amtsgericht Augsburg HRA 14521
>
> ___
> 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] SQLITE AIX Driver

[Richard Hipp]

AIX is an obscure platform these days. Probably you will need to get
the source code to an ODBC driver and compile it yourself.

On 4/24/17, Periasamy, Rathnakumar (Larsen & Toubro Infotech Ltd.)
 wrote:
> Team,
>
> I would like to know whether do we have a sqlite ODBC driver for AIX which
> has level 2 or above. We like to use the driver to connect the sqlite
> through Informatica.
>
>
> I have searched across the net but I don't fine one for AIX. I appreciates
> your reply.
>
> Thanks & Regards
> Rathnakumar P
> Cell: +1 918 815 3229
> Phone: 918 977 5190
>
> ___
> 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] 0x11C: automatic index warning for CTEs ?

[Clemens Ladisch]

Howard Kapustein wrote:
>> you might have to change the query itself
>
> The question is how?

The database has estimated that even with the cost of creating the
temporary index, this way is the fastest.  So it's unlikely that there
is another way that would be an improvement.

Anyway, try using the CTE on the left side of a CROSS JOIN to force the
DB to use it as the outer table of the nested loop:
http://www.sqlite.org/lang_select.html#crossjoin

> Right now my log's being spammed with oodles of automatic index
> messages because of the CTE.

You could disabling logging for this query, or filter out these specific
messages.


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