Form Storage -- Entry/Last Entry: Difference between revisions
(4 intermediate revisions by 3 users not shown) | |||
Line 33: | Line 33: | ||
record [shape=record , label="table:record|{id}"] | record [shape=record , label="table:record|{id}"] | ||
last_entry [shape=record,label="table:last_entry|{record|form_field|string_value|date_value|...}"] | last_entry [shape=record,label="table:last_entry|{record|form_field|string_value|date_value|...}"] | ||
entry [shape= | entry [shape=record,label="table:entry|{record|form_field|string_value|text_value|...}"] | ||
form_field->field [label="field=id"] | form_field->field [label="field=id"] | ||
Line 62: | Line 62: | ||
JOIN form on form_field.form = form.id | JOIN form on form_field.form = form.id | ||
WHERE form.name = 'person') | WHERE form.name = 'person') | ||
[[Category: | |||
[[Category:Developer Resources]] |
Latest revision as of 20:08, 1 March 2019
This is the default storage mechanism that a form uses
Features
This entry storage mechanism tracks changes made to the data by user and time.
Database Structure
This is a vertical database structure.
Entry and Last Entry
All data is stored in two structurally identical tables, entry and last_entry. The entry table contains all data saved into the system, while last_entry only saves the current version of the data. Each row of this table corresponds to one field of one instance of a form. These references are made by the columns record and form_field which are the id's of the tables record and form_field
Record
The record table has one row for each form instance. A form instance is the name of a form and an id.
The record table contains the columns id, form, parent_form and parent_id. The form column matches the id column in the form table and which tells us which form this record, or instance, is. The parent_form column is a string and tells us the name, if any, of a parent form for this form instance. Similarly, the parent_id tells us the id, if any, of the parent form. The parent form may or may not be saved into the entry table.
The id is referenced by the entry and last_entry tables
There is also a modification time which tracks the last time any field of this form instance was modified.
Form
The form table describe the forms which have been saved into the entry table. There are columns id and name. It's id is referenced by the form field table.
Field
The field table describe all of the fields and their database types. It contains the columns: id, name and type. There is one row for each pair (name of a field, field database type) which is assigned an id. The database type, tells us which column that the field data is saved in the last_entry and entry tables. It's id column is referenced by the form_field table.
Form Field
The form field table has on row for each pair of (form, field id). It's id column is referenced by the entry and last entry tables.
Diagram
<graphviz border='frame' format='png'>
digraph "Database Structure" { form [shape=record, label="table:form|{id|name}"] field [shape=record, label="table:field|{id|name}"] form_field [shape=record,label="table:form_field|{id|form|field}"] record [shape=record , label="table:record|{id}"] last_entry [shape=record,label="table:last_entry|{record|form_field|string_value|date_value|...}"] entry [shape=record,label="table:entry|{record|form_field|string_value|text_value|...}"]
form_field->field [label="field=id"] form_field->form [label="form=id"] entry->form_field[label="form_field=id"] last_entry->form_field[label="form_field=id"] entry->record[label="record=id"] last_entry->record[label="record=id"]
} </graphviz>
Sample SQL Queries
Getting all the data for a form
Suppose you want to get all the fields for the 'person' form with id '10260.' This can be done by:
SELECT field.name AS `Field`, field.type AS `Type`, last_entry.integer_value AS `Integer`, last_entry.string_value AS `String`, last_entry.text_value AS `Text`, last_entry.date_value AS `Date` FROM last_entry JOIN form_field ON last_entry.form_field = form_field.id JOIN field ON form_field.field = field.id WHERE last_entry.record = '10260' AND last_entry.form_field IN (SELECT form_field.id FROM form_field JOIN form on form_field.form = form.id WHERE form.name = 'person')