Migrations

What is a migration?

Migration is a script modifying the tables in the database to bring its structure into accordance with the current Keitaro version.

Is migration launch mandatory?

Yes, otherwise there may be failures in Keitaro working process.

Migrations for versions 3 and 4

Find the database modifications necessary for upgrade to a new release below.

All the sql commands are launched in phpMyAdmin, SQL tab. Launching one command at a time is highly recommended, as some of them might be not relevant for your database. Then there will be an error displayed in phpMyAdmin. When upgrading from an old TDS, launch the commands in ascending order, for example, 4.3 → 4.4 section first, then 4.4.3 → 4.5.

If table prefix is something other than standard keitaro_, change it to the same one in the commands before launching. For example, if your prefix is “kflint_”, table name “keitaro_monitoring_history” will become “kflint_monitoring_history”.

4.6.6 → 4.6.9

CREATE TABLE IF NOT EXISTS `schema_version` (
  `version` int(10) unsigned NOT NULL,
  PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4.6 → 4.6.1

ALTER TABLE `keitaro_ips` CHANGE `ip` `ip` INT( 10 ) UNSIGNED NOT NULL;

4.4.8 → 4.6

ALTER TABLE `keitaro_streams` ADD `operator_filter` ENUM( '', 'allow', 'block' ) DEFAULT NULL AFTER `mobile_filter` ,
ADD `operator_filter_value` TEXT DEFAULT NULL AFTER `operator_filter`;

The next migration should be executed for all the keitaro_stats_* tables: from the current date (in “YearMonthDay” format) to the last one. No need to update the old ones.

ALTER TABLE `keitaro_stats_дата` ADD `operator` VARCHAR( 50 ) NULL

4.4.3 → 4.5

CREATE TABLE IF NOT EXISTS `keitaro_monitoring_history` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `level` varchar(10) NOT NULL,
  `stream_id` int(10) unsigned NOT NULL,
  `trigger_id` int(11) NOT NULL,
  `message` text NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `trigger_id` (`trigger_id`),
  KEY `stream_id` (`stream_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8  AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `keitaro_triggers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `stream_id` int(10) unsigned NOT NULL,
  `target` varchar(50) NOT NULL,
  `condition` varchar(100) NOT NULL,
  `selected_page` varchar(255) DEFAULT NULL,
  `pattern` varchar(255) DEFAULT NULL,
  `action` varchar(100) NOT NULL,
  `interval` int(10) unsigned NOT NULL,
  `next_run_at` int(10) unsigned DEFAULT NULL,
  `alternative_urls` text,
  `grab_from_page` varchar(250) DEFAULT NULL,
  `av_settings` text,
  `reverse` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `enabled` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `enabled` (`enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8  AUTO_INCREMENT=1;
ALTER TABLE `keitaro_archive_referers` DROP INDEX `referer`, ADD INDEX `referer` ( `group_id` , `referer` );
ALTER TABLE `keitaro_archive_se` DROP INDEX `se`, ADD INDEX `se` ( `group_id` , `se` );
DROP TABLE `keitaro_uniq_states`;
DROP TABLE `keitaro_stats`;

4.3 → 4.4

ALTER TABLE `keitaro_ips` DROP INDEX `date`;
ALTER TABLE IGNORE `keitaro_ips` ADD UNIQUE (`date` ,`group_id` ,`ip` ,`stream_id`);
ALTER TABLE `keitaro_archive_keywords` DROP INDEX `group_id`;
ALTER TABLE `keitaro_archive_keywords` DROP INDEX `date`;
ALTER TABLE `keitaro_archive_keywords` ADD `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
ALTER TABLE `keitaro_archive_keywords` ADD INDEX (`date` ,`group_id`, `keyword`);
ALTER TABLE `keitaro_archive_sources` ADD `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
ALTER TABLE `keitaro_archive_sources` DROP INDEX `date`;
ALTER TABLE `keitaro_archive_sources` DROP INDEX `source`;
ALTER TABLE `keitaro_archive_sources` ADD INDEX ( `date` , `group_id` , `source` );
ALTER TABLE `keitaro_archive_referers` ADD `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
ALTER TABLE `keitaro_archive_se` ADD `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
ALTER TABLE `keitaro_archive_streams` ADD `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
ALTER TABLE `keitaro_archive_countries` ADD `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

3.3.9 → 4.0.0

CREATE TABLE IF NOT EXISTS `keitaro_shards` (
`id` INT unsigned NOT NULL AUTO_INCREMENT,
`date` VARCHAR( 8 ) NOT NULL ,
PRIMARY KEY (`id`),
INDEX ( `date` )
) ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `keitaro_ips` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ip` bigint(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `stream_id` int(11) NOT NULL DEFAULT '0',
  `date` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `date` (`date`,`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
ALTER TABLE `keitaro_archive_streams` CHANGE `stream_id` `stream_id` INT UNSIGNED NOT NULL

3.0.6 → 3.1

CREATE TABLE IF NOT EXISTS `keitaro_archive_streams` (
  `stream_id` varchar(250) NOT NULL,
  `group_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `raws` int(11) NOT NULL,
  `uniq` int(11) NOT NULL DEFAULT '0',
  KEY `group` (`date`,`group_id`),
  KEY `stream_id` (`stream_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.0.2 → 3.0.3

ALTER TABLE `keitaro_stats` DROP INDEX `is_mobile`;
ALTER TABLE `keitaro_stats` DROP INDEX `referer`;
ALTER TABLE `keitaro_stats` DROP INDEX `group_id`;
ALTER TABLE `keitaro_stats` DROP INDEX `group_id_2`;
ALTER TABLE `keitaro_stats` DROP INDEX `in_se_archive`;
ALTER TABLE `keitaro_stats` DROP INDEX `in_countries_archive`;
ALTER TABLE `keitaro_stats` DROP INDEX `in_referers_archive`;
ALTER TABLE `keitaro_stats` DROP INDEX `ip`;
ALTER TABLE `keitaro_stats` DROP INDEX `is_bot`;
ALTER TABLE `keitaro_stats` DROP INDEX `in_source_archive`;
ALTER TABLE `keitaro_stats` DROP INDEX `in_visits_archive`;
ALTER TABLE `keitaro_stats` DROP INDEX `in_keywords_archive`;
ALTER TABLE `keitaro_stats` DROP INDEX `hour`;

2.5.1.12 → 3.0

ALTER TABLE `keitaro_streams` ADD `cities_filter` ENUM( '', 'allow', 'block' ) NULL DEFAULT NULL AFTER `countries_filter_value` ,
ADD `cities_filter_value` TEXT NULL DEFAULT NULL AFTER `cities_filter` ;
ALTER TABLE  `keitaro_stats` ADD `city` VARCHAR(60) NULL DEFAULT NULL

2.5.1.3 → 2.5.1.4

ALTER TABLE `keitaro_uniq_states` CHANGE `ip` `ip` BIGINT( 11 ) NOT NULL;

2.5.1.2 → 2.5.1.3

ALTER TABLE `keitaro_stats` ENGINE = InnoDB;
ALTER TABLE `keitaro_archive_keywords` ENGINE = InnoDB;
 
ALTER TABLE `keitaro_streams` CHANGE `checker_interval` `checker_interval` FLOAT NOT NULL DEFAULT '0'
 
ALTER TABLE `keitaro_archive_visits` ADD `mobile` INT UNSIGNED NOT NULL ;