1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Basic tools for Oracle version control: wanted!

Discussion in 'General' started by Jollo, Aug 26, 2011.

  1. Jollo

    Jollo Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi all,

    I’m looking for 3 simple things... well, I thought of them as simple, but I’m realizing they’re not to be taken for granted!

    1) a reverse engineering tool that I can point to an Oracle schema and get a “baseline” script to re-create that schema from scratch, with decently formatted DDL files (1 per object) neatly organized in a directory tree (by object type) and called in the correct order. Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to populate (insert) those tables as part of the script.

    2) a diff tool that I can point to a pair of Oracle instances (source and target) containing a given schema and get a “delta” script to alter the target schema so that it becomes identical to the source schema. If data loss occurs on the target instance (i.e. drop a column) I would like to find a warning comment inserted in the script (e.g. “-- Attention: data migration DML needed here?”). Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to update (delete, update and insert) the data in the target tables to become identical to the contents in the source tables *without* deleting and re-inserting all rows (or dropping and repopulating the table).

    3) I would like the above two tools (that, as you will have recognized, are basic to putting your database design under version control) to be open-source, with a command-line interface and a vibrant community backing them.

    I must be one out of a couple million people asking for the same things over and over again: I’ve seen the questions all over the internet but I could find no straight answer. Please help!

    Thanks and take care.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I know of no such tool providing the complete list of features in item 1, open source or not. ERWin is an excellent tool which can provide such output, however a small bit of scripting and some familiarity with the DBMS_METADATA package can provide your complete schema DDL and can provide it in order provided you use the information found in dba_dependencies to generate the list of objects. Also, using DBMS_METADATA gives you the option to output selected objects/tables as desired.

    TOAD does an admirable job of running 'diffs' against two supposedly matching schemas and generating the DDL to correct one or the other.

    Neither ERWin nor TOAD are open source and, for commercial use, aren't free. You found many asking for similar items on the web; you can also search that same web for the tools that do exist and offer at least part of the functionality you want.
     
  3. Jollo

    Jollo Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Thanks for the info on TOAD diffs, Zargon: I haven't tested that feature in recent versions (a few years ago it used to suck), I'll check it out. You know if you can invoke diff generation from the command line?

    I am not insisting on open source: more than willing to pay for robust tools...

    I am aware that such tools can be home-grown (have done it, and seen it done with different levels of success, many times, actually), but it strikes me as balatant reinvention of the weel. "Can do" does not mean I feel justified in investing resources in redeveloping for the n-th time the same tools for very very basic tasks.

    What I've found over the net is either overkill (Redgate) or off scope (DB Ghost has almost exactly the features I'm looking for, including command line, but is SQLServer only...). And, even if I did find some ads that claim to match the specs, getting other professionals' opinion is supposed to be the whole point of this forum, isn't it?

    Cheers
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    "And, even if I did find some ads that claim to match the specs, getting other professionals' opinion is supposed to be the whole point of this forum, isn't it?"

    I believe I did give you my opinion, and the discussion is still open for others to respond as well. We dispense opinion, advice, direction and, in some cases, solutions to questions/problems posted here. Mine is but one offering; that you took it as the final word on the discussion is unfortunate and unintended.
     
  5. Jollo

    Jollo Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Gotcha, no ill feelings at all. On the contrary, thanks again for the suggestion. I'll keep on collecting tidbits and will post a summary as soon as I feel I have a comprehensinve picture.
     
  6. hansbarnard

    hansbarnard Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    St Albans
    Hi Jollo

    Check out the tool Lure from the company Earthly Software Ltd. (I am not able to post a direct link to the website but just search for the website.) The demos on the website will give you a quick introduction of how this tool works.

    Lure addresses 1, and 2 (except it compares the source code in the extracted DDL files with the target database)

    Regarding 3, it is not open source but does have a command line interface.
     
  7. Jollo

    Jollo Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hey, thanks for the heads up! Looks interesting: I'll have to run through the demos, but it sure feels like a good match to my requirements. Right away, though, I am intrigued by the release strategy: the "patch history" is stored internally or on the user's version control repository? If you happen to know, I'd be grateful for a straight answer.

    Cheers
     
  8. hansbarnard

    hansbarnard Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    St Albans
    The idea is that both source files and patch files are ultimately stored in the version control system.

    Consider for example a table with name EMPLOYEE. The CREATE statement for this table is typically stored in a file with name "EMPLOYEE.table". Every time you make a change to the table and commit the source file to a version control system like Subversion, Subversion assigns a new revision number to the file. Let's say that the file EMPLOYEE.table was initially added at revision 38 and then subsequently changed at revisions 50, 71 and 94.

    Lure generates the following patch files:

    EMPLOYEE.table.patch.r38
    EMPLOYEE.table.patch.r50
    EMPLOYEE.table.patch.r71

    The idea is to add these patch files to the version control system as well. During deploy Lure compares the table in the target database with the different revisions of the file EMPLOYEE.table as stored in Subversion. If it finds a match then it executes the corresponding patch file.

    The advantage of this deployment strategy is twofold:

    1. Deployment is automated no matter what the current state of the target table is.
    2. As a user you maintain control as to how tables are migrated by being allowed to edit the patch files before adding them to the version control system.

    Hans
     
  9. cfondacci

    cfondacci Guest

    Hi,

    I think that neXtep open designer is the tool you are looking for :
    version control, reverse engineering, DDL instant generation from diff, synchronization, data management, free & open source.

    Please have a look at nextep-softwares dot com

    Christophe.