Menu

#9 Table alias names

closed
sqlbuilder (11)
5
2012-09-29
2011-10-11
No

Alias names are uncontrolled and unpredictable: 1) there is no easy way to set certain alias name for Table (e.g. setAlias(String)), only overriding Spec; 2) no good way to NOT use aliases (e.g. hard-coded alias appending in com.healthmarketscience.sqlbuilder.TableDefObject class).

Discussion

  • James Ahlborn

    James Ahlborn - 2011-10-11

    1) the DbTable's alias is controllable, as you mentioned, you can create a custom implementation of DbSpec and override getNextAlias(). i will add a constructor to DbTable which passes in an alias for further control. also, you are free to use your own custom implementation of Table which uses whatever aliases you would like, you are not required to use the com.healthmarketscience.sqlbuilder.dbspec.basic classes (that's why the interfaces are separate from the implementation).

    2) you could return a blank alias for your Table and use a custom SqlContext which always returns false for getUseTableAliases() if you really want to avoid using table aliases.

     
  • Ivan Bondarenko

    Ivan Bondarenko - 2011-10-11

    1) Yes, constructor is not bad idea.

    2) Blank alias will not work with columns then (columns generated in ColumnObject will look like ".col_name"). This is actually more architectural issue, so it intercepts with other my reports (e.g. point 2 of #3421713). getUseTableAliases() can be not same for whole query, some queries require mixed usage: SELECT alias.a, b FROM <something>, where <something> is ambiguous as for column "a".

     
  • James Ahlborn

    James Ahlborn - 2011-10-11

    2) i still don't understand, why do you need to have a without an alias?

     
  • Ivan Bondarenko

    Ivan Bondarenko - 2011-10-11

    2) This is more related to convenience rather than to functionality. If column is not ambiguous, there is no need to add prefixes which are not always good readable. for example "SELECT a, b, c FROM tab" looks better than "SELECT alias.a, alias.b, alias.c FROM tab alias".
    In any case query should have a possibility to have no aliases (e.g. sql string length can be significant).
    Also if we have nested SELECT queries, it is possible that some of them have aliases and some haven't.
    Looks like TableDefObject must be changed only slightly, but it it is also hampered on conversion issue (somebody may want to have " AS " table aliasing instead of " ", but it is a lot of code to do this now).

     
  • James Ahlborn

    James Ahlborn - 2011-10-12

    SqlBuilder should now handle a Table with no alias and allow the DbTable alias to be passed in the constructor.

    Fixed in trunk, will be in the 2.0.10 release.

     

Log in to post a comment.