CGI-FR / CGI-FR/LINO

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

Open
#42 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Go
Stars
23
Forks
5
PR merge metrics
No merged PRs in 30d

Description

## 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.

```yaml
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

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

inserting the following JSON object

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

will produce the sql statements

```sql
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 );
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.