<?php if (!defined('PmWiki')) exit();
# vim: set ts=4 sw=4 et:
##
##        File: WikiShDb.php
##     Version: 2011-02-13
##      SVN ID: $Id: WikiSh.php 418 2010-06-09 05:55:02Z pbowers $
##      Status: beta
##      Author: Peter Bowers
## Create Date: January 30, 2011
##   Copyright: 2011, Peter Bowers
##
## This program is free software; you can redistribute it and/or modify
## it under the terms of the GNU General Public License, Version 2, as
## published by the Free Software Foundation.
## http://www.gnu.org/copyleft/gpl.html
## This program is distributed in the hope that it will be useful,
## but WITHOUT ANY WARRANTY; without even the implied warranty of
## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
## GNU General Public License for more details.
##
#
# Security for WikiShDb is handled via SecLayer, using the $wshAuthPage
# config variable.  If the page containing the "SQL Statement Definition"
# ($DBDefPage) has 'SQL' authorization and in whatever way (via read
# or forceread) the script can read $DBDefPage then the SQL statement
# is deemed authorized and will be executed.  Of course the underlying
# database can implement other layers of permissions & authorization via
# the username and password and etc.
#

$RecipeInfo['WikiShDb']['Version'] = '2011-02-13';

# DBConfig involves settings username, password, database, dbhost, engine
# if $wshdbEnableDBConfigOnPage then these can be set on any page that has
# SQL authorization (via SecLayer).  If not enabled these can only be configured
# via $WikiShDb in the config.php or other PHP script (this is the default for
# security purposes).
SDV($wshdbEnableDBConfigOnPage, false);

SDVA($WikiShDb, array(
     'username'=>''
    ,'password'=>''
    #,'filename'=>null // synonym for database
    ,'database'=>''
    ,'dbhost'=>'' // used for mysql
    #,'dbopts'=>null
    #,'dsn'=>null
    ,'engine'=>''
    ,'FS'=>''
    ,'OFS'=>','
    ,'IFS'=>','
    ,'RS'=>''
    ,'IRS'=>"\n" 
    #,'ors'=>null // synonym for 'newline'
    ,'newline'=>"\n"
    ,'default_section'=>'#defaults'
    ,'enclosure'=>'"'
    ,'escape'=>'\\'
    ,'sql'=>''
    #,'table'=>null
    #,'itable'=>null
    #,'otable'=>null
    #,'csv'=>null
    #,'icsv'=>null
    #,'ocsv'=>null
));

