* Cédric Krier: " Re: [tryton-dev] Database schema name limit" (Sat, 23 May 2015 12:18:32 +0200):
> On 23 May 11:01, Mathias Behrle wrote:
> > * Cédric Krier: " [tryton-dev] Database schema name limit" (Thu, 21 May 2015
> > 17:52:57 +0200):
> >
> > > Hi,
> > >
> > > I'm facing a limitation with how trytond generate the table name for a
> > > ModelSQL. Databases have different length limitation for schema name.
> > > For example,
> > > PostgreSQL has the limit to 64 when Oracle has the limit to 30
> > > (yes I'm working on an Oracle backend).
> > >
> > > I don't want that we change our naming convention because it is quite
> > > good and reducing the name will just bring a lot in readability.
> > > And we will be forced to use the least common constraint.
> > >
> > > So my idea is to have a configuration section which will provide the
> > > table name to use for a Model.
> > >
> > > Example:
> > >
> > > [table]
> > > account.invoice.payment_term.line.relativedelta = acc_inv_pt_l_reldelta
> > > account.payment.sepa.message = acc_payment_sepa_msg
> > >
> > > Of course such configuration could not be modified once a database has
> > > been created (or the table should be renamed).
> > >
> > > Side effect, it could also be used to fix naming conflict between 2
> > > unrelated module (at the database level not Model.__name__).
> > >
> > > What do you think?
> >
> > The backside of this translation table is, that you have to know beforehand
> > all tables in your database, before you install them and that it has to be
> > done manually.
>
> Yes but any way, any *real* production installation will require to
> customize the database schema. I always thought that Tryton will never
> generate the perfect schema but only a minimal working schema.
>
> > What about a configuration option 'oracle_compatibility = True', that will
> > slug the ususal names in a reproducible way?
>
> The problem is not oracle. The problem is the limitation that all
> databases have.
> But if you have a better solution, I will be graceful to evaluate.
> For example, a good algorithm to generate size compatible from Model
> name.
As we seem to have a maximal length of 64, I would propose to just truncate
table names, that hit that limit and to number them for avoidance of
collisions, e.g.:
table_name_longer_than_sixtyfour_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
would transform to
table_name_longer_than_sixtyfour_xxxxxxxxxxxxxxxxxxxxxxxxxx_001
If that maximal table name length cannot be determined automatically
(with python-sql or by some other means) this could be
the configuration parameter 'table_name_max_length'.
Additionally we could introduce the configuration option
'table_element_length'
The expected results would be
e.g. with
table_element_length = 3
table_name_max_length = 32
account.invoice.payment_term.line.relativedelta => acc_inv_pay_ter_lin_rel
account.payment.sepa.message => acc_pay_sep_mes
table.name.longer.than.sixtyfour.xxxxxxxxxxxxxxxx.yyyyy =>
tab_nam_lon_tha_six_xxx_yyy
table.name.longer.than.sixtyfour.xxxxxxxxxxxxxxxx.yyyyyy.zzzzz =>
tab_nam_lon_tha_six_xxx_yyy_001
The shorter the elements will be, the less a table name of course will be
readable for its underlying model. But an algorithm like this seems to a good
compromise for me.
--
Mathias Behrle
MBSolutions
Gilgenmatten 10 A
D-79114 Freiburg
Tel: +49(761)471023
Fax: +49(761)4770816
http://www.m9s.biz
UStIdNr: DE 142009020
PGP/GnuPG key availabable from any keyserver, ID: 0x8405BBF6
pgpgQmEkq2TVD.pgp
Description: Digitale Signatur von OpenPGP
