More patches soon ...

Index: mdbtools-0.6pre1/src/util/mdb-export.c
===================================================================
--- mdbtools-0.6pre1.orig/src/util/mdb-export.c
+++ mdbtools-0.6pre1/src/util/mdb-export.c
@@ -82,6 +82,8 @@
 	char sanitize = 0;
 	char *namespace = "";
 	int  opt;
+	char *value;
+	size_t length;
 
 	while ((opt=getopt(argc, argv, "HQq:X:d:D:R:I:N:S"))!=-1) {
 		switch (opt) {
@@ -168,6 +170,7 @@
 	if (insert_dialect)
 		if (!mdb_set_default_backend(mdb, insert_dialect)) {
 			fprintf(stderr, "Invalid backend type\n");
+			if (escape_char) g_free (escape_char);
 			mdb_exit();
 			exit(1);
 		}
@@ -227,22 +230,26 @@
 		}
 
 		for (j=0;j<table->num_cols;j++) {
+			if (j>0)
+				fputs(delimiter, stdout);
 			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 (!bound_lens[j]) {
-				print_col(insert_dialect?"NULL":"",0,col->col_type, 0, quote_char, escape_char);
+				if (insert_dialect)
+					fputs("NULL", stdout);
 			} else {
-				print_col(bound_values[j], quote_text, col->col_type, bound_lens[j], quote_char, escape_char);
+				if (col->col_type == MDB_OLE) {
+					value = mdb_ole_read_full(mdb, col, &length);
+				} else {
+					value = bound_values[j];
+					length = bound_lens[j];
+				}
+				print_col(value, quote_text, col->col_type, length, quote_char, escape_char);
+				if (col->col_type == MDB_OLE)
+					free(value);
 			}
 		}
-		if (insert_dialect) fprintf(stdout,");");
-		fprintf(stdout, row_delimiter);
+		if (insert_dialect) fputs(");", stdout);
+		fputs(row_delimiter, stdout);
 	}
 	for (j=0;j<table->num_cols;j++) {
 		g_free(bound_values[j]);
@@ -258,7 +265,7 @@
 	mdb_close(mdb);
 	mdb_exit();
 
-	exit(0);
+	return 0;
 }
 
 static char *escapes(char *s)
Index: mdbtools-0.6pre1/src/libmdb/data.c
===================================================================
--- mdbtools-0.6pre1.orig/src/libmdb/data.c
+++ mdbtools-0.6pre1/src/libmdb/data.c
@@ -19,6 +19,7 @@
 
 #include "mdbtools.h"
 #include "time.h"
+#include "math.h"
 
 #ifdef DMALLOC
 #include "dmalloc.h"
@@ -346,8 +347,8 @@
 		/* On rare occasion, mdb_map_find_next will return a wrong page */
 		/* Found in a big file, over 4,000,000 records */
 		fprintf(stderr,
-			"warning: page %d from map doesn't match: Type=%d, buf[4..7]=%d Expected table_pg=%d\n",
-			next_pg, mdb_get_int32(mdb->pg_buf, 4), entry->table_pg);
+			"warning: page %d from map doesn't match: Type=%d, buf[4..7]=%ld Expected table_pg=%ld\n",
+			next_pg, mdb->pg_buf[0], mdb_get_int32(mdb->pg_buf, 4), entry->table_pg);
 	}
 	fprintf(stderr, "Warning: defaulting to brute force read\n");
 #endif 
@@ -475,21 +476,28 @@
 	size_t len;
 
 	ole_len = mdb_get_int32(ole_ptr, 0);
