2 /****************************************
3 * SQLite-MySQL wrapper for Nucleus *
5 * Written by Katsumi License: GPL *
6 ****************************************/
8 // Register user-defined functions used in SQL query.
9 // The SQLite_QueryFunctions object is created to register SQLite queries.
10 $SQLITECONF['object']=new SQLite_Functions;
11 // After the registration, the object is not required any more.
12 unset($SQLITECONF['object']);
15 // The class for SQLite user functions
16 class SQLite_Functions {
18 // Constructor is used for the registration of user-defined functions of SQLite
19 function SQLite_Functions(){
20 global $SQLITE_DBHANDLE;
21 foreach($this as $key=>$value){
22 $key=strtoupper($key);
23 if (substr($key,0,7)!='DEFINE_') continue;
25 if (substr($value,0,7)=='sqlite_') $value=array('SQLite_Functions',$value);
26 @sqlite_create_function($SQLITE_DBHANDLE,$key,$value);
30 var $define_ASCII='ord';
32 var $define_BIN='decbin';
34 var $define_BIT_LENGTH='sqlite_userfunc_BIT_LENGTH';
35 function sqlite_userfunc_BIT_LENGTH($p1){
39 var $define_CHAR='sqlite_userfunc_CHAR';
40 function sqlite_userfunc_CHAR(){
41 if (!($lastnum=func_num_args())) return null;
42 $args=&func_get_args();
44 for ($i=0;$i<$lastnum;$i++) {
45 if ($args[$i]!==null) $ret.=chr($args[$i]);
50 var $define_CHAR_LENGTH='sqlite_userfunc_CHAR_LENGTH';
51 function sqlite_userfunc_CHAR_LENGTH($str){
52 if (function_exists('mb_strlen')) return mb_strlen($str);
56 var $define_CONCAT_WS='sqlite_userfunc_CONCAT_WS';
57 function sqlite_userfunc_CONCAT_WS(){
58 if (($lastnum=func_num_args())<2) return null;
59 $args=&func_get_args();
60 if ($args[0]===null) return null;
62 for ($i=1;$i<$lastnum;$i++) {
63 if ($args[$i]===null) continue;
64 if ($ret) $ret.=$args[0];
65 $ret.=(string)($args[$i]);
70 var $define_CONV='sqlite_userfunc_CONV';
71 function sqlite_userfunc_CONV($p1,$p2,$p3){
72 $t36='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
73 $p1=strtoupper(trim((string)$p1));
74 if ($p3<0 && substr($p1,0,1)=='-') {
80 for ($i=0;$i<strlen($p1);$i++) $v=$v*$p2+strpos($t36,$p1[$i]);
91 var $define_ELT='sqlite_userfunc_ELT';
92 function sqlite_userfunc_ELT(){
93 if (($lastnum=func_num_args())<2) return null;
94 $args=&func_get_args();
95 if ($args[0]<1 || $lastnum<$args[0]) return null;
96 return $args[$args[0]];
99 var $define_EXPORT_SET='sqlite_userfunc_EXPORT_SET';
100 function sqlite_userfunc_EXPORT_SET($p1,$p2,$p3,$p4=',',$p5=64){
101 if ($p1<2147483648) $p1=decbin($p1);
102 else $p1=decbin($p1/2147483648).decbin($p1 % 2147483648);
103 $p1=substr(decbin($p1).str_repeat('0',$p5),0,$p5);
104 $p1=str_replace(array('1','0'),array($p2.$p4,$p3.$p4),$p1);
105 return substr($p1,0,strlen($p1)-strlen($p4));
108 var $define_FIELD='sqlite_userfunc_FIELD';
109 function sqlite_userfunc_FIELD(){
110 if (($lastnum=func_num_args())<2) return null;
111 $args=&func_get_args();
112 for ($i=1;$i<$lastnum;$i++) if ($args[0]==$args[$i]) return $i;
116 var $define_HEX='sqlite_userfunc_HEX';
117 function sqlite_userfunc_HEX($p1){
118 if (is_numeric($p1)) return dechex ($p1);
121 for($i=0;$i<strlen($p1);$i++) $ret.=substr('0'.dechex($p1[$i]),-2);
125 var $define_INSERT='sqlite_userfunc_INSERT';
126 function sqlite_userfunc_INSERT($p1,$p2,$p3,$p4){
127 if (function_exists('mb_substr')) return mb_substr($p1,0,$p2-1).$p4.mb_substr($p1,$p2+$p3-1);
128 return substr($p1,0,$p2-1).$p4.substr($p1,$p2+$p3-1);
131 var $define_LOCATE='sqlite_userfunc_LOCATE';
132 function sqlite_userfunc_LOCATE($p1,$p2,$p3=1){
133 if (substr($p1,1)=='_') $p2='-'.$p2;
135 if (function_exists('mb_strpos')) return (int)mb_strpos($p2,$p1,$p3);
136 if (($p=strpos(substr($p2,$p3),$p1))===false) return 0;
140 var $define_INSTR='sqlite_userfunc_INSTR';
141 function sqlite_userfunc_INSTR($p1,$p2){
142 return SQLite_Functions::sqlite_userfunc_LOCATE($p2,$p1);
145 var $define_LCASE='sqlite_userfunc_LOWER';
146 var $define_LOWER='sqlite_userfunc_LOWER';
147 function sqlite_userfunc_LOWER($p1){
148 if (function_exists('mb_strtolower')) return mb_strtolower($p1);
149 return strtolower($p1);
152 var $define_LEFT='sqlite_userfunc_LEFT';
153 function sqlite_userfunc_LEFT($p1,$p2){
154 if (function_exists('mb_substr')) return mb_substr($p1,0,$p2);
155 return substr($p1,0,$p2);
158 var $define_LENGTH='strlen';
159 var $define_OCTET_LENGTH='strlen';
161 var $define_LOAD_FILE='sqlite_userfunc_LOAD_FILE';
162 function sqlite_userfunc_LOAD_FILE($p1){
163 if (!file_exists($p1)) return null;
164 if (!is_array($a=@file($p1))) return null;
166 foreach($a as $value) $ret.=$value;
170 var $define_LPAD='sqlite_userfunc_LPAD';
171 function sqlite_userfunc_LPAD($p1,$p2,$p3){
172 return substr(str_repeat($p3,$p2/strlen($p3)).$p1,0-$p2);
175 var $define_LTRIM='ltrim';
177 var $define_MAKE_SET='sqlite_userfunc_MAKE_SET';
178 function sqlite_userfunc_MAKE_SET($p1,$p2,$p3){
179 if (($lastnum=func_num_args())<2) return null;
180 $args=&func_get_args();
182 if ($args[0]<2147483648) $bits=decbin($args[0]);
183 else $bits=decbin($args[0]/2147483648).decbin($args[0] % 2147483648);
184 for ($i=1;$i<$lastnum;$i++) {
185 if ($bits[strlen($bits)-$i]=='1' && $args[$i]) {
193 var $define_MID='sqlite_userfunc_SUBSTRING';
194 var $define_SUBSTRING='sqlite_userfunc_SUBSTRING';
195 function sqlite_userfunc_SUBSTRING($p1,$p2,$p3=null){
197 if (function_exists('mb_substr')) {
198 if ($p3) return mb_substr($p1,$p2,$p3);
199 return mb_substr($p1,$p2);
201 if ($p3) return substr($p1,$p2,$p3);
202 return substr($p1,$p2);
205 var $define_OCT='sqlite_userfunc_OCT';
206 function sqlite_userfunc_OCT($p1){
207 if ($p1===null) return null;
208 return SQLite_Functions::sqlite_userfunc_CONV($p1,10,8);
211 var $define_ORD='sqlite_userfunc_ORD';
212 function sqlite_userfunc_ORD($p1){
213 if (function_exists('mb_substr')) $p1=mb_substr($p1,0,1);
214 else $p1=substr($p1,0,1);
216 for ($i=0;$i<strlen($p1);$i++) $ret=$ret*256+ord($p1[$i]);
220 var $define_QUOTE='sqlite_userfunc_QUOTE';
221 function sqlite_userfunc_QUOTE($p1){
222 if ($p1===null) return 'NULL';
223 return str_replace(array("'","\\","\x1A"),array("\\'","\\\\","\\z"),$p1);
226 var $define_REPEAT='str_repeat';
228 var $define_REVERSE='sqlite_userfunc_REVERSE';
229 function sqlite_userfunc_REVERSE($p1){
230 if (function_exists('mb_strlen')) {
232 for ($i=mb_strlen($p1)-1;0<=$i;$i++) $ret.=mb_substr($p1,$i,1);
238 var $define_RIGHT='sqlite_userfunc_RIGHT';
239 function sqlite_userfunc_RIGHT($p1){
240 if (function_exists('mb_substr')) return mb_substr($p1,0-$p2);
241 return substr($p1,0-$p2);
244 var $define_RPAD='sqlite_userfunc_RPAD';
245 function sqlite_userfunc_RPAD($p1,$p2,$p3){
246 return substr($p1.str_repeat($p3,$p2/strlen($p3)),0,$p2);
249 var $define_RTRIM='rtrim';
250 var $define_SOUNDEX='soundex';
252 var $define_SPACE='sqlite_userfunc_SPACE';
253 function sqlite_userfunc_SPACE($p1){
254 return str_repeat(' ',$p1);
257 var $define_SUBSTRING_INDEX='sqlite_userfunc_SUBSTRING_INDEX';
258 function sqlite_userfunc_SUBSTRING_INDEX($p1,$p2,$p3){
259 if (!is_array($a=explode($p2,$p1))) return null;
262 for ($i=0;$i<$p3;$i++) {
267 for ($i=0;$i<0-$p3;$i++) {
269 $ret.=$a[count($a)-1-$i];
275 var $define_TRIM='sqlite_userfunc_TRIM';
276 function sqlite_userfunc_TRIM($p1,$p2=null,$p3=null){
277 if (!$p2 && !$p3) return trim($p1);
279 switch(strtoupper($p1)){
281 while (strpos($p3,$p2)===0) $p3=substr($p3,strlen($p2));
283 while (strrpos($p3,$p2)===strlen($p3)-strlen($p2)-1) $p3=substr($p3,0,strlen($p3)-strlen($p2));
286 while (strpos($p3,$p2)===0) $p3=substr($p3,strlen($p2));
292 var $define_UCASE='sqlite_userfunc_UPPER';
293 var $define_UPPER='sqlite_userfunc_UPPER';
294 function sqlite_userfunc_UPPER($p1){
295 if (function_exists('mb_strtoupper')) return mb_strtoupper($p1);
296 return strtoupper($p1);
299 var $define_ACOS='acos';
300 var $define_ASIN='asin';
302 var $define_ATAN='sqlite_userfunc_ATAN';
303 function sqlite_userfunc_ATAN($p1,$p2=null){
304 if (!$p2) return atan($p1);
305 if ($p1>0 && $p2>0) return atan($p1/$p2);
306 else if ($p1>0 && $p2<0) return pi-atan(-$p1/$p2);
307 else if ($p1<0 && $p2<0) return pi+atan($p1/$p2);
308 else if ($p1<0 && $p2>0) return 2*pi-atan(-$p1/$p2);
312 var $define_CEIL='ceil';
313 var $define_CEILING='ceil';
314 var $define_COS='cos';
316 var $define_COT='sqlite_userfunc_COT';
317 function sqlite_userfunc_COT($p1){
321 var $define_CRC32='crc32';
323 var $define_DEGREES='sqlite_userfunc_DEGREES';
324 function sqlite_userfunc_DEGREES($p1){
328 var $define_EXP='exp';
329 var $define_FLOOR='floor';
330 var $define_GREATEST='max';
331 var $define_MAX='max';
332 var $define_LEAST='min';
333 var $define_MIN='min';
334 var $define_ln='log';
336 var $define_log='sqlite_userfunc_LOG';
337 function sqlite_userfunc_LOG($p1,$p2=null){
338 if ($p2) return log($p1)/log($p2);
342 var $define_log2='sqlite_userfunc_LOG2';
343 function sqlite_userfunc_LOG2($p1){
344 return log($p1)/log(2);
347 var $define_log10='log10';
349 var $define_MOD='sqlite_userfunc_MOD';
350 function sqlite_userfunc_MOD($p1,$p2){
354 var $define_PI='sqlite_userfunc_PI';
355 function sqlite_userfunc_PI(){
359 var $define_POW='sqlite_userfunc_POW';
360 var $define_POWER='sqlite_userfunc_POW';
361 function sqlite_userfunc_POW($p1,$p2){
365 var $define_RADIANS='sqlite_userfunc_RADIANS';
366 function sqlite_userfunc_RADIANS($p1){
370 var $define_RAND='sqlite_userfunc_RAND';
371 function sqlite_userfunc_RAND($p1=null){
373 return rand(0,1073741823)/1073741824;
376 var $define_SIGN='sqlite_userfunc_SIGN';
377 function sqlite_userfunc_SIGN($p1){
379 else if ($p1<0) return -1;
383 var $define_SIN='sin';
384 var $define_SQRT='sqrt';
385 var $define_TAN='tan';
387 var $define_TRUNCATE='sqlite_userfunc_TRUNCATE';
388 function sqlite_userfunc_TRUNCATE($p1,$p2){
390 return ((int)($p1*$p2))/$p2;
393 var $define_FORMAT='sqlite_userfunc_FORMAT';
394 function sqlite_userfunc_FORMAT($p1,$p2){
395 return number_format($p1, $p2, '.', ',');
398 var $define_INET_ATON='sqlite_userfunc_INET_ATON';
399 function sqlite_userfunc_INET_ATON($p1){
401 return (($a[0]*256+$a[1])*256+$a[2])*256+$a[3];
404 var $define_INET_NTOA='sqlite_userfunc_INET_NTOA';
405 function sqlite_userfunc_INET_NTOA($p1){
407 for ($i=0;$i<4;$i++){
408 $a[$i]=(string)($p1 % 256);
411 return $a[3].'.'.$a[2].'.'.$a[1].'.'.$a[0];
414 var $define_MD5='md5';
417 var $define_CURDATE='sqlite_userfunc_CURDATE';
418 var $define_CURRENT_DATE='sqlite_userfunc_CURDATE';
419 function sqlite_userfunc_CURDATE(){
420 return date('Y-m-d');
423 var $define_CURTIME='sqlite_userfunc_CURTIME';
424 var $define_CURRENT_TIME='sqlite_userfunc_CURTIME';
425 function sqlite_userfunc_CURTIME(){
426 return date('H:i:s');
429 var $define_CURRENT_TIMESTAMP='sqlite_userfunc_NOW';
430 var $define_LOCALTIME='sqlite_userfunc_NOW';
431 var $define_LOCALTIMESTAMP='sqlite_userfunc_NOW';
432 var $define_SYSDATE='sqlite_userfunc_NOW';
433 function sqlite_userfunc_NOW(){
434 return date('Y-m-d H:i:s');
437 var $define_DATE_FORMAT='sqlite_userfunc_DATE_FORMAT';
438 var $define_TIME_FORMAT='sqlite_userfunc_DATE_FORMAT';
439 function sqlite_userfunc_DATE_FORMAT($p1,$p2){
440 $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
441 $func='if ($matches=="%") return "%";';
442 $func='return date($matches,'.$t.');';
443 return preg_replace_callback ('/%([%a-zA-Z])/',create_function('$matches',$func), $p2);
446 var $define_DAYNAME='sqlite_userfunc_DAYNAME';
447 function sqlite_userfunc_DAYNAME($p1){
448 $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
452 var $define_DAYOFWEEK='sqlite_userfunc_DAYOFWEEK';
453 function sqlite_userfunc_DAYOFWEEK($p1){
454 $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
455 return date('w',$t)+1;
458 var $define_DAYOFYEAR='sqlite_userfunc_DAYOFYEAR';
459 function sqlite_userfunc_DAYOFYEAR($p1){
460 $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
464 var $define_EXTRACT='sqlite_userfunc_EXTRACT';
465 function sqlite_userfunc_EXTRACT($p1,$p2){
466 $t=SQLite_Functions::sqlite_resolvedatetime($p2,$yr,$mt,$dy,$hr,$mn,$sc);
467 switch(strtoupper($p1)) {
468 case'SECOND': // SECONDS
470 case'MINUTE': // MINUTES
474 case'MONTH': // MONTHS
478 case'MINUTE_SECOND': // 'MINUTES:SECONDS'
479 return date('is',$t);
480 case'HOUR_MINUTE': // 'HOURS:MINUTES'
481 return date('Hi',$t);
482 case'DAY_HOUR': // 'DAYS HOURS'
483 return date('dH',$t);
484 case'YEAR_MONTH': // 'YEARS-MONTHS'
485 return date('Ym',$t);
486 case'HOUR_SECOND': // 'HOURS:MINUTES:SECONDS'
487 return date('Hs',$t);
488 case'DAY_MINUTE': // 'DAYS HOURS:MINUTES'
489 return date('di',$t);
490 case'DAY_SECOND': // 'DAYS HOURS:MINUTES:SECONDS'
491 return date('ds',$t);
498 var $define_FROM_DAYS='sqlite_userfunc_FROM_DAYS';
499 function sqlite_userfunc_FROM_DAYS($p1){
500 return date('Y-m-d',($p1-719528)*86400);
503 var $define_FROM_UNIXTIME='sqlite_userfunc_FROM_UNIXTIME';
504 function sqlite_userfunc_FROM_UNIXTIME($p1,$p2=null){
505 if ($p2) return sqlite_userfunc_DATE_FORMAT($p1,$p2);
506 return date('Y-m-d H:i:s',$p1);
509 var $define_HOUR='sqlite_userfunc_HOUR';
510 function sqlite_userfunc_HOUR($p1){
511 SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
515 var $define_MINUTE='sqlite_userfunc_MINUTE';
516 function sqlite_userfunc_MINUTE($p1){
517 SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
521 var $define_MONTHNAME='sqlite_userfunc_MONTHNAME';
522 function sqlite_userfunc_MONTHNAME($p1){
523 $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
528 var $define_PERIOD_ADD='sqlite_userfunc_PERIOD_ADD';
529 function sqlite_userfunc_PERIOD_ADD($p1,$p2){
532 $t=mktime(0,0,0,$m+$p2,1,$y, -1);
533 return date('Ym',$t);
536 var $define_PERIOD_DIFF='sqlite_userfunc_PERIOD_DIFF';
537 function sqlite_userfunc_PERIOD_DIFF($p1,$p2){
542 $t1=mktime(0,0,0,$m1,1,$y1, -1);
543 $t2=mktime(0,0,0,$m2,1,$y2, -1);
546 return (int)(mktime(0,0,0,$m1-$m2,1,1970+$y1-$y2, -1)/60/60/24/28);
549 var $define_QUARTER='sqlite_userfunc_QUARTER';
550 function sqlite_userfunc_QUARTER($p1){
551 SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
553 case 1: case 2: case 3: return 1;
554 case 4: case 5: case 6: return 2;
555 case 7: case 8: case 9: return 3;
560 var $define_SECOND='sqlite_userfunc_SECOND';
561 function sqlite_userfunc_SECOND($p1){
562 SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
566 var $define_SEC_TO_TIME='sqlite_userfunc_SEC_TO_TIME';
567 function sqlite_userfunc_SEC_TO_TIME($p1){
568 return date('H:i:s',$p1);
571 var $define_WEEK='sqlite_userfunc_WEEK';
572 function sqlite_userfunc_WEEK($p1){
573 $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
577 var $define_WEEKDAY='sqlite_userfunc_WEEKDAY';
578 function sqlite_userfunc_WEEKDAY($p1){
579 $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
580 if (0<($w=date('w',$t))) return $w-1;
584 var $define_YEAR='sqlite_userfunc_YEAR';
585 function sqlite_userfunc_YEAR($p1){
586 $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
590 var $define_YEARWEEK='sqlite_userfunc_YEARWEEK';
591 function sqlite_userfunc_YEARWEEK($p1){
592 $t=SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
593 return date('YW',$t);
597 var $define_FIND_IN_SET='sqlite_userfunc_FIND_IN_SET';
598 function sqlite_userfunc_FIND_IN_SET($p1,$p2){
599 if ($p1==null && $p2==null) return null;
601 foreach (explode(',',$p2) as $key=>$value) if ($value==$p1) return ($key+1);
606 var $define_ADDDATE='sqlite_userfunc_ADDDATE';
607 function sqlite_userfunc_ADDDATE($p1,$p2,$p3='DAY'){
608 return date("Y-m-d",sqlite_ADDDATE($p1,$p2,$p3));
611 var $define_SUBDATE='sqlite_userfunc_SUBDATE';
612 function sqlite_userfunc_SUBDATE($p1,$p2,$p3='DAY'){
613 return date("Y-m-d",sqlite_ADDDATE($p1,0-$p2,$p3));
616 var $define_CONCAT='sqlite_userfunc_CONCAT';
617 function sqlite_userfunc_CONCAT(){
618 if (!($lastnum=func_num_args())) return null;
619 $args=&func_get_args();
621 for ($i=0;$i<$lastnum;$i++) {
622 if ($args[$i]===null) return null;
623 $ret.=(string)($args[$i]);
628 var $define_IF='sqlite_userfunc_IF';
629 function sqlite_userfunc_IF($p1,$p2,$p3){
630 if ((int)$p1) return $p2;
634 var $define_IFNULL='sqlite_userfunc_IFNULL';
635 function sqlite_userfunc_IFNULL($p1,$p2){
636 if ($p1!=null) return $p1;
640 var $define_NULLIF='sqlite_userfunc_NULLIF';
641 function sqlite_userfunc_NULLIF($p1,$p2){
642 if ($p1==$p2) return null;
647 var $define_match_against='sqlite_userfunc_match_against';
648 function sqlite_userfunc_match_against(){
649 if (!($lastnum=func_num_args())) return 0;
650 if (!(--$lastnum)) return 0;
651 $args=&func_get_args();
652 if (!$args[$lastnum]) return 0;
653 $pattern='/'.quotemeta($args[$lastnum]).'/i';
655 for($i=0;$i<$lastnum;$i++) $ret=$ret+preg_match_all ($pattern,$args[$i],$matches);
659 var $define_replace='sqlite_userfunc_replace';
660 function sqlite_userfunc_replace($p1,$p2,$p3){
661 return str_replace($p3,$p1,$p2);
664 var $define_UNIX_TIMESTAMP='sqlite_userfunc_UNIX_TIMESTAMP';
665 function sqlite_userfunc_UNIX_TIMESTAMP($p1=null){
666 if (!$p1) return time();
667 SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
668 if ($yr) return mktime($hr,$mn,$sc,$mt,$dy,$yr, -1);
669 return $p1;//TIMESTAMP
672 var $define_REGEXP='sqlite_userfunc_REGEXP';
673 function sqlite_userfunc_REGEXP($p1,$p2){
674 return preg_match ("/$p2/",$p1);
677 var $define_DAYOFMONTH='sqlite_userfunc_DAYOFMONTH';
678 function sqlite_userfunc_DAYOFMONTH($p1){
679 SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
683 var $define_MONTH='sqlite_userfunc_MONTH';
684 function sqlite_userfunc_MONTH($p1){
685 SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
690 function sqlite_resolvedatetime($p1,&$yr,&$mt,&$dy,&$hr,&$mn,&$sc){
692 if (preg_match('/^([0-9]{4})-([0-9]{2})-([0-9]{2}) ([0-9]{2}):([0-9]{2}):([0-9]{2})$/', $t,$matches)) {
694 $yr=(int)$matches[1];
695 $mt=(int)$matches[2];
696 $dy=(int)$matches[3];
697 $hr=(int)$matches[4];
698 $mn=(int)$matches[5];
699 $sc=(int)$matches[6];
700 } else if (preg_match('/^([0-9]{4})-([0-9]{2})-([0-9]{2})$/', $t,$matches)) {
702 $yr=(int)$matches[1];
703 $mt=(int)$matches[2];
704 $dy=(int)$matches[3];
706 } else if (preg_match('/^([0-9]{4})([0-9]{2})([0-9]{2})$/', $t,$matches)) {
708 $yr=(int)$matches[1];
709 $mt=(int)$matches[2];
710 $dy=(int)$matches[3];
712 } else if (preg_match('/^([0-9]{2})([0-9]{2})([0-9]{2})$/', $t,$matches)) {
714 $yr=(int)$matches[1];
715 $mt=(int)$matches[2];
716 $dy=(int)$matches[3];
718 if ($yr<70) $yr=$yr+2000;
721 return mktime($hr,$mn,$sc,$mt,$dy,$yr, -1);
724 function sqlite_ADDDATE($p1,$p2,$p3){
725 SQLite_Functions::sqlite_resolvedatetime($p1,$yr,$mt,$dy,$hr,$mn,$sc);
726 $a=explode(' ',preg_replace('/[^0-9]/',' ',trim((string)$p2)));
727 switch(strtoupper($p3)) {
728 case'SECOND': // SECONDS
731 case'MINUTE': // MINUTES
737 case'MONTH': // MONTHS
743 case'MINUTE_SECOND': // 'MINUTES:SECONDS'
747 case'HOUR_MINUTE': // 'HOURS:MINUTES'
751 case'DAY_HOUR': // 'DAYS HOURS'
755 case'YEAR_MONTH': // 'YEARS-MONTHS'
759 case'HOUR_SECOND': // 'HOURS:MINUTES:SECONDS'
764 case'DAY_MINUTE': // 'DAYS HOURS:MINUTES'
769 case'DAY_SECOND': // 'DAYS HOURS:MINUTES:SECONDS'
780 return mktime($hr,$mn,$sc,$mt,$dy,$yr, -1);
783 // For creating table structure (table and index/indeces)
784 var $define_sqlite_table_structure='sqlite_userfunc_sqlite_table_structure';
785 function sqlite_userfunc_sqlite_table_structure($p1){
786 global $SQLITE_DBHANDLE;
788 if ($res=sqlite_query($SQLITE_DBHANDLE,"SELECT sql FROM sqlite_master WHERE tbl_name='$p1'")) {
789 while ($array=sqlite_fetch_array($res,SQLITE_NUM)) $ret.=$array[0].";\n";
795 // Modification of query for some functions.
796 function sqlite_modifyQueryForUserFunc(&$query,$strpositions,$pattern=null,$replacement=null){
797 // Write this part very carefully. Otherwise, you may allow crackers to do SQL-injection.
798 global $SQLITE_MQFUFCB_OK,$SQLITE_MQFUFCB_COUNT,$SQLITE_MQFUFCB_REPLACE;
800 // Store the previous string
802 foreach ($strpositions as $start => $end) array_push($orgstrings, trim(substr($query,$start,$end-$start)));
804 $lquery=strtolower($query);
805 if (!$pattern) $pattern=array();
806 if (!$replacement) $replacement=array();
808 // match() against() support. Following way does NOT accept SQL-injection. Note that the string is always quoted by "'".
809 array_push($pattern,'/match \(([^\']*?)\) against \(/i');
810 array_push($replacement,'match_against ($1,');
812 if (strpos($lquery,'regexp')!==false) {
813 array_push($pattern,'/([^a-z_\.])([a-z_\.]+)[\s]+REGEXP[\s]+\'([^\']*?)\'([^\']?)/i');
814 array_push($replacement,'$1regexp($2,\'$3\')$4');
816 // ADDDATE/SUBDATE support (INTERVAL support)
817 array_push($pattern,'/([^a-zA-Z_])ADDDATE[\s]*?\(([^,]+),[\s]*?INTERVAL[\s]+([\S]+)[\s]+([^\)]+)\)/i');
818 array_push($replacement,'$1adddate($2,$3,\'$4\')');
819 array_push($pattern,'/([^a-zA-Z_])DATE_ADD[\s]*?\(([^,]+),[\s]*?INTERVAL[\s]+([\S]+)[\s]+([^\)]+)\)/i');
820 array_push($replacement,'$1adddate($2,$3,\'$4\')');
821 array_push($pattern,'/([^a-zA-Z_])SUBDATE[\s]*?\(([^,]+),[\s]*?INTERVAL[\s]+([\S]+)[\s]+([^\)]+)\)/i');
822 array_push($replacement,'$1subdate($2,$3,\'$4\')');
823 array_push($pattern,'/([^a-zA-Z_])DATE_SUB[\s]*?\(([^,]+),[\s]*?INTERVAL[\s]+([\S]+)[\s]+([^\)]+)\)/i');
824 array_push($replacement,'$1subdate($2,$3,\'$4\')');
827 array_push($pattern,'/([^a-zA-Z_])EXTRACT[\s]*?\(([a-zA-Z_]+)[\s]+FROM/i');
828 array_push($replacement,'$1extract(\'$2\',');
831 array_push($pattern,'/([^a-zA-Z_])TRIM[\s]*?\((BOTH|LEADING|TRAILING)[\s]+FROM/i');
832 array_push($replacement,'$1extract(\'$2\',\' \',');
833 array_push($pattern,'/([^a-zA-Z_])TRIM[\s]*?\((BOTH|LEADING|TRAILING)[\s]+([\s\S]+)[\s]+FROM/i');
834 array_push($replacement,'$1extract(\'$2\',$3,');
837 $temp=preg_replace ($pattern,$replacement,$query);
839 // Comfirm if strings did not change.
841 foreach ($orgstrings as $key=>$value) if ($value) {
842 if (strpos($temp,$value)!==false) {
843 // This string is OK, therefore will be ignored in the next "step by step" step.
844 $orgstrings[$key]='';
849 if ($ok) { // return if everything is OK.
854 // At least one of string changed. Need to do step by step.
855 foreach ($pattern as $key=>$pat) {
856 // Replace is done step by step for each RegExp replace statement.
857 $SQLITE_MQFUFCB_REPLACE=$replace[$key];// Set the grobal var.
858 $num=preg_match_all($pat,$query,$matches);
859 // First, check if OK.
861 for ($i=1;$i<=$num;$i++) {
862 $SQLITE_MQFUFCB_OK=array();
863 $SQLITE_MQFUFCB_OK[$i]=true; // Set the grobal var.
864 $SQLITE_MQFUFCB_COUNT=0; // Set the grobal var.
865 // Only $i-st replacement will be done in the next line.
866 $temp=preg_replace_callback($pat,array('SQLite_Functions','sqlite_modifyQueryForUserFuncCallBack'), $query, $i);
868 foreach ($orgstrings as $value) if ($value) {
869 if (strpos($temp,$value)!==false) continue;
873 if ($ok) $replaceOK[$i]=true;
876 $SQLITE_MQFUFCB_OK=$replaceOK;// Copy the OK array
877 $SQLITE_MQFUFCB_COUNT=0;
878 $query=preg_replace_callback($pat,array('SQLite_Functions','sqlite_modifyQueryForUserFuncCallBack'), $query);
882 function sqlite_modifyQueryForUserFuncCallBack($mathces){
883 global $SQLITE_MQFUFCB_OK,$SQLITE_MQFUFCB_COUNT,$SQLITE_MQFUFCB_REPLACE;
884 if ($SQLITE_MQFUFCB_OK[++$SQLITE_MQFUFCB_COUNT]) return $SQLITE_MQFUFCB_REPLACE;
885 else return $mathces[0];
888 }//class SQLite_QueryFunctions