[PROPOSAL] Feed primary key field with sequence.nextval and foreign key with sequence.currval

[youen]

Context

We have to insert the jsonobject in a table film

{
  "film_id": 452
  "title" :  "The Matrix"
   "year": 1999
}

Problem

During a dataset insert primary key have to be unique to avoid conflict with existing values. Also sequences have to be updated upper than the maximum primary key inserted to avoid future conflict.

Solution

Add sequence information in table.yaml and use it during lino push insert process.

version: v1
tables:
  - name: film
    keys:
      - film_id
    sequence :
      film_id:  sequence_film_id

If the film_id is omitted, lino use the value of sequence_film_id.nextval to feed the primary_key.

Relation

If the primary key is a part of a relation

  - name: film_film_category
    parent:
        name: film
        keys:
          - film_id
    child:
        name: film_category
        keys:
          - film_id

inserting the following JSON object

{
  "title" :  "The Matrix",
   "year": 1999,
   "film_film_category" : [
      {
         "category_id": 151
      },
      {
         "category_id": 452
      },
   ]
}

will produce the sql statements

insert into film (film_id, title, year)
values (sequence_film_id.nextval, 'The Matrix', 1999);
insert into film_category (film_id, category_id)
values (sequence_film_id.currval, 151 );
insert into film_category (film_id, category_id)
values (sequence_film_id.currval, 452 );