database-migrate.txt 147 KB
Newer Older
1
#
Mike Hibler's avatar
Mike Hibler committed
2
# EMULAB-COPYRIGHT
3
# Copyright (c) 2003-2007 University of Utah and the Flux Group.
Mike Hibler's avatar
Mike Hibler committed
4
5
# All rights reserved.
#
6
7
8
9
10
# 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.
11
12
# 

13
14
15
16
17
18
19
20
21
22
23
24
25
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
26
27
28
29
30
31
	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';
32

33
1.114: Add nextosid slot to os_info table for mapping the generic
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
       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.
49

50
1.115   Add node_activity table for new slothd stuff: (Mac)
51
52
53
54
55
56
57
58
59
60

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;

61
62
63
64
65
66
67
68
69
70
71
72
	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);

73
74
75
76
77
78
79
80
81
82
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.

83
84
1.118: Add nobwshaping slot to the virt_lans table. Set in the front
       end, used in assign_wrapper.
85

86
87
88
	alter table virt_lans add nobwshaping tinyint(4) default '0' \
			after uselinkdelay;

89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
        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;
		     
111
112
113
114
115
116
117
118
119
120
121
122
123
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
124
125
126
127
128
129
130
131
132
133
134
135
136
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;
137
138
139
140
141
142
143
144
145

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
146

147
1.123: Create empty 'sitevariables' table with the following schema:
Chad Barb's avatar
   
Chad Barb committed
148
149
150
151
152
153
154
155
156

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

157
1.124: Change address fields in widearea_nodeinfo to tinytext:
Chad Barb's avatar
   
Chad Barb committed
158

159
160
161
162
       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;
163
164
165
166
167
168
169
170
171

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.)

172
       ALTER TABLE widearea_nodeinfo ADD COLUMN external_node_id tinytext;
Chad Barb's avatar
   
Chad Barb committed
173
174
175
176
177
178
179
180
181

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;

182
183
184
185
186
187
188
189
190
       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
191
1.128: Add usr_country column to users table.
192
193

       ALTER TABLE users ADD COLUMN usr_country tinytext AFTER usr_zip;
194
195
196
197

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

       alter table state_triggers add 
198
	     node_id varchar(10) not null default '' first;
199
200
201
202
       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);

203
204
205
206
207
208
209
210
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';
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231

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;
232

233
234
235
1.132: This commit was superseded by 1.133

1.133: Changed the new_nodes and new_interfaces table to use a new unique
236
237
238
239
240
241
242
243
244
245
246
       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);
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
348
349
350
351

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;

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
383
384
385
386
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
387
388
389
	is over 1MB. 

1.137: Minor file formatting changes, no DB schema changes
390
391
392
393
394

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

       drop table lastlogin;
395
396
397
398
399
400
401
402
403
404
405
406
407

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;
408
409
410
411
412
413

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);

414
415
416
417
418
419
420
421
422
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;

423
424
425
426
427
428
429
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;
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
519
520
521
522

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");
	}
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539

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 ".
540
			 "left join experiment_stats as s ");
541
542
543
544
545
546
547
548
549

	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";
	}			
550
551
552
553
554
555
556
557
558
559
560
561

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;
562
563
564
565
566

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

        alter table veth_interfaces change vnode vnode_id varchar(32)
		default NULL;
567
568
569
570
571
572
573

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;
574
575
576
577
578

       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
579
580
581
582
583
584

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';
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605

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;
606
607
608
609
610
611
612
613
614
615
616
617
618

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;
		
619
620
621
622
623
624
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;
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644

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'");
	}
645
646
647
648
649

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;
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667

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.
668
669
670

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

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";
	}
692
693
694
695
696
697
698

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 "";

699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
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;
	}
726
727
728
729
730
731

1.158: Add jail osid to node_types table.

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

732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
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;
749
750
751
752
753
754
755
756
757
758

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

759
760
       (You can run prerender_all _after_ doing a boss-install).

761
762
763
764
       Also remove the vis_experiments table, since no longer needed.

        drop table vis_experiments;

765
1.162: Add a node_type_features table that lists the assign 'features' for each
766
767
768
769
770
       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));
771

772
1.163: Add columns to the new_interfaces table indicating where the interface
773
774
775
776
777
       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);
778

779
1.164: Chage the new_interfaces table to contain card numbers, rather than
780
781
782
783
784
785
786
       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);
787

788
1.165: Added a new column to new_nodes, so we can tell the IP address that
789
790
791
       the node was temporarily given while it's configuring:

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

