<?php
/**
* This file is an example of an application with Snippet class.
* Distribution is intended for education / studying purposes only.
*
* Copyright [2020] [Wim Niemans <niemans@pbsolo.nl>]
*/
include_once '../Snippet.class.php';
/**
* @author wim niemans, Rotterdam, Bonn
* @license EUPL
*/
class sqlSnippet {
public $name = ''; // name, will be used for tVar's
private const SNIPPETS =
[
'UPDATE' => 'UPDATE {which} SET {what} {where} {orderBy} {limit}', // general UPDATE
'DELETE' => 'DELETE FROM {which} {where} {orderBy} {limit}', // general DELETE
'INSERT' => 'INSERT INTO {which} SET {what}', // general INSERT
'CREATE' => 'INSERT INTO {which} ({cols}) VALUES ({values})', // alternative INSERT syntax
'MODIFY' => 'REPLACE INTO {which} SET {what}', // alternative UPDATE syntax, general REPLACE
'REPLACE' => 'REPLACE INTO {which} ({cols}) VALUES ({values})', // alternative UPDATE syntax, alternative REPLACE syntax
'LOOKUP' => 'SELECT {what} FROM {which} {where}', // general LOOKUP: getOne item
'SUMMARY' => 'SELECT DISTINCT {what} FROM {which} {where}', // get DISTINCT items
'COUNT' => 'SELECT COUNT(*) AS rowCount FROM {which} {where}', // get COUNT rows
];
protected $sql = ''; // complete sql statement
private $snippet = '';
/**
* syntax elements : where, orderBy, Limit
*/
public $orderBy = ''; // optional orderBy colNames
public $limit = ''; // optional Limit clause
public $where = ''; // where conditions, condensed from $this->when, optional extended by addPredicate()
public function __construct($command)
{
$name = strtoupper($command);
$this->name = 'command::' . $name;
$this->snippet = self::SNIPPETS[$command];
}
public function getSQL()
{
return $this->sql;
}
/**
* creates a sql statement
*
* @param array $what column names
* @param array $values column values :note: string values should contain proper quoteing
* @returns string sql statement
*/
public function createSql($what = array(), $values = array())
{
$templet = new Snippet('remove');
$templet->setVar('snippet', $this->snippet);
$templet->setVar('which', $this->which);
if (!empty($what)) {
if (!is_array($what)) {
$templet->setVar('what', $what);
} elseif (array_diff_key($what, array_keys(array_keys($what)))) { // is assoc ?
$templet->setVar('cols', implode(', ', array_keys($what)));
$templet->setVar('values', implode(', ', array_values($what)));
} elseif (!empty($values)) {
$templet->setVar('cols', implode(', ', $what));
$templet->setVar('values', implode(', ', $values));
} else {
$templet->setVar('what', implode(', ', $what));
}
}
if (!empty($this->where)) { $where = ' WHERE ' .$this->where; }
if (!empty($this->orderBy)) { $orderBy = ' ORDER BY ' .$this->orderBy; } // $this->guessOrderBy();
if (!empty($this->limit)) { $limit = ' LIMIT ' .$this->limit; } // $this->guessLimit();
$templet->setVar('where', $where);
$templet->setVar('orderBy', $orderBy); // $this->guessOrderBy();
$templet->setVar('limit', $limit); // $this->guessLimit();
$this->sql = $templet->parse('output', 'snippet');
$this->sql = $templet->tidy('output');
return $this->getSQL();
}
/**
* sets which: sqlTable name
*/
public function setWhich($which)
{
$this->which = $which;
}
public function addWhere($_string, $how = 'AND')
{
if (!empty($_string)) {
$string = $this->makeNative($_string);
if (empty($this->where)) { $this->where = $string; }
else { $this->where .= ' ' .$how. ' (' .$string. ')'; }
}
}
public function setLimit($limit)
{
$this->limit = $limit;
}
public function setOrderBy($orderBy)
{
if (!empty($this->orderBy)) { $this->orderBy .= ', '; }
$this->orderBy .= $orderBy;
}
} // end of sqlSnippet class
?>
|
Download
