Scenario: PK Generation by Sequence

A scenario with two tables mapping to a document with a sub-document. The primary key on the main table is generated by a sequence. Use of the sequence is handled by the client code (lightblue).

Goal

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

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 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
                },
                "rdbms": {
                    "table": "BASE",
                    "column": "ID",
                    "generate": "SELECT BASE_SEQ.nextval FROM DUAL"
                }
            },
            "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

SELECT BASE_SEQ.nextval FROM DUAL;
/* for documentation purposes, assuming returning a value of 1*/

INSERT INTO BASE (ID, A_FIELD)
VALUES (1, 'something');

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

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

lightblue response

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