forked from davidpanderson/science_united
-
Notifications
You must be signed in to change notification settings - Fork 0
/
schema.sql
190 lines (177 loc) · 8.42 KB
/
schema.sql
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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
/*
* schema for Science United database.
* These tables are added to a standard BOINC database.
*/
/*
* represents a BOINC project
*/
create table su_project (
id integer not null auto_increment,
create_time double not null,
name varchar(254) not null,
url varchar(254) not null,
web_rpc_url_base varchar(254) not null,
url_signature varchar(1024) not null,
share double not null,
status tinyint not null,
avg_ec double not null,
# average EC per second over last week
avg_ec_adjusted double not null,
# same, but adjusted for hosts arriving and leaving
# reset to avg_ec at start of each accounting period
web_url varchar(254) not null,
authenticator varchar(254) not null,
nhosts integer not null,
# value at end of last accounting period
# rough estimate of how many hosts attached
primary key (id)
) engine=InnoDB;
/* an allocation to a project. Not implemented yet */
create table su_allocation (
id integer not null auto_increment,
project_id integer not null,
init_alloc double not null,
alloc_rate double not null,
start_time double not null,
duration double not null,
status tinyint not null,
/* 0 init, 1 in progress, 2 over */
primary key (id)
) engine=InnoDB;
create table su_user_keyword (
user_id integer not null,
keyword_id integer not null,
yesno smallint not null,
/* -1=no, 1=yes */
unique(user_id, keyword_id)
) engine=InnoDB;
/*
* a user account on a project.
* NOTE: originally SU users had separate project accounts,
* which had to be dynamically created by RPCs.
* This has been replaced by a "single-account" model where
* all SU users share a single project account.
* We still have separate su_account records per user,
* for accounting purposes; they'll have the same authenticator.
*/
create table su_account (
user_id integer not null,
project_id integer not null,
create_time double not null,
authenticator varchar(254) not null,
state smallint not null,
retry_time double not null default 0,
cpu_ec double not null default 0,
cpu_time double not null default 0,
gpu_ec double not null default 0,
gpu_time double not null default 0,
njobs_success integer not null default 0,
njobs_fail integer not null default 0,
opt_out tinyint not null default 0,
email_addr varchar(254) not null default '',
passwd_hash varchar(254) not null default '',
name varchar(254) not null default '',
unique(user_id, project_id),
index account_state (state)
) engine=InnoDB;
/*
* Per (host, project) record. No history.
* Stores totals at last AM RPC; used to compute deltas.
*/
create table su_host_project (
host_id integer not null,
project_id integer not null,
last_rpc double not null default 0,
/* when host last made RPC to project */
active tinyint not null default 0,
/* whether host is currently attached to project */
requested tinyint not null default 0,
/* what is this? */
cpu_ec double not null default 0,
cpu_time double not null default 0,
gpu_ec double not null default 0,
gpu_time double not null default 0,
njobs_success integer not null default 0,
njobs_fail integer not null default 0,
project_host_id integer not null default 0,
unique(host_id, project_id),
index project_host (project_id, project_host_id)
) engine=InnoDB;
/*
* historical accounting records, for
* - total
* - per project
* - per user
*
* We could also have:
* per-host: might be worth it
* per-user-project, per-host-project etc.: probably not worth it.
*/
create table su_accounting (
id integer not null auto_increment,
create_time double not null default 0,
cpu_ec_delta double not null default 0,
cpu_ec_total double not null default 0,
gpu_ec_delta double not null default 0,
gpu_ec_total double not null default 0,
cpu_time_delta double not null default 0,
cpu_time_total double not null default 0,
gpu_time_delta double not null default 0,
gpu_time_total double not null default 0,
nactive_hosts integer not null default 0,
nactive_hosts_gpu integer not null default 0,
nactive_users integer not null default 0,
njobs_success_delta integer not null default 0,
njobs_success_total integer not null default 0,
njobs_fail_delta integer not null default 0,
njobs_fail_total integer not null default 0,
primary key (id)
) engine=InnoDB;
create table su_accounting_project (
id integer not null auto_increment,
create_time double not null default 0,
project_id integer not null default 0,
share double not null default 0,
cpu_ec_delta double not null default 0,
cpu_ec_total double not null default 0,
gpu_ec_delta double not null default 0,
gpu_ec_total double not null default 0,
cpu_time_delta double not null default 0,
cpu_time_total double not null default 0,
gpu_time_delta double not null default 0,
gpu_time_total double not null default 0,
njobs_success_delta integer not null default 0,
njobs_success_total integer not null default 0,
njobs_fail_delta integer not null default 0,
njobs_fail_total integer not null default 0,
nhosts integer not null default 0,
# # of hosts that have reported being attached to this project
# during current accounting period.
index (project_id),
primary key (id)
) engine=InnoDB;
create table su_accounting_user (
id integer not null auto_increment,
create_time double not null default 0,
user_id integer not null default 0,
cpu_ec_delta double not null default 0,
cpu_ec_total double not null default 0,
gpu_ec_delta double not null default 0,
gpu_ec_total double not null default 0,
cpu_time_delta double not null default 0,
cpu_time_total double not null default 0,
gpu_time_delta double not null default 0,
gpu_time_total double not null default 0,
njobs_success_delta integer not null default 0,
njobs_success_total integer not null default 0,
njobs_fail_delta integer not null default 0,
njobs_fail_total integer not null default 0,
index (user_id),
primary key (id)
) engine=InnoDB;
/* global allocation info (1 record) */
create table su_allocate (
nprojects integer not null default 0,
avg_ec_total double not null default 0,
share_total double not null default 0
) engine=InnoDB;