From 0ec70f575b3c45116223654709908f721f33e26f Mon Sep 17 00:00:00 2001 From: kmorimatsu Date: Tue, 10 Apr 2007 01:34:32 +0000 Subject: [PATCH] Ver 0.8.5 git-svn-id: https://svn.sourceforge.jp/svnroot/nucleus-jp/plugin@553 1ca29b6e-896d-4ea0-84a5-967f57386b96 --- trunk/sqlite/nucleus/sqlite/convert.php | 6 +- trunk/sqlite/nucleus/sqlite/sqlite.php | 15 +- trunk/sqlite/nucleus/sqlite/sqliteconfig.php | 2 +- .../sqlite/nucleus/sqlite/sqlitequeryfunctions.php | 478 +++++++++++---------- 4 files changed, 264 insertions(+), 237 deletions(-) diff --git a/trunk/sqlite/nucleus/sqlite/convert.php b/trunk/sqlite/nucleus/sqlite/convert.php index db4b07e..a1c7d9e 100644 --- a/trunk/sqlite/nucleus/sqlite/convert.php +++ b/trunk/sqlite/nucleus/sqlite/convert.php @@ -2,7 +2,7 @@ /******************************************* * mysql_xxx => nucleus_mysql_xxx converter * * for Nucleus * -* ver 0.8.0 Written by Katsumi * +* ver 0.8.5 Written by Katsumi * *******************************************/ // The license of this script is GPL @@ -65,6 +65,10 @@ function modifyConfigInstall(){ } else { if (preg_match('/CREATE[\s]+TABLE[\s]+`nucleus_plugin_option`/i',$line)) $pluginoptiontable=true; } + if (strlen($after)==0) { + $replace='PRAGMA synchronous = off;'; + if (!strstr($line,$replace)) $after=preg_replace('/^[^\r\n]*([\r\n]*)$/',$replace.'$1$1',$line); + } $after.=$line; } if ($after!=$before) { diff --git a/trunk/sqlite/nucleus/sqlite/sqlite.php b/trunk/sqlite/nucleus/sqlite/sqlite.php index f78538e..e54c065 100644 --- a/trunk/sqlite/nucleus/sqlite/sqlite.php +++ b/trunk/sqlite/nucleus/sqlite/sqlite.php @@ -1,7 +1,7 @@ $end) array_push($orgstrings, trim(substr($query,$start,$end-$start))); - - $lquery=strtolower($query); - if (!$pattern) $pattern=array(); - if (!$replacement) $replacement=array(); - - // match() against() support. Following way does NOT accept SQL-injection. Note that the string is always quoted by "'". - array_push($pattern,'/match \(([^\']*?)\) against \(/i'); - array_push($replacement,'match_against ($1,'); - // REGEXP support - if (strpos($lquery,'regexp')!==false) { - array_push($pattern,'/([^a-z_\.])([a-z_\.]+)[\s]+REGEXP[\s]+\'([^\']*?)\'([^\']?)/i'); - array_push($replacement,'$1regexp($2,\'$3\')$4'); - } - // ADDDATE/SUBDATE support (INTERVAL support) - array_push($pattern,'/([^a-zA-Z_])ADDDATE[\s]*?\(([^,]+),[\s]*?INTERVAL[\s]+([\S]+)[\s]+([^\)]+)\)/i'); - array_push($replacement,'$1adddate($2,$3,\'$4\')'); - array_push($pattern,'/([^a-zA-Z_])DATE_ADD[\s]*?\(([^,]+),[\s]*?INTERVAL[\s]+([\S]+)[\s]+([^\)]+)\)/i'); - array_push($replacement,'$1adddate($2,$3,\'$4\')'); - array_push($pattern,'/([^a-zA-Z_])SUBDATE[\s]*?\(([^,]+),[\s]*?INTERVAL[\s]+([\S]+)[\s]+([^\)]+)\)/i'); - array_push($replacement,'$1subdate($2,$3,\'$4\')'); - array_push($pattern,'/([^a-zA-Z_])DATE_SUB[\s]*?\(([^,]+),[\s]*?INTERVAL[\s]+([\S]+)[\s]+([^\)]+)\)/i'); - array_push($replacement,'$1subdate($2,$3,\'$4\')'); - - // EXTRACT support - array_push($pattern,'/([^a-zA-Z_])EXTRACT[\s]*?\(([a-zA-Z_]+)[\s]+FROM/i'); - array_push($replacement,'$1extract(\'$2\','); - - // TRIM support: - array_push($pattern,'/([^a-zA-Z_])TRIM[\s]*?\((BOTH|LEADING|TRAILING)[\s]+FROM/i'); - array_push($replacement,'$1extract(\'$2\',\' \','); - array_push($pattern,'/([^a-zA-Z_])TRIM[\s]*?\((BOTH|LEADING|TRAILING)[\s]+([\s\S]+)[\s]+FROM/i'); - array_push($replacement,'$1extract(\'$2\',$3,'); - - // Change it. - $temp=preg_replace ($pattern,$replacement,$query); - - // Comfirm if strings did not change. - $ok=true; - foreach ($orgstrings as $key=>$value) if ($value) { - if (strpos($temp,$value)!==false) { - // This string is OK, therefore will be ignored in the next "step by step" step. - $orgstrings[$key]=''; - continue; - } - $ok=false; - } - if ($ok) { // return if everything is OK. - $query=$temp; - return; - } - - // At least one of string changed. Need to do step by step. - foreach ($pattern as $key=>$pat) { - // Replace is done step by step for each RegExp replace statement. - $SQLITE_MQFUFCB_REPLACE=$replace[$key];// Set the grobal var. - $num=preg_match_all($pat,$query,$matches); - // First, check if OK. - $replaceOK=array(); - for ($i=1;$i<=$num;$i++) { - $SQLITE_MQFUFCB_OK=array(); - $SQLITE_MQFUFCB_OK[$i]=true; // Set the grobal var. - $SQLITE_MQFUFCB_COUNT=0; // Set the grobal var. - // Only $i-st replacement will be done in the next line. - $temp=preg_replace_callback($pat,'sqlite_modifyQueryForUserFuncCallBack', $query, $i); - $ok=true; - foreach ($orgstrings as $value) if ($value) { - if (strpos($temp,$value)!==false) continue; - $ok=false; - break; - } - if ($ok) $replaceOK[$i]=true; - } - // Replace - $SQLITE_MQFUFCB_OK=$replaceOK;// Copy the OK array - $SQLITE_MQFUFCB_COUNT=0; - $query=preg_replace_callback($pat,'sqlite_modifyQueryForUserFuncCallBack', $query); +// Register user-defined functions used in SQL query. +// The SQLite_QueryFunctions object is created to register SQLite queries. +$SQLITECONF['object']=new SQLite_Functions; +// After the registration, the object is not required any more. +unset($SQLITECONF['object']); + + +// The class for SQLite user functions +class SQLite_Functions { + +// Constructor is used for the registration of user-defined functions of SQLite +function SQLite_Functions(){ + global $SQLITE_DBHANDLE; + foreach($this as $key=>$value){ + $key=strtoupper($key); + if (substr($key,0,7)!='DEFINE_') continue; + $key=substr($key,7); + if (substr($value,0,7)=='sqlite_') $value=array('SQLite_Functions',$value); + @sqlite_create_function($SQLITE_DBHANDLE,$key,$value); } } -function sqlite_modifyQueryForUserFuncCallBack($mathces){ - global $SQLITE_MQFUFCB_OK,$SQLITE_MQFUFCB_COUNT,$SQLITE_MQFUFCB_REPLACE; - if ($SQLITE_MQFUFCB_OK[++$SQLITE_MQFUFCB_COUNT]) return $SQLITE_MQFUFCB_REPLACE; - else return $mathces[0]; -} - -// Register user-defined functions used in SQL query. -sqlite_createQueryFunction('ASCII', 'ord'); +var $define_ASCII='ord'; -sqlite_createQueryFunction('BIN', 'decbin'); +var $define_BIN='decbin'; -sqlite_createQueryFunction( 'BIT_LENGTH', 'sqlite_userfunc_BIT_LENGTH'); +var $define_BIT_LENGTH='sqlite_userfunc_BIT_LENGTH'; function sqlite_userfunc_BIT_LENGTH($p1){ return strlen($p1)*8; } -sqlite_createQueryFunction( 'CHAR', 'sqlite_userfunc_CHAR'); +var $define_CHAR='sqlite_userfunc_CHAR'; function sqlite_userfunc_CHAR(){ if (!($lastnum=func_num_args())) return null; $args=&func_get_args(); @@ -146,9 +74,9 @@ function sqlite_userfunc_CHAR(){ return $ret; } -sqlite_createQueryFunction( 'CHAR_LENGTH', 'mb_strlen'); +var $define_CHAR_LENGTH='mb_strlen'; -sqlite_createQueryFunction( 'CONCAT_WS', 'sqlite_userfunc_CONCAT_WS'); +var $define_CONCAT_WS='sqlite_userfunc_CONCAT_WS'; function sqlite_userfunc_CONCAT_WS(){ if (($lastnum=func_num_args())<2) return null; $args=&func_get_args(); @@ -162,7 +90,7 @@ function sqlite_userfunc_CONCAT_WS(){ return $ret; } -sqlite_createQueryFunction( 'CONV', 'sqlite_userfunc_CONV'); +var $define_CONV='sqlite_userfunc_CONV'; function sqlite_userfunc_CONV($p1,$p2,$p3){ $t36='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'; $p1=strtoupper(trim((string)$p1)); @@ -183,7 +111,7 @@ function sqlite_userfunc_CONV($p1,$p2,$p3){ return $sign.$ret; } -sqlite_createQueryFunction( 'ELT', 'sqlite_userfunc_ELT'); +var $define_ELT='sqlite_userfunc_ELT'; function sqlite_userfunc_ELT(){ if (($lastnum=func_num_args())<2) return null; $args=&func_get_args(); @@ -191,7 +119,7 @@ function sqlite_userfunc_ELT(){ return $args[$args[0]]; } -sqlite_createQueryFunction( 'EXPORT_SET', 'sqlite_userfunc_EXPORT_SET'); +var $define_EXPORT_SET='sqlite_userfunc_EXPORT_SET'; function sqlite_userfunc_EXPORT_SET($p1,$p2,$p3,$p4=',',$p5=64){ if ($p1<2147483648) $p1=decbin($p1); else $p1=decbin($p1/2147483648).decbin($p1 % 2147483648); @@ -200,7 +128,7 @@ function sqlite_userfunc_EXPORT_SET($p1,$p2,$p3,$p4=',',$p5=64){ return substr($p1,0,strlen($p1)-strlen($p4)); } -sqlite_createQueryFunction( 'FIELD', 'sqlite_userfunc_FIELD'); +var $define_FIELD='sqlite_userfunc_FIELD'; function sqlite_userfunc_FIELD(){ if (($lastnum=func_num_args())<2) return null; $args=&func_get_args(); @@ -208,7 +136,7 @@ function sqlite_userfunc_FIELD(){ return 0; } -sqlite_createQueryFunction( 'HEX', 'sqlite_userfunc_HEX'); +var $define_HEX='sqlite_userfunc_HEX'; function sqlite_userfunc_HEX($p1){ if (is_numeric($p1)) return dechex ($p1); $p1=(string)$p1; @@ -217,13 +145,13 @@ function sqlite_userfunc_HEX($p1){ return $ret; } -sqlite_createQueryFunction( 'INSERT', 'sqlite_userfunc_INSERT'); +var $define_INSERT='sqlite_userfunc_INSERT'; function sqlite_userfunc_INSERT($p1,$p2,$p3,$p4){ if (function_exists('mb_substr')) return mb_substr($p1,0,$p2-1).$p4.mb_substr($p1,$p2+$p3-1); return substr($p1,0,$p2-1).$p4.substr($p1,$p2+$p3-1); } -sqlite_createQueryFunction( 'LOCATE', 'sqlite_userfunc_LOCATE'); +var $define_LOCATE='sqlite_userfunc_LOCATE'; function sqlite_userfunc_LOCATE($p1,$p2,$p3=1){ if (substr($p1,1)=='_') $p2='-'.$p2; else $p2='_'.$p2; @@ -232,28 +160,28 @@ function sqlite_userfunc_LOCATE($p1,$p2,$p3=1){ return $p+$p3; } -sqlite_createQueryFunction( 'INSTR', 'sqlite_userfunc_INSTR'); +var $define_INSTR='sqlite_userfunc_INSTR'; function sqlite_userfunc_INSTR($p1,$p2){ - return sqlite_userfunc_LOCATE($p2,$p1); + return SQLite_Functions::sqlite_userfunc_LOCATE($p2,$p1); } -sqlite_createQueryFunction( 'LCASE', 'sqlite_userfunc_LOWER'); -sqlite_createQueryFunction( 'LOWER', 'sqlite_userfunc_LOWER'); +var $define_LCASE='sqlite_userfunc_LOWER'; +var $define_LOWER='sqlite_userfunc_LOWER'; function sqlite_userfunc_LOWER($p1){ if (function_exists('mb_strtolower')) return mb_strtolower($p1); return strtolower($p1); } -sqlite_createQueryFunction( 'LEFT', 'sqlite_userfunc_LEFT'); +var $define_LEFT='sqlite_userfunc_LEFT'; function sqlite_userfunc_LEFT($p1,$p2){ if (function_exists('mb_substr')) return mb_substr($p1,0,$p2); return substr($p1,0,$p2); } -sqlite_createQueryFunction( 'LENGTH', 'strlen'); -sqlite_createQueryFunction( 'OCTET_LENGTH', 'strlen'); +var $define_LENGTH='strlen'; +var $define_OCTET_LENGTH='strlen'; -sqlite_createQueryFunction( 'LOAD_FILE', 'sqlite_userfunc_LOAD_FILE'); +var $define_LOAD_FILE='sqlite_userfunc_LOAD_FILE'; function sqlite_userfunc_LOAD_FILE($p1){ if (!file_exists($p1)) return null; if (!is_array($a=@file($p1))) return null; @@ -262,14 +190,14 @@ function sqlite_userfunc_LOAD_FILE($p1){ return $ret; } -sqlite_createQueryFunction( 'LPAD', 'sqlite_userfunc_LPAD'); +var $define_LPAD='sqlite_userfunc_LPAD'; function sqlite_userfunc_LPAD($p1,$p2,$p3){ return substr(str_repeat($p3,$p2/strlen($p3)).$p1,0-$p2); } -sqlite_createQueryFunction( 'LTRIM', 'ltrim'); +var $define_LTRIM='ltrim'; -sqlite_createQueryFunction( 'MAKE_SET', 'sqlite_userfunc_MAKE_SET'); +var $define_MAKE_SET='sqlite_userfunc_MAKE_SET'; function sqlite_userfunc_MAKE_SET($p1,$p2,$p3){ if (($lastnum=func_num_args())<2) return null; $args=&func_get_args(); @@ -285,8 +213,8 @@ function sqlite_userfunc_MAKE_SET($p1,$p2,$p3){ return 0; } -sqlite_createQueryFunction( 'MID', 'sqlite_userfunc_SUBSTRING'); -sqlite_createQueryFunction( 'SUBSTRING', 'sqlite_userfunc_SUBSTRING'); +var $define_MID='sqlite_userfunc_SUBSTRING'; +var $define_SUBSTRING='sqlite_userfunc_SUBSTRING'; function sqlite_userfunc_SUBSTRING($p1,$p2,$p3=null){ $p2--; if (function_exists('mb_substr')) { @@ -297,13 +225,13 @@ function sqlite_userfunc_SUBSTRING($p1,$p2,$p3=null){ return substr($p1,$p2); } -sqlite_createQueryFunction( 'OCT', 'sqlite_userfunc_OCT'); +var $define_OCT='sqlite_userfunc_OCT'; function sqlite_userfunc_OCT($p1){ if ($p1===null) return null; - return sqlite_userfunc_CONV($p1,10,8); + return SQLite_Functions::sqlite_userfunc_CONV($p1,10,8); } -sqlite_createQueryFunction( 'ORD', 'sqlite_userfunc_ORD'); +var $define_ORD='sqlite_userfunc_ORD'; function sqlite_userfunc_ORD($p1){ if (function_exists('mb_substr')) $p1=mb_substr($p1,0,1); else $p1=substr($p1,0,1); @@ -312,15 +240,15 @@ function sqlite_userfunc_ORD($p1){ return $ret; } -sqlite_createQueryFunction( 'QUOTE', 'sqlite_userfunc_QUOTE'); +var $define_QUOTE='sqlite_userfunc_QUOTE'; function sqlite_userfunc_QUOTE($p1){ if ($p1===null) return 'NULL'; return str_replace(array("'","\\","\x1A"),array("\\'","\\\\","\\z"),$p1); } -sqlite_createQueryFunction( 'REPEAT', 'str_repeat'); +var $define_REPEAT='str_repeat'; -sqlite_createQueryFunction( 'REVERSE', 'sqlite_userfunc_REVERSE'); +var $define_REVERSE='sqlite_userfunc_REVERSE'; function sqlite_userfunc_REVERSE($p1){ if (function_exists('mb_strlen')) { $ret=''; @@ -330,26 +258,26 @@ function sqlite_userfunc_REVERSE($p1){ return strrev($p1); } -sqlite_createQueryFunction( 'RIGHT', 'sqlite_userfunc_RIGHT'); +var $define_RIGHT='sqlite_userfunc_RIGHT'; function sqlite_userfunc_RIGHT($p1){ if (function_exists('mb_substr')) return mb_substr($p1,0-$p2); return substr($p1,0-$p2); } -sqlite_createQueryFunction( 'RPAD', 'sqlite_userfunc_RPAD'); +var $define_RPAD='sqlite_userfunc_RPAD'; function sqlite_userfunc_RPAD($p1,$p2,$p3){ return substr($p1.str_repeat($p3,$p2/strlen($p3)),0,$p2); } -sqlite_createQueryFunction( 'RTRIM', 'rtrim'); -sqlite_createQueryFunction( 'SOUNDEX', 'soundex'); +var $define_RTRIM='rtrim'; +var $define_SOUNDEX='soundex'; -sqlite_createQueryFunction( 'SPACE', 'sqlite_userfunc_SPACE'); +var $define_SPACE='sqlite_userfunc_SPACE'; function sqlite_userfunc_SPACE($p1){ return str_repeat(' ',$p1); } -sqlite_createQueryFunction( 'SUBSTRING_INDEX', 'sqlite_userfunc_SUBSTRING_INDEX'); +var $define_SUBSTRING_INDEX='sqlite_userfunc_SUBSTRING_INDEX'; function sqlite_userfunc_SUBSTRING_INDEX($p1,$p2,$p3){ if (!is_array($a=explode($p2,$p1))) return null; $ret=''; @@ -367,7 +295,7 @@ function sqlite_userfunc_SUBSTRING_INDEX($p1,$p2,$p3){ return $ret; } -sqlite_createQueryFunction( 'TRIM', 'sqlite_userfunc_TRIM'); +var $define_TRIM='sqlite_userfunc_TRIM'; function sqlite_userfunc_TRIM($p1,$p2=null,$p3=null){ if (!$p2 && !$p3) return trim($p1); if (!$p2) $p2=' '; @@ -384,17 +312,17 @@ function sqlite_userfunc_TRIM($p1,$p2=null,$p3=null){ return $p2; } -sqlite_createQueryFunction( 'UCASE', 'sqlite_userfunc_UPPER'); -sqlite_createQueryFunction( 'UPPER', 'sqlite_userfunc_UPPER'); +var $define_UCASE='sqlite_userfunc_UPPER'; +var $define_UPPER='sqlite_userfunc_UPPER'; function sqlite_userfunc_UPPER($p1){ if (function_exists('mb_strtoupper')) return mb_strtoupper($p1); return strtoupper($p1); } -sqlite_createQueryFunction( 'ACOS', 'acos'); -sqlite_createQueryFunction( 'ASIN', 'asin'); +var $define_ACOS='acos'; +var $define_ASIN='asin'; -sqlite_createQueryFunction( 'ATAN', 'sqlite_userfunc_ATAN'); +var $define_ATAN='sqlite_userfunc_ATAN'; function sqlite_userfunc_ATAN($p1,$p2=null){ if (!$p2) return atan($p1); if ($p1>0 && $p2>0) return atan($p1/$p2); @@ -404,99 +332,99 @@ function sqlite_userfunc_ATAN($p1,$p2=null){ else return 0; } -sqlite_createQueryFunction( 'CEIL', 'ceil'); -sqlite_createQueryFunction( 'CEILING', 'ceil'); -sqlite_createQueryFunction( 'COS', 'cos'); +var $define_CEIL='ceil'; +var $define_CEILING='ceil'; +var $define_COS='cos'; -sqlite_createQueryFunction( 'COT', 'sqlite_userfunc_COT'); +var $define_COT='sqlite_userfunc_COT'; function sqlite_userfunc_COT($p1){ return 1/tan($p1); } -sqlite_createQueryFunction( 'CRC32', 'crc32'); +var $define_CRC32='crc32'; -sqlite_createQueryFunction( 'DEGREES', 'sqlite_userfunc_DEGREES'); +var $define_DEGREES='sqlite_userfunc_DEGREES'; function sqlite_userfunc_DEGREES($p1){ return ($p1/pi)*180; } -sqlite_createQueryFunction( 'EXP', 'exp'); -sqlite_createQueryFunction( 'FLOOR', 'floor'); -sqlite_createQueryFunction( 'GREATEST', 'max'); -sqlite_createQueryFunction( 'MAX', 'max'); -sqlite_createQueryFunction( 'LEAST', 'min'); -sqlite_createQueryFunction( 'MIN', 'min'); -sqlite_createQueryFunction( 'ln', 'log'); +var $define_EXP='exp'; +var $define_FLOOR='floor'; +var $define_GREATEST='max'; +var $define_MAX='max'; +var $define_LEAST='min'; +var $define_MIN='min'; +var $define_ln='log'; -sqlite_createQueryFunction( 'log', 'sqlite_userfunc_LOG'); +var $define_log='sqlite_userfunc_LOG'; function sqlite_userfunc_LOG($p1,$p2=null){ if ($p2) return log($p1)/log($p2); return log($p1); } -sqlite_createQueryFunction( 'log2', 'sqlite_userfunc_LOG2'); +var $define_log2='sqlite_userfunc_LOG2'; function sqlite_userfunc_LOG2($p1){ return log($p1)/log(2); } -sqlite_createQueryFunction( 'log10', 'log10'); +var $define_log10='log10'; -sqlite_createQueryFunction( 'MOD', 'sqlite_userfunc_MOD'); +var $define_MOD='sqlite_userfunc_MOD'; function sqlite_userfunc_MOD($p1,$p2){ return $p1 % $p2; } -sqlite_createQueryFunction( 'PI', 'sqlite_userfunc_PI'); +var $define_PI='sqlite_userfunc_PI'; function sqlite_userfunc_PI(){ return pi; } -sqlite_createQueryFunction( 'POW', 'sqlite_userfunc_POW'); -sqlite_createQueryFunction( 'POWER', 'sqlite_userfunc_POW'); +var $define_POW='sqlite_userfunc_POW'; +var $define_POWER='sqlite_userfunc_POW'; function sqlite_userfunc_POW($p1,$p2){ return pow($p1,$p2); } -sqlite_createQueryFunction( 'RADIANS', 'sqlite_userfunc_RADIANS'); +var $define_RADIANS='sqlite_userfunc_RADIANS'; function sqlite_userfunc_RADIANS($p1){ return ($p1/180)*pi; } -sqlite_createQueryFunction( 'RAND', 'sqlite_userfunc_RAND'); +var $define_RAND='sqlite_userfunc_RAND'; function sqlite_userfunc_RAND($p1=null){ if ($p1) srand($p1); return rand(0,1073741823)/1073741824; } -sqlite_createQueryFunction( 'SIGN', 'sqlite_userfunc_SIGN'); +var $define_SIGN='sqlite_userfunc_SIGN'; function sqlite_userfunc_SIGN($p1){ if ($p1>0) return 1; else if ($p1<0) return -1; return 0; } -sqlite_createQueryFunction( 'SIN', 'sin'); -sqlite_createQueryFunction( 'SQRT', 'sqrt'); -sqlite_createQueryFunction( 'TAN', 'tan'); +var $define_SIN='sin'; +var $define_SQRT='sqrt'; +var $define_TAN='tan'; -sqlite_createQueryFunction( 'TRUNCATE', 'sqlite_userfunc_TRUNCATE'); +var $define_TRUNCATE='sqlite_userfunc_TRUNCATE'; function sqlite_userfunc_TRUNCATE($p1,$p2){ $p2=pow(10,$p2); return ((int)($p1*$p2))/$p2; } -sqlite_createQueryFunction( 'FORMAT', 'sqlite_userfunc_FORMAT'); +var $define_FORMAT='sqlite_userfunc_FORMAT'; function sqlite_userfunc_FORMAT($p1,$p2){ return number_format($p1, $p2, '.', ','); } -sqlite_createQueryFunction( 'INET_ATON', 'sqlite_userfunc_INET_ATON'); +var $define_INET_ATON='sqlite_userfunc_INET_ATON'; function sqlite_userfunc_INET_ATON($p1){ $a=explode('.',$p1); return (($a[0]*256+$a[1])*256+$a[2])*256+$a[3]; } -sqlite_createQueryFunction( 'INET_NTOA', 'sqlite_userfunc_INET_NTOA'); +var $define_INET_NTOA='sqlite_userfunc_INET_NTOA'; function sqlite_userfunc_INET_NTOA($p1){ $a=array(); for ($i=0;$i<4;$i++){ @@ -506,59 +434,59 @@ function sqlite_userfunc_INET_NTOA($p1){ return $a[3].'.'.$a[2].'.'.$a[1].'.'.$a[0]; } -sqlite_createQueryFunction( 'MD5', 'md5'); +var $define_MD5='md5'; -sqlite_createQueryFunction( 'CURDATE', 'sqlite_userfunc_CURDATE'); -sqlite_createQueryFunction( 'CURRENT_DATE', 'sqlite_userfunc_CURDATE'); +var $define_CURDATE='sqlite_userfunc_CURDATE'; +var $define_CURRENT_DATE='sqlite_userfunc_CURDATE'; function sqlite_userfunc_CURDATE(){ return date('Y-m-d'); } -sqlite_createQueryFunction( 'CURTIME', 'sqlite_userfunc_CURTIME'); -sqlite_createQueryFunction( 'CURRENT_TIME', 'sqlite_userfunc_CURTIME'); +var $define_CURTIME='sqlite_userfunc_CURTIME'; +var $define_CURRENT_TIME='sqlite_userfunc_CURTIME'; function sqlite_userfunc_CURTIME(){ return date('H:i:s'); } -sqlite_createQueryFunction( 'CURRENT_TIMESTAMP', 'sqlite_userfunc_NOW'); -sqlite_createQueryFunction( 'LOCALTIME', 'sqlite_userfunc_NOW'); -sqlite_createQueryFunction( 'LOCALTIMESTAMP', 'sqlite_userfunc_NOW'); -sqlite_createQueryFunction( 'SYSDATE', 'sqlite_userfunc_NOW'); +var $define_CURRENT_TIMESTAMP='sqlite_userfunc_NOW'; +var $define_LOCALTIME='sqlite_userfunc_NOW'; +var $define_LOCALTIMESTAMP='sqlite_userfunc_NOW'; +var $define_SYSDATE='sqlite_userfunc_NOW'; function sqlite_userfunc_NOW(){ return date('Y-m-d H:i:s'); } -sqlite_createQueryFunction( 'DATE_FORMAT', 'sqlite_userfunc_DATE_FORMAT'); -sqlite_createQueryFunction( 'TIME_FORMAT', 'sqlite_userfunc_DATE_FORMAT'); +var $define_DATE_FORMAT='sqlite_userfunc_DATE_FORMAT'; +var $define_TIME_FORMAT='sqlite_userfunc_DATE_FORMAT'; function sqlite_userfunc_DATE_FORMAT($p1,$p2){ - $t=sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); + $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); $func='if ($matches=="%") return "%";'; $func='return date($matches,'.$t.');'; return preg_replace_callback ('/%([%a-zA-Z])/',create_function('$matches',$func), $p2); } -sqlite_createQueryFunction( 'DAYNAME', 'sqlite_userfunc_DAYNAME'); +var $define_DAYNAME='sqlite_userfunc_DAYNAME'; function sqlite_userfunc_DAYNAME($p1){ - $t=sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); + $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); return date('l',$t); } -sqlite_createQueryFunction( 'DAYOFWEEK', 'sqlite_userfunc_DAYOFWEEK'); +var $define_DAYOFWEEK='sqlite_userfunc_DAYOFWEEK'; function sqlite_userfunc_DAYOFWEEK($p1){ - $t=sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); + $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); return date('w',$t)+1; } -sqlite_createQueryFunction( 'DAYOFYEAR', 'sqlite_userfunc_DAYOFYEAR'); +var $define_DAYOFYEAR='sqlite_userfunc_DAYOFYEAR'; function sqlite_userfunc_DAYOFYEAR($p1){ - $t=sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); + $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); return date('z',$t); } -sqlite_createQueryFunction( 'EXTRACT', 'sqlite_userfunc_EXTRACT'); +var $define_EXTRACT='sqlite_userfunc_EXTRACT'; function sqlite_userfunc_EXTRACT($p1,$p2){ - $t=sqlite_resolvedatetime($p2,$yr,$mt,$dy,$hr,$mn,$sc); + $t=SQLite_Functions::sqlite_resolvedatetime($p2,$yr,$mt,$dy,$hr,$mn,$sc); switch(strtoupper($p1)) { case'SECOND': // SECONDS return $sc; @@ -590,37 +518,37 @@ function sqlite_userfunc_EXTRACT($p1,$p2){ } } -sqlite_createQueryFunction( 'FROM_DAYS', 'sqlite_userfunc_FROM_DAYS'); +var $define_FROM_DAYS='sqlite_userfunc_FROM_DAYS'; function sqlite_userfunc_FROM_DAYS($p1){ return date('Y-m-d',($p1-719528)*86400); } -sqlite_createQueryFunction( 'FROM_UNIXTIME', 'sqlite_userfunc_FROM_UNIXTIME'); +var $define_FROM_UNIXTIME='sqlite_userfunc_FROM_UNIXTIME'; function sqlite_userfunc_FROM_UNIXTIME($p1,$p2=null){ if ($p2) return sqlite_userfunc_DATE_FORMAT($p1,$p2); return date('Y-m-d H:i:s',$p1); } -sqlite_createQueryFunction( 'HOUR', 'sqlite_userfunc_HOUR'); +var $define_HOUR='sqlite_userfunc_HOUR'; function sqlite_userfunc_HOUR($p1){ - sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); + SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); return $hr; } -sqlite_createQueryFunction( 'MINUTE', 'sqlite_userfunc_MINUTE'); +var $define_MINUTE='sqlite_userfunc_MINUTE'; function sqlite_userfunc_MINUTE($p1){ - sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); + SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); return $mn; } -sqlite_createQueryFunction( 'MONTHNAME', 'sqlite_userfunc_MONTHNAME'); +var $define_MONTHNAME='sqlite_userfunc_MONTHNAME'; function sqlite_userfunc_MONTHNAME($p1){ - $t=sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); + $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); return date('F',$t); } -sqlite_createQueryFunction( 'PERIOD_ADD', 'sqlite_userfunc_PERIOD_ADD'); +var $define_PERIOD_ADD='sqlite_userfunc_PERIOD_ADD'; function sqlite_userfunc_PERIOD_ADD($p1,$p2){ $y=(int)($p1/100); $m=$p1-$y*100; @@ -628,7 +556,7 @@ function sqlite_userfunc_PERIOD_ADD($p1,$p2){ return date('Ym',$t); } -sqlite_createQueryFunction( 'PERIOD_DIFF', 'sqlite_userfunc_PERIOD_DIFF'); +var $define_PERIOD_DIFF='sqlite_userfunc_PERIOD_DIFF'; function sqlite_userfunc_PERIOD_DIFF($p1,$p2){ $y1=(int)($p1/100); $m1=$p1-$y1*100; @@ -641,9 +569,9 @@ function sqlite_userfunc_PERIOD_DIFF($p1,$p2){ return (int)(mktime(0,0,0,$m1-$m2,1,1970+$y1-$y2, -1)/60/60/24/28); } -sqlite_createQueryFunction( 'QUARTER', 'sqlite_userfunc_QUARTER'); +var $define_QUARTER='sqlite_userfunc_QUARTER'; function sqlite_userfunc_QUARTER($p1){ - sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); + SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); switch($mt){ case 1: case 2: case 3: return 1; case 4: case 5: case 6: return 2; @@ -652,44 +580,44 @@ function sqlite_userfunc_QUARTER($p1){ } } -sqlite_createQueryFunction( 'SECOND', 'sqlite_userfunc_SECOND'); +var $define_SECOND='sqlite_userfunc_SECOND'; function sqlite_userfunc_SECOND($p1){ - sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); + SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); return $sc; } -sqlite_createQueryFunction( 'SEC_TO_TIME', 'sqlite_userfunc_SEC_TO_TIME'); +var $define_SEC_TO_TIME='sqlite_userfunc_SEC_TO_TIME'; function sqlite_userfunc_SEC_TO_TIME($p1){ return date('H:i:s',$p1); } -sqlite_createQueryFunction( 'WEEK', 'sqlite_userfunc_WEEK'); +var $define_WEEK='sqlite_userfunc_WEEK'; function sqlite_userfunc_WEEK($p1){ - $t=sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); + $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); return date('W',$t); } -sqlite_createQueryFunction( 'WEEKDAY', 'sqlite_userfunc_WEEKDAY'); +var $define_WEEKDAY='sqlite_userfunc_WEEKDAY'; function sqlite_userfunc_WEEKDAY($p1){ - $t=sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); + $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); if (0<($w=date('w',$t))) return $w-1; return 6; } -sqlite_createQueryFunction( 'YEAR', 'sqlite_userfunc_YEAR'); +var $define_YEAR='sqlite_userfunc_YEAR'; function sqlite_userfunc_YEAR($p1){ - $t=sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); + $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); return date('Y',$t); } -sqlite_createQueryFunction( 'YEARWEEK', 'sqlite_userfunc_YEARWEEK'); +var $define_YEARWEEK='sqlite_userfunc_YEARWEEK'; function sqlite_userfunc_YEARWEEK($p1){ - $t=sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); + $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); return date('YW',$t); } -sqlite_createQueryFunction( 'FIND_IN_SET', 'sqlite_userfunc_FIND_IN_SET'); +var $define_FIND_IN_SET='sqlite_userfunc_FIND_IN_SET'; function sqlite_userfunc_FIND_IN_SET($p1,$p2){ if ($p1==null && $p2==null) return null; if (!$p2) return 0; @@ -698,17 +626,17 @@ function sqlite_userfunc_FIND_IN_SET($p1,$p2){ } -sqlite_createQueryFunction( 'ADDDATE', 'sqlite_userfunc_ADDDATE'); +var $define_ADDDATE='sqlite_userfunc_ADDDATE'; function sqlite_userfunc_ADDDATE($p1,$p2,$p3='DAY'){ return date("Y-m-d",sqlite_ADDDATE($p1,$p2,$p3)); } -sqlite_createQueryFunction( 'SUBDATE', 'sqlite_userfunc_SUBDATE'); +var $define_SUBDATE='sqlite_userfunc_SUBDATE'; function sqlite_userfunc_SUBDATE($p1,$p2,$p3='DAY'){ return date("Y-m-d",sqlite_ADDDATE($p1,0-$p2,$p3)); } -sqlite_createQueryFunction( 'CONCAT', 'sqlite_userfunc_CONCAT'); +var $define_CONCAT='sqlite_userfunc_CONCAT'; function sqlite_userfunc_CONCAT(){ if (!($lastnum=func_num_args())) return null; $args=&func_get_args(); @@ -720,26 +648,26 @@ function sqlite_userfunc_CONCAT(){ return $ret; } -sqlite_createQueryFunction( 'IF', 'sqlite_userfunc_IF'); +var $define_IF='sqlite_userfunc_IF'; function sqlite_userfunc_IF($p1,$p2,$p3){ if ((int)$p1) return $p2; return $p3; } -sqlite_createQueryFunction( 'IFNULL', 'sqlite_userfunc_IFNULL'); +var $define_IFNULL='sqlite_userfunc_IFNULL'; function sqlite_userfunc_IFNULL($p1,$p2){ if ($p1!=null) return $p1; return $p2; } -sqlite_createQueryFunction( 'NULLIF', 'sqlite_userfunc_NULLIF'); +var $define_NULLIF='sqlite_userfunc_NULLIF'; function sqlite_userfunc_NULLIF($p1,$p2){ if ($p1==$p2) return null; return $p1; } -sqlite_createQueryFunction( 'match_against', 'sqlite_userfunc_match_against'); +var $define_match_against='sqlite_userfunc_match_against'; function sqlite_userfunc_match_against(){ if (!($lastnum=func_num_args())) return 0; if (!(--$lastnum)) return 0; @@ -751,33 +679,33 @@ function sqlite_userfunc_match_against(){ return $ret; } -sqlite_createQueryFunction( 'replace', 'sqlite_userfunc_replace'); +var $define_replace='sqlite_userfunc_replace'; function sqlite_userfunc_replace($p1,$p2,$p3){ return str_replace($p3,$p1,$p2); } -sqlite_createQueryFunction( 'UNIX_TIMESTAMP', 'sqlite_userfunc_UNIX_TIMESTAMP'); +var $define_UNIX_TIMESTAMP='sqlite_userfunc_UNIX_TIMESTAMP'; function sqlite_userfunc_UNIX_TIMESTAMP($p1=null){ if (!$p1) return time(); - sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); + SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); if ($yr) return mktime($hr,$mn,$sc,$mt,$dy,$yr, -1); return $p1;//TIMESTAMP } -sqlite_createQueryFunction( 'REGEXP', 'sqlite_userfunc_REGEXP'); +var $define_REGEXP='sqlite_userfunc_REGEXP'; function sqlite_userfunc_REGEXP($p1,$p2){ return preg_match ("/$p2/",$p1); } -sqlite_createQueryFunction( 'DAYOFMONTH', 'sqlite_userfunc_DAYOFMONTH'); +var $define_DAYOFMONTH='sqlite_userfunc_DAYOFMONTH'; function sqlite_userfunc_DAYOFMONTH($p1){ - sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); + SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); return $dy; } -sqlite_createQueryFunction( 'MONTH', 'sqlite_userfunc_MONTH'); +var $define_MONTH='sqlite_userfunc_MONTH'; function sqlite_userfunc_MONTH($p1){ - sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); + SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); return $mt; } @@ -817,7 +745,7 @@ function sqlite_resolvedatetime($p1,&$yr,&$mt,&$dy,&$hr,&$mn,&$sc){ } function sqlite_ADDDATE($p1,$p2,$p3){ - sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); + SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc); $a=explode(' ',preg_replace('/[^0-9]/',' ',trim((string)$p2))); switch(strtoupper($p3)) { case'SECOND': // SECONDS @@ -876,7 +804,7 @@ function sqlite_ADDDATE($p1,$p2,$p3){ } // For creating table structure (table and index/indeces) -sqlite_createQueryFunction( 'sqlite_table_structure', 'sqlite_userfunc_sqlite_table_structure'); +var $define_sqlite_table_structure='sqlite_userfunc_sqlite_table_structure'; function sqlite_userfunc_sqlite_table_structure($p1){ global $SQLITE_DBHANDLE; $ret=''; @@ -886,4 +814,100 @@ function sqlite_userfunc_sqlite_table_structure($p1){ return $ret; } + +// Modification of query for some functions. +function sqlite_modifyQueryForUserFunc(&$query,$strpositions,$pattern=null,$replacement=null){ + // Write this part very carefully. Otherwise, you may allow crackers to do SQL-injection. + global $SQLITE_MQFUFCB_OK,$SQLITE_MQFUFCB_COUNT,$SQLITE_MQFUFCB_REPLACE; + + // Store the previous string + $orgstrings=array(); + foreach ($strpositions as $start => $end) array_push($orgstrings, trim(substr($query,$start,$end-$start))); + + $lquery=strtolower($query); + if (!$pattern) $pattern=array(); + if (!$replacement) $replacement=array(); + + // match() against() support. Following way does NOT accept SQL-injection. Note that the string is always quoted by "'". + array_push($pattern,'/match \(([^\']*?)\) against \(/i'); + array_push($replacement,'match_against ($1,'); + // REGEXP support + if (strpos($lquery,'regexp')!==false) { + array_push($pattern,'/([^a-z_\.])([a-z_\.]+)[\s]+REGEXP[\s]+\'([^\']*?)\'([^\']?)/i'); + array_push($replacement,'$1regexp($2,\'$3\')$4'); + } + // ADDDATE/SUBDATE support (INTERVAL support) + array_push($pattern,'/([^a-zA-Z_])ADDDATE[\s]*?\(([^,]+),[\s]*?INTERVAL[\s]+([\S]+)[\s]+([^\)]+)\)/i'); + array_push($replacement,'$1adddate($2,$3,\'$4\')'); + array_push($pattern,'/([^a-zA-Z_])DATE_ADD[\s]*?\(([^,]+),[\s]*?INTERVAL[\s]+([\S]+)[\s]+([^\)]+)\)/i'); + array_push($replacement,'$1adddate($2,$3,\'$4\')'); + array_push($pattern,'/([^a-zA-Z_])SUBDATE[\s]*?\(([^,]+),[\s]*?INTERVAL[\s]+([\S]+)[\s]+([^\)]+)\)/i'); + array_push($replacement,'$1subdate($2,$3,\'$4\')'); + array_push($pattern,'/([^a-zA-Z_])DATE_SUB[\s]*?\(([^,]+),[\s]*?INTERVAL[\s]+([\S]+)[\s]+([^\)]+)\)/i'); + array_push($replacement,'$1subdate($2,$3,\'$4\')'); + + // EXTRACT support + array_push($pattern,'/([^a-zA-Z_])EXTRACT[\s]*?\(([a-zA-Z_]+)[\s]+FROM/i'); + array_push($replacement,'$1extract(\'$2\','); + + // TRIM support: + array_push($pattern,'/([^a-zA-Z_])TRIM[\s]*?\((BOTH|LEADING|TRAILING)[\s]+FROM/i'); + array_push($replacement,'$1extract(\'$2\',\' \','); + array_push($pattern,'/([^a-zA-Z_])TRIM[\s]*?\((BOTH|LEADING|TRAILING)[\s]+([\s\S]+)[\s]+FROM/i'); + array_push($replacement,'$1extract(\'$2\',$3,'); + + // Change it. + $temp=preg_replace ($pattern,$replacement,$query); + + // Comfirm if strings did not change. + $ok=true; + foreach ($orgstrings as $key=>$value) if ($value) { + if (strpos($temp,$value)!==false) { + // This string is OK, therefore will be ignored in the next "step by step" step. + $orgstrings[$key]=''; + continue; + } + $ok=false; + } + if ($ok) { // return if everything is OK. + $query=$temp; + return; + } + + // At least one of string changed. Need to do step by step. + foreach ($pattern as $key=>$pat) { + // Replace is done step by step for each RegExp replace statement. + $SQLITE_MQFUFCB_REPLACE=$replace[$key];// Set the grobal var. + $num=preg_match_all($pat,$query,$matches); + // First, check if OK. + $replaceOK=array(); + for ($i=1;$i<=$num;$i++) { + $SQLITE_MQFUFCB_OK=array(); + $SQLITE_MQFUFCB_OK[$i]=true; // Set the grobal var. + $SQLITE_MQFUFCB_COUNT=0; // Set the grobal var. + // Only $i-st replacement will be done in the next line. + $temp=preg_replace_callback($pat,array('SQLite_Functions','sqlite_modifyQueryForUserFuncCallBack'), $query, $i); + $ok=true; + foreach ($orgstrings as $value) if ($value) { + if (strpos($temp,$value)!==false) continue; + $ok=false; + break; + } + if ($ok) $replaceOK[$i]=true; + } + // Replace + $SQLITE_MQFUFCB_OK=$replaceOK;// Copy the OK array + $SQLITE_MQFUFCB_COUNT=0; + $query=preg_replace_callback($pat,array('SQLite_Functions','sqlite_modifyQueryForUserFuncCallBack'), $query); + } +} + +function sqlite_modifyQueryForUserFuncCallBack($mathces){ + global $SQLITE_MQFUFCB_OK,$SQLITE_MQFUFCB_COUNT,$SQLITE_MQFUFCB_REPLACE; + if ($SQLITE_MQFUFCB_OK[++$SQLITE_MQFUFCB_COUNT]) return $SQLITE_MQFUFCB_REPLACE; + else return $mathces[0]; +} + +}//class SQLite_QueryFunctions + ?> \ No newline at end of file -- 2.11.0