[sqlite] Lemon: Simple recursive rule causes assertion failed: stateno <= YY_SHIFT_COUNT

[Kelvin Sherlock]

This lemon bug was reported about 6 months ago:


8<

%include {
#include 
#include 
#include 
#include "lemon-bug.h"
}

%code {
int main()
{
void *pParser;

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

ParseTrace(stderr, "Debug: ");

Parse(pParser, CMDNAME, 0);
Parse(pParser, INTEGER, 0);
Parse(pParser, TEXT, 0);
Parse(pParser, EOL, 0);

Parse(pParser, CMDNAME, 0);
Parse(pParser, INTEGER, 0);
Parse(pParser, TEXT, 0);
Parse(pParser, EOL, 0);

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


database ::= entrylist.

entrylist ::= command.
entrylist ::= entrylist command.

command ::= CMDNAME cmdargs EOL. 
cmdargs ::= .
cmdargs ::= cmdargs cmdarg.

cmdarg ::= INTEGER.
cmdarg ::= TEXT.


8<


./lemon-bug 
Debug: Input 'CMDNAME'
Debug: Shift 'CMDNAME', go to state 3
Debug: Return. Stack=[CMDNAME]
Debug: Input 'INTEGER'
Assertion failed: (stateno <= YY_SHIFT_COUNT), function yy_find_shift_action, 
file lemon-bug.c, line 512.
Abort trap: 6

which generates this code:

#define YY_MAX_SHIFT 3
#define YY_SHIFT_COUNT(2)
#define YY_SHIFT_USE_DFLT (13)
static const unsigned char yy_shift_ofst[] = {
 /* 0 */ 7,1,6,
};
…

  assert( stateno <= YY_SHIFT_COUNT );

without the shift table compression -- lemon.c line 4235:
while( n>0 && lemp->sorted[n-1]->iTknOfst==NO_OFFSET ) n—;

it will generate this:

#define YY_SHIFT_COUNT(3)
static const unsigned char yy_shift_ofst[] = {
 /* 0 */ 7,1,6,   13,
};

and the assert doesn’t fail.

Most of the time (and in the case of SQLite parse.y) the shift table won’t 
compress and YY_MAX_SHIFT == YY_SHIFT_COUNT.
Given that, it probably shouldn’t try to compress the shift table.

Kelvin Sherlock



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

Re: [sqlite] Security vulnerabilities prevalent in web tutorials for PHP, javascript, etc..

[James K. Lowden]

On Sun, 23 Apr 2017 21:31:42 +0100
Simon Slavin  wrote:

> If you don?t understand what you?re doing, hire an experienced
> programmer.  

Ah, but you don't know what you don't know.  After all, 90% of
programmers rate themselves "above average".  

When I first heard of "SQL injection" years ago, I started looking into
it, of course.  Every single one I read about could have been prevented
by following two simple, well known rules:

1.  Every database access must be through stored procedures. 

2.  The process accessing the database must have no rights to
the database except through stored procedures.  

(SQLite can't provide the same degree of protection because it doesn't
offer process separation.  That makes it inappropriate for some
applications.  OK.)

For SQL injection to be a problem requires the whole technical
organization to neglect to protect the data.  That criminals try to
steal data is no surprise.  That so-called professionals abet them
through neglect borders on malfeasance.  

--jkl
___
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 ?

[Keith Medcalf]

Change UNION ALL to UNION.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Howard Kapustein
> Sent: Monday, 24 April, 2017 15:46
> To: SQLite mailing list
> Subject: Re: [sqlite] 0x11C: automatic index warning for CTEs ?
> 
> >but for CTEs/views/subqueries, you might have to change the query itself,
> or the database schema, or determine that the automatic index is the best
> choice in your situation.
> 
> Yes. The question is how?
> 
> The CTE's doing a SELECT _PackageID FROM Package where _PackageID is a
> primary key. If I change the CTE to SELECT rowid FROM Package would SQLite
> still think an automatic index is needed? Or is the automatic index for
> the CTE itself? I can only declare the PackageIdByAll CTE has results but
> no type info e.g.
>   WITH PackageIdByAll(_PackageID PRIMARY KEY NOT NULL)
> isn't legal.
> 
> Right now my log's being spammed with oodles of automatic index messages
> because of the CTE. I'd like to *fix* this, but right now the only
> solution I'm seeing is suppressing all automatic index log messages. I'd
> like to still see REAL ones to not mask new issues.
> 
> Suggestions?
> 
> 
> 
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Clemens Ladisch
> Sent: Friday, April 21, 2017 11:13 PM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] 0x11C: automatic index warning for CTEs ?
> 
> Howard Kapustein wrote:
> > I'm setting SQLite logging a warning
> > Warning 0x11C: automatic index on PackageIdByAll(_PackageID)
> >
> > WITH ...
> > PackageIdByAll(_PackageID) AS (...)
> > SELECT *
> > FROM ...
> > INNER JOIN PackageIdByAll AS cte_p ON
> > cte_p._PackageID=pkg._PackageID
> >
> > Why?
> >
> > PackageIdByAll is a CTE. I can't CREATE INDEX for it.
> 
> The warning tells you that the database created an automatic index.
> This does not imply that you _must_ create an index, it's just a hint that
> you should think about whether it is possible to improve the query.
> If you have a table, creating the index explicitly is the easiest way, but
> for CTEs/views/subqueries, you might have to change the query itself, or
> the database schema, or determine that the automatic index is the best
> choice in your situation.
> 
> > _PackageID is the primary key of the Package table So indexing should
> > be covered being the table's ROWID.
> 
> When you use the rowid to look up a table row, you still don't know which
> CTE row that would correspond to.
> 
> 
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglis
> ts.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-
> users=02%7C01%7Choward.kapustein%40microsoft.com%7C97a3efe7e6f7483a93
> ca08d48946c459%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C63628438442085
> 4952=6DPB0%2Bbtibn2aYYms6AILfHztq7gORwmdEq0uYcsaG0%3D=0
> ___
> 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] 0x11C: automatic index warning for CTEs ?

