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 ++++++++++++++++++++++++
 1 file changed, 36 insertions(+)
 create mode 100644 res/sql/tables/orcl_add_constraints_defaults.sql

(limited to 'res/sql/tables/orcl_add_constraints_defaults.sql')

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;
-- 
cgit v1.2.3