Subversion Repositories general

Rev

Rev 969 | Rev 975 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
3 dev 1
--
11 dev 2
-- kickup sql tables
3 dev 3
--
4
 
5
create sequence hibernate_sequence;
6
 
9 dev 7
/* removed in this version
3 dev 8
create table participants
9
(
10
	id          integer                         not null,
11
	nick        varchar(255)                    not null,
12
	email       varchar(255)                    not null,
13
	name        varchar(255),
14
	phone       varchar(255),
15
	comment     text,
16
	mod_stamp   timestamp,
17
 
18
	constraint participants_prim  primary key (id),
19
	constraint participants_email unique(email)
20
);
9 dev 21
*/
3 dev 22
 
10 dev 23
create table acts
24
(
25
	id          integer                         not null,
26
	name        varchar(255)                    not null,
27
	comment     text,
28
	mod_stamp   timestamp,
29
 
30
	constraint acts_prim primary key (id),
31
	constraint acts_name unique(name)
32
);
33
 
34
create table apartments
35
(
36
	id          integer                         not null,
37
	name        varchar(255)                    not null,
38
	address     text,
39
	price       decimal,
40
	url         varchar(4000),
41
	comment     text,
42
	mod_stamp   timestamp,
43
 
31 dev 44
	constraint apartments_prim primary key (id),
45
	constraint apartments_name unique(name)
10 dev 46
);
47
 
3 dev 48
create table events
49
(
50
	id              integer                     not null,
51
	name            varchar(255)                not null,
19 dev 52
	email           varchar(255)                not null,
53
	admins          varchar(255)                not null,
972 dev 54
	phones          varchar(255),
3 dev 55
	place           varchar(255),
56
	address         text,
57
	transport_desc  text,
58
	start           date,
59
	stop            date,
60
	last_register   date,
61
	last_unregister date,
62
	price           decimal,
63
	money_account   text,
64
	enabled         char(1)                     default '1',
65
	comment         text,
66
	mod_stamp       timestamp,
67
 
68
	constraint events_prim primary key (id),
69
	constraint events_name unique(name)
70
);
71
 
14 dev 72
/*
3 dev 73
create table event_files
74
(
75
	id          integer                         not null,
76
	event       integer                         not null,
77
	name        varchar(255)                    not null,
78
	data        file,
79
	type        char(1),
80
	comment     text,
81
	mod_stamp   timestamp,
82
 
83
	constraint event_files_prim primary key (id)
84
);
14 dev 85
*/
3 dev 86
 
87
create table event_apartments
88
(
89
	id          integer                         not null,
90
	event       integer                         not null,
9 dev 91
	apartment   integer                         not null,
3 dev 92
	distance    decimal,
93
	comment     text,
94
	mod_stamp   timestamp,
95
 
31 dev 96
	constraint event_apartments_prim      primary key (id),
97
	constraint event_apartments_event     foreign key (event)     references events(id),
98
	constraint event_apartments_apartment foreign key (apartment) references apartments(id)
3 dev 99
);
100
 
101
create table event_acts
102
(
103
	id          integer                         not null,
104
	event       integer                         not null,
105
	act         integer                         not null,
106
	comment     text,
107
	mod_stamp   timestamp,
108
 
31 dev 109
	constraint event_acts_prim  primary key (id),
110
	constraint event_acts_event foreign key (event) references events(id),
111
	constraint event_acts_act   foreign key (act)   references acts(id)
3 dev 112
);
113
 
9 dev 114
/* removed in this version
3 dev 115
create table participant_events
116
(
117
	participant       integer                   not null,
118
	event             integer                   not null,
119
	persons           integer,
120
	from_zip          varchar(10),
121
	from_city         varchar(10),
122
	departure         datetime,
123
	free_transport    integer,
124
	transport_comment integer,
125
	free_sleep        integer,
126
	sleep_comment     text,
9 dev 127
	payed             char(1)                   default ' ',
3 dev 128
	comment           text,
129
	mod_stamp         timestamp,
130
 
131
	constraint participant_events_prim primary key (participant, event)
132
);
9 dev 133
*/
3 dev 134
 
9 dev 135
create table participants
136
(
137
	id                integer                   not null,
138
	ident             varchar(255)              not null,
139
	nick              varchar(255)              not null,
140
	email             varchar(255)              not null,
141
	email_public      char(1)                   default ' ',
142
	name              varchar(255),
143
	phone             varchar(255),
144
	event             integer                   not null,
968 dev 145
	persons           integer                   not null,
9 dev 146
	from_zip          varchar(10),
147
	from_city         varchar(10),
148
	departure         timestamp,
149
	free_transport    integer,
150
	transport_comment text,
151
	free_sleep        integer,
152
	sleep_comment     text,
969 dev 153
	payed             decimal,
9 dev 154
	comment           text,
155
	private_comment   text,
156
	mod_stamp         timestamp,
157
 
158
	constraint participants_prim  primary key (id),
32 dev 159
	constraint participants_email unique(email, event)
9 dev 160
);
161
 
3 dev 162
create table participant_acts
163
(
27 dev 164
    id                integer                   not null,
165
	participant       integer                   not null,
166
	act               integer                   not null,
167
	comment           text,
168
	mod_stamp         timestamp,
3 dev 169
 
31 dev 170
	constraint participant_acts_prim        primary key (id),
171
	constraint participant_acts_participant foreign key (participant) references participants(id),
172
	constraint participant_acts_act         foreign key (act)         references acts(id)
3 dev 173
);