Re: [sqlite] How to verify referential integrity of SQLite database
On Wed, Dec 12, 2012 at 10:01 PM, Jos Groot Lipman donts...@home.nl wrote: When you enable foreign keys *after* you insert records, no guarantee is given by SQLite that the foreign key constraints are fulfilled. This may not be what most users would want or expect but it certainly not a corrupt database by SQLite standards. I guess one thing I don't understand in this thread is why PRAGMA foreign_keys = ON is *not* persistent with the DB file itself, just like the page size for example. Apologies if this has been discussed before, I'm newly subscribed*, but it seems to me that if the DB creator/owner intends to enforce relational integrity, that should be part of the data, otherwise anyone can come and mess up the data with no regard for the constraints, as demonstrated below. The second session *should* fail to insert (1,1) into the child table IMHO. Relying on all clients to issue the PRAGMA foreign_keys = ON (explicitly and via compile-time switch to default it to ON) is just too brittle and ignores humans natural tendency to make mistakes. If someone explicitly disables the constraints integrity, I guess that's OK, but like others in this thread, I think that trying to re-enable it later should fail if the current DB state violates the FK constraints. What am I missing? Thanks, --DD * is the reason for the non-persistent FK enforcement the fixed SQLite3 DB file format, thus the talk about SQLite4? C:\Users\DDeviennesqlite3 testfk.db SQLite version 3.7.11 2012-03-20 11:35:50 Enter .help for instructions Enter SQL statements terminated with a ; sqlite sqlite PRAGMA foreign_keys = ON; sqlite create table parent (id int primary key); sqlite create table child (id int primary key, parent int references parent(id)); sqlite insert into child values (0, 0); Error: foreign key constraint failed sqlite insert into parent values (0); sqlite insert into child values (0, 0); sqlite select * from parent; 0 sqlite select * from child; 0|0 sqlite .q C:\Users\DDeviennesqlite3 testfk.db SQLite version 3.7.11 2012-03-20 11:35:50 Enter .help for instructions Enter SQL statements terminated with a ; sqlite sqlite insert into child values (1, 1); sqlite select * from parent; 0 sqlite select * from child; 0|0 1|1 sqlite ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
Jay A. Kreibich wrote: I can also see situations when someone might want to run one set or the other set of checks. Breaking it out, so that these checks are done by a different PRAGMA (integrity_check_v2 ?) seems like a wise idea. Indeed; with a separate PRAGMA fk_integrity_check, it would be possible to run the check even when foreign keys are not currently enabled. This would be a useful thing to do just before enabling foreign keys. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] JDBC Drivers for SQLite?
I have sqlite-jdbc-3.7.2.jar that I use as the sqlite driver for squirrel-sql. It is 3.1MB, so I don't think I can send it as an email attachment. You may want to search for that file name. If you can't find it, let me know and I will put is somewhere on the net for you. LMH Tilsley, Jerry M. [via SQLite] wrote: All, Might be a silly question, but does anyone know if any JDBC drivers exist for SQLite? Thanks, Jerry Disclaimer This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of St. Claire Regional Medical Center. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing or copying of the email is strictly prohibited. If you received this email in error please notify the St. Claire Regional Helpdesk by telephone at 606-783-6565. ___ sqlite-users mailing list [hidden email] /user/SendEmail.jtp?type=nodenode=66105i=0 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users If you reply to this email, your message will be added to the discussion below: http://sqlite.1065341.n5.nabble.com/JDBC-Drivers-for-SQLite-tp66105.html To unsubscribe from SQLite, click here http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=2code=bG1oX3VzZXJzLWdyb3Vwc0Btb2xjb25uLmNvbXwyfC02ODY4MzM2NjI=. NAML http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespacebreadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml -- View this message in context: http://sqlite.1065341.n5.nabble.com/JDBC-Drivers-for-SQLite-tp66105p66109.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] malloc failures on ubuntu
Really appritiate if any one can help me on this, I didnot get a solution for this Thanks Brijesh -- View this message in context: http://sqlite.1065341.n5.nabble.com/malloc-failures-on-ubuntu-tp65936p66126.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] malloc failures on ubuntu
On Wed, Dec 12, 2012 at 11:41 PM, bkk brijeshk_...@yahoo.com wrote: Really appritiate if any one can help me on this, I didnot get a solution for this Apparently, nobody else can reproduce your problem. All of our tests are working fine. Thanks Brijesh -- View this message in context: http://sqlite.1065341.n5.nabble.com/malloc-failures-on-ubuntu-tp65936p66126.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
Jay A. Kreibich wrote: I can also see situations when someone might want to run one set or the other set of checks. Breaking it out, so that these checks are done by a different PRAGMA (integrity_check_v2 ?) seems like a wise idea. Indeed; with a separate PRAGMA fk_integrity_check, it would be possible to run the check even when foreign keys are not currently enabled. This would be a useful thing to do just before enabling foreign keys. Isn't something else than a pragma more appropiate? SELECT consistency_check() FROM mytable; would return rows from a specific table where any constraint, unicity or FK is violated: rowid | constraint_name | diag_code SELECT consistency_check_all(); would return rows from every table in turn where any constraint, unicity or FK is violated: table_name | rowid | constraint_name | diag_code ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] JDBC Drivers for SQLite?
I will sure and report back, I appreciate the response and assistance! -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of LMHmedchem Sent: Wednesday, December 12, 2012 12:33 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] JDBC Drivers for SQLite? I have sqlite-jdbc-3.7.2.jar that I use as the sqlite driver for squirrel-sql. It is 3.1MB, so I don't think I can send it as an email attachment. You may want to search for that file name. If you can't find it, let me know and I will put is somewhere on the net for you. LMH Tilsley, Jerry M. [via SQLite] wrote: All, Might be a silly question, but does anyone know if any JDBC drivers exist for SQLite? Thanks, Jerry Disclaimer This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of St. Claire Regional Medical Center. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing or copying of the email is strictly prohibited. If you received this email in error please notify the St. Claire Regional Helpdesk by telephone at 606-783-6565. ___ sqlite-users mailing list [hidden email] /user/SendEmail.jtp?type=nodenode=66105i=0 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- -- If you reply to this email, your message will be added to the discussion below: http://sqlite.1065341.n5.nabble.com/JDBC-Drivers-for-SQLite-tp66105.ht ml To unsubscribe from SQLite, click here http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=2code=bG1oX3VzZXJzLWdyb3Vwc0Btb2xjb25uLmNvbXwyfC02ODY4MzM2NjI=. NAML http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=ma cro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.name spaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble. view.web.template.NodeNamespacebreadcrumbs=notify_subscribers%21nabbl e%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email %21nabble%3Aemail.naml -- View this message in context: http://sqlite.1065341.n5.nabble.com/JDBC-Drivers-for-SQLite-tp66105p66109.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Disclaimer This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of St. Claire Regional Medical Center. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing or copying of the email is strictly prohibited. If you received this email in error please notify the St. Claire Regional Helpdesk by telephone at 606-783-6565. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
Hello, You can give the following tool a try if you want: https://github.com/gwenn/checkfkey But I'm not sure that it correctly handles composite. Regards. On Thu, Dec 13, 2012 at 4:22 PM, Jean-Christophe Deschamps j...@antichoc.net wrote: Jay A. Kreibich wrote: I can also see situations when someone might want to run one set or the other set of checks. Breaking it out, so that these checks are done by a different PRAGMA (integrity_check_v2 ?) seems like a wise idea. Indeed; with a separate PRAGMA fk_integrity_check, it would be possible to run the check even when foreign keys are not currently enabled. This would be a useful thing to do just before enabling foreign keys. Isn't something else than a pragma more appropiate? SELECT consistency_check() FROM mytable; would return rows from a specific table where any constraint, unicity or FK is violated: rowid | constraint_name | diag_code SELECT consistency_check_all(); would return rows from every table in turn where any constraint, unicity or FK is violated: table_name | rowid | constraint_name | diag_code ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] The database disk image is malformed
I have encountered an error of the database disk image is malformed. Database was send in by a customer so I'm not exactly sure how it came to be this. I have found that this can be repaired by SQLiteExpertPro by the Database | Repair functionality but have not been able yet to find anything in the C API that might be used to implement this functionality. Could someone please point me to some information on how I might make my product be able to recover from this situation. SQLite3 version 3.7.13 Windows 7 x64 Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com 330819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The database disk image is malformed
On Thu, Dec 13, 2012 at 3:56 PM, Jeff Archer jsarc...@nanotronicsimaging.com wrote: I have encountered an error of the database disk image is malformed. Database was send in by a customer so I'm not exactly sure how it came to be this. I have found that this can be repaired by SQLiteExpertPro by the Database | Repair functionality but have not been able yet to find anything in the C API that might be used to implement this functionality. Could someone please point me to some information on how I might make my product be able to recover from this situation. Information on how to corrupt a database file: http://www.sqlite.org/howtocorrupt.html There is no general technique for repairing corruption. I don't know how SQLiteExpertPro does it. One thing that sometimes works, at least partially is to do: sqlite3 corrupt.db .dump out.txt # edit out.txt to change ROLLBACK on the last line to COMMIT sqlite3 fixed.db out.txt In other words, walk through each table, do a query to extract the content and print it as text, then feed that text into a new database file. If you encounter corruption while doing the initial scan in ascending rowid order, go back and try again in descending rowid order. You might not get all of the content this way, but you should get a lot of it. SQLite3 version 3.7.13 Windows 7 x64 Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com 330819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The database disk image is malformed
On 13 Dec 2012, at 8:56pm, Jeff Archer jsarc...@nanotronicsimaging.com wrote: I have found that this can be repaired by SQLiteExpertPro by the Database | Repair functionality Worth noting that there's a difference between restoring the database to a condition where it no longer gives corruption errors, and restoring all the data that was in it. Don't continue to use the 'repaired' database SQLiteExpertPro gave you for real work, without first making sure it has the data you expect in it. You might find you're better-off restoring from a backup taken before the corruption occurred. I'm not taking a swing at SQLiteExpertPro specifically here. I know nothing about it. I'm just warning against a common mistake with 'repaired' files. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
