/** * */ protected function migrateTables() { foreach ($this->legacyTableNames as $legacyTableName) { $newTableName = str_replace('_mmforum_', '_typo3forum_', $legacyTableName); // special case for tx_typo3forum_domain_model_moderation_reportworkflowstatus_followup which is too long for MySQL (>64 characters) if ($newTableName === 'tx_typo3forum_domain_model_moderation_reportworkflowstatus_followup') { $newTableName = 'tx_typo3forum_domain_model_moderation_reportworkflowstatus_mm'; } // special case for tx_typo3forum_domain_model_user_privatemessages which has been renamed to singluar if ($newTableName === 'tx_typo3forum_domain_model_user_privatemessages') { $newTableName = 'tx_typo3forum_domain_model_user_privatemessage'; } // special case for tx_typo3forum_domain_model_user_privatemessages_text which has been renamed to singluar if ($newTableName === 'tx_typo3forum_domain_model_user_privatemessages_text') { $newTableName = 'tx_typo3forum_domain_model_user_privatemessage_text'; } if ($this->tableExists($legacyTableName)) { if ($this->tableExists($newTableName)) { if (!$this->tableIsEmpty($newTableName)) { $this->log(sprintf('Table %s couldn\'t be migrated to %s, because it exists and is not empty.', $legacyTableName, $newTableName)); continue; } else { $this->databaseConnection->sql_query(sprintf('DROP TABLE %s', $newTableName)); } } $res = $this->databaseConnection->sql_query(sprintf('RENAME TABLE %s TO %s', $legacyTableName, $newTableName)); if (!$res) { $this->log($this->databaseConnection->sql_error()); } } } }
/** * @test */ public function addDataThrowsExceptionOnDatabaseError() { $this->dbProphecy->exec_SELECTgetRows(Argument::cetera())->willReturn(null); $this->dbProphecy->sql_error(Argument::cetera())->willReturn(null); $this->setExpectedException(\UnexpectedValueException::class, '', 1440777705); $this->subject->addData(['effectivePid' => 1]); }
/** * @test */ public function addDataThrowsExceptionOnDatabaseError() { $this->dbProphecy->exec_SELECTgetRows(Argument::cetera())->willReturn(null); $this->dbProphecy->sql_error(Argument::cetera())->willReturn(null); $this->setExpectedException(\UnexpectedValueException::class, $this->anything(), 1438170741); $this->subject->addData([]); }
/** * @param $query * @return Tx_PtExtlist_Domain_DataBackend_DataSource_Typo3DataSource * @throws Exception */ public function executeQuery($query) { try { $this->startTimeMeasure(); $this->resource = $this->connection->sql_query($query); $this->stopTimeMeasure(); } catch (Exception $e) { throw new Exception('Error while retrieving data from database using typo3 db object.<br> Error: ' . $e->getMessage() . ' sql_error says: ' . $this->connection->sql_error() . ' 1280400023<br><br> SQL QUERY: <br> </strong><hr>' . nl2br($query) . '<hr><strong>', 1280400023); } return $this; }
/** * Process not yet migrated tx_news category records to sys_category records */ protected function migrateNewsCategoryRecords() { $rows = $this->databaseConnection->exec_SELECTgetRows('uid, pid, tstamp, crdate, cruser_id, starttime, endtime, sorting, ' . 'sys_language_uid, l10n_parent, l10n_diffsource, ' . 'title, description, ' . 'fe_group, single_pid, shortcut, import_id, import_source', 'tx_news_domain_model_category', 'migrate_sys_category_uid = 0 AND deleted = 0'); if ($this->databaseConnection->sql_error()) { $message = ' SQL ERROR: ' . $this->databaseConnection->sql_error(); $status = FlashMessage::ERROR; $title = 'Failed selecting old category records'; $this->messageArray[] = array($status, $title, $message); } // Create a new sys_category record for each found record $newCategoryRecords = 0; foreach ($rows as $row) { $oldUid = $row['uid']; unset($row['uid']); if (is_null($row['l10n_diffsource'])) { $row['l10n_diffsource'] = ''; } if ($this->databaseConnection->exec_INSERTquery('sys_category', $row) !== FALSE) { $newUid = $this->databaseConnection->sql_insert_id(); $this->databaseConnection->exec_UPDATEquery('tx_news_domain_model_category', 'uid=' . $oldUid, array('migrate_sys_category_uid' => $newUid)); $newCategoryRecords++; } else { $message = ' SQL ERROR: ' . $this->databaseConnection->sql_error(); $status = FlashMessage::ERROR; $title = 'Failed copying [' . $oldUid . '] ' . htmlspecialchars($row['title']) . ' to sys_category'; $this->messageArray[] = array($status, $title, $message); } } $message = 'Created ' . $newCategoryRecords . ' sys_category records'; $status = FlashMessage::INFO; $title = ''; $this->messageArray[] = array($status, $title, $message); }
/** * Checks if there are SQL errors in the last query, and if yes, throw an exception. * * @return void * @param string $sql The SQL statement * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Storage\Exception\SqlErrorException */ protected function checkSqlErrors($sql = '') { $error = $this->databaseHandle->sql_error(); if ($error !== '') { $error .= $sql ? ': ' . $sql : ''; throw new \TYPO3\CMS\Extbase\Persistence\Generic\Storage\Exception\SqlErrorException($error, 1247602160); } }
/** * Removes existing relations from the mm table from the database */ protected function deleteExistingRelations() { $deleteQuery = $this->typo3Db->DELETEquery($this->table, $this->uidLocalField . '=' . $this->localUid); $this->utilityFuncs->debugMessage('sql_request', array($deleteQuery)); $deleteResult = $this->typo3Db->sql_query($deleteQuery); if (!$deleteResult) { $this->utilityFuncs->throwException('Error in SQL query for deleting existing relations: ' . $this->typo3Db->sql_error()); } }
/** * Get records from table where the field to migrate is not empty (NOT NULL and != '') * and also not numeric (which means that it is migrated) * * @param string $table * @param string $fieldToMigrate * @param array $relationFields * @param int $limit Maximum number records to select * @throws \RuntimeException * @return array */ protected function getRecordsFromTable($table, $fieldToMigrate, $relationFields, $limit) { $fields = implode(',', array_merge($relationFields, ['uid', 'pid'])); $deletedCheck = isset($GLOBALS['TCA'][$table]['ctrl']['delete']) ? ' AND ' . $GLOBALS['TCA'][$table]['ctrl']['delete'] . '=0' : ''; $where = $fieldToMigrate . ' IS NOT NULL' . ' AND ' . $fieldToMigrate . ' != \'\'' . ' AND CAST(CAST(' . $fieldToMigrate . ' AS DECIMAL) AS CHAR) <> CAST(' . $fieldToMigrate . ' AS CHAR)' . $deletedCheck; $result = $this->database->exec_SELECTgetRows($fields, $table, $where, '', 'uid', $limit); if ($result === null) { throw new \RuntimeException('Database query failed. Error was: ' . $this->database->sql_error()); } return $result; }
/** * Query marker * * @return string */ public function queryMaker() { $output = ''; if (is_array($GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['t3lib_fullsearch'])) { $this->hookArray = $GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['t3lib_fullsearch']; } $msg = $this->procesStoreControl(); if (!$this->backendUserAuthentication->userTS['mod.']['dbint.']['disableStoreControl']) { $output .= '<h2>Load/Save Query</h2>'; $output .= '<div>' . $this->makeStoreControl() . '</div>'; $output .= $msg; } // Query Maker: $qGen = GeneralUtility::makeInstance(QueryGenerator::class); $qGen->init('queryConfig', $GLOBALS['SOBE']->MOD_SETTINGS['queryTable']); if ($this->formName) { $qGen->setFormName($this->formName); } $tmpCode = $qGen->makeSelectorTable($GLOBALS['SOBE']->MOD_SETTINGS); $output .= '<div id="query"></div>' . '<h2>Make query</h2><div>' . $tmpCode . '</div>'; $mQ = $GLOBALS['SOBE']->MOD_SETTINGS['search_query_makeQuery']; // Make form elements: if ($qGen->table && is_array($GLOBALS['TCA'][$qGen->table])) { if ($mQ) { // Show query $qGen->enablePrefix = 1; $qString = $qGen->getQuery($qGen->queryConfig); switch ($mQ) { case 'count': $qExplain = $this->databaseConnection->SELECTquery('count(*)', $qGen->table, $qString . BackendUtility::deleteClause($qGen->table)); break; default: $qExplain = $qGen->getSelectQuery($qString); if ($mQ == 'explain') { $qExplain = 'EXPLAIN ' . $qExplain; } } if (!$this->backendUserAuthentication->userTS['mod.']['dbint.']['disableShowSQLQuery']) { $output .= '<h2>SQL query</h2><div>' . $this->tableWrap(htmlspecialchars($qExplain)) . '</div>'; } $res = @$this->databaseConnection->sql_query($qExplain); if ($this->databaseConnection->sql_error()) { $out = '<p><strong>Error: <span class="text-danger">' . $this->databaseConnection->sql_error() . '</span></strong></p>'; $output .= '<h2>SQL error</h2><div>' . $out . '</div>'; } else { $cPR = $this->getQueryResultCode($mQ, $res, $qGen->table); $this->databaseConnection->sql_free_result($res); $output .= '<h2>' . $cPR['header'] . '</h2><div>' . $cPR['content'] . '</div>'; } } } return '<div class="query-builder">' . $output . '</div>'; }
/** * Db delete object * * @param int $uid Uid * @param Tx_Commerce_Dao_BasicDaoObject $object Object * * @return void */ protected function dbDelete($uid, Tx_Commerce_Dao_BasicDaoObject &$object) { $dbWhere = 'uid = ' . (int) $uid; // execute query $this->database->exec_DELETEquery($this->dbTable, $dbWhere); // any errors $error = $this->database->sql_error(); if (!empty($error)) { $this->addError(array($error, $this->database->DELETEquery($this->dbTable, $dbWhere))); } // remove object itself $object->destroy(); }
/** * Inserts a new word into the search index table and returns it's UID. * If the word already exists in the search index, just the UID is returned. * @param string $word The word to be inserted * @return int The word's UID */ function wordAdd($word) { // Attempt to load word from database $res = $this->databaseHandle->exec_SELECTquery('uid', 'tx_mmforum_wordlist', "word=" . $this->databaseHandle->fullQuoteStr($word, 'tx_mmforum_wordlist') . " " . $this->getPidQuery($this->conf)); if (!$res) { echo $this->databaseHandle->sql_error() . '<hr>'; } // If words already exists, just return the UID if ($this->databaseHandle->sql_num_rows($res) > 0) { list($uid) = $this->databaseHandle->sql_fetch_row($res); } else { // Compost insert query $insertArray = array('pid' => $this->getFirstPid($this->conf), 'word' => $word, 'metaphone' => metaphone($word)); // Execute insert query $this->databaseHandle->exec_INSERTquery('tx_mmforum_wordlist', $insertArray); $uid = $this->databaseHandle->sql_insert_id(); } return $uid; }
/** * Creates a user session record and returns its values. * * @param array $tempuser User data array * * @return array The session data for the newly created session. * @todo Define visibility */ public function createUserSession($tempuser) { if ($this->writeDevLog) { GeneralUtility::devLog('Create session ses_id = ' . $this->id, 'TYPO3\\CMS\\Core\\Authentication\\AbstractUserAuthentication'); } // Delete session entry first $this->db->exec_DELETEquery($this->session_table, 'ses_id = ' . $this->db->fullQuoteStr($this->id, $this->session_table) . ' AND ses_name = ' . $this->db->fullQuoteStr($this->name, $this->session_table)); // Re-create session entry $insertFields = $this->getNewSessionRecord($tempuser); $inserted = (bool) $this->db->exec_INSERTquery($this->session_table, $insertFields); if (!$inserted) { $message = 'Session data could not be written to DB. Error: ' . $this->db->sql_error(); GeneralUtility::sysLog($message, 'Core', GeneralUtility::SYSLOG_SEVERITY_WARNING); if ($this->writeDevLog) { GeneralUtility::devLog($message, 'TYPO3\\CMS\\Core\\Authentication\\AbstractUserAuthentication', 2); } } // Updating lastLogin_column carrying information about last login. if ($this->lastLogin_column && $inserted) { $this->db->exec_UPDATEquery($this->user_table, $this->userid_column . '=' . $this->db->fullQuoteStr($tempuser[$this->userid_column], $this->user_table), array($this->lastLogin_column => $GLOBALS['EXEC_TIME'])); } return $inserted ? $insertFields : array(); }
/** * Process not yet migrated tx_news category records to sys_category records */ protected function migrateNewsCategoryRecords() { // migrate default language category records $rows = $this->databaseConnection->exec_SELECTgetRows('uid, pid, tstamp, crdate, cruser_id, starttime, endtime, sorting, ' . 'sys_language_uid, l10n_parent, l10n_diffsource, ' . 'title, description, ' . 'fe_group, single_pid, shortcut, import_id, import_source', 'tx_news_domain_model_category', 'migrate_sys_category_uid = 0 AND deleted = 0 AND sys_language_uid = 0'); if ($this->databaseConnection->sql_error()) { $message = ' SQL ERROR: ' . $this->databaseConnection->sql_error(); $status = FlashMessage::ERROR; $title = 'Failed selecting old default language category records'; $this->messageArray[] = array($status, $title, $message); } // Create a new sys_category record for each found record in default language, then $newCategoryRecords = 0; $oldNewDefaultLanguageCategoryUidMapping = array(); foreach ($rows as $row) { $oldUid = $row['uid']; unset($row['uid']); if (is_null($row['l10n_diffsource'])) { $row['l10n_diffsource'] = ''; } if (is_null($row['description'])) { $row['description'] = ''; } if ($this->databaseConnection->exec_INSERTquery('sys_category', $row) !== false) { $newUid = $this->databaseConnection->sql_insert_id(); $oldNewDefaultLanguageCategoryUidMapping[$oldUid] = $newUid; $this->databaseConnection->exec_UPDATEquery('tx_news_domain_model_category', 'uid=' . $oldUid, array('migrate_sys_category_uid' => $newUid)); $newCategoryRecords++; } else { $message = ' SQL ERROR: ' . $this->databaseConnection->sql_error(); $status = FlashMessage::ERROR; $title = 'Failed copying [' . $oldUid . '] ' . htmlspecialchars($row['title']) . ' to sys_category'; $this->messageArray[] = array($status, $title, $message); } } // migrate non-default language category records $rows = $this->databaseConnection->exec_SELECTgetRows('uid, pid, tstamp, crdate, cruser_id, starttime, endtime, sorting, ' . 'sys_language_uid, l10n_parent, l10n_diffsource, ' . 'title, description, ' . 'fe_group, single_pid, shortcut, import_id, import_source', 'tx_news_domain_model_category', 'migrate_sys_category_uid = 0 AND deleted = 0 AND sys_language_uid != 0'); if ($this->databaseConnection->sql_error()) { $message = ' SQL ERROR: ' . $this->databaseConnection->sql_error(); $status = FlashMessage::ERROR; $title = 'Failed selecting old non-default language category records'; $this->messageArray[] = array($status, $title, $message); } foreach ($rows as $row) { $oldUid = $row['uid']; unset($row['uid']); if (is_null($row['l10n_diffsource'])) { $row['l10n_diffsource'] = ''; } if (is_null($row['description'])) { $row['description'] = ''; } // set l10n_parent if category is a localized version if (array_key_exists($row['l10n_parent'], $oldNewDefaultLanguageCategoryUidMapping)) { $row['l10n_parent'] = $oldNewDefaultLanguageCategoryUidMapping[$row['l10n_parent']]; } if ($this->databaseConnection->exec_INSERTquery('sys_category', $row) !== false) { $newUid = $this->databaseConnection->sql_insert_id(); $oldNewDefaultLanguageCategoryUidMapping[$oldUid] = $newUid; $this->databaseConnection->exec_UPDATEquery('tx_news_domain_model_category', 'uid=' . $oldUid, array('migrate_sys_category_uid' => $newUid)); $newCategoryRecords++; } else { $message = ' SQL ERROR: ' . $this->databaseConnection->sql_error(); $status = FlashMessage::ERROR; $title = 'Failed copying [' . $oldUid . '] ' . htmlspecialchars($row['title']) . ' to sys_category'; $this->messageArray[] = array($status, $title, $message); } } $message = 'Created ' . $newCategoryRecords . ' sys_category records'; $status = FlashMessage::INFO; $title = ''; $this->messageArray[] = array($status, $title, $message); }
/** * Insert into database * Does not check permissions but expects them to be verified on beforehand * * @param string $table Record table name * @param string $id "NEW...." uid string * @param array $fieldArray Array of field=>value pairs to insert. FIELDS MUST MATCH the database FIELDS. No check is done. "pid" must point to the destination of the record! * @param bool $newVersion Set to TRUE if new version is created. * @param int $suggestedUid Suggested UID value for the inserted record. See the array $this->suggestedInsertUids; Admin-only feature * @param bool $dontSetNewIdIndex If TRUE, the ->substNEWwithIDs array is not updated. Only useful in very rare circumstances! * @return int|NULL Returns ID on success. */ public function insertDB($table, $id, $fieldArray, $newVersion = false, $suggestedUid = 0, $dontSetNewIdIndex = false) { if (is_array($fieldArray) && is_array($GLOBALS['TCA'][$table]) && isset($fieldArray['pid'])) { // Do NOT insert the UID field, ever! unset($fieldArray['uid']); if (!empty($fieldArray)) { // Check for "suggestedUid". // This feature is used by the import functionality to force a new record to have a certain UID value. // This is only recommended for use when the destination server is a passive mirror of another server. // As a security measure this feature is available only for Admin Users (for now) $suggestedUid = (int) $suggestedUid; if ($this->BE_USER->isAdmin() && $suggestedUid && $this->suggestedInsertUids[$table . ':' . $suggestedUid]) { // When the value of ->suggestedInsertUids[...] is "DELETE" it will try to remove the previous record if ($this->suggestedInsertUids[$table . ':' . $suggestedUid] === 'DELETE') { // DELETE: $this->databaseConnection->exec_DELETEquery($table, 'uid=' . (int) $suggestedUid); } $fieldArray['uid'] = $suggestedUid; } $fieldArray = $this->insertUpdateDB_preprocessBasedOnFieldType($table, $fieldArray); // Execute the INSERT query: $this->databaseConnection->exec_INSERTquery($table, $fieldArray); // If succees, do...: if (!$this->databaseConnection->sql_error()) { // Set mapping for NEW... -> real uid: // the NEW_id now holds the 'NEW....' -id $NEW_id = $id; $id = $this->databaseConnection->sql_insert_id(); if (!$dontSetNewIdIndex) { $this->substNEWwithIDs[$NEW_id] = $id; $this->substNEWwithIDs_table[$NEW_id] = $table; } $newRow = array(); // Checking the record is properly saved and writing to log if ($this->enableLogging && $this->checkStoredRecords) { $newRow = $this->checkStoredRecord($table, $id, $fieldArray, 1); } // Update reference index: $this->updateRefIndex($table, $id); if ($newVersion) { if ($this->enableLogging) { $propArr = $this->getRecordPropertiesFromRow($table, $newRow); $this->log($table, $id, 1, 0, 0, 'New version created of table \'%s\', uid \'%s\'. UID of new version is \'%s\'', 10, array($table, $fieldArray['t3ver_oid'], $id), $propArr['event_pid'], $NEW_id); } } else { if ($this->enableLogging) { $propArr = $this->getRecordPropertiesFromRow($table, $newRow); $page_propArr = $this->getRecordProperties('pages', $propArr['pid']); $this->log($table, $id, 1, 0, 0, 'Record \'%s\' (%s) was inserted on page \'%s\' (%s)', 10, array($propArr['header'], $table . ':' . $id, $page_propArr['header'], $newRow['pid']), $newRow['pid'], $NEW_id); } // Clear cache for relevant pages: $this->registerRecordIdForPageCacheClearing($table, $id); } return $id; } elseif ($this->enableLogging) { $this->log($table, $id, 1, 0, 2, 'SQL error: \'%s\' (%s)', 12, array($this->databaseConnection->sql_error(), $table . ':' . $id)); } } } return null; }
/** * Returns true if a specified database table contains record rows: the existence of a table ashould have been checked before using Tx_PtExtbase_Div::dbTableExists()! * * @param string database table name to check * @param string database name of the table to check * @param DatabaseConnection database object of type \TYPO3\CMS\Core\Database\DatabaseConnection to use (e.g. $GLOBALS['TYPO3_DB'] to use TYPO3 default database) * @return boolean TRUE if specified table contains record rows, FALSE if not * @throws \PunktDe\PtExtbase\Exception\Exception if the SHOW TABLE STATUS query fails/returns false */ public static function dbTableHasRecords($table, $dbName, DatabaseConnection $dbObj) { $tableHasRecords = false; $query = 'SHOW TABLE STATUS FROM ' . $dbObj->quoteStr($dbName, $table) . ' LIKE "' . $dbObj->quoteStr($table, $table) . '"'; // exec query using TYPO3 DB API $res = $dbObj->sql_query($query); if ($res == false) { throw new \PunktDe\PtExtbase\Exception\Exception('Query failed', 1, $dbObj->sql_error()); } $a_row = $dbObj->sql_fetch_assoc($res); $dbObj->sql_free_result($res); // check number of table rows if ($a_row['Rows'] > 0) { $tableHasRecords = true; } return $tableHasRecords; }
/** * @test * * @return void */ public function sqlErrorWhenInsertIntoInexistentField() { $this->subject->exec_INSERTquery($this->testTable, ['test' => 'test']); $this->assertEquals('Unknown column \'test\' in \'field list\'', $this->subject->sql_error()); }