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:

• schemas
  • 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 ts column
  • using the ts column it's easy to figure out which schema was used at a certain time
• types
  • defines the type and configuration of the fields in the schemas and contains the visible name used for this field
• schema_cols
  • defines which fields are in a schema, in what order to display them and if they are currently enabled
  • colref shows what the actual column is name in the datatable. A colref = 3 means that data is stored in a column named col3

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 value column.

All data tables have the following meta data:

• pid
  • The page id this data is associated with (for page and serial data)
  • NULL for global data
• rid
  • A row counter for serial and global data
  • 0 for page data
• rev
  • the page revision this data is associated with (for page data)
  • NULL for serial and global data
• latest
  • a boolean to show if this is the newest available data for this field (simplifies selects for current data)

NOTE: correct me if I'm wrong but it seems to me like there is a mistake in the image above. For the Serial Schema table:

the values for the field1 column should be aaa (not xyz) for RID=1 and bbb (not zyc) for RID=2, right?

Similarly,

the values for the field2 column should be xxx (not 34) for RID=1 and yyy (not 22) for RID=2, right?