database-migrate.txt 172 KB
Newer Older
1
#
Mike Hibler's avatar
Mike Hibler committed
2
# EMULAB-COPYRIGHT
3
# Copyright (c) 2003-2009 University of Utah and the Flux Group.
Mike Hibler's avatar
Mike Hibler committed
4 5
# All rights reserved.
#
6 7 8 9 10
# Anytime you change database-create.sql, but sure to put any special
# (sql) instructions that need to be executed to bring an existing DB
# into compliance.
#
# Please indicate the CVS revision number your instructions refer to.
11 12
# 

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

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

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

Leigh Stoller's avatar
Leigh Stoller committed
26 27 28 29 30 31
	update node_types set disktype='ad' where type='pc600';
	update node_types set disktype='ad' where type='pc850';
	update node_types set disktype='ad' where type='pc1500';
	update node_types set disktype='ad' where type='pcL440GX';
	update node_types set disktype='ad' where type='pc2000';
	update node_types set disktype='ad' where type='pc601';
32

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

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

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

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

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

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

61 62 63 64 65 66 67 68 69 70 71 72
	No population of the table is necessary. It will self populate
	as nodes start to get activity reports.

1.116   Add last_report to node_activity table: (Mac)

alter table node_activity add last_report datetime not null;

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

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

73 74 75 76 77 78 79 80 81 82
1.117:  Add emulated and uselinkdelay slots to the virt_lans
        table. Set in the front end, used in assign_wrapper.

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

        Nothing special needs to be done.

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

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

89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110
        Nothing special needs to be done.

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

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

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

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

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

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

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

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

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

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

159 160 161 162
       ALTER TABLE widearea_nodeinfo CHANGE city city tinytext;
       ALTER TABLE widearea_nodeinfo CHANGE state state tinytext;
       ALTER TABLE widearea_nodeinfo CHANGE country country tinytext;
       ALTER TABLE widearea_nodeinfo CHANGE zip zip tinytext;
163 164 165 166 167 168 169 170 171

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

172
       ALTER TABLE widearea_nodeinfo ADD COLUMN external_node_id tinytext;
173 174 175 176 177 178 179 180 181

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

182 183 184 185 186 187 188 189 190
       ALTER TABLE sitevariables DROP INDEX name;

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

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

191
1.128: Add usr_country column to users table.
192 193

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

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

       alter table state_triggers add 
198
	     node_id varchar(10) not null default '' first;
199 200 201 202
       update state_triggers set node_id="*";
       alter table state_triggers drop primary key;
       alter table state_triggers add primary key (node_id,op_mode,state);

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

       alter table users add widearearoot tinyint(4) default '0';
       alter table users add wideareajailroot tinyint(4) default '0';
211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231

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

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

233 234 235
1.132: This commit was superseded by 1.133

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

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

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

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


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


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


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

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

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

       Also add testbed wide stats table. 

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

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

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

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

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

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

	CREATE TABLE veth_interfaces (
	  node_id varchar(10) NOT NULL default '',
	  veth_id int(10) unsigned NOT NULL auto_increment,
	  mac varchar(12) NOT NULL default '000000000000',
	  IP varchar(15) default NULL,
	  iface varchar(10) NOT NULL default '',
	  PRIMARY KEY  (node_id,veth_id),
	  KEY IP (IP)
	) TYPE=MyISAM;
408 409 410 411 412 413

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

       alter table experiment_stats add index(idx);

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

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

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

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

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

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

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

      Be clear about what index is what:

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

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

      To populate the new table from the old table:

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

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

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

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

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

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

	my $query_result =
	    DBQueryFatal("select t.idx,t.exptidx,s.rsrcidx ".
			 " from testbed_stats as t ".
540
			 "left join experiment_stats as s ");
541 542 543 544 545 546 547 548 549

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

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

        alter table veth_interfaces add vnode varchar(32) default NULL
		after iface;
562 563 564 565 566

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

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

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

        alter table experiments add expt_swap_uid varchar(8) NOT NULL
		 default '' after expt_swapped;
574 575 576 577 578

       Must also init that field for existing experiments.

        update experiments set expt_swap_uid=expt_head_uid
		where expt_swapped is not null;
