protogeni.sql 8.7 KB
Newer Older
Leigh B. Stoller's avatar
Leigh B. Stoller committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
#
# These are new tables to add to the Emulab DB. 
#

#
# Remote SAs register users at Emulab (we export GENI ClearingHouse
# APIs, whatever they are). Local users do not need to be in this table,
# we can get their data via the Emulab libraries. 
#
#  * This is a GENI ClearingHouse table; remote emulabs have just a
#    local users table. 
#  * Users in this table are users at other emulabs.
#  * uid_idx is for indexing into other tables, like user_sslcerts and
#    user_pubkeys. No need to have geni versions of those tables, as
#    long as we maintain uid_idx uniqueness. 
#  * sa_idx is an index into another table. SAs in in the prototype are
#    other Emulabs.
#  * The uid does not have to be unique, except for a given SA. 
#
DROP TABLE IF EXISTS `geni_users`;
CREATE TABLE `geni_users` (
  `hrn` varchar(256) NOT NULL default '',
  `uid` varchar(8) NOT NULL default '',
  `idx` mediumint(8) unsigned NOT NULL default '0',
  `uuid` varchar(40) NOT NULL default '',
  `created` datetime default NULL,
  `archived` datetime default NULL,
  `status` enum('active','archived','frozen') NOT NULL default 'frozen',
  `name` tinytext,
  `email` tinytext,
31
  `sa_uuid` varchar(40) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52
  PRIMARY KEY  (`idx`),
  KEY `hrn` (`hrn`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

#
# All geni components have a GID (UUID->Key) and this
# table stores them all. In protogeni there are not that many components,
# and they mostly refer to other Emulabs.
#
#  * This is a GENI Clearinghouse table; Components are remote emulabs.
#  * The ID is a GENI dotted name.
#  * We store the pubkey for the component here. Remember that
#    components self generate their keys.
#  * Not worrying about Management Authorities at this point. I think
#    the prototype has a single MA, and its operated by hand with
#    direct mysql statements.
#
DROP TABLE IF EXISTS `geni_components`;
CREATE TABLE `geni_components` (
  `idx` mediumint(8) unsigned NOT NULL default '0',
53
  `hrn` varchar(256) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73
  `uuid` varchar(40) NOT NULL default '',
  `created` datetime default NULL,
  `url` tinytext,
  PRIMARY KEY  (`idx`),
  UNIQUE KEY `hrn` (`hrn`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

#
# Geni Slice Authorities also have a GID. As with components, SAs are
# mostly other emulabs. GENI users are registered by SAs, as are slices.
#
#  * This is a GENI Clearinghouse table; SAs are remote emulabs.
#  * The ID is a GENI dotted name.
#  * We store the pubkey for the SA here. Remember that SAs self
#    generate their keys.
#  * The uuid_prefix is the topmost 8 bytes assigned to the SA; all
#    UUIDs generated (signed) by and registered must have these top
#    8 bytes. See wiki discussion.
#
74 75
DROP TABLE IF EXISTS `geni_authorities`;
CREATE TABLE `geni_authorities` (
76 77
  `hrn` varchar(256) NOT NULL default '',
  `idx` mediumint(8) unsigned NOT NULL default '0',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
78
  `uuid` varchar(40) NOT NULL default '',
79
  `uuid_prefix` varchar(12) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
80
  `created` datetime default NULL,
81
  `type` enum('sa','ma','ch') NOT NULL default 'sa',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
82
  `url` tinytext,
83
  PRIMARY KEY  (`idx`),
Leigh B. Stoller's avatar
Leigh B. Stoller committed
84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

#
# Geni Slices. Not to be confused with plab_slices ... a geni slice
# is an emulab experiment that spans the entire set of emulabs. SAs
# register geni slices at the Clearinghouse, in this table.
# 
# * Slices have UUIDs.
# * The sa_idx refers to the slice authority that created the slice.
# * The creator UUID should already be in the geni_users table, or in
#   the local users table.
# * The pubkey bound to the slice is that of the user creating the slice.
# 
DROP TABLE IF EXISTS `geni_slices`;
CREATE TABLE `geni_slices` (
  `hrn` varchar(256) NOT NULL default '',
  `idx` mediumint(8) unsigned NOT NULL default '0',
  `uuid` varchar(40) NOT NULL default '',
103
  `exptidx` int(11) default NULL,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
104 105 106
  `created` datetime default NULL,
  `creator_uuid` varchar(40) NOT NULL default '',
  `name` tinytext,
107
  `sa_uuid` varchar(40) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
108 109 110 111 112 113
  PRIMARY KEY  (`idx`),
  UNIQUE KEY `hrn` (`hrn`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

#
Leigh B. Stoller's avatar
Leigh B. Stoller committed
114 115
# Geni Slivers. Created on components (by CMs of course). Locally, a sliver
# corresponds to an allocated node. 
Leigh B. Stoller's avatar
Leigh B. Stoller committed
116 117 118 119 120
#
DROP TABLE IF EXISTS `geni_slivers`;
CREATE TABLE `geni_slivers` (
  `idx` mediumint(8) unsigned NOT NULL default '0',
  `uuid` varchar(40) NOT NULL default '',
121 122
  `hrn` varchar(256) NOT NULL default '',
  `name` varchar(256) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
123 124
  `slice_uuid` varchar(40) NOT NULL default '',
  `creator_uuid` varchar(40) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
125
  `resource_uuid` varchar(40) NOT NULL default '',
126
  `resource_type` varchar(40) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
127 128
  `created` datetime default NULL,
  `credential_idx` int(10) unsigned default NULL,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
129 130
  `component_uuid` varchar(40) default NULL,
  `aggregate_uuid` varchar(40) default NULL,
131
  `status` enum('created','ready','broken') NOT NULL default 'created',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
132
  `rspec_string` text,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
133 134 135 136 137 138 139 140 141 142 143
  PRIMARY KEY  (`idx`),
  UNIQUE KEY `uuid` (`uuid`),
  INDEX `slice_uuid` (`slice_uuid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

#
# Geni Aggregates, which are a collection of resources (nodes, links, etc).
#
DROP TABLE IF EXISTS `geni_aggregates`;
CREATE TABLE `geni_aggregates` (
  `idx` mediumint(8) unsigned NOT NULL default '0',
144 145
  `hrn` varchar(256) NOT NULL default '',
  `name` varchar(256) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
146
  `uuid` varchar(40) NOT NULL default '',
147
  `type` varchar(40) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
148 149
  `slice_uuid` varchar(40) NOT NULL default '',
  `creator_uuid` varchar(40) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
150
  `created` datetime default NULL,
151 152 153
  `credential_idx` int(10) unsigned default NULL,
  `ticket_idx` int(10) unsigned default NULL,
  `component_idx` int(10) unsigned NOT NULL default '0',
154
  `aggregate_idx` int(10) unsigned default NULL,
155
  `status` enum('created','ready','broken') NOT NULL default 'created',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
156 157 158 159 160
  PRIMARY KEY  (`idx`),
  UNIQUE KEY `uuid` (`uuid`),
  INDEX `slice_uuid` (`slice_uuid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Leigh B. Stoller's avatar
Leigh B. Stoller committed
161
#
162
# Table to remember tickets.
Leigh B. Stoller's avatar
Leigh B. Stoller committed
163 164 165 166
#
DROP TABLE IF EXISTS `geni_tickets`;
CREATE TABLE `geni_tickets` (
  `idx` mediumint(8) unsigned NOT NULL default '0',
167
  `ticket_uuid` varchar(40) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
168 169 170 171 172
  `owner_uuid` varchar(40) NOT NULL default '',
  `slice_uuid` varchar(40) NOT NULL default '',
  `created` datetime default NULL,
  `redeem_before` datetime default NULL,
  `valid_until` datetime default NULL,
173
  `component_uuid` varchar(40) NOT NULL default '',
174
  `seqno` int(10) unsigned NOT NULL default '0',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
175
  `ticket_string` text,
176
  PRIMARY KEY  (`idx`), 
Leigh B. Stoller's avatar
Leigh B. Stoller committed
177
  INDEX `owner_uuid` (`owner_uuid`),
178 179
  INDEX `slice_uuid` (`slice_uuid`),
  UNIQUE KEY `compseqno` (`component_idx`, `seqno`)
Leigh B. Stoller's avatar
Leigh B. Stoller committed
180
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
181 182 183 184 185 186 187

#
# Table to remember credentials.
#
DROP TABLE IF EXISTS `geni_credentials`;
CREATE TABLE `geni_credentials` (
  `idx` mediumint(8) unsigned NOT NULL default '0',
188
  `uuid` varchar(40) NOT NULL default '',
189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213
  `owner_uuid` varchar(40) NOT NULL default '',
  `this_uuid` varchar(40) NOT NULL default '',
  `created` datetime default NULL,
  `valid_until` datetime default NULL,
  `credential_string` text,
  PRIMARY KEY  (`idx`),
  INDEX `owner_uuid` (`owner_uuid`),
  INDEX `this_uuid` (`this_uuid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

#
# Table to hold uuid<->certificate bindings, keeping them out of the other
# tables above. We use this on both the client and server side (storing the
# private key), say for a sliver.
#
DROP TABLE IF EXISTS `geni_certificates`;
CREATE TABLE `geni_certificates` (
  `uuid` varchar(40) NOT NULL default '',
  `created` datetime default NULL,
  `cert` text,
  `privkey` text,
  `revoked` datetime default NULL,
  PRIMARY KEY  (`uuid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Leigh B. Stoller's avatar
Leigh B. Stoller committed
214
#
215
# A clearinghouse table to hold keys associated with geni users.
Leigh B. Stoller's avatar
Leigh B. Stoller committed
216
#
217 218 219
DROP TABLE IF EXISTS `geni_userkeys`;
CREATE TABLE `geni_userkeys` (
  `type` enum('ssh','password') NOT NULL default 'ssh',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
220 221
  `uuid` varchar(40) NOT NULL default '',
  `created` datetime default NULL,
222 223
  `key` text,
  INDEX `uuid` (`uuid`)
Leigh B. Stoller's avatar
Leigh B. Stoller committed
224
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
225 226 227 228 229 230 231 232 233

#
# This table maps between resources and their component manager. 
#
DROP TABLE IF EXISTS `geni_resources`;
CREATE TABLE `geni_resources` (
  `resource_uuid` varchar(40) NOT NULL default '',
  `resource_type` varchar(40) NOT NULL default '',
  `created` datetime default NULL,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
234 235
  `component_uuid` varchar(40) NOT NULL default '',
  PRIMARY KEY  (`resource_uuid`)
236 237 238 239 240 241 242 243 244 245 246 247 248
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

#
# Hold the users that are bound to slices.
#
DROP TABLE IF EXISTS `geni_bindings`;
CREATE TABLE `geni_bindings` (
  `slice_uuid` varchar(40) NOT NULL default '',
  `user_uuid` varchar(40) NOT NULL default '',
  `created` datetime default NULL,
  PRIMARY KEY  (`slice_uuid`,`user_uuid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;