# $pagename is our current page (usually where the script resides)
# $DBDefPage is contains the page (&, optionally, section) where the SQL 
#    definitions reside
# $opts will pass back the defined options:
#    ['sql'] = 'the actual statement(s) to be executed'
#    ['username']
#    ['password']
#    ['database']
#    ['engine']
#    ['OFS'] (output field separator)
#    ['IFS'] (input field separator)
#    ['IRS'] (input record separator)
function wshdbInitialize($pagename, $DBDefPage, &$opts)
{
    global $WikiShDb, $wshAuthPage, $wshdbEnableDBConfigOnPage;

    $func="wshdbInitialize";
    wdbg(4,"$func($pagename, $DBDefPage): Entering");
    ##
    # Options ($WikiShDb (lowest), #defaults, $DBDefPage, $opts (highest))
    ##
    $pn = MakePageName($pagename, $DBDefPage); // strip #section
    $section = str_replace($pn, '', $DBDefPage);
    if (wshIsAWikiPage(false, $pn)) {
        $pna = substr($pn, strlen(WIKIPAGEID));
        $pnb = substr($DBDefPage, strlen(WIKIPAGEID));
    } else {
        $pna = $pn;
        $pnb = $DBDefPage;
    }
    if (!slAuthorized($pna, $wshAuthPage, 'SQL') && !slAuthorized($pnb, $wshAuthPage, 'SQL')) {
        wshStdErr($pagename, $opts, "ERROR: Page \"$pagename\" does not have SQL privileges on \"$pna\" or \"$pnb\"");
        return false;
    }
    #echo "DEBUG: DBDefPage=$DBDefPage, pn=$pn<br>\n";
    if (!($page = wshReadPage($pagename, $opts, $pn)) || !@$page['text']) {
        wshStdErr($pagename, $opts, "ERROR: Non-existent (or blank) page \"$pn\"");
        return false;
    }
    #echo "DEBUG: page[text]=<pre>$page[text]</pre><br>\n";
    #echo "DEBUG: page=<pre>".print_r($page,true)."</pre><br>\n";
    #echo "DEBUG: WikiShDb=<pre>".print_r($WikiShDb,true)."</pre><br>\n";
    $WikiShDb = wshdbOptSynonyms($pagename, $WikiShDb, true);
    if ($dflt_text = TextSection($page['text'], $WikiShDb['default_section'])) {
        #echo "DEBUG: dflt_text=$dflt_text<br>\n";
        $dflt_text = preg_replace("/#.*$/", '', $dflt_text); // strip comments
        $defaults = wshdbOptSynonyms($pagename, ParseArgs($dflt_text), $wshdbEnableDBConfigOnPage);
        #echo "DEBUG: text of defaults=$dflt_text, parsed=<pre>".print_r($defaults,true)."</pre><br>\n";
    } else {
        #echo "DEBUG: No #default section<br>\n";
        $defaults = array();
    }
    if ($opts['sql']) $dbdef_opts = array();
    else {
        if (!($text = TextSection($page['text'], $section))) {
            wshStdErr($pagename, $opts, "ERROR: Non-existent section \"$section\" in page \"$pn\"");
            return false;
        }
        $text = preg_replace("/#.*$/", '', $text); // strip comments
        list($opt_text, $sql_statement) = preg_split("/^\s*[-=]{4,}\s*$/m", $text);
        if (!$sql_statement) { // no divider - means no opts were specified
            $sql_statement = $text;
            $opt_text = '';
        }
        #echo "DEBUG: opt_text=$opt_text, sql_statement=$sql_statement<br>\n";
        $dbdef_opts = wshdbOptSynonyms($pagename, ParseArgs($opt_text), $wshdbEnableDBConfigOnPage);
        $opts['sql'] = trim($sql_statement);
    }
    #echo "DEBUG: text of $section=$text, parsed=<pre>".print_r($dbdef_opts,true)."</pre><br>\n";
    $opts = wshdbOptSynonyms($pagename, $opts, $wshdbEnableDBConfigOnPage);
    #echo "DEBUG: opts=".print_r($opts,true)."<br>\n";
    #echo "DEBUG: dbdef_opts=".print_r($dbdef_opts,true)."<br>\n";
    #echo "DEBUG: defaults=".print_r($defaults,true)."<br>\n";
    #echo "DEBUG: WikiShDb=".print_r($WikiShDb,true)."<br>\n";
    $opts = array_merge($WikiShDb, $defaults, $dbdef_opts, $opts);
    #echo "DEBUG: AFTER opts=".print_r($opts,true)."<br>\n";
    if (!@$opts['dsn']) {
        switch ($opts['engine']) {
        case 'mysql':
            if (!$opts['database'])
                wshStdErr($pagename, $opts, "ERROR: MySQL database not specified in database");
            if (!$opts['database'])
                wshStdErr($pagename, $opts, "ERROR: MySQL host not specified in dbhost");
            $opts['dsn'] = "mysql:dbname=$opts[database];host=$opts[dbhost]";
            break;
        case 'sqlite':
        default:
            if (!$opts['database']) {
                if (@$opts['engine'])
                    wshStdErr($pagename, $opts, "ERROR: SQLite file not specified in database ($opts[engine])");
                else
                    wshStdErr($pagename, $opts, "ERROR: Database engine not configured");
                return false;
            }
            $opts['dsn'] = "sqlite:$opts[database]";
            break;
        }
    }
    wdbg(3,"$func: dsn=$opts[dsn]");

    ##
    # Now open the database
    ##
    try {
        # Note that $opts['dbopts'] can be set only via config.php when
        # you set up $WikiShDb['dbopts'].
        $dbh = new PDO($opts['dsn'], $opts['username'], $opts['password'], $opts['dbopts']);
    } catch (PDOException $e) {
        wshStdErr($pagename, $opts, "ERROR: Connection failed: " . $e->getMessage() . "<br>(Error occurred while attempting to open dsn=$opts[dsn])");
        return false;
    }
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $opts['dbh'] = $dbh;
    return true;
}

