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

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:

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

1.114: Add nextosid slot to os_info table for mapping the generic
       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.

1.115   Add node_activity table for new slothd stuff: (Mac)

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;

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

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.

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

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

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

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.

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

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

1.124: Change address fields in widearea_nodeinfo to tinytext:

       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;

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

       ALTER TABLE widearea_nodeinfo ADD COLUMN external_node_id tinytext;

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;

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

1.128: Add usr_country column to users table.

       ALTER TABLE users ADD COLUMN usr_country tinytext AFTER usr_zip;

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

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

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

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;

1.132: This commit was superseded by 1.133

1.133: Changed the new_nodes and new_interfaces table to use a new unique
       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);

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;

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
	is over 1MB. 

1.137: Minor file formatting changes, no DB schema changes

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

       drop table lastlogin;

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;

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

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;

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;

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

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 ".
			 "left join experiment_stats as s ");

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

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;

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

        alter table veth_interfaces change vnode vnode_id varchar(32)
		default NULL;

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;

       Must also init that field for existing experiments.

        update experiments set expt_swap_uid=expt_head_uid
		where expt_swapped is not null;

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

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;

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

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'");
	}

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;

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.

1.155: Bring the definition for the roottag column into sync with what's in
       the database on boss - no database changes required.

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

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

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

1.158: Add jail osid to node_types table.

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

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;

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

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

       Also remove the vis_experiments table, since no longer needed.

        drop table vis_experiments;

1.162: Add a node_type_features table that lists the assign 'features' for each
       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));

1.163: Add columns to the new_interfaces table indicating where the interface
       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);

1.164: Chage the new_interfaces table to contain card numbers, rather than
       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);

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

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

1.166: Kill max_ports and max_cards, replace with max_interfaces.

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


1.167: As per Jay's request, start recording the start time as well as
       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;

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.

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;

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;

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;

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;

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

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

1.178: Add notes field to the users table for storing admin only
       notes.

       alter table users add notes text after wideareajailroot;

1.179: Add a table to contain auxiliary types for nodes.

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

1.180: Add a table to map auxtypes to node_types table. 

	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;

1.181: Add a node_features table, just like the node_type_features
       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));


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

       alter table experiment_resources add
          plabnodes smallint(5) unsigned default '0' after wanodes;

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

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

       alter table os_info add key (path(255));

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

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;

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;

       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

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

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

1.190: Add an index...

       alter table testbed_stats add key (exptidx);

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

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

1.192: Add sfshostid to nodes table. We store that in the filesystem
       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;

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

1.194: Add a label to the state and mode transitions table. After
       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;

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:

       		sudo ./initkey.pl

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

	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;

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;

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;

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

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

1.200: Add table to hold per table/slot contraint data (min/max len
       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 '',
	  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',
	  comment tinytext,
	  UNIQUE KEY table_name (table_name,column_name)
	) TYPE=MyISAM;

1.202: Change the enum for pcremote_ok to match the phys type that was
       ultimately chosen by Austin.

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

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

       alter table nodetypeXpid_permissions add key (pid);

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;

1.205: Add enum to projects table to store which interface users
       should start with.

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

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

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

1.207: Add fields to support user directed Forgot My Password changes.

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

1.208: Add project table linked_to_us entry.

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

1.209: Went back to revision 202 and fixed it up; nothing for you to
       do, just skip ahead to the next revision.

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;

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

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;

1.213: Minor whitespace change to make schemacheck happy.

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;

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

       drop table virt_simnode_attributes;

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

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;

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

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

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;

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.

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

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

1.221: Fixed allowfixnode to default to 1 instead of 0

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

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

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

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

1.223: Add jail_osname and delay_osname as temporary (well, perhaps)
       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;
		
1.224: Add rtabid to nodes table, athough it is meaninful only on a
       jailed vnode. Set in assign_wrapper.

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

1.225: Add 'veths' and 'mlinks' to the list of possible osfeatures in
       is_info table.

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

1.226: Add a use_ipassign column to the experiments table, to enable and
       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;

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

1.228: Increase size of eventlist arguments (and atstring) from
       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;

1.229: Change the definition of the plab_* tables to store module-specific
       data in opaque columns

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

       alter table plab_slice_nodes drop column ticketdata, \
           drop column leasedata, \
           add column nodemeta text default NULL;

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

1.231 add leaseend column to plab_slices table

       alter table plab_slices add column leaseend datetime default NULL;

1.232 add isadmin column to plab_slices table

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

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;

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;

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;

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;

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;

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;

1.239: Add "wirelesslans" slot to experiment_resources table.

       alter table experiment_resources add
          wirelesslans smallint(5) unsigned default '0' after shapedlans;

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

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;

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;

1.244: Add trivlink_maxspeed for Mike.

      alter table node_types add trivlink_maxspeed int(11) unsigned \
	   NOT NULL default '0' after simnode_capacity;

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

1.246: Change routertype slots to include new type of route stuff.

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

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

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;

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

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

1.250: Add another index to virt_lans for above change.

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

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.

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

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;

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;

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;

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

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

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

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;

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;

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;

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

1.263: I fixed typo in previous revision; nothing to do, skip to next
       entry.

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

1.265: Removed a field I never actually added; skip to next entry.

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

1.267: Remove table definition that snuck in while developing; skip to
       next entry;

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 \
             NOT NULL default '0' after eventtype;

      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.

        alter table virt_lans add mustdelay tinyint(1) unsigned \
             default '0' after nobwshaping;
	update virt_lans set mustdelay=q_red;

      Then run:
	
		./mustdelay.pl