The root page @home could not be found in space Query Variant Plugin.
Skip to end of metadata
Go to start of metadata

Plugin version : 1.0.0.1

Main Features

  1. Define SQL with Velocity template blocks and special SQL Comment markup to calculate various combination of SQL query.
  2. No limitation on datasources - as defined in context.xml of tomcat server.
  3. Injection of meta-tags into page where SQL plugin resides.

Supported Servers

  • MS SQL Server
  • PostgreSQL
  • MySQL
  • Oracle DB

A proper JDBC driver needs to be installed in confluence lib and eventually in jre directories. MSSQL server plugin requires additional .dll or .so files to be executed.

This plugin has been tested only with MSSQL Server however should work with others.

SQL Metalanguage

When we define SQL query that should be rendered in a table we can use SQL comments like /* comment */ .

In our plugin we introduced a block of SQL comment that can be toggled depending on parameter value, this looks like below:

/*(false){*/
Content of SQL query 1
/*}*/
/*(true){*/
Content of SQL query 2
/*}*/

This syntax can be used across all SQL languages.

Parameters in this example are keywords - true / false.

From the 2 above blocks if we save those in plugin and look how they will be rendered - the final result will be just

"Content of SQL query 2" - that is because of  /*(true){*/ directive which passes through everything between /*(true){*/ and /*}*/ . The opposite is with the 

/*(false){*/ directive which hides fragment of SQL query from being displayed and executed.

Why such notation ? - for at least one good reason - you can define sql query which uses parameter insted of true/false values. This parameter can come from e.g. BobSwift Run plugin parameter.

These parameters will drive form of the final SQL query that will be executed.

Apart from the above syntax you can use Apache Velocity processing language, prefferably inside comment blocks.

See how it works on a live form:

The above screen-cast shows how SQL Metalanguage is processed by SQL Query Variant Plugin.

We executed below SQL fragment and got above results:

/*(false){*/
/* #set ($p1 = "$join1") */
/*}*/
SELECT 
p.[POS_CODE],
p.[POS_ID]/*($p1){*/, 
n.NSC_NAME/*}*/
 FROM [dbo].[POS] p 
/*($p1){*/
inner join dbo.[NSC] n on p.NSC_ID = n.NSC_ID
/*}*/

The order of processing code:

  1. First Velocity template language
  2. SQL Metalanguage directives in like /*(false){*/ or /*(true){*/

Despite having such complex structure one of the goals while designing this meta notation was - keep the source SQL query executable. You can paste the above query into e.g. SQL Management Studio and still get some results (this code uses T-SQL).

In the above example the $join1 parameter comes from BobSwift Run plugin and is assigned to velocity parameter $p1, this one is further used to show or hide fragment with join and related fields in select.

$p1 can have values like true or false and leads to different SQL Queries in final result.

 

 

  • No labels

2 Comments

  1. Anonymous

    So that's the case? Quite a revatelion that is.
  2. The idea is simple, but still of some use.

    If you like I can share with you project code - if only I find it on my local drive.