database-migrate.txt 63.8 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
1.123: Create empty 'sitevariables' table with the following schema:
Chad Barb's avatar
 
Chad Barb committed
144
145
146
147
148
149
150
151
152

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

153
1.124: Change address fields in widearea_nodeinfo to tinytext:
Chad Barb's avatar
 
Chad Barb committed
154

155
156
157
158
       ALTER TABLE widearea_nodeinfo CHANGE city city tinytext;
       ALTER TABLE widearea_nodeinfo CHANGE state state tinytext;
       ALTER TABLE widearea_nodeinfo CHANGE country country tinytext;
       ALTER TABLE widearea_nodeinfo CHANGE zip zip tinytext;
159
160
161
162
163
164
165
166
167

1.125: Add 'external_node_id' field to widearea_nodeinfo.
       This is pretty much a comment field for now; 
       NULL is a safe value, but if a node is a RON node
       "ron:" followed by the name of the node in the ron
       hosts table (in the external-to-emulab ron db) is
       the preferred value. (This may facilitate automatic
       db synchronization later.)

168
       ALTER TABLE widearea_nodeinfo ADD COLUMN external_node_id tinytext;
Chad Barb's avatar
 
Chad Barb committed
169
170
171
172
173
174
175
176
177

1.126: Change 'name' column in 'sitevariables' from
       text to varchar(255). Also make it a PRIMARY KEY,
       and get rid of old INDEX. 
       This table has not yet been used.
       
       ALTER TABLE sitevariables 
         CHANGE COLUMN name name varchar(255) PRIMARY KEY;

178
179
180
181
182
183
184
185
186
       ALTER TABLE sitevariables DROP INDEX name;

1.127: Make (pid,eid,vname) unique in the reserved table. Recommended
       commands for updating are:

       alter table reserved drop index pid;
       update reserved set vname=node_id where vname is null;
       alter table reserved add unique vname (pid,eid,vname); 

Chad Barb's avatar
 
Chad Barb committed
187
1.128: Add usr_country column to users table.
188
189

       ALTER TABLE users ADD COLUMN usr_country tinytext AFTER usr_zip;
190
191
192
193

1.129: Add node_id to state_triggers table, and change its primary key.

       alter table state_triggers add 
194
	     node_id varchar(10) not null default '' first;
195
196
197
198
       update state_triggers set node_id="*";
       alter table state_triggers drop primary key;
       alter table state_triggers add primary key (node_id,op_mode,state);

199
200
201
202
203
204
205
206
1.130: Add widearearoot and wideareajailroot to the users table, to
       control who gets root on widearea nodes, inside and outside of
       jail. Kinda brute force; might need to make this more flexible
       at some point, perhaps with a node/user mapping table, but this
       will do for now since its handled entirely inside of tmcd. 

       alter table users add widearearoot tinyint(4) default '0';
       alter table users add wideareajailroot tinyint(4) default '0';
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227

1.131: Add two new tables, new_nodes and new_interfaces, for nodes
       that will automatically report in when they are first booted
       up. The testbed admins will then be notified, so that they can
       move these into the real nodes and interfaces tables with the
       newnode script.

       CREATE TABLE new_interfaces (
          node_id varchar(10) NOT NULL default '',
          iface varchar(10) NOT NULL default '',
          mac varchar(12) NOT NULL default '',
          interface_type varchar(15) default NULL,
          PRIMARY KEY  (node_id,iface)
       ) TYPE=MyISAM;
       CREATE TABLE new_nodes (
          node_id varchar(10) NOT NULL default '',
          type varchar(30) default NULL,
	  IP varchar(15) default NULL,
	  dmesg text,
	  PRIMARY KEY  (node_id)
       ) TYPE=MyISAM;
228

229
230
231
1.132: This commit was superseded by 1.133

1.133: Changed the new_nodes and new_interfaces table to use a new unique
232
233
234
235
236
237
238
239
240
241
242
       int as they primary key instead of the node_id, so that the latter can
       be easily changed. Also added a timestamp the the new_nodes column.

       alter table new_nodes add column created timestamp not null;
       alter table new_nodes drop primary key;
       alter table new_nodes add column new_node_id int not null auto_increment
             first, add primary key (new_node_id);
       alter table new_interfaces drop primary key;
       alter table new_interfaces drop column node_id;
       alter table new_interfaces add column new_node_id int not null first,
              add primary key(new_node_id,iface);
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347

1.134: New stats tables. I've checked in a script in this directory to
       fill in the tables as best as possible. See ./initstats.pl. Just
       run it and pipe the output into "mysql tbdb". Its a hack job,
       since there is not enough info in the system to do a good job.
       Need Mac's scripts to make it work properly.

	CREATE TABLE experiment_stats (
	  pid varchar(12) NOT NULL default '',
	  eid varchar(32) NOT NULL default '',
	  creator varchar(8) NOT NULL default '',
	  idx int(10) unsigned NOT NULL default '0',
	  gid varchar(16) NOT NULL default '',
	  created datetime default NULL,
	  destroyed datetime default NULL,
	  swapin_count smallint(5) unsigned default '0',
	  swapin_last datetime default NULL,
	  swapout_count smallint(5) unsigned default '0',
	  swapout_last datetime default NULL,
	  swapmodify_count smallint(5) unsigned default '0',
	  swapmodify_last datetime default NULL,
	  swapin_duration int(10) unsigned default '0',
	  vnodes smallint(5) unsigned default '0',
	  pnodes smallint(5) unsigned default '0',
	  wanodes smallint(5) unsigned default '0',
	  simnodes smallint(5) unsigned default '0',
	  jailnodes smallint(5) unsigned default '0',
	  delaynodes smallint(5) unsigned default '0',
	  linkdelays smallint(5) unsigned default '0',
	  walinks smallint(5) unsigned default '0',
	  links smallint(5) unsigned default '0',
	  lans smallint(5) unsigned default '0',
	  shapedlinks smallint(5) unsigned default '0',
	  shapedlans smallint(5) unsigned default '0',
	  minlinks tinyint(3) unsigned default '0',
	  maxlinks tinyint(3) unsigned default '0',
	  PRIMARY KEY  (eid,pid,idx)
	) TYPE=MyISAM;


	CREATE TABLE group_stats (
	  pid varchar(12) NOT NULL default '',
	  gid varchar(12) NOT NULL default '',
	  exptstart_count int(11) unsigned default '0',
	  exptstart_last datetime default NULL,
	  exptpreload_count int(11) unsigned default '0',
	  exptpreload_last datetime default NULL,
	  exptswapin_count int(11) unsigned default '0',
	  exptswapin_last datetime default NULL,
	  exptswapout_count int(11) unsigned default '0',
	  exptswapout_last datetime default NULL,
	  exptswapmodify_count int(11) unsigned default '0',
	  exptswapmodify_last datetime default NULL,
	  allexpt_duration int(11) unsigned default '0',
	  allexpt_vnodes int(11) unsigned default '0',
	  allexpt_vnode_duration int(11) unsigned default '0',
	  allexpt_pnodes int(11) unsigned default '0',
	  allexpt_pnode_duration int(11) unsigned default '0',
	  PRIMARY KEY  (pid,gid)
	) TYPE=MyISAM;


	CREATE TABLE project_stats (
	  pid varchar(12) NOT NULL default '',
	  exptstart_count int(11) unsigned default '0',
	  exptstart_last datetime default NULL,
	  exptpreload_count int(11) unsigned default '0',
	  exptpreload_last datetime default NULL,
	  exptswapin_count int(11) unsigned default '0',
	  exptswapin_last datetime default NULL,
	  exptswapout_count int(11) unsigned default '0',
	  exptswapout_last datetime default NULL,
	  exptswapmodify_count int(11) unsigned default '0',
	  exptswapmodify_last datetime default NULL,
	  allexpt_duration int(11) unsigned default '0',
	  allexpt_vnodes int(11) unsigned default '0',
	  allexpt_vnode_duration int(11) unsigned default '0',
	  allexpt_pnodes int(11) unsigned default '0',
	  allexpt_pnode_duration int(11) unsigned default '0',
	  PRIMARY KEY  (pid)
	) TYPE=MyISAM;


	CREATE TABLE user_stats (
	  uid varchar(8) NOT NULL default '',
	  weblogin_count int(11) unsigned default '0',
	  weblogin_last datetime default NULL,
	  exptstart_count int(11) unsigned default '0',
	  exptstart_last datetime default NULL,
	  exptpreload_count int(11) unsigned default '0',
	  exptpreload_last datetime default NULL,
	  exptswapin_count int(11) unsigned default '0',
	  exptswapin_last datetime default NULL,
	  exptswapout_count int(11) unsigned default '0',
	  exptswapout_last datetime default NULL,
	  exptswapmodify_count int(11) unsigned default '0',
	  exptswapmodify_last datetime default NULL,
	  allexpt_duration int(11) unsigned default '0',
	  allexpt_vnodes int(11) unsigned default '0',
	  allexpt_vnode_duration int(11) unsigned default '0',
	  allexpt_pnodes int(11) unsigned default '0',
	  allexpt_pnode_duration int(11) unsigned default '0',
	  PRIMARY KEY  (uid)
	) TYPE=MyISAM;

