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
