Tags:
create new tag
, view all tags

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:

Forename Surname
Bill Brewer
Jan Stewer
Peter Gurney
Peter Davy
Daniel Whiddon
Harry Hawke
Tom Cobley

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:

Forename Surname
Peter Gurney
Peter Davy

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:

Username Wikiusername Time Remote IP Remote Location
guest TWikiGuest 29 Jun 2017 - 10:08 54.166.146.212 Ashburn, VA, USA
guest TWikiGuest 29 Jun 2017 - 08:04 157.55.39.148 Redmond, WA, USA
guest TWikiGuest 29 Jun 2017 - 06:12 40.77.167.60 Boydton, VA, USA
guest TWikiGuest 28 Jun 2017 - 22:52 207.46.13.167 Redmond, WA, USA
guest TWikiGuest 28 Jun 2017 - 10:35 37.187.56.81 , , France
guest TWikiGuest 27 Jun 2017 - 17:20 207.46.13.130 Redmond, WA, USA
guest TWikiGuest 27 Jun 2017 - 14:42 176.9.139.229 , , Germany
guest TWikiGuest 26 Jun 2017 - 07:45 207.46.13.167 Redmond, WA, USA
guest TWikiGuest 25 Jun 2017 - 22:29 207.46.13.130 Redmond, WA, USA
guest TWikiGuest 25 Jun 2017 - 21:57 157.55.39.100 Redmond, WA, USA
guest TWikiGuest 25 Jun 2017 - 18:57 157.55.39.173 Redmond, WA, USA
guest TWikiGuest 25 Jun 2017 - 16:57 157.55.39.173 Redmond, WA, USA
guest TWikiGuest 25 Jun 2017 - 16:45 40.77.167.126 Boydton, VA, USA
guest TWikiGuest 25 Jun 2017 - 15:34 157.55.39.62 Redmond, WA, USA
guest TWikiGuest 25 Jun 2017 - 15:29 157.55.39.100 Redmond, WA, USA
guest TWikiGuest 25 Jun 2017 - 09:31 157.55.39.173 Redmond, WA, USA
guest TWikiGuest 25 Jun 2017 - 07:55 157.55.39.62 Redmond, WA, USA
guest TWikiGuest 25 Jun 2017 - 07:43 40.77.167.126 Boydton, VA, USA
guest TWikiGuest 25 Jun 2017 - 06:09 40.77.167.126 Boydton, VA, USA
guest TWikiGuest 25 Jun 2017 - 02:50 157.55.39.173 Redmond, WA, USA

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 http://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.

Info:
Choice:
Mycheck:
Options:
Multichoice:
 

Returned Data:

Info Choice Mycheck Options Multichoice
Special entry Bad on Option#3 optionc
  Excellent   Option#1 optionc
  Rubbish on Option#3 optionc
  Good on Option#2 optionb
  Indifferent   Option#2 optionc
BoogertTest Excellent on Option#2 optiona
6th entry Bad on Option#3 optiona
5th entry Indifferent on Option#2 optiona
forth entry Rubbish   Option#1 optionc
third entry Indifferent   Option#1 optionb

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:

Forename Surname
Bill Brewer
Jan Stewer
Peter Gurney
Peter Davy
Daniel Whiddon
Harry Hawke
Tom Cobley

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;

List of tables in database
customers
employees
offices
orderdetails
orders
payments
productlines
products

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

Some example SQL queries.

Cut and paste these queries into the TWiki form below.

  • Show all customer data records
select * FROM classicmodels.customers
  • Show data on UK only customers
select * FROM classicmodels.customers WHERE country='UK'
  • Show all employee data records
select * FROM classicmodels.employees
  • Show employees who report directly to the company president
select * FROM classicmodels.employees WHERE reportsTo=1002
  • Show all office data records
select * FROM classicmodels.offices
  • Show all order details records
select * FROM classicmodels.orderdetails
  • Show order details records where more than 20 items were ordered.
select * FROM classicmodels.orderdetails WHERE quantityOrdered > 20
  • Show order details records where between 50 and 56 items were ordered.
select * FROM classicmodels.orderdetails WHERE quantityOrdered > 50 AND quantityOrdered < 56
  • Show order details records where between 50 and 56 items were ordered and costed more than $100.
select * FROM classicmodels.orderdetails WHERE quantityOrdered > 50 AND quantityOrdered < 56 AND priceEach > 100.00
  • Show all orders records
select * FROM classicmodels.orders
  • Show orders records for items shipped in May 2005
select * FROM classicmodels.orders WHERE shippedDate > '2005-05-00' AND shippedDate < '2005-06-00'
  • Show payments records involving payments of more than $100,000
select * FROM classicmodels.payments WHERE amount > 100000;
  • Show payments records involving payments of more than $100,000 that were paid on 5th May 2005
select * FROM classicmodels.payments WHERE amount > 100000 AND paymentDate='2005-03-05'
  • Show all product lines
select * FROM classicmodels.productlines
  • Show the first 3 product lines
select * FROM classicmodels.productlines LIMIT 3
  • Show products where fewer than 1000 items are currently in stock
select * FROM classicmodels.products WHERE quantityInStock < 1000
  • Show products where fewer than 1000 or more than 9000 items are currently in stock
select * FROM classicmodels.products WHERE quantityInStock < 1000 OR quantityInStock > 9000
  • Show models with a 1:32 scale where fewer than 1000 or more than 9000 items are currently in stock
select * FROM classicmodels.products WHERE ( quantityInStock < 1000 OR quantityInStock > 9000 ) AND productScale='1:32'

Notes.

  • SQL language is very rich and these examples only provide a very small example of what is possible by way of selecting and displaying data. This topic uses a separate %DATABASE_SQL% call for each of the 8 tables and displays all fields present in the table. To display fields selectively, individual %DATABASE_SQL% calls must be provided.

See http://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

SQL query:
Table:
 

-- TomCraneAdmin - 13 August 2012

Physics WebpagesRHUL WebpagesCampus Connect • Royal Holloway, University of London, Egham, Surrey TW20 0EX; Tel/Fax +44 (0)1784 434455/437520

Topic revision: r2 - 08 Aug 2012 - TomCraneAdmin

 
This site is powered by the TWiki collaboration platformCopyright © 2008-2017 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding RHUL Physics Department TWiki? Send feedback