Differences

This shows you the differences between two versions of the page.

--- jvx:server:storage:insteadof [2018/02/07 08:14]
admin created
+++ jvx:server:storage:insteadof [2018/02/07 08:15]
admin
@@ Line 236: / Line 236: @@
 You'll find the implementation of SimpleTestLauncher [[jvx:code_snippets#test_ui_with_simple_junit_test|here]].
+The database objects (Oracle):
+<file sql create.sql>
+CREATE TABLE CONTRACT
+(
+  id    NUMBER(16) NOT NULL,
+  title VARCHAR2(1000),
+  state NUMBER(1) DEFAULT 1
+)
+;
+COMMENT ON column CONTRACT.id
+  IS 'PK';
+COMMENT ON column CONTRACT.title
+  IS 'Contract title';
+COMMENT ON column CONTRACT.state
+  IS 'Contract state';
+ALTER TABLE CONTRACT
+  add constraint CONT_PK primary key (ID);
+CREATE TABLE ACTIVITY
+(
+  id                  NUMBER(16) NOT NULL,
+  cont_id             NUMBER(16),
+  name                VARCHAR2(1000),
+  cost                NUMBER(10,2),
+  valid_from          DATE,
+  valid_to            DATE,
+  responsible_user_id NUMBER(16),
+  validated           CHAR(1) DEFAULT 'Y'
+)
+;
+COMMENT ON column ACTIVITY.id
+  IS 'PK';
+COMMENT ON column ACTIVITY.cont_id
+  IS 'Contract FK';
+COMMENT ON column ACTIVITY.name
+  IS 'Activity name';
+COMMENT ON column ACTIVITY.cost
+  IS 'Activity costs';
+COMMENT ON column ACTIVITY.valid_from
+  IS 'Valid from';
+COMMENT ON column ACTIVITY.valid_to
+  IS 'Valid to';
+COMMENT ON column ACTIVITY.responsible_user_id
+  IS 'Responsible User FK';
+COMMENT ON column ACTIVITY.validated
+  IS 'Whether the activity is valid';
+ALTER TABLE ACTIVITY
+  add constraint ACTI_PK primary key (ID);
+ALTER TABLE ACTIVITY
+  add constraint ACTI_CONT_FK foreign key (CONT_ID)
+  references CONTRACT (ID);
+ALTER TABLE ACTIVITY
+  add constraint ACTI_RESP_USER_FK foreign key (RESPONSIBLE_USER_ID)
+  references USERS (ID);
+CREATE sequence SEQ_ACTIVITY_ID
+minvalue 1
+maxvalue 999999999999999999999999999
+START WITH 41
+increment BY 1
+cache 20
+ORDER;
+CREATE sequence SEQ_CONTRACT_ID
+minvalue 1
+maxvalue 999999999999999999999999999
+START WITH 21
+increment BY 1
+cache 20
+ORDER;
+CREATE OR REPLACE force VIEW v_activities AS
+SELECT a.id
+      ,a.cont_id
+      ,c.title cont_title
+      ,a.name
+      ,a.cost
+      ,a.valid_from
+      ,a.valid_to
+  FROM activity a
+      ,contract c
+ WHERE a.cont_id = c.id;
+CREATE OR REPLACE TRIGGER TR_ACTIVITY_BR_IU
+  before INSERT OR UPDATE ON activity
+  FOR each ROW
+BEGIN
+  IF (:NEW.id IS NULL) THEN
+    SELECT seq_activity_id.NEXTVAL INTO :NEW.id FROM dual;
+  END IF;
+  IF (:NEW.validated = 'N') THEN
+    IF (:NEW.valid_from IS NULL) THEN
+      raise_application_error(-20000, 'Valid from can''t be null!');
+    END IF;
+  END IF;
+END TR_ACTIVITY_BR_IU;
+/
+CREATE OR REPLACE TRIGGER TR_CONTRACT_BR_IU
+  before INSERT OR UPDATE ON contract
+  FOR each ROW
+BEGIN
+  IF (:NEW.id IS NULL) THEN
+    SELECT seq_contract_id.NEXTVAL INTO :NEW.id FROM dual;
+  END IF;
+  IF (:NEW.state = 4) THEN
+    UPDATE activity
+       SET validated = 'N'
+     WHERE cont_id = id
+       AND validated = 'Y';
+  END IF;
+END TR_CONTRACT_BR_IU;
+/
+prompt Disabling triggers FOR CONTRACT...
+ALTER TABLE CONTRACT disable ALL triggers;
+prompt Disabling triggers FOR ACTIVITY...
+ALTER TABLE ACTIVITY disable ALL triggers;
+prompt Disabling foreign key constraints FOR ACTIVITY...
+ALTER TABLE ACTIVITY disable constraint ACTI_CONT_FK;
+ALTER TABLE ACTIVITY disable constraint ACTI_RESP_USER_FK;
+prompt Loading CONTRACT...
+INSERT INTO CONTRACT (id, title, state)
+VALUES (1, 'Air', 1);
+COMMIT;
+prompt 1 records loaded
+prompt Loading ACTIVITY...
+INSERT INTO ACTIVITY (id, cont_id, name, cost, valid_from, valid_to, responsible_user_id, validated)
+VALUES (1, 1, 'Part 1', .2, NULL, NULL, NULL, 'Y');
+COMMIT;
+prompt 1 records loaded
+prompt Enabling foreign key constraints FOR ACTIVITY...
+ALTER TABLE ACTIVITY enable constraint ACTI_CONT_FK;
+ALTER TABLE ACTIVITY enable constraint ACTI_RESP_USER_FK;
+prompt Enabling triggers FOR CONTRACT...
+ALTER TABLE CONTRACT enable ALL triggers;
+prompt Enabling triggers FOR ACTIVITY...
+ALTER TABLE ACTIVITY enable ALL triggers;
+</file>