How do you handle maintaining large data tables?

Questions
#1

In my games I have a lot of large tables containing a lot of data. For example:

Map information in Fates of Ort:

M.map_info[hash("forest_tree_h1")] = {script_require = "maps.map_codes.forest_tree_h1", display_name=[[]], biome = "forest", max_level=10, min_level=1, store_level_cap=false, player_tint_add = vmath.vector4(0,0,0,0), player_tint_subtract = vmath.vector4(0,0,0,0), cloud =vmath.vector4(0.15, 0.20, 0.10, 0), border = true, map_zoom = true, music = {id="", force=false, ambient="indoor_empty", ambient_multiplier=1, rest_time=120}}
M.map_info[hash("rusty_fortress_h1")] = {script_require = "maps.map_codes.rusty_fortress_h1", display_name=[[]], biome = "rusty", max_level=10, min_level=1, store_level_cap=false, player_tint_add = vmath.vector4(0,0,0,0), player_tint_subtract = vmath.vector4(0,0,0,0), cloud =nil, border = true, map_zoom = true, music = {id="", force=false, ambient="indoor_empty", ambient_multiplier=1, rest_time=120}}
M.map_info[hash("rusty_huts_h1")] = {script_require = "maps.map_codes.rusty_huts_h1", display_name=[[]], biome = "rusty", max_level=10, min_level=1, store_level_cap=false, player_tint_add = vmath.vector4(0,0,0,0), player_tint_subtract = vmath.vector4(0,0,0,0), cloud =nil, border = true, map_zoom = true, music = {id="", force=false, ambient="indoor_empty", ambient_multiplier=1, rest_time=120}}

Skill information in my alien genetics game:

{skill_name = "LOG-LOG-4", skill_id = 1089, race_ids = {4,4}, category_hash = hash("4-4-"), texture = hash("skill_icons"), flipbook = hash("4-4-"), description = "add 97 crew limit", is_mutation = false, is_variant = true, can_combine = true, show_in_skillcheck = false, texture_string = "skill_icons", flipbook_string = "4-4-", default_unlocked = false, },
{skill_name = "LOG-LOG-5", skill_id = 1090, race_ids = {4,4}, category_hash = hash("4-4-"), texture = hash("skill_icons"), flipbook = hash("4-4-"), description = "remove 38 damage", is_mutation = false, is_variant = true, can_combine = true, show_in_skillcheck = false, texture_string = "skill_icons", flipbook_string = "4-4-", default_unlocked = false, },
{skill_name = "POW-POW-POW-1", skill_id = 1091, race_ids = {1,1,1}, category_hash = hash("1-1-1-"), texture = hash("skill_icons"), flipbook = hash("1-1-1-"), description = "add 21 damage", is_mutation = false, is_variant = true, can_combine = false, show_in_skillcheck = false, texture_string = "skill_icons", flipbook_string = "1-1-1-", default_unlocked = true, },
{skill_name = "POW-POW-POW-2", skill_id = 1092, race_ids = {1,1,1}, category_hash = hash("1-1-1-"), texture = hash("skill_icons"), flipbook = hash("1-1-1-"), description = "add 68 crew limit", is_mutation = false, is_variant = true, can_combine = false, show_in_skillcheck = false, texture_string = "skill_icons", flipbook_string = "1-1-1-", default_unlocked = false, },

This ends up being sometimes hundreds of lines of data, with a lot of stuff (strings, numbers, tables in tables, etc). Since the data is live during development, I end up wanting to change it a lot, add and remove things, etc. It obviously gets unwieldy doing it right in the editor, editing hundreds of lines. Previously I’ve made Google Sheets to handle it. I love spreadsheets but even I will admit this is cumbersome:

="M.map_info[hash("""&B3&""")] = {script_require = ""maps.map_codes."&B3&""", display_name=[["&E3&"]], biome = """&C3&""", max_level="&P3&", min_level="&Q3&", store_level_cap="&R3&", player_tint_add = vmath.vector4("&S3&","&T3&","&U3&","&V3&"), player_tint_subtract = vmath.vector4("&W3&","&X3&","&Y3&","&Z3&"), cloud ="&M3&", border = "&N3&", map_zoom = "&O3&", music = {id="""&H3&""", force="&I3&", ambient="""&K3&""", ambient_multiplier="&L3&", rest_time="&J3&"}}"

Google Sheets does make it a piece of cake to change data, but modifying the data structure is a pain (as you can see from the formula above).

Furthermore, I have been unable to come up with a good solution for handling subtables with a variable number of values in them (none, or one, or multiple). I either have one cell and comma-delineate the values (this is bad because it’s manual, makes it hard to keep track of the data, and eventually begins resembling the reason I implemented the spreadsheet in the first place), or I have x number of columns for each individual data point (this is bad because it is cumbersome to set up and implies a maximum number of values).

What do you use? Is there a tool you can recommend? Free is great, but if it saves time and makes my life easier I’ll gladly pony up.

2 Likes
#2

I would look into exporting the sheets into CSV files, and then processing those columns/rows and replacing as necessary in a formatting string. E.g. using a python script.

A python example, where the named keys can occurr whereever in the string.

print 'Hey %(name)s, there is a 0x%(errno)x error!' % {"name": name, "errno": errno }

