/**
*
*/
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());
}
