Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

#40 Following synonyms

Next Release
open
John Currier
5
2008-01-10
2008-01-10
veeroohre
No

Hi,

I often use synonyms to improve the handling of monthly loaded data (a schema per load). So after loading the new data I just have to change the synonyms and the querys are still working.
Unfortunately SchemaSpy doesn't follow synonyms (as far as I know).

Would be very nice to implement a switch (on/off) which controls the behavior of SchemaSpy to follow such synonyms.

If it's already there please forgive me. Please let me know where to find this option.

Veeroohre

Discussion

  • John Currier
    John Currier
    2008-01-11

    Logged In: YES
    user_id=1264584
    Originator: NO

    That's correct...SchemaSpy doesn't know anything about synonyms. It looks like the database metadata returns some basic info about synonyms, but I haven't seen where it says what it's a synonym for.

    I assume that from SchemaSpy's perspective a synonym is identical to an alias, right?

    John

     
  • Logged In: NO

    Explanation:

    "Use the CREATE SYNONYM statement to create a synonym, which is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class schema object, user-defined object type, or another synonym.

    Synonyms provide both data independence and location transparency. Synonyms permit applications to function without modification regardless of which user owns the table or view and regardless of which database holds the table or view. However, synonyms are not a substitute for privileges on database objects. Such privileges must be granted to a user before the user can use the synonym.

    You can refer to synonyms in the following DML statements: SELECT, INSERT, UPDATE, DELETE, EXPLAIN PLAN, and LOCK TABLE.

    You can refer to synonyms in the following DDL statements: AUDIT, NOAUDIT, GRANT, REVOKE, and COMMENT."

    see "http://www.lorentzcenter.nl/awcourse/oracle/server.920/a96540/statements_72a.htm#2060937"

    Example: "CREATE SYNONYM b1 FOR b.b1"

    Hopefully now it's clearer what i meant.
    Is it possible to implement?

    Greetings

    Veeroohre

     
  • John Currier
    John Currier
    2008-03-04

    Logged In: YES
    user_id=1264584
    Originator: NO

    The JDBC metadata doesn't appear to give any synonym details. SchemaSpy would somehow have to figure out what each synonym was associated with, but those details aren't available without custom code for each type of database.

    The problematic part is that SchemaSpy uses table and view names as keys for its mappings between tables. With synonyms those table and view names can no longer be used without being "de-aliased". Note that the concept of mapping between database objects by name is pervasive throughout the code. It's not impossible, but it's definitely not simple.