793
1.166: Kill max_ports and max_cards, replace with max_interfaces.
794
795
796
797
798
799
800
801
802

       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';
803
804


805
1.167: As per Jay's request, start recording the start time as well as
806
807
808
809
810
811
812
813
814
       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;
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832

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.
833
834
835
836
837
838
839

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;

840
841
842
843
844
845
846
847
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;

848
849
850
851
852
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
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
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;

878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
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%');
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918

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';
919

Leigh B. Stoller's avatar
Leigh B. Stoller committed
920
1.178: Add notes field to the users table for storing admin only
921
922
923
924
       notes.

       alter table users add notes text after wideareajailroot;

Leigh B. Stoller's avatar
Leigh B. Stoller committed
925
1.179: Add a table to contain auxiliary types for nodes.
926
927
928
929

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

Leigh B. Stoller's avatar
Leigh B. Stoller committed
931
1.180: Add a table to map auxtypes to node_types table. 
932
933
934
935
936
937
938
939
940
941
942
943
944
945

	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;
946

Leigh B. Stoller's avatar
Leigh B. Stoller committed
947
1.181: Add a node_features table, just like the node_type_features
948
949
950
951
952
953
       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));

954

Leigh B. Stoller's avatar
Leigh B. Stoller committed
955
1.182: Add plabnodes count to the experiment resource table so we can
956
957
958
959
       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
960
961
962
963
964

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);
965
966
967
968

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
969
       alter table os_info add key (path(255));
970
971
972
973
974
975

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);
976
977
978
979
980
981
982

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;
983
984
985
986
987
988
989
990

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;

991
992
993
994
995
996
997
       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

998
999
1000
1001
1002
1003
1004
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));
1005
1006
1007
1008
1009
1010

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);
1011
1012
1013
1014

1.190: Add an index...

       alter table testbed_stats add key (exptidx);
1015
1016
1017
1018
1019

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

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

1021
1.192: Add sfshostid to nodes table. We store that in the filesystem
1022
1023
1024
1025
       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;
1026

1027
1.193: Add a range of VLANs to be used, per switch stack. NULL values will
1028
1029
1030
1031
       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;
1032

1033
1.194: Add a label to the state and mode transitions table. After
1034
1035
1036
1037
       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;
1038
1039
1040
1041
1042
1043
1044
1045

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:

1046
       		sudo ./initkey.pl
1047

1048
1049
1.196: Add new virt_programs table to hold the details of a program
       object.
1050

1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
	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;
1069

1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
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;
1087

1088
1089
1090
1091
1092
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;
1093
1094

1.199: Add a bit to the users table so that we can flag planetlab users:
1095
1096

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

1098
1.200: Add table to hold per table/slot contraint data (min/max len
1099
1100
1101
1102
1103
       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 '',
1104
1105
1106
1107
1108
	  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',
1109
1110
1111
1112
	  comment tinytext,
	  UNIQUE KEY table_name (table_name,column_name)
	) TYPE=MyISAM;

1113
1.202: Change the enum for pcremote_ok to match the phys type that was
1114
1115
1116
1117
       ultimately chosen by Austin.

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

1119
1.203: Add an index to nodetypeXpid_permissions to make it more efficient
1120
1121
1122
       to get a list of what types a project is allowed to use.

       alter table nodetypeXpid_permissions add key (pid);
1123

1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
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;
1147
1148

1.205: Add enum to projects table to store which interface users
1149
1150
1151
1152
1153
       should start with.

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

1154
1.206: Add enum to users table to store which interface user is currently
1155
1156
1157
1158
       set to use. This will replace plab_user bit.

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

1160
1.207: Add fields to support user directed Forgot My Password changes.
1161
1162
1163
1164

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

1166
1.208: Add project table linked_to_us entry.
1167
1168
1169
1170

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

Leigh B. Stoller's avatar
Leigh B. Stoller committed
1171
1172
1.209: Went back to revision 202 and fixed it up; nothing for you to
       do, just skip ahead to the next revision.
1173

1174
1175
1176
1177
1178
1179
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;
1180

1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
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';
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214

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;
1215
1216

1.213: Minor whitespace change to make schemacheck happy.
1217
1218
1219
1220
1221
1222

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;
1223
1224
1225
1226

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

1227
1228
       drop table virt_simnode_attributes;

1229
1230
1231
1232
1233
1234
1235
1236
       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));

1237
1238
1239
1240
1241
1242
1243
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;
1244
1245
1246
1247
1248

1.217: Add slot to nodes table to facilitate better stated debugging.
      
	alter table nodes add stated_tag varchar(32) default NULL \
			after sfshostid;
