export to postgresql

Patrick Welche <[email protected]>
Newsgroups gmane.comp.db.mdb-tools.devel
Message-ID <[email protected]> 
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

-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV

_______________________________________________
mdbtools-dev mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mdbtools-dev
pwdiff (text/plain, 11.1 KB) 
Index: autogen.sh
===================================================================
RCS file: /cvsroot/mdbtools/mdbtools/autogen.sh,v
retrieving revision 1.8
diff -u -r1.8 autogen.sh
--- autogen.sh	31 Mar 2005 05:22:13 -0000	1.8
+++ autogen.sh	3 Aug 2006 11:37:59 -0000
@@ -125,12 +125,12 @@
 	echo "Making $dr/aclocal.m4 writable ..."
 	test -r $dr/aclocal.m4 && chmod u+w $dr/aclocal.m4
       fi
-      echo "Running aclocal $aclocalinclude ..."
-      aclocal $aclocalinclude
       if grep "^A[CM]_PROG_LIBTOOL" configure.in >/dev/null; then
 	echo "Running libtoolize..."
 	libtoolize --force --copy
       fi
+      echo "Running aclocal $aclocalinclude ..."
+      aclocal -I m4 $aclocalinclude
       if grep "^A[CM]_CONFIG_HEADER" configure.in >/dev/null; then
 	echo "Running autoheader..."
 	autoheader
Index: configure.in
===================================================================
RCS file: /cvsroot/mdbtools/mdbtools/configure.in,v
retrieving revision 1.38
diff -u -r1.38 configure.in
--- configure.in	24 May 2006 01:37:18 -0000	1.38
+++ configure.in	3 Aug 2006 11:37:59 -0000
@@ -3,6 +3,7 @@
 AC_CONFIG_SRCDIR(src/extras/mdb-dump.c)
 AM_INIT_AUTOMAKE
 AC_CONFIG_HEADERS(include/config.h)
+AC_CONFIG_MACRO_DIR(m4)
 
 AC_PROG_CC(gcc)
 dnl Checks for programs.
@@ -77,7 +78,7 @@
 if test "$with_unixodbc"; then
 	HAVE_ODBC=true
 	ODBC_CFLAGS="-I$with_unixodbc/include"
-	ODBC_LIBS="-L$with_unixodbc/lib"
+	ODBC_LIBS="-L$with_unixodbc/lib -Wl,-R$with_unixodbc/lib"
 	CFLAGS="$CFLAGS -DUNIXODBC"
 
 	OLDLDFLAGS=$LDFLAGS
Index: libmdb.pc.in
===================================================================
RCS file: /cvsroot/mdbtools/mdbtools/libmdb.pc.in,v
retrieving revision 1.2
diff -u -r1.2 libmdb.pc.in
--- libmdb.pc.in	15 Jan 2005 05:02:10 -0000	1.2
+++ libmdb.pc.in	3 Aug 2006 11:37:59 -0000
@@ -11,5 +11,5 @@
 Description: core MDB file support library
 Requires: glib-2.0
 Version: @VERSION@
-Libs: -L${libdir} -lmdb
+Libs: -Wl,-R${libdir} -L${libdir} -lmdb
 Cflags:
Index: libmdbsql.pc.in
===================================================================
RCS file: /cvsroot/mdbtools/mdbtools/libmdbsql.pc.in,v
retrieving revision 1.2
diff -u -r1.2 libmdbsql.pc.in
--- libmdbsql.pc.in	15 Jan 2005 05:02:10 -0000	1.2
+++ libmdbsql.pc.in	3 Aug 2006 11:37:59 -0000
@@ -11,6 +11,6 @@
 Description: libmdb based SQL engine
 Requires: libmdb
 Version: @VERSION@
-Libs: -L${libdir} -lmdbsql
+Libs: -Wl,-R${libdir} -L${libdir} -lmdbsql
 Cflags:
 
