''
,'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
\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]=$page[text]
\n";
#echo "DEBUG: page=".print_r($page,true)."
\n";
#echo "DEBUG: WikiShDb=".print_r($WikiShDb,true)."
\n";
$WikiShDb = wshdbOptSynonyms($pagename, $WikiShDb, true);
if ($dflt_text = TextSection($page['text'], $WikiShDb['default_section'])) {
#echo "DEBUG: dflt_text=$dflt_text
\n";
$dflt_text = preg_replace("/#.*$/", '', $dflt_text); // strip comments
$defaults = wshdbOptSynonyms($pagename, ParseArgs($dflt_text), $wshdbEnableDBConfigOnPage);
#echo "DEBUG: text of defaults=$dflt_text, parsed=".print_r($defaults,true)."
\n";
} else {
#echo "DEBUG: No #default section
\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
\n";
$dbdef_opts = wshdbOptSynonyms($pagename, ParseArgs($opt_text), $wshdbEnableDBConfigOnPage);
$opts['sql'] = trim($sql_statement);
}
#echo "DEBUG: text of $section=$text, parsed=".print_r($dbdef_opts,true)."
\n";
$opts = wshdbOptSynonyms($pagename, $opts, $wshdbEnableDBConfigOnPage);
#echo "DEBUG: opts=".print_r($opts,true)."
\n";
#echo "DEBUG: dbdef_opts=".print_r($dbdef_opts,true)."
\n";
#echo "DEBUG: defaults=".print_r($defaults,true)."
\n";
#echo "DEBUG: WikiShDb=".print_r($WikiShDb,true)."
\n";
$opts = array_merge($WikiShDb, $defaults, $dbdef_opts, $opts);
#echo "DEBUG: AFTER opts=".print_r($opts,true)."
\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() . "
(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=".print_r($opts,true)."
\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=".print_r($opts,true)."
\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)."
\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=".print_r($args,true)."
\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=".print_r($opts,true)."
\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() . "
(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
\n";
if ($opts['csv'] || $opts['icsv']) {
#echo "DEBUG: Found CSV
\n";
#echo "DEBUG: CSV text=$text
\n";
$macros = parse_csv($text, $opts);
#echo "DEBUG: CSV rows=".print_r($macros,true)."
\n";
} else {
$rows = array();
#echo "DEBUG: SQLexec(): IRS=$opts[IRS], IFS=$opts[IFS]
\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=".print_r($macros,true)."
\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() . "
(Error occurred while attempting to execute sql=$opts[sql], macro=".print_r($macro,true).")
\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)."
\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
\n";
if ($opts['csv'] || $opts['ocsv']) {
#echo "csv_implode: in CSV
\n";
$rtn = '';
foreach ($fields as $f) {
#echo "csv_implode: f=$f
\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\"
\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=".print_r($opts,true)."
\n";
#echo "DEBUG: parse_csv: str=$str
\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=".print_r($oneline,true)."
\n";
#echo "DEBUG: oneline[0]=".wshDbgOd($oneline[0])."
\n";
if ($oneline && $oneline !== array(null)) $results[] = $oneline;
}
fclose($fh);
return $results;
}