Welcome Guest, you are in: Login

Water Mission Country Programs Wiki

RSS RSS

Navigation




Search the wiki
»

PoweredBy

Query Table Help

RSS
Modified on 2010/05/11 16:27 by QueryTableFormatter Categorized as Uncategorized

Query Table's

Autogenerates table's based on SQL queries executed against a Database.

Table of Contents [Hide/Show]


Query Table's
    Administrators
       System Requirements
       Warning
       Guidelines
       Security
       Setup
    Markup Usage
       Minimum
       Styling


Administrators

This section is targeted at wiki administrators only.
You can verify that the formatter is working by opening this test page (be patient, it may be slow).

System Requirements

Database support:
One or more of the following drivers MUST be installed in the GAC of the server running ScrewTurn:

Warning

The use of the SQL Query Table plugin is a potential security risks. It is the administrators task to ensure that Database links set up, are secure with regards to the following:
  • The db user/scheme/catalog used to access the db, should ONLY have READ access to tables/views.
  • The db user/scheme/catalog used to access the db, should ONLY have access to exactly the tables/views which are to be queried.
  • The db user/scheme/catalog, should preferbly ONLY contain views.



Guidelines

  • Always create views, insted of writting the where clauses in the query.
  • Use only select * from xx.MyTableView if possible. More advanced clauses are messy to read in the wiki markup and are NOT gaurateed to work when starting to use special chars. Use Views instead.



Security

The plugin has builtin simple security check's for potential dangerous sql keywords.
Currently the plugin contains 125 forbidden keywords.
Go here to see the forbidden keywords.



Setup

In the formatters section of the administation view, click the select button.
Follow the guidelines in the help section or read on below.
To create a DB Link use the following format:
{LinkKey=Type,ConnectionString}
Where:
  • LinkKey - Your chose name, ex test
  • Type - One of the supported DB's (Oracle,MsSql,MySql,SqLite)
  • ConnectionString - Standard connection string



Examples:
  • {MyLink1=Oracle,User Id=MyUser;Password=MyPass;Data Source=TnsName;}
  • {MyLink2=MsSql,Data Source=MyServer\SQLEXPRESS;Initial Catalog=MyCatalog;User ID=MyUser;Password=MyPass;}
  • {MyLink3=MySql,Database=MyDB;Data Source=MyServer;User Id=MyUser;Password=MyPass;}
  • {MyLink4=SqLite,Data Source=MyDB.sqlite;Version=3;}



Markup Usage

What can you do?
  • Query the database
  • Add a heading to your table
  • Show only specified columns
  • Change column order
  • Override column headers and make them more user friendly
  • Change table style
  • Change columns header style
  • Change row style
  • Use one of 3 predefined styles bw,bg,gb
  • Combine several of the above options



Usage:

{QTable(Link,Query,TblHeading,Columns,Headers,TblStyle,HeadStyle,RowStyle)}

Where:
  • Required:
    • Link - Must match a link created by the admin, ex. MyLink1, ask your admin for further information.
    • Query - Sql query, must be encapsulated in ' ' ex. 'select * from myView' .
  • Optional:
    • TblHeading - Heading of the table, must be encapsulated in ' ' ex. 'My Heading'
    • Columns - Columns and column order starting at 0, must be encapsulated in ' ' ex. 1,2,3 or 1,0,3.
    • Headers - Columnheaders must match column order, must be encapsulated in ' ' ex. Head1,Head,Head3 or Head1,Head0,Head3.
    • TblStyle - Style format, must be encapsulated in ' ' ex. 'align="center" style="color: #000000;"' .
    • HeadFStyle - Style format, must be encapsulated in ' ' ex. 'align="center" style="color: #000000;"' .
    • RowStyle - Style format, must be encapsulated in ' ' ex. 'align="center" style="color: #000000;"' .

  • All "," must always be included in the tag.



Minimum

This will create a table with the default style of your chosen wiki theme.
Markup:

{QTable(Link,Query,,,,,,) }

Result:

ColName1ColName2
DataCell1DataCell2



Styling

Default style
Depends on your chosen theme.
Markup:

{QTable(Link,Query,,,,,,) }

Result:

ColName1ColName2
DataCell1DataCell2



Predefined style: Black and White
Markup:

{QTable(Link,Query,,,,'bw','bw','bw') }

Result:

ColName1ColName2
DataCell1DataCell2



Predefined style: Black and Grey
Markup:

{QTable(Link,Query,,,,'bg','bg','bg') }

Result:

ColName1ColName2
DataCell1DataCell2



Predefined style: Green and Black
Markup:

{QTable(Link,Query,,,,'gb','gb','gb') }

Result:

ColName1ColName2
DataCell1DataCell2



Custom style:
Markup:

{QTable(Link,Query,,,,'cellspacing="10" style="background-color: #88CC33; color: #000000;"','style="color: #00AAAA;"','style="color: #BBBB00;"') }

Result:

ColName1ColName2
DataCell1DataCell2



Custom heading + headers:
Markup:

{QTable(Link,Query,'My heading','1,2','Head1,Head2',,,) }

Result:

My heading
Head1Head2
DataCell1DataCell2



Custom heading + headers + order:
Markup:

{QTable(Link,Query,'My heading','2,1','Head2,Head1',,,) }

Result:

My heading
Head2Head1
DataCell2DataCell1

ScrewTurn Wiki version 3.0.2.509. Some of the icons created by FamFamFam.


Disclaimer