PHP DatabaseCompatibilityUtil类代码示例

 /**
  * @param string $tableAliasName
  * @param string $columnName
  * @return string
  */
 public static function makeColumnNameWithTableAlias($tableAliasName, $columnName)
 {
     assert('is_string($tableAliasName)');
     assert('is_string($columnName)');
     $quote = DatabaseCompatibilityUtil::getQuote();
     return $quote . $tableAliasName . $quote . '.' . $quote . $columnName . $quote;
 }

 /**
  * Searching Many To Many on a custom field (dropdown)
  */
 public function testManyManyCustomFieldSearch()
 {
     $quote = DatabaseCompatibilityUtil::getQuote();
     $searchAttributeData = array();
     $searchAttributeData['clauses'] = array(1 => array('attributeName' => 'opportunities', 'relatedModelData' => array('attributeName' => 'stage', 'relatedAttributeName' => 'value', 'operatorType' => 'oneOf', 'value' => array(0 => 'something'))));
     $searchAttributeData['structure'] = '1';
     //Build the query 'where' and 'joins'. Confirm they are as expected
     $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Contact');
     $where = ModelDataProviderUtil::makeWhere('Contact', $searchAttributeData, $joinTablesAdapter);
     $compareWhere = "({$quote}customfield{$quote}.{$quote}value{$quote} IN('something'))";
     $this->assertEquals($compareWhere, $where);
     $this->assertEquals(0, $joinTablesAdapter->getFromTableJoinCount());
     $this->assertEquals(3, $joinTablesAdapter->getLeftTableJoinCount());
     $leftTables = $joinTablesAdapter->getLeftTablesAndAliases();
     $this->assertEquals('contact_opportunity', $leftTables[0]['tableName']);
     $this->assertEquals('opportunity', $leftTables[1]['tableName']);
     $this->assertEquals('customfield', $leftTables[2]['tableName']);
     $this->assertTrue($joinTablesAdapter->getSelectDistinct());
     //Now test that the subsetSQL query produced is correct.
     $subsetSql = Contact::makeSubsetOrCountSqlQuery('contact', $joinTablesAdapter, 1, 5, $where, null, false, $joinTablesAdapter->getSelectDistinct());
     $compareSubsetSql = "select distinct {$quote}contact{$quote}.{$quote}id{$quote} id ";
     $compareSubsetSql .= "from {$quote}contact{$quote} ";
     $compareSubsetSql .= "left join {$quote}contact_opportunity{$quote} on ";
     $compareSubsetSql .= "{$quote}contact_opportunity{$quote}.{$quote}contact_id{$quote} = {$quote}contact{$quote}.{$quote}id{$quote} ";
     $compareSubsetSql .= "left join {$quote}opportunity{$quote} on ";
     $compareSubsetSql .= "{$quote}opportunity{$quote}.{$quote}id{$quote} = {$quote}contact_opportunity{$quote}.{$quote}opportunity_id{$quote} ";
     $compareSubsetSql .= "left join {$quote}customfield{$quote} on ";
     $compareSubsetSql .= "{$quote}customfield{$quote}.{$quote}id{$quote} = {$quote}opportunity{$quote}.{$quote}stage_customfield_id{$quote} ";
     $compareSubsetSql .= "where " . $compareWhere . ' ';
     $compareSubsetSql .= 'limit 5 offset 1';
     $this->assertEquals($compareSubsetSql, $subsetSql);
     //Make sure the sql runs properly.
     $data = Contact::getSubset($joinTablesAdapter, 0, 5, $where, null, null, $joinTablesAdapter->getSelectDistinct());
 }

 protected static function makeSqlQuery($searchAttributeData)
 {
     $quote = DatabaseCompatibilityUtil::getQuote();
     $where = null;
     $selectDistinct = false;
     $autoresponderTableName = Autoresponder::getTableName('Autoresponder');
     $autoresponderItemTableName = AutoresponderItem::getTableName('AutoresponderItem');
     $emailMessageTableName = EmailMessage::getTableName('EmailMessage');
     $sentDateTimeColumnName = EmailMessage::getColumnNameByAttribute('sentDateTime');
     $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Autoresponder');
     $selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct);
     $queuedEmailsSelectPart = "sum(CASE WHEN {$quote}{$emailMessageTableName}{$quote}.{$quote}{$sentDateTimeColumnName}" . $quote . " = '0000-00-00 00:00:00' OR {$quote}{$emailMessageTableName}{$quote}" . ".{$quote}{$sentDateTimeColumnName}{$quote} IS NULL THEN 1 ELSE 0 END)";
     // Not Coding Standard
     $sentEmailsSelectPart = "sum(CASE WHEN {$quote}{$emailMessageTableName}{$quote}.{$quote}{$sentDateTimeColumnName}" . $quote . " > '0000-00-00 00:00:00' THEN 1 ELSE 0 END)";
     $uniqueOpensSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_OPEN);
     $uniqueClicksSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_CLICK);
     $bouncedSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_BOUNCE);
     $optedOutSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_UNSUBSCRIBE);
     static::addEmailMessageDayDateClause($selectQueryAdapter, $sentDateTimeColumnName);
     static::addEmailMessageFirstDayOfWeekDateClause($selectQueryAdapter, $sentDateTimeColumnName);
     static::addEmailMessageFirstDayOfMonthDateClause($selectQueryAdapter, $sentDateTimeColumnName);
     $selectQueryAdapter->addNonSpecificCountClause();
     $selectQueryAdapter->addClauseByQueryString($queuedEmailsSelectPart, static::QUEUED);
     $selectQueryAdapter->addClauseByQueryString($sentEmailsSelectPart, static::SENT);
     $selectQueryAdapter->addClauseByQueryString("count((" . $uniqueOpensSelectPart . "))", static::UNIQUE_OPENS);
     $selectQueryAdapter->addClauseByQueryString("count((" . $uniqueClicksSelectPart . "))", static::UNIQUE_CLICKS);
     $selectQueryAdapter->addClauseByQueryString("count((" . $bouncedSelectPart . "))", static::BOUNCED);
     $selectQueryAdapter->addClauseByQueryString("count((" . $optedOutSelectPart . "))", static::UNSUBSCRIBED);
     $joinTablesAdapter->addLeftTableAndGetAliasName($autoresponderItemTableName, 'id', $autoresponderTableName, 'autoresponder_id');
     $joinTablesAdapter->addLeftTableAndGetAliasName($emailMessageTableName, 'emailmessage_id', $autoresponderItemTableName, 'id');
     $where = RedBeanModelDataProvider::makeWhere('Autoresponder', $searchAttributeData, $joinTablesAdapter);
     $sql = SQLQueryUtil::makeQuery($autoresponderTableName, $selectQueryAdapter, $joinTablesAdapter, null, null, $where);
     return $sql;
 }

 public function testRun()
 {
     $quote = DatabaseCompatibilityUtil::getQuote();
     $super = User::getByUsername('super');
     Yii::app()->user->userModel = $super;
     $box = EmailBox::resolveAndGetByName(EmailBox::NOTIFICATIONS_NAME);
     $folder = EmailFolder::getByBoxAndType($box, EmailFolder::TYPE_SENT);
     //Create 2 sent notifications, and set one with a date over a week ago (8 days ago) for the modifiedDateTime
     $emailMessage = EmailMessageTestHelper::createDraftSystemEmail('My Email Message', $super);
     $emailMessage->folder = $folder;
     $saved = $emailMessage->save();
     $this->assertTrue($saved);
     $modifiedDateTime = DateTimeUtil::convertTimestampToDbFormatDateTime(time() - 60 * 60 * 24 * 8);
     $sql = "Update item set modifieddatetime = '" . $modifiedDateTime . "' where id = " . $emailMessage->getClassId('Item');
     ZurmoRedBean::exec($sql);
     $emailMessage2 = EmailMessageTestHelper::createDraftSystemEmail('My Email Message 2', $super);
     $emailMessage2->folder = $folder;
     $saved = $emailMessage2->save();
     $this->assertTrue($saved);
     $this->assertEquals(2, EmailMessage::getCount());
     $job = new ClearSentNotificationsEmailJob();
     $this->assertTrue($job->run());
     $emailMessages = EmailMessage::getAll();
     $this->assertEquals(1, count($emailMessages));
     $this->assertEquals($emailMessage2->id, $emailMessages[0]->id);
 }

 protected static function getGroupBySqlPart($tableName, $columnName)
 {
     assert('$tableName');
     assert('$columnName');
     $quote = DatabaseCompatibilityUtil::getQuote();
     return "{$quote}{$tableName}{$quote}.{$quote}{$columnName}{$quote}";
 }

 /**
  * @param string $attribute
  * @return string
  */
 protected function resolveTimeZoneAdjustmentForACalculatedDateTimeModifier($attribute)
 {
     $resolvedAttribute = $this->modelToReportAdapter->resolveRealAttributeName($attribute);
     if ($this->modelToReportAdapter->getRealModelAttributeType($resolvedAttribute) == 'DateTime') {
         return DatabaseCompatibilityUtil::makeTimeZoneAdjustmentContent();
     }
 }

 public function testRun()
 {
     $quote = DatabaseCompatibilityUtil::getQuote();
     //Create 2 imports, and set one with a date over a week ago (8 days ago) for the modifiedDateTime
     $import = new Import();
     $serializedData['importRulesType'] = 'ImportModelTestItem';
     $import->serializedData = serialize($serializedData);
     $this->assertTrue($import->save());
     ImportTestHelper::createTempTableByFileNameAndTableName('importAnalyzerTest.csv', $import->getTempTableName(), true);
     $modifiedDateTime = DateTimeUtil::convertTimestampToDbFormatDateTime(time() - 60 * 60 * 24 * 8);
     $sql = "Update item set modifieddatetime = '" . $modifiedDateTime . "' where id = " . $import->getClassId('Item');
     ZurmoRedBean::exec($sql);
     $staleImportId = $import->id;
     $import2 = new Import();
     $serializedData['importRulesType'] = 'ImportModelTestItem';
     $import2->serializedData = serialize($serializedData);
     $this->assertTrue($import2->save());
     ImportTestHelper::createTempTableByFileNameAndTableName('importAnalyzerTest.csv', $import2->getTempTableName(), true);
     $this->assertEquals(2, Import::getCount());
     $tableExists = ZurmoRedBean::$writer->doesTableExist($import->getTempTableName());
     $this->assertTrue($tableExists);
     $job = new ImportCleanupJob();
     $this->assertTrue($job->run());
     $tableExists = ZurmoRedBean::$writer->doesTableExist($import->getTempTableName());
     $this->assertFalse($tableExists);
     $imports = Import::getAll();
     $this->assertEquals(1, count($imports));
     $this->assertEquals($import2->id, $imports[0]->id);
 }

 protected static function generateCampaignItems($campaign, $pageSize)
 {
     if ($pageSize == null) {
         $pageSize = self::DEFAULT_CAMPAIGNITEMS_TO_CREATE_PAGE_SIZE;
     }
     $contacts = array();
     $quote = DatabaseCompatibilityUtil::getQuote();
     $marketingListMemberTableName = RedBeanModel::getTableName('MarketingListMember');
     $campaignItemTableName = RedBeanModel::getTableName('CampaignItem');
     $sql = "select {$quote}{$marketingListMemberTableName}{$quote}.{$quote}contact_id{$quote} from {$quote}{$marketingListMemberTableName}{$quote}";
     // Not Coding Standard
     $sql .= "left join {$quote}{$campaignItemTableName}{$quote} on ";
     $sql .= "{$quote}{$campaignItemTableName}{$quote}.{$quote}contact_id{$quote} ";
     $sql .= "= {$quote}{$marketingListMemberTableName}{$quote}.{$quote}contact_id{$quote}";
     $sql .= "AND {$quote}{$campaignItemTableName}{$quote}.{$quote}campaign_id{$quote} = " . $campaign->id . " ";
     $sql .= "where {$quote}{$marketingListMemberTableName}{$quote}.{$quote}marketinglist_id{$quote} = " . $campaign->marketingList->id;
     $sql .= " and {$quote}{$campaignItemTableName}{$quote}.{$quote}id{$quote} is null limit " . $pageSize;
     $ids = R::getCol($sql);
     foreach ($ids as $contactId) {
         $contacts[] = Contact::getById((int) $contactId);
     }
     if (!empty($contacts)) {
         //todo: if the return value is false, then we might need to catch that since it didn't go well.
         CampaignItem::registerCampaignItemsByCampaign($campaign, $contacts);
         if (count($ids) < $pageSize) {
             return true;
         }
     } else {
         return true;
     }
     return false;
 }

 public function __construct($modelClassName, $attributeName)
 {
     parent::__construct($modelClassName, $attributeName);
     assert('is_string($attributeName)');
     $this->maxLength = DatabaseCompatibilityUtil::getMaxVarCharLength();
     $this->messageCountData[static::EMAIL_TOO_LONG] = 0;
 }

 /**
  * Gets matched models. Currently only supports if the attribute is 'name'
  * @param $value
  * @param null|int $pageSize
  * @return array
  */
 protected function getMatchedModels($value, $pageSize)
 {
     $matchedModels = array();
     $penultimateModelClassName = $this->penultimateModelClassName;
     $classToEvaluate = new ReflectionClass($penultimateModelClassName);
     if ($penultimateModelClassName != null && $classToEvaluate->isSubclassOf('Item') && $penultimateModelClassName::isAnAttribute('name')) {
         $matchedModels = $penultimateModelClassName::getSubset(null, null, $pageSize, 'name' . " = '" . DatabaseCompatibilityUtil::escape($value) . "'");
     }
     return $matchedModels;
 }

 public static function deleteRowsFromAllTablesExceptLog()
 {
     assert('RedBeanDatabase::isSetup()');
     $tableNames = DatabaseCompatibilityUtil::getAllTableNames();
     foreach ($tableNames as $tableName) {
         if ($tableName != 'log') {
             R::exec("delete from {$tableName}");
         }
     }
 }

 protected function checkService()
 {
     $passed = true;
     if (DatabaseCompatibilityUtil::isDatabaseStrictMode('mysql', $this->form->databaseHostname, $this->form->databaseUsername, $this->form->databasePassword, $this->form->databasePort)) {
         $this->message = Zurmo::t('InstallModule', 'Database is in strict mode.');
         $passed = false;
     } else {
         $this->message = Zurmo::t('InstallModule', 'Database is not in strict mode.');
     }
     return $passed;
 }

 /**
  * For a give User name, run a partial search by
  * full name and retrieve user models.
  *
  */
 public static function getUsersByPartialFullName($partialName, $pageSize)
 {
     assert('is_string($partialName)');
     assert('is_int($pageSize)');
     $personTableName = RedBeanModel::getTableName('Person');
     $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('User');
     $joinTablesAdapter->addFromTableAndGetAliasName($personTableName, "{$personTableName}_id");
     $fullNameSql = DatabaseCompatibilityUtil::concat(array('person.firstname', '\' \'', 'person.lastname'));
     $where = "      (person.firstname      like lower('{$partialName}%') or " . "       person.lastname       like lower('{$partialName}%') or " . "       {$fullNameSql} like lower('{$partialName}%')) ";
     return User::getSubset($joinTablesAdapter, null, $pageSize, $where, "person.firstname, person.lastname");
 }

 /**
  * Given a related model type, a related model id, and a page size, return a list of comment models.
  * @param string $type
  * @param integer $relatedId
  * @param integer $pageSize
  */
 public static function getCommentsByRelatedModelTypeIdAndPageSize($type, $relatedId, $pageSize)
 {
     assert('is_string($type)');
     assert('is_int($relatedId)');
     assert('is_int($pageSize) || $pageSize = null');
     $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Comment');
     $orderByColumnName = RedBeanModelDataProvider::resolveSortAttributeColumnName('Comment', $joinTablesAdapter, 'createdDateTime');
     $where = "relatedmodel_type = '" . DatabaseCompatibilityUtil::escape(strtolower($type)) . "' AND relatedmodel_id = '" . DatabaseCompatibilityUtil::escape($relatedId) . "'";
     $orderBy = $orderByColumnName . ' desc';
     return self::getSubset($joinTablesAdapter, null, $pageSize, $where, $orderBy);
 }

 /**
  * Given an id of a currency model, determine if any currency values are using this currency.
  * @return true if at least one currency value model is using this currency.
  * @param integer $currencyId
  */
 public static function isCurrencyInUseById($currencyId)
 {
     assert('is_int($currencyId)');
     $columnName = RedBeanModel::getForeignKeyName('CurrencyValue', 'currency');
     $quote = DatabaseCompatibilityUtil::getQuote();
     $where = "{$quote}{$columnName}{$quote} = '{$currencyId}'";
     $count = CurrencyValue::getCount(null, $where);
     if ($count > 0) {
         return true;
     }
     return false;
 }