1
|
|
|
<?php |
2
|
|
|
/** |
3
|
|
|
* KumbiaPHP web & app Framework. |
4
|
|
|
* |
5
|
|
|
* LICENSE |
6
|
|
|
* |
7
|
|
|
* This source file is subject to the new BSD license that is bundled |
8
|
|
|
* with this package in the file LICENSE.txt. |
9
|
|
|
* It is also available through the world-wide-web at this URL: |
10
|
|
|
* http://wiki.kumbiaphp.com/Licencia |
11
|
|
|
* If you did not receive a copy of the license and are unable to |
12
|
|
|
* obtain it through the world-wide-web, please send an email |
13
|
|
|
* to [email protected] so we can send you a copy immediately. |
14
|
|
|
* |
15
|
|
|
* @category Kumbia |
16
|
|
|
* |
17
|
|
|
* @copyright 2005 - 2020 Kumbia Team (http://www.kumbiaphp.com) |
18
|
|
|
* @license http://wiki.kumbiaphp.com/Licencia New BSD License |
19
|
|
|
*/ |
20
|
|
|
namespace Kumbia\ActiveRecord; |
21
|
|
|
|
22
|
|
|
/** |
23
|
|
|
* Generador de codigo SQL. |
24
|
|
|
*/ |
25
|
|
|
class QueryGenerator |
26
|
|
|
{ |
27
|
|
|
/** |
28
|
|
|
* Construye una consulta select desde una lista de parametros. |
29
|
|
|
* |
30
|
|
|
* where: condiciones where |
31
|
|
|
* order: criterio de ordenamiento |
32
|
|
|
* fields: lista de campos |
33
|
|
|
* join: joins de tablas |
34
|
|
|
* group: agrupar campos |
35
|
|
|
* having: condiciones de grupo |
36
|
|
|
* limit: valor limit |
37
|
|
|
* offset: valor offset |
38
|
|
|
* @param array $params parametros de consulta select |
39
|
|
|
* @param string $source |
40
|
|
|
* @param string $type |
41
|
|
|
* @return string |
42
|
|
|
*/ |
43
|
|
|
public static function select($source, $type, array $params) |
44
|
|
|
{ |
45
|
|
|
$params = array_merge([ |
46
|
|
|
'fields' => '*', |
47
|
|
|
'join' => '', |
48
|
|
|
'limit' => \null, |
49
|
|
|
'offset' => \null, |
50
|
|
|
'where' => \null, |
51
|
|
|
'group' => \null, |
52
|
|
|
'having' => \null, |
53
|
|
|
'order' => \null |
54
|
|
|
], $params); |
55
|
|
|
|
56
|
|
|
list($where, $group, $having, $order) = static::prepareParam($params); |
57
|
|
|
$sql = "SELECT {$params['fields']} FROM $source {$params['join']} $where $group $having $order"; |
58
|
|
|
|
59
|
|
|
if ( ! \is_null($params['limit']) || ! \is_null($params['offset'])) { |
60
|
|
|
$sql = self::query($type, 'limit', $sql, $params['limit'], $params['offset']); |
61
|
|
|
} |
62
|
|
|
|
63
|
|
|
return $sql; |
64
|
|
|
} |
65
|
|
|
|
66
|
|
|
/** |
67
|
|
|
* Permite construir el WHERE, GROUP BY, HAVING y ORDER BY de una consulta SQL |
68
|
|
|
* en base a los parámetros $params. |
69
|
|
|
* |
70
|
|
|
* @param array $params |
71
|
|
|
* @return string[] |
72
|
|
|
*/ |
73
|
|
|
protected static function prepareParam(array $params) |
74
|
|
|
{ |
75
|
|
|
return [ |
76
|
|
|
static::where($params['where']), |
77
|
|
|
static::group($params['group']), |
78
|
|
|
static::having($params['having']), |
79
|
|
|
static::order($params['order']) |
80
|
|
|
]; |
81
|
|
|
} |
82
|
|
|
|
83
|
|
|
/** |
84
|
|
|
* Genera una sentencia where. |
85
|
|
|
* |
86
|
|
|
* @param string $where |
87
|
|
|
* @return string |
88
|
|
|
*/ |
89
|
|
|
protected static function where($where) |
90
|
|
|
{ |
91
|
|
|
return empty($where) ? '' : "WHERE $where"; |
92
|
|
|
} |
93
|
|
|
|
94
|
|
|
/** |
95
|
|
|
* Genera una sentencia GROUP. |
96
|
|
|
* |
97
|
|
|
* @param string $group |
98
|
|
|
* @return string |
99
|
|
|
*/ |
100
|
|
|
protected static function group($group) |
101
|
|
|
{ |
102
|
|
|
return empty($group) ? '' : "GROUP BY $group"; |
103
|
|
|
} |
104
|
|
|
|
105
|
|
|
/** |
106
|
|
|
* Genera una sentencia HAVING. |
107
|
|
|
* |
108
|
|
|
* @param string $having |
109
|
|
|
* @return string |
110
|
|
|
*/ |
111
|
|
|
protected static function having($having) |
112
|
|
|
{ |
113
|
|
|
return empty($having) ? '' : "HAVING $having"; |
114
|
|
|
} |
115
|
|
|
|
116
|
|
|
/** |
117
|
|
|
* Genera una sentencia ORDER BY. |
118
|
|
|
* |
119
|
|
|
* @param string $order |
120
|
|
|
* @return string |
121
|
|
|
*/ |
122
|
|
|
protected static function order($order) |
123
|
|
|
{ |
124
|
|
|
return empty($order) ? '' : "ORDER BY $order"; |
125
|
|
|
} |
126
|
|
|
|
127
|
|
|
/** |
128
|
|
|
* Construye una consulta INSERT. |
129
|
|
|
* |
130
|
|
|
* @param LiteRecord $model Modelo a actualizar |
131
|
|
|
* @param array $data Datos pasados a la consulta preparada |
132
|
|
|
* @return string |
133
|
|
|
*/ |
134
|
|
|
public static function insert(LiteRecord $model, array &$data) |
135
|
|
|
{ |
136
|
|
|
$meta = $model::metadata(); |
137
|
|
|
$data = []; |
138
|
|
|
$columns = []; |
139
|
|
|
$values = []; |
140
|
|
|
|
141
|
|
|
// Preparar consulta |
142
|
|
|
foreach ($meta->getFieldsList() as $field) { |
143
|
|
|
$columns[] = $field; |
144
|
|
|
static::insertField($field, $model, $data, $values); |
145
|
|
|
} |
146
|
|
|
$columns = \implode(',', $columns); |
147
|
|
|
$values = \implode(',', $values); |
148
|
|
|
$source = $model::getSource(); |
149
|
|
|
|
150
|
|
|
return "INSERT INTO $source ($columns) VALUES ($values)"; |
151
|
|
|
} |
152
|
|
|
|
153
|
|
|
/** |
154
|
|
|
* Agrega un campo a para generar una consulta preparada para un INSERT. |
155
|
|
|
* |
156
|
|
|
* @param string $field Nombre del campo |
157
|
|
|
* @param LiteRecord $model valor del campo |
158
|
|
|
* @param array $data array de datos |
159
|
|
|
* @param array $values array de valores |
160
|
|
|
* @return void |
161
|
|
|
*/ |
162
|
|
|
protected static function insertField($field, LiteRecord $model, array &$data, array &$values) |
163
|
|
|
{ |
164
|
|
|
//$meta = $model::metadata(); |
165
|
|
|
if (self::haveValue($model, $field)) { |
166
|
|
|
$data[":$field"] = $model->$field; |
167
|
|
|
$values[] = ":$field"; |
168
|
|
|
} else { |
169
|
|
|
//if (!\in_array($field, $meta->getWithDefault()) && !\in_array($field, $meta->getAutoFields())) { |
170
|
|
|
$values[] = 'NULL'; |
171
|
|
|
} |
172
|
|
|
} |
173
|
|
|
|
174
|
|
|
/** |
175
|
|
|
* Permite conocer si la columna debe definirse como nula. |
176
|
|
|
* |
177
|
|
|
* @param LiteRecord $model |
178
|
|
|
* @param string $field |
179
|
|
|
* @return bool |
180
|
|
|
*/ |
181
|
|
|
protected static function haveValue(LiteRecord $model, $field) |
182
|
|
|
{ |
183
|
|
|
return isset($model->$field) && $model->$field !== ''; |
184
|
|
|
} |
185
|
|
|
|
186
|
|
|
/** |
187
|
|
|
* Construye una consulta UPDATE. |
188
|
|
|
* |
189
|
|
|
* @param LiteRecord $model Modelo a actualizar |
190
|
|
|
* @param array $data Datos pasados a la consulta preparada |
191
|
|
|
* @return string |
192
|
|
|
*/ |
193
|
|
|
public static function update(LiteRecord $model, array &$data) |
194
|
|
|
{ |
195
|
|
|
$set = []; |
196
|
|
|
$pk = $model::getPK(); |
197
|
|
|
/*elimina la clave primaria*/ |
198
|
|
|
$list = \array_diff($model::metadata()->getFieldsList(), [$pk]); |
199
|
|
|
foreach ($list as $field) { |
200
|
|
|
$value = isset($model->$field) ? $model->$field : \null; |
201
|
|
|
static::updateField($field, $value, $data, $set); |
202
|
|
|
} |
203
|
|
|
$set = \implode(', ', $set); |
204
|
|
|
$source = $model::getSource(); |
205
|
|
|
$data[":$pk"] = $model->$pk; |
206
|
|
|
|
207
|
|
|
return "UPDATE $source SET $set WHERE $pk = :$pk"; |
208
|
|
|
} |
209
|
|
|
|
210
|
|
|
/** |
211
|
|
|
* Generate SQL for DELETE sentence. |
212
|
|
|
* |
213
|
|
|
* @param string $source source |
214
|
|
|
* @param string $where condition |
215
|
|
|
* @return string SQL |
216
|
|
|
*/ |
217
|
|
|
public static function deleteAll($source, $where) |
218
|
|
|
{ |
219
|
|
|
return "DELETE FROM $source ".static::where($where); |
220
|
|
|
} |
221
|
|
|
|
222
|
|
|
/** |
223
|
|
|
* Generate SQL for COUNT sentence. |
224
|
|
|
* |
225
|
|
|
* @param string $source source |
226
|
|
|
* @param string $where condition |
227
|
|
|
* @return string SQL |
228
|
|
|
*/ |
229
|
|
|
public static function count($source, $where) |
230
|
|
|
{ |
231
|
|
|
return "SELECT COUNT(*) AS count FROM $source ".static::where($where); |
232
|
|
|
} |
233
|
|
|
|
234
|
|
|
/** |
235
|
|
|
* Agrega un campo a para generar una consulta preparada para un UPDATE. |
236
|
|
|
* |
237
|
|
|
* @param string $field Nombre del campo |
238
|
|
|
* @param mixed $value valor |
239
|
|
|
* @param array $data array de datos |
240
|
|
|
* @param array $set array de valores |
241
|
|
|
* @return void |
242
|
|
|
*/ |
243
|
|
|
protected static function updateField($field, $value, array &$data, array &$set) |
244
|
|
|
{ |
245
|
|
|
if ( ! empty($value)) { |
246
|
|
|
$data[":$field"] = $value; |
247
|
|
|
$set[] = "$field = :$field"; |
248
|
|
|
} else { |
249
|
|
|
$set[] = "$field = NULL"; |
250
|
|
|
} |
251
|
|
|
} |
252
|
|
|
|
253
|
|
|
/** |
254
|
|
|
* Construye una consulta UPDATE. |
255
|
|
|
* |
256
|
|
|
* @todo ¿Hay que escapar los nombres de los campos? |
257
|
|
|
* @param string $model nombre del modelo a actualizar |
258
|
|
|
* @param array $fields campos a actualizar |
259
|
|
|
* @param array $data Datos pasados a la consulta preparada |
260
|
|
|
* @param string|null $where |
261
|
|
|
* @return string |
262
|
|
|
*/ |
263
|
|
|
public static function updateAll($model, array $fields, array &$data, $where) |
264
|
|
|
{ |
265
|
|
|
$set = []; |
266
|
|
|
//$pk = $model::getPK(); |
267
|
|
|
/*elimina la clave primaria*/ |
268
|
|
|
foreach ($fields as $field => $value) { |
269
|
|
|
static::updateField($field, $value, $data, $set); |
270
|
|
|
} |
271
|
|
|
$set = \implode(', ', $set); |
272
|
|
|
$source = $model::getSource(); |
273
|
|
|
$where = static::where($where); |
274
|
|
|
|
275
|
|
|
return "UPDATE $source SET $set $where"; |
276
|
|
|
} |
277
|
|
|
|
278
|
|
|
/** |
279
|
|
|
* Ejecuta una consulta. |
280
|
|
|
* |
281
|
|
|
* @thow KumbiaException |
282
|
|
|
* @param string $type tipo de driver |
283
|
|
|
* @param string $query_function nombre de funcion |
284
|
|
|
* @return mixed |
285
|
|
|
*/ |
286
|
|
|
public static function query($type, $query_function) |
287
|
|
|
{ |
288
|
|
|
$query_function = "{$type}_{$query_function}"; |
289
|
|
|
|
290
|
|
|
require_once __DIR__."/Query/{$query_function}.php"; |
291
|
|
|
|
292
|
|
|
$args = \array_slice(\func_get_args(), 2); |
293
|
|
|
|
294
|
|
|
return \call_user_func_array(__NAMESPACE__."\\Query\\$query_function", $args); |
295
|
|
|
} |
296
|
|
|
} |
297
|
|
|
|