Welcome Guest, you are in: Login

Search:

download
Version2.0.1.0
Rating  
DeveloperChristian Hollerup Mikkelsen
Updated2016-01-09
Homepagehttp://master-garrett.blogspot.com/
Works withScrewTurn Wiki v5.0.x
LicenseGPLv2
Supported languages-
Visual-editorno

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


Table of Contents [Hide/Show]


    Administrators
       System Requirements
       Warning
       Guidelines
       Security
          Oracle keywords
          MsSql keywords
          MySql keywords
          SqLite keywords
       Setup
    Markup Usage
       Minimum
    Tables and Styling
       Markup Usage
       Add A Heading
       Add A Footer
       Change Column Order and Display
       Change Column Names
       Styling
          Examples



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.


All words WILL be scanned for ALL types of DB's.


Oracle keywords

  • ADD
  • ALTER
  • AUDIT
  • CALL
  • COMMENT
  • COMPRESS
  • CREATE
  • DELETE
  • DROP
  • GRANT
  • INCREMENT
  • INDEX
  • INSERT
  • INTO
  • LOCK
  • MODIFY
  • PRIVILEGES
  • RENAME
  • REVOKE
  • SET
  • START
  • TRIGGER
  • TRUNCATE
  • UPDATE



MsSql keywords

  • ADD
  • ALTER
  • CASCADE
  • COMMIT
  • COMPUTE
  • CREATE
  • DELETE
  • DENY
  • DROP
  • DUMP
  • EXEC
  • EXECUTE
  • GRANT
  • HOLDLOCK
  • IDENTITY_INSERT
  • INSERT
  • INTO
  • KILL
  • PROC
  • PROCEDURE
  • RECONFIGURE
  • REFERENCES
  • REPLICATION
  • RESTORE
  • RESTRICT
  • REVOKE
  • ROLLBACK
  • RULE
  • SAVE
  • SET
  • SETUSER
  • TRANSACTION
  • TRIGGER
  • TRUNCATE
  • USE



MySql keywords

  • ADD
  • ALTER
  • CALL
  • CASCADE
  • CHANGE
  • CREATE
  • DEC
  • DECLARE
  • DECLARE
  • DELETE
  • DROP
  • EXIT
  • FETCH
  • FORCE
  • GRANT
  • INSERT
  • INTO
  • KILL
  • LOCK
  • LOOP
  • PROCEDURE
  • PURGE
  • REFERENCES
  • RELEASE
  • RENAME
  • REPLACE
  • REQUIRE
  • RESTRICT
  • REVOKE
  • SET
  • TRIGGER
  • UNLOCK
  • UPDATE
  • UPGRADE
  • WRITE



SqLite keywords

  • ABORT
  • ACTION
  • ADD
  • ALTER
  • AUTOINCREMENT
  • CASCADE
  • COMMIT
  • CREATE
  • DELETE
  • DROP
  • END
  • INSERT
  • INSTEAD
  • INTO
  • REFERENCES
  • RELEASE
  • RENAME
  • REPLACE
  • RESTRICT
  • ROLLBACK
  • SAVEPOINT
  • SET
  • TRANSACTION
  • TRIGGER
  • UPDATE
  • VACUUM



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 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:


 
ColName1ColName2  
DataCell1DataCell2




Tables and Styling

The Keeper Garrett Screwturn Formatters currently consists of 4 formatters which all uses the same tables and styling scheme, they are:


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
Col1Col2  
DataCell1DataCell2




Add A Footer


Markup:


{SomeFormatter( ... foot='My Footer'}


Result:


Col1Col2  
DataCell1DataCell2




Change Column Order and Display


Markup:


{SomeFormatter( ... cols='Col1,Col3,Col2'}


Result:


Col1Col3Col2
DataCell1DataCell3DataCell2


Here a 4 column called Col4 is omitted since it is not mentioned.



Change Column Names


Markup:


{SomeFormatter( ... colnames='X,Y,Z'}


Result:


XYZ
DataCell1DataCell2DataCell3


OR


Markup:


{SomeFormatter( ... cols='Col1,Col3,Col2' colnames='X,Y,Z'}


Result:


XYZ
DataCell1DataCell3DataCell2




Styling



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:
  1. No style specified - will use the current theme's default style
  2. generic - will use the current theme's generic style
    1. 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:
  1. hor-minimalist-a
  2. hor-minimalist-b
  3. ver-minimalist
  4. box-table-a
  5. box-table-b
  6. hor-zebra
  7. hor-zebra-a
  8. hor-zebra-b
  9. rounded-corner
  10. background-image
  11. 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

Image

Image

Image



 
  Name Size
- Garrett.png 27.79 KB
- Style1.png 19.58 KB
- Style2.png 30.79 KB
- Style3.png 28.36 KB
- TableStyle.css 14.43 KB

Side Projects

  • RESX Synchronizer allows to synchronize multi-language .resx files (used for the development of ScrewTurn Wiki).
  • Pixel Picker enables to pick the color of pixels on your screen — very handy for day-to-day graphics-related activities.

About