348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
1.135: Add exit codes. Obviously, we get just the last exit code, but
       I still think this will be useful.  Note that we do not track
       front end parse errors or other very early errors that are
       likely the result of system problems, not user problems. Also
       note that we need to standardize the error codes so that these
       are meaningful a year from now!

	alter table experiment_stats add swap_errors smallint(5) \
		unsigned default '0' after swapmodify_last;
	alter table experiment_stats add swap_exitcode tinyint(3) \
		unsigned default '0' after swap_errors;
	alter table experiment_stats add idle_swaps smallint(5) \
		unsigned default '0' after swap_exitcode;
	alter table experiment_stats add batch tinyint(3) \
		unsigned default '0' after maxlinks;

       Also add testbed wide stats table. 

	CREATE TABLE testbed_stats (
	  idx int(10) unsigned NOT NULL auto_increment,
	  tstamp datetime default NULL,
	  exptidx int(10) unsigned NOT NULL default '0',
  	  action varchar(16) NOT NULL default '',
	  exitcode tinyint(3) unsigned default '0', 
	  PRIMARY KEY  (idx)
	) TYPE=MyISAM;

	This table gets an entry for each experiment operation. By
	cross indexing with experiment_stats via the exptidx field, we
	can figure out what was running on the testbed and how many
	nodes it was using, etc. We also get Jay's desire for a per
	operation log, except its not in a file but in the DB, where
	all such information belongs. Average size of an entry is 30
	bytes. We can squeeze this down easily by making the "action"
	a tinyint instead varchar, but not gonna worry until the table
383
384
385
	is over 1MB. 

1.137: Minor file formatting changes, no DB schema changes
386
387
388
389
390

1.138: Drop the lastlogin table since that info is now kept in
       user_stats.

       drop table lastlogin;
391
392
393
394
395
396
397
398
399
400
401
402
403

1.139: Add veth_interfaces table, which are virtual ethernet devices
       in FreeBSD.

	CREATE TABLE veth_interfaces (
	  node_id varchar(10) NOT NULL default '',
	  veth_id int(10) unsigned NOT NULL auto_increment,
	  mac varchar(12) NOT NULL default '000000000000',
	  IP varchar(15) default NULL,
	  iface varchar(10) NOT NULL default '',
	  PRIMARY KEY  (node_id,veth_id),
	  KEY IP (IP)
	) TYPE=MyISAM;
404
405
406
407
408
409

1.140: Add index to experiment_stats table to speed up big joins by
       several orders of magnitude!

       alter table experiment_stats add index(idx);

410
411
412
413
414
415
416
417
418
1.141: Add usevethiface flag to virt_lans. Mostly a development aid so
       that we can create emulated links that use the veth device
       instead of an IP alias. Without linux support, mere users would
       not get to choose this option since it will only work between
       FreeBSD boxes with veth support.

       alter table virt_lans add usevethiface tinyint(4) default '0'
		after nobwshaping;

419
420
421
422
423
424
425
1.142: Made the max_concurrent value per-OS rather than per-image. If you have
       any images with this restriction, you'll need to move it over to the
       OS by hand, but I think the main database on boss is the only place with
       any of these!

       alter table os_info add column max_concurrent int(11) default NULL;
       alter table images drop column max_concurrent;
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518

1.143: Split the experiment stats table into two parts. The first is
       the per-experiment instantiation with aggregate data like the
       number of swapins, the dates and the like. The other part is
       the per swapin/modify stats. These are number of pnodes, links,
       lans, etc. Long term, I think we want more precise swapin
       stats, and with experiment modify in the mix, we need to have
       multiple stat records per experiment, but do not need to
       duplicate all the stuff in the other table just mentioned.

       To reduce the amount the table size, we cross reference the
       tables by index only instead of with pid,eid and the like. We use
       exptidx to link experiments, experiment_stats, and the new
       experiment_resources table. experiment_resources and stats are
       linked by another index in the resources table, which indicates
       which is the current resource row. On a modify, a new resource
       record is created, and the stats record updated to point to the
       new (latest) resource record. 

	DROP TABLE IF EXISTS experiment_resources;
	CREATE TABLE experiment_resources (
	  idx int(10) unsigned NOT NULL auto_increment,
	  exptidx int(10) unsigned NOT NULL default '0',
	  tstamp datetime default NULL,
	  vnodes smallint(5) unsigned default '0',
	  pnodes smallint(5) unsigned default '0',
	  wanodes smallint(5) unsigned default '0',
	  simnodes smallint(5) unsigned default '0',
          jailnodes smallint(5) unsigned default '0',
          delaynodes smallint(5) unsigned default '0',
          linkdelays smallint(5) unsigned default '0',
          walinks smallint(5) unsigned default '0',
          links smallint(5) unsigned default '0',
          lans smallint(5) unsigned default '0',
          shapedlinks smallint(5) unsigned default '0',
          shapedlans smallint(5) unsigned default '0',
          minlinks tinyint(3) unsigned default '0',
          maxlinks tinyint(3) unsigned default '0',
          PRIMARY KEY  (idx),
	  KEY exptidx (exptidx)
        ) TYPE=MyISAM;

      Be clear about what index is what:

        alter table experiment_stats change idx exptidx 
			int(10) unsigned NOT NULL default '0';
        alter table experiment_stats drop index idx;
	alter table experiment_stats add index(exptidx);
        alter table experiment_stats add rsrcidx 
			int(10) unsigned NOT NULL default '0' after exptidx;
	alter table experiment_stats add index(rsrcidx);
        alter table experiment_stats add lastrsrc
			int(10) unsigned default NULL after rsrcidx;

      Change a few names cause I picked bad ones initially:
        alter table experiment_stats change swapmodify_count swapmod_count
			smallint(5) unsigned default '0';
        alter table experiment_stats change swapmodify_last swapmod_last
			datetime default NULL;
        alter table group_stats change exptswapmodify_count exptswapmod_count
			int(11) unsigned default '0';
        alter table group_stats change exptswapmodify_last exptswapmod_last
			datetime default NULL;
        alter table project_stats change exptswapmodify_count exptswapmod_count
			int(11) unsigned default '0';
        alter table project_stats change exptswapmodify_last exptswapmod_last
			datetime default NULL;
        alter table user_stats change exptswapmodify_count exptswapmod_count
			int(11) unsigned default '0';
        alter table user_stats change exptswapmodify_last exptswapmod_last
			datetime default NULL;

      To populate the new table from the old table:

	insert into experiment_resources (idx, exptidx, tstamp, vnodes, 
		pnodes, wanodes, simnodes, jailnodes, delaynodes, 
		linkdelays, walinks, links, lans, shapedlinks, 
		shapedlans, minlinks, maxlinks) 
               select 0, exptidx, created, vnodes, pnodes, wanodes, 
	        simnodes, jailnodes, delaynodes, linkdelays, walinks, 
		links, lans, shapedlinks, shapedlans, minlinks, 
		maxlinks from experiment_stats order by exptidx;

      Now we have to insert the newly created resource idx into the
      stats table to link them up.

	my $query_result =
	    DBQueryFatal("select idx,exptidx from experiment_resources");

	while (($idx,$exptidx) = $query_result->fetchrow_array()) {
	    DBQueryFatal("update experiment_stats set rsrcidx=$idx ".
			 "where exptidx=$exptidx");
	}
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535

