database.yaml

apiVersion: v1
kind: Service
metadata:
  name: db-service
  namespace: default
spec:
  selector:
    app: database
  type: ClusterIP
  ports:
  - name: db-service
    protocol: TCP
    port: 1521
    targetPort: 1521
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: database-pv-claim
  labels:
    app: database
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 20Gi
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name:  database
  namespace: default
  labels:
    app: database
spec:
  selector:
    matchLabels:
      app: database
  replicas: 1
  template:
    metadata:
      labels:
        app: database
    spec:
      securityContext:
        runAsUser: 54321    # Oracle User 
        runAsGroup: 54321   # Oinstall group
        fsGroup: 54321      # Oinstall group
      volumes:
      - name: db-config-volume
        configMap:
          name: db-config-map
      - name: database-persistent-storage
        persistentVolumeClaim:
          claimName: database-pv-claim
      containers:
      - name: oracle-xe
        image: gvenzl/oracle-xe:21.3.0
        resources:
          limits:
            cpu: 1000m
            memory: 3000Mi
        env:
          - name: ORACLE_PASSWORD
            valueFrom:
              secretKeyRef:
                name: db-secret
                key: sys-pass
          - name: APP_USER
            value: appuser
          - name: APP_USER_PASSWORD
            valueFrom:
              secretKeyRef:
                name: db-secret
                key: app-pass
        ports:
        - containerPort: 1521
          name: database
        volumeMounts:
        - name: db-config-volume
          mountPath: /container-entrypoint-initdb.d
        - name: database-persistent-storage
          mountPath: /opt/oracle/oradata
---
apiVersion: v1
kind: Secret
metadata:
  name: db-secret
  namespace: default
type: Opaque
data:
  sys-pass: aG9sYXN5cw==
  app-pass: aG9sYW11bmRv
---
kind: configMap
apiVersion: v1
metadata:
  name: db-configmap
  namespace: default
