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
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
|
# h-source, a web software to build a community of people that want to share their hardware information.
# Copyright (C) 2010 Antonio Gallo (h-source-copyright.txt)
#
# This file is part of h-source
#
# h-source is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# h-source is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with h-source. If not, see <http://www.gnu.org/licenses/>.
create table regusers (
id_user INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(80) binary NOT NULL,
password CHAR(40) binary NOT NULL,
last_failure INT UNSIGNED NOT NULL,
has_confirmed INT UNSIGNED NOT NULL,
e_mail VARCHAR(60) binary NOT NULL,
confirmation_token CHAR(32) NOT NULL,
creation_date timestamp default CURRENT_TIMESTAMP,
creation_time INT UNSIGNED NOT NULL,
temp_field CHAR(32) NOT NULL,
deleted CHAR(4) NOT NULL default 'no',
blocked CHAR(4) NOT NULL default 'no',
forgot_token CHAR(32) NOT NULL,
forgot_time INT UNSIGNED NOT NULL,
unique(username),
index(username, password)
)engine=innodb;
create table reggroups (
id_group INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) binary NOT NULL,
unique(name)
)engine=innodb;
create table regusers_groups (
id_user int(11) UNSIGNED not null,
id_group int(11) UNSIGNED not null,
index group_indx(id_group),
index user_indx(id_user),
foreign key group_fky(id_group) references reggroups (id_group),
foreign key user_fky(id_user) references regusers (id_user),
unique (id_group,id_user)
)engine=innodb;
CREATE TABLE regsessions (
uid CHAR(32) NOT NULL,
token CHAR(32) NOT NULL,
id_user INT UNSIGNED NOT NULL,
creation_date INT UNSIGNED NOT NULL,
user_agent CHAR(32) NOT NULL,
INDEX(uid)
)engine=innodb;
create table regaccesses (
id int(12) not null auto_increment primary key,
ip char(20) not null,data char(10) not null,
ora char(8) not null,
username varchar(30) not null
);
create table hardware (
id_hard INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
type varchar(50) not null,
subtype char(15) not null,
kernel varchar(50) not null,
vendor varchar(50) not null,
model varchar(100) not null,
created_by int not null,
updated_by int not null,
creation_date timestamp default CURRENT_TIMESTAMP,
update_date datetime not null,
compatibility char(15) not null,
description text CHARACTER SET utf8 not null,
distribution varchar(300) CHARACTER SET utf8 not null,
video_card_type varchar(100) CHARACTER SET utf8 not null,
video_card_works varchar(30) CHARACTER SET utf8 not null,
wifi_type varchar(100) CHARACTER SET utf8 not null,
wifi_works varchar(30) CHARACTER SET utf8 not null,
comm_year char(15) not null,
ask_for_del char(4) not null default 'no',
deleted char(4) not null default 'no',
approved char(4) not null default 'yes',
cleared char(3) not null default 'no',
pci_id char(10) not null,
driver varchar(50) not null,
interface char(15) not null default 'not-specified',
bios char(15) not null default 'not-specified',
webcam_type varchar(100) CHARACTER SET utf8 not null,
webcam_works varchar(30) CHARACTER SET utf8 not null default 'not-specified',
sound_card_works varchar(30) CHARACTER SET utf8 not null default 'not-specified',
bluetooth_works varchar(30) CHARACTER SET utf8 not null default 'not-specified',
fingerprint_works varchar(30) CHARACTER SET utf8 not null default 'not-specified',
architecture char(15) not null default 'not-specified',
other_names text CHARACTER SET utf8 not null
)engine=innodb;
create table revisions (
id_rev INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
type varchar(50) not null,
subtype char(15) not null,
kernel varchar(50) not null,
vendor varchar(50) not null,
model varchar(100) not null,
created_by int not null,
updated_by int not null,
creation_date timestamp default CURRENT_TIMESTAMP,
update_date datetime not null,
compatibility char(15) not null,
description text CHARACTER SET utf8 not null,
distribution varchar(300) CHARACTER SET utf8 not null,
video_card_type varchar(100) CHARACTER SET utf8 not null,
video_card_works varchar(30) CHARACTER SET utf8 not null,
wifi_type varchar(100) CHARACTER SET utf8 not null,
wifi_works varchar(30) CHARACTER SET utf8 not null,
comm_year char(15) not null,
pci_id char(10) not null,
driver varchar(50) not null,
interface char(15) not null default 'not-specified',
bios char(15) not null default 'not-specified',
webcam_type varchar(100) CHARACTER SET utf8 not null,
webcam_works varchar(30) CHARACTER SET utf8 not null default 'not-specified',
sound_card_works varchar(30) CHARACTER SET utf8 not null default 'not-specified',
bluetooth_works varchar(30) CHARACTER SET utf8 not null default 'not-specified',
fingerprint_works varchar(30) CHARACTER SET utf8 not null default 'not-specified',
architecture char(15) not null default 'not-specified',
other_names text CHARACTER SET utf8 not null,
approved char(4) not null default 'yes',
cleared char(3) not null default 'no',
id_hard INT UNSIGNED NOT NULL
)engine=innodb;
create table hardware_users (
id_user int(11) UNSIGNED not null,
id_hard int(11) UNSIGNED not null,
index hard_indx(id_hard),
index user_indx(id_user),
foreign key hard_fky(id_hard) references hardware (id_hard),
foreign key user_fky(id_user) references regusers (id_user),
unique (id_user,id_hard)
)engine=innodb;
create table talk (
id_talk INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
created_by int not null,
creation_date timestamp default CURRENT_TIMESTAMP,
title varchar(100) CHARACTER SET utf8 not null,
message text CHARACTER SET utf8 not null,
id_hard INT UNSIGNED NOT NULL,
deleted char(4) not null default 'no'
)engine=innodb;
create table profile (
id_prof INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
created_by int not null,
creation_date timestamp default CURRENT_TIMESTAMP,
update_date datetime not null,
real_name varchar(100) CHARACTER SET utf8 not null,
where_you_are varchar(100) CHARACTER SET utf8 not null,
birth_date varchar(100) CHARACTER SET utf8 not null,
fav_distro varchar(100) CHARACTER SET utf8 not null,
projects text CHARACTER SET utf8 not null ,
publish_mail char(4) not null default 'no',
description text CHARACTER SET utf8 not null,
website varchar(100) CHARACTER SET utf8 not null,
send_notification char(4) not null default 'yes'
)engine=innodb;
create table issues (
id_issue INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
created_by int not null,
creation_date timestamp default CURRENT_TIMESTAMP,
update_date datetime not null,
topic char(35) CHARACTER SET utf8 not null,
title varchar(100) CHARACTER SET utf8 not null,
message text CHARACTER SET utf8 not null,
priority char(15) CHARACTER SET utf8 not null,
status char(15) CHARACTER SET utf8 not null,
notice text CHARACTER SET utf8 not null,
deleted char(4) not null default 'no'
)engine=innodb;
create table deletion (
id_del INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
created_by int not null,
creation_date timestamp default CURRENT_TIMESTAMP,
object char(15) not null,
message text CHARACTER SET utf8 not null,
id_hard INT UNSIGNED NOT NULL,
id_duplicate INT UNSIGNED NOT NULL
)engine=innodb;
create table params (
id_par INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
updating char(4) not null default 'no'
)engine=innodb;
insert into params (updating) values ('no');
create table news (
id_news INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
creation_date timestamp default CURRENT_TIMESTAMP,
title varchar(150) not null,
message text CHARACTER SET utf8 not null
)engine=innodb;
create table boxes (
id_box int not null auto_increment primary key,
creation_date timestamp default CURRENT_TIMESTAMP,
title varchar(150) not null,
message text CHARACTER SET utf8 not null
);
create table messages (
id_mes int not null auto_increment primary key,
created_by int not null,
creation_date timestamp default CURRENT_TIMESTAMP,
message text CHARACTER SET utf8 not null,
id_issue INT UNSIGNED NOT NULL,
deleted char(4) not null default 'no',
has_read char(4) not null default 'no'
);
create table history (
id_history int not null auto_increment primary key,
created_by int not null,
creation_date timestamp default CURRENT_TIMESTAMP,
type char(10) not null,
action char(15) not null,
id INT UNSIGNED NOT NULL,
message text CHARACTER SET utf8 not null,
gr char(15) not null
);
insert into reggroups (name) values ('moderator');
insert into reggroups (name) values ('admin');
create table wiki (
id_wiki INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
created_by int not null,
creation_date timestamp default CURRENT_TIMESTAMP,
update_date datetime not null,
title varchar(200) CHARACTER SET utf8 not null,
title_clean varchar(200) CHARACTER SET utf8 not null,
page text CHARACTER SET utf8 not null,
deleted char(3) not null default 'no',
blocked CHAR(3) NOT NULL default 'no',
is_main char(3) not null default 'no'
)engine=innodb;
create table wiki_revisions (
id_rev INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
created_by int not null,
creation_date timestamp default CURRENT_TIMESTAMP,
update_date datetime not null,
title varchar(200) CHARACTER SET utf8 not null,
title_clean varchar(200) CHARACTER SET utf8 not null,
page text CHARACTER SET utf8 not null,
id_wiki INT UNSIGNED NOT NULL
)engine=innodb;
create table wiki_users (
id_user int(11) UNSIGNED not null,
id_wiki int(11) UNSIGNED not null,
index wiki_indx(id_wiki),
index user_indx(id_user),
foreign key wiki_fky(id_wiki) references wiki (id_wiki),
foreign key user_fky(id_user) references regusers (id_user),
unique (id_user,id_wiki)
)engine=innodb;
create table wiki_talk (
id_talk INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
created_by int not null,
creation_date timestamp default CURRENT_TIMESTAMP,
title varchar(100) CHARACTER SET utf8 not null,
message text CHARACTER SET utf8 not null,
id_wiki INT UNSIGNED NOT NULL,
deleted char(4) not null default 'no'
)engine=innodb;
insert into wiki (title,title_clean,is_main) values ('Main Page','Main-Page','yes');
update wiki set update_date=creation_date where title='Main Page';
|
