Converting between Databases

[Ed Singleton]

Is there anyway with SQLObject to output all the sql necessary to
recreate your table with the data as well as the table definitions?
If so would it be possible to connect to a Postgres database and
create the sql for an sqlite database?

As SQLObject clearly can create the sql for all these databases, it
seems like it should be possible.

If not does anyone know any other way to convert postgres's sql to sql
suitable for sqlite?

Thanks

Ed

[Christopher Arndt]

Ed Singleton schrieb:

> If not does anyone know any other way to convert postgres's sql to sql
> suitable for sqlite?

I have not tried this, but if your model isn't too complex:

- change the database URL in your project's config file to point to a
non-existent SQLite database

- do 'tg-admin sql create'

- Dump your Postgres database with

pg_dump --attribute-inserts --no-owner --no-privileges --data-only mydb >data.sql

- Import into the SQLite database with 'sqlite3 db.sqlite <data.sql'

Since SQLite doesn't know anything about the different data types, this should
work. I don't know about DATE etc. columns though.

HTH, Chris

[Ed Singleton]

Thanks for this. I tried something similar and it mostly works. But
several data types cause errors, the main one being Bool cols. I
tried using regexs to fix it, but it's far too complex to use regexs
on (some of the data contains examples of sql, which causes
nightmares).

It just seems that as SQLObject can create the sql to insert it into
the database, there most be a way to get it to out put the sql rather
than execute it.

Ed