Page MenuHomePhabricator

Support 'static' columns in SQLite3 module back-end for RESTBase
Closed, ResolvedPublic

Description

After having some discussion on github. we decided to support 'static' columns in SQLite3 by create a new '_static' table.

Here's the rough roadmap

  1. Create a new table with foreign key
  2. On each insert in main table with "static columns"
    1. Do insert a new row in static table - done
    2. Update other static columns with new static columns
  3. On each get with static columns perform a join with static table

Event Timeline

Hardikj created this task.Feb 18 2015, 9:08 PM
Hardikj claimed this task.
Hardikj raised the priority of this task from to Normal.
Hardikj updated the task description. (Show Details)
Hardikj added a project: RESTBase.
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptFeb 18 2015, 9:08 PM

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.

Aklapper renamed this task from Support 'static' coulums in SQLite3 module back-end for RESTBase to Support 'static' columns in SQLite3 module back-end for RESTBase.Feb 19 2015, 11:53 AM
Aklapper set Security to None.
Hardikj added a comment.EditedFeb 19 2015, 4:58 PM

@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.

Hardikj moved this task from Backlog to In progress on the RESTBase board.May 13 2015, 11:37 PM
GWicke reassigned this task from Hardikj to Eevans.Jun 20 2015, 12:18 AM
Eevans reassigned this task from Eevans to Pchelolo.Jul 20 2015, 4:49 PM

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

Pchelolo closed this task as Resolved.Jul 20 2015, 4:51 PM