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

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

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

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

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

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

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

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

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

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

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

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

1.116   Add last_report to node_activity table: (Mac)

alter table node_activity add last_report datetime not null;

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

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

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

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

        Nothing special needs to be done.

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

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

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

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

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

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

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

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

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

       These fields had never been used, so the type change and init
       are okay.
Chad Barb's avatar
Chad Barb committed
142

143
1.123: Create empty 'sitevariables' table with the following schema:
Chad Barb's avatar
Chad Barb committed
144 145 146 147 148 149 150 151 152

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

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

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

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

168
       ALTER TABLE widearea_nodeinfo ADD COLUMN external_node_id tinytext;
169 170 171 172 173 174 175 176 177

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

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

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

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

187
1.128: Add usr_country column to users table.
188 189

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

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

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

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

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

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

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

229 230 231
1.132: This commit was superseded by 1.133

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

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

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

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


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


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


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

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

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

       Also add testbed wide stats table. 

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

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

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

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

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

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

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

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

       alter table experiment_stats add index(idx);

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

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

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

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

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

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

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

      Be clear about what index is what:

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

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

      To populate the new table from the old table:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

       Must also init that field for existing experiments.

        update experiments set expt_swap_uid=expt_head_uid
		where expt_swapped is not null;
575 576 577 578 579 580

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

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

	ALTER TABLE experiments ADD COLUMN noswap_reason tinytext 
	     AFTER priority;

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

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

	ALTER TABLE experiments ADD COLUMN noidleswap_reason tinytext 
	      AFTER idleswap_timeout;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1.156: Add uid to testbed_stats table:

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

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

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

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

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

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

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

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

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

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

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

1.158: Add jail osid to node_types table.

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

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

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

1.160: Put the thumbnail into the resources table. 

	alter table experiment_resources add thumbnail mediumblob
			 after maxlinks;

       Then create the thumbs in the DB

	        prerender_all -t

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

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

        drop table vis_experiments;

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


       Now we have to set them.

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

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

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

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

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

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

916
1.178: Add notes field to the users table for storing admin only
917 918 919 920
       notes.

       alter table users add notes text after wideareajailroot;

921
1.179: Add a table to contain auxiliary types for nodes.
922 923 924 925

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

927
1.180: Add a table to map auxtypes to node_types table. 
928 929 930 931 932 933 934 935 936 937 938 939 940 941

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

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

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

943
1.181: Add a node_features table, just like the node_type_features
944 945 946 947 948 949
       table, but for individual nodes.

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

950

951
1.182: Add plabnodes count to the experiment resource table so we can
952 953 954 955
       track exactly how many slices an experiment is using.

       alter table experiment_resources add
          plabnodes smallint(5) unsigned default '0' after wanodes;
956 957 958 959 960

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

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

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

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

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

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

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

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

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

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

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

		sql/newstates.pl | mysql tbdb

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

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

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

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

1.190: Add an index...

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

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

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

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

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

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

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

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

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

1.195: Add secret key for the event system.

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

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

1042
       		sudo ./initkey.pl
1043

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

       alter table nodetypeXpid_permissions add key (pid);
1119

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

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

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

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

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

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

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

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

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

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

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

1170 1171 1172 1173 1174 1175
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;
1176

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

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;
1211 1212

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

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;
1219 1220 1221 1222

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

1223 1224
       drop table virt_simnode_attributes;

1225 1226 1227 1228 1229 1230 1231 1232
       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));

1233 1234 1235 1236 1237 1238 1239
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;
1240 1241 1242 1243 1244

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

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';
1251 1252 1253 1254 1255 1256

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;

1257 1258 1259 1260
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.

1261 1262 1263
	alter table experiments add veth_encapsulate tinyint(4) \
		NOT NULL default '1' after sim_reswap_count;

1264
        alter table experiments add allowfixnode tinyint(4) \
1265
	       not null default 0 after veth_encapsulate;
1266

1267 1268
1.221: Fixed allowfixnode to default to 1 instead of 0

1269
        alter table experiments change allowfixnode allowfixnode tinyint(4) \
1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280
	       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);
1281

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

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

