|
From: Patrick W. <pr...@ne...> - 2006-08-03 11:58:32
|
I have had some fun exporting a M$ Access database to PostgreSQL, and found
the enclose changes helpful. This is my first posting, so really, this is
just a "do you think this is useful / how would you like patches best
submitted" note.
autogen.sh, configure.in are just because I use cvs-autotools
libmdb.pc.in, libmdbsql.pc.in are just because I use an OS which uses -rpath
doc/mdb-export.text is just fixing a typo
mdbtools.h fixed a compile problem
backend.c:
- data types mapping - we might as well use SQL datatypes and let the
particular database do its own aliasing - it may mean that those
tables become redundant?
- allow foreign key constraints to be output for postgesql too, just
adding a few "
mdb-export.c:
- make boolean a text type because in postgresql:
postgres=# create table test ( a boolean);
CREATE TABLE
postgres=# insert into test values ( 0 );
ERROR: column "a" is of type boolean but expression is of type integer
HINT: You will need to rewrite or cast the expression.
postgres=# insert into test values ( '0' );
INSERT 0 1
postgres=# select * from test;
a
---
f
(1 row)
so 0 (false in access) wants to be quoted, so that string -> boolean
conversion occurs.
and the rest is all about quoting. In PostgreSQL, to preserve case and
spaces with table/column names, we need double quotes.
create table "Some Table" -> Some Table
create table Some Table -> error
create table "SomeTable" -> SomeTable
create table SomeTable -> sometable
so I pop double quotes everywhere.
Another niggle is that if an escape character is defined, ' is then quoted
as escape char ' rather than '' .
The patches need discussing - I didn't go out of my way not to break other
backends, and it seems some of the above would be better off in an extension
to *mdb_backends.
Thoughts?
Cheers,
Patrick
|