Re: Sanitizing table/column names

Hi, Nirgal!

Some others difficults has in the process to import scheme in MySQL DB.
This errors produce situation to ignore first command of mysql like as "DROP
DATABASE IF EXIST" and as result INSERT dates into old table.
The 2 lines of comment in header of scheme formed in src/libmdb/backend.c
has "-------------------------------------------------------------------------------", wich not ignored
by mysql. I propose replace there one symbol to space, as "-- ---------------------------"
This lines will not reproduce the warnings in import scheme to MySQL
Attach the small patch do this: mdbtools-mysql-warning.patch

My conf: Mandriva 2010 x86_64, mysql-5.1.42-0.5mdv2010.0 mdbtools-0.6pre1-git-2011-02-21 with
format-security and odbctypes patches.

Nirgal Vourgère <[email protected]> wrote:
> On Monday 07 March 2011 16:57:09 Dmitry Nikitin wrote:
> > > > 2) need to fix the procedure of sanitize (-S) the dump of MDB files. You
> > > already changes dates to utf-8 codepage, and the more "dummy"
> > > > MS windows users and "admins" used the non ASCII symbols for names of tables
> > > and columns in the MDB-files, like as cyrilic :(
> > > > May be the procedure of sanitize can replace the symbols in names as
> > > transliterate (need check the match of names and length?)
> > > > Now, the replaced only spaces in names as "_", and .... the other
> > > non-english symbols taking "____________________________" as name of table.
> > 
> > > Sanitize is really buggy.
> > > I would recommand removing it from the code.
> > > All backends should now handle the non ascii characters in table/colum names
> > > correctly, quoting them.
> > > Do you really need -S?
> > 
> > I think- Yes, "-S" its good idea for package!!! (non-ASCII symbols in variables, names of tables and other is bad practise in developing.
> > I tested JET4 file with ciryllic symbols, spaces, " ' " and others :((( in names of tables, fields.
> 
> As you pointed out, sanitizing is really tricky.
> I tested a BIG5 encoded jet3 database, and just like you, ended up with conflicts because I had a lot of columns with the same name "__"!
> What do you suggest we should do? Rename every thing into table1, table2, table3...?? Remember we now export relationships. I 
> suspect we might export relationships on linked tables in the future. Renaming these will really be messy.
> I do agree having ideograms as column names, or even spaces is not very fun.
> But in my experience, export does work with these -- I tested postgres backend only I'm afraid.
> *I think fixing database schemas is beyond the scope of mdbtools.*
> mdbtools will convert your poorly design access schema into a poorly design postgres schema for example. It will keep spaces, cyrilic, ideogram or whatever. It will produce a working unicode database. You just need to properly quote your 
> names in your queries, that's all. I've read mysql uses ` character.
> IMHO, you should use another tool if you want to rename your columns.

Yes, I use sed in shell script to automatic rename the badlly names in scheme and dump. But it's need more time for testing and make
good scheme to rename.
I propouse (as temporary) leave in the code sanitize for replace the scaces by underscore symbol and should comment the code, wich replace others (non literal-numerical) symbols. So, in future will continued develop the procedure to replace non-ASCII symbols in the transliterated. May be, create two arrays of letters ASCII and matched for character transliteration, such as in Ukrainian:
"Ніргал Вургере" => "nirgal_vourgere"

Good Luck, Dmitry

------------------------------------------------------------------------------
Colocation vs. Managed Hosting
A question and answer guide to determining the best fit
for your organization - today and in the future.
http://p.sf.net/sfu/internap-sfd2d

_______________________________________________
mdbtools-dev mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mdbtools-dev

--- mdbtools-0.6pre1.orig/src/libmdb/backend.cg	2011-03-10 17:20:09.000000000 +0200
+++ mdbtools-0.6pre1/src/libmdb/backend.c	2011-03-10 17:21:28.000000000 +0200
@@ -835,13 +835,13 @@
 
 	/* Print out a little message to show that this came from mdb-tools.
 	   I like to know how something is generated. DW */
-	fputs("-------------------------------------------------------------\n"
+	fputs("-- ----------------------------------------------------------\n"
 		"-- MDB Tools - A library for reading MS Access database files\n"
 		"-- Copyright (C) 2000-2011 Brian Bruns and others.\n"
 		"-- Files in libmdb are licensed under LGPL and the utilities under\n"
 		"-- the GPL, see COPYING.LIB and COPYING files respectively.\n"
 		"-- Check out http://mdbtools.sourceforge.net\n"
-		"-------------------------------------------------------------\n\n",
+		"-- ----------------------------------------------------------\n\n",
 		outfile);
 
 	const char *charset = mdb_target_charset(mdb);