Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,463,825
Community Members
 
Community Events
176
Community Groups

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

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

1 answer

1 accepted

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!

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PERMISSIONS LEVEL
Site Admin
TAGS

Atlassian Community Events