<?php
/**
* @package myOneLineSQL
*/
/**
*class MyOneLineSQL
*
* A composition of DBQuery and RunDBQuery objects to execute queries in one line of code.
*
* Each method in this class is static and always return a RunDBQuery object.
* The object returned contains info about the query executed which can be retrieved via the protocol of RunDBQuery class
* @package myOneLineSQL
* @author Carlo Tasca
* @version 1.0
*/
class MyOneLineSQL
{
/**
* No constructor
*
* Only static methods. Throws fatal error alerting class cannot have instances
*
*/
public function __construct()
{
trigger_error ( "Class SQLQuery cannot be instantiated.", E_USER_ERROR );
}
/**
* Does SELECT {WHAT} FROM {TABLE_NAME}
*
* Keyword for DBQuery Object: SELECT
*
* @param string $dbTableName
* @param db connection $dbc
* @param array $dbFields
* @return RunDBQuery
*/
public static function doSelect($dbTableName, $dbc, $dbFields = array())
{
$q = new DBQuery ( 'SELECT' );
$s = "";
for($i = 0; $i < count ( $dbFields ); $i ++)
{
if ($i !== (count ( $dbFields ) - 1))
{
$s .= $dbFields [$i] . ", ";
} else
{
$s .= $dbFields [$i] . " ";
}
}
$q->SetParameters ( "WHAT", $s );
$q->SetParameters ( "TABLE_NAME", $dbTableName );
$q->queryBuilder ();
$r = new RunDBQuery ( $q, $dbc );
$r->runQuery ();
return $r;
}
/**
* SELECT {WHAT} FROM {TABLE_NAME} WHERE {WHERE_EXP}
*
* Keyword for DBQuery Object: SELECT_WHERE
*
* @param string $dbTableName
* @param db connection $dbc
* @param string $where
* @param array $dbFields
* @return RunDBQuery object
*/
public static function doSelectWhere($dbTableName, $dbc, $where, $dbFields = array())
{
$q = new DBQuery ( 'SELECT_WHERE' );
$s = "";
for($i = 0; $i < count ( $dbFields ); $i ++)
{
if ($i !== (count ( $dbFields ) - 1))
{
$s .= $dbFields [$i] . ", ";
} else
{
$s .= $dbFields [$i] . " ";
}
}
$q->SetParameters ( "WHAT", $s );
$q->SetParameters ( "TABLE_NAME", $dbTableName );
$q->SetParameters ( "WHERE_EXP", $where );
$q->queryBuilder ();
$q->getThisQuery ();
$r = new RunDBQuery ( $q, $dbc );
$r->runQuery ();
$r->getQueryResultInfo ();
return $r;
}
/**
* Runs SELECT ... ORDER BY query
*
* SELECT {WHAT} FROM {TABLE_NAME} ORDER BY {ORDER_BY} {ASC_DESC}
*
* Keyword for DBQuery Object: SELECT_ORDER
*
* Takes a table name, a dbconnetion, a order expresssion, order mode (ASC, DESC)
* and an array of fields to select as arguments, runs query and returns RunDBQuery object
*
* @param string $dbTableName
* @param dbconnect $dbc
* @param string $order
* @param string $asc_desc
* @param array $dbFields
* @return RunDBQuery
*/
public static function doSelectOrder($dbTableName, $dbc, $order, $asc_desc = "", $dbFields = array())
{
$q = new DBQuery ( 'SELECT_ORDER' );
$s = "";
for($i = 0; $i < count ( $dbFields ); $i ++)
{
if ($i !== (count ( $dbFields ) - 1))
{
$s .= $dbFields [$i] . ", ";
} else
{
$s .= $dbFields [$i] . " ";
}
}
$q->SetParameters ( "WHAT", $s );
$q->SetParameters ( "TABLE_NAME", $dbTableName );
$q->SetParameters ( "ORDER_BY", $order );
$q->SetParameters ( "ASC_DESC", $asc_desc );
$q->queryBuilder ();
$r = new RunDBQuery ( $q, $dbc );
$r->runQuery ();
return $r;
}
/**
* Runs SELECT, WHERE, ORDER BY
*
* SELECT {WHAT} FROM {TABLE_NAME} WHERE {WHERE_EXP} ORDER BY {ORDER_BY} {ASC_DESC}
*
* Keyword for DBQuery Object: SELECT_WHERE_ORDER
*
* Takes a table name, a db connetion reference, a where expression, a order expression, a order mode (ASC, DESC) and an array of fields to select as arguments, runs query and returns RunDBQuery Objet
*
* @param string $dbTableName
* @param dbconnect $dbc
* @param string $where
* @param string $order
* @param string $asc_desc
* @param array $dbFields
* @return RunDBQuery
*/
public static function doSelectWhereOrder($dbTableName, $dbc, $where, $order, $asc_desc = "", $dbFields = array())
{
$q = new DBQuery ( 'SELECT_WHERE_ORDER' );
$s = "";
for($i = 0; $i < count ( $dbFields ); $i ++)
{
if ($i !== (count ( $dbFields ) - 1))
{
$s .= $dbFields [$i] . ", ";
} else
{
$s .= $dbFields [$i] . " ";
}
}
$q->SetParameters ( "WHAT", $s );
$q->SetParameters ( "TABLE_NAME", $dbTableName );
$q->SetParameters ( "WHERE_EXP", $where );
$q->SetParameters ( "ORDER_BY", $order );
$q->SetParameters ( "ASC_DESC", $asc_desc );
$q->queryBuilder ();
$r = new RunDBQuery ( $q, $dbc );
$r->runQuery ();
return $r;
}
/**
* Runs SELECT, WHERE, ORDER BY, LIMIT
*
* SELECT {WHAT} FROM {TABLE_NAME} WHERE {WHERE_EXP} ORDER BY {ORDER_BY} {ASC_DESC} LIMIT {QUERY_LIMITS}
*
* Keyword for DBQuery Object: SELECT_WHERE_ORDER_LIMIT
*
* Takes a table name, a db connection reference, a where expression, a order expression, a limit definition , a order mode (ASC, DESC) and an array of fields to select as arguments, runs the query and returns a RunDBQuery object
*
* @param string $dbTableName
* @param dbconnect $dbc
* @param string $where
* @param string $order
* @param string $limit
* @param string $asc_desc
* @param array $dbFields
* @return uRunDBQuery
*/
public static function doSelectWhereOrderByLimit($dbTableName, $dbc, $where, $order, $limit, $asc_desc = "", $dbFields = array())
{
$q = new DBQuery ( 'SELECT_WHERE_ORDER_LIMIT' );
$s = "";
for($i = 0; $i < count ( $dbFields ); $i ++)
{
if ($i !== (count ( $dbFields ) - 1))
{
$s .= $dbFields [$i] . ", ";
} else
{
$s .= $dbFields [$i] . " ";
}
}
$q->SetParameters ( "WHAT", $s );
$q->SetParameters ( "TABLE_NAME", $dbTableName );
$q->SetParameters ( "WHERE_EXP", $where );
$q->SetParameters ( "ORDER_BY", $order );
$q->SetParameters ( "ASC_DESC", $asc_desc );
$q->SetParameters ( "QUERY_LIMITS", $limit );
$q->queryBuilder ();
$r = new RunDBQuery ( $q, $dbc );
$r->runQuery ();
return $r;
}
/**
* Runs UPDATE ... WHERE
*
* UPDATE {TABLE_NAME} SET {WHICH_SET} WHERE {WHERE_EXP}
*
* Keyword for DBQuery Object: UPDATE
*
* Takes a table name, a db connection reference, a where expression, and array
* of set expressions as argument.
* Each value of sets array is separated by a comma (apart the very last one)
* Runs query and returns RunDBQuery object
*
* @param string $dbTableName
* @param dbconnect $dbc
* @param string $where
* @param array $sets
* @return RunDBQuery
*/
public static function doUpdateWhere($dbTableName, $dbc, $where, $sets = array())
{
$query = new DBQuery ( 'UPDATE' );
$s = "";
for($i = 0; $i < count ( $sets ); $i ++)
{
if ($i !== (count ( $sets ) - 1))
{
$s .= $sets [$i] . ", ";
} else
{
$s .= $sets [$i] . " ";
}
}
$query->SetParameters ( "TABLE_NAME", $dbTableName );
$query->SetParameters ( "WHICH_SET", $s );
$query->SetParameters ( "WHERE_EXP", $where );
$query->queryBuilder ();
$r = new RunDBQuery ( $query, $dbc );
$r->runQuery ();
return $r;
}
/**
* Runs INSERT query
*
* INSERT INTO {TABLE_NAME} VALUES ({WHICH_VALUES})
*
* Keyword for DBQuery Object: INSERT
*
* Takes a table name, db connection reference and an array of values to be inserted as argument, runs the query and returns RunDBQuery object
*
* @param string $dbTableName
* @param dbconnect $dbc
* @param array $values
* @return RunDBQuery
*/
public static function doInsert($dbTableName, $dbc, $values = array())
{
$query = new DBQuery ( 'INSERT' );
$s = "";
for($i = 0; $i < count ( $values ); $i ++)
{
if ($i !== (count ( $values ) - 1))
{
$s .= "'" . $values [$i] . "'" . ", ";
} else
{
$s .= "'" . $values [$i] . "'" . " ";
}
}
$query->SetParameters ( "TABLE_NAME", $dbTableName );
$query->SetParameters ( "WHICH_VALUES", $s );
$query->queryBuilder ();
$query->getThisQuery ();
$r = new RunDBQuery ( $query, $dbc );
$r->runQuery ();
return $r;
}
/**
* Runs CREATE TABLE query
*
* CREATE TABLE {TABLE_STRUCTURE}
*
* Keyword for DBQuery Object: CREATE
*
* Takes a table structure and a database connection reference as arguments
* runs the query and returns RunDBQuery object
*
* @param string $tableStructure
* @param dbconnect $dbc
* @return RunDBQuery
*/
public static function doCreateTable($tableStructure, $dbc)
{
$query = new DBQuery ( 'CREATE' );
$query->SetParameters ( "TABLE_STRUCTURE", $tableStructure );
$query->queryBuilder ();
$r = new RunDBQuery ( $query, $dbc );
$r->runQuery ();
return $r;
}
/**
* Runs ALTER query
*
* ALTER TABLE {TABLE_NAME} ADD {ADD_WHAT} AFTER {AFTER_WHAT}
*
* Keyword for DBQuery Object: ALTER
*
* Runs ALTER query on table name passed as argument.
* Arguments to the method are also a database connection reference,
* the definition of the field to add, and the field after which the new field must be added
*
* @param string $dbTableName
* @param dbconnect $dbc
* @param string $add_what
* @param string $after_what
* @return RunDBQuery
*/
public static function doAlterTable($dbTableName, $dbc, $add_what, $after_what)
{
$query = new DBQuery ( 'ALTER' );
$query->SetParameters ( "TABLE_NAME", $dbTableName );
$query->SetParameters ( "ADD_WHAT", $add_what );
$query->SetParameters ( "AFTER_WHAT", $after_what );
$query->queryBuilder ();
$r = new RunDBQuery ( $query, $dbc );
$r->runQuery ();
return $r;
}
/**
* Runs DROP TABLE query
*
* DROP TABLE {TABLE_NAME}
*
* Keyword for DBQuery Object: DROP_TABLE
*
* Takes a table name and db connection as arguments runs the query and returns RunDBQuery object
*
* @param string $dbTableName
* @param dbconnect $dbc
* @return RunDBQuery
*/
public static function doDropTable($dbTableName, $dbc)
{
$query = new DBQuery ( 'DROP_TABLE' );
$query->SetParameters ( "TABLE_NAME", $dbTableName );
$query->queryBuilder ();
$r = new RunDBQuery ( $query, $dbc );
$r->runQuery ();
return $r;
}
/**
* Runs DELETE FROM, WHERE
*
* DELETE FROM {TABLE_NAME} WHERE {WHERE_EXP}
*
* Keyword for DBQuery Object: DELETE_WHERE
*
* Takes a table name, db connection reference and where expression
* as argument and returns RunDBQuery obejct
*
* @param string $dbTableName
* @param dbconnect $dbc
* @param string $where
* @return RunDBQuery
*/
public static function doDeleteWhere($dbTableName, $dbc, $where)
{
$query = new DBQuery ( 'DELETE_WHERE' );
$query->SetParameters ( "TABLE_NAME", $dbTableName );
$query->SetParameters ( "WHERE_EXP", $where );
$query->queryBuilder ();
$r = new RunDBQuery ( $query, $dbc );
$r->runQuery ();
return $r;
}
/**
* Runs SHOW TABLES query
*
* SHOW TABLES
*
* Keyword for DBQuery Object: SHOW
*
* Takes a db connection reference as argument, runs query and returns RunDBQuery object
*
* @param dbconnect $dbc
* @return RunDBQuery
*/
public static function doShowTables($dbc)
{
$query = new DBQuery ( 'SHOW' );
$query->queryBuilder ();
$r = new RunDBQuery ( $query, $dbc );
$r->runQuery ();
return $r;
}
/**
* Runs OPTIMIZE query
*
* OPTIMIZE TABLE {TABLE_NAME}
*
* Keyword for DBQuery Object: OPTIMIZE
*
* Takes a table name and db connection as arguments, runs query and returns RunDBQuery object
*
* @param string $dbTableName
* @param dbconnect $dbc
* @return RunDBquery
*/
public static function doOptimize($dbTableName, $dbc)
{
$query = new DBQuery ( 'OPTIMIZE' );
$query->SetParameters ( "TABLE_NAME", $dbTableName );
$query->queryBuilder ();
$r = new RunDBQuery ( $query, $dbc );
$r->runQuery ();
return $r;
}
/**
* Runs OPTIMIZE query on every table in database
*
*
* Takes db connection as argument, runs optimize query for each database table in DB
*
* @param dbconnect $dbc
*/
public static function doOptimizeAll($dbc)
{
$query = new DBQuery ( 'SHOW' );
$query->queryBuilder ();
$r = new RunDBQuery ( $query, $dbc );
$r->runQuery ();
$r->getQueryResultArray ();
foreach ( $r->getQueryResultArray () as $value )
{
foreach ( $value as $v )
{
SQLQuery::doOptimize ( $v, $dbc );
}
}
}
}
?>