Database schema name limit

[Cédric Krier]

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?
--
Cédric Krier - B2CK SPRL
Email/Jabber: cedric...@b2ck.com
Tel: +32 472 54 46 59
Website: http://www.b2ck.com/

[Mathias Behrle]

* 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.

What about a configuration option 'oracle_compatibility = True', that will slug
the ususal names in a reproducible way?



--

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

[Cédric Krier]

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.

[Raimon Esteve]

2015-05-21 17:52 GMT+02:00 Cédric Krier <cedric...@b2ck.com>:
>
> 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?

Other idea is about add new attribute class to reference "short databade name".

for example, now we have (1):

_table = 'party_category_rel'

Idea is to add new attribute short table:

_short_table = 'par_cat_rel'

And finally a new param in database section in trytond.conf database
to active/desactive short tables names.

(1) http://hg.tryton.org/modules/party/file/ea17e7b5ecef/party.py#l253

Regards

--
Raimon Esteve

[Mathias Behrle]

* Cédric Krier: " Re: [tryton-dev] Database schema name limit" (Sat, 23 May
2015 12:18:32 +0200):

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.

[Pierre-Louis Bonicoli]

On 21/05/2015 17:52, Cédric Krier wrote:
> 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 names of the tables should be identical for two installations using
the same backend. A module should not require this kind of configuration.

Could not we delegate the transformation of a model name into a table
name to the backends with something like that:
'cls._table = backend.TableHandler.name(cls.__name__)' ?
Another solution could be to add a 'max_name_length' for each backend
and to implement 'truncate_table' in the core (like sqlalchemy and
django [1]).

Besides we should take care of the 'history' tables for which the suffix
'__history' is added.

Ideally, the max length should be configurable using trytond.conf:
PostgreSQL could be recompiled [2].

[1]
https://github.com/django/django/blob/master/django/db/backends/utils.py#L178
[2]
http://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

--
Pierre-Louis

[Oscar Andres Alvarez Montero]

I am not expert but, I agree with Raimon

[Cédric Krier]

What is this last number, how is it computed?

> 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.

I don't see any description of an algorithm here, just some wished
behaviour.

[Cédric Krier]

And who will set all those "short" name? And what will be the properties
of such short name? Which length is allowed? How prevent they don't
collide?

[Cédric Krier]

OK but what is your proposal on how such method will always create valid
table name.

> Another solution could be to add a 'max_name_length' for each backend
> and to implement 'truncate_table' in the core (like sqlalchemy and
> django [1]).

And how do you avoid name collision?

> Besides we should take care of the 'history' tables for which the suffix
> '__history' is added.
>
> Ideally, the max length should be configurable using trytond.conf:
> PostgreSQL could be recompiled [2].

That's why it should be a case by case solution because there is no
silver bullet here for me.

[Pierre-Louis Bonicoli]

We keep the current behavior (dots replaced by underscore) and use the
same approach as django (last X characters replaced by
'hash(name)[:X]').

>> Another solution could be to add a 'max_name_length' for each backend
>> and to implement 'truncate_table' in the core (like sqlalchemy and
>> django [1]).
>
> And how do you avoid name collision?

Same as above.

--
Pierre-Louis

[Nicolas Évrard]

* Pierre-Louis Bonicoli [2015-05-26 02:33 +0200]:

Isn't this better:

hash = str(hash(old_name))
new_name = old_name[:maximum_length - len(hash)] + hash

(It is probably better to use an hexadecimal representation of the
hash in order to reduce its length).

But it is still possible to create a table that will collide with
another whose name is too long (unless we transform every table with
this function but it is not a desirable behavior according to me).

I find this idea quite elegant (because there is no need for the
configuration file) but the names generated will be strange and not
meaningful.

So for the sake of interoperability with other systems I still prefer
the mapping solution (although it's an ugly one and I would welcome a
better idea).

--
Nicolas Évrard - B2CK SPRL
E-mail/Jabber: nicolas...@b2ck.com

[Jean C]

2015-05-26 2:33 GMT+02:00 Pierre-Louis Bonicoli <pierre-lou...@ir5.eu>:

We keep the current behavior (dots replaced by underscore) and use the
same approach as django (last X characters replaced by
'hash(name)[:X]').

Problem is, tryton sometimes relies on finding the table in the database

for data migration or schema update. How can it be done if you cannot

identify consistently the table name on which you need to work ?

Jean Cavallo

Coopengo

[Cédric Krier]

I don't understand. It is one or the other but I don't see the point of
both together.
Also having only hashed name table will make the database schema very
difficult to understand.

> >> Another solution could be to add a 'max_name_length' for each backend
> >> and to implement 'truncate_table' in the core (like sqlalchemy and
> >> django [1]).
> >
> > And how do you avoid name collision?
>
> Same as above.

