How do I search within the SQL of all PocketQuery queries?

[Jacob Boyko]

I have a fairly large number of queries and I would like to be able to search for keywords in the SQL statements.

For example, I'm editing a stored procedure in my database that I know is used in some queries, and I'd like to search for the procedure name so I can check dependencies.

Is there a way to do this? I looked in the database but this query wasn't useful:

SELECT * FROM plugindata where PLUGINKEY = 'de.scandio.confluence.plugins.pocketquery'

[Felix Grund (Scandio) · Accepted solution]

Hi Jacob,

I prepared a little piece of JavaScript code that retrieves the query objects and their statements in Confluence Admin. When you are on that screen, you can open a browser console (e.g. DevTools in Chrome) and execute the script. It should then print all query objects (which you can expand and see all data) and a string that contains all statements separated.

(function($) {

	var allQueryObjects = [];
	var statementsString = '';

	$('#queries-list li').each(function() {
		var $this = $(this);
		var queryName = $this.data('entityname');
		var queryObj = PocketQuery.getEntity('query', queryName);
		allQueryObjects.push(queryObj);
		statementsString += '\n===\n' + queryObj.statement;
	});

	console.log('All query objects:');
	console.log(allQueryObjects);

	console.log('All query statements');
	console.log(statementsString);

}(jQuery))

Let me know if you need further help!

Regards, Felix

[Jacob Boyko]

Hi Felix,

Wow, that will work nicely, thanks for such a quick response!

Cheers, Jacob

[Jacob Boyko]

This is just related info - it does the same thing for the templates as the above does for the queries:

(function($) {
    var PQEntityObjects = [];
    var outputString = '';
 
    $('#templates-list li').each(function() {
        var $this = $(this);
        var entityname= $this.data('entityname');
        var entityObj = PocketQuery.getEntity('template', entityname);
        PQEntityObjects.push(entityObj);
        outputString += '\n\n=== '+ entityname +' ===\n' + entityObj.content;
    });
 
    console.log('All template objects:');
    console.log(PQEntityObjects);
 
    console.log('All template content:');
    console.log(outputString);
 
}(jQuery))