-
Notifications
You must be signed in to change notification settings - Fork 1
/
table_structure_updates.txt
144 lines (120 loc) · 8.74 KB
/
table_structure_updates.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
CUID now used for linking into forum profile:
ALTER TABLE `lg_users` CHANGE `cuid` `cuid` VARCHAR( 45 ) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '';
Add scenario_user_data:
CREATE TABLE `lg_scenario_user_data` (
`scenario_id` int(10) unsigned NOT NULL DEFAULT '0',
`user_id` int(10) unsigned NOT NULL DEFAULT '0',
`data` varchar(2048) COLLATE latin1_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`scenario_id`,`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
======================================================================================================
24.02.09: resource filenames are unique (use insert_update in admin2.php to add and update a resource)
======================================================================================================
ALTER TABLE `lg_resources` DROP PRIMARY KEY,
ADD PRIMARY KEY USING BTREE(`filename`),
ADD INDEX `hash`(`hash`);
======================================================================================================
23.02.09: save user_is_deleted also in lg_scores and lg_game_players to to save joins and improve performance
======================================================================================================
ALTER TABLE `lg_scores` ADD COLUMN `user_is_deleted` BOOLEAN NOT NULL DEFAULT 0 AFTER `rank_order`;
ALTER TABLE `lg_game_players` ADD COLUMN `user_is_deleted` BOOLEAN NOT NULL DEFAULT 0 AFTER `ip`;
UPDATE lg_scores sc JOIN lg_users u ON sc.user_id = u.id
SET sc.user_is_deleted = 1
WHERE u.is_deleted = 1;
UPDATE lg_game_players gp JOIN lg_users u ON gp.user_id = u.id
SET gp.user_is_deleted = 1
WHERE u.is_deleted = 1;
======================================================================================================
23.02.09: add max player count per scenario and league
======================================================================================================
ALTER TABLE `lg_league_scenarios` ADD COLUMN `max_player_count` INTEGER UNSIGNED NOT NULL AFTER `scenario_id`;
======================================================================================================
23.02.09: keep old revisions of debug-counter, compare current with last revision
======================================================================================================
ALTER TABLE `lg_debug_counter` ADD COLUMN `revision` INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER `mean_duration`, ADD INDEX `revision`(`revision`);
ALTER TABLE `lg_debug_counter` DROP PRIMARY KEY, ADD PRIMARY KEY USING BTREE(`name`, `revision`);
======================================================================================================
17.02.09: recalculate clan-statistics (only) in cronjob
======================================================================================================
ALTER TABLE `lg_clans` ADD COLUMN `cronjob_update_stats` BOOLEAN NOT NULL COMMENT 'if flag is set: update stats in cronjob' AFTER `description`;
======================================================================================================
04.01.09: add saving of record-streams
======================================================================================================
ALTER TABLE `lg_games` ADD COLUMN `record_status` ENUM('none','incomplete','complete') NOT NULL DEFAULT 'none' AFTER `settle_rank`;
ALTER TABLE `lg_games` ADD COLUMN `record_filename` VARCHAR(45) NOT NULL DEFAULT '' AFTER `record_status`;
INSERT INTO lg_admin_permissions (SELECT DISTINCT user_id, "game" AS part, "download_record" AS method FROM `lg_admin_permissions`);
======================================================================================================
26.09.08: speed up game-list displaying settle-scores
======================================================================================================
ALTER TABLE `lg_games` ADD COLUMN `settle_score` DECIMAL(10,0) UNSIGNED NOT NULL DEFAULT 0 AFTER `seed`,
ADD COLUMN `settle_rank` INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER `settle_score`;
======================================================================================================
02.09.08: speed up game-list-filter by scenairo-id
======================================================================================================
ALTER TABLE `lg_games` ADD INDEX `scenario_id` ( `scenario_id` );
ALTER TABLE `lg_game_scores` ADD INDEX `game_id` ( `game_id` );
======================================================================================================
01.09.08: sha-hash for scenarios
======================================================================================================
ALTER TABLE `league2`.`lg_scenario_versions` ADD COLUMN `hash_sha` VARCHAR(40) NOT NULL DEFAULT '' AFTER `scenario_id`;
======================================================================================================
30.08.08-01.09.08: add frame, settle-rank, add resource-table, insert admin-persmissions for resource
add product_string/id, add seed
======================================================================================================
ALTER TABLE `league2`.`lg_games` ADD COLUMN `frame` INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER `is_paused`;
ALTER TABLE `league2`.`lg_games` ADD INDEX `frame`(`frame`);
ALTER TABLE `league2`.`lg_game_scores` ADD COLUMN `settle_rank` INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER `old_player_score`;
CREATE TABLE `league2`.`lg_resources` (
`filename` VARCHAR(255) NOT NULL DEFAULT '',
`hash` VARCHAR(40) NOT NULL DEFAULT '',
PRIMARY KEY(`hash`)
)
ENGINE = MYISAM;
INSERT INTO lg_admin_permissions (SELECT DISTINCT user_id, "resource" AS part, '' AS method FROM
`lg_admin_permissions`);
ALTER TABLE `league2`.`lg_products` ADD COLUMN `product_string` VARCHAR(2) NOT NULL DEFAULT '' AFTER `version`,
ADD UNIQUE `product_string`(`product_string`);
ALTER TABLE `league2`.`lg_game_reference_cache` ADD COLUMN `product_string` VARCHAR(2) NOT NULL DEFAULT '' AFTER `date_created`,
ADD INDEX `product_string`(`product_string`);
ALTER TABLE `league2`.`lg_games` ADD COLUMN `seed` INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER `frame`;
======================================================================================================
10.04.08: restict scenarios played in a league
======================================================================================================
CREATE TABLE `league2`.`lg_league_scenarios` (
`league_id` INTEGER UNSIGNED NOT NULL,
`scenario_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY(`league_id`, `scenario_id`),
INDEX `scenario_id`(`scenario_id`)
)
ENGINE = MYISAM;
======================================================================================================
07.04.08: speed up ranking, clan-ranking and scenario-list
======================================================================================================
ALTER TABLE `league2`.`lg_scores` ADD COLUMN `rank_order` INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER `duration`;
ALTER TABLE `league2`.`lg_clan_scores` ADD COLUMN `rank_order` INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER `duration`;
ALTER TABLE `lg_scores` ADD INDEX `league_id_rank_order` ( `league_id` , `rank_order` );
ALTER TABLE `lg_clan_scores` ADD INDEX `league_id_rank_order` ( `league_id` , `rank_order` );
ALTER TABLE `lg_scores` DROP INDEX `league_id`;
ALTER TABLE `lg_clan_scores` DROP INDEX `league_id`;
ALTER TABLE `lg_scenarios` ADD INDEX `games_count` ( `games_count` ) ;
======================================================================================================
06.04.08: manually add league-games: give every admin permission to do so
======================================================================================================
INSERT INTO lg_admin_permissions (SELECT DISTINCT user_id, "game" AS part, "add" AS method FROM
`lg_admin_permissions`);
INSERT INTO lg_admin_permissions (SELECT DISTINCT user_id, "game" AS part, "add2" AS method FROM
`lg_admin_permissions`);
======================================================================================================
04.04.08: cuid-bans: add table, give every admin permission to use cuid-bans
======================================================================================================
CREATE TABLE `lg_cuid_bans` (
`cuid` varchar(8) collate latin1_general_ci NOT NULL default '',
`date_created` int(10) NOT NULL default '0',
`date_until` int(10) NOT NULL default '0',
`reason` text collate latin1_general_ci NOT NULL,
`comment` text collate latin1_general_ci NOT NULL,
PRIMARY KEY (`cuid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO lg_admin_permissions (SELECT DISTINCT user_id, "cuid_ban" AS part, '' AS method FROM
`lg_admin_permissions`);
======================================================================================================