DokuWiki

It's better when it's simple

User Tools

Site Tools

Trace:
plugin:sqlraw

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
plugin:sqlraw [2013-10-25 02:24] – tom_cplugin:sqlraw [2018-06-05 23:59] (current) – [Installation] Klap-in
Line 1: Line 1:
-====== sqlraw Plugin ======+====== SQLRaw Plugin ======
  
 ---- plugin ---- ---- plugin ----
Line 6: Line 6:
 email      : tcafferty@glocalfocal.com  email      : tcafferty@glocalfocal.com 
 type       : syntax type       : syntax
-lastupdate : 2013-09-03 +lastupdate : 2013-11-17 
-compatible : Weatherwax+compatible : Adora Belle, Weatherwax, Binky
 depends    :  depends    : 
 conflicts  :  conflicts  : 
Line 27: Line 27:
   * Select, and    * Select, and 
   * Insert.    * 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.+I recommend creating a user account and granting database specific privilege to that account, specifying 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:** [[http://pear.php.net/package/DB|PEAR::DB]]. Install the Pear DB.php package. +Search and install the plugin using the [[plugin:extension|Extension Manager]]. Refer to [[:Plugins]] on how to install plugins manually.
- +
-Install the plugin using the [[plugin:plugin|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. Update the configuration data for the user account login information for the database.
 +
 +:!: **External requirements:** 
 +  - [[http://pear.php.net/package/DB|PEAR::DB]]. Install the Pear DB.php package.
 +  - If the table sort capability is wanted  then the [[plugin:keywords|Keywords plugin]] is needed. If you do not want the sort capability you can delete the following files from the install
 +  * sorttable.min
 +  * action.php
  
 ===== Examples/Usage ===== ===== Examples/Usage =====
  
-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).+Examples can be seen at <del>[[http://clean.glocalfocal.com/doku.php?id=start|Plugin Examples Site]]</del>
  
-  <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 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: Oct_2013). This example uses the fixTable='1' option to correct issues with the table.
  
-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='unemployment' title='Unemployment by education level' source='scrapeUrl' link="http://www.bls.gov/news.release/empsit.a.htm" fixTable='1'>SELECT category, Oct_2013 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>
  
-  <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
  
-The following is an example using a dokuwiki page with a table +Create a dokuwiki page called runexample with the following content. This example created a page at users:admin:data.
- +
-   - Create a dokuwiki page called runexample with the following content. This example created a page at users:admin:data.+
  
 <code>   <code>  
Line 70: Line 72:
 </code> </code>
  
-  - 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+Create another dokuwiki page with the following content. This uses the sqlraw plugin to query the existing table and display the query results in a table. 
 +<code>  
 +<sqlraw id='myrun' title='My Running' source='scrapeUrl'  link="users:admin:data:runexample" startMarker="Running">SELECT date, round(time/distance,1) FROM temptable;</sqlraw>
  
-<code>+</code>
 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. 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> +  * The following is an example using the sort capability for the created table 
- +<code>  
-<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} }}& +  <sqlraw id='prices' class='sortable' title='World Gasoline Prices' source='scrapeUrl' link="http://en.wikipedia.org/wiki/Gasoline_and_diesel_usage_and_pricing">SELECT country_territory AS Country, us$_us_gallon_95_ron AS "US Dollars for Gallon Regular", RIGHT(date_of_price, 8) AS Date FROM temptable;</sqlraw> 
-</tableplot>+{{keywords>sqlRawSort}} 
 +</code> 
 +Set the class as 'sortable". Use the keywords plugin to pull in the sorting javascript code. Clicking on a column header will sort the table one way (ASC, DESC) and clicking again will sort the table the other direction.
  
 +  * The following example gets the data from an online csv file. The source option is now set to csvfile. The url link points to a downloadable csv file.
 +<code>
 +  <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> 
 </code> </code>
 ===== Syntax ===== ===== Syntax =====
Line 86: Line 95:
 <code><sqlraw source='csvfile|scrapeUrl' link="url">mysql query;</sqlraw></code> <code><sqlraw source='csvfile|scrapeUrl' link="url">mysql query;</sqlraw></code>
  
-  * id = 'string' --- an html id for the table. (This can then be used by tableplot plugin to plot.) +^ Parameter ^ Values ^ Description ^ 
-  * link = 'string' --- url to csv or table +|  id  |  'string'  | an html id for the table. (This can then be used by tableplot plugin to plot.)  | 
-  * 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.  +|  link  |  'string'  | url to csv or table  | 
-  * display = 'inline' or 'block' (default block) +|  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.  |  
-  * position = 'horizontal' or 'vertical' (default horizontal) +|  display  |  'inline' or 'block'  | default block  | 
-  * tableid = 'string' --- id of output table +|  position  |  'horizontal' or 'vertical'  | Table orientation. Default  is horizontal  | 
-  * class = 'string' --- css class identifier (eg: sortable for table sorting) (default inline) +|  class  |  'string'  | css class identifier (eg: sortable for table sorting) (default inline)  | 
-  * title = 'string' --- table caption +|  title  |  'string'  | Table caption  | 
-  * source = 'csvfile' or 'scrapeUrl' +|  source  |  'csvfile' or 'scrapeUrl'  | Source of data is either a csv file or a table on a webpage  |  
 +|  caption  |  '0' or '1'  | A '1' specifies to skip the 1st row as it is a caption. Default is '0'.  | 
 +|  tableNumber  |  'number'  | The position number of a table to scrape on a web page. For example '2' would scrape the 2nd table. The default is '1'.  | 
 +|  fixTable  |  '0' or '1'  | A '1' will run a function that attempts to handle such items as row or col span, or a caption as a table heading. The default is '0' to execute faster.  |
  
 ===== Configuration and Settings ===== ===== Configuration and Settings =====
Line 100: Line 112:
 Configuration item  Configuration item 
  
-**'tempdb'**+**'sqlraw_tempdb'**
  
 is used to set the temporary database login. It is in the format of  is used to set the temporary database login. It is in the format of 
Line 106: Line 118:
   'mysql://user:password@hostname/database'   'mysql://user:password@hostname/database'
      
-**'debugfilepath'** +**'sqlraw_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.+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. If not needed for debug then set to a empty string to execute faster.
  
-**'restrict_names'**+**'sqlraw_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. 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'**+**'sqlraw_mysqlDisallow'**
  
 array of elements to replace if restrict_names is true (1) array of elements to replace if restrict_names is true (1)
  
-**'mysqlReplace'**+**'sqlraw_mysqlReplace'**
  
 array of replacement elements for disallowed header elements. array of replacement elements for disallowed header elements.
  
 +===== Development =====
  
 +The plugin will run considerably faster on scraping website tables if the following debug capabilities are not used. . They are turned off with the following settings.
  
- +in the conf/default.php file 
- +  $conf['sqlraw_debugfilepath'] = ''; 
-===== Development ===== +   
- +On an individual page, the following default parameter.  
-//[downloadable blocks, development comments]//+  fixTable = '0'
  
 === Change Log === === Change Log ===
plugin/sqlraw.1382660677.txt.gz · Last modified: 2013-10-25 02:24 by tom_c
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
CC Attribution-Share Alike 4.0 International Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki

Global DokuWiki Links