+	mdb_debug(MDB_DEBUG_OLE,"ole len = %d ole flags = %02x",
+		ole_len & 0x00ffffff, ole_len >> 24);
 
 	if ((ole_len & 0x80000000)
 	 || (ole_len & 0x40000000)) {
 		/* inline or single-page fields don't have a next */
 		return 0;
 	} else {
+		mdb_debug(MDB_DEBUG_OLE, "pg_row %d", col->cur_blob_pg_row);
+		if (!col->cur_blob_pg_row)
+			return 0; /* we are done */
 		if (mdb_find_pg_row(mdb, col->cur_blob_pg_row,
 			&buf, &row_start, &len)) {
 			return 0;
 		}
+		mdb_debug(MDB_DEBUG_OLE,"start %d len %d", row_start, len);
+
 		if (col->bind_ptr)
 			memcpy(col->bind_ptr, buf + row_start + 4, len - 4);
 		col->cur_blob_pg_row = mdb_get_int32(buf, row_start);
 
-		return len;
+		return len - 4;
 	}
 	return 0;
 }
@@ -540,21 +548,59 @@
 		return len;
 	} else if ((ole_len & 0xff000000) == 0) {
 		col->cur_blob_pg_row = mdb_get_int32(ole_ptr, 4);
+		mdb_debug(MDB_DEBUG_OLE,"ole row = %d ole pg = %ld",
+			col->cur_blob_pg_row & 0xff,
+			col->cur_blob_pg_row >> 8);
 
 		if (mdb_find_pg_row(mdb, col->cur_blob_pg_row,
 			&buf, &row_start, &len)) {
 			return 0;
 		}
+		mdb_debug(MDB_DEBUG_OLE,"start %d len %d", row_start, len);
+
 		if (col->bind_ptr) 
 			memcpy(col->bind_ptr, buf + row_start + 4, len - 4);
 		col->cur_blob_pg_row = mdb_get_int32(buf, row_start);
+		mdb_debug(MDB_DEBUG_OLE, "next pg_row %d", col->cur_blob_pg_row);
 
-		return len;
+		return len - 4;
 	} else {
 		fprintf(stderr,"Unhandled ole field flags = %02x\n", ole_len >> 24);
 		return 0;
 	}
 }
