alphdb_create.sql

CREATE DATABASE IF NOT EXISTS alph DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
USE alph;

CREATE TABLE `ACCOUNT` (
	`idaccount` int NOT NULL AUTO_INCREMENT,
	`status` bit NOT NULL,
	`hyperpower` bit NOT NULL,
	`email` varchar(254) NOT NULL UNIQUE,
	`username` varchar(36) NOT NULL UNIQUE,
	`password` varchar(60) NOT NULL,
	`code` char(100),
	`createddate` DATETIME NOT NULL,
	`editeddate` DATETIME,
	PRIMARY KEY (`idaccount`)
);

CREATE TABLE `TERMINAL_DIRECTORY` (
	`iddir` int NOT NULL AUTO_INCREMENT,
	`terminal` char(17) NOT NULL,
	`parent` int,
	`name` varchar(255) NOT NULL,
	`chmod` int(3) NOT NULL,
	`owner` int NOT NULL,
	`group` int NOT NULL,
	`createddate` DATETIME NOT NULL,
	`editeddate` DATETIME,
	PRIMARY KEY (`iddir`)
);

CREATE TABLE `TERMINAL_FILE` (
	`idfile` int NOT NULL AUTO_INCREMENT,
	`terminal` char(17) NOT NULL,
	`parent` int,
	`name` varchar(255),
	`data` TEXT,
	`chmod` int(3) NOT NULL,
	`owner` int NOT NULL,
	`group` int NOT NULL,
	`createddate` DATETIME NOT NULL,
	`editeddate` DATETIME,
	PRIMARY KEY (`idfile`)
);

CREATE TABLE `TERMINAL` (
	`mac` char(17) NOT NULL,
	`account` int NOT NULL,
	`localnetwork` char(17),
	PRIMARY KEY (`mac`)
);

CREATE TABLE `NETWORK` (
	`mac` char(17) NOT NULL,
	`ipv4` varchar(15) NOT NULL UNIQUE,
	`ipv6` varchar(45) NOT NULL UNIQUE,
	PRIMARY KEY (`mac`)
);

CREATE TABLE `PORT` (
	`network` char(17) NOT NULL,
	`port` int NOT NULL,
	`status` int NOT NULL,
	`ip` varchar(15) NOT NULL,
	`ipport` int NOT NULL,
	PRIMARY KEY (`network`,`port`)
);

CREATE TABLE `PRIVATEIP` (
	`network` char(17) NOT NULL,
	`terminal` char(17) NOT NULL,
	`ip` varchar(15) NOT NULL,
	PRIMARY KEY (`network`,`terminal`)
);

CREATE TABLE `TERMINAL_USER` (
	`idterminal_user` int NOT NULL AUTO_INCREMENT,
	`terminal` char(17) NOT NULL,
	`uid` int NOT NULL,
	`gid` int NOT NULL,
	`status` bit NOT NULL,
	`username` varchar(255) NOT NULL,
	`password` varchar(255) NOT NULL,
	PRIMARY KEY (`idterminal_user`)
);

CREATE TABLE `TERMINAL_GROUP` (
	`idterminal_group` int NOT NULL AUTO_INCREMENT,
	`terminal` char(17) NOT NULL,
	`gid` int NOT NULL,
	`status` bit NOT NULL,
	`groupname` varchar(255) NOT NULL,
	PRIMARY KEY (`idterminal_group`)
);

CREATE TABLE `TERMINAL_GROUP_LINK` (
	`terminal_user` int NOT NULL,
	`terminal_group` int NOT NULL,
	PRIMARY KEY (`terminal_user`,`terminal_group`)
);

CREATE TABLE `TERMINAL_USER_HISTORY` (
	`idhistory` int NOT NULL AUTO_INCREMENT,
	`terminal_user` int NOT NULL,
	`account` int NOT NULL,
	`status` bit NOT NULL,
	`command` TEXT NOT NULL,
	`date` DATETIME NOT NULL,
	PRIMARY KEY (`idhistory`)
);

CREATE TABLE `CONFIG` (
	`key` varchar(255) NOT NULL,
	`value` varchar(255) NOT NULL,
	PRIMARY KEY (`key`)
);

CREATE TABLE `ACCOUNT_GROUP_LINK` (
	`account` int NOT NULL,
	`groupid` int NOT NULL,
	PRIMARY KEY (`account`,`groupid`)
);

CREATE TABLE `REFERENTIAL` (
	`idreferential` int NOT NULL AUTO_INCREMENT,
	`type` bit NOT NULL,
	`code` varchar(255) NOT NULL UNIQUE,
	`category` int,
	`value` TEXT,
	PRIMARY KEY (`idreferential`)
);

CREATE TABLE `GROUP` (
	`idgroup` int NOT NULL AUTO_INCREMENT,
	`name` varchar(255) NOT NULL,
	PRIMARY KEY (`idgroup`)
);

CREATE TABLE `PERMISSION` (
	`code` int NOT NULL,
	`idgroup` int NOT NULL,
	`value` bit NOT NULL,
	PRIMARY KEY (`code`,`idgroup`)
);

ALTER TABLE `TERMINAL_DIRECTORY` ADD CONSTRAINT `TERMINAL_DIRECTORY_fk0` FOREIGN KEY (`terminal`) REFERENCES `TERMINAL`(`mac`);