579 580 581 582 583 584

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

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

	ALTER TABLE experiments ADD COLUMN noswap_reason tinytext 
	     AFTER priority;

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

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

	ALTER TABLE experiments ADD COLUMN noidleswap_reason tinytext 
	      AFTER idleswap_timeout;

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

	ALTER TABLE experiments ADD COLUMN autoswap_timeout int(4) 
	      NOT NULL default '0' AFTER autoswap;
606 607 608 609 610 611 612 613 614 615 616 617 618

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

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

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

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

	alter table veth_interfaces change iface iface varchar(10)
		 default NULL;
625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644

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

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

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

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

	    DBQueryFatal("update nodes set ".
			 "jailip='172.16.${p}.${v}' where node_id='$node_id'");
	}
645 646 647 648 649

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

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

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

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

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

1.156: Add uid to testbed_stats table:

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

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

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

	while (($idx,$creator) = $query_result->fetchrow_array()) {
	    print "update testbed_stats set uid='$creator' where idx=$idx;\n";
	}
692 693 694 695 696 697 698

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

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

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

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

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

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

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

1.158: Add jail osid to node_types table.

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

732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748
1.159: Remove old slots from experiment_stats table that were moved to
       experiment_resources table in revision 1.143.

        alter table experiment_stats drop vnodes;
	alter table experiment_stats drop pnodes;
	alter table experiment_stats drop wanodes;
	alter table experiment_stats drop simnodes;
	alter table experiment_stats drop jailnodes;
	alter table experiment_stats drop delaynodes;
	alter table experiment_stats drop linkdelays;
	alter table experiment_stats drop walinks;
	alter table experiment_stats drop links;
	alter table experiment_stats drop lans;
	alter table experiment_stats drop shapedlinks;
	alter table experiment_stats drop shapedlans;
	alter table experiment_stats drop minlinks;
	alter table experiment_stats drop maxlinks;
749 750 751 752 753 754 755 756 757 758

1.160: Put the thumbnail into the resources table. 

	alter table experiment_resources add thumbnail mediumblob
			 after maxlinks;

       Then create the thumbs in the DB

	        prerender_all -t

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

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

        drop table vis_experiments;

765
1.162: Add a node_type_features table that lists the assign 'features' for each
766 767 768 769 770
       node type:

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

772
1.163: Add columns to the new_interfaces table indicating where the interface
773 774 775 776 777
       is plugged into a switch:

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

779
1.164: Chage the new_interfaces table to contain card numbers, rather than
780 781 782 783 784 785 786
       iface names:

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

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

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

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

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

       update node_types set max_interfaces=5 where type='pc600';
       update node_types set max_interfaces=5 where type='pc850';
       update node_types set max_interfaces=3 where type='pcL440GX';
803 804


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

        alter table testbed_stats change tstamp \
			end_time datetime default NULL;
	alter table testbed_stats add start_time datetime \
			default NULL after idx;
815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832

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

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

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

       I contemplated putting this into the nodes table instead, but a
       node has no role when its not reserved, so it seemed like the
       wrong place for it.
833 834 835 836 837 838 839

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

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

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

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

848 849 850 851 852
1.171: Add issubnode slot for dealing with IXP type things.

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

853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877
1.172: Add plab stuff:

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

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

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

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

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


       Now we have to set them.

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

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

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

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

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

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

920
1.178: Add notes field to the users table for storing admin only
921 922 923 924
       notes.

       alter table users add notes text after wideareajailroot;

925
1.179: Add a table to contain auxiliary types for nodes.
926 927 928 929

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

931
1.180: Add a table to map auxtypes to node_types table. 
932 933 934 935 936 937 938 939 940 941 942 943 944 945

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

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

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

947
1.181: Add a node_features table, just like the node_type_features
948 949 950 951 952 953
       table, but for individual nodes.

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

954

955
1.182: Add plabnodes count to the experiment resource table so we can
956 957 958 959
       track exactly how many slices an experiment is using.

       alter table experiment_resources add
          plabnodes smallint(5) unsigned default '0' after wanodes;