+/*
+ * mdb_ole_read_full calls mdb_ole_read then loop over mdb_ole_read_next as much as necessary.
+ * returns the result in a big buffer.
+ * The call must free it.
+ * Note that this function is not indempotent: It may be called only once per column after each bind.
+ */
+void*
+mdb_ole_read_full(MdbHandle *mdb, MdbColumn *col, size_t *size)
+{
+	char ole_ptr[MDB_MEMO_OVERHEAD];
+	char *result = malloc(MDB_BIND_SIZE);
+	size_t result_buffer_size = MDB_BIND_SIZE;
+	size_t len, pos;
+
+	memcpy(ole_ptr, col->bind_ptr, MDB_MEMO_OVERHEAD);
+
+	len = mdb_ole_read(mdb, col, ole_ptr, MDB_BIND_SIZE);
+	memcpy(result, col->bind_ptr, len);
+	pos = len;
+	while ((len = mdb_ole_read_next(mdb, col, ole_ptr))) {
+		if (pos+len >= result_buffer_size) {
+			result_buffer_size += MDB_BIND_SIZE;
+			result = realloc(result, result_buffer_size);
+		}
+		memcpy(result + pos, col->bind_ptr, len);
+		pos += len;
+	}
+	if (size)
+		*size = pos;
+	return result;
+}
+
 #ifdef MDB_COPY_OLE
 static size_t mdb_copy_ole(MdbHandle *mdb, void *dest, int start, int size)
 {
@@ -863,7 +909,6 @@
 			td = mdb_get_double(buf, start);
 			text = g_strdup_printf("%.16e", td);
 		break;
-		case MDB_BINARY:
 		case MDB_TEXT:
 			if (size<0) {
 				text = g_strdup("");
@@ -877,6 +922,7 @@
 			text = mdb_date_to_string(mdb, start);
 		break;
 		case MDB_MEMO:
+		case MDB_BINARY:
 			text = mdb_memo_to_string(mdb, start, size);
 		break;
 		case MDB_MONEY:
Index: mdbtools-0.6pre1/src/util/mdb-prop.c
===================================================================
--- mdbtools-0.6pre1.orig/src/util/mdb-prop.c
+++ mdbtools-0.6pre1/src/util/mdb-prop.c
@@ -80,21 +80,11 @@
 	}
 
 	if (found) {
-		MdbColumn *col;
-		gchar kkd_ptr[MDB_MEMO_OVERHEAD];
-		void *kkd_pg = g_malloc(200000);
-		size_t len, pos;
-		memcpy(kkd_ptr, buf, MDB_MEMO_OVERHEAD);
-		col = g_ptr_array_index(table->columns, col_num - 1);
-		len = mdb_ole_read(mdb, col, kkd_ptr, MDB_BIND_SIZE);
-		memcpy(kkd_pg, buf, len);
-		pos = len;
-		while ((len = mdb_ole_read_next(mdb, col, kkd_ptr))) {
-			memcpy(kkd_pg + pos, buf, len);
-			pos += len;
-		}
-		dump_kkd(mdb, kkd_pg, pos);
-		g_free(kkd_pg);
+		MdbColumn *col = g_ptr_array_index(table->columns, col_num - 1);
+		size_t size;
+		void *kkd = mdb_ole_read_full(mdb, col, &size);
+		dump_kkd(mdb, kkd, size);
+		free(kkd);
 	}
 
 	g_free(buf);
@@ -121,6 +111,7 @@
 #endif
 	if (strcmp("KKD", kkd)) {
 		fprintf(stderr, "Unrecognized format.\n");
+		buffer_dump(kkd, 0, len);
 		return;
 	}
 	
Index: mdbtools-0.6pre1/include/mdbtools.h
===================================================================
--- mdbtools-0.6pre1.orig/include/mdbtools.h
+++ mdbtools-0.6pre1/include/mdbtools.h
@@ -448,6 +448,7 @@
 extern int mdb_col_disp_size(MdbColumn *col);
 extern size_t mdb_ole_read_next(MdbHandle *mdb, MdbColumn *col, void *ole_ptr);
 extern size_t mdb_ole_read(MdbHandle *mdb, MdbColumn *col, void *ole_ptr, int chunk_size);
+extern void* mdb_ole_read_full(MdbHandle *mdb, MdbColumn *col, size_t *size);
 extern void mdb_set_date_fmt(const char *);
 extern int mdb_read_row(MdbTableDef *table, unsigned int row);

Index: mdbtools-0.6pre1/src/libmdb/catalog.c
===================================================================
--- mdbtools-0.6pre1.orig/src/libmdb/catalog.c
+++ mdbtools-0.6pre1/src/libmdb/catalog.c
@@ -66,7 +66,11 @@
 	char obj_name[256];
 	char obj_type[256];
 	char obj_flags[256];
+	char obj_props[MDB_BIND_SIZE];
 	int type;
+	unsigned int i;
+	MdbColumn *col_props;
+	int kkd_size_ole;
 
 	if (!mdb) return NULL;
 	if (mdb->catalog) mdb_free_catalog(mdb);
@@ -91,14 +95,16 @@
 	mdb_bind_column_by_name(table, "Name", obj_name, NULL);
 	mdb_bind_column_by_name(table, "Type", obj_type, NULL);
 	mdb_bind_column_by_name(table, "Flags", obj_flags, NULL);
+	i = mdb_bind_column_by_name(table, "LvProp", obj_props, &kkd_size_ole);
+	col_props = g_ptr_array_index(table->columns, i-1);
 
 	mdb_rewind_table(table);
 
 	while (mdb_fetch_row(table)) {
 		type = atoi(obj_type);
 		if (objtype==MDB_ANY || type == objtype) {
-			// fprintf(stdout, "obj_id: %10ld objtype: %-3d obj_name: %s\n", 
-			// (atol(obj_id) & 0x00FFFFFF), type, obj_name); 
+			//fprintf(stderr, "obj_id: %10ld objtype: %-3d (0x%04x) obj_name: %s\n",
+			//	(atol(obj_id) & 0x00FFFFFF), type, type, obj_name);
 			entry = (MdbCatalogEntry *) g_malloc0(sizeof(MdbCatalogEntry));
 			entry->mdb = mdb;
 			strcpy(entry->object_name, obj_name);
@@ -106,7 +112,14 @@
 			entry->table_pg = atol(obj_id) & 0x00FFFFFF;
 			entry->flags = atol(obj_flags);
 			mdb->num_catalog++;
-			g_ptr_array_add(mdb->catalog, entry); 
+			g_ptr_array_add(mdb->catalog, entry);
+			if (kkd_size_ole) {
+				size_t kkd_len;
+				void *kkd = mdb_ole_read_full(mdb, col_props, &kkd_len);
+				//buffer_dump(kkd, 0, kkd_len);
+				entry->props = kkd_to_props(mdb, kkd, kkd_len);
+				free(kkd);
+			}
 		}
 	}
 	//mdb_dump_catalog(mdb, MDB_TABLE);
Index: mdbtools-0.6pre1/src/libmdb/kkd.c
===================================================================
--- mdbtools-0.6pre1.orig/src/libmdb/kkd.c
+++ mdbtools-0.6pre1/src/libmdb/kkd.c
@@ -28,7 +28,7 @@
 ** Note: This code is mostly garbage right now...just a test to parse out the
 **       KKD structures.
 */
-
+#if 0
 static GArray *mdb_get_column_props(MdbCatalogEntry *entry, int start)
 {
 int pos, cnt=0;
@@ -109,7 +109,7 @@
 int rowid = entry->kkd_rowid;
 
 
-	fprintf(stdout, "kkd_pg=%d kkd_rowid=%d\n", entry->kkd_pg, rowid);
+	fprintf(stdout, "kkd_pg=%ld kkd_rowid=%d\n", entry->kkd_pg, rowid);
 
 	mdb_read_pg(mdb, entry->kkd_pg);
 	rows = mdb_get_int16(mdb->pg_buf, 8);
@@ -148,4 +148,4 @@
 		mdb_get_column_def(entry, datapos);
 	}
 }