# This function takes certain meta-options (--csv, --table, etc.) and
# "fills them out" to say what the --IFS or --IRS options should be.
function wshdbOptSynonyms($pagename, $opts, $leave_db_config=true)
{
    if (!$leave_db_config) {
        #echo "BEFORE: opts=<pre>".print_r($opts,true)."</pre><br>\n";
        $opts = array_diff_key($opts, array('database'=>0, 'dbhost'=>0, 'dsn'=>0, 'username'=>0, 'password'=>0, 'engine'=>0, 'filename'=>0, 'dbopts'=>0));
        #echo "AFTER: opts=<pre>".print_r($opts,true)."</pre><br>\n";
    }
    unset($opts['']); // unused
    unset($opts['#']); // unused
    if ($opts['ors']) { 
        SDV($opts['newline'], $opts['ors']);
        unset($opts['ors']);
    }
    if (@$opts['csv']) {
        SDVA($opts, array(
            'icsv'=>true,
            'ocsv'=>true
            ));
        #unset($opts['csv']); // don't unset this - we check for it later
    }
    if (@$opts['ocsv']) {
        SDVA($opts, array(
            'OFS'=>',',
            'line_prefix'=>'',
            'line_suffix'=>'',
            'newline'=>"\n"
            ));
        #unset($opts['ocsv']); // don't unset this - we check for it later
    }
    if (@$opts['icsv']) {
        SDVA($opts, array(
            'IFS'=>',',
            'line_prefix'=>'',
            'line_suffix'=>'',
            'IRS'=>"\n"
            ));
        #unset($opts['icsv']); // don't unset this - we check for it later
    }
    if (@$opts['table']) {
        $opts['itable'] = true;
        $opts['otable'] = true;
        unset($opts['table']);
    }
    if (@$opts['itable']) {
        SDVA($opts, array(
            'IFS'=>'/\|\|/',
            'line_prefix'=>'||',
            'line_suffix'=>'||',
            'IRS'=>"\n"
            ));
        unset($opts['itable']);
    }
    if (@$opts['otable']) {
        SDVA($opts, array(
            'OFS'=>'||',
            'line_prefix'=>'||',
            'line_suffix'=>'||',
            'newline'=>"\n"
            ));
        unset($opts['otable']);
    }
    if ($opts['FS']) {
        SDV($opts['IFS'], $opts['FS']);
        SDV($opts['OFS'], $opts['FS']);
        unset($opts['FS']);
    }
    if ($opts['RS']) {
        SDV($opts['IRS'], $opts['RS']);
        SDV($opts['newline'], $opts['RS']);
        unset($opts['RS']);
    }
    if ($opts['filename']) {
        SDV($opts['database'], $opts['filename']);
        unset($opts['filename']);
    }
    if ($opts['read0']) {
        SDVA($opts, array(
            'IFS'=>CHR(1),
            'IRS'=>CHR(2),
            'line_prefix'=>'',
            'line_suffix'=>''
            ));
        unset($opts['read0']);
    }
    if ($opts['print0']) {
        SDVA($opts, array(
            'OFS'=>CHR(1),
            'newline'=>CHR(2),
            'line_prefix'=>'',
            'line_suffix'=>''
            ));
        unset($opts['print0']);
    }
    foreach (array('IFS', 'OFS', 'IRS', 'newline', 'line_prefix', 'line_suffix') as $i)
        if (isset($opts[$i])) $opts[$i] = str_replace('\n', "\n", $opts[$i]);
    #echo "DEBUG: returning opts=".print_r($opts,true)."<br>\n";
    return $opts;
}

