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