Subversion Repositories general

--
-- kickup sql tables
--

create sequence hibernate_sequence;

/* removed in this version
create table participants
(
        id          integer                         not null,
        nick        varchar(255)                    not null,
        email       varchar(255)                    not null,
        name        varchar(255),
        phone       varchar(255),
        comment     text,
        mod_stamp   timestamp,

        constraint participants_prim  primary key (id),
        constraint participants_email unique(email)
);
*/

create table acts
(
        id          integer                         not null,
        name        varchar(255)                    not null,
        comment     text,
        mod_stamp   timestamp,

        constraint acts_prim primary key (id),
        constraint acts_name unique(name)
);

create table apartments
(
        id          integer                         not null,
        name        varchar(255)                    not null,
        address     text,
        price       decimal,
        url         varchar(4000),
        comment     text,
        mod_stamp   timestamp,

        constraint apartments_prim primary key (id),
        constraint apartments_name unique(name)
);

create table events
(
        id              integer                     not null,
        name            varchar(255)                not null,
        email           varchar(255)                not null,
        admins          varchar(255)                not null,
        phones          varchar(255),
        place           varchar(255),
        address         text,
        transport_desc  text,
        start           date,
        stop            date,
        last_register   date,
        last_unregister date,
        price           decimal,
        money_account   text,
        enabled         char(1)                     default '1',
        comment         text,
        mod_stamp       timestamp,

        constraint events_prim primary key (id),
        constraint events_name unique(name)
);

/*
create table event_files
(
        id          integer                         not null,
        event       integer                         not null,
        name        varchar(255)                    not null,
        data        file,
        type        char(1),
        comment     text,
        mod_stamp   timestamp,

        constraint event_files_prim primary key (id)
);
*/

create table event_apartments
(
        id          integer                         not null,
        event       integer                         not null,
        apartment   integer                         not null,
        distance    decimal,
        comment     text,
        mod_stamp   timestamp,

        constraint event_apartments_prim      primary key (id),
        constraint event_apartments_event     foreign key (event)     references events(id),
        constraint event_apartments_apartment foreign key (apartment) references apartments(id)
);

create table event_acts
(
        id          integer                         not null,
        event       integer                         not null,
        act         integer                         not null,
        comment     text,
        mod_stamp   timestamp,

        constraint event_acts_prim  primary key (id),
        constraint event_acts_event foreign key (event) references events(id),
        constraint event_acts_act   foreign key (act)   references acts(id)
);

/* removed in this version
create table participant_events
(
        participant       integer                   not null,
        event             integer                   not null,
        persons           integer,
        from_zip          varchar(10),
        from_city         varchar(10),
        departure         datetime,
        free_transport    integer,
        transport_comment integer,
        free_sleep        integer,
        sleep_comment     text,
        payed             char(1)                   default ' ',
        comment           text,
        mod_stamp         timestamp,

        constraint participant_events_prim primary key (participant, event)
);
*/

create table participants
(
        id                integer                   not null,
        ident             varchar(255)              not null,
        nick              varchar(255)              not null,
        email             varchar(255)              not null,
        email_public      char(1)                   default ' ',
        name              varchar(255),
        phone             varchar(255),
        event             integer                   not null,
        persons           integer                   not null,
        from_zip          varchar(10),
        from_city         varchar(100),
        departure         timestamp,
        free_transport    integer,
        transport_comment text,
        free_sleep        integer,
        sleep_comment     text,
        payed             decimal,
        comment           text,
        private_comment   text,
        mod_stamp         timestamp,

        constraint participants_prim  primary key (id),
        constraint participants_email unique(email, event)
);

create table participant_acts
(
    id                integer                   not null,
        participant       integer                   not null,
        act               integer                   not null,
        comment           text,
        mod_stamp         timestamp,

        constraint participant_acts_prim        primary key (id),
        constraint participant_acts_participant foreign key (participant) references participants(id),
        constraint participant_acts_act         foreign key (act)         references acts(id)
);