How to not use aliases for tables?

[goruneta]

The 'a', 'b', … aliases are pretty un-intuitive, is there a way to not use aliases and use the table names instead?

[ced (Cédric Krier)]

No because it ensures to avoid name collision.

[nicoe (Nicolas Évrard)]

Could we delegate this task to the user and allow her to set herself the alias of the table?

Something like:

>>> invoice = sql.Table('account_invoice').as_('invoice')
>>> line = sql.Table('acccount_invoice_line').as_('line')
>>> amount = invoice.join(line, condition=invoice.id == line.invoice).select(Sum(line.unit_price * line.quantity))
>>> print(amount)
'SELECT line.unit_price * line.quantity FROM account_invoice AS invoice INNER JOIN account_invoice_line as line'

I think it would just be a patch on AliasManager so that FromItem that are marked with an alias bypass completely the alias assignation mechanism and returns the user set alias.

AFAICS it seems doable.

[ced (Cédric Krier)]

It may be doable but not desirable. Alias are just a technical thing, user get no benefit from it as it does not change the query result. And allowing such naming will just introduce bugs with alias name collision.

[nicoe (Nicolas Évrard)]

@ced

It may be doable but not desirable.

I think it’s desirable: understandable aliases in logs helps tremendously.

@ced

And allowing such naming will just introduce bugs with alias name collision.

Once a developer decides to use this feature it’s his responsibility to ensure that collisions do not occur.

But if we want to guarantee that we could prefix user-set alias with an underscore, it would not hamper the readability too much and it wouldn’t clash with the generated ones.

[ced (Cédric Krier)]

@nicoe

I think it’s desirable: understandable aliases in logs helps tremendously.

But increase greatly the size of the query, so the speed of parsing and also may reach the limit of the database.
For me we must see the python-sql query generated as an optimized/minified language. So if you need to analyze the result, you could use a SQL editor which allow to rename aliases for example.

@nicoe

But if we want to guarantee that we could prefix user-set alias with an underscore, it would not hamper the readability too much and it wouldn’t clash with the generated ones.

I guess FromItem and Window could have a mode to add a suffix to the alias. But it can not be set once an alias has already be used.
But I doubt it will be used for the reasons above and because giving names is hard.

[nicoe (Nicolas Évrard)]

@ced

But increase greatly the size of the query, so the speed of parsing and also may reach the limit of the database.

I don’t think it’s really an issue.

@ced

I guess FromItem and Window could have a mode to add a suffix to the alias. But it can not be set once an alias has already be used.

I don’t understand the generated aliases are as good as it gets.
I was talking about adding a prefix in order to prevent a clash because we know this prefix is not used by the function generating those aliases.

@ced

But I doubt it will be used for the reasons above and because giving names is hard.

I don’t know why @goruneta wants to change the alias but I have already wanted to have more meaningful aliases for the sake of debugging (and yes I used an editor and a bunch of search & replace but it’s a tedious and error prone process).