Index: doc/mdb-export.txt
===================================================================
RCS file: /cvsroot/mdbtools/mdbtools/doc/mdb-export.txt,v
retrieving revision 1.2
diff -u -r1.2 mdb-export.txt
--- doc/mdb-export.txt	7 Sep 2005 23:27:43 -0000	1.2
+++ doc/mdb-export.txt	3 Aug 2006 11:37:59 -0000
@@ -10,7 +10,7 @@
 
 OPTIONS
   -H            Supress header row
-  -Q            Don't wrap text-like fields (text, memo, date) in quotes.  If not specified text fiels will be surrounded by " (double quote) characters.
+  -Q            Don't wrap text-like fields (text, memo, date) in quotes.  If not specified text fields will be surrounded by " (double quote) characters.
   -d 		Specify an alternative column delimiter If no delimiter is specified, table names will be delimited by a , (comma) character.
   -R            Specify a row delimiter
   -I            INSERT statements (instead of CSV)
Index: include/mdbtools.h
===================================================================
RCS file: /cvsroot/mdbtools/mdbtools/include/mdbtools.h,v
retrieving revision 1.79
diff -u -r1.79 mdbtools.h
--- include/mdbtools.h	17 Dec 2005 15:59:19 -0000	1.79
+++ include/mdbtools.h	3 Aug 2006 11:37:59 -0000
@@ -32,7 +32,6 @@
 #include <ctype.h>
 #include <string.h>
 #include <glib.h>
-#include <config.h>
 
 #ifdef HAVE_ICONV
 #include <iconv.h>
Index: src/libmdb/backend.c
===================================================================
RCS file: /cvsroot/mdbtools/mdbtools/src/libmdb/backend.c,v
retrieving revision 1.25
diff -u -r1.25 backend.c
--- src/libmdb/backend.c	17 Oct 2005 12:04:34 -0000	1.25
+++ src/libmdb/backend.c	3 Aug 2006 11:37:59 -0000
@@ -103,23 +103,24 @@
 /*    Postgres data types */
 static MdbBackendType mdb_postgres_types[] = {
 		MdbBackendType_STRUCT_ELEMENT("Postgres_Unknown 0x00",0,0,0),
-		MdbBackendType_STRUCT_ELEMENT("Bool",0,0,0),
-		MdbBackendType_STRUCT_ELEMENT("Int2",0,0,0),
-		MdbBackendType_STRUCT_ELEMENT("Int4",0,0,0),
-		MdbBackendType_STRUCT_ELEMENT("Int8",0,0,0),
-		MdbBackendType_STRUCT_ELEMENT("Money",0,0,0),
-		MdbBackendType_STRUCT_ELEMENT("Float4",0,0,0),
-		MdbBackendType_STRUCT_ELEMENT("Float8",0,0,0),
-		MdbBackendType_STRUCT_ELEMENT("Timestamp",0,0,0),
+		MdbBackendType_STRUCT_ELEMENT("boolean",0,0,1),
+		MdbBackendType_STRUCT_ELEMENT("smallint",0,0,1), /* is a byte in access */
+		MdbBackendType_STRUCT_ELEMENT("integer",0,0,0),  /* is a smallint in access */
+		MdbBackendType_STRUCT_ELEMENT("integer",0,0,0),  /* is an integer in access */
+		MdbBackendType_STRUCT_ELEMENT("money",0,0,0),
+		MdbBackendType_STRUCT_ELEMENT("real",0,0,0),
+		MdbBackendType_STRUCT_ELEMENT("double precision",0,0,0),
+		MdbBackendType_STRUCT_ELEMENT("timestamp",0,0,1),
 		MdbBackendType_STRUCT_ELEMENT("Postgres_Unknown 0x09",0,0,0),
-		MdbBackendType_STRUCT_ELEMENT("Char",1,0,1),
-		MdbBackendType_STRUCT_ELEMENT("Postgres_Unknown 0x0b",0,0,0),
-		MdbBackendType_STRUCT_ELEMENT("Postgres_Unknown 0x0c",0,0,0),
+		MdbBackendType_STRUCT_ELEMENT("character varying",1,0,1),
+		MdbBackendType_STRUCT_ELEMENT("bytea",0,0,1),
+		MdbBackendType_STRUCT_ELEMENT("text",0,0,1),
 		MdbBackendType_STRUCT_ELEMENT("Postgres_Unknown 0x0d",0,0,0),
 		MdbBackendType_STRUCT_ELEMENT("Postgres_Unknown 0x0e",0,0,0),
-		MdbBackendType_STRUCT_ELEMENT("Serial",0,0,0),
-		MdbBackendType_STRUCT_ELEMENT("Postgres_Unknown 0x10",0,0,0),
+		MdbBackendType_STRUCT_ELEMENT("serial",0,0,0),
+		MdbBackendType_STRUCT_ELEMENT("numeric",1,1,0),
 };
