Twitter from the MySQL Data Warehouse

Some weeks ago I created a tweeting PHP script . Now I use this script for posting job activity from the Data Warehouse on Twitter. Since the  Data Warehouse jobs are registered in MySQL databases, we had to sum up the figures and feed them into the PHP script twitter02.php.  It turned out to an easy task, using the Integration Tag Language . I decided to test this with a  status message showing how many jobs have run the last 24 hours. Here is the schedule:

The first job ‘crtSQLMsg’ sums up all job activity and pass the result to the sqlconverter_CSV.php  script which converts the result table to a file:

Which looks like:

Now it’s only to post this file with the job ‘twittaMsg’, if you study the job you see how the job status message is prefixed with #DataWarehouse.

If you follow @tooljn at Twitter you have seen this tweet as:

 

I’m very happy with the SQL converter  functionality, which out of the box converted the result table into a readable message, and the  @tag GETSQLMSG  which slurps up the message in the subsequent twittaMsg job.

I end this post with the sqlconverter_CSV.php script:

<?php

/**

* SQL result converter - dynamically included in function {@link execSql()}

*

* This converter converts a SQL select result to a CSV file.

*

* This converter also accepts:

*

* Syntax:  <sqlconverter name='sqlconverter_CSV.php' target='report0' headers='no' delim='space' enclosed=''/>

* 1 delim                field delimiter                 default ';' semicolon

* 2 header        field headers                default TRUE/yes

* 3 enclosed        field enclosed by                default NULL

*

* Note delim ' ' doesn't work for unknown reason, so use 'space' instead. Bug?

*

* @see sqlconverter_default.php

* @author Lasse Johansson <lars.a.johansson@se.atlascopco.com>

* @version  1.0.0

* @package adac

* @subpackage sqlconverter

*/

$metafile = $sqltarget.'meta_';

$metasfx = '.TXT';

$targetsfx = 'CSV';

$fieldDelimiter = ';';        // default

$fieldEnclosed = "'";        // default

$headers = TRUE;        // default

if(array_key_exists('delim',$xmlconverter))

  $fieldDelimiter = is_string($xmlconverter['delim']) ? $xmlconverter['delim'] : $xmlconverter['delim'][0]['value'];

if(array_key_exists('enclosed',$xmlconverter))

  $fieldEnclosed = is_string($xmlconverter['enclosed']) ? $xmlconverter['enclosed'] : $xmlconverter['enclosed'][0]['value'];

if(array_key_exists('headers',$xmlconverter))

  $t_headers = is_string($xmlconverter['headers']) ? $xmlconverter['headers'] : $xmlconverter['headers'][0]['value'];

if ($t_headers == 'no') $headers = FALSE;

if ("$fieldDelimiter" == 'space') $fieldDelimiter = ' ';

if ("$$fieldEnclosed" == 'space') $fieldEnclosed = ' ';

$sqllog->logit('Note',"Enter sqlconverter_CVS.php using target=$sqltarget");

if(is_numeric(substr($sqltarget, -1,1))) {

        $metafile = "$metafile$metasfx";

        $sqltarget = "$sqltarget.$targetsfx";

} else {

        clearstatcache();

        for ($x=0; 1==1; $x++){

                if (!file_exists("$metafile$x$metasfx")){

                        $metafile = "$metafile$x$metasfx";

                        $sqltarget = "$sqltarget$x.$targetsfx";

                        break;

                }

        }

}

if (file_exists($sqltarget)) $fpc_flag = 'FILE_APPEND';

else $fpc_flag = NULL;

$report = '';

if ($fpc_flag == NULL and $headers){;

        $meta = '';

        while ($finfo = $result->fetch_field()) {

          $report .= $finfo->name."$fieldDelimiter";

          $meta .= sprintf("Name:    %s;", $finfo->name);

          $meta .= sprintf("OrgName:    %s;", $finfo->orgname);

          $meta .= sprintf("Table:    %s;", $finfo->table);

          $meta .= sprintf("OrgTable:    %s;", $finfo->orgtable);

          $meta .= sprintf("Default:    %s;", $finfo->def);

          $meta .= sprintf("MaxLen:    %d;", $finfo->max_length);

          $meta .= sprintf("Len:    %d;", $finfo->length);

          $meta .= sprintf("Charsetnr:    %d;", $finfo->charsetnr);

          $meta .= sprintf("Flags:    %d;", $finfo->flags);

          $meta .= sprintf("Type:    %d;", $finfo->type);

          $meta .= sprintf("Decimals:    %d;", $finfo->decimals);

          $meta .= "\n";

        }  

        file_put_contents($metafile,$meta);

        unset($meta);

        $report .= "\n";

}

//  Here comes the working code

while ($row = $result->fetch_row()) {

  foreach($row as &$fld) {$fld = "$fieldEnclosed"."$fld"."$fieldEnclosed";}

  $rowstr = implode("$fieldDelimiter", $row);

  $log->logit('Note',"$rowstr");

  $report .= $rowstr."\n";

}

if($fpc_flag == 'FILE_APPEND') file_put_contents($sqltarget,$report,FILE_APPEND);

else file_put_contents($sqltarget,$report);

unset($report);

$sqllog->logit('Note',"Exit sqlconverter_CVS.php");