SQL Power Business Intelligence Productivity Tools

SQL Power Software Forum

SQL Power Software Forum

  [Search] Search   [Recent Topics] Recent Topics   [Hottest Topics] Hottest Topics   [Members]  Member Listing   [Groups] Back to home page  [Register] Register /  [Login] Login 
Features we want in the Architect  XML
Forum Index » SQL Power Architect Suggestions
Author Message
Jonathan
SQL Power Developer

Joined: 2007-01-08 15:10:32
Messages: 873
Offline

Hi everyone,

It's time to go in the other direction now.. we (the Architect developersw) have been responding to all the great bug reports and enhancement requests from the community for a few months now, but we've never mentioned what we want the Architect to do for our own uses. We'd like to use the Architect to maintain a metadata repository schema that we currently code by hand for Oracle, SQL Server, and PostgreSQL (MySQL support coming soon). Our hand coding involves making a create script for each schema version, as well as an upgrade script that does the incremental change from one version to the next. This is a lot of work, and we'd really prefer to use the Architect.

Here's a list of what we'd like the Architect to do before abandoning our hand-made repository schema create and upgrade scripts:

1. Project versioning. We would like to save the history of all schema revisions in one project file. The older versions wouldn't necessarily have to be editable (in fact, it would be better if they were read-only).

2. Subject Areas. The repository's data model is fairly large, but it's divided into several distinct collections of tables. It would be nice to have each subject area in a separate playpen.

3. Views. This is a hole in the Architect's current functionality. The problem with creating views in general is that different platforms have different syntax (i.e. Oracle joins) and different function names for almost everything (date and time functions, nvl/coalesce, case..when, and so on). I'm not really sure how to do this except to allow the user to write views using ANSI syntax and function names, then parse the SQL and write out the correct statement for the target platform. It's not trivial, but what choice do we have?

4. Seed data for tables. Some of the tables in our schema need to be pre-populated with data. It would be best if we could specify this in some sort of tabular view within the Architect. The data could come out as INSERT statements in the create script.

