root/dev/common/includes/autoupgrade.php @ 201

Revision 201, 18.6 KB (checked in by exi, 16 years ago)

Attempt to fix various issues with the ContractListTable?-sql summary code.
The raw killmail now shows the time left for classified kills.
Added code to autoupgrade.php to add indexes to kb3_inv_crp and kb3_inv_all.

Line 
1<?php
2require_once("db.php");
3function check_commenttable()
4{
5    $qry = new DBQuery();
6    $query = 'select count(*) from kb3_comments';
7    $result = mysql_query($query);
8    if ($result)
9    {
10        check_commenttablerow();
11        return;
12    }
13    $query = 'CREATE TABLE `kb3_comments` (
14`ID` INT NOT NULL AUTO_INCREMENT ,
15`kll_id` INT NOT NULL ,
16`comment` TEXT NOT NULL ,
17`name` TINYTEXT NOT NULL ,
18`posttime` TIMESTAMP DEFAULT \'0000-00-00 00:00:00\' NOT NULL,
19PRIMARY KEY ( `ID` )
20) TYPE = MYISAM';
21    $qry->execute($query);
22}
23
24function check_commenttablerow()
25{
26    $qry = new DBQuery();
27    $query = 'select posttime from kb3_comments limit 1';
28    $result = mysql_query($query);
29    if ($result)
30    {
31        $query = 'ALTER TABLE `kb3_comments` CHANGE `ID` `id` INT( 11 ) NOT NULL AUTO_INCREMENT';
32        $qry->execute($query);
33        return;
34    }
35    $query = 'ALTER TABLE `kb3_comments` ADD `posttime` TIMESTAMP DEFAULT \'0000-00-00 00:00:00\' NOT NULL';
36    $qry->execute($query);
37}
38
39function check_shipvaltable()
40{
41    $qry = new DBQuery();
42    $query = 'select count(*) from kb3_ships_values';
43    $result = mysql_query($query);
44    if ($result)
45    {
46        return;
47    }
48    $query = 'CREATE TABLE `kb3_ships_values` (
49`shp_id` INT( 11 ) NOT NULL ,
50`shp_value` BIGINT( 4 ) NOT NULL ,
51PRIMARY KEY ( `shp_id` )
52) TYPE = MYISAM ;';
53    $qry->execute($query);
54    $qry->execute('UPDATE kb3_ships set shp_class = 8 WHERE shp_id=257 limit 1');
55    $qry->execute('UPDATE kb3_ships set shp_class = 8 WHERE shp_id=252 limit 1');
56    $qry->execute('UPDATE kb3_ships set shp_class = 4 WHERE shp_id=266 limit 1');
57    $qry->execute('UPDATE kb3_ships set shp_class = 4 WHERE shp_id=275 limit 1');
58    $qry->execute('UPDATE kb3_ships set shp_class = 4 WHERE shp_id=272 limit 1');
59    $qry->execute('UPDATE kb3_ships set shp_class = 4 WHERE shp_id=246 limit 1');
60    $qry->execute('UPDATE kb3_ships set shp_class = 4 WHERE shp_id=249 limit 1');
61    $qry->execute('UPDATE kb3_ships set shp_techlevel = 2 where shp_class = 22');
62}
63
64function check_invdetail()
65{
66    $qry = new DBQuery();
67    $query = 'select ind_sec_status from kb3_inv_detail limit 1';
68    $qry->execute($query);
69    $len = mysql_field_len($qry->resid_,0);
70    if ($len == 4)
71    {
72        $query = 'ALTER TABLE `kb3_inv_detail` CHANGE `ind_sec_status` `ind_sec_status` VARCHAR(5)';
73        $qry->execute($query);
74    }
75}
76
77function check_pilots()
78{
79    $qry = new DBQuery();
80    $query = 'select plt_name from kb3_pilots limit 1';
81    $qry->execute($query);
82    $len = mysql_field_len($qry->resid_,0);
83    if ($len == 32)
84    {
85        $query = 'ALTER TABLE `kb3_pilots` CHANGE `plt_name` `plt_name` VARCHAR(64) NOT NULL';
86        $qry->execute($query);
87    }
88}
89
90function check_contracts()
91{
92    $qry = new DBQuery();
93    $query = 'select ctd_sys_id from kb3_contract_details limit 1';
94    $result = mysql_query($query);
95    if ($result)
96    {
97        return;
98    }
99    $qry->execute('ALTER TABLE `kb3_contract_details` ADD `ctd_sys_id` INT(11) NOT NULL DEFAULT \'0\'');
100
101    $qry->execute('SHOW columns from `kb3_contract_details` like \'ctd_ctr_id\'');
102    $arr = $qry->getRow();
103    if ($arr['Key'] == 'PRI')
104    {
105        return;
106    }
107    $qry->execute('ALTER TABLE `kb3_contract_details` ADD INDEX (`ctd_ctr_id`) ');
108}
109function check_index()
110{
111    check_index_invcrp();
112    check_index_invall();
113    $qry = new DBQuery();
114    $qry->execute('SHOW columns from kb3_item_types like \'itt_id\'');
115    $arr = $qry->getRow();
116    if ($arr['Key'] == 'PRI')
117    {
118        return;
119    }
120    $qry->execute('ALTER TABLE `kb3_item_types` ADD PRIMARY KEY ( `itt_id` ) ');
121}
122function check_index_invcrp()
123{
124    $qry = new DBQuery();
125    $qry->execute('SHOW columns from kb3_inv_crp like \'inc_kll_id\'');
126    $arr = $qry->getRow();
127    if ($arr['Key'] == 'MUL')
128    {
129        return;
130    }
131    $qry->execute('ALTER TABLE `kb3_inv_crp` ADD INDEX ( `inc_kll_id` ) ');
132}
133function check_index_invall()
134{
135    $qry = new DBQuery();
136    $qry->execute('SHOW columns from kb3_inv_all like \'ina_kll_id\'');
137    $arr = $qry->getRow();
138    if ($arr['Key'] == 'MUL')
139    {
140        return;
141    }
142    $qry->execute('ALTER TABLE `kb3_inv_all` ADD INDEX ( `ina_kll_id` ) ');
143}
144function check_tblstrct1()
145{
146    $qry = new DBQuery();
147    $query = 'select shp_description from kb3_ships limit 1';
148    $result = mysql_query($query);
149    if (!$result)
150    {
151        return;
152    }
153    $query = 'ALTER TABLE `kb3_ships` DROP `shp_description`';
154    $qry->execute($query);
155}
156function check_tblstrct2()
157{
158    $qry = new DBQuery();
159    $query = 'select itm_description from kb3_items limit 1';
160    $result = mysql_query($query);
161    if (!$result)
162    {
163        return;
164    }
165    $query = 'ALTER TABLE `kb3_items` DROP `itm_description`';
166    $qry->execute($query);
167}
168function check_tblstrct3()
169{
170    $qry = new DBQuery();
171    $query = 'select Value from kb3_items limit 1';
172    $result = mysql_query($query);
173    if ($result)
174    {
175        $query = 'ALTER TABLE `kb3_items` CHANGE `Value` `itm_value` INT( 11 ) NOT NULL DEFAULT \'0\'';
176        $qry->execute($query);
177    }
178}
179function check_tblstrct4()
180{
181    $qry = new DBQuery();
182    $query = 'select itm_value from kb3_items limit 1';
183    $result = mysql_query($query);
184    if ($result)
185    {
186        return;
187    }
188    $query = 'ALTER TABLE `kb3_items` ADD `itm_value` INT( 11 ) NOT NULL DEFAULT \'0\'';
189    $qry->execute($query);
190    $qry->execute('ALTER TABLE `kb3_items` CHANGE `itm_externalid` `itm_externalid` INT( 11 ) NOT NULL DEFAULT \'0\'');
191}
192
193function check_tblstrct5()
194{
195    $qry = new DBQuery();
196    $query = 'select count(*) from kb3_standings';
197    $result = mysql_query($query);
198    if ($result)
199    {
200        $query = 'select count(*) from kb3_standings where sta_from=1 and sta_to=1 and sta_from_type=\'a\' and
201                  sta_to_type=\'c\'';
202        $result = mysql_query($query);
203        if ($result)
204        {
205            return;
206        }
207        $qry->execute('drop table kb3_standings');
208    }
209$query = 'CREATE TABLE `kb3_standings` (
210  `sta_from` int(11) NOT NULL default \'0\',
211  `sta_to` int(11) NOT NULL default \'0\',
212  `sta_from_type` enum(\'a\',\'c\') NOT NULL default \'a\',
213  `sta_to_type` enum(\'a\',\'c\') NOT NULL default \'a\',
214  `sta_value` float NOT NULL default \'0\',
215  `sta_comment` varchar(200) NOT NULL,
216  KEY `sta_from` (`sta_from`)
217) TYPE=MyISAM;';
218    $qry->execute($query);
219}
220
221function chk_kb3_items()
222{
223    $qry = new DBQuery();
224    $query = 'select count(*) from kb3_item_types where itt_id = 787';
225    $result = mysql_query($query);
226    $result = mysql_fetch_array($result);
227    if ($result['cnt'] == 1)
228    {
229        return;
230    }
231    $queries = "
232        INSERT IGNORE INTO `kb3_item_types` VALUES (737, 'Gas Cloud Harvester',1);
233        INSERT IGNORE INTO `kb3_item_types` VALUES (762, 'Inertia Stabilizer',3);
234        INSERT IGNORE INTO `kb3_item_types` VALUES (763, 'Nanofiber Internal Structure',3);
235        INSERT IGNORE INTO `kb3_item_types` VALUES (764, 'Overdrive Injector System',3);
236        INSERT IGNORE INTO `kb3_item_types` VALUES (765, 'Expanded Cargohold',3);
237        INSERT IGNORE INTO `kb3_item_types` VALUES (766, 'Power Diagnostic System',3);
238        INSERT IGNORE INTO `kb3_item_types` VALUES (767, 'Capacitor Power Relay',3);
239        INSERT IGNORE INTO `kb3_item_types` VALUES (768, 'Capacitor Flux Coil',3);
240        INSERT IGNORE INTO `kb3_item_types` VALUES (769, 'Reactor Control Unit',3);
241        INSERT IGNORE INTO `kb3_item_types` VALUES (770, 'Shield Flux Coil',3);
242        INSERT IGNORE INTO `kb3_item_types` VALUES (771, 'Missile Launcher Heavy Assault',1);
243        INSERT IGNORE INTO `kb3_item_types` VALUES (738, 'Cyber Armor', 0);
244        INSERT IGNORE INTO `kb3_item_types` VALUES (740, 'Cyber Electronics', 0);
245        INSERT IGNORE INTO `kb3_item_types` VALUES (741, 'Cyber Engineering', 0);
246        INSERT IGNORE INTO `kb3_item_types` VALUES (742, 'Cyber Gunnery', 0);
247        INSERT IGNORE INTO `kb3_item_types` VALUES (743, 'Cyber Industry', 0);
248        INSERT IGNORE INTO `kb3_item_types` VALUES (744, 'Cyber Leadership', 0);
249        INSERT IGNORE INTO `kb3_item_types` VALUES (745, 'Cyber Learning', 0);
250        INSERT IGNORE INTO `kb3_item_types` VALUES (746, 'Cyber Missile', 0);
251        INSERT IGNORE INTO `kb3_item_types` VALUES (747, 'Cyber Navigation', 0);
252        INSERT IGNORE INTO `kb3_item_types` VALUES (748, 'Cyber Science', 0);
253        INSERT IGNORE INTO `kb3_item_types` VALUES (749, 'Cyber Shields', 0);
254        INSERT IGNORE INTO `kb3_item_types` VALUES (772, 'Assault Missile', 0);
255        INSERT IGNORE INTO `kb3_item_types` VALUES (773, 'Rig Armor', 5);
256        INSERT IGNORE INTO `kb3_item_types` VALUES (774, 'Rig Shield', 5);
257        INSERT IGNORE INTO `kb3_item_types` VALUES (775, 'Rig Energy Weapon', 5);
258        INSERT IGNORE INTO `kb3_item_types` VALUES (776, 'Rig Hybrid Weapon', 5);
259        INSERT IGNORE INTO `kb3_item_types` VALUES (777, 'Rig Projectile Weapon', 5);
260        INSERT IGNORE INTO `kb3_item_types` VALUES (778, 'Rig Drones', 5);
261        INSERT IGNORE INTO `kb3_item_types` VALUES (779, 'Rig Launcher', 5);
262        INSERT IGNORE INTO `kb3_item_types` VALUES (780, 'Rig Electronics', 5);
263        INSERT IGNORE INTO `kb3_item_types` VALUES (781, 'Rig Energy Grid', 5);
264        INSERT IGNORE INTO `kb3_item_types` VALUES (782, 'Rig Astronautic', 5);
265        INSERT IGNORE INTO `kb3_item_types` VALUES (783, 'Cyber X Specials', 0);
266        INSERT IGNORE INTO `kb3_item_types` VALUES (785, 'Script', 0);
267        INSERT IGNORE INTO `kb3_item_types` VALUES (786, 'Rig Electronics Superiority', 5);
268        INSERT IGNORE INTO `kb3_item_types` VALUES (722, 'Advanced Hybrid Ammo Blueprint', 0);
269        INSERT IGNORE INTO `kb3_item_types` VALUES (723, 'Tractor Beam Blueprint', 0);
270        INSERT IGNORE INTO `kb3_item_types` VALUES (724, 'Implant Blueprints', 0);
271        INSERT IGNORE INTO `kb3_item_types` VALUES (725, 'Advanced Projectile Ammo Blueprint', 0);
272        INSERT IGNORE INTO `kb3_item_types` VALUES (726, 'Advanced Frequency Crystal Blueprint', 0);
273        INSERT IGNORE INTO `kb3_item_types` VALUES (727, 'Mining Crystal Blueprint', 0);
274        INSERT IGNORE INTO `kb3_item_types` VALUES (728, 'Decryptors - Amarr', 0);
275        INSERT IGNORE INTO `kb3_item_types` VALUES (729, 'Decryptors - Minmatar', 0);
276        INSERT IGNORE INTO `kb3_item_types` VALUES (730, 'Decryptors - Gallente', 0);
277        INSERT IGNORE INTO `kb3_item_types` VALUES (731, 'Decryptors - Caldari', 0);
278        INSERT IGNORE INTO `kb3_item_types` VALUES (732, 'Decryptors - Sleepers', 0);
279        INSERT IGNORE INTO `kb3_item_types` VALUES (733, 'Decryptors - Yan Jung', 0);
280        INSERT IGNORE INTO `kb3_item_types` VALUES (734, 'Decryptors - Takmahl', 0);
281        INSERT IGNORE INTO `kb3_item_types` VALUES (735, 'Decryptors - Talocan', 0);
282        INSERT IGNORE INTO `kb3_item_types` VALUES (754, 'Salvaged Materials', 0);
283        INSERT IGNORE INTO `kb3_item_types` VALUES (784, 'Large Collidable Ship', 0);
284        INSERT IGNORE INTO `kb3_item_types` VALUES (787, 'Rig Blueprint', 0);
285        ";
286    $query = explode("\n", $queries);
287    foreach ($query as $querystring)
288    {
289        if ($string = trim(str_replace(');', ')', $querystring)))
290        {
291            $qry->execute($string);
292        }
293    }
294}
295function chk_kb3_items2()
296{
297    $qry = new DBQuery();
298    $query = 'select itm_externalid from kb3_items where itm_name = \'Repair Drone\'';
299    $result = mysql_query($query);
300    $result = mysql_fetch_array($result);
301    if ($result['itm_externalid'] == '9871')
302    {
303        return;
304    }
305    $queries = "
306            update kb3_items set itm_externalid='10246' where itm_name='Mining Drone I';
307            update kb3_items set itm_externalid='10248' where itm_name='Mining Drone - Improved UNUSED';
308            update kb3_items set itm_externalid='10250' where itm_name='Mining Drone II';
309            update kb3_items set itm_externalid='1187' where itm_name='Mining Drone - Elite';
310            update kb3_items set itm_externalid='1201' where itm_name='Proximity Drone';
311            update kb3_items set itm_externalid='1202' where itm_name='Wasp I';
312            update kb3_items set itm_externalid='15508' where itm_name='Civilian Mining Drone';
313            update kb3_items set itm_externalid='15510' where itm_name='Vespa I';
314            update kb3_items set itm_externalid='16206' where itm_name='Valkyrie I';
315            update kb3_items set itm_externalid='17565' where itm_name='Hellhound I';
316            update kb3_items set itm_externalid='21050' where itm_name='Unanchoring Drone';
317            update kb3_items set itm_externalid='21638' where itm_name='Survey Drone';
318            update kb3_items set itm_externalid='21640' where itm_name='Vespa II';
319            update kb3_items set itm_externalid='2173' where itm_name='Valkyrie II';
320            update kb3_items set itm_externalid='2175' where itm_name='Infiltrator I';
321            update kb3_items set itm_externalid='2183' where itm_name='Infiltrator II';
322            update kb3_items set itm_externalid='2185' where itm_name='Hammerhead I';
323            update kb3_items set itm_externalid='2193' where itm_name='Hammerhead II';
324            update kb3_items set itm_externalid='2195' where itm_name='Praetor I';
325            update kb3_items set itm_externalid='2203' where itm_name='Praetor II';
326            update kb3_items set itm_externalid='2205' where itm_name='Acolyte I';
327            update kb3_items set itm_externalid='2205' where itm_name='Acolyte II';
328            update kb3_items set itm_externalid='22572' where itm_name='Praetor EV-900';
329            update kb3_items set itm_externalid='22574' where itm_name='Warp Scrambling Drone';
330            update kb3_items set itm_externalid='22713' where itm_name='10mn webscramblifying Drone';
331            update kb3_items set itm_externalid='22765' where itm_name='Heavy Shield Maintenance Bot I';
332            update kb3_items set itm_externalid='22780' where itm_name='Fighter Uno';
333            update kb3_items set itm_externalid='23055' where itm_name='Templar';
334            update kb3_items set itm_externalid='23057' where itm_name='Dragonfly';
335            update kb3_items set itm_externalid='23059' where itm_name='Firbolg';
336            update kb3_items set itm_externalid='23061' where itm_name='Einherji';
337            update kb3_items set itm_externalid='23473' where itm_name='Wasp EC-900';
338            update kb3_items set itm_externalid='23506' where itm_name='Ogre SD-900';
339            update kb3_items set itm_externalid='23510' where itm_name='Praetor TD-900';
340            update kb3_items set itm_externalid='23512' where itm_name='Berserker TP-900';
341            update kb3_items set itm_externalid='23523' where itm_name='Heavy Armor Maintenance Bot I';
342            update kb3_items set itm_externalid='23525' where itm_name='Curator I';
343            update kb3_items set itm_externalid='23559' where itm_name='Berserker SW-900';
344            update kb3_items set itm_externalid='23559' where itm_name='Warden I';
345            update kb3_items set itm_externalid='23561' where itm_name='Garde I';
346            update kb3_items set itm_externalid='23563' where itm_name='Bouncer I';
347            update kb3_items set itm_externalid='23659' where itm_name='Acolyte EV-300';
348            update kb3_items set itm_externalid='23702' where itm_name='Infiltrator EV-600';
349            update kb3_items set itm_externalid='23705' where itm_name='Vespa EC-600';
350            update kb3_items set itm_externalid='23707' where itm_name='Hornet EC-300';
351            update kb3_items set itm_externalid='23709' where itm_name='Medium Armor Maintenance Bot I';
352            update kb3_items set itm_externalid='23711' where itm_name='Light Armor Maintenance Bot I';
353            update kb3_items set itm_externalid='23713' where itm_name='Hammerhead SD-600';
354            update kb3_items set itm_externalid='23715' where itm_name='Hobgoblin SD-300';
355            update kb3_items set itm_externalid='23717' where itm_name='Medium Shield Maintenance Bot I';
356            update kb3_items set itm_externalid='23719' where itm_name='Light Shield Maintenance Bot I';
357            update kb3_items set itm_externalid='23721' where itm_name='Valkyrie TP-600';
358            update kb3_items set itm_externalid='23723' where itm_name='Warrior TP-300';
359            update kb3_items set itm_externalid='23725' where itm_name='Infiltrator TD-600';
360            update kb3_items set itm_externalid='23727' where itm_name='Acolyte TD-300';
361            update kb3_items set itm_externalid='23729' where itm_name='Valkyrie SW-600';
362            update kb3_items set itm_externalid='23731' where itm_name='Warrior SW-300';
363            update kb3_items set itm_externalid='23759' where itm_name='FA-14 Templar';
364            update kb3_items set itm_externalid='2436' where itm_name='Wasp II';
365            update kb3_items set itm_externalid='2444' where itm_name='Ogre I';
366            update kb3_items set itm_externalid='2446' where itm_name='Ogre II';
367            update kb3_items set itm_externalid='2454' where itm_name='Hobgoblin I';
368            update kb3_items set itm_externalid='2456' where itm_name='Hobgoblin II';
369            update kb3_items set itm_externalid='24618' where itm_name='horrible tracking drone';
370            update kb3_items set itm_externalid='2464' where itm_name='Hornet I';
371            update kb3_items set itm_externalid='2466' where itm_name='Hornet II';
372            update kb3_items set itm_externalid='2476' where itm_name='Berserker I';
373            update kb3_items set itm_externalid='2478' where itm_name='Berserker II';
374            update kb3_items set itm_externalid='2486' where itm_name='Warrior I';
375            update kb3_items set itm_externalid='2488' where itm_name='Warrior II';
376            update kb3_items set itm_externalid='3218' where itm_name='Harvester Mining Drone';
377            update kb3_items set itm_externalid='3549' where itm_name='Tutorial Attack Drone';
378            update kb3_items set itm_externalid='9871' where itm_name='Repair Drone';
379        ";
380    $query = explode("\n", $queries);
381    foreach ($query as $querystring)
382    {
383        if ($string = trim(str_replace(');', ')', $querystring)))
384        {
385            $qry->execute($string);
386        }
387    }
388}
389function check_tblstrct6()
390{
391    $qry = new DBQuery();
392    $query = 'select all_img from kb3_alliances limit 1';
393    $result = mysql_query($query);
394    if (!$result)
395    {
396        return;
397    }
398    $query = 'ALTER TABLE `kb3_alliances` DROP `all_img`';
399    $qry->execute($query);
400}
401?>
Note: See TracBrowser for help on using the browser.