Example:
$tableDefinition = "age INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL";
DbHelper::createTable('tablename', $tableDefinition);
| public static createTable ( string $nameWithoutPrefix, string $createDefinition ) | ||
| $nameWithoutPrefix | string | The name of the table without any piwik prefix. |
| $createDefinition | string | The table create definition |
public static function install()
{
$tableAlert = "`idalert` INT NOT NULL PRIMARY KEY ,\n `name` VARCHAR(100) NOT NULL ,\n `login` VARCHAR(100) NOT NULL ,\n `period` VARCHAR(5) NOT NULL ,\n `report` VARCHAR(150) NOT NULL ,\n `report_condition` VARCHAR(50) ,\n `report_matched` VARCHAR(255) ,\n `metric` VARCHAR(150) NOT NULL ,\n `metric_condition` VARCHAR(50) NOT NULL ,\n `metric_matched` FLOAT NOT NULL ,\n `compared_to` SMALLINT (4) UNSIGNED NOT NULL DEFAULT 1 ,\n `email_me` BOOLEAN NOT NULL DEFAULT '0',\n `additional_emails` TEXT ,\n `phone_numbers` TEXT ";
DbHelper::createTable('alert', $tableAlert);
$tableAlertSite = "`idalert` INT( 11 ) NOT NULL ,\n `idsite` INT( 11 ) NOT NULL ,\n PRIMARY KEY ( idalert, idsite )";
DbHelper::createTable('alert_site', $tableAlertSite);
$tableAlertLog = "`idtriggered` BIGINT unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t `idalert` INT( 11 ) NOT NULL ,\n\t\t\t `idsite` INT( 11 ) NOT NULL ,\n\t\t\t `ts_triggered` timestamp NOT NULL default CURRENT_TIMESTAMP,\n\t\t\t `ts_last_sent` timestamp NULL DEFAULT NULL,\n\t\t\t `value_old` DECIMAL (20,3) DEFAULT NULL,\n\t\t\t `value_new` DECIMAL (20,3) DEFAULT NULL,\n `name` VARCHAR(100) NOT NULL ,\n\t\t\t `login` VARCHAR(100) NOT NULL ,\n\t\t\t `period` VARCHAR(5) NOT NULL ,\n\t\t\t `report` VARCHAR(150) NOT NULL ,\n\t\t\t `report_condition` VARCHAR(50) ,\n\t\t\t `report_matched` VARCHAR(1000) ,\n\t\t\t `metric` VARCHAR(150) NOT NULL ,\n\t\t\t `metric_condition` VARCHAR(50) NOT NULL ,\n\t\t\t `metric_matched` FLOAT NOT NULL ,\n\t\t\t `compared_to` SMALLINT NOT NULL DEFAULT 1 ,\n\t\t\t `email_me` BOOLEAN NOT NULL DEFAULT '0',\n\t\t\t `additional_emails` TEXT ,\n\t\t\t `phone_numbers` TEXT ,\n\t\t\t PRIMARY KEY (idtriggered)";
DbHelper::createTable('alert_triggered', $tableAlertLog);
}
/**
* Installs the plugin. Derived classes should implement this class if the plugin
* needs to:
*
* - create tables
* - update existing tables
* - etc.
*
* @throws \Exception if installation of fails for some reason.
*/
public function install()
{
try {
DbHelper::createTable('bannerstats', "\n `label` varchar(100) not null,\n `content_name_id` int not null,\n `impression` int not null,\n `interaction` int not null,\n `referrer` varchar(200),\n `target` varchar(200),\n `date` date,\n `custom_var_v1`\tvarchar(200),\n `custom_var_v2`\tvarchar(200),\n `custom_var_v3`\tvarchar(200),\n `custom_var_v4`\tvarchar(200),\n `custom_var_v5`\tvarchar(200),\n\n UNIQUE KEY `unique_combination` (`date`, `label`, `content_name_id`, `referrer`, `target`)\n ");
} catch (Exception $e) {
// ignore error if table already exists (1050 code is for 'table already exists')
if (!Db::get()->isErrNo($e, '1050')) {
throw $e;
}
}
}
public static function install()
{
$reportTable = "idreport SERIAL4 NOT NULL,\n\t\t\t\t\t idsite INTEGER NOT NULL,\n\t\t\t\t\t login VARCHAR(100) NOT NULL,\n\t\t\t\t\t description VARCHAR(255) NOT NULL,\n\t\t\t\t\t idsegment INTEGER,\n\t\t\t\t\t period VARCHAR(10) NOT NULL,\n\t\t\t\t\t hour TINYINT NOT NULL DEFAULT 0,\n\t\t\t\t\t type VARCHAR(10) NOT NULL,\n\t\t\t\t\t format VARCHAR(10) NOT NULL,\n\t\t\t\t\t reports TEXT NOT NULL,\n\t\t\t\t\t parameters TEXT NULL,\n\t\t\t\t\t ts_created TIMESTAMP WITHOUT TIME ZONE NULL,\n\t\t\t\t\t ts_last_sent TIMESTAMP WITHOUT TIME ZONE NULL,\n\t\t\t\t\t deleted TINYINT NOT NULL DEFAULT 0,\n\t\t\t\t\t PRIMARY KEY (idreport)";
DbHelper::createTable(self::$rawPrefix, $reportTable);
}
public function install()
{
$reportTable = "`idreport` INT(11) NOT NULL AUTO_INCREMENT,\n\t\t\t\t\t `idsite` INTEGER(11) NOT NULL,\n\t\t\t\t\t `login` VARCHAR(100) NOT NULL,\n\t\t\t\t\t `description` VARCHAR(255) NOT NULL,\n\t\t\t\t\t `idsegment` INT(11),\n\t\t\t\t\t `period` VARCHAR(10) NOT NULL,\n\t\t\t\t\t `hour` tinyint NOT NULL default 0,\n\t\t\t\t\t `type` VARCHAR(10) NOT NULL,\n\t\t\t\t\t `format` VARCHAR(10) NOT NULL,\n\t\t\t\t\t `reports` TEXT NOT NULL,\n\t\t\t\t\t `parameters` TEXT NULL,\n\t\t\t\t\t `ts_created` TIMESTAMP NULL,\n\t\t\t\t\t `ts_last_sent` TIMESTAMP NULL,\n\t\t\t\t\t `deleted` tinyint(4) NOT NULL default 0,\n\t\t\t\t\t PRIMARY KEY (`idreport`)";
DbHelper::createTable('report', $reportTable);
}
/**
* @throws Exception if non-recoverable error
*/
public function install()
{
$userLanguage = "login VARCHAR( 100 ) NOT NULL ,\n\t\t\t\t\t language VARCHAR( 10 ) NOT NULL ,\n\t\t\t\t\t PRIMARY KEY ( login )";
DbHelper::createTable('user_language', $userLanguage);
}
public static function install()
{
$userLanguage = "login VARCHAR( 100 ) NOT NULL ,\n\t\t\t\t\t language VARCHAR( 10 ) NOT NULL ,\n\t\t\t\t\t use_12_hour_clock TINYINT(1) NOT NULL DEFAULT 0 ,\n\t\t\t\t\t PRIMARY KEY ( login )";
DbHelper::createTable(self::$rawPrefix, $userLanguage);
}
public static function install()
{
$dashboard = "login VARCHAR( 100 ) NOT NULL ,\n\t\t\t\t\t iddashboard INT NOT NULL ,\n\t\t\t\t\t name VARCHAR( 100 ) NULL DEFAULT NULL ,\n\t\t\t\t\t layout TEXT NOT NULL,\n\t\t\t\t\t PRIMARY KEY ( login , iddashboard )";
DbHelper::createTable(self::$rawPrefix, $dashboard);
}
public function install()
{
$segmentTable = "`idsegment` INT(11) NOT NULL AUTO_INCREMENT,\n\t\t\t\t\t `name` VARCHAR(255) NOT NULL,\n\t\t\t\t\t `definition` TEXT NOT NULL,\n\t\t\t\t\t `login` VARCHAR(100) NOT NULL,\n\t\t\t\t\t `enable_all_users` tinyint(4) NOT NULL default 0,\n\t\t\t\t\t `enable_only_idsite` INTEGER(11) NULL,\n\t\t\t\t\t `auto_archive` tinyint(4) NOT NULL default 0,\n\t\t\t\t\t `ts_created` TIMESTAMP NULL,\n\t\t\t\t\t `ts_last_edit` TIMESTAMP NULL,\n\t\t\t\t\t `deleted` tinyint(4) NOT NULL default 0,\n\t\t\t\t\t PRIMARY KEY (`idsegment`)";
DbHelper::createTable('segment', $segmentTable);
}
public function install()
{
$table = "`creation_date` datetime NOT NULL ,\n `category` VARCHAR(200) NOT NULL ,\n `name` VARCHAR(200) NOT NULL ,\n `action` VARCHAR(200) NOT NULL,\n `count` INT UNSIGNED NOT NULL DEFAULT 0 ,\n `wall_time` BIGINT UNSIGNED NOT NULL DEFAULT 0 ,\n PRIMARY KEY(`category`, `name`, `action`)";
DbHelper::createTable($this->tableName, $table);
}
public static function install()
{
$userLanguage = "login VARCHAR( 100 ) NOT NULL ,\n\t\t\t\t\t language VARCHAR( 10 ) NOT NULL ,\n\t\t\t\t\t PRIMARY KEY ( login )";
DbHelper::createTable(self::$rawPrefix, $userLanguage);
}
public function install()
{
$table = "`idcustomdimension` BIGINT UNSIGNED NOT NULL,\n `idsite` BIGINT UNSIGNED NOT NULL ,\n `name` VARCHAR(100) NOT NULL ,\n `index` SMALLINT UNSIGNED NOT NULL ,\n `scope` VARCHAR(10) NOT NULL ,\n `active` TINYINT UNSIGNED NOT NULL DEFAULT 0,\n `extractions` TEXT NOT NULL DEFAULT '',\n `case_sensitive` TINYINT UNSIGNED NOT NULL DEFAULT 1,\n UNIQUE KEY idcustomdimension_idsite (`idcustomdimension`, `idsite`),\n UNIQUE KEY uniq_hash(idsite, `scope`, `index`)";
DbHelper::createTable($this->tableName, $table);
}
public static function install()
{
$segmentTable = "idsegment SERIAL4 NOT NULL,\n\t\t\t\t\t name VARCHAR(255) NOT NULL,\n\t\t\t\t\t definition TEXT NOT NULL,\n\t\t\t\t\t login VARCHAR(100) NOT NULL,\n\t\t\t\t\t enable_all_users SMALLINT NOT NULL DEFAULT 0,\n\t\t\t\t\t enable_only_idsite INTEGER NULL,\n\t\t\t\t\t auto_archive SMALLINT NOT NULL DEFAULT 0,\n\t\t\t\t\t ts_created TIMESTAMP WITHOUT TIME ZONE NULL,\n\t\t\t\t\t ts_last_edit TIMESTAMP WITHOUT TIME ZONE NULL,\n\t\t\t\t\t deleted SMALLINT NOT NULL DEFAULT 0,\n\t\t\t\t\t PRIMARY KEY (idsegment)";
DbHelper::createTable(self::$rawPrefix, $segmentTable);
}
