1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
|
<?php namespace Illuminate\Database\Query\Grammars;
use Illuminate\Database\Query\Builder;
class SQLiteGrammar extends Grammar {
/**
* All of the available clause operators.
*
* @var array
*/
protected $operators = array(
'=', '<', '>', '<=', '>=', '<>', '!=',
'like', 'not like', 'between', 'ilike',
'&', '|', '<<', '>>',
);
/**
* Compile an insert statement into SQL.
*
* @param \Illuminate\Database\Query\Builder $query
* @param array $values
* @return string
*/
public function compileInsert(Builder $query, array $values)
{
// Essentially we will force every insert to be treated as a batch insert which
// simply makes creating the SQL easier for us since we can utilize the same
// basic routine regardless of an amount of records given to us to insert.
$table = $this->wrapTable($query->from);
if ( ! is_array(reset($values)))
{
$values = array($values);
}
// If there is only one record being inserted, we will just use the usual query
// grammar insert builder because no special syntax is needed for the single
// row inserts in SQLite. However, if there are multiples, we'll continue.
if (count($values) == 1)
{
return parent::compileInsert($query, reset($values));
}
$names = $this->columnize(array_keys(reset($values)));
$columns = array();
// SQLite requires us to build the multi-row insert as a listing of select with
// unions joining them together. So we'll build out this list of columns and
// then join them all together with select unions to complete the queries.
foreach (array_keys(reset($values)) as $column)
{
$columns[] = '? as '.$this->wrap($column);
}
$columns = array_fill(0, count($values), implode(', ', $columns));
return "insert into $table ($names) select ".implode(' union select ', $columns);
}
/**
* Compile a truncate table statement into SQL.
*
* @param \Illuminate\Database\Query\Builder $query
* @return array
*/
public function compileTruncate(Builder $query)
{
$sql = array('delete from sqlite_sequence where name = ?' => array($query->from));
$sql['delete from '.$this->wrapTable($query->from)] = array();
return $sql;
}
/**
* Compile a "where day" clause.
*
* @param \Illuminate\Database\Query\Builder $query
* @param array $where
* @return string
*/
protected function whereDay(Builder $query, $where)
{
return $this->dateBasedWhere('%d', $query, $where);
}
/**
* Compile a "where month" clause.
*
* @param \Illuminate\Database\Query\Builder $query
* @param array $where
* @return string
*/
protected function whereMonth(Builder $query, $where)
{
return $this->dateBasedWhere('%m', $query, $where);
}
/**
* Compile a "where year" clause.
*
* @param \Illuminate\Database\Query\Builder $query
* @param array $where
* @return string
*/
protected function whereYear(Builder $query, $where)
{
return $this->dateBasedWhere('%Y', $query, $where);
}
/**
* Compile a date based where clause.
*
* @param string $type
* @param \Illuminate\Database\Query\Builder $query
* @param array $where
* @return string
*/
protected function dateBasedWhere($type, Builder $query, $where)
{
$value = str_pad($where['value'], 2, '0', STR_PAD_LEFT);
$value = $this->parameter($value);
return 'strftime(\''.$type.'\', '.$this->wrap($where['column']).') '.$where['operator'].' '.$value;
}
}
|