www/ecrire/req/sqlite_fonctions.php

   1 <?php
   2
   3 /***************************************************************************\
   4  *  SPIP, Systeme de publication pour l'internet                           *
   5  *                                                                         *
   6  *  Copyright (c) 2001-2014                                                *
   7  *  Arnaud Martin, Antoine Pitrou, Philippe Riviere, Emmanuel Saint-James  *
   8  *                                                                         *
   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 \***************************************************************************/
  12
  13
  14 if (!defined('_ECRIRE_INC_VERSION')) return;
  15
  16 /*
  17  * Des fonctions pour les requetes SQL
  18  *
  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
  21  *
  22  */
  23 // http://doc.spip.org/@_sqlite_init_functions
  24 function _sqlite_init_functions(&$sqlite){
  25
  26         if (!$sqlite) return false;
  27
  28
  29         $fonctions = array(
  30                 'CONCAT'                => array( '_sqlite_func_concat'                 ,-1),
  31                 'CEIL'      => array( '_sqlite_func_ceil', 1), // absent de sqlite2
  32
  33                 'DATE_FORMAT'   => array( '_sqlite_func_strftime'               ,2),
  34                 'DAYOFMONTH'    => array( '_sqlite_func_dayofmonth'             ,1),
  35
  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
  40
  41                 'IF'                    => array( '_sqlite_func_if'                     ,3),
  42                 'INSERT'                => array( '_sqlite_func_insert'                 ,4),
  43                 'INSTR'                 => array( '_sqlite_func_instr'                  ,2),
  44
  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
  50
  51                 'NOW'                   => array( '_sqlite_func_now'                    ,0),
  52
  53                 'MD5'                   => array( 'md5'                                                 ,1),
  54                 'MONTH'                 => array( '_sqlite_func_month'                  ,1),
  55
  56                 'PREG_REPLACE'  => array( '_sqlite_func_preg_replace'   ,3),
  57
  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
  61
  62                 'RIGHT'                 => array( '_sqlite_func_right'                  ,2),
  63 #               'RTRIM'                 => array( 'rtrim'                                               ,1), // present en theorie
  64
  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
  68
  69                 'TO_DAYS'               => array( '_sqlite_func_to_days'                ,1),
  70 #               'TRIM'                  => array( 'trim'                                                ,1), // present en theorie
  71
  72                 'TIMESTAMPDIFF' => array('_sqlite_timestampdiff'                /*,3*/),
  73
  74                 'UNIX_TIMESTAMP'=> array( '_sqlite_func_unix_timestamp' ,1),
  75 #               'UPPER'                 => array( 'strtoupper'                                  ,1), // present v2.4
  76
  77                 'VIDE'                  => array( '_sqlite_func_vide'                   ,0), // du vide pour SELECT 0 as x ... ORDER BY x -> ORDER BY vide()
  78
  79                 'YEAR'                  => array( '_sqlite_func_year'                   ,1)
  80         );
  81
  82
  83         foreach ($fonctions as $f=>$r){
  84                 _sqlite_add_function($sqlite, $f, $r);
  85         }
  86
  87         #spip_log('functions sqlite chargees ','sqlite.'._LOG_DEBUG);
  88 }
  89
  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)){
  94                 isset($r[1])
  95                         ?$sqlite->sqliteCreateFunction($f, $r[0], $r[1])
  96                         :$sqlite->sqliteCreateFunction($f, $r[0]);
  97         } else {
  98                 isset($r[1])
  99                         ?sqlite_create_function($sqlite, $f, $r[0], $r[1])
 100                         :sqlite_create_function($sqlite, $f, $r[0]);
 101         }
 102 }
 103
 104 //
 105 // SQLite : fonctions sqlite -> php
 106 // entre autre auteurs : mlebas
 107 //
 108
 109 function _sqlite_func_ceil($a) {
 110         return ceil($a);
 111 }
 112
 113 // http://doc.spip.org/@_sqlite_func_concat
 114 function _sqlite_func_concat () {
 115         $args = func_get_args();
 116         return join('',$args);
 117 }
 118
 119
 120 // http://doc.spip.org/@_sqlite_func_dayofmonth
 121 function _sqlite_func_dayofmonth ($d) {
 122         return _sqlite_func_date("d",$d);
 123 }
 124
 125
 126 // http://doc.spip.org/@_sqlite_func_find_in_set
 127 function _sqlite_func_find_in_set($num, $set) {
 128   $rank=0;
 129   foreach (explode(",",$set) as $v) {
 130    if ($v == $num) return (++$rank);
 131    $rank++;
 132   }
 133   return 0;
 134 }
 135
 136 function _sqlite_func_floor($a) {
 137         return floor($a);
 138 }
 139
 140 // http://doc.spip.org/@_sqlite_func_if
 141 function _sqlite_func_if ($bool, $oui, $non) {
 142     return ($bool)?$oui:$non;
 143 }
 144
 145
 146 /*
 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.
 150  */
 151 // http://doc.spip.org/@_sqlite_func_insert
 152 function _sqlite_func_insert ($s, $index, $longueur, $chaine) {
 153         return
 154                         substr($s,0, $index)
 155                 . $chaine
 156                 . substr(substr($s, $index), $longueur);
 157 }
 158
 159
 160 // http://doc.spip.org/@_sqlite_func_instr
 161 function _sqlite_func_instr ($s, $search) {
 162     return strpos($s,$search);
 163 }
 164
 165
 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);
 171         return $least;
 172 }
 173
 174
 175 // http://doc.spip.org/@_sqlite_func_left
 176 function _sqlite_func_left ($s, $lenght) {
 177     return substr($s,0,$lenght);
 178 }
 179
 180
 181 // http://doc.spip.org/@_sqlite_func_now
 182 function _sqlite_func_now(){
 183         static $now = null;
 184         if (is_null($now))
 185                 $now = date("Y-m-d H:i:s");
 186         #spip_log("Passage avec NOW : $now",'sqlite.'._LOG_DEBUG);
 187         return $now;
 188 }
 189
 190
 191 // http://doc.spip.org/@_sqlite_func_month
 192 function _sqlite_func_month ($d) {
 193         return _sqlite_func_date("m",$d);
 194 }
 195
 196
 197
 198 // http://doc.spip.org/@_sqlite_func_preg_replace
 199 function _sqlite_func_preg_replace($quoi, $cherche, $remplace) {
 200         $return = preg_replace('%'.$cherche.'%', $remplace, $quoi);
 201         #spip_log("preg_replace : $quoi, $cherche, $remplace, $return",'sqlite.'._LOG_DEBUG);
 202         return $return;
 203 }
 204
 205 /**
 206  * Extrait une langue d'un texte <multi>[fr] xxx [en] yyy</multi>
 207  *
 208  * @param string $quoi le texte contenant ou non un multi
 209  * @param string $lang la langue a extraire
 210  * @return string, l'extrait trouve.
 211 **/
 212 function _sqlite_func_extraire_multi($quoi, $lang) {
 213         if (!defined('_EXTRAIRE_MULTI'))
 214                 include_spip('inc/filtres');
 215         if (!function_exists('approcher_langue'))
 216                 include_spip('inc/lang');
 217         if (preg_match_all(_EXTRAIRE_MULTI, $quoi, $regs, PREG_SET_ORDER)) {
 218                 foreach ($regs as $reg) {
 219                         // chercher la version de la langue courante
 220                         $trads = extraire_trads($reg[1]);
 221                         if ($l = approcher_langue($trads, $lang)) {
 222                                 $trad = $trads[$l];
 223                         } else {
 224                                 $trad = reset($trads);
 225                         }
 226                         $quoi = str_replace($reg[0], $trad, $quoi);
 227                 }
 228         }
 229         return $quoi;
 230 }
 231
 232
 233 // http://doc.spip.org/@_sqlite_func_rand
 234 function _sqlite_func_rand() {
 235         return rand();
 236 }
 237
 238
 239 // http://doc.spip.org/@_sqlite_func_right
 240 function _sqlite_func_right ($s, $length) {
 241         return substr($s,0 - $length);
 242 }
 243
 244
 245 // http://doc.spip.org/@_sqlite_func_regexp_match
 246 function _sqlite_func_regexp_match($cherche, $quoi) {
 247         // optimiser un cas tres courant avec les requetes en base
 248         if (!$quoi AND !strlen($quoi)) return false;
 249         $u = $GLOBALS['meta']['pcre_u'];
 250         $return = preg_match('%'.$cherche.'%imsS'.$u, $quoi);
 251         #spip_log("regexp_replace : $quoi, $cherche, $remplace, $return",'sqlite.'._LOG_DEBUG);
 252         return $return;
 253 }
 254
 255 // http://doc.spip.org/@_sqlite_func_strftime
 256 function _sqlite_func_strftime($date, $conv){
 257         return strftime($conv, is_int($date)?$date:strtotime($date));
 258 }
 259
 260 /**
 261  * Nombre de jour entre 0000-00-00 et $d
 262  * http://doc.spip.org/@_sqlite_func_to_days
 263  * cf http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_to-days
 264  * @param string $d
 265  * @return int
 266  */
 267 function _sqlite_func_to_days ($d) {
 268         static $offset = 719528; // nb de jour entre 0000-00-00 et timestamp 0=1970-01-01
 269         $result = $offset+(int)ceil(_sqlite_func_unix_timestamp($d)/(24*3600));
 270         #spip_log("Passage avec TO_DAYS : $d, $result",'sqlite.'._LOG_DEBUG);
 271         return $result;
 272 }
 273
 274 function _sqlite_func_substring($string,$start,$len=null){
 275         // SQL compte a partir de 1, php a partir de 0
 276         $start = ($start>0)?$start-1:$start;
 277         if (is_null($len))
 278                 return substr($string,$start);
 279         else
 280                 return substr($string,$start,$len);
 281 }
 282
 283 /**
 284  * Calcul de la difference entre 2 timestamp, exprimes dans l'unite fournie en premier argument
 285  * emule https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampdiff
 286  * @param string $unit
 287  * @param string $date1
 288  * @param string $date2
 289  * @return int
 290  */
 291 function _sqlite_timestampdiff($unit,$date1,$date2){
 292         // PHP >= 5.3
 293         if (function_exists("date_diff")){
 294                 $d1 = date_create($date1);
 295                 $d2 = date_create($date2);
 296                 $diff = date_diff($d1,$d2);
 297                 $inv = $diff->invert?-1:1;
 298                 switch($unit){
 299                         case "YEAR":
 300                                 return $inv*$diff->y;
 301                         case "QUARTER":
 302                                 return $inv*(4*$diff->y+intval(floor($diff->m/3)));
 303                         case "MONTH":
 304                                 return $inv*(12*$diff->y+$diff->m);
 305                         case "WEEK":
 306                                 return $inv*intval(floor($diff->days/7));
 307                         case "DAY":
 308                                 #var_dump($inv*$diff->days);
 309                                 return $inv*$diff->days;
 310                         case "HOUR":
 311                                 return $inv*(24*$diff->days+$diff->h);
 312                         case "MINUTE":
 313                                 return $inv*((24*$diff->days+$diff->h)*60+$diff->i);
 314                         case "SECOND":
 315                                 return $inv*(((24*$diff->days+$diff->h)*60+$diff->i)*60+$diff->s);
 316                         case "MICROSECOND":
 317                                 return $inv*(((24*$diff->days+$diff->h)*60+$diff->i)*60+$diff->s)*1000000;
 318                 }
 319                 return 0;
 320         }
 321         // PHP < 5.3
 322         else {
 323                 $d1 = strtotime($date1);
 324                 $d2 = strtotime($date2);
 325                 $diff = $d2 - $d1;
 326                 $sign = ($diff<0?-1:1);
 327                 $diff = $sign * $diff;
 328                 switch($unit){
 329                         case "YEAR":
 330                                 $diff = $d2-$d1;
 331                                 return $sign*(date('Y',abs($diff))-date('Y',0));
 332                         case "QUARTER":
 333                                 return $sign*(4*(date('Y',abs($diff))-date('Y',0))+intval(floor((date('m',$diff)-1)/3)));
 334                         case "MONTH":
 335                                 return $sign*((date('Y',$diff)-date('Y',0))*12+date('m',$diff)-1);
 336                         case "WEEK":
 337                                 return intval(floor(($d2-$d1)/3600/7));
 338                         case "DAY":
 339                                 return intval(floor(($d2-$d1)/3600/24));
 340                         case "HOUR":
 341                                 return intval(floor(($d2-$d1)/3600));
 342                         case "MINUTE":
 343                                 return intval(floor(($d2-$d1)/60));
 344                         case "SECOND":
 345                                 return $d2-$d1;
 346                         case "MICROSECOND":
 347                                 return $d2-$d1*1000000;
 348                 }
 349         }
 350 }
 351
 352 // http://doc.spip.org/@_sqlite_func_unix_timestamp
 353 function _sqlite_func_unix_timestamp($d) {
 354         static $mem = array();
 355         static $n = 0;
 356         if (isset($mem[$d])) return $mem[$d];
 357         if ($n++>100) {$mem = array();$n=0;}
 358
 359         //2005-12-02 20:53:53
 360         #spip_log("Passage avec UNIX_TIMESTAMP : $d",'sqlite.'._LOG_DEBUG);
 361         if (!$d) return $mem[$d] = mktime();
 362         // une pile plus grosse n'accelere pas le calcul
 363         return $mem[$d] = strtotime($d);
 364 }
 365
 366
 367 // http://doc.spip.org/@_sqlite_func_year
 368 function _sqlite_func_year ($d) {
 369         return _sqlite_func_date("Y",$d);
 370 }
 371
 372 /**
 373  * version optimisee et memoizee de date() utilisee par
 374  * _sqlite_func_year, _sqlite_func_month, _sqlite_func_dayofmonth
 375  * @param string $quoi
 376  *   format : Y, m, ou d
 377  * @param int $d
 378  *   timestamp
 379  * @return int
 380  */
 381 function _sqlite_func_date($quoi,$d) {
 382         static $mem = array();
 383         static $n = 0;
 384         if (isset($mem[$d])) return $mem[$d][$quoi];
 385         if ($n++>100) {$mem = array();$n=0;}
 386
 387         $dec = date("Y-m-d",_sqlite_func_unix_timestamp($d));
 388         $mem[$d] = array("Y"=>substr($dec,0,4),"m"=>substr($dec,5,2),"d"=>substr($dec,8,2));
 389         return $mem[$d][$quoi];
 390 }
 391
 392 // http://doc.spip.org/@_sqlite_func_vide
 393 function _sqlite_func_vide(){
 394         return;
 395 }
 396
 397
 398
 399 ?>