[sqlite] How to disable and enable constraints in SQLite ?
Hello all, i have to copy data from some database into an SQLite database. The fasted way for me would be to copy simply all tables. To do so i have to disable all SQLite - constraints, copy data and finally enable the constraints again. How can this be done ? Thanx in advance. Andreas -- GMX DSL Doppel-Flat ab 19,99 euro;/mtl.! Jetzt auch mit gratis Notebook-Flat! http://portal.gmx.net/de/go/dsl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to disable and enable constraints in SQLite ?
On 19 Nov 2010, at 1:52pm, Andreas Mucha wrote: i have to copy data from some database into an SQLite database. The fasted way for me would be to copy simply all tables. To do so i have to disable all SQLite - constraints, copy data and finally enable the constraints again. There's no easy way to do that in SQLite, I'm afraid. The fastest non-custom way to fill a table with data is usually the following: 1) CREATE TABLE with the variable definitions you want it to have 2) BEGIN TRANSACTION 3) INSERT all the data 4) END TRANSACTION 5) CREATE INDEX for each index 6) CREATE TRIGGER for each trigger You can either write your conversion software to do this, or write your conversion software to create a big text file with the appropriate SQL commands, and use the '.read' command of the command-line tool to turn it into a database file: http://www.sqlite.org/sqlite.html Using a long text file is often /far/ easier to debug. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to disable and enable constraints in SQLite ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/19/2010 05:52 AM, Andreas Mucha wrote: i have to copy data from some database into an SQLite database. The fasted way for me would be to copy simply all tables. To do so i have to disable all SQLite - constraints, copy data and finally enable the constraints again. How can this be done ? Disabling normal constraints doesn't make any sense. For example if you have a 'not null' constraint then disabling while loading would allow null values in the data. Foreign keys can be disabled with PRAGMA foreign_keys=OFF; and you would have to do so if you had any. There are basically two ways of getting at the data. One would be to implement virtual tables where the backend talks to your existing database system. Copies can then be done 'insert into ... select ... from ...' or 'create table ... as select ... from ...'. This approach would also let you only copy some of the data into SQLite while letting the rest remain in your existing database system and you could transparently use both at the same time. The second approach is to create a dump - a text file with raw SQL statements in it. This is easy to develop as you keep reading the SQL text until you are happy with it. That text is also a good way of doing backups, comparisons between databases etc. The SQLite shell includes a .dump command so you can get some idea of what they should look like. If you are just doing this copy data process once then you'll do great. If you will be repeating the process many times then you'll need to make your dump more robust. Here is what my dump code outputs when dumping a SQLite database to give you an idea: - - Disables foreign keys if they are used (this must be done outside a transaction) - - BEGIN TRANSACTION For each table: - - DROP TABLE IF EXISTS name - - CREATE TABLE name ... - - INSERT for each row data - - Creates any indices - - Creates any triggers - - Creates any views last as they could involve multiple tables - - Populates the sqlite_sequence table if any tables have autoincrement primary keys - - Runs analyze for any tables that had that before - - COMMIT TRANSACTION - - Enable foreign keys If you are happy with Python as a language then I'll toot the benefits of my APSW wrapper around SQLite. You can very easily implement virtual tables. It also has a builtin shell that produces nicer dumps than the SQLite shell (eg there are comments in it) and you can easily add your own commands if you want to provide a more interactive experience doing the data importing. http://apidoc.apsw.googlecode.com/hg/vtable.html http://apidoc.apsw.googlecode.com/hg/shell.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzn6U0ACgkQmOOfHg372QStPQCeLzOanb7LHlqLtBO8qV33+3j/ Xt4An0SKbgdGWqJ43FUr8B09V3XP8JK6 =AZUr -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to disable and enable constraints in SQLite ?
Roger, I tried building your APSW wrapper. It built fine, but running python setup.py test failed: ERROR: Verify VFS functionality -- Traceback (most recent call last): File /Users/cwolf/src/apsw-3.6.23.1-r1/tests.py, line 3526, in testVFS vfs=ObfuscatedVFS() File /Users/cwolf/src/apsw-3.6.23.1-r1/tests.py, line 3521, in __init__ apsw.VFS.__init__(self, self.vfsname, self.basevfs) ValueError: Base vfs implements version 2 of vfs spec, but apsw only supports version 1 I am building apsw-3.6.23.1-r1 against sqlite3-3.7.3 (amalgamation pulled down via fetch) on MacOS 10.5. Does this mean I won't be able to do virtual table from Python? Thanks, -Chris Roger Binns wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/19/2010 05:52 AM, Andreas Mucha wrote: i have to copy data from some database into an SQLite database. The fasted way for me would be to copy simply all tables. To do so i have to disable all SQLite - constraints, copy data and finally enable the constraints again. How can this be done ? Disabling normal constraints doesn't make any sense. For example if you have a 'not null' constraint then disabling while loading would allow null values in the data. Foreign keys can be disabled with PRAGMA foreign_keys=OFF; and you would have to do so if you had any. There are basically two ways of getting at the data. One would be to implement virtual tables where the backend talks to your existing database system. Copies can then be done 'insert into ... select ... from ...' or 'create table ... as select ... from ...'. This approach would also let you only copy some of the data into SQLite while letting the rest remain in your existing database system and you could transparently use both at the same time. The second approach is to create a dump - a text file with raw SQL statements in it. This is easy to develop as you keep reading the SQL text until you are happy with it. That text is also a good way of doing backups, comparisons between databases etc. The SQLite shell includes a .dump command so you can get some idea of what they should look like. If you are just doing this copy data process once then you'll do great. If you will be repeating the process many times then you'll need to make your dump more robust. Here is what my dump code outputs when dumping a SQLite database to give you an idea: - - Disables foreign keys if they are used (this must be done outside a transaction) - - BEGIN TRANSACTION For each table: - - DROP TABLE IF EXISTS name - - CREATE TABLE name ... - - INSERT for each row data - - Creates any indices - - Creates any triggers - - Creates any views last as they could involve multiple tables - - Populates the sqlite_sequence table if any tables have autoincrement primary keys - - Runs analyze for any tables that had that before - - COMMIT TRANSACTION - - Enable foreign keys If you are happy with Python as a language then I'll toot the benefits of my APSW wrapper around SQLite. You can very easily implement virtual tables. It also has a builtin shell that produces nicer dumps than the SQLite shell (eg there are comments in it) and you can easily add your own commands if you want to provide a more interactive experience doing the data importing. http://apidoc.apsw.googlecode.com/hg/vtable.html http://apidoc.apsw.googlecode.com/hg/shell.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzn6U0ACgkQmOOfHg372QStPQCeLzOanb7LHlqLtBO8qV33+3j/ Xt4An0SKbgdGWqJ43FUr8B09V3XP8JK6 =AZUr -END PGP SIGNATURE- ___ 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
Re: [sqlite] How to disable and enable constraints in SQLite ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/20/2010 08:33 AM, Chris Wolf wrote: I tried building your APSW wrapper. It built fine, but running python setup.py test failed: ERROR: Verify VFS functionality -- Traceback (most recent call last): File /Users/cwolf/src/apsw-3.6.23.1-r1/tests.py, line 3526, in testVFS vfs=ObfuscatedVFS() File /Users/cwolf/src/apsw-3.6.23.1-r1/tests.py, line 3521, in __init__ apsw.VFS.__init__(self, self.vfsname, self.basevfs) ValueError: Base vfs implements version 2 of vfs spec, but apsw only supports version 1 I am building apsw-3.6.23.1-r1 against sqlite3-3.7.3 (amalgamation pulled down via fetch) on MacOS 10.5. Does this mean I won't be able to do virtual table from Python? The failing test is for VFS functionality which deals with SQLite's interface to the underlying operating system - eg file read and write(*). It has no effect on support for virtual tables which will work fine with any combination of APSW and SQLite after they introduced virtual tables. In any event you are using APSW from the beginning of April which predates SQLite introducing version 2 VFS. Try a more recent APSW: http://code.google.com/p/apsw/ Unfortunately Google's left and right hands have issues talking to each other so searching for APSW documentation brings up old pages from a Google code site I can't delete and ignores the more recent documentation also on Google code. The links from the page above are however correct. (*) And in particular the issue is with functionality in APSW that lets you inherit from an existing VFS such as if you wanted to augment or override a few methods rather than write an entire one from scratch. When doing this it is a requirement that the VFS API versions match hence the message. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzoAy8ACgkQmOOfHg372QSCYwCeKjU0JeBJ5/6jhk/h1Z7tnzt5 HEYAoMSklYJ7Hyr0ZnNBoNHuOhTa25RH =ReHP -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
