PHP DatabaseConnection::exec_SELECTcountRows方法代码示例

 /**
  * Checks if an update is needed.
  *
  * @param string &$description The description for the update
  * @return boolean TRUE if an update is needed, FALSE otherwise
  */
 public function checkForUpdate(&$description)
 {
     $description = 'Add file identifier hash to sys_file records, where it is missing. Additionally upgrade storage configurations.';
     $unhashedFileCount = $this->db->exec_SELECTcountRows('uid', 'sys_file', 'identifier_hash = "" OR folder_hash = ""');
     $unmigratedStorageCount = $this->db->exec_SELECTcountRows('uid', 'sys_file_storage', 'driver = "Local" AND configuration NOT LIKE "%caseSensitive%"');
     return $unhashedFileCount > 0 || $unmigratedStorageCount > 0;
 }

 /**
  * @param \S3b0\ProjectRegistration\Scheduler\InfoMail\Task $task
  *
  * @return bool
  */
 public function run(\S3b0\ProjectRegistration\Scheduler\InfoMail\Task $task)
 {
     $settings = unserialize($GLOBALS['TYPO3_CONF_VARS']['EXT']['extConf'][$this->extensionName]);
     $upperLimit = new \DateTime();
     $lowerLimit = new \DateTime();
     $daysLeft = $settings['warnXDaysBeforeExpireDate'];
     $sender = [$task->getSenderAddress()];
     $receiver = [$task->getReceiverAddress()];
     $subject = \TYPO3\CMS\Extbase\Utility\LocalizationUtility::translate('infomail.subject', $this->extensionName);
     $this->databaseConnection = $GLOBALS['TYPO3_DB'];
     // Upper limit (expiry) = Current date + Days left
     $upperLimit->setTimestamp($upperLimit->getTimestamp() + $daysLeft * 86400);
     // Lower limit (expiry) = Current date + Days left - Scheduler frequency
     $lowerLimit->setTimestamp($lowerLimit->getTimestamp() + $daysLeft * 86400 - $task->getExecution()->getInterval());
     $where = "date_of_expiry > '{$lowerLimit->format('Y-m-d h:i:s')}' AND date_of_expiry < '{$upperLimit->format('Y-m-d h:i:s')}'";
     if ($this->databaseConnection->exec_SELECTcountRows('*', 'tx_projectregistration_domain_model_project', $where)) {
         $expiredProjects = $this->databaseConnection->exec_SELECTgetRows('project.*, registrant.name as registrant_name, registrant.company as registrant_company', 'tx_projectregistration_domain_model_project as project join tx_projectregistration_domain_model_person as registrant on project.registrant=registrant.uid', $where);
         $list = [];
         /** @var array $expiredProject */
         foreach ($expiredProjects as $expiredProject) {
             $list[] = "#{$expiredProject['uid']} - '{$expiredProject['title']}' by {$expiredProject['registrant_name']} ({$expiredProject['registrant_company']})";
         }
         $mailContent = \TYPO3\CMS\Extbase\Utility\LocalizationUtility::translate('infomail.message', $this->extensionName, [$daysLeft, '<li>' . implode('</li><li>', $list) . '</li>']);
         /** @var \TYPO3\CMS\Core\Mail\MailMessage $mail */
         $mail = \TYPO3\CMS\Core\Utility\GeneralUtility::makeInstance(\TYPO3\CMS\Core\Mail\MailMessage::class);
         $mail->setContentType('text/html');
         /**
          * Email to sender
          */
         $mail->setFrom($sender)->setTo($receiver)->setPriority(1)->setSubject($subject)->setBody($mailContent)->send();
     }
     return true;
 }

 /**
  * Checks whether updates need to be performed
  *
  * @param string &$description The description for the update
  * @param integer &$showUpdate 0=dont show update; 1=show update and next button; 2=only show description
  * @return boolean
  */
 public function checkForUpdate(&$description, &$showUpdate = 0)
 {
     $mediaElements = $this->db->exec_SELECTcountRows('*', $GLOBALS['TYPO3_CONF_VARS']['SYS']['contentTable'], 'CType = ' . $this->db->fullQuoteStr('media', $GLOBALS['TYPO3_CONF_VARS']['SYS']['contentTable']) . ' AND pi_flexform LIKE ' . $this->db->fullQuoteStr('%<sheet index="sDEF">%', $GLOBALS['TYPO3_CONF_VARS']['SYS']['contentTable']));
     if ($mediaElements > 0) {
         $description = 'You have media elements within your installation. As the structure of the flexform changed, your data needs to be migrated.';
         $showUpdate = 1;
     } else {
         $description = 'You currently have no media elements within your installation. Therefore nothing to be migrated';
         $showUpdate = 0;
     }
     return $showUpdate > 0;
 }

 /**
  * Check if the note plugin expects output. If there are no sys_note records on the given
  * pages, the extbase bootstrap doesn't have to run the complete plugin.
  * This mechanism should increase the performance of the hooked backend modules heavily.
  *
  * @param array $arguments Arguments for the extbase plugin
  * @return bool
  */
 protected function expectOutput(array $arguments = array())
 {
     // no pids set
     if (!isset($arguments['pids']) || empty($arguments['pids']) || empty($GLOBALS['BE_USER']->user['uid'])) {
         return false;
     }
     $pidList = $this->databaseConnection->cleanIntList($arguments['pids']);
     if (empty($pidList)) {
         return false;
     }
     // check if there are records
     return $this->databaseConnection->exec_SELECTcountRows('*', 'sys_note', 'pid IN (' . $pidList . ')' . BackendUtility::deleteClause('sys_note')) > 0;
 }

 /**
  * @param $table
  * @param $fieldsString
  * @throws \Exception
  */
 protected function expectEmptyTable($table, $fieldsString = 'uid', $message = '')
 {
     if ($this->db->exec_SELECTcountRows($fieldsString, $table)) {
         $this->errorMsg = "{$message}\n  {$table} table already has data. Aborting migration.";
         throw new \Exception();
     }
 }

 /**
  * Return queries to migrate old class paths in newsletter records
  *
  * @return string[]
  */
 private function getQueriesToMigrateClassPathsInRecords()
 {
     $count = $this->databaseConnection->exec_SELECTcountRows('*', 'tx_scheduler_task', 'serialized_task_object LIKE "%Tx_Newsletter_%"');
     $count += $this->databaseConnection->exec_SELECTcountRows('*', 'tx_newsletter_domain_model_recipientlist', 'type LIKE "%Tx_Newsletter_%"');
     $count += $this->databaseConnection->exec_SELECTcountRows('*', 'tx_newsletter_domain_model_newsletter', 'plain_converter LIKE "%Tx_Newsletter_%"');
     if (!$count) {
         return [];
     }
     return ['Migrate non-namespaced classes to namespaced classes' => ["UPDATE tx_scheduler_task SET serialized_task_object = REPLACE(serialized_task_object, 'O:29:\"Tx_Newsletter_Task_SendEmails\"', 'O:33:\"Ecodev\\\\Newsletter\\\\Task\\\\SendEmails\"');", "UPDATE tx_scheduler_task SET serialized_task_object = REPLACE(serialized_task_object, 'O:31:\"Tx_Newsletter_Task_FetchBounces\"', 'O:35:\"Ecodev\\\\Newsletter\\\\Task\\\\FetchBounces\"');", "UPDATE tx_newsletter_domain_model_recipientlist SET type = REPLACE(type, 'Tx_Newsletter_Domain_Model_RecipientList_', 'Ecodev\\\\Newsletter\\\\Domain\\\\Model\\\\RecipientList\\\\');", "UPDATE tx_newsletter_domain_model_newsletter SET plain_converter = REPLACE(plain_converter, 'Tx_Newsletter_Domain_Model_PlainConverter_', 'Ecodev\\\\Newsletter\\\\Domain\\\\Model\\\\PlainConverter\\\\');"]];
 }

 /**
  * Add registrant record (if new) and return corresponding uid
  *
  * @param         $data
  * @param integer $uid
  *
  * @return integer
  */
 private function addRegistrant($data, $uid = 1)
 {
     if ((int) $data['registrant'] > 0 && $this->databaseConnection->exec_SELECTcountRows('*', 'fe_users', 'uid=' . (int) $data['registrant'])) {
         $registrant = $this->databaseConnection->exec_SELECTgetSingleRow('*', 'fe_users', 'uid=' . (int) $data['registrant']);
     } else {
         $registrant = ['name' => $data['registrant_name'], 'company' => $data['registrant_company'], 'email' => $data['registrant_email'], 'telephone' => $data['registrant_phone']];
     }
     $checkForExistingRecord = $this->databaseConnection->exec_SELECTgetSingleRow('*', 'tx_projectregistration_domain_model_person', 'name="' . $registrant['name'] . '" AND email="' . $registrant['email'] . '" AND company="' . $registrant['company'] . '" AND phone="' . $registrant['telephone'] . '"');
     if ($checkForExistingRecord) {
         return (int) $checkForExistingRecord['uid'];
     } else {
         $this->databaseConnection->exec_INSERTquery('tx_projectregistration_domain_model_person', ['username' => $registrant['username'] ?: '', 'name' => $registrant['name'] ?: '', 'first_name' => $registrant['first_name'] ?: '', 'middle_name' => $registrant['middle_name'] ?: '', 'last_name' => $registrant['last_name'] ?: '', 'company' => $registrant['company'] ?: '', 'address' => $registrant['address'] ?: '', 'zip' => $registrant['zip'] ?: '', 'city' => $registrant['city'] ?: '', 'country' => abs((int) $registrant['ecom_toolbox_country']), 'state' => abs((int) $registrant['ecom_toolbox_state']), 'email' => $registrant['email'] ?: '', 'phone' => $registrant['telephone'] ?: '', 'fax' => $registrant['fax'] ?: '', 'title' => $registrant['title'] ?: '', 'www' => $registrant['www'] ?: '', 'fe_user' => abs((int) $data['registrant'])]);
         return $uid;
     }
 }

 /**
  * Search
  *
  * @return string
  */
 public function search()
 {
     $SET = $GLOBALS['SOBE']->MOD_SETTINGS;
     $swords = $SET['sword'];
     $out = '';
     $limit = 200;
     if ($swords) {
         foreach ($GLOBALS['TCA'] as $table => $value) {
             // Get fields list
             $conf = $GLOBALS['TCA'][$table];
             // Avoid querying tables with no columns
             if (empty($conf['columns'])) {
                 continue;
             }
             $fieldsInDatabase = $this->databaseConnection->admin_get_fields($table);
             $list = array_intersect(array_keys($conf['columns']), array_keys($fieldsInDatabase));
             // Get query
             $qp = $this->databaseConnection->searchQuery(array($swords), $list, $table);
             // Count:
             $count = $this->databaseConnection->exec_SELECTcountRows('*', $table, $qp . BackendUtility::deleteClause($table));
             if ($count) {
                 $rowArr = array();
                 $res = $this->databaseConnection->exec_SELECTquery('uid,' . $conf['ctrl']['label'], $table, $qp . BackendUtility::deleteClause($table), '', '', $limit);
                 $lrow = null;
                 while ($row = $this->databaseConnection->sql_fetch_assoc($res)) {
                     $rowArr[] = $this->resultRowDisplay($row, $conf, $table);
                     $lrow = $row;
                 }
                 $this->databaseConnection->sql_free_result($res);
                 $markup = [];
                 $markup[] = '<div class="panel panel-default">';
                 $markup[] = '  <div class="panel-heading">';
                 $markup[] = $this->languageService->sL($conf['ctrl']['title'], true) . ' (' . $count . ')';
                 $markup[] = '  </div>';
                 $markup[] = '  <table class="table table-striped table-hover">';
                 $markup[] = $this->resultRowTitles($lrow, $conf, $table);
                 $markup[] = '  </table>';
                 $markup[] = '</div>';
                 $out .= implode(LF, $markup);
             }
         }
     }
     return $out;
 }

 /**
  * Returns TRUE if upgrade wizard for legacy EXT:eu_ldap records should be run.
  *
  * @return bool
  */
 protected function checkEuLdap()
 {
     $table = 'tx_euldap_server';
     $migrationField = 'tx_igldapssoauth_migrated';
     // We check the database table itself and not whether EXT:eu_ldap is loaded
     // because it may have been deactivated since it is not incompatible
     $existingTables = $this->databaseConnection->admin_get_tables();
     if (!isset($existingTables[$table])) {
         return FALSE;
     }
     // Ensure the column used to flag processed records is present
     $fields = $this->databaseConnection->admin_get_fields($table);
     if (!isset($fields[$migrationField])) {
         $alterTableQuery = 'ALTER TABLE ' . $table . ' ADD ' . $migrationField . ' tinyint(4) NOT NULL default \'0\'';
         // Method admin_query() will parse the query and make it compatible with DBAL, if needed
         $this->databaseConnection->admin_query($alterTableQuery);
     }
     $euLdapConfigurationRecords = $this->databaseConnection->exec_SELECTcountRows('*', $table, $migrationField . '=0');
     return $euLdapConfigurationRecords > 0;
 }

 /**
  * Processes the actual transformation from CSV to sys_file_references
  *
  * @param array $source
  * @param array $destination
  * @param array $configuration
  *
  * @return void
  */
 protected function migrateFilesToFal(array $source, array $destination, array $configuration)
 {
     $path = PATH_site . $configuration['sourcePath'];
     $files = GeneralUtility::trimExplode(',', $source[$configuration['sourceField']], true);
     $i = 1;
     foreach ($files as $file) {
         if (file_exists($path . $file)) {
             GeneralUtility::upload_copy_move($path . $file, $this->targetDirectory . $file);
             /** @var \TYPO3\CMS\Core\Resource\File $fileObject */
             $fileObject = $this->storage->getFile(self::FILE_MIGRATION_FOLDER . $file);
             $this->fileIndexRepository->add($fileObject);
             $count = $this->database->exec_SELECTcountRows('*', 'sys_file_reference', 'tablenames = ' . $this->database->fullQuoteStr($configuration['destinationTable'], 'sys_file_reference') . ' AND fieldname = ' . $this->database->fullQuoteStr($configuration['destinationField'], 'sys_file_reference') . ' AND uid_local = ' . $fileObject->getUid() . ' AND uid_foreign = ' . $destination['uid']);
             if (!$count) {
                 $dataArray = array('uid_local' => $fileObject->getUid(), 'tablenames' => $configuration['destinationTable'], 'uid_foreign' => $destination['uid'], 'pid' => $source['pid'], 'fieldname' => $configuration['destinationField'], 'sorting_foreign' => $i, 'table_local' => 'sys_file');
                 $this->database->exec_INSERTquery('sys_file_reference', $dataArray);
             }
         }
         $i++;
     }
 }

 /**
  * Returns the number of tuples matching the query.
  *
  * @param QueryInterface $query
  * @throws Exception\BadConstraintException
  * @return int The number of matching tuples
  */
 public function getObjectCountByQuery(QueryInterface $query)
 {
     if ($query->getConstraint() instanceof Qom\Statement) {
         throw new \TYPO3\CMS\Extbase\Persistence\Generic\Storage\Exception\BadConstraintException('Could not execute count on queries with a constraint of type TYPO3\\CMS\\Extbase\\Persistence\\Generic\\Qom\\Statement', 1256661045);
     }
     list($statementParts) = $this->getStatementParts($query);
     $fields = '*';
     if (isset($statementParts['keywords']['distinct'])) {
         $fields = 'DISTINCT ' . reset($statementParts['tables']) . '.uid';
     }
     $queryCommandParameters = $this->createQueryCommandParametersFromStatementParts($statementParts);
     $count = $this->databaseHandle->exec_SELECTcountRows($fields, $queryCommandParameters['fromTable'], $queryCommandParameters['whereClause']);
     $this->checkSqlErrors();
     if ($statementParts['offset']) {
         $count -= $statementParts['offset'];
     }
     if ($statementParts['limit']) {
         $count = min($count, $statementParts['limit']);
     }
     return (int) max(0, $count);
 }

 /**
  * Create new category MM records
  *
  * @param array $oldNewCategoryUidMapping
  */
 protected function migrateCategoryMmRecords(array $oldNewCategoryUidMapping)
 {
     $newMmCount = 0;
     $oldMmRecords = $this->databaseConnection->exec_SELECTgetRows('uid_local, uid_foreign, tablenames, sorting', 'tx_news_domain_model_news_category_mm', '');
     foreach ($oldMmRecords as $oldMmRecord) {
         $oldCategoryUid = $oldMmRecord['uid_foreign'];
         if (!empty($oldNewCategoryUidMapping[$oldCategoryUid])) {
             $newMmRecord = array('uid_local' => $oldNewCategoryUidMapping[$oldCategoryUid], 'uid_foreign' => $oldMmRecord['uid_local'], 'tablenames' => $oldMmRecord['tablenames'] ?: 'tx_news_domain_model_news', 'sorting_foreign' => $oldMmRecord['sorting'], 'fieldname' => 'categories');
             // check if relation already exists
             $foundRelations = $this->databaseConnection->exec_SELECTcountRows('uid_local', 'sys_category_record_mm', 'uid_local=' . $newMmRecord['uid_local'] . ' AND uid_foreign=' . $newMmRecord['uid_foreign'] . ' AND tablenames="' . $newMmRecord['tablenames'] . '"' . ' AND fieldname="' . $newMmRecord['fieldname'] . '"');
             if ($foundRelations === 0) {
                 $this->databaseConnection->exec_INSERTquery('sys_category_record_mm', $newMmRecord);
                 if ($this->databaseConnection->sql_affected_rows()) {
                     $newMmCount++;
                 }
             }
         }
     }
     $message = 'Created ' . $newMmCount . ' new MM relations';
     $status = FlashMessage::INFO;
     $title = '';
     $this->messageArray[] = array($status, $title, $message);
 }

 /**
  * Checks if an update is needed.
  *
  * @param string &$description The description for the update
  * @return boolean TRUE if an update is needed, FALSE otherwise
  */
 public function checkForUpdate(&$description)
 {
     $description = 'Rename the settings.list.rss.channel setting to settings.list.rss.channel.title';
     $newsPluginWithOldRssSettingCount = $this->db->exec_SELECTcountRows('uid', 'tt_content', $this->flexFormWhere);
     return $newsPluginWithOldRssSettingCount > 0;
 }

 public function purge($simulate)
 {
     $this->output->info('Purge deleted');
     $this->purgeDeleted('sys_file_reference', $simulate);
     $this->db->exec_DELETEquery('sys_file_reference', 'tablenames = \'\' OR fieldname = \'\'');
     $delete = new PreparedStatement('DELETE FROM sys_file_reference WHERE uid = ?', 'sys_file_reference');
     $this->output->info('Purge references pointing to deleted records');
     $res = $this->db->exec_SELECTquery('*', 'sys_file_reference', '');
     $pageTools = new PageRepository();
     $pageTools->init(FALSE);
     while ($row = $this->db->sql_fetch_assoc($res)) {
         $cnt = $this->db->exec_SELECTcountRows('uid', $row['tablenames'], 'uid = ' . $row['uid_foreign'] . $pageTools->enableFields($row['tablenames']));
         if (!$cnt) {
             if ($simulate) {
                 $this->output->info('Would delete reference ' . $row['uid']);
             } else {
                 $delete->execute(array($row['uid']));
                 $this->output->info('Deleted reference ' . $row['uid']);
             }
         }
     }
     $delete->free();
     $this->output->info('Purge sys_file records with no references');
     $delete = new PreparedStatement('DELETE FROM sys_file WHERE uid = ?', 'sys_file');
     $res = $this->db->exec_SELECTquery('uid', 'sys_file', 'uid NOT IN (select uid_local from sys_file_reference group by uid_local)');
     while ($row = $this->db->sql_fetch_assoc($res)) {
         if ($simulate) {
             $this->output->info('Would delete file record %s', array($row['uid']));
         } else {
             $delete->execute(array($row['uid']));
             $this->output->info('Deleted file record <b>%s</b>', array($row['uid']));
         }
     }
     $this->output->info('Purge actual files with no record');
     $prefixRegex = '/^' . preg_quote(PATH_site, '/') . '(fileadmin|uploads)/';
     $files = new \RegexIterator(new RecursiveIteratorIterator(new RecursiveDirectoryIterator(PATH_site, RecursiveDirectoryIterator::SKIP_DOTS | RecursiveDirectoryIterator::UNIX_PATHS), RecursiveIteratorIterator::LEAVES_ONLY | RecursiveIteratorIterator::CHILD_FIRST), $prefixRegex);
     $exists = new PreparedStatement('SELECT uid FROM sys_file WHERE identifier = ?', 'sys_file');
     $fileSize = 0;
     foreach ($files as $file) {
         $filename = (string) $file;
         if (!is_file($filename)) {
             continue;
         }
         $fileId = preg_replace($prefixRegex, '', $filename);
         $exists->execute(array($fileId));
         $result = $exists->fetchAll();
         if (empty($result[0]['uid'])) {
             $fileSize += filesize($filename);
             if ($simulate) {
                 $this->output->info('<i>Would delete file %s</i>', array($filename));
             } else {
                 unlink($filename);
                 $this->output->info('Delete file %s', array($filename));
             }
         }
     }
     $size = GeneralUtility::formatSize($fileSize);
     if ($simulate) {
         $this->output->info('Would delete %s of files', array($size));
         $this->output->info('Would truncate table sys_file_processedfile');
     } else {
         $this->output->info('Deleted %s of files', array($size));
         $this->db->exec_TRUNCATEquery('sys_file_processedfile');
         $this->output->info('Truncated table sys_file_processedfile');
     }
 }

 /**
  * Used by the deleteFunctions to check if there are records from disallowed tables under the pages to be deleted.
  *
  * @param string $inList List of page integers
  * @return bool Return TRUE, if permission granted
  */
 public function noRecordsFromUnallowedTables($inList)
 {
     $inList = trim($this->rmComma(trim($inList)));
     if ($inList && !$this->admin) {
         foreach ($GLOBALS['TCA'] as $table => $_) {
             $count = $this->databaseConnection->exec_SELECTcountRows('uid', $table, 'pid IN (' . $inList . ')' . BackendUtility::deleteClause($table));
             if ($count && ($this->tableReadOnly($table) || !$this->checkModifyAccessList($table))) {
                 return false;
             }
         }
     }
     return true;
 }