-
+#endif
Index: mdbtools-0.6pre1/src/util/mdb-prop.c
===================================================================
--- mdbtools-0.6pre1.orig/src/util/mdb-prop.c
+++ mdbtools-0.6pre1/src/util/mdb-prop.c
@@ -80,7 +80,7 @@
 	}
 
 	if (found) {
-		MdbColumn *col = g_ptr_array_index(table->columns, col_num - 1);
+		MdbColumn *col = g_ptr_array_index(table->columns, col_num-1);
 		size_t size;
 		void *kkd = mdb_ole_read_full(mdb, col, &size);
 		dump_kkd(mdb, kkd, size);
Index: mdbtools-0.6pre1/include/mdbtools.h
===================================================================
--- mdbtools-0.6pre1.orig/include/mdbtools.h
+++ mdbtools-0.6pre1/include/mdbtools.h
@@ -240,8 +240,8 @@
 	unsigned long  table_pg; /* misnomer since object may not be a table */
 	unsigned long  kkd_pg;
 	unsigned int   kkd_rowid;
-	int			num_props;
-	GArray		*props;
+	//int			num_props; please use props->len
+	GArray		*props; /* GArray of MdbProperties */
 	GArray		*columns;
 	int		flags;
 } MdbCatalogEntry;
@@ -519,6 +519,9 @@
 extern GPtrArray *mdb_read_props_list(gchar *kkd, int len);
 extern void mdb_free_props(MdbProperties *props);
 extern MdbProperties *mdb_read_props(MdbHandle *mdb, GPtrArray *names, gchar *kkd, int len);
