[mdb-dev] export to postgresql

[Patrick W. <pr...@ne...>]

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