Building (and HACKING) an Oracle Database

Docker Command to create a 23c Container

sudo docker run -itd --name theoracle -e ORACLE_PWD='password' -p 1521:1521 container-registry.oracle.com/database/free:latest

Create a Pluggable Database

CREATE PLUGGABLE DATABASE thematrix ADMIN USER neo IDENTIFIED BY trinity123 create_file_dest='/home/oracle';


ALTER PLUGGABLE DATABASE thematrix OPEN;

Give permissions to Neo

Note: You will need to login to "thematrix" pdb as sys to give Neo these permissions

GRANT create session TO neo;
GRANT create table TO neo;
GRANT unlimited tablespace TO neo;

sqlplus neo/trinity123@localhost:1521/thematrix

Creating the redpilldb

CREATE TABLE redpilldb (
m_id INTEGER,
name VARCHAR2(50) UNIQUE,
realname VARCHAR2(50),
role VARCHAR2(50),
ability VARCHAR2(50), 
CONSTRAINT m_plk PRIMARY KEY (m_id)
);

Adding the Matrix Characters

INSERT INTO RedPillDB VALUES (1, 'Neo', 'Thomas Anderson', 'The One', 'Bullet-dodging');
INSERT INTO RedPillDB VALUES (2, 'Morpheus', 'Morpheus', 'Captain', 'Hand-to-hand combat');
INSERT INTO RedPillDB VALUES (3, 'Trinity', 'Trinity', 'Hacker', 'Hacking');
INSERT INTO RedPillDB VALUES (4, 'Agent Smith', 'Smith', 'Agent', 'Bullet-dodging');

commit;

Playing with the redpilldb

select * from redpilldb;

select realname from redpilldb where name='Neo';
select realname from redpilldb where name!='Neo';

delete from redpilldb where name='Agent Smith';

select table_name from all_tables where user='NEO';

Create the coffee table

CREATE TABLE networkchuck_coffee (
    coffee_id NUMBER,
    name VARCHAR2(50) UNIQUE,
    origin VARCHAR2(50),
    roast VARCHAR2(50),
    description JSON,
    CONSTRAINT coffee_pk PRIMARY KEY (coffee_id)
);


INSERT INTO networkchuck_coffee VALUES (1, 'Default Route', 'Ethiopian', 'Light Roast', '{"Notes": "100 percent Natural notes of Berries, Chocolate, and Caramel", "Region": "Uraga", "Process": "Natural", "Elevation": "6000 Feet Above Sea Level"}'); 

INSERT INTO networkchuck_coffee VALUES (2, 'On-Call', 'Brazil', 'Medium Dark', '{"Notes": "100 percent Natural notes Cocoa, Cherry, and Maple Syrup. High quality single origin scoring 85", "Region": "Araponga", "Process": "Natural", "Elevation": "3900 Feet Above Sea Level"}'); 

INSERT INTO networkchuck_coffee VALUES (3, 'Sudo', 'Nicaragua', 'Light', '{"Notes": "100 percent Natural notes Honey, Caramel, Mango, and Pineapple. Scored 88", "Region": "Jinotega and Matagalpa", "Process": "Natural, Dried for 34 Days", "Elevation": "3900 - 5200 Feet Above Sea Level"}'); 

INSERT INTO networkchuck_coffee VALUES (4, '200 OK', 'Rwanda', 'Medium', '{"Notes": "100 percent Natural notes of Orange, Honey, and Caramel. Full Bodied, an excellent morning cup of coffee.", "Region": "Jarama", "Process": "Fully Washed and dried on raised beds", "Elevation": "1700-1900 MASL"}');

Create Customer Orders

CREATE TABLE customer_orders (
    order_id NUMBER,
    customer_id NUMBER,
    coffee_id NUMBER,
    quantity NUMBER,
    order_date DATE,
    CONSTRAINT order_pk PRIMARY KEY (order_id),
    CONSTRAINT coffee_fk FOREIGN KEY (coffee_id) REFERENCES networkchuck_coffee(coffee_id),
    CONSTRAINT customer_fk FOREIGN KEY (customer_id) REFERENCES redpilldb(m_id)
);

---customer orders

-- Adding orders for Neo (customer_id = 1)
INSERT INTO customer_orders VALUES (1, 1, 1, 2, TO_DATE('2023-07-10', 'YYYY-MM-DD'));
INSERT INTO customer_orders VALUES (2, 1, 2, 1, TO_DATE('2023-07-12', 'YYYY-MM-DD'));

-- Adding orders for Morpheus (customer_id = 2)
INSERT INTO customer_orders VALUES (3, 2, 3, 3, TO_DATE('2023-07-10', 'YYYY-MM-DD'));
INSERT INTO customer_orders VALUES (4, 2, 1, 1, TO_DATE('2023-07-13', 'YYYY-MM-DD'));

-- Adding orders for Trinity (customer_id = 3)
INSERT INTO customer_orders VALUES (5, 3, 4, 1, TO_DATE('2023-07-11', 'YYYY-MM-DD'));
INSERT INTO customer_orders VALUES (6, 3, 2, 2, TO_DATE('2023-07-14', 'YYYY-MM-DD'));

-- Adding orders for Agent Smith (customer_id = 4)
INSERT INTO customer_orders VALUES (7, 4, 3, 1, TO_DATE('2023-07-15', 'YYYY-MM-DD'));
INSERT INTO customer_orders VALUES (8, 4, 4, 2, TO_DATE('2023-07-16', 'YYYY-MM-DD'));

Create the JSON Duality View

Note: You will need to login as the sys (as sysdba) in order to have the correct permissions

CREATE JSON RELATIONAL DUALITY VIEW coffee_dv AS
  SELECT JSON {'coffeeID' : c.coffee_id,
               'name'   : c.name,
               'origin' : c.origin,
               'orders' :
                 [ SELECT JSON {'order_id' : o.order_id,
                                'cust_id'     : o.customer_id,
                                'quantity'   : o.quantity,
                                'order_date'   : o.order_date}
                     FROM customer_orders o WITH INSERT UPDATE
                     WHERE c.coffee_id = o.coffee_id ]}
  FROM networkchuck_coffee c WITH INSERT UPDATE DELETE;

Playing with the JSON Duality View

--- Selecting JSON data from the view

SELECT json_value(data, '$.name') AS coffee_name FROM coffee_dv;

--- select all the data
SELECT json_serialize(data FORMAT JSON) AS serialized_data FROM coffee_dv;

--- add coffee to the underlying tables

INSERT INTO networkchuck_coffee VALUES (6, 'Chuck Blend', 'Colombia', 'Medium', '{}');
COMMIT;

--- Insert JSON document into the duality view

INSERT INTO coffee_dv VALUES ('{"coffeeID": 6, "name": "Ethiopian Love", "origin": "Ethiopia", "orders": []}');
COMMIT;

Deploy ODAT Container

docker run --rm -it hktalent/odat /bin/bash
#the rm means it will remove when we are done with it
cd /root/odat
python3 ./odat.py -h