| 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 |