This doesn't prevent collision and more over in case of such collision
happens there will be no solution to fix it.

[Pierre-Louis Bonicoli]

What hash function would you like to use ? Django uses a value of 4 for
X, we could use a greater value.

> (It is probably better to use an hexadecimal representation of the
> hash in order to reduce its length).
>
> But it is still possible to create a table that will collide with
> another whose name is too long (unless we transform every table with
> this function but it is not a desirable behavior according to me).

You mean that collision is still possible using a model name like
'account.invoice.paymen97171639' ?

> I find this idea quite elegant (because there is no need for the
> configuration file) but the names generated will be strange and not
> meaningful.
>
> So for the sake of interoperability with other systems I still prefer
> the mapping solution (although it's an ugly one and I would welcome a
> better idea).

If the chosen solution is the mapping one, the documentation (core and
each module) should list the names of the tables.

--
Pierre-Louis

[Pierre-Louis Bonicoli]

On 26/05/2015 09:20, Jean C wrote:
>
> 2015-05-26 2:33 GMT+02:00 Pierre-Louis Bonicoli

> <pierre-lou...@ir5.eu <mailto:pierre-lou...@ir5.eu>>:

>
> We keep the current behavior (dots replaced by underscore) and use the
> same approach as django (last X characters replaced by
> 'hash(name)[:X]').
>
>
> Problem is, tryton sometimes relies on finding the table in the database
> for data migration or schema update. How can it be done if you cannot
> identify consistently the table name on which you need to work ?
>
> Jean Cavallo

> /Coopengo/

The method above allows to identify consistently the table name.

For example, using the oracle backend, the name of the table used by the
model 'account.invoice.payment_term.line.relativedelta' will always be
'account_invoice_paymen97171639'.

--
Pierre-Louis

[Nicolas Évrard]

* Pierre-Louis Bonicoli [2015-05-26 13:58 +0200]:

Any good hash function that will return the same result across python
versions. But as I said this idea comes with its drawbacks so I don't
think we should implement it.

>> (It is probably better to use an hexadecimal representation of the
>> hash in order to reduce its length).
>>
>> But it is still possible to create a table that will collide with
>> another whose name is too long (unless we transform every table with
>> this function but it is not a desirable behavior according to me).
>
>You mean that collision is still possible using a model name like
>'account.invoice.paymen97171639' ?

Yes.

>> I find this idea quite elegant (because there is no need for the
>> configuration file) but the names generated will be strange and not
>> meaningful.
>>
>> So for the sake of interoperability with other systems I still prefer
>> the mapping solution (although it's an ugly one and I would welcome a
>> better idea).
>
>If the chosen solution is the mapping one, the documentation (core and
>each module) should list the names of the tables.

Why should it?

If it's up to the administrators of the tryton server to decide which
name a table should have through configuration why should we add this
information in the documentation? We might as well define the name of
the tables in the objects if this information is static.

But of course the mapping defined in the configuration has to be known
by people using other systems to access the data. But this is up to
the people implementing their ERP to provide this information.

[Pierre-Louis Bonicoli]

Model name using numbers only from twenty-third to thirtieth characters
could be considered as invalid.

>>> I find this idea quite elegant (because there is no need for the
>>> configuration file) but the names generated will be strange and not
>>> meaningful.
>>>
>>> So for the sake of interoperability with other systems I still prefer
>>> the mapping solution (although it's an ugly one and I would welcome a
>>> better idea).
>>
>> If the chosen solution is the mapping one, the documentation (core and
>> each module) should list the names of the tables.
>
> Why should it?

How will an administrator know that the table used by the model
'account.invoice.payment_term.line.relativedelta' should be mapped ?

Before a new module installation, knowing only the name of a table which
should be mapped, how will an administrator know the names of the other
tables used by this module - the names he can not use with the mapping ?

> If it's up to the administrators of the tryton server to decide which
> name a table should have through configuration why should we add this
> information in the documentation? We might as well define the name of
> the tables in the objects if this information is static.
>
> But of course the mapping defined in the configuration has to be known
> by people using other systems to access the data. But this is up to
> the people implementing their ERP to provide this information.

--

Pierre-Louis

[Cédric Krier]

On 26 May 14:35, Nicolas Évrard wrote:
> * Pierre-Louis Bonicoli [2015-05-26 13:58 +0200]:

> >If the chosen solution is the mapping one, the documentation (core and
> >each module) should list the names of the tables.
>
> Why should it?
>
> If it's up to the administrators of the tryton server to decide which
> name a table should have through configuration why should we add this
> information in the documentation? We might as well define the name of
> the tables in the objects if this information is static.
>
> But of course the mapping defined in the configuration has to be known
> by people using other systems to access the data. But this is up to
> the people implementing their ERP to provide this information.

