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");
}
public function RunRowFilter(&$row, &$filteropts, &$fetchnext)
{
switch ($filteropts["mode"]) {
case "SHOW DATABASES":
if ($row !== false) {
$row->name = $row->Database;
unset($row->Database);
}
break;
case "SHOW TABLES":
if ($row !== false) {
foreach ($row as $key => $val) {
if (substr($key, 0, 10) == "Tables_in_") {
$row->name = $val;
unset($row->{$key});
}
}
}
break;
case "SHOW CREATE DATABASE":
if ($row !== false) {
$opts = array($row->Database);
$str = $row->{"Create Database"};
$pos = strpos($str, "/*!40100");
if ($pos !== false) {
$pos2 = strpos($str, "*/", $pos);
if ($pos2 !== false) {
$str = " " . trim(substr($str, $pos + 8, $pos2 - $pos - 8)) . " ";
$pos = strpos($str, " CHARACTER SET ");
if ($pos !== false) {
$pos += 15;
$pos2 = strpos($str, " ", $pos);
if ($pos2 !== false) {
$opts["CHARACTER SET"] = substr($str, $pos, $pos2 - $pos);
}
}
$pos = strpos($str, " COLLATE ");
if ($pos !== false) {
$pos += 9;
$pos2 = strpos($str, " ", $pos);
if ($pos2 !== false) {
$opts["COLLATE"] = substr($str, $pos, $pos2 - $pos);
}
}
}
}
$row->cmd = "CREATE DATABASE";
$row->opts = $opts;
}
break;
case "SHOW CREATE TABLE":
if ($row !== false) {
$opts = array($row->Table, array(), array());
$str = $row->{"Create Table"};
if (strtoupper(substr($str, 0, 23)) == "CREATE TEMPORARY TABLE ") {
$opts["TEMPORARY"] = true;
}
$pos = stripos($str, " TABLE ");
if ($pos !== false) {
$str = substr($str, $pos + 7);
$ident = true;
$id = $this->ExtractIdentifier($str, $ident);
$str = trim($str);
if ($str[0] == "(") {
$str = trim(substr($str, 1));
// Process columns and keys.
$colextras = array("NOT NULL", "NULL", "DEFAULT", "AUTO_INCREMENT", "UNIQUE KEY", "UNIQUE", "PRIMARY KEY", "PRIMARY", "COMMENT", "REFERENCES");
$keytypes = array("CONSTRAINT" => true, "INDEX" => true, "KEY" => true, "FULLTEXT" => true, "SPATIAL" => true, "PRIMARY" => true, "UNIQUE" => true, "FOREIGN" => true, "CHECK" => true);
$keyextras = array("PRIMARY KEY", "INDEX", "KEY", "UNIQUE INDEX", "UNIQUE KEY", "UNIQUE", "FULLTEXT INDEX", "FULLTEXT KEY", "FULLTEXT", "SPATIAL INDEX", "SPATIAL KEY", "SPATIAL", "FOREIGN KEY", "CHECK");
while ($str != "" && $str[0] != ")") {
$id = $this->ExtractIdentifier($str, $ident);
if ($ident || !isset($keytypes[strtoupper($id)])) {
// Column. Extract data type and options/values.
$pos = strpos($str, "(");
$pos2 = strpos($str, " ");
if ($pos === false) {
$pos = strlen($str);
}
if ($pos2 === false) {
$pos2 = strlen($str);
}
if ($pos < $pos2) {
$type = substr($str, 0, $pos);
$str = trim(substr($str, $pos + 1));
$typeopts = array();
while ($str != "" && $str[0] != ")") {
$typeopts[] = $this->ExtractAndUnescapeValue($str);
if ($str != "" && $str[0] == ",") {
$str = trim(substr($str, 1));
}
}
$str = trim(substr($str, 1));
} else {
$type = substr($str, 0, $pos2);
$str = trim(substr($str, $pos2 + 1));
$typeopts = array();
}
// Process data type. Stop processing on an unknown column type.
$extras = array();
switch (strtoupper($type)) {
case "BIT":
$opts2 = array("INTEGER");
if (!count($typeopts) || $typeopts[0] <= 8) {
$opts2[] = 1;
} else {
if ($typeopts[0] <= 16) {
$opts2[] = 2;
} else {
if ($typeopts[0] <= 24) {
$opts2[] = 3;
} else {
if ($typeopts[0] <= 32) {
$opts2[] = 4;
} else {
$opts2[] = 8;
}
}
}
}
$opts2["UNSIGNED"] = true;
break;
case "TINYINT":
$opts2 = array("INTEGER", 1);
$extras = array("UNSIGNED" => "keep", "ZEROFILL" => "ignore");
break;
case "SMALLINT":
$opts2 = array("INTEGER", 2);
$extras = array("UNSIGNED" => "keep", "ZEROFILL" => "ignore");
break;
case "MEDIUMINT":
$opts2 = array("INTEGER", 3);
$extras = array("UNSIGNED" => "keep", "ZEROFILL" => "ignore");
break;
case "INT":
case "INTEGER":
$opts2 = array("INTEGER", 4);
$extras = array("UNSIGNED" => "keep", "ZEROFILL" => "ignore");
break;
case "BIGINT":
$opts2 = array("INTEGER", 8);
$extras = array("UNSIGNED" => "keep", "ZEROFILL" => "ignore");
break;
case "DOUBLE":
case "REAL":
$opts2 = array("FLOAT", 8);
$extras = array("UNSIGNED" => "ignore", "ZEROFILL" => "ignore");
break;
case "FLOAT":
$opts2 = array("FLOAT", 4);
$extras = array("UNSIGNED" => "ignore", "ZEROFILL" => "ignore");
break;
case "DECIMAL":
case "NUMERIC":
$opts2[] = array("DECIMAL", count($typeopts) ? $typeopts[0] : 10, count($typeopts) > 1 ? $typeopts[1] : 0);
$extras = array("UNSIGNED" => "ignore", "ZEROFILL" => "ignore");
break;
case "DATE":
$opts2 = array("DATE");
break;
case "TIME":
$opts2 = array("TIME");
break;
case "DATETIME":
case "TIMESTAMP":
$opts2 = array("DATETIME");
break;
case "YEAR":
$opts2 = array("INTEGER", 4);
break;
case "CHAR":
$opts2 = array("STRING", 1, count($typeopts) ? $typeopts[0] : 255, "FIXED" => true);
$extras = array("CHARACTER SET" => "ignore_with_opt", "COLLATE" => "ignore_with_opt");
break;
case "VARCHAR":
$opts2 = array("STRING", $typeopts[0] > 255 ? 2 : 1, $typeopts[0]);
$extras = array("CHARACTER SET" => "ignore_with_opt", "COLLATE" => "ignore_with_opt");
break;
case "TINYTEXT":
$opts2 = array("STRING", 1, 255);
$extras = array("BINARY" => "ignore", "CHARACTER SET" => "ignore_with_opt", "COLLATE" => "ignore_with_opt");
break;
case "TEXT":
$opts2 = array("STRING", 2);
$extras = array("BINARY" => "ignore", "CHARACTER SET" => "ignore_with_opt", "COLLATE" => "ignore_with_opt");
break;
case "MEDIUMTEXT":
$opts2 = array("STRING", 3);
$extras = array("BINARY" => "ignore", "CHARACTER SET" => "ignore_with_opt", "COLLATE" => "ignore_with_opt");
break;
case "LONGTEXT":
$opts2 = array("STRING", 4);
$extras = array("BINARY" => "ignore", "CHARACTER SET" => "ignore_with_opt", "COLLATE" => "ignore_with_opt");
break;
case "ENUM":
$opts2 = array("STRING", 1, 255);
$extras = array("CHARACTER SET" => "ignore_with_opt", "COLLATE" => "ignore_with_opt");
break;
case "SET":
$opts2 = array("STRING", 2);
$extras = array("CHARACTER SET" => "ignore_with_opt", "COLLATE" => "ignore_with_opt");
break;
case "BINARY":
$opts2 = array("BINARY", 1, count($typeopts) ? $typeopts[0] : 255, "FIXED" => true);
break;
case "VARBINARY":
$opts2 = array("BINARY", $typeopts[0] > 255 ? 2 : 1, $typeopts[0]);
break;
case "TINYBLOB":
$opts2 = array("BINARY", 1, 255);
break;
case "BLOB":
$opts2 = array("BINARY", 2);
break;
case "MEDIUMBLOB":
$opts2 = array("BINARY", 3);
break;
case "LONGBLOB":
$opts2 = array("BINARY", 4);
break;
default:
return;
}
do {
$found = false;
foreach ($extras as $extra => $rule) {
if (strtoupper(substr($str, 0, strlen($extra))) == $extra) {
$str = trim(substr($str, strlen($extra)));
if ($rule == "keep") {
$opts2[$extra] = true;
} else {
if ($rule == "ignore_with_opt") {
$this->ExtractIdentifier($str, $ident);
}
}
$found = true;
}
}
} while ($found);
while ($str != "" && $str[0] != "," && $str[0] != ")") {
foreach ($colextras as $extra) {
if (strtoupper(substr($str, 0, strlen($extra))) == $extra) {
$str = trim(substr($str, strlen($extra)));
switch ($extra) {
case "NOT NULL":
$opts2["NOT NULL"] = true;
break;
case "NULL":
break;
case "DEFAULT":
$opts2["DEFAULT"] = $this->ExtractAndUnescapeValue($str);
break;
case "AUTO_INCREMENT":
$opts2["AUTO INCREMENT"] = true;
break;
case "UNIQUE KEY":
case "UNIQUE":
$opts2["UNIQUE KEY"] = true;
break;
case "PRIMARY KEY":
case "PRIMARY":
$opts2["PRIMARY KEY"] = true;
break;
case "COMMENT":
$opts2["COMMENT"] = $this->ExtractAndUnescapeValue($str);
break;
case "REFERENCES":
$opts2["REFERENCES"] = $this->ExtractReferencesInfo($str);
break;
}
}
}
}
if (isset($filteropts["hints"]) && isset($filteropts["hints"][$id])) {
$opts2 = $filteropts["hints"][$id];
}
$opts[1][$id] = $opts2;
} else {
// Key or constraint.
$opts2 = array();
if (strtoupper($id) == "CONSTRAINT") {
$id = $this->ExtractIdentifier($str, $ident);
$id2 = strtoupper($id);
if ($ident || !isset($keytypes[strtoupper($id)])) {
$opts2["CONSTRAINT"] = $id;
$id = $this->ExtractIdentifier($str, $ident);
}
}
$str = $id . " " . $str;
foreach ($keyextras as $extra) {
if (strtoupper(substr($str, 0, strlen($extra))) == $extra) {
$str = trim(substr($str, strlen($extra)));
switch ($extra) {
case "PRIMARY KEY":
$opts2[] = "PRIMARY";
while ($str != "" && $str[0] != "(") {
$id = $this->ExtractIdentifier($str, $ident);
if (!$ident && strtoupper($id) == "USING") {
$opts2["USING"] = $this->ExtractIdentifier($str, $ident);
}
}
$opts2[] = $this->ExtractMultipleIdentifiers($str);
break;
case "INDEX":
case "KEY":
$opts2[] = "KEY";
while ($str != "" && $str[0] != "(") {
$id = $this->ExtractIdentifier($str, $ident);
if (!$ident && strtoupper($id) == "USING") {
$opts2["USING"] = $this->ExtractIdentifier($str, $ident);
} else {
$opts2["NAME"] = $id;
}
}
$opts2[] = $this->ExtractMultipleIdentifiers($str);
break;
case "UNIQUE INDEX":
case "UNIQUE KEY":
case "UNIQUE":
$opts2[] = "UNIQUE";
while ($str != "" && $str[0] != "(") {
$id = $this->ExtractIdentifier($str, $ident);
if (!$ident && strtoupper($id) == "USING") {
$opts2["USING"] = $this->ExtractIdentifier($str, $ident);
} else {
$opts2["NAME"] = $id;
}
}
$opts2[] = $this->ExtractMultipleIdentifiers($str);
break;
case "FULLTEXT INDEX":
case "FULLTEXT KEY":
case "FULLTEXT":
$opts2[] = "FULLTEXT";
while ($str != "" && $str[0] != "(") {
$opts2["NAME"] = $this->ExtractIdentifier($str, $ident);
}
$opts2[] = $this->ExtractMultipleIdentifiers($str);
break;
case "SPATIAL INDEX":
case "SPATIAL KEY":
case "SPATIAL":
// GIS is not portable.
while ($str != "" && $str[0] != "(") {
$this->ExtractIdentifier($str, $ident);
}
$this->ExtractMultipleIdentifiers($str);
break;
case "FOREIGN KEY":
$opts2[] = "FOREIGN";
while ($str != "" && $str[0] != "(") {
$opts2["NAME"] = $this->ExtractIdentifier($str, $ident);
}
$opts2[] = $this->ExtractMultipleIdentifiers($str);
$opts2[] = $this->ExtractReferencesInfo($str);
break;
case "CHECK":
$pos = strpos($str, ")");
$str = substr($str, $pos + 1);
break;
}
}
}
while ($str != "" && $str[0] != "," && $str[0] != ")") {
$id = $this->ExtractIdentifier($str, $ident);
if (!$ident && strtoupper($id) == "USING") {
$opts2["USING"] = $this->ExtractIdentifier($str, $ident);
}
}
if (isset($opts2[0])) {
$opts[2][] = $opts2;
}
}
if ($str != "" && $str[0] == ",") {
$str = trim(substr($str, 1));
}
$str = trim($str);
}
// Process the last line.
$str = trim(substr($str, 1));
$extras = array("ENGINE", "TYPE", "AUTO_INCREMENT", "AVG_ROW_LENGTH", "DEFAULT CHARACTER SET", "CHARACTER SET", "DEFAULT CHARSET", "CHARSET", "CHECKSUM", "DEFAULT COLLATE", "COLLATE", "COMMENT", "CONNECTION", "DATA DIRECTORY", "DELAY_KEY_WRITE", "INDEX DIRECTORY", "INSERT_METHOD", "MAX_ROWS", "MIN_ROWS", "PACK_KEYS", "PASSWORD", "ROW_FORMAT");
do {
$found = false;
foreach ($extras as $extra) {
if (strtoupper(substr($str, 0, strlen($extra))) == $extra) {
$str = trim(substr($str, strlen($extra)));
if ($str[0] == "=") {
$str = trim(substr($str, 1));
}
switch ($extra) {
case "ENGINE":
case "TYPE":
$opts["ENGINE"] = $this->ExtractAndUnescapeValue($str);
break;
case "DEFAULT CHARACTER SET":
case "CHARACTER SET":
case "DEFAULT CHARSET":
case "CHARSET":
$opts["CHARACTER SET"] = $this->ExtractAndUnescapeValue($str);
break;
case "DEFAULT COLLATE":
case "COLLATE":
$opts["COLLATE"] = $this->ExtractAndUnescapeValue($str);
break;
case "AUTO_INCREMENT":
case "AVG_ROW_LENGTH":
case "CHECKSUM":
case "COMMENT":
case "CONNECTION":
case "DATA DIRECTORY":
case "DELAY_KEY_WRITE":
case "INDEX DIRECTORY":
case "INSERT_METHOD":
case "MAX_ROWS":
case "MIN_ROWS":
case "PACK_KEYS":
case "PASSWORD":
case "ROW_FORMAT":
$this->ExtractAndUnescapeValue($str);
break;
}
$found = true;
}
}
} while ($found);
}
}
$row->cmd = "CREATE TABLE";
$row->opts = $opts;
}
break;
}
parent::RunRowFilter($row, $filteropts, $fetchnext);
}
protected function ProcessKeyDefinition($info)
{
$sql = "";
if (isset($info["CONSTRAINT"])) {
$sql .= "CONSTRAINT " . $info["CONSTRAINT"] . " ";
}
$type = strtoupper($info[0]);
foreach ($info[1] as $num => $field) {
$info[1][$num] = $this->QuoteIdentifier($field);
}
switch ($type) {
case "PRIMARY":
$sql .= "PRIMARY KEY";
$sql .= " (" . implode(", ", $info[1]) . ")";
break;
case "KEY":
// SQLite CREATE TABLE doesn't support regular KEY indexes, but ALTER TABLE does.
break;
case "UNIQUE":
$sql .= "UNIQUE";
$sql .= " (" . implode(", ", $info[1]) . ")";
break;
case "FULLTEXT":
// SQLite doesn't support FULLTEXT indexes.
break;
case "FOREIGN":
$sql .= "FOREIGN KEY";
$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);
}
public function RunRowFilter(&$row, &$filteropts, &$fetchnext)
{
switch ($filteropts["mode"]) {
case "INSERT":
if ($row !== false) {
$key = $filteropts["queryinfo"]["AUTO INCREMENT"];
$this->lastid = $row->{$key};
}
break;
}
if (!$fetchnext) {
parent::RunRowFilter($row, $filteropts, $fetchnext);
}
}
public function RunRowFilter(&$row, &$filteropts, &$fetchnext)
{
switch ($filteropts["mode"]) {
case "INSERT":
// Use the private function provided above to get the Last Inserted ID
$this->lastid = $this->GetOracleInsertID($filteropts["queryinfo"][0]);
break;
}
if (!$fetchnext) {
parent::RunRowFilter($row, $filteropts, $fetchnext);
}
}
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);
}
