error in api tutorial (is_nullable)

[wingechr]

According to multiple tutorials, columns can be configured to not allow NULL values by adding "is_nullable": "NO"in the definition.

This does not work.

Here is a complete example. I create a table and upload an empty record, then retrieve the data and get a record with a NULL value (None)

import requests
import os

oep_url = 'https://openenergy-platform.org'
token = os.environ['OEP_API_TOKEN']
schema = 'model_draft'
table = 'demo_nullable'

tabl_url = oep_url + '/api/v0/schema/' + schema + '/tables/' + table + '/'

table_def = { 
    "query": { 
        "columns": [
            {"name": "id", "data_type": "bigserial", "is_nullable": "NO"},
            {"name": "name", "data_type": "int", "is_nullable": "NO"},            
        ],
        "constraints": [{"constraint_type": "PRIMARY KEY", "constraint_parameter": "id"}] 
    }
}

data = {
    "query": {} # empty record!
}

ses = requests.session()
ses.headers = {'Authorization': 'Token %s' % token}

# create table
res = ses.put(tabl_url, json=table_def)

# insert data
res = ses.post(tabl_url + 'rows/new', json=data)

# retrieve data
res = ses.get(tabl_url + 'rows')
res.json() # [{'id': 1, 'name': None}] !!! not ok !!!

# cleanup
res = ses.delete(tabl_url)

[wingechr]

It works if you use "is_nullable": False instead.

[jh-RLI]

Good catch! should be "False" of course. I will update this in the API tutorial series.

[wingechr]

Cool, thanks a lot. If you update the tutorial, I have something else that you may want to include and is currently not documented, as far as i could see:

to add (single columns) foreign keys with create table api request, you cannot use the constraints section, instead, you also add it to the column definition like so:

{
    "name": "SOME_COLUMN_NAME",
    "data_type": "SOME_DATA_TYPE",
    "foreign_key": [{
        "table": "SCHEMA.REFERENCED_TABLE",
        "column": "REFERENCED_COLUMN"
    }]
}

[jh-RLI]

then we should also update the oep api readthedocs? I will include/update this in the tutorial 👍

[han-f]

we can close this issue, correct? This related to some legacy tutorial @wingechr @jh-RLI?

[jh-RLI]

Cool, thanks a lot. If you update the tutorial, I have something else that you may want to include and is currently not documented, as far as i could see:

to add (single columns) foreign keys with create table api request, you cannot use the constraints section, instead, you also add it to the column definition like so:

{
    "name": "SOME_COLUMN_NAME",
    "data_type": "SOME_DATA_TYPE",
    "foreign_key": [{
        "table": "SCHEMA.REFERENCED_TABLE",
        "column": "REFERENCED_COLUMN"
    }]
}

Not sure if this was updated in the "old tutorials", looking at our currently offered courses / tutorials i cant find any foreign_key references.
I think the constrains have not yet been documented for the API. I also think this may be a new issue. We would need to change the example to include a foreign_key constraint that references another table that also needs to be created in the database during the tutorial.