Access the Confluence database from a macro

Hi,

I’m trying to write a simple macro to output some data from the Confluence DB. I’m wondering what is the best way to gain JDBC access to the configured Confluence DB from within a plugin?

thanks.

Hello Johnny,

can you be more specific on what exactly you want to store/retrieve from Confluence database? I suppose you mean custom tables, then Active Objects are the easiest way: https://developer.atlassian.com/server/framework/atlassian-sdk/getting-started-with-active-objects/. However, there are other possibilities of data storage, e.g., Bandana: https://developer.atlassian.com/server/confluence/what-is-bandana-one-form-of-confluence-persistence/, page properties, etc., which are more lightweight (suitable for smaller amounts of data).
If you want to access Confluence data (not from custom plugins), you should use its managers, definitely do not access it directly.

Hope it helps.
Markéta

1 Like

I do want to access the database directly in order to run some SQL that provides info that I don’t believe I can get from the managers. Specifically, I’d like a macro that shows the data described in this kb article. https://confluence.atlassian.com/confkb/how-to-list-which-spaces-a-group-can-access-720636535.html.
I understand that it is not a recommended approach and maybe I can do it with the managers.

Hello Johnny,

If I understand your use case well, I would say the contrary; it can be definitely performed using managers. For sure, specific implementation depends on your instance size, etc. but please, try the following.

As an input you either have a single group or you can obtain all groups using

final Pager<Group> groups = groupManager.getGroups();

… and then iterate over these groups and use group.getName(). You can obtain all permissions of the group using spacePermissionManager.getAllPermissionsForGroup(groupName), which returns a list of SpacePermission, where you can find all relevant information (i.e. space ID, permission type…).

And then it depends on what you want to display. This obtains all permissions so you can filter whatever you need. :slight_smile:

The page you provided is also valid way but when we have an access to Java API, why not use it…

Hope it helps.
Markéta

1 Like

thank you for taking the time to respond. I will investigate this option more thoroughly.

thanks,

John

Dear John,

in case you need a starting point you may take pieces from my code below. I use it to get all labels of a set of pages. Iterating through all pages was too slow - therefore I’m accessing the DB directly.

I hope that helps.

Kind regards

Andreas

	// collect all labels of a topic with count and watch information for the given user
	private List<JSONObject> getLabelsWithInfoViaQuery(Long topicPageId, String userKey) {
		
		// get all questions of the topic
		ArrayList<Long> questionIds = simpleQAHelper.getQuestionsInTopic(topicPageId);
		
		// prepare the label counter
		KeyCounter labelCounter = new KeyCounter();
		
		// query the Confluence table
		final TransactionalExecutor transactionalExecutor = execFactory.createExecutor().readOnly().newTransaction();
		transactionalExecutor.execute(new ConnectionCallback<Boolean>()
		 {
		     public Boolean execute(final Connection connection)
		     {
		    	 Boolean success = false;
		    	 int subsetStart = 0;
		    	 
		         try
		         {
		        	 while(subsetStart < questionIds.size()) {
		        	
		        		 // build subset
		        		 final int subsetEnd = subsetStart + IN_QUERY_SIZE > questionIds.size() ? questionIds.size() : subsetStart + IN_QUERY_SIZE;
		        		 final List<Long> subset = questionIds.subList(subsetStart, subsetEnd);
		        		 
		        		 // prepare query
			             //final String schemaPrefix = transactionalExecutor.getSchemaName().map(s -> s + '.').getOrElse("");
		        		 // according to ConfluenceConnectionProvider no schemaPrefix is required currently
		        		 final String schemaPrefix = "";
			             final String inPlaceholder = StringUtils.repeat("? ,", subset.size() - 1).concat("?");
			             final PreparedStatement preparedStatement = connection.prepareStatement("select LABELID from " + schemaPrefix + "CONTENT_LABEL where CONTENTID in (" + inPlaceholder + ")");
			             for(int n = 0; n < subset.size(); n++) {
			            	 preparedStatement.setLong(n + 1, subset.get(n));
			             }
	
			             // execute query
			             final ResultSet resultSet = preparedStatement.executeQuery();
	
			             // count the label Ids
			             while(resultSet.next()) {
			            	 labelCounter.count(Long.toString(resultSet.getLong("LABELID")));
			             }
			             
			             // next subset
			             subsetStart += IN_QUERY_SIZE;
		        	 }
		             
		             success = true;
		         }
		         catch (SQLException e) {
		        	 log.error(e.toString());
		        	 log.debug(simpleQAHelper.getStackTrace(e));
		         }
	    	 
		    	 return success; 
		     }
		 });
		
		// boost watched labels
		String watchesAsString = simpleQAWatchMan.getAllWatchesInTopicForUserAsString(Long.toString(topicPageId), userKey);
		for(Entry<String, Integer> entry : labelCounter.getMap().entrySet()) {
			if(watchesAsString.contains( labelMan.getLabel(Long.parseLong(entry.getKey())).getName())) {
				labelCounter.increaseCounter(entry.getKey(), 1000);
			}
		}
		
		// build the result
		ArrayList<JSONObject> labelsWithInfo = new ArrayList<JSONObject>();
		for(Entry<String, Integer> entry : labelCounter.getSortedMap().entrySet()) {
			String labelName = labelMan.getLabel(Long.parseLong(entry.getKey())).getName();
			if(!labelName.contains("favourite")) {
				JSONObject labelWithInfo = new JSONObject();
				labelWithInfo.put("text", labelName);
				labelWithInfo.put("count", entry.getValue());
				labelWithInfo.put("isWatching", simpleQAWatchMan.isWatchingLabel(topicPageId, userKey, labelName));
				labelsWithInfo.add(labelWithInfo);
			}
		}
		
		return labelsWithInfo;
	}
1 Like