Demonstrate the Database plugin

Here are some examples showing how to access an external Databasesystem fromn within TWiki. Two database systems are used: SQLite and Mysql. SQLite is the simpler of the two. In both cases the database will need to be configured within the TWiki before use, so please contact the admins at physicstwikiadmin@lists.rhul.ac.uk if you wish to use this plugin. For SQLite it should be possible for users to externally modify a database file and upload it to the TWiki for use there by attaching it to a topic.

See DatabasePlugin for plugin details.

Example 1

In this example the request will make a simple query to an SQLite database. The database contains a table listing the attendees at Widecombe Fair according to a folk song.

Request:

%DATABASE_SQL{description="test_data" sql="SELECT * FROM Widecombe_Fair" header="|*Forename* | *Surname* |" format="| $forename | $surname |"}%

Returned Data:

Example 2

As above but only list attendees whose forname is Peter.

Request:

%DATABASE_SQL{description="test_data" sql="SELECT * FROM Widecombe_Fair WHERE forename='Peter'" header="|*Forename* | *Surname* |" format="| $forename | $surname |"}%

Returned Data:

Example 3

Insert some dynamical information obtained from TWikiVariables into an SQLite database table. This demo also uses the GeoLookupPlugin to lookup the remote IP's geographical location. Everytime the topic is viewed the current information is inserted into the SQLite database table TWiki_Demo. The last 20 rows of the table are then dumped as a TWiki table in descending order. The following SQL command creates/initialises the table,

CREATE TABLE TWiki_Demo (int primary key, username varchar, wikiusername varchar, time varchar, ip varchar, location varchar)

Request:

%DATABASE_SQL{description="test_data" sql="INSERT INTO 'TWiki_demo' VALUES(NULL, \"%USERNAME%\", \"%WIKIUSERNAME%\", \"%SERVERTIME%\", \"%REMOTE_ADDR%\", \"%GEOLOOKUP{%REMOTE_ADDR%}%\" )"}%

inserts the dynamical information into the SQLite database table.

Request:

%DATABASE_SQL{description="test_data" sql="SELECT * FROM TWiki_demo order by 1 DESC limit 20" header="| *Username* | *Wikiusername* | *Time* | *Remote IP* | *Remote Location* |" format="| $username | $wikiusername | $time | $ip | $location |"}%

displays the updated contents of SQLite database table.

Returned Data:

Example 4

Read a variety of different types of information from a user using a TWIki form and store it in SQLite database table. The last 20 rows of the table are then dumped as a TWiki table in descending order. The following SQL command creates/initialises the table,

CREATE TABLE Mychoices (int primary key, info varchar, choice varchar, mycheck varchar, options varchar, multichoice varchar);

See https://twiki.ph.rhul.ac.uk/twiki/bin/view/ShowCase/PluginTestDatabase?raw=on to view the Form's TWiki markup language code and %DATABASE_SQL{}% calls below. To use, fill in the Info text field, select values for the other options and click Commit to commit the data to the database. In particular, note the escaping needed in the %DATABASE_SQL{}% call's arguments for the " and % characters. The " are double-escaped. Such escaping is needed inside the %CALC% call which is needed to delay execution of the %DATABASE_SQL{}% call to prevent it being executed when the %IF{}% clause's then statement is not executed, when a user views the page but does not click the Commit button.

Returned Data:

Example 5

In this example the request will make a simple query as in Example 1 above but this time to a Mysql database. Mysql is a much larger, more sophisticated database system than Sqlite and works on a client server basis rather than as a standalone utility. Consequently, more back-end sysadmin work is required to configure it. Please contact the admins at physicstwikiadmin@lists.rhul.ac.uk for more information if you would like to use Mysql.

Request:

%DATABASE_SQL{description="twikidemo" sql="SELECT * FROM Widecombe_Fair" header="| *Forename* | *Surname* |" format="| $forename | $surname |"}%

Returned Data:

Example 6

This more sophisticated example demonstrates using SQL to query the Classic Models Mysql sample database available for download at http://www.mysqltutorial.org/mysql-sample-database.aspx. It represents the company records of a classic model car company. The procedure uses the TWiki Form below to select the table and query. There are 8 data-tables within the Classic Models database as follows;

To use,

1. Select a table in the Table pull-down menu
2. Enter an SQL instruction in the SQL query text field. Be sure to specify the same table in the query and Table pull-down menu.
3. Click Submit

Show some example SQL instructions to try Hide the example SQL instructions

select * FROM classicmodels.customers

select * FROM classicmodels.customers WHERE country='UK'

select * FROM classicmodels.employees

select * FROM classicmodels.employees WHERE reportsTo=1002

select * FROM classicmodels.offices

select * FROM classicmodels.orderdetails

select * FROM classicmodels.orderdetails WHERE quantityOrdered > 20

select * FROM classicmodels.orderdetails WHERE quantityOrdered > 50 AND quantityOrdered < 56

select * FROM classicmodels.orderdetails WHERE quantityOrdered > 50 AND quantityOrdered < 56 AND priceEach > 100.00

select * FROM classicmodels.orders

select * FROM classicmodels.orders WHERE shippedDate > '2005-05-00' AND shippedDate < '2005-06-00'

select * FROM classicmodels.payments WHERE amount > 100000;

select * FROM classicmodels.payments WHERE amount > 100000 AND paymentDate='2005-03-05'

select * FROM classicmodels.productlines

select * FROM classicmodels.productlines LIMIT 3

select * FROM classicmodels.products WHERE quantityInStock < 1000

select * FROM classicmodels.products WHERE quantityInStock < 1000 OR quantityInStock > 9000

select * FROM classicmodels.products WHERE ( quantityInStock < 1000 OR quantityInStock > 9000 ) AND productScale='1:32'

See https://twiki.ph.rhul.ac.uk/twiki/bin/view/ShowCase/PluginTestDatabase?raw=on to view the Form's TWiki markup language code and the %DATABASE_SQL{}% calls

-- TomCraneAdmin - 13 August 2012