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