-
Notifications
You must be signed in to change notification settings - Fork 4
/
Copy pathsql.sql
16 lines (15 loc) · 7 KB
/
sql.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE Roles (id INT AUTO_INCREMENT NOT NULL, user_id INT DEFAULT NULL, entity VARCHAR(50) NOT NULL, role VARCHAR(1) NOT NULL, INDEX IDX_77FF01C3A76ED395 (user_id), UNIQUE INDEX role_key_constraint (entity, role, user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE User (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, salt VARCHAR(255) NOT NULL, isActive TINYINT(1) DEFAULT '1' NOT NULL, isAdmin TINYINT(1) DEFAULT '0' NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE Company (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, address VARCHAR(255) DEFAULT NULL, district VARCHAR(50) DEFAULT NULL, city VARCHAR(50) DEFAULT NULL, email VARCHAR(100) DEFAULT NULL, website VARCHAR(100) DEFAULT NULL, phone VARCHAR(50) DEFAULT NULL, comments LONGTEXT DEFAULT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE Contact (id INT AUTO_INCREMENT NOT NULL, company_id INT DEFAULT NULL, name VARCHAR(255) NOT NULL, email VARCHAR(100) DEFAULT NULL, phone VARCHAR(50) DEFAULT NULL, cellphone VARCHAR(50) DEFAULT NULL, address VARCHAR(255) DEFAULT NULL, district VARCHAR(50) DEFAULT NULL, city VARCHAR(255) DEFAULT NULL, comments LONGTEXT DEFAULT NULL, INDEX IDX_83DFDFA4979B1AD6 (company_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE Accounts (id INT AUTO_INCREMENT NOT NULL, company_id INT DEFAULT NULL, type SMALLINT NOT NULL COMMENT '{0: contas a receber, 1: contas a pagar}', status SMALLINT NOT NULL COMMENT '{0: pago, 1: pendente de pagamento}', description VARCHAR(255) NOT NULL, createdAt DATETIME NOT NULL, paidAt DATE DEFAULT NULL, dueDate DATE NOT NULL, value NUMERIC(10, 2) NOT NULL, INDEX IDX_33BEFCFA979B1AD6 (company_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE CashFlowItem (id INT AUTO_INCREMENT NOT NULL, company_id INT DEFAULT NULL, type SMALLINT NOT NULL COMMENT '{0: entrada, 1: saida}', status SMALLINT NOT NULL COMMENT '{0: pago, 1: pendente de pagamento}', description VARCHAR(255) NOT NULL, createdAt DATETIME NOT NULL, paidAt DATE DEFAULT NULL, dueDate DATE NOT NULL, value NUMERIC(10, 2) NOT NULL, INDEX IDX_D86ECE27979B1AD6 (company_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
ALTER TABLE Roles ADD CONSTRAINT FK_77FF01C3A76ED395 FOREIGN KEY (user_id) REFERENCES User (id);
ALTER TABLE Contact ADD CONSTRAINT FK_83DFDFA4979B1AD6 FOREIGN KEY (company_id) REFERENCES Company (id);
ALTER TABLE Accounts ADD CONSTRAINT FK_33BEFCFA979B1AD6 FOREIGN KEY (company_id) REFERENCES Company (id);
ALTER TABLE CashFlowItem ADD CONSTRAINT FK_D86ECE27979B1AD6 FOREIGN KEY (company_id) REFERENCES Company (id);
##VIEW
CREATE VIEW `cashflow` AS
select `c`.`id` AS `id`,`c`.`type` AS `type`,`c`.`status` AS `status`,`c`.`description` AS `description`,`c`.`createdAt` AS `createdAt`,`c`.`dueDate` AS `dueDate`,`c`.`paidAt` AS `paidAt`,`c`.`value` AS `value`,`c`.`company_id` AS `company_id`,'cashflowitem' AS `accountType` from `Cashflowitem` `c`
union
select `a`.`id` AS `id`,`a`.`type` AS `type`,`a`.`status` AS `status`,`a`.`description` AS `description`,`a`.`createdAt` AS `createdAt`,`a`.`dueDate` AS `dueDate`,`a`.`paidAt` AS `paidAt`,`a`.`value` AS `value`,`a`.`company_id` AS `company_id`,'accounts' AS `accountType` from `Accounts` `a`;