• View
• Edit
• History
• Attach
• Print
• Backlinks

WikiShDb

Questions answered by this recipe

• I'm finding WikiSh so useful that my applications are growing and I'm outgrowing flat-file-based storage solutions. How can I make use of a relational database?
• I need to set up web-based forms that feed into a database. How can I do that with pmwiki?
• I need to display information from a database in my wiki -- how can I access it and format it?

Description

Allow database access via Markup Expressions.

Installation

1. WikiShDb is dependent on WikiSh. Thus first you must install WikiSh.
2. Download WikiShDb.phpΔ and place it in your cookbook directory.
3. Place the usual line in your config.php or farmconfig.php (placing this line after your WikiSh installation lines):

include_once("$FarmD/cookbook/WikiShDb.php");

4. WikiShDb by default has no authorization/privileges to do anything on any database. To use a database you must give the page containing the SQL script "SQL" authorizations via SecLayer. Normally this would be done by including a line such as this in your SiteAdmin.WikiShAuth page (this assumes that the SQL code is on a page named Test.SQL and your Markup Expressions are in a page named Test.ScriptPage):

(:if name Test.ScriptPage:)
Test.SQL: read, forceread, SQL
(:ifend:)

5. Basically for security configuration you want to make sure of the following:
   1. The page containing SQL code ("the SQL page") has "SQL" authorization
   2. The page containing the markup expressions ("the script page") has "read" authorization on the page containing the SQL code (this is not necessary if your script page and your SQL page are one and the same and all your SQL statements are declared "inline" via use of the --sql:"SQL STATEMENT" option)
   3. Normally both the SQL page and the script page would have password protected edit (and probably source) capabilities via normal pmwiki authorization
   4. If the SQL page does not have read authorization via pmwiki then you would need to give WikiSh "forceread" privileges on that page via SecLayer
6. For database configuration you can set things up on the page containing the SQL code (if you are comfortable putting your database username & password in plaintext on that page -- to do this you must also explicitly enable $wshdbEnableDBConfigOnPage in your config.php) or you can set up a $WikiShDb variable within your config.php:
   1. config.php - an example using MySQL:

$WikiShDb['username'] = 'sam';
$WikiShDb['password'] = 'samssecret';
$WikiShDb['database'] = 'foodb';
$WikiShDb['engine'] = 'mysql';
$WikiShDb['dbhost'] = 'localhost';

2. config.php - an example using SQLite:

$WikiShDb['database'] = 'my/path/foo.sqlite';
$WikiShDb['engine'] = 'sqlite';

