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); }