960 961 962 963 964

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

       alter table testbed_stats add index(rsrcidx);
965 966 967 968

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

Robert Ricci's avatar
Robert Ricci committed
969
       alter table os_info add key (path(255));
970 971 972 973 974 975

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

       alter table widearea_nodeinfo add column hostname varchar(255);
       alter table widearea_nodeinfo add column site varchar(255);
976 977 978 979 980 981 982

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

       alter table experiments add keyhash varchar(64) default NULL \
		after mem_usage;
983 984 985 986 987 988 989 990

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

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

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

		sql/newstates.pl | mysql tbdb

998 999 1000 1001 1002 1003 1004
1.188: Add a virt_node_desires table to finally be able to pass node
       desires into assign.

       create table virt_node_desires (pid varchar(12) not null, \
	   eid varchar(32) not null, vname varchar(32) not null, \
	   desire varchar(30) not null, weight float, \
	   primary key(pid,eid,vname));
1005 1006 1007 1008 1009 1010

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

       alter table virt_node_desires drop primary key;
       alter table virt_node_desires add primary key(pid,eid,vname,desire);
1011 1012 1013 1014

1.190: Add an index...

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

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

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

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

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

1027
1.193: Add a range of VLANs to be used, per switch stack. NULL values will
1028 1029 1030 1031
       default to the old VLAN range.

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

1033
1.194: Add a label to the state and mode transitions table. After
1034 1035 1036 1037
       running the two statements below, reload a current version of db-fill.

       alter table state_transitions add label varchar(255) not null;
       alter table mode_transitions add label varchar(255) not null;
1038 1039 1040 1041 1042 1043 1044 1045

1.195: Add secret key for the event system.

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

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

1046
       		sudo ./initkey.pl
1047

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

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

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

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

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

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

1088 1089 1090 1091 1092
1.198: Add an issimnode column in node_types table so that checks for
       sim node type in assign_wrapper is consistent with other similar
       checks

       alter table node_types add issimnode tinyint(4) not null default 0;
1093 1094

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

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

1098
1.200: Add table to hold per table/slot contraint data (min/max len
1099 1100 1101 1102 1103
       and a perl regex defining valid data for the slot).

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

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

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

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

       alter table nodetypeXpid_permissions add key (pid);
1123

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

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

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

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

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

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

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

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

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

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

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

1174 1175 1176 1177 1178 1179
1.210: Add vnode_id column in the interfaces table to indicate which
       vnode in an allocated experiment that the interface belongs
       to. This is useful when multiple jail vnodes are hosted on a
       physical node. 

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

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

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

       alter table reserved add simhost_violation tinyint(3) \
               unsigned not null default '0';
1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214

1.212: Add resource usage table.

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

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

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

       alter table nseconfigs modify nseconfig mediumtext;
       alter table nsfiles modify nsfile mediumtext;
1223 1224 1225 1226

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

1227 1228
       drop table virt_simnode_attributes;

1229 1230 1231 1232 1233 1234 1235 1236
       create table virt_simnode_attributes ( \
         pid varchar(12) NOT NULL default '', \
         eid varchar(32) NOT NULL default '', \
         vname varchar(32) NOT NULL default '', \
         nodeweight smallint(5) unsigned not null default '1', \
         eventrate int(11) unsigned not null default '0', \
         PRIMARY KEY (pid,eid,vname));

1237 1238 1239 1240 1241 1242 1243
1.216: Add mfs flag to os_info table, and temp_boot_osid to nodes
       table.

	alter table nodes add temp_boot_osid varchar(35) NOT NULL \
		default '' after def_boot_cmd_line;
	alter table os_info add mfs tinyint(4) NOT NULL default '0' \
		after max_concurrent;
1244 1245 1246 1247 1248

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

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

       alter table experiments add column sim_reswap_count \
             smallint(5) unsigned NOT NULL default '0';
1255 1256 1257 1258 1259 1260

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

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

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

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

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

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

1273
        alter table experiments change allowfixnode allowfixnode tinyint(4) \
1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284
	       not null default 1;

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

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

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

