00001 <?php
00002
00006 class oxDb extends oxSuperCfg
00007 {
00013 private static $_instance = null;
00014
00020 private static $_oDB = null;
00021
00027 protected static $_aTblDescCache = array();
00028
00034 public static function getInstance()
00035 {
00036
00037 if ( defined( 'OXID_PHP_UNIT' ) ) {
00038 static $inst = array();
00039 self::$_instance = $inst[oxClassCacheKey()];
00040 }
00041
00042
00043 if ( !self::$_instance instanceof oxDb ) {
00044
00045
00046 self::$_instance = oxNew( 'oxdb' );
00047
00048 if ( defined( 'OXID_PHP_UNIT' ) ) {
00049 $inst[oxClassCacheKey()] = self::$_instance;
00050 }
00051 }
00052 return self::$_instance;
00053 }
00054
00064 public static function getDb( $blAssoc = false )
00065 {
00066 if ( defined( 'OXID_PHP_UNIT' ) ) {
00067 if ( isset( modDB::$unitMOD ) && is_object( modDB::$unitMOD ) ) {
00068 return modDB::$unitMOD;
00069 }
00070 }
00071
00072 global $ADODB_FETCH_MODE;
00073
00074 if ( $blAssoc )
00075 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
00076 else
00077 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
00078
00079 if ( self::$_oDB !== null ) {
00080 return self::$_oDB;
00081 }
00082
00083 global $ADODB_CACHE_DIR;
00084 global $ADODB_DRIVER,
00085 $ADODB_SESSION_TBL,
00086 $ADODB_SESSION_CONNECT,
00087 $ADODB_SESSION_DRIVER,
00088 $ADODB_SESSION_USER,
00089 $ADODB_SESSION_PWD,
00090 $ADODB_SESSION_DB,
00091 $ADODB_SESS_LIFE,
00092 $ADODB_SESS_DEBUG;
00093
00094
00095 $myConfig = self::getInstance()->getConfig();
00096 $iDebug = $myConfig->getConfigParam( 'iDebug' );
00097 if ( $iDebug ) {
00098 require_once getShopBasePath() . 'core/adodblite/adodb-exceptions.inc.php';
00099 }
00100
00101
00102
00103
00104
00105
00106
00107 $ADODB_SESS_LIFE = 3000 * 60;
00108 $ADODB_SESSION_TBL = "oxsessions";
00109
00110
00111 $ADODB_SESSION_DRIVER = "mysql";
00112 $ADODB_SESSION_USER = $myConfig->getConfigParam( 'dbUser' );
00113 $ADODB_SESSION_PWD = $myConfig->getConfigParam( 'dbPwd' );
00114 $ADODB_SESSION_DB = $myConfig->getConfigParam( 'dbName' );
00115 $ADODB_SESSION_CONNECT = $myConfig->getConfigParam( 'dbHost' );
00116 $ADODB_SESS_DEBUG = false;
00117 $ADODB_CACHE_DIR = $myConfig->getConfigParam( 'sCompileDir' );
00118
00119 $sModules = '';
00120 if ( $iDebug == 2 || $iDebug == 3 || $iDebug == 4 || $iDebug == 7 ) {
00121 $sModules = 'perfmon';
00122 }
00123
00124
00125 if ( $myConfig->isAdmin() && $myConfig->getConfigParam( 'blLogChangesInAdmin' ) ) {
00126 $sModules = ( $sModules ? ':' : '' ) . 'oxadminlog';
00127 }
00128
00129 self::$_oDB = ADONewConnection( $myConfig->getConfigParam( 'dbType' ), $sModules );
00130
00131 $sVerPrefix = '';
00132 $sVerPrefix = '_ce';
00133
00134 if ( !self::$_oDB->connect( $myConfig->getConfigParam( 'dbHost' ), $myConfig->getConfigParam( 'dbUser' ), $myConfig->getConfigParam( 'dbPwd' ), $myConfig->getConfigParam( 'dbName' ) ) ) {
00135 $sConfig = join( '', file( getShopBasePath().'config.inc.php' ) );
00136 if ( strpos( $sConfig, '<dbHost'.$sVerPrefix.'>' ) !== false &&
00137 strpos( $sConfig, '<dbName'.$sVerPrefix.'>' ) !== false ) {
00138 header( 'location:setup/index.php' );
00139 exit();
00140 } else {
00141
00142
00143 $sFailedShop = isset( $_REQUEST['shp'] )?addslashes( $_REQUEST['shp'] ):'Base shop';
00144
00145 $sDate = date( 'l dS of F Y h:i:s A');
00146 $sScript = $_SERVER['SCRIPT_NAME'].'?'.$_SERVER['QUERY_STRING'];
00147 $sReferer = $_SERVER['HTTP_REFERER'];
00148
00149
00150 $sWarningSubject = 'Offline warning!';
00151 $sWarningBody = "
00152 Database error in OXID eShop:
00153 Date: $sDate
00154 Shop: $sFailedShop
00155
00156 mysql error: ".self::$_oDB->errorMsg()."
00157 mysql error no: ".self::$_oDB->errorNo()."
00158
00159 Script: $sScript
00160 Referer: $sReferer";
00161
00162 if ( ( $sAdminEmail = $myConfig->getConfigParam( 'sAdminEmail' ) ) ) {
00163 include 'core/phpmailer/class.phpmailer.php';
00164
00165 $oMailer = new phpmailer();
00166 $oMailer->isMail();
00167 $oMailer->From = $sAdminEmail;
00168 $oMailer->AddAddress( $sAdminEmail );
00169 $oMailer->Subject = $sWarningSubject;
00170 $oMailer->Body = $sWarningBody;
00171 $oMailer->send();
00172 }
00173
00174
00175 $oEx = new oxConnectionException();
00176 $oEx->setMessage( 'EXCEPTION_CONNECTION_NODB' );
00177 $oEx->setConnectionError( $myConfig->getConfigParam( 'dbUser' ).'s'.getShopBasePath().self::$_oDB->errorMsg() );
00178 throw $oEx;
00179 }
00180 }
00181
00182 if ( $iDebug == 2 || $iDebug == 3 || $iDebug == 4 || $iDebug == 7 ) {
00183 try {
00184 self::$_oDB->execute('truncate table adodb_logsql;');
00185 } catch (ADODB_Exception $e) {
00186
00187 }
00188 self::$_oDB->logSQL( true );
00189 }
00190
00191 self::$_oDB->cacheSecs = 60 * 10;
00192 self::$_oDB->execute( 'SET @@session.sql_mode = ""' );
00193
00194 if ( $myConfig->isUtf() ) {
00195 self::$_oDB->execute( 'SET NAMES "utf8"' );
00196 self::$_oDB->execute( 'SET CHARACTER SET utf8' );
00197 self::$_oDB->execute( 'SET CHARACTER_SET_CONNECTION = utf8' );
00198 self::$_oDB->execute( 'SET CHARACTER_SET_DATABASE = utf8' );
00199 self::$_oDB->execute( 'SET character_set_results = utf8' );
00200 self::$_oDB->execute( 'SET character_set_server = utf8' );
00201 }
00202
00203 return self::$_oDB;
00204 }
00205
00213 public function getMultiLangFieldName( $sField )
00214 {
00215
00216
00217
00218
00219
00220
00221 return $sField . oxLang::getInstance()->getLanguageTag();
00222 }
00223
00232 public function isQuoteNeeded( $sFieldtype)
00233 {
00234 $aTypesWoQuotes = array('int', 'decimal', 'float', 'tinyint', 'smallint', 'mediumint', 'bigint', 'double');
00235 return !in_array( $sFieldtype, $aTypesWoQuotes);
00236 }
00237
00245 public function quoteArray( $aStrArray)
00246 {
00247 foreach ( $aStrArray as $sKey => $sString ) {
00248 $aStrArray[$sKey] = self::getDb()->quote($sString);
00249 }
00250 return $aStrArray;
00251 }
00252
00260 public function getTableDescription( $sTableName )
00261 {
00262
00263 if ( isset( self::$_aTblDescCache[$sTableName] ) ) {
00264 return self::$_aTblDescCache[$sTableName];
00265 }
00266
00267 $aFields = self::getDb()->MetaColumns( $sTableName );
00268
00269 self::$_aTblDescCache[$sTableName] = $aFields;
00270
00271 return $aFields;
00272 }
00273
00283 public function convertDBDateTime( $oObject, $blToTimeStamp = false, $blOnlyDate = false )
00284 {
00285 $sDate = $oObject->value;
00286
00287
00288 $sLocalDateFormat = $this->_defineAndCheckDefaultDateValues( $blToTimeStamp );
00289 $sLocalTimeFormat = $this->_defineAndCheckDefaultTimeValues( $blToTimeStamp );
00290
00291
00292 $aDefDatePatterns = $this->_defaultDatePattern();
00293
00294
00295 $aDatePatterns = $this->_regexp2ValidateDateInput();
00296 $aTimePatterns = $this->_regexp2ValidateTimeInput();
00297
00298
00299 $aDFormats = $this->_defineDateFormattingRules();
00300 $aTFormats = $this->_defineTimeFormattingRules();
00301
00302
00303 if ( !$sDate) {
00304 $this->_setDefaultDateTimeValue($oObject, $sLocalDateFormat, $sLocalTimeFormat, $blOnlyDate);
00305 return $oObject->value;
00306 }
00307
00308 $blDefDateFound = false;
00309
00310
00311 foreach ( array_keys( $aDefDatePatterns ) as $sDefDatePattern ) {
00312 if ( preg_match( $sDefDatePattern, $sDate)) {
00313 $blDefDateFound = true;
00314 break;
00315 }
00316 }
00317
00318
00319 if ( $blDefDateFound) {
00320 $this->_setDefaultFormatedValue($oObject, $sDate, $sLocalDateFormat, $sLocalTimeFormat, $blOnlyDate);
00321 return $oObject->value;
00322 }
00323
00324 $blDateFound = false;
00325 $blTimeFound = false;
00326 $aDateMatches = array();
00327 $aTimeMatches = array();
00328
00329
00330 foreach ( $aDatePatterns as $sPattern => $sType) {
00331 if ( preg_match( $sPattern, $sDate, $aDateMatches)) {
00332 $blDateFound = true;
00333
00334
00335 $sDateFormat = $aDFormats[$sLocalDateFormat][0];
00336 $aDFields = $aDFormats[$sType][1];
00337 break;
00338 }
00339 }
00340
00341
00342 if ( !$blDateFound) {
00343 return $sDate;
00344 }
00345
00346 if ( $blOnlyDate) {
00347 $this->_setDate($oObject, $sDateFormat, $aDFields, $aDateMatches);
00348 return $oObject->value;
00349 }
00350
00351
00352 foreach ( $aTimePatterns as $sPattern => $sType) {
00353 if ( preg_match( $sPattern, $sDate, $aTimeMatches)) {
00354 $blTimeFound = true;
00355
00356
00357 $sTimeFormat = $aTFormats[$sLocalTimeFormat][0];
00358 $aTFields = $aTFormats[$sType][1];
00359
00360
00361 if ( $sType == "USA" && isset($aTimeMatches[4])) {
00362 $iIntVal = (int) $aTimeMatches[1];
00363 if ( $aTimeMatches[4] == "PM") {
00364 if ( $iIntVal < 13) {
00365 $iIntVal += 12;
00366 }
00367 } elseif ( $aTimeMatches[4] == "AM" && $aTimeMatches[1] == "12") {
00368 $iIntVal = 0;
00369 }
00370
00371 $aTimeMatches[1] = sprintf("%02d", $iIntVal);
00372 }
00373
00374 break;
00375 }
00376 }
00377
00378 if ( !$blTimeFound) {
00379
00380
00381 $this->_setDate($oObject, $sDateFormat, $aDFields, $aDateMatches);
00382 return $oObject->value;
00383 }
00384
00385 $this->_formatCorrectTimeValue($oObject, $sDateFormat, $sTimeFormat, $aDateMatches, $aTimeMatches, $aTFields, $aDFields);
00386
00387
00388 if ( !$oObject->fldmax_length) {
00389 return $this->convertDBDateTime( $oObject, $blToTimeStamp, $blOnlyDate);
00390 }
00391 return $oObject->value;
00392 }
00393
00402 public function convertDBTimestamp( $oObject, $blToTimeStamp = false )
00403 {
00404
00405 $sSQLTimeStampPattern = "/^([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})$/";
00406 $sISOTimeStampPattern = "/^([0-9]{4})-([0-9]{2})-([0-9]{2}) ([0-9]{2}):([0-9]{2}):([0-9]{2})$/";
00407 $aMatches = array();
00408
00409
00410 if ( $blToTimeStamp) {
00411
00412 $this->convertDBDateTime( $oObject, $blToTimeStamp );
00413
00414 if ( preg_match( $sISOTimeStampPattern, $oObject->value, $aMatches)) {
00415
00416 $oObject->setValue($aMatches[1].$aMatches[2].$aMatches[3].$aMatches[4].$aMatches[5].$aMatches[6]);
00417 $oObject->fldmax_length = strlen( $oObject->value);
00418 return $oObject->value;
00419 }
00420 } else {
00421
00422
00423
00424 if ( preg_match( $sSQLTimeStampPattern, $oObject->value, $aMatches ) ) {
00425 $iTimestamp = mktime( $aMatches[4],
00426 $aMatches[5],
00427 $aMatches[6],
00428 $aMatches[2],
00429 $aMatches[3],
00430 $aMatches[1]);
00431 if ( !$iTimestamp ) {
00432 $iTimestamp = "0";
00433 }
00434
00435 $oObject->setValue(trim( date( "Y-m-d H:i:s", $iTimestamp)));
00436 $oObject->fldmax_length = strlen( $oObject->value);
00437 $this->convertDBDateTime( $oObject, $blToTimeStamp );
00438 return $oObject->value;
00439 }
00440 }
00441 }
00442
00451 public function convertDBDate( $oObject, $blToTimeStamp = false )
00452 {
00453 return $this->convertDBDateTime( $oObject, $blToTimeStamp, true );
00454 }
00455
00463 public function createSQLList( $aArray )
00464 {
00465 $sRet = "";
00466
00467 $blSep = false;
00468 foreach ( $aArray as $aToken) {
00469 if ( !$aToken[0]) {
00470 continue;
00471 }
00472 if ( $blSep) {
00473 $sRet .= ",";
00474 }
00475 $sRet .= "'".$aToken[0]."'";
00476 $blSep = true;
00477 }
00478 return $sRet;
00479 }
00480
00486 static public function startTransaction()
00487 {
00488 self::$_oDB->execute( 'START TRANSACTION' );
00489 }
00490
00496 static public function commitTransaction()
00497 {
00498 self::$_oDB->execute( 'COMMIT' );
00499 }
00500
00506 static public function rollbackTransaction()
00507 {
00508 self::$_oDB->execute( 'ROLLBACK' );
00509 }
00510
00519 static public function setTransactionIsolationLevel( $sLevel = null )
00520 {
00521 $aLevels = array( 'READ UNCOMMITTED', 'READ COMMITTED', 'REPEATABLE READ', 'SERIALIZABLE' );
00522
00523 if (in_array(strtoupper($sLevel), $aLevels)) {
00524 self::$_oDB->execute( 'SET TRANSACTION ISOLATION LEVEL ' . $sLevel );
00525 }
00526 }
00527
00528
00537 public function isValidFieldName( $sField )
00538 {
00539 return ( boolean ) preg_match( "#^[\w\d\._]*$#", $sField );
00540 }
00541
00553 protected function _setDefaultFormatedValue( $oObject, $sDate, $sLocalDateFormat, $sLocalTimeFormat, $blOnlyDate )
00554 {
00555 $aDefTimePatterns = $this->_defaultTimePattern();
00556 $aDFormats = $this->_defineDateFormattingRules();
00557 $aTFormats = $this->_defineTimeFormattingRules();
00558
00559 foreach ( array_keys( $aDefTimePatterns ) as $sDefTimePattern ) {
00560 if ( preg_match( $sDefTimePattern, $sDate ) ) {
00561 $blDefTimeFound = true;
00562 break;
00563 }
00564 }
00565
00566
00567 if ( $blOnlyDate) {
00568 $oObject->setValue(trim( $aDFormats[$sLocalDateFormat][2] ));
00569
00570 $oObject->fldmax_length = strlen( $oObject->value );
00571 return ;
00572 } elseif ( $blDefTimeFound ) {
00573
00574 $oObject->setValue(trim( $aDFormats[$sLocalDateFormat][2] . " " . $aTFormats[$sLocalTimeFormat][2] ));
00575
00576 $oObject->fldmax_length = strlen( $oObject->value );
00577 return ;
00578 }
00579 }
00580
00588 protected function _defineAndCheckDefaultTimeValues( $blToTimeStamp )
00589 {
00590
00591
00592 $sLocalTimeFormat = $this->getConfig()->getConfigParam( 'sLocalTimeFormat' );
00593 if ( !$sLocalTimeFormat || $blToTimeStamp) {
00594 $sLocalTimeFormat = "ISO";
00595 }
00596 return $sLocalTimeFormat;
00597 }
00598
00606 protected function _defineAndCheckDefaultDateValues( $blToTimeStamp )
00607 {
00608
00609
00610 $sLocalDateFormat = $this->getConfig()->getConfigParam( 'sLocalDateFormat' );
00611 if ( !$sLocalDateFormat || $blToTimeStamp) {
00612 $sLocalDateFormat = "ISO";
00613 }
00614 return $sLocalDateFormat;
00615 }
00616
00622 protected function _defaultDatePattern()
00623 {
00624
00625 $aDefDatePatterns = array("/^0000-00-00/" => "ISO",
00626 "/^00\.00\.0000/" => "EUR",
00627 "/^00\/00\/0000/" => "USA"
00628 );
00629 return $aDefDatePatterns;
00630 }
00631
00637 protected function _defaultTimePattern()
00638 {
00639
00640 $aDefTimePatterns = array("/00:00:00$/" => "ISO",
00641 "/00\.00\.00$/" => "EUR",
00642 "/00:00:00 AM$/" => "USA"
00643 );
00644 return $aDefTimePatterns;
00645 }
00646
00652 protected function _regexp2ValidateDateInput()
00653 {
00654
00655 $aDatePatterns = array("/^([0-9]{4})-([0-9]{2})-([0-9]{2})/" => "ISO",
00656 "/^([0-9]{2})\.([0-9]{2})\.([0-9]{4})/" => "EUR",
00657 "/^([0-9]{2})\/([0-9]{2})\/([0-9]{4})/" => "USA"
00658 );
00659 return $aDatePatterns;
00660 }
00661
00667 protected function _regexp2ValidateTimeInput()
00668 {
00669
00670 $aTimePatterns = array("/([0-9]{2}):([0-9]{2}):([0-9]{2})$/" => "ISO",
00671 "/([0-9]{2})\.([0-9]{2})\.([0-9]{2})$/" => "EUR",
00672 "/([0-9]{2}):([0-9]{2}):([0-9]{2}) ([AP]{1}[M]{1})$/" => "USA"
00673 );
00674 return $aTimePatterns;
00675 }
00676
00682 protected function _defineDateFormattingRules()
00683 {
00684
00685 $aDFormats = array("ISO" => array("Y-m-d", array(2, 3, 1), "0000-00-00"),
00686 "EUR" => array("d.m.Y", array(2, 1, 3), "00.00.0000"),
00687 "USA" => array("m/d/Y", array(1, 2, 3), "00/00/0000")
00688 );
00689 return $aDFormats;
00690 }
00691
00697 protected function _defineTimeFormattingRules()
00698 {
00699
00700 $aTFormats = array("ISO" => array("H:i:s", array(1, 2, 3 ), "00:00:00"),
00701 "EUR" => array("H.i.s", array(1, 2, 3 ), "00.00.00"),
00702 "USA" => array("h:i:s A", array(1, 2, 3 ), "00:00:00 AM")
00703 );
00704 return $aTFormats;
00705 }
00706
00717 protected function _setDefaultDateTimeValue( $oObject, $sLocalDateFormat, $sLocalTimeFormat, $blOnlyDate )
00718 {
00719 $aDFormats = $this->_defineDateFormattingRules();
00720 $aTFormats = $this->_defineTimeFormattingRules();
00721
00722 $sReturn = $aDFormats[$sLocalDateFormat][2];
00723 if ( !$blOnlyDate) {
00724 $sReturn .= " ".$aTFormats[$sLocalTimeFormat][2];
00725 }
00726
00727 if ($oObject instanceof oxField) {
00728 $oObject->setValue(trim($sReturn));
00729 } else {
00730 $oObject->value = trim($sReturn);
00731 }
00732
00733 $oObject->fldmax_length = strlen( $oObject->value);
00734 }
00735
00746 protected function _setDate( $oObject, $sDateFormat, $aDFields, $aDateMatches )
00747 {
00748
00749 $iTimestamp = mktime( 0, 0, 0, $aDateMatches[$aDFields[0]],
00750 $aDateMatches[$aDFields[1]],
00751 $aDateMatches[$aDFields[2]]);
00752
00753 if ($oObject instanceof oxField) {
00754 $oObject->setValue(@date( $sDateFormat, $iTimestamp ));
00755 } else {
00756 $oObject->value = @date( $sDateFormat, $iTimestamp );
00757 }
00758
00759 $oObject->fldmax_length = strlen( $oObject->value );
00760 }
00761
00775 protected function _formatCorrectTimeValue( $oObject, $sDateFormat, $sTimeFormat, $aDateMatches, $aTimeMatches, $aTFields, $aDFields )
00776 {
00777
00778 $iTimestamp = @mktime( (int) $aTimeMatches[$aTFields[0]],
00779 (int) $aTimeMatches[$aTFields[1]],
00780 (int) $aTimeMatches[$aTFields[2]],
00781 (int) $aDateMatches[$aDFields[0]],
00782 (int) $aDateMatches[$aDFields[1]],
00783 (int) $aDateMatches[$aDFields[2]] );
00784
00785 if ($oObject instanceof oxField) {
00786 $oObject->setValue(trim( @date( $sDateFormat." ".$sTimeFormat, $iTimestamp ) ));
00787 } else {
00788 $oObject->value = trim( @date( $sDateFormat." ".$sTimeFormat, $iTimestamp ) );
00789 }
00790
00791
00792 $oObject->fldmax_length = strlen( $oObject->value );
00793 }
00794
00800 protected function _getConnectionId()
00801 {
00802 if ( self::$_oDB !== null ) {
00803 return self::$_oDB->connectionId;
00804 }
00805
00806 return null;
00807 }
00808
00816 public function escapeString( $sString )
00817 {
00818 if ( !get_magic_quotes_gpc() ) {
00819 $sString = mysql_real_escape_string( $sString, $this->_getConnectionId() );
00820 }
00821
00822 return $sString;
00823 }
00824
00825 }