public function tearDown() { CM_Db_Db::exec('DROP TABLE `test`'); $paging = new CM_PagingSource_Sql('`num`', 'test'); $paging->enableCache(); $paging->clearCache(); }
public static function setUpBeforeClass() { $switzerland = CM_Db_Db::insert('cm_model_location_country', array('abbreviation' => 'CH', 'name' => 'Switzerland')); $germany = CM_Db_Db::insert('cm_model_location_country', array('abbreviation' => 'DE', 'name' => 'Germany')); $baselStadt = CM_Db_Db::insert('cm_model_location_state', array('countryId' => $switzerland, 'name' => 'Basel-Stadt')); $zuerich = CM_Db_Db::insert('cm_model_location_state', array('countryId' => $switzerland, 'name' => 'Zürich')); $basel = CM_Db_Db::insert('cm_model_location_city', array('stateId' => $baselStadt, 'countryId' => $switzerland, 'name' => 'Basel', 'lat' => 47.569535, 'lon' => 7.574063)); $winterthur = CM_Db_Db::insert('cm_model_location_city', array('stateId' => $zuerich, 'countryId' => $switzerland, 'name' => 'Winterthur', 'lat' => 47.502315, 'lon' => 8.724947)); $baselZip = CM_Db_Db::insert('cm_model_location_zip', array('cityId' => $basel, 'name' => '4056', 'lat' => 47.569535, 'lon' => 7.574063)); CM_Db_Db::insert('cm_model_location_zip', array('cityId' => $basel, 'name' => '4057', 'lat' => 47.574155, 'lon' => 7.592993)); $location = CM_Db_Db::exec('SELECT `1`.`id` `1.id`, `1`.`name` `1.name`, `2`.`id` `2.id`, `2`.`name` `2.name`, `3`.`id` `3.id`, `3`.`name` `3.name`, `3`.`lat` `3.lat`, `3`.`lon` `3.lon`, `4`.`id` `4.id`, `4`.`name` `4.name`, `4`.`lat` `4.lat`, `4`.`lon` `4.lon` FROM `cm_model_location_zip` AS `4` JOIN `cm_model_location_city` AS `3` ON(`4`.`cityId`=`3`.`id`) JOIN `cm_model_location_state` AS `2` ON(`3`.`stateId`=`2`.`id`) JOIN `cm_model_location_country` AS `1` ON(`3`.`countryId`=`1`.`id`) LIMIT 1')->fetch(); self::$_fields[CM_Model_Location::LEVEL_COUNTRY] = array('id' => (int) $location['1.id'], 'name' => $location['1.name']); self::$_fields[CM_Model_Location::LEVEL_STATE] = array('id' => (int) $location['2.id'], 'name' => $location['2.name']); self::$_fields[CM_Model_Location::LEVEL_CITY] = array('id' => (int) $location['3.id'], 'name' => $location['3.name']); self::$_fields[CM_Model_Location::LEVEL_ZIP] = array('id' => (int) $location['4.id'], 'name' => $location['4.name']); self::$_switzerlandId = $switzerland; self::$_baselStadtId = $baselStadt; self::$_baselId = $basel; self::$_baselZipId = $baselZip; }
public static function tearDownAfterClass() { CM_Db_Db::exec("DROP TABLE `indexTest_1`"); CM_Db_Db::exec("DROP TABLE `indexTest_2`"); CM_Db_Db::exec("DROP TABLE `indexTest_3`"); parent::tearDownAfterClass(); }
/** * @param string $namespace */ private function _dbToFileSql($namespace) { $namespace = (string) $namespace; $tables = CM_Db_Db::exec("SHOW TABLES LIKE ?", array(strtolower($namespace) . '_%'))->fetchAllColumn(); sort($tables); $dump = CM_Db_Db::getDump($tables, true); CM_File::create(CM_Util::getModulePath($namespace) . '/resources/db/structure.sql', $dump); }
/** * @param int $age */ protected function _deleteOlderThan($age) { $age = (int) $age; $type = $this->getType(); $deleteOlderThan = time() - $age; CM_Db_Db::exec('DELETE FROM `cm_log` WHERE `timestamp` <= ? AND `type` = ?', array($deleteOlderThan, $type)); $this->_change(); }
public function reload(CM_OutputStream_Interface $output) { $tableNames = CM_Db_Db::exec('SHOW TABLES')->fetchAllColumn(); CM_Db_Db::exec('SET foreign_key_checks = 0;'); foreach ($tableNames as $table) { CM_Db_Db::delete($table); } CM_Db_Db::exec('SET foreign_key_checks = 1;'); $this->_setInitialVersion(); }
public function setUp() { CM_Db_Db::exec('ALTER TABLE cm_model_location_ip AUTO_INCREMENT = 1'); CM_Db_Db::exec('ALTER TABLE cm_model_location_zip AUTO_INCREMENT = 1'); CM_Db_Db::exec('ALTER TABLE cm_model_location_city AUTO_INCREMENT = 1'); CM_Db_Db::exec('ALTER TABLE cm_model_location_state AUTO_INCREMENT = 1'); CM_Db_Db::exec('ALTER TABLE cm_model_location_country AUTO_INCREMENT = 1'); $this->_errorStream = new CM_OutputStream_Null(); $file = new CM_File('/CM_OutputStream_File-' . uniqid(), CM_Service_Manager::getInstance()->getFilesystems()->getTmp()); $file->truncate(); $this->_outputStream = new CM_OutputStream_File($file); }
public static function tearDownAfterClass() { parent::tearDownAfterClass(); CM_Db_Db::exec('DROP TABLE `test`'); }
<?php if (!CM_Db_Db::existsColumn('cm_streamChannelArchive_media', 'key')) { CM_Db_Db::exec("ALTER TABLE cm_streamChannelArchive_media ADD `key` VARCHAR(64) DEFAULT NULL, ADD INDEX (`key`)"); }
public function tearDown() { CM_Db_Db::exec('DROP TABLE `test`'); }
public static function tearDownAfterClass() { parent::tearDownAfterClass(); CM_Db_Db::exec("DROP TABLE `cm_modelmock`"); CM_Db_Db::exec("DROP TABLE `modelThasIsAnAssetMock`"); }
<?php if ('1.00' === CM_Db_Db::describeColumn('cm_splittestVariation_fixture', 'conversionWeight')->getDefaultValue()) { CM_Db_Db::exec('ALTER TABLE cm_splittestVariation_fixture MODIFY COLUMN conversionWeight decimal(10,2) NOT NULL DEFAULT 0'); CM_Db_Db::update('cm_splittestVariation_fixture', array('conversionWeight' => 0), array('conversionStamp' => null)); }
<?php if (!CM_Db_Db::existsColumn('cm_splittestVariation_fixture', 'id')) { CM_Db_Db::exec("\n ALTER TABLE `cm_splittestVariation_fixture`\n ADD `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,\n ADD PRIMARY KEY (`id`)\n "); }
<?php return; $rows = CM_Db_Db::execRead('SELECT `id`, `thumbnailCount` FROM cm_streamChannel_media WHERE thumbnailCount > 0'); while ($row = $rows->fetch()) { CM_Db_Db::exec('UPDATE cm_streamChannel_media SET `data`=? WHERE `id`=? ', [CM_Params::encode(['thumbnailCount' => (int) $row['thumbnailCount']], true), $row['id']]); } $rows = CM_Db_Db::execRead('SELECT `id`, `thumbnailCount` FROM cm_streamChannelArchive_media WHERE thumbnailCount > 0'); while ($row = $rows->fetch()) { CM_Db_Db::exec('UPDATE cm_streamChannelArchive_media SET `data`=? WHERE `id`=? ', [CM_Params::encode(['thumbnailCount' => (int) $row['thumbnailCount']], true), $row['id']]); }
<?php if (CM_Db_Db::describeColumn('cm_stream_publish', 'allowedUntil')->getAllowNull()) { CM_Db_Db::exec('ALTER TABLE `cm_stream_publish` CHANGE `allowedUntil` `allowedUntil` int(10) unsigned NOT NULL'); } if (CM_Db_Db::describeColumn('cm_stream_subscribe', 'allowedUntil')->getAllowNull()) { CM_Db_Db::exec('ALTER TABLE `cm_stream_subscribe` CHANGE `allowedUntil` `allowedUntil` int(10) unsigned NOT NULL'); }
<?php if (!CM_Db_Db::describeColumn('cm_stream_subscribe', 'allowedUntil')->getAllowNull()) { CM_Db_Db::exec("ALTER TABLE cm_stream_subscribe CHANGE allowedUntil allowedUntil INT(10) UNSIGNED DEFAULT NULL"); } if (!CM_Db_Db::describeColumn('cm_stream_publish', 'allowedUntil')->getAllowNull()) { CM_Db_Db::exec("ALTER TABLE cm_stream_publish CHANGE allowedUntil allowedUntil INT(10) UNSIGNED DEFAULT NULL"); }
<?php if (!CM_Db_Db::existsColumn('cm_tmp_location', 'nameFull')) { CM_Db_Db::exec('ALTER TABLE `cm_tmp_location` ADD COLUMN `nameFull` varchar(480) DEFAULT NULL AFTER `name`;'); $searchCli = new CM_Elasticsearch_Index_Cli(null, new CM_OutputStream_Stream_Output()); $searchCli->create('location'); }
protected function _upgradeIpBlocks() { if ($this->_withoutIpBlocks) { return; } $this->_streamOutput->writeln('Updating IP blocks database…'); $ipBlocksReader = $this->_getIpBlocksReader(); CM_Db_Db::exec('DROP TABLE IF EXISTS `cm_model_location_ip_new`'); CM_Db_Db::exec('CREATE TABLE `cm_model_location_ip_new` LIKE `cm_model_location_ip`'); $infoListWarning = array(); $stream = $ipBlocksReader['stream']; $count = $ipBlocksReader['lineCount']; $item = 0; $batch = []; while (false !== ($row = fgetcsv($stream))) { if ($item >= 2 && count($row) >= 3) { // Skip copyright, column names and empty lines list($ipStart, $ipEnd, $maxMind) = $row; $ipStart = (int) $ipStart; $ipEnd = (int) $ipEnd; $maxMind = (int) $maxMind; $level = null; $id = null; if (isset($this->_zipCodeIdListByMaxMind[$maxMind])) { $level = CM_Model_Location::LEVEL_ZIP; $id = $this->_zipCodeIdListByMaxMind[$maxMind]; } elseif (isset($this->_cityIdList[$maxMind])) { $level = CM_Model_Location::LEVEL_CITY; $id = $this->_cityIdList[$maxMind]; } elseif (isset($this->_regionIdListByMaxMind[$maxMind])) { $level = CM_Model_Location::LEVEL_STATE; $id = $this->_regionIdListByMaxMind[$maxMind]; } elseif (isset($this->_countryCodeListByMaxMind[$maxMind])) { $level = CM_Model_Location::LEVEL_COUNTRY; $countryCode = $this->_countryCodeListByMaxMind[$maxMind]; if (isset($this->_countryIdList[$countryCode])) { $id = $this->_countryIdList[$countryCode]; } } if ($level && $id) { $batch[] = [$id, $level, $ipStart, $ipEnd]; if (1000 === count($batch)) { CM_Db_Db::insert('cm_model_location_ip_new', ['id', 'level', 'ipStart', 'ipEnd'], $batch); $batch = []; } } else { $infoListWarning['Ignoring unknown locations'][] = $maxMind; } } $this->_printProgressCounter(++$item, $count); } if (!empty($batch)) { CM_Db_Db::insert('cm_model_location_ip_new', ['id', 'level', 'ipStart', 'ipEnd'], $batch); } unset($batch); unset($this->_countryIdList); unset($this->_countryCodeListByMaxMind); unset($this->_regionIdListByMaxMind); unset($this->_cityIdList); unset($this->_zipCodeIdListByMaxMind); $this->_printInfoList($infoListWarning, '!'); $this->_streamOutput->writeln('Checking overlapping of IP blocks…'); $result = CM_Db_Db::select('cm_model_location_ip_new', ['ipStart', 'ipEnd'], null, 'ipEnd ASC'); $ipStartPrevious = $ipEndPrevious = 0; $count = $result->getAffectedRows(); $item = 0; while (false !== ($row = $result->fetch())) { list($ipStart, $ipEnd) = array_values($row); if ($ipStart <= $ipEndPrevious) { $infoListWarning['Overlapping IP blocks'][] = "{$ipStartPrevious}-{$ipEndPrevious} and {$ipStart}-{$ipEnd}"; } $ipStartPrevious = $ipStart; $ipEndPrevious = $ipEnd; $this->_printProgressCounter(++$item, $count); } CM_Db_Db::replaceTable('cm_model_location_ip', 'cm_model_location_ip_new'); $this->_printInfoList($infoListWarning, '!'); }
/** * @param Closure|null $callbackBefore fn($version) * @param Closure|null $callbackAfter fn($version) * @return int Number of version bumps */ public function runUpdateScripts(Closure $callbackBefore = null, Closure $callbackAfter = null) { CM_Cache_Shared::getInstance()->flush(); CM_Cache_Local::getInstance()->flush(); $versionBumps = 0; foreach ($this->getUpdateScriptPaths() as $namespace => $path) { $version = $versionStart = $this->getVersion($namespace); while (true) { $version++; if (!$this->runUpdateScript($namespace, $version, $callbackBefore, $callbackAfter)) { $version--; break; } $this->setVersion($version, $namespace); } $versionBumps += $version - $versionStart; } if ($versionBumps > 0) { $db = $this->getServiceManager()->getDatabases()->getMaster()->getDatabaseName(); CM_Db_Db::exec('DROP DATABASE IF EXISTS `' . $db . '_test`'); } return $versionBumps; }
<?php if (!CM_Db_Db::existsTable('cm_model_currency')) { CM_Db_Db::exec("CREATE TABLE `cm_model_currency` (\n `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `code` varchar(3) NOT NULL DEFAULT '',\n `abbreviation` varchar(3) NOT NULL DEFAULT '',\n PRIMARY KEY (`id`),\n UNIQUE KEY `code` (`code`),\n UNIQUE KEY `string` (`abbreviation`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8;"); } if (!CM_Db_Db::existsTable('cm_model_currency_country')) { CM_Db_Db::exec("CREATE TABLE `cm_model_currency_country` (\n `currencyId` int(10) NOT NULL,\n `countryId` int(10) NOT NULL,\n UNIQUE KEY `countryId` (`countryId`)\n ) ENGINE=MyISAM DEFAULT CHARSET=utf8;"); } if (!CM_Db_Db::existsColumn('cm_user', 'currencyId')) { CM_Db_Db::exec("ALTER TABLE `cm_user` ADD `currencyId` INT(10) UNSIGNED DEFAULT NULL"); }
<?php $nameList[] = 'Your browser is no longer supported'; $nameList[] = 'We recommend upgrading to the latest Internet Explorer, Google Chrome, Firefox, or Opera. Click here for <a href="{$url}">more information</a>'; $nameList[] = 'If you are using IE 9 or later, make sure you <a href="{$url}">turn off "Compatibility View"</a>'; foreach ($nameList as $name) { $id = CM_Db_Db::select('cm_languageKey', 'id', array('name' => $name))->fetchColumn(); if ($id) { CM_Db_Db::update('cm_languageKey', array('name' => $name . '.'), array('id' => $id)); CM_Db_Db::exec('UPDATE `cm_languageValue` SET `value`= CONCAT(`value`, ".") WHERE `languageKeyId` = ' . $id); } }
<?php if (!CM_Db_Db::existsTable('`cm_tmp_location_coordinates`')) { CM_Db_Db::exec('CREATE TABLE IF NOT EXISTS `cm_tmp_location_coordinates` ( `level` tinyint(4) NOT NULL, `id` int(10) unsigned NOT NULL, `coordinates` point NOT NULL, PRIMARY KEY (`level`,`id`), SPATIAL KEY `coordinates_spatial` (`coordinates`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;'); CM_Db_Db::exec('INSERT INTO `cm_tmp_location_coordinates` (`level`,`id`,`coordinates`) SELECT `level`, `id`, POINT(lat, lon) FROM `cm_tmp_location` WHERE `lat` IS NOT NULL AND `lon` IS NOT NULL'); }
<?php if (!CM_Db_Db::existsColumn('cm_splittest', 'optimized')) { CM_Db_Db::exec(' ALTER TABLE `cm_splittest` ADD COLUMN `optimized` int(1) unsigned NOT NULL AFTER `name`'); } if (!CM_Db_Db::existsIndex('cm_splittestVariation_fixture', 'splittestVariationCreateStamp')) { CM_Db_Db::exec(' ALTER TABLE `cm_splittestVariation_fixture` ADD INDEX `splittestVariationCreateStamp` (`splittestId`,`variationId`,`createStamp`), ADD INDEX `splittestVariationConversionStamp` (`splittestId`,`variationId`,`conversionStamp`), DROP INDEX `splittestId`, DROP INDEX `createStamp`, DROP INDEX `conversionStamp`'); }
<?php if (CM_Db_Db::existsColumn('cm_stream_publish', 'userId')) { CM_Db_Db::exec('ALTER TABLE `cm_stream_publish` MODIFY `userId` int(10) unsigned DEFAULT NULL'); }
public static function createAggregation() { CM_Db_Db::truncate('cm_tmp_location'); CM_Db_Db::exec('INSERT INTO `cm_tmp_location` (`level`,`id`,`1Id`,`2Id`,`3Id`,`4Id`,`name`, `abbreviation`, `nameFull`, `lat`,`lon`) SELECT 1, `1`.`id`, `1`.`id`, NULL, NULL, NULL, `1`.`name`, `1`.`abbreviation`, CONCAT_WS(" ", `1`.`name`, `1`.`abbreviation`), NULL, NULL FROM `cm_model_location_country` AS `1` UNION SELECT 2, `2`.`id`, `1`.`id`, `2`.`id`, NULL, NULL, `2`.`name`, `2`.`abbreviation`, CONCAT_WS(" ", `2`.name, `2`.`abbreviation`, `1`.`name`, `1`.`abbreviation`), NULL, NULL FROM `cm_model_location_state` AS `2` LEFT JOIN `cm_model_location_country` AS `1` ON(`2`.`countryId`=`1`.`id`) UNION SELECT 3, `3`.`id`, `1`.`id`, `2`.`id`, `3`.`id`, NULL, `3`.`name`, NULL, CONCAT_WS(" ", `3`.`name`, `2`.`name`, `2`.`abbreviation`, `1`.`name`, `1`.`abbreviation`), `3`.`lat`, `3`.`lon` FROM `cm_model_location_city` AS `3` LEFT JOIN `cm_model_location_state` AS `2` ON(`3`.`stateId`=`2`.`id`) LEFT JOIN `cm_model_location_country` AS `1` ON(`3`.`countryId`=`1`.`id`) UNION SELECT 4, `4`.`id`, `1`.`id`, `2`.`id`, `3`.`id`, `4`.`id`, `4`.`name`, NULL, CONCAT_WS(" ", `4`.`name`, `3`.`name`, `2`.`name`, `2`.`abbreviation`, `1`.`name`, `1`.`abbreviation`), `4`.`lat`, `4`.`lon` FROM `cm_model_location_zip` AS `4` LEFT JOIN `cm_model_location_city` AS `3` ON(`4`.`cityId`=`3`.`id`) LEFT JOIN `cm_model_location_state` AS `2` ON(`3`.`stateId`=`2`.`id`) LEFT JOIN `cm_model_location_country` AS `1` ON(`3`.`countryId`=`1`.`id`)'); CM_Db_Db::truncate('cm_tmp_location_coordinates'); CM_Db_Db::exec('INSERT INTO `cm_tmp_location_coordinates` (`level`,`id`,`coordinates`) SELECT 3, `id`, POINT(lat, lon) FROM `cm_model_location_city` WHERE `lat` IS NOT NULL AND `lon` IS NOT NULL UNION SELECT 4, `id`, POINT(lat, lon) FROM `cm_model_location_zip` WHERE `lat` IS NOT NULL AND `lon` IS NOT NULL'); }
public static function setUpBeforeClass() { CM_Db_Db::exec("CREATE TABLE IF NOT EXISTS `cm_paging_modelabstracttest_modelmock` (\n\t\t\t\t`id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,\n\t\t\t\t`foo` VARCHAR(32)\n\t\t\t) ENGINE=MyISAM AUTO_INCREMENT=" . rand(1, 1000) . " DEFAULT CHARSET=utf8;\n\t\t"); CM_Db_Db::exec("CREATE TABLE IF NOT EXISTS `cm_paging_modelabstracttest_modelmock2` (\n\t\t\t\t`id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,\n\t\t\t\t`bar` VARCHAR(32)\n\t\t\t) ENGINE=MyISAM AUTO_INCREMENT=" . rand(1, 1000) . " DEFAULT CHARSET=utf8;\n\t\t"); }
<?php if (CM_Db_Db::existsTable('cm_model_location_city_ip') && !CM_Db_Db::existsTable('cm_model_location_ip')) { CM_Db_Db::exec('RENAME TABLE `cm_model_location_city_ip` TO `cm_model_location_ip`'); } if (CM_Db_Db::existsTable('cm_model_location_ip')) { if (CM_Db_Db::existsIndex('cm_model_location_ip', 'cityId')) { CM_Db_Db::exec('DROP INDEX `cityId` ON `cm_model_location_ip`'); } if (CM_Db_Db::existsColumn('cm_model_location_ip', 'cityId') && !CM_Db_Db::existsColumn('cm_model_location_ip', 'id')) { CM_Db_Db::exec('ALTER TABLE `cm_model_location_ip` CHANGE COLUMN `cityId` `id` int(10) unsigned NOT NULL '); } if (!CM_Db_Db::existsColumn('cm_model_location_ip', 'level')) { CM_Db_Db::exec('ALTER TABLE `cm_model_location_ip` ADD COLUMN `level` int(10) unsigned NOT NULL AFTER `id`'); } if (CM_Db_Db::existsColumn('cm_model_location_ip', 'level')) { CM_Db_Db::update('cm_model_location_ip', array('level' => CM_Model_Location::LEVEL_CITY), array('level' => 0)); } if (CM_Db_Db::existsTable('cm_model_location_country_ip')) { $result = CM_Db_Db::select('cm_model_location_country_ip', array('countryId', 'ipStart', 'ipEnd')); foreach ($result->fetchAll() as $row) { CM_Db_Db::insert('cm_model_location_ip', array('id' => $row['countryId'], 'level' => CM_Model_Location::LEVEL_COUNTRY, 'ipStart' => $row['ipStart'], 'ipEnd' => $row['ipEnd'])); } CM_Db_Db::exec('DROP TABLE `cm_model_location_country_ip`'); } }
<?php // To be run in prod beforehand: // pt-online-schema-change --execute --charset=utf8 --user=root --ask-pass --database=skadate t=cm_user --alter='CHANGE `site` `site` int(10) unsigned DEFAULT NULL' if ('INT' !== CM_Db_Db::describeColumn('cm_user', 'site')->getType()) { CM_Db_Db::exec("ALTER TABLE `cm_user` CHANGE `site` `site` int(10) unsigned DEFAULT NULL;"); }
/** * @return array */ public static function getStats() { return CM_Db_Db::exec("SELECT `preferenceId`, COUNT(*) AS `count`, `value` FROM `cm_user_preference` GROUP BY `preferenceId`, `value`")->fetchAllTree(); }
/** * @param CM_Model_LanguageKey $languageKey * @return CM_Model_LanguageKey */ protected static function _replaceWithExisting(CM_Model_LanguageKey $languageKey) { $name = $languageKey->getName(); $languageKeyIdList = CM_Db_Db::select('cm_model_languagekey', 'id', array('name' => $name), 'id ASC')->fetchAllColumn(); if (count($languageKeyIdList) > 1) { $languageKeyId = array_shift($languageKeyIdList); CM_Db_Db::exec("DELETE FROM `cm_model_languagekey` WHERE `name` = ? AND `id` != ?", array($name, $languageKeyId)); $languageKey = new self($languageKeyId); } return $languageKey; }