wiki:Software/Productionalisation/MuaSchema

Version 4 (modified by smartp@…, 7 years ago) (diff)

--

MUA Schema


    create table AggregatedEvent (
        persistantId int8 not null,
        eventtime timestamp,
        serviceHost varchar(400),
        resourceHost varchar(255),
        serviceId varchar(255),
        resourceId varchar(255),
        eventId int4 not null,
        eventType varchar(255),
        resourceIdCategory int4,
        entityId varchar(255),
        serviceName varchar(255),
        organisationName varchar(255),
        periodStart timestamp,
        periodEnd timestamp,
        primary key (persistantId)
    );

    create table AuthenticationEvent (
        persistantId int8 not null,
        eventtime timestamp,
        serviceHost varchar(400),
        resourceHost varchar(255),
        serviceId varchar(255),
        resourceId varchar(255),
        eventId int4 not null,
        eventType varchar(255),
        resourceIdCategory int4,
        entityId varchar(255),
        serviceName varchar(255),
        organisationName varchar(255),
        authenticationType varchar(255),
        principalName varchar(255),
        school varchar(255),
        affiliation varchar(255),
        primary key (persistantId)
    );

    create table EzproxyAuthenticationEvent (
        persistantId int8 not null,
        eventtime timestamp,
        serviceHost varchar(400),
        resourceHost varchar(255),
        serviceId varchar(255),
        resourceId varchar(255),
        eventId int4 not null,
        eventType varchar(255),
        resourceIdCategory int4,
        entityId varchar(255),
        serviceName varchar(255),
        organisationName varchar(255),
        authenticationType varchar(255),
        principalName varchar(255),
        school varchar(255),
        affiliation varchar(255),
        requesterIp varchar(255),
        sessionId varchar(255),
        current_hashCode int4,
        primary key (persistantId)
    );

    create table JuspAggregatedEvent (
        persistantId int8 not null,
        eventtime timestamp,
        serviceHost varchar(400),
        resourceHost varchar(255),
        serviceId varchar(255),
        resourceId varchar(255),
        eventId int4 not null,
        eventType varchar(255),
        resourceIdCategory int4,
        entityId varchar(255),
        serviceName varchar(255),
        organisationName varchar(255),
        periodStart timestamp,
        periodEnd timestamp,
        jr1Downloads int4,
        jr1aDownloads int4,
        plId varchar(255),
        totalDownloads int4,
        primary key (persistantId)
    );

    create table RadiusAuthenticationEvent (
        persistantId int8 not null,
        eventtime timestamp,
        serviceHost varchar(400),
        resourceHost varchar(255),
        serviceId varchar(255),
        resourceId varchar(255),
        eventId int4 not null,
        eventType varchar(255),
        resourceIdCategory int4,
        entityId varchar(255),
        serviceName varchar(255),
        organisationName varchar(255),
        authenticationType varchar(255),
        principalName varchar(255),
        school varchar(255),
        affiliation varchar(255),
        clientIdentifier varchar(255),
        primary key (persistantId)
    );

    create table ShibbolethIdpAuthenticationEvent (
        persistantId int8 not null,
        eventtime timestamp,
        serviceHost varchar(400),
        resourceHost varchar(255),
        serviceId varchar(255),
        resourceId varchar(255),
        eventId int4 not null,
        eventType varchar(255),
        resourceIdCategory int4,
        entityId varchar(255),
        serviceName varchar(255),
        organisationName varchar(255),
        authenticationType varchar(255),
        principalName varchar(255),
        school varchar(255),
        affiliation varchar(255),
        requestID varchar(255),
        messageProfileId varchar(255),
        responseBinding varchar(255),
        requestBinding varchar(255),
        releasedattributes varchar(400),
        assertionid varchar(255),
        nameIdentifier varchar(255),
        responseId varchar(255),
        current_hashCode int4,
        primary key (persistantId)
    );

    create table ShibbolethSpAuthenticationEvent (
        persistantId int8 not null,
        eventtime timestamp,
        serviceHost varchar(400),
        resourceHost varchar(255),
        serviceId varchar(255),
        resourceId varchar(255),
        eventId int4 not null,
        eventType varchar(255),
        resourceIdCategory int4,
        entityId varchar(255),
        serviceName varchar(255),
        organisationName varchar(255),
        authenticationType varchar(255),
        principalName varchar(255),
        school varchar(255),
        affiliation varchar(255),
        protocol varchar(255),
        clientIp varchar(255),
        sessionId varchar(255),
        primary key (persistantId)
    );

    create table WugenEvent (
        persistantId int8 not null,
        eventtime timestamp,
        serviceHost varchar(400),
        resourceHost varchar(255),
        serviceId varchar(255),
        resourceId varchar(255),
        eventId int4 not null,
        eventType varchar(255),
        resourceIdCategory int4,
        entityId varchar(255),
        serviceName varchar(255),
        organisationName varchar(255),
        target varchar(255),
        quality int4,
        wayflessUrl varchar(255),
        primary key (persistantId)
    );

    create table event (
        persistantId int8 not null,
        eventtime timestamp,
        serviceHost varchar(400),
        resourceHost varchar(255),
        serviceId varchar(255),
        resourceId varchar(255),
        eventId int4 not null,
        eventType varchar(255),
        resourceIdCategory int4,
        entityId varchar(255),
        serviceName varchar(255),
        organisationName varchar(255),
        primary key (persistantId)
    );

    create table latestEqualEntries (
        latestEqualEntries_id int8 not null,
        hashcode int4
    );

    create table release_information (
        persistantId int8 not null,
        serviceEndpoint varchar(255) not null,
        lastReleasedEventTime timestamp,
        primary key (persistantId)
    );

    create table resource_metadata (
        persistantId int8 not null,
        resourceId varchar(255),
        internal bool,
        external bool,
        primary key (persistantId)
    );

    create index AggregatedEventeventid_i on AggregatedEvent (eventId);

    create index AggregatedEventorgname_i on AggregatedEvent (organisationName);

    create index AggregatedEventserviceid_i on AggregatedEvent (serviceId);

    create index AggregatedEventservicename_i on AggregatedEvent (serviceName);

    create index AggregatedEventrequesthost_i on AggregatedEvent (serviceHost);

    create index AggregatedEvententityid_i on AggregatedEvent (entityId);

    create index AggregatedEventdt_i on AggregatedEvent (eventtime);

    create index AuthenticationEventeventid_i on AuthenticationEvent (eventId);

    create index AuthenticationEventorgname_i on AuthenticationEvent (organisationName);

    create index AuthenticationEventserviceid_i on AuthenticationEvent (serviceId);

    create index AuthenticationEventservicename_i on AuthenticationEvent (serviceName);

    create index AuthenticationEventrequesthost_i on AuthenticationEvent (serviceHost);

    create index AuthenticationEvententityid_i on AuthenticationEvent (entityId);

    create index AuthenticationEventdt_i on AuthenticationEvent (eventtime);

    create index affiliation_i on AuthenticationEvent (affiliation);

    create index principalname_i on AuthenticationEvent (principalName);

    create index school_i on AuthenticationEvent (school);

    create index EzproxyAuthenticationEventAuthenticationEventeventid_i on EzproxyAuthenticationEvent (eventId);

    create index EzproxyAuthenticationEventAuthenticationEventorgname_i on EzproxyAuthenticationEvent (organisationName);

    create index EzproxyAuthenticationEventAuthenticationEventserviceid_i on EzproxyAuthenticationEvent (serviceId);

    create index EzproxyAuthenticationEventAuthenticationEventservicename_i on EzproxyAuthenticationEvent (serviceName);

    create index EzproxyAuthenticationEventAuthenticationEventrequesthost_i on EzproxyAuthenticationEvent (serviceHost);

    create index EzproxyAuthenticationEventAuthenticationEvententityid_i on EzproxyAuthenticationEvent (entityId);

    create index EzproxyAuthenticationEventAuthenticationEventdt_i on EzproxyAuthenticationEvent (eventtime);

    create index EzproxyAuthenticationEventaffiliation_i on EzproxyAuthenticationEvent (affiliation);

    create index EzproxyAuthenticationEventprincipalname_i on EzproxyAuthenticationEvent (principalName);

    create index EzproxyAuthenticationEventschool_i on EzproxyAuthenticationEvent (school);

    create index hashcode_i on EzproxyAuthenticationEvent (current_hashCode);

    create index JuspAggregatedEventAggregatedEventeventid_i on JuspAggregatedEvent (eventId);

    create index JuspAggregatedEventAggregatedEventorgname_i on JuspAggregatedEvent (organisationName);

    create index JuspAggregatedEventAggregatedEventserviceid_i on JuspAggregatedEvent (serviceId);

    create index JuspAggregatedEventAggregatedEventservicename_i on JuspAggregatedEvent (serviceName);

    create index JuspAggregatedEventAggregatedEventrequesthost_i on JuspAggregatedEvent (serviceHost);

    create index JuspAggregatedEventAggregatedEvententityid_i on JuspAggregatedEvent (entityId);

    create index JuspAggregatedEventAggregatedEventdt_i on JuspAggregatedEvent (eventtime);

    create index RadiusAuthenticationEventAuthenticationEventeventid_i on RadiusAuthenticationEvent (eventId);

    create index RadiusAuthenticationEventAuthenticationEventorgname_i on RadiusAuthenticationEvent (organisationName);

    create index RadiusAuthenticationEventAuthenticationEventserviceid_i on RadiusAuthenticationEvent (serviceId);

    create index RadiusAuthenticationEventAuthenticationEventservicename_i on RadiusAuthenticationEvent (serviceName);

    create index RadiusAuthenticationEventAuthenticationEventrequesthost_i on RadiusAuthenticationEvent (serviceHost);

    create index RadiusAuthenticationEventAuthenticationEvententityid_i on RadiusAuthenticationEvent (entityId);

    create index RadiusAuthenticationEventAuthenticationEventdt_i on RadiusAuthenticationEvent (eventtime);

    create index RadiusAuthenticationEventaffiliation_i on RadiusAuthenticationEvent (affiliation);

    create index RadiusAuthenticationEventprincipalname_i on RadiusAuthenticationEvent (principalName);

    create index RadiusAuthenticationEventschool_i on RadiusAuthenticationEvent (school);

    create index ShibbolethIdpAuthenticationEventAuthenticationEventeventid_i on ShibbolethIdpAuthenticationEvent (eventId);

    create index ShibbolethIdpAuthenticationEventAuthenticationEventorgname_i on ShibbolethIdpAuthenticationEvent (organisationName);

    create index ShibbolethIdpAuthenticationEventAuthenticationEventserviceid_i on ShibbolethIdpAuthenticationEvent (serviceId);

    create index ShibbolethIdpAuthenticationEventAuthenticationEventservicename_i on ShibbolethIdpAuthenticationEvent (serviceName);

    create index ShibbolethIdpAuthenticationEventAuthenticationEventrequesthost_i on ShibbolethIdpAuthenticationEvent (serviceHost);

    create index ShibbolethIdpAuthenticationEventAuthenticationEvententityid_i on ShibbolethIdpAuthenticationEvent (entityId);

    create index ShibbolethIdpAuthenticationEventAuthenticationEventdt_i on ShibbolethIdpAuthenticationEvent (eventtime);

    create index ShibbolethIdpAuthenticationEventaffiliation_i on ShibbolethIdpAuthenticationEvent (affiliation);

    create index ShibbolethIdpAuthenticationEventprincipalname_i on ShibbolethIdpAuthenticationEvent (principalName);

    create index ShibbolethIdpAuthenticationEventschool_i on ShibbolethIdpAuthenticationEvent (school);

    create index hashcode_i on ShibbolethIdpAuthenticationEvent (current_hashCode);

    create index ShibbolethSpAuthenticationEventAuthenticationEventeventid_i on ShibbolethSpAuthenticationEvent (eventId);

    create index ShibbolethSpAuthenticationEventAuthenticationEventorgname_i on ShibbolethSpAuthenticationEvent (organisationName);

    create index ShibbolethSpAuthenticationEventAuthenticationEventserviceid_i on ShibbolethSpAuthenticationEvent (serviceId);

    create index ShibbolethSpAuthenticationEventAuthenticationEventservicename_i on ShibbolethSpAuthenticationEvent (serviceName);

    create index ShibbolethSpAuthenticationEventAuthenticationEventrequesthost_i on ShibbolethSpAuthenticationEvent (serviceHost);

    create index ShibbolethSpAuthenticationEventAuthenticationEvententityid_i on ShibbolethSpAuthenticationEvent (entityId);

    create index ShibbolethSpAuthenticationEventAuthenticationEventdt_i on ShibbolethSpAuthenticationEvent (eventtime);

    create index ShibbolethSpAuthenticationEventaffiliation_i on ShibbolethSpAuthenticationEvent (affiliation);

    create index ShibbolethSpAuthenticationEventprincipalname_i on ShibbolethSpAuthenticationEvent (principalName);

    create index ShibbolethSpAuthenticationEventschool_i on ShibbolethSpAuthenticationEvent (school);

    create index WugenEventeventid_i on WugenEvent (eventId);

    create index WugenEventorgname_i on WugenEvent (organisationName);

    create index WugenEventserviceid_i on WugenEvent (serviceId);

    create index WugenEventservicename_i on WugenEvent (serviceName);

    create index WugenEventrequesthost_i on WugenEvent (serviceHost);

    create index WugenEvententityid_i on WugenEvent (entityId);

    create index WugenEventdt_i on WugenEvent (eventtime);

    create index eventid_i on event (eventId);

    create index orgname_i on event (organisationName);

    create index serviceid_i on event (serviceId);

    create index servicename_i on event (serviceName);

    create index requesthost_i on event (serviceHost);

    create index entityid_i on event (entityId);

    create index dt_i on event (eventtime);

    alter table latestEqualEntries 
        add constraint FK283FC3E3AAC8B6DE 
        foreign key (latestEqualEntries_id) 
        references release_information;

    create index serviceid_index on resource_metadata (external);

    create index requesthost_index on resource_metadata (resourceId);

    create sequence hib_release_information_seq;

    create sequence hib_resource_metadata_seq;

    create table hibernate_unique_key (
         next_hi int4 
    );

    insert into hibernate_unique_key values ( 0 );