How to query bitbucket's database using SQL from a plugin

[Manuel Ramirez-Evrard]

We are developing a plugin for bitbucket that creates it's own tables in bitbucket's database. It is done using Atlassian's active objects library. However, this library has many limitations.

For example, we would like to join multiple tables and get data from all of them in a single SQL request to assure the coherence between the data received from each table. The database can be accessed from multiple threads so we want to ensure that the data retrieved from the first table and the data retrieved from the second table come from the same version of the database. Therefore, we would like to retrieve all the data needed in a single request.

Here is what we have tried so far :

1- Using the active objects library's find function and the java.ao.Query object we can send sql queries to the database. However, when performing a join in the java query, the active object's find function automatically overrides the select parameter to only choose the value of a single table. We haven't found a way to retrieve data from multiple tables using active objects.

2- Importing that pocketknife querydsl library to perform more powerful SQL queries. However, when injecting that DatabaseAccessor object from the application, it gives an UnsupportedTransactionalExecutorFactory and when running a query an exception is thrown with the message indicating that SAL's TransactionalExecutor is not supported with bitbucket.

3- Using native QueryDsl to access the database directly. However, for this to work, it requires a way for us to access the database's connection parameters (URL, username, password). Although we can hardcode those for testing purposes, we need a way to retrieve that information from bitbucket through the plugin, but we aren't sure if that's at all possible.

We are not sure which of the 3 approaches is the best one, or if any of them can actually work for what we are trying to do. Is there any way to actually run SQL queries on our own tables in bitbucket's database ?

Thank you for your help

[Christian Ott _Mibex Software_ · Accepted solution]

Hi,

We reached similar pain points with AO very quickly and decided to go with the pocketknife QueryDSL wrapper. Be advised that by using raw SQL this way, edge cases of different DB systems and versions have tendencies to creep up  :D 

 Here some points that made it work for us:

• Using SpringScanner 2, we disabled the scanning of some classes to be able to provide our own DatabaseAccessor, for us it looked like:
  https://gist.github.com/tux19/d698e0cccc19c228338b420bfdd42b14
  
• Most layers of Pocketknife are straight forward to replace, the core of it is the HostConnectionAccessor. In Scala we did it this way: 
  https://gist.github.com/tux19/4ea019f7ef333f0036ef3a8cf4b6019e
  
• All in all we implemented the following classes:
  • DatabaseMigrationListener to disable the plugin when a DB migration succeeds to completely clean all caches
  • Event listener to clear pocketknife caches  with PKQCacheClearer
  • DatabaseAccessor that uses our HostConnectionAccessor from above (almost same implementation as pocketknife but using another class)
  • ProductSchemaProvider that gets the schema from our HostConnectionAccessor

After the tedious setup, the usage is easier. I suggest to use AO for the schema management of the tables, and use QueryDSL for everything else. 

Hopefully that gets you going!