Storage format for pSCompose

[sartaj10]

Based on our discussion at perfSONAR F2F

The concepts :

• in many cases, we will need to trace parent/child relationships between objects. For instance, a host is included in a host group, included in a test, included in a template.
• when we change a child, we’ll have to recalculate the JSON for all parents
• one of the key ideas then, is that we need to be able to discover parents of objects efficiently
• if we store objects in a single flat table, we can use a high-entropy ID (like a UUID) to refer to a specfic object, no matter its type.
• we can also reconstruct the JSON state, then, using only the list of high-entropy object IDs, because we can reconstruct parent-child relationships between specific types

For instance, consider a template, which has a reference to test, host_group and host. Here’s our example table

+----+--------------+---------------+--------------+-----------------+
| id | ref_set      | type          | json         | name            |
+----+--------------+---------------+--------------+-----------------+
| a1 | []           | host          | {...}        | ps-test.es.net  |
+----+--------------+---------------+--------------+-----------------+
| b2 | [a1]         | host_group    | {...}        | Test Group      |
+----+--------------+---------------+--------------+-----------------+
| c3 | [a1, b2]     | test          | {...}        | Andy's Test     |
+----+--------------+---------------+--------------+-----------------+
| d4 | [a1, b2, c3] | template      | {...}        | Andy's Template |
+----+--------------+---------------+--------------+-----------------+

In this table,

• Because we know row d4 has children a1, b2, c3, we can accurately reconstruct its JSON because we can quickly query SELECT id, type, json, name FROM storage WHERE id IN d4.ref_set
• From that data, we know that a1 is a host, b2 is a group, c3 is a test. They can all be put in the correct “area” of the template, and we have reconstructed the JSON
• In the case that we edit the name for a1, we do SELECT id FROM storage WHERE a1 IN ref_set, and recaculate the JSON for each object involved
• we can organize this stuff as either DB triggers or part of the API

[sartaj10]

Questions / Answers

1. We need another column called username or last_edited_by to identify who pushed this template / task etc and render it on the UI

• Yes, agreed. This will be part of the main table. We also prob need a "last_edited_by_time", "created_at" timestamp

2. How would we achieve favorites, recently edited in this table scenario?

• This can be achieved through a userdata table that stores such information. Or, in the main table, we can have a "last_edited_by" and "created_by" column that can track this

3. Do we need to discuss more about the users table?

• This table will only include info about the users such as username, password, name, role etc