1.144: Add link from testbed_stats to the experiment_resources record
       that was active at the time of the operation. This makes it
       possible for showstats.php3 to act like a log file, since we
       have the info that corresponds at that time (rather than just
       the most recent). Also a minor bug fix for exitcode, which
       should not be unsigned.
       
        alter table testbed_stats change exitcode exitcode
		tinyint(3) default '0';

        alter table testbed_stats add rsrcidx 
		int(10) unsigned NOT NULL default '0' after exptidx;

	my $query_result =
	    DBQueryFatal("select t.idx,t.exptidx,s.rsrcidx ".
			 " from testbed_stats as t ".
536
			 "left join experiment_stats as s ");
537
538
539
540
541
542
543
544
545

	while (($idx,$exptidx,$rsrcidx) = $query_result->fetchrow_array()) {
	    if (!defined($rsrcidx)) {
		print STDERR "No rsrcidx for $idx ($exptidx)\n";
		next;
	    }
	    print "update testbed_stats set rsrcidx=$rsrcidx ".
		  	"where idx=$idx;\n";
	}			
546
547
548
549
550
551
552
553
554
555
556
557

1.145: Add vnode to veth_interfaces. This serves to distinguish veths
       for jailed nodes from veths for plain emulated links between
       phys nodes, which we might generally allow at some point for
       supporting emulated links (rather than using IP aliases). My
       plan is to use this im tmcd to determine what ifconfig returns;
       no point in returning jailed veth configuration when the node
       boots, but should wait until the jail is setup (this is further
       complicated by rtabids, but thats another story).

        alter table veth_interfaces add vnode varchar(32) default NULL
		after iface;
558
559
560
561
562

1.146: Minor mod to last revision; use a less ambiguous name. 

        alter table veth_interfaces change vnode vnode_id varchar(32)
		default NULL;
563
564
565
566
567
568
569

1.147: Add expt_swap_uid to experiments table to store who was the
       last to swap in/out/modify an experiment. Needed for proper
       user accounting.

        alter table experiments add expt_swap_uid varchar(8) NOT NULL
		 default '' after expt_swapped;
570
571
572
573
574

       Must also init that field for existing experiments.

        update experiments set expt_swap_uid=expt_head_uid
		where expt_swapped is not null;
Chad Barb's avatar
 
Chad Barb committed
575
576
577
578
579
580

1.148: Add usr_shell to users table to specify user's preferred shell.
       Retroactively make each user's preferred shell 'tcsh'.
       
       ALTER TABLE users ADD COLUMN usr_shell TINYTEXT AFTER usr_phone;
       UPDATE users SET usr_shell='tcsh';
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601

1.149: Add more fields to expts table for new swapping features. All
       the "bits" default to 0, so no further updating is needed.

	ALTER TABLE experiments ADD COLUMN noswap_reason tinytext 
	     AFTER priority;

	ALTER TABLE experiments ADD COLUMN idleswap tinyint(4) 
	      NOT NULL default '0' AFTER noswap_reason;

	ALTER TABLE experiments ADD COLUMN idleswap_timeout int(4) 
	      NOT NULL default '0' AFTER idleswap;

	ALTER TABLE experiments ADD COLUMN noidleswap_reason tinytext 
	      AFTER idleswap_timeout;

	ALTER TABLE experiments ADD COLUMN autoswap tinyint(4) 
	      NOT NULL default '0' AFTER noidleswap_reason;

	ALTER TABLE experiments ADD COLUMN autoswap_timeout int(4) 
	      NOT NULL default '0' AFTER autoswap;
602
603
604
605
606
607
608
609
610
611
612
613
614

1.150: Add trivial_ok to virt_lans for (very) crude control of
       colocation of virtual nodes.

        alter table virt_lans add trivial_ok tinyint(4) default '1'
		after usevethiface;

       Add multiplex_factor to experiments table to allow user to
       override the factor in the node_types table. Also very crude.

        alter table experiments add multiplex_factor smallint(5)
	        default NULL after forcelinkdelays;
		
615
616
617
618
619
620
1.151: Allow iface in the veth_interfaces table to be null to allow
       for trivial links between veth devices (on the same node, so no
       iface mapping).

	alter table veth_interfaces change iface iface varchar(10)
		 default NULL;
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640

1.152: Add jailip to nodes table. This is the control network IP for
       the jail, to be added as an alias on the control network
       interface on the phys node. There is no need for this to be in
       another table at this point. Eventually, vnodes will be dynamic
       anyway, and a number of things will change, including this.

	alter table nodes add jailip varchar(15) default NULL after jailflag;

	my $query_result =
	    DBQueryFatal("select node_id from nodes as n ".
			 "left join node_types as nt on nt.type=n.type ".
			 "where nt.class='pcvm'");

	while (($node_id) = $query_result->fetchrow_array()) {
	    my ($p,$v) = ($node_id =~ /pcvm(\d*)-(\d*)/);

	    DBQueryFatal("update nodes set ".
			 "jailip='172.16.${p}.${v}' where node_id='$node_id'");
	}
641
642
643
644
645

1.153:	Disallow nulls in state_timeouts table.
	
	alter table state_timeouts modify timeout int(11) not null;
	alter table state_timeouts modify action mediumtext not null;
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663

1.154: Add simple support for updating widearea images (slice 1 only).

	CREATE TABLE widearea_updates (
	  IP varchar(15) NOT NULL default '1.1.1.1',
	  roottag tinytext NOT NULL default '',
	  update_requested datetime NOT NULL default '0000-00-00 00:00:00',
	  update_started datetime default NULL,
	  PRIMARY KEY  (IP)
	) TYPE=MyISAM;

	The IP is of course the IP of the node you want to update.
	The roottag is the MD5 of the new slice to be layed down,
	which should also have a DB entry someplace, but I won't
	bother with that now; I'll just hard code that into the web
	page. The records are dynamic; someone like Dave will insert
	them, and the web page will remove them after a node
	sucessfully updates.
664
665
666

1.155: Bring the definition for the roottag column into sync with what's in
       the database on boss - no database changes required.
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687

1.156: Add uid to testbed_stats table:

	alter table testbed_stats add uid varchar(8) NOT NULL
			default	'' after exitcode;

       Need to init the table. For lack of better info, use the
       experiment creator for each record. Turns out there are just a
       few cases where this is not true.

	my $query_result =
	    DBQueryWarn("select t.idx,s.creator,t.action ".
			" from testbed_stats as t ".
			"left join experiment_stats as s on ".
			"  s.exptidx=t.exptidx ".
			"where t.uid='' ".
			"order by t.tstamp");

	while (($idx,$creator) = $query_result->fetchrow_array()) {
	    print "update testbed_stats set uid='$creator' where idx=$idx;\n";
	}
688
689
690
691
692
693
694

1.157	Take nulls out of comments table, so primary key works right.

	alter ignore table comments modify description text not null;
	alter ignore table comments modify 
		column_name varchar(64) not null default "";