So, a rest api call (if possible) to get the spreadsheet as a .csv file locally (e.g. https://stackoverflow.com/a/61107170/468516 or similar), then a python script to process that csv file and output a generated.lua.

Could work?

2 Likes
#3

That is more elegant than my spreadsheet solution - or at least the python script has much fewer &'s and "'s than the spreadsheet equivalent. Does require me to learn (presumably only the very basics of) python and what a rest api call is. :slight_smile:

I will edit my initial post to add this because it’s very relevant, but I’m thinking since the python solution is based on CSV input it will have the same problem handling values in subtables as my current setup does. Consider the values in subtable enemies for example:

{other_info = "blah", enemies = {1,5,7}}
{other_info = "bleh", enemies = {3,2}}
{other_info = "bloh", enemies = {1,1,1,1,1,8,3,3,3,3,3}}
{other_info = "bluh", enemies = {}}

In my spreadsheet solution, I haven’t been able to come up with a solution to handle data points that can have none or one or multiple values (like table enemies above). I would either just have one cell be the entry point and manually add comma-delineated values (bad because it’s so manual) or I would have to set up x number of columns to handle multiple values (bad because it’s cumbersome and implies a maximum number of entries for the subtable).

1 Like
#4

Well, how would you like to edit it? Currently, the list is a bunch ones followed by a bunch of threes (not sure what they’re representing). I guess it’s going to be a bit cumbersome to generalize to something “beautiful”.

1 Like
#5

I’m not sure what I want, was hoping someone would just go: “Ah! You’re obviously looking for the XYZ tool that handles all of this so elegantly!” :sweat_smile:

The example doesn’t really mean anything. A real example would be that I want to have a subtable with a variable number of tags added to an events table:

{tags = {ALIEN, SHIP, PIRATE}}
{tags = {OUTPOST, SCIENCE}}

As it stands I would just have a column for tags and for each cell value I’d input “ALIEN, SHIP, PIRATE” etc. I can’t imagine a better way to handle it - that might just mean I don’t have a good idea, or a better way doesn’t exist!

1 Like
#6

I’m no expert on excel or sheets, but a quick search reveals that there’s such a thing as a drop down selector. Perhaps you can use that to add/select values from a list?

google

1 Like
#7

Drop-downs are great and I do make use of them for data validation purposes and convenience, but they only work when you’re selecting one single value. In the image in your link, for example, I might want item B, C, and D. But a drop down only allows me to select one.

1 Like
#8

The idea was to inspire looking for more alternatives, like a multiple choice drop down list

1 Like
#9

It sounds like you might need a custom editor for your data? Perhaps based on Dear ImGUI and an underlying JSON structure for your data.

2 Likes
#10

Now I’m with you! That’s pretty clever.

That’s not a bad idea. Will think about this as a possibility. Obviously more involved than using something that already exists, but perhaps my needs are best suited for a custom solution.

2 Likes
#11

Sounds like an interesting problem, and certainly one with different approaches to solve. I think a better group to reach out to would be a forum for data scientists? Perhaps some ideas they could suggest would evolve into a good solution for you? I’d recommend some data scientist subreddit.

Would love to see what solution you go with.

2 Likes
#12

The best tool I’ve used for this type of task is the JSON editor that’s part of the commercial XMLSpy package. It’s expensive, but the grid-based editing is very nice.

image
image1198Ă—673 35 KB

1 Like
#13

Good idea - submitted a post. So far I’ve got a downvote and “use xml or json”. Let’s hope I get a bit more out of it! :slight_smile:

Thanks for the recommendation! That looks a lot like what I want - but you’re right about the pricetag being a bit spicy. I wonder if there’s something like it without all the many additional features that I don’t need.

1 Like
#14

This dynamic JSON editor also seems pretty nice: JSON Editor Interactive Example

1 Like
#15

Maybe http://castledb.org/ fits you requirements?

2 Likes
#16

Thanks for the recommendation! I have tested it and it’s very close, but the List functionality (i.e. what I call subtables) doesn’t quite fit what I want. For simpler usage I think this tool will be great.

Starting to think my spreadsheet solution isn’t that bad… I think the main thing is that the complexity I desire in maintaing variable length subtables just isn’t feasible unless I create a bespoke solution myself. As a former hammer (accountant) I will inevitably end up hitting the nail (spreadsheet). :slight_smile:

2 Likes
#17

JSON Buddy looks like it might be useful, similar to XMLSpy but much cheaper - https://www.json-buddy.com/

2 Likes
#18

Are you using formulas in your spreadsheet to calculate things, or just using it to store data?

If it’s just for storage, it does seem like some kind of simple JSON editor would be better. You’d get variable-length arrays and arbitrary key/value dictionaries.

For example

If you paste this... 
{
  "skill_name": "POW-POW-POW-1",
  "skill_id": 1091,
  "race_ids": [
    1,
    1,
    1
  ],
  "category_hash": {
    "type": "hash",
    "val": "1-1-1-"
  },
  "texture": {
    "type": "hash",
    "val": "skill_icons"
  },
  "flipbook": {
    "type": "hash",
    "val": "1-1-1-"
  },
  "description": "add 21 damage",
  "is_mutation": false,
  "is_variant": true,
  "can_combine": false,
  "show_in_skillcheck": false,
  "texture_string": "skill_icons",
  "flipbook_string": "1-1-1-",
  "default_unlocked": true
}

Into here: http://jsoneditoronline.org/

And switch to the “tree” view, you get a decent editor, even just the basic features. Checkboxes for the booleans, easily insert new things into the arrays/dictionaries, etc.

You could probably make an editor yourself in a couple days if there’s nothing suitable out there.

2 Likes