PageRenderTime 5ms CodeModel.GetById 3ms app.highlight 7ms RepoModel.GetById 1ms app.codeStats 0ms

/core/src/main/php/rdbms/sqlite/SQLiteDialect.class.php

http://github.com/xp-framework/xp-framework
PHP | 243 lines | 160 code | 12 blank | 71 comment | 13 complexity | 7bcab7cb9273d3848446d3d57f687972 MD5 | raw file
  1<?php
  2/* This class is part of the XP framework
  3 *
  4 * $Id$ 
  5 */
  6  uses('rdbms.SQLDialect');
  7
  8  /**
  9   * helps to build functions for different SQL servers
 10   *
 11   */
 12  class SQLiteDialect extends SQLDialect {
 13    public
 14      $escape       = "'",
 15      $escapeRules  = array("'"  => "''"),
 16      $escapeT      = "'",
 17      $escapeRulesT = array("'"  => "''"),
 18      $dateFormat   = 'Y-m-d H:i:s';
 19        
 20    private static
 21      $dateparts= array(
 22        'day'         => '"d"',
 23        'dayofyear'   => '"z"',
 24        'hour'        => '"H"',
 25        'microsecond' => FALSE,
 26        'millisecond' => FALSE,
 27        'minute'      => '"i"',
 28        'month'       => '"m"',
 29        'quarter'     => FALSE,
 30        'second'      => 's',
 31        'week'        => FALSE,
 32        'weekday'     => FALSE,
 33        'year'        => 'Y',
 34      ),
 35      // http://www.sqlite.org/lang_expr.html
 36      $implementations= array(
 37        'abs_1'        => 'php("abs", %d)',
 38        'acos_1'       => 'php("acos", %d)',
 39        'ascii_1'      => 'php("ord", %s)',
 40        'asin_1'       => 'php("asin", %d)',
 41        'atan_1'       => 'php("atan", %d)',
 42        'atan_2'       => 'php("atan2", %d, %d)',
 43        'bit_length_1' => 'bit_length_not_implemented',
 44        'cast_2'       => 'cast(%s as %e)',
 45        'ceil_1'       => 'php("ceil", %d)',
 46        'char_1'       => 'php("chr", %d)',
 47        'cos_1'        => 'php("cos", %d)',
 48        'cot_1'        => 'php("tan", php("pi") / 2 - %d)',
 49        'dateadd_3'    => 'dateadd(%t, %d, %s)',
 50        'datediff_3'   => 'datediff_not_implemented',
 51        'datename_2'   => 'php("strval", php("idate", %t, php("strtotime", %s)))',
 52        'datepart_2'   => 'php("idate", %t, php("strtotime", %s))',
 53        'day_1'        => 'php("idate", "d", php("strtotime", %s))',
 54        'degrees_1'    => 'php("rad2deg", %d)',
 55        'exp_1'        => 'php("exp", %d)',
 56        'floor_1'      => 'php("floor", %d)',
 57        'getdate_0'    => 'php("date", "Y-m-d H:i:s", php("time"))',
 58        'hour_1'       => 'php("idate", "H", php("strtotime", %s))',
 59        'len_1'        => 'php("strlen", %s)',
 60        'length_1'     => 'php("strlen", %s)',
 61        'locate_2'     => 'locate(%s, %s, 0)',
 62        'locate_3'     => 'locate(%s, %s, %d)',
 63        'log10_1'      => 'php("log10", %d)',
 64        'log_1'        => 'php("log", %d)',
 65        'lower_1'      => 'php("strtolower, "%s)',
 66        'ltrim_1'      => 'php("ltrim", %s)',
 67        'ltrim_2'      => 'php("ltrim", %s, %s)',
 68        'minute_1'     => 'php("idate", "i", php("strtotime", %s))',
 69        'month_1'      => 'php("idate", "m", php("strtotime", %s))',
 70        'nullif_2'     => 'nullif(%s, %s)',
 71        'pi_0'         => 'php("pi")',
 72        'power_2'      => 'php("pow", %d, %d)',
 73        'radians_1'    => 'php("deg2rad", %d)',
 74        'rand_0'       => 'php("rand")',
 75        'reverse_1'    => 'php("strrev", %s)',
 76        'round_2'      => 'php("round", %d, %d)',
 77        'rtrim_1'      => 'php("rtrim", %s)',
 78        'rtrim_2'      => 'php("rtrim", %s, %s)',
 79        'second_1'     => 'php("idate", "s", php("strtotime", %s))',
 80        'sign_1'       => 'sign(%d)',
 81        'sin_1'        => 'php("sin", %d)',
 82        'soundex_1'    => 'php("soundex", %s)',
 83        'space_1'      => 'php("str_repeat", " ", %d)',
 84        'sqrt_1'       => 'php("sqrt", %d)',
 85        'str_1'        => 'php("strval", %s)',
 86        'substring_3'  => 'php("substr", %s, %d, %d)',
 87        'substring_2'  => 'php("substr", %s, %d)',
 88        'tan_1'        => 'php("tan", %d)',
 89        'trim_2'       => 'php("trim", %s)',
 90        'trim_3'       => 'php("trim", %s, %s)',
 91        'upper_1'      => 'php("strtoupper", %s)',
 92        'year_1'       => 'php("idate", "Y", php("strtotime", %s))',
 93      );
 94
 95    /**
 96     * register sql standard functions for a connection
 97     *
 98     * @param   db handel conn
 99     */
100    public function registerCallbackFunctions($conn) {
101      sqlite_create_function($conn, 'cast', array($this, '_cast'), 2);
102      sqlite_create_function($conn, 'sign', array($this, '_sign'), 1);
103      sqlite_create_function($conn, 'dateadd', array($this, '_dateadd'), 3);
104      sqlite_create_function($conn, 'locate',  array($this, '_locate'), 3);
105      sqlite_create_function($conn, 'nullif',  array($this, '_nullif'), 2);
106    }
107
108    /**
109     * Callback function to cast data
110     *
111     * @param   var s
112     * @param   var type
113     * @return  var
114     */
115    public function _cast($s, $type) {
116      static $identifiers= array(
117        'bigint'     => "\3",
118        'date'       => "\2",
119        'datetime'   => "\2",
120        'decimal'    => "\4",
121        'double'     => "\4",
122        'float'      => "\4",
123        'int'        => "\3",
124        'integer'    => "\3",
125        'smallint'   => "\3",
126      );
127      return is_null($s) ? NULL : $identifiers[strtolower($type)].$s;
128    }
129
130    /**
131     * Callback function to compare to statements
132     *
133     * @param   string arg1
134     * @param   string arg2
135     * @return  int
136     */
137    public function _nullif($arg1, $arg2) {
138      if ($arg1 == $arg2) return NULL;
139      return $arg1;
140    }
141
142    /**
143     * Callback function to find a string in a string
144     *
145     * @param   string haystack
146     * @param   string needle
147     * @param   int start
148     * @return  int
149     */
150    public function _locate($h, $n, $s) {
151      if (is_null($h) or is_null($n))  return NULL;
152      return intval(strpos($h, $n, $s));
153    }
154
155    /**
156     * Callback function to find the signature of a float
157     *
158     * @param   float dig
159     * @return  int
160     */
161    public function _sign($dig) {
162      $dig= floatval($dig);
163      if ($dig > 0) return 1;
164      if ($dig < 0) return -1;
165      return 0;
166    }
167
168    /**
169     * Callback function add a datepart to a date
170     *
171     * @param   sring datepart
172     * @param   int amount to add
173     * @param   string datestr
174     * @return  string
175     */
176    public function _dateadd($part, $amount, $datestr) {
177      $part= current(array_keys(self::$dateparts, '"'.$part.'"'));
178      $date= new DateTime($datestr);
179      $date->modify($amount.' '.$part);
180      return $date->format($this->dateFormat);
181    }
182
183    /**
184     * get a function format string
185     *
186     * @param   SQLFunction func
187     * @return  string
188     * @throws  lang.IllegalArgumentException
189     */
190    public function formatFunction(SQLFunction $func) {
191      $func_i= $func->func.'_'.sizeof($func->args);
192      switch ($func->func) {
193        case 'concat':
194        return implode(' || ', array_fill(0, sizeof($func->args), '%s'));
195
196        default:
197        if (isset(self::$implementations[$func_i])) return self::$implementations[$func_i];
198        return parent::formatFunction($func);
199      }
200    }
201  
202    /**
203     * get a dialect specific datepart
204     *
205     * @param   string datepart
206     * @return  string
207     * @throws  lang.IllegalArgumentException
208     */
209    public function datepart($datepart) {
210      $datepart= strToLower($datepart);
211      if (!array_key_exists($datepart, self::$dateparts)) return parent::datepart($datepart);
212      if (FALSE === self::$dateparts[$datepart]) throw new IllegalArgumentException('PostgreSQL does not support datepart '.$datepart);
213      return self::$dateparts[$datepart];
214    }
215
216    /**
217     * build join related part of an SQL query
218     *
219     * @param   rdbms.join.JoinRelation[] conditions
220     * @return  string
221     * @throws  lang.IllegalArgumentException
222     */
223    public function makeJoinBy(Array $conditions) {
224      if (0 == sizeof($conditions)) throw new IllegalArgumentException('conditions can not be empty');
225      $querypart= '';
226      $first= TRUE;
227      foreach ($conditions as $link) {
228        if ($first) {
229          $first= FALSE;
230          $querypart.= sprintf(
231            '%s LEFT OUTER JOIN %s on (%s) ',
232            $link->getSource()->toSqlString(),
233            $link->getTarget()->toSqlString(),
234            implode(' and ', $link->getConditions())
235          );
236        } else {
237          $querypart.= sprintf('LEFT JOIN %s on (%s) ', $link->getTarget()->toSqlString(), implode(' and ', $link->getConditions()));
238        }
239      }
240      return $querypart.'where ';
241    }
242  }
243?>