Skip to content

Commit

Permalink
Browse files Browse the repository at this point in the history
Make last set of DB migrations more readable, defensive [ci skip]
  • Loading branch information
ginatrapani committed Jul 4, 2012
1 parent a646de8 commit 67ae631
Show file tree
Hide file tree
Showing 2 changed files with 9 additions and 19 deletions.
4 changes: 2 additions & 2 deletions webapp/install/sql/build-db_mysql.sql
@@ -1,6 +1,6 @@
--
-- ThinkUp Database Creation Script
-- Auto-generated by thinkup/extras/scripts/migratedb script on 2012-06-17
-- Auto-generated by thinkup/extras/scripts/migratedb script on 2012-07-03
--

ALTER DATABASE DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Expand Down Expand Up @@ -498,7 +498,7 @@ CREATE TABLE tu_users (
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Service user details.';


-- Dump completed on 2012-06-17 0:53:47
-- Dump completed on 2012-07-03 18:14:47

--
-- Insert DB Version
Expand Down
24 changes: 7 additions & 17 deletions webapp/install/sql/mysql_migrations/2012-06-16_insights.sql
@@ -1,4 +1,4 @@
CREATE TABLE tu_insights (
CREATE TABLE IF NOT EXISTS tu_insights (
id int(11) NOT NULL AUTO_INCREMENT COMMENT 'Internal unique ID.',
instance_id int(11) NOT NULL COMMENT 'Instance ID.',
slug varchar(100) NOT NULL COMMENT 'Identifier for a type of statistic.',
Expand All @@ -11,24 +11,14 @@ CREATE TABLE tu_insights (
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Insights for a given service user.';

-- Add follows index to speed up instances table update at the end of crawl:
-- DROP INDEX user_id; ADD UNIQUE INDEX network_follower_user(network,follower_id,user_id);
-- ALTER TABLE tu_follows ADD INDEX user_id ( user_id , network , active );

CREATE TABLE tu_follows_1_0_8 (
user_id varchar(30) NOT NULL COMMENT 'User ID on a particular service who has been followed.',
follower_id varchar(30) NOT NULL COMMENT 'User ID on a particular service who has followed user_id.',
last_seen timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last time this relationship was seen on the originating network.',
first_seen timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'First time this relationship was seen on the originating network.',
active int(11) NOT NULL DEFAULT '1' COMMENT 'Whether or not the relationship is active (1 if so, 0 if not.)',
network varchar(20) NOT NULL DEFAULT 'twitter' COMMENT 'Originating network in lower case, i.e., twitter or facebook.',
debug_api_call varchar(255) NOT NULL COMMENT 'Developer-only field for storing the API URL source of this data point.',
UNIQUE KEY network_follower_user (network,follower_id,user_id),
KEY active (network,active,last_seen),
KEY network (network,last_seen),
KEY user_id (user_id,network,active)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Service user follow and friend relationships.';
DROP TABLE IF EXISTS tu_follows_1_0_8;
CREATE TABLE tu_follows_1_0_8 LIKE tu_follows;
ALTER TABLE tu_follows_1_0_8 DROP INDEX user_id;
ALTER TABLE tu_follows_1_0_8 ADD UNIQUE INDEX network_follower_user(network,follower_id,user_id);
ALTER TABLE tu_follows_1_0_8 ADD INDEX user_id ( user_id, network, active );

INSERT IGNORE INTO tu_follows_1_0_8 (SELECT * FROM tu_follows)#rollback=1;
INSERT IGNORE INTO tu_follows_1_0_8 (SELECT * FROM tu_follows)#rollback=5;

RENAME TABLE tu_follows TO tu_follows_1_0_7;

Expand Down

0 comments on commit 67ae631

Please sign in to comment.