Patch for Sqlite3 backend

[Jarl Friis-2]

I have made a patch.

There are two user-level features:
1) New sqlite3 backend (for mdb-schema).
2) The mdb-export utility does no longer need table name as argument,
if ommitted loops over all tables.

Code level changes: Minor refactoring

The sqlite3 backend is still under investigation, I want it to match
some of the sqliteodbc specific row type definitions.

Now an mdb file can be converted into a sqlite3 file in two
steps. first use mdb-schema, then mdb-export.

Could someone please commit this to CVS.

Jarl


Index: include/mdbtools.h
===================================================================
RCS file: /cvsroot/mdbtools/mdbtools/include/mdbtools.h,v
retrieving revision 1.81
diff -u -w -r1.81 mdbtools.h
--- include/mdbtools.h 5 Mar 2007 23:34:47 -0000 1.81
+++ include/mdbtools.h 24 Oct 2007 12:36:54 -0000
@@ -412,6 +412,7 @@
 extern GPtrArray *mdb_read_catalog(MdbHandle *mdb, int obj_type);
 extern void mdb_dump_catalog(MdbHandle *mdb, int obj_type);
 extern char *mdb_get_objtype_string(int obj_type);
+extern char *mdb_sanitize_name(char *str, int sanitize);
 
 /* table.c */
 extern MdbTableDef *mdb_alloc_tabledef(MdbCatalogEntry *entry);
Index: src/libmdb/backend.c
===================================================================
RCS file: /cvsroot/mdbtools/mdbtools/src/libmdb/backend.c,v
retrieving revision 1.25
diff -u -w -r1.25 backend.c
--- src/libmdb/backend.c 17 Oct 2005 12:04:34 -0000 1.25
+++ src/libmdb/backend.c 24 Oct 2007 12:36:55 -0000
@@ -58,6 +58,27 @@
  MdbBackendType_STRUCT_ELEMENT("Numeric",1,1,0)
 };
 
+   /*    Access data types */
+static MdbBackendType mdb_sqlite3_types[] = {
+ MdbBackendType_STRUCT_ELEMENT("Sqlite3_Unknown 0x00", 0,0,0),
+ MdbBackendType_STRUCT_ELEMENT("Byte", 0,0,0),
+ MdbBackendType_STRUCT_ELEMENT("Byte", 0,0,0),
+ MdbBackendType_STRUCT_ELEMENT("Integer", 0,0,0),
+ MdbBackendType_STRUCT_ELEMENT("Integer", 0,0,0),
+ MdbBackendType_STRUCT_ELEMENT("Numeric", 0,0,0),
+ MdbBackendType_STRUCT_ELEMENT("Integer", 0,0,0),
+ MdbBackendType_STRUCT_ELEMENT("Numeric", 0,0,0),
+ MdbBackendType_STRUCT_ELEMENT("DateTime", 0,0,1),
+ MdbBackendType_STRUCT_ELEMENT("Sqlite3_Unknown 0x09", 0,0,0),
+ MdbBackendType_STRUCT_ELEMENT("Text", 1,0,1),
+ MdbBackendType_STRUCT_ELEMENT("Blob", 1,0,1),
+ MdbBackendType_STRUCT_ELEMENT("Text",1,0,1),
+ MdbBackendType_STRUCT_ELEMENT("Sqlite3_Unknown 0x0d",0,0,0),
+ MdbBackendType_STRUCT_ELEMENT("Sqlite3_Unknown 0x0e",0,0,0),
+ MdbBackendType_STRUCT_ELEMENT("Integer",0,0,0),
+ MdbBackendType_STRUCT_ELEMENT("Numeric",1,1,0)
+};
+
 /*    Oracle data types */
 static MdbBackendType mdb_oracle_types[] = {
  MdbBackendType_STRUCT_ELEMENT("Oracle_Unknown 0x00",0,0,0),
@@ -176,6 +197,7 @@
  mdb_register_backend(mdb_oracle_types, "oracle");
  mdb_register_backend(mdb_postgres_types, "postgres");
  mdb_register_backend(mdb_mysql_types, "mysql");
+ mdb_register_backend(mdb_sqlite3_types, "sqlite3");
 }
 void mdb_register_backend(MdbBackendType *backend_type, char *backend_name)
 {
Index: src/libmdb/catalog.c
===================================================================
RCS file: /cvsroot/mdbtools/mdbtools/src/libmdb/catalog.c,v
retrieving revision 1.30
diff -u -w -r1.30 catalog.c
--- src/libmdb/catalog.c 17 Jun 2005 02:42:18 -0000 1.30
+++ src/libmdb/catalog.c 24 Oct 2007 12:36:55 -0000
@@ -137,3 +137,41 @@
  return;
 }
 
