3 * This is the IBM DB2 database abstraction layer.
4 * See maintenance/ibm_db2/README for development notes
5 * and other specific information
9 * @author leo.petr+mediawiki@gmail.com
13 * This represents a column in a DB2 database
16 class IBM_DB2Field
implements Field
{
18 private $tablename = '';
20 private $nullable = false;
21 private $max_length = 0;
24 * Builder method for the class
25 * @param $db DatabaseIbm_db2: Database interface
26 * @param $table String: table name
27 * @param $field String: column name
28 * @return IBM_DB2Field
30 static function fromText( $db, $table, $field ) {
35 lcase( coltype ) AS typname,
36 nulls AS attnotnull, length AS attlen
37 FROM sysibm.syscolumns
38 WHERE tbcreator=%s AND tbname=%s AND name=%s;
42 $db->addQuotes( $wgDBmwschema ),
43 $db->addQuotes( $table ),
44 $db->addQuotes( $field )
47 $row = $db->fetchObject( $res );
51 $n = new IBM_DB2Field
;
52 $n->type
= $row->typname
;
53 $n->nullable
= ( $row->attnotnull
== 'N' );
55 $n->tablename
= $table;
56 $n->max_length
= $row->attlen
;
61 * @return string column name
63 function name() { return $this->name
; }
66 * @return string table name
68 function tableName() { return $this->tablename
; }
71 * @return string column type
73 function type() { return $this->type
; }
76 * @return bool true or false
78 function isNullable() { return $this->nullable
; }
80 * How much can you fit in the column per row?
83 function maxLength() { return $this->max_length
; }
87 * Wrapper around binary large objects
93 public function __construct( $data ) {
97 public function getData() {
101 public function __toString() {
107 * Wrapper to address lack of certain operations in the DB2 driver
116 private $current_pos;
117 private $columns = array();
120 private $resultSet = array();
121 private $loadedLines = 0;
124 * Construct and initialize a wrapper for DB2 query results
125 * @param $db DatabaseBase
126 * @param $result Object
127 * @param $num_rows Integer
129 * @param $columns Array
131 public function __construct( $db, $result, $num_rows, $sql, $columns ){
134 if( $result instanceof ResultWrapper
){
135 $this->result
= $result->result
;
138 $this->result
= $result;
141 $this->num_rows
= $num_rows;
142 $this->current_pos
= 0;
143 if ( $this->num_rows
> 0 ) {
144 // Make a lower-case list of the column names
145 // By default, DB2 column names are capitalized
146 // while MySQL column names are lowercase
148 // Is there a reasonable maximum value for $i?
149 // Setting to 2048 to prevent an infinite loop
150 for( $i = 0; $i < 2048; $i++
) {
151 $name = db2_field_name( $this->result
, $i );
152 if ( $name != false ) {
159 $this->columns
[$i] = strtolower( $name );
167 * Unwrap the DB2 query results
168 * @return mixed Object on success, false on failure
170 public function getResult() {
171 if ( $this->result
) {
172 return $this->result
;
178 * Get the number of rows in the result set
181 public function getNum_rows() {
182 return $this->num_rows
;
186 * Return a row from the result set in object format
187 * @return mixed Object on success, false on failure.
189 public function fetchObject() {
191 && $this->num_rows
> 0
192 && $this->current_pos
>= 0
193 && $this->current_pos
< $this->num_rows
)
195 $row = $this->fetchRow();
196 $ret = new stdClass();
198 foreach ( $row as $k => $v ) {
199 $lc = $this->columns
[$k];
208 * Return a row form the result set in array format
209 * @return mixed Array on success, false on failure
210 * @throws DBUnexpectedError
212 public function fetchRow(){
214 && $this->num_rows
> 0
215 && $this->current_pos
>= 0
216 && $this->current_pos
< $this->num_rows
)
218 if ( $this->loadedLines
<= $this->current_pos
) {
219 $row = db2_fetch_array( $this->result
);
220 $this->resultSet
[$this->loadedLines++
] = $row;
221 if ( $this->db
->lastErrno() ) {
222 throw new DBUnexpectedError( $this->db
, 'Error in fetchRow(): '
223 . htmlspecialchars( $this->db
->lastError() ) );
227 if ( $this->loadedLines
> $this->current_pos
){
228 return $this->resultSet
[$this->current_pos++
];
236 * Free a DB2 result object
237 * @throws DBUnexpectedError
239 public function freeResult(){
240 unset( $this->resultSet
);
241 if ( !@db2_free_result
( $this->result
) ) {
242 throw new DBUnexpectedError( $this, "Unable to free DB2 result\n" );
248 * Primary database interface
251 class DatabaseIbm_db2
extends DatabaseBase
{
254 protected $mLastQuery = '';
255 protected $mPHPError = false;
257 protected $mServer, $mUser, $mPassword, $mConn = null, $mDBname;
258 protected $mOpened = false;
260 protected $mTablePrefix;
262 protected $mTrxLevel = 0;
263 protected $mErrorCount = 0;
264 protected $mLBInfo = array();
265 protected $mFakeSlaveLag = null, $mFakeMaster = false;
269 /** Database server port */
270 protected $mPort = null;
271 /** Schema for tables, stored procedures, triggers */
272 protected $mSchema = null;
273 /** Whether the schema has been applied in this session */
274 protected $mSchemaSet = false;
275 /** Result of last query */
276 protected $mLastResult = null;
277 /** Number of rows affected by last INSERT/UPDATE/DELETE */
278 protected $mAffectedRows = null;
279 /** Number of rows returned by last SELECT */
280 protected $mNumRows = null;
281 /** Current row number on the cursor of the last SELECT */
282 protected $currentRow = 0;
284 /** Connection config options - see constructor */
285 public $mConnOptions = array();
286 /** Statement config options -- see constructor */
287 public $mStmtOptions = array();
289 /** Default schema */
290 const USE_GLOBAL
= 'get from global';
292 /** Option that applies to nothing */
293 const NONE_OPTION
= 0x00;
294 /** Option that applies to connection objects */
295 const CONN_OPTION
= 0x01;
296 /** Option that applies to statement objects */
297 const STMT_OPTION
= 0x02;
299 /** Regular operation mode -- minimal debug messages */
300 const REGULAR_MODE
= 'regular';
301 /** Installation mode -- lots of debug messages */
302 const INSTALL_MODE
= 'install';
304 /** Controls the level of debug message output */
305 protected $mMode = self
::REGULAR_MODE
;
307 /** Last sequence value used for a primary key */
308 protected $mInsertId = null;
310 ######################################
311 # Getters and Setters
312 ######################################
315 * Returns true if this database supports (and uses) cascading deletes
318 function cascadingDeletes() {
323 * Returns true if this database supports (and uses) triggers (e.g. on the
327 function cleanupTriggers() {
332 * Returns true if this database is strict about what can be put into an
334 * Specifically, it uses a NULL value instead of an empty string.
337 function strictIPs() {
342 * Returns true if this database uses timestamps rather than integers
345 function realTimestamps() {
350 * Returns true if this database does an implicit sort when doing GROUP BY
353 function implicitGroupby() {
358 * Returns true if this database does an implicit order by when the column
360 * For example: SELECT page_title FROM page LIMIT 1
363 function implicitOrderby() {
368 * Returns true if this database can do a native search on IP columns
369 * e.g. this works as expected: .. WHERE rc_ip = '127.42.12.102/32';
372 function searchableIPs() {
377 * Returns true if this database can use functional indexes
380 function functionalIndexes() {
385 * Returns a unique string representing the wiki on the server
388 public function getWikiID() {
389 if( $this->mSchema
) {
390 return "{$this->mDBname}-{$this->mSchema}";
392 return $this->mDBname
;
397 * Returns the database software identifieir
400 public function getType() {
405 * Returns the database connection object
408 public function getDb(){
414 * @param $server String: hostname of database server
415 * @param $user String: username
416 * @param $password String: password
417 * @param $dbName String: database name on the server
418 * @param $flags Integer: database behaviour flags (optional, unused)
419 * @param $schema String
421 public function __construct( $server = false, $user = false,
423 $dbName = false, $flags = 0,
424 $schema = self
::USE_GLOBAL
)
426 global $wgDBmwschema;
428 if ( $schema == self
::USE_GLOBAL
) {
429 $this->mSchema
= $wgDBmwschema;
431 $this->mSchema
= $schema;
434 // configure the connection and statement objects
435 $this->setDB2Option( 'db2_attr_case', 'DB2_CASE_LOWER',
436 self
::CONN_OPTION | self
::STMT_OPTION
);
437 $this->setDB2Option( 'deferred_prepare', 'DB2_DEFERRED_PREPARE_ON',
439 $this->setDB2Option( 'rowcount', 'DB2_ROWCOUNT_PREFETCH_ON',
441 parent
::__construct( $server, $user, $password, $dbName, DBO_TRX |
$flags );
445 * Enables options only if the ibm_db2 extension version supports them
446 * @param $name String: name of the option in the options array
447 * @param $const String: name of the constant holding the right option value
448 * @param $type Integer: whether this is a Connection or Statement otion
450 private function setDB2Option( $name, $const, $type ) {
451 if ( defined( $const ) ) {
452 if ( $type & self
::CONN_OPTION
) {
453 $this->mConnOptions
[$name] = constant( $const );
455 if ( $type & self
::STMT_OPTION
) {
456 $this->mStmtOptions
[$name] = constant( $const );
460 "$const is not defined. ibm_db2 version is likely too low." );
465 * Outputs debug information in the appropriate place
466 * @param $string String: the relevant debug message
468 private function installPrint( $string ) {
469 wfDebug( "$string\n" );
470 if ( $this->mMode
== self
::INSTALL_MODE
) {
471 print "<li><pre>$string</pre></li>";
477 * Opens a database connection and returns it
478 * Closes any existing connection
480 * @param $server String: hostname
481 * @param $user String
482 * @param $password String
483 * @param $dbName String: database name
484 * @return DatabaseBase a fresh connection
486 public function open( $server, $user, $password, $dbName ) {
487 wfProfileIn( __METHOD__
);
489 # Load IBM DB2 driver if missing
492 # Test for IBM DB2 support, to avoid suppressed fatal error
493 if ( !function_exists( 'db2_connect' ) ) {
494 throw new DBConnectionError( $this, "DB2 functions missing, have you enabled the ibm_db2 extension for PHP?" );
499 // Close existing connection
502 $this->mServer
= $server;
503 $this->mPort
= $port = $wgDBport;
504 $this->mUser
= $user;
505 $this->mPassword
= $password;
506 $this->mDBname
= $dbName;
508 $this->openUncataloged( $dbName, $user, $password, $server, $port );
510 if ( !$this->mConn
) {
511 $this->installPrint( "DB connection error\n" );
513 "Server: $server, Database: $dbName, User: $user, Password: "
514 . substr( $password, 0, 3 ) . "...\n" );
515 $this->installPrint( $this->lastError() . "\n" );
516 wfProfileOut( __METHOD__
);
517 wfDebug( "DB connection error\n" );
518 wfDebug( "Server: $server, Database: $dbName, User: $user, Password: " . substr( $password, 0, 3 ) . "...\n" );
519 wfDebug( $this->lastError() . "\n" );
520 throw new DBConnectionError( $this, $this->lastError() );
523 // Some MediaWiki code is still transaction-less (?).
524 // The strategy is to keep AutoCommit on for that code
525 // but switch it off whenever a transaction is begun.
526 db2_autocommit( $this->mConn
, DB2_AUTOCOMMIT_ON
);
528 $this->mOpened
= true;
529 $this->applySchema();
531 wfProfileOut( __METHOD__
);
536 * Opens a cataloged database connection, sets mConn
538 protected function openCataloged( $dbName, $user, $password ) {
539 wfSuppressWarnings();
540 $this->mConn
= db2_pconnect( $dbName, $user, $password );
545 * Opens an uncataloged database connection, sets mConn
547 protected function openUncataloged( $dbName, $user, $password, $server, $port )
549 $dsn = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$dbName;CHARSET=UTF-8;HOSTNAME=$server;PORT=$port;PROTOCOL=TCPIP;UID=$user;PWD=$password;";
550 wfSuppressWarnings();
551 $this->mConn
= db2_pconnect( $dsn, "", "", array() );
556 * Closes a database connection, if it is open
557 * Returns success, true if already closed
560 public function close() {
561 $this->mOpened
= false;
562 if ( $this->mConn
) {
563 if ( $this->trxLevel() > 0 ) {
566 return db2_close( $this->mConn
);
573 * Retrieves the most current database error
574 * Forces a database rollback
575 * @return bool|string
577 public function lastError() {
578 $connerr = db2_conn_errormsg();
583 $stmterr = db2_stmt_errormsg();
593 * Get the last error number
594 * Return 0 if no error
597 public function lastErrno() {
598 $connerr = db2_conn_error();
602 $stmterr = db2_stmt_error();
610 * Is a database connection open?
613 public function isOpen() { return $this->mOpened
; }
616 * The DBMS-dependent part of query()
617 * @param $sql String: SQL query.
618 * @return object Result object for fetch functions or false on failure
620 protected function doQuery( $sql ) {
621 $this->applySchema();
623 // Needed to handle any UTF-8 encoding issues in the raw sql
624 // Note that we fully support prepared statements for DB2
625 // prepare() and execute() should be used instead of doQuery() whenever possible
626 $sql = utf8_decode( $sql );
628 $ret = db2_exec( $this->mConn
, $sql, $this->mStmtOptions
);
629 if( $ret == false ) {
630 $error = db2_stmt_errormsg();
632 $this->installPrint( "<pre>$sql</pre>" );
633 $this->installPrint( $error );
634 throw new DBUnexpectedError( $this, 'SQL error: '
635 . htmlspecialchars( $error ) );
637 $this->mLastResult
= $ret;
638 $this->mAffectedRows
= null; // Not calculated until asked for
643 * @return string Version information from the database
645 public function getServerVersion() {
646 $info = db2_server_info( $this->mConn
);
647 return $info->DBMS_VER
;
651 * Queries whether a given table exists
654 public function tableExists( $table, $fname = __METHOD__
) {
655 $schema = $this->mSchema
;
657 $sql = "SELECT COUNT( * ) FROM SYSIBM.SYSTABLES ST WHERE ST.NAME = '" .
658 strtoupper( $table ) .
659 "' AND ST.CREATOR = '" .
660 strtoupper( $schema ) . "'";
661 $res = $this->query( $sql );
666 // If the table exists, there should be one of it
667 $row = $this->fetchRow( $res );
669 if ( $count == '1' ||
$count == 1 ) {
677 * Fetch the next row from the given result object, in object form.
678 * Fields can be retrieved with $row->fieldname, with fields acting like
681 * @param $res array|ResultWrapper SQL result object as returned from Database::query(), etc.
682 * @return DB2 row object
683 * @throws DBUnexpectedError Thrown if the database returns an error
685 public function fetchObject( $res ) {
686 if ( $res instanceof ResultWrapper
) {
689 wfSuppressWarnings();
690 $row = db2_fetch_object( $res );
692 if( $this->lastErrno() ) {
693 throw new DBUnexpectedError( $this, 'Error in fetchObject(): '
694 . htmlspecialchars( $this->lastError() ) );
700 * Fetch the next row from the given result object, in associative array
701 * form. Fields are retrieved with $row['fieldname'].
703 * @param $res array|ResultWrapper SQL result object as returned from Database::query(), etc.
704 * @return ResultWrapper row object
705 * @throws DBUnexpectedError Thrown if the database returns an error
707 public function fetchRow( $res ) {
708 if ( $res instanceof ResultWrapper
) {
711 if ( db2_num_rows( $res ) > 0) {
712 wfSuppressWarnings();
713 $row = db2_fetch_array( $res );
715 if ( $this->lastErrno() ) {
716 throw new DBUnexpectedError( $this, 'Error in fetchRow(): '
717 . htmlspecialchars( $this->lastError() ) );
726 * Doesn't escape numbers
728 * @param $s String: string to escape
729 * @return string escaped string
731 public function addQuotes( $s ) {
732 //$this->installPrint( "DB2::addQuotes( $s )\n" );
733 if ( is_null( $s ) ) {
735 } elseif ( $s instanceof Blob
) {
736 return "'" . $s->fetch( $s ) . "'";
737 } elseif ( $s instanceof IBM_DB2Blob
) {
738 return "'" . $this->decodeBlob( $s ) . "'";
740 $s = $this->strencode( $s );
741 if ( is_numeric( $s ) ) {
749 * Verifies that a DB2 column/field type is numeric
751 * @param $type String: DB2 column type
752 * @return Boolean: true if numeric
754 public function is_numeric_type( $type ) {
755 switch ( strtoupper( $type ) ) {
770 * Alias for addQuotes()
771 * @param $s String: string to escape
772 * @return string escaped string
774 public function strencode( $s ) {
775 // Bloody useless function
776 // Prepends backslashes to \x00, \n, \r, \, ', " and \x1a.
777 // But also necessary
778 $s = db2_escape_string( $s );
779 // Wide characters are evil -- some of them look like '
780 $s = utf8_encode( $s );
782 $from = array( "\\\\", "\\'", '\\n', '\\t', '\\"', '\\r' );
783 $to = array( "\\", "''", "\n", "\t", '"', "\r" );
784 $s = str_replace( $from, $to, $s ); // DB2 expects '', not \' escaping
789 * Switch into the database schema
791 protected function applySchema() {
792 if ( !( $this->mSchemaSet
) ) {
793 $this->mSchemaSet
= true;
795 $this->doQuery( "SET SCHEMA = $this->mSchema" );
801 * Start a transaction (mandatory)
803 public function begin( $fname = 'DatabaseIbm_db2::begin' ) {
804 // BEGIN is implicit for DB2
805 // However, it requires that AutoCommit be off.
807 // Some MediaWiki code is still transaction-less (?).
808 // The strategy is to keep AutoCommit on for that code
809 // but switch it off whenever a transaction is begun.
810 db2_autocommit( $this->mConn
, DB2_AUTOCOMMIT_OFF
);
812 $this->mTrxLevel
= 1;
817 * Must have a preceding begin()
819 public function commit( $fname = 'DatabaseIbm_db2::commit' ) {
820 db2_commit( $this->mConn
);
822 // Some MediaWiki code is still transaction-less (?).
823 // The strategy is to keep AutoCommit on for that code
824 // but switch it off whenever a transaction is begun.
825 db2_autocommit( $this->mConn
, DB2_AUTOCOMMIT_ON
);
827 $this->mTrxLevel
= 0;
831 * Cancel a transaction
833 public function rollback( $fname = 'DatabaseIbm_db2::rollback' ) {
834 db2_rollback( $this->mConn
);
835 // turn auto-commit back on
836 // not sure if this is appropriate
837 db2_autocommit( $this->mConn
, DB2_AUTOCOMMIT_ON
);
838 $this->mTrxLevel
= 0;
842 * Makes an encoded list of strings from an array
844 * LIST_COMMA - comma separated, no field names
845 * LIST_AND - ANDed WHERE clause (without the WHERE)
846 * LIST_OR - ORed WHERE clause (without the WHERE)
847 * LIST_SET - comma separated with field names, like a SET clause
848 * LIST_NAMES - comma separated field names
849 * LIST_SET_PREPARED - like LIST_SET, except with ? tokens as values
852 function makeList( $a, $mode = LIST_COMMA
) {
853 if ( !is_array( $a ) ) {
854 throw new DBUnexpectedError( $this,
855 'DatabaseIbm_db2::makeList called with incorrect parameters' );
858 // if this is for a prepared UPDATE statement
859 // (this should be promoted to the parent class
860 // once other databases use prepared statements)
861 if ( $mode == LIST_SET_PREPARED
) {
864 foreach ( $a as $field => $value ) {
866 $list .= ", $field = ?";
868 $list .= "$field = ?";
877 // otherwise, call the usual function
878 return parent
::makeList( $a, $mode );
882 * Construct a LIMIT query with optional offset
883 * This is used for query pages
885 * @param $sql string SQL query we will append the limit too
886 * @param $limit integer the SQL limit
887 * @param $offset integer the SQL offset (default false)
890 public function limitResult( $sql, $limit, $offset=false ) {
891 if( !is_numeric( $limit ) ) {
892 throw new DBUnexpectedError( $this,
893 "Invalid non-numeric limit passed to limitResult()\n" );
896 if ( stripos( $sql, 'where' ) === false ) {
897 return "$sql AND ( ROWNUM BETWEEN $offset AND $offset+$limit )";
899 return "$sql WHERE ( ROWNUM BETWEEN $offset AND $offset+$limit )";
902 return "$sql FETCH FIRST $limit ROWS ONLY ";
906 * Handle reserved keyword replacement in table names
908 * @param $name Object
909 * @param $format String Ignored parameter Default 'quoted'Boolean
912 public function tableName( $name, $format = 'quoted' ) {
913 // we want maximum compatibility with MySQL schema
918 * Generates a timestamp in an insertable format
920 * @param $ts string timestamp
921 * @return String: timestamp value
923 public function timestamp( $ts = 0 ) {
924 // TS_MW cannot be easily distinguished from an integer
925 return wfTimestamp( TS_DB2
, $ts );
929 * Return the next in a sequence, save the value for retrieval via insertId()
930 * @param $seqName String: name of a defined sequence in the database
931 * @return int next value in that sequence
933 public function nextSequenceValue( $seqName ) {
934 // Not using sequences in the primary schema to allow for easier migration
936 // Emulating MySQL behaviour of using NULL to signal that sequences
939 $safeseq = preg_replace( "/'/", "''", $seqName );
940 $res = $this->query( "VALUES NEXTVAL FOR $safeseq" );
941 $row = $this->fetchRow( $res );
942 $this->mInsertId = $row[0];
943 return $this->mInsertId;
949 * This must be called after nextSequenceVal
950 * @return int Last sequence value used as a primary key
952 public function insertId() {
953 return $this->mInsertId
;
957 * Updates the mInsertId property with the value of the last insert
958 * into a generated column
960 * @param $table String: sanitized table name
961 * @param $primaryKey Mixed: string name of the primary key
962 * @param $stmt Resource: prepared statement resource
963 * of the SELECT primary_key FROM FINAL TABLE ( INSERT ... ) form
965 private function calcInsertId( $table, $primaryKey, $stmt ) {
967 $this->mInsertId
= db2_last_insert_id( $this->mConn
);
972 * INSERT wrapper, inserts an array into a table
974 * $args may be a single associative array, or an array of arrays
975 * with numeric keys, for multi-row insert
977 * @param $table String: Name of the table to insert to.
978 * @param $args Array: Items to insert into the table.
979 * @param $fname String: Name of the function, for profiling
980 * @param $options String or Array. Valid options: IGNORE
982 * @return bool Success of insert operation. IGNORE always returns true.
984 public function insert( $table, $args, $fname = 'DatabaseIbm_db2::insert',
987 if ( !count( $args ) ) {
990 // get database-specific table name (not used)
991 $table = $this->tableName( $table );
992 // format options as an array
993 $options = IBM_DB2Helper
::makeArray( $options );
994 // format args as an array of arrays
995 if ( !( isset( $args[0] ) && is_array( $args[0] ) ) ) {
996 $args = array( $args );
999 // prevent insertion of NULL into primary key columns
1000 list( $args, $primaryKeys ) = $this->removeNullPrimaryKeys( $table, $args );
1001 // if there's only one primary key
1002 // we'll be able to read its value after insertion
1003 $primaryKey = false;
1004 if ( count( $primaryKeys ) == 1 ) {
1005 $primaryKey = $primaryKeys[0];
1009 $keys = array_keys( $args[0] );
1010 $key_count = count( $keys );
1012 // If IGNORE is set, we use savepoints to emulate mysql's behavior
1013 $ignore = in_array( 'IGNORE', $options ) ?
'mw' : '';
1017 // If we are not in a transaction, we need to be for savepoint trickery
1018 if ( !$this->mTrxLevel
) {
1022 $sql = "INSERT INTO $table ( " . implode( ',', $keys ) . ' ) VALUES ';
1023 if ( $key_count == 1 ) {
1026 $sql .= '( ?' . str_repeat( ',?', $key_count-1 ) . ' )';
1028 $this->installPrint( "Preparing the following SQL:" );
1029 $this->installPrint( "$sql" );
1030 $this->installPrint( print_r( $args, true ));
1031 $stmt = $this->prepare( $sql );
1033 // start a transaction/enter transaction mode
1038 foreach ( $args as $row ) {
1039 //$this->installPrint( "Inserting " . print_r( $row, true ));
1040 // insert each row into the database
1041 $res = $res & $this->execute( $stmt, $row );
1043 $this->installPrint( 'Last error:' );
1044 $this->installPrint( $this->lastError() );
1046 // get the last inserted value into a generated column
1047 $this->calcInsertId( $table, $primaryKey, $stmt );
1050 $olde = error_reporting( 0 );
1051 // For future use, we may want to track the number of actual inserts
1052 // Right now, insert (all writes) simply return true/false
1053 $numrowsinserted = 0;
1055 // always return true
1058 foreach ( $args as $row ) {
1059 $overhead = "SAVEPOINT $ignore ON ROLLBACK RETAIN CURSORS";
1060 db2_exec( $this->mConn
, $overhead, $this->mStmtOptions
);
1062 $res2 = $this->execute( $stmt, $row );
1065 $this->installPrint( 'Last error:' );
1066 $this->installPrint( $this->lastError() );
1068 // get the last inserted value into a generated column
1069 $this->calcInsertId( $table, $primaryKey, $stmt );
1071 $errNum = $this->lastErrno();
1073 db2_exec( $this->mConn
, "ROLLBACK TO SAVEPOINT $ignore",
1074 $this->mStmtOptions
);
1076 db2_exec( $this->mConn
, "RELEASE SAVEPOINT $ignore",
1077 $this->mStmtOptions
);
1082 $olde = error_reporting( $olde );
1083 // Set the affected row count for the whole operation
1084 $this->mAffectedRows
= $numrowsinserted;
1086 // commit either way
1088 $this->freePrepared( $stmt );
1094 * Given a table name and a hash of columns with values
1095 * Removes primary key columns from the hash where the value is NULL
1097 * @param $table String: name of the table
1098 * @param $args Array of hashes of column names with values
1099 * @return Array: tuple( filtered array of columns, array of primary keys )
1101 private function removeNullPrimaryKeys( $table, $args ) {
1102 $schema = $this->mSchema
;
1104 // find out the primary keys
1105 $keyres = $this->doQuery( "SELECT NAME FROM SYSIBM.SYSCOLUMNS WHERE TBNAME = '"
1106 . strtoupper( $table )
1107 . "' AND TBCREATOR = '"
1108 . strtoupper( $schema )
1109 . "' AND KEYSEQ > 0" );
1113 $row = $this->fetchRow( $keyres );
1115 $row = $this->fetchRow( $keyres )
1118 $keys[] = strtolower( $row[0] );
1120 // remove primary keys
1121 foreach ( $args as $ai => $row ) {
1122 foreach ( $keys as $key ) {
1123 if ( $row[$key] == null ) {
1124 unset( $row[$key] );
1129 // return modified hash
1130 return array( $args, $keys );
1134 * UPDATE wrapper, takes a condition array and a SET array
1136 * @param $table String: The table to UPDATE
1137 * @param $values array An array of values to SET
1138 * @param $conds array An array of conditions ( WHERE ). Use '*' to update all rows.
1139 * @param $fname String: The Class::Function calling this function
1141 * @param $options array An array of UPDATE options, can be one or
1142 * more of IGNORE, LOW_PRIORITY
1145 public function update( $table, $values, $conds, $fname = 'DatabaseIbm_db2::update',
1146 $options = array() )
1148 $table = $this->tableName( $table );
1149 $opts = $this->makeUpdateOptions( $options );
1150 $sql = "UPDATE $opts $table SET "
1151 . $this->makeList( $values, LIST_SET_PREPARED
);
1152 if ( $conds != '*' ) {
1153 $sql .= " WHERE " . $this->makeList( $conds, LIST_AND
);
1155 $stmt = $this->prepare( $sql );
1156 $this->installPrint( 'UPDATE: ' . print_r( $values, true ) );
1157 // assuming for now that an array with string keys will work
1158 // if not, convert to simple array first
1159 $result = $this->execute( $stmt, $values );
1160 $this->freePrepared( $stmt );
1166 * DELETE query wrapper
1168 * Use $conds == "*" to delete all rows
1169 * @return bool|\ResultWrapper
1171 public function delete( $table, $conds, $fname = 'DatabaseIbm_db2::delete' ) {
1173 throw new DBUnexpectedError( $this,
1174 'DatabaseIbm_db2::delete() called with no conditions' );
1176 $table = $this->tableName( $table );
1177 $sql = "DELETE FROM $table";
1178 if ( $conds != '*' ) {
1179 $sql .= ' WHERE ' . $this->makeList( $conds, LIST_AND
);
1181 $result = $this->query( $sql, $fname );
1187 * Returns the number of rows affected by the last query or 0
1188 * @return Integer: the number of rows affected by the last query
1190 public function affectedRows() {
1191 if ( !is_null( $this->mAffectedRows
) ) {
1192 // Forced result for simulated queries
1193 return $this->mAffectedRows
;
1195 if( empty( $this->mLastResult
) ) {
1198 return db2_num_rows( $this->mLastResult
);
1202 * Returns the number of rows in the result set
1203 * Has to be called right after the corresponding select query
1204 * @param $res Object result set
1205 * @return Integer: number of rows
1207 public function numRows( $res ) {
1208 if ( $res instanceof ResultWrapper
) {
1209 $res = $res->result
;
1212 if ( $this->mNumRows
) {
1213 return $this->mNumRows
;
1220 * Moves the row pointer of the result set
1221 * @param $res Object: result set
1222 * @param $row Integer: row number
1223 * @return bool success or failure
1225 public function dataSeek( $res, $row ) {
1226 if ( $res instanceof ResultWrapper
) {
1227 return $res = $res->result
;
1229 if ( $res instanceof IBM_DB2Result
) {
1230 return $res->dataSeek( $row );
1232 wfDebug( "dataSeek operation in DB2 database\n" );
1237 # Fix notices in Block.php
1241 * Frees memory associated with a statement resource
1242 * @param $res Object: statement resource to free
1243 * @return Boolean success or failure
1245 public function freeResult( $res ) {
1246 if ( $res instanceof ResultWrapper
) {
1247 $res = $res->result
;
1249 wfSuppressWarnings();
1250 $ok = db2_free_result( $res );
1251 wfRestoreWarnings();
1253 throw new DBUnexpectedError( $this, "Unable to free DB2 result\n" );
1258 * Returns the number of columns in a resource
1259 * @param $res Object: statement resource
1260 * @return Number of fields/columns in resource
1262 public function numFields( $res ) {
1263 if ( $res instanceof ResultWrapper
) {
1264 $res = $res->result
;
1266 if ( $res instanceof IBM_DB2Result
) {
1267 $res = $res->getResult();
1269 return db2_num_fields( $res );
1273 * Returns the nth column name
1274 * @param $res Object: statement resource
1275 * @param $n Integer: Index of field or column
1276 * @return String name of nth column
1278 public function fieldName( $res, $n ) {
1279 if ( $res instanceof ResultWrapper
) {
1280 $res = $res->result
;
1282 if ( $res instanceof IBM_DB2Result
) {
1283 $res = $res->getResult();
1285 return db2_field_name( $res, $n );
1291 * @param $table Array or string, table name(s) (prefix auto-added)
1292 * @param $vars Array or string, field name(s) to be retrieved
1293 * @param $conds Array or string, condition(s) for WHERE
1294 * @param $fname String: calling function name (use __METHOD__)
1295 * for logs/profiling
1296 * @param $options array Associative array of options
1297 * (e.g. array( 'GROUP BY' => 'page_title' )),
1298 * see Database::makeSelectOptions code for list of
1300 * @param $join_conds array Associative array of table join conditions (optional)
1301 * (e.g. array( 'page' => array('LEFT JOIN',
1302 * 'page_latest=rev_id') )
1303 * @return Mixed: database result resource for fetch functions or false
1306 public function select( $table, $vars, $conds = '', $fname = 'DatabaseIbm_db2::select', $options = array(), $join_conds = array() )
1308 $res = parent
::select( $table, $vars, $conds, $fname, $options,
1310 $sql = $this->selectSQLText( $table, $vars, $conds, $fname, $options, $join_conds );
1312 // We must adjust for offset
1313 if ( isset( $options['LIMIT'] ) && isset ( $options['OFFSET'] ) ) {
1314 $limit = $options['LIMIT'];
1315 $offset = $options['OFFSET'];
1318 // DB2 does not have a proper num_rows() function yet, so we must emulate
1319 // DB2 9.5.4 and the corresponding ibm_db2 driver will introduce
1323 // we want the count
1324 $vars2 = array( 'count( * ) as num_rows' );
1325 // respecting just the limit option
1326 $options2 = array();
1327 if ( isset( $options['LIMIT'] ) ) {
1328 $options2['LIMIT'] = $options['LIMIT'];
1330 // but don't try to emulate for GROUP BY
1331 if ( isset( $options['GROUP BY'] ) ) {
1335 $res2 = parent
::select( $table, $vars2, $conds, $fname, $options2,
1338 $obj = $this->fetchObject( $res2 );
1339 $this->mNumRows
= $obj->num_rows
;
1341 return new ResultWrapper( $this, new IBM_DB2Result( $this, $res, $obj->num_rows
, $vars, $sql ) );
1345 * Handles ordering, grouping, and having options ('GROUP BY' => colname)
1346 * Has limited support for per-column options (colnum => 'DISTINCT')
1350 * @param $options array Associative array of options to be turned into
1351 * an SQL query, valid keys are listed in the function.
1354 function makeSelectOptions( $options ) {
1355 $preLimitTail = $postLimitTail = '';
1358 $noKeyOptions = array();
1359 foreach ( $options as $key => $option ) {
1360 if ( is_numeric( $key ) ) {
1361 $noKeyOptions[$option] = true;
1365 if ( isset( $options['GROUP BY'] ) ) {
1366 $preLimitTail .= " GROUP BY {$options['GROUP BY']}";
1368 if ( isset( $options['HAVING'] ) ) {
1369 $preLimitTail .= " HAVING {$options['HAVING']}";
1371 if ( isset( $options['ORDER BY'] ) ) {
1372 $preLimitTail .= " ORDER BY {$options['ORDER BY']}";
1375 if ( isset( $noKeyOptions['DISTINCT'] )
1376 ||
isset( $noKeyOptions['DISTINCTROW'] ) )
1378 $startOpts .= 'DISTINCT';
1381 return array( $startOpts, '', $preLimitTail, $postLimitTail );
1385 * Returns link to IBM DB2 free download
1386 * @return String: wikitext of a link to the server software's web site
1388 public static function getSoftwareLink() {
1389 return '[http://www.ibm.com/db2/express/ IBM DB2]';
1393 * Get search engine class. All subclasses of this
1394 * need to implement this if they wish to use searching.
1398 public function getSearchEngine() {
1399 return 'SearchIBM_DB2';
1403 * Did the last database access fail because of deadlock?
1406 public function wasDeadlock() {
1408 $err = $this->lastErrno();
1410 // This is literal port of the MySQL logic and may be wrong for DB2
1411 case '40001': // sql0911n, Deadlock or timeout, rollback
1412 case '57011': // sql0904n, Resource unavailable, no rollback
1413 case '57033': // sql0913n, Deadlock or timeout, no rollback
1414 $this->installPrint( "In a deadlock because of SQLSTATE $err" );
1421 * Ping the server and try to reconnect if it there is no connection
1422 * The connection may be closed and reopened while this happens
1423 * @return Boolean: whether the connection exists
1425 public function ping() {
1426 // db2_ping() doesn't exist
1429 $this->mConn
= $this->openUncataloged( $this->mDBName
, $this->mUser
,
1430 $this->mPassword
, $this->mServer
, $this->mPort
);
1434 ######################################
1435 # Unimplemented and not applicable
1436 ######################################
1439 * @return string $sql
1441 public function limitResultForUpdate( $sql, $num ) {
1442 $this->installPrint( 'Not implemented for DB2: limitResultForUpdate()' );
1447 * Only useful with fake prepare like in base Database class
1450 public function fillPreparedArg( $matches ) {
1451 $this->installPrint( 'Not useful for DB2: fillPreparedArg()' );
1455 ######################################
1457 ######################################
1460 * Returns information about an index
1461 * If errors are explicitly ignored, returns NULL on failure
1462 * @param $table String: table name
1463 * @param $index String: index name
1464 * @param $fname String: function name for logging and profiling
1465 * @return Object query row in object form
1467 public function indexInfo( $table, $index,
1468 $fname = 'DatabaseIbm_db2::indexExists' )
1470 $table = $this->tableName( $table );
1472 SELECT name as indexname
1473 FROM sysibm.sysindexes si
1474 WHERE si.name='$index' AND si.tbname='$table'
1475 AND sc.tbcreator='$this->mSchema'
1477 $res = $this->query( $sql, $fname );
1481 $row = $this->fetchObject( $res );
1482 if ( $row != null ) {
1490 * Returns an information object on a table column
1491 * @param $table String: table name
1492 * @param $field String: column name
1493 * @return IBM_DB2Field
1495 public function fieldInfo( $table, $field ) {
1496 return IBM_DB2Field
::fromText( $this, $table, $field );
1500 * db2_field_type() wrapper
1501 * @param $res Object: result of executed statement
1502 * @param $index Mixed: number or name of the column
1503 * @return String column type
1505 public function fieldType( $res, $index ) {
1506 if ( $res instanceof ResultWrapper
) {
1507 $res = $res->result
;
1509 if ( $res instanceof IBM_DB2Result
) {
1510 $res = $res->getResult();
1512 return db2_field_type( $res, $index );
1516 * Verifies that an index was created as unique
1517 * @param $table String: table name
1518 * @param $index String: index name
1519 * @param $fname string function name for profiling
1522 public function indexUnique ( $table, $index,
1523 $fname = 'DatabaseIbm_db2::indexUnique' )
1525 $table = $this->tableName( $table );
1527 SELECT si.name as indexname
1528 FROM sysibm.sysindexes si
1529 WHERE si.name='$index' AND si.tbname='$table'
1530 AND sc.tbcreator='$this->mSchema'
1531 AND si.uniquerule IN ( 'U', 'P' )
1533 $res = $this->query( $sql, $fname );
1537 if ( $this->fetchObject( $res ) ) {
1545 * Returns the size of a text field, or -1 for "unlimited"
1546 * @param $table String: table name
1547 * @param $field String: column name
1548 * @return Integer: length or -1 for unlimited
1550 public function textFieldSize( $table, $field ) {
1551 $table = $this->tableName( $table );
1553 SELECT length as size
1554 FROM sysibm.syscolumns sc
1555 WHERE sc.name='$field' AND sc.tbname='$table'
1556 AND sc.tbcreator='$this->mSchema'
1558 $res = $this->query( $sql );
1559 $row = $this->fetchObject( $res );
1565 * Description is left as an exercise for the reader
1566 * @param $b Mixed: data to be encoded
1567 * @return IBM_DB2Blob
1569 public function encodeBlob( $b ) {
1570 return new IBM_DB2Blob( $b );
1574 * Description is left as an exercise for the reader
1575 * @param $b IBM_DB2Blob: data to be decoded
1578 public function decodeBlob( $b ) {
1583 * Convert into a list of string being concatenated
1584 * @param $stringList Array: strings that need to be joined together
1586 * @return String: joined by the concatenation operator
1588 public function buildConcat( $stringList ) {
1589 // || is equivalent to CONCAT
1590 // Sample query: VALUES 'foo' CONCAT 'bar' CONCAT 'baz'
1591 return implode( ' || ', $stringList );
1595 * Generates the SQL required to convert a DB2 timestamp into a Unix epoch
1596 * @param $column String: name of timestamp column
1597 * @return String: SQL code
1599 public function extractUnixEpoch( $column ) {
1601 // see SpecialAncientpages
1604 ######################################
1605 # Prepared statements
1606 ######################################
1609 * Intended to be compatible with the PEAR::DB wrapper functions.
1610 * http://pear.php.net/manual/en/package.database.db.intro-execute.php
1612 * ? = scalar value, quoted as necessary
1613 * ! = raw SQL bit (a function for instance)
1614 * & = filename; reads the file and inserts as a blob
1615 * (we don't use this though...)
1616 * @param $sql String: SQL statement with appropriate markers
1617 * @param $func String: Name of the function, for profiling
1618 * @return resource a prepared DB2 SQL statement
1620 public function prepare( $sql, $func = 'DB2::prepare' ) {
1621 $stmt = db2_prepare( $this->mConn
, $sql, $this->mStmtOptions
);
1626 * Frees resources associated with a prepared statement
1627 * @return Boolean success or failure
1629 public function freePrepared( $prepared ) {
1630 return db2_free_stmt( $prepared );
1634 * Execute a prepared query with the various arguments
1635 * @param $prepared String: the prepared sql
1636 * @param $args Mixed: either an array here, or put scalars as varargs
1637 * @return Resource: results object
1639 public function execute( $prepared, $args = null ) {
1640 if( !is_array( $args ) ) {
1642 $args = func_get_args();
1643 array_shift( $args );
1645 $res = db2_execute( $prepared, $args );
1647 $this->installPrint( db2_stmt_errormsg() );
1653 * Prepare & execute an SQL statement, quoting and inserting arguments
1654 * in the appropriate places.
1655 * @param $query String
1659 public function safeQuery( $query, $args = null ) {
1660 // copied verbatim from Database.php
1661 $prepared = $this->prepare( $query, 'DB2::safeQuery' );
1662 if( !is_array( $args ) ) {
1664 $args = func_get_args();
1665 array_shift( $args );
1667 $retval = $this->execute( $prepared, $args );
1668 $this->freePrepared( $prepared );
1673 * For faking prepared SQL statements on DBs that don't support
1675 * @param $preparedQuery String: a 'preparable' SQL statement
1676 * @param $args Array of arguments to fill it with
1677 * @return String: executable statement
1679 public function fillPrepared( $preparedQuery, $args ) {
1681 $this->preparedArgs
=& $args;
1683 foreach ( $args as $i => $arg ) {
1684 db2_bind_param( $preparedQuery, $i+
1, $args[$i] );
1687 return $preparedQuery;
1691 * Switches module between regular and install modes
1694 public function setMode( $mode ) {
1695 $old = $this->mMode
;
1696 $this->mMode
= $mode;
1701 * Bitwise negation of a column or value in SQL
1702 * Same as (~field) in C
1703 * @param $field String
1706 function bitNot( $field ) {
1707 // expecting bit-fields smaller than 4bytes
1708 return "BITNOT( $field )";
1712 * Bitwise AND of two columns or values in SQL
1713 * Same as (fieldLeft & fieldRight) in C
1714 * @param $fieldLeft String
1715 * @param $fieldRight String
1718 function bitAnd( $fieldLeft, $fieldRight ) {
1719 return "BITAND( $fieldLeft, $fieldRight )";
1723 * Bitwise OR of two columns or values in SQL
1724 * Same as (fieldLeft | fieldRight) in C
1725 * @param $fieldLeft String
1726 * @param $fieldRight String
1729 function bitOr( $fieldLeft, $fieldRight ) {
1730 return "BITOR( $fieldLeft, $fieldRight )";
1734 class IBM_DB2Helper
{
1735 public static function makeArray( $maybeArray ) {
1736 if ( !is_array( $maybeArray ) ) {
1737 return array( $maybeArray );