1249
1250
1251
1252
1253
1254

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';
1255
1256
1257
1258
1259
1260

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;

1261
1262
1263
1264
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.

1265
1266
1267
	alter table experiments add veth_encapsulate tinyint(4) \
		NOT NULL default '1' after sim_reswap_count;

1268
        alter table experiments add allowfixnode tinyint(4) \
1269
	       not null default 0 after veth_encapsulate;
1270

1271
1272
1.221: Fixed allowfixnode to default to 1 instead of 0

1273
        alter table experiments change allowfixnode allowfixnode tinyint(4) \
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
	       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);
1285

1286
1.223: Add jail_osname and delay_osname as temporary (well, perhaps)
1287
1288
1289
1290
1291
1292
1293
1294
1295
       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;
		
1296
1.224: Add rtabid to nodes table, athough it is meaninful only on a
1297
1298
1299
1300
       jailed vnode. Set in assign_wrapper.

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

1302
1.225: Add 'veths' and 'mlinks' to the list of possible osfeatures in
1303
1304
1305
1306
       is_info table.

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

1308
1.226: Add a use_ipassign column to the experiments table, to enable and
1309
1310
1311
1312
1313
1314
1315
       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;
1316

1317
1.227: Add cable and len columns to the new_interfaces table, so that when
1318
1319
1320
1321
       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;
1322

1323
1.228: Increase size of eventlist arguments (and atstring) from
1324
1325
1326
1327
1328
1329
1330
       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;

1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
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, \
1341
           add column nodemeta text default NULL;
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
1388
1389
1390
1391
1392
1393
1394

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 '';
1395
1396
1397
1398

1.231 add leaseend column to plab_slices table

       alter table plab_slices add column leaseend datetime default NULL;
1399
1400
1401
1402
1403

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
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
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;

1418
1419
1420
1421
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;
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441

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;

1442
1443
1444
1445
1446
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;
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459

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;
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483

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;

1484
1485
1486
1487
1.239: Add "wirelesslans" slot to experiment_resources table.

       alter table experiment_resources add
          wirelesslans smallint(5) unsigned default '0' after shapedlans;
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502

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;
	
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
	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 '';

1521
1522
1523
1524
1525
1526
1527
1528
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;
1529
1530
1531
1532
1533
1534
1535

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;

1536
1537
1538
1539
1.244: Add trivlink_maxspeed for Mike.

      alter table node_types add trivlink_maxspeed int(11) unsigned \
	   NOT NULL default '0' after simnode_capacity;
1540
1541
1542
1543
1544
1545
1546

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';

1547
1548
1549
1550
1551
1552
1553
1554
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') \
1555
		 NOT NULL default 'none';
1556

1557
1558
1559
1560
1561
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;
1562
1563
1564
1565
1566

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);
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
1600
1601
1602
1603
1604
1605
1606

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).
1607
1608
1609
1610
1611

1.250: Add another index to virt_lans for above change.

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

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
1644
1645
1646
1647
1648
1649
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.

1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1.253: Add duplicate fields from the location_info table into the new_nodes
       table - this will allow us to place them on the map while they are still
       in limbo.

        ALTER TABLE new_nodes ADD COLUMN (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);
1661
1662
1663
1664
1665
1666
1667

1.254: Add a "force" field in widearea_updates to specify that the
       disk should be reloaded even though it's already registered as
       being the current version.  Good for cleaning up messes...

       ALTER TABLE widearea_updates ADD
           force enum('yes','no') NOT NULL default 'no' after update_started;
1668
1669
1670
1671
1672
1673

1.255: Add a linktest_pid slot to the experiments table so that we can
       kill off linktest async via the web interface.
       
	alter table experiments add linktest_pid int(11) default '0' \
		after linktest_level;
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711