+
 /*    MySQL data types */
 static MdbBackendType mdb_mysql_types[] = {
 		MdbBackendType_STRUCT_ELEMENT("Text",1,0,1),
@@ -246,10 +247,12 @@
 	gchar *text = NULL;  /* String to be returned */
 	static char *bound[4];  /* Bound values */
 	static MdbTableDef *table;  /* Relationships table */
-	int backend = 0;  /* Backends: 1=oracle */
+	int backend = 0;  /* Backends: 1=oracle,postgres */
 
 	if (strncmp(mdb->backend_name,"oracle",6) == 0) {
 		backend = 1;
+	} else if (strncmp(mdb->backend_name,"postgres",8) == 0) {
+		backend = 2;
 	} else {
 		if (is_init == 0) { /* the first time through */
 			is_init = 1;
@@ -301,6 +304,12 @@
 			" foreign key (", bound[0], ")"
 			" references ", bound[3], "(", bound[2], ")", NULL);
 		break;
+	  case 2:  /* postgres */
+		text = g_strconcat("alter table \"", bound[1],
+			"\" add constraint \"", bound[3], "_", bound[1],
+			"\" foreign key (\"", bound[0], "\")"
+			" references \"", bound[3], "\"(\"", bound[2], "\");", NULL);
+		break;
 	}
 
 	return (char *)text;
Index: src/util/mdb-export.c
===================================================================
RCS file: /cvsroot/mdbtools/mdbtools/src/util/mdb-export.c,v
retrieving revision 1.30
diff -u -r1.30 mdb-export.c
--- src/util/mdb-export.c	24 May 2006 00:41:52 -0000	1.30
+++ src/util/mdb-export.c	3 Aug 2006 11:37:59 -0000
@@ -26,7 +26,7 @@
 #undef MDB_BIND_SIZE
 #define MDB_BIND_SIZE 200000
 
-#define is_text_type(x) (x==MDB_TEXT || x==MDB_MEMO || x==MDB_SDATETIME)
+#define is_text_type(x) (x==MDB_TEXT || x==MDB_MEMO || x==MDB_SDATETIME || x==MDB_BOOL)
 
 static char *sanitize_name(char *str, int sanitize);
 static char *escapes(char *s);
@@ -41,11 +41,14 @@
 		for (s=col_val;*s;s++) {
 			if (strlen(quote_char)==1 && *s==quote_char[0]) {
 		/* double the char if no escape char passed */
-				if (!escape_char) {
+				//if (!escape_char) {
 					fprintf(stdout,"%s%s",quote_char,quote_char);
-				} else {
-					fprintf(stdout,"%s%s",escape_char,quote_char);
-				}
+				//} else {
+				//	fprintf(stdout,"%s%s",escape_char,quote_char);
+				//}
+			}
+			else if (strlen(escape_char)==1 && *s==escape_char[0]) {
+					fprintf(stdout,"%s%s",escape_char,escape_char);
 			}
 			else fprintf(stdout,"%c",*s);
 		}
@@ -110,6 +113,9 @@
 	if (!quote_char) {
 		quote_char = (char *) g_strdup("\"");
 	}
+	if (!escape_char) {
+		escape_char = (char *) g_strdup("\\");
+	}
 	if (!delimiter) {
 		delimiter = (char *) g_strdup(",");
 	}
@@ -174,11 +180,11 @@
 	}
 	if (header_row) {
 		col=g_ptr_array_index(table->columns,0);
-		fprintf(stdout,"%s",col->name);
+		fprintf(stdout,"\"%s\"",col->name);
 		for (j=1;j<table->num_cols;j++) {
 			col=g_ptr_array_index(table->columns,j);
 			fprintf(stdout,delimiter);
-			fprintf(stdout,"%s",col->name);
+			fprintf(stdout,"\"%s\"",col->name);
 		}
 		fprintf(stdout,"\n");
 	}