1298
1.225: Add 'veths' and 'mlinks' to the list of possible osfeatures in
1299 1300 1301 1302
       is_info table.

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

1304
1.226: Add a use_ipassign column to the experiments table, to enable and
1305 1306 1307 1308 1309 1310 1311
       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;
1312

1313
1.227: Add cable and len columns to the new_interfaces table, so that when
1314 1315 1316 1317
       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;
1318

1319
1.228: Increase size of eventlist arguments (and atstring) from
1320 1321 1322 1323 1324 1325 1326
       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;

1327 1328 1329 1330 1331 1332 1333 1334 1335 1336
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, \
1337
           add column nodemeta text default NULL;
1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390

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 '';
1391 1392 1393 1394

1.231 add leaseend column to plab_slices table

       alter table plab_slices add column leaseend datetime default NULL;
1395 1396 1397 1398 1399

1.232 add isadmin column to plab_slices table

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

Leigh Stoller's avatar
Leigh Stoller committed
1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413
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;

1414 1415 1416 1417
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;
1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437

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;

1438 1439 1440 1441 1442
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;
1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455

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;
1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479

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;

1480 1481 1482 1483
1.239: Add "wirelesslans" slot to experiment_resources table.

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

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;
	
1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516
	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 '';

1517 1518 1519 1520 1521 1522 1523 1524
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;
1525 1526 1527 1528 1529 1530 1531

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;

1532 1533 1534 1535
1.244: Add trivlink_maxspeed for Mike.

      alter table node_types add trivlink_maxspeed int(11) unsigned \
	   NOT NULL default '0' after simnode_capacity;
1536 1537 1538 1539 1540 1541 1542

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

1543 1544 1545 1546 1547 1548 1549 1550
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') \
1551
		 NOT NULL default 'none';
1552

1553 1554 1555 1556 1557
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;
1558 1559 1560 1561 1562

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);
1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602

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).
1603 1604 1605 1606 1607

1.250: Add another index to virt_lans for above change.

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

1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645
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.

1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656
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);
1657 1658 1659 1660 1661 1662 1663

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;
1664 1665 1666 1667 1668 1669

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;
1670 1671 1672 1673 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

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;
1708 1709 1710 1711

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

	alter table node_types change class class varchar(30) default NULL;
1712 1713 1714

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

1716 1717 1718 1719 1720 1721 1722
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;
1723 1724 1725 1726 1727 1728

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;

1729 1730 1731 1732 1733 1734 1735 1736
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;

1737 1738 1739 1740 1741 1742 1743
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';
1744 1745 1746

1.263: I fixed typo in previous revision; nothing to do, skip to next
       entry.
1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762

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

1.265: Removed a field I never actually added; skip to next entry.
1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777

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';
1778 1779 1780

1.267: Remove table definition that snuck in while developing; skip to
       next entry;
1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805

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 \
1806
              default '0' after eventtype;
1807 1808 1809 1810 1811 1812

      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.

1813
        alter table virt_lans add mustdelay tinyint(1) \
1814 1815 1816 1817 1818 1819
             default '0' after nobwshaping;
	update virt_lans set mustdelay=q_red;

      Then run:
	
		./mustdelay.pl
1820 1821 1822 1823 1824 1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844

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;

1845 1846 1847 1848 1849
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';
1850 1851 1852 1853 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

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.
1916 1917 1918 1919 1920 1921 1922 1923
       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.
1924 1925 1926 1927 1928 1929 1930 1931 1932 1933

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 Stoller's avatar
Leigh Stoller committed
1934
        idx int(10) unsigned NOT NULL default '0',
1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948
	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 Stoller's avatar
Leigh Stoller committed
1949
        idx int(10) unsigned NOT NULL default '0',
1950 1951 1952 1953 1954
        PRIMARY KEY  (name)
       ) TYPE=MyISAM;

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

1.273: Minor fix to previous revision; skip to next entry ...
Leigh Stoller's avatar
Leigh Stoller committed
1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973

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

1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986
       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'),
1987
	         ('MEB', 1, 5, 'meb1fl-5.jpg', 'meb1fl-thumb.jpg'),
