Add all tables, all columns

[mdavis-xyz]

I'm working on a project where I'm scraping the schema documentation, and putting it in a big json file. (Among other things, such as making a more usable and comprehensive documentation website than AEMO's.)

So I'll have:

• the list of all MMS tables (except the special ones like 4 second scada)
• for each table:
  • which columns are primary keys
  • which sets of columns are indexes
  • for each column:
    • name
    • SQL type as per the documentation
    • whether it's deprecated/unused
    • a more narrow type for polars/pandas (e.g. binary instead of NUMBER(3) for INTERVENTION)
    • Enum/factor options (e.g. knowing that a column is only ever GENERATOR or LOAD)
    • column specific quirks - e.g. there is one table where INTERVENTION is y and n instead of 0 and 1

My understanding is that currently Nemosis uses hard-coded lists of tables and columns. With such a json schema, we could use nemosis do be able to download all tables. Well, at least all tables in the monthly MMSDM folders since the latest renaming.

We could also use very narrow schemas (e.g. enum for REGIONID) to make parsing faster and use lower memory. (e.g. let us process huge bid files on a normal or even weak laptop.)

Is this something you think we should do?

[dylanjmcconnell]

I sort of essentially did this (and semi* still do this) for the original implementation of the openNEM backend. I store basically all the metadata for the tables (see example below) - and have (multiple) translation layers that can be used to explicitly create schema in your database of choice (e.g. mysql/dolphinDB, postgres) - or an experimental implementation of forcing types on reading the csv's.

Those translation layers do some of the things you mention - (though some small differences, and more translating from oracle to other DB engine). e.g:

- NUMBER(12,4)  --> DECIMAL(12,4) 
- NUMBER(4) --> SMALLINT
- DATE --> TIMESTAMP

Having this level of explicit schema data makes sense for a recreating an RDBM - not as familiar with NEMOSIS, but I gather schema types are managed by in python file / sort of like a config. Similarly type handling done with a python file. So I don't know (one way or the other) would be useful for NEMOSIS? It does seem like pretty big departure from current approach - would guess a big refactor of existing approach, or duplication (i.e. having the schema info in a json and the existing approach). Not to say it's not worth pursing - but seem to be bringing more of an RDBM paradigm into where it historically hasn't existed.

*Not up to date - looks like latest version of MMS schema I am up to is v5.5, so may be missing a a few tables and columns.

  "DISPATCH_UNIT_SCADA": {
    "Comment": "Dispatchable unit MW from SCADA at the start of the dispatch interval. The table includes all scheduled and semi-scheduled (and non-scheduled units where SCADA is available)",
    "Schema": [
      {
        "Name": "SETTLEMENTDATE",
        "Data Type": "Date",
        "Mandatory": "X",
        "Comment": "Date Time of the Dispatch Interval"
      },
      {
        "Name": "DUID",
        "Data Type": "varchar2(20)",
        "Mandatory": "X",
        "Comment": "Dispatchable Unit Identifier"
      }, 
      ...
   ]
"Key": {
      "Index": [
        "LASTCHANGED"
      ],
      "Primary": [
        "RUNNO",
        "SETTLEMENTDATE"
      ]
    }