+char *mdb_sanitize_name(char *str, int sanitize)
+{
+  
+  static char* keywords[] = { "group",
+                              "table",
+                              "default",
+                              NULL };//TODO: This list is probably specific to the backend.
+  static char namebuf[256];
+  static char lowernamebuf[256];
+ char *p = namebuf;
+ char *lp = lowernamebuf;
+
+ if (!sanitize)
+ return str;
+
+ while (*str) {
+ *p = isalnum(*str) ? *str : '_';
+                *lp = tolower(*p);
+ p++;
+ lp++;
+ str++;
+ }
+ *lp = 0;
+        
+        int accepted = 1;
+        int j = 0;
+        while(accepted && (keywords[j] != NULL)){
+          accepted = strcmp(lowernamebuf, keywords[j]) == 0 ? 0 : 1;
+          ++j;
+        }
+        if(!accepted){
+          *p = '_';
+          ++p;
+        }
+ *p = 0;
+
+ return namebuf;
+}
Index: src/util/mdb-export.c
===================================================================
RCS file: /cvsroot/mdbtools/mdbtools/src/util/mdb-export.c,v
retrieving revision 1.31
diff -u -w -r1.31 mdb-export.c
--- src/util/mdb-export.c 6 Mar 2007 01:16:54 -0000 1.31
+++ src/util/mdb-export.c 24 Oct 2007 12:36:55 -0000
@@ -28,9 +28,43 @@
 
 #define is_text_type(x) (x==MDB_TEXT || x==MDB_MEMO || x==MDB_SDATETIME)
 
-static char *sanitize_name(char *str, int sanitize);
 static char *escapes(char *s);
 
+typedef struct
+{
+  char *table_name;
+  char *delimiter;
+  char *row_delimiter;
+  char *quote_char;
+  char *escape_char;
+  char header_row;
+  char quote_text;
+  char insert_statements;
+  char sanitize;
+} mdb_export_context;
+
+void mdb_export_context_ctor(mdb_export_context * const context)
+{
+  context->table_name = NULL;
+  context->delimiter = NULL;
+  context->row_delimiter = NULL;
+  context->quote_char = NULL;
+  context->escape_char = NULL;
+  context->header_row = 1;
+  context->quote_text = 1;
+  context->insert_statements = 0;
+  context->sanitize = 0;
+}
+
+void mdb_export_context_dtor(mdb_export_context * const context)
+{
+  if(context->table_name) g_free(context->table_name);
+  g_free (context->delimiter);
+  g_free (context->row_delimiter);
+  g_free (context->quote_char);
+  if (context->escape_char) g_free (context->escape_char);
+}
+
 void
 print_col(gchar *col_val, int quote_text, int col_type, char *quote_char, char *escape_char)
 {
@@ -54,75 +88,150 @@
  fprintf(stdout,"%s",col_val);
  }
 }