1.256: Add a usemodelnet slot to the experiments table to indicate
       that an experiment prefers to use modelnet for traffic shaping.
       At some future time maybe we can mix modelnet with delay nodes,
       but please do not hold your breath waiting.

	alter table experiments add usemodelnet tinyint(1) NOT NULL \
		default '0' after useprepass;

       Add modelnet_cores and modelnet_edges variables to hold user
       tunable number of physical core nodes and edge nodes to throw
       at a virtual topology. 

	alter table experiments add modelnet_cores tinyint(4) unsigned \
		NOT NULL default '0' after usemodelnet;
        alter table experiments add modelnet_edges tinyint(4) unsigned \
		NOT NULL default '0' after modelnet_cores;

       Add ismodelnet and isphantom slots to node_types table. ismodelnet
       is set for modelnet nodes (which are also going to be isvirt).  Add
       isphantom to mark nodes that do no get underlying physical nodes;
       there will be an entry in the reserved table but no entries in the
       nodes table. Initially, modelnet virtual nodes will all be phantoms.
       Will possibly change later.

	alter table node_types add ismodelnet tinyint(1) NOT NULL \
		default '0' after isvirtnode;
	alter table node_types add isphantom tinyint(1) NOT NULL \
		default '0' after ismodelnet;
		
       Not sure that isphantom is going to work cause of the number of
       queries that join reserved with the nodes table. I might end up
       making nodes dynamic instead, creating and detroying them on the
       fly. I would prefer that isphantom works, but I am willing to live
       with isdynamic since we can use that for all of our virtual nodes.

	alter table node_types add isdynamic tinyint(1) NOT NULL \
		default '0' after isphantom;
1712
1713
1714
1715

1.257: Change node_types:class to length 30 instead of 10.

	alter table node_types change class class varchar(30) default NULL;
1716
1717
1718

1.258: Minor whitespace change in database-create.txt to make
       schemacheck happy. 
1719

1720
1721
1722
1723
1724
1725
1726
1.259: Add modelnet OSIDs to node_types table so we know what image to load
       when doing modelnet. Sure wish we had a better way to do this!

       alter table node_types add modelnetcore_osid varchar(35) \
                 default NULL after jail_osid;
       alter table node_types add modelnetedge_osid varchar(35) 
                 default NULL after modelnetcore_osid;
1727
1728
1729
1730
1731
1732

1.260: Change virt_nodes:type to length 30 instead of 12 (to match the rest
       of the database).

	alter table virt_nodes change type type varchar(30) default NULL;

1733
1734
1735
1736
1737
1738
1739
1740
1.261: Add modelnetcore_osname and modelnetedge_osname to experiments
       table so that user can select them (like jail/delay osname)

        alter table experiments add modelnetcore_osname varchar(20) \
		default NULL after modelnet_edges;
        alter table experiments add modelnetedge_osname varchar(20) \
		default NULL after modelnetcore_osname;

1741
1742
1743
1744
1745
1746
1747
1.262: Change the current_speed enum in the interfaces table to allow "0"
       as a legal value; when interfaces are not in use, set the speed
       to 0.

	alter table interfaces change current_speed \
              current_speed enum('0','10','100','1000') \
	      NOT NULL default '0';
1748
1749
1750

1.263: I fixed typo in previous revision; nothing to do, skip to next
       entry.
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766

1.264: Add some new roles for modelnet nodes, not currently used. Drop
       node_types:isphantom which I added in 1.256 since I decided not
       to use it; just use isdynamic instead. Add an isjailed slot to
       node_types rather then hardwiring that fact to "pcvm" node type.

	alter table reserved change erole erole\
             enum('node','virthost','delaynode','simhost', \
                  'modelnet-core','modelnet-edge') \
              NOT NULL default 'node';
	alter table node_types drop isphantom;
	alter table node_types add isjailed tinyint(1) NOT NULL \
		default '0' after ismodelnet;

        update node_types set isjailed=1 where type='pcvm';
        update node_types set isjailed=1 where type='modelnet-edge';
1767
1768

1.265: Removed a field I never actually added; skip to next entry.
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781

1.266: Change routertype slots to include token for old centralized
       routing calculation (static-old).

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

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

1.267: Remove table definition that snuck in while developing; skip to
       next entry;
Leigh B. Stoller's avatar
Leigh B. Stoller committed
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809

1.268: Add event_groups table to allow users to define groups of
       targets for events. The agent_name refers to an entry in the
       virt_agents table. All members of an eventgroup must of course
       be of the same type.  I am not currently enforcing this.  (note
       that the vnode slot of the eventlist table was effectively
       deprecated quite some time ago; the event scheduler uses the
       vnode slot of the virt_agents entry instead).

	CREATE TABLE event_groups (
	  pid varchar(12) NOT NULL default '',
	  eid varchar(32) NOT NULL default '',
	  idx int(10) unsigned NOT NULL auto_increment,
	  group_name varchar(64) NOT NULL default '',
	  agent_name varchar(64) NOT NULL default '',
	  PRIMARY KEY  (pid,eid,idx),
	  KEY group_name (group_name),
	  KEY agent_name (agent_name)
	) TYPE=MyISAM;


      Also add a boolean to the eventlist table to mark an event as a
      group event. 

        alter table eventlist add isgroup tinyint(1) unsigned \