1286
1.223: Add jail_osname and delay_osname as temporary (well, perhaps)
1287 1288 1289 1290 1291 1292 1293 1294 1295
       aids for paper effort. Note, not an osid, but an osname like we
       specify for a virt_node. Lookup rules are the same; first check
       the current project for a match, and then check emulab-ops.

       alter table experiments add jail_osname varchar(20) \
		default NULL after allowfixnode;
       alter table experiments add delay_osname varchar(20) \
		default NULL after jail_osname;
		
1296
1.224: Add rtabid to nodes table, athough it is meaninful only on a
1297 1298 1299 1300
       jailed vnode. Set in assign_wrapper.

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

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

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

1308
1.226: Add a use_ipassign column to the experiments table, to enable and
1309 1310 1311 1312 1313 1314 1315
       disable the use of Jon's ipassign program. Also add a column to
       specify arguments for it.

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

1317
1.227: Add cable and len columns to the new_interfaces table, so that when
1318 1319 1320 1321
       we're adding a small number of nodes by hand, we can set these:

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

1323
1.228: Increase size of eventlist arguments (and atstring) from
1324 1325 1326 1327 1328 1329 1330
       tinytext (255 chars) to text (65K chars) to avoid too-short problems
       with program agent command lines. Note that the maximum size allowed
       by table_regex checking will be set to 1024 for now.

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

1331 1332 1333 1334 1335 1336 1337 1338 1339 1340
1.229: Change the definition of the plab_* tables to store module-specific
       data in opaque columns

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

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

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

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

1.231 add leaseend column to plab_slices table

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

1.232 add isadmin column to plab_slices table

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

Leigh Stoller's avatar
Leigh Stoller committed
1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417
1.233: Add location_info table for generating wireless location maps.

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

1418 1419 1420 1421
1.234: Add a column to node_types identifying plab physical nodes.

       alter table node_types add column isplabphysnode tinyint(4) not null \
	   default 0 after isplabdslice;
1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441

1.235: Initial wireless support. 

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

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

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

1442 1443 1444 1445 1446
1.236: Add linktest_level to experiments table. Integer value 0-4
       where 0 means not to run linktest.

       alter table experiments add column linktest_level tinyint(4) \
	   not null default 0 after ipassign_args;
1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459

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

	CREATE TABLE interface_settings (
	  node_id varchar(32) NOT NULL default '',
	  iface varchar(32) NOT NULL default '',
	  capkey varchar(32) NOT NULL default '',
	  capval varchar(64) NOT NULL default '',
	  PRIMARY KEY  (node_id,iface,capkey),
          KEY node_id (node_id)
	) TYPE=MyISAM;
1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483

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

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

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

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

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

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

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

	insert into node_hostkeys (node_id, sfshostid) \
		select node_id,sfshostid from nodes;
	
1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520
	Be sure to restart tmcd after you make these changes.

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

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

       Make equiv change in virt_agents table:

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

1521 1522 1523 1524 1525 1526 1527 1528
1.242: Add est_bandwidth and rest_bandwidth to virt_lans, to hold the estimated
       bandwidth that will be used on the link/LAN. This can then be passed to
       assign, to do less conservative bandwidth allocation.

       alter table virt_lans add est_bandwidth int(10) unsigned \
	   default NULL after bandwidth;
       alter table virt_lans add rest_bandwidth int(10) unsigned \
	   default NULL after rbandwidth;
1529 1530 1531 1532 1533 1534 1535

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

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

1536 1537 1538 1539
1.244: Add trivlink_maxspeed for Mike.

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

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

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

1547 1548 1549 1550 1551 1552 1553 1554
1.246: Change routertype slots to include new type of route stuff.

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

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

1557 1558 1559 1560 1561
1.247:  Add a 'useprepass' column to the experiments table to enable or
        disable the assign prepass.

	 alter table experiments add column useprepass tinyint(1) \
		 default 0 not null;
1562 1563 1564 1565 1566

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

         alter table new_nodes add column identifier varchar(255);