+extern void mdb_dump_props(MdbProperties *props, FILE *outfile, int show_name);
+extern GArray* kkd_to_props(MdbHandle *mdb, void *kkd, size_t len);
+
 
 /* worktable.c */
 extern MdbTableDef *mdb_create_temp_table(MdbHandle *mdb, char *name);
Index: mdbtools-0.6pre1/src/libmdb/props.c
===================================================================
--- mdbtools-0.6pre1.orig/src/libmdb/props.c
+++ mdbtools-0.6pre1/src/libmdb/props.c
@@ -27,7 +27,6 @@
 	int pos = 0;
 	gchar *name;
 	GPtrArray *names = NULL;
-	int i = 0;
 
 	names = g_ptr_array_new();
 #if MDB_DEBUG
@@ -77,7 +76,6 @@
 	int elem, dtype, dsize;
 	gchar *name, *value;
 	MdbProperties *props;
-	int i = 0;
 
 #if MDB_DEBUG
 	buffer_dump(kkd, 0, len);
@@ -127,3 +125,66 @@
 	return props;
 	
 }
+
+static void
+print_keyvalue(gpointer key, gpointer value, gpointer outfile)
+{
+		fprintf((FILE*)outfile,"\t%s: %s\n", (gchar *)key, (gchar *)value);
+}
+void
+mdb_dump_props(MdbProperties *props, FILE *outfile, int show_name) {
+	if (show_name)
+		fprintf(outfile,"name: %s\n", props->name ? props->name : "(none)");
+	g_hash_table_foreach(props->hash, print_keyvalue, outfile);
+	if (show_name)
+		fputc('\n', outfile);
+}
+
+GArray*
+kkd_to_props(MdbHandle *mdb, void *kkd, size_t len) {
+	guint32 record_len;
+	guint16 record_type;
+	size_t pos;
+	GPtrArray *names = NULL;
+	MdbProperties *props;
+
+#if MDB_DEBUG
+	buffer_dump(kkd, 0, len);
+#endif
+	if (strcmp("KKD", kkd)) {
+		fprintf(stderr, "Unrecognized format.\n");
+		buffer_dump(kkd, 0, len);
+		return NULL;
+	}
+
+	GArray *result = g_array_new(0, 0, sizeof(MdbProperties*));
+
+	pos = 4;
+	while (pos < len) {
+		record_len = mdb_get_int32(kkd, pos);
+		record_type = mdb_get_int16(kkd, pos + 4);
+		//printf("len = %d type = %d\n", record_len, record_type);
+		//buffer_dump(kkd, pos+4, record_len);
+		switch (record_type) {
+			case 0x80:
+				names = mdb_read_props_list(kkd+pos+6, record_len - 6);
+				break;
+			case 0x00:
+			case 0x01:
+				if (!names) {
+					fprintf(stderr,"sequence error!\n");
+					break;
+				}
+				props = mdb_read_props(mdb, names, kkd+pos+6, record_len - 6);
+				g_array_append_val(result, props);
+				//mdb_dump_props(props, stderr, 1);
+				break;
+			default:
+				fprintf(stderr,"Unknown record type %d\n", record_type);
+				break;
+		}
+		pos += record_len;
+	}
+	// TODO free names?
+	return result;
+}
Index: mdbtools-0.6pre1/src/libmdb/table.c
===================================================================
--- mdbtools-0.6pre1.orig/src/libmdb/table.c
+++ mdbtools-0.6pre1/src/libmdb/table.c
@@ -78,6 +78,7 @@
 	MdbFormatConstants *fmt = mdb->fmt;
 	int len, row_start, pg_row;
 	void *buf, *pg_buf = mdb->pg_buf;
+	guint i;
 
 	mdb_read_pg(mdb, entry->table_pg);
 	if (mdb_get_byte(pg_buf, 0) != 0x02)  /* not a valid table def page */
@@ -110,6 +111,13 @@
 
 	table->first_data_pg = mdb_get_int16(pg_buf, fmt->tab_first_dpg_offset);
 