#
# Allow SELECT statements via Markup Expressions
#
$MarkupExpr["SQLselect"] = 'wshdbSQLexec($pagename, "select", @$argp, @$args)';
#
# Allow INSERT/UPDATE statements via Markup Expressions
# (also COMMIT, etc.)
#
$MarkupExpr["SQLdelete"] = 'wshdbSQLexec($pagename, "exec", @$argp, @$args)'; 
$MarkupExpr["SQLupdate"] = 'wshdbSQLexec($pagename, "exec", @$argp, @$args)'; 
$MarkupExpr["SQLinsert"] = 'wshdbSQLexec($pagename, "exec", @$argp, @$args)'; 
$MarkupExpr["SQLexec"] = 'wshdbSQLexec($pagename, "exec", @$argp, @$args)'; 
function wshdbSQLexec($pagename, $mode, $opts, $args) {
    global $WikiShVars, $WikiShPipeActive, $WikiShPipeText;

    if (wshNotNow($pagename)) return('');
    $func="SQLexec()";
    wdbg(4,"$func: Entering");
    wdbg(1,$args);
    wshInitOpts($pagename, '', $opts, $args, true, false); // don't process pipe - we handle that manually below

    #echo "DEBUG: args=<pre>".print_r($args,true)."</pre><br>\n";
    if (@$opts['sql']) {
        // SQL statement was specified in-line, must have SQL auth on current pg
        $SQL_defs = array($pagename); // will only be used for authorization now
    } else {
        $SQL_defs = array(array_shift($args)); // 1st arg contains SQL def
    }
    wdbg(3, "$func: SQL_defs=".print_r($SQL_defs,true));
    wshExpandWildCards($pagename, $opts, $SQL_defs, false, false, false);
    $SQL_def = array_shift($SQL_defs);
    wdbg(3,"$func: SQL_def=$SQL_def");
    if (wshdbInitialize($pagename, $SQL_def, $opts)) {
        #echo "DEBUG: final opts=<pre>".print_r($opts,true)."</pre><br>\n";
        $dbh = $opts['dbh'];
    } else {
        wshStdErr($pagename, $opts, "ERROR: Cannot Initialize WikiShDb (see above)");
        $WikiShVars['STATUS'] = 1;
        return false;
    }
    wdbg(3,"$func: SQL=$opts[sql]");
    try {
        $stmt = $dbh->prepare($opts['sql']);
    } catch (PDOException $e) {
        wshStdErr($pagename, $opts, "ERROR: Statement preparation failed: " . $e->getMessage() . "<br>(Error occurred while attempting to prepare sql=$opts[sql])");
        $stmt = false;
    }
    if ($stmt === false) {
        wshStdErr($pagename, $opts, "ERROR: Invalid SQL ($opts[sql])");
        $WikiShVars['STATUS'] = 1;
        return false;
    }
    $macros = array();
    foreach (preg_grep("/^_/", array_keys($opts)) as $k) // named params
        $macros[':'.substr($k,1)] = $opts[$k];
    if (!$macros) $macros = $args; // positional params
    if ($macros) { 
        $macros = array($macros); // convert to array of array for below
    } else { // OK, try looking for stdin (either from pipe or redirected)
        if ($WikiShPipeActive) {
            $text = $WikiShPipeText;
            $WikiShPipeActive = $WikiShPipeText = false; // get rid of pipe
        } elseif ($opts['stdin']) {
            if (!wshIsAWikiPage('', $opts['stdin']) && !wshIsATextFile('', $opts['stdin']) && !wshIsASessionFile('', $opts['stdin']) && !wshIsASessionGroup($opts['stdin']))
                $opts['stdin'] = WIKIPAGEID . $opts['stdin'];
            $page = wshReadPage($pagename, $opts, $opts['stdin']);
            $text = $page['text'];
        }
        // OK, we've got text - now split it up...
        #echo "DEBUG: Checking for CSV<br>\n";
        if ($opts['csv'] || $opts['icsv']) {
        #echo "DEBUG: Found CSV<br>\n";
            #echo "DEBUG: CSV text=<pre>$text</pre><br>\n";
            $macros = parse_csv($text, $opts);
            #echo "DEBUG: CSV rows=<pre>".print_r($macros,true)."</pre><br>\n";
        } else {
            $rows = array();
            #echo "DEBUG: SQLexec(): IRS=$opts[IRS], IFS=$opts[IFS]<br>\n";
            if ($opts['IRS']{0} == '/')
                $tmprows = preg_split($opts['IRS'], $text);
            else
                $tmprows = explode($opts['IRS'], $text);
            foreach ($tmprows as $row)
                if (trim($row)) {
                    if ($opts['IFS']{0} == '/')
                        $rows[] = preg_split($opts['IFS'], $row);
                    else
                        $rows[] = explode($opts['IFS'], $row);
                }
            $macros = $rows;
        }
    }
    if (!$macros) { // OK, just run it once - must not be any parms
        $macros = array(array());
    }
    $rtn = true;
    $count = 0;
    $out = array();
    #echo "DEBUG: sql=$opts[sql], macros=<pre>".print_r($macros,true)."</pre><br>\n";
    wdbg(3,"$func: looping with macros=".print_r($macros,true));
    foreach ($macros as $macro) {
        wdbg(3,"$func: macro=".print_r($macro,true));
        try {
            $rtn = $stmt->execute($macro);
        } catch (PDOException $e) {
            wshStdErr($pagename, $opts, "ERROR: Statement execution failed: " . $e->getMessage() . "<br>(Error occurred while attempting to execute sql=$opts[sql], macro=".print_r($macro,true).")<br>\n");
            $rtn = false;
        }
        if ($rtn === false) {
            break;
        }
        if ($mode == 'select') {
            $stmt->setFetchMode(PDO::FETCH_ASSOC);
            $Results = $stmt->fetchAll();
            foreach ($Results as $row){
                if (!$row) continue; // PDO returns FALSE if no results
                #echo "DEBUG: $func: row=".print_r( $row,true)."<br>\n";
                wdbg(2,"$func: OFS=$opts[OFS], newline=".wshDbgOd($opts[newline]).", row=".print_r( $row,true));
                $out[] = csv_implode($opts, $row);
                $count++;
            }
            $page = array('filename'=>''); // junk for calls to wshReplace()
            if ($opts['file_prefix'])
                $array_prefix = array(wshReplace($opts, $page, $opts['file_prefix']));
            else
                $array_prefix = array();
            $line_prefix = wshReplace($opts, $page, $opts['line_prefix']);
            $line_suffix = wshReplace($opts, $page, $opts['line_suffix']);
            if ($line_prefix || $line_suffix)
                for ($i = 0; $i < sizeof($out); $i++) {
                    if (strstr($opts['line_prefix'], 'LINENO')) 
                        $line_prefix = wshReplace($opts, $page, $opts['line_prefix'], $i+1);
                    $out[$i] = $line_prefix . $out[$i] . $line_suffix;
                }
            $out = array_merge($array_prefix, $out);
        } else {
            $count += $stmt->rowCount();
        }
    }
    if ($rtn !== false) {
        $WikiShVars['STATUS'] = 0;
        $WikiShVars['SQL_COUNT'] = $count;
        $WikiShVars['SQL_PLURAL'] = ($count == 1 ? '' : 's'); // simplistic but helpful
    } else {
        $WikiShVars['STATUS'] = 2;
        $WikiShVars['SQL_COUNT'] = 0;
        $WikiShVars['SQL_PLURAL'] = 's';
    }
    return (wshPostProcess($pagename, $opts, $out, $page));
}

