protected function GenerateSQL(&$master, &$sql, &$opts, $cmd, $queryinfo, $args, $subquery)
{
switch ($cmd) {
case "SELECT":
$supported = array("PRECOLUMN" => array("DISTINCT" => "bool", "HIGH_PRIORITY" => "bool", "SUBQUERIES" => true), "FROM" => array("SUBQUERIES" => true), "WHERE" => array("SUBQUERIES" => true), "GROUP BY" => true, "HAVING" => true, "ORDER BY" => true, "LIMIT" => ", ");
return $this->ProcessSELECT($master, $sql, $opts, $queryinfo, $args, $subquery, $supported);
case "INSERT":
$supported = array("PREINTO" => array("LOW_PRIORITY" => "bool", "DELAYED" => "bool", "HIGH_PRIORITY" => "bool", "IGNORE" => "bool"), "SELECT" => true, "BULKINSERT" => true);
return $this->ProcessINSERT($master, $sql, $opts, $queryinfo, $args, $subquery, $supported);
case "UPDATE":
$supported = array("PRETABLE" => array("LOW_PRIORITY" => "bool", "IGNORE" => "bool"), "WHERE" => array("SUBQUERIES" => true), "ORDER BY" => true, "LIMIT" => ", ");
return $this->ProcessUPDATE($master, $sql, $opts, $queryinfo, $args, $subquery, $supported);
case "DELETE":
$supported = array("PREFROM" => array("LOW_PRIORITY" => "bool", "QUICK" => "bool", "IGNORE" => "bool"), "WHERE" => array("SUBQUERIES" => true), "ORDER BY" => true, "LIMIT" => ", ");
return $this->ProcessDELETE($master, $sql, $opts, $queryinfo, $args, $subquery, $supported);
case "SET":
$sql = "SET " . $queryinfo;
return array("success" => true);
case "USE":
$sql = "USE " . $this->QuoteIdentifier($queryinfo);
return array("success" => true);
case "TRUNCATE TABLE":
$master = true;
$sql = "TRUNCATE TABLE " . $this->QuoteIdentifier($queryinfo[0]);
return array("success" => true);
}
return array("success" => false, "error" => \CubicleSoft\CSDB::DB_Translate("Unknown query command '%s'.", $cmd), "errorcode" => "unknown_query_command");
}
protected function GenerateSQL(&$master, &$sql, &$opts, $cmd, $queryinfo, $args, $subquery)
{
switch ($cmd) {
case "SELECT":
$supported = array("PRECOLUMN" => array("DISTINCT" => "bool", "SUBQUERIES" => true), "FROM" => array("SUBQUERIES" => true), "WHERE" => array("SUBQUERIES" => true), "GROUP BY" => true, "HAVING" => true, "ORDER BY" => true, "LIMIT" => " OFFSET ");
return $this->ProcessSELECT($master, $sql, $opts, $queryinfo, $args, $subquery, $supported);
case "INSERT":
$supported = array("PREINTO" => array(), "POSTVALUES" => array("RETURNING" => "key_identifier"), "SELECT" => true, "BULKINSERT" => true);
// To get the last insert ID via GetInsertID(), the field that contains a 'serial' (auto increment) field must be specified.
if (isset($queryinfo["AUTO INCREMENT"])) {
$queryinfo["RETURNING"] = $queryinfo["AUTO INCREMENT"];
}
$this->lastid = 0;
$result = $this->ProcessINSERT($master, $sql, $opts, $queryinfo, $args, $subquery, $supported);
if ($result["success"] && isset($queryinfo["AUTO INCREMENT"])) {
$result["filter_opts"] = array("mode" => "INSERT", "queryinfo" => $queryinfo);
}
return $result;
case "UPDATE":
// No ORDER BY or LIMIT support.
$supported = array("PRETABLE" => array("ONLY" => "bool"), "WHERE" => array("SUBQUERIES" => true));
return $this->ProcessUPDATE($master, $sql, $opts, $queryinfo, $args, $subquery, $supported);
case "DELETE":
// No ORDER BY or LIMIT support.
$supported = array("PREFROM" => array("ONLY" => "bool"), "WHERE" => array("SUBQUERIES" => true));
return $this->ProcessDELETE($master, $sql, $opts, $queryinfo, $args, $subquery, $supported);
case "SET":
$sql = "SET " . $queryinfo;
return array("success" => true);
case "USE":
// Fake multiple databases with PostgreSQL schemas.
// http://www.postgresql.org/docs/7.3/static/ddl-schemas.html
$sql = "SET search_path TO " . ($queryinfo != "" ? $this->QuoteIdentifier($queryinfo) . "," : "") . "\"\$user\",public";
return array("success" => true);
case "TRUNCATE TABLE":
$master = true;
$sql = "TRUNCATE TABLE " . $this->QuoteIdentifier($queryinfo[0]);
return array("success" => true);
}
return array("success" => false, "error" => \CubicleSoft\CSDB::DB_Translate("Unknown query command '%s'.", $cmd), "errorcode" => "unknown_query_command");
}
protected function ProcessKeyDefinition($info)
{
$sql = "";
$type = strtoupper($info[0]);
foreach ($info[1] as $num => $field) {
$info[1][$num] = $this->QuoteIdentifier($field);
}
switch ($type) {
case "PRIMARY":
if (isset($info["CONSTRAINT"])) {
$sql .= "CONSTRAINT " . $info["CONSTRAINT"] . " ";
}
$sql .= "PRIMARY KEY";
$sql .= " (" . implode(", ", $info[1]) . ")";
if (isset($info["OPTION"])) {
$sql .= " " . $info["OPTION"];
}
break;
case "KEY":
$sql .= "KEY";
if (isset($info["NAME"])) {
$sql .= " " . $this->QuoteIdentifier($info["NAME"]);
}
$sql .= " (" . implode(", ", $info[1]) . ")";
if (isset($info["OPTION"])) {
$sql .= " " . $info["OPTION"];
}
break;
case "UNIQUE":
if (isset($info["CONSTRAINT"])) {
$sql .= "CONSTRAINT " . $info["CONSTRAINT"] . " ";
}
$sql .= "UNIQUE KEY";
if (isset($info["NAME"])) {
$sql .= " " . $this->QuoteIdentifier($info["NAME"]);
}
$sql .= " (" . implode(", ", $info[1]) . ")";
if (isset($info["OPTION"])) {
$sql .= " " . $info["OPTION"];
}
break;
case "FULLTEXT":
$sql .= "FULLTEXT";
if (isset($info["NAME"])) {
$sql .= " " . $this->QuoteIdentifier($info["NAME"]);
}
$sql .= " (" . implode(", ", $info[1]) . ")";
if (isset($info["OPTION"])) {
$sql .= " " . $info["OPTION"];
}
break;
case "FOREIGN":
if (isset($info["CONSTRAINT"])) {
$sql .= "CONSTRAINT " . $info["CONSTRAINT"] . " ";
}
$sql .= "FOREIGN KEY";
if (isset($info["NAME"])) {
$sql .= " " . $this->QuoteIdentifier($info["NAME"]);
}
$sql .= " (" . implode(", ", $info[1]) . ")";
$sql .= " REFERENCES " . $this->ProcessReferenceDefinition($info[2]);
break;
default:
return array("success" => false, "error" => \CubicleSoft\CSDB::DB_Translate("Unknown key type '%s'.", $type), "errorcode" => "unknown_key_type");
}
return array("success" => true, "sql" => $sql);
}
protected function GenerateSQL(&$master, &$sql, &$opts, $cmd, $queryinfo, $args, $subquery)
{
$mystr = "test";
switch ($cmd) {
case "SELECT":
$supported = array("PRECOLUMN" => array("DISTINCT" => "bool", "SUBQUERIES" => true), "FROM" => array("SUBQUERIES" => true), "WHERE" => array("SUBQUERIES" => true), "GROUP BY" => true, "HAVING" => true, "ORDER BY" => true);
// Oracle does not support aliasing table names in the FROM clause.
// However, alias' are supported in COLUMN names.
// AS is used in the Oracle FROM clause to process nested queries,
// but does not support alias'.
$queryinfo["FROM"] = str_replace("? AS ", "? ", $queryinfo["FROM"]);
return $this->ProcessSELECT($master, $sql, $opts, $queryinfo, $args, $subquery, $supported);
case "INSERT":
$supported = array("PREINTO" => array(), "POSTVALUES" => array("RETURNING" => "key_identifier"), "SELECT" => true, "BULKINSERT" => false);
$result = $this->ProcessINSERT($master, $sql, $opts, $queryinfo, $args, $subquery, $supported);
if ($result["success"] && isset($queryinfo["AUTO INCREMENT"])) {
$result["filter_opts"] = array("mode" => "INSERT", "queryinfo" => $queryinfo);
}
// Handle bulk insert by rewriting the queries because, well, Oracle.
// http://stackoverflow.com/questions/39576/best-way-to-do-multi-row-insert-in-oracle
if ($result["success"] && is_array($sql)) {
$sql2 = "INSERT ALL";
foreach ($sql as $entry) {
$sql2 .= substr($entry, 6);
}
$sql2 .= " SELECT 1 FROM DUAL";
$sql = $sql2;
}
return $result;
case "UPDATE":
// No ORDER BY or LIMIT support.
$supported = array("PRETABLE" => array("ONLY" => "bool"), "WHERE" => array("SUBQUERIES" => true));
return $this->ProcessUPDATE($master, $sql, $opts, $queryinfo, $args, $subquery, $supported);
case "DELETE":
// No ORDER BY or LIMIT support.
$supported = array("PREFROM" => array("ONLY" => "bool"), "WHERE" => array("SUBQUERIES" => true));
return $this->ProcessDELETE($master, $sql, $opts, $queryinfo, $args, $subquery, $supported);
case "SET":
$sql = "ALTER SESSION SET " . $queryinfo;
return array("success" => true);
case "USE":
// Fake multiple databases with Oracle schemas.
// SCHEMA is already selected with user
// $sql = "SELECT 1 FROM DUAL";
return array("success" => false, "errorcode" => "skip_sql_query");
case "TRUNCATE TABLE":
$master = true;
$sql = "TRUNCATE TABLE " . $this->QuoteIdentifier($queryinfo[0]);
return array("success" => true);
}
return array("success" => false, "error" => \CubicleSoft\CSDB::DB_Translate("Unknown query command '%s'.", $cmd), "errorcode" => "unknown_query_command");
}
protected function ProcessCREATE_TABLE(&$master, &$sql, &$opts, $queryinfo, $args, $subquery, $supported)
{
$master = true;
if (isset($supported["TEMPORARY"]) && isset($queryinfo["TEMPORARY"]) && $queryinfo["TEMPORARY"]) {
$cmd = $supported["TEMPORARY"];
} else {
$cmd = "CREATE TABLE";
}
$prefix = isset($supported["DBPREFIX"]) ? $supported["DBPREFIX"] : "";
$sql = $cmd . " " . $this->QuoteIdentifier($prefix . $queryinfo[0]);
if (isset($queryinfo["SELECT"])) {
if (!isset($supported["AS_SELECT"]) || !$supported["AS_SELECT"]) {
return array("success" => false, \CubicleSoft\CSDB::DB_Translate("CREATE TABLE AS SELECT not supported."), "create_table_select_unsupported");
}
$sql2 = "";
$opts2 = array();
$queryinfo2 = array_shift($queryinfo["SELECT"]);
if (count($queryinfo["SELECT"]) == 1 && is_array($queryinfo["SELECT"][0])) {
$queryinfo["SELECT"] = $queryinfo["SELECT"][0];
}
$result = $this->GenerateSQL($master, $sql2, $opts2, "SELECT", $queryinfo2, $queryinfo["SELECT"], false);
if (!$result["success"]) {
return $result;
}
if (isset($supported["PRE_AS"])) {
foreach ($supported["PRE_AS"] as $key => $mode) {
if (isset($queryinfo[$key])) {
if ($mode == "bool" && $queryinfo[$key]) {
$sql .= " " . $key;
}
}
}
}
$sql .= " AS " . $sql2;
} else {
$sql2 = array();
foreach ($queryinfo[1] as $key => $info) {
$sql3 = $this->QuoteIdentifier($key);
$result = $this->ProcessColumnDefinition($info);
if (!$result["success"]) {
return $result;
}
$sql2[] = $sql3 . $result["sql"];
}
if (isset($supported["PROCESSKEYS"]) && $supported["PROCESSKEYS"] && isset($queryinfo[2]) && is_array($queryinfo[2])) {
foreach ($queryinfo[2] as $info) {
$result = $this->ProcessKeyDefinition($info);
if (!$result["success"]) {
return $result;
}
if ($result["sql"] != "") {
$sql2[] = $result["sql"];
}
}
}
$sql .= " (\n";
if (count($sql2)) {
$sql .= "\t" . implode(",\n\t", $sql2) . "\n";
}
$sql .= ")";
foreach ($supported["POSTCREATE"] as $key => $mode) {
if (isset($queryinfo[$key])) {
if ($mode == "bool" && $queryinfo[$key]) {
$sql .= " " . $key;
} else {
if ($mode == "string") {
$sql .= " " . $key . " " . $queryinfo[$key];
}
}
}
}
}
$opts = $args;
return array("success" => true);
}