@@ -186,12 +192,12 @@
 	while(mdb_fetch_row(table)) {
 
 		if (insert_statements) {
-			fprintf(stdout, "INSERT INTO %s (",
+			fprintf(stdout, "INSERT INTO \"%s\" (",
 				sanitize_name(argv[optind + 1],sanitize));
 			for (j=0;j<table->num_cols;j++) {
 				if (j>0) fprintf(stdout, ", ");
 				col=g_ptr_array_index(table->columns,j);
-				fprintf(stdout,"%s", sanitize_name(col->name,sanitize));
+				fprintf(stdout,"\"%s\"", sanitize_name(col->name,sanitize));
 			} 
 			fprintf(stdout, ") VALUES (");
 		}
Index: src/util/mdb-schema.c
===================================================================
RCS file: /cvsroot/mdbtools/mdbtools/src/util/mdb-schema.c,v
retrieving revision 1.21
diff -u -r1.21 mdb-schema.c
--- src/util/mdb-schema.c	17 Jun 2005 02:39:45 -0000	1.21
+++ src/util/mdb-schema.c	3 Aug 2006 11:37:59 -0000
@@ -130,11 +130,11 @@
 	MdbColumn *col;
 
 	/* drop the table if it exists */
-	fprintf (stdout, "DROP TABLE %s%s;\n", (namespace) ? namespace : "",
+	fprintf (stdout, "DROP TABLE %s\"%s\";\n", (namespace) ? namespace : "",
 		sanitize_name(entry->object_name, sanitize));
 
 	/* create the table */
-	fprintf (stdout, "CREATE TABLE %s%s\n", (namespace) ? namespace : "",
+	fprintf (stdout, "CREATE TABLE %s\"%s\"\n", (namespace) ? namespace : "",
 		sanitize_name(entry->object_name, sanitize));
 	fprintf (stdout, " (\n");
 	       	       
@@ -148,7 +148,7 @@
 	for (i = 0; i < table->num_cols; i++) {
 		col = g_ptr_array_index (table->columns, i);
 		   
-		fprintf (stdout, "\t%s\t\t\t%s", sanitize_name(col->name,sanitize), 
+		fprintf (stdout, "\t\"%s\"\t\t\t%s", sanitize_name(col->name,sanitize), 
 		mdb_get_coltype_string (mdb->default_backend, col->col_type));
 		   
 		if (mdb_coltype_takes_length(mdb->default_backend, 
Index: src/util/mdb-tables.c
===================================================================
RCS file: /cvsroot/mdbtools/mdbtools/src/util/mdb-tables.c,v
retrieving revision 1.17
diff -u -r1.17 mdb-tables.c
--- src/util/mdb-tables.c	15 Jan 2005 05:02:05 -0000	1.17
+++ src/util/mdb-tables.c	3 Aug 2006 11:37:59 -0000
@@ -142,11 +142,11 @@
 			continue;
 
        		if (line_break) 
-			fprintf (stdout, "%s\n", entry->object_name);
+			fprintf (stdout, "\"%s\"\n", entry->object_name);
 		else if (delimiter) 
-			fprintf (stdout, "%s%s", entry->object_name, delimiter);
+			fprintf (stdout, "\"%s\"%s", entry->object_name, delimiter);
 		else 
-			fprintf (stdout, "%s ", entry->object_name);
+			fprintf (stdout, "\"%s\" ", entry->object_name);
 	}
 	if (!line_break) 
 		fprintf (stdout, "\n");
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.