Differences

This shows you the differences between two versions of the page.

--- plugin:struct:dbinternals [2022-05-05 13:37] – created andi
+++ plugin:struct:dbinternals [2024-04-22 10:23] (current) – [Data] asheenlevrai
@@ Line 2: / Line 2: @@
 ====== Database Internals ======
-The struct plugin maps the 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.
+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 map directly to tables in the database.
+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.
+===== Schema Management =====
+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:
 <code sql>
-SELECT S.ts, S.tbl, T.label, SC.colref, T.ismulti
+  SELECT S.ts, S.tbl, T.label, SC.colref, T.ismulti
-  FROM schemas S,
+    FROM schemas S,
-       schema_cols SC,
+         schema_cols SC,
-       types T
+         types T
- WHERE SC.sid = S.id
+   WHERE SC.sid = S.id
-   AND SC.tid = T.id
+     AND SC.tid = T.id
-   AND SC.enabled = 1
+     AND SC.enabled = 1
 GROUP BY S.tbl, T.label
-HAVING MAX(S.ts)
+  HAVING MAX(S.ts)
 ORDER BY S.tbl, SC.sort
-;
 </code>
-FIXME work in progress...
+===== Data =====
+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)
+{{ :plugin:struct:structinternals.png?600 }}
+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?