# csv_implode() will handle basic CSV output, etc.
# It respects 
#    $opts['OFS'] (output field separator, usually comma)
#    $opts['enclosure'] (usually double-quotes)
#    $opts['newline'] (usually "\n")
#    $opts['escape'] (usually backslash)
#    $opts['csv'] or $opts['ocsv'] - must be set to actually do csv output
function csv_implode($opts, $fields)
{
    #echo "csv_implode: entering<br>\n";
    if ($opts['csv'] || $opts['ocsv']) {
    #echo "csv_implode: in CSV<br>\n";
        $rtn = '';
        foreach ($fields as $f) {
            #echo "csv_implode: f=$f<br>\n";
            if ($rtn) $rtn .= $opts['OFS'];
            $need_enclosure = false;
            foreach (array($opts['OFS'], $opts['enclosure'], $opts['newline'], $opts['escape']) as $char) {
                if ($char && (strpos($f, $char) !== false)) {
                    $need_enclosure = true;
                    break;
                }
            }
            if ($need_enclosure) {
                $rtn .= $opts['enclosure'].str_replace($opts['enclosure'], $opts['escape'].$opts['enclosure'], $f).$opts['enclosure'];
            } else {
                $rtn .= $f;
            }
        }
    } else {
        $rtn = implode($opts['OFS'], $fields);
    }
    #echo "csv_implode: returning \"$rtn\"<br>\n";
    return $rtn;
}

# parse_csv()
# take a string ($str) and return a 2-dimensional array, rows of cols
function parse_csv($str, $opts)
{
    SDV($opts['len'], 4096);
    #echo "DEBUG: parse_csv: opts=<pre>".print_r($opts,true)."</pre><br>\n";
    #echo "DEBUG: parse_csv: str=<pre>$str</pre><br>\n";
  $fh = fopen('php://memory', 'rw');
  fwrite($fh, $str);
  rewind($fh);
  $results=array();
  while ($oneline = fgetcsv( $fh, $opts['len'], $opts['IFS'], $opts['enclosure'], $opts['escape'])) {
      #echo "DEBUG: oneline=<pre>".print_r($oneline,true)."</pre><br>\n";
      #echo "DEBUG: oneline[0]=".wshDbgOd($oneline[0])."<br>\n";
      if ($oneline && $oneline !== array(null)) $results[] = $oneline;
  }
  fclose($fh);
  return $results;
}