From afe377a9b10b5c1b2badc74d4b9b636e7c4d0cbf Mon Sep 17 00:00:00 2001 From: Pacien TRAN-GIRARD Date: Tue, 27 Jan 2015 18:01:45 +0100 Subject: Add E-R model and SQL bootstrap scripts --- res/sql/tables/orcl_add_constraints_defaults.sql | 36 ++++++++ res/sql/tables/orcl_ddl.sql | 113 +++++++++++++++++++++++ res/sql/tables/pg_add_constraints_defaults.sql | 26 ++++++ res/sql/tables/pg_ddl.sql | 113 +++++++++++++++++++++++ 4 files changed, 288 insertions(+) create mode 100644 res/sql/tables/orcl_add_constraints_defaults.sql create mode 100644 res/sql/tables/orcl_ddl.sql create mode 100644 res/sql/tables/pg_add_constraints_defaults.sql create mode 100644 res/sql/tables/pg_ddl.sql (limited to 'res/sql/tables') diff --git a/res/sql/tables/orcl_add_constraints_defaults.sql b/res/sql/tables/orcl_add_constraints_defaults.sql new file mode 100644 index 0000000..4fd386c --- /dev/null +++ b/res/sql/tables/orcl_add_constraints_defaults.sql @@ -0,0 +1,36 @@ +-- constraints + +ALTER TABLE USERS +ADD CONSTRAINT username_unique UNIQUE (USERNAME); + + +-- defaults + +-- oracle can not use user defined functions (gen_uuid()) as default values :c + +ALTER TABLE USERS +MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid()) +, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})' +, '\1-\2-\3-\4-\5'); + +ALTER TABLE BIDS +MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid()) +, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})' +, '\1-\2-\3-\4-\5'); + +ALTER TABLE CHARGES +MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid()) +, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})' +, '\1-\2-\3-\4-\5'); + +ALTER TABLE ITEMS +MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid()) +, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})' +, '\1-\2-\3-\4-\5'); + +ALTER TABLE TRANSACTIONS +MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid()) +, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})' +, '\1-\2-\3-\4-\5'); + +COMMIT; diff --git a/res/sql/tables/orcl_ddl.sql b/res/sql/tables/orcl_ddl.sql new file mode 100644 index 0000000..78afd5c --- /dev/null +++ b/res/sql/tables/orcl_ddl.sql @@ -0,0 +1,113 @@ +-- Generated by Oracle SQL Developer Data Modeler 4.0.2.840 +-- at: 2015-01-25 15:20:45 CET +-- site: Oracle Database 11g +-- type: Oracle Database 11g + + + + +CREATE TABLE bids +( + uuid VARCHAR(36) NOT NULL, + item_uuid VARCHAR(36) NOT NULL, + user_uuid VARCHAR(36) NOT NULL, + bid_date TIMESTAMP WITH TIME ZONE NOT NULL, + offer NUMERIC(8, 2) NOT NULL +); +ALTER TABLE bids ADD CONSTRAINT bids_PK PRIMARY KEY (uuid); + +CREATE TABLE charges +( + uuid VARCHAR(36) NOT NULL, + start_date TIMESTAMP WITH TIME ZONE NOT NULL, + end_date TIMESTAMP WITH TIME ZONE NOT NULL, + fee NUMERIC(8, 2) NOT NULL, + rate NUMERIC(8, 2) NOT NULL +); +ALTER TABLE charges ADD CONSTRAINT charges_PK PRIMARY KEY (uuid); + +CREATE TABLE items +( + uuid VARCHAR(36) NOT NULL, + user_uuid VARCHAR(36) NOT NULL, + start_date TIMESTAMP WITH TIME ZONE NOT NULL, + end_date TIMESTAMP WITH TIME ZONE NOT NULL, + item_name VARCHAR(20) NOT NULL, + short_desc VARCHAR(30) NOT NULL, + long_desc CLOB NOT NULL, + initial_price NUMERIC(8, 2) NOT NULL +); +ALTER TABLE items ADD CONSTRAINT items_PK PRIMARY KEY (uuid); + +CREATE TABLE transactions +( + uuid VARCHAR(36) NOT NULL, + user_uuid VARCHAR(36) NOT NULL, + transaction_date TIMESTAMP WITH TIME ZONE NOT NULL, + amount NUMERIC(8, 2) NOT NULL, + label VARCHAR(255) NOT NULL +); +ALTER TABLE transactions ADD CONSTRAINT transactions_PK PRIMARY KEY (uuid); + +CREATE TABLE users +( + uuid VARCHAR(36) NOT NULL, + username VARCHAR(20) NOT NULL, + email VARCHAR(255) NOT NULL, + user_password VARCHAR(255) NOT NULL, + creation_date TIMESTAMP WITH TIME ZONE NOT NULL, + first_name VARCHAR(255) NOT NULL, + last_name VARCHAR(255) NOT NULL, + country_code VARCHAR(2) NOT NULL, + postal_code VARCHAR(20) NOT NULL, + address VARCHAR(255) NOT NULL, + phone VARCHAR(20) NOT NULL, + birthdate TIMESTAMP WITH TIME ZONE NOT NULL +); +ALTER TABLE users ADD CONSTRAINT users_PK PRIMARY KEY (uuid); + +ALTER TABLE bids ADD CONSTRAINT bids_items_FK FOREIGN KEY (item_uuid) REFERENCES items (uuid); + +ALTER TABLE bids ADD CONSTRAINT bids_users_FK FOREIGN KEY (user_uuid) REFERENCES users (uuid); + +ALTER TABLE items ADD CONSTRAINT items_users_FK FOREIGN KEY (user_uuid) REFERENCES users (uuid); + +ALTER TABLE transactions ADD CONSTRAINT transactions_users_FK FOREIGN KEY (user_uuid) REFERENCES users (uuid); + + +-- Oracle SQL Developer Data Modeler Summary Report: +-- +-- CREATE TABLE 5 +-- CREATE INDEX 0 +-- ALTER TABLE 9 +-- CREATE VIEW 0 +-- CREATE PACKAGE 0 +-- CREATE PACKAGE BODY 0 +-- CREATE PROCEDURE 0 +-- CREATE FUNCTION 0 +-- CREATE TRIGGER 0 +-- ALTER TRIGGER 0 +-- CREATE COLLECTION TYPE 0 +-- CREATE STRUCTURED TYPE 0 +-- CREATE STRUCTURED TYPE BODY 0 +-- CREATE CLUSTER 0 +-- CREATE CONTEXT 0 +-- CREATE DATABASE 0 +-- CREATE DIMENSION 0 +-- CREATE DIRECTORY 0 +-- CREATE DISK GROUP 0 +-- CREATE ROLE 0 +-- CREATE ROLLBACK SEGMENT 0 +-- CREATE SEQUENCE 0 +-- CREATE MATERIALIZED VIEW 0 +-- CREATE SYNONYM 0 +-- CREATE TABLESPACE 0 +-- CREATE USER 0 +-- +-- DROP TABLESPACE 0 +-- DROP DATABASE 0 +-- +-- REDACTION POLICY 0 +-- +-- ERRORS 0 +-- WARNINGS 0 diff --git a/res/sql/tables/pg_add_constraints_defaults.sql b/res/sql/tables/pg_add_constraints_defaults.sql new file mode 100644 index 0000000..df9250d --- /dev/null +++ b/res/sql/tables/pg_add_constraints_defaults.sql @@ -0,0 +1,26 @@ +-- constraints + +ALTER TABLE USERS +ADD CONSTRAINT username_unique UNIQUE (USERNAME); + + +-- defaults + +CREATE EXTENSION "uuid-ossp"; + +ALTER TABLE USERS +ALTER COLUMN UUID SET DEFAULT gen_uuid(); + +ALTER TABLE BIDS +ALTER COLUMN UUID SET DEFAULT gen_uuid(); + +ALTER TABLE CHARGES +ALTER COLUMN UUID SET DEFAULT gen_uuid(); + +ALTER TABLE ITEMS +ALTER COLUMN UUID SET DEFAULT gen_uuid(); + +ALTER TABLE TRANSACTIONS +ALTER COLUMN UUID SET DEFAULT gen_uuid(); + +COMMIT; diff --git a/res/sql/tables/pg_ddl.sql b/res/sql/tables/pg_ddl.sql new file mode 100644 index 0000000..0d8b17c --- /dev/null +++ b/res/sql/tables/pg_ddl.sql @@ -0,0 +1,113 @@ +-- Generated by Oracle SQL Developer Data Modeler 4.0.2.840 +-- at: 2015-01-25 15:20:45 CET +-- site: Oracle Database 11g +-- type: Oracle Database 11g + + + + +CREATE TABLE bids +( + uuid VARCHAR(36) NOT NULL, + item_uuid VARCHAR(36) NOT NULL, + user_uuid VARCHAR(36) NOT NULL, + bid_date TIMESTAMP WITH TIME ZONE NOT NULL, + offer NUMERIC(8, 2) NOT NULL +); +ALTER TABLE bids ADD CONSTRAINT bids_PK PRIMARY KEY (uuid); + +CREATE TABLE charges +( + uuid VARCHAR(36) NOT NULL, + start_date TIMESTAMP WITH TIME ZONE NOT NULL, + end_date TIMESTAMP WITH TIME ZONE NOT NULL, + fee NUMERIC(8, 2) NOT NULL, + rate NUMERIC(8, 2) NOT NULL +); +ALTER TABLE charges ADD CONSTRAINT charges_PK PRIMARY KEY (uuid); + +CREATE TABLE items +( + uuid VARCHAR(36) NOT NULL, + user_uuid VARCHAR(36) NOT NULL, + start_date TIMESTAMP WITH TIME ZONE NOT NULL, + end_date TIMESTAMP WITH TIME ZONE NOT NULL, + item_name VARCHAR(20) NOT NULL, + short_desc VARCHAR(30) NOT NULL, + long_desc TEXT NOT NULL, + initial_price NUMERIC(8, 2) NOT NULL +); +ALTER TABLE items ADD CONSTRAINT items_PK PRIMARY KEY (uuid); + +CREATE TABLE transactions +( + uuid VARCHAR(36) NOT NULL, + user_uuid VARCHAR(36) NOT NULL, + transaction_date TIMESTAMP WITH TIME ZONE NOT NULL, + amount NUMERIC(8, 2) NOT NULL, + label VARCHAR(255) NOT NULL +); +ALTER TABLE transactions ADD CONSTRAINT transactions_PK PRIMARY KEY (uuid); + +CREATE TABLE users +( + uuid VARCHAR(36) NOT NULL, + username VARCHAR(20) NOT NULL, + email VARCHAR(255) NOT NULL, + user_password VARCHAR(255) NOT NULL, + creation_date TIMESTAMP WITH TIME ZONE NOT NULL, + first_name VARCHAR(255) NOT NULL, + last_name VARCHAR(255) NOT NULL, + country_code VARCHAR(2) NOT NULL, + postal_code VARCHAR(20) NOT NULL, + address VARCHAR(255) NOT NULL, + phone VARCHAR(20) NOT NULL, + birthdate TIMESTAMP WITH TIME ZONE NOT NULL +); +ALTER TABLE users ADD CONSTRAINT users_PK PRIMARY KEY (uuid); + +ALTER TABLE bids ADD CONSTRAINT bids_items_FK FOREIGN KEY (item_uuid) REFERENCES items (uuid); + +ALTER TABLE bids ADD CONSTRAINT bids_users_FK FOREIGN KEY (user_uuid) REFERENCES users (uuid); + +ALTER TABLE items ADD CONSTRAINT items_users_FK FOREIGN KEY (user_uuid) REFERENCES users (uuid); + +ALTER TABLE transactions ADD CONSTRAINT transactions_users_FK FOREIGN KEY (user_uuid) REFERENCES users (uuid); + + +-- Oracle SQL Developer Data Modeler Summary Report: +-- +-- CREATE TABLE 5 +-- CREATE INDEX 0 +-- ALTER TABLE 9 +-- CREATE VIEW 0 +-- CREATE PACKAGE 0 +-- CREATE PACKAGE BODY 0 +-- CREATE PROCEDURE 0 +-- CREATE FUNCTION 0 +-- CREATE TRIGGER 0 +-- ALTER TRIGGER 0 +-- CREATE COLLECTION TYPE 0 +-- CREATE STRUCTURED TYPE 0 +-- CREATE STRUCTURED TYPE BODY 0 +-- CREATE CLUSTER 0 +-- CREATE CONTEXT 0 +-- CREATE DATABASE 0 +-- CREATE DIMENSION 0 +-- CREATE DIRECTORY 0 +-- CREATE DISK GROUP 0 +-- CREATE ROLE 0 +-- CREATE ROLLBACK SEGMENT 0 +-- CREATE SEQUENCE 0 +-- CREATE MATERIALIZED VIEW 0 +-- CREATE SYNONYM 0 +-- CREATE TABLESPACE 0 +-- CREATE USER 0 +-- +-- DROP TABLESPACE 0 +-- DROP DATABASE 0 +-- +-- REDACTION POLICY 0 +-- +-- ERRORS 0 +-- WARNINGS 0 -- cgit v1.2.3