1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606

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

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

		vname:  link1
		vport:  1
		ip:     1.1.2.2

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

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

		./ipmember.pl

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

	Whats vport about? Rather then rely on IP addresses to form a
	unique key, we use vport (a small integer) so that we can delay the
	IP assignment until later (after initial DB insertion).
1607 1608 1609 1610 1611

1.250: Add another index to virt_lans for above change.

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

1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649
1.251: Add floorimages and buildings tables for wireless floormap
       support. This is rather primitive still; We just need a place to
       define buildings and floors in buildings, so that we do not hardwire
       them into the code. This can get arbitrarily complicated, but not
       until we need it.

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

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

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

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

        ALTER TABLE new_nodes ADD COLUMN (floor varchar(32) default NULL,
	    building varchar(32) default NULL,
	    loc_x int(10) unsigned NOT NULL default '0',
	    loc_y int(10) unsigned NOT NULL default '0',
	    contact tinytext,
	    phone tinytext,
	    room varchar(32) default NULL);
1661 1662 1663 1664 1665 1666 1667

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

        alter table eventlist add isgroup tinyint(1) unsigned \
1810
              default '0' after eventtype;
1811 1812 1813 1814 1815 1816

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

1817
        alter table virt_lans add mustdelay tinyint(1) \
1818 1819 1820 1821 1822 1823
             default '0' after nobwshaping;
	update virt_lans set mustdelay=q_red;

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

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

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

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

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

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

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

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

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

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

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

       and the associated table_regex's for parsing:

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

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

       Create the IPFW firewall OSID with:

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

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

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

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

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

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

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

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

1.274: Add usrp_orders table.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2069 2070 2071 2072 2073 2074 2075 2076 2077 2078 2079 2080 2081 2082 2083 2084 2085 2086
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;

2087
       update table_regex set `check`='^(ipfw|ipfw2|ipchains|ipfw2-vlan)$' \
2088 2089 2090 2091 2092 2093 2094 2095 2096 2097 2098
           where table_name='firewalls' and column_name='type';

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

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

1.279: Still more VLAN-enforced firewall changes.

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

       alter table firewalls add index(vlan);
2105

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

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

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

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

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

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

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

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

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

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

	select node_id from nodes where cnet_vlan is not null;

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

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

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

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

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

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

	alter table experiment_stats add elabinelab tinyint(1) NOT NULL \
            default '0' after batch;
2164
	alter table experiment_stats add elabinelab_exptidx int(10) \
2165 2166 2167
             unsigned default NULL after elabinelab;
	alter table experiment_stats add security_level tinyint(1) NOT NULL \
            default '0' after elabinelab_exptidx;
2168 2169 2170 2171

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

2173
1.288: Add a 'rebootable' bit to the node_types table to indicate that a 
2174 2175 2176 2177 2178 2179
       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');
	

2180
1.289: Add "emulab" style of firewall for elabinelab
2181 2182 2183 2184 2185

       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';
2186
       UPDATE table_regex SET `check`='^(open|closed|basic|emulab)$' WHERE \
2187
	 table_name='firewalls' AND column_name='style';
2188 2189 2190 2191

       and update the default rules:

       mysql tbdb < fwrules-create.sql
2192

2193 2194 2195 2196 2197 2198
       If fwrules-create.sql does not exist (it disappeared circa 1.300),
       then instead do:

	cd <your_build_tree>/firewall
	gmake insertrules

2199
1.290: Add conversion factor floorimages that converts from pixels to
2200 2201 2202 2203
      meters.

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

2205
1.291: Add Windows to the OS list in the os_info table.
2206 2207 2208 2209

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

2211
1.292: Add a settable Windows password.  We derive a default password
2212 2213 2214 2215
       from the Unix usr_pswd MD5 hash string.

       alter table users add column usr_w_pswd tinytext after usr_pswd;

2216
1.293: Add "paniced" bit to experiments table.
2217 2218 2219 2220 2221

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

2223
1.294: Add a "leader" column to the switch_stack_types table so that we no
2224 2225 2226 2227 2228 2229 2230 2231 2232 2233 2234 2235 2236 2237
       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 \
2238
         s on n.node_id=s.node_id where n.role='testswitch';
2239 2240 2241 2242

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

