SqlGridPlugin

Description

Navigate SQL data with built-in paging, sorting, and filtering. Also can be used to build full CRUD (CReate, Update, Delete) capabilities.

Note - currently only supports mysql. SqlMysqlConnector.pm includes hints on how Oracle and Sybase could be supported - please contact the author if you want to contribute code.

Prerequisite Plugins

SqlGridPlugin is built on top of other plugins. The below plugins must be installed and configured (on the configure page) by the Foswiki administrator as a pre-requisite to using SqlGridPlugin. SqlGridPlugin itself does not have any settings that need to be configured by the Foswiki administrator.

JQGridPlugin

The Foswiki wrapper for jqGrid.

See the documentation for JQGridPlugin to see all the options available to configure the grid view. All unprocessed options for %SQLGRID% are passed along to %GRID%.

SqlPlugin

SqlPlugin provides one place to configure perl DBI database connections, and to configure access control.

JQueryUIDialog

JQueryUIDialog is used to create popup windows for CRUD actions. This plugin doesn't require any configuration.

Example

See SqlGridPluginExample.

Quick Starts

Quick Start SELECT Only

%SQLGRID{
    connector="mysql"
    dbconn="a connector configured in SqlPlugin"
    idcol="the column to treat as the primary key"
    sql="SELECT blah blah FROM blah blah WHERE blah = blah"
}%

• connector - A JQGridPlugin connector, which must be configured in JQGridPlugin's ExternalConnectors section on the configure web page. For example, here is how to configure the mysql connector:

{
  'mysql' => 'Foswiki::Plugins::SqlGridPlugin::SqlMysqlConnector'
}

• dbconn - An SqlPlugin connection, which must be configured in SqlPlugins's Databases section on the configure web page.
• idcol - Each SQL query must have a column that uniquely identifies the row - this is required for the grid to work properly.
• sql - This SQL statement is parsed so that the back-end code can be able to dynamically generate queries to sort on any desired column, etc.

Quick Start Simple CRUD

%SQLGRID{
    (Attributes from Quick Start SELECT Only)
    templates="System.SqlGridPluginSimpleTable"
    table_popupactionarg="name_of_table_for_CRUD"
    add_popup_sqlgridbutton="URL_for_add_popup"
    edit_popup_sqlgridbutton="URL_for_edit_popup"
}%

• templates - SqlGridPluginSimpleTable contains default settings that simplify creating a CRUD interface operating on one table. See The Template Attribute
• table_popupactionarg - The default popup handlers require this parameter to be set to the name of the table targeted for INSERT / UPDATE / DELETE SQL statements. See Popup Actions
• [button]_popup_sqlgridbutton - This URL is retrieved when the [button] button is clicked. See Creating Popups

The Template Attribute

templates="Web.FirstAttributeDefaultsTopic, Web2.SecondOne"
Each topic in the list is checked to see if it contains an %SQLGRID% macro. The values in the %SQLGRID% macros are evaluated (if they contain Topic Markup Language), and then used as default values for the %SQLGRID% macro. If an attribute is present in more than one topic, then the value in the last topic takes precedence. If a template has a template, then it is expanded.

Adding Buttons to the Grid

Three things are required to add buttons to the grid:

1. Specifying the button caption, icon etc. in the %SQLGRID% macro.
2. Creating the popup that is displayed when the user clicks the button.
3. Hooking up to simple CRUD REST handler. More complex usage will require writing a custom REST handler (e.g. if INSERTs must be done on two tables).

See SqlGridPluginSimpleTable for an example. The delete button is fully working 'out of the box', but the edit and add buttons need some additional customization (Step 2).

Button Specifications

sqlgridbuttons="button1,button2,button3"
A list of buttons to be placed under the grid, in the order that they should appear.

• [button]_caption_sqlgridbutton="Text on button"
  
• [button]_hover_sqlgridbutton="Displayed when hovering mouse over button"
  
• [button]_icon_sqlgridbutton="a JQuery-UI icon name"
  
• [button]_popup_sqlgridbutton="URL for the popup"
  
• [button]_popupaction_sqlgridbutton="URL for the popup action"
  
• [button]_needrow_sqlgridbutton="true if the button requires that the user has clicked on a row first"
  

