How to access Subversion from Oracle PL/SQL?

Question

For a governmental agency, we build a release management system developped in PHP and Oracle. The data for this application is stored in database tables and is processed with PL/SQL packages and procedures.

The release management process is extensively based on metadata coming from Subversion repositories. We access the repositories from PL/SQL through the internal Oracle JVM to execute svn commands on the unix server on which resides the Oracle instances. The results from svn commands are received in XML and parsed before beeing processed by PL/SQL. Accessing Subversion this way is not very performant for frequent repeated use.

Currently, what we do is storing the Subversion metadata in database tables at each commit in the Subversion repositories (via Subversion hooks). We extract the log information for each Subversion transaction and keep it in some oracle tables. We are then able to obtain Subversion metadata with normal SQL queries.

Is there better ways to access Subversion from PL/SQL ?

Answer 1

If your using Oracle's Java JVM, you could try to use SVNKit to communicate with the SVN server nativly from Java, instead of shelling out to the operating system to execute commands.

Answer 2

I think the basic flow makes sense. I would recommend doing experiments to see where exactly the performance bottlenecks are and then take if from there. For example, is it crossing from PL/SQL to Oracle JVM? Is it JVM shelling out to execute the svn command? Is it the svn round trip? Is it the parsing of the XML?

Let's say, for example, it's the svn round trip. Maybe you could have a process on the oracle machine that caches answers from the svn server so that at times the round trip could be avoided? Maybe the svn round trip could be async?

But, like I said, you need to know where the bottleneck is.