695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
1.158: Add chain field to experiment_resources to make it easier to
       step back through experiment history, and keep the join size
       down.

        alter table experiment_resources add lastidx int(10)
			unsigned default NULL after exptidx;
	alter table experiment_resources add index(lastidx);

	my $query_result =
	    DBQueryWarn("select idx,exptidx from experiment_resources ".
			"order by exptidx,tstamp asc");

	my $lastexpt = -1;
	my $lastidx  = -1;

	while (($idx,$exptidx) = $query_result->fetchrow_array()) {
	    if ($exptidx != $lastexpt) {
		print "update experiment_resources set lastidx=NULL ".
		    "where exptidx=$exptidx and idx=$idx;\n";
		$lastexpt = $exptidx;
		$lastidx  = $idx;
		next;
	    }
	    print "update experiment_resources set lastidx=$lastidx ".
		"where exptidx=$exptidx and idx=$idx;\n";
	    $lastidx  = $idx;
	}
722
723
724
725
726
727

1.158: Add jail osid to node_types table.

	alter table node_types add jail_osid varchar(35) default NULL
		after delay_osid

728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
1.159: Remove old slots from experiment_stats table that were moved to
       experiment_resources table in revision 1.143.

        alter table experiment_stats drop vnodes;
	alter table experiment_stats drop pnodes;
	alter table experiment_stats drop wanodes;
	alter table experiment_stats drop simnodes;
	alter table experiment_stats drop jailnodes;
	alter table experiment_stats drop delaynodes;
	alter table experiment_stats drop linkdelays;
	alter table experiment_stats drop walinks;
	alter table experiment_stats drop links;
	alter table experiment_stats drop lans;
	alter table experiment_stats drop shapedlinks;
	alter table experiment_stats drop shapedlans;
	alter table experiment_stats drop minlinks;
	alter table experiment_stats drop maxlinks;
745
746
747
748
749
750
751
752
753
754

1.160: Put the thumbnail into the resources table. 

	alter table experiment_resources add thumbnail mediumblob
			 after maxlinks;

       Then create the thumbs in the DB

	        prerender_all -t

755
756
       (You can run prerender_all _after_ doing a boss-install).

757
758
759
760
       Also remove the vis_experiments table, since no longer needed.

        drop table vis_experiments;

761
1.162: Add a node_type_features table that lists the assign 'features' for each
762
763
764
765
766
       node type:

       create table node_type_features (type varchar(30) not null,
	   feature varchar(30) not null, weight float not null default 0.0,
	   primary key (type,feature));
767

768
1.163: Add columns to the new_interfaces table indicating where the interface
769
770
771
772
773
       is plugged into a switch:

       alter table new_interfaces add column switch_id char(10);
       alter table new_interfaces add column switch_card tinyint(3);
       alter table new_interfaces add column switch_port tinyint(3);
774

775
1.164: Chage the new_interfaces table to contain card numbers, rather than
776
777
778
779
780
781
782
       iface names:

       alter table new_interfaces drop primary key;
       alter table new_interfaces drop column iface;
       alter table new_interfaces add column card int not null after
	   new_node_id;
       alter table new_interfaces add primary key (new_node_id,card);
783

784
1.165: Added a new column to new_nodes, so we can tell the IP address that
785
786
787
       the node was temporarily given while it's configuring:

       alter table new_nodes add column temporary_IP varchar(15) after IP;
788

789
1.166: Kill max_ports and max_cards, replace with max_interfaces.
790
791
792
793
794
795
796
797
798

       alter table node_types drop max_ports;
       alter table node_types drop max_cards;
       alter table node_types add max_interfaces tinyint(3) \
		unsigned default '0' after HD;

       update node_types set max_interfaces=5 where type='pc600';
       update node_types set max_interfaces=5 where type='pc850';
       update node_types set max_interfaces=3 where type='pcL440GX';
799
800


801
1.167: As per Jay's request, start recording the start time as well as
802
803
804
805
806
807
808
809
810
       the end time, in the testbed_stats table. So we know how long
       things like swapin take, etc. Below, we convert the current
       timestamp into the "end" time, and add a new "start" time,
       which is null for all existing records. 

        alter table testbed_stats change tstamp \
			end_time datetime default NULL;
	alter table testbed_stats add start_time datetime \
			default NULL after idx;
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828

1.168: Add "role" to reserved table, to make explicit how a node is
       being used in an experiment (node, virthost, delaynode,
       simnode). This is for helping to simplify the swapmod code
       support in assign, and to eventually fix the delaynode problem.
       Yes, we could use the vname and a set convention, but thats a
       silly way to do it!

       alter table reserved add erole \
	       enum('node','virthost','delaynode','simnode') \
		NOT NULL default 'node' after vname;

       Note, that only physical nodes can take on different roles;
       virtual nodes can only be plain (experimental) nodes.

       I contemplated putting this into the nodes table instead, but a
       node has no role when its not reserved, so it seemed like the
       wrong place for it.
829
830
831
832
833
834
835

1.169: Add a prerender pid so that it can run in the background, but
       be killed if the experiment fails, terminates, or modifies.

       alter table experiments add prerender_pid int(11) default '0' \
		after event_sched_pid;

836
837
838
839
840
841
842
843
1.170: Add a sync_server slot, which is the "vname" of the node
       hosting the sync server. When non-null (set by parser) that
       node will start a sync server and other nodes will know to use
       it. Returned by tmcd of course.

       alter table experiments add sync_server varchar(32) \
		default NULL after idle_ignore;

844
845
846
847
848
1.171: Add issubnode slot for dealing with IXP type things.

	alter table node_types add issubnode tinyint(4) NOT NULL default '0' \
		after isremotenode;

Mac Newbold's avatar
Mac Newbold committed
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
1.172: Add plab stuff:

	alter table node_types add isplabdslice tinyint(4) NOT NULL 
		default '0' after issubnode;

	CREATE TABLE plab_slice_nodes (
	  pid varchar(12) NOT NULL default '',
	  eid varchar(32) NOT NULL default '',
	  slicename varchar(64) NOT NULL default '',
	  node_id varchar(10) NOT NULL default '',
	  ticketdata text,
	  leasedata text,
	  leaseend datetime default NULL,
	  PRIMARY KEY  (node_id)
	) TYPE=MyISAM;

	CREATE TABLE plab_slices (
	  pid varchar(12) NOT NULL default '',
	  eid varchar(32) NOT NULL default '',
	  slicename varchar(64) NOT NULL default '',
	  privkey text,
	  pubkey text,
	  PRIMARY KEY  (pid,eid)
	) TYPE=MyISAM;

874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
1.173: Add "role" to interfaces table, to replace the control_net and
       control_iface join with node_types, since new nodes have more
       than just expt and ctrl interfaces (ixps). The control_net slot
       is bogus anyway. Note that 'jail' is a placeholder for later.
       A 'fake' interface is not to be confused with veth interfaces; it
       is a psuedo interface like that found between an ixp and the host.

       alter table interfaces add \
		role enum('ctrl', 'expt', 'jail', 'fake', 'other') \
		NOT NULL default 'other' after iface;


       Now we have to set them.

       update interfaces set role='ctrl' where \
        ip like '155.101.%' or ip like '172.17.%' or \
        node_id like 'wa%' or node_id like 'ron%';

       update interfaces set role='expt' where \
	(ip like '192.168.%' or IP='') and \
	(node_id like 'pc%' or node_id like 'ixp%');

       update interfaces set role='fake' where \
	(interface_type='ixp_fake' and node_id like 'pc%');
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914

1.176: Add netmask to various tables. Currently in text format, but
       might have to change to an int format to save space.

       alter table interfaces add mask varchar(15) \
		default NULL after IPaliases;
       alter table virt_lans add mask varchar(15) \
		default '255.255.255.0' after member;
       alter table tunnels add mask varchar(15) \
       		default NULL after peer_ip;
       alter table veth_interfaces add mask varchar(15) \
		default NULL after IP;

       update interfaces set mask='255.255.255.0' \
		where role='ctrl' or role='ctrl' or role='fake';
       update veth_interfaces set mask='255.255.255.0';
       update tunnels set mask='255.255.255.0';