[Howard Kapustein]

>but for CTEs/views/subqueries, you might have to change the query itself, or 
>the database schema, or determine that the automatic index is the best choice 
>in your situation.

Yes. The question is how?

The CTE's doing a SELECT _PackageID FROM Package where _PackageID is a primary 
key. If I change the CTE to SELECT rowid FROM Package would SQLite still think 
an automatic index is needed? Or is the automatic index for the CTE itself? I 
can only declare the PackageIdByAll CTE has results but no type info e.g.
WITH PackageIdByAll(_PackageID PRIMARY KEY NOT NULL)
isn't legal.

Right now my log's being spammed with oodles of automatic index messages 
because of the CTE. I'd like to *fix* this, but right now the only solution I'm 
seeing is suppressing all automatic index log messages. I'd like to still see 
REAL ones to not mask new issues.

Suggestions?



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Clemens Ladisch
Sent: Friday, April 21, 2017 11:13 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] 0x11C: automatic index warning for CTEs ?

Howard Kapustein wrote:
> I'm setting SQLite logging a warning
> Warning 0x11C: automatic index on PackageIdByAll(_PackageID)
>
> WITH ...
> PackageIdByAll(_PackageID) AS (...)
> SELECT *
> FROM ...
> INNER JOIN PackageIdByAll AS cte_p ON 
> cte_p._PackageID=pkg._PackageID
>
> Why?
>
> PackageIdByAll is a CTE. I can't CREATE INDEX for it.

The warning tells you that the database created an automatic index.
This does not imply that you _must_ create an index, it's just a hint that you 
should think about whether it is possible to improve the query.
If you have a table, creating the index explicitly is the easiest way, but for 
CTEs/views/subqueries, you might have to change the query itself, or the 
database schema, or determine that the automatic index is the best choice in 
your situation.

> _PackageID is the primary key of the Package table So indexing should 
> be covered being the table's ROWID.

When you use the rowid to look up a table row, you still don't know which CTE 
row that would correspond to.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users=02%7C01%7Choward.kapustein%40microsoft.com%7C97a3efe7e6f7483a93ca08d48946c459%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636284384420854952=6DPB0%2Bbtibn2aYYms6AILfHztq7gORwmdEq0uYcsaG0%3D=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple sessions, page sizes and cache

[Kim Gräsman]

