SQL Developer Data Modeler

Apologies for the length. I'm baffled.

If I'm supposed to be opening support tickets elsewhere for DM, please let me know.

All the parties referred to in this thread are using DM 4.1 (4.1.0.881) on Windows 7 Enterprise 64bit. We all have TortoiseSVN 1.8.11 64bit. And the SVN client in DM is 1.8.5.

Short version:

I am able to open a version-controlled model in DM, make changes, save them, commit them and everything works.

My teammates can open their local working copy of the same model, refresh, see my incoming changes, and update their working copy. Then we learned the hard way they have to close it and re-open it to see the changes in the diagram. At that point they have the latest design open. Then like me, they make a change, save it, and attempt to commit, but get the following error message:

svn: E200007: Commit failed (details follow):

svn: E200007: CHECKOUT can only be performed on a version resource [at this time].

svn: E175002: CHECKOUT request failed on '/svn/MMSDB/!svn/rvr/31368/trunk/models/MDMI/MDMI/rel/EF6D8D17-6077C9C6CF40/table/seg_0/2FD63EA9-F729-11ED-B865-69AE700E6385.xml'

Among the many things we tried, we tried skipping the refresh, update, close and re-open steps. Instead I had them close the design, go to their file manager and use TortoiseSVN to do an SVN Update on the models directory of our Subversion repository. It pulled down my latest changes to their working copy. They then went back to DM and re-opened the design, which showed that it was up to date with no pending changes. They then tried the same test as above, making a change, saving it, then attempting to commit. No joy. Same error message.

Why is it attempting checkout during commit?

Why is it failing?

Are we using Subversion and DM in some manner that was not intended? Why does my setup work?

Long version with detail:

I sure do wish I could pick up the phone, talk to a DM developer and share my desktop. We could then get to the bottom of this quickly.

We really need this to be smoother, bug-free, and more intuitive before I introduce it to the other 50 DBAs at my company. Hence, the reason I'm trying to understand DM's Subversion integration and why our setup only works for me, so I can eventually explain it to them.

We have an existing subversion server that serves many departments. It has many projects (repositories) on it. Ours is the "MMSDB" project, containing all our department's database development source code and artifacts. We want it to contain all the modeling we do as well. Since we don't use branches or tags, everything is under trunk. All the DBAs in my department have checked out the URL https://{subversion server}/svn/MMSDB/trunk to C:\Projects\mmsdb on their laptops. New files (and sometimes new directories) are added and committed somewhere under MMSDB every day. We use Tortoise SVN, and its shell integration with Windows Explorer or File Commander to do all SVN commands. C:\Projects\mmsdb has a .svn folder. The Smith/Helskyaho OOW presentation mentions this .svn folder is created for the working directory only once, upon initial checkout. I think point is important for this problem diagnosis and we'll come back to it.

I struggled for several days last week trying to get our existing Subversion repository integrated with DM. Had to read every help doc and web resource available on the subject. Turns out the 4.0 release notes and the OOW seminar Jeff and Heli gave were the most useful resources. Here's what finally worked for me (after deleting the remnants of many failed attempts):