1988 1989 1990 1991
	         ('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'),
1992
	         ('MEB', 2, 5, 'meb2fl-5.jpg', 'meb2fl-thumb.jpg'),
1993 1994 1995 1996
	         ('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'),
1997
	         ('MEB', 3, 5, 'meb3fl-5.jpg', 'meb3fl-thumb.jpg'),
1998 1999 2000
	         ('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'),
2001 2002
	         ('MEB', 4, 4, 'meb4fl-4.jpg', 'meb4fl-thumb.jpg'),
	         ('MEB', 4, 5, 'meb4fl-5.jpg', 'meb4fl-thumb.jpg');
2003

2004 2005 2006 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
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');
2049 2050 2051 2052 2053 2054 2055 2056 2057 2058 2059

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;

2060 2061 2062 2063 2064
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;

2065 2066 2067 2068 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
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);
2095 2096 2097 2098 2099 2100

1.279: Still more VLAN-enforced firewall changes.

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

       alter table firewalls add index(vlan);
2101

2102
1.280: Add another role for interfaces; an outer control network role,
2103 2104 2105 2106 2107 2108
       used for ElabinElab.

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

2109
1.281: Add elabinelab_vlans table for mapping inner elab vlan ids to
2110 2111 2112 2113 2114 2115 2116 2117 2118 2119
       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;

2120
1.282: Add "archived" flag to webnews table for hiding entries.
2121

2122 2123 2124 2125
	alter table webnews add archived tinyint(1) NOT NULL \
                default '0' after body;
	alter table webnews add archived_date datetime default NULL \
	        after archived;
2126

2127 2128 2129 2130 2131 2132 2133 2134 2135 2136 2137 2138 2139 2140 2141 2142 2143
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;
2144 2145 2146 2147 2148

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

	alter table firewalls add vlanid int(11) default NULL;
2149 2150

1.286: ElabinElab and Firewall stuff
2151
1.287: 
2152 2153 2154 2155 2156 2157 2158 2159

	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;
2160
	alter table experiment_stats add elabinelab_exptidx int(10) \
2161 2162 2163
             unsigned default NULL after elabinelab;
	alter table experiment_stats add security_level tinyint(1) NOT NULL \
            default '0' after elabinelab_exptidx;
2164 2165 2166 2167

	alter table reserved add \
              inner_elab_role enum('boss','ops','node') default NULL \
              after cnet_vlan;
2168

2169
1.288: Add a 'rebootable' bit to the node_types table to indicate that a 
2170 2171 2172 2173 2174 2175
       given type should not be attempted to be rebooted. This should be
       temporary, and will go away when we have node_capabilities:

         alter table node_types add (isrebootable tinyint(1) default '1');
	

2176
1.289: Add "emulab" style of firewall for elabinelab
2177 2178 2179 2180 2181

       ALTER table firewalls MODIFY style \
	 enum('open','closed','basic','emulab') not NULL default 'basic';
       ALTER table default_firewall_rules MODIFY style \
	 enum('open','closed','basic','emulab') not NULL default 'basic';
2182 2183
       UPDATE table_regex SET check='^(open|closed|basic|emulab)$' WHERE \
	 table_name='firewalls' AND column_name='style';
2184 2185 2186 2187

       and update the default rules:

       mysql tbdb < fwrules-create.sql
2188

2189
1.290: Add conversion factor floorimages that converts from pixels to
2190 2191 2192 2193
      meters.

      alter table floorimages add pixels_per_meter float(10,3) \
             NOT NULL default '0.000' after scale;
2194

2195
1.291: Add Windows to the OS list in the os_info table.
2196 2197 2198 2199

	 alter table os_info modify column \
	   OS enum('Unknown','Linux','FreeBSD','NetBSD','OSKit','Windows','Other') \
	   NOT NULL default 'Unknown';
2200

2201
1.292: Add a settable Windows password.  We derive a default password
2202 2203 2204 2205
       from the Unix usr_pswd MD5 hash string.

       alter table users add column usr_w_pswd tinytext after usr_pswd;

