[mdb-dev] Re: Sanitizing table/column names

[Nirgal V. <con...@ni...>]

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.
>   
> > If using Jet3, don't forget to set MDB_JET3_CHARSET as described in mdb-schema
> > manual and HACKING file.
> 
> 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.