data:
  init.sql: |
    ALTER SESSION SET CONTAINER=XEPDB1;

    -- Table person creation
    CREATE TABLE APPUSER.LOGIN_USER (
      LOGIN_USER_ID NUMBER,
      EMAIL VARCHAR2(100) NOT NULL,
      PASSWORD VARCHAR2(1000) NOT NULL,
      USER_TOKEN VARCHAR2(1000) NOT NULL,
      PHONE VARCHAR2(15) DEFAULT NULL,
      GENDER VARCHAR2(1) DEFAULT NULL,
      AGE NUMBER DEFAULT 0,
      FIRST_NAME VARCHAR2(100) NOT NULL,
      LAST_NAME VARCHAR2(100) NOT NULL,
      ADD_DATE DATE DEFAULT SYSDATE,
      MOD_DATE DATE DEFAULT NULL,
      PRIMARY KEY(LOGIN_USER_ID)
    );

    -- sequence user
    CREATE SEQUENCE  APPUSER.SQ_LOGIN_USER NOCACHE;

    -- type account, for example cash, saving, personal saving etc
    CREATE TABLE APPUSER.BA_TYPE(
        BA_TYPE_ID NUMBER,
        NAME VARCHAR2(20),
        DESCRIPTION VARCHAR2(400),
        PRIMARY KEY (BA_TYPE_ID)
    );

    -- sequence type
    CREATE SEQUENCE APPUSER.SQ_BA_TYPE NOCACHE;

    -- currencies available
    CREATE TABLE APPUSER.BA_CURRENCY(
        BA_CURRENCY_ID NUMBER,
        NAME VARCHAR2(100),
        DESCRIPTION VARCHAR2(500),
        CURRENCY_SYMBOL VARCHAR2(10),
        PRIMARY KEY (BA_CURRENCY_ID)
    );

    -- sequence currency
    CREATE SEQUENCE APPUSER.SQ_BA_CURRENCY NOCACHE;

    -- Data from bank accounts created
    CREATE TABLE APPUSER.BA_ACCOUNT(
        BA_ACCOUNT_ID NUMBER,
        NAME VARCHAR2(200),
        DESCRIPTION VARCHAR2(400),
        ACCOUNT_NUMBER NUMBER,
        ACCOUNT_BALANCE NUMBER,
        ACCOUNT_STATUS NUMBER,
        ADD_DATE DATE DEFAULT  SYSDATE,
        MOD_DATE DATE DEFAULT NULL,
        LOGIN_USER_ID NUMBER NOT NULL,
        BA_TYPE_ID NUMBER NOT NULL,
        BA_CURRENCY_ID NUMBER NOT NULL,
        PRIMARY KEY (BA_ACCOUNT_ID),
        FOREIGN KEY (LOGIN_USER_ID) REFERENCES APPUSER.LOGIN_USER(LOGIN_USER_ID),
        FOREIGN KEY (BA_TYPE_ID) REFERENCES APPUSER.BA_TYPE(BA_TYPE_ID),
        FOREIGN KEY (BA_CURRENCY_ID) REFERENCES APPUSER.BA_CURRENCY(BA_CURRENCY_ID)
    );

    -- sequence ba_account
    CREATE SEQUENCE APPUSER.SQ_BA_ACCOUNT NOCACHE;

    -- category, category available for example gym, personal, vacations etc
    CREATE TABLE APPUSER.OP_CATEGORY(
        OP_CATEGORY_ID NUMBER,
        NAME VARCHAR2(100),
        DESCRIPTION VARCHAR2(100),
        PRIMARY KEY (OP_CATEGORY_ID)
    );

    -- sequence category
    CREATE SEQUENCE APPUSER.SQ_OP_CATEGORY NOCACHE;

    -- transaction typ expense/income and more if is available
    CREATE TABLE APPUSER.OP_TRANSACTION_TYPE(
        OP_TRANSACTION_TYPE_ID NUMBER,
        NAME VARCHAR2(200),
        DESCRIPTION VARCHAR2(400),
        PRIMARY KEY (OP_TRANSACTION_TYPE_ID)
    );

    -- sequence transaction type
    CREATE SEQUENCE APPUSER.SQ_OP_TRANSACTION_TYPE NOCACHE;

    -- transactions
    CREATE TABLE APPUSER.OP_TRANSACTION(
        OP_TRANSACTION_ID NUMBER,
        DESCRIPTION VARCHAR2(100),
        AMOUNT NUMBER,
        ADD_DATE DATE DEFAULT  SYSDATE,
        MOD_DATE DATE DEFAULT NULL,
        LOGIN_USER_ID NUMBER NOT NULL,
        OP_TRANSACTION_TYPE_ID NUMBER NOT NULL,
        OP_CATEGORY_ID NUMBER NOT NULL,
        BA_ACCOUNT_ID NUMBER NOT NULL,
        PRIMARY KEY (OP_TRANSACTION_ID),
        FOREIGN KEY (LOGIN_USER_ID) REFERENCES APPUSER.LOGIN_USER(LOGIN_USER_ID),
        FOREIGN KEY (BA_ACCOUNT_ID) REFERENCES APPUSER.BA_ACCOUNT(BA_ACCOUNT_ID),
        FOREIGN KEY (OP_TRANSACTION_TYPE_ID) REFERENCES APPUSER.OP_TRANSACTION_TYPE(OP_TRANSACTION_TYPE_ID),
        FOREIGN KEY (OP_CATEGORY_ID) REFERENCES  APPUSER.OP_CATEGORY(OP_CATEGORY_ID)
    );

    -- sequence transaction
    CREATE SEQUENCE APPUSER.SQ_OP_TRANSACTION NOCACHE;

    -- Table op_transfer, for transfer between different bank account
    CREATE TABLE APPUSER.OP_TRANSFER(
        OP_TRANSFER_ID NUMBER,
        DESCRIPTION VARCHAR2(300),
        TRANSFER_AMOUNT NUMBER,
        ADD_DATE DATE DEFAULT SYSDATE,
        DESTINATION_ACCOUNT_ID NUMBER NOT NULL,
        BA_ACCOUNT_ID NUMBER NOT NULL,
        LOGIN_USER_ID NUMBER NOT NULL,
        PRIMARY KEY (OP_TRANSFER_ID),
        FOREIGN KEY (LOGIN_USER_ID) REFERENCES APPUSER.LOGIN_USER(LOGIN_USER_ID),
        FOREIGN KEY (BA_ACCOUNT_ID) REFERENCES APPUSER.BA_ACCOUNT(BA_ACCOUNT_ID)
    );

    -- sequence OP_TRANSFER
    CREATE SEQUENCE APPUSER.SQ_OP_TRANSFER NOCACHE;

    -- CREATE FUNCTION FOR GENERATE USER_TOKEN
    CREATE OR REPLACE FUNCTION APPUSER.API_TOKEN(PSECRET VARCHAR2) RETURN VARCHAR2
    IS
      VRESULT VARCHAR2(4000);
    BEGIN
      SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_I18N.STRING_TO_RAW(STANDARD_HASH(PSECRET, 'MD5'), 'AL32UTF8')) INTO VRESULT from dual;
      RETURN VRESULT;
    END API_TOKEN;

    -- DEFAULT VALUES
    -- Default Values for currency
    INSERT INTO APPUSER.BA_CURRENCY(BA_CURRENCY_ID, NAME, DESCRIPTION, CURRENCY_SYMBOL)
    VALUES(APPUSER.SQ_BA_CURRENCY.nextval,'Quetzals','Currency for Guatemala City','Q');
    INSERT INTO APPUSER.BA_CURRENCY(BA_CURRENCY_ID, NAME, DESCRIPTION, CURRENCY_SYMBOL)
    VALUES(APPUSER.SQ_BA_CURRENCY.nextval,'Dollar','Currency for United States','$');

    --Default Values for type bank account
    INSERT INTO APPUSER.BA_TYPE(BA_TYPE_ID,NAME, DESCRIPTION)
    VALUES(APPUSER.SQ_BA_TYPE.nextval,'Monetary','Monetary bank account, for your daily financial expenses');
    INSERT INTO BA_TYPE(BA_TYPE_ID,NAME, DESCRIPTION)
    VALUES(APPUSER.SQ_BA_TYPE.nextval,'Saving','Saving bank account, you do not need constant access to his money');

    -- Default Values for category of transaction
    INSERT INTO APPUSER.OP_CATEGORY(OP_CATEGORY_ID, NAME, DESCRIPTION)
    VALUES(APPUSER.SQ_OP_CATEGORY.nextval,'Investments','Category for different investments');
    INSERT INTO APPUSER.OP_CATEGORY(OP_CATEGORY_ID, NAME, DESCRIPTION)
    VALUES (APPUSER.SQ_OP_CATEGORY.nextval,'Gym','Category for gym');
    INSERT INTO APPUSER.OP_CATEGORY(OP_CATEGORY_ID, NAME, DESCRIPTION)
    VALUES(APPUSER.SQ_OP_CATEGORY.nextval,'Shopping','Category for shopping');
    INSERT INTO APPUSER.OP_CATEGORY(OP_CATEGORY_ID, NAME, DESCRIPTION)
    VALUES(APPUSER.SQ_OP_CATEGORY.nextval,'Education','Category for Education and certificates');
    INSERT INTO APPUSER.OP_CATEGORY(OP_CATEGORY_ID, NAME, DESCRIPTION)
    VALUES(APPUSER.SQ_OP_CATEGORY.nextval,'Salary','Category for salary');
    INSERT INTO APPUSER.OP_CATEGORY(OP_CATEGORY_ID, NAME, DESCRIPTION)
    VALUES(APPUSER.SQ_OP_CATEGORY.nextval,'Others','Category for others')

    -- Default Values for type of transaction
    INSERT INTO APPUSER.OP_TRANSACTION_TYPE(OP_TRANSACTION_TYPE_ID, NAME, DESCRIPTION)
    VALUES (APPUSER.SQ_OP_TRANSACTION_TYPE.nextval,'Income','Type for incomes');
    INSERT INTO APPUSER.OP_TRANSACTION_TYPE(OP_TRANSACTION_TYPE_ID, NAME, DESCRIPTION)
    VALUES (APPUSER.SQ_OP_TRANSACTION_TYPE.nextval,'Expense','Type for expenses');