Table creation

Create a new table with foreign key

Note that the foreign key needs to be created in the data table, not the one holding static columns. Ideally, the layout should be:

• static column table:
  • table - varchar, the table the column is coming from (and where the foreign key constraint is defined)
  • column - varchar, the static column's name
  • type - varchar, the column's original type (even though we know this from the data table's schema)
  • value - blob or text, the JSON representation of the static column's value
  • primary key is (table, column)
• data table:
  • normal columns
  • for each static column we need two columns to account for the foreign key (one for the table name, the other for the column name)
  • foreign key: FOREIGN KEY(stable_name, stable_col) REFERENCES(static_table.table, static_table.column)

Note that such a layout means having only one static column table for multiple data tables. Thus, the table should be created on module start-up, regardless of whether any requests comprising static columns actually arrive.

When inserting data, you need to insert / update values in the static column table first.

Table deletion

When dropping a table, first delete the data table, and then delete all records from the static column table which have the table column set to the name of the table being dropped. Deleting individual rows from a data table is a bit trickier, though, since we would need to check if a particular static column has disappeared completely or not before deleting it from the static column table.

@mobrovac I like your idea about having one static table for multiple data table. But here is the original Idea that I discussed with @GWicke yesterday on IRC which include one to one mapping for static and data table.

So, For each data table with static column(s) we will create a new '_static' table. The layout would look like:

• 'Static' Table
  • hash_key - Hash key from the main data table (i.e primary key for this table)
  • static columns - Static columns
  • foreign key
• Data Table
  • normal columns - static column

NB: Special thing about static columns are that thier value is shared between columns with the same hash key, irrespective of their range keys, So we don't need a range key there. Also, we might have more than one static columns.

Now, for each insert in main data table we do a insert in static table if hash is different else we do a update in the static table with new static columns.

This have been implemented here https://github.com/wikimedia/restbase-mod-table-sqlite/pull/1

This is implemented together with T88191, so I'm closing this task as it's a subset of the main task.