Total Complexity | 457 |
Total Lines | 2137 |
Duplicated Lines | 0 % |
Changes | 1 | ||
Bugs | 0 | Features | 0 |
Complex classes like Db often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes.
Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.
While breaking up the class, it is a good idea to analyze how other classes use Db, and based on these observations, apply Extract Interface, too.
1 | <?php |
||
51 | class Db |
||
52 | { |
||
53 | /** |
||
54 | * Fetchmode to fetch only as associative array with $colname => $value pairs |
||
55 | * |
||
56 | * Use the FETCH_* constants to be compatible, if we replace ADOdb ... |
||
57 | */ |
||
58 | const FETCH_ASSOC = ADODB_FETCH_ASSOC; |
||
59 | /** |
||
60 | * Fetchmode to fetch only as (numeric indexed) array: array($val1,$val2,...) |
||
61 | */ |
||
62 | const FETCH_NUM = ADODB_FETCH_NUM; |
||
63 | /** |
||
64 | * Fetchmode to have both numeric and column-name indexes |
||
65 | */ |
||
66 | const FETCH_BOTH = ADODB_FETCH_BOTH; |
||
67 | /** |
||
68 | * @var string $type translated database type: mysqlt+mysqli ==> mysql, same for odbc-types |
||
69 | */ |
||
70 | var $Type = ''; |
||
71 | |||
72 | /** |
||
73 | * @var string $type database type as defined in the header.inc.php, eg. mysqlt |
||
74 | */ |
||
75 | var $setupType = ''; |
||
76 | |||
77 | /** |
||
78 | * @var string $Host database host to connect to |
||
79 | */ |
||
80 | var $Host = ''; |
||
81 | |||
82 | /** |
||
83 | * @var string $Port port number of database to connect to |
||
84 | */ |
||
85 | var $Port = ''; |
||
86 | |||
87 | /** |
||
88 | * @var string $Database name of database to use |
||
89 | */ |
||
90 | var $Database = ''; |
||
91 | |||
92 | /** |
||
93 | * @var string $User name of database user |
||
94 | */ |
||
95 | var $User = ''; |
||
96 | |||
97 | /** |
||
98 | * @var string $Password password for database user |
||
99 | */ |
||
100 | var $Password = ''; |
||
101 | |||
102 | /** |
||
103 | * @var boolean $readonly only allow readonly access to database |
||
104 | */ |
||
105 | var $readonly = false; |
||
106 | |||
107 | /** |
||
108 | * @var int $Debug enable debuging - 0 no, 1 yes |
||
109 | */ |
||
110 | var $Debug = 0; |
||
111 | |||
112 | /** |
||
113 | * Log update querys to error_log |
||
114 | * |
||
115 | * @var boolean |
||
116 | */ |
||
117 | var $log_updates = false; |
||
118 | |||
119 | /** |
||
120 | * @var array $Record current record |
||
121 | */ |
||
122 | var $Record = array(); |
||
123 | |||
124 | /** |
||
125 | * @var int row number for current record |
||
126 | */ |
||
127 | var $Row; |
||
128 | |||
129 | /** |
||
130 | * @var int $Errno internal rdms error number for last error |
||
131 | */ |
||
132 | var $Errno = 0; |
||
133 | |||
134 | /** |
||
135 | * @var string descriptive text from last error |
||
136 | */ |
||
137 | var $Error = ''; |
||
138 | |||
139 | /** |
||
140 | * eGW's own query log, independent of the db-type, eg. /tmp/query.log |
||
141 | * |
||
142 | * @var string |
||
143 | */ |
||
144 | var $query_log; |
||
145 | |||
146 | /** |
||
147 | * ADOdb connection |
||
148 | * |
||
149 | * @var ADOConnection |
||
|
|||
150 | */ |
||
151 | var $Link_ID = 0; |
||
152 | /** |
||
153 | * ADOdb connection |
||
154 | * |
||
155 | * @var ADOConnection |
||
156 | */ |
||
157 | var $privat_Link_ID = False; // do we use a privat Link_ID or a reference to the global ADOdb object |
||
158 | |||
159 | /** |
||
160 | * Can be used to transparently convert tablenames, eg. 'mytable' => 'otherdb.othertable' |
||
161 | * |
||
162 | * Can be set eg. at the *end* of header.inc.php. |
||
163 | * Only works with new Api\Db methods (select, insert, update, delete) not query! |
||
164 | * |
||
165 | * @var array |
||
166 | */ |
||
167 | static $tablealiases = array(); |
||
168 | |||
169 | /** |
||
170 | * Callback to check if selected node is healty / should be used |
||
171 | * |
||
172 | * @var callback throwing Db\Exception\Connection, if connected node should NOT be used |
||
173 | */ |
||
174 | static $health_check; |
||
175 | |||
176 | /** |
||
177 | * db allows sub-queries, true for everything but mysql < 4.1 |
||
178 | * |
||
179 | * use like: if ($db->capabilities[self::CAPABILITY_SUB_QUERIES]) ... |
||
180 | */ |
||
181 | const CAPABILITY_SUB_QUERIES = 'sub_queries'; |
||
182 | /** |
||
183 | * db allows union queries, true for everything but mysql < 4.0 |
||
184 | */ |
||
185 | const CAPABILITY_UNION = 'union'; |
||
186 | /** |
||
187 | * db allows an outer join, will be set eg. for postgres |
||
188 | */ |
||
189 | const CAPABILITY_OUTER_JOIN = 'outer_join'; |
||
190 | /** |
||
191 | * db is able to use DISTINCT on text or blob columns |
||
192 | */ |
||
193 | const CAPABILITY_DISTINCT_ON_TEXT = 'distinct_on_text'; |
||
194 | /** |
||
195 | * DB is able to use LIKE on text columns |
||
196 | */ |
||
197 | const CAPABILITY_LIKE_ON_TEXT = 'like_on_text'; |
||
198 | /** |
||
199 | * DB allows ORDER on text columns |
||
200 | * |
||
201 | * boolean or string for sprintf for a cast (eg. 'CAST(%s AS varchar) |
||
202 | */ |
||
203 | const CAPABILITY_ORDER_ON_TEXT = 'order_on_text'; |
||
204 | /** |
||
205 | * case of returned column- and table-names: upper, lower(pgSql), preserv(MySQL) |
||
206 | */ |
||
207 | const CAPABILITY_NAME_CASE = 'name_case'; |
||
208 | /** |
||
209 | * does DB supports a changeable client-encoding |
||
210 | */ |
||
211 | const CAPABILITY_CLIENT_ENCODING = 'client_encoding'; |
||
212 | /** |
||
213 | * case insensitiv like statement (in $db->capabilities[self::CAPABILITY_CASE_INSENSITIV_LIKE]), default LIKE, ILIKE for postgres |
||
214 | */ |
||
215 | const CAPABILITY_CASE_INSENSITIV_LIKE = 'case_insensitive_like'; |
||
216 | /** |
||
217 | * DB requires varchar columns to be truncated to the max. size (eg. Postgres) |
||
218 | */ |
||
219 | const CAPABILITY_REQUIRE_TRUNCATE_VARCHAR = 'require_truncate_varchar'; |
||
220 | /** |
||
221 | * How to cast a column to varchar: CAST(%s AS varchar) |
||
222 | * |
||
223 | * MySQL requires to use CAST(%s AS char)! |
||
224 | * |
||
225 | * Use as: $sql = sprintf($GLOBALS['egw']->db->capabilities[self::CAPABILITY_CAST_AS_VARCHAR],$expression); |
||
226 | */ |
||
227 | const CAPABILITY_CAST_AS_VARCHAR = 'cast_as_varchar'; |
||
228 | /** |
||
229 | * default capabilities will be changed by method set_capabilities($ado_driver,$db_version) |
||
230 | * |
||
231 | * should be used with the CAPABILITY_* constants as key |
||
232 | * |
||
233 | * @var array |
||
234 | */ |
||
235 | var $capabilities = array( |
||
236 | self::CAPABILITY_SUB_QUERIES => true, |
||
237 | self::CAPABILITY_UNION => true, |
||
238 | self::CAPABILITY_OUTER_JOIN => false, |
||
239 | self::CAPABILITY_DISTINCT_ON_TEXT => true, |
||
240 | self::CAPABILITY_LIKE_ON_TEXT => true, |
||
241 | self::CAPABILITY_ORDER_ON_TEXT => true, |
||
242 | self::CAPABILITY_NAME_CASE => 'upper', |
||
243 | self::CAPABILITY_CLIENT_ENCODING => false, |
||
244 | self::CAPABILITY_CASE_INSENSITIV_LIKE => 'LIKE', |
||
245 | self::CAPABILITY_REQUIRE_TRUNCATE_VARCHAR => true, |
||
246 | self::CAPABILITY_CAST_AS_VARCHAR => 'CAST(%s AS varchar)', |
||
247 | ); |
||
248 | |||
249 | var $prepared_sql = array(); // sql is the index |
||
250 | |||
251 | /** |
||
252 | * Constructor |
||
253 | * |
||
254 | * @param array $db_data =null values for keys 'db_name', 'db_host', 'db_port', 'db_user', 'db_pass', 'db_type', 'db_readonly' |
||
255 | */ |
||
256 | function __construct(array $db_data=null) |
||
257 | { |
||
258 | if (!is_null($db_data)) |
||
259 | { |
||
260 | foreach(array( |
||
261 | 'Database' => 'db_name', |
||
262 | 'Host' => 'db_host', |
||
263 | 'Port' => 'db_port', |
||
264 | 'User' => 'db_user', |
||
265 | 'Password' => 'db_pass', |
||
266 | 'Type' => 'db_type', |
||
267 | 'readonly' => 'db_readonly', |
||
268 | ) as $var => $key) |
||
269 | { |
||
270 | $this->$var = $db_data[$key]; |
||
271 | } |
||
272 | } |
||
273 | //if ($GLOBALS['egw_info']['server']['default_domain'] == 'ralfsmacbook.local') $this->query_log = '/tmp/query.log'; |
||
274 | } |
||
275 | |||
276 | /** |
||
277 | * @param string $query query to be executed (optional) |
||
278 | */ |
||
279 | |||
280 | function db($query = '') |
||
281 | { |
||
282 | $this->query($query); |
||
283 | } |
||
284 | |||
285 | /** |
||
286 | * @return int current connection id |
||
287 | */ |
||
288 | function link_id() |
||
289 | { |
||
290 | return $this->Link_ID; |
||
291 | } |
||
292 | |||
293 | /** |
||
294 | * Open a connection to a database |
||
295 | * |
||
296 | * @param string $Database name of database to use (optional) |
||
297 | * @param string $Host database host to connect to (optional) |
||
298 | * @param string $Port database port to connect to (optional) |
||
299 | * @param string $User name of database user (optional) |
||
300 | * @param string $Password password for database user (optional) |
||
301 | * @param string $Type type of database (optional) |
||
302 | * @throws Db\Exception\Connection |
||
303 | * @return ADOConnection |
||
304 | */ |
||
305 | function connect($Database = NULL, $Host = NULL, $Port = NULL, $User = NULL, $Password = NULL, $Type = NULL) |
||
306 | { |
||
307 | /* Handle defaults */ |
||
308 | if (!is_null($Database) && $Database) |
||
309 | { |
||
310 | $this->Database = $Database; |
||
311 | } |
||
312 | if (!is_null($Host) && $Host) |
||
313 | { |
||
314 | $this->Host = $Host; |
||
315 | } |
||
316 | if (!is_null($Port) && $Port) |
||
317 | { |
||
318 | $this->Port = $Port; |
||
319 | } |
||
320 | if (!is_null($User) && $User) |
||
321 | { |
||
322 | $this->User = $User; |
||
323 | } |
||
324 | if (!is_null($Password) && $Password) |
||
325 | { |
||
326 | $this->Password = $Password; |
||
327 | } |
||
328 | if (!is_null($Type) && $Type) |
||
329 | { |
||
330 | $this->Type = $Type; |
||
331 | } |
||
332 | elseif (!$this->Type) |
||
333 | { |
||
334 | $this->Type = $GLOBALS['egw_info']['server']['db_type']; |
||
335 | } |
||
336 | // on connection failure re-try with an other host |
||
337 | // remembering in session which host we used last time |
||
338 | $use_host_from_session = true; |
||
339 | while(($host = $this->get_host(!$use_host_from_session))) |
||
340 | { |
||
341 | try { |
||
342 | //error_log(__METHOD__."() this->Host(s)=$this->Host, n=$n --> host=$host"); |
||
343 | $new_connection = !$this->Link_ID || !$this->Link_ID->IsConnected(); |
||
344 | $this->_connect($host); |
||
345 | // check if connected node is healty |
||
346 | if ($new_connection && self::$health_check) |
||
347 | { |
||
348 | call_user_func(self::$health_check, $this); |
||
349 | } |
||
350 | //error_log(__METHOD__."() host=$host, new_connection=$new_connection, this->Type=$this->Type, this->Host=$this->Host, wsrep_local_state=".array2string($state)); |
||
351 | return $this->Link_ID; |
||
352 | } |
||
353 | catch(Db\Exception\Connection $e) { |
||
354 | //_egw_log_exception($e); |
||
355 | $this->disconnect(); // force a new connect |
||
356 | $this->Type = $this->setupType; // get set to "mysql" for "mysqli" |
||
357 | $use_host_from_session = false; // re-try with next host from list |
||
358 | } |
||
359 | } |
||
360 | if (!isset($e)) |
||
361 | { |
||
362 | $e = new Db\Exception\Connection('No DB host set!'); |
||
363 | } |
||
364 | throw $e; |
||
365 | } |
||
366 | |||
367 | /** |
||
368 | * Check if just connected Galera cluster node is healthy / fully operational |
||
369 | * |
||
370 | * A node in state "Donor/Desynced" will block updates at the end of a SST. |
||
371 | * Therefore we try to avoid that node, if we have an alternative. |
||
372 | * |
||
373 | * To enable this check add the following to your header.inc.php: |
||
374 | * |
||
375 | * require_once(EGW_INCLUDE_ROOT.'/api/src/Db.php'); |
||
376 | * EGroupware\Api\Db::$health_check = array('EGroupware\Api\Db', 'galera_cluster_health'); |
||
377 | * |
||
378 | * @param Api\Db $db already connected Api\Db instance to check |
||
379 | * @throws Db\Exception\Connection if node should NOT be used |
||
380 | */ |
||
381 | static function galera_cluster_health(Db $db) |
||
382 | { |
||
383 | if (($state = $db->query("SHOW STATUS WHERE Variable_name in ('wsrep_cluster_size','wsrep_local_state','wsrep_local_state_comment')", |
||
384 | // GetAssoc in ADOdb 5.20 does not work with our default self::FETCH_BOTH |
||
385 | __LINE__, __FILE__, 0, -1, false, self::FETCH_ASSOC)->GetAssoc())) |
||
386 | { |
||
387 | if ($state['wsrep_local_state_comment'] == 'Synced' || |
||
388 | // if we have only 2 nodes (2. one starting), we can only use the donor |
||
389 | $state['wsrep_local_state_comment'] == 'Donor/Desynced' && |
||
390 | $state['wsrep_cluster_size'] == 2) return; |
||
391 | |||
392 | throw new Db\Exception\Connection('Node is NOT Synced! '.array2string($state)); |
||
393 | } |
||
394 | } |
||
395 | |||
396 | /** |
||
397 | * Get one of multiple (semicolon-separated) DB-hosts to use |
||
398 | * |
||
399 | * Which host to use is cached in session, default is first one. |
||
400 | * |
||
401 | * @param boolean $next =false true: move to next host |
||
402 | * @return boolean|string hostname or false, if already number-of-hosts plus 2 times called with $next == true |
||
403 | */ |
||
404 | public function get_host($next = false) |
||
422 | } |
||
423 | |||
424 | /** |
||
425 | * Connect to given host |
||
426 | * |
||
427 | * @param string $Host host to connect to |
||
428 | * @return ADOConnection |
||
429 | * @throws Db\Exception\Connection |
||
430 | */ |
||
431 | protected function _connect($Host) |
||
432 | { |
||
433 | if (!$this->Link_ID || $Host != $this->Link_ID->host) |
||
434 | { |
||
435 | $Database = $User = $Password = $Port = $Type = ''; |
||
436 | foreach(array('Database','User','Password','Port','Type') as $name) |
||
437 | { |
||
438 | $$name = $this->$name; |
||
439 | if (${$name}[0] == '@' && $name != 'Password') $$name = getenv(substr($$name, 1)); |
||
440 | } |
||
441 | $this->setupType = $php_extension = $Type; |
||
442 | |||
443 | switch($Type) // convert to ADO db-type-names |
||
444 | { |
||
445 | case 'pgsql': |
||
446 | $Type = 'postgres'; // name in ADOdb |
||
447 | // create our own pgsql connection-string, to allow unix domain soccets if !$Host |
||
448 | $Host = "dbname=$Database".($Host ? " host=$Host".($Port ? " port=$Port" : '') : ''). |
||
449 | " user=$User".($Password ? " password='".addslashes($Password)."'" : ''); |
||
450 | $User = $Password = $Database = ''; // to indicate $Host is a connection-string |
||
451 | break; |
||
452 | |||
453 | case 'odbc_mssql': |
||
454 | $php_extension = 'odbc'; |
||
455 | $Type = 'mssql'; |
||
456 | // fall through |
||
457 | case 'mssql': |
||
458 | if ($Port) $Host .= ','.$Port; |
||
459 | break; |
||
460 | |||
461 | case 'odbc_oracle': |
||
462 | $php_extension = 'odbc'; |
||
463 | $Type = 'oracle'; |
||
464 | break; |
||
465 | case 'oracle': |
||
466 | $php_extension = $Type = 'oci8'; |
||
467 | break; |
||
468 | |||
469 | case 'sapdb': |
||
470 | $Type = 'maxdb'; |
||
471 | // fall through |
||
472 | case 'maxdb': |
||
473 | $Type ='sapdb'; // name in ADOdb |
||
474 | $php_extension = 'odbc'; |
||
475 | break; |
||
476 | |||
477 | case 'mysqlt': |
||
478 | case 'mysql': |
||
479 | // if mysqli is available silently switch to it, mysql extension is deprecated and no longer available in php7+ |
||
480 | if (check_load_extension('mysqli')) |
||
481 | { |
||
482 | $php_extension = $Type = 'mysqli'; |
||
483 | } |
||
484 | else |
||
485 | { |
||
486 | $php_extension = 'mysql'; // you can use $this->setupType to determine if it's mysqlt or mysql |
||
487 | } |
||
488 | // fall through |
||
489 | case 'mysqli': |
||
490 | $this->Type = 'mysql'; // need to be "mysql", so apps can check just for "mysql"! |
||
491 | // fall through |
||
492 | default: |
||
493 | if ($Port) $Host .= ':'.$Port; |
||
494 | break; |
||
495 | } |
||
496 | if (!isset($GLOBALS['egw']->ADOdb) || // we have no connection so far |
||
497 | (is_object($GLOBALS['egw']->db) && // we connect to a different db, then the global one |
||
498 | ($this->Type != $GLOBALS['egw']->db->Type || |
||
499 | $this->Database != $GLOBALS['egw']->db->Database || |
||
500 | $this->User != $GLOBALS['egw']->db->User || |
||
501 | $this->Host != $GLOBALS['egw']->db->Host || |
||
502 | $this->Port != $GLOBALS['egw']->db->Port))) |
||
503 | { |
||
504 | if (!check_load_extension($php_extension)) |
||
505 | { |
||
506 | throw new Db\Exception\Connection("Necessary php database support for $this->Type (".PHP_SHLIB_PREFIX.$php_extension.'.'.PHP_SHLIB_SUFFIX.") not loaded and can't be loaded, exiting !!!"); |
||
507 | } |
||
508 | if (!isset($GLOBALS['egw']->ADOdb)) // use the global object to store the connection |
||
509 | { |
||
510 | $this->Link_ID =& $GLOBALS['egw']->ADOdb; |
||
511 | } |
||
512 | else |
||
513 | { |
||
514 | $this->privat_Link_ID = True; // remember that we use a privat Link_ID for disconnect |
||
515 | } |
||
516 | $this->Link_ID = ADONewConnection($Type); |
||
517 | if (!$this->Link_ID) |
||
518 | { |
||
519 | throw new Db\Exception\Connection("No ADOdb support for '$Type' ($this->Type) !!!"); |
||
520 | } |
||
521 | if ($Type == 'mysqli') |
||
522 | { |
||
523 | // set a connection timeout of 1 second, to allow quicker failover to other db-nodes (default is 20s) |
||
524 | $this->Link_ID->setConnectionParameter(MYSQLI_OPT_CONNECT_TIMEOUT, 1); |
||
525 | } |
||
526 | $connect = $GLOBALS['egw_info']['server']['db_persistent'] && |
||
527 | // do NOT attempt persistent connection, if it is switched off in php.ini (it will only cause a warning) |
||
528 | ($Type !== 'mysqli' || ini_get('mysqli.allow_persistent')) ? |
||
529 | 'PConnect' : 'Connect'; |
||
530 | |||
531 | if (($Ok = $this->Link_ID->$connect($Host, $User, $Password, $Database))) |
||
532 | { |
||
533 | $this->ServerInfo = $this->Link_ID->ServerInfo(); |
||
534 | $this->set_capabilities($Type,$this->ServerInfo['version']); |
||
535 | |||
536 | // switch off MySQL 5.7+ ONLY_FULL_GROUP_BY sql_mode |
||
537 | if (substr($this->Type, 0, 5) == 'mysql' && $this->ServerInfo['version'] >= 5.7 && $this->ServerInfo['version'] < 10.0) |
||
538 | { |
||
539 | $this->query("SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))", __LINE__, __FILE__); |
||
540 | } |
||
541 | } |
||
542 | if (!$Ok) |
||
543 | { |
||
544 | $Host = preg_replace('/password=[^ ]+/','password=$Password',$Host); // eg. postgres dsn contains password |
||
545 | throw new Db\Exception\Connection("ADOdb::$connect($Host, $User, \$Password, $Database) failed."); |
||
546 | } |
||
547 | if ($this->Debug) |
||
548 | { |
||
549 | echo function_backtrace(); |
||
550 | echo "<p>new ADOdb connection to $Type://$Host/$Database: Link_ID".($this->Link_ID === $GLOBALS['egw']->ADOdb ? '===' : '!==')."\$GLOBALS[egw]->ADOdb</p>"; |
||
551 | //echo "<p>".print_r($this->Link_ID->ServerInfo(),true)."</p>\n"; |
||
552 | _debug_array($this); |
||
553 | echo "\$GLOBALS[egw]->db="; _debug_array($GLOBALS[egw]->db); |
||
554 | } |
||
555 | if ($Type == 'mssql') |
||
556 | { |
||
557 | // this is the format ADOdb expects |
||
558 | $this->Link_ID->Execute('SET DATEFORMAT ymd'); |
||
559 | // sets the limit to the maximum |
||
560 | ini_set('mssql.textlimit',2147483647); |
||
561 | ini_set('mssql.sizelimit',2147483647); |
||
562 | } |
||
563 | // set our default charset |
||
564 | $this->Link_ID->SetCharSet($this->Type == 'mysql' ? 'utf8' : 'utf-8'); |
||
565 | |||
566 | $new_connection = true; |
||
567 | } |
||
568 | else |
||
569 | { |
||
570 | $this->Link_ID =& $GLOBALS['egw']->ADOdb; |
||
571 | } |
||
572 | } |
||
573 | if (!$this->Link_ID->isConnected() && !$this->Link_ID->Connect()) |
||
574 | { |
||
575 | $Host = preg_replace('/password=[^ ]+/','password=$Password',$Host); // eg. postgres dsn contains password |
||
576 | throw new Db\Exception\Connection("ADOdb::$connect($Host, $User, \$Password, $Database) reconnect failed."); |
||
577 | } |
||
578 | // fix due to caching and reusing of connection not correctly set $this->Type == 'mysql' |
||
579 | if ($this->Type == 'mysqli') |
||
580 | { |
||
581 | $this->setupType = $this->Type; |
||
582 | $this->Type = 'mysql'; |
||
583 | } |
||
584 | if ($new_connection) |
||
585 | { |
||
586 | foreach(get_included_files() as $file) |
||
587 | { |
||
588 | if (strpos($file,'adodb') !== false && !in_array($file,(array)$_SESSION['egw_required_files'])) |
||
589 | { |
||
590 | $_SESSION['egw_required_files'][] = $file; |
||
591 | //error_log(__METHOD__."() egw_required_files[] = $file"); |
||
592 | } |
||
593 | } |
||
594 | } |
||
595 | //echo "<p>".print_r($this->Link_ID->ServerInfo(),true)."</p>\n"; |
||
596 | return $this->Link_ID; |
||
597 | } |
||
598 | |||
599 | /** |
||
600 | * Magic method to re-connect with the database, if the object get's restored from the session |
||
601 | */ |
||
602 | function __wakeup() |
||
603 | { |
||
604 | $this->connect(); // we need to re-connect |
||
605 | } |
||
606 | |||
607 | /** |
||
608 | * Magic method called when object get's serialized |
||
609 | * |
||
610 | * We do NOT store Link_ID and private_Link_ID, as we need to reconnect anyway. |
||
611 | * This also ensures reevaluating environment-data or multiple hosts in connection-data! |
||
612 | * |
||
613 | * @return array |
||
614 | */ |
||
615 | function __sleep() |
||
616 | { |
||
617 | if (!empty($this->setupType)) $this->Type = $this->setupType; // restore Type eg. to mysqli |
||
618 | |||
619 | $vars = get_object_vars($this); |
||
620 | unset($vars['Link_ID'], $vars['Query_ID'], $vars['privat_Link_ID']); |
||
621 | return array_keys($vars); |
||
622 | } |
||
623 | |||
624 | /** |
||
625 | * changes defaults set in class-var $capabilities depending on db-type and -version |
||
626 | * |
||
627 | * @param string $adodb_driver mysql, postgres, mssql, sapdb, oci8 |
||
628 | * @param string $db_version version-number of connected db-server, as reported by ServerInfo |
||
629 | */ |
||
630 | function set_capabilities($adodb_driver,$db_version) |
||
631 | { |
||
632 | switch($adodb_driver) |
||
633 | { |
||
634 | case 'mysql': |
||
635 | case 'mysqlt': |
||
636 | case 'mysqli': |
||
637 | $this->capabilities[self::CAPABILITY_SUB_QUERIES] = (float) $db_version >= 4.1; |
||
638 | $this->capabilities[self::CAPABILITY_UNION] = (float) $db_version >= 4.0; |
||
639 | $this->capabilities[self::CAPABILITY_NAME_CASE] = 'preserv'; |
||
640 | $this->capabilities[self::CAPABILITY_CLIENT_ENCODING] = (float) $db_version >= 4.1; |
||
641 | $this->capabilities[self::CAPABILITY_CAST_AS_VARCHAR] = 'CAST(%s AS char)'; |
||
642 | break; |
||
643 | |||
644 | case 'postgres': |
||
645 | $this->capabilities[self::CAPABILITY_NAME_CASE] = 'lower'; |
||
646 | $this->capabilities[self::CAPABILITY_CLIENT_ENCODING] = (float) $db_version >= 7.4; |
||
647 | $this->capabilities[self::CAPABILITY_OUTER_JOIN] = true; |
||
648 | $this->capabilities[self::CAPABILITY_CASE_INSENSITIV_LIKE] = '::text ILIKE'; |
||
649 | $this->capabilities[self::CAPABILITY_REQUIRE_TRUNCATE_VARCHAR] = true; |
||
650 | break; |
||
651 | |||
652 | case 'mssql': |
||
653 | $this->capabilities[self::CAPABILITY_DISTINCT_ON_TEXT] = false; |
||
654 | $this->capabilities[self::CAPABILITY_ORDER_ON_TEXT] = 'CAST (%s AS varchar)'; |
||
655 | break; |
||
656 | |||
657 | case 'maxdb': // if Lim ever changes it to maxdb ;-) |
||
658 | case 'sapdb': |
||
659 | $this->capabilities[self::CAPABILITY_DISTINCT_ON_TEXT] = false; |
||
660 | $this->capabilities[self::CAPABILITY_LIKE_ON_TEXT] = $db_version >= 7.6; |
||
661 | $this->capabilities[self::CAPABILITY_ORDER_ON_TEXT] = false; |
||
662 | break; |
||
663 | } |
||
664 | //echo "db::set_capabilities('$adodb_driver',$db_version)"; _debug_array($this->capabilities); |
||
665 | } |
||
666 | |||
667 | /** |
||
668 | * Close a connection to a database |
||
669 | */ |
||
670 | function disconnect() |
||
671 | { |
||
672 | if (!$this->privat_Link_ID) |
||
673 | { |
||
674 | unset($GLOBALS['egw']->ADOdb); |
||
675 | } |
||
676 | unset($this->Link_ID); |
||
677 | $this->Link_ID = 0; |
||
678 | |||
679 | if (!empty($this->setupType)) $this->Type = $this->setupType; |
||
680 | } |
||
681 | |||
682 | /** |
||
683 | * Convert a unix timestamp to a rdms specific timestamp |
||
684 | * |
||
685 | * @param int unix timestamp |
||
686 | * @return string rdms specific timestamp |
||
687 | */ |
||
688 | function to_timestamp($epoch) |
||
689 | { |
||
690 | if (!$this->Link_ID && !$this->connect()) |
||
691 | { |
||
692 | return False; |
||
693 | } |
||
694 | // the substring is needed as the string is already in quotes |
||
695 | return substr($this->Link_ID->DBTimeStamp($epoch),1,-1); |
||
696 | } |
||
697 | |||
698 | /** |
||
699 | * Convert a rdms specific timestamp to a unix timestamp |
||
700 | * |
||
701 | * @param string rdms specific timestamp |
||
702 | * @return int unix timestamp |
||
703 | */ |
||
704 | function from_timestamp($timestamp) |
||
705 | { |
||
706 | if (!$this->Link_ID && !$this->connect()) |
||
707 | { |
||
708 | return False; |
||
709 | } |
||
710 | return $this->Link_ID->UnixTimeStamp($timestamp); |
||
711 | } |
||
712 | |||
713 | /** |
||
714 | * convert a rdbms specific boolean value |
||
715 | * |
||
716 | * @param string $val boolean value in db-specfic notation |
||
717 | * @return boolean |
||
718 | */ |
||
719 | public static function from_bool($val) |
||
722 | } |
||
723 | |||
724 | /** |
||
725 | * Execute a query |
||
726 | * |
||
727 | * @param string $Query_String the query to be executed |
||
728 | * @param int $line the line method was called from - use __LINE__ |
||
729 | * @param string $file the file method was called from - use __FILE__ |
||
730 | * @param int $offset row to start from, default 0 |
||
731 | * @param int $num_rows number of rows to return (optional), default -1 = all, 0 will use $GLOBALS['egw_info']['user']['preferences']['common']['maxmatchs'] |
||
732 | * @param array|boolean $inputarr array for binding variables to parameters or false (default) |
||
733 | * @param int $fetchmode =self::FETCH_BOTH self::FETCH_BOTH (default), self::FETCH_ASSOC or self::FETCH_NUM |
||
734 | * @param boolean $reconnect =true true: try reconnecting if server closes connection, false: dont (mysql only!) |
||
735 | * @return ADORecordSet or false, if the query fails |
||
736 | * @throws Db\Exception\InvalidSql with $this->Link_ID->ErrorNo() as code |
||
737 | */ |
||
738 | function query($Query_String, $line = '', $file = '', $offset=0, $num_rows=-1, $inputarr=false, $fetchmode=self::FETCH_BOTH, $reconnect=true) |
||
739 | { |
||
740 | unset($line, $file); // not used anymore |
||
741 | |||
742 | if ($Query_String == '') |
||
743 | { |
||
744 | return 0; |
||
745 | } |
||
746 | if (!$this->Link_ID && !$this->connect()) |
||
747 | { |
||
748 | return False; |
||
749 | } |
||
750 | |||
751 | if ($this->Link_ID->fetchMode != $fetchmode) |
||
752 | { |
||
753 | $this->Link_ID->SetFetchMode($fetchmode); |
||
754 | } |
||
755 | if (!$num_rows) |
||
756 | { |
||
757 | $num_rows = $GLOBALS['egw_info']['user']['preferences']['common']['maxmatchs']; |
||
758 | } |
||
759 | if (($this->readonly || $this->log_updates) && !preg_match('/^\(?(SELECT|SET|SHOW)/i', $Query_String)) |
||
760 | { |
||
761 | if ($this->log_updates) error_log($Query_String.': '.function_backtrace()); |
||
762 | if ($this->readonly) |
||
763 | { |
||
764 | $this->Error = 'Database is readonly'; |
||
765 | $this->Errno = -2; |
||
766 | return 0; |
||
767 | } |
||
768 | } |
||
769 | if ($num_rows > 0) |
||
770 | { |
||
771 | $rs = $this->Link_ID->SelectLimit($Query_String,$num_rows,(int)$offset,$inputarr); |
||
772 | } |
||
773 | else |
||
774 | { |
||
775 | $rs = $this->Link_ID->Execute($Query_String,$inputarr); |
||
776 | } |
||
777 | $this->Row = 0; |
||
778 | $this->Errno = $this->Link_ID->ErrorNo(); |
||
779 | $this->Error = $this->Link_ID->ErrorMsg(); |
||
780 | |||
781 | if ($this->query_log && ($f = @fopen($this->query_log,'a+'))) |
||
782 | { |
||
783 | fwrite($f,'['.(isset($GLOBALS['egw_setup']) ? $GLOBALS['egw_setup']->ConfigDomain : $GLOBALS['egw_info']['user']['domain']).'] '); |
||
784 | fwrite($f,date('Y-m-d H:i:s ').$Query_String.($inputarr ? "\n".print_r($inputarr,true) : '')."\n"); |
||
785 | if (!$rs) |
||
786 | { |
||
787 | fwrite($f,"*** Error $this->Errno: $this->Error\n".function_backtrace()."\n"); |
||
788 | } |
||
789 | fclose($f); |
||
790 | } |
||
791 | if (!$rs) |
||
792 | { |
||
793 | if ($reconnect && $this->Type == 'mysql' && $this->Errno == 2006) // Server has gone away |
||
794 | { |
||
795 | $this->disconnect(); |
||
796 | return $this->query($Query_String, $line, $file, $offset, $num_rows, $inputarr, $fetchmode, false); |
||
797 | } |
||
798 | throw new Db\Exception\InvalidSql("Invalid SQL: ".(is_array($Query_String)?$Query_String[0]:$Query_String). |
||
799 | "\n$this->Error ($this->Errno)". |
||
800 | ($inputarr ? "\nParameters: '".implode("','",$inputarr)."'":''), $this->Errno); |
||
801 | } |
||
802 | elseif(empty($rs->sql)) $rs->sql = $Query_String; |
||
803 | return $rs; |
||
804 | } |
||
805 | |||
806 | /** |
||
807 | * Execute a query with limited result set |
||
808 | * |
||
809 | * @param string $Query_String the query to be executed |
||
810 | * @param int $offset row to start from, default 0 |
||
811 | * @param int $line the line method was called from - use __LINE__ |
||
812 | * @param string $file the file method was called from - use __FILE__ |
||
813 | * @param int $num_rows number of rows to return (optional), default -1 = all, 0 will use $GLOBALS['egw_info']['user']['preferences']['common']['maxmatchs'] |
||
814 | * @param array|boolean $inputarr array for binding variables to parameters or false (default) |
||
815 | * @return ADORecordSet or false, if the query fails |
||
816 | */ |
||
817 | function limit_query($Query_String, $offset, $line = '', $file = '', $num_rows = '',$inputarr=false) |
||
818 | { |
||
819 | return $this->query($Query_String,$line,$file,$offset,$num_rows,$inputarr); |
||
820 | } |
||
821 | |||
822 | /** |
||
823 | * Begin Transaction |
||
824 | * |
||
825 | * @return int/boolean current transaction-id, of false if no connection |
||
826 | */ |
||
827 | function transaction_begin() |
||
828 | { |
||
829 | if (!$this->Link_ID && !$this->connect()) |
||
830 | { |
||
831 | return False; |
||
832 | } |
||
833 | //return $this->Link_ID->BeginTrans(); |
||
834 | return $this->Link_ID->StartTrans(); |
||
835 | } |
||
836 | |||
837 | /** |
||
838 | * Complete the transaction |
||
839 | * |
||
840 | * @return bool True if sucessful, False if fails |
||
841 | */ |
||
842 | function transaction_commit() |
||
843 | { |
||
844 | if (!$this->Link_ID && !$this->connect()) |
||
845 | { |
||
846 | return False; |
||
847 | } |
||
848 | //return $this->Link_ID->CommitTrans(); |
||
849 | return $this->Link_ID->CompleteTrans(); |
||
850 | } |
||
851 | |||
852 | /** |
||
853 | * Rollback the current transaction |
||
854 | * |
||
855 | * @return bool True if sucessful, False if fails |
||
856 | */ |
||
857 | function transaction_abort() |
||
858 | { |
||
859 | if (!$this->Link_ID && !$this->connect()) |
||
860 | { |
||
861 | return False; |
||
862 | } |
||
863 | //return $this->Link_ID->RollbackTrans(); |
||
864 | return $this->Link_ID->FailTrans(); |
||
865 | } |
||
866 | |||
867 | /** |
||
868 | * Lock a rows in table |
||
869 | * |
||
870 | * Will escalate and lock the table if row locking not supported. |
||
871 | * Will normally free the lock at the end of the transaction. |
||
872 | * |
||
873 | * @param string $table name of table to lock |
||
874 | * @param string $where ='true' where clause to use, eg: "WHERE row=12". Defaults to lock whole table. |
||
875 | * @param string $col ='1 as adodbignore' |
||
876 | */ |
||
877 | function row_lock($table, $where='true', $col='1 as adodbignore') |
||
878 | { |
||
879 | if (!$this->Link_ID && !$this->connect()) |
||
880 | { |
||
881 | return False; |
||
882 | } |
||
883 | if (self::$tablealiases && isset(self::$tablealiases[$table])) |
||
884 | { |
||
885 | $table = self::$tablealiases[$table]; |
||
886 | } |
||
887 | |||
888 | return $this->Link_ID->RowLock($table, $where, $col); |
||
889 | } |
||
890 | |||
891 | /** |
||
892 | * Commit changed rows in table |
||
893 | * |
||
894 | * @param string $table |
||
895 | * @return boolean |
||
896 | */ |
||
897 | function commit_lock($table) |
||
898 | { |
||
899 | if (!$this->Link_ID && !$this->connect()) |
||
900 | { |
||
901 | return False; |
||
902 | } |
||
903 | if (self::$tablealiases && isset(self::$tablealiases[$table])) |
||
904 | { |
||
905 | $table = self::$tablealiases[$table]; |
||
906 | } |
||
907 | |||
908 | return $this->Link_ID->CommitLock($table); |
||
909 | } |
||
910 | |||
911 | /** |
||
912 | * Unlock rows in table |
||
913 | * |
||
914 | * @param string $table |
||
915 | * @return boolean |
||
916 | */ |
||
917 | function rollback_lock($table) |
||
929 | } |
||
930 | |||
931 | /** |
||
932 | * Find the primary key of the last insertion on the current db connection |
||
933 | * |
||
934 | * @param string $table name of table the insert was performed on |
||
935 | * @param string $field the autoincrement primary key of the table |
||
936 | * @return int the id, -1 if fails |
||
937 | */ |
||
938 | function get_last_insert_id($table, $field) |
||
957 | } |
||
958 | |||
959 | /** |
||
960 | * Get the number of rows affected by last update or delete |
||
961 | * |
||
962 | * @return int number of rows |
||
963 | */ |
||
964 | function affected_rows() |
||
973 | } |
||
974 | |||
975 | /** |
||
976 | * Get description of a table |
||
977 | * |
||
978 | * Beside the column-name all other data depends on the db-type !!! |
||
979 | * |
||
980 | * @param string $table name of table to describe |
||
981 | * @param bool $full optional, default False summary information, True full information |
||
982 | * @return array table meta data |
||
983 | */ |
||
984 | function metadata($table='',$full=false) |
||
985 | { |
||
986 | if (!$this->Link_ID && !$this->connect()) |
||
987 | { |
||
988 | return False; |
||
989 | } |
||
990 | $columns = $this->Link_ID->MetaColumns($table); |
||
991 | //$columns = $this->Link_ID->MetaColumnsSQL($table); |
||
992 | //echo "<b>metadata</b>('$table')=<pre>\n".print_r($columns,True)."</pre>\n"; |
||
993 | |||
994 | $metadata = array(); |
||
995 | $i = 0; |
||
996 | foreach($columns as $column) |
||
997 | { |
||
998 | // for backwards compatibilty (depreciated) |
||
999 | $flags = null; |
||
1000 | if($column->auto_increment) $flags .= "auto_increment "; |
||
1001 | if($column->primary_key) $flags .= "primary_key "; |
||
1002 | if($column->binary) $flags .= "binary "; |
||
1003 | |||
1004 | $metadata[$i] = array( |
||
1005 | 'table' => $table, |
||
1006 | 'name' => $column->name, |
||
1007 | 'type' => $column->type, |
||
1008 | 'len' => $column->max_length, |
||
1009 | 'flags' => $flags, // for backwards compatibilty (depreciated) used by JiNN atm |
||
1010 | 'not_null' => $column->not_null, |
||
1011 | 'auto_increment' => $column->auto_increment, |
||
1012 | 'primary_key' => $column->primary_key, |
||
1013 | 'binary' => $column->binary, |
||
1014 | 'has_default' => $column->has_default, |
||
1015 | 'default' => $column->default_value, |
||
1016 | ); |
||
1017 | $metadata[$i]['table'] = $table; |
||
1018 | if ($full) |
||
1019 | { |
||
1020 | $metadata['meta'][$column->name] = $i; |
||
1021 | } |
||
1022 | ++$i; |
||
1023 | } |
||
1024 | if ($full) |
||
1025 | { |
||
1026 | $metadata['num_fields'] = $i; |
||
1027 | } |
||
1028 | return $metadata; |
||
1029 | } |
||
1030 | |||
1031 | /** |
||
1032 | * Get a list of table names in the current database |
||
1033 | * |
||
1034 | * @param boolean $just_name =false true return array of table-names, false return old format |
||
1035 | * @return array list of the tables |
||
1036 | */ |
||
1037 | function table_names($just_name=false) |
||
1062 | } |
||
1063 | |||
1064 | /** |
||
1065 | * Return a list of indexes in current database |
||
1066 | * |
||
1067 | * @return array list of indexes |
||
1068 | */ |
||
1069 | function index_names() |
||
1086 | } |
||
1087 | |||
1088 | /** |
||
1089 | * Returns an array containing column names that are the primary keys of $tablename. |
||
1090 | * |
||
1091 | * @return array of columns |
||
1092 | */ |
||
1093 | function pkey_columns($tablename) |
||
1100 | } |
||
1101 | |||
1102 | /** |
||
1103 | * Create a new database |
||
1104 | * |
||
1105 | * @param string $adminname name of database administrator user (optional) |
||
1106 | * @param string $adminpasswd password for the database administrator user (optional) |
||
1107 | * @param string $charset default charset for the database |
||
1108 | * @param string $grant_host ='localhost' host/ip of the webserver |
||
1109 | */ |
||
1110 | function create_database($adminname = '', $adminpasswd = '', $charset='', $grant_host='localhost') |
||
1111 | { |
||
1112 | $currentUser = $this->User; |
||
1113 | $currentPassword = $this->Password; |
||
1114 | $currentDatabase = $this->Database; |
||
1115 | |||
1116 | if ($adminname != '') |
||
1117 | { |
||
1118 | $this->User = $adminname; |
||
1119 | $this->Password = $adminpasswd; |
||
1120 | $this->Database = $this->Type == 'pgsql' ? 'template1' : 'mysql'; |
||
1121 | } |
||
1122 | $this->disconnect(); |
||
1123 | |||
1124 | $sqls = array(); |
||
1125 | switch ($this->Type) |
||
1126 | { |
||
1127 | case 'pgsql': |
||
1128 | $sqls[] = "CREATE DATABASE $currentDatabase"; |
||
1129 | break; |
||
1130 | case 'mysql': |
||
1131 | case 'mysqli': |
||
1132 | case 'mysqlt': |
||
1133 | $create = "CREATE DATABASE `$currentDatabase`"; |
||
1134 | if ($charset && isset($this->Link_ID->charset2mysql[$charset]) && (float) $this->ServerInfo['version'] >= 4.1) |
||
1135 | { |
||
1136 | $create .= ' DEFAULT CHARACTER SET '.$this->Link_ID->charset2mysql[$charset].';'; |
||
1137 | } |
||
1138 | $sqls[] = $create; |
||
1139 | $sqls[] = "CREATE USER $currentUser@'$grant_host' IDENTIFIED BY ".$this->quote($currentPassword); |
||
1140 | $sqls[] = "GRANT ALL PRIVILEGES ON `$currentDatabase`.* TO $currentUser@'$grant_host'"; |
||
1141 | break; |
||
1142 | default: |
||
1143 | throw new Exception\WrongParameter(__METHOD__."(user=$adminname, \$pw) not yet implemented for DB-type '$this->Type'"); |
||
1144 | } |
||
1145 | //error_log(__METHOD__."() this->Type=$this->Type: sqls=".array2string($sqls)); |
||
1146 | foreach($sqls as $sql) |
||
1147 | { |
||
1148 | $this->query($sql,__LINE__,__FILE__); |
||
1149 | } |
||
1150 | $this->disconnect(); |
||
1151 | |||
1152 | $this->User = $currentUser; |
||
1153 | $this->Password = $currentPassword; |
||
1154 | $this->Database = $currentDatabase; |
||
1155 | $this->connect(); |
||
1156 | } |
||
1157 | |||
1158 | /** |
||
1159 | * concat a variable number of strings together, to be used in a query |
||
1160 | * |
||
1161 | * Example: $db->concat($db->quote('Hallo '),'username') would return |
||
1162 | * for mysql "concat('Hallo ',username)" or "'Hallo ' || username" for postgres |
||
1163 | * @param string $str1 already quoted stringliteral or column-name, variable number of arguments |
||
1164 | * @return string to be used in a query |
||
1165 | */ |
||
1166 | function concat(/*$str1, ...*/) |
||
1167 | { |
||
1168 | $args = func_get_args(); |
||
1169 | |||
1170 | if (!$this->Link_ID && !$this->connect()) |
||
1171 | { |
||
1172 | return False; |
||
1173 | } |
||
1174 | return call_user_func_array(array(&$this->Link_ID,'concat'),$args); |
||
1175 | } |
||
1176 | |||
1177 | /** |
||
1178 | * Concat grouped values of an expression with optional order and separator |
||
1179 | * |
||
1180 | * @param string $expr column-name or expression optional prefixed with "DISTINCT" |
||
1181 | * @param string $order_by ='' optional order |
||
1182 | * @param string $separator =',' optional separator, default is comma |
||
1183 | * @return string|boolean false if not supported by dbms |
||
1184 | */ |
||
1185 | function group_concat($expr, $order_by='', $separator=',') |
||
1186 | { |
||
1187 | switch($this->Type) |
||
1188 | { |
||
1189 | case 'mysqli': |
||
1190 | case 'mysql': |
||
1191 | $sql = 'GROUP_CONCAT('.$expr; |
||
1192 | if ($order_by) $sql .= ' ORDER BY '.$order_by; |
||
1193 | if ($separator != ',') $sql .= ' SEPARATOR '.$this->quote($separator); |
||
1194 | $sql .= ')'; |
||
1195 | break; |
||
1196 | |||
1197 | case 'pgsql': // requires for Postgresql < 8.4 to have a custom ARRAY_AGG method installed! |
||
1198 | if ($this->Type == 'pgsql' && $this->ServerInfo['version'] < 8.4) |
||
1199 | { |
||
1200 | return false; |
||
1201 | } |
||
1202 | $sql = 'ARRAY_TO_STRING(ARRAY_AGG('.$expr; |
||
1203 | if ($order_by) $sql .= ' ORDER BY '.$order_by; |
||
1204 | $sql .= '), '.$this->quote($separator).')'; |
||
1205 | break; |
||
1206 | |||
1207 | default: // probably gives an sql error anyway |
||
1208 | return false; |
||
1209 | } |
||
1210 | return $sql; |
||
1211 | } |
||
1212 | |||
1213 | /** |
||
1214 | * SQL returning character (not byte!) positions for $substr in $str |
||
1215 | * |
||
1216 | * @param string $str |
||
1217 | * @param string $substr |
||
1218 | * @return string SQL returning character (not byte!) positions for $substr in $str |
||
1219 | */ |
||
1220 | function strpos($str, $substr) |
||
1221 | { |
||
1222 | switch($this->Type) |
||
1223 | { |
||
1224 | case 'mysql': |
||
1225 | return "LOCATE($substr,$str)"; |
||
1226 | case 'pgsql': |
||
1227 | return "STRPOS($str,$substr)"; |
||
1228 | case 'mssql': |
||
1229 | return "CHARINDEX($substr,$str)"; |
||
1230 | } |
||
1231 | die(__METHOD__." not implemented for DB type '$this->Type'!"); |
||
1232 | } |
||
1233 | |||
1234 | /** |
||
1235 | * Convert a DB specific timestamp in a unix timestamp stored as integer, like MySQL: UNIX_TIMESTAMP(ts) |
||
1236 | * |
||
1237 | * @param string $expr name of an integer column or integer expression |
||
1238 | * @return string SQL expression of type timestamp |
||
1239 | */ |
||
1240 | function unix_timestamp($expr) |
||
1241 | { |
||
1242 | switch($this->Type) |
||
1243 | { |
||
1244 | case 'mysql': |
||
1245 | return "UNIX_TIMESTAMP($expr)"; |
||
1246 | |||
1247 | case 'pgsql': |
||
1248 | return "EXTRACT(EPOCH FROM CAST($expr AS TIMESTAMP))"; |
||
1249 | |||
1250 | case 'mssql': |
||
1251 | return "DATEDIFF(second,'1970-01-01',($expr))"; |
||
1252 | } |
||
1253 | } |
||
1254 | |||
1255 | /** |
||
1256 | * Convert a unix timestamp stored as integer in the db into a db timestamp, like MySQL: FROM_UNIXTIME(ts) |
||
1257 | * |
||
1258 | * @param string $expr name of an integer column or integer expression |
||
1259 | * @return string SQL expression of type timestamp |
||
1260 | */ |
||
1261 | function from_unixtime($expr) |
||
1262 | { |
||
1263 | switch($this->Type) |
||
1264 | { |
||
1265 | case 'mysql': |
||
1266 | return "FROM_UNIXTIME($expr)"; |
||
1267 | |||
1268 | case 'pgsql': |
||
1269 | return "(TIMESTAMP WITH TIME ZONE 'epoch' + ($expr) * INTERVAL '1 sec')"; |
||
1270 | |||
1271 | case 'mssql': // we use date(,0) as we store server-time |
||
1272 | return "DATEADD(second,($expr),'".date('Y-m-d H:i:s',0)."')"; |
||
1273 | } |
||
1274 | return false; |
||
1275 | } |
||
1276 | |||
1277 | /** |
||
1278 | * format a timestamp as string, like MySQL: DATE_FORMAT(ts) |
||
1279 | * |
||
1280 | * Please note: only a subset of the MySQL formats are implemented |
||
1281 | * |
||
1282 | * @param string $expr name of a timestamp column or timestamp expression |
||
1283 | * @param string $format format specifier like '%Y-%m-%d %H:%i:%s' or '%V%X' ('%v%x') weeknumber & year with Sunday (Monday) as first day |
||
1284 | * @return string SQL expression of type timestamp |
||
1285 | */ |
||
1286 | function date_format($expr,$format) |
||
1287 | { |
||
1288 | switch($this->Type) |
||
1289 | { |
||
1290 | case 'mysql': |
||
1291 | return "DATE_FORMAT($expr,'$format')"; |
||
1292 | |||
1293 | case 'pgsql': |
||
1294 | $format = str_replace( |
||
1295 | array('%Y', '%y','%m','%d','%H', '%h','%i','%s','%V','%v','%X', '%x'), |
||
1296 | array('YYYY','YY','MM','DD','HH24','HH','MI','SS','IW','IW','YYYY','YYYY'), |
||
1297 | $format); |
||
1298 | return "TO_CHAR($expr,'$format')"; |
||
1299 | |||
1300 | case 'mssql': |
||
1301 | $from = $to = array(); |
||
1302 | foreach(array('%Y'=>'yyyy','%y'=>'yy','%m'=>'mm','%d'=>'dd','%H'=>'hh','%i'=>'mi','%s'=>'ss','%V'=>'wk','%v'=>'wk','%X'=>'yyyy','%x'=>'yyyy') as $f => $t) |
||
1303 | { |
||
1304 | $from[] = $f; |
||
1305 | $to[] = "'+DATEPART($t,($expr))+'"; |
||
1306 | } |
||
1307 | $from[] = "''+"; $to[] = ''; |
||
1308 | $from[] = "+''"; $to[] = ''; |
||
1309 | return str_replace($from,$to,$format); |
||
1310 | } |
||
1311 | return false; |
||
1312 | } |
||
1313 | |||
1314 | /** |
||
1315 | * Cast a column or sql expression to integer, necessary at least for postgreSQL or MySQL for sorting |
||
1316 | * |
||
1317 | * @param string $expr |
||
1318 | * @return string |
||
1319 | */ |
||
1320 | function to_double($expr) |
||
1321 | { |
||
1322 | switch($this->Type) |
||
1323 | { |
||
1324 | case 'pgsql': |
||
1325 | return $expr.'::double'; |
||
1326 | case 'mysql': |
||
1327 | return 'CAST('.$expr.' AS DECIMAL(24,3))'; |
||
1328 | } |
||
1329 | return $expr; |
||
1330 | } |
||
1331 | |||
1332 | /** |
||
1333 | * Cast a column or sql expression to integer, necessary at least for postgreSQL |
||
1334 | * |
||
1335 | * @param string $expr |
||
1336 | * @return string |
||
1337 | */ |
||
1338 | function to_int($expr) |
||
1339 | { |
||
1340 | switch($this->Type) |
||
1341 | { |
||
1342 | case 'pgsql': |
||
1343 | return $expr.'::integer'; |
||
1344 | case 'mysql': |
||
1345 | return 'CAST('.$expr.' AS SIGNED)'; |
||
1346 | } |
||
1347 | return $expr; |
||
1348 | } |
||
1349 | |||
1350 | /** |
||
1351 | * Cast a column or sql expression to varchar, necessary at least for postgreSQL |
||
1352 | * |
||
1353 | * @param string $expr |
||
1354 | * @return string |
||
1355 | */ |
||
1356 | function to_varchar($expr) |
||
1364 | } |
||
1365 | |||
1366 | /** |
||
1367 | * Correctly Quote Identifiers like table- or colmnnames for use in SQL-statements |
||
1368 | * |
||
1369 | * This is mostly copy & paste from adodb's datadict class |
||
1370 | * @param string $_name |
||
1371 | * @return string quoted string |
||
1372 | */ |
||
1373 | function name_quote($_name = NULL) |
||
1374 | { |
||
1375 | if (!is_string($_name)) |
||
1376 | { |
||
1377 | return false; |
||
1378 | } |
||
1379 | |||
1380 | $name = trim($_name); |
||
1381 | |||
1382 | if (!$this->Link_ID && !$this->connect()) |
||
1383 | { |
||
1384 | return false; |
||
1385 | } |
||
1386 | |||
1387 | $quote = $this->Link_ID->nameQuote; |
||
1388 | $type = $this->Type; |
||
1389 | |||
1390 | // if name is of the form `name`, remove MySQL quotes and leave it to automatic below |
||
1391 | if ($name[0] === '`' && substr($name, -1) === '`') |
||
1392 | { |
||
1393 | $name = substr($name, 1, -1); |
||
1394 | } |
||
1395 | |||
1396 | $quoted = array_map(function($name) use ($quote, $type) |
||
1397 | { |
||
1398 | // if name contains special characters, quote it |
||
1399 | // always quote for postgreSQL, as this is the only way to support mixed case names |
||
1400 | if (preg_match('/\W/', $name) || $type == 'pgsql' && preg_match('/[A-Z]+/', $name) || $name == 'index') |
||
1401 | { |
||
1402 | return $quote . $name . $quote; |
||
1403 | } |
||
1404 | return $name; |
||
1405 | }, explode('.', $name)); |
||
1406 | |||
1407 | return implode('.', $quoted); |
||
1408 | } |
||
1409 | |||
1410 | /** |
||
1411 | * Escape values before sending them to the database - prevents SQL injection and SQL errors ;-) |
||
1412 | * |
||
1413 | * Please note that the quote function already returns necessary quotes: quote('Hello') === "'Hello'". |
||
1414 | * Int and Auto types are casted to int: quote('1','int') === 1, quote('','int') === 0, quote('Hello','int') === 0 |
||
1415 | * Arrays of id's stored in strings: quote(array(1,2,3),'string') === "'1,2,3'" |
||
1416 | * |
||
1417 | * @param mixed $value the value to be escaped |
||
1418 | * @param string|boolean $type =false string the type of the db-column, default False === varchar |
||
1419 | * @param boolean $not_null =true is column NOT NULL, default true, else php null values are written as SQL NULL |
||
1420 | * @param int $length =null length of the varchar column, to truncate it if the database requires it (eg. Postgres) |
||
1421 | * @param string $glue =',' used to glue array values together for the string type |
||
1422 | * @return string escaped sting |
||
1423 | */ |
||
1424 | function quote($value,$type=False,$not_null=true,$length=null,$glue=',') |
||
1425 | { |
||
1426 | if ($this->Debug) echo "<p>db::quote(".(is_null($value)?'NULL':"'$value'").",'$type','$not_null')</p>\n"; |
||
1427 | |||
1428 | if (!$not_null && is_null($value)) // writing unset php-variables and those set to NULL now as SQL NULL |
||
1429 | { |
||
1430 | return 'NULL'; |
||
1431 | } |
||
1432 | switch($type) |
||
1433 | { |
||
1434 | case 'int': |
||
1435 | // if DateTime object given, convert it to a unix timestamp (NOT converting the timezone!) |
||
1436 | if (is_object($value) && ($value instanceof \DateTime)) |
||
1437 | { |
||
1438 | return ($value instanceof DateTime) ? $value->format('ts') : DateTime::to($value,'ts'); |
||
1439 | } |
||
1440 | case 'auto': |
||
1441 | // atm. (php5.2) php has only 32bit integers, it converts everything else to float. |
||
1442 | // Casting it to int gives a negative number instead of the big 64bit integer! |
||
1443 | // There for we have to keep it as float by using round instead the int cast. |
||
1444 | return is_float($value) ? round($value) : (int) $value; |
||
1445 | case 'bool': |
||
1446 | if ($this->Type == 'mysql') // maybe it's not longer necessary with mysql5 |
||
1447 | { |
||
1448 | return $value ? 1 : 0; |
||
1449 | } |
||
1450 | return $value ? 'true' : 'false'; |
||
1451 | case 'float': |
||
1452 | case 'decimal': |
||
1453 | return (double) $value; |
||
1454 | } |
||
1455 | if (!$this->Link_ID && !$this->connect()) |
||
1456 | { |
||
1457 | return False; |
||
1458 | } |
||
1459 | switch($type) |
||
1460 | { |
||
1461 | case 'blob': |
||
1462 | switch ($this->Link_ID->blobEncodeType) |
||
1463 | { |
||
1464 | case 'C': // eg. postgres |
||
1465 | return "'" . $this->Link_ID->BlobEncode($value) . "'"; |
||
1466 | case 'I': |
||
1467 | return $this->Link_ID->BlobEncode($value); |
||
1468 | } |
||
1469 | break; // handled like strings |
||
1470 | case 'date': |
||
1471 | // if DateTime object given, convert it (NOT converting the timezone!) |
||
1472 | if (is_object($value) && ($value instanceof \DateTime)) |
||
1473 | { |
||
1474 | return $this->Link_ID->qstr($value->format('Y-m-d')); |
||
1475 | } |
||
1476 | return $this->Link_ID->DBDate($value); |
||
1477 | case 'timestamp': |
||
1478 | // if DateTime object given, convert it (NOT converting the timezone!) |
||
1479 | if (is_object($value) && ($value instanceof \DateTime)) |
||
1480 | { |
||
1481 | return $this->Link_ID->qstr($value->format('Y-m-d H:i:s')); |
||
1482 | } |
||
1483 | return $this->Link_ID->DBTimeStamp($value); |
||
1484 | } |
||
1485 | if (is_array($value)) |
||
1486 | { |
||
1487 | $value = implode($glue,$value); |
||
1488 | } |
||
1489 | // truncate to long strings for varchar(X) columns as PostgreSQL and newer MySQL/MariaDB given an error otherwise |
||
1490 | if (!is_null($length) && mb_strlen($value) > $length) |
||
1491 | { |
||
1492 | $value = mb_substr($value, 0, $length); |
||
1493 | } |
||
1494 | // casting boolean explicitly to string, as ADODB_postgres64::qstr() has an unwanted special handling |
||
1495 | // for boolean types, causing it to return "true" or "false" and not a quoted string like "'1'"! |
||
1496 | if (is_bool($value)) $value = (string)$value; |
||
1497 | |||
1498 | // MySQL and MariaDB not 10.1 need 4-byte utf8 chars replaced with our default utf8 charset |
||
1499 | // (MariaDB 10.1 does the replacement automatic, 10.0 cuts everything off behind and MySQL gives an error) |
||
1500 | // (MariaDB 10.3 gives an error too: Incorrect string value: '\xF0\x9F\x98\x8A\x0AW...') |
||
1501 | // Changing charset to utf8mb4 requires schema update, shortening of some indexes and probably have negative impact on performace! |
||
1502 | if (substr($this->Type, 0, 5) == 'mysql') |
||
1503 | { |
||
1504 | $value = preg_replace('/[\x{10000}-\x{10FFFF}]/u', "\xEF\xBF\xBD", $value); |
||
1505 | } |
||
1506 | |||
1507 | // need to cast to string, as ADOdb 5.20 would return NULL instead of '' for NULL, causing us to write that into NOT NULL columns |
||
1508 | return $this->Link_ID->qstr((string)$value); |
||
1509 | } |
||
1510 | |||
1511 | /** |
||
1512 | * Implodes an array of column-value pairs for the use in sql-querys. |
||
1513 | * All data is run through quote (does either addslashes() or (int)) - prevents SQL injunction and SQL errors ;-). |
||
1514 | * |
||
1515 | * @author RalfBecker<at>outdoor-training.de |
||
1516 | * |
||
1517 | * @param string $glue in most cases this will be either ',' or ' AND ', depending you your query |
||
1518 | * @param array $array column-name / value pairs, if the value is an array all its array-values will be quoted |
||
1519 | * according to the type of the column, and the whole array with be formatted like (val1,val2,...) |
||
1520 | * If $use_key == True, an ' IN ' instead a '=' is used. Good for category- or user-lists. |
||
1521 | * If the key is numerical (no key given in the array-definition) the value is used as is, eg. |
||
1522 | * array('visits=visits+1') gives just "visits=visits+1" (no quoting at all !!!) |
||
1523 | * @param boolean|string $use_key If $use_key===True a "$key=" prefix each value (default), typically set to False |
||
1524 | * or 'VALUES' for insert querys, on 'VALUES' "(key1,key2,...) VALUES (val1,val2,...)" is returned |
||
1525 | * @param array|boolean $only if set to an array only colums which are set (as data !!!) are written |
||
1526 | * typicaly used to form a WHERE-clause from the primary keys. |
||
1527 | * If set to True, only columns from the colum_definitons are written. |
||
1528 | * @param array|boolean $column_definitions this can be set to the column-definitions-array |
||
1529 | * of your table ($tables_baseline[$table]['fd'] of the setup/tables_current.inc.php file). |
||
1530 | * If its set, the column-type-data determinates if (int) or addslashes is used. |
||
1531 | * @return string SQL |
||
1532 | */ |
||
1533 | function column_data_implode($glue,$array,$use_key=True,$only=False,$column_definitions=False) |
||
1534 | { |
||
1535 | if (!is_array($array)) // this allows to give an SQL-string for delete or update |
||
1536 | { |
||
1537 | return $array; |
||
1538 | } |
||
1539 | if (!$column_definitions) |
||
1540 | { |
||
1541 | $column_definitions = $this->column_definitions; |
||
1542 | } |
||
1543 | if ($this->Debug) echo "<p>db::column_data_implode('$glue',".print_r($array,True).",'$use_key',".print_r($only,True).",<pre>".print_r($column_definitions,True)."</pre>\n"; |
||
1544 | |||
1545 | // do we need to truncate varchars to their max length (INSERT and UPDATE on Postgres) |
||
1546 | $truncate_varchar = $glue == ',' && $this->capabilities[self::CAPABILITY_REQUIRE_TRUNCATE_VARCHAR]; |
||
1547 | |||
1548 | $keys = $values = array(); |
||
1549 | foreach($array as $key => $data) |
||
1550 | { |
||
1551 | if (is_int($key) && $use_key !== 'VALUES' || !$only || $only === True && isset($column_definitions[$key]) || |
||
1552 | is_array($only) && in_array($key,$only)) |
||
1553 | { |
||
1554 | $keys[] = $this->name_quote($key); |
||
1555 | |||
1556 | $col = $key; |
||
1557 | // fix "table.column" expressions, to not trigger exception, if column alone would work |
||
1558 | if (!is_int($key) && is_array($column_definitions) && !isset($column_definitions[$key])) |
||
1559 | { |
||
1560 | if (strpos($key, '.') !== false) list(, $col) = explode('.', $key); |
||
1561 | if (!isset($column_definitions[$col])) |
||
1562 | { |
||
1563 | throw new Db\Exception\InvalidSql("db::column_data_implode('$glue',".print_r($array,True).",'$use_key',".print_r($only,True).",<pre>".print_r($column_definitions,True)."</pre><b>nothing known about column '$key'!</b>"); |
||
1564 | } |
||
1565 | } |
||
1566 | $column_type = is_array($column_definitions) ? @$column_definitions[$col]['type'] : False; |
||
1567 | $not_null = is_array($column_definitions) && isset($column_definitions[$col]['nullable']) ? !$column_definitions[$col]['nullable'] : false; |
||
1568 | |||
1569 | $maxlength = null; |
||
1570 | if ($truncate_varchar) |
||
1571 | { |
||
1572 | $maxlength = in_array($column_definitions[$col]['type'], array('varchar','ascii')) ? $column_definitions[$col]['precision'] : null; |
||
1573 | } |
||
1574 | // dont use IN ( ), if there's only one value, it's slower for MySQL |
||
1575 | if (is_array($data) && count($data) <= 1) |
||
1576 | { |
||
1577 | $data = array_shift($data); |
||
1578 | } |
||
1579 | // array for SET or VALUES, not WHERE --> automatic store comma-separated |
||
1580 | if (is_array($data) && $glue === ',' && in_array($column_type, ['varchar','ascii'])) |
||
1581 | { |
||
1582 | $data = implode(',', $data); |
||
1583 | } |
||
1584 | if (is_array($data)) |
||
1585 | { |
||
1586 | $or_null = ''; |
||
1587 | foreach($data as $k => $v) |
||
1588 | { |
||
1589 | if (!$not_null && $use_key===True && is_null($v)) |
||
1590 | { |
||
1591 | $or_null = $this->name_quote($key).' IS NULL)'; |
||
1592 | unset($data[$k]); |
||
1593 | continue; |
||
1594 | } |
||
1595 | $data[$k] = $this->quote($v,$column_type,$not_null,$maxlength); |
||
1596 | } |
||
1597 | $values[] = ($or_null?'(':'').(!count($data) ? |
||
1598 | // empty array on insert/update, store as NULL, or if not allowed whatever value NULL is casted to |
||
1599 | $this->quote(null, $column_type, $not_null) : |
||
1600 | ($use_key===True ? $this->name_quote($key).' IN ' : '') . |
||
1601 | '('.implode(',',$data).')'.($or_null ? ' OR ' : '')).$or_null; |
||
1602 | } |
||
1603 | elseif (is_int($key) && $use_key===True) |
||
1604 | { |
||
1605 | if (empty($data)) continue; // would give SQL error |
||
1606 | $values[] = $data; |
||
1607 | } |
||
1608 | elseif ($glue != ',' && $use_key === True && !$not_null && is_null($data)) |
||
1609 | { |
||
1610 | $values[] = $this->name_quote($key) .' IS NULL'; |
||
1611 | } |
||
1612 | else |
||
1613 | { |
||
1614 | $values[] = ($use_key===True ? $this->name_quote($key) . '=' : '') . $this->quote($data,$column_type,$not_null,$maxlength); |
||
1615 | } |
||
1616 | } |
||
1617 | } |
||
1618 | return ($use_key==='VALUES' ? '('.implode(',',$keys).') VALUES (' : ''). |
||
1619 | implode($glue,$values) . ($use_key==='VALUES' ? ')' : ''); |
||
1620 | } |
||
1621 | |||
1622 | /** |
||
1623 | * Sets the default column-definitions for use with column_data_implode() |
||
1624 | * |
||
1625 | * @author RalfBecker<at>outdoor-training.de |
||
1626 | * |
||
1627 | * @param array|boolean $column_definitions this can be set to the column-definitions-array |
||
1628 | * of your table ($tables_baseline[$table]['fd'] of the setup/tables_current.inc.php file). |
||
1629 | * If its set, the column-type-data determinates if (int) or addslashes is used. |
||
1630 | */ |
||
1631 | function set_column_definitions($column_definitions=False) |
||
1632 | { |
||
1633 | $this->column_definitions=$column_definitions; |
||
1634 | } |
||
1635 | |||
1636 | /** |
||
1637 | * Application name used by the API |
||
1638 | * |
||
1639 | */ |
||
1640 | const API_APPNAME = 'api'; |
||
1641 | /** |
||
1642 | * Default app, if no app specified in select, insert, delete, ... |
||
1643 | * |
||
1644 | * @var string |
||
1645 | */ |
||
1646 | private $app=self::API_APPNAME; |
||
1647 | |||
1648 | /** |
||
1649 | * Sets the application in which the db-class looks for table-defintions |
||
1650 | * |
||
1651 | * Used by table_definitions, insert, update, select, expression and delete. If the app is not set via set_app, |
||
1652 | * it need to be set for these functions on every call |
||
1653 | * |
||
1654 | * @param string $app the app-name |
||
1655 | */ |
||
1656 | function set_app($app) |
||
1667 | } |
||
1668 | |||
1669 | /** |
||
1670 | * Data used by (get|set)_table_defintion and get_column_attribute |
||
1671 | * |
||
1672 | * @var array |
||
1673 | */ |
||
1674 | protected static $all_app_data = array(); |
||
1675 | |||
1676 | /** |
||
1677 | * Set/changes definition of one table |
||
1678 | * |
||
1679 | * If you set or change defition of a single table of an app, other tables |
||
1680 | * are not loaded from $app/setup/tables_current.inc.php! |
||
1681 | * |
||
1682 | * @param string $app name of the app $table belongs too |
||
1683 | * @param string $table table name |
||
1684 | * @param array $definition table definition |
||
1685 | */ |
||
1686 | public static function set_table_definitions($app, $table, array $definition) |
||
1687 | { |
||
1688 | self::$all_app_data[$app][$table] = $definition; |
||
1689 | } |
||
1690 | |||
1691 | /** |
||
1692 | * reads the table-definitions from the app's setup/tables_current.inc.php file |
||
1693 | * |
||
1694 | * The already read table-definitions are shared between all db-instances via a static var. |
||
1695 | * |
||
1696 | * @author RalfBecker<at>outdoor-training.de |
||
1697 | * |
||
1698 | * @param bool|string $app name of the app or default False to use the app set by db::set_app or the current app, |
||
1699 | * true to search the already loaded table-definitions for $table and then search all existing apps for it |
||
1700 | * @param bool|string $table if set return only defintions of that table, else return all defintions |
||
1701 | * @return mixed array with table-defintions or False if file not found |
||
1702 | */ |
||
1703 | function get_table_definitions($app=False,$table=False) |
||
1704 | { |
||
1705 | // ease the transition to api |
||
1706 | if ($app === 'phpgwapi') $app = 'api'; |
||
1707 | |||
1708 | if ($app === true && $table) |
||
1709 | { |
||
1710 | foreach(self::$all_app_data as $app => &$app_data) |
||
1711 | { |
||
1712 | if (isset($app_data[$table])) |
||
1713 | { |
||
1714 | return $app_data[$table]; |
||
1715 | } |
||
1716 | } |
||
1717 | // $table not found in loaded apps, check not yet loaded ones |
||
1718 | foreach(scandir(EGW_INCLUDE_ROOT) as $app) |
||
1719 | { |
||
1720 | if ($app[0] == '.' || !is_dir(EGW_INCLUDE_ROOT.'/'.$app) || isset(self::$all_app_data[$app])) |
||
1721 | { |
||
1722 | continue; |
||
1723 | } |
||
1724 | $tables_current = EGW_INCLUDE_ROOT . "/$app/setup/tables_current.inc.php"; |
||
1725 | if (!@file_exists($tables_current)) |
||
1726 | { |
||
1727 | self::$all_app_data[$app] = False; |
||
1728 | } |
||
1729 | else |
||
1730 | { |
||
1731 | $phpgw_baseline = null; |
||
1732 | include($tables_current); |
||
1733 | self::$all_app_data[$app] =& $phpgw_baseline; |
||
1734 | unset($phpgw_baseline); |
||
1735 | |||
1736 | if (isset(self::$all_app_data[$app][$table])) |
||
1737 | { |
||
1738 | return self::$all_app_data[$app][$table]; |
||
1739 | } |
||
1740 | } |
||
1741 | } |
||
1742 | $app = false; |
||
1743 | } |
||
1744 | if (!$app) |
||
1745 | { |
||
1746 | $app = $this->app ? $this->app : $GLOBALS['egw_info']['flags']['currentapp']; |
||
1747 | } |
||
1748 | $app_data =& self::$all_app_data[$app]; |
||
1749 | |||
1750 | if (!isset($app_data)) |
||
1751 | { |
||
1752 | $tables_current = EGW_INCLUDE_ROOT . "/$app/setup/tables_current.inc.php"; |
||
1753 | if (!@file_exists($tables_current)) |
||
1754 | { |
||
1755 | return $app_data = False; |
||
1756 | } |
||
1757 | include($tables_current); |
||
1758 | $app_data =& $phpgw_baseline; |
||
1759 | unset($phpgw_baseline); |
||
1760 | } |
||
1761 | if ($table && (!$app_data || !isset($app_data[$table]))) |
||
1762 | { |
||
1763 | if ($this->Debug) echo "<p>!!!get_table_definitions($app,$table) failed!!!</p>\n"; |
||
1764 | return False; |
||
1765 | } |
||
1766 | if ($this->Debug) echo "<p>get_table_definitions($app,$table) succeeded</p>\n"; |
||
1767 | return $table ? $app_data[$table] : $app_data; |
||
1768 | } |
||
1769 | |||
1770 | /** |
||
1771 | * Get specified attribute (default comment) of a colum or whole definition (if $attribute === null) |
||
1772 | * |
||
1773 | * Can be used static, in which case the global db object is used ($GLOBALS['egw']->db) and $app should be specified |
||
1774 | * |
||
1775 | * @param string $column name of column |
||
1776 | * @param string $table name of table |
||
1777 | * @param string $app=null app name or NULL to use $this->app, set via self::set_app() |
||
1778 | * @param string $attribute='comment' what field to return, NULL for array with all fields, default 'comment' to return the comment |
||
1779 | * @return string|array NULL if table or column or attribute not found |
||
1780 | */ |
||
1781 | /* static */ function get_column_attribute($column,$table,$app=null,$attribute='comment') |
||
1782 | { |
||
1783 | static $cached_columns=null,$cached_table=null; // some caching |
||
1784 | |||
1785 | if ($cached_table !== $table || is_null($cached_columns)) |
||
1786 | { |
||
1787 | $db = isset($this) && is_a($this, __CLASS__) ? $this : $GLOBALS['egw']->db; |
||
1788 | $table_def = $db->get_table_definitions($app,$table); |
||
1789 | $cached_columns = is_array($table_def) ? $table_def['fd'] : false; |
||
1790 | } |
||
1791 | if ($cached_columns === false) return null; |
||
1792 | |||
1793 | return is_null($attribute) ? $cached_columns[$column] : $cached_columns[$column][$attribute]; |
||
1794 | } |
||
1795 | |||
1796 | /** |
||
1797 | * Insert a row of data into a table or updates it if $where is given, all data is quoted according to it's type |
||
1798 | * |
||
1799 | * @author RalfBecker<at>outdoor-training.de |
||
1800 | * |
||
1801 | * @param string $table name of the table |
||
1802 | * @param array $data with column-name / value pairs |
||
1803 | * @param mixed $where string with where clause or array with column-name / values pairs to check if a row with that keys already exists, or false for an unconditional insert |
||
1804 | * if the row exists db::update is called else a new row with $date merged with $where gets inserted (data has precedence) |
||
1805 | * @param int $line line-number to pass to query |
||
1806 | * @param string $file file-name to pass to query |
||
1807 | * @param string|boolean $app string with name of app or False to use the current-app |
||
1808 | * @param bool $use_prepared_statement use a prepared statement |
||
1809 | * @param array|bool $table_def use this table definition. If False, the table definition will be read from tables_baseline |
||
1810 | * @return ADORecordSet or false, if the query fails |
||
1811 | */ |
||
1812 | function insert($table,$data,$where,$line,$file,$app=False,$use_prepared_statement=false,$table_def=False) |
||
1813 | { |
||
1814 | if ($this->Debug) echo "<p>db::insert('$table',".print_r($data,True).",".print_r($where,True).",$line,$file,'$app')</p>\n"; |
||
1815 | |||
1816 | if (!$table_def) $table_def = $this->get_table_definitions($app,$table); |
||
1817 | |||
1818 | $sql_append = ''; |
||
1819 | $cmd = 'INSERT'; |
||
1820 | if (is_array($where) && count($where)) |
||
1821 | { |
||
1822 | switch($this->Type) |
||
1823 | { |
||
1824 | case 'sapdb': case 'maxdb': |
||
1825 | $sql_append = ' UPDATE DUPLICATES'; |
||
1826 | break; |
||
1827 | case 'mysql': |
||
1828 | // use replace if primary keys are included |
||
1829 | if (count(array_intersect(array_keys($where),(array)$table_def['pk'])) == count($table_def['pk'])) |
||
1830 | { |
||
1831 | $cmd = 'REPLACE'; |
||
1832 | break; |
||
1833 | } |
||
1834 | // fall through !!! |
||
1835 | default: |
||
1836 | if ($this->select($table,'count(*)',$where,$line,$file)->fetchColumn()) |
||
1837 | { |
||
1838 | return !!$this->update($table,$data,$where,$line,$file,$app,$use_prepared_statement,$table_def); |
||
1839 | } |
||
1840 | break; |
||
1841 | } |
||
1842 | // the checked values need to be inserted too, value in data has precedence, also cant insert sql strings (numerical id) |
||
1843 | foreach($where as $column => $value) |
||
1844 | { |
||
1845 | if (!is_numeric($column) && !isset($data[$column]) && |
||
1846 | // skip auto-id of 0 or NULL, as PostgreSQL does NOT create an auto-id, if they are given |
||
1847 | !(!$value && count($table_def['pk']) == 1 && $column == $table_def['pk'][0])) |
||
1848 | { |
||
1849 | $data[$column] = $value; |
||
1850 | } |
||
1851 | } |
||
1852 | } |
||
1853 | if (self::$tablealiases && isset(self::$tablealiases[$table])) |
||
1854 | { |
||
1855 | $table = self::$tablealiases[$table]; |
||
1856 | } |
||
1857 | $inputarr = false; |
||
1858 | if (isset($data[0]) && is_array($data[0])) // multiple data rows |
||
1859 | { |
||
1860 | if ($where) throw new Exception\WrongParameter('Can NOT use $where together with multiple data rows in $data!'); |
||
1861 | |||
1862 | $sql = "$cmd INTO $table "; |
||
1863 | foreach($data as $k => $d) |
||
1864 | { |
||
1865 | if (!$k) |
||
1866 | { |
||
1867 | $sql .= $this->column_data_implode(',',$d,'VALUES',true,$table_def['fd']); |
||
1868 | } |
||
1869 | else |
||
1870 | { |
||
1871 | $sql .= ",\n(".$this->column_data_implode(',',$d,false,true,$table_def['fd']).')'; |
||
1872 | } |
||
1873 | } |
||
1874 | $sql .= $sql_append; |
||
1875 | } |
||
1876 | elseif ($use_prepared_statement && $this->Link_ID->_bindInputArray) // eg. MaxDB |
||
1877 | { |
||
1878 | $this->Link_ID->Param(false); // reset param-counter |
||
1879 | $cols = array_keys($data); |
||
1880 | foreach($cols as $k => $col) |
||
1881 | { |
||
1882 | if (!isset($table_def['fd'][$col])) // ignore columns not in this table |
||
1883 | { |
||
1884 | unset($cols[$k]); |
||
1885 | continue; |
||
1886 | } |
||
1887 | $params[] = $this->Link_ID->Param($col); |
||
1888 | } |
||
1889 | $sql = "$cmd INTO $table (".implode(',',$cols).') VALUES ('.implode(',',$params).')'.$sql_append; |
||
1890 | // check if we already prepared that statement |
||
1891 | if (!isset($this->prepared_sql[$sql])) |
||
1892 | { |
||
1893 | $this->prepared_sql[$sql] = $this->Link_ID->Prepare($sql); |
||
1894 | } |
||
1895 | $sql = $this->prepared_sql[$sql]; |
||
1896 | $inputarr = &$data; |
||
1897 | } |
||
1898 | else |
||
1899 | { |
||
1900 | $sql = "$cmd INTO $table ".$this->column_data_implode(',',$data,'VALUES',true,$table_def['fd']).$sql_append; |
||
1901 | } |
||
1902 | if ($this->Debug) echo "<p>db::insert('$table',".print_r($data,True).",".print_r($where,True).",$line,$file,'$app') sql='$sql'</p>\n"; |
||
1903 | return $this->query($sql,$line,$file,0,-1,$inputarr); |
||
1904 | } |
||
1905 | |||
1906 | /** |
||
1907 | * Updates the data of one or more rows in a table, all data is quoted according to it's type |
||
1908 | * |
||
1909 | * @author RalfBecker<at>outdoor-training.de |
||
1910 | * |
||
1911 | * @param string $table name of the table |
||
1912 | * @param array $data with column-name / value pairs |
||
1913 | * @param array $where column-name / values pairs and'ed together for the where clause |
||
1914 | * @param int $line line-number to pass to query |
||
1915 | * @param string $file file-name to pass to query |
||
1916 | * @param string|boolean $app string with name of app or False to use the current-app |
||
1917 | * @param bool $use_prepared_statement use a prepared statement |
||
1918 | * @param array|bool $table_def use this table definition. If False, the table definition will be read from tables_baseline |
||
1919 | * @return ADORecordSet or false, if the query fails |
||
1920 | */ |
||
1921 | function update($table,$data,$where,$line,$file,$app=False,$use_prepared_statement=false,$table_def=False) |
||
1922 | { |
||
1923 | if ($this->Debug) echo "<p>db::update('$table',".print_r($data,true).','.print_r($where,true).",$line,$file,'$app')</p>\n"; |
||
1924 | if (!$table_def) $table_def = $this->get_table_definitions($app,$table); |
||
1925 | |||
1926 | $blobs2update = array(); |
||
1927 | // SapDB/MaxDB cant update LONG columns / blob's: if a blob-column is included in the update we remember it in $blobs2update |
||
1928 | // and remove it from $data |
||
1929 | switch ($this->Type) |
||
1930 | { |
||
1931 | case 'sapdb': |
||
1932 | case 'maxdb': |
||
1933 | if ($use_prepared_statement) break; |
||
1934 | // check if data contains any LONG columns |
||
1935 | foreach($data as $col => $val) |
||
1936 | { |
||
1937 | switch ($table_def['fd'][$col]['type']) |
||
1938 | { |
||
1939 | case 'text': |
||
1940 | case 'longtext': |
||
1941 | case 'blob': |
||
1942 | $blobs2update[$col] = &$data[$col]; |
||
1943 | unset($data[$col]); |
||
1944 | break; |
||
1945 | } |
||
1946 | } |
||
1947 | break; |
||
1948 | } |
||
1949 | $where_str = $this->column_data_implode(' AND ',$where,True,true,$table_def['fd']); |
||
1950 | |||
1951 | if (self::$tablealiases && isset(self::$tablealiases[$table])) |
||
1952 | { |
||
1953 | $table = self::$tablealiases[$table]; |
||
1954 | } |
||
1955 | if (!empty($data)) |
||
1956 | { |
||
1957 | $inputarr = false; |
||
1958 | if ($use_prepared_statement && $this->Link_ID->_bindInputArray) // eg. MaxDB |
||
1959 | { |
||
1960 | $this->Link_ID->Param(false); // reset param-counter |
||
1961 | foreach($data as $col => $val) |
||
1962 | { |
||
1963 | if (!isset($table_def['fd'][$col])) continue; // ignore columns not in this table |
||
1964 | $params[] = $this->name_quote($col).'='.$this->Link_ID->Param($col); |
||
1965 | } |
||
1966 | $sql = "UPDATE $table SET ".implode(',',$params).' WHERE '.$where_str; |
||
1967 | // check if we already prepared that statement |
||
1968 | if (!isset($this->prepared_sql[$sql])) |
||
1969 | { |
||
1970 | $this->prepared_sql[$sql] = $this->Link_ID->Prepare($sql); |
||
1971 | } |
||
1972 | $sql = $this->prepared_sql[$sql]; |
||
1973 | $inputarr = &$data; |
||
1974 | } |
||
1975 | else |
||
1976 | { |
||
1977 | $sql = "UPDATE $table SET ". |
||
1978 | $this->column_data_implode(',',$data,True,true,$table_def['fd']).' WHERE '.$where_str; |
||
1979 | } |
||
1980 | $ret = $this->query($sql,$line,$file,0,-1,$inputarr); |
||
1981 | if ($this->Debug) echo "<p>db::query('$sql',$line,$file)</p>\n"; |
||
1982 | } |
||
1983 | // if we have any blobs to update, we do so now |
||
1984 | if (($ret || !count($data)) && count($blobs2update)) |
||
1985 | { |
||
1986 | foreach($blobs2update as $col => $val) |
||
1987 | { |
||
1988 | $ret = $this->Link_ID->UpdateBlob($table,$col,$val,$where_str,$table_def['fd'][$col]['type'] == 'blob' ? 'BLOB' : 'CLOB'); |
||
1989 | if ($this->Debug) echo "<p>adodb::UpdateBlob('$table','$col','$val','$where_str') = '$ret'</p>\n"; |
||
1990 | if (!$ret) throw new Db\Exception\InvalidSql("Error in UpdateBlob($table,$col,\$val,$where_str)",$line,$file); |
||
1991 | } |
||
1992 | } |
||
1993 | return $ret; |
||
1994 | } |
||
1995 | |||
1996 | /** |
||
1997 | * Deletes one or more rows in table, all data is quoted according to it's type |
||
1998 | * |
||
1999 | * @author RalfBecker<at>outdoor-training.de |
||
2000 | * |
||
2001 | * @param string $table name of the table |
||
2002 | * @param array $where column-name / values pairs and'ed together for the where clause |
||
2003 | * @param int $line line-number to pass to query |
||
2004 | * @param string $file file-name to pass to query |
||
2005 | * @param string|boolean $app string with name of app or False to use the current-app |
||
2006 | * @param array|bool $table_def use this table definition. If False, the table definition will be read from tables_baseline |
||
2007 | * @return ADORecordSet or false, if the query fails |
||
2008 | */ |
||
2009 | function delete($table,$where,$line,$file,$app=False,$table_def=False) |
||
2010 | { |
||
2011 | if (!$table_def) $table_def = $this->get_table_definitions($app,$table); |
||
2012 | |||
2013 | if (self::$tablealiases && isset(self::$tablealiases[$table])) |
||
2014 | { |
||
2015 | $table = self::$tablealiases[$table]; |
||
2016 | } |
||
2017 | $sql = "DELETE FROM $table WHERE ". |
||
2018 | $this->column_data_implode(' AND ',$where,True,False,$table_def['fd']); |
||
2019 | |||
2020 | return $this->query($sql,$line,$file); |
||
2021 | } |
||
2022 | |||
2023 | /** |
||
2024 | * Formats and quotes a sql expression to be used eg. as where-clause |
||
2025 | * |
||
2026 | * The function has a variable number of arguments, from which the expession gets constructed |
||
2027 | * eg. db::expression('my_table','(',array('name'=>"test'ed",'lang'=>'en'),') OR ',array('owner'=>array('',4,10))) |
||
2028 | * gives "(name='test\'ed' AND lang='en') OR 'owner' IN (0,4,5,6,10)" if name,lang are strings and owner is an integer |
||
2029 | * |
||
2030 | * @param string|array $table_def table-name or definition array |
||
2031 | * @param mixed $args variable number of arguments of the following types: |
||
2032 | * string: get's as is into the result |
||
2033 | * array: column-name / value pairs: the value gets quoted according to the type of the column and prefixed |
||
2034 | * with column-name=, multiple pairs are AND'ed together, see db::column_data_implode |
||
2035 | * bool: If False or is_null($arg): the next 2 (!) arguments gets ignored |
||
2036 | * |
||
2037 | * Please note: As the function has a variable number of arguments, you CAN NOT add further parameters !!! |
||
2038 | * |
||
2039 | * @return string the expression generated from the arguments |
||
2040 | */ |
||
2041 | function expression($table_def/*,$args, ...*/) |
||
2042 | { |
||
2043 | if (!is_array($table_def)) $table_def = $this->get_table_definitions(true,$table_def); |
||
2044 | $sql = ''; |
||
2045 | $ignore_next = 0; |
||
2046 | foreach(func_get_args() as $n => $arg) |
||
2047 | { |
||
2048 | if ($n < 1) continue; // table-name |
||
2049 | |||
2050 | if ($ignore_next) |
||
2051 | { |
||
2052 | --$ignore_next; |
||
2053 | continue; |
||
2054 | } |
||
2055 | if (is_null($arg)) $arg = False; |
||
2056 | |||
2057 | switch(gettype($arg)) |
||
2058 | { |
||
2059 | case 'string': |
||
2060 | $sql .= $arg; |
||
2061 | break; |
||
2062 | case 'boolean': |
||
2063 | $ignore_next += !$arg ? 2 : 0; |
||
2064 | break; |
||
2065 | case 'array': |
||
2066 | $sql .= $this->column_data_implode(' AND ',$arg,True,False,$table_def['fd']); |
||
2067 | break; |
||
2068 | } |
||
2069 | } |
||
2070 | return $sql; |
||
2071 | } |
||
2072 | |||
2073 | /** |
||
2074 | * Selects one or more rows in table depending on where, all data is quoted according to it's type |
||
2075 | * |
||
2076 | * @author RalfBecker<at>outdoor-training.de |
||
2077 | * |
||
2078 | * @param string $table name of the table |
||
2079 | * @param array|string $cols string or array of column-names / select-expressions |
||
2080 | * @param array|string $where string or array with column-name / values pairs AND'ed together for the where clause |
||
2081 | * @param int $line line-number to pass to query |
||
2082 | * @param string $file file-name to pass to query |
||
2083 | * @param int|bool $offset offset for a limited query or False (default) |
||
2084 | * @param string $append string to append to the end of the query, eg. ORDER BY ... |
||
2085 | * @param string|boolean $app string with name of app or False to use the current-app |
||
2086 | * @param int $num_rows number of rows to return if offset set, default 0 = use default in user prefs |
||
2087 | * @param string $join =null sql to do a join, added as is after the table-name, eg. ", table2 WHERE x=y" or |
||
2088 | * "LEFT JOIN table2 ON (x=y)", Note: there's no quoting done on $join! |
||
2089 | * @param array|bool $table_def use this table definition. If False, the table definition will be read from tables_baseline |
||
2090 | * @param int $fetchmode =self::FETCH_ASSOC self::FETCH_ASSOC (default), self::FETCH_BOTH or self::FETCH_NUM |
||
2091 | * @return ADORecordSet or false, if the query fails |
||
2092 | */ |
||
2093 | function select($table,$cols,$where,$line,$file,$offset=False,$append='',$app=False,$num_rows=0,$join='',$table_def=False,$fetchmode=self::FETCH_ASSOC) |
||
2094 | { |
||
2095 | if ($this->Debug) echo "<p>db::select('$table',".print_r($cols,True).",".print_r($where,True).",$line,$file,$offset,'$app',$num_rows,'$join')</p>\n"; |
||
2096 | |||
2097 | if (!$table_def) $table_def = $this->get_table_definitions($app,$table); |
||
2098 | if (is_array($cols)) |
||
2099 | { |
||
2100 | $cols = implode(',',$cols); |
||
2101 | } |
||
2102 | if (is_array($where)) |
||
2103 | { |
||
2104 | $where = $this->column_data_implode(' AND ',$where,True,False,$table_def['fd']); |
||
2105 | } |
||
2106 | if (self::$tablealiases && isset(self::$tablealiases[$table])) |
||
2107 | { |
||
2108 | $table = self::$tablealiases[$table]; |
||
2109 | } |
||
2110 | $sql = "SELECT $cols FROM $table $join"; |
||
2111 | |||
2112 | // if we have a where clause, we need to add it together with the WHERE statement, if thats not in the join |
||
2113 | if ($where) $sql .= (strpos($join,"WHERE")!==false) ? ' AND ('.$where.')' : ' WHERE '.$where; |
||
2114 | |||
2115 | if ($append) $sql .= ' '.$append; |
||
2116 | |||
2117 | if ($this->Debug) echo "<p>sql='$sql'</p>"; |
||
2118 | |||
2119 | if ($line === false && $file === false) // call by union, to return the sql rather then run the query |
||
2120 | { |
||
2121 | return $sql; |
||
2122 | } |
||
2123 | return $this->query($sql,$line,$file,$offset,$offset===False ? -1 : (int)$num_rows,false,$fetchmode); |
||
2124 | } |
||
2125 | |||
2126 | /** |
||
2127 | * Does a union over multiple selects |
||
2128 | * |
||
2129 | * @author RalfBecker<at>outdoor-training.de |
||
2130 | * |
||
2131 | * @param array $selects array of selects, each select is an array with the possible keys/parameters: table, cols, where, append, app, join, table_def |
||
2132 | * For further info about parameters see the definition of the select function, beside table, cols and where all other params are optional |
||
2133 | * @param int $line line-number to pass to query |
||
2134 | * @param string $file file-name to pass to query |
||
2135 | * @param string $order_by ORDER BY statement for the union |
||
2136 | * @param int|bool $offset offset for a limited query or False (default) |
||
2137 | * @param int $num_rows number of rows to return if offset set, default 0 = use default in user prefs |
||
2138 | * @param int $fetchmode =self::FETCH_ASSOC self::FETCH_ASSOC (default), self::FETCH_BOTH or self::FETCH_NUM |
||
2139 | * @return ADORecordSet or false, if the query fails |
||
2140 | */ |
||
2141 | function union($selects,$line,$file,$order_by='',$offset=false,$num_rows=0,$fetchmode=self::FETCH_ASSOC) |
||
2142 | { |
||
2143 | if ($this->Debug) echo "<p>db::union(".print_r($selects,True).",$line,$file,$order_by,$offset,$num_rows)</p>\n"; |
||
2144 | |||
2145 | $union = array(); |
||
2146 | foreach($selects as $select) |
||
2147 | { |
||
2148 | $union[] = call_user_func_array(array($this,'select'),array( |
||
2149 | $select['table'], |
||
2150 | $select['cols'], |
||
2151 | $select['where'], |
||
2152 | false, // line |
||
2153 | false, // file |
||
2154 | false, // offset |
||
2155 | $select['append'], |
||
2156 | $select['app'], |
||
2157 | 0, // num_rows, |
||
2158 | $select['join'], |
||
2159 | $select['table_def'], |
||
2160 | )); |
||
2161 | } |
||
2162 | $sql = count($union) > 1 ? '(' . implode(")\nUNION\n(",$union).')' : 'SELECT DISTINCT'.substr($union[0],6); |
||
2163 | |||
2164 | if ($order_by) $sql .= (!stristr($order_by,'ORDER BY') ? "\nORDER BY " : '').$order_by; |
||
2165 | |||
2166 | if ($this->Debug) echo "<p>sql='$sql'</p>"; |
||
2167 | |||
2168 | return $this->query($sql,$line,$file,$offset,$offset===False ? -1 : (int)$num_rows,false,$fetchmode); |
||
2169 | } |
||
2170 | |||
2171 | /** |
||
2172 | * Strip eg. a prefix from the keys of an array |
||
2173 | * |
||
2174 | * @param array $arr |
||
2175 | * @param string|array $strip |
||
2176 | * @return array |
||
2177 | */ |
||
2178 | static function strip_array_keys($arr,$strip) |
||
2188 | } |
||
2189 | } |
||
2190 |