tables.struct

This file defines the database structure used. The format is not complicated. You add a field or to this file, go to the admin part of Spot and run upgrade DB. That will compare the structure defined in the file and issue any required changes to the database with alter table or create table statements. You can also remove fields from this file and they will be removed from the database. Removing whole tables will not have them removed from the database however.

Currently there's only one tables.struct file, but it's in the process of being rewritten so it can be split into several parts to keep adaptions separate from the core tables. When it's split into several files it will be possible to add more fields to a table defined in the core, but not to delete tables or fields from the core.

The tables are defined this way <table id="tablename"> followed by the definition of fields and some other metadata. To close </table>. Fields are entered as a single <field ... /> entry. Primary keys are defined with <primarykey field="somefield"/> and other metadata not related to the database are either <display ...> that contains info on how it should be displayed on screen,<extra .../> contains additional information on how to handle the data. If the primary key consist of serveral items, they are separated by comma.

Here's an example:

<table id="sometable">
  <field id="somekey_no" type="int" notnull="yes" extra="auto_increment"/>
  <field id="descript" type="varchar" len="60" notnull="yes"/>
  <field id="cust_no" type="int" notnull="yes" references="cust_entity"/>
  <field id="rawdata" type="longblob" notnull="yes"/>
  <field id="notefield" type="text" notnull="yes"/>
  <primarykey field="somekey_no"/>
  <index id="ix1" field="cust_no">
  <display id="table" name="Some Table" width="400" height="400"/>
  <extra id="descript" view="%descript%"/>
  <extra id="type" value="Data" group="Data"/>
</table>

Display can have id=table where the name and the width/height controls the name and the size of the window to display it, or id can be one of the fields and then gives some hints on how to display that field. For fields it's possible on text fields to set rows="" and cols="" to set the size in characters how large the textarea should be. For all fields you can set the name="" field to give a better name for the field.

extra id="descript" view="" is there to allow a template to be entered that will be used to present the information stored in the record in lists and links. extra id="type" can have value="Data" | "Definition" | "Configuration". group can be either "Data" or "Config". This is used in some cases to limit searches for links to only include related information in the same group or set as the table you start from.

On fields, references is used to tell if a field references another table and what fields are used for that link. It comes in two forms, references="cust_entity" will assume that the fieldname that links are the same in both tables. If it's not it's possible to write references="cust_entity.cust_no" to make sure it links via the correct record. It's never wrong to use the dot notation.

The above example contains the minimum required for a table to work well for all uses, so x number of <field/>, one <primarykey/>, at least one <display/> and two <extra/>, one with descript and view and one with type and what group the table belongs to.

For the table grouping, the difference between definition and configuration is a bit gray, but in general definition is only used for lookup tables that doesn't affect the flow of the program. If it contains information actually used by the software and not just displayed it should be Configuration instead. The theory is that group Config information could be used to seed a new installation, while data shouldn't be moved.