00001 <?php
00002
00003
00004
00005 require_once getShopBasePath() . 'core/adodblite/adodb.inc.php';
00006
00010 class oxDb extends oxSuperCfg
00011 {
00017 private static $_instance = null;
00018
00024 private static $_oDB = null;
00025
00031 protected static $_aTblDescCache = array();
00032
00038 public static function getInstance()
00039 {
00040
00041 if ( defined( 'OXID_PHP_UNIT' ) ) {
00042 static $inst = array();
00043 self::$_instance = $inst[oxClassCacheKey()];
00044 }
00045
00046
00047 if ( !self::$_instance instanceof oxDb ) {
00048
00049
00050 self::$_instance = oxNew( 'oxdb' );
00051
00052 if ( defined( 'OXID_PHP_UNIT' ) ) {
00053 $inst[oxClassCacheKey()] = self::$_instance;
00054 }
00055 }
00056 return self::$_instance;
00057 }
00058
00068 public static function getDb( $blAssoc = false )
00069 {
00070 if ( defined( 'OXID_PHP_UNIT' ) ) {
00071 if ( isset( modDB::$unitMOD ) && is_object( modDB::$unitMOD ) ) {
00072 return modDB::$unitMOD;
00073 }
00074 }
00075
00076 global $ADODB_FETCH_MODE;
00077
00078 if ( $blAssoc )
00079 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
00080 else
00081 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00082
00083 if ( self::$_oDB !== null ) {
00084 return self::$_oDB;
00085 }
00086
00087 global $ADODB_CACHE_DIR;
00088 global $ADODB_DRIVER,
00089 $ADODB_SESSION_TBL,
00090 $ADODB_SESSION_CONNECT,
00091 $ADODB_SESSION_DRIVER,
00092 $ADODB_SESSION_USER,
00093 $ADODB_SESSION_PWD,
00094 $ADODB_SESSION_DB,
00095 $ADODB_SESS_LIFE,
00096 $ADODB_SESS_DEBUG;
00097
00098
00099 $myConfig = self::getInstance()->getConfig();
00100 $iDebug = $myConfig->getConfigParam( 'iDebug' );
00101 if ( $iDebug ) {
00102 require_once getShopBasePath() . 'core/adodblite/adodb-exceptions.inc.php';
00103 }
00104
00105
00106
00107
00108
00109
00110
00111 $ADODB_SESS_LIFE = 3000 * 60;
00112 $ADODB_SESSION_TBL = "oxsessions";
00113
00114
00115 $ADODB_SESSION_DRIVER = "mysql";
00116 $ADODB_SESSION_USER = $myConfig->getConfigParam( 'dbUser' );
00117 $ADODB_SESSION_PWD = $myConfig->getConfigParam( 'dbPwd' );
00118 $ADODB_SESSION_DB = $myConfig->getConfigParam( 'dbName' );
00119 $ADODB_SESSION_CONNECT = $myConfig->getConfigParam( 'dbHost' );
00120 $ADODB_SESS_DEBUG = false;
00121 $ADODB_CACHE_DIR = $myConfig->getConfigParam( 'sCompileDir' );
00122
00123 $sModules = '';
00124 if ( $iDebug == 2 || $iDebug == 3 || $iDebug == 4 || $iDebug == 7 ) {
00125 $sModules = 'perfmon';
00126 }
00127
00128
00129 if ( $myConfig->isAdmin() && $myConfig->getConfigParam( 'blLogChangesInAdmin' ) ) {
00130 $sModules = ( $sModules ? ':' : '' ) . 'oxadminlog';
00131 }
00132
00133 self::$_oDB = ADONewConnection( $myConfig->getConfigParam( 'dbType' ), $sModules );
00134
00135 $sVerPrefix = '';
00136 $sVerPrefix = '_ce';
00137
00138 if ( !self::$_oDB->connect( $myConfig->getConfigParam( 'dbHost' ), $myConfig->getConfigParam( 'dbUser' ), $myConfig->getConfigParam( 'dbPwd' ), $myConfig->getConfigParam( 'dbName' ) ) ) {
00139 $sConfig = join( '', file( getShopBasePath().'config.inc.php' ) );
00140 if ( strpos( $sConfig, '<dbHost'.$sVerPrefix.'>' ) !== false &&
00141 strpos( $sConfig, '<dbName'.$sVerPrefix.'>' ) !== false ) {
00142 header( 'location:setup/index.php' );
00143 exit();
00144 } else {
00145
00146
00147 $sFailedShop = isset( $_REQUEST['shp'] )?addslashes( $_REQUEST['shp'] ):'Base shop';
00148
00149 $sDate = date( 'l dS of F Y h:i:s A');
00150 $sScript = $_SERVER['SCRIPT_NAME'].'?'.$_SERVER['QUERY_STRING'];
00151 $sReferer = $_SERVER['HTTP_REFERER'];
00152
00153
00154 $sWarningSubject = 'Offline warning!';
00155 $sWarningBody = "
00156 Database error in OXID eShop:
00157 Date: $sDate
00158 Shop: $sFailedShop
00159
00160 mysql error: ".self::$_oDB->errorMsg()."
00161 mysql error no: ".self::$_oDB->errorNo()."
00162
00163 Script: $sScript
00164 Referer: $sReferer";
00165
00166 if ( ( $sAdminEmail = $myConfig->getConfigParam( 'sAdminEmail' ) ) ) {
00167 include 'core/phpmailer/class.phpmailer.php';
00168
00169 $oMailer = new phpmailer();
00170 $oMailer->isMail();
00171 $oMailer->From = $sAdminEmail;
00172 $oMailer->AddAddress( $sAdminEmail );
00173 $oMailer->Subject = $sWarningSubject;
00174 $oMailer->Body = $sWarningBody;
00175 $oMailer->send();
00176 }
00177
00178
00179 $oEx = new oxConnectionException();
00180 $oEx->setMessage( 'EXCEPTION_CONNECTION_NODB' );
00181 $oEx->setConnectionError( $myConfig->getConfigParam( 'dbUser' ).'s'.getShopBasePath().self::$_oDB->errorMsg() );
00182 throw $oEx;
00183 }
00184 }
00185
00186 if ( $iDebug == 2 || $iDebug == 3 || $iDebug == 4 || $iDebug == 7 ) {
00187 try {
00188 self::$_oDB->execute('truncate table adodb_logsql;');
00189 } catch (ADODB_Exception $e) {
00190
00191 }
00192 self::$_oDB->logSQL( true );
00193 }
00194
00195 self::$_oDB->cacheSecs = 60 * 10;
00196 self::$_oDB->execute( 'SET @@session.sql_mode = ""' );
00197
00198 if ( $myConfig->isUtf() ) {
00199 self::$_oDB->execute( 'SET NAMES "utf8"' );
00200 self::$_oDB->execute( 'SET CHARACTER SET utf8' );
00201 self::$_oDB->execute( 'SET CHARACTER_SET_CONNECTION = utf8' );
00202 self::$_oDB->execute( 'SET CHARACTER_SET_DATABASE = utf8' );
00203 self::$_oDB->execute( 'SET character_set_results = utf8' );
00204 self::$_oDB->execute( 'SET character_set_server = utf8' );
00205 }
00206
00207 return self::$_oDB;
00208 }
00209
00217 public function getMultiLangFieldName( $sField )
00218 {
00219
00220
00221
00222
00223
00224
00225 return $sField . oxLang::getInstance()->getLanguageTag();
00226 }
00227
00236 public function isQuoteNeeded( $sFieldtype)
00237 {
00238 $aTypesWoQuotes = array('int', 'decimal', 'float', 'tinyint', 'smallint', 'mediumint', 'bigint', 'double');
00239 return !in_array( $sFieldtype, $aTypesWoQuotes);
00240 }
00241
00249 public function quoteArray( $aStrArray)
00250 {
00251 foreach ( $aStrArray as $sKey => $sString ) {
00252 $aStrArray[$sKey] = self::getDb()->quote($sString);
00253 }
00254 return $aStrArray;
00255 }
00256
00264 public function getTableDescription( $sTableName )
00265 {
00266
00267 if ( isset( self::$_aTblDescCache[$sTableName] ) ) {
00268 return self::$_aTblDescCache[$sTableName];
00269 }
00270
00271 $aFields = self::getDb()->MetaColumns( $sTableName );
00272
00273 self::$_aTblDescCache[$sTableName] = $aFields;
00274
00275 return $aFields;
00276 }
00277
00287 public function convertDBDateTime( $oObject, $blToTimeStamp = false, $blOnlyDate = false )
00288 {
00289 $sDate = $oObject->value;
00290
00291
00292 $sLocalDateFormat = $this->_defineAndCheckDefaultDateValues( $blToTimeStamp );
00293 $sLocalTimeFormat = $this->_defineAndCheckDefaultTimeValues( $blToTimeStamp );
00294
00295
00296 $aDefDatePatterns = $this->_defaultDatePattern();
00297
00298
00299 $aDatePatterns = $this->_regexp2ValidateDateInput();
00300 $aTimePatterns = $this->_regexp2ValidateTimeInput();
00301
00302
00303 $aDFormats = $this->_defineDateFormattingRules();
00304 $aTFormats = $this->_defineTimeFormattingRules();
00305
00306
00307 if ( !$sDate) {
00308 $this->_setDefaultDateTimeValue($oObject, $sLocalDateFormat, $sLocalTimeFormat, $blOnlyDate);
00309 return $oObject->value;
00310 }
00311
00312 $blDefDateFound = false;
00313
00314
00315 foreach ( array_keys( $aDefDatePatterns ) as $sDefDatePattern ) {
00316 if ( preg_match( $sDefDatePattern, $sDate)) {
00317 $blDefDateFound = true;
00318 break;
00319 }
00320 }
00321
00322
00323 if ( $blDefDateFound) {
00324 $this->_setDefaultFormatedValue($oObject, $sDate, $sLocalDateFormat, $sLocalTimeFormat, $blOnlyDate);
00325 return $oObject->value;
00326 }
00327
00328 $blDateFound = false;
00329 $blTimeFound = false;
00330 $aDateMatches = array();
00331 $aTimeMatches = array();
00332
00333
00334 foreach ( $aDatePatterns as $sPattern => $sType) {
00335 if ( preg_match( $sPattern, $sDate, $aDateMatches)) {
00336 $blDateFound = true;
00337
00338
00339 $sDateFormat = $aDFormats[$sLocalDateFormat][0];
00340 $aDFields = $aDFormats[$sType][1];
00341 break;
00342 }
00343 }
00344
00345
00346 if ( !$blDateFound) {
00347 return $sDate;
00348 }
00349
00350 if ( $blOnlyDate) {
00351 $this->_setDate($oObject, $sDateFormat, $aDFields, $aDateMatches);
00352 return $oObject->value;
00353 }
00354
00355
00356 foreach ( $aTimePatterns as $sPattern => $sType) {
00357 if ( preg_match( $sPattern, $sDate, $aTimeMatches)) {
00358 $blTimeFound = true;
00359
00360
00361 $sTimeFormat = $aTFormats[$sLocalTimeFormat][0];
00362 $aTFields = $aTFormats[$sType][1];
00363
00364
00365 if ( $sType == "USA" && isset($aTimeMatches[4])) {
00366 $iIntVal = (int) $aTimeMatches[1];
00367 if ( $aTimeMatches[4] == "PM") {
00368 if ( $iIntVal < 13) {
00369 $iIntVal += 12;
00370 }
00371 } elseif ( $aTimeMatches[4] == "AM" && $aTimeMatches[1] == "12") {
00372 $iIntVal = 0;
00373 }
00374
00375 $aTimeMatches[1] = sprintf("%02d", $iIntVal);
00376 }
00377
00378 break;
00379 }
00380 }
00381
00382 if ( !$blTimeFound) {
00383
00384
00385 $this->_setDate($oObject, $sDateFormat, $aDFields, $aDateMatches);
00386 return $oObject->value;
00387 }
00388
00389 $this->_formatCorrectTimeValue($oObject, $sDateFormat, $sTimeFormat, $aDateMatches, $aTimeMatches, $aTFields, $aDFields);
00390
00391
00392 if ( !$oObject->fldmax_length) {
00393 return $this->convertDBDateTime( $oObject, $blToTimeStamp, $blOnlyDate);
00394 }
00395 return $oObject->value;
00396 }
00397
00406 public function convertDBTimestamp( $oObject, $blToTimeStamp = false )
00407 {
00408
00409 $sSQLTimeStampPattern = "/^([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})$/";
00410 $sISOTimeStampPattern = "/^([0-9]{4})-([0-9]{2})-([0-9]{2}) ([0-9]{2}):([0-9]{2}):([0-9]{2})$/";
00411 $aMatches = array();
00412
00413
00414 if ( $blToTimeStamp) {
00415
00416 $this->convertDBDateTime( $oObject, $blToTimeStamp );
00417
00418 if ( preg_match( $sISOTimeStampPattern, $oObject->value, $aMatches)) {
00419
00420 $oObject->setValue($aMatches[1].$aMatches[2].$aMatches[3].$aMatches[4].$aMatches[5].$aMatches[6]);
00421 $oObject->fldmax_length = strlen( $oObject->value);
00422 return $oObject->value;
00423 }
00424 } else {
00425
00426
00427
00428 if ( preg_match( $sSQLTimeStampPattern, $oObject->value, $aMatches ) ) {
00429 $iTimestamp = mktime( $aMatches[4],
00430 $aMatches[5],
00431 $aMatches[6],
00432 $aMatches[2],
00433 $aMatches[3],
00434 $aMatches[1]);
00435 if ( !$iTimestamp ) {
00436 $iTimestamp = "0";
00437 }
00438
00439 $oObject->setValue(trim( date( "Y-m-d H:i:s", $iTimestamp)));
00440 $oObject->fldmax_length = strlen( $oObject->value);
00441 $this->convertDBDateTime( $oObject, $blToTimeStamp );
00442 return $oObject->value;
00443 }
00444 }
00445 }
00446
00455 public function convertDBDate( $oObject, $blToTimeStamp = false )
00456 {
00457 return $this->convertDBDateTime( $oObject, $blToTimeStamp, true );
00458 }
00459
00467 public function createSQLList( $aArray )
00468 {
00469 $sRet = "";
00470
00471 $blSep = false;
00472 foreach ( $aArray as $aToken) {
00473 if ( !$aToken[0]) {
00474 continue;
00475 }
00476 if ( $blSep) {
00477 $sRet .= ",";
00478 }
00479 $sRet .= "'".$aToken[0]."'";
00480 $blSep = true;
00481 }
00482 return $sRet;
00483 }
00484
00490 static public function startTransaction()
00491 {
00492 self::$_oDB->execute( 'START TRANSACTION' );
00493 }
00494
00500 static public function commitTransaction()
00501 {
00502 self::$_oDB->execute( 'COMMIT' );
00503 }
00504
00510 static public function rollbackTransaction()
00511 {
00512 self::$_oDB->execute( 'ROLLBACK' );
00513 }
00514
00523 static public function setTransactionIsolationLevel( $sLevel = null )
00524 {
00525 $aLevels = array( 'READ UNCOMMITTED', 'READ COMMITTED', 'REPEATABLE READ', 'SERIALIZABLE' );
00526
00527 if (in_array(strtoupper($sLevel), $aLevels)) {
00528 self::$_oDB->execute( 'SET TRANSACTION ISOLATION LEVEL ' . $sLevel );
00529 }
00530 }
00531
00532
00541 public function isValidFieldName( $sField )
00542 {
00543 return ( boolean ) preg_match( "#^[\w\d\._]*$#", $sField );
00544 }
00545
00557 protected function _setDefaultFormatedValue( $oObject, $sDate, $sLocalDateFormat, $sLocalTimeFormat, $blOnlyDate )
00558 {
00559 $aDefTimePatterns = $this->_defaultTimePattern();
00560 $aDFormats = $this->_defineDateFormattingRules();
00561 $aTFormats = $this->_defineTimeFormattingRules();
00562
00563 foreach ( array_keys( $aDefTimePatterns ) as $sDefTimePattern ) {
00564 if ( preg_match( $sDefTimePattern, $sDate ) ) {
00565 $blDefTimeFound = true;
00566 break;
00567 }
00568 }
00569
00570
00571 if ( $blOnlyDate) {
00572 $oObject->setValue(trim( $aDFormats[$sLocalDateFormat][2] ));
00573
00574 $oObject->fldmax_length = strlen( $oObject->value );
00575 return ;
00576 } elseif ( $blDefTimeFound ) {
00577
00578 $oObject->setValue(trim( $aDFormats[$sLocalDateFormat][2] . " " . $aTFormats[$sLocalTimeFormat][2] ));
00579
00580 $oObject->fldmax_length = strlen( $oObject->value );
00581 return ;
00582 }
00583 }
00584
00592 protected function _defineAndCheckDefaultTimeValues( $blToTimeStamp )
00593 {
00594
00595
00596 $sLocalTimeFormat = $this->getConfig()->getConfigParam( 'sLocalTimeFormat' );
00597 if ( !$sLocalTimeFormat || $blToTimeStamp) {
00598 $sLocalTimeFormat = "ISO";
00599 }
00600 return $sLocalTimeFormat;
00601 }
00602
00610 protected function _defineAndCheckDefaultDateValues( $blToTimeStamp )
00611 {
00612
00613
00614 $sLocalDateFormat = $this->getConfig()->getConfigParam( 'sLocalDateFormat' );
00615 if ( !$sLocalDateFormat || $blToTimeStamp) {
00616 $sLocalDateFormat = "ISO";
00617 }
00618 return $sLocalDateFormat;
00619 }
00620
00626 protected function _defaultDatePattern()
00627 {
00628
00629 $aDefDatePatterns = array("/^0000-00-00/" => "ISO",
00630 "/^00\.00\.0000/" => "EUR",
00631 "/^00\/00\/0000/" => "USA"
00632 );
00633 return $aDefDatePatterns;
00634 }
00635
00641 protected function _defaultTimePattern()
00642 {
00643
00644 $aDefTimePatterns = array("/00:00:00$/" => "ISO",
00645 "/00\.00\.00$/" => "EUR",
00646 "/00:00:00 AM$/" => "USA"
00647 );
00648 return $aDefTimePatterns;
00649 }
00650
00656 protected function _regexp2ValidateDateInput()
00657 {
00658
00659 $aDatePatterns = array("/^([0-9]{4})-([0-9]{2})-([0-9]{2})/" => "ISO",
00660 "/^([0-9]{2})\.([0-9]{2})\.([0-9]{4})/" => "EUR",
00661 "/^([0-9]{2})\/([0-9]{2})\/([0-9]{4})/" => "USA"
00662 );
00663 return $aDatePatterns;
00664 }
00665
00671 protected function _regexp2ValidateTimeInput()
00672 {
00673
00674 $aTimePatterns = array("/([0-9]{2}):([0-9]{2}):([0-9]{2})$/" => "ISO",
00675 "/([0-9]{2})\.([0-9]{2})\.([0-9]{2})$/" => "EUR",
00676 "/([0-9]{2}):([0-9]{2}):([0-9]{2}) ([AP]{1}[M]{1})$/" => "USA"
00677 );
00678 return $aTimePatterns;
00679 }
00680
00686 protected function _defineDateFormattingRules()
00687 {
00688
00689 $aDFormats = array("ISO" => array("Y-m-d", array(2, 3, 1), "0000-00-00"),
00690 "EUR" => array("d.m.Y", array(2, 1, 3), "00.00.0000"),
00691 "USA" => array("m/d/Y", array(1, 2, 3), "00/00/0000")
00692 );
00693 return $aDFormats;
00694 }
00695
00701 protected function _defineTimeFormattingRules()
00702 {
00703
00704 $aTFormats = array("ISO" => array("H:i:s", array(1, 2, 3 ), "00:00:00"),
00705 "EUR" => array("H.i.s", array(1, 2, 3 ), "00.00.00"),
00706 "USA" => array("h:i:s A", array(1, 2, 3 ), "00:00:00 AM")
00707 );
00708 return $aTFormats;
00709 }
00710
00721 protected function _setDefaultDateTimeValue( $oObject, $sLocalDateFormat, $sLocalTimeFormat, $blOnlyDate )
00722 {
00723 $aDFormats = $this->_defineDateFormattingRules();
00724 $aTFormats = $this->_defineTimeFormattingRules();
00725
00726 $sReturn = $aDFormats[$sLocalDateFormat][2];
00727 if ( !$blOnlyDate) {
00728 $sReturn .= " ".$aTFormats[$sLocalTimeFormat][2];
00729 }
00730
00731 if ($oObject instanceof oxField) {
00732 $oObject->setValue(trim($sReturn));
00733 } else {
00734 $oObject->value = trim($sReturn);
00735 }
00736
00737 $oObject->fldmax_length = strlen( $oObject->value);
00738 }
00739
00750 protected function _setDate( $oObject, $sDateFormat, $aDFields, $aDateMatches )
00751 {
00752
00753 $iTimestamp = mktime( 0, 0, 0, $aDateMatches[$aDFields[0]],
00754 $aDateMatches[$aDFields[1]],
00755 $aDateMatches[$aDFields[2]]);
00756
00757 if ($oObject instanceof oxField) {
00758 $oObject->setValue(@date( $sDateFormat, $iTimestamp ));
00759 } else {
00760 $oObject->value = @date( $sDateFormat, $iTimestamp );
00761 }
00762
00763 $oObject->fldmax_length = strlen( $oObject->value );
00764 }
00765
00779 protected function _formatCorrectTimeValue( $oObject, $sDateFormat, $sTimeFormat, $aDateMatches, $aTimeMatches, $aTFields, $aDFields )
00780 {
00781
00782 $iTimestamp = @mktime( (int) $aTimeMatches[$aTFields[0]],
00783 (int) $aTimeMatches[$aTFields[1]],
00784 (int) $aTimeMatches[$aTFields[2]],
00785 (int) $aDateMatches[$aDFields[0]],
00786 (int) $aDateMatches[$aDFields[1]],
00787 (int) $aDateMatches[$aDFields[2]] );
00788
00789 if ($oObject instanceof oxField) {
00790 $oObject->setValue(trim( @date( $sDateFormat." ".$sTimeFormat, $iTimestamp ) ));
00791 } else {
00792 $oObject->value = trim( @date( $sDateFormat." ".$sTimeFormat, $iTimestamp ) );
00793 }
00794
00795
00796 $oObject->fldmax_length = strlen( $oObject->value );
00797 }
00798
00804 protected function _getConnectionId()
00805 {
00806 if ( self::$_oDB !== null ) {
00807 return self::$_oDB->connectionId;
00808 }
00809
00810 return null;
00811 }
00812
00820 public function escapeString( $sString )
00821 {
00822 return mysql_real_escape_string( $sString, $this->_getConnectionId() );
00823 }
00824
00825 }