1810
              default '0' after eventtype;
Leigh B. Stoller's avatar
Leigh B. Stoller committed
1811
1812
1813
1814
1815
1816

      Add mustdelay boolean to virt_lans to relieve assign_wrapper
      from the chore of guessing when a delay node needs to be
      inserted; assign_wrapper can still override of course, but this
      should make it less error prone.

1817
        alter table virt_lans add mustdelay tinyint(1) \
Leigh B. Stoller's avatar
Leigh B. Stoller committed
1818
1819
1820
1821
1822
1823
             default '0' after nobwshaping;
	update virt_lans set mustdelay=q_red;

      Then run:
	
		./mustdelay.pl
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848

1.269: Add new table to generate a per virt_lan index for use with
       veth vlan tags. This would be so much easier if the virt_lans
       table had been split into virt_lans and virt_lan_members.
       Anyway, this table might someday become the per-lan table, with a
       table of member settings. This would reduce the incredible amount of
       duplicate info in virt_lans!

	CREATE TABLE virt_lan_lans (
	  pid varchar(12) NOT NULL default '',
	  eid varchar(32) NOT NULL default '',
	  idx int(11) NOT NULL auto_increment,  
	  vname varchar(32) NOT NULL default '',
	  PRIMARY KEY  (pid,eid,idx),
	  UNIQUE KEY vname (pid,eid,vname)
	) TYPE=MyISAM;

       This arrangement will provide a unique index per virt_lan, within
       each pid,eid. That is, it starts from 1 for each pid,eid. That is
       necessary since the limit is 16 bits, so a global index would
       quickly overflow. The above table is populated with:

	insert into virt_lan_lans (pid, eid, vname) 
            select distinct pid,eid,vname from virt_lans;

1849
1850
1851
1852
1853
1.270: Add a 'role' column to the newnodes table so that we can add nodes
       other than experiment nodes (such as ops) using the newnode system.

       alter table new_nodes add column role varchar(32) not null \
	   default 'testnode';
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919

1.271: Add new tables for experiment firewalls: firewalls, firewall_rules
       and default_firewall_rules:

	CREATE TABLE firewalls (
	  pid varchar(12) NOT NULL default '',
	  eid varchar(32) NOT NULL default '',
	  fwname varchar(32) NOT NULL default '',
	  type enum('ipfw','ipfw2','ipchains') NOT NULL default 'ipfw',
	  style enum('open','closed','basic') NOT NULL default 'basic',
	  PRIMARY KEY  (pid,eid,fwname)
	) TYPE=MyISAM;

	CREATE TABLE firewall_rules (
	  pid varchar(12) NOT NULL default '',
	  eid varchar(32) NOT NULL default '',
	  fwname varchar(32) NOT NULL default '',
	  ruleno int(10) unsigned NOT NULL default '0',
	  rule text NOT NULL,
	  PRIMARY KEY  (pid,eid,fwname,ruleno),
	  KEY fwname (fwname)
	) TYPE=MyISAM;

	CREATE TABLE default_firewall_rules (
	  type enum('ipfw','ipfw2','ipchains') NOT NULL default 'ipfw',
	  style enum('open','closed','basic') NOT NULL default 'basic',
	  enabled tinyint(4) NOT NULL default '0',
	  ruleno int(10) unsigned NOT NULL default '0',
	  rule text NOT NULL,
	  PRIMARY KEY  (type,style,ruleno)
	) TYPE=MyISAM;

       and the associated table_regex's for parsing:

	INSERT INTO table_regex VALUES \
	  ('firewalls','pid','text','redirect','projects:pid',0,0,NULL);
	INSERT INTO table_regex VALUES \
	  ('firewalls','eid','text','redirect','experimenets:eid',0,0,NULL);
	INSERT INTO table_regex VALUES \
	  ('firewalls','fwname','text','redirect','virt_nodes:vname',0,0,NULL);
	INSERT INTO table_regex VALUES \
	  ('firewalls','type','text','regex','^(ipfw|ipfw2|ipchains)$',0,0,NULL);
	INSERT INTO table_regex VALUES \
	  ('firewalls','style','text','regex','^(open|closed|basic)$',0,0,NULL);
	INSERT INTO table_regex VALUES \
	  ('firewall_rules','pid','text','redirect','projects:pid',0,0,NULL);
	INSERT INTO table_regex VALUES \
	  ('firewall_rules','eid','text','redirect','experimenets:eid',0,0,NULL);
	INSERT INTO table_regex VALUES \
	  ('firewall_rules','fwname','text','redirect','virt_nodes:vname',0,0,NULL);
	INSERT INTO table_regex VALUES \
	  ('firewall_rules','ruleno','int','redirect','default:int',0,50000,NULL);
	INSERT INTO table_regex VALUES \
	  ('firewall_rules','rule','text','regex','^\\w[-\\w \\t,/\\{\\}\\(\\)!:\\.]*$',0,1024,NULL);

       Note that there are no regex's for default_firewall_rules since user
       supplied data are never used to fill the table.

       Create the IPFW firewall OSID with:

	INSERT INTO os_info VALUES ('FW-IPFW','emulab-ops','FW-IPFW',\
	    'root',NULL,'IPFW Firewall','FreeBSD','',NULL,'FreeBSD',\
	    '','ping,ssh,ipod,isup,veths,mlinks',0,1,1,'NORMAL',\
	    'emulab-ops-FBSD47-STD',NULL,0);

       Poplulate the default_firewall_rules table using sql/fwrules-create.sql.