On Fri, Apr 21, 2017 at 5:18 PM, Kim Gräsman  wrote:
>
> I have a nagging suspicion that the tempdb is allocated from the
> private heap (we've set pragma temp_store=memory) -- what are the
> characteristics of that database? Can I forecast how large it will be?

The behavior is the same with pragma temp_store=file (turns out it was
`file` when this statement ran all along.)

I've tried dumping all available global memory statistics when I run
out of memory, but I don't see much I can use:

SQLite error 7: failed to allocate 1024 bytes
SQLite error 7: failed to allocate 1024 bytes
SQLite error 7: statement aborts at 3: [UPDATE mytable SET ... WHERE ...]
Effective soft heap limit: 22192128 bytes
Memory used 5064704 (max 22535968)
Number of outstanding allocations: 9807 (max 26872)
Number of pcache pages used: 0 (max 0)
Number of pcache overflow bytes: 3239936 (max 3239936)
Number of scratch allocations used: 0 (max 0)
Number of scratch overflow bytes: 0 (max 1024)
Largest allocation: 65540 bytes
Largest pcache allocation: 672 bytes
Largest scratch allocation: 560 bytes

It looks like the highwater for memory-used is when the heap was
depleted. But other than that, I'm stumped. What's a good way to
understand where memory is going in this statement?

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

Re: [sqlite] Best practices for huge batch inserts with WAL?

[Clemens Ladisch]

Scott Hess wrote:
> WRT #3, you could also consider journal_mode off (or memory, if your code
> requires transactions to work right).  In that case, the database state is
> indeterminate if you have an app-level crash, but you should be fine if you
> make it to the end.

It would be a better idea to change to the rollback journal mode (DELETE/
TRUNCATE/PERSIST) - it's guaranteed to work correctly in all situations,
and with a small database to be filled, the journal cannot become large
because it stores only _old_ data.


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

Re: [sqlite] Best practices for huge batch inserts with WAL?

[Scott Hess]

What I'd do:

1) Look at the indices, and make sure the input is sorted to insert in
index order.  Also drop any unnecessary indices and add them back at the
end.  [Read the code for vacuum to see what kinds of things make sense to
defer.]

2) Bump up the cache a lot.  Inserting in sorted order makes this less
essential, but for a one-off like this it can't hurt.

3) Turn synchronous off while loading, and commit to starting over if you
get an OS-level crash.  Even with synchronous off, things should be
correctly ordered for app-level crashes.

4) Maybe use exclusive locking?  That wouldn't have a huge impact if you're
batching inserts, I expect.

---

WRT #3, you could also consider journal_mode off (or memory, if your code
requires transactions to work right).  In that case, the database state is
indeterminate if you have an app-level crash, but you should be fine if you
make it to the end.

WRT #1, I would consider fts as an index for these purposes, but it may
require schema changes to make is possible to selectively disable/enable
the indexing.  See https://sqlite.org/fts5.html#external_content_tables .
I'm not sure there would be much gain from disabling fts when loading,
though, as long as your bulk transactions are large.

-scott


On Mon, Apr 24, 2017 at 11:00 AM, Jens Alfke  wrote:

> I’m importing a large data set with a lot of rows — an entire Wikipedia
> dump, about 60GB, one article per row — into a brand new SQLite database in
> WAL mode. What’s the fastest way to import it?
>
> I started with one big transaction, but noted that (of course) the WAL
> file was growing rapidly while the main database file stayed tiny. I
> figured this would become inefficient, so I stopped the run and adjusted my
> code to commit and re-open a transaction every 10,000 rows.
>
> With that, the import started quickly, but as time went on the commits
> were taking longer and longer, so the process was spending most of its time
> committing. (I wasn’t able to finish the job, as it ran into an unrelated
> fatal error in my code about ⅔ of the way through.)
>
> Would it have been faster to use a single transaction? Even if the commit
> at the end is epic-length, it wouldn’t be rewriting the b-tree nodes over
> and over again. If so, would periodic WAL checkpoints help?
>
> —Jens
> ___
> 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] Best practices for huge batch inserts with WAL?

[John Found]

Do you have some indices on the table? Drop them before the huge insert and 
then recreate again. It is faster.

On Mon, 24 Apr 2017 11:00:00 -0700
Jens Alfke  wrote:

