This plugin will get data from either a csv file or scrape a table on a web page and load it into a temporary database. It will execute the provided sql query on the database and output the results as a table

To use this plugin a user needs access to a mysql database and privilege to

• Create temporary tables,
• Select, and
• Insert.

I recommend creating a user account and granting database specific privilege to that account, specifing a database external from your other databases. That user account information will be specified in the plugin software and the user will not need to know the login information.

External requirements: PEAR::DB. Install the Pear DB.php package.

Install the plugin using the Plugin Manager and the download URL above, which points to latest version of the plugin. Refer to Plugins on how to install plugins manually.

Update the configuration data for the user account login information for the database.

The following is data from a scrape of a US Employment data table. (Note that column headings have no spaces when a new line is started. Otherwise use an underscore for spaces. Eg: July2013).

<sqlraw  id='unemployment' title='Unemployment by education level' source='scrapeUrl' link="http://www.bls.gov/news.release/empsit.a.htm">SELECT category, July2013 FROM temptable where (Category = "Less than a high school diploma" or Category = "High school graduates, no college" or Category = "Some college or associate degree" OR Category = "Bachelor's degree and higher")  ;</sqlraw>

The following is data from a csv file provided by the US Geological Survey (USGS) of recent earthquake activity worldwide. This example links directly to the csv file.

<sqlraw id='earthquakes' source='csvfile' title='Large Recent Earthquakes' link="http://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/4.5_week.csv">SELECT time, mag, depth, place FROM temptable WHERE mag > 5.0 AND depth < 70;</sqlraw>

The following is an example using a dokuwiki page with a table

1. Create a dokuwiki page called runexample with the following content. This example created a page at users:admin:data.

  
====== Running ======

^ Date ^ Distance ^ Time ^ Excuse ^ Factor ^ Events ^
| 2012-06-22 |  |  |  Time  |  |  |
| 2012-06-24 |  3.5  |  35  |  |  Hot  |  |
| 2012-06-26 |  3.5  |  34  |  |  |  |
| 2012-06-28 |  |  |  |  |  6.2 mile race (Lehigh Valley Zoo - 10k) |
| 2012-06-30 |  3.6  |  35  |  |  |  |
| 2012-07-02 |  |  |  |  |  |
| 2012-07-04 |  |  |   |   |  |
|  2012-07-06  |  3.7  |  36  |   |   |  |
|  2012-07-08  |  4  |  37  |    |  Tired  |   |
|  2012-07-10  |    |    |     |     |  the marathon to end all marathons |
|  2012-07-12  |  4.1  |  37  |     |     |   |
|  2012-07-14  |  4.1  |  37  |     |     |   |
|  2012-07-16  |  4.2  |  38  |     |     |   |
|  2012-07-18  |  4.2  |  38  |     |     |   |
|  2012-07-04  |  3.4  |  30  |     |  Hot  |   |

1. Create another dokuwiki page with the following content. This uses the tableplot plugin but that is not required to see the output of the sqlraw plugin

This page processes a scrape of the supplied link to my_run_log page to get the first table present following a marker. The data is put into a temporary database table. Then a query is run on the temporary table to select only date and calculate the speed in minutes per mile. The query produces an output table which is replaced by a plot of the data.

<sqlraw id='myrun' title='My Running' source='scrapeUrl'  link="users:admin:data" startMarker="Running">SELECT date, round(time/distance,1) FROM temptable;</sqlraw>

<tableplot>&series=columns&width=500&height=250&id=myrun&position=after&plotArgs={title: {text: 'My run times', fontSize: '14pt', textColor: 'skyblue'}, axes: {xaxis: {renderer: jQuery.jqplot.CategoryAxisRenderer, tickRenderer: jQuery.jqplot.CanvasAxisTickRenderer ,tickOptions: {angle: -30}}}, seriesDefaults: {renderer:jQuery.jqplot.BarRenderer, pointLabels: { show: true, location: 'e', edgeTolerance: -15} }}&
</tableplot>

Basic syntax:

<sqlraw source='csvfile|scrapeUrl' link="url">mysql query;</sqlraw>

• id = 'string' — an html id for the table. (This can then be used by tableplot plugin to plot.)
• link = 'string' — url to csv or table
• startMarker = 'string' — a marker of text to start looking for the next table. Only needed if not using the first table on the page, For dokuwiki pages this may be required as the sidebar page index is usually the first table.
• display = 'inline' or 'block' (default block)
• position = 'horizontal' or 'vertical' (default horizontal)
• tableid = 'string' — id of output table
• class = 'string' — css class identifier (eg: sortable for table sorting) (default inline)
• title = 'string' — table caption
• source = 'csvfile' or 'scrapeUrl'

Configuration item

'tempdb'

is used to set the temporary database login. It is in the format of

'mysql://user:password@hostname/database'

'debugfilepath'

provides a file path and filename to save the raw data for scraped tables. The table data will be saved as a csv file. This is useful for debugging the table output from a scraped table. An empty or null string will specify do not write the csv file. Note that this is only for table data. The path is relative to the dokuwiki base install folder.

'restrict_names'

If true (=1) then scraped table headings will be searched for all elements in the configuration array of 'mysqlDisallow' and if found they will be replaced with the corresponding element in 'mysqlReplace'. This allows removing header elements that will cause errors when the heading is used as a database field name.

'mysqlDisallow'

array of elements to replace if restrict_names is true (1)

'mysqlReplace'

array of replacement elements for disallowed header elements.

[downloadable blocks, development comments]

• disallow brackets in headings (2013-12-15 15:02)
• remove close db due to error (2013-11-30 17:22)
• fix csv file processing (2013-11-17 14:07)
• fix errors (2013-11-16 02:54)
• do not strip end commas (2013-11-15 00:39)
• made more robust (2013-11-14 22:59)
• enhance table formatting capability (2013-11-09 21:21)
• add table sort capability (2013-11-02 16:37)

None

Sqlraw discussion page