Friday, August 22, 2014

Sharing SQLDeveloper SQL history across several machines with Dropbox

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

A quick tip for keeping a single shared SQL history repository among different installations of SQLDeveloper, for the same user, on distinct computers synchronized with Dropbox.
 
This seems to be working just fine on Mac OS X and SQLDeveloper 4.0.2 however it's a totally unsupported hack, which means ** use at your own risk **.

I ignore what will happen when I'll upgrade to the next release of SQLDeveloper but I'll find out sooner or later.
(Updated September 24: No problems whatsoever after upgrading to 4.0.3, the shared SQL History folder is still there and working.)
From what I've seen, SQL Developer makes a copy of the history folder when it upgrades an existing installation, may be it crashes or may be I'll have to repeat the whole procedure, I don't know, for the moment I really appreciate the fact that I can pick up a work on a different location exactly from where I left off without having to worry about making copies of certain queries manually.

As your SQL history is going to be bigger than before, it makes sense to increase the maximum number of statements that you want to keep, to do so go to Preferences/Database/Utilities/Worksheet pane and increase the SQL history limit and remember to do so on all the machines, setting the same value *before* proceding with the next step, otherwise the installation with the shortest history will wipe out the files added by the others!


From the shell prompt, assuming you are in your home folder and SQLDeveloper is NOT running:
cd .sqldeveloper
cp -R SqlHistory ~/Dropbox/.SqlHistory
mv SqlHistory SqlHistory.old
ln -sf ~/Dropbox/.SqlHistory/ SqlHistory

SqlHistory.old is your safety net, in case something goes wrong.

Then, on the second machine you may either decide to get rid of the current history and use the history shared from the first machine or you may try to merge them.

In the former case, just execute:

cd .sqldeveloper
mv SqlHistory SqlHistory.old
ln -sf ~/Dropbox/.SqlHistory/ SqlHistory

In the latter case, in addition to the three steps above, execute also:

cp -R SqlHistory.old SqlHistory.tmp 
mv -n SqlHistory.tmp/* ~/Dropbox/.SqlHistory

Any duplicate filename (albeit with different SQL content) will remain in the SqlHistory.tmp folder, so you have a chance to decide what to do with it. If the SqlHistory.tmp is empty at the end of the operation, then get rid of it.

SQLDeveloper keeps the log as a bunch of XML files whose names are generated by prepending a pseudo random number (I ignore the details) to the fixed string "history.xml".
As I don't know if this number can be unique across different computers in virtue of some clever algorithm, I guess that occasionally there could be a conflict with an existing one, but when I merged the histories of my SQL Developer installations, there were no conflicts at all (may be the magic number is based on the timestamp, which explains why there were no conflicts as I haven't been given the gift of ubiquity...)

If there is a third machine, repeat the same steps as for the second one and so on.


Probably it could make sense to do the same with the library of connections, but this is an exercise I'll keep for another occasion.

I guess the same procedure should work also on most Linux distros as I don't see any difference under the hood.

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles