Add E-R model and SQL bootstrap scripts

1 files changed, 36 insertions, 0 deletions

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;