function mysql2sqlite($query)
{
//converts mysql specific syntax to sqlite syntax
//order of replace is important to optimize stuff
$doReturn = false;
//table struct query
if (preg_match("/^\\s*show\\s+tables/is", $query)) {
$query = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name";
$doReturn = true;
} else {
if (preg_match("/^\\s*drop\\s+table\\s+(if\\s+exists)/is", $query, $matches)) {
$query = str_replace($matches[1], "", $query);
$query = preg_replace("/(\\s+)/is", " ", $query);
// no risk to trim data here
$doReturn = true;
} else {
if (preg_match("/^\\s*alter\\s+table/is", $query, $matches)) {
$query = preg_replace("/(COLUMN)/is", "", $query);
$query = preg_replace("/(\\s+)/is", " ", $query);
// no risk to trim data here
$doReturn = true;
} else {
if (preg_match("/^\\s*create\\s+table/is", $query)) {
/* We are using a class found into SQLLiteManager project!
We need to check the licence of SQLLiteManager and (include it?)
Otherwise, we must rebuild this function by ourself
*/
include_once "ParsingQuery.class.php";
$query = preg_replace("/(\\s+)/is", " ", $query);
// no risk to trim data here
$parse = new ParsingQuery($query, 2);
//2=type mysql
$query = $parse->convertQuery();
$doReturn = true;
}
}
}
}
if ($doReturn || !$query) {
return $query;
}
//count(distinct(x)) is not supported
if (preg_match("/^\\s*select\\s+(count\\s*\\(distinct\\s*\\(([^)]+)\\)\\s*\\))/is", $query, $matches)) {
$field = $matches[2];
$query = str_replace($matches[0], "select count(*)", $query);
$query = preg_replace("/from\\s/is", "from (select distinct({$field}) from ", $query);
$query .= ")";
}
/*
we must be carefull to not change text content but only SQL part
We do a really dirty hack here:
we replace all texts by an REFERENCES that will not interfere into the parsing
$i=0;
$tabStrings=array();
//when note description are too big, doing basic str_replace/preg_replace
//sometime crash... so we try to be less "aggressive"
$len=strlen($query);
$quoteOpen=false;
$j=0;
$new_query=$buffer=$field="";
$tabReplace=array();
for ($i=0;$i<$len;$i++) {
$current=substr($query,$i,1);
if ($i<$len-1) $next=substr($query,$i+1,1);
else $next="";
if ("$current"=="'") {
if (!$quoteOpen) {
if ($next=="'") {
if ($i<$len-2 && substr($query,$i+2,1)!="'") {
$new_query.="''";
$i++;
continue;
}
}
$buffer=$current;
$field="__GREGA_${j}__";
$new_query.=$field;
$quoteOpen=true;
$j++;
}
else if ("$next"=="'") {
$buffer.="''";
$i++;
}
else {
//closing quote
$buffer.=$current;
$tabReplace["$field"]=$buffer;
$buffer="";
$quoteOpen=false;
}
}
else if ($quoteOpen) $buffer.=$current;
else $new_query.=$current;
}
unset($query);
$query=preg_replace("/(\s+)/is"," ",$new_query); // we can now trim data, but not before to prevent a change in values
//we restore all the strings values
if (is_array($tabReplace) && count($tabReplace)>0) {
foreach ($tabReplace as $search=>$replace) {
$query=str_replace($search,$replace,$query);
unset($tabReplace["$search"]);
}
unset($tabReplace);
}
*/
return $query;
}
/**
* Verify and exec query
*
* @access public
*/
function verify($autocommit = true)
{
if (is_resource($this->connId->connId) || is_object($this->connId->connId)) {
if (!empty($GLOBALS['attachDbList'])) {
// attachment of all database
foreach ($GLOBALS['attachDbList'] as $attachDbId) {
$attachQuery = 'ATTACH DATABASE ' . quotes($GLOBALS['attachInfo'][$attachDbId]['location']) . ' AS ' . quotes($GLOBALS['attachInfo'][$attachDbId]['name']) . ';';
$this->execQuery($attachQuery);
}
}
if ($this->query != '') {
$parsing = new ParsingQuery($this->query, isset($_POST['sqltype']) ? $_POST['sqltype'] : 1);
$tabQuery = $parsing->convertQuery();
if (!is_array($tabQuery)) {
$this->multipleQuery = false;
$this->query = $tabQuery;
if (!($result = $this->execQuery($tabQuery))) {
$this->addChanges();
}
return $result;
} else {
$time = 0;
$this->multipleQuery = true;
$this->connId->connId->query('BEGIN TRANSACTION;');
$error = false;
$lineNum = 1;
$this->changesLine = $queryNum = 0;
foreach ($tabQuery as $query) {
if (!empty($query) && substr(trim($query), 0, 1) != '#') {
if ($this->_checkBeginQuery($query)) {
$queryNum++;
if (isset($commitafter) && $commitafter) {
$this->connId->connId->query('COMMIT TRANSACTION;');
$this->connId->connId->query('BEGIN TRANSACTION;');
$commitafter = false;
}
}
if ($this->_checkBeginQuery($query, 'CREATE|DROP') && !preg_match('#^create[[:space:]]database#i', $query)) {
if ($autocommit) {
$commitafter = true;
}
}
if ($this->execQuery($query)) {
$error = true;
$this->lineError[] = $lineNum;
}
$time += $this->queryTime;
$this->addChanges();
$lineNum++;
}
if ($error) {
break;
}
}
if ($error) {
$this->connId->connId->query('ROLLBACK TRANSACTION;');
} else {
$this->connId->connId->query('COMMIT TRANSACTION;');
}
$this->error = $error;
$this->withReturn = false;
$this->nbQuery = $queryNum;
}
} else {
$this->error = true;
$this->errorMessage = $GLOBALS['traduct']->get(64);
}
} else {
$this->error = true;
$this->errorMessage = $GLOBALS['traduct']->get(65);
}
$this->queryTime = isset($time) ? $time : 0;
return $this->error;
}
/**
* highlight query for a proch future!!
*
* @param string $query SQL command string
*/
function highlight_query($query)
{
include_once INCLUDE_LIB . 'ParsingQuery.class.php';
$Colorize = new ParsingQuery($query, 1);
$Colorize->explodeQuery();
$Colorize->colorWordList();
return $Colorize->highlightQuery();
}
/**
* Retrive Record from current query and numId
*
* @access public
* @param string $req current query
* @param integer $numId Number of record from current query
* @param boolean $error if true return POST value
*/
function recupElement($req, $numId, $error = false)
{
include_once INCLUDE_LIB . 'ParsingQuery.class.php';
$tabQueryElement = ParsingQuery::explodeSelect($req);
$tabQueryElement['SELECT'] = 'ROWID, ' . $tabQueryElement['SELECT'];
if (preg_match('#FROM#i', $req)) {
$tabFrom = explode(',', $tabQueryElement['FROM']);
foreach ($tabFrom as $key => $value) {
$tabFrom[$key] = brackets(unquote($value));
}
$tabQueryElement['FROM'] = implode(',', $tabFrom);
}
if (preg_match('#LIMIT#i', $req)) {
$tabLimit = explode(',', $tabQueryElement['LIMIT']);
$tabQueryElement['LIMIT'] = (int) $tabLimit[0] + $numId . ',1';
} else {
$tabQueryElement['LIMIT'] = $numId . ',1';
}
$querySearch = '';
foreach ($tabQueryElement as $clause => $contentClause) {
$querySearch .= $clause . ' ' . $contentClause . ' ';
}
$this->connId->connId->query($querySearch);
$tabData = $this->connId->connId->fetch_array(null, $this->connId->connId->getVersion() == 3 ? SQLITE_BOTH : SQLITE_ASSOC);
if ($this->connId->connId->getVersion() == 3) {
$tabData["ROWID"] = $tabData[0];
}
if ($error) {
foreach ($tabData as $fieldname => $fieldvalue) {
if (isset($_POST[$fieldname])) {
$tabData[$fieldname] = $_POST[$fieldname];
}
}
}
return $tabData;
}
