/***************************************************************************\
* SPIP, Systeme de publication pour l'internet *
* *
- * Copyright (c) 2001-2012 *
+ * Copyright (c) 2001-2014 *
* Arnaud Martin, Antoine Pitrou, Philippe Riviere, Emmanuel Saint-James *
* *
* Ce programme est un logiciel libre distribue sous licence GNU/GPL. *
$fonctions = array(
- 'CONCAT' => array( '_sqlite_func_concat' ,2),
+ 'CONCAT' => array( '_sqlite_func_concat' ,-1),
'CEIL' => array( '_sqlite_func_ceil', 1), // absent de sqlite2
'DATE_FORMAT' => array( '_sqlite_func_strftime' ,2),
'TO_DAYS' => array( '_sqlite_func_to_days' ,1),
# 'TRIM' => array( 'trim' ,1), // present en theorie
+ 'TIMESTAMPDIFF' => array('_sqlite_timestampdiff' /*,3*/),
+
'UNIX_TIMESTAMP'=> array( '_sqlite_func_unix_timestamp' ,1),
# 'UPPER' => array( 'strtoupper' ,1), // present v2.4
// http://doc.spip.org/@_sqlite_func_dayofmonth
function _sqlite_func_dayofmonth ($d) {
- return date("d",_sqlite_func_unix_timestamp($d));
+ return _sqlite_func_date("d",$d);
}
// http://doc.spip.org/@_sqlite_func_now
function _sqlite_func_now(){
- $result = date("Y-m-d H:i:s");
- #spip_log("Passage avec NOW : $result",'sqlite.'._LOG_DEBUG);
- return $result;
+ static $now = null;
+ if (is_null($now))
+ $now = date("Y-m-d H:i:s");
+ #spip_log("Passage avec NOW : $now",'sqlite.'._LOG_DEBUG);
+ return $now;
}
// http://doc.spip.org/@_sqlite_func_month
function _sqlite_func_month ($d) {
- return date("m",_sqlite_func_unix_timestamp($d));
+ return _sqlite_func_date("m",$d);
}
// http://doc.spip.org/@_sqlite_func_regexp_match
function _sqlite_func_regexp_match($cherche, $quoi) {
- $return = preg_match('%'.$cherche.'%', $quoi);
+ // optimiser un cas tres courant avec les requetes en base
+ if (!$quoi AND !strlen($quoi)) return false;
+ $u = $GLOBALS['meta']['pcre_u'];
+ $return = preg_match('%'.$cherche.'%imsS'.$u, $quoi);
#spip_log("regexp_replace : $quoi, $cherche, $remplace, $return",'sqlite.'._LOG_DEBUG);
return $return;
}
* @return int
*/
function _sqlite_func_to_days ($d) {
- $offset = 719528; // nb de jour entre 0000-00-00 et timestamp 0=1970-01-01
+ static $offset = 719528; // nb de jour entre 0000-00-00 et timestamp 0=1970-01-01
$result = $offset+(int)ceil(_sqlite_func_unix_timestamp($d)/(24*3600));
#spip_log("Passage avec TO_DAYS : $d, $result",'sqlite.'._LOG_DEBUG);
return $result;
return substr($string,$start,$len);
}
+/**
+ * Calcul de la difference entre 2 timestamp, exprimes dans l'unite fournie en premier argument
+ * emule https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampdiff
+ * @param string $unit
+ * @param string $date1
+ * @param string $date2
+ * @return int
+ */
+function _sqlite_timestampdiff($unit,$date1,$date2){
+ // PHP >= 5.3
+ if (function_exists("date_diff")){
+ $d1 = date_create($date1);
+ $d2 = date_create($date2);
+ $diff = date_diff($d1,$d2);
+ $inv = $diff->invert?-1:1;
+ switch($unit){
+ case "YEAR":
+ return $inv*$diff->y;
+ case "QUARTER":
+ return $inv*(4*$diff->y+intval(floor($diff->m/3)));
+ case "MONTH":
+ return $inv*(12*$diff->y+$diff->m);
+ case "WEEK":
+ return $inv*intval(floor($diff->days/7));
+ case "DAY":
+ #var_dump($inv*$diff->days);
+ return $inv*$diff->days;
+ case "HOUR":
+ return $inv*(24*$diff->days+$diff->h);
+ case "MINUTE":
+ return $inv*((24*$diff->days+$diff->h)*60+$diff->i);
+ case "SECOND":
+ return $inv*(((24*$diff->days+$diff->h)*60+$diff->i)*60+$diff->s);
+ case "MICROSECOND":
+ return $inv*(((24*$diff->days+$diff->h)*60+$diff->i)*60+$diff->s)*1000000;
+ }
+ return 0;
+ }
+ // PHP < 5.3
+ else {
+ $d1 = strtotime($date1);
+ $d2 = strtotime($date2);
+ $diff = $d2 - $d1;
+ $sign = ($diff<0?-1:1);
+ $diff = $sign * $diff;
+ switch($unit){
+ case "YEAR":
+ $diff = $d2-$d1;
+ return $sign*(date('Y',abs($diff))-date('Y',0));
+ case "QUARTER":
+ return $sign*(4*(date('Y',abs($diff))-date('Y',0))+intval(floor((date('m',$diff)-1)/3)));
+ case "MONTH":
+ return $sign*((date('Y',$diff)-date('Y',0))*12+date('m',$diff)-1);
+ case "WEEK":
+ return intval(floor(($d2-$d1)/3600/7));
+ case "DAY":
+ return intval(floor(($d2-$d1)/3600/24));
+ case "HOUR":
+ return intval(floor(($d2-$d1)/3600));
+ case "MINUTE":
+ return intval(floor(($d2-$d1)/60));
+ case "SECOND":
+ return $d2-$d1;
+ case "MICROSECOND":
+ return $d2-$d1*1000000;
+ }
+ }
+}
+
// http://doc.spip.org/@_sqlite_func_unix_timestamp
function _sqlite_func_unix_timestamp($d) {
+ static $mem = array();
+ static $n = 0;
+ if (isset($mem[$d])) return $mem[$d];
+ if ($n++>100) {$mem = array();$n=0;}
+
//2005-12-02 20:53:53
#spip_log("Passage avec UNIX_TIMESTAMP : $d",'sqlite.'._LOG_DEBUG);
- // mktime ( [int hour [, int minute [, int second [, int month [, int day [, int year [, int is_dst]]]]]]] )
- if (!$d) return mktime();
- return strtotime($d);
- #preg_match(";^([0-9]{4})-([0-9]+)-([0-9]+)\s*(?:([0-9]+)(?::([0-9]+)(?::([0-9]+))?)?)?;", $d, $f);
- #return mktime($f[4],$f[5],$f[6],$f[2],$f[3],$f[1]);
+ if (!$d) return $mem[$d] = mktime();
+ // une pile plus grosse n'accelere pas le calcul
+ return $mem[$d] = strtotime($d);
}
// http://doc.spip.org/@_sqlite_func_year
function _sqlite_func_year ($d) {
- return date("Y",_sqlite_func_unix_timestamp($d));
+ return _sqlite_func_date("Y",$d);
}
+/**
+ * version optimisee et memoizee de date() utilisee par
+ * _sqlite_func_year, _sqlite_func_month, _sqlite_func_dayofmonth
+ * @param string $quoi
+ * format : Y, m, ou d
+ * @param int $d
+ * timestamp
+ * @return int
+ */
+function _sqlite_func_date($quoi,$d) {
+ static $mem = array();
+ static $n = 0;
+ if (isset($mem[$d])) return $mem[$d][$quoi];
+ if ($n++>100) {$mem = array();$n=0;}
+
+ $dec = date("Y-m-d",_sqlite_func_unix_timestamp($d));
+ $mem[$d] = array("Y"=>substr($dec,0,4),"m"=>substr($dec,5,2),"d"=>substr($dec,8,2));
+ return $mem[$d][$quoi];
+}
// http://doc.spip.org/@_sqlite_func_vide
function _sqlite_func_vide(){
dépôts / ptitvelo / web / www.git / blobdiff