+	if (entry->props)
+		for (i=0; i<entry->props->len; ++i) {
+			MdbProperties *props = g_array_index(entry->props, MdbProperties*, i);
+			if (!props->name)
+				table->props = props;
+		}
+
 	return table;
 }
 MdbTableDef *mdb_read_table_by_name(MdbHandle *mdb, gchar *table_name, int obj_type)
@@ -205,7 +213,7 @@
 	MdbFormatConstants *fmt = mdb->fmt;
 	MdbColumn *pcol;
 	unsigned char *col;
-	unsigned int i;
+	unsigned int i, j;
 	int cur_pos;
 	size_t name_sz;
 	
@@ -291,11 +299,25 @@
 		mdb_unicode2ascii(mdb, tmp_buf, name_sz, pcol->name, MDB_MAX_OBJ_NAME);
 		g_free(tmp_buf);
 
+
 	}
 
 	/* Sort the columns by col_num */
 	g_ptr_array_sort(table->columns, (GCompareFunc)mdb_col_comparer);
 
+	GArray *allprops = table->entry->props;
+	if (allprops)
+		for (i=0;i<table->num_cols;i++) {
+			pcol = g_ptr_array_index(table->columns, i);
+			for (j=0; j<allprops->len; ++j) {
+				MdbProperties *props = g_array_index(allprops, MdbProperties*, j);
+				if (props->name && pcol->name && !strcmp(props->name, pcol->name)) {
+					pcol->props = props;
+					break;
+				}
+
+			}
+		}
 	table->index_start = cur_pos;
 	return table->columns;
 }
@@ -316,6 +338,8 @@
 	fprintf(stdout,"number of columns   = %d\n",table->num_cols);
 	fprintf(stdout,"number of indices   = %d\n",table->num_real_idxs);
 
+	if (table->props)
+		mdb_dump_props(table->props, stdout, 0);
 	mdb_read_columns(table);
 	mdb_read_indices(table);
 
@@ -326,6 +350,8 @@
 			i, col->name,
 			mdb_get_coltype_string(mdb->default_backend, col->col_type),
 			col->col_size);
+		if (col->props)
+			mdb_dump_props(col->props, stdout, 0);
 	}
 
 	for (i=0;i<table->num_idxs;i++) {

Index: mdbtools-0.6pre1/src/libmdb/backend.c
===================================================================
--- mdbtools-0.6pre1.orig/src/libmdb/backend.c
+++ mdbtools-0.6pre1/src/libmdb/backend.c
@@ -189,6 +189,22 @@
 	return result;
 }
 
+static char* quote_name_with_squotes(const char* name)
+{
+	char *result = malloc(2*strlen(name)+3);
+	char *p = result;
+	*p++ = '\'';
+	while (*name) {
+		*p++ = *name;
+		if (*name == '\'')
+			*p++ = *name; /* double it */
+		name ++;
+	}
+	*p++ = '\'';
+	*p++ = 0;
+	return result;
+}
+
 static char* quote_name_with_rquotes(const char* name)
 {
 	return (char*)g_strconcat("`", name, "`", NULL);
@@ -596,8 +612,9 @@
 		quoted_table_name = table_name;
 	}
 
-	/* drop the table if it exists */
-	fprintf (output, "DROP TABLE %s;\n", quoted_table_name);
+	if (use_drop)
+		/* drop the table if it exists */
+		fprintf (output, "DROP TABLE %s;\n", quoted_table_name);
 
 	/* create the table */
 	fprintf (output, "CREATE TABLE %s\n", quoted_table_name);
@@ -609,7 +626,6 @@
 	mdb_read_columns (table);
 
 	/* loop over the columns, dumping the names and types */
-
 	for (i = 0; i < table->num_cols; i++) {
 		col = g_ptr_array_index (table->columns, i);
 
@@ -631,6 +647,12 @@
 	    			fprintf(output, " (%d)", col->col_size);
 		}
 
