1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace ByJG\AnyDataset\Database; |
4
|
|
|
|
5
|
|
|
use ByJG\AnyDataset\AnyDatasetContext; |
6
|
|
|
use ByJG\AnyDataset\LogHandler; |
7
|
|
|
use ByJG\AnyDataset\Repository\DBDataset; |
8
|
|
|
use ByJG\AnyDataset\Repository\IteratorInterface; |
9
|
|
|
|
10
|
|
|
class BaseDBAccess |
11
|
|
|
{ |
12
|
|
|
|
13
|
|
|
/** |
14
|
|
|
* @var DBDataset |
15
|
|
|
*/ |
16
|
|
|
private $_db = null; |
17
|
|
|
|
18
|
|
|
/** |
19
|
|
|
* Wrapper for SQLHelper |
20
|
|
|
* |
21
|
|
|
* @var SQLHelper |
22
|
|
|
*/ |
23
|
|
|
protected $_sqlhelper = null; |
24
|
|
|
|
25
|
|
|
/** |
26
|
|
|
* Base Class Constructor. Don't must be override. |
27
|
|
|
* @param DBDataset $db |
28
|
|
|
*/ |
29
|
|
|
public function __construct(DBDataset $db) |
30
|
|
|
{ |
31
|
|
|
$this->_db = $db; |
32
|
|
|
} |
33
|
|
|
|
34
|
|
|
/** |
35
|
|
|
* Create a instance of DBDataset to connect database |
36
|
|
|
* @return DBDataset |
37
|
|
|
*/ |
38
|
|
|
protected function getDBDataset() |
39
|
|
|
{ |
40
|
|
|
return $this->_db; |
41
|
|
|
} |
42
|
|
|
|
43
|
|
|
/** |
44
|
|
|
* Execute a SQL and dont wait for a response. |
45
|
|
|
* @param string $sql |
46
|
|
|
* @param string $param |
47
|
|
|
* @param bool $getId |
48
|
|
|
* @return int|null |
49
|
|
|
*/ |
50
|
|
|
protected function executeSQL($sql, $param = null, $getId = false) |
51
|
|
|
{ |
52
|
|
|
$dbfunction = $this->getDbFunctions(); |
53
|
|
|
|
54
|
|
|
$debug = $this->getDebug(); |
55
|
|
|
$start = 0; |
56
|
|
|
if ($debug) { |
57
|
|
|
$log = LogHandler::getInstance(); |
58
|
|
|
$log->debug("Class name: " . get_class($this)); |
59
|
|
|
$log->debug("SQL: " . $sql); |
60
|
|
|
if (!is_null($param)) { |
61
|
|
|
$s = ""; |
62
|
|
|
foreach ($param as $key => $value) { |
|
|
|
|
63
|
|
|
if ($s != "") { |
64
|
|
|
$s .= ", "; |
65
|
|
|
} |
66
|
|
|
$s .= "[$key]=$value"; |
67
|
|
|
} |
68
|
|
|
$log->debug("Params: $s"); |
69
|
|
|
} |
70
|
|
|
$start = microtime(true); |
71
|
|
|
} |
72
|
|
|
|
73
|
|
|
if ($getId) { |
74
|
|
|
$id = $dbfunction->executeAndGetInsertedId($this->getDBDataset(), $sql, $param); |
|
|
|
|
75
|
|
|
} else { |
76
|
|
|
$id = null; |
77
|
|
|
$this->getDBDataset()->execSQL($sql, $param); |
|
|
|
|
78
|
|
|
} |
79
|
|
|
|
80
|
|
|
if ($debug) { |
81
|
|
|
$end = microtime(true); |
82
|
|
|
$log->debug("Execution time: " . ($end - $start) . " seconds "); |
|
|
|
|
83
|
|
|
} |
84
|
|
|
|
85
|
|
|
return $id; |
86
|
|
|
} |
87
|
|
|
|
88
|
|
|
/** |
89
|
|
|
* Execulte SELECT SQL Query |
90
|
|
|
* |
91
|
|
|
* @param string $sql |
92
|
|
|
* @param array $param |
93
|
|
|
* @param int $ttl |
94
|
|
|
* @return IteratorInterface |
95
|
|
|
*/ |
96
|
|
View Code Duplication |
protected function getIterator($sql, $param = null, $ttl = null) |
|
|
|
|
97
|
|
|
{ |
98
|
|
|
$debug = $this->getDebug(); |
99
|
|
|
$start = 0; |
100
|
|
|
if ($debug) { |
101
|
|
|
$log = LogHandler::getInstance(); |
102
|
|
|
$log->debug("Class name: " . get_class($this)); |
103
|
|
|
$log->debug("SQL: " . $sql); |
104
|
|
|
if (!is_null($param)) { |
105
|
|
|
$s = ""; |
106
|
|
|
foreach ($param as $key => $value) { |
107
|
|
|
if ($s != "") { |
108
|
|
|
$s .= ", "; |
109
|
|
|
} |
110
|
|
|
$s .= "[$key]=$value"; |
111
|
|
|
} |
112
|
|
|
$log->debug("Params: $s"); |
113
|
|
|
} |
114
|
|
|
$start = microtime(true); |
115
|
|
|
} |
116
|
|
|
$it = $this->getDBDataset()->getIterator($sql, $param, $ttl); |
117
|
|
|
if ($debug) { |
118
|
|
|
$end = microtime(true); |
119
|
|
|
$log->debug("Execution Time: " . ($end - $start) . " segundos "); |
|
|
|
|
120
|
|
|
} |
121
|
|
|
return $it; |
122
|
|
|
} |
123
|
|
|
|
124
|
|
View Code Duplication |
protected function getScalar($sql, $param = null) |
|
|
|
|
125
|
|
|
{ |
126
|
|
|
$debug = $this->getDebug(); |
127
|
|
|
$start = 0; |
128
|
|
|
if ($debug) { |
129
|
|
|
$log = LogHandler::getInstance(); |
130
|
|
|
$log->debug("Class name: " . get_class($this)); |
131
|
|
|
$log->debug("SQL: " . $sql); |
132
|
|
|
if (!is_null($param)) { |
133
|
|
|
$s = ""; |
134
|
|
|
foreach ($param as $key => $value) { |
135
|
|
|
if ($s != "") { |
136
|
|
|
$s .= ", "; |
137
|
|
|
} |
138
|
|
|
$s .= "[$key]=$value"; |
139
|
|
|
} |
140
|
|
|
$log->debug("Params: $s"); |
141
|
|
|
} |
142
|
|
|
$start = microtime(true); |
143
|
|
|
} |
144
|
|
|
$scalar = $this->getDBDataset()->getScalar($sql, $param); |
145
|
|
|
if ($debug) { |
146
|
|
|
$end = microtime(true); |
147
|
|
|
$log->debug("Execution Time: " . ($end - $start) . " segundos "); |
|
|
|
|
148
|
|
|
} |
149
|
|
|
return $scalar; |
150
|
|
|
} |
151
|
|
|
|
152
|
|
|
/** |
153
|
|
|
* Get a SQLHelper object |
154
|
|
|
* |
155
|
|
|
* @return SQLHelper |
156
|
|
|
*/ |
157
|
|
|
public function getSQLHelper() |
158
|
|
|
{ |
159
|
|
|
if (is_null($this->_sqlhelper)) { |
160
|
|
|
$this->_sqlhelper = new SQLHelper($this->getDBDataset()); |
161
|
|
|
} |
162
|
|
|
|
163
|
|
|
return $this->_sqlhelper; |
164
|
|
|
} |
165
|
|
|
|
166
|
|
|
/** |
167
|
|
|
* Get an Interator from an ID. Ideal for get data from PK |
168
|
|
|
* |
169
|
|
|
* @param string $tablename |
170
|
|
|
* @param string $key |
171
|
|
|
* @param string $value |
172
|
|
|
* @return IteratorInterface |
173
|
|
|
*/ |
174
|
|
|
protected function getIteratorbyId($tablename, $key, $value) |
175
|
|
|
{ |
176
|
|
|
$sql = "select * from $tablename where $key = [[$key]] "; |
177
|
|
|
$param = array(); |
178
|
|
|
$param[$key] = $value; |
179
|
|
|
return $this->getIterator($sql, $param); |
180
|
|
|
} |
181
|
|
|
|
182
|
|
|
/** |
183
|
|
|
* Get an Array from an existing Iterator |
184
|
|
|
* |
185
|
|
|
* @param IteratorInterface $it |
186
|
|
|
* @param string $key |
187
|
|
|
* @param string $value |
188
|
|
|
* @param string $firstElement |
189
|
|
|
* @return array |
190
|
|
|
*/ |
191
|
|
|
public static function getArrayFromIterator(IteratorInterface $it, $key, $value, $firstElement = "-- Selecione --") |
192
|
|
|
{ |
193
|
|
|
$retArray = array(); |
194
|
|
|
if ($firstElement != "") { |
195
|
|
|
$retArray[""] = $firstElement; |
196
|
|
|
} |
197
|
|
|
while ($it->hasNext()) { |
198
|
|
|
$sr = $it->moveNext(); |
199
|
|
|
$retArray[$sr->getField(strtolower($key))] = $sr->getField(strtolower($value)); |
200
|
|
|
} |
201
|
|
|
return $retArray; |
202
|
|
|
} |
203
|
|
|
|
204
|
|
|
/** |
205
|
|
|
* |
206
|
|
|
* @param IteratorInterface $it |
207
|
|
|
* @param string $name |
208
|
|
|
* @param array $fields |
209
|
|
|
* @param bool $echoToBrowser |
210
|
|
|
* @return string |
211
|
|
|
*/ |
212
|
|
|
public static function saveToCSV($it, $name = "data.csv", $fields = null, $echoToBrowser = true) |
213
|
|
|
{ |
214
|
|
|
if ($echoToBrowser) { |
215
|
|
|
ob_clean(); |
216
|
|
|
|
217
|
|
|
header("Content-Type: text/csv;"); |
218
|
|
|
header("Content-Disposition: attachment; filename=$name"); |
219
|
|
|
} |
220
|
|
|
|
221
|
|
|
$first = true; |
222
|
|
|
$line = ""; |
223
|
|
|
foreach ($it as $sr) { |
|
|
|
|
224
|
|
|
if ($first) { |
225
|
|
|
$first = false; |
226
|
|
|
|
227
|
|
|
if (is_null($fields)) { |
228
|
|
|
$fields = $sr->getFieldNames(); |
229
|
|
|
} |
230
|
|
|
|
231
|
|
|
$line .= '"' . implode('","', $fields) . '"' . "\n"; |
232
|
|
|
} |
233
|
|
|
|
234
|
|
|
$raw = array(); |
235
|
|
|
foreach ($fields as $field) { |
236
|
|
|
$raw[] = $sr->getField($field); |
237
|
|
|
} |
238
|
|
|
$line .= '"' . implode('","', array_values($raw)) . '"' . "\n"; |
239
|
|
|
|
240
|
|
|
if ($echoToBrowser) { |
241
|
|
|
echo $line; |
242
|
|
|
$line = ""; |
243
|
|
|
} |
244
|
|
|
} |
245
|
|
|
|
246
|
|
|
if (!$echoToBrowser) { |
247
|
|
|
return $line; |
248
|
|
|
} |
249
|
|
|
|
250
|
|
|
return null; |
251
|
|
|
} |
252
|
|
|
|
253
|
|
|
/** |
254
|
|
|
* Get a IDbFunctions class containing specific database operations |
255
|
|
|
* @return DBFunctionsInterface |
256
|
|
|
*/ |
257
|
|
|
public function getDbFunctions() |
258
|
|
|
{ |
259
|
|
|
return $this->getDBDataset()->getDbFunctions(); |
260
|
|
|
} |
261
|
|
|
|
262
|
|
|
public function beginTransaction() |
263
|
|
|
{ |
264
|
|
|
$this->getDBDataset()->beginTransaction(); |
265
|
|
|
} |
266
|
|
|
|
267
|
|
|
public function commitTransaction() |
268
|
|
|
{ |
269
|
|
|
$this->getDBDataset()->commitTransaction(); |
270
|
|
|
} |
271
|
|
|
|
272
|
|
|
public function rollbackTransaction() |
273
|
|
|
{ |
274
|
|
|
$this->getDBDataset()->rollbackTransaction(); |
275
|
|
|
} |
276
|
|
|
|
277
|
|
|
/** |
278
|
|
|
* @return bool |
279
|
|
|
*/ |
280
|
|
|
public function getDebug() |
281
|
|
|
{ |
282
|
|
|
return AnyDatasetContext::getInstance()->getDebug(); |
283
|
|
|
} |
284
|
|
|
} |
285
|
|
|
|