5. Refactorings. This one was already requested on the forum, and it's something we want too. We'd definitely need to be able to identify and properly handle the following refactorings:
  • Rename column

  • Rename table

  • Rename constraint (relationship, primary key, index)

  • Change column data type

  • Change primary key (in our case, from natural to surrogate)


  • This refactoring stuff is a big topic.. there are lots of corner cases to worry about. For instance, when changing a column's data type, it might be necessary to null out all values in the column, then put them back (for instance, Oracle doesn't allow reducing numeric scale when a column contains non-null values).

    So that's that. As always, I'd love to hear your comments.

    -Jonathan

    This message was edited 1 time. Last update was at 2008-07-10 16:44:00

    supermario


    Joined: 2006-09-13 14:50:52
    Messages: 60
    Offline

    great suggestions....

    I've played with the TOAD data modeler and here's a list of things that I like about it. If you want me to demo these features so that you can see if they are good/applicable, let me konw:

    * the ability to go with straight vs orthogonal relationship lines
    * the relationship lines always seem to land in the right spot on the object...always around the centre
    * toggle between physical and logical mode
    * ability to toggle indices on or off
    * when i click on an object on the left navigator tree pane, the view changes so that the object is visible
    * the html report is awesome
    * it has a to-do list so that you can jot down a task list of all things that need to be done to the model and tick them off when done
    * submodels (aka subject areas)
    * projects and version control
    * ability to compare versions
    * zooming function is cool...although I'd like to see birds-eye view of the model that I could navigate a la google maps
    * being able to grab the piece of paper with my mouse and move around the sheet would be useful too
    husein


    Joined: 2008-01-08 13:14:14
    Messages: 14
    Offline

    The above list is great. Here is my 2 cents...

    1) enhance logical modeling by introducing following:

    a) subtype object add option to roll-up or roll-down the columns
    b) column domain values; add option to display domain text values beside the table containing the column (no existing tool on the market has this!!!)
    c) add relationship names (labels) definition and display relationship name on the model (should be an option)
    d) allow for relationship labels to be moved/dragged around the model
    e) add option to select target model DBMS (makes choosing column data types much easier)
    f) allow for Alternative Key creation (AK) in the table
    g) add option to display column key designation (PK,AK,FK) in the table,
    h) option to enforce unique column names across the model or within the table
    i) table/column fill color palette (great for showing old vs. new on the model)
    j) Add table/column definition section in addition to remarks
    k) Data Dictionary creation (HTML, CSV, XML)

    2) enhance physical modeling by introducing following:

    a) data volumetric (records or Bytes). Allow to specify Min rows, Max rows, Avg daily/monthly growth in num. of records or percentage
    b) table partitioning support
    c) index type selection (hash, bitmap, etc) (good for performance tunning)
    d) materialized view support for Oracle
    e) Data Lineage tracking for target column sourced from many source columns.

    Cheers,
    Husein
    Jonathan
    SQL Power Developer

    Joined: 2007-01-08 15:10:32
    Messages: 873
    Offline

    Thanks for these suggestions, Husein! These are definitely the types of things we want to have available before calling the Architect a "version 1.0" production-ready tool.

    I have a few questions about items I'm not clear on:

    1a) I'm not sure what this means.. can you explain? Even if you can just point to the documentation or a screenshot of another data modeling tool that supports this feature, it might help me understand.

    1j) Do you mean to add another free-form text field where the user can type stuff in, or is this some information the Architect would generate for you?

    1k) Do you mean a human-readable data dictionary that enumerates all the tables, columns, relationships, indexes, and so on? If so, for the XML version, is there an established standard for this? I've learned a little bit about DBXML, but I think that's more about data transfer than data definition. Any further suggestions you can offer on this point would be a great help.

    Thanks again for your input!

    -Jonathan
    husein


    Joined: 2008-01-08 13:14:14
    Messages: 14
    Offline

    Jonathan wrote:Thanks for these suggestions, Husein! These are definitely the types of things we want to have available before calling the Architect a "version 1.0" production-ready tool.

    I have a few questions about items I'm not clear on:

    1a) I'm not sure what this means.. can you explain? Even if you can just point to the documentation or a screenshot of another data modeling tool that supports this feature, it might help me understand.

    -- Please see image attached below

    1j) Do you mean to add another free-form text field where the user can type stuff in, or is this some information the Architect would generate for you?
    -- Yes, free form multi-line field

    1k) Do you mean a human-readable data dictionary that enumerates all the tables, columns, relationships, indexes, and so on? If so, for the XML version, is there an established standard for this? I've learned a little bit about DBXML, but I think that's more about data transfer than data definition. Any further suggestions you can offer on this point would be a great help.

    Yes, data dictionary provides table/column definitions, etc. I'm not sure about any XML standards, but as long as we publish XML schema, this should not be a problem

    Thanks again for your input!

    -Jonathan
    [Thumb - Supertype-pattern.JPG]
     Filename Supertype-pattern.JPG [Disk] Download
     Description
     Filesize 32 Kbytes
     Downloaded:  2661 time(s)

    Jonathan
    SQL Power Developer

    Joined: 2007-01-08 15:10:32
    Messages: 873
    Offline

    That's great, thanks! Everything you've suggested is now clear to me.

    Once we've worked through setting our priorities for the next few releases (which includes making sure everything is accurately represented in Bugzilla), we will post a "roadmap" type writeup to this forum. Your suggestions will all be in the pool along with the others we already had.

    -Jonathan
    Jeff
    SQL Power Developer
    [Avatar]

    Joined: 2007-06-27 18:31:33
    Messages: 410
    Offline

    I think this topic warrants being made into a 'sticky'. Also, I'm working on a draft 'roadmap' which is available on the project wiki:

    http://code.google.com/p/power-architect/wiki/ProjectRoadmap

    It's still a work in progress, and may have some items moved around into different versions.

    If you have any things you'd like added to the list, post to this thread!

    -Jeff
    Milla


    Joined: 2008-07-17 06:37:34
    Messages: 2
    Offline

    husein wrote:
    Jonathan wrote:
    1k) Do you mean a human-readable data dictionary that enumerates all the tables, columns, relationships, indexes, and so on? If so, for the XML version, is there an established standard for this? I've learned a little bit about DBXML, but I think that's more about data transfer than data definition. Any further suggestions you can offer on this point would be a great help.

    Yes, data dictionary provides table/column definitions, etc. I'm not sure about any XML standards, but as long as we publish XML schema, this should not be a problem
    -Jonathan

    I've had a look for XML Schemas that define database data dictionaries, and found the following two:

  • Turbine XML format used by various Apache tools

  • AXMLS used by ADOdb
  • Jonathan
    SQL Power Developer

    Joined: 2007-01-08 15:10:32
    Messages: 873
    Offline

    Thanks, Milla.

    I took a 5-minute look at both of the XML Schemas you mentioned. They both look reasonably complete. I don't think they're quite rich enough to replace the Architect's native format (and such an undertaking probably wouldn't be terribly useful) but I can definitely see good value in providing an "export" feature to either or both of those formats.

    It could be done in XSLT, much like Thomas' script that transforms a project into an HTML report; it could also be done in Java code that carefully creates XML directly, or code that builds up a DOM tree then serializes it.

    -Jonathan

    Jonathan
    SQL Power Developer

    Joined: 2007-01-08 15:10:32
    Messages: 873
    Offline

    Thanks, Milla.

    I took a 5-minute look at both of the XML Schemas you mentioned. They both look reasonably complete. I don't think they're quite rich enough to replace the Architect's native format (and such an undertaking probably wouldn't be terribly useful) but I can definitely see good value in providing an "export" feature to either or both of those formats.

    It could be done in XSLT, much like Thomas' script that transforms a project into an HTML report; it could also be done in Java code that carefully creates XML directly, or code that builds up a DOM tree then serializes it.

    -Jonathan

    AndreyM


    Joined: 2008-07-25 08:15:05
    Messages: 1
    Offline

    For Husein's suggestions about relationship labels (Bug#: 1483) I'm thinking this should be an option which will allow to write own labels or display constraint names.

    Thank you for great tool, the only other feature I'm missing is "Subject Areas" but ic it's in Road Map too
    Jonathan
    SQL Power Developer

    Joined: 2007-01-08 15:10:32
    Messages: 873
    Offline

    Thanks for your comments, Andrey.

    I agree with your conclusions about Husein's suggestion on relationship labeling, and yes--subject areas are certainly on the to-do list.

    -Jonathan
    kandrews


    Joined: 2008-09-22 14:26:40
    Messages: 7
    Offline

    Hi Jonathon,

    These may have already been addressed or ruled out. Not sure...

    I would like to be able to suppress dropping columns and tables in a forward engineer scenario or in the DB Compare and gen SQL script.

    In other words it should add tables and columns but not drop anything.

    Also it would be great if the tool was able to check if the existing column's content will fit in the adjusted column. So if it is going to alter column foo_a which is currently a varchar(30) with varchar(200) then no issues and let it fly however if it is going to try and alter it to be a varchar(5) then I would like the ability to alter it's behavior and tell it not do make this adjustment.

    Are these on teh list already?

    Are these already features which it has?

    Thoughts?

    Thanks

    Kent
    Jonathan
    SQL Power Developer

    Joined: 2007-01-08 15:10:32
    Messages: 873
    Offline

    Hi Kent,

    These are both great ideas, and should actually be easy to implement. Unfortunately, I can't think of a way to achieve either function with the product as-is. You'll have to wait until we get the time to implement these ideas.

    This enhancement request is bug 1657.

    -Jonathan
    kandrews


    Joined: 2008-09-22 14:26:40
    Messages: 7
    Offline

    "Excellent" ... to quote Monty Burns....



    Many Thanks

    Kent
     
    Forum Index » SQL Power Architect Suggestions
    Go to:   
    Powered by JForum 2.1.8 © JForum Team