+		if (col->props) {
+			gchar *not_null = g_hash_table_lookup(col->props->hash, "Required");
+			if (not_null && not_null[0]=='y') {
+				fputs(" NOT NULL", output);
+			}
+		}
 		if (i < table->num_cols - 1)
 			fputs(", \n", output);
 		else
@@ -639,6 +661,43 @@
 
 	fputs(");\n", output);
 
+
+	/* Add the constraints */
+	for (i = 0; i < table->num_cols; i++) {
+		MdbProperties *props;
+		gchar *prop_value;
+
+		col = g_ptr_array_index (table->columns, i);
+		props = col->props;
+		if (!props)
+			continue;
+
+		if (sanitize)
+			quoted_name = sanitize_name(col->name);
+		else
+			quoted_name = mdb->default_backend->quote_name(col->name);
+
+		prop_value = g_hash_table_lookup(col->props->hash, "AllowZeroLength");
+		if (prop_value && prop_value[0]=='n') {
+			fprintf(output,
+				"ALTER TABLE %s ADD CHECK (%s <>'');\n",
+				quoted_table_name, quoted_name);
+		}
+
+		prop_value = g_hash_table_lookup(col->props->hash, "Description");
+		if (prop_value) {
+			char *comment = quote_name_with_squotes(prop_value);
+			fprintf(output,
+				"COMMENT ON COLUMN %s.%s IS '%s';\n",
+				quoted_table_name, quoted_name,
+				prop_value);
+			free(comment);
+		}
+
+		free(quoted_name);
+	}
+
+
 	fputs("-- CREATE SEQUENCES ...\n", output);
 	fputs("\n", output);

-------------------------------------------------------------
-- MDB Tools - A library for reading MS Access database files
-- Copyright (C) 2000-2011 Brian Bruns and others.
-- Files in libmdb are licensed under LGPL and the utilities under
-- the GPL, see COPYING.LIB and COPYING files respectively.
-- Check out http://mdbtools.sourceforge.net
-------------------------------------------------------------

DROP TABLE "tConstituent";
CREATE TABLE "tConstituent"
 (
	"iConstituentID"			INTEGER, 
	"sPreviousID"			VARCHAR (20), 
	"sLastName"			VARCHAR (50) NOT NULL, 
	"sFirstName"			VARCHAR (50), 
	"sMiddleName"			VARCHAR (50), 
	"sAddress1"			VARCHAR (50) NOT NULL, 
	"sAddress2"			VARCHAR (50), 
	"sPostcode"			VARCHAR (20), 
	"sCity"			VARCHAR (50), 
	"iCountyID"			INTEGER, 
	"iCountryID"			INTEGER NOT NULL, 
	"iSourceID"			INTEGER NOT NULL, 
	"dAdded"			TIMESTAMP WITHOUT TIME ZONE NOT NULL, 
	"sAddedBy"			VARCHAR (30) NOT NULL, 
	"dModified"			TIMESTAMP WITHOUT TIME ZONE NOT NULL, 
	"sModifiedBy"			VARCHAR (30) NOT NULL
);
COMMENT ON COLUMN "tConstituent"."iConstituentID" IS 'Primary Key';
ALTER TABLE "tConstituent" ADD CHECK ("sPreviousID" <>'');
COMMENT ON COLUMN "tConstituent"."sPreviousID" IS 'Previous ID (must be unique!)';
ALTER TABLE "tConstituent" ADD CHECK ("sLastName" <>'');
COMMENT ON COLUMN "tConstituent"."sLastName" IS 'Last Name or Organisation Name';
ALTER TABLE "tConstituent" ADD CHECK ("sFirstName" <>'');
COMMENT ON COLUMN "tConstituent"."sFirstName" IS 'First Name';
ALTER TABLE "tConstituent" ADD CHECK ("sMiddleName" <>'');
COMMENT ON COLUMN "tConstituent"."sMiddleName" IS 'Middle Name';
ALTER TABLE "tConstituent" ADD CHECK ("sAddress1" <>'');
COMMENT ON COLUMN "tConstituent"."sAddress1" IS 'Address Line 1';
ALTER TABLE "tConstituent" ADD CHECK ("sAddress2" <>'');
COMMENT ON COLUMN "tConstituent"."sAddress2" IS 'Address Line 2';
ALTER TABLE "tConstituent" ADD CHECK ("sPostcode" <>'');
COMMENT ON COLUMN "tConstituent"."sPostcode" IS 'Postcode';
ALTER TABLE "tConstituent" ADD CHECK ("sCity" <>'');
COMMENT ON COLUMN "tConstituent"."sCity" IS 'City';
COMMENT ON COLUMN "tConstituent"."iCountyID" IS 'Foreign key to tCounty';
COMMENT ON COLUMN "tConstituent"."iCountryID" IS 'Foreign key to tCountry';
COMMENT ON COLUMN "tConstituent"."iSourceID" IS 'Foreign key to tSource';
COMMENT ON COLUMN "tConstituent"."dAdded" IS 'Date of creation (ie. added to the database)';
ALTER TABLE "tConstituent" ADD CHECK ("sAddedBy" <>'');
COMMENT ON COLUMN "tConstituent"."sAddedBy" IS 'User that added this record';
COMMENT ON COLUMN "tConstituent"."dModified" IS 'DateLast modified';
ALTER TABLE "tConstituent" ADD CHECK ("sModifiedBy" <>'');
COMMENT ON COLUMN "tConstituent"."sModifiedBy" IS 'User that last modified this record';
-- CREATE SEQUENCES ...