> I’m importing a large data set with a lot of rows — an entire Wikipedia dump, 
> about 60GB, one article per row — into a brand new SQLite database in WAL 
> mode. What’s the fastest way to import it?
> 
> I started with one big transaction, but noted that (of course) the WAL file 
> was growing rapidly while the main database file stayed tiny. I figured this 
> would become inefficient, so I stopped the run and adjusted my code to commit 
> and re-open a transaction every 10,000 rows.
> 
> With that, the import started quickly, but as time went on the commits were 
> taking longer and longer, so the process was spending most of its time 
> committing. (I wasn’t able to finish the job, as it ran into an unrelated 
> fatal error in my code about ⅔ of the way through.)
> 
> Would it have been faster to use a single transaction? Even if the commit at 
> the end is epic-length, it wouldn’t be rewriting the b-tree nodes over and 
> over again. If so, would periodic WAL checkpoints help?
> 
> —Jens
> ___
> 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] Best practices for huge batch inserts with WAL?

[Jens Alfke]

I’m importing a large data set with a lot of rows — an entire Wikipedia dump, 
about 60GB, one article per row — into a brand new SQLite database in WAL mode. 
What’s the fastest way to import it?

I started with one big transaction, but noted that (of course) the WAL file was 
growing rapidly while the main database file stayed tiny. I figured this would 
become inefficient, so I stopped the run and adjusted my code to commit and 
re-open a transaction every 10,000 rows.

With that, the import started quickly, but as time went on the commits were 
taking longer and longer, so the process was spending most of its time 
committing. (I wasn’t able to finish the job, as it ran into an unrelated fatal 
error in my code about ⅔ of the way through.)

Would it have been faster to use a single transaction? Even if the commit at 
the end is epic-length, it wouldn’t be rewriting the b-tree nodes over and over 
again. If so, would periodic WAL checkpoints help?

—Jens
___
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