915

Leigh B. Stoller's avatar
Leigh B. Stoller committed
916
1.178: Add notes field to the users table for storing admin only
917
918
919
920
       notes.

       alter table users add notes text after wideareajailroot;

Leigh B. Stoller's avatar
Leigh B. Stoller committed
921
1.179: Add a table to contain auxiliary types for nodes.
922
923
924
925

       create table node_auxtypes (node_id varchar(10) not null, type \
       		varchar(30) not null, count int default 1, primary \
		key(node_id,type));
926

Leigh B. Stoller's avatar
Leigh B. Stoller committed
927
1.180: Add a table to map auxtypes to node_types table. 
928
929
930
931
932
933
934
935
936
937
938
939
940
941

	CREATE TABLE node_types_auxtypes (
	  auxtype varchar(30) NOT NULL default '',
	  type varchar(30) NOT NULL default '',
	  PRIMARY KEY  (auxtype)
	) TYPE=MyISAM;

       Add cpu_usage and mem_usage to experiments table. Typed as tiny
       ints, zero means ignore.

       alter table experiments add cpu_usage tinyint(4) unsigned \
		NOT NULL default '0' after sync_server;
       alter table experiments add mem_usage tinyint(4) unsigned \
		NOT NULL default '0' after cpu_usage;
942

Leigh B. Stoller's avatar
Leigh B. Stoller committed
943
1.181: Add a node_features table, just like the node_type_features
944
945
946
947
948
949
       table, but for individual nodes.

       create table node_features (node_id varchar(30) not null,
	   feature varchar(30) not null, weight float not null default 0.0,
	   primary key (node_id,feature));

950

Leigh B. Stoller's avatar
Leigh B. Stoller committed
951
1.182: Add plabnodes count to the experiment resource table so we can
952
953
954
955
       track exactly how many slices an experiment is using.

       alter table experiment_resources add
          plabnodes smallint(5) unsigned default '0' after wanodes;
Leigh B. Stoller's avatar
Leigh B. Stoller committed
956
957
958
959
960

1.183: Add index to testbed_stats table to speed up big joins by
       several orders of magnitude!

       alter table testbed_stats add index(rsrcidx);
961
962
963
964

1.184: Put an index on the path in the os_info table, since stated needs
       to select on it

Robert Ricci's avatar
Robert Ricci committed
965
       alter table os_info add key (path(255));
966
967
968
969
970
971

1.185: Add two new columns to widearea_nodeinfo to record the node's hostname
       and a match-able site name

       alter table widearea_nodeinfo add column hostname varchar(255);
       alter table widearea_nodeinfo add column site varchar(255);
972
973
974
975
976
977
978

1.186: Add hashkey slot to experiments table to use as a secret key to
       pass from the nodes to the webserver, to download tar/rpm
       files.

       alter table experiments add keyhash varchar(64) default NULL \
		after mem_usage;
979
980
981
982
983
984
985
986

1.187: Increase the length of the state and batchstate fields.

       alter table experiments change state state varchar(16) NOT NULL \
		default 'new';
       alter table experiments change batchstate batchstate varchar(16) \
	        default NULL;

987
988
989
990
991
992
993
       Note that you need to update fields in the DB so that
       experiments will be in the proper states for operating on
       (after the new scripts are installed, nothing will swap).
       To do that run:

		sql/newstates.pl | mysql tbdb

994
995
996
997
998
999
1000
1.188: Add a virt_node_desires table to finally be able to pass node
       desires into assign.

       create table virt_node_desires (pid varchar(12) not null, \
	   eid varchar(32) not null, vname varchar(32) not null, \
	   desire varchar(30) not null, weight float, \
	   primary key(pid,eid,vname));
1001
1002
1003
1004
1005
1006

1.189: Hah, I had the primary key for virt_node_desires wrong - it should
       allow multiple desires for each vnode:

       alter table virt_node_desires drop primary key;
       alter table virt_node_desires add primary key(pid,eid,vname,desire);
1007
1008
1009
1010

1.190: Add an index...

       alter table testbed_stats add key (exptidx);
1011
1012
1013
1014
1015

1.191: Add a new role 'gw' for 'gateway' interfaces:

       alter table interfaces modify role enum('ctrl','expt','jail','fake', \
           'other','gw');
1016

1017
1.192: Add sfshostid to nodes table. We store that in the filesystem
1018
1019
1020
1021
       on ops, but its nice to have it in the DB too so that we do not
       have to read that file!

       alter table nodes add sfshostid varchar(128) default NULL;
1022

1023
1.193: Add a range of VLANs to be used, per switch stack. NULL values will
1024
1025
1026
1027
       default to the old VLAN range.

       alter table switch_stack_types add column min_vlan int default NULL;
       alter table switch_stack_types add column max_vlan int default NULL;
1028

1029
1.194: Add a label to the state and mode transitions table. After
1030
1031
1032
1033
       running the two statements below, reload a current version of db-fill.

       alter table state_transitions add label varchar(255) not null;
       alter table mode_transitions add label varchar(255) not null;
1034
1035
1036
1037
1038
1039
1040
1041

1.195: Add secret key for the event system.

       alter table experiments add eventkey varchar(64) default NULL \
		after keyhash;

       To initialize run the initkey.pl script in this directory:

1042
       		sudo ./initkey.pl
1043

1044
1045
1.196: Add new virt_programs table to hold the details of a program
       object.
1046

1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
	CREATE TABLE virt_programs (
	  pid varchar(12) NOT NULL default '',
	  eid varchar(32) NOT NULL default '',
	  vnode varchar(32) NOT NULL default '',
	  vname varchar(32) NOT NULL default '',
	  command tinytext,
	  PRIMARY KEY  (pid,eid,vnode,vname),
	  KEY vnode (vnode)
	) TYPE=MyISAM;

	Existing experiments have the info contained in the static
	eventlist. We can create the above records from the eventlist
	with the following query, which I will run once all the
	support is installed.

	insert into virt_programs (pid, eid, vnode, vname, command) \
	    select pid,eid,vnode,vname,SUBSTRING(arguments,9) \
	    from eventlist where objecttype=4 and eventtype=1;
1065

1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1.197: Add login failure stuff to users table, and add new table to
       track login failures from specific IP addresses.

       alter table users add weblogin_frozen tinyint unsigned \
			NOT NULL default '0';
       alter table users add weblogin_failcount smallint unsigned \
			NOT NULL default '0';
       alter table users add weblogin_failstamp int unsigned \
			NOT NULL default '0';
			    			
       CREATE TABLE login_failures (
	  IP varchar(15) NOT NULL default '1.1.1.1',
	  frozen tinyint unsigned NOT NULL default '0',
	  failcount smallint unsigned NOT NULL default '0',
	  failstamp int unsigned NOT NULL default '0',
	  PRIMARY KEY  (IP)
       ) TYPE=MyISAM;
1083

1084
1085
1086
1087
1088
1.198: Add an issimnode column in node_types table so that checks for
       sim node type in assign_wrapper is consistent with other similar
       checks

       alter table node_types add issimnode tinyint(4) not null default 0;
1089
1090

1.199: Add a bit to the users table so that we can flag planetlab users:
1091
1092

       alter table users add column plab_user tinyint(1) not null default 0;
1093

