From 232aa1924c8c0f10d87b210b46c9f061af5c844c Mon Sep 17 00:00:00 2001 From: Antonio Gallo Date: Sun, 17 Oct 2010 13:29:57 +0000 Subject: added files --- h-source/tables.sql | 220 ++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 220 insertions(+) create mode 100644 h-source/tables.sql (limited to 'h-source/tables.sql') diff --git a/h-source/tables.sql b/h-source/tables.sql new file mode 100644 index 0000000..c68b26b --- /dev/null +++ b/h-source/tables.sql @@ -0,0 +1,220 @@ +# 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 program 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. +# +# This program 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 this program. If not, see . + +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', + 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', + pci_id char(10) not null, + driver varchar(50) not null, + interface char(15) not null default 'not-specified' +)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', + 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 +)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(10) not null, + id INT UNSIGNED NOT NULL, + message text CHARACTER SET utf8 not null +); + +insert into reggroups (name) values ('moderator'); -- cgit v1.2.3