[Summary view] [Print] [Text view]

   1  <?php
   2  /**
   3   * Part of the Joomla Framework Database Package
   4   *
   5   * @copyright  Copyright (C) 2005 - 2021 Open Source Matters, Inc. All rights reserved.
   6   * @license    GNU General Public License version 2 or later; see LICENSE
   7   */
   8  
   9  namespace Joomla\Database\Sqlite;
  10  
  11  use Joomla\Database\DatabaseQuery;
  12  use Joomla\Database\Pdo\PdoQuery;
  13  use Joomla\Database\Query\QueryElement;
  14  
  15  /**
  16   * SQLite Query Building Class.
  17   *
  18   * @since  1.0
  19   */
  20  class SqliteQuery extends PdoQuery
  21  {
  22      /**
  23       * Magic function to convert the query to a string.
  24       *
  25       * @return  string  The completed query.
  26       *
  27       * @since   2.0.0
  28       */
  29  	public function __toString()
  30      {
  31          switch ($this->type)
  32          {
  33              case 'select':
  34                  if ($this->selectRowNumber)
  35                  {
  36                      $orderBy          = $this->selectRowNumber['orderBy'];
  37                      $orderColumnAlias = $this->selectRowNumber['orderColumnAlias'];
  38  
  39                      $column = "ROW_NUMBER() AS $orderColumnAlias";
  40  
  41                      if ($this->select === null)
  42                      {
  43                          $query = PHP_EOL . 'SELECT 1'
  44                              . (string) $this->from
  45                              . (string) $this->where;
  46                      }
  47                      else
  48                      {
  49                          $tmpOffset    = $this->offset;
  50                          $tmpLimit     = $this->limit;
  51                          $this->offset = 0;
  52                          $this->limit  = 0;
  53                          $tmpOrder     = $this->order;
  54                          $this->order  = null;
  55                          $query        = parent::__toString();
  56                          $column       = "w.*, $column";
  57                          $this->order  = $tmpOrder;
  58                          $this->offset = $tmpOffset;
  59                          $this->limit  = $tmpLimit;
  60                      }
  61  
  62                      // Special sqlite query to count ROW_NUMBER
  63                      $query = PHP_EOL . "SELECT $column"
  64                          . PHP_EOL . "FROM ($query" . PHP_EOL . "ORDER BY $orderBy"
  65                          . PHP_EOL . ') AS w,(SELECT ROW_NUMBER(0)) AS r'
  66                          // Forbid to flatten subqueries.
  67                          . ((string) $this->order ?: PHP_EOL . 'ORDER BY NULL');
  68  
  69                      return $this->processLimit($query, $this->limit, $this->offset);
  70                  }
  71  
  72                  break;
  73  
  74              case 'querySet':
  75                  $query = $this->querySet;
  76  
  77                  if ($query->order || $query->limit || $query->offset)
  78                  {
  79                      // If ORDER BY or LIMIT statement exist then parentheses is required for the first query
  80                      $query = PHP_EOL . "SELECT * FROM ($query)";
  81                  }
  82  
  83                  if ($this->merge)
  84                  {
  85                      // Special case for merge
  86                      foreach ($this->merge as $element)
  87                      {
  88                          $query .= (string) $element;
  89                      }
  90                  }
  91  
  92                  if ($this->order)
  93                  {
  94                      $query .= (string) $this->order;
  95                  }
  96  
  97                  return $query;
  98  
  99              case 'update':
 100                  if ($this->join)
 101                  {
 102                      $table = $this->update->getElements();
 103                      $table = $table[0];
 104  
 105                      $tableName = explode(' ', $table);
 106                      $tableName = $tableName[0];
 107  
 108                      if ($this->columns === null)
 109                      {
 110                          $fields = $this->db->getTableColumns($tableName);
 111  
 112                          foreach ($fields as $key => $value)
 113                          {
 114                              $fields[$key] = $key;
 115                          }
 116  
 117                          $this->columns = new QueryElement('()', $fields);
 118                      }
 119  
 120                      $fields   = $this->columns->getElements();
 121                      $elements = $this->set->getElements();
 122  
 123                      foreach ($elements as $nameValue)
 124                      {
 125                          $setArray = explode(' = ', $nameValue, 2);
 126  
 127                          if ($setArray[0][0] === '`')
 128                          {
 129                              // Unquote column name
 130                              $setArray[0] = substr($setArray[0], 1, -1);
 131                          }
 132  
 133                          $fields[$setArray[0]] = $setArray[1];
 134                      }
 135  
 136                      $select = new static($this->db);
 137                      $select->select(array_values($fields))
 138                          ->from($table);
 139  
 140                      $select->join  = $this->join;
 141                      $select->where = $this->where;
 142  
 143                      return 'INSERT OR REPLACE INTO ' . $tableName
 144                          . ' (' . implode(',', array_keys($fields)) . ')'
 145                          . (string) $select;
 146                  }
 147          }
 148  
 149          return parent::__toString();
 150      }
 151  
 152      /**
 153       * Gets the number of characters in a string.
 154       *
 155       * Note, use 'length' to find the number of bytes in a string.
 156       *
 157       * Usage:
 158       * $query->select($query->charLength('a'));
 159       *
 160       * @param   string       $field      A value.
 161       * @param   string|null  $operator   Comparison operator between charLength integer value and $condition
 162       * @param   string|null  $condition  Integer value to compare charLength with.
 163       *
 164       * @return  string  The required char length call.
 165       *
 166       * @since   1.1.0
 167       */
 168  	public function charLength($field, $operator = null, $condition = null)
 169      {
 170          $statement = 'length(' . $field . ')';
 171  
 172          if ($operator !== null && $condition !== null)
 173          {
 174              $statement .= ' ' . $operator . ' ' . $condition;
 175          }
 176  
 177          return $statement;
 178      }
 179  
 180      /**
 181       * Concatenates an array of column names or values.
 182       *
 183       * Usage:
 184       * $query->select($query->concatenate(array('a', 'b')));
 185       *
 186       * @param   string[]     $values     An array of values to concatenate.
 187       * @param   string|null  $separator  As separator to place between each value.
 188       *
 189       * @return  string  The concatenated values.
 190       *
 191       * @since   1.1.0
 192       */
 193  	public function concatenate($values, $separator = null)
 194      {
 195          if ($separator !== null)
 196          {
 197              return implode(' || ' . $this->quote($separator) . ' || ', $values);
 198          }
 199  
 200          return implode(' || ', $values);
 201      }
 202  
 203      /**
 204       * Method to modify a query already in string format with the needed additions to make the query limited to a particular number of
 205       * results, or start at a particular offset.
 206       *
 207       * @param   string   $query   The query in string format
 208       * @param   integer  $limit   The limit for the result set
 209       * @param   integer  $offset  The offset for the result set
 210       *
 211       * @return  string
 212       *
 213       * @since   1.0
 214       */
 215  	public function processLimit($query, $limit, $offset = 0)
 216      {
 217          if ($limit > 0 || $offset > 0)
 218          {
 219              $query .= ' LIMIT ' . $offset . ', ' . $limit;
 220          }
 221  
 222          return $query;
 223      }
 224  
 225      /**
 226       * Return the number of the current row.
 227       *
 228       * Usage:
 229       * $query->select('id');
 230       * $query->selectRowNumber('ordering,publish_up DESC', 'new_ordering');
 231       * $query->from('#__content');
 232       *
 233       * @param   string  $orderBy           An expression of ordering for window function.
 234       * @param   string  $orderColumnAlias  An alias for new ordering column.
 235       *
 236       * @return  $this
 237       *
 238       * @since   2.0.0
 239       * @throws  \RuntimeException
 240       */
 241  	public function selectRowNumber($orderBy, $orderColumnAlias)
 242      {
 243          $this->validateRowNumber($orderBy, $orderColumnAlias);
 244  
 245          return $this;
 246      }
 247  
 248      /**
 249       * Add a query to UNION with the current query.
 250       *
 251       * Usage:
 252       * $query->union('SELECT name FROM  #__foo')
 253       * $query->union('SELECT name FROM  #__foo', true)
 254       *
 255       * @param   DatabaseQuery|string  $query     The DatabaseQuery object or string to union.
 256       * @param   boolean               $distinct  True to only return distinct rows from the union.
 257       *
 258       * @return  $this
 259       *
 260       * @since   1.0
 261       */
 262  	public function union($query, $distinct = true)
 263      {
 264          // Set up the name with parentheses, the DISTINCT flag is redundant
 265          return $this->merge($distinct ? 'UNION SELECT * FROM ()' : 'UNION ALL SELECT * FROM ()', $query);
 266      }
 267  
 268      /**
 269       * Aggregate function to get input values concatenated into a string, separated by delimiter
 270       *
 271       * Usage:
 272       * $query->groupConcat('id', ',');
 273       *
 274       * @param   string  $expression  The expression to apply concatenation to, this may be a column name or complex SQL statement.
 275       * @param   string  $separator   The delimiter of each concatenated value
 276       *
 277       * @return  string  Input values concatenated into a string, separated by delimiter
 278       *
 279       * @since   2.0.0
 280       */
 281  	public function groupConcat($expression, $separator = ',')
 282      {
 283          return 'group_concat(' . $expression . ', ' . $this->quote($separator) . ')';
 284      }
 285  }