2206
1.293: Add "paniced" bit to experiments table.
2207 2208 2209 2210 2211

	alter table experiments add paniced tinyint(1) NOT NULL \
            default '0' after security_level;
	alter table experiments add panic_date datetime default NULL \
            after paniced;
2212

2213
1.294: Add a "leader" column to the switch_stack_types table so that we no
2214 2215 2216 2217 2218 2219 2220 2221 2222 2223 2224 2225 2226 2227
       longer need to assume that the leader of a stack is the switch after
       which it was named - we can now name stacks things like 'Control' or
       'Experiment'.

       alter table switch_stack_types add column leader varchar(32) \
         default NULL;

       Set the leader names like this:

       update switch_stack_types set leader=stack_id;

       Then, determine the name of the experimental net stack like so:

       select distinct stack_id from nodes as n left join switch_stacks as \
2228
         s on n.node_id=s.node_id where n.role='testswitch';
2229 2230 2231 2232

       You can then set the name of the experimental stack like this
       (substituting in the name of the stack you just found):

2233
       update switch_stacks set stack_id='Experiment' where \
2234
         stack_id='<stackid>';
2235
       update switch_stack_types set stack_id='Experiment' where \
2236 2237 2238
         stack_id='<stackid>';

       Then, do the same with the control net:
2239 2240 2241 2242 2243 2244 2245

       select distinct stack_id from nodes as n left join switch_stacks as \
         s on n.node_id=s.node_id where n.role='ctrlswitch';
       update switch_stacks set stack_id='Control' where \
         stack_id='<stackid>';
       update switch_stack_types set stack_id='Control' where \
         stack_id='<stackid>';
2246

2247
1.295: Add some battery stuff for robots to the nodes table.
2248 2249 2250 2251 2252

        alter table nodes add battery_voltage float default NULL;
        alter table nodes add battery_percentage float default NULL;
        alter table nodes add battery_timestamp int(10) unsigned default NULL;

2253
1.296: Add stuff for capturing boot errors.
2254 2255 2256 2257 2258 2259 2260 2261 2262 2263

        alter table nodes add boot_errno int(11) NOT NULL default '0';

        CREATE TABLE node_bootlogs (
          node_id varchar(32) NOT NULL default '',
          bootlog text,
          bootlog_timestamp datetime default NULL,
          PRIMARY KEY  (node_id)
        ) TYPE=MyISAM;

2264
1.297: Fix virt_vtypes table.
2265

2266 2267 2268
         alter table virt_vtypes change eid eid varchar(32) NOT NULL \
                default '';

2269
1.298: Add webcams support.
2270 2271 2272 2273 2274 2275 2276 2277

         CREATE TABLE webcams (
          id int(11) unsigned NOT NULL default '0',
          server varchar(64) NOT NULL default '',
          last_update datetime default NULL,
          PRIMARY KEY  (id)
         ) TYPE=MyISAM;

2278
1.299: Add obstacles table for the robots.
2279 2280 2281 2282 2283 2284 2285 2286 2287 2288 2289 2290 2291 2292

         CREATE TABLE obstacles (
          obstacle_id int(11) unsigned NOT NULL auto_increment,
          floor varchar(32) default NULL,
          building varchar(32) default NULL,
          x1 int(10) unsigned NOT NULL default '0',
          y1 int(10) unsigned NOT NULL default '0',
          z1 int(10) unsigned NOT NULL default '0',
          x2 int(10) unsigned NOT NULL default '0',
          y2 int(10) unsigned NOT NULL default '0',
          z2 int(10) unsigned NOT NULL default '0',
          description tinytext,
          PRIMARY KEY  (obstacle_id)
         ) TYPE=MyISAM;
2293

2294
1.300: Add default_firewall_vars table.  These are global (not
2295 2296 2297 2298 2299 2300 2301 2302 2303 2304 2305 2306 2307 2308 2309 2310 2311 2312 2313 2314
       per-experiment!) variables that get expanded on the client-side
       before using them.  Intended to keep the default rules
       site-independent.

	CREATE TABLE default_firewall_vars (
	  name varchar(255) NOT NULL default '',
	  value text,
	  PRIMARY KEY  (name)
	) TYPE=MyISAM;

       Populate the vars table at you site by:

	cd <your_build_tree>/firewall
	gmake insertvars

       Update the default_firewall_rules with (NOTE: this will clobber
       any rules you have added to default_firewall_rules!):

	cd <your_build_tree>/firewall
	gmake insertrules