2243
       update switch_stacks set stack_id='Experiment' where \
2244
         stack_id='<stackid>';
2245
       update switch_stack_types set stack_id='Experiment' where \
2246 2247 2248
         stack_id='<stackid>';

       Then, do the same with the control net:
2249 2250 2251 2252 2253 2254 2255

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

2257
1.295: Add some battery stuff for robots to the nodes table.
2258 2259 2260 2261 2262

        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;

2263
1.296: Add stuff for capturing boot errors.
2264 2265 2266 2267 2268 2269 2270 2271 2272 2273

        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;

2274
1.297: Fix virt_vtypes table.
2275

2276 2277 2278
         alter table virt_vtypes change eid eid varchar(32) NOT NULL \
                default '';

2279
1.298: Add webcams support.
2280 2281 2282 2283 2284 2285 2286 2287

         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;

2288
1.299: Add obstacles table for the robots.
2289 2290 2291 2292 2293 2294 2295 2296 2297 2298 2299 2300 2301 2302

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

2304
1.300: Add default_firewall_vars table.  These are global (not
2305 2306 2307 2308 2309 2310 2311 2312 2313 2314 2315 2316 2317 2318 2319 2320 2321 2322 2323 2324
       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
2325

2326
1.301: Allow a switch to be in multiple stacks, and add a column that records
2327 2328 2329 2330 2331
       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');
2332

2333
1.302: Add node history table at Mike's request to track what
2334 2335 2336 2337 2338 2339 2340 2341 2342 2343 2344 2345 2346
       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;

2347
1.303: Add a "lockdown" bit to the experiments table to prevent
2348 2349 2350 2351
       accidental swaps, even by admin people.

         alter table experiments add lockdown tinyint(1) NOT NULL \
              default '0' after security_level;
2352 2353 2354 2355 2356 2357 2358 2359 2360 2361 2362 2363 2364 2365

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;
2366 2367 2368 2369 2370 2371 2372 2373 2374

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; 
2375 2376 2377 2378 2379

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 '';
2380 2381 2382 2383 2384 2385 2386 2387 2388 2389 2390 2391 2392 2393 2394 2395 2396 2397 2398 2399 2400 2401 2402 2403 2404 2405 2406 2407

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;

2408 2409 2410 2411 2412 2413 2414 2415 2416
       Populating the tables from the existing nodetypeXpid_permissions.

       insert into group_policies (pid, gid, policy, auxdata, count) \
          select distinct '-','-','type',type,0 from nodetypeXpid_permissions;

       insert into group_policies (pid, gid, policy, auxdata, count) \
          select distinct pid,pid,'type',type,9999999 from \
	       nodetypeXpid_permissions;

2417 2418 2419 2420 2421 2422 2423 2424 2425 2426 2427 2428 2429 2430 2431 2432
1.308: Add a table for the robot tracking cameras.

	CREATE TABLE cameras (
	  name varchar(32) NOT NULL default '',
	  building varchar(32) NOT NULL default '',
	  floor varchar(32) NOT NULL default '',
	  hostname varchar(255) default NULL,
	  port smallint(5) unsigned NOT NULL default '6100',
	  device varchar(64) NOT NULL default '',
	  loc_x float NOT NULL default '0',
	  loc_y float NOT NULL default '0',
	  width float NOT NULL default '0',
	  height float NOT NULL default '0',
	  config tinytext,
	  PRIMARY KEY  (name,building,floor)
	) TYPE=MyISAM;
2433 2434 2435

1.309: Add destination coords to the nodes table for robots.

2436 2437
	alter table nodes add destination_x float default NULL;
	alter table nodes add destination_y float default NULL;
2438 2439 2440

       Unlike other coords, these are meters and translated to pixels
       later when displayed (trying to head in the right direction ...)
2441 2442 2443 2444 2445 2446 2447 2448

1.310: Addendum to previous revision. Add orientation and change
       default values to NULL.

	alter table location_info add orientation float \
			default NULL after loc_y;
	alter table nodes add destination_orientation float \
			default NULL;
2449 2450

1.311: Fix mistake in last revision. Skip to next entry.