3. A MySQL example configured on the page Test.SQL (you can use a different section besides #defaults if you change $WikiShDb['default_section']) (Note that $wshdbEnableDBConfigOnPage must be set to true in config.php before you can configure your database on a wiki page like this.):

[[#defaults]]
username=sam
password=samssecret
database=foodb
engine=mysql
dbhost=localhost
[[#defaultsend]]

4. An SQLite example configured on the page Test.SQL (you can use a different section besides #defaults if you change $WikiShDb['default_section']) (Note that $wshdbEnableDBConfigOnPage must be set to true in config.php before you can configure your database on a wiki page like this.):

[[#defaults]]
database=my/path/to/foodb.sqlite
engine=sqlite
[[#defaultsend]]

Note that to do this (#3 or #4 above) you would need to have this in your config.php or farmconfig.php or etc.:

$wshdbEnableDBConfigOnPage = 1;

7. Configuration of other options. (See options for more details.)
8. Rather than setting the engine, database, etc. individually and letting WikiShDb construct the DSN to open the database you can also manually set up the DSN string for accessing the database by setting the 'dsn' property, either within $WikiShDb or within the #defaults section of the SQL page (assuming $wshdbEnableDBConfigOnPage is set):

dsn=mysql:dbname=foodb;host=localhost

9. The following configuration options are unavailable on a page unless you explicitly set $wshdbEnableDBConfigOnPage within your config.php/farmconfig.php/etc. (They will be silently ignored.) Be aware that setting $wshdbEnableDBConfigOnPage and then configuring your database access on a page has significant security ramifications, particularly if that page is not locked down for source/edit/etc access!

    'username'=>''
    'password'=>''
    'database'=>''
    'dbhost'=>''
    'engine'=>''
    'dsn'=>''
    'dbopts'=>''

Usage

It will be of great help to you if you already have a fairly good understanding of how WikiSh handles input and output before jumping right into WikiShDb.

There are several markup expressions within WikiShDb, but all are simply synonyms of 2 commands:

• {(SQLselect ...)}
• {(SQLexec ...)} (synonyms: SQLinsert, SQLupdate, SQLdelete)

The first (SQLselect) should be used for SQL statements reading the database and producing output (usually SELECT statements). The second (SQLexec or synonyms) should be used for SQL statements writing to the database which do not produce output.

SQL statements can be defined with parameters either positionally:

[[#select_by_fname]]
SELECT * FROM contacts WHERE fname LIKE ?
[[#select_by_fname_end]]

[[#select_by_lname]]
SELECT * FROM contacts WHERE lname = ? ORDER BY fname
[[#select_by_lname_end]]

or by named parameters:

[[#select_by_fname_named_parms]]
SELECT * FROM contacts WHERE fname LIKE :fname
[[#select_by_fname_named_parms_end]]

[[#select_by_lname_named_parms]]
SELECT * FROM contacts WHERE lname = :lname ORDER BY fname
[[#select_by_lname_named_parms_end]]

If you are using positional parameters they can be specified while calling the SQL statement in 1 of 3 ways:

1. on the "command line" as simple arguments (here selecting any records where the first name starts with "A"):

{(wikish SQLselect Test.SQL#select_by_fname A%)}

2. piped from a preceding command:

{(wikish echo "A%" | SQLselect Test.SQL#select_by_fname)}

3. redirecting stdin from some other page or section:

[[#my_stdin]]
A%
[[#my_stdin_end]]
{(wikish SQLselect Test.SQL#select_by_fname <{$FullName}#my_stdin)}

Note that multiple lines ("records") of input can be present via the pipe or the redirected stdin. This will result in the command being executed multiple times, each with a new set of parameters. This is expected and normal for an INSERT and for an UPDATE as we either insert multiple records or update multiple records. It also works with SELECT statements, but you need to be aware that the output is simply being appended, so don't expect "ORDER BY" or "GROUP BY" or etc to be respected across multiple calls to SELECT... For example:

{(wikish echo "Smith\nJones" | SQLselect --otable Test.SQL#select_by_lname)}

This might result in output such as this (even though the SELECT statement has "ORDER BY lname"):

If you are specifying parameters via named parameters, currently the only way to do it is by setting options where the option name begins with an underscore (there is no way currently to use named parameters for multiple records of input):

{(SQLselect _fname=B% Test.SQL#select_by_fname_named_parms)}

Theoretically multiple records could be handled by CSV input with the first row being field names, but this is not yet implemented.

In addition to placing your SQL statements in a section or on a page, you can also specify them "inline" via the --sql option. Obviously this is only allowed if you have "SQL" authorization on the current page via SecLayer. It would look like this:

{(SQLselect --sql:"SELECT * FROM contacts WHERE fname = 'Patty' ORDER BY lname")}

There is also CSV capability, available via the --csv option. Note that the CSV definition is as per the PHP function fgetcsv(), for good or ill (depending on how you like to format your CSV). Here's how it might look:

[[#csvdata]]
Aaron,Jones
Bob,Smith
Cassandra,Black
Derek,Green
"Zachary \"yo-yo\"",Ford
[[#csvdataend]]
{(SQLinsert --csv --sql:"INSERT INTO contacts (fname, lname) VALUES (?, ?)" <{$FullName}#csvdata)}

When outputting data you can use the --table option to produce output in a table: (SQLselect --table --sql:SELECT * FROM contacts WHERE fname = 'Patty' ORDER BY lname)

There is also the capability of outputting with binary field- and record-separators. This can be very useful, for instance, if piping the output into a "while read ..." loop:

• Note the use of --print0 and --read0

{(wikish source {$FullName}#test)}
[[#test]]
SQLselect --print0 --sql:"SELECT * FROM contacts" | while read --read0 fname lname
do
   echo "I can now work with ${fname} and ${lname} without worrying about whitespace or commas or quotes in my data."
done
[[#testend]]

Options

Options can be set in any of several different places. Always the closer the option-setting occurs in relation to the actual execution of the SQL statement, the greater priority it has. The following, in ascending order of priority, is a complete list of the ways options can be set:

1. By setting $WikiShDb values within config.php or farmconfig.php (see below for an example)

$WikiShDb['otable'] = true; // default to output in a simple table format

2. Placing option=value statements in the section of the page containing the SQL statement(s)

[[#defaults]]
otable=1 # default to output in a simple table format
[[#defaultsend]]

3. Placing option=value statements in the section containing the SQL statement (separate options from SQL statement with a line containing 4 dashes)

[[#select_from_mytable]]
otable=1 # default to output in a simple table format
----
SELECT FROM mytable
[[#select_from_mytable_end]]

4. Setting options within the markup expression which actually calls the SQL in any of these ways:
   • --option
   • --option:value
   • option=value
   • Any of the following 3 lines are equivalent (since the option table is simply a true/false value):

{(wikish SQLselect --table {$FullName}#select_from_mytable)}
{(wikish SQLselect --table:1 {$FullName}#select_from_mytable)}
{(wikish SQLselect table=1 {$FullName}#select_from_mytable)}

Here is a complete reference list of available options:

Notes

Although some flexibility is given in the security configuration above, below are some good precautions:

• Specify your database name, username, and password via $WikiShDb in config.php rather than on a page.
• Make sure your page containing SQL statements and your script page are edit-protected and probably source-protected via pmwiki authorizations.
  • Note that your SQL page and your script page can be one and the same
• Since there is currently only one "SQL" authorization, it may be necessary to carefully split up your various SQL statements onto different pages so as to arrive at an adequately limiting form of security. (I.e., you might put all your INSERT/UPDATE/DELETE statements in Test.SQL_mods and your SELECT statements in Test.SQL_select -- then you can give any page in the Test group SQL authorization on Test.SQL_select but only the pre-approved script pages (which are locked down) would have SQL authorization on Test.SQL_mods. You can become arbitrarily complex to fit your security needs.)

Change log / Release notes

• 2011-02-13 - Renamed some options (upper-cased ifs, irs, ofs, ors, etc. to increase compatibility with WikiSh), added print0 and read0 capabilities, etc. Note that read0 is only available in the currently pre-release version of WikiSh so the example of piping to while read --read0 doesn't work until the next version of WikiSh is released.
• 2011-02-08 - Added $wshdbEnableDBConfigOnPage as an additional security measure, changed some defaults, added SQLite support, etc.
• 2011-02-06 Initial release. Basic functionality has been tested in mysql. Lots more testing needed.

Known bugs

• If you set a synonym option at a lower level which gets over-ridden by another synonym at another level, it is possible that some of the options will not be unset. For instance, if you specify --otable in the [[#defaults]] section and then specify --ocsv in the SQL section (or the markup expression itself) then the line_prefix and line_suffix do not get unset and you end up with CSV output enclosed within double-pipe-symbols. I haven't figured out an elegant way to get around this at present, because there's no way to differentiate between an option set by a synonym and an option set explicitly (you may have intentionally set a line_prefix option and combined it with CSV for some inexplicable reason). While setting options at different places is possible and has significant usefulness in certain contexts, it is currently recommended to choose one or two places (probably $WikiShDb or #defaults and the markup expression itself) and do all your option-setting there.
• It's not really a bug, but do be aware that --read0 is not yet implemented in a released version of WikiSh. If you need a pre-release give me a holler.

See also

Contributors

Comments

See discussion at WikiShDb-Talk?