-----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