2315

2316
1.301: Allow a switch to be in multiple stacks, and add a column that records
2317 2318 2319 2320 2321
       of the memberships is the primary one.

        alter table switch_stacks drop primary key;
	alter table switch_stacks add index (node_id);
	alter table switch_stacks add column (is_primary tinyint(1) not null default '1');
2322

2323
1.302: Add node history table at Mike's request to track what
2324 2325 2326 2327 2328 2329 2330 2331 2332 2333 2334 2335 2336
       experiments are assigned to over time.

	CREATE TABLE node_history (
	  history_id int(10) unsigned NOT NULL auto_increment,
	  node_id varchar(32) NOT NULL default '',
	  op enum('alloc','free','move') NOT NULL default 'alloc',
	  uid varchar(8) NOT NULL default '',
	  exptidx int(10) unsigned default NULL,
	  stamp int(10) unsigned default NULL,
	  PRIMARY KEY  (history_id),
	  KEY node_id (node_id)
	) TYPE=MyISAM;

2337
1.303: Add a "lockdown" bit to the experiments table to prevent
2338 2339 2340 2341
       accidental swaps, even by admin people.

         alter table experiments add lockdown tinyint(1) NOT NULL \
              default '0' after security_level;
2342 2343 2344 2345 2346 2347 2348 2349 2350 2351 2352 2353 2354 2355

1.304: Add a table for the starting location of the robots.

	CREATE TABLE virt_node_startloc (
	  pid varchar(12) NOT NULL default '',
	  eid varchar(32) NOT NULL default '',
	  vname varchar(32) NOT NULL default '',
	  building varchar(32) NOT NULL default '',
	  floor varchar(32) NOT NULL default '',
	  loc_x float NOT NULL default '0',
	  loc_y float NOT NULL default '0',
	  orientation float NOT NULL default '0',
	  PRIMARY KEY  (pid,eid,vname)
	) TYPE=MyISAM;
2356 2357 2358 2359 2360 2361 2362 2363 2364

1.305: Fix up minimum_nodes and maximum_nodes columns; they were
       tinyints which is too small.

        alter table experiments change minimum_nodes minimum_nodes \
            int(6) unsigned default NULL;

        alter table experiments change maximum_nodes maximum_nodes \
            int(6) unsigned default NULL; 
2365 2366 2367 2368 2369

1.306: Add a parent slot to the eventlist table for tying events to a
       timeline or sequence.

	alter table eventlist add parent varchar(64) not null default '';
2370 2371 2372 2373 2374 2375 2376 2377 2378 2379 2380 2381 2382 2383 2384 2385 2386 2387 2388 2389 2390 2391 2392 2393 2394 2395 2396 2397

1.307: Policy stuff. See long comment in db/libadminctrl.pm.

	CREATE TABLE global_policies (
	  policy varchar(32) NOT NULL default '',
	  auxdata varchar(64) NOT NULL default '',
	  test varchar(32) NOT NULL default '',
	  count int(10) NOT NULL default '0',
	  PRIMARY KEY  (policy,auxdata)
	) TYPE=MyISAM;

	CREATE TABLE group_policies (
	  pid varchar(12) NOT NULL default '',
	  gid varchar(12) NOT NULL default '',
	  policy varchar(32) NOT NULL default '',
	  auxdata varchar(64) NOT NULL default '',
	  count int(10) NOT NULL default '0',
	  PRIMARY KEY  (pid,gid,policy,auxdata)
	) TYPE=MyISAM;

	CREATE TABLE user_policies (
	  uid varchar(8) NOT NULL default '',
	  policy varchar(32) NOT NULL default '',
	  auxdata varchar(64) NOT NULL default '',
	  count int(10) NOT NULL default '0',
	  PRIMARY KEY  (uid,policy,auxdata)
	) TYPE=MyISAM;