3 /***************************************************************************\
4 * SPIP, Systeme de publication pour l'internet *
6 * Copyright (c) 2001-2012 *
7 * Arnaud Martin, Antoine Pitrou, Philippe Riviere, Emmanuel Saint-James *
9 * Ce programme est un logiciel libre distribue sous licence GNU/GPL. *
10 * Pour plus de details voir le fichier COPYING.txt ou l'aide en ligne. *
11 \***************************************************************************/
14 if (!defined('_ECRIRE_INC_VERSION')) return;
17 * Des fonctions pour les requetes SQL
19 * Voir la liste des fonctions natives : http://www.sqlite.org/lang_corefunc.html
20 * Et la liste des evolutions pour : http://sqlite.org/changes.html
23 // http://doc.spip.org/@_sqlite_init_functions
24 function _sqlite_init_functions(&$sqlite){
26 if (!$sqlite) return false;
30 'CONCAT' => array( '_sqlite_func_concat' ,-1),
31 'CEIL' => array( '_sqlite_func_ceil', 1), // absent de sqlite2
33 'DATE_FORMAT' => array( '_sqlite_func_strftime' ,2),
34 'DAYOFMONTH' => array( '_sqlite_func_dayofmonth' ,1),
36 'EXTRAIRE_MULTI' => array( '_sqlite_func_extraire_multi', 2), // specifique a SPIP/sql_multi()
37 'EXP' => array( 'exp' ,1),//exponentielle
38 'FIND_IN_SET' => array( '_sqlite_func_find_in_set' ,2),
39 'FLOOR' => array( '_sqlite_func_floor', 1), // absent de sqlite2
41 'IF' => array( '_sqlite_func_if' ,3),
42 'INSERT' => array( '_sqlite_func_insert' ,4),
43 'INSTR' => array( '_sqlite_func_instr' ,2),
45 'LEAST' => array( '_sqlite_func_least' ,3),
46 '_LEFT' => array( '_sqlite_func_left' ,2),
47 # 'LENGTH' => array( 'strlen' ,1), // present v1.0.4
48 # 'LOWER' => array( 'strtolower' ,1), // present v2.4
49 # 'LTRIM' => array( 'ltrim' ,1), // present en theorie
51 'NOW' => array( '_sqlite_func_now' ,0),
53 'MD5' => array( 'md5' ,1),
54 'MONTH' => array( '_sqlite_func_month' ,1),
56 'PREG_REPLACE' => array( '_sqlite_func_preg_replace' ,3),
58 'RAND' => array( '_sqlite_func_rand' ,0), // sinon random() v2.4
59 'REGEXP' => array( '_sqlite_func_regexp_match' ,2), // critere REGEXP supporte a partir de v3.3.2
60 //'REGEXP_MATCH' => array( '_sqlite_func_regexp_match' ,2), // critere REGEXP supporte a partir de v3.3.2
62 'RIGHT' => array( '_sqlite_func_right' ,2),
63 # 'RTRIM' => array( 'rtrim' ,1), // present en theorie
65 'SETTYPE' => array( 'settype' ,2), // CAST present en v3.2.3
66 'SQRT' => array( 'sqrt' ,1),
67 'SUBSTRING' => array( '_sqlite_func_substring' /*,3*/), // peut etre appelee avec 2 ou 3 arguments, index base 1 et non 0
69 'TO_DAYS' => array( '_sqlite_func_to_days' ,1),
70 # 'TRIM' => array( 'trim' ,1), // present en theorie
72 'TIMESTAMPDIFF' => array('_sqlite_timestampdiff' /*,3*/),
74 'UNIX_TIMESTAMP'=> array( '_sqlite_func_unix_timestamp' ,1),
75 # 'UPPER' => array( 'strtoupper' ,1), // present v2.4
77 'VIDE' => array( '_sqlite_func_vide' ,0), // du vide pour SELECT 0 as x ... ORDER BY x -> ORDER BY vide()
79 'YEAR' => array( '_sqlite_func_year' ,1)
83 foreach ($fonctions as $f=>$r){
84 _sqlite_add_function($sqlite, $f, $r);
87 #spip_log('functions sqlite chargees ','sqlite.'._LOG_DEBUG);
90 // permet au besoin de charger des fonctions ailleurs par _sqlite_init_functions();
91 // http://doc.spip.org/@_sqlite_add_function
92 function _sqlite_add_function(&$sqlite, &$f, &$r){
93 if (_sqlite_is_version(3, $sqlite)){
95 ?
$sqlite->sqliteCreateFunction($f, $r[0], $r[1])
96 :$sqlite->sqliteCreateFunction($f, $r[0]);
99 ?
sqlite_create_function($sqlite, $f, $r[0], $r[1])
100 :sqlite_create_function($sqlite, $f, $r[0]);
105 // SQLite : fonctions sqlite -> php
106 // entre autre auteurs : mlebas
109 function _sqlite_func_ceil($a) {
113 // http://doc.spip.org/@_sqlite_func_concat
114 function _sqlite_func_concat () {
115 $args = func_get_args();
116 return join('',$args);
120 // http://doc.spip.org/@_sqlite_func_dayofmonth
121 function _sqlite_func_dayofmonth ($d) {
122 return date("d",_sqlite_func_unix_timestamp($d));
126 // http://doc.spip.org/@_sqlite_func_find_in_set
127 function _sqlite_func_find_in_set($num, $set) {
129 foreach (explode(",",$set) as $v) {
130 if ($v == $num) return (++
$rank);
136 function _sqlite_func_floor($a) {
140 // http://doc.spip.org/@_sqlite_func_if
141 function _sqlite_func_if ($bool, $oui, $non) {
142 return ($bool)?
$oui:$non;
147 * INSERT(chaine, index, longueur, chaine) MySQL
148 * Retourne une chaine de caracteres a partir d'une chaine dans laquelle "sschaine"
149 * a ete inseree a la position "index" en remplacant "longueur" caracteres.
151 // http://doc.spip.org/@_sqlite_func_insert
152 function _sqlite_func_insert ($s, $index, $longueur, $chaine) {
156 . substr(substr($s, $index), $longueur);
160 // http://doc.spip.org/@_sqlite_func_instr
161 function _sqlite_func_instr ($s, $search) {
162 return strpos($s,$search);
166 // http://doc.spip.org/@_sqlite_func_least
167 function _sqlite_func_least () {
168 $arg_list = func_get_args();
169 $least = min($arg_list);
170 #spip_log("Passage avec LEAST : $least",'sqlite.'._LOG_DEBUG);
175 // http://doc.spip.org/@_sqlite_func_left
176 function _sqlite_func_left ($s, $lenght) {
177 return substr($s,0,$lenght);
181 // http://doc.spip.org/@_sqlite_func_now
182 function _sqlite_func_now(){
183 $result = date("Y-m-d H:i:s");
184 #spip_log("Passage avec NOW : $result",'sqlite.'._LOG_DEBUG);
189 // http://doc.spip.org/@_sqlite_func_month
190 function _sqlite_func_month ($d) {
191 return date("m",_sqlite_func_unix_timestamp($d));
196 // http://doc.spip.org/@_sqlite_func_preg_replace
197 function _sqlite_func_preg_replace($quoi, $cherche, $remplace) {
198 $return = preg_replace('%'.$cherche.'%', $remplace, $quoi);
199 #spip_log("preg_replace : $quoi, $cherche, $remplace, $return",'sqlite.'._LOG_DEBUG);
204 * Extrait une langue d'un texte <multi>[fr] xxx [en] yyy</multi>
206 * @param string $quoi le texte contenant ou non un multi
207 * @param string $lang la langue a extraire
208 * @return string, l'extrait trouve.
210 function _sqlite_func_extraire_multi($quoi, $lang) {
211 if (!defined('_EXTRAIRE_MULTI'))
212 include_spip('inc/filtres');
213 if (!function_exists('approcher_langue'))
214 include_spip('inc/lang');
215 if (preg_match_all(_EXTRAIRE_MULTI
, $quoi, $regs, PREG_SET_ORDER
)) {
216 foreach ($regs as $reg) {
217 // chercher la version de la langue courante
218 $trads = extraire_trads($reg[1]);
219 if ($l = approcher_langue($trads, $lang)) {
222 $trad = reset($trads);
224 $quoi = str_replace($reg[0], $trad, $quoi);
231 // http://doc.spip.org/@_sqlite_func_rand
232 function _sqlite_func_rand() {
237 // http://doc.spip.org/@_sqlite_func_right
238 function _sqlite_func_right ($s, $length) {
239 return substr($s,0 - $length);
243 // http://doc.spip.org/@_sqlite_func_regexp_match
244 function _sqlite_func_regexp_match($cherche, $quoi) {
245 $return = preg_match('%'.$cherche.'%', $quoi);
246 #spip_log("regexp_replace : $quoi, $cherche, $remplace, $return",'sqlite.'._LOG_DEBUG);
250 // http://doc.spip.org/@_sqlite_func_strftime
251 function _sqlite_func_strftime($date, $conv){
252 return strftime($conv, is_int($date)?
$date:strtotime($date));
256 * Nombre de jour entre 0000-00-00 et $d
257 * http://doc.spip.org/@_sqlite_func_to_days
258 * cf http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_to-days
262 function _sqlite_func_to_days ($d) {
263 $offset = 719528; // nb de jour entre 0000-00-00 et timestamp 0=1970-01-01
264 $result = $offset+
(int)ceil(_sqlite_func_unix_timestamp($d)/(24*3600));
265 #spip_log("Passage avec TO_DAYS : $d, $result",'sqlite.'._LOG_DEBUG);
269 function _sqlite_func_substring($string,$start,$len=null){
270 // SQL compte a partir de 1, php a partir de 0
271 $start = ($start>0)?
$start-1:$start;
273 return substr($string,$start);
275 return substr($string,$start,$len);
279 * Calcul de la difference entre 2 timestamp, exprimes dans l'unite fournie en premier argument
280 * emule https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampdiff
281 * @param string $unit
282 * @param string $date1
283 * @param string $date2
286 function _sqlite_timestampdiff($unit,$date1,$date2){
288 if (function_exists("date_diff")){
289 $d1 = date_create($date1);
290 $d2 = date_create($date2);
291 $diff = date_diff($d1,$d2);
292 $inv = $diff->invert?
-1:1;
295 return $inv*$diff->y
;
297 return $inv*(4*$diff->y+
intval(floor($diff->m
/3)));
299 return $inv*(12*$diff->y+
$diff->m
);
301 return $inv*intval(floor($diff->days
/7));
303 #var_dump($inv*$diff->days);
304 return $inv*$diff->days
;
306 return $inv*(24*$diff->days+
$diff->h
);
308 return $inv*((24*$diff->days+
$diff->h
)*60+
$diff->i
);
310 return $inv*(((24*$diff->days+
$diff->h
)*60+
$diff->i
)*60+
$diff->s
);
312 return $inv*(((24*$diff->days+
$diff->h
)*60+
$diff->i
)*60+
$diff->s
)*1000000;
318 $d1 = strtotime($date1);
319 $d2 = strtotime($date2);
321 $sign = ($diff<0?
-1:1);
322 $diff = $sign * $diff;
326 return $sign*(date('Y',abs($diff))-date('Y',0));
328 return $sign*(4*(date('Y',abs($diff))-date('Y',0))+
intval(floor((date('m',$diff)-1)/3)));
330 return $sign*((date('Y',$diff)-date('Y',0))*12+
date('m',$diff)-1);
332 return intval(floor(($d2-$d1)/3600/7));
334 return intval(floor(($d2-$d1)/3600/24));
336 return intval(floor(($d2-$d1)/3600));
338 return intval(floor(($d2-$d1)/60));
342 return $d2-$d1*1000000;
347 // http://doc.spip.org/@_sqlite_func_unix_timestamp
348 function _sqlite_func_unix_timestamp($d) {
349 //2005-12-02 20:53:53
350 #spip_log("Passage avec UNIX_TIMESTAMP : $d",'sqlite.'._LOG_DEBUG);
351 // mktime ( [int hour [, int minute [, int second [, int month [, int day [, int year [, int is_dst]]]]]]] )
352 if (!$d) return mktime();
353 return strtotime($d);
354 #preg_match(";^([0-9]{4})-([0-9]+)-([0-9]+)\s*(?:([0-9]+)(?::([0-9]+)(?::([0-9]+))?)?)?;", $d, $f);
355 #return mktime($f[4],$f[5],$f[6],$f[2],$f[3],$f[1]);
359 // http://doc.spip.org/@_sqlite_func_year
360 function _sqlite_func_year ($d) {
361 return date("Y",_sqlite_func_unix_timestamp($d));
365 // http://doc.spip.org/@_sqlite_func_vide
366 function _sqlite_func_vide(){