[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

Re: [sqlite] Performances and Foreign keys

[John Found]

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

Re: [sqlite] Performances and Foreign keys

[Simon Slavin]

On 24 Apr 2017, at 3:20pm, Clemens Ladisch  wrote:

> Bubu Bubu wrote:
>> Can someone tell me if there can really be performance issues when one uses
>> foreign keys in their database?
> 
> The documentation 
> warns against a case where some operations can be slow if you do not
> have an index on the child key.

Yeah.  To do FOREIGN KEYs with speed you need two indexes: a UNIQUE one on the 
parent key columns and one on the child key columns.  To understand why you 
need the one on the child table, consider what should happen if you delete a 
row in the parent table.

> But if you have all suggested indexes, then all the checks done to
> enforce (foreign key) constraints are simple index lookups.  These are
> mostly harmless, unless the number of rows you are changing is so large
> that all the individual operations add up to something noticeable.
> (If you predict that that happens, you can simply disable foreign key
> constraint checks.)

Right.  As this post and others have mentioned, there will be a small increase 
in time taken, but this increase is tiny compared to any other way of ensuring 
integrity.  FOREIGN KEYs really are the most efficient way to do this.

Simon.
___
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

[Clemens Ladisch]

Bubu Bubu wrote:
> Can someone tell me if there can really be performance issues when one uses
> foreign keys in their database?

The documentation 
warns against a case where some operations can be slow if you do not
have an index on the child key.

But if you have all suggested indexes, then all the checks done to
enforce (foreign key) constraints are simple index lookups.  These are
mostly harmless, unless the number of rows you are changing is so large
that all the individual operations add up to something noticeable.
(If you predict that that happens, you can simply disable foreign key
constraint checks.)


Regards,
Clemens
___
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

[R Smith]

On 2017/04/24 2:31 PM, 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?


YES, But...

That very much depends on what you mean by "performance issues". Does it 
come at a processing cost? Yes of course, like everything else, but is 
very cheap by comparison.


For everything in life that you do, you pay a cost in energy. Whether 
you simply look to your left - that costs energy - but you may not mind 
that or worry about it, and so can look both left and right often 
without fearing the energy it saps from you - plus it becomes 
ridiculously cheap when you consider the benefits, especially when 
crossing busy roads.


A foreign key is simply a data integrity tool, Set-theory doesn't demand 
it - not all data are equal, some may justify it, others may not. (I 
certainly don't always use them).


I think I can safely state that any performance degradation due to a 
foreign-key check pales in comparison to the advantages in the cases 
where they are needed. To put this into perspective, SQLite has improved 
in speed for bog-standard database operations since 3.6.19 by a factor 
that completely dwarfs any speed-penalty that a foreign-key check might 
impose. Another point that might be pertinent is that lots of DBs all 
over the world use foreign keys - without much shunning it for 
performance problems (which is why the lack of google results - though 
there are some).


Let me caution about one situation that do need special consideration. A 
foreign key constraint requires a unique index (because you can't have a 
relation to a plural item) and to maintain a unique index on a really 
large table (high record count) *IS* actually somewhat expensive and 
adds to the overhead of checking the key on most operations, and if that 
index is not useful for anything other than to maintain the foreign key 
relation, then perhaps you may need to reconsider - but that is a very 
remote case.


Also, if you do experience very slow inserts, you could turn the foreign 
keys off in most DBs and run large inserts without them, though that 
obviates the point a bit. That said, gratuitously adding hundreds of 
foreign keys is also bad. It's like everything in life - Use it if it 
serves the purpose, but use only where needed and only as much as is needed.


I realize the above is not very specific, but it's really hard to give a 
precise answer on a value-based concept. Some people simply won't wear a 
seat-belt because it is too much effort - others would wear it even if 
took half-an-hour to strap into. It's the same with foreign keys.
I would say this though: Foreign keys work fine; Your debates with your 
boss should be about when to use them and when not based on the 
system/data needs - It shouldn't be about whether the "foreign keys" 
functionality itself is a valid tool or not.



Pardon my rattling on a bit...
Good luck :)
Ryan

___
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

[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

Re: [sqlite] Security vulnerabilities prevalent in web tutorials for PHP, javascript, etc..

[Dominique Devienne]

On Mon, Apr 24, 2017 at 3:33 AM, Keith Medcalf  wrote:

> > I’m curious about binding as an idea. [...]
> [...] The EXEC SQL interface has all but disappeared in most languages
> [...]


Oracle still supports https://en.wikipedia.org/wiki/Pro*C
but that's pure client-side, while it seems your early EXEC SQL
"precompilers"
were both client-side *and* server-side.

An advantage of Pro*C is that it checks at pre-compilation-time the SQL text
against the schema of the database. But that's also it's downfall, since no
one
wants to ties its CI and builds in general to contacting an Oracle
database, nor
is it necessarily practical to have the schema your targeting instantiated
server-side.

A big inconvenience is that other software tools (like IDEs, linters,
etc...) don't like
"embedded DSLs" in a "foreign language" compared to the "host" language.
--DD

PS: I write the above, but then my CI runs unit tests against Oracle, so
that's not that different :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Performances and Foreign keys

[Bubu Bubu]

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

Re: [sqlite] Security vulnerabilities prevalent in web tutorials for PHP, javascript, etc..

[Simon Slavin]

On 24 Apr 2017, at 2:33am, Keith Medcalf  wrote:

> [history]

That’s very interesting.  I’m remembering the first DBMS language I used on 
desktop computers rather than mainframes or minis.  It was something called 
"Q-Pro 4" and included both database commands and user interface commands.  
Just as you describe Sybase, there was no opportunity for injection because 
there was no way to construct a database command from a string.  Variables 
could lead only to values for storage and searching, not to entire commands.

The company used Q-Pro 4 for financial programs for years without problems.  We 
had only one hacking attempt by an employee of one of our customers.  It was 
initially successful because the database files stored data in a very simple 
all-text structure.  It was found out because the hacker didn’t update the 
accompanying index files and a few days after the hack the program complained 
about a corrupt index.  Had the hacker known, he could have had the software 
update each index file with one simple command for each one, but he didn’t have 
the language documentation or take the time to explore how it worked.  
Alternatively he could have written a little Q-Pro program to do the updating 
instead of (we guessed) using a text editor.

And that, folks, was the beginning of my interest in computer security.

Injection vulnerabilities had to wait for free form English-like database 
commands.  Not certain when that was.  I’m betting that SQL wasn’t the first 
language described that way, but I don’t remember what was.  Certainly, 
problems like

execute ("UPDATE accounts SET phone = '$p'")

$p <-- "5551234"
—> UPDATE accounts SET phone = '5551234'

$p <-- "5551234', balance = '99.99"
—> UPDATE accounts SET phone = '5551234', balance = '99.99'

didn’t trouble me until SQL appeared.

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