+
+void
+export_table(mdb_export_context const * const context, MdbHandle * const mdb, MdbTableDef * const table){
+  MdbColumn *col;
+  char **bound_values;
+  int  *bound_lens;
+  unsigned int j;
+
+  mdb_read_columns(table);
+  mdb_rewind_table(table);
+
+  bound_values = (char **) g_malloc(table->num_cols * sizeof(char *));
+  bound_lens = (int *) g_malloc(table->num_cols * sizeof(int));
+  for (j=0;j<table->num_cols;j++) {
+    bound_values[j] = (char *) g_malloc0(MDB_BIND_SIZE);
+    mdb_bind_column(table, j+1, bound_values[j], &bound_lens[j]);
+  }
+  if (context->header_row) {
+    col=g_ptr_array_index(table->columns,0);
+    fprintf(stdout,"%s",mdb_sanitize_name(col->name,context->sanitize));
+    for (j=1;j<table->num_cols;j++) {
+      col=g_ptr_array_index(table->columns,j);
+      fprintf(stdout,context->delimiter);
+      fprintf(stdout,"%s",mdb_sanitize_name(col->name,context->sanitize));
+    }
+    fprintf(stdout,"\n");
+  }
+
+  while(mdb_fetch_row(table)) {
+
+    if (context->insert_statements) {
+      fprintf(stdout, "INSERT INTO %s (",
+              mdb_sanitize_name(table->name ,context->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", mdb_sanitize_name(col->name,context->sanitize));
+      }
+      fprintf(stdout, ") VALUES (");
+    }
+
+    for (j=0;j<table->num_cols;j++) {
+      col=g_ptr_array_index(table->columns,j);
+      if ((col->col_type == MDB_OLE)
+          && ((j==0) || (col->cur_value_len))) {
+        mdb_ole_read(mdb, col, bound_values[j], MDB_BIND_SIZE);
+      }
+      if (j>0) {
+        fprintf(stdout,context->delimiter);
+      }
+      if (context->insert_statements && !bound_lens[j]) {
+        print_col("NULL",0,col->col_type, context->quote_char, context->escape_char);
+      } else {
+        print_col(bound_values[j], context->quote_text, col->col_type, context->quote_char, context->escape_char);
+      }
+    }
+    if (context->insert_statements) fprintf(stdout,");");
+    fprintf(stdout, context->row_delimiter);
+  }
+  for (j=0;j<table->num_cols;j++) {
+    g_free(bound_values[j]);
+  }
+  g_free(bound_values);
+  g_free(bound_lens);
+}
+
+void export_all_tables(mdb_export_context const * const context, MdbHandle * const mdb){
+  unsigned int   i;
+  MdbCatalogEntry *entry;
+  MdbTableDef *table;
+
+  for (i=0; i < mdb->num_catalog; i++) {
+    entry = g_ptr_array_index (mdb->catalog, i);
+    if (entry->object_type == MDB_TABLE) {
+      if (mdb_is_user_table(entry)) {
+        table = mdb_read_table_by_name(mdb, entry->object_name, MDB_TABLE);
+        export_table(context, mdb, table);
+        mdb_free_tabledef(table);
+      }
+    }
+  }
+  
+}
+
 int
 main(int argc, char **argv)
 {
  unsigned int j;
  MdbHandle *mdb;
  MdbTableDef *table;
- MdbColumn *col;
- char **bound_values;
- int  *bound_lens;
- char *delimiter = NULL;
- char *row_delimiter = NULL;
- char *quote_char = NULL;
- char *escape_char = NULL;
- char header_row = 1;
- char quote_text = 1;
- char insert_statements = 0;
- char sanitize = 0;
+        mdb_export_context context;
  int  opt;
 
+        mdb_export_context_ctor(&context);
  while ((opt=getopt(argc, argv, "HQq:X:d:D:R:IS"))!=-1) {
  switch (opt) {
  case 'H':
- header_row = 0;
+ context.header_row = 0;
  break;
  case 'Q':
- quote_text = 0;
+ context.quote_text = 0;
  break;
  case 'q':
- quote_char = (char *) g_strdup(optarg);
+ context.quote_char = (char *) g_strdup(optarg);
  break;
  case 'd':
- delimiter = escapes(optarg);
+ context.delimiter = escapes(optarg);
  break;
  case 'R':
- row_delimiter = escapes(optarg);
+ context.row_delimiter = escapes(optarg);
  break;
  case 'I':
- insert_statements = 1;
- header_row = 0;
+ context.insert_statements = 1;
+ context.header_row = 0;
  break;
  case 'S':
- sanitize = 1;
+ context.sanitize = 1;
  break;
  case 'D':
  mdb_set_date_fmt(optarg);
  break;
  case 'X':
- escape_char = (char *) g_strdup(optarg);
+ context.escape_char = (char *) g_strdup(optarg);
  break;
  default:
  break;
  }
  }
- if (!quote_char) {
- quote_char = (char *) g_strdup("\"");
+ if (!context.quote_char) {
+ context.quote_char = (char *) g_strdup("\"");
  }
- if (!delimiter) {
- delimiter = (char *) g_strdup(",");
+ if (!context.delimiter) {
+ context.delimiter = (char *) g_strdup(",");
  }
- if (!row_delimiter) {
- row_delimiter = (char *) g_strdup("\n");
+ if (!context.row_delimiter) {
+ context.row_delimiter = (char *) g_strdup("\n");
  }
 
  /*
  ** optind is now the position of the first non-option arg,
  ** see getopt(3)
  */
- if (argc-optind < 2) {
- fprintf(stderr,"Usage: %s [options] <file> <table>\n",argv[0]);
+ if (argc-optind < 1) {
+ fprintf(stderr,"Usage: %s [options] <file> [<table>]\n",argv[0]);
  fprintf(stderr,"where options are:\n");
  fprintf(stderr,"  -H             supress header row\n");
  fprintf(stderr,"  -Q             don't wrap text-like fields in quotes\n");
@@ -133,123 +242,47 @@
  fprintf(stderr,"  -S             Sanitize names (replace spaces etc. with underscore)\n");
  fprintf(stderr,"  -q <char>      Use <char> to wrap text-like fields. Default is \".\n");
  fprintf(stderr,"  -X <char>      Use <char> to escape quoted characters within a field. Default is doubling.\n");
- g_free (delimiter);
- g_free (row_delimiter);
- g_free (quote_char);
- if (escape_char) g_free (escape_char);
+                mdb_export_context_dtor(&context);
  exit(1);
  }
 
  mdb_init();
 
  if (!(mdb = mdb_open(argv[optind], MDB_NOFLAGS))) {
- g_free (delimiter);
- g_free (row_delimiter);
- g_free (quote_char);
- if (escape_char) g_free (escape_char);
+          mdb_export_context_dtor(&context);
  mdb_exit();
  exit(1);
  }
 
- table = mdb_read_table_by_name(mdb, argv[argc-1], MDB_TABLE);
+        if(optind+2 == argc){
+          context.table_name = g_strdup(argv[optind+1]);
+          table = mdb_read_table_by_name(mdb, context.table_name, MDB_TABLE);
  if (!table) {
- fprintf(stderr, "Error: Table %s does not exist in this database.\n", argv[argc-1]);
- g_free (delimiter);
- g_free (row_delimiter);
- g_free (quote_char);
- if (escape_char) g_free (escape_char);
+            fprintf(stderr, "Error: Table %s does not exist in this database.\n", context.table_name);
+            mdb_export_context_dtor(&context);
  mdb_close(mdb);
  mdb_exit();
  exit(1);
  }
 
- mdb_read_columns(table);
- mdb_rewind_table(table);
-
- bound_values = (char **) g_malloc(table->num_cols * sizeof(char *));
- bound_lens = (int *) g_malloc(table->num_cols * sizeof(int));
- for (j=0;j<table->num_cols;j++) {
- bound_values[j] = (char *) g_malloc0(MDB_BIND_SIZE);
- mdb_bind_column(table, j+1, bound_values[j], &bound_lens[j]);
- }
- if (header_row) {
- col=g_ptr_array_index(table->columns,0);
- fprintf(stdout,"%s",sanitize_name(col->name,sanitize));
- for (j=1;j<table->num_cols;j++) {
- col=g_ptr_array_index(table->columns,j);
- fprintf(stdout,delimiter);
- fprintf(stdout,"%s",sanitize_name(col->name,sanitize));
- }
- fprintf(stdout,"\n");
- }
-
- while(mdb_fetch_row(table)) {
-
- if (insert_statements) {
- 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, ") VALUES (");
- }
-
- for (j=0;j<table->num_cols;j++) {
- col=g_ptr_array_index(table->columns,j);
- if ((col->col_type == MDB_OLE)
- && ((j==0) || (col->cur_value_len))) {
- mdb_ole_read(mdb, col, bound_values[j], MDB_BIND_SIZE);
- }
- if (j>0) {
- fprintf(stdout,delimiter);
- }
- if (insert_statements && !bound_lens[j]) {
- print_col("NULL",0,col->col_type, quote_char, escape_char);
+          export_table(&context, mdb, table);
+          mdb_free_tabledef(table);
  } else {
- print_col(bound_values[j], quote_text, col->col_type, quote_char, escape_char);
- }
- }
- if (insert_statements) fprintf(stdout,")");
- fprintf(stdout, row_delimiter);
+          /* read the catalog */
+          if (!mdb_read_catalog (mdb, MDB_TABLE)) {
+            fprintf(stderr,"File does not appear to be an Access database\n");
+            exit(1);
  }
- for (j=0;j<table->num_cols;j++) {
- g_free(bound_values[j]);
+          export_all_tables(&context, mdb);
  }
- g_free(bound_values);
- g_free(bound_lens);
- mdb_free_tabledef(table);
 
- g_free (delimiter);
- g_free (row_delimiter);
- g_free (quote_char);
- if (escape_char) g_free (escape_char);
+        mdb_export_context_dtor(&context);
  mdb_close(mdb);
  mdb_exit();
 
  exit(0);
 }
 
-static char *sanitize_name(char *str, int sanitize)
-{
- static char namebuf[256];
- char *p = namebuf;
-
- if (!sanitize)
- return str;
-
- while (*str) {
- *p = isalnum(*str) ? *str : '_';
- p++;
- str++;
- }
-
- *p = 0;
-
- return namebuf;
-}
-
 static char *escapes(char *s)
 {
  char *d = (char *) g_strdup(s);
Index: src/util/mdb-schema.c
===================================================================
RCS file: /cvsroot/mdbtools/mdbtools/src/util/mdb-schema.c,v
retrieving revision 1.21
diff -u -w -r1.21 mdb-schema.c
--- src/util/mdb-schema.c 17 Jun 2005 02:39:45 -0000 1.21
+++ src/util/mdb-schema.c 24 Oct 2007 12:36:55 -0000
@@ -24,7 +24,6 @@
 #include "dmalloc.h"
 #endif
 
-static char *sanitize_name(char *str, int sanitize);
 static void generate_table_schema(MdbCatalogEntry *entry, char *namespace, int sanitize);
 
 int
@@ -131,11 +130,11 @@
 
  /* drop the table if it exists */
  fprintf (stdout, "DROP TABLE %s%s;\n", (namespace) ? namespace : "",
- sanitize_name(entry->object_name, sanitize));
+ mdb_sanitize_name(entry->object_name, sanitize));
 
  /* create the table */
  fprintf (stdout, "CREATE TABLE %s%s\n", (namespace) ? namespace : "",
- sanitize_name(entry->object_name, sanitize));
+ mdb_sanitize_name(entry->object_name, sanitize));
  fprintf (stdout, " (\n");
             
  table = mdb_read_table (entry);
@@ -148,7 +147,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", mdb_sanitize_name(col->name,sanitize),
  mdb_get_coltype_string (mdb->default_backend, col->col_type));
   
  if (mdb_coltype_takes_length(mdb->default_backend,
@@ -174,22 +173,4 @@
  mdb_free_tabledef (table);
 }
 
-static char *sanitize_name(char *str, int sanitize)
-{
- static char namebuf[256];
- char *p = namebuf;
-
- if (!sanitize)
- return str;
-
- while (*str) {
- *p = isalnum(*str) ? *str : '_';
- p++;
- str++;
- }
-
- *p = 0;
-
- return namebuf;
-}
 


--
Jarl Friis
Gavia Technologies ApS
Omøgade 8, 2.sal
2100 København Ø.
Denmark
Phone:  [phone]
E-mail: jarl@...
LinkedIn: www.linkedin.com/in/jarlfriis

-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems?  Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
_______________________________________________
mdbtools-dev mailing list
mdbtools-dev@...
https://lists.sourceforge.net/lists/listinfo/mdbtools-dev