You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
92 lines
2.6 KiB
92 lines
2.6 KiB
CREATE TABLE `ACCOUNTS` ( |
|
`ID` integer PRIMARY KEY NOT NULL AUTO_INCREMENT, |
|
`NAME` varchar(40), |
|
`CREATED` datetime NOT NULL, |
|
`UPDATED` datetime |
|
); |
|
|
|
CREATE TABLE `TRANSACTION` ( |
|
`ID` char(64) PRIMARY KEY NOT NULL, |
|
`TDATE` date NOT NULL, |
|
`ACCOUNT_ID` integer NOT NULL, |
|
`MEMO` varchar(64) NOT NULL, |
|
`COUNTRY` char(2), |
|
`OUTFLOW` decimal(20,2), |
|
`INFLOW` decimal(12,2), |
|
`OWNER_ID` integer, |
|
`INSTALLMENT_NR` integer, |
|
`INSTALLMENT_TT` integer, |
|
`CREATED` datetime NOT NULL, |
|
`UPDATED` datetime |
|
); |
|
|
|
CREATE TABLE `PAYEE` ( |
|
`ID` integer PRIMARY KEY NOT NULL AUTO_INCREMENT, |
|
`NAME` varchar(40), |
|
`CREATED` datetime NOT NULL, |
|
`UPDATED` datetime |
|
); |
|
|
|
CREATE TABLE `OWNER` ( |
|
`ID` integer PRIMARY KEY NOT NULL AUTO_INCREMENT, |
|
`NAME` varchar(40), |
|
`CREATED` datetime NOT NULL, |
|
`UPDATED` datetime |
|
); |
|
|
|
CREATE TABLE `CATEGORY` ( |
|
`ID` integer PRIMARY KEY NOT NULL AUTO_INCREMENT, |
|
`NAME` varchar(40), |
|
`CREATED` datetime NOT NULL, |
|
`UPDATED` datetime |
|
); |
|
|
|
CREATE TABLE `SUBCATEGORY` ( |
|
`ID` integer PRIMARY KEY NOT NULL AUTO_INCREMENT, |
|
`CATEGORY_ID` integer, |
|
`NAME` varchar(40), |
|
`CREATED` datetime NOT NULL, |
|
`UPDATED` datetime |
|
); |
|
|
|
CREATE TABLE `CATEGORIZED_TRANSACTIONS` ( |
|
`ID` integer PRIMARY KEY NOT NULL AUTO_INCREMENT, |
|
`ACCOUNT_ID` integer, |
|
`TRANSACTION_ID` char(64), |
|
`PAYEE_ID` integer, |
|
`CATEGORY_ID` integer, |
|
`SUBCATEGORY_ID` integer, |
|
`CREATED` datetime NOT NULL, |
|
`UPDATED` datetime |
|
); |
|
|
|
DROP TABLE IF EXISTS `RULES`; |
|
|
|
CREATE TABLE `RULES` ( |
|
`ID` integer PRIMARY KEY NOT NULL AUTO_INCREMENT, |
|
`PATTERN` varchar(50) NOT NULL, |
|
`CATEGORY_ID` integer, |
|
`SUBCATEGORY_ID` integer, |
|
`CREATED` datetime NOT NULL, |
|
`UPDATED` datetime |
|
); |
|
|
|
ALTER TABLE `CATEGORY` ADD FOREIGN KEY (`ID`) REFERENCES `RULES` (`CATEGORY_ID`); |
|
|
|
ALTER TABLE `SUBCATEGORY` ADD FOREIGN KEY (`ID`) REFERENCES `RULES` (`SUBCATEGORY_ID`); |
|
|
|
ALTER TABLE `ACCOUNTS` ADD FOREIGN KEY (`ID`) REFERENCES `TRANSACTION` (`ACCOUNT_ID`); |
|
|
|
ALTER TABLE `OWNER` ADD FOREIGN KEY (`ID`) REFERENCES `TRANSACTION` (`OWNER_ID`); |
|
|
|
ALTER TABLE `CATEGORY` ADD FOREIGN KEY (`ID`) REFERENCES `SUBCATEGORY` (`CATEGORY_ID`); |
|
|
|
ALTER TABLE `TRANSACTION` ADD FOREIGN KEY (`ID`) REFERENCES `CATEGORIZED_TRANSACTIONS` (`TRANSACTION_ID`); |
|
|
|
ALTER TABLE `CATEGORY` ADD FOREIGN KEY (`ID`) REFERENCES `CATEGORIZED_TRANSACTIONS` (`CATEGORY_ID`); |
|
|
|
ALTER TABLE `SUBCATEGORY` ADD FOREIGN KEY (`ID`) REFERENCES `CATEGORIZED_TRANSACTIONS` (`SUBCATEGORY_ID`); |
|
|
|
ALTER TABLE `PAYEE` ADD FOREIGN KEY (`ID`) REFERENCES `CATEGORIZED_TRANSACTIONS` (`PAYEE_ID`); |
|
|
|
ALTER TABLE `ACCOUNTS` ADD FOREIGN KEY (`ID`) REFERENCES `CATEGORIZED_TRANSACTIONS` (`ACCOUNT_ID`);
|
|
|