1094
1.200: Add table to hold per table/slot contraint data (min/max len
1095
1096
1097
1098
1099
       and a perl regex defining valid data for the slot).

	CREATE TABLE table_regex (
	  table_name varchar(64) NOT NULL default '',
	  column_name varchar(64) NOT NULL default '',
1100
1101
1102
1103
1104
	  column_type enum('text','int','float') default NULL,
	  check_type enum('regex','function','redirect') default NULL,
	  check tinytext NOT NULL,
	  min int(11) NOT NULL default '0',
	  max int(11) NOT NULL default '0',
1105
1106
1107
1108
	  comment tinytext,
	  UNIQUE KEY table_name (table_name,column_name)
	) TYPE=MyISAM;

1109
1.202: Change the enum for pcremote_ok to match the phys type that was
1110
1111
1112
1113
       ultimately chosen by Austin.

	alter table projects modify pcremote_ok \
		set('pcplabphys','pcron','pcwa') default NULL;
1114

1115
1.203: Add an index to nodetypeXpid_permissions to make it more efficient
1116
1117
1118
       to get a list of what types a project is allowed to use.

       alter table nodetypeXpid_permissions add key (pid);
1119

1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1.204: Added the following entries so that the simulation (nse)
       backend support could be tested in a development tree.
       With this, simulation resources are automatically
       mapped to a distributed set of physical resources for
       scaling simulation resources that cannot be simulated 
       on a single physical node. The simnode_capacity column is used
       by ptopgen to associate different sim node capacities per
       physical node type in the ptop file. Change the erole column in
       the reserved table for enum simnode to simhost. Added rtabid
       fields in the interfaces and the veth_interfaces table whichs
       being computed in assign_wrapper for use in simhosts. The field
       could be used by jail vnodes as well although it is not used
       by the jail vnodes currently.

       alter table node_types add simnode_capacity smallint(5) \
               unsigned not null default 0;
       alter table reserved change erole erole \
               enum('node','virthost','delaynode','simhost') \
	       NOT NULL default 'node';
       alter table interfaces add rtabid smallint(5) \
               unsigned not null default 0;
       alter table veth_interfaces add rtabid smallint(5) \
               unsigned not null default 0;
1143
1144

1.205: Add enum to projects table to store which interface users
1145
1146
1147
1148
1149
       should start with.

       alter table projects add default_user_interface \
		 enum('emulab','plab') NOT NULL default 'emulab';

1150
1.206: Add enum to users table to store which interface user is currently
1151
1152
1153
1154
       set to use. This will replace plab_user bit.

       alter table users add user_interface \
		 enum('emulab','plab') NOT NULL default 'emulab';
1155

1156
1.207: Add fields to support user directed Forgot My Password changes.
1157
1158
1159
1160

       alter table users add chpasswd_key varchar(32) default NULL;
       alter table users add chpasswd_expires int(10) unsigned \
			NOT NULL default '0';
1161

1162
1.208: Add project table linked_to_us entry.
1163
1164
1165
1166

       alter table projects add linked_to_us tinyint(4) NOT NULL \
	       default '0' after default_user_interface;

1167
1168
1.209: Go back to revision 202 and change a bit.

1169
1170
1171
1172
1173
1174
1.210: Add vnode_id column in the interfaces table to indicate which
       vnode in an allocated experiment that the interface belongs
       to. This is useful when multiple jail vnodes are hosted on a
       physical node. 

       alter table interfaces add vnode_id varchar(32) default NULL;
1175

1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1.211: Add a new table virt_simnode_attributes that contains the number
       of slots that a virt_node will occupy on a pnode. This is useful
       during mapping. The default value is 1. The column eventrate will
       also be used during mapping. The initial value is 0 since it is not
       determined during the first swapin. Updates to these columns during
       an experimental run will persist till the next swapin. Currently,
       the updates will be done when a simulator (nse) running on a pnode
       cannot keep up with real-time. In future, it could be done
       periodically. The reserved table has a new column simhost_violation
       whose value can be '0' or '1' to indicate that a pnode running nse 
       cannot keep up with real-time.

       create table virt_simnode_attributes ( \
       pid varchar(12) NOT NULL default '', \
       eid varchar(32) NOT NULL default '', \
       vname varchar(32) NOT NULL default '', \
       nodeweight smallint(5) unsigned not null default '1', \
       eventrate int(11) unsigned not null default '0', \
       KEY pid (pid,eid,vname));

       alter table reserved add simhost_violation tinyint(3) \
               unsigned not null default '0';
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209

1.212: Add resource usage table.

	CREATE TABLE node_rusage (
	  node_id varchar(10) NOT NULL default '',
	  load_1min float NOT NULL default '0',
	  load_5min float NOT NULL default '0',
	  load_15min float NOT NULL default '0',
	  disk_used float  NOT NULL default '0',
	  status_timestamp datetime default NULL,
	  PRIMARY KEY  (node_id)
	) TYPE=MyISAM;
1210
1211

1.213: Minor whitespace change to make schemacheck happy.
1212
1213
1214
1215
1216
1217

1.214: Changes to nsfiles and nseconfigs tables to accomodate
       larger nsfiles and nse configuration information.

       alter table nseconfigs modify nseconfig mediumtext;
       alter table nsfiles modify nsfile mediumtext;
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229

1.215  Fixed the PRIMARY KEYs for virt_simnode_attributes by
       dropping the table and re-creating it correctly.

       create table virt_simnode_attributes ( \
         pid varchar(12) NOT NULL default '', \
         eid varchar(32) NOT NULL default '', \
         vname varchar(32) NOT NULL default '', \
         nodeweight smallint(5) unsigned not null default '1', \
         eventrate int(11) unsigned not null default '0', \
         PRIMARY KEY (pid,eid,vname));

1230
1231
1232
1233
1234
1235
1236
1.216: Add mfs flag to os_info table, and temp_boot_osid to nodes
       table.

	alter table nodes add temp_boot_osid varchar(35) NOT NULL \
		default '' after def_boot_cmd_line;
	alter table os_info add mfs tinyint(4) NOT NULL default '0' \
		after max_concurrent;
1237
1238
1239
1240
1241

1.217: Add slot to nodes table to facilitate better stated debugging.
      
	alter table nodes add stated_tag varchar(32) default NULL \
			after sfshostid;
1242
1243
1244
1245
1246
1247

1.218: Add nse_reswap_count in the experiments table to count how many
       times we tried re-mapping a sim experiment.

       alter table experiments add column sim_reswap_count \
             smallint(5) unsigned NOT NULL default '0';
1248
1249
1250
1251
1252
1253

1.219: Add pid to images table so we can kill frisbee for an image.

	alter table images add frisbee_pid int(11) default '0' \
		after load_address;

1254
1255
1256
1257
1.220: Add allowfixnode flag to experiments table so it can be turned off.
       This is useful when a user changes the vnode resources (such as cpu) 
       for a vnode and then does a swapmod.

1258
1259
1260
1261
	alter table experiments add veth_encapsulate tinyint(4) \
		NOT NULL default '1' after sim_reswap_count;

        alter table experiments add allowfixnode tinyint(4) \ 
1262
	       not null default 0 after veth_encapsulate;
1263

1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1.221: Fixed allowfixnode to default to 1 instead of 0

        alter table experiments change allowfixnode allowfixnode tinyint(4) \ 
	       not null default 1;

1.222: Added old_pid and old_eid in reserved table. This allows the moving of
       current reservations into a holding experiment with old_pid/old_eid
       suitably filled during assign_wrapper. Eventually, any unused
       entries will first be torn down and entries in the DB deleted.

       alter table reserved add column old_pid varchar(12) NOT NULL default '', \
             add column old_eid varchar(32) NOT NULL default '';

       alter table reserved add KEY old_pid (old_pid,old_eid);
1278

1279
1.223: Add jail_osname and delay_osname as temporary (well, perhaps)
1280
1281
1282
1283
1284
1285
1286
1287
1288
       aids for paper effort. Note, not an osid, but an osname like we
       specify for a virt_node. Lookup rules are the same; first check
       the current project for a match, and then check emulab-ops.

       alter table experiments add jail_osname varchar(20) \
		default NULL after allowfixnode;
       alter table experiments add delay_osname varchar(20) \
		default NULL after jail_osname;
		
