2 /***************************************
3 * SQLite-MySQL wrapper for Nucleus *
6 ***************************************/
8 // The licence of this script is GPL
10 // The features that are supported by this script but not
11 // generally by SQLite are as follows:
13 // CONCAT, IF, IFNULL, NULLIF, SUBSTRING,
15 // replace, UNIX_TIMESTAMP, REGEXP, DAYOFMONTH, MONTH, YEAR,
16 // ADDDATE, DATE_ADD, SUBDATE, DATE_SUB, FIND_IN_SET,
17 // CURDATE, CURRENT_DATE, CURTIME, CURRENT_TIME, CURRENT_TIMESTAMP,
18 // LOCALTIME, LOCALTIMESTAMP, SYSDATE, DATE_FORMAT, TIME_FORMAT,
19 // DAYNAME, DAYOFWEEK, DAYOFYEAR, EXTRACT, FROM_DAYS, FROM_UNIXTIME,
20 // HOUR, MINUTE, MONTH, MONTHNAME, PERIOD_ADD, PERIOD_DIFF, QUARTER,
21 // SECOND, SEC_TO_TIME, SECOND, WEEK, WEEKDAY, YEAR, YEARWEEK,
22 // FORMAT, INET_ATON, INET_NTOA, MD5,
23 // ACOS, ASIN, ATAN, CEIL, CEILING, COS, COT, CRC32, DEGREES,
24 // EXP, FLOOR, GREATEST, MAX, LEAST, MIN, ln, log, log2, log10,
25 // MOD, PI, POW, POWER, RADIANS, RAND, SIGN, SIN, SQRT, TAN,
26 // ASCII, BIN, BIT_LENGTH, CHAR, CHAR_LENGTH, CONCAT_WS,
27 // CONV, ELT, EXPORT_SET, FIELD, HEX, INSERT, LOCATE,
28 // INSTR, LCASE, LOWER, LEFT, LENGTH, OCTET_LENGTH,
29 // LOAD_FILE, LPAD, LTRIM, MAKE_SET, MID, SUBSTRING,
30 // OCT, ORD, QUOTE, REPEAT, REVERSE, RIGHT, RPAD,
31 // RTRIM, SOUNDEX, SPACE, SUBSTRING_INDEX, TRIM,
35 // Register user-defined functions used in SQL query.
36 // The SQLite_QueryFunctions object is created to register SQLite queries.
37 $SQLITECONF['object']=new SQLite_Functions;
38 // After the registration, the object is not required any more.
39 unset($SQLITECONF['object']);
42 // The class for SQLite user functions
43 class SQLite_Functions {
45 // Constructor is used for the registration of user-defined functions of SQLite
46 function SQLite_Functions(){
47 global $SQLITE_DBHANDLE;
48 foreach($this as $key=>$value){
49 $key=strtoupper($key);
50 if (substr($key,0,7)!='DEFINE_') continue;
52 if (substr($value,0,7)=='sqlite_') $value=array('SQLite_Functions',$value);
53 @sqlite_create_function($SQLITE_DBHANDLE,$key,$value);
57 var $define_ASCII='ord';
59 var $define_BIN='decbin';
61 var $define_BIT_LENGTH='sqlite_userfunc_BIT_LENGTH';
62 function sqlite_userfunc_BIT_LENGTH($p1){
66 var $define_CHAR='sqlite_userfunc_CHAR';
67 function sqlite_userfunc_CHAR(){
68 if (!($lastnum=func_num_args())) return null;
69 $args=&func_get_args();
71 for ($i=0;$i<$lastnum;$i++) {
72 if ($args[$i]!==null) $ret.=chr($args[$i]);
77 var $define_CHAR_LENGTH='mb_strlen';
79 var $define_CONCAT_WS='sqlite_userfunc_CONCAT_WS';
80 function sqlite_userfunc_CONCAT_WS(){
81 if (($lastnum=func_num_args())<2) return null;
82 $args=&func_get_args();
83 if ($args[0]===null) return null;
85 for ($i=1;$i<$lastnum;$i++) {
86 if ($args[$i]===null) continue;
87 if ($ret) $ret.=$args[0];
88 $ret.=(string)($args[$i]);
93 var $define_CONV='sqlite_userfunc_CONV';
94 function sqlite_userfunc_CONV($p1,$p2,$p3){
95 $t36='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
96 $p1=strtoupper(trim((string)$p1));
97 if ($p3<0 && substr($p1,0,1)=='-') {
103 for ($i=0;$i<strlen($p1);$i++) $v=$v*$p2+strpos($t36,$p1[$i]);
114 var $define_ELT='sqlite_userfunc_ELT';
115 function sqlite_userfunc_ELT(){
116 if (($lastnum=func_num_args())<2) return null;
117 $args=&func_get_args();
118 if ($args[0]<1 || $lastnum<$args[0]) return null;
119 return $args[$args[0]];
122 var $define_EXPORT_SET='sqlite_userfunc_EXPORT_SET';
123 function sqlite_userfunc_EXPORT_SET($p1,$p2,$p3,$p4=',',$p5=64){
124 if ($p1<2147483648) $p1=decbin($p1);
125 else $p1=decbin($p1/2147483648).decbin($p1 % 2147483648);
126 $p1=substr(decbin($p1).str_repeat('0',$p5),0,$p5);
127 $p1=str_replace(array('1','0'),array($p2.$p4,$p3.$p4),$p1);
128 return substr($p1,0,strlen($p1)-strlen($p4));
131 var $define_FIELD='sqlite_userfunc_FIELD';
132 function sqlite_userfunc_FIELD(){
133 if (($lastnum=func_num_args())<2) return null;
134 $args=&func_get_args();
135 for ($i=1;$i<$lastnum;$i++) if ($args[0]==$args[$i]) return $i;
139 var $define_HEX='sqlite_userfunc_HEX';
140 function sqlite_userfunc_HEX($p1){
141 if (is_numeric($p1)) return dechex ($p1);
144 for($i=0;$i<strlen($p1);$i++) $ret.=substr('0'.dechex($p1[$i]),-2);
148 var $define_INSERT='sqlite_userfunc_INSERT';
149 function sqlite_userfunc_INSERT($p1,$p2,$p3,$p4){
150 if (function_exists('mb_substr')) return mb_substr($p1,0,$p2-1).$p4.mb_substr($p1,$p2+$p3-1);
151 return substr($p1,0,$p2-1).$p4.substr($p1,$p2+$p3-1);
154 var $define_LOCATE='sqlite_userfunc_LOCATE';
155 function sqlite_userfunc_LOCATE($p1,$p2,$p3=1){
156 if (substr($p1,1)=='_') $p2='-'.$p2;
158 if (function_exists('mb_strpos')) return (int)mb_strpos($p2,$p1,$p3);
159 if (($p=strpos(substr($p2,$p3),$p1))===false) return 0;
163 var $define_INSTR='sqlite_userfunc_INSTR';
164 function sqlite_userfunc_INSTR($p1,$p2){
165 return SQLite_Functions::sqlite_userfunc_LOCATE($p2,$p1);
168 var $define_LCASE='sqlite_userfunc_LOWER';
169 var $define_LOWER='sqlite_userfunc_LOWER';
170 function sqlite_userfunc_LOWER($p1){
171 if (function_exists('mb_strtolower')) return mb_strtolower($p1);
172 return strtolower($p1);
175 var $define_LEFT='sqlite_userfunc_LEFT';
176 function sqlite_userfunc_LEFT($p1,$p2){
177 if (function_exists('mb_substr')) return mb_substr($p1,0,$p2);
178 return substr($p1,0,$p2);
181 var $define_LENGTH='strlen';
182 var $define_OCTET_LENGTH='strlen';
184 var $define_LOAD_FILE='sqlite_userfunc_LOAD_FILE';
185 function sqlite_userfunc_LOAD_FILE($p1){
186 if (!file_exists($p1)) return null;
187 if (!is_array($a=@file($p1))) return null;
189 foreach($a as $value) $ret.=$value;
193 var $define_LPAD='sqlite_userfunc_LPAD';
194 function sqlite_userfunc_LPAD($p1,$p2,$p3){
195 return substr(str_repeat($p3,$p2/strlen($p3)).$p1,0-$p2);
198 var $define_LTRIM='ltrim';
200 var $define_MAKE_SET='sqlite_userfunc_MAKE_SET';
201 function sqlite_userfunc_MAKE_SET($p1,$p2,$p3){
202 if (($lastnum=func_num_args())<2) return null;
203 $args=&func_get_args();
205 if ($args[0]<2147483648) $bits=decbin($args[0]);
206 else $bits=decbin($args[0]/2147483648).decbin($args[0] % 2147483648);
207 for ($i=1;$i<$lastnum;$i++) {
208 if ($bits[strlen($bits)-$i]=='1' && $args[$i]) {
216 var $define_MID='sqlite_userfunc_SUBSTRING';
217 var $define_SUBSTRING='sqlite_userfunc_SUBSTRING';
218 function sqlite_userfunc_SUBSTRING($p1,$p2,$p3=null){
220 if (function_exists('mb_substr')) {
221 if ($p3) return mb_substr($p1,$p2,$p3);
222 return mb_substr($p1,$p2);
224 if ($p3) return substr($p1,$p2,$p3);
225 return substr($p1,$p2);
228 var $define_OCT='sqlite_userfunc_OCT';
229 function sqlite_userfunc_OCT($p1){
230 if ($p1===null) return null;
231 return SQLite_Functions::sqlite_userfunc_CONV($p1,10,8);
234 var $define_ORD='sqlite_userfunc_ORD';
235 function sqlite_userfunc_ORD($p1){
236 if (function_exists('mb_substr')) $p1=mb_substr($p1,0,1);
237 else $p1=substr($p1,0,1);
239 for ($i=0;$i<strlen($p1);$i++) $ret=$ret*256+ord($p1[$i]);
243 var $define_QUOTE='sqlite_userfunc_QUOTE';
244 function sqlite_userfunc_QUOTE($p1){
245 if ($p1===null) return 'NULL';
246 return str_replace(array("'","\\","\x1A"),array("\\'","\\\\","\\z"),$p1);
249 var $define_REPEAT='str_repeat';
251 var $define_REVERSE='sqlite_userfunc_REVERSE';
252 function sqlite_userfunc_REVERSE($p1){
253 if (function_exists('mb_strlen')) {
255 for ($i=mb_strlen($p1)-1;0<=$i;$i++) $ret.=mb_substr($p1,$i,1);
261 var $define_RIGHT='sqlite_userfunc_RIGHT';
262 function sqlite_userfunc_RIGHT($p1){
263 if (function_exists('mb_substr')) return mb_substr($p1,0-$p2);
264 return substr($p1,0-$p2);
267 var $define_RPAD='sqlite_userfunc_RPAD';
268 function sqlite_userfunc_RPAD($p1,$p2,$p3){
269 return substr($p1.str_repeat($p3,$p2/strlen($p3)),0,$p2);
272 var $define_RTRIM='rtrim';
273 var $define_SOUNDEX='soundex';
275 var $define_SPACE='sqlite_userfunc_SPACE';
276 function sqlite_userfunc_SPACE($p1){
277 return str_repeat(' ',$p1);
280 var $define_SUBSTRING_INDEX='sqlite_userfunc_SUBSTRING_INDEX';
281 function sqlite_userfunc_SUBSTRING_INDEX($p1,$p2,$p3){
282 if (!is_array($a=explode($p2,$p1))) return null;
285 for ($i=0;$i<$p3;$i++) {
290 for ($i=0;$i<0-$p3;$i++) {
292 $ret.=$a[count($a)-1-$i];
298 var $define_TRIM='sqlite_userfunc_TRIM';
299 function sqlite_userfunc_TRIM($p1,$p2=null,$p3=null){
300 if (!$p2 && !$p3) return trim($p1);
302 switch(strtoupper($p1)){
304 while (strpos($p3,$p2)===0) $p3=substr($p3,strlen($p2));
306 while (strrpos($p3,$p2)===strlen($p3)-strlen($p2)-1) $p3=substr($p3,0,strlen($p3)-strlen($p2));
309 while (strpos($p3,$p2)===0) $p3=substr($p3,strlen($p2));
315 var $define_UCASE='sqlite_userfunc_UPPER';
316 var $define_UPPER='sqlite_userfunc_UPPER';
317 function sqlite_userfunc_UPPER($p1){
318 if (function_exists('mb_strtoupper')) return mb_strtoupper($p1);
319 return strtoupper($p1);
322 var $define_ACOS='acos';
323 var $define_ASIN='asin';
325 var $define_ATAN='sqlite_userfunc_ATAN';
326 function sqlite_userfunc_ATAN($p1,$p2=null){
327 if (!$p2) return atan($p1);
328 if ($p1>0 && $p2>0) return atan($p1/$p2);
329 else if ($p1>0 && $p2<0) return pi-atan(-$p1/$p2);
330 else if ($p1<0 && $p2<0) return pi+atan($p1/$p2);
331 else if ($p1<0 && $p2>0) return 2*pi-atan(-$p1/$p2);
335 var $define_CEIL='ceil';
336 var $define_CEILING='ceil';
337 var $define_COS='cos';
339 var $define_COT='sqlite_userfunc_COT';
340 function sqlite_userfunc_COT($p1){
344 var $define_CRC32='crc32';
346 var $define_DEGREES='sqlite_userfunc_DEGREES';
347 function sqlite_userfunc_DEGREES($p1){
351 var $define_EXP='exp';
352 var $define_FLOOR='floor';
353 var $define_GREATEST='max';
354 var $define_MAX='max';
355 var $define_LEAST='min';
356 var $define_MIN='min';
357 var $define_ln='log';
359 var $define_log='sqlite_userfunc_LOG';
360 function sqlite_userfunc_LOG($p1,$p2=null){
361 if ($p2) return log($p1)/log($p2);
365 var $define_log2='sqlite_userfunc_LOG2';
366 function sqlite_userfunc_LOG2($p1){
367 return log($p1)/log(2);
370 var $define_log10='log10';
372 var $define_MOD='sqlite_userfunc_MOD';
373 function sqlite_userfunc_MOD($p1,$p2){
377 var $define_PI='sqlite_userfunc_PI';
378 function sqlite_userfunc_PI(){
382 var $define_POW='sqlite_userfunc_POW';
383 var $define_POWER='sqlite_userfunc_POW';
384 function sqlite_userfunc_POW($p1,$p2){
388 var $define_RADIANS='sqlite_userfunc_RADIANS';
389 function sqlite_userfunc_RADIANS($p1){
393 var $define_RAND='sqlite_userfunc_RAND';
394 function sqlite_userfunc_RAND($p1=null){
396 return rand(0,1073741823)/1073741824;
399 var $define_SIGN='sqlite_userfunc_SIGN';
400 function sqlite_userfunc_SIGN($p1){
402 else if ($p1<0) return -1;
406 var $define_SIN='sin';
407 var $define_SQRT='sqrt';
408 var $define_TAN='tan';
410 var $define_TRUNCATE='sqlite_userfunc_TRUNCATE';
411 function sqlite_userfunc_TRUNCATE($p1,$p2){
413 return ((int)($p1*$p2))/$p2;
416 var $define_FORMAT='sqlite_userfunc_FORMAT';
417 function sqlite_userfunc_FORMAT($p1,$p2){
418 return number_format($p1, $p2, '.', ',');
421 var $define_INET_ATON='sqlite_userfunc_INET_ATON';
422 function sqlite_userfunc_INET_ATON($p1){
424 return (($a[0]*256+$a[1])*256+$a[2])*256+$a[3];
427 var $define_INET_NTOA='sqlite_userfunc_INET_NTOA';
428 function sqlite_userfunc_INET_NTOA($p1){
430 for ($i=0;$i<4;$i++){
431 $a[$i]=(string)($p1 % 256);
434 return $a[3].'.'.$a[2].'.'.$a[1].'.'.$a[0];
437 var $define_MD5='md5';
440 var $define_CURDATE='sqlite_userfunc_CURDATE';
441 var $define_CURRENT_DATE='sqlite_userfunc_CURDATE';
442 function sqlite_userfunc_CURDATE(){
443 return date('Y-m-d');
446 var $define_CURTIME='sqlite_userfunc_CURTIME';
447 var $define_CURRENT_TIME='sqlite_userfunc_CURTIME';
448 function sqlite_userfunc_CURTIME(){
449 return date('H:i:s');
452 var $define_CURRENT_TIMESTAMP='sqlite_userfunc_NOW';
453 var $define_LOCALTIME='sqlite_userfunc_NOW';
454 var $define_LOCALTIMESTAMP='sqlite_userfunc_NOW';
455 var $define_SYSDATE='sqlite_userfunc_NOW';
456 function sqlite_userfunc_NOW(){
457 return date('Y-m-d H:i:s');
460 var $define_DATE_FORMAT='sqlite_userfunc_DATE_FORMAT';
461 var $define_TIME_FORMAT='sqlite_userfunc_DATE_FORMAT';
462 function sqlite_userfunc_DATE_FORMAT($p1,$p2){
463 $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
464 $func='if ($matches=="%") return "%";';
465 $func='return date($matches,'.$t.');';
466 return preg_replace_callback ('/%([%a-zA-Z])/',create_function('$matches',$func), $p2);
469 var $define_DAYNAME='sqlite_userfunc_DAYNAME';
470 function sqlite_userfunc_DAYNAME($p1){
471 $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
475 var $define_DAYOFWEEK='sqlite_userfunc_DAYOFWEEK';
476 function sqlite_userfunc_DAYOFWEEK($p1){
477 $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
478 return date('w',$t)+1;
481 var $define_DAYOFYEAR='sqlite_userfunc_DAYOFYEAR';
482 function sqlite_userfunc_DAYOFYEAR($p1){
483 $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
487 var $define_EXTRACT='sqlite_userfunc_EXTRACT';
488 function sqlite_userfunc_EXTRACT($p1,$p2){
489 $t=SQLite_Functions::sqlite_resolvedatetime($p2,$yr,$mt,$dy,$hr,$mn,$sc);
490 switch(strtoupper($p1)) {
491 case'SECOND': // SECONDS
493 case'MINUTE': // MINUTES
497 case'MONTH': // MONTHS
501 case'MINUTE_SECOND': // 'MINUTES:SECONDS'
502 return date('is',$t);
503 case'HOUR_MINUTE': // 'HOURS:MINUTES'
504 return date('Hi',$t);
505 case'DAY_HOUR': // 'DAYS HOURS'
506 return date('dH',$t);
507 case'YEAR_MONTH': // 'YEARS-MONTHS'
508 return date('Ym',$t);
509 case'HOUR_SECOND': // 'HOURS:MINUTES:SECONDS'
510 return date('Hs',$t);
511 case'DAY_MINUTE': // 'DAYS HOURS:MINUTES'
512 return date('di',$t);
513 case'DAY_SECOND': // 'DAYS HOURS:MINUTES:SECONDS'
514 return date('ds',$t);
521 var $define_FROM_DAYS='sqlite_userfunc_FROM_DAYS';
522 function sqlite_userfunc_FROM_DAYS($p1){
523 return date('Y-m-d',($p1-719528)*86400);
526 var $define_FROM_UNIXTIME='sqlite_userfunc_FROM_UNIXTIME';
527 function sqlite_userfunc_FROM_UNIXTIME($p1,$p2=null){
528 if ($p2) return sqlite_userfunc_DATE_FORMAT($p1,$p2);
529 return date('Y-m-d H:i:s',$p1);
532 var $define_HOUR='sqlite_userfunc_HOUR';
533 function sqlite_userfunc_HOUR($p1){
534 SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
538 var $define_MINUTE='sqlite_userfunc_MINUTE';
539 function sqlite_userfunc_MINUTE($p1){
540 SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
544 var $define_MONTHNAME='sqlite_userfunc_MONTHNAME';
545 function sqlite_userfunc_MONTHNAME($p1){
546 $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
551 var $define_PERIOD_ADD='sqlite_userfunc_PERIOD_ADD';
552 function sqlite_userfunc_PERIOD_ADD($p1,$p2){
555 $t=mktime(0,0,0,$m+$p2,1,$y, -1);
556 return date('Ym',$t);
559 var $define_PERIOD_DIFF='sqlite_userfunc_PERIOD_DIFF';
560 function sqlite_userfunc_PERIOD_DIFF($p1,$p2){
565 $t1=mktime(0,0,0,$m1,1,$y1, -1);
566 $t2=mktime(0,0,0,$m2,1,$y2, -1);
569 return (int)(mktime(0,0,0,$m1-$m2,1,1970+$y1-$y2, -1)/60/60/24/28);
572 var $define_QUARTER='sqlite_userfunc_QUARTER';
573 function sqlite_userfunc_QUARTER($p1){
574 SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
576 case 1: case 2: case 3: return 1;
577 case 4: case 5: case 6: return 2;
578 case 7: case 8: case 9: return 3;
583 var $define_SECOND='sqlite_userfunc_SECOND';
584 function sqlite_userfunc_SECOND($p1){
585 SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
589 var $define_SEC_TO_TIME='sqlite_userfunc_SEC_TO_TIME';
590 function sqlite_userfunc_SEC_TO_TIME($p1){
591 return date('H:i:s',$p1);
594 var $define_WEEK='sqlite_userfunc_WEEK';
595 function sqlite_userfunc_WEEK($p1){
596 $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
600 var $define_WEEKDAY='sqlite_userfunc_WEEKDAY';
601 function sqlite_userfunc_WEEKDAY($p1){
602 $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
603 if (0<($w=date('w',$t))) return $w-1;
607 var $define_YEAR='sqlite_userfunc_YEAR';
608 function sqlite_userfunc_YEAR($p1){
609 $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
613 var $define_YEARWEEK='sqlite_userfunc_YEARWEEK';
614 function sqlite_userfunc_YEARWEEK($p1){
615 $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
616 return date('YW',$t);
620 var $define_FIND_IN_SET='sqlite_userfunc_FIND_IN_SET';
621 function sqlite_userfunc_FIND_IN_SET($p1,$p2){
622 if ($p1==null && $p2==null) return null;
624 foreach (explode(',',$p2) as $key=>$value) if ($value==$p1) return ($key+1);
629 var $define_ADDDATE='sqlite_userfunc_ADDDATE';
630 function sqlite_userfunc_ADDDATE($p1,$p2,$p3='DAY'){
631 return date("Y-m-d",sqlite_ADDDATE($p1,$p2,$p3));
634 var $define_SUBDATE='sqlite_userfunc_SUBDATE';
635 function sqlite_userfunc_SUBDATE($p1,$p2,$p3='DAY'){
636 return date("Y-m-d",sqlite_ADDDATE($p1,0-$p2,$p3));
639 var $define_CONCAT='sqlite_userfunc_CONCAT';
640 function sqlite_userfunc_CONCAT(){
641 if (!($lastnum=func_num_args())) return null;
642 $args=&func_get_args();
644 for ($i=0;$i<$lastnum;$i++) {
645 if ($args[$i]===null) return null;
646 $ret.=(string)($args[$i]);
651 var $define_IF='sqlite_userfunc_IF';
652 function sqlite_userfunc_IF($p1,$p2,$p3){
653 if ((int)$p1) return $p2;
657 var $define_IFNULL='sqlite_userfunc_IFNULL';
658 function sqlite_userfunc_IFNULL($p1,$p2){
659 if ($p1!=null) return $p1;
663 var $define_NULLIF='sqlite_userfunc_NULLIF';
664 function sqlite_userfunc_NULLIF($p1,$p2){
665 if ($p1==$p2) return null;
670 var $define_match_against='sqlite_userfunc_match_against';
671 function sqlite_userfunc_match_against(){
672 if (!($lastnum=func_num_args())) return 0;
673 if (!(--$lastnum)) return 0;
674 $args=&func_get_args();
675 if (!$args[$lastnum]) return 0;
676 $pattern='/'.quotemeta($args[$lastnum]).'/i';
678 for($i=0;$i<$lastnum;$i++) $ret=$ret+preg_match_all ($pattern,$args[$i],$matches);
682 var $define_replace='sqlite_userfunc_replace';
683 function sqlite_userfunc_replace($p1,$p2,$p3){
684 return str_replace($p3,$p1,$p2);
687 var $define_UNIX_TIMESTAMP='sqlite_userfunc_UNIX_TIMESTAMP';
688 function sqlite_userfunc_UNIX_TIMESTAMP($p1=null){
689 if (!$p1) return time();
690 SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
691 if ($yr) return mktime($hr,$mn,$sc,$mt,$dy,$yr, -1);
692 return $p1;//TIMESTAMP
695 var $define_REGEXP='sqlite_userfunc_REGEXP';
696 function sqlite_userfunc_REGEXP($p1,$p2){
697 return preg_match ("/$p2/",$p1);
700 var $define_DAYOFMONTH='sqlite_userfunc_DAYOFMONTH';
701 function sqlite_userfunc_DAYOFMONTH($p1){
702 SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
706 var $define_MONTH='sqlite_userfunc_MONTH';
707 function sqlite_userfunc_MONTH($p1){
708 SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
713 function sqlite_resolvedatetime($p1,&$yr,&$mt,&$dy,&$hr,&$mn,&$sc){
715 if (preg_match('/^([0-9]{4})-([0-9]{2})-([0-9]{2}) ([0-9]{2}):([0-9]{2}):([0-9]{2})$/', $t,$matches)) {
717 $yr=(int)$matches[1];
718 $mt=(int)$matches[2];
719 $dy=(int)$matches[3];
720 $hr=(int)$matches[4];
721 $mn=(int)$matches[5];
722 $sc=(int)$matches[6];
723 } else if (preg_match('/^([0-9]{4})-([0-9]{2})-([0-9]{2})$/', $t,$matches)) {
725 $yr=(int)$matches[1];
726 $mt=(int)$matches[2];
727 $dy=(int)$matches[3];
729 } else if (preg_match('/^([0-9]{4})([0-9]{2})([0-9]{2})$/', $t,$matches)) {
731 $yr=(int)$matches[1];
732 $mt=(int)$matches[2];
733 $dy=(int)$matches[3];
735 } else if (preg_match('/^([0-9]{2})([0-9]{2})([0-9]{2})$/', $t,$matches)) {
737 $yr=(int)$matches[1];
738 $mt=(int)$matches[2];
739 $dy=(int)$matches[3];
741 if ($yr<70) $yr=$yr+2000;
744 return mktime($hr,$mn,$sc,$mt,$dy,$yr, -1);
747 function sqlite_ADDDATE($p1,$p2,$p3){
748 SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
749 $a=explode(' ',preg_replace('/[^0-9]/',' ',trim((string)$p2)));
750 switch(strtoupper($p3)) {
751 case'SECOND': // SECONDS
754 case'MINUTE': // MINUTES
760 case'MONTH': // MONTHS
766 case'MINUTE_SECOND': // 'MINUTES:SECONDS'
770 case'HOUR_MINUTE': // 'HOURS:MINUTES'
774 case'DAY_HOUR': // 'DAYS HOURS'
778 case'YEAR_MONTH': // 'YEARS-MONTHS'
782 case'HOUR_SECOND': // 'HOURS:MINUTES:SECONDS'
787 case'DAY_MINUTE': // 'DAYS HOURS:MINUTES'
792 case'DAY_SECOND': // 'DAYS HOURS:MINUTES:SECONDS'
803 return mktime($hr,$mn,$sc,$mt,$dy,$yr, -1);
806 // For creating table structure (table and index/indeces)
807 var $define_sqlite_table_structure='sqlite_userfunc_sqlite_table_structure';
808 function sqlite_userfunc_sqlite_table_structure($p1){
809 global $SQLITE_DBHANDLE;
811 if ($res=sqlite_query($SQLITE_DBHANDLE,"SELECT sql FROM sqlite_master WHERE tbl_name='$p1'")) {
812 while ($array=sqlite_fetch_array($res,SQLITE_NUM)) $ret.=$array[0].";\n";
818 // Modification of query for some functions.
819 function sqlite_modifyQueryForUserFunc(&$query,$strpositions,$pattern=null,$replacement=null){
820 // Write this part very carefully. Otherwise, you may allow crackers to do SQL-injection.
821 global $SQLITE_MQFUFCB_OK,$SQLITE_MQFUFCB_COUNT,$SQLITE_MQFUFCB_REPLACE;
823 // Store the previous string
825 foreach ($strpositions as $start => $end) array_push($orgstrings, trim(substr($query,$start,$end-$start)));
827 $lquery=strtolower($query);
828 if (!$pattern) $pattern=array();
829 if (!$replacement) $replacement=array();
831 // match() against() support. Following way does NOT accept SQL-injection. Note that the string is always quoted by "'".
832 array_push($pattern,'/match \(([^\']*?)\) against \(/i');
833 array_push($replacement,'match_against ($1,');
835 if (strpos($lquery,'regexp')!==false) {
836 array_push($pattern,'/([^a-z_\.])([a-z_\.]+)[\s]+REGEXP[\s]+\'([^\']*?)\'([^\']?)/i');
837 array_push($replacement,'$1regexp($2,\'$3\')$4');
839 // ADDDATE/SUBDATE support (INTERVAL support)
840 array_push($pattern,'/([^a-zA-Z_])ADDDATE[\s]*?\(([^,]+),[\s]*?INTERVAL[\s]+([\S]+)[\s]+([^\)]+)\)/i');
841 array_push($replacement,'$1adddate($2,$3,\'$4\')');
842 array_push($pattern,'/([^a-zA-Z_])DATE_ADD[\s]*?\(([^,]+),[\s]*?INTERVAL[\s]+([\S]+)[\s]+([^\)]+)\)/i');
843 array_push($replacement,'$1adddate($2,$3,\'$4\')');
844 array_push($pattern,'/([^a-zA-Z_])SUBDATE[\s]*?\(([^,]+),[\s]*?INTERVAL[\s]+([\S]+)[\s]+([^\)]+)\)/i');
845 array_push($replacement,'$1subdate($2,$3,\'$4\')');
846 array_push($pattern,'/([^a-zA-Z_])DATE_SUB[\s]*?\(([^,]+),[\s]*?INTERVAL[\s]+([\S]+)[\s]+([^\)]+)\)/i');
847 array_push($replacement,'$1subdate($2,$3,\'$4\')');
850 array_push($pattern,'/([^a-zA-Z_])EXTRACT[\s]*?\(([a-zA-Z_]+)[\s]+FROM/i');
851 array_push($replacement,'$1extract(\'$2\',');
854 array_push($pattern,'/([^a-zA-Z_])TRIM[\s]*?\((BOTH|LEADING|TRAILING)[\s]+FROM/i');
855 array_push($replacement,'$1extract(\'$2\',\' \',');
856 array_push($pattern,'/([^a-zA-Z_])TRIM[\s]*?\((BOTH|LEADING|TRAILING)[\s]+([\s\S]+)[\s]+FROM/i');
857 array_push($replacement,'$1extract(\'$2\',$3,');
860 $temp=preg_replace ($pattern,$replacement,$query);
862 // Comfirm if strings did not change.
864 foreach ($orgstrings as $key=>$value) if ($value) {
865 if (strpos($temp,$value)!==false) {
866 // This string is OK, therefore will be ignored in the next "step by step" step.
867 $orgstrings[$key]='';
872 if ($ok) { // return if everything is OK.
877 // At least one of string changed. Need to do step by step.
878 foreach ($pattern as $key=>$pat) {
879 // Replace is done step by step for each RegExp replace statement.
880 $SQLITE_MQFUFCB_REPLACE=$replace[$key];// Set the grobal var.
881 $num=preg_match_all($pat,$query,$matches);
882 // First, check if OK.
884 for ($i=1;$i<=$num;$i++) {
885 $SQLITE_MQFUFCB_OK=array();
886 $SQLITE_MQFUFCB_OK[$i]=true; // Set the grobal var.
887 $SQLITE_MQFUFCB_COUNT=0; // Set the grobal var.
888 // Only $i-st replacement will be done in the next line.
889 $temp=preg_replace_callback($pat,array('SQLite_Functions','sqlite_modifyQueryForUserFuncCallBack'), $query, $i);
891 foreach ($orgstrings as $value) if ($value) {
892 if (strpos($temp,$value)!==false) continue;
896 if ($ok) $replaceOK[$i]=true;
899 $SQLITE_MQFUFCB_OK=$replaceOK;// Copy the OK array
900 $SQLITE_MQFUFCB_COUNT=0;
901 $query=preg_replace_callback($pat,array('SQLite_Functions','sqlite_modifyQueryForUserFuncCallBack'), $query);
905 function sqlite_modifyQueryForUserFuncCallBack($mathces){
906 global $SQLITE_MQFUFCB_OK,$SQLITE_MQFUFCB_COUNT,$SQLITE_MQFUFCB_REPLACE;
907 if ($SQLITE_MQFUFCB_OK[++$SQLITE_MQFUFCB_COUNT]) return $SQLITE_MQFUFCB_REPLACE;
908 else return $mathces[0];
911 }//class SQLite_QueryFunctions