Indeed it is the opposite, if we implement the pilou proposal we will
have to document the table name that each Model will get under every
conditions (each backend constraint).

--
Cédric Krier - B2CK SPRL
Email/Jabber: cedric...@b2ck.com

[Cédric Krier]

On 26 May 15:14, Pierre-Louis Bonicoli wrote:
> How will an administrator know that the table used by the model
> 'account.invoice.payment_term.line.relativedelta' should be mapped ?

Easy it will crash otherwise.

> Before a new module installation, knowing only the name of a table which
> should be mapped, how will an administrator know the names of the other
> tables used by this module - the names he can not use with the mapping ?

We don't care because we don't expect people installing new module
without proper testing.

--
Cédric Krier - B2CK SPRL
Email/Jabber: cedric...@b2ck.com

[Nicolas Évrard]

* Pierre-Louis Bonicoli [2015-05-26 15:14 +0200]:

>On 26/05/2015 14:35, Nicolas Évrard wrote:
>> * Pierre-Louis Bonicoli [2015-05-26 13:58 +0200]:
>>> On 26/05/2015 09:19, Nicolas Évrard wrote:
>>>> (It is probably better to use an hexadecimal representation of the
>>>> hash in order to reduce its length).
>>>>
>>>> But it is still possible to create a table that will collide with
>>>> another whose name is too long (unless we transform every table with
>>>> this function but it is not a desirable behavior according to me).
>>>
>>> You mean that collision is still possible using a model name like
>>> 'account.invoice.paymen97171639' ?
>>
>> Yes.
>>
>
>Model name using numbers only from twenty-third to thirtieth
>characters could be considered as invalid.

If we decide to implement to such a rule then we might as well decide
that model name must be < 30 character wide.

>>>> I find this idea quite elegant (because there is no need for the
>>>> configuration file) but the names generated will be strange and
>>>> not meaningful.
>>>>
>>>> So for the sake of interoperability with other systems I still
>>>> prefer the mapping solution (although it's an ugly one and I
>>>> would welcome a better idea).
>>>
>>> If the chosen solution is the mapping one, the documentation (core
>>> and each module) should list the names of the tables.
>>
>> Why should it?
>
>How will an administrator know that the table used by the model
>'account.invoice.payment_term.line.relativedelta' should be mapped ?

If the installation does not crash (can we expect a DBMS to truncate
silently the name of a table? (MySQL maybe)) then there will be no
error so maybe we should raise an error ourselves.

>Before a new module installation, knowing only the name of a table which
>should be mapped, how will an administrator know the names of the other
>tables used by this module - the names he can not use with the mapping ?

He will indeed have to compute the name of the new table. But I guess
they tested their work before going live.

[Pierre-Louis Bonicoli]

On 26/05/2015 15:18, Cédric Krier wrote:
> On 26 May 14:35, Nicolas Évrard wrote:
>> * Pierre-Louis Bonicoli [2015-05-26 13:58 +0200]:
>>> If the chosen solution is the mapping one, the documentation (core and
>>> each module) should list the names of the tables.
>>
>> Why should it?
>>
>> If it's up to the administrators of the tryton server to decide which
>> name a table should have through configuration why should we add this
>> information in the documentation? We might as well define the name of
>> the tables in the objects if this information is static.
>>
>> But of course the mapping defined in the configuration has to be known
>> by people using other systems to access the data. But this is up to
>> the people implementing their ERP to provide this information.
>
> Indeed it is the opposite, if we implement the pilou proposal we will
> have to document the table name that each Model will get under every
> conditions (each backend constraint).

Why would that be needed ?

--
Pierre-Louis

[Cédric Krier]

On 26 May 15:40, Nicolas Évrard wrote:
> If the installation does not crash (can we expect a DBMS to truncate
> silently the name of a table? (MySQL maybe)) then there will be no
> error so maybe we should raise an error ourselves.

How could we know there is a problem if the database doesn't tell us.
I think we should not care about bad behaviour of such database if it
exists.

--
Cédric Krier - B2CK SPRL
Email/Jabber: cedric...@b2ck.com

[Cédric Krier]

I want to write a SQL query on the database (not inside trytond), how
can I know the tables to use as they are generated instead of using the
class name.
More over with your design we will have a lot of 'account_invoicexxxx'
and the worst part of this design is that usually it is the last char of
the name that contains the most important information.
For example:

- account_invoice_line
- account_invoice_tax
- account_invoice_line-account_tax

While with my proposal, it is explicit (better than implicit) and use
can use meaningful table name for his usage like:

- acc_inv_line
- acc_inv_tax
- acc_inv_line-tax

[Cédric Krier]

On 21 May 17:52, Cédric Krier wrote:
> 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

Here is the review of this solution: https://bugs.tryton.org/issue4774
It is a very small change.