path: root/tabelle.txt

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');