ALTER TABLE `TERMINAL_DIRECTORY` ADD CONSTRAINT `TERMINAL_DIRECTORY_fk1` FOREIGN KEY (`parent`) REFERENCES `TERMINAL_DIRECTORY`(`iddir`);

ALTER TABLE `TERMINAL_DIRECTORY` ADD CONSTRAINT `TERMINAL_DIRECTORY_fk2` FOREIGN KEY (`owner`) REFERENCES `TERMINAL_USER`(`idterminal_user`);

ALTER TABLE `TERMINAL_DIRECTORY` ADD CONSTRAINT `TERMINAL_DIRECTORY_fk3` FOREIGN KEY (`group`) REFERENCES `TERMINAL_GROUP`(`idterminal_group`);

ALTER TABLE `TERMINAL_FILE` ADD CONSTRAINT `TERMINAL_FILE_fk0` FOREIGN KEY (`terminal`) REFERENCES `TERMINAL`(`mac`);

ALTER TABLE `TERMINAL_FILE` ADD CONSTRAINT `TERMINAL_FILE_fk1` FOREIGN KEY (`parent`) REFERENCES `TERMINAL_DIRECTORY`(`iddir`);

ALTER TABLE `TERMINAL_FILE` ADD CONSTRAINT `TERMINAL_FILE_fk2` FOREIGN KEY (`owner`) REFERENCES `TERMINAL_USER`(`idterminal_user`);

ALTER TABLE `TERMINAL_FILE` ADD CONSTRAINT `TERMINAL_FILE_fk3` FOREIGN KEY (`group`) REFERENCES `TERMINAL_GROUP`(`idterminal_group`);

ALTER TABLE `TERMINAL` ADD CONSTRAINT `TERMINAL_fk0` FOREIGN KEY (`account`) REFERENCES `ACCOUNT`(`idaccount`);

ALTER TABLE `TERMINAL` ADD CONSTRAINT `TERMINAL_fk1` FOREIGN KEY (`localnetwork`) REFERENCES `NETWORK`(`mac`);

ALTER TABLE `PORT` ADD CONSTRAINT `PORT_fk0` FOREIGN KEY (`network`) REFERENCES `NETWORK`(`mac`);

ALTER TABLE `PRIVATEIP` ADD CONSTRAINT `PRIVATEIP_fk0` FOREIGN KEY (`network`) REFERENCES `NETWORK`(`mac`);

ALTER TABLE `PRIVATEIP` ADD CONSTRAINT `PRIVATEIP_fk1` FOREIGN KEY (`terminal`) REFERENCES `TERMINAL`(`mac`);

ALTER TABLE `TERMINAL_USER` ADD CONSTRAINT `TERMINAL_USER_fk0` FOREIGN KEY (`terminal`) REFERENCES `TERMINAL`(`mac`);

ALTER TABLE `TERMINAL_USER` ADD CONSTRAINT `TERMINAL_USER_fk1` FOREIGN KEY (`gid`) REFERENCES `TERMINAL_GROUP`(`idterminal_group`);

ALTER TABLE `TERMINAL_GROUP` ADD CONSTRAINT `TERMINAL_GROUP_fk0` FOREIGN KEY (`terminal`) REFERENCES `TERMINAL`(`mac`);

ALTER TABLE `TERMINAL_GROUP_LINK` ADD CONSTRAINT `TERMINAL_GROUP_LINK_fk0` FOREIGN KEY (`terminal_user`) REFERENCES `TERMINAL_USER`(`idterminal_user`);

ALTER TABLE `TERMINAL_GROUP_LINK` ADD CONSTRAINT `TERMINAL_GROUP_LINK_fk1` FOREIGN KEY (`terminal_group`) REFERENCES `TERMINAL_GROUP`(`idterminal_group`);

ALTER TABLE `TERMINAL_USER_HISTORY` ADD CONSTRAINT `TERMINAL_USER_HISTORY_fk0` FOREIGN KEY (`terminal_user`) REFERENCES `TERMINAL_USER`(`idterminal_user`);

ALTER TABLE `TERMINAL_USER_HISTORY` ADD CONSTRAINT `TERMINAL_USER_HISTORY_fk1` FOREIGN KEY (`account`) REFERENCES `ACCOUNT`(`idaccount`);

ALTER TABLE `ACCOUNT_GROUP_LINK` ADD CONSTRAINT `ACCOUNT_GROUP_LINK_fk0` FOREIGN KEY (`account`) REFERENCES `ACCOUNT`(`idaccount`);

ALTER TABLE `ACCOUNT_GROUP_LINK` ADD CONSTRAINT `ACCOUNT_GROUP_LINK_fk1` FOREIGN KEY (`groupid`) REFERENCES `GROUP`(`idgroup`);

ALTER TABLE `REFERENTIAL` ADD CONSTRAINT `REFERENTIAL_fk0` FOREIGN KEY (`category`) REFERENCES `REFERENTIAL`(`idreferential`);

ALTER TABLE `PERMISSION` ADD CONSTRAINT `PERMISSION_fk0` FOREIGN KEY (`code`) REFERENCES `REFERENTIAL`(`idreferential`);

ALTER TABLE `PERMISSION` ADD CONSTRAINT `PERMISSION_fk1` FOREIGN KEY (`idgroup`) REFERENCES `GROUP`(`idgroup`);