1289
1.224: Add rtabid to nodes table, athough it is meaninful only on a
1290
1291
1292
1293
       jailed vnode. Set in assign_wrapper.

       alter table nodes add rtabid smallint(5) unsigned NOT NULL \
		default '0' after stated_tag;
1294

1295
1.225: Add 'veths' and 'mlinks' to the list of possible osfeatures in
1296
1297
1298
1299
       is_info table.

       alter table os_info change osfeatures osfeatures \
	set('ping','ssh','ipod','isup','veths','mlinks') default NULL;
1300

1301
1.226: Add a use_ipassign column to the experiments table, to enable and
1302
1303
1304
1305
1306
1307
1308
       disable the use of Jon's ipassign program. Also add a column to
       specify arguments for it.

       alter table experiments add column use_ipassign tinyint(1) \
	   not null default 0;
       alter table experiments add column ipassign_args varchar(255) \
	   default NULL;
1309

1310
1.227: Add cable and len columns to the new_interfaces table, so that when
1311
1312
1313
1314
       we're adding a small number of nodes by hand, we can set these:

       alter table new_interfaces add cable smallint;
       alter table new_interfaces add len tinyint;
1315

1316
1.228: Increase size of eventlist arguments (and atstring) from
1317
1318
1319
1320
1321
1322
1323
       tinytext (255 chars) to text (65K chars) to avoid too-short problems
       with program agent command lines. Note that the maximum size allowed
       by table_regex checking will be set to 1024 for now.

       alter table eventlist change arguments arguments text;
       alter table eventlist change atstring atstring text;

1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1.229: Change the definition of the plab_* tables to store module-specific
       data in opaque columns

       alter table plab_slices drop column privkey, \
           drop column pubkey, \
           add column slicemeta text default NULL;
           

       alter table plab_slice_nodes drop column ticketdata, \
           drop column leasedata, \
           add column nodemeta text default NULL
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387

1.230: Change all node_id-type columns to be 32 characters long

       alter table current_reloads modify node_id varchar(32) NOT NULL default '';
       alter table delays modify node_id varchar(32) NOT NULL default '';
       alter table delta_inst modify node_id varchar(32) NOT NULL default '';
       alter table iface_counters modify node_id varchar(32) NOT NULL default '';
       alter table interfaces modify node_id varchar(32) NOT NULL default '';
       alter table last_reservation modify node_id varchar(32) NOT NULL default '';
       alter table linkdelays modify node_id varchar(32) NOT NULL default '';
       alter table new_interfaces modify switch_id varchar(32) default NULL;
       alter table new_nodes modify node_id varchar(32) NOT NULL default '';
       alter table newdelays modify node_id varchar(32) NOT NULL default '';
       alter table next_reserve modify node_id varchar(32) NOT NULL default '';
       alter table node_activity modify node_id varchar(32) NOT NULL default '';
       alter table node_auxtypes modify node_id varchar(32) NOT NULL default '';
       alter table node_features modify node_id varchar(32) NOT NULL default '';
       alter table node_idlestats modify node_id varchar(32) NOT NULL default '';
       alter table node_rusage modify node_id varchar(32) NOT NULL default '';
       alter table node_status modify node_id varchar(32) NOT NULL default '';
       alter table nodeipportnum modify node_id varchar(32) NOT NULL default '';
       alter table nodelog modify node_id varchar(32) NOT NULL default '';
       alter table nodes modify node_id varchar(32) NOT NULL default '';
       alter table nodes modify phys_nodeid varchar(32) default NULL;
       alter table nodeuidlastlogin modify node_id varchar(32) NOT NULL default '';
       alter table ntpinfo modify node_id varchar(32) NOT NULL default '';
       alter table outlets modify node_id varchar(32) NOT NULL default '';
       alter table outlets modify power_id varchar(32) NOT NULL default '';
       alter table partitions modify node_id varchar(32) NOT NULL default '';
       alter table plab_slice_nodes modify node_id varchar(32) NOT NULL default '';
       alter table port_counters modify node_id char(32) NOT NULL default '';
       alter table reserved modify node_id varchar(32) NOT NULL default '';
       alter table scheduled_reloads modify node_id varchar(32) NOT NULL default '';
       alter table state_triggers modify node_id varchar(32) NOT NULL default '';
       alter table switch_paths modify node_id1 varchar(32) default NULL;
       alter table switch_paths modify node_id2 varchar(32) default NULL;
       alter table switch_stack_types modify stack_id varchar(32) NOT NULL default '';
       alter table switch_stacks modify node_id varchar(32) NOT NULL default '';
       alter table switch_stacks modify stack_id varchar(32) NOT NULL default '';
       alter table tiplines modify node_id varchar(32) NOT NULL default '';
       alter table tmcd_redirect modify node_id varchar(32) NOT NULL default '';
       alter table tunnels modify node_id varchar(32) NOT NULL default '';
       alter table uidnodelastlogin modify node_id varchar(32) NOT NULL default '';
       alter table v2pmap modify node_id varchar(32) NOT NULL default '';
       alter table veth_interfaces modify node_id varchar(32) NOT NULL default '';
       alter table widearea_accounts modify node_id varchar(32) NOT NULL default '';
       alter table widearea_delays modify node_id1 varchar(32) NOT NULL default '';
       alter table widearea_delays modify node_id2 varchar(32) NOT NULL default '';
       alter table widearea_nodeinfo modify node_id varchar(32) NOT NULL default '';
       alter table widearea_recent modify node_id1 varchar(32) NOT NULL default '';
       alter table widearea_recent modify node_id2 varchar(32) NOT NULL default '';
       alter table wires modify node_id1 char(32) NOT NULL default '';
       alter table wires modify node_id2 char(32) NOT NULL default '';
1388
1389
1390
1391

1.231 add leaseend column to plab_slices table

       alter table plab_slices add column leaseend datetime default NULL;
1392
1393
1394
1395
1396

1.232 add isadmin column to plab_slices table

       alter table plab_slices add column admin tinyint(1) default 0;

Leigh B. Stoller's avatar
Leigh B. Stoller committed
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1.233: Add location_info table for generating wireless location maps.

	CREATE TABLE location_info (
	  node_id varchar(32) NOT NULL default '',
	  floor varchar(32) default NULL,
	  building varchar(32) default NULL,
	  loc_x int(10) unsigned NOT NULL default '0',
	  loc_y int(10) unsigned NOT NULL default '0',
	  contact tinytext,
	  phone tinytext,
	  room varchar(32) default NULL,
	  PRIMARY KEY  (node_id)
	) TYPE=MyISAM;

1411
1412
1413
1414
1.234: Add a column to node_types identifying plab physical nodes.

       alter table node_types add column isplabphysnode tinyint(4) not null \
	   default 0 after isplabdslice;
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434

1.235: Initial wireless support. 

	alter table virt_lans add column protocol varchar(30) NOT NULL \
		default 'ethernet' AFTER trivial_ok;
	alter table virt_lans add column is_accesspoint tinyint(4) \
		default '0' AFTER protocol;

	CREATE TABLE interface_capabilities (
	  type varchar(30) NOT NULL default '',
	  capkey varchar(64) NOT NULL default '',
	  capval varchar(64) NOT NULL default '',
	  PRIMARY KEY  (type, capkey)
	) TYPE=MyISAM;

	insert into interface_capabilities (type, capkey, capval) \
	    select type,"protocols","ethernet" from interface_types;
	insert into interface_capabilities (type, capkey, capval) \
	    select type,"ethernet_defspeed",max_speed from interface_types;

1435
1436
1437
1438
1439
1.236: Add linktest_level to experiments table. Integer value 0-4
       where 0 means not to run linktest.

       alter table experiments add column linktest_level tinyint(4) \
	   not null default 0 after ipassign_args;
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452

