database-migrate.txt 5.21 KB
Newer Older
1
2
3
4
5
6
#
# Anytime you change database-create.sql, but sure to put any special
# (sql) instructions that need to be executed to bring an existing DB
# into compliance.
#
# Please indicate the CVS revision number your instructions refer to.
7
8
# 

9
10
11
12
13
14
15
16
17
18
19
20
21
1.112: Made it illegal to put NULL values in interfaces.iface.
       Run this first to make sure you don't have any (fix if you
       do). 
       
	select * from interfaces where iface is null;
	
1.113: Add disktype to node_types for Mike:

	alter table node_types add disktype enum('ad','da','ar') \
		default NULL after control_iface;

       Used by frisbee/imagezip. Locally, changed these types:

Leigh B. Stoller's avatar
Leigh B. Stoller committed
22
23
24
25
26
27
	update node_types set disktype='ad' where type='pc600';
	update node_types set disktype='ad' where type='pc850';
	update node_types set disktype='ad' where type='pc1500';
	update node_types set disktype='ad' where type='pcL440GX';
	update node_types set disktype='ad' where type='pc2000';
	update node_types set disktype='ad' where type='pc601';
28

29
1.114: Add nextosid slot to os_info table for mapping the generic
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
       osids (RHL-STD) to a specific one. Primarily used in os_setup
       to figure out what to do with the generic names, but might also
       be used someday to migrate people to newer versions?

        alter table os_info add nextosid varchar(35) default NULL \
	        after op_mode;

	update os_info set nextosid='RHL71-STD' where \
		osid='RHL-STD';
	update os_info set nextosid='emulab-ops-FBSD47-STD' where \
		osid='FBSD-STD';

       Note that the osids are a little odd on boss, where the early
       ones had osid=osname. On other testbeds, need to check the
       actual value of the osids.
45

46
1.115   Add node_activity table for new slothd stuff: (Mac)
47
48
49
50
51
52
53
54
55
56

CREATE TABLE node_activity (
  node_id varchar(10) NOT NULL default '',
  last_tty_act datetime NOT NULL default '0000-00-00 00:00:00',
  last_net_act datetime NOT NULL default '0000-00-00 00:00:00',
  last_cpu_act datetime NOT NULL default '0000-00-00 00:00:00',
  last_ext_act datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (node_id)
) TYPE=MyISAM;

57
58
59
60
61
62
63
64
65
66
67
68
	No population of the table is necessary. It will self populate
	as nodes start to get activity reports.

1.116   Add last_report to node_activity table: (Mac)

alter table node_activity add last_report datetime not null;

        To make its values consistent with the rest of the table:

update node_activity set last_report=greatest(last_tty_act,
last_net_act,last_cpu_act,last_ext_act);

69
70
71
72
73
74
75
76
77
78
1.117:  Add emulated and uselinkdelay slots to the virt_lans
        table. Set in the front end, used in assign_wrapper.

	alter table virt_lans add emulated tinyint(4) default '0' \
			after widearea;
	alter table virt_lans add uselinkdelay tinyint(4) default '0' \
			after emulated;

        Nothing special needs to be done.

79
80
1.118: Add nobwshaping slot to the virt_lans table. Set in the front
       end, used in assign_wrapper.
81

82
83
84
	alter table virt_lans add nobwshaping tinyint(4) default '0' \
			after uselinkdelay;

85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
        Nothing special needs to be done.

1.119: Change user_pubkeys to use an idx for its unique index instead
       of the comment field in the key (a really dumb idea I had).
       However, adding an auto_increment field is always a pain. So:

	CREATE TABLE user_pubkeys_new (
	  uid varchar(8) NOT NULL default '',
	  idx int(10) unsigned NOT NULL auto_increment,
	  pubkey text,
	  stamp datetime default NULL,
	  comment varchar(128) NOT NULL default '',
	  PRIMARY KEY  (uid,idx)
	) TYPE=MyISAM;
	
	insert into user_pubkeys_new (uid, idx, pubkey, stamp, comment)
		select uid, 0, pubkey, stamp, comment from
		user_pubkeys;
        rename table user_pubkeys to user_pubkeys_old,
                     user_pubkeys_new to user_pubkeys;
	drop table user_pubkeys_old;
		     
107
108
109
110
111
112
113
114
115
116
117
118
119
1.120:  Add gid slot to images table for per-subgroup image support
        Also add global global flag, to replace shared flag. Global
	means testbed wide, while shared means within a project. To
	migrate an existing DB, just need to set pid=gid,global=shared
	for all existing images, and then set shared=0.

	alter table images add gid varchar(12) NOT NULL default '' after pid;
	alter table images add INDEX (gid);
	alter table images add global tinyint(4) NOT NULL default '0' \
			after shared;
	update images set gid=pid,global=shared;
	update images set shared=0;
	
Chad Barb's avatar
   
Chad Barb committed
120
121
122
123
124
125
126
127
128
129
130
131
132
1.121:  Create empty 'webnews' table with following schema for 
	$TBSRC/www/news.php3 ('blog-like news posting system):
	('date' is keyed since 'ORDER BY date DESC' is used a lot.)

	CREATE TABLE webnews (
	  msgid int(11) NOT NULL auto_increment,
	  subject tinytext,
	  date datetime default NULL,
	  author varchar(32) default NULL,
	  body text,
	  PRIMARY KEY  (msgid),
	  KEY date (date)
	) TYPE=MyISAM;
133
134
135
136
137
138
139
140
141

1.122: Convert usr_zip to tinytext from int. In prep for using these
       fields.

       alter table users change usr_zip usr_zip tinytext;
       update users set usr_addr2='',usr_city='',usr_state='',usr_zip='';

       These fields had never been used, so the type change and init
       are okay.
Chad Barb's avatar
   
Chad Barb committed
142
143
144
145
146
147
148
149
150
151
152
153
154

1.223: Create empty 'sitevariables' table with the following schema:

       CREATE TABLE sitevariables (
         name text NOT NULL,
	 value text,
	 defaultvalue text NOT NULL,
	 description text,
	 KEY name (name(32))
       ) TYPE=MyISAM;