You're on your way to the next level! Join the Kudos program to earn points and save your progress.
Level 1: Seed
25 / 150 points
1 badge earned
Challenges come and go, but your rewards stay with you. Do more to earn more!
What goes around comes around! Share the love by gifting kudos to your peers.
Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!
Join now to unlock these features and more
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
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:
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!