Scenario: PK Generation by DB

A scenario with two tables mapping to a document with a sub-document. The interesting bit is a trigger that always assigns a value to the PK, no matter what is passed in.

Goal: Show how a primary key generated by a trigger in the database will work with dependent objects requiring that PK value for a foreign key.

!!! NOT SUPPORTED !!!
This scenarios is complicated and requires generation of PL/SQL to get the value of the generated PK. Additionally, given it's anonymous PL/SQL it gets tricky to send the generated key to the lightblue application code. Therefore this scenario is not recommended for support at this time.

Contents:

• Setup
• insert

Setup

Schema: Oracle

Create Table BASE (
  ID NUMBER(19) NOT NULL,
  A_FIELD VARCHAR2(256),
  PRIMARY KEY (ID)
);
/

CREATE SEQUENCE BASE_SEQ
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE;
/

CREATE TRIGGER BASE_TRIGGER
  BEFORE INSERT ON BASE
  FOR EACH ROW
DECLARE
BEGIN
  :new.ID := BASE_SEQ.nextval;
END BASE_TRIGGER;
/

Create Table SUB_DOCUMENT (
  BASE_ID NUMBER(19) NOT NULL,
  S_FIELD VARCHAR2(256),
  FOREIGN KEY (BASE_ID) REFERENCES BASE(ID)
);

Schema: lightblue

{
    "entityInfo": {
        "datastore": {
            "backend": "rdbms",
            "datasource": "my_datasource",
            "dialect": "oracle"
        },
        "name": "pkGeneration"
    },
    "schema": {
        "name": "pkGeneration",
        "status": {
            "value": "active"
        },
        "version": {
            "value": "0.1.0"
        },
        "fields": {
            "id": {
                "type": "integer",
                "constraints": {
                    "identity": true,
                    "generated": true
                },
                "rdbms": {
                    "table": "BASE",
                    "column": "ID"
                }
            },
            "a": {
                "type": "string",
                "constraints": {
                    "maxLength": 256
                },
                "rdbms": {
                    "table": "BASE",
                    "column": "A_FIELD"
                }
            },
            "subDocument": {
                "type": "object",
                "fields": {
                    "s": {
                        "type": "string",
                        "constraints": {
                            "maxLength": 256
                        },
                        "rdbms": {
                            "table": "SUB_DOCUMENT",
                            "column": "S_FIELD"
                        }
                    }
                }
            }
        }
    }
}

insert

lightblue request

PUT /data/insert/pkGeneration/0.1.0

{
    "objectType": "pkGeneration",
    "version": "0.1.0",
    "data": [
        {
            "a": "something",
            "subDocument": {
                "s": "something else"
            }
        }
    ],
    "projection": [
        {
            "field": "*",
            "include": true
        }
    ]
}

generated SQL

DECLARE
    V_ID NUMBER(19);
BEGIN
    INSERT INTO BASE (A_FIELD)
    VALUES ('something')
    RETURNING ID INTO V_ID;

    INSERT INTO SUB_DOCUMENT (BASE_ID, S_FIELD)
    VALUES (V_ID, 'something else');
END
/

/* how do we get the ID returned? */

lightblue response

{
    "status": "complete",
    "modifiedCount": 1,
    "matchCount": 1,
    "processed": [
        {
            "id": 1,
            "a": "something",
            "subDocument": {
                "s": "something else"
            }
        }
    ]
}