DB Failure Handling

[ryantaplin]

The database failure handling is not that great.

Would be better to do some additional logging and atleast return something more presentable on GUI when it fails due to bad URL or bad data.

[ryantaplin]

Also to note: is it possible to handle a DB update and prevent the stack from falling over

If i deploy a new version of the application; it will apply db script updates while old versions of the application are still running and using the DB.

e.g. update table with new field; the insert SQL command will most likely fail (especially if value specified has constraint of NOT NULL).

[ryantaplin]

After brief discussion with theslin last week. I need to work out database view editioning scripts for SQL and also java implementation to give the application the ability to understand what version of the view it is to be using at any one time.

https://docs.oracle.com/database/121/SQLRF/statements_8004.htm#SQLRF01504
https://www.w3schools.com/sql/sql_view.asp

[ryantaplin]

Can't use view editioning under MySQL. Consider changing database to ORACLE 11 + to give me the ability to edition views and create a robust distributed updating system for my application.

Currently will not be able to do so.

CREATE OR REPLACE EDITIONING VIEW StaffView AS
SELECT * FROM staff

[tjheslin1]

Here's some Oracle databases you can run in docker:

https://hub.docker.com/r/sath89/oracle-12c/
https://hub.docker.com/r/sath89/oracle-xe-11g/

For the 12c container. This is the jdbc url: "jdbc:oracle:thin:system/oracle@localhost:1521:xe"

[ryantaplin]

Thanks Tom; I've just decided to start downloading oracle into docker now... My potato internet will take years to download it.

I'll do some reading in the mean time; maybe it will be finished by tomorrow. 🥔

[ryantaplin]

Todo

• Revise sql scripts for oracle (no auto-increment - instead sequence trigger)

• Create users who can own editions

• Create editioning views

• Other oracle stuff (triggers, altering tables etc).

• Potentially apply to application (not worried about this at this stage); however it will require updating long term to run the scripts automatically (now without 'CREATE TABLE IF EXISTS').

CREATE TABLE staff (
  id              INT NOT NULL,
  title           VARCHAR(10) NOT NULL,
  firstname       VARCHAR(50),
  surname         VARCHAR(50),
  PRIMARY KEY     (id)
);

CREATE SEQUENCE seq_staffid
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;

[ryantaplin]

Revised scripts under commit a5152ed
https://www.techonthenet.com/oracle/schemas/create_schema.php

Note: Need to ensure that seq.NextVal is called in implementation when inserting into table to ensure unique auto generated id is populated.

INSERT INTO staff
(staff_id, title, firstname, surname)
VALUES
(seq_staff_id.NEXTVAL, 'a', 'a', 'a');

Qs

• What is the point of tablespace? The separation of physical & logical data? Is it necessary?

[ryantaplin]

Revised user permissions and added statements to create editioning views under commit 3cfcf74
https://docs.oracle.com/cloud/latest/db112/SQLRF/statements_5009.htm#SQLRF20017

Qs

• What is best practice around making / dropping views? Do I keep them forever.

• Should I be dropping the whole edition or just the view? I am making an assumption that you create the edition based on release version (i.e edition 200), when you release version 201 with edition 201 you will still be using edition 200 during the migration. When you release version 202 you can then drop edition 200 as you will be sure that nobody is using it.

[ryantaplin]

https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_editions.htm#ADFNS99921
Good to know queries:

• SELECT EDITION_NAME FROM ALL_EDITIONS; // view all editions (for dropping unused ones).
• ALTER DATABASE DEFAULT EDITION = testANewEditionView; // default edition for users
• SELECT SYS_CONTEXT('USERENV', 'CURRENT_EDITION_NAME') FROM DUAL; // view cur session

---

Can't apply roles to user for views (ins, del, up, select?) only apply the type to the view? -> investigate

Changing the 'Write-ability' of an Editioning View

• To change an existing editioning view from read-only to read-write, use the SQL statement ALTER VIEW READ WRITE. To change an existing editioning view from read-write to read-only, use the SQL statement ALTER VIEW READ ONLY.

[ryantaplin]

Qs

• How does the trigger know that is needs to fire and for which edition. You set it in edition 5 and it will trigger for edition 4?! But how does it know. I'm a little confused about the details of that implementation.

CREATE OR REPLACE TRIGGER TRIG_NAME
BEFORE INSERT OR UPDATE TABLE_NAME
FOR EACH ROW
FORWARD CROSSEDITION
DISABLE 
BEGIN
   :new.Value = new.newValue;
   :new.ValueValue = old.Value;
END;

/* Specified DISABLE on the trigger; so enable it now - by default it's enabled. */
ALTER TRIGGER TRIG_NAME ENABLE;

Follows -> for a SQL XE forward trigger
Precedes -> for a SQL XE reverse trigger.

To back fill the database you can run all old rows in DB and overwrite a value with itself to force the trigger to fill in new column value. For deleting a row it is probably a bit more problematic.

To delete a row you should create an edition view; use it and wait until the old view with that column is no longer in use. You can then delete the old edition using that column and follow up by deleting that column itself.

[ryantaplin]

Some issues with setting edition with TC4Connection query execute. Check the ojdbc version and implementation of oracle db from tjheslin's project.

https://github.com/tjheslin1/Patterdale-jvm

Edit: updating the ojdbc to use the one in Patterdale-jvm has made this work now. I am nearly there with the implementation of an oracle. I just need to spend some time working out the triggers (as above) which I will proceed to write a blog about.

I would like to make it so that the database connection is not created every time I create the page but held somewhere (keeping an open connection) for the duration of the application. I was thinking about pushing the database into the wiring object and holding it there (but will need to experiment with the implementation).