CREATE SEQUENCE "tConstituent_iConstituentID_seq" OWNED BY "tConstituent"."iConstituentID";
ALTER TABLE "tConstituent" ALTER COLUMN "iConstituentID" SET DEFAULT pg_catalog.nextval('"tConstituent_iConstituentID_seq"');

-- CREATE ANY INDEXES ...
CREATE INDEX "tConstituentdModified_idx" ON "tConstituent" ("dModified");
ALTER TABLE "tConstituent" ADD CONSTRAINT "tConstituentPrimaryKey_pk" PRIMARY KEY ("iConstituentID");
CREATE INDEX "tConstituentsAddress1_idx" ON "tConstituent" ("sAddress1");
CREATE INDEX "tConstituentsFirstname_idx" ON "tConstituent" ("sFirstName");
CREATE INDEX "tConstituentsLastName_idx" ON "tConstituent" ("sLastName");
CREATE INDEX "tConstituentsMiddlename_idx" ON "tConstituent" ("sMiddleName");
CREATE INDEX "tConstituentsPostcode_idx" ON "tConstituent" ("sPostcode");
CREATE INDEX "tConstituentsPostcodeFirstname_idx" ON "tConstituent" ("sPostcode", "sFirstName");
CREATE INDEX "tConstituentsPostcodeLastname_idx" ON "tConstituent" ("sPostcode", "sLastName");
CREATE UNIQUE INDEX "tConstituentsPreviousID_idx" ON "tConstituent" ("sPreviousID");



-- CREATE Relationships ...
ALTER TABLE "tConstituent" ADD CONSTRAINT "tConstituent_iCountryID_fk" FOREIGN KEY ("iCountryID") REFERENCES "tCountry"("iCountryID") ON UPDATE CASCADE;
ALTER TABLE "tConstituent" ADD CONSTRAINT "tConstituent_iCountyID_fk" FOREIGN KEY ("iCountyID") REFERENCES "tCounty"("iCountyID") ON UPDATE CASCADE;
ALTER TABLE "tConstituent" ADD CONSTRAINT "tConstituent_iSourceID_fk" FOREIGN KEY ("iSourceID") REFERENCES "tSource"("iSourceID") ON UPDATE CASCADE;