1920
1921
1922
1923
1924
1925
1926
1927
       Note that this file include a number of rules involving the networks
       155.98.32.0/23 and 155.98.36.0/22.  These are specific to Utah's
       Emulab and would have to be customized for your environment.
       155.98.32.0/23 refers to the combination of the 'public' and 'private'
       control net subnets as described in doc/setup-network.txt.  If your
       public and private nets cannot be covered by a single CIDR block,
       you will need to create a separate rule for each.
       155.98.36.0/22 refers to the 'control' net for all nodes.
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937

1.272: Table to manage to SSL certs we create for people to access the
       SSL version of the RPC server. In addition to storing text
       versions of the cert and private keys, we also store the serial
       number of the cert so we can "revoke" certificates at the
       server simply by removing them from the table. The SSL server
       checks the serial number to make sure its still valid.

       CREATE TABLE user_sslcerts (
        uid varchar(8) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
1938
        idx int(10) unsigned NOT NULL default '0',
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
	cert text,
	privkey text,
	created datetime default NULL,
	encrypted tinyint(1) NOT NULL default '0',
        PRIMARY KEY  (idx)
       ) TYPE=MyISAM;

       This next table is to deal with the need for a unique index
       that will not start from zero (filling in deleted rows) when
       the DB is dropped. We have several tables like that, and we
       should use this table for those too. 

       CREATE TABLE emulab_indicies (
        name varchar(64) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
1953
        idx int(10) unsigned NOT NULL default '0',
1954
1955
1956
1957
1958
        PRIMARY KEY  (name)
       ) TYPE=MyISAM;

       The certs for all users are created from the doc/UPDATING file.
       Please read that. 
Leigh B. Stoller's avatar
Leigh B. Stoller committed
1959
1960

1.273: Minor fix to previous revision; skip to next entry ...
Leigh B. Stoller's avatar
Leigh B. Stoller committed
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977

1.274: Add usrp_orders table.

	CREATE TABLE usrp_orders (
	  order_id varchar(32) NOT NULL default '',
	  email tinytext,
	  name tinytext,
	  phone tinytext,
	  affiliation tinytext,
	  num_mobos int(11) default '0',
	  num_dboards int(11) default '0',
	  intended_use tinytext,
	  comments tinytext,
	  order_date datetime default NULL,
	  modify_date datetime default NULL,
	  PRIMARY KEY  (order_id)
	) TYPE=MyISAM;
1978

1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
       Also added a scale field to the floorimages table.

	ALTER TABLE floorimages ADD COLUMN scale tinyint(4) 
	  NOT NULL default '1' AFTER floor;
	ALTER TABLE floorimages DROP PRIMARY KEY;
	ALTER TABLE floorimages ADD PRIMARY KEY (building,floor,scale);

	REPLACE INTO floorimages (building, floor, scale, image_path, thumb_path)
	  VALUES ('MEB', 1, 1, 'meb1fl-1.jpg', 'meb1fl-thumb.jpg'),
	         ('MEB', 1, 2, 'meb1fl-2.jpg', 'meb1fl-thumb.jpg'),
	         ('MEB', 1, 3, 'meb1fl-3.jpg', 'meb1fl-thumb.jpg'),
	         ('MEB', 1, 4, 'meb1fl-4.jpg', 'meb1fl-thumb.jpg'),
1991
	         ('MEB', 1, 5, 'meb1fl-5.jpg', 'meb1fl-thumb.jpg'),
