Proposal: generate flatjson files and support corresponding loader

[wenchy]

TODO: CSV and SQLite3 may also be supported?

Flat (table) JSON file extension can be defined as .flat.json (Question: how to distinguish protojson and flat json?).

Generate two-dimensional string table files from Excel/CSV.

The flatjson in converter workflow is:

json-> flatjson -> protojson/protowire/prototext

flowchart TD
    Excel/CSV --> flatgen;
    flatgen((flatgen)) --> flatjson;
    Excel/CSV -->  confgen;
    Excel/CSV --> protogen;
    flatjson --> confgen;
    flatjson --> protogen;
    confgen((confgen )) --> JSON/Bin/Text;
    protogen((protogen)) --> protoconf;

Loading

Example

Refer to https://tableauio.github.io/docs/excel/list-in-map/#vertical-list-in-vertical-map

ID	Name	PropID	PropValue
map<uint32, Item>	string	[Prop]int32	int64
Item’s ID	Item’s name	Prop’s ID	Prop’s value
1	Apple	1	10
2	Orange	1	20
2	Banana	2	30

Generated proto:

// --snip--
option (tableau.workbook) = {name:"HelloWorld.xlsx" namerow:1 typerow:2 noterow:3 datarow:4};

message ItemConf {
  option (tableau.worksheet) = {name:"ItemConf"};

  map<uint32, Item> item_map = 1 [(tableau.field) = {key:"ID" layout:LAYOUT_VERTICAL}];
  message Item {
    uint32 id = 1 [(tableau.field) = {name:"ID"}];
    string name = 2 [(tableau.field) = {name:"Name"}];
    repeated Prop prop_list = 3 [(tableau.field) = {layout:LAYOUT_VERTICAL}];
    message Prop {
      int32 prop_id = 1 [(tableau.field) = {name:"PropID"}];
      int64 prop_value = 2 [(tableau.field) = {name:"PropValue"}];
    }
  }
}

Generated protojson

{
    "itemMap": {
        "1": {
            "id": 1,
            "name": "Apple",
            "desc": "A kind of delicious fruit."
        },
        "2": {
            "id": 2,
            "name": "Orange",
            "desc": "A kind of sour fruit."
        },
        "3": {
            "id": 3,
            "name": "Banana",
            "desc": "A kind of calorie-rich fruit."
        }
    }
}

Challenge

If we change the nesting struct, field layout, and field type, but the underlying data not changes.
So how to just modify protoconf (withou need to regenerate the underlying data (e.g.: to JSON)), then load the same underlying data with compatibility?

ID	Name	PropID	PropValue
[Item]uint64	string	uint64	int64
Item’s ID	Item’s name	Prop’s ID	Prop’s value
1	Apple	1	10
2	Orange	1	20
2	Banana	2	30

Generated proto:

// --snip--
option (tableau.workbook) = {name:"HelloWorld.xlsx" namerow:1 typerow:2 noterow:3 datarow:4};

message ItemConf {
  option (tableau.worksheet) = {name:"ItemConf"};

  repeated Item item_list = 1 [(tableau.field) = {layout:LAYOUT_VERTICAL}];
  message Item {
    uint64 id = 1 [(tableau.field) = {name:"ID"}];
    string name = 2 [(tableau.field) = {name:"Name"}];
    uint64 prop_id = 3 [(tableau.field) = {name:"PropID"}];
    int64 prop_value = 4 [(tableau.field) = {name:"PropValue"}];
  }
}

Generated flatjson

Generated flatjson format 1

Just convert the Excel/CSV to two-dimensional strings without no data type parsing.
This format has following advantages:

• Simple and intuitive as two two-dimensional strings
• confgen: generate proto files
• confgen: generate config files.
• loader:
  • go: import this flatjson to internal sheet table struct, and load the config data by using origin loader.
  • Other programming languages: use go confgen to generate protojson or protowire as temp files, and then use correponding loader to load them as usual.

[
    [
        "ID",
        "Name",
        "PropID",
        "PropValue"
    ],
    [
        "map<uint32, Item>",
        "string",
        "[Prop]int32",
        "int64"
    ],
    [
        "Item’s ID",
        "Item’s name",
        "Prop’s ID",
        "Prop’s value"
    ],
    [
        "1",
        "Apple",
        "1",
        "10"
    ],
    [
        "2",
        "Orange",
        "1",
        "20"
    ],
    [
        "2",
        "Banana",
        "2",
        "30"
    ]
]

Generated flatjson format 2

Just convert each data row of the Excel/CSV to Key-Value pairs without no data type parsing.
This format has following disadvantages:

• ❌confgen: generate proto files (NOT SUPPORTED)
• ❌confgen: generate config files. (NOT SUPPORTED)
• verbose string keys
• loader: import this flatjson to internal sheet table struct, and load the config data by using origin loader.

[
    {
        "ID": "1",
        "Name": "Apple",
        "PropID": "1",
        "PropValue": "10"
    },
    {
        "ID": "2",
        "Name": "Orange",
        "PropID": "1",
        "PropValue": "10"
    },
    {
        "ID": "2",
        "Name": "Banana",
        "PropID": "2",
        "PropValue": "30"
    }
]