Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all
You need a UNIQUE index on the PARENT KEY because when you operate on a child, you need to be able to look up the PARENT. If there is no index on the PARENT KEY then you have to do a table scan. A table scan of a billion parent records many take quite some time. Similarly, you need an index on the CHILD KEY because when you operate on a PARENT record, you need to be able to look up the CHILD. If there is no index on the CHILD KEY then you have to do a table scan. A table scan of a billion child records may take quite some time. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: Yuri [mailto:y...@rawbw.com] >Sent: Thursday, 2 August, 2018 17:06 >To: SQLite mailing list; Keith Medcalf >Subject: Re: [sqlite] Violated failed foreign key constraint delays >the rest of transaction ; Some foreign key violations don't trigger >the error at all > >On 8/2/18 3:46 PM, Keith Medcalf wrote: >> You are required to have a UNIQUE index on the PARENT KEYS in a >foreign key relationship. >> >> Depending on whether the PARENT:CHILD is 1:1 or 1:N you need either >a UNIQUE (1:1) or regular index on the child key. > > >Why is index on PARENT KEY in a foreign key relationship required for >inserts? Missing index should slow down deletion of the target record >in >FK, but insertions shouldn't need checking if such parent key is >already >present or not. Insertion in the parent part of FK checks if the >target >exists or not. If it exists, insertion succeeds, if not, it fails. It >doesn't need to check if another parent key already exists. > > >Yuri > > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all
Hello Yuri, Friday, August 03, 2018, 12:06:14 AM, Yuri wrote: > On 8/2/18 3:46 PM, Keith Medcalf wrote: >> You are required to have a UNIQUE index on the PARENT KEYS in a foreign key >> relationship. >> >> Depending on whether the PARENT:CHILD is 1:1 or 1:N you need either >> a UNIQUE (1:1) or regular index on the child key. > Why is index on PARENT KEY in a foreign key relationship required for > inserts? Missing index should slow down deletion of the target record in > FK, but insertions shouldn't need checking if such parent key is already > present or not. Insertion in the parent part of FK checks if the target > exists or not. If it exists, insertion succeeds, if not, it fails. It > doesn't need to check if another parent key already exists. > Yuri I believe David Raymond explained this: with deferred foreign key checks, once there has been a violation, it not only needs to check whether a new row _breaks_ a constraint, it needs to check whether the new row _fixes_ a previous violation. For the latter, while you don't _need_ the index Keith is suggesting, it prevents the slow-down you are seeing. Graham ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all
On 8/2/18 3:46 PM, Keith Medcalf wrote: You are required to have a UNIQUE index on the PARENT KEYS in a foreign key relationship. Depending on whether the PARENT:CHILD is 1:1 or 1:N you need either a UNIQUE (1:1) or regular index on the child key. Why is index on PARENT KEY in a foreign key relationship required for inserts? Missing index should slow down deletion of the target record in FK, but insertions shouldn't need checking if such parent key is already present or not. Insertion in the parent part of FK checks if the target exists or not. If it exists, insertion succeeds, if not, it fails. It doesn't need to check if another parent key already exists. Yuri ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all
It is. If you create the missing index then your "testcase" does not demonstrate any slowdown. After this line: doSql(db, "create table a (id int PRIMARY KEY, name text, aid int not null, FOREIGN KEY(aid) REFERENCES a(id))"); insert this line: doSql(db, "create index aid on a (aid)"); All your problems will vanish. You are required to have a UNIQUE index on the PARENT KEYS in a foreign key relationship. Depending on whether the PARENT:CHILD is 1:1 or 1:N you need either a UNIQUE (1:1) or regular index on the child key. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: Yuri [mailto:y...@rawbw.com] >Sent: Thursday, 2 August, 2018 16:37 >To: SQLite mailing list; Keith Medcalf >Subject: Re: [sqlite] Violated failed foreign key constraint delays >the rest of transaction ; Some foreign key violations don't trigger >the error at all > >On 8/2/18 3:17 PM, Keith Medcalf wrote: >> .lint fkey-indexes >> >> and it will tell you what indexes you forgot to create that cause >the issue you are seeing. > > >But this problem isn't about a missing index. > > >Yuri > > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all
On 8/2/18 3:17 PM, Keith Medcalf wrote: .lint fkey-indexes and it will tell you what indexes you forgot to create that cause the issue you are seeing. But this problem isn't about a missing index. Yuri ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all
Many versions ago a CLI command (that is, the sqlite3 Command Line Interface) was created so that folks would stop complaining about referential integrity enforcement being slow when they did not create the indexes that were necessary to enforce referential integrity (because failing to have the appropriate indexes means that a table scan is required, rather than a simple B-Tree index operation, and this is VERY slow, especially for non-trivially sized tables (meaning more than the number of rows that can be counted on one hand)). This command is: .lint fkey-indexes and it will tell you what indexes you forgot to create that cause the issue you are seeing. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Yuri >Sent: Thursday, 2 August, 2018 16:05 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Violated failed foreign key constraint delays >the rest of transaction ; Some foreign key violations don't trigger >the error at all > >On 8/2/18 7:02 AM, David Raymond wrote: >> So for your test script there, try inserting a record with the >violation ID a little bit later and see if it suddenly speeds up >again. In my Python version of your script it does indeed speed back >up again once the outstanding violation is fixed. > > >The main problem is that this bug makes it difficult to handle bugs >in >our code. A bug causing the FK violation automatically wastes a lot >of >time before being detected, because the slowdown is in the range of >1000X. FK violations aren't out of the ordinary or abnormal, they are >a >valid outcome of some queries, and should be handled reasonably. > > >Yuri > > >___ >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] Common index for multiple databases
Well, ok, but it is not really creating an index drawing data from multiple tables. The table definition part "interleaves" data with the same value for the same column into a "cluster" of pages, and the creation of the index on the cluster is an index of the "common column data". Creating an index from "multiple databases" would be something like: create table a(a ...); create table b(b ...); create index blah on multisources (a.a, b.b); Since there is no syncronized motion between the two tables a and b, the index is impossible to maintain, except perhaps in a navigational database and then only through a linked to owner set, and only for changes to the right-hand table in the set. You can only create an index drawing data from a single database object. In oracle's case, that "single object" is a cluster of tables where "some one column" contains linkage data between the two tables. That is, it is a hybrid of a relation and a network extended set index. That is one table in the cluster contains a "linked list" of the other table rows having the same value for the cluster column (a LINK TO PRIOR LINK TO OWNER set) and the index is created on the column of the first table (the parent or left-hand table) in the cluster (set). That the various rows of the various tables are stored in relative proximity to each other (in order to reduce I/O) is a nice implementation detail but irrelevant (and it depends on the insertion order of the table rows or requires a re-org to get the rows interleaved properly). --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Jean-Luc Hainaut >Sent: Thursday, 2 August, 2018 15:04 >To: SQLite mailing list >Subject: Re: [sqlite] Common index for multiple databases > >On 02/08/2018 20:50, Keith Medcalf wrote: >> In no DBMS known can you index data sourced from multiple tables in >the same index -- this applies to "Relational" databases and all >other database models (such as pure hierarchical, network, network >extended, etc.) In all DBMS systems the contents of the index must >be sourced from a single object. > >To the best of my (limited) knowledge, Oracle's CLUSTER is the only >technique allowing an index to reference rows from several tables. >The >rows (from source tables) that share the same value of a column are >collected into a page, if needed complemented by an overflow chain of >pages. Whether the OP is ready to migrate to Oracle is another story! > >J-L Hainaut >___ >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] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all
On 8/2/18 7:02 AM, David Raymond wrote: So for your test script there, try inserting a record with the violation ID a little bit later and see if it suddenly speeds up again. In my Python version of your script it does indeed speed back up again once the outstanding violation is fixed. The main problem is that this bug makes it difficult to handle bugs in our code. A bug causing the FK violation automatically wastes a lot of time before being detected, because the slowdown is in the range of 1000X. FK violations aren't out of the ordinary or abnormal, they are a valid outcome of some queries, and should be handled reasonably. Yuri ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using CTE with date comparison
On 2018/08/02 10:29 PM, Csányi Pál wrote:
Hi,
I just want to know why the following SQLite query does not work as I expected?
WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
date('2016-11-01') ) SELECT max(dateD), count(dateD) FROM dates;
2017-10-03|3
which is not what I am expecting.
I am expecting the followings:
1. the query add to the date('2015-10-03') 1 year which is '2016-10-03'
2. then it compares the two dates: 2016-10-03 with 2016-10-03
3. because 2016-10-03 = 2016-10-03 it count 1
4. then add to the result date 2016-10-03 once again 1 year which is 2017-10-03
5. then it compares the two dates: 2017-10-03 with 2016-10-03
6. because 2017-10-03 > 2016-10-03 it does not count 2
7. it should gives the following result:
2016-10-03|1
What am I doing wrong here?
When the recursive Query starts up, the first value that it outputs is
given by the very first part of the query, namely: VALUES('2015-10-03')
So on the first iteration, it will produce one row like this:
'2015-10-03'
regardless of the rest of the Query. This row is pushed into the
recursion buffer.
After that it then reads a row from the recursion buffer and checks
(within the WHERE clause) whether the value in it (namely: '2015-10-03')
is <= '2016-11-01', and finds that it definitely IS less, so continues
to produce the another line of output.
The output created is that date from the buffer (2015-10-03) which is
put through the given calculation: date(dateD, '+1 year') to give:
'2016-10-03'
It then continues to push that next row into the recursion buffer and
next reads again from it and again checks if it (2016-10-03) is <= than
2016-11-01, which again it is... so it continues to produce the next
output row, which after calculation becomes:
'2017-10-03'
It then continues to push that again into the buffer and again read it
and again checks if it (2017-10-03) is less than 2016-11-01, which THIS
TIME, it isn't... so it stops right there.
So in the end, it has produced 3 output rows namely:
'2015-10-03'
'2016-10-03'
'2017-10-03'
Which is exactly what you've asked for.
Note: The first part of the query will ALWAYS reach the output buffer,
even if it isn't a recursive query, and the UNION is NOT specified, you
will get at least the '2015-10-03' value.
Note: When comparing in the WHERE clause, you do not compare the newly
calculated value (date(xxx, +1 year)), but indeed you compare the
before-calculated value, i.e. the previous value in the buffer (the same
as how your calculation is done on the PREVIOUS value in the buffer to
yield the new date with.
I hope that helps to make sense.
Cheers,
Ryan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common index for multiple databases
On 02/08/2018 20:50, Keith Medcalf wrote: In no DBMS known can you index data sourced from multiple tables in the same index -- this applies to "Relational" databases and all other database models (such as pure hierarchical, network, network extended, etc.) In all DBMS systems the contents of the index must be sourced from a single object. To the best of my (limited) knowledge, Oracle's CLUSTER is the only technique allowing an index to reference rows from several tables. The rows (from source tables) that share the same value of a column are collected into a page, if needed complemented by an overflow chain of pages. Whether the OP is ready to migrate to Oracle is another story! J-L Hainaut ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using CTE with date comparison
Hi,
I just want to know why the following SQLite query does not work as I expected?
WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
date('2016-11-01') ) SELECT max(dateD), count(dateD) FROM dates;
2017-10-03|3
which is not what I am expecting.
I am expecting the followings:
1. the query add to the date('2015-10-03') 1 year which is '2016-10-03'
2. then it compares the two dates: 2016-10-03 with 2016-10-03
3. because 2016-10-03 = 2016-10-03 it count 1
4. then add to the result date 2016-10-03 once again 1 year which is 2017-10-03
5. then it compares the two dates: 2017-10-03 with 2016-10-03
6. because 2017-10-03 > 2016-10-03 it does not count 2
7. it should gives the following result:
2016-10-03|1
What am I doing wrong here?
--
Best, Pali
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common index for multiple databases
On 2 Aug 2018, at 7:44pm, John R. Sowden wrote: > another point that I did not make clear. The accounting programs are not > associated with the technical programs, different people, different security > access. The tech databases and programs are in portable computers that go > out in the field, but not the accounting, etc. There indexes would have to > be updated when the computers are back at the office. That's not how SQLite works. It might help if you forget how FoxPro works and start again. In SQlite, a database file can hold one or more tables. If the database file holds a table, all the indexes of that table are stored in the same file. When the table is updated, all its indexes are updated immediately, as part of the same operation. It's not something that you have to worry about. It happens automatically. If you copy a databbase file from one computer to another, since the indexes are in the same file as the table, the indexes go with it. If you write an application that uses a number of tables, it's normal to put all those tables in the same database file. Copy one file, you have your entire database. Back that one file up, you've backed up your entire database. However, you can have one application access multiple database files at once if you need to. But if you have two tables in different files you cannot use SQL abilities like FOREIGN KEYs to ensure referential integrity. So most people don't do that. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common index for multiple databases
>SQLite3 stores the table (.DBF) and all the indexes associated with >that table in a single file called a database. You can also have >multiple tables in one database (rather than one table per file) and >all the indexes associates with all those tables are stored in the >same database file. You cannot split the table and it's indexes into >multiple files. You can have one table and its indexes stored in a >single file and have a crapload of those files, however, that defeats >the entire purpose of having a Relational Database (ACID) and also >precludes referential integrity enforcement and triggers. Further to this, you can "emulate" the current structure by creating multiple databases each containing only the tables needed for that "bit" of your application. For example, you can create a customers.db containing the customers table and all the indexes associated with the customers table. You can also create an accounting.db containing all the accounting tables (but not the customers table) and all the indexes that belong to those tables. Lather, Rinse, Repeat. Create multiple databases and do not duplicate table names -- that is each table goes in one and only one database). You then "open" a :memory: database and "ATTACH" your other database hunks as required for the application. You refer to the tables only by table name and provided that you have not duplicated tables in multiple database files, then SQLite3 will operate on the appropriate attached database. Since the indexes associated with a table must be stored in the same database file as the data table itself, all the indexes will be kept up-to-date. You will not be able to have the database enforce referential integrity across multiple "attached" databases, nor use cross "attachment" triggers (but you cannot do that now anyway). --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >Sent: Thursday, 2 August, 2018 12:51 >To: SQLite mailing list >Subject: Re: [sqlite] Common index for multiple databases > > >You misunderstand how dBase databases work. An index is created on a >table (.DBF file) and stored in an index file (.NDX). You can have >multiple indexes associated with a single .DBF file (which means >multiple .NDX files). FoxPro has a non-standard index format that >permits the multiple indexes associated with one database (.DBF) file >to be stored in a single index file (.CDX). In no case can an index >reference data that is not stored in the associated database. > >SQLite3 stores the table (.DBF) and all the indexes associated with >that table in a single file called a database. You can also have >multiple tables in one database (rather than one table per file) and >all the indexes associates with all those tables are stored in the >same database file. You cannot split the table and it's indexes into >multiple files. You can have one table and its indexes stored in a >single file and have a crapload of those files, however, that defeats >the entire purpose of having a Relational Database (ACID) and also >precludes referential integrity enforcement and triggers. > >In no DBMS known can you index data sourced from multiple tables in >the same index -- this applies to "Relational" databases and all >other database models (such as pure hierarchical, network, network >extended, etc.) In all DBMS systems the contents of the index must >be sourced from a single object. > >Of course, dBase-style databases (of which FoxPro is but one >instance) are "navigational databases", not set-based relational >databases, so if you are expecting to use SQLite3 as a "navigational" >database you will likely run into other issues resulting from your >attempts to "navigate" a "set-based" datastore. > >--- >The fact that there's a Highway to Hell but only a Stairway to Heaven >says a lot about anticipated traffic volume. > >>-Original Message- >>From: sqlite-users [mailto:sqlite-users- >>boun...@mailinglists.sqlite.org] On Behalf Of John R. Sowden >>Sent: Thursday, 2 August, 2018 12:27 >>To: sqlite-users@mailinglists.sqlite.org >>Subject: Re: [sqlite] Common index for multiple databases >> >>I made a mistake. I should have said table, not database. My >>concern >>is if I have 4 databases each with tables associated with a >>particular >>use, like accounting, technical, etc., which may reside on different >>computers, how do I keep the index in each database file current. I >>assume that I have an external database with the account number >>field, >>and its index that each database connects to to "refresh" its >account >>number index from the external index. Otherwise if the table with >>the >>accounting index is modified, the tech table and its index would >have >>to >>communicate with the master in order to stay
Re: [sqlite] Common index for multiple databases
You misunderstand how dBase databases work. An index is created on a table (.DBF file) and stored in an index file (.NDX). You can have multiple indexes associated with a single .DBF file (which means multiple .NDX files). FoxPro has a non-standard index format that permits the multiple indexes associated with one database (.DBF) file to be stored in a single index file (.CDX). In no case can an index reference data that is not stored in the associated database. SQLite3 stores the table (.DBF) and all the indexes associated with that table in a single file called a database. You can also have multiple tables in one database (rather than one table per file) and all the indexes associates with all those tables are stored in the same database file. You cannot split the table and it's indexes into multiple files. You can have one table and its indexes stored in a single file and have a crapload of those files, however, that defeats the entire purpose of having a Relational Database (ACID) and also precludes referential integrity enforcement and triggers. In no DBMS known can you index data sourced from multiple tables in the same index -- this applies to "Relational" databases and all other database models (such as pure hierarchical, network, network extended, etc.) In all DBMS systems the contents of the index must be sourced from a single object. Of course, dBase-style databases (of which FoxPro is but one instance) are "navigational databases", not set-based relational databases, so if you are expecting to use SQLite3 as a "navigational" database you will likely run into other issues resulting from your attempts to "navigate" a "set-based" datastore. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of John R. Sowden >Sent: Thursday, 2 August, 2018 12:27 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Common index for multiple databases > >I made a mistake. I should have said table, not database. My >concern >is if I have 4 databases each with tables associated with a >particular >use, like accounting, technical, etc., which may reside on different >computers, how do I keep the index in each database file current. I >assume that I have an external database with the account number >field, >and its index that each database connects to to "refresh" its account >number index from the external index. Otherwise if the table with >the >accounting index is modified, the tech table and its index would have >to >communicate with the master in order to stay current. > >I do this now because I have 1 account number index and the various >foxpro databases (tables) all open that one index when each is used. > >John > > >On 08/02/2018 10:31 AM, Simon Slavin wrote: >> On 2 Aug 2018, at 6:11pm, John R. Sowden > wrote: >> >>> I do not want these databases to all reside in one sqlite file. >How do I index each database on this customer account number when >each database and associated index are in separate files? Is this >what seems to be referred to as an external file? I assume that I >would have to reindex each database each time it is opened, since a >record could have been edited, etc. >> You have been misinformed. In SQLite, >> >> A) each table is stored one database file >> B) each index indexes just one table >> C) all indexes for a table are stored in the same file as that >table. >> >> An index is updated when its table is updated. You never need to >manually reindex unless you changed the table structure or index >structure. >> >> It is normal to keep all tables related to one application in one >big database file. So, for example, if you run a library you would >normally keep tables and indexes for books, borrowers, and current >loans all in one file. And therefore all the indexes for those >tables would be in that file too. SQLite is designed to handle >things this way, and does it very efficiently. >> >> However, it is possible to keep different tables in different >database files. So you might keep books (and all indexes on books) >in one file, and borrowers and current loans (and all the indexes on >those tables) in another file. >> >> 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common index for multiple databases
Hi, On Thu, Aug 2, 2018 at 1:44 PM, John R. Sowden wrote: > another point that I did not make clear. The accounting programs are not > associated with the technical programs, different people, different security > access. The tech databases and programs are in portable computers that go > out in the field, but not the accounting, etc. There indexes would have to > be updated when the computers are back at the office. Then the solution by David applies. You will have one database (centralized), which will be updated with the changes to the local DBs when they come back to the office. Thank you. > > John > > > > On 08/02/2018 11:33 AM, Igor Korot wrote: >> >> Hi, >> >> On Thu, Aug 2, 2018 at 1:27 PM, John R. Sowden >> wrote: >>> >>> I made a mistake. I should have said table, not database. My concern is >>> if >>> I have 4 databases each with tables associated with a particular use, >>> like >>> accounting, technical, etc., which may reside on different computers, how >>> do >>> I keep the index in each database file current. I assume that I have an >>> external database with the account number field, and its index that each >>> database connects to to "refresh" its account number index from the >>> external >>> index. Otherwise if the table with the accounting index is modified, the >>> tech table and its index would have to communicate with the master in >>> order >>> to stay current. >> >> Why do you need 4 databases in the first place? >> If you client is designed to access all 4 databases then all tables >> should be in 1 DB file. >> >> Thank you. >> >>> I do this now because I have 1 account number index and the various >>> foxpro >>> databases (tables) all open that one index when each is used. >>> >>> John >>> >>> >>> On 08/02/2018 10:31 AM, Simon Slavin wrote: On 2 Aug 2018, at 6:11pm, John R. Sowden wrote: > I do not want these databases to all reside in one sqlite file. How do > I > index each database on this customer account number when each database > and > associated index are in separate files? Is this what seems to be > referred > to as an external file? I assume that I would have to reindex each > database > each time it is opened, since a record could have been edited, etc. You have been misinformed. In SQLite, A) each table is stored one database file B) each index indexes just one table C) all indexes for a table are stored in the same file as that table. An index is updated when its table is updated. You never need to manually reindex unless you changed the table structure or index structure. It is normal to keep all tables related to one application in one big database file. So, for example, if you run a library you would normally keep tables and indexes for books, borrowers, and current loans all in one file. And therefore all the indexes for those tables would be in that file too. SQLite is designed to handle things this way, and does it very efficiently. However, it is possible to keep different tables in different database files. So you might keep books (and all indexes on books) in one file, and borrowers and current loans (and all the indexes on those tables) in another file. 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 >> >> ___ >> 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] Common index for multiple databases
another point that I did not make clear. The accounting programs are not associated with the technical programs, different people, different security access. The tech databases and programs are in portable computers that go out in the field, but not the accounting, etc. There indexes would have to be updated when the computers are back at the office. John On 08/02/2018 11:33 AM, Igor Korot wrote: Hi, On Thu, Aug 2, 2018 at 1:27 PM, John R. Sowden wrote: I made a mistake. I should have said table, not database. My concern is if I have 4 databases each with tables associated with a particular use, like accounting, technical, etc., which may reside on different computers, how do I keep the index in each database file current. I assume that I have an external database with the account number field, and its index that each database connects to to "refresh" its account number index from the external index. Otherwise if the table with the accounting index is modified, the tech table and its index would have to communicate with the master in order to stay current. Why do you need 4 databases in the first place? If you client is designed to access all 4 databases then all tables should be in 1 DB file. Thank you. I do this now because I have 1 account number index and the various foxpro databases (tables) all open that one index when each is used. John On 08/02/2018 10:31 AM, Simon Slavin wrote: On 2 Aug 2018, at 6:11pm, John R. Sowden wrote: I do not want these databases to all reside in one sqlite file. How do I index each database on this customer account number when each database and associated index are in separate files? Is this what seems to be referred to as an external file? I assume that I would have to reindex each database each time it is opened, since a record could have been edited, etc. You have been misinformed. In SQLite, A) each table is stored one database file B) each index indexes just one table C) all indexes for a table are stored in the same file as that table. An index is updated when its table is updated. You never need to manually reindex unless you changed the table structure or index structure. It is normal to keep all tables related to one application in one big database file. So, for example, if you run a library you would normally keep tables and indexes for books, borrowers, and current loans all in one file. And therefore all the indexes for those tables would be in that file too. SQLite is designed to handle things this way, and does it very efficiently. However, it is possible to keep different tables in different database files. So you might keep books (and all indexes on books) in one file, and borrowers and current loans (and all the indexes on those tables) in another file. 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 ___ 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] Common index for multiple databases
The "index" is on what's actually in the "table". So the index on each database's tables are always up to date. What you're saying is that each satellite database should have a complete, up to date copy of the customer ID "table". The only way to do that would be to have a centrally accessible database with that info. Each of the satellite databases would connect to and ATTACH that central database to read the overarching customer table while they make local edits to their own local tables. The usual concepts of caching and such still apply, but yeah, you'd have 1 central db with everything that's needed by all satellite db's, and have those satellites connect to it as needed. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of John R. Sowden Sent: Thursday, August 02, 2018 2:27 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Common index for multiple databases I made a mistake. I should have said table, not database. My concern is if I have 4 databases each with tables associated with a particular use, like accounting, technical, etc., which may reside on different computers, how do I keep the index in each database file current. I assume that I have an external database with the account number field, and its index that each database connects to to "refresh" its account number index from the external index. Otherwise if the table with the accounting index is modified, the tech table and its index would have to communicate with the master in order to stay current. I do this now because I have 1 account number index and the various foxpro databases (tables) all open that one index when each is used. John On 08/02/2018 10:31 AM, Simon Slavin wrote: > On 2 Aug 2018, at 6:11pm, John R. Sowden wrote: > >> I do not want these databases to all reside in one sqlite file. How do I >> index each database on this customer account number when each database and >> associated index are in separate files? Is this what seems to be referred >> to as an external file? I assume that I would have to reindex each database >> each time it is opened, since a record could have been edited, etc. > You have been misinformed. In SQLite, > > A) each table is stored one database file > B) each index indexes just one table > C) all indexes for a table are stored in the same file as that table. > > An index is updated when its table is updated. You never need to manually > reindex unless you changed the table structure or index structure. > > It is normal to keep all tables related to one application in one big > database file. So, for example, if you run a library you would normally keep > tables and indexes for books, borrowers, and current loans all in one file. > And therefore all the indexes for those tables would be in that file too. > SQLite is designed to handle things this way, and does it very efficiently. > > However, it is possible to keep different tables in different database files. > So you might keep books (and all indexes on books) in one file, and > borrowers and current loans (and all the indexes on those tables) in another > file. > > 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common index for multiple databases
Hi, On Thu, Aug 2, 2018 at 1:27 PM, John R. Sowden wrote: > I made a mistake. I should have said table, not database. My concern is if > I have 4 databases each with tables associated with a particular use, like > accounting, technical, etc., which may reside on different computers, how do > I keep the index in each database file current. I assume that I have an > external database with the account number field, and its index that each > database connects to to "refresh" its account number index from the external > index. Otherwise if the table with the accounting index is modified, the > tech table and its index would have to communicate with the master in order > to stay current. Why do you need 4 databases in the first place? If you client is designed to access all 4 databases then all tables should be in 1 DB file. Thank you. > > I do this now because I have 1 account number index and the various foxpro > databases (tables) all open that one index when each is used. > > John > > > On 08/02/2018 10:31 AM, Simon Slavin wrote: >> >> On 2 Aug 2018, at 6:11pm, John R. Sowden >> wrote: >> >>> I do not want these databases to all reside in one sqlite file. How do I >>> index each database on this customer account number when each database and >>> associated index are in separate files? Is this what seems to be referred >>> to as an external file? I assume that I would have to reindex each database >>> each time it is opened, since a record could have been edited, etc. >> >> You have been misinformed. In SQLite, >> >> A) each table is stored one database file >> B) each index indexes just one table >> C) all indexes for a table are stored in the same file as that table. >> >> An index is updated when its table is updated. You never need to manually >> reindex unless you changed the table structure or index structure. >> >> It is normal to keep all tables related to one application in one big >> database file. So, for example, if you run a library you would normally >> keep tables and indexes for books, borrowers, and current loans all in one >> file. And therefore all the indexes for those tables would be in that file >> too. SQLite is designed to handle things this way, and does it very >> efficiently. >> >> However, it is possible to keep different tables in different database >> files. So you might keep books (and all indexes on books) in one file, and >> borrowers and current loans (and all the indexes on those tables) in another >> file. >> >> 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common index for multiple databases
I made a mistake. I should have said table, not database. My concern is if I have 4 databases each with tables associated with a particular use, like accounting, technical, etc., which may reside on different computers, how do I keep the index in each database file current. I assume that I have an external database with the account number field, and its index that each database connects to to "refresh" its account number index from the external index. Otherwise if the table with the accounting index is modified, the tech table and its index would have to communicate with the master in order to stay current. I do this now because I have 1 account number index and the various foxpro databases (tables) all open that one index when each is used. John On 08/02/2018 10:31 AM, Simon Slavin wrote: On 2 Aug 2018, at 6:11pm, John R. Sowden wrote: I do not want these databases to all reside in one sqlite file. How do I index each database on this customer account number when each database and associated index are in separate files? Is this what seems to be referred to as an external file? I assume that I would have to reindex each database each time it is opened, since a record could have been edited, etc. You have been misinformed. In SQLite, A) each table is stored one database file B) each index indexes just one table C) all indexes for a table are stored in the same file as that table. An index is updated when its table is updated. You never need to manually reindex unless you changed the table structure or index structure. It is normal to keep all tables related to one application in one big database file. So, for example, if you run a library you would normally keep tables and indexes for books, borrowers, and current loans all in one file. And therefore all the indexes for those tables would be in that file too. SQLite is designed to handle things this way, and does it very efficiently. However, it is possible to keep different tables in different database files. So you might keep books (and all indexes on books) in one file, and borrowers and current loans (and all the indexes on those tables) in another file. 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] Common index for multiple databases
On 2 Aug 2018, at 6:11pm, John R. Sowden wrote: > I do not want these databases to all reside in one sqlite file. How do I > index each database on this customer account number when each database and > associated index are in separate files? Is this what seems to be referred to > as an external file? I assume that I would have to reindex each database > each time it is opened, since a record could have been edited, etc. You have been misinformed. In SQLite, A) each table is stored one database file B) each index indexes just one table C) all indexes for a table are stored in the same file as that table. An index is updated when its table is updated. You never need to manually reindex unless you changed the table structure or index structure. It is normal to keep all tables related to one application in one big database file. So, for example, if you run a library you would normally keep tables and indexes for books, borrowers, and current loans all in one file. And therefore all the indexes for those tables would be in that file too. SQLite is designed to handle things this way, and does it very efficiently. However, it is possible to keep different tables in different database files. So you might keep books (and all indexes on books) in one file, and borrowers and current loans (and all the indexes on those tables) in another file. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common index for multiple databases
Each SQLite file will be its own database with its own tables and indexes. So for each database you just define an index on the appropriate tables. I'm not sure why you want multiple files, but it's fine. You can always have one master db from which you ATTACH the others as needed. You just can't have 1 index that spans multiple files, or do foreign key checks between files, etc. https://www.sqlite.org/lang_attach.html -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of John R. Sowden Sent: Thursday, August 02, 2018 1:12 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Common index for multiple databases I have been reviewing sqlite for a couple of years, but still use foxpro. I have a question regarding an index issue. Currently I have several types of databases (in foxpro, one per file) that all point to an index of a common field, a customer account number. The databases are for accounting, technical, general info lookup, etc. \ I do not want these databases to all reside in one sqlite file. How do I index each database on this customer account number when each database and associated index are in separate files? Is this what seems to be referred to as an external file? I assume that I would have to reindex each database each time it is opened, since a record could have been edited, etc. tia, John ___ 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] Common index for multiple databases
I have been reviewing sqlite for a couple of years, but still use foxpro. I have a question regarding an index issue. Currently I have several types of databases (in foxpro, one per file) that all point to an index of a common field, a customer account number. The databases are for accounting, technical, general info lookup, etc. \ I do not want these databases to all reside in one sqlite file. How do I index each database on this customer account number when each database and associated index are in separate files? Is this what seems to be referred to as an external file? I assume that I would have to reindex each database each time it is opened, since a record could have been edited, etc. tia, John ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Add Column with "If Not Exists"
On 8/1/18, Charles Leifer wrote:
> You can simply use:
>
> PRAGMA table_info('my_table')
>
> To get a list of columns, which you can check against and then
> conditionally add your column.
From C-code, you can use the sqlite_table_column_metadata() interface
[1] to quickly check for the existance of tables and/or columns. This
happens a lot in Fossil, which has an evolving schema but still needs
to work with older repositories.
[1] https://www.sqlite.org/c3ref/table_column_metadata.html
--
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] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all
The way I it happens with deferred foreign keys is this (Gut feeling from observations. Experts please correct me if I'm way off): Keep track with an integer, let's call it "netBroken" While netBroken = 0 then when you insert a row you don't have to check if anything references it, only if it references something. (In this case that's a quick search with the primary key) If it does, then you're good. If it doesn't, then increment netBroken to let you know "hey, I broke something" If netBroken is != 0, then when you insert a row you not only have to check if the new row is broken, but you also have to check if it fixes a previously broken thing. (In this case that includes an unindexed search on aid) If the new row doesn't reference something, then increment netBroken. If something references the new row, then decrement netBroken by the number of rows that reference it, "hey, I fixed something" At the end of the transaction look at what you have for netBroken. If it's 0 then yay! Your balance sheet came out ok and you can commit. If it's non-zero then you didn't fix everything you broke. This also explains why with deferred foreign keys it can't tell you what record violated things, because it didn't keep track of the individual record, only the net count of broken things. So for your test script there, try inserting a record with the violation ID a little bit later and see if it suddenly speeds up again. In my Python version of your script it does indeed speed back up again once the outstanding violation is fixed. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Dominique Devienne Sent: Thursday, August 02, 2018 4:59 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all On Thu, Aug 2, 2018 at 10:34 AM Keith Medcalf wrote: > > Yes. Look at the CREATE TABLE for table A (completely ignore table B as > it serves no purpose whatsoever) > Rah, silly me... I assumed A.aid referenced B.id. Why have a B table at all then. > Also, if you create an index on the child key as is required for > performance, there is no "slowdown" whatsoever Of course. Something I always do in Oracle, index all my FK columns... Silly me again. Boy, not my morning :). Thanks for setting me straight Keith. --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
Re: [sqlite] In tea/configure.ac why isn't --with-system-sqlite default on linux?
This is also a problem on FreeBSD, I just haven’t noticed it because the ports guys are on top of keeping their sqlite3 build up to date, and they do the smart thing and build --with-system-sqlite. If you’re building sqlite-autoconf-3xx/tea, then why would the “system sqlite” ever be anything but current? Why would you install the Tcl extension but not the library itself? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all
On Thu, Aug 2, 2018 at 10:34 AM Keith Medcalf wrote: > > Yes. Look at the CREATE TABLE for table A (completely ignore table B as > it serves no purpose whatsoever) > Rah, silly me... I assumed A.aid referenced B.id. Why have a B table at all then. > Also, if you create an index on the child key as is required for > performance, there is no "slowdown" whatsoever Of course. Something I always do in Oracle, index all my FK columns... Silly me again. Boy, not my morning :). Thanks for setting me straight Keith. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all
This is the line that create the violation: sprintf(s, "insert into a values(%d, 'The name field for %d', %d)", NROWS+i, i, NROWS+i+VIOLATION); if VIOLATION is 0 then you are inserting a record with id = NROWS+i and aid = NROWS+i. Since the referential constraint is that aid references id in the same table, it is satisfied by the record itself, thus the constraint is not violated. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Dominique Devienne >Sent: Thursday, 2 August, 2018 01:48 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] Violated failed foreign key constraint delays >the rest of transaction ; Some foreign key violations don't trigger >the error at all > >On Thu, Aug 2, 2018 at 9:35 AM Keith Medcalf >wrote: > >> You observe no violation when VIOLATION is 0 because there is no >> referential integrity violation to report ... >> > >Really Keith? Parent IDs are in range [0, NROWS) >Child/FK IDs inserted are in range [NROWS, 2*NROWS) >How's that not an FK violation? At least that's how I read the code. >Am I >reading wrong? --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
Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all
Yes. Look at the CREATE TABLE for table A (completely ignore table B as it serves no purpose whatsoever) If you change the database to write to a database on disk so you can examine it after (or modify doSql so that it outputs the SQL so that you can read it) everything will become much clearer ... Also, if you create an index on the child key as is required for performance, there is no "slowdown" whatsoever ie after this line: doSql(db, "create table a (id int PRIMARY KEY, name text, aid int not null, FOREIGN KEY(aid) REFERENCES a(id))"); insert this line" doSql(db, "create index aid on a (aid)"); --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Dominique Devienne >Sent: Thursday, 2 August, 2018 01:48 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] Violated failed foreign key constraint delays >the rest of transaction ; Some foreign key violations don't trigger >the error at all > >On Thu, Aug 2, 2018 at 9:35 AM Keith Medcalf >wrote: > >> You observe no violation when VIOLATION is 0 because there is no >> referential integrity violation to report ... >> > >Really Keith? Parent IDs are in range [0, NROWS) >Child/FK IDs inserted are in range [NROWS, 2*NROWS) >How's that not an FK violation? At least that's how I read the code. >Am I >reading wrong? --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
Re: [sqlite] [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?
I didn't say I knew how you to do it in SQLitespeed, I said you your
use-case was very likely the same as mine.
I did notice the new keyword API and will move to 3.24 so I can use that.
Will look further into using explain to solve this problem, but guess the
output of explain may change in future versions,
so maybe can't rely on that.
RBS
On Wed, Aug 1, 2018 at 8:27 PM, R Smith wrote:
> On 2018/08/01 5:56 PM, Bart Smissaert wrote:
>
>> May I ask about your use-case and what specifically is needed?
>>>
>> Probably exactly the same as you are using in your SQLitespeed app.
>> There is a SQL text box and the user can type anything in there he/she
>> wants.
>> App then needs to determine how to handle that string:
>> Produce data to show, run a non-data producing SQL, or reject it because
>> it
>> is invalid.
>>
>
> Ah, well, since you already know SQLitespeed does it, I'll confess how
> it's done. Firstly the API is really helpful in both determining if a
> Keyword is valid, and in counting the valid Keywords - making parsing easy.
> You can see here: https://sqlite.org/c3ref/keyword_check.html
>
> Secondly, I confess, we actually maintain a list of pragmas inside the
> code to verify against, and we update this list on every major update.
>
> To confess more - We actually keep an entire list of all sqlite keywords
> and for all the major SQL phrases/pragmas/etc. direct help links to the
> sqlite online documentation and of course the code hinting and highlighting
> needs it. (It's a little harder to maintain than a simple "check_keyword()"
> api, but so much more friendly and the tool isn't as sensitive to
> code-bloat as the sqlite engine).
>
> Last confession, every table-data-producing pragma has a
> table-valued-function alternative in the form "pragma_xxx" where a pragma
> that can be called like this:
> PRAGMA table_info(MyTable);
>
> can also be called like this via said t.v.f:
>
> SELECT * FROM pragma_table_info('MyTable');
>
> And THAT will error out if it doesn't exist, or is misused, right upon
> prepare - no guessing.
>
> You'll have to still keep a list to know which pragmas are data-producing
> and which not, some can be used both ways, so a 2-list approach works
> better. Of course, once you maintain a list of valid Pragmas, the quest for
> a way to know which are valid, becomes somewhat moot.
>
>
> PS: SQLitespeed hasn't seen an update for a while, but the final
> adjustments and testing is ongoing now for the newest release due later
> this Month. The SQLitespeed community has been alpha testing and pencils
> down for beta starting in about a week. If anyone not on the list would
> like to join testing, please mail me off-list, otherwise we'll share
> release details later in August.
>
> Most notable new addition: Schema-testing to warn about misspelled type
> names, unintentional errors, using Integer FK on a Text parent column, and
> all kinds of similar mishaps we know of thanks to people posting to this
> list - so thank you all for that.
>
>
>
>
> ___
> 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] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all
On Thu, Aug 2, 2018 at 9:35 AM Keith Medcalf wrote: > You observe no violation when VIOLATION is 0 because there is no > referential integrity violation to report ... > Really Keith? Parent IDs are in range [0, NROWS) Child/FK IDs inserted are in range [NROWS, 2*NROWS) How's that not an FK violation? At least that's how I read the code. Am I reading wrong? --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Best approach for applying DB migrations to an existing SQLite DB
Our application is an ASP.NET MVC application with an N-Tier architecture running on Mono in Linux. Interaction with SQLite DB is done through ADO.Net using System.Data.SQLite provider. Can you suggest best approach for implementing and applying migrations to the DB? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all
On Thu, Aug 2, 2018 at 12:57 AM Yuri wrote: > The attached testcase injects the foreign key violation into a long > transaction. This makes the remainder of the transaction much slower, > even though the foreign key is deferred, and should only be checked in > the end of the transaction. > Right. Reproduced on Win7 VS2017 (/Od x64), SQLite 3.24 from official amalgamation. (see 1st run's output at the end) First 25K rows inserted within the same second, then after the first violation is introduced, each 1K batch takes 3-4s, increasing up to 12-13s per 1K batch towards the end. That's a serious slow down indeed. Behaves correctly, so not a bug, but a fairly big missed opportunity to optimize this case. While working on this testcase, I found that sometimes the foreign key > violation doesn't trigger the error at all. Please change VIOLATION to > 0, and observe that there is no failure now, though it should be. > Also reproduced (see 2nd run output below). This does appear as a bug, OTOH, although maybe I'm missing something... I'm sure DRH or Dan will tell us soon what's going on. That one is "fast all the way", taking 2-3s, and reports no violation indeed. In a weird way, that's consistent, since it "doesn't see" the violations, it remains "fast" I guess :) Interesting test case Yuri! --DD --DD --- 1st run: with VIOLATION = 100 D:\>sqlite_fk_error.exe creating B ... populating B ... creating A ... populating A ... ...row#0... (time=1533192850) ...row#1000... (time=1533192850) ...row#2000... (time=1533192850) ...row#3000... (time=1533192850) ...row#4000... (time=1533192850) ...row#5000... (time=1533192850) ...row#6000... (time=1533192850) ...row#7000... (time=1533192850) ...row#8000... (time=1533192850) ...row#9000... (time=1533192850) ...row#1... (time=1533192850) ...row#11000... (time=1533192850) ...row#12000... (time=1533192850) ...row#13000... (time=1533192850) ...row#14000... (time=1533192850) ...row#15000... (time=1533192850) ...row#16000... (time=1533192850) ...row#17000... (time=1533192850) ...row#18000... (time=1533192850) ...row#19000... (time=1533192850) ...row#2... (time=1533192850) ...row#21000... (time=1533192850) ...row#22000... (time=1533192850) ...row#23000... (time=1533192850) ...row#24000... (time=1533192850) ...row#25000... (time=1533192850) ...row#26000... (time=1533192853) ...row#27000... (time=1533192857) ...row#28000... (time=1533192860) ...row#29000... (time=1533192864) ...row#3... (time=1533192868) ...row#31000... (time=1533192872) ...row#32000... (time=1533192876) ...row#33000... (time=1533192880) ...row#34000... (time=1533192884) ...row#35000... (time=1533192889) ...row#36000... (time=1533192893) ...row#37000... (time=1533192898) ...row#38000... (time=1533192903) ...row#39000... (time=1533192908) ...row#4... (time=1533192913) ...row#41000... (time=1533192918) ...row#42000... (time=1533192924) ...row#43000... (time=1533192929) ...row#44000... (time=1533192935) ...row#45000... (time=1533192940) ...row#46000... (time=1533192946) ...row#47000... (time=1533192952) ...row#48000... (time=1533192959) ...row#49000... (time=1533192965) ...row#5... (time=1533192971) ...row#51000... (time=1533192978) ...row#52000... (time=1533192985) ...row#53000... (time=1533192992) ...row#54000... (time=1533192999) ...row#55000... (time=1533193006) ...row#56000... (time=1533193013) ...row#57000... (time=1533193020) ...row#58000... (time=1533193028) ...row#59000... (time=1533193035) ...row#6... (time=1533193043) ...row#61000... (time=1533193051) ...row#62000... (time=1533193059) ...row#63000... (time=1533193067) ...row#64000... (time=1533193075) ...row#65000... (time=1533193084) ...row#66000... (time=1533193092) ...row#67000... (time=1533193101) ...row#68000... (time=1533193110) ...row#69000... (time=1533193119) ...row#7... (time=1533193128) ...row#71000... (time=1533193137) ...row#72000... (time=1533193146) ...row#73000... (time=1533193156) ...row#74000... (time=1533193165) ...row#75000... (time=1533193175) ...row#76000... (time=1533193185) ...row#77000... (time=1533193195) ...row#78000... (time=1533193205) ...row#79000... (time=1533193216) ...row#8... (time=1533193226) ...row#81000... (time=1533193236) ...row#82000... (time=1533193247) ...row#83000... (time=1533193258) ...row#84000... (time=1533193269) ...row#85000... (time=1533193280) ...row#86000... (time=1533193291) ...row#87000... (time=1533193303) ...row#88000... (time=1533193314) ...row#89000... (time=1533193326) ...row#9... (time=1533193337) ...row#91000... (time=1533193349) ...row#92000... (time=1533193361) ...row#93000... (time=1533193373) ...row#94000... (time=1533193386) ...row#95000... (time=1533193398) ...row#96000... (time=1533193411) ...row#97000... (time=1533193423) ...row#98000... (time=1533193436) ...row#99000... (time=1533193449) SQL error: FOREIGN KEY constraint failed --- 2nd run: with VIOLATION = 0 D:\>sqlite_fk_error.exe creating B ... populating B ... creating A ...
Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all
You observe no violation when VIOLATION is 0 because there is no referential
integrity violation to report ...
However, you are correct that when inserting data the as shown in your code
(where there is a referential integrity violation) the insertion is much slower
after the violation occurs, presumably because on each subsequent insert into
table a it is checking whether the deferred violation has been resolved yet.
Presently this is how the referential integrity checking works in SQLite3.
Note that if you change your reference to table b rather than table a and you
are operating only on table a this does not occur since there is no operation
on table b which might resolve the violation. It also makes the extra insert a
violation when you define VIOLATION as 0 ...
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Yuri
>Sent: Wednesday, 1 August, 2018 16:57
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Violated failed foreign key constraint delays the
>rest of transaction ; Some foreign key violations don't trigger the
>error at all
>
>The attached testcase injects the foreign key violation into a long
>transaction. This makes the remainder of the transaction much slower,
>even though the foreign key is deferred, and should only be checked
>in
>the end of the transaction.
>
>
>While working on this testcase, I found that sometimes the foreign
>key
>violation doesn't trigger the error at all. Please change VIOLATION
>to
>0, and observe that there is no failure now, though it should be.
>
>
>sqlite3-3.24.0 on FreeBSD 11.2
>
>
>Yuri
>
>
>
>---testcase---
>
>#include
>#include
>#include
>#include
>
>void doSql(sqlite3 *db, const char *sql) {
> char *err_msg = 0;
> int rc = sqlite3_exec(db, sql, 0, 0, _msg);
> if (rc != SQLITE_OK ) {
> fprintf(stderr, "SQL error: %s\n", err_msg);
> sqlite3_free(err_msg);
> sqlite3_close(db);
> exit(1);
> }
>}
>
>#define NROWS 10
>#define VIOLATION 100
>
>int main(void) {
> sqlite3 *db;
>
> char s[512];
>
> int rc = sqlite3_open(":memory:", );
> if (rc != SQLITE_OK) {
> fprintf(stderr, "Cannot open database: %s\n",
>sqlite3_errmsg(db));
> sqlite3_close(db);
> return 1;
> }
>
> doSql(db, "PRAGMA foreign_keys = ON;");
>
> printf("creating B ...\n");
> doSql(db, "create table b (id int PRIMARY KEY, name text)");
>
> printf("populating B ...\n");
> for (int i = 0; i < NROWS; i++) {
> sprintf(s, "insert into b values(%d, 'The name field for %d')",
>i, i);
> doSql(db, s);
> }
>
> printf("creating A ...\n");
> doSql(db, "create table a (id int PRIMARY KEY, name text, aid int
>not
>null, FOREIGN KEY(aid) REFERENCES a(id))");
>
> printf("populating A ...\n");
> doSql(db, "BEGIN TRANSACTION;");
> doSql(db, "PRAGMA defer_foreign_keys=ON;");
> for (int i = 0; i < NROWS; i++) {
> if (i % 1000 == 0)
> printf("...row#%d... (time=%ld)\n", i, time(0));
> sprintf(s, "insert into a values(%d, 'The name field for %d',
>%d)",
>i, i, i);
> doSql(db, s);
> // introfuce the FK violation
> if (i == NROWS/4) {
> sprintf(s, "insert into a values(%d, 'The name field for %d',
>%d)", NROWS+i, i, NROWS+i+VIOLATION);
> doSql(db, s);
> }
> }
> doSql(db, "COMMIT TRANSACTION;");
>
> sqlite3_close(db);
>
> return 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
