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
00260 public function resetTblDescCache()
00261 {
00262 self::$_aTblDescCache = array();
00263 }
00264
00272 public function getTableDescription( $sTableName )
00273 {
00274
00275 if ( isset( self::$_aTblDescCache[$sTableName] ) ) {
00276 return self::$_aTblDescCache[$sTableName];
00277 }
00278
00279 $aFields = self::getDb()->MetaColumns( $sTableName );
00280
00281 self::$_aTblDescCache[$sTableName] = $aFields;
00282
00283 return $aFields;
00284 }
00285
00295 public function convertDBDateTime( $oObject, $blToTimeStamp = false, $blOnlyDate = false )
00296 {
00297 $sDate = $oObject->value;
00298
00299
00300 $sLocalDateFormat = $this->_defineAndCheckDefaultDateValues( $blToTimeStamp );
00301 $sLocalTimeFormat = $this->_defineAndCheckDefaultTimeValues( $blToTimeStamp );
00302
00303
00304 $aDefDatePatterns = $this->_defaultDatePattern();
00305
00306
00307 $aDatePatterns = $this->_regexp2ValidateDateInput();
00308 $aTimePatterns = $this->_regexp2ValidateTimeInput();
00309
00310
00311 $aDFormats = $this->_defineDateFormattingRules();
00312 $aTFormats = $this->_defineTimeFormattingRules();
00313
00314
00315 if ( !$sDate) {
00316 $this->_setDefaultDateTimeValue($oObject, $sLocalDateFormat, $sLocalTimeFormat, $blOnlyDate);
00317 return $oObject->value;
00318 }
00319
00320 $blDefDateFound = false;
00321 $oStr = getStr();
00322
00323
00324 foreach ( array_keys( $aDefDatePatterns ) as $sDefDatePattern ) {
00325 if ( $oStr->preg_match( $sDefDatePattern, $sDate)) {
00326 $blDefDateFound = true;
00327 break;
00328 }
00329 }
00330
00331
00332 if ( $blDefDateFound) {
00333 $this->_setDefaultFormatedValue($oObject, $sDate, $sLocalDateFormat, $sLocalTimeFormat, $blOnlyDate);
00334 return $oObject->value;
00335 }
00336
00337 $blDateFound = false;
00338 $blTimeFound = false;
00339 $aDateMatches = array();
00340 $aTimeMatches = array();
00341
00342
00343 foreach ( $aDatePatterns as $sPattern => $sType) {
00344 if ( $oStr->preg_match( $sPattern, $sDate, $aDateMatches)) {
00345 $blDateFound = true;
00346
00347
00348 $sDateFormat = $aDFormats[$sLocalDateFormat][0];
00349 $aDFields = $aDFormats[$sType][1];
00350 break;
00351 }
00352 }
00353
00354
00355 if ( !$blDateFound) {
00356 return $sDate;
00357 }
00358
00359 if ( $blOnlyDate) {
00360 $this->_setDate($oObject, $sDateFormat, $aDFields, $aDateMatches);
00361 return $oObject->value;
00362 }
00363
00364
00365 foreach ( $aTimePatterns as $sPattern => $sType) {
00366 if ( $oStr->preg_match( $sPattern, $sDate, $aTimeMatches)) {
00367 $blTimeFound = true;
00368
00369
00370 $sTimeFormat = $aTFormats[$sLocalTimeFormat][0];
00371 $aTFields = $aTFormats[$sType][1];
00372
00373
00374 if ( $sType == "USA" && isset($aTimeMatches[4])) {
00375 $iIntVal = (int) $aTimeMatches[1];
00376 if ( $aTimeMatches[4] == "PM") {
00377 if ( $iIntVal < 13) {
00378 $iIntVal += 12;
00379 }
00380 } elseif ( $aTimeMatches[4] == "AM" && $aTimeMatches[1] == "12") {
00381 $iIntVal = 0;
00382 }
00383
00384 $aTimeMatches[1] = sprintf("%02d", $iIntVal);
00385 }
00386
00387 break;
00388 }
00389 }
00390
00391 if ( !$blTimeFound) {
00392
00393
00394 $this->_setDate($oObject, $sDateFormat, $aDFields, $aDateMatches);
00395 return $oObject->value;
00396 }
00397
00398 $this->_formatCorrectTimeValue($oObject, $sDateFormat, $sTimeFormat, $aDateMatches, $aTimeMatches, $aTFields, $aDFields);
00399
00400
00401 if ( !$oObject->fldmax_length) {
00402 return $this->convertDBDateTime( $oObject, $blToTimeStamp, $blOnlyDate);
00403 }
00404 return $oObject->value;
00405 }
00406
00415 public function convertDBTimestamp( $oObject, $blToTimeStamp = false )
00416 {
00417
00418 $sSQLTimeStampPattern = "/^([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})$/";
00419 $sISOTimeStampPattern = "/^([0-9]{4})-([0-9]{2})-([0-9]{2}) ([0-9]{2}):([0-9]{2}):([0-9]{2})$/";
00420 $aMatches = array();
00421 $oStr = getStr();
00422
00423
00424 if ( $blToTimeStamp) {
00425
00426 $this->convertDBDateTime( $oObject, $blToTimeStamp );
00427
00428 if ( $oStr->preg_match( $sISOTimeStampPattern, $oObject->value, $aMatches)) {
00429
00430 $oObject->setValue($aMatches[1].$aMatches[2].$aMatches[3].$aMatches[4].$aMatches[5].$aMatches[6]);
00431 $oObject->fldmax_length = strlen( $oObject->value);
00432 return $oObject->value;
00433 }
00434 } else {
00435
00436
00437
00438 if ( $oStr->preg_match( $sSQLTimeStampPattern, $oObject->value, $aMatches ) ) {
00439 $iTimestamp = mktime( $aMatches[4],
00440 $aMatches[5],
00441 $aMatches[6],
00442 $aMatches[2],
00443 $aMatches[3],
00444 $aMatches[1]);
00445 if ( !$iTimestamp ) {
00446 $iTimestamp = "0";
00447 }
00448
00449 $oObject->setValue(trim( date( "Y-m-d H:i:s", $iTimestamp)));
00450 $oObject->fldmax_length = strlen( $oObject->value);
00451 $this->convertDBDateTime( $oObject, $blToTimeStamp );
00452 return $oObject->value;
00453 }
00454 }
00455 }
00456
00465 public function convertDBDate( $oObject, $blToTimeStamp = false )
00466 {
00467 return $this->convertDBDateTime( $oObject, $blToTimeStamp, true );
00468 }
00469
00477 public function createSQLList( $aArray )
00478 {
00479 $sRet = "";
00480
00481 $blSep = false;
00482 foreach ( $aArray as $aToken) {
00483 if ( !$aToken[0]) {
00484 continue;
00485 }
00486 if ( $blSep) {
00487 $sRet .= ",";
00488 }
00489 $sRet .= "'".$aToken[0]."'";
00490 $blSep = true;
00491 }
00492 return $sRet;
00493 }
00494
00500 static public function startTransaction()
00501 {
00502 self::$_oDB->execute( 'START TRANSACTION' );
00503 }
00504
00510 static public function commitTransaction()
00511 {
00512 self::$_oDB->execute( 'COMMIT' );
00513 }
00514
00520 static public function rollbackTransaction()
00521 {
00522 self::$_oDB->execute( 'ROLLBACK' );
00523 }
00524
00533 static public function setTransactionIsolationLevel( $sLevel = null )
00534 {
00535 $aLevels = array( 'READ UNCOMMITTED', 'READ COMMITTED', 'REPEATABLE READ', 'SERIALIZABLE' );
00536
00537 if (in_array(strtoupper($sLevel), $aLevels)) {
00538 self::$_oDB->execute( 'SET TRANSACTION ISOLATION LEVEL ' . $sLevel );
00539 }
00540 }
00541
00542
00551 public function isValidFieldName( $sField )
00552 {
00553 return ( boolean ) getStr()->preg_match( "#^[\w\d\._]*$#", $sField );
00554 }
00555
00567 protected function _setDefaultFormatedValue( $oObject, $sDate, $sLocalDateFormat, $sLocalTimeFormat, $blOnlyDate )
00568 {
00569 $aDefTimePatterns = $this->_defaultTimePattern();
00570 $aDFormats = $this->_defineDateFormattingRules();
00571 $aTFormats = $this->_defineTimeFormattingRules();
00572 $oStr = getStr();
00573
00574 foreach ( array_keys( $aDefTimePatterns ) as $sDefTimePattern ) {
00575 if ( $oStr->preg_match( $sDefTimePattern, $sDate ) ) {
00576 $blDefTimeFound = true;
00577 break;
00578 }
00579 }
00580
00581
00582 if ( $blOnlyDate) {
00583 $oObject->setValue(trim( $aDFormats[$sLocalDateFormat][2] ));
00584
00585 $oObject->fldmax_length = strlen( $oObject->value );
00586 return ;
00587 } elseif ( $blDefTimeFound ) {
00588
00589 $oObject->setValue(trim( $aDFormats[$sLocalDateFormat][2] . " " . $aTFormats[$sLocalTimeFormat][2] ));
00590
00591 $oObject->fldmax_length = strlen( $oObject->value );
00592 return ;
00593 }
00594 }
00595
00603 protected function _defineAndCheckDefaultTimeValues( $blToTimeStamp )
00604 {
00605
00606
00607 $sLocalTimeFormat = $this->getConfig()->getConfigParam( 'sLocalTimeFormat' );
00608 if ( !$sLocalTimeFormat || $blToTimeStamp) {
00609 $sLocalTimeFormat = "ISO";
00610 }
00611 return $sLocalTimeFormat;
00612 }
00613
00621 protected function _defineAndCheckDefaultDateValues( $blToTimeStamp )
00622 {
00623
00624
00625 $sLocalDateFormat = $this->getConfig()->getConfigParam( 'sLocalDateFormat' );
00626 if ( !$sLocalDateFormat || $blToTimeStamp) {
00627 $sLocalDateFormat = "ISO";
00628 }
00629 return $sLocalDateFormat;
00630 }
00631
00637 protected function _defaultDatePattern()
00638 {
00639
00640 $aDefDatePatterns = array("/^0000-00-00/" => "ISO",
00641 "/^00\.00\.0000/" => "EUR",
00642 "/^00\/00\/0000/" => "USA"
00643 );
00644 return $aDefDatePatterns;
00645 }
00646
00652 protected function _defaultTimePattern()
00653 {
00654
00655 $aDefTimePatterns = array("/00:00:00$/" => "ISO",
00656 "/00\.00\.00$/" => "EUR",
00657 "/00:00:00 AM$/" => "USA"
00658 );
00659 return $aDefTimePatterns;
00660 }
00661
00667 protected function _regexp2ValidateDateInput()
00668 {
00669
00670 $aDatePatterns = array("/^([0-9]{4})-([0-9]{2})-([0-9]{2})/" => "ISO",
00671 "/^([0-9]{2})\.([0-9]{2})\.([0-9]{4})/" => "EUR",
00672 "/^([0-9]{2})\/([0-9]{2})\/([0-9]{4})/" => "USA"
00673 );
00674 return $aDatePatterns;
00675 }
00676
00682 protected function _regexp2ValidateTimeInput()
00683 {
00684
00685 $aTimePatterns = array("/([0-9]{2}):([0-9]{2}):([0-9]{2})$/" => "ISO",
00686 "/([0-9]{2})\.([0-9]{2})\.([0-9]{2})$/" => "EUR",
00687 "/([0-9]{2}):([0-9]{2}):([0-9]{2}) ([AP]{1}[M]{1})$/" => "USA"
00688 );
00689 return $aTimePatterns;
00690 }
00691
00697 protected function _defineDateFormattingRules()
00698 {
00699
00700 $aDFormats = array("ISO" => array("Y-m-d", array(2, 3, 1), "0000-00-00"),
00701 "EUR" => array("d.m.Y", array(2, 1, 3), "00.00.0000"),
00702 "USA" => array("m/d/Y", array(1, 2, 3), "00/00/0000")
00703 );
00704 return $aDFormats;
00705 }
00706
00712 protected function _defineTimeFormattingRules()
00713 {
00714
00715 $aTFormats = array("ISO" => array("H:i:s", array(1, 2, 3 ), "00:00:00"),
00716 "EUR" => array("H.i.s", array(1, 2, 3 ), "00.00.00"),
00717 "USA" => array("h:i:s A", array(1, 2, 3 ), "00:00:00 AM")
00718 );
00719 return $aTFormats;
00720 }
00721
00732 protected function _setDefaultDateTimeValue( $oObject, $sLocalDateFormat, $sLocalTimeFormat, $blOnlyDate )
00733 {
00734 $aDFormats = $this->_defineDateFormattingRules();
00735 $aTFormats = $this->_defineTimeFormattingRules();
00736
00737 $sReturn = $aDFormats[$sLocalDateFormat][2];
00738 if ( !$blOnlyDate) {
00739 $sReturn .= " ".$aTFormats[$sLocalTimeFormat][2];
00740 }
00741
00742 if ($oObject instanceof oxField) {
00743 $oObject->setValue(trim($sReturn));
00744 } else {
00745 $oObject->value = trim($sReturn);
00746 }
00747
00748 $oObject->fldmax_length = strlen( $oObject->value);
00749 }
00750
00761 protected function _setDate( $oObject, $sDateFormat, $aDFields, $aDateMatches )
00762 {
00763
00764 $iTimestamp = mktime( 0, 0, 0, $aDateMatches[$aDFields[0]],
00765 $aDateMatches[$aDFields[1]],
00766 $aDateMatches[$aDFields[2]]);
00767
00768 if ($oObject instanceof oxField) {
00769 $oObject->setValue(@date( $sDateFormat, $iTimestamp ));
00770 } else {
00771 $oObject->value = @date( $sDateFormat, $iTimestamp );
00772 }
00773
00774 $oObject->fldmax_length = strlen( $oObject->value );
00775 }
00776
00790 protected function _formatCorrectTimeValue( $oObject, $sDateFormat, $sTimeFormat, $aDateMatches, $aTimeMatches, $aTFields, $aDFields )
00791 {
00792
00793 $iTimestamp = @mktime( (int) $aTimeMatches[$aTFields[0]],
00794 (int) $aTimeMatches[$aTFields[1]],
00795 (int) $aTimeMatches[$aTFields[2]],
00796 (int) $aDateMatches[$aDFields[0]],
00797 (int) $aDateMatches[$aDFields[1]],
00798 (int) $aDateMatches[$aDFields[2]] );
00799
00800 if ($oObject instanceof oxField) {
00801 $oObject->setValue(trim( @date( $sDateFormat." ".$sTimeFormat, $iTimestamp ) ));
00802 } else {
00803 $oObject->value = trim( @date( $sDateFormat." ".$sTimeFormat, $iTimestamp ) );
00804 }
00805
00806
00807 $oObject->fldmax_length = strlen( $oObject->value );
00808 }
00809
00815 protected function _getConnectionId()
00816 {
00817 if ( self::$_oDB !== null ) {
00818 return self::$_oDB->connectionId;
00819 }
00820
00821 return null;
00822 }
00823
00831 public function escapeString( $sString )
00832 {
00833 return mysql_real_escape_string( $sString, $this->_getConnectionId() );
00834 }
00835
00843 public function updateViews( $aTables = null )
00844 {
00845 $myConfig = $this->getConfig();
00846
00847 $oShopList = oxNew("oxshoplist" );
00848 $oShopList->selectString( "select * from oxshops");
00849
00850 $aTables = $aTables ? $aTables : $myConfig->getConfigParam( 'aMultiShopTables' );
00851 foreach ( $oShopList as $key => $oShop ) {
00852 $oShop->setMultiShopTables( $aTables );
00853 $blMultishopInherit = $myConfig->getShopConfVar( 'blMultishopInherit_oxcategories', $oShop->sOXID );
00854 $aMallInherit = array();
00855 foreach ( $aTables as $sTable ) {
00856 $aMallInherit[$sTable] = $myConfig->getShopConfVar( 'blMallInherit_' . $sTable, $oShop->sOXID );
00857 }
00858 $oShop->generateViews( $blMultishopInherit, $aMallInherit );
00859 }
00860 }
00861 }