1992
1993
1994
1995
	         ('MEB', 2, 1, 'meb2fl-1.jpg', 'meb2fl-thumb.jpg'),
	         ('MEB', 2, 2, 'meb2fl-2.jpg', 'meb2fl-thumb.jpg'),
	         ('MEB', 2, 3, 'meb2fl-3.jpg', 'meb2fl-thumb.jpg'),
	         ('MEB', 2, 4, 'meb2fl-4.jpg', 'meb2fl-thumb.jpg'),
1996
	         ('MEB', 2, 5, 'meb2fl-5.jpg', 'meb2fl-thumb.jpg'),
1997
1998
1999
2000
	         ('MEB', 3, 1, 'meb3fl-1.jpg', 'meb3fl-thumb.jpg'),
	         ('MEB', 3, 2, 'meb3fl-2.jpg', 'meb3fl-thumb.jpg'),
	         ('MEB', 3, 3, 'meb3fl-3.jpg', 'meb3fl-thumb.jpg'),
	         ('MEB', 3, 4, 'meb3fl-4.jpg', 'meb3fl-thumb.jpg'),
2001
	         ('MEB', 3, 5, 'meb3fl-5.jpg', 'meb3fl-thumb.jpg'),
2002
2003
2004
	         ('MEB', 4, 1, 'meb4fl-1.jpg', 'meb4fl-thumb.jpg'),
	         ('MEB', 4, 2, 'meb4fl-2.jpg', 'meb4fl-thumb.jpg'),
	         ('MEB', 4, 3, 'meb4fl-3.jpg', 'meb4fl-thumb.jpg'),
2005
2006
	         ('MEB', 4, 4, 'meb4fl-4.jpg', 'meb4fl-thumb.jpg'),
	         ('MEB', 4, 5, 'meb4fl-5.jpg', 'meb4fl-thumb.jpg');
2007

2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
1.275: Add timed-based mapping table for generic OSIDs.  This augments the
       nextosid mechinism of 1.114 making it possible to map a generic *-STD
       OSID based on the time in which an experiment is created.  This
       provides backward compatibility for old experiments when the standard
       images are changed.

       The osid_map table lookup is triggered when the value of the nextosid
       field is set to 'MAP:osid_map'.  The nextosid also continues to behave
       as before: if it contains a valid osid, that OSID value is used to map
       independent of the experiment creation time.  The two styles can also
       be mixed, for example FBSD-JAIL has a nextosid of FBSD-STD which in
       turn is looked up and redirects to the osid_map and selects one of
       FBSD47-STD or FBSD410-STD depending on the time.

	CREATE TABLE osid_map (
	  osid varchar(35) NOT NULL default '',
	  btime datetime NOT NULL default '1000-01-01 00:00:00',
	  etime datetime NOT NULL default '9999-12-31 23:59:59',
	  nextosid varchar(35) default NULL,
	  PRIMARY KEY  (osid,btime,etime)
	) TYPE=MyISAM;

       Yeah, yeah, I'm using another magic date as a sentinel value.
       Tell ya what, in 7995 years, find out where I'm buried, dig me up,
       and kick my ass for being so short-sighted...

       The following commands are not strictly needed, they just give
       an example, default population of the table.  They cause the standard
       images to be revectored through the table and then remapped, based on
       two time ranges, to the exact same image.  Obviously, the second set
       would normally be mapped to a different set of images (say RHL90 and
       FBSD410):

	INSERT INTO osid_map (osid,etime,nextosid) VALUES \
	  ('RHL-STD','2004-09-08 08:59:59','emulab-ops-RHL73-STD');
	INSERT INTO osid_map (osid,etime,nextosid) VALUES \
	  ('FBSD-STD','2004-09-08 08:59:59','emulab-ops-FBSD47-STD');

	INSERT INTO osid_map (osid,btime,nextosid) VALUES \
	  ('RHL-STD','2004-09-08 09:00:00','emulab-ops-RHL73-STD');
	INSERT INTO osid_map (osid,btime,nextosid) VALUES \
	  ('FBSD-STD','2004-09-08 09:00:00','emulab-ops-FBSD47-STD');

	UPDATE os_info SET nextosid='MAP:osid_map' \
	  WHERE osname IN ('RHL-STD','FBSD-STD');
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063

1.276: Add inner_elab_role for elab-in-elab. Add elab_in_elab boolean
       to the experiments table. Not sure these will survive.

	alter table experiments add elab_in_elab tinyint(1) NOT NULL \
		default '0' after modelnetedge_osname;

	alter table virt_nodes add \
	      inner_elab_role enum('boss', 'ops', 'node') \
	      default NULL after fixed;

