Get the SQL to create a specific Piwik table
| public static getTableCreateSql ( string $tableName ) : string | ||
| $tableName | string | Unprefixed table name. |
| return | string | SQL |
private function ensureTargetTableExists($archiveTable)
{
$data = $this->targetDb->getAdapter()->fetchCol("SHOW TABLES LIKE '" . $this->targetDb->prefixTable($archiveTable) . "'");
if (count($data) == 0) {
$tableType = strpos($archiveTable, 'blob') ? 'archive_blob' : 'archive_numeric';
$sql = PiwikDbHelper::getTableCreateSql($tableType);
$sql = str_replace($tableType, $archiveTable, $sql);
$sql = str_replace($this->sourceDb->prefixTable($tableType), $this->targetDb->prefixTable($tableType), $sql);
$this->targetDb->getAdapter()->query($sql);
}
}
public function setUp()
{
parent::setUp();
// recreate log_visit/log_link_visit_action/log_conversion tables w/o any dimensions
$tablesToRecreate = array('log_visit', 'log_link_visit_action', 'log_conversion');
foreach ($tablesToRecreate as $table) {
Db::exec("DROP TABLE `" . Common::prefixTable($table) . "`");
$tableCreateSql = DbHelper::getTableCreateSql($table);
Db::exec($tableCreateSql);
}
$visitDimensions = array($this->getMockVisitDimension("test_visit_col_1", "INTEGER(10) UNSIGNED NOT NULL"), $this->getMockVisitDimension("test_visit_col_2", "VARCHAR(32) NOT NULL"));
$actionDimensions = array($this->getMockActionDimension("test_action_col_1", "VARCHAR(32) NOT NULL"), $this->getMockActionDimension("test_action_col_2", "INTEGER(10) UNSIGNED DEFAULT NULL"));
$conversionDimensions = array($this->getMockConversionDimension("test_conv_col_1", "FLOAT DEFAULT NULL"), $this->getMockConversionDimension("test_conv_col_2", "VARCHAR(32) NOT NULL"));
$this->columnsUpdater = new ColumnsUpdater($visitDimensions, $actionDimensions, $conversionDimensions);
$this->tableColumnsCache = array();
}
public function createArchiveTable($tableName, $tableNamePrefix)
{
$db = Db::get();
$sql = DbHelper::getTableCreateSql($tableNamePrefix);
// replace table name template by real name
$tableNamePrefix = Common::prefixTable($tableNamePrefix);
$sql = str_replace($tableNamePrefix, $tableName, $sql);
try {
$db->query($sql);
} catch (Exception $e) {
// accept mysql error 1050: table already exists, throw otherwise
if (!$db->isErrNo($e, '1050')) {
throw $e;
}
}
}
protected static function createArchiveTablesIfAbsent($tableName, $tableNamePrefix)
{
if (is_null(self::$tablesAlreadyInstalled)) {
self::refreshTableList();
}
if (!in_array($tableName, self::$tablesAlreadyInstalled)) {
$db = Db::get();
$sql = DbHelper::getTableCreateSql($tableNamePrefix);
// replace table name template by real name
$tableNamePrefix = Common::prefixTable($tableNamePrefix);
$sql = str_replace($tableNamePrefix, $tableName, $sql);
try {
$db->query($sql);
} catch (Exception $e) {
// accept mysql error 1050: table already exists, throw otherwise
if (!$db->isErrNo($e, '1050')) {
throw $e;
}
}
self::$tablesAlreadyInstalled[] = $tableName;
}
}
/**
* Truncates all tables then inserts the data in $tables into each
* mapped table.
*
* @param array $tables Array mapping table names with arrays of row data.
*/
protected static function restoreDbTables($tables)
{
// truncate existing tables
DbHelper::truncateAllTables();
// insert data
$existingTables = DbHelper::getTablesInstalled();
foreach ($tables as $table => $rows) {
// create table if it's an archive table
if (strpos($table, 'archive_') !== false && !in_array($table, $existingTables)) {
$tableType = strpos($table, 'archive_numeric') !== false ? 'archive_numeric' : 'archive_blob';
$createSql = DbHelper::getTableCreateSql($tableType);
$createSql = str_replace(Common::prefixTable($tableType), $table, $createSql);
Db::query($createSql);
}
if (empty($rows)) {
continue;
}
$rowsSql = array();
$bind = array();
foreach ($rows as $row) {
$values = array();
foreach ($row as $value) {
if (is_null($value)) {
$values[] = 'NULL';
} else {
if (is_numeric($value)) {
$values[] = $value;
} else {
if (!ctype_print($value)) {
$values[] = "x'" . bin2hex(substr($value, 1)) . "'";
} else {
$values[] = "?";
$bind[] = $value;
}
}
}
}
$rowsSql[] = "(" . implode(',', $values) . ")";
}
$sql = "INSERT INTO `{$table}` VALUES " . implode(',', $rowsSql);
Db::query($sql, $bind);
}
}
protected function getPartitionTableSql($tableName, $generatedTableName)
{
$config = Config::getInstance();
$prefix = $config->database['tables_prefix'];
$sql = DbHelper::getTableCreateSql($tableName);
$sql = str_replace($prefix . $tableName, $generatedTableName, $sql);
$sql = str_replace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $sql);
return $sql;
}
public function createArchiveTable($tableName, $tableNamePrefix)
{
$sql = DbHelper::getTableCreateSql($tableNamePrefix);
// replace table name template by real name
$tableNamePrefix = Common::prefixTable($tableNamePrefix);
$sql = str_replace($tableNamePrefix, $tableName, $sql);
try {
$this->db->query($sql);
} catch (Exception $e) {
// accept mysql error 1050: table already exists, throw otherwise
if (!$this->db->isErrNo($e, '1050')) {
throw $e;
}
}
try {
if (ArchiveTableCreator::NUMERIC_TABLE === ArchiveTableCreator::getTypeFromTableName($tableName)) {
$sequence = Factory::getDAO('sequence');
$sequence->setName($tableName);
$sequence->create();
}
} catch (Exception $e) {
}
}