• In DM, created new repository connection, pointing to MMSDB\trunk. Named the new connection "MMSDB". Test. Success.
• In Versions, under the MMSDB repository connection, created a new remote directory and named it "models". (We already had a place to put models under MMSDB\trunk\_Documentation\DataModels, but so many attempts failed when I was trying use that existing folder for DM models, that I finally gave up on it and decided to create a new home for all our models directly under MMSDB\trunk.
• Clicked the models directory and created another new remote directory under it and named it "MDMI" (which stands for Master Data Management Interface). This was to be the future home of our new MDMI table designs.
• Learned the hard way that I would need a place to keep system types. So under the models directory, I created another new remote directory and named it "types".
• Checked out the "models" subfolder in the Versions navigator, which created empty folders on my hard drive. I now had C:\Projects\mmsdb\models\MDMI and C:\Projects\mmsdb\models\types.
• At this point, or sometime later while doing File | Save As or fiddling with things trying to make them work, I ended up with a .svn folder under C:\Projects\mmsdb\models\MDMI. None of my co-workers have this .svn folder. Does this have something to do with why my commits work and theirs do not? Why was a new .svn folder created at this level instead of using the existing one seen under C:\Projects\mmsdb?
  
• After checking out the new remote directories to the working area, I reverse engineered the MDMI tables from an existing Oracle schema and saved the whole design to C:\Temp\models\MDMI. Under there I see an MDMI subfolder and an MDMI.dmd file.
• I then did a File | Save As on the open design and saved it to C:\Projects\mmsdb\models\MDMI
• It asked if I wanted to version the design. Of course I chose Yes (after days of struggling to get it this far).
• DM took a while to add and commit all the MDMI files, but it finally finished. Checked in my file manager. Yep. It shows the MDMI.dmd and MDMI subfolder under the C:\Projects\mmsdb\models\MDMI working copy folder. They have the green checkmark icon indicating they are versioned, checked-in and current. After this I had no trouble opening the model from the working copy area, making changes to the model, saving it, seeing the outgoing pending changes, committing and then checking the Subversion logs to ensure the changes were sent to the Subversion repository.

With victory under my belt, I got two of the six co-DBAs to install JDK 1.8, SQL Developer and SDDM 4.1 on their laptops, which are exact replicas of mine. With that done...

• My co-DBAs each set up a new repository connection to MMSDB/trunk, naming it MMSDB just like mine.
• They don't need to create the models, types or MDMI subfolders or check them out, because they already exist after I created them.
• However, the 4.0 release notes and the Smith/Helskyaho presentation (under "Working with a new design" slide) both indicate you have to check out a design from a Subversion repository and then open the design from the working directory. The 4.0 release notes also mention immediately committing after checking out because "This commit action sets the repository property against the design within the product." I am very interested in the details underneath that statement.  Where and how is the "repository property set against the design within the product"? I think this is essential to finding the answer to our problem.
• Checking out nor committing make any sense to me in this context (existing design already committed to existing Subversion repository). But sticking to instructions, in Versions, my co-DBAs navigate down to the new models directory. They attempt to check out the models directory to their local disk. They get this error:
  • "svn: E155000: 'C:\Projects\mmsdb\models' is already a working copy for a different URL; perform update to complete it"
• That failed. So they go to their file management tool and use TortoiseSVN to do an SVN Update. This sees the new models directory (and its subfolders), and brings it all down from the server to their local disk. Now they have C:\Projects\mmsdb\models, types and MDMI folders just like me with the latest content.
• They then return to DM and open the C:\Projects\mmsdb\models\MDMI\MDMI.dmd file
• It all comes up and looks good and current.
• To test the team modeling feature of DM, they make a change to an entity (change the length of a column). They save it. The Pending Changes tab now shows the outgoing changes to be 1.
• They then attempt to commit, and get error the stack seen above, especially E200007: CHECKOUT can only be performed on a version resource [at this time].

The Smith\Helskyaho slide on "Working with a design" makes sense, but it is the commit step that works for me and not my co-workers. I suspect it has to do with the fact I created the initial folders in the repository and did an initial checkout before saving a design into the working directory. I suspect it might have something to do with the .svn folder I have under the MDMI folder that they don't have. But beyond that, I'm at a loss.

Let's get this problem solved for the current version. Then I'd love to see this made smoother for a future versions of DM.

If anybody like Jeff Smith or the lead designer/developer there at Oracle cares to know, this shouldn't be this hard. If I were designing DM, here's how I'd like to see the user story of an incoming change work:

• Lead modeler takes an existing Subversion repository and creates a new models folder in Versions. This is to be the home directory of all modeling work (this works)
• Lead modeler creates a new project-specific folder under the models folder in Versions (this works)
• Lead modeler right-clicks the models folder in Versions and picks an action to "Update working copy", which brings the new structure down to local drive. (this action is not offered independent of a model. Have to open a model, then go to Pending Changes, Incoming Changes for that.)
  
• Lead modeler creates or RE's a new design and saves it to the local working copy folder, saves it (which adds it to Subversion) and then commits it (which commits it to Subversion). (this is working for me, but not my co-workers)

Here's where things get interesting and useful for concurrent design on the same model

• Story 1: I notify my co-workers the model has changed. They should be able to:
  • Using Tortoise, do an SVN Update of their local models folder and get all the new stuff.
  • They go to DM, and open the model's .dmd file and can begin to make their changes and commit. (this seems to work, until they try to commit; then I think that updating outside of DM was a mistake, but shouldn't be. You should be able to update the local working copy outside of DM and not be punished for it.)
• Story 2: I forget to notify my co-workers.
  • If they already have the design open (parallel design), DM should be checking the server every 5 minutes or so (only for the design that is open) to see if the timestamp or version # has changed. If it changes, automatically refresh the contents of the Incoming Changes folder, but more importantly display a little modal alert dialog that lets me know user X has recently committed N changes to the open model, and ask if I'd like to close and re-open (updating the working copy in the background), merge automatically, or work out the conflict manually. (I see an Incoming Changes Timer Interval under Tools | Preferences | Versioning | Subversion | Version Tools, but in our limited testing, we didn't see Incoming Changes ever update on my co-DBA's DM. So if DM is polling the Subversion server, I'm not seeing it.)
  • If they do not have the design open, when they go to open it, DM check's against Subversion and displays a similar dialog mentioned above, but this time just informing them that the design has been modified since they last had it open and that DM will now update the working copy and display the current design. They acknowledge the dialog and then DM does its thing. If they have a saved working copy that is out of date, then things get a little messier as they'll need to decide whether to auto merge, manually merge, or revert their changes and take the latest version.

Thanks for sticking with me this long. Between the error messages, confusing an antiquated advice on the web, oddities between checking out and updating using DM vs other SVN clients, not knowing how DM establishes or sees the relationship between a working copy and a repository, my co-workers being unable to commit, the design remaining stale after updating (finding out we have to close and re-open after updating), the slow clunky behavior when refreshing all and updating individual nodes from the Incoming Changes tree, it just got overwhelming and frustrating. Would like to see my current problem go away and the whole integration designed a little better. You're really onto something here. If Subversion integration worked flawlessly, you have an amazing tool that replaces about $8,000 worth of software (for one seat) from other companies that you somehow keep free for our use. I care very much about ironing out the kinks, which is why I've taken the time to write this tome.

Keep it up, great work!