Attributes for a button named [button]. (The VarMAKETEXT Macro is used for Foswiki's built-in language localization)

Creating Popups

The [button]_popupaction_sqlgridbutton attribute contains a URL that is loaded when [button] is clicked. Assuming that this URL is generated from a foswiki topic, it will likely contain the following two parameters:

• skin=text This returns the bare content, ignoring the skin path (See SkinTemplates).
• section=sectionName Specifies the section to use - so that the referred topic can also include other content (documentation, other popups, etc.)

The Popup URL is passed the following parameters:

• dbconn - passthru from the %SQLGRID%
• idcol - passthru from the %SQLGRID%
• _selected_row - the value of idcol for the currently selected row
• col_[column] - the value of column

Popup Actions

The popup action URL is passed the following parameters:

• dbconn - passthru from the %SQLGRID%
• idcol - passthru from the %SQLGRID%
• All input elements defined on an HTML form (e.g. <input type="text">)

In addition, all %SQLGRID% parameters of the form     [key]_popupactionarg=value
Are passed thru to the popup as key=value.

SQL Parsing

If you provide an sql parameter, then the plugin will parse it and inject the following parameters. If you have an SQL expression that's too complicated for the parser, then instead of providing an sql parameter, you can set the following parameters:

• fromwhere_connectorparam - the part of the SQL query after 'from'.
• columns - a comma-separated list of columns.
• col_${col}_expr_connectorparam - for each column, the sql expression for that column.

The parser assumes that the select list expressions contain balanced parentheses. So this would not work:

select '(' || foo as bar from table1

All Attributes to %SQLGRID% Macro

Select Only

• connector - A JQGridPlugin connector, which must be configured in JQGridPlugin's ExternalConnectors section on the configure web page. For example, here is how to configure the mysql connector:

{
  'mysql' => 'Foswiki::Plugins::SqlGridPlugin::SqlMysqlConnector'
}

• dbconn - An SqlPlugin connection, which must be configured in SqlPlugins's Databases section on the configure web page.
• idcol - Each SQL query must have a column that uniquely identifies the row - this is required for the grid to work properly.
• sql - This SQL statement is parsed so that the back-end code can be able to dynamically generate queries to sort on any desired column, etc.

Simple CRUD

• templates - SqlGridPluginSimpleTable contains default settings that simplify creating a CRUD interface operating on one table. See The Template Attribute
• table_popupactionarg - The default popup handlers require this parameter to be set to the name of the table targeted for INSERT / UPDATE / DELETE SQL statements. See Popup Actions
• [button]_popup_sqlgridbutton - This URL is retrieved when the [button] button is clicked. See Creating Popups

Buttons

• [button]_caption_sqlgridbutton="Text on button"
  
• [button]_hover_sqlgridbutton="Displayed when hovering mouse over button"
  
• [button]_icon_sqlgridbutton="a JQuery-UI icon name"
  
• [button]_popup_sqlgridbutton="URL for the popup"
  
• [button]_popupaction_sqlgridbutton="URL for the popup action"
  
• [button]_needrow_sqlgridbutton="true if the button requires that the user has clicked on a row first"
  

Other

• debugging="on" - Prints URLs in a div below the grid, helpful for debugging.

All additional parameters are passed verbatim through to JQGridPlugin.

Installation Instructions

You do not need to install anything in the browser to use this extension. The following instructions are for the administrator who installs the extension on the server.

Open configure, and open the "Extensions" section. Use "Find More Extensions" to get a list of available extensions. Select "Install".

If you have any problems, or if the extension isn't available in configure, then you can still install manually from the command-line. See http://foswiki.org/Support/ManuallyInstallingExtensions for more help.

Info

Author(s):	Kip Lubliner
Copyright:	© 2012 Kip Lubliner
License:	GPL (Gnu General Public License)
Release:	0.0.1
Version:	15017 (2012-06-17)
Change History:
Dependencies:	Name Version Description Foswiki::Plugins::JQGridPlugin >=2.03 Required Foswiki::Plugins::SqlPlugin >=1.02 Required HOP::Lexer >=0.032 Required
Home page:	http://foswiki.org/bin/view/Extensions/SqlGridPlugin
Support:	http://foswiki.org/bin/view/Support/SqlGridPlugin