2064
2065
2066
2067
2068
1.277: Add cd_version to nodes table, only meaningful to CD booted nodes.
       Returned from nodes via tmcd.

       alter table nodes add cd_version varchar(32) default NULL;

2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
1.278: Add partial support for hardware control net vlan implementation of
       firewalls.  The code to manipulate the control net switch has not
       yet been done and will no doubt lead to more DB changes.  The hardware
       assisted firewall is done at the moment by specifying a different type
       'ipfw2-vlan'.

       Note that you will need a FBSD410-IPFW2 image which is a standard
       image but with an IPFW2 enabled kernel and new versions of ipfw and
       libaliases (as mentioned in the ipfw man page).

       alter table default_firewall_rules change type type \
           enum('ipfw','ipfw2','ipchains','ipfw2-vlan') not null default 'ipfw';

       alter table firewalls change type type \
           enum('ipfw','ipfw2','ipchains','ipfw2-vlan') not null default 'ipfw';

       alter table firewalls add vlan int(11) default NULL;

       update table_regex set check='^(ipfw|ipfw2|ipchains|ipfw2-vlan)$' \
           where table_name='firewalls' and column_name='type';

       INSERT INTO os_info VALUES ('FW-IPFW2','emulab-ops','FW-IPFW2', \
           'root',NULL,'IPFW2 Firewall','FreeBSD','',NULL,'FreeBSD','', \
	   'ping,ssh,ipod,isup,veths,mlinks',0,1,1,'NORMAL', \
	   'emulab-ops-FBSD410-IPFW2',NULL,0);

       INSERT INTO os_info VALUES ('FBSD410-IPFW2','emulab-ops', \
           'emulab-ops-FBSD410-IPFW2','root',NULL, \
	   'FreeBSD 4.10 with IPFW2','FreeBSD','4.10',NULL,NULL,'', \
	   'ping,ssh,ipod,isup,veths,mlinks',1,1,1,'NORMALv2',NULL,NULL,0);
2099
2100
2101
2102
2103
2104

1.279: Still more VLAN-enforced firewall changes.

       alter table nodes add cnet_vlan int(11) default NULL;

       alter table firewalls add index(vlan);
2105

2106
1.280: Add another role for interfaces; an outer control network role,
2107
2108
2109
2110
2111
2112
       used for ElabinElab.

       alter table interfaces change role role \
          enum('ctrl','expt','jail','fake','other','gw','outer_ctrl') \
	  default NULL;

2113
1.281: Add elabinelab_vlans table for mapping inner elab vlan ids to
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
       auto assigned outer elab vlan ids. 

	CREATE TABLE elabinelab_vlans (
	  pid varchar(12) NOT NULL default '',
	  eid varchar(32) NOT NULL default '',
	  inner_id int(11) unsigned NOT NULL default 0,
	  outer_id int(11) unsigned NOT NULL default 0,
	  PRIMARY KEY  (pid,eid,inner_id)
	) TYPE=MyISAM;

2124
1.282: Add "archived" flag to webnews table for hiding entries.
2125

2126
2127
2128
2129
	alter table webnews add archived tinyint(1) NOT NULL \
                default '0' after body;
	alter table webnews add archived_date datetime default NULL \
	        after archived;
2130

2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
1.283: Fix errors in last revision; skip to next entry.

1.284: Fix a firewall botch (see 1.279).  Should have placed the cnet_vlan
       column in the reserved table and not the nodes table:

	alter table reserved add cnet_vlan int(11) default NULL;

       The nodes cnet_vlan column should be NULL for all nodes since
       that column was never used before now.  Verify this by doing:

	select node_id from nodes where cnet_vlan is not null;

       The select should return no rows.  If it does, and you want to
       save those values, copy all the non-NULL values from nodes to the
       appropriate place in reserved.  Then do:

	alter table nodes drop column cnet_vlan;
2148
2149
2150
2151
2152

1.285: More firewall state.  Possibly temporary hack, store unique vlan
       ID in firewalls table:

	alter table firewalls add vlanid int(11) default NULL;
2153
2154

1.286: ElabinElab and Firewall stuff
2155
1.287: 
2156
2157
2158
2159
2160
2161
2162
2163

	alter table experiments add elabinelab_eid varchar(32) \
            default NULL after elab_in_elab;
	alter table experiments add security_level tinyint(1) NOT NULL \
            default '0' after elabinelab_eid;

	alter table experiment_stats add elabinelab tinyint(1) NOT NULL \
            default '0' after batch;