protogeni.sql 8.71 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,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
81
  `type` enum('sa','cm','ma','ch') NOT NULL default 'sa',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
82
  `url` tinytext,
83
  PRIMARY KEY  (`idx`),
84
85
  UNIQUE KEY `uuid` (`uuid`),
  UNIQUE KEY `uuid_prefix` (`uuid_prefix`)
Leigh B. Stoller's avatar
Leigh B. Stoller committed
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
) 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 '',
104
  `exptidx` int(11) default NULL,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
105
106
107
  `created` datetime default NULL,
  `creator_uuid` varchar(40) NOT NULL default '',
  `name` tinytext,
108
  `sa_uuid` varchar(40) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
109
110
111
112
113
114
  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
115
116
# 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
117
118
119
120
121
#
DROP TABLE IF EXISTS `geni_slivers`;
CREATE TABLE `geni_slivers` (
  `idx` mediumint(8) unsigned NOT NULL default '0',
  `uuid` varchar(40) NOT NULL default '',
122
  `hrn` varchar(256) NOT NULL default '',
123
  `nickname` varchar(256) default NULL,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
124
125
  `slice_uuid` varchar(40) NOT NULL default '',
  `creator_uuid` varchar(40) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
126
  `resource_uuid` varchar(40) NOT NULL default '',
127
  `resource_type` varchar(40) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
128
129
  `created` datetime default NULL,
  `credential_idx` int(10) unsigned default NULL,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
130
131
  `component_uuid` varchar(40) default NULL,
  `aggregate_uuid` varchar(40) default NULL,
132
  `status` enum('created','ready','broken') NOT NULL default 'created',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
133
  `rspec_string` text,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
134
135
136
137
138
139
140
141
142
143
144
  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',
145
  `hrn` varchar(256) NOT NULL default '',
146
  `nickname` varchar(256) default NULL,
Leigh B. Stoller's avatar
Leigh B. Stoller committed
147
  `uuid` varchar(40) NOT NULL default '',
148
  `type` varchar(40) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
149
150
  `slice_uuid` varchar(40) NOT NULL default '',
  `creator_uuid` varchar(40) NOT NULL default '',
Leigh B. Stoller's avatar
Leigh B. Stoller committed
151
  `created` datetime default NULL,
152
153
  `credential_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
  INDEX `slice_uuid` (`slice_uuid`),
179
  UNIQUE KEY `compseqno` (`component_uuid`, `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
  `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,
209
  `DN` text,
210
211
212
213
214
  `privkey` text,
  `revoked` datetime default NULL,
  PRIMARY KEY  (`uuid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Leigh B. Stoller's avatar
Leigh B. Stoller committed
215
#
216
# A clearinghouse table to hold keys associated with geni users.
Leigh B. Stoller's avatar
Leigh B. Stoller committed
217
#
218
219
220
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
221
222
  `uuid` varchar(40) NOT NULL default '',
  `created` datetime default NULL,
223
224
  `key` text,
  INDEX `uuid` (`uuid`)
Leigh B. Stoller's avatar
Leigh B. Stoller committed
225
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
226
227
228
229
230
231
232
233
234

#
# 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
235
236
  `component_uuid` varchar(40) NOT NULL default '',
  PRIMARY KEY  (`resource_uuid`)
237
238
239
240
241
242
243
244
245
246
247
248
249
) 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;