protogeni.sql 9.56 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
#
# 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,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
27
28
  `expires` datetime default NULL,
  `locked` datetime default NULL,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
29
30
31
32
  `archived` datetime default NULL,
  `status` enum('active','archived','frozen') NOT NULL default 'frozen',
  `name` tinytext,
  `email` tinytext,
33
  `sa_uuid` varchar(40) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
  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` (
54
  `hrn` varchar(256) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
55
  `uuid` varchar(40) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
56
  `manager_uuid` varchar(40) default NULL,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
57
58
  `created` datetime default NULL,
  `url` tinytext,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
59
60
  PRIMARY KEY  (`uuid`),
  UNIQUE KEY `hrn` (`hrn`)
Leigh B. Stoller's avatar
Leigh B. Stoller committed
61
62
63
64
65
66
67
68
69
70
71
72
73
74
) 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.
#
75
76
DROP TABLE IF EXISTS `geni_authorities`;
CREATE TABLE `geni_authorities` (
77
  `hrn` varchar(256) NOT NULL default '',
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,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
81
  `type` enum('sa','ma','ch','cm') NOT NULL default 'sa',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
82
  `url` tinytext,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
83
84
  PRIMARY KEY  (`uuid`),
  UNIQUE KEY `hrn` (`hrn`)
Leigh B. Stoller's avatar
Leigh B. Stoller committed
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
) 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
  `created` datetime default NULL,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
105
  `expires` datetime default NULL,
106
  `shutdown` datetime default NULL,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
107
  `locked` datetime default NULL,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
108
109
  `creator_uuid` varchar(40) NOT NULL default '',
  `name` tinytext,
110
  `sa_uuid` varchar(40) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
111
112
113
114
115
116
  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
117
118
# 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
119
120
121
122
123
#
DROP TABLE IF EXISTS `geni_slivers`;
CREATE TABLE `geni_slivers` (
  `idx` mediumint(8) unsigned NOT NULL default '0',
  `uuid` varchar(40) NOT NULL default '',
124
  `hrn` varchar(256) NOT NULL default '',
125
  `nickname` varchar(256) default NULL,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
126
127
  `slice_uuid` varchar(40) NOT NULL default '',
  `creator_uuid` varchar(40) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
128
  `resource_uuid` varchar(40) NOT NULL default '',
129
  `resource_type` varchar(40) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
130
  `created` datetime default NULL,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
131
132
  `expires` datetime default NULL,
  `locked` datetime default NULL,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
133
  `credential_idx` int(10) unsigned default NULL,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
134
135
  `component_uuid` varchar(40) default NULL,
  `aggregate_uuid` varchar(40) default NULL,
136
  `status` enum('created','ready','broken') NOT NULL default 'created',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
137
  `rspec_string` text,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
138
139
140
141
142
143
144
145
146
147
148
  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',
149
  `hrn` varchar(256) NOT NULL default '',
150
  `nickname` varchar(256) default NULL,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
151
  `uuid` varchar(40) NOT NULL default '',
152
  `type` varchar(40) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
153
154
  `slice_uuid` varchar(40) NOT NULL default '',
  `creator_uuid` varchar(40) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
155
  `created` datetime default NULL,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
156
157
  `expires` datetime default NULL,
  `locked` datetime default NULL,
158
159
  `credential_idx` int(10) unsigned default NULL,
  `component_idx` int(10) unsigned NOT NULL default '0',
160
  `aggregate_idx` int(10) unsigned default NULL,
161
  `status` enum('created','ready','broken') NOT NULL default 'created',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
162
163
164
165
166
  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
167
#
168
# Table to remember tickets.
Leigh B. Stoller's avatar
Leigh B. Stoller committed
169
170
171
172
#
DROP TABLE IF EXISTS `geni_tickets`;
CREATE TABLE `geni_tickets` (
  `idx` mediumint(8) unsigned NOT NULL default '0',
173
  `ticket_uuid` varchar(40) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
174
175
176
177
  `owner_uuid` varchar(40) NOT NULL default '',
  `slice_uuid` varchar(40) NOT NULL default '',
  `created` datetime default NULL,
  `redeem_before` datetime default NULL,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
178
  `locked` datetime default NULL,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
179
  `valid_until` datetime default NULL,
180
  `component_uuid` varchar(40) NOT NULL default '',
181
  `seqno` int(10) unsigned NOT NULL default '0',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
182
  `ticket_string` text,
183
  PRIMARY KEY  (`idx`), 
Leigh B. Stoller's avatar
Leigh B. Stoller committed
184
  INDEX `owner_uuid` (`owner_uuid`),
185
  INDEX `slice_uuid` (`slice_uuid`),
186
  UNIQUE KEY `compseqno` (`component_uuid`, `seqno`)
Leigh B. Stoller's avatar
Leigh B. Stoller committed
187
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
188
189
190
191
192
193
194

#
# Table to remember credentials.
#
DROP TABLE IF EXISTS `geni_credentials`;
CREATE TABLE `geni_credentials` (
  `idx` mediumint(8) unsigned NOT NULL default '0',
195
  `uuid` varchar(40) NOT NULL default '',
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
  `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`;
Leigh B. Stoller's avatar
Leigh B. Stoller committed
212
213
214
215
216
217
218
219
220
221
222
223
CREATE TABLE `geni_certificates` (
  `uuid` varchar(40) NOT NULL default '',
  `created` datetime default NULL,
  `expires` datetime default NULL,
  `revoked` datetime default NULL,
  `serial` int(10) unsigned NOT NULL default '0',
  `cert` text,
  `DN` text,
  `privkey` text,
  PRIMARY KEY  (`uuid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

224
225
226
227
CREATE TABLE `geni_certificates` (
  `uuid` varchar(40) NOT NULL default '',
  `created` datetime default NULL,
  `cert` text,
228
  `DN` text,
229
230
231
232
233
  `privkey` text,
  `revoked` datetime default NULL,
  PRIMARY KEY  (`uuid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Leigh B. Stoller's avatar
Leigh B. Stoller committed
234
235
236
237
238
239
240
241
242
243
244
245
#
# A clearinghouse table to hold CRLs to be distributed.
#
DROP TABLE IF EXISTS `geni_crls`;
CREATE TABLE `geni_crls` (
  `uuid` varchar(40) NOT NULL default '',
  `created` datetime default NULL,
  `expires` datetime default NULL,
  `cert` text,
  PRIMARY KEY  (`uuid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Leigh B. Stoller's avatar
Leigh B. Stoller committed
246
#
247
# A clearinghouse table to hold keys associated with geni users.
Leigh B. Stoller's avatar
Leigh B. Stoller committed
248
#
249
250
251
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
252
253
  `uuid` varchar(40) NOT NULL default '',
  `created` datetime default NULL,
254
255
  `key` text,
  INDEX `uuid` (`uuid`)
Leigh B. Stoller's avatar
Leigh B. Stoller committed
256
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
257
258
259
260
261
262
263
264
265

#
# 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
266
267
  `component_uuid` varchar(40) NOT NULL default '',
  PRIMARY KEY  (`resource_uuid`)
268
269
270
271
272
273
274
275
276
277
278
279
280
) 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;