rfK1jU49

1CREATE TABLE IF NOT EXISTS `#__comm_subscribers` (
2      `subscriber_id` int(11) NOT NULL auto_increment,
3      `user_id` int(11) NOT NULL default '0',
4      `subscriber_name` varchar(64) NOT NULL default '',
5      `subscriber_surname` varchar(64) NOT NULL default '',
6      `subscriber_email` varchar(64) NOT NULL default '',
7      `confirmed` tinyint(1) NOT NULL default '0',
8      `subscribe_date` datetime NOT NULL default '0000-00-00 00:00:00',
9      PRIMARY KEY  (`subscriber_id`),
10      UNIQUE KEY `subscriber_email` (`subscriber_email`)
11    ) ENGINE=MyISAM CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' COMMENT='Subscribers for Comm are stored here.';
12	
13ALTER TABLE `#__comm_subscribers` ADD `subscriber_surname`;
14ALTER TABLE `#__comm_subscribers` MODIFY `subscriber_surname` varchar(64) NOT NULL default '';
15	
16/*delimiter '//'
17CREATE PROCEDURE addcol() BEGIN
18IF NOT EXISTS(
19SELECT * FROM information_schema.COLUMNS
20WHERE COLUMN_NAME='subscriber_surname' AND TABLE_NAME='#__comm_subscribers'
21)
22THEN
23	ALTER TABLE `#__comm_subscribers`
24	ADD COLUMN `subscriber_surname` varchar(64) NOT NULL default '';
25END IF;
26END;
27//
28delimiter ';'
29CALL addcol();
30DROP PROCEDURE addcol;*/
31	
32ERROR 1060 (42S21): Duplicate column name 'newcolumnname'
33	
34DELIMITER $$
35
36DROP PROCEDURE IF EXISTS upgrade_database_1_0_to_2_0 $$
37CREATE PROCEDURE upgrade_database_1_0_to_2_0()
38BEGIN
39
40-- rename a table safely
41IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()
42        AND TABLE_NAME='my_old_table_name') ) THEN
43    RENAME TABLE 
44        my_old_table_name TO my_new_table_name,
45END IF;
46
47-- add a column safely
48IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()
49        AND COLUMN_NAME='my_additional_column' AND TABLE_NAME='my_table_name') ) THEN
50    ALTER TABLE my_table_name ADD my_additional_column varchar(2048) NOT NULL DEFAULT '';
51END IF;
52
53END $$
54
55CALL upgrade_database_1_0_to_2_0() $$
56
57DELIMITER ;
58	
59delimiter ;;
60create procedure foo ()
61begin
62    declare continue handler for 1060 begin end;
63    alter table atable add subscriber_surname varchar(64);
64end;;
65call foo();;
66	
67delimiter //
68	
69delimiter '//'