Software/Productionalisation/RealDb: raptor_v1.2.1_mua_schema.sql

File raptor_v1.2.1_mua_schema.sql, 12.6 KB (added by smith@…, 5 years ago)

DB Schema as of v1.2.1 (information model v1.1.4)

Line 
1
2    create table EzproxyAuthE (
3        persistantId int8 not null,
4        eventtime timestamp,
5        serviceHost varchar(700),
6        resourceHost varchar(255),
7        serviceId varchar(255),
8        resourceId varchar(255),
9        eventId int4 not null,
10        eventType varchar(255),
11        resourceIdCategory int4,
12        entityId varchar(255),
13        serviceName varchar(255),
14        organisationName varchar(255),
15        authenticationType varchar(255),
16        principalName varchar(255),
17        school varchar(255),
18        affiliation varchar(255),
19        requesterIp varchar(255),
20        sessionId varchar(255),
21        current_hashCode int4,
22        primary key (persistantId)
23    );
24
25    create table JuspAggE (
26        persistantId int8 not null,
27        eventtime timestamp,
28        serviceHost varchar(700),
29        resourceHost varchar(255),
30        serviceId varchar(255),
31        resourceId varchar(255),
32        eventId int4 not null,
33        eventType varchar(255),
34        resourceIdCategory int4,
35        entityId varchar(255),
36        serviceName varchar(255),
37        organisationName varchar(255),
38        periodStart timestamp,
39        periodEnd timestamp,
40        jr1Downloads int4,
41        jr1aDownloads int4,
42        plId varchar(255),
43        totalDownloads int4,
44        primary key (persistantId)
45    );
46
47    create table ShibIdpAuthE (
48        persistantId int8 not null,
49        eventtime timestamp,
50        serviceHost varchar(700),
51        resourceHost varchar(255),
52        serviceId varchar(255),
53        resourceId varchar(255),
54        eventId int4 not null,
55        eventType varchar(255),
56        resourceIdCategory int4,
57        entityId varchar(255),
58        serviceName varchar(255),
59        organisationName varchar(255),
60        authenticationType varchar(255),
61        principalName varchar(255),
62        school varchar(255),
63        affiliation varchar(255),
64        requestID varchar(255),
65        messageProfileId varchar(255),
66        responseBinding varchar(255),
67        requestBinding varchar(255),
68        releasedattributes varchar(600),
69        assertionid varchar(255),
70        nameIdentifier varchar(255),
71        responseId varchar(255),
72        current_hashCode int4,
73        primary key (persistantId)
74    );
75
76    create table ShibSpAuthE (
77        persistantId int8 not null,
78        eventtime timestamp,
79        serviceHost varchar(700),
80        resourceHost varchar(255),
81        serviceId varchar(255),
82        resourceId varchar(255),
83        eventId int4 not null,
84        eventType varchar(255),
85        resourceIdCategory int4,
86        entityId varchar(255),
87        serviceName varchar(255),
88        organisationName varchar(255),
89        authenticationType varchar(255),
90        principalName varchar(255),
91        school varchar(255),
92        affiliation varchar(255),
93        protocol varchar(255),
94        clientIp varchar(255),
95        sessionId varchar(255),
96        primary key (persistantId)
97    );
98
99    create table WugenEvent (
100        persistantId int8 not null,
101        eventtime timestamp,
102        serviceHost varchar(700),
103        resourceHost varchar(255),
104        serviceId varchar(255),
105        resourceId varchar(255),
106        eventId int4 not null,
107        eventType varchar(255),
108        resourceIdCategory int4,
109        entityId varchar(255),
110        serviceName varchar(255),
111        organisationName varchar(255),
112        target varchar(255),
113        quality int4,
114        wayflessUrl varchar(255),
115        primary key (persistantId)
116    );
117
118    create table eduRadAuthE (
119        persistantId int8 not null,
120        eventtime timestamp,
121        serviceHost varchar(700),
122        resourceHost varchar(255),
123        serviceId varchar(255),
124        resourceId varchar(255),
125        eventId int4 not null,
126        eventType varchar(255),
127        resourceIdCategory int4,
128        entityId varchar(255),
129        serviceName varchar(255),
130        organisationName varchar(255),
131        authenticationType varchar(255),
132        principalName varchar(255),
133        school varchar(255),
134        affiliation varchar(255),
135        clientIdentifier varchar(255),
136        eapType varchar(255),
137        serviceClass varchar(255),
138        serverSoftware varchar(255),
139        result varchar(255),
140        resultInfo varchar(255),
141        userIdCui varchar(255),
142        realm varchar(255),
143        serviceClientIdentifier varchar(255),
144        rpOperatorName varchar(255),
145        serverSoftwareVersion varchar(255),
146        primary key (persistantId)
147    );
148
149    create table latestEqualEntries (
150        latestEqualEntries_id int8 not null,
151        hashcode int4
152    );
153
154    create table moonRadAuthE (
155        persistantId int8 not null,
156        eventtime timestamp,
157        serviceHost varchar(700),
158        resourceHost varchar(255),
159        serviceId varchar(255),
160        resourceId varchar(255),
161        eventId int4 not null,
162        eventType varchar(255),
163        resourceIdCategory int4,
164        entityId varchar(255),
165        serviceName varchar(255),
166        organisationName varchar(255),
167        authenticationType varchar(255),
168        principalName varchar(255),
169        school varchar(255),
170        affiliation varchar(255),
171        clientIdentifier varchar(255),
172        eapType varchar(255),
173        serviceClass varchar(255),
174        serverSoftware varchar(255),
175        result varchar(255),
176        resultInfo varchar(255),
177        userIdCui varchar(255),
178        realm varchar(255),
179        serviceClientIdentifier varchar(255),
180        rpOperatorName varchar(255),
181        serverSoftwareVersion varchar(255),
182        primary key (persistantId)
183    );
184
185    create table openAthensAuthE (
186        persistantId int8 not null,
187        eventtime timestamp,
188        serviceHost varchar(700),
189        resourceHost varchar(255),
190        serviceId varchar(255),
191        resourceId varchar(255),
192        eventId int4 not null,
193        eventType varchar(255),
194        resourceIdCategory int4,
195        entityId varchar(255),
196        serviceName varchar(255),
197        organisationName varchar(255),
198        authenticationType varchar(255),
199        principalName varchar(255),
200        school varchar(255),
201        affiliation varchar(255),
202        requesterIp varchar(255),
203        primary key (persistantId)
204    );
205
206    create table release_information (
207        persistantId int8 not null,
208        serviceEndpoint varchar(255) not null,
209        lastReleasedEventTime timestamp,
210        primary key (persistantId)
211    );
212
213    create table resource_metadata (
214        persistantId int8 not null,
215        resourceId varchar(255),
216        internal bool,
217        external bool,
218        primary key (persistantId)
219    );
220
221    create index EzproxyAuthEAuthEeventid_i on EzproxyAuthE (eventId);
222
223    create index EzproxyAuthEAuthEservicehost_i on EzproxyAuthE (serviceHost);
224
225    create index EzproxyAuthEAuthEorgname_i on EzproxyAuthE (organisationName);
226
227    create index EzproxyAuthEAuthEserviceid_i on EzproxyAuthE (serviceId);
228
229    create index EzproxyAuthEAuthEm_sname_i on EzproxyAuthE (serviceName);
230
231    create index EzproxyAuthEAuthEm_eid_i on EzproxyAuthE (entityId);
232
233    create index EzproxyAuthEAuthEdt_i on EzproxyAuthE (eventtime);
234
235    create index EzproxyAuthEp_affiliation_i on EzproxyAuthE (affiliation);
236
237    create index EzproxyAuthEprincipal_name_i on EzproxyAuthE (principalName);
238
239    create index EzproxyAuthEp_school_i on EzproxyAuthE (school);
240
241    create index ezproxy_hashcode_i on EzproxyAuthE (current_hashCode);
242
243    create index JuspAggEAggEeventid_i on JuspAggE (eventId);
244
245    create index JuspAggEAggEservicehost_i on JuspAggE (serviceHost);
246
247    create index JuspAggEAggEorgname_i on JuspAggE (organisationName);
248
249    create index JuspAggEAggEserviceid_i on JuspAggE (serviceId);
250
251    create index JuspAggEAggEm_sname_i on JuspAggE (serviceName);
252
253    create index JuspAggEAggEm_eid_i on JuspAggE (entityId);
254
255    create index JuspAggEAggEdt_i on JuspAggE (eventtime);
256
257    create index ShibIdpAuthEAuthEeventid_i on ShibIdpAuthE (eventId);
258
259    create index ShibIdpAuthEAuthEservicehost_i on ShibIdpAuthE (serviceHost);
260
261    create index ShibIdpAuthEAuthEorgname_i on ShibIdpAuthE (organisationName);
262
263    create index ShibIdpAuthEAuthEserviceid_i on ShibIdpAuthE (serviceId);
264
265    create index ShibIdpAuthEAuthEm_sname_i on ShibIdpAuthE (serviceName);
266
267    create index ShibIdpAuthEAuthEm_eid_i on ShibIdpAuthE (entityId);
268
269    create index ShibIdpAuthEAuthEdt_i on ShibIdpAuthE (eventtime);
270
271    create index ShibIdpAuthEp_affiliation_i on ShibIdpAuthE (affiliation);
272
273    create index ShibIdpAuthEprincipal_name_i on ShibIdpAuthE (principalName);
274
275    create index ShibIdpAuthEp_school_i on ShibIdpAuthE (school);
276
277    create index shib_idp_hashcode_i on ShibIdpAuthE (current_hashCode);
278
279    create index ShibSpAuthEAuthEeventid_i on ShibSpAuthE (eventId);
280
281    create index ShibSpAuthEAuthEservicehost_i on ShibSpAuthE (serviceHost);
282
283    create index ShibSpAuthEAuthEorgname_i on ShibSpAuthE (organisationName);
284
285    create index ShibSpAuthEAuthEserviceid_i on ShibSpAuthE (serviceId);
286
287    create index ShibSpAuthEAuthEm_sname_i on ShibSpAuthE (serviceName);
288
289    create index ShibSpAuthEAuthEm_eid_i on ShibSpAuthE (entityId);
290
291    create index ShibSpAuthEAuthEdt_i on ShibSpAuthE (eventtime);
292
293    create index ShibSpAuthEp_affiliation_i on ShibSpAuthE (affiliation);
294
295    create index ShibSpAuthEprincipal_name_i on ShibSpAuthE (principalName);
296
297    create index ShibSpAuthEp_school_i on ShibSpAuthE (school);
298
299    create index WugenEventeventid_i on WugenEvent (eventId);
300
301    create index WugenEventservicehost_i on WugenEvent (serviceHost);
302
303    create index WugenEventorgname_i on WugenEvent (organisationName);
304
305    create index WugenEventserviceid_i on WugenEvent (serviceId);
306
307    create index WugenEventm_sname_i on WugenEvent (serviceName);
308
309    create index WugenEventm_eid_i on WugenEvent (entityId);
310
311    create index WugenEventdt_i on WugenEvent (eventtime);
312
313    create index eduRadAuthEAuthEeventid_i on eduRadAuthE (eventId);
314
315    create index eduRadAuthEAuthEservicehost_i on eduRadAuthE (serviceHost);
316
317    create index eduRadAuthEAuthEorgname_i on eduRadAuthE (organisationName);
318
319    create index eduRadAuthEAuthEserviceid_i on eduRadAuthE (serviceId);
320
321    create index eduRadAuthEAuthEm_sname_i on eduRadAuthE (serviceName);
322
323    create index eduRadAuthEAuthEm_eid_i on eduRadAuthE (entityId);
324
325    create index eduRadAuthEAuthEdt_i on eduRadAuthE (eventtime);
326
327    create index eduRadAuthEp_affiliation_i on eduRadAuthE (affiliation);
328
329    create index eduRadAuthEprincipal_name_i on eduRadAuthE (principalName);
330
331    create index eduRadAuthEp_school_i on eduRadAuthE (school);
332
333    alter table latestEqualEntries
334        add constraint FK283FC3E3AAC8B6DE
335        foreign key (latestEqualEntries_id)
336        references release_information;
337
338    create index moonRadAuthEAuthEeventid_i on moonRadAuthE (eventId);
339
340    create index moonRadAuthEAuthEservicehost_i on moonRadAuthE (serviceHost);
341
342    create index moonRadAuthEAuthEorgname_i on moonRadAuthE (organisationName);
343
344    create index moonRadAuthEAuthEserviceid_i on moonRadAuthE (serviceId);
345
346    create index moonRadAuthEAuthEm_sname_i on moonRadAuthE (serviceName);
347
348    create index moonRadAuthEAuthEm_eid_i on moonRadAuthE (entityId);
349
350    create index moonRadAuthEAuthEdt_i on moonRadAuthE (eventtime);
351
352    create index moonRadAuthEp_affiliation_i on moonRadAuthE (affiliation);
353
354    create index moonRadAuthEprincipal_name_i on moonRadAuthE (principalName);
355
356    create index moonRadAuthEp_school_i on moonRadAuthE (school);
357
358    create index openAthensAuthEAuthEeventid_i on openAthensAuthE (eventId);
359
360    create index openAthensAuthEAuthEservicehost_i on openAthensAuthE (serviceHost);
361
362    create index openAthensAuthEAuthEorgname_i on openAthensAuthE (organisationName);
363
364    create index openAthensAuthEAuthEserviceid_i on openAthensAuthE (serviceId);
365
366    create index openAthensAuthEAuthEm_sname_i on openAthensAuthE (serviceName);
367
368    create index openAthensAuthEAuthEm_eid_i on openAthensAuthE (entityId);
369
370    create index openAthensAuthEAuthEdt_i on openAthensAuthE (eventtime);
371
372    create index openAthensAuthEp_affiliation_i on openAthensAuthE (affiliation);
373
374    create index openAthensAuthEprincipal_name_i on openAthensAuthE (principalName);
375
376    create index openAthensAuthEp_school_i on openAthensAuthE (school);
377
378    create index rm_external_index on resource_metadata (external);
379
380    create index rm_internal_index on resource_metadata (external);
381
382    create index rm_resourceid_index on resource_metadata (resourceId);
383
384    create sequence hib_release_information_seq;
385
386    create sequence hib_resource_metadata_seq;
387
388    create table hibernate_unique_key (
389         next_hi int4
390    );
391
392    insert into hibernate_unique_key values ( 0 );