More about this Plugin
Autogenerates table's based on SQL queries executed against a Database.
Administrators
No special actions required.
Note that this plugin creates it's own help and test pages inside your wiki, for your benefit.
These pages cannot be deleted without disabeling the formatter first.
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
- {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 a database (Oracle, MsSql, MySql, SqLite).
- Make all sorts of visual changes to the table generated from the query.
Usage:
{ QTable conn= query= }
Where:
- conn - 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' .
- To control and modify the display of columns use the table tags found here.
All args which have a value that contains whitespaces, must be encapsulated in ' ', ex. 'select * from myView'.
Minimum
This will create a table with the default style of your chosen wiki theme.
Markup:
{ QTable conn=MyLink query='select * from myView'}
Result:
| ColName1 | ColName2 |
|---|
| DataCell1 | DataCell2 |
Tables and Styling ¶
The
Keeper Garrett Screwturn Formatters currently consists of 4 formatters which all uses the same tables and styling scheme, they are:
For a visual preview of the supplied styles visit my site:
http://keeper.endoftheinternet.org Markup Usage
What can you do?
- Add a heading to your table
- Add a footer to your table
- Show only specified columns
- Change column order
- Override column headers
- Use one of the 11 predefined styles
- Define your own style
- Use the default wiki theme style
- Use the default generic wiki theme style
Usage:
{SomeFormatter( ...Formatter_Specific_Args=XX... head=XXX foot=YYY cols=ZZZ colnames=XYZ style=ZXY}
Where:
- head - Heading of the table
- foot - Footer of the table
- cols - Name and order of the columns to show, must be seperated by ,. Ex. 'col1,col2,col3'
- colnames - Custom column names, must be seperated by ,. Ex. 'col1,col2,col3'
- style - Name of one of the predefined styles, if not specified the default wiki style is used.
All args which have a value that contains whitespaces, must be encapsulated in ' ', ex. 'My Heading'.
Add A Heading
Markup:
{SomeFormatter( ... head='My Heading'}
Result:
My Heading | Col1 | Col2 |
|---|
| DataCell1 | DataCell2 |
Add A Footer
Markup:
{SomeFormatter( ... foot='My Footer'}
Result:
| Col1 | Col2 |
|---|
| DataCell1 | DataCell2 |
Change Column Order and Display ¶
Markup:
{SomeFormatter( ... cols='Col1,Col3,Col2'}
Result:
| Col1 | Col3 | Col2 |
|---|
| DataCell1 | DataCell3 | DataCell2 |
Here a 4 column called Col4 is omitted since it is not mentioned.
Change Column Names ¶
Markup:
{SomeFormatter( ... colnames='X,Y,Z'}
Result:
| X | Y | Z |
|---|
| DataCell1 | DataCell2 | DataCell3 |
OR
Markup:
{SomeFormatter( ... cols='Col1,Col3,Col2' colnames='X,Y,Z'}
Result:
| X | Y | Z |
|---|
| DataCell1 | DataCell3 | DataCell2 |
Styling
For a visual preview of the supplied styles visit my site:
/Keeper.Garrett.Formatters/Tables/table-examples.html
Markup:
{SomeFormatter( ... style='generic'}
If the style argument is not supplied the default wiki table style is used.
There are 2 theme based styles and 11 predefined styles which are bundled with all the formatters (which uses tables).
Wiki Theme styles:
- No style specified - will use the current theme's default style
- generic - will use the current theme's generic style
- There's a small section in the TableStyle.css which removes the generic padding so that the generic style mathces the one used in the wiki. Uncomment the generic section if you do not like this and save the TableStyle.css.
Predefined table styles:
- hor-minimalist-a
- hor-minimalist-b
- ver-minimalist
- box-table-a
- box-table-b
- hor-zebra
- hor-zebra-a
- hor-zebra-b
- rounded-corner
- background-image
- gradient-style
All these styles are defined in the TableStyle.css file which is located in default file storage provider on the following path:/Keeper.Garrett.Formatters/Tables/TableStyle.css
So to create your own custom style simply add your CSS to this file.
Examples
Discuss
No Messages for this Page. Be the first!