[patch] indexes
Nirgal Vourgère <[email protected]> Sat, 5 Feb 2011 18:16:37 +0000
| Newsgroups | gmane.comp.db.mdb-tools.devel |
|---|---|
| Message-ID | <[email protected]> |
Hello MDB folks.
I'm trying to have mdb-schema export the indexes.
I hit a couple of problems in mdb_read_indices:
1/ When parsing table definition page, we expect index->index_num to be growing from 0 to table->num_real_idxs.
This is not allways true.
I think you can reproduce the problem by deleting some indexes.
This resulted in wrong columns beeing associated with indexes.
Include is a patch that will find the index with matching index_num.
2/ When associating the columns with the index, we were assuming the column position in table->columns is the same than col->col_num
This is not allways true.
I think you can reproduce this by deleting some columns.
This resulted in segment violation.
The patch include a loop to find the column with the correct col_num.
Also, I moved the test about pidx being null and num_real_idxs beeing patched, up in the logic. I still don't really like it, but it's a bit better IMHO.
Finally, I updated the HACKING file, and I left some of my debug messages commented out.
I suggest you merge the indexes patch, for the github repository.
Included is also another patch for mdb-schema to output the index creation section. This is work in progress, and I suggest you don't merge it yet, because it wrongfully assumes you are using postgres backend:
It will generate this kind of lines:
-- CREATE ANY INDEXES ...
CREATE INDEX "tTelecomsDescription" ON "tTelecom" ("sDescription");
CREATE UNIQUE INDEX "tAuthorisationActionsDescription" ON "tAuthorisationAction" ("sDescription");
ALTER TABLE "tGeneralDefinition" ADD CONSTRAINT "tGeneralDefinitionprimarykey" PRIMARY KEY ("iDefinitionGroupID", "iDatabaseID");
Could any one point to me the different index syntaxes for our supported backends?
------------------------------------------------------------------------------
The modern datacenter depends on network connectivity to access resources
and provide services. The best practices for maximizing a physical server's
connectivity to a physical network are well understood - see how these
rules translate into the virtual world?
http://p.sf.net/sfu/oracle-sfdevnlfb
_______________________________________________
mdbtools-dev mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mdbtools-dev
indexes.patch
(text/x-patch, 9 KB)
Index: mdbtools-0.6pre1/src/libmdb/index.c
===================================================================
--- mdbtools-0.6pre1.orig/src/libmdb/index.c
+++ mdbtools-0.6pre1/src/libmdb/index.c
@@ -69,15 +69,15 @@
MdbHandle *mdb = entry->mdb;
MdbFormatConstants *fmt = mdb->fmt;
MdbIndex *pidx;
- unsigned int i, j;
- int idx_num, key_num, col_num;
+ unsigned int i, j, k;
+ int key_num, col_num, cleaned_col_num;
int cur_pos, name_sz, idx2_sz, type_offset;
int index_start_pg = mdb->cur_pg;
gchar *tmpbuf;
- table->indices = g_ptr_array_new();
+ table->indices = g_ptr_array_new();
- if (IS_JET4(mdb)) {
+ if (IS_JET4(mdb)) {
cur_pos = table->index_start + 52 * table->num_real_idxs;
idx2_sz = 28;
type_offset = 23;
@@ -87,15 +87,33 @@
type_offset = 19;
}
+ //fprintf(stderr, "num_idxs:%d num_real_idxs:%d\n", table->num_idxs, table->num_real_idxs);
+ /* num_real_idxs should be the number of indexes of type 2.
+ * It's not always the case. Happens on Northwind Orders table.
+ */
+ table->num_real_idxs = 0;
tmpbuf = (gchar *) g_malloc(idx2_sz);
for (i=0;i<table->num_idxs;i++) {
read_pg_if_n(mdb, tmpbuf, &cur_pos, idx2_sz);
pidx = (MdbIndex *) g_malloc0(sizeof(MdbIndex));
pidx->table = table;
pidx->index_num = mdb_get_int16(tmpbuf, 4);
- pidx->index_type = tmpbuf[type_offset];
+ pidx->index_type = tmpbuf[type_offset];
g_ptr_array_add(table->indices, pidx);
+ /*
+ {
+ gint32 dumy0 = mdb_get_int32(tmpbuf, 0);
+ gint8 dumy1 = tmpbuf[8];
+ gint32 dumy2 = mdb_get_int32(tmpbuf, 9);
+ gint32 dumy3 = mdb_get_int32(tmpbuf, 13);
+ gint16 dumy4 = mdb_get_int16(tmpbuf, 17);
+ fprintf(stderr, "idx #%d: num2:%d type:%d\n", i, pidx->index_num, pidx->index_type);
+ fprintf(stderr, "idx #%d: %d %d %d %d %d\n", i, dumy0, dumy1, dumy2, dumy3, dumy4);
+ }*/
+ if (pidx->index_type!=2)
+ table->num_real_idxs++;
}
+ //fprintf(stderr, "num_idxs:%d num_real_idxs:%d\n", table->num_idxs, table->num_real_idxs);
g_free(tmpbuf);
for (i=0;i<table->num_idxs;i++) {
@@ -109,31 +127,35 @@
read_pg_if_n(mdb, tmpbuf, &cur_pos, name_sz);
mdb_unicode2ascii(mdb, tmpbuf, name_sz, pidx->name, MDB_MAX_OBJ_NAME);
g_free(tmpbuf);
- //fprintf(stderr, "index name %s\n", pidx->name);
+ //fprintf(stderr, "index %d type %d name %s\n", pidx->index_num, pidx->index_type, pidx->name);
}
mdb_read_alt_pg(mdb, entry->table_pg);
mdb_read_pg(mdb, index_start_pg);
cur_pos = table->index_start;
- idx_num=0;
for (i=0;i<table->num_real_idxs;i++) {
if (IS_JET4(mdb)) cur_pos += 4;
- do {
- pidx = g_ptr_array_index (table->indices, idx_num++);
- } while (pidx && pidx->index_type==2);
-
- /* if there are more real indexes than index entries left after
- removing type 2's decrement real indexes and continue. Happens
- on Northwind Orders table.
- */
- if (!pidx) {
- table->num_real_idxs--;
- continue;
+ /* look for index number i */
+ for (j=0; j<table->num_idxs; ++j) {
+ pidx = g_ptr_array_index (table->indices, j);
+ if (pidx->index_type!=2 && pidx->index_num==i)
+ break;
}
+ if (j==table->num_idxs)
+ fprintf(stderr, "ERROR: can't find index #%d.\n", i);
+ //fprintf(stderr, "index %d #%d (%s) index_type:%d\n", i, pidx->index_num, pidx->name, pidx->index_type);
pidx->num_rows = mdb_get_int32(mdb->alt_pg_buf,
fmt->tab_cols_start_offset +
- (i*fmt->tab_ridx_entry_size));
+ (pidx->index_num*fmt->tab_ridx_entry_size));
+ /*
+ fprintf(stderr, "ridx block1 i:%d data1:0x%08x data2:0x%08x\n",
+ i,
+ mdb_get_int32(mdb->pg_buf,
+ fmt->tab_cols_start_offset + pidx->index_num * fmt->tab_ridx_entry_size),
+ mdb_get_int32(mdb->pg_buf,
+ fmt->tab_cols_start_offset + pidx->index_num * fmt->tab_ridx_entry_size +4));
+ fprintf(stderr, "pidx->num_rows:%d\n", pidx->num_rows);*/
key_num=0;
for (j=0;j<MDB_MAX_IDX_COLS;j++) {
@@ -142,17 +164,36 @@
cur_pos++;
continue;
}
+ /* here we have the internal column number that does not
+ * always match the table columns because of deletions */
+ cleaned_col_num = -1;
+ for (k=0; k<=table->num_cols; k++) {
+ MdbColumn *col = g_ptr_array_index(table->columns,k);
+ if (col->col_num == col_num) {
+ cleaned_col_num = k;
+ break;
+ }
+ }
+ if (cleaned_col_num==-1) {
+ fprintf(stderr, "CRITICAL: can't find column with internal id %d in index %s\n",
+ col_num, pidx->name);
+ cur_pos++;
+ continue;
+ }
/* set column number to a 1 based column number and store */
- pidx->key_col_num[key_num] = col_num + 1;
+ pidx->key_col_num[key_num] = cleaned_col_num + 1;
pidx->key_col_order[key_num] =
(read_pg_if_8(mdb, &cur_pos)) ? MDB_ASC : MDB_DESC;
+ //fprintf(stderr, "component %d using column #%d (internally %d)\n", j, cleaned_col_num, col_num);
key_num++;
}
pidx->num_keys = key_num;
cur_pos += 4;
+ //fprintf(stderr, "pidx->unknown_pre_first_pg:0x%08x\n", read_pg_if_32(mdb, &cur_pos));
pidx->first_pg = read_pg_if_32(mdb, &cur_pos);
pidx->flags = read_pg_if_8(mdb, &cur_pos);
+ //fprintf(stderr, "pidx->first_pg:%d pidx->flags:0x%02x\n", pidx->first_pg, pidx->flags);
if (IS_JET4(mdb)) cur_pos += 9;
}
return NULL;
Index: mdbtools-0.6pre1/HACKING
===================================================================
--- mdbtools-0.6pre1.orig/HACKING
+++ mdbtools-0.6pre1/HACKING
@@ -316,7 +316,7 @@
| ???? | 1 byte | col_name_len| len of the name of the column |
| ???? | n bytes | col_name | Name of the column |
+-------------------------------------------------------------------------+
-| Iterate for the number of num_real_idx (30+9 = 39 bytes) |
+| Iterate for indexes with index_type 0 or 1 (30+9 = 39 bytes) |
+-------------------------------------------------------------------------+
| Iterate 10 times for 10 possible columns (10*3 = 30 bytes) |
+-------------------------------------------------------------------------+
@@ -327,7 +327,7 @@
| ???? | 4 bytes | first_dp | Data pointer of the index page |
| ???? | 1 byte | flags | See flags table for indexes |
+-------------------------------------------------------------------------+
-| Iterate for the number of num_real_idx (20 bytes) |
+| Iterate for the number of num_idx (20 bytes) |
+-------------------------------------------------------------------------+
| ???? | 4 bytes | index_num | Number of the index |
| | | |(warn: not always in the sequential order)|
@@ -338,7 +338,7 @@
| 0x04 | 2 bytes | ??? | |
| ???? | 1 byte | primary_key | 0x01 if this index is primary |
+-------------------------------------------------------------------------+
-| Iterate for the number of num_real_idx |
+| Iterate for the number of num_idx |
+-------------------------------------------------------------------------+
| ???? | 1 byte | idx_name_len| len of the name of the index |
| ???? | n bytes | idx_name | Name of the index |
@@ -394,7 +394,7 @@
| ???? | 2 bytes | col_name_len| len of the name of the column |
| ???? | n bytes | col_name | Name of the column (UCS-2 format) |
+-------------------------------------------------------------------------+
-| Iterate for the number of num_real_idx (30+22 = 52 bytes) |
+| Iterate for indexes with index_type 0 or 1 (30+22 = 52 bytes) |
+-------------------------------------------------------------------------+
| ???? | 4 bytes | ??? | |
+-------------------------------------------------------------------------+
@@ -408,7 +408,7 @@
| ???? | 1 byte | flags | See flags table for indexes |
| ???? | 9 bytes | unknown | |
+-------------------------------------------------------------------------+
-| Iterate for the number of num_real_idx (28 bytes) |
+| Iterate for the number of num_idx (28 bytes) |
+-------------------------------------------------------------------------+
| ???? | 4 bytes | unknown | matches first unknown definition block |
| ???? | 4 bytes | index_num | Number of the index |
@@ -421,7 +421,7 @@
| ???? | 1 byte | primary_key | 0x01 if this index is primary |
| ???? | 4 bytes | unknown | |
+-------------------------------------------------------------------------+
-| Iterate for the number of num_real_idx |
+| Iterate for the number of num_idx |
+-------------------------------------------------------------------------+
| ???? | 2 bytes | idx_name_len| len of the name of the index |
| ???? | n bytes | idx_name | Name of the index (UCS-2) |
schema-indexes.WIP.patch
(text/x-patch, 2.1 KB)
Index: mdbtools-0.6pre1/src/util/mdb-schema.c
===================================================================
--- mdbtools-0.6pre1.orig/src/util/mdb-schema.c
+++ mdbtools-0.6pre1/src/util/mdb-schema.c
@@ -125,12 +125,14 @@
{
MdbTableDef *table;
MdbHandle *mdb = entry->mdb;
- unsigned int i;
+ unsigned int i, j;
MdbColumn *col;
char* table_name;
+ char* index_name;
char* quoted_table_name;
char* quoted_name;
char* sql_sequences;
+ MdbIndex *idx;
if (sanitize)
quoted_table_name = sanitize_name(entry->object_name);
@@ -213,8 +215,58 @@
}
*/
+ /* read indexes */
+ mdb_read_indices(table);
+
fprintf (stdout, "-- CREATE ANY INDEXES ...\n");
fprintf (stdout, "\n");
+ for (i=0;i<table->num_idxs;i++) {
+ idx = g_ptr_array_index (table->indices, i);
+ if (idx->index_type==2)
+ continue;
+ /*
+ if (!strcmp(idx->name, argv[3])) {
+ walk_index(mdb, idx);
+ }*/
+
+ index_name = malloc(strlen(entry->object_name)+strlen(idx->name)+1);
+ strcpy(index_name, entry->object_name);
+ strcat(index_name, idx->name);
+ if (sanitize)
+ quoted_name = sanitize_name(index_name);
+ else
+ quoted_name = mdb->default_backend->quote_name(index_name);
+ if (idx->index_type==1) {
+ fprintf (stdout, "ALTER TABLE %s ADD CONSTRAINT %s PRIMARY KEY (", quoted_table_name, quoted_name);
+ } else {
+ fprintf (stdout, "CREATE");
+ if (idx->flags & MDB_IDX_UNIQUE)
+ fprintf (stdout, " UNIQUE");
+ fprintf (stdout, " INDEX %s ON %s (", quoted_name, quoted_table_name);
+ }
+ free(quoted_name);
+ free(index_name);
+
+ for (j=0;j<idx->num_keys;j++) {
+ if (j)
+ fprintf(stdout, ", ");
+ col=g_ptr_array_index(table->columns,idx->key_col_num[j]-1);
+ if (sanitize)
+ quoted_name = sanitize_name(col->name);
+ else
+ quoted_name = mdb->default_backend->quote_name(col->name);
+ fprintf (stdout, "%s", quoted_name);
+ if (idx->index_type!=1 && idx->key_col_order[j])
+ /* no DESC for primary keys */
+ fprintf(stdout, " DESC");
+
+ free(quoted_name);
+
+ }
+ fprintf (stdout, ");\n");
+ }
+ fprintf (stdout, "\n");
+ fprintf (stdout, "\n");
free(quoted_table_name);