The struct plugin maps a relatively dynamic schema mechanism on a much more rigid relational database system. It also needs to take care that schemas evolve over time, but data in the past has to be preserved and the appropriate schema at that time need to be used.
To do this, schemas in struct do not directly map to tables in the database.
Generally you should not need to know about any of these internals, but it might be helpful for developers.
The schemas themselves are managed in three tables:
- manages the schema name and meta configuration (like editors and translated schema label)
- a new entry is saved every time the schema is edited, the newest version has the largest timestamp in the
- using the
tscolumn it's easy to figure out which schema was used at a certain time
- defines the type and configuration of the fields in the schemas and contains the visible name used for this field
- defines which fields are in a schema, in what order to display them and if they are currently enabled
colrefshows what the actual column is name in the datatable. A
colref = 3means that data is stored in a column named
Here is a select that will, select the most current schema versions and their currently enabled columns:
SELECT S.ts, S.tbl, T.label, SC.colref, T.ismulti FROM schemas S, schema_cols SC, types T WHERE SC.sid = S.id AND SC.tid = T.id AND SC.enabled = 1 GROUP BY S.tbl, T.label HAVING MAX(S.ts) ORDER BY S.tbl, SC.sort
Data is stored in tables named after their schema. For each schema, two tables exist to hold the data:
data_<schemaname>holds single value data
multi_<schemaname>holds multi value data
For single value field values are stored in columns named
col<colref>, where the colref integer can be read from the
schema_cols table as described above.
For multivalue field values, one row per value is used in the table with a
colref column and a
row column. The value is stored in the
All data tables have the following meta data:
- The page id this data is associated with (for page and serial data)
NULLfor global data
- A row counter for serial and global data
0for page data
- the page revision this data is associated with (for page data)
NULLfor serial and global data
- a boolean to show if this is the newest available data for this field (simplifies selects for current data)