1.237: Add interface_settings table for storing and sending arbitrary
       interface settings to the nodes when they configure their
       interfaces. Added primarily for wireless interface support.

	CREATE TABLE interface_settings (
	  node_id varchar(32) NOT NULL default '',
	  iface varchar(32) NOT NULL default '',
	  capkey varchar(32) NOT NULL default '',
	  capval varchar(64) NOT NULL default '',
	  PRIMARY KEY  (node_id,iface,capkey),
          KEY node_id (node_id)
	) TYPE=MyISAM;
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476

1.238: Add virt_lan_settings and virt_lan_member_settings for storing
       arbitrary interface settings associated with the virtual part
       of the topology. 

	CREATE TABLE virt_lan_member_settings (
	  pid varchar(12) NOT NULL default '',
	  eid varchar(32) NOT NULL default '',
	  vname varchar(32) NOT NULL default '',
	  member varchar(32) NOT NULL default '',
	  capkey varchar(32) NOT NULL default '',
	  capval varchar(64) NOT NULL default '',
	  PRIMARY KEY  (pid,eid,vname,member,capkey)
	) TYPE=MyISAM;

	CREATE TABLE virt_lan_settings (
	  pid varchar(12) NOT NULL default '',
	  eid varchar(32) NOT NULL default '',
	  vname varchar(32) NOT NULL default '',
	  capkey varchar(32) NOT NULL default '',
	  capval varchar(64) NOT NULL default '',
	  PRIMARY KEY  (pid,eid,vname,capkey)
	) TYPE=MyISAM;

1477
1478
1479
1480
1.239: Add "wirelesslans" slot to experiment_resources table.

       alter table experiment_resources add
          wirelesslans smallint(5) unsigned default '0' after shapedlans;
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495

1.240: Add node_hostkeys table for recording sfs/ssh keys.

	CREATE TABLE node_hostkeys (
	  node_id varchar(32) NOT NULL default '',
	  sshrsa_v1 mediumtext,
	  sshrsa_v2 mediumtext,
	  sshdsa_v2 mediumtext,
	  sfshostid varchar(128) default NULL,
	  PRIMARY KEY  (node_id)
	) TYPE=MyISAM;

	insert into node_hostkeys (node_id, sfshostid) \
		select node_id,sfshostid from nodes;
	
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
	Be sure to restart tmcd after you make these changes.

1.241: Change to eventlist table; bump size of vname from 20 to 64. I
       do not see (or remember) any reason for this slot to be 20
       chars, when the name of every other vname slot is 32. I looked
       in the event scheduler and there do not appear to be any
       problems there with bumping it. Note that I choose 64 cause we
       tend to construct agent names that might be longer then 32
       since they are based on real vnames (lan0, node0, etc). 

	alter table eventlist change vname \
		vname varchar(64) NOT NULL default '';

       Make equiv change in virt_agents table:

	alter table virt_agents change vname \
		vname varchar(64) NOT NULL default '';

1514
1515
1516
1517
1518
1519
1520
1521
1.242: Add est_bandwidth and rest_bandwidth to virt_lans, to hold the estimated
       bandwidth that will be used on the link/LAN. This can then be passed to
       assign, to do less conservative bandwidth allocation.

       alter table virt_lans add est_bandwidth int(10) unsigned \
	   default NULL after bandwidth;
       alter table virt_lans add rest_bandwidth int(10) unsigned \
	   default NULL after rbandwidth;
1522
1523
1524
1525
1526
1527
1528

1.243: Add bootdisk_unit to node_types so Mike can pass a unit number
       through to Frisbee in the tmcd loadinfo call.

       alter table node_types add column bootdisk_unit tinyint(3) unsigned \
	   not null default 0 after disktype;

1529
1530
1531
1532
1.244: Add trivlink_maxspeed for Mike.

      alter table node_types add trivlink_maxspeed int(11) unsigned \
	   NOT NULL default '0' after simnode_capacity;
1533
1534
1535
1536
1537
1538
1539

1.245: Change use_ipassign in experiments table to more than a
       boolean:

	alter table experiments change use_ipassign \
		  use_ipassign tinyint(4) NOT NULL default '0';

1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1.246: Change routertype slots to include new type of route stuff.

	alter table nodes change routertype routertype \
		 enum('none','ospf','static','manual','static-ddijk') \
		 NOT NULL default 'none';

	alter table virt_nodes change routertype routertype \
		 enum('none','ospf','static','manual','static-ddijk') \
		 NOT NULL default 'none',	

1550
1551
1552
1553
1554
1.247:  Add a 'useprepass' column to the experiments table to enable or
        disable the assign prepass.

	 alter table experiments add column useprepass tinyint(1) \
		 default 0 not null;
1555
1556
1557
1558
1559

1.248:  Add an 'identifier' column to the new_nodes table to give users a
        way to figure out which new node is which.

         alter table new_nodes add column identifier varchar(255);
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599

1.249:  Add slots to virt_lans to rationalize the relationship between
        virt_lans and virt_nodes. The intent is to migrate away from
        the convention we use in virt_nodes:ips and virt_lans:member
        to a more acceptable representation (one that does not rely
        on textual conventions like space separated lists of colon
        seperate entities. Instead:

	virt_nodes
		vname:	nodeA
        virt_lans
		vname:  link0
		vport:  0
		ip:     1.1.1.2

		vname:  link1
		vport:  1
		ip:     1.1.2.2

	alter table virt_lans add vnode varchar(32) NOT NULL default '' \
		after vname;
	alter table virt_lans add vport tinyint(3) NOT NULL default '0' \
		after vnode;
        alter table virt_lans add ip varchar(15) NOT NULL default '' \
		after vport;

	Then run this script to update these new fields from the
	existing ips,member slots. This must be run after installing
	the parser changes, or you can just run it again. 

		./ipmember.pl

        This is a transitional phase; the old slots will be left in place
        until they are no longer used, at which time we will also add a
        unique key to the table (pid,eid,vname,vnode,vport). assign_wrapper
        will be the hardest to change, but other scripts should be easy. 

	Whats vport about? Rather then rely on IP addresses to form a
	unique key, we use vport (a small integer) so that we can delay the
	IP assignment until later (after initial DB insertion).
1600
1601
1602
1603
1604

1.250: Add another index to virt_lans for above change.

	alter table virt_lans add index vnode (pid,eid,vnode);

1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643

1.251: Add floorimages and buildings tables for wireless floormap
       support. This is rather primitive still; We just need a place to
       define buildings and floors in buildings, so that we do not hardwire
       them into the code. This can get arbitrarily complicated, but not
       until we need it.

	CREATE TABLE buildings (
	  building varchar(32) NOT NULL default '',
	  image_path tinytext,
	  title tinytext NOT NULL,
	  PRIMARY KEY  (building)
	) TYPE=MyISAM;

       The image_path is optional for buildings. The title is a string to
       print along with the images (Merril Engineering Building).
       
	CREATE TABLE floorimages (
	  building varchar(32) NOT NULL default '',
	  floor varchar(32) NOT NULL default '',
	  image_path tinytext,
	  thumb_path tinytext,
	  x1 int(6) NOT NULL default '0',
	  y1 int(6) NOT NULL default '0',
	  x2 int(6) NOT NULL default '0',
	  y2 int(6) NOT NULL default '0',
	  PRIMARY KEY  (building, floor)
	) TYPE=MyISAM;

       The image_path is not optional; it is either an absolute path or a
       filename in $TB/www. The thumb_path is for a tiny view of it. Floor
       is something like 1, 2, 3 but could also be basement, lobby,
       penthouse, etc. The x,y coordinates are intended to be bounding box
       coords of the "interesting" part of the image so that it is easier
       to scale specific entries from the location_info table. But, not
       really sure about this yet; needs more thought and some
       investigation about appropriate ways to store coordinate values like
       this.