Metadata: Variable and Measurement (a Cell is associated to a variable and measurement)

[sorja]

Example query to get all labels for each variable. Example output:

variablename	labels	table_relations
forest	{"66817a08-dc93-4151-b5ed-176d8f04e9b7": {"key": "growingStock.forest"}, "66da2217-da42-492f-9ff4-c99a59e6675c": {"key": "fra.growingStock.totalForest"}}	["growingStockTotal", "growingStockAvg"]
forest_above_ground	{"66817a08-dc93-4151-b5ed-176d8f04e9b7": {"key": "biomassStock.aboveGround"}, "66da2217-da42-492f-9ff4-c99a59e6675c": {"key": "fra.biomassStock.aboveGround2025"}}	["biomassStockTotal", "biomassStockAvg"]
forestArea	{"66817a08-dc93-4151-b5ed-176d8f04e9b7": {"key": "extentOfForest.forestArea"}, "66da2217-da42-492f-9ff4-c99a59e6675c": {"key": "extentOfForest.forestArea"}}	["extentOfForest"]

-- NOTE: This query expects variable to have one label !!
-- Map of variableName <-> label
select r.props ->> 'variableName' as variableName, c.props ->> 'labels' as labels, jsonb_agg(t.props -> 'name') as table_relations
from assessment_fra.section s
         left join assessment_fra.table_section ts on s.id = ts.section_id
         left join assessment_fra.table t on ts.id = t.table_section_id
         left join assessment_fra.row r on t.id = r.table_id
         left join assessment_fra.col c on r.id = c.row_id
where
    r.props -> 'cycles' ? '66da2217-da42-492f-9ff4-c99a59e6675c' and -- update to cycle you want or remove for all
    r.props -> 'variableName' is not null and
    c.props -> 'cycles' ? '66da2217-da42-492f-9ff4-c99a59e6675c' and -- update to cycle you want or remove for all
    c.props -> 'labels' is not null
group by 1, 2

[sorja]

Some ideas..

``` --- public.variable type Variable { name: string // 'forestArea' translations: { en: string, -- 'Forest Area', ... } props: {}, uuid: string, id: number, } --- cycle.variable can overRide translations and props, eg some translations changes for certain cycle ``` ``` -- measurement uuid | variable_uuid | value | props ``` Allows to have dynamic translations under admin view, same as for files currently