Database layout overview
| HelpSet Table of Contents | Back to Project Home Page |
Layout |
[ToC] |
The database layout as described in this document is in line with schema version 50.
The image below gives a graphical view of the links between the various tables of the database (click on the image to view the full-size file).
Database layout overview
For more information about the meaning of the fields in each table, their expected format and validation rules, please refer to the Edition Page manual for the object or entity in question.
Slides-related tables |
[ToC] |
slides table:
Contains all Slides records. Managed through the Slides List and Slides Edition Page.
MariaDB [hirtius]> desc slides;
+----------------+------------------+------+-----+---------------------+-------------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------------------+-------------------------------+
| obj_id | int(11) | NO | PRI | NULL | auto_increment |
| slide_id | int(11) | NO | UNI | 0 | |
| tray_id | varchar(15) | NO | UNI | | |
| location | varchar(40) | NO | MUL | | |
| country | char(2) | NO | | | |
| descript_1 | varchar(76) | NO | | NULL | |
| descript_2 | varchar(76) | YES | | NULL | |
| shot_date | datetime | YES | | NULL | |
| subj_date | varchar(30) | YES | | NULL | |
| type | char(3) | YES | | NULL | |
| can_publish | enum('yes','no') | NO | | no | |
| coord_lat_deg | tinyint(4) | YES | | NULL | |
| coord_lat_min | tinyint(4) | YES | | NULL | |
| coord_lat_sec | decimal(4,2) | YES | | NULL | |
| coord_lat_ns | enum('N','S') | YES | | NULL | |
| coord_lat_dd | decimal(11,8) | YES | | NULL | |
| coord_long_deg | tinyint(4) | YES | | NULL | |
| coord_long_min | tinyint(4) | YES | | NULL | |
| coord_long_sec | decimal(4,2) | YES | | NULL | |
| coord_long_we | enum('W','E') | YES | | NULL | |
| coord_long_dd | decimal(11,8) | YES | | NULL | |
| is_anchor | enum('yes','no') | NO | | no | |
| quality | int(11) | NO | | 3 | |
| last_modif | timestamp | NO | | current_timestamp() | on update current_timestamp() |
| last_mod_by | varchar(12) | NO | | NULL | |
| created | timestamp | NO | | 0000-00-00 00:00:00 | |
| created_by | varchar(12) | NO | | NULL | |
| cloned_from | int(11) | NO | | 0 | |
| comment | mediumtext | YES | | NULL | |
| releve | mediumtext | YES | | NULL | |
| transcript | mediumtext | YES | | NULL | |
| translation | mediumtext | YES | | NULL | |
+----------------+------------------+------+-----+---------------------+-------------------------------+
32 rows in set (0.02 sec)
mysql> show index from slides;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| slides | 0 | PRIMARY | 1 | obj_id | A | 3766 | NULL | NULL | | BTREE | |
| slides | 0 | slide_id | 1 | slide_id | A | 3766 | NULL | NULL | | BTREE | |
| slides | 0 | tray_id | 1 | tray_id | A | 3766 | NULL | NULL | | BTREE | |
| slides | 1 | location | 1 | location | A | 941 | NULL | NULL | | BTREE | |
| slides | 1 | location | 2 | country | A | 1255 | NULL | NULL | | BTREE | |
| slides | 1 | location | 3 | descript_1 | A | 3766 | NULL | NULL | | BTREE | |
| slides | 1 | location | 4 | descript_2 | A | 3766 | NULL | NULL | YES | BTREE | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
7 rows in set (0.03 sec)
obsolete_slides table:
Implements a Slide to Slide one-to-many relationship. Managed through the Slides Edition Page (obsoleted by field).
mysql> desc obsolete_slides; +------------------+-------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+-------------+------+-----+-------------------+-------+ | oid_obsoleted | int(11) | NO | PRI | NULL | | | oid_obsoleted_by | int(11) | NO | | NULL | | | created | timestamp | NO | | CURRENT_TIMESTAMP | | | created_by | varchar(12) | YES | | NULL | | +------------------+-------------+------+-----+-------------------+-------+ 4 rows in set (0.00 sec) mysql> show index from obsolete_slides; +-----------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-----------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ | obsolete_slides | 0 | PRIMARY | 1 | oid_obsoleted | A | 7 | NULL | NULL | | BTREE | | +-----------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ 1 row in set (0.00 sec)
exif_data table:
Stores a copy of the EXIF data contained in digital Slides (in order to make it searchable by Project Hirtius). Each property/value pair is stored in a separate record, linked with the Slide record it belongs to. This data is R/O in the application, and can only be uploaded through an external script for now (see Helper Scripts).
mysql> desc exif_data; +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | slide_id | int(11) | NO | MUL | NULL | | | property | varchar(120) | NO | | NULL | | | value | text | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> show index from exif_data; +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | exif_data | 0 | PRIMARY | 1 | id | A | 26303 | NULL | NULL | | BTREE | | | exif_data | 0 | slide_id | 1 | slide_id | A | NULL | NULL | NULL | | BTREE | | | exif_data | 0 | slide_id | 2 | property | A | 26303 | NULL | NULL | | BTREE | | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 3 rows in set (0.00 sec)
trays table:
Contains all Trays records. Managed through the Slide Trays List and Slide Trays Edition Page.
mysql> desc trays;
+-----------------+------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------------------+-------+
| tray_id | varchar(4) | NO | PRI | | |
| tray_name | varchar(120) | NO | | | |
| slides_per_tray | int(11) | NO | | 50 | |
| is_digital | enum('yes','no') | NO | | no | |
| hex_numbering | enum('yes','no') | NO | | no | |
| last_modif | timestamp | NO | | CURRENT_TIMESTAMP | |
| last_mod_by | varchar(12) | NO | | NULL | |
| created | timestamp | NO | | 0000-00-00 00:00:00 | |
| created_by | varchar(12) | NO | | NULL | |
| comment | mediumtext | YES | | NULL | |
+-----------------+------------------+------+-----+---------------------+-------+
10 rows in set (0.00 sec)
mysql> show index from trays;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| trays | 0 | PRIMARY | 1 | tray_id | A | 64 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
Sources-related tables |
[ToC] |
sources table:
Contains all Sources records. Managed through the Sources List and Sources Edition Page.
MariaDB [hirtius]> desc sources;
+----------------+------------------+------+-----+---------------------+-------------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------------------+-------------------------------+
| obj_id | int(11) | NO | PRI | NULL | auto_increment |
| author | varchar(120) | YES | MUL | NULL | |
| title | varchar(400) | NO | | NULL | |
| subtitle | varchar(400) | YES | | NULL | |
| ms_ref | varchar(60) | YES | | NULL | |
| available_at | int(11) | NO | | NULL | |
| source_type_id | int(11) | NO | | NULL | |
| subj_date | varchar(30) | YES | | NULL | |
| can_publish | enum('yes','no') | NO | | no | |
| url | varchar(400) | YES | | NULL | |
| comment | mediumtext | YES | | NULL | |
| reading | mediumtext | YES | | NULL | |
| transcript | mediumtext | YES | | NULL | |
| translation | mediumtext | YES | | NULL | |
| last_modif | timestamp | NO | | current_timestamp() | on update current_timestamp() |
| last_mod_by | varchar(12) | NO | | NULL | |
| created | timestamp | NO | | 0000-00-00 00:00:00 | |
| created_by | varchar(12) | NO | | NULL | |
| cloned_from | int(11) | NO | | 0 | |
+----------------+------------------+------+-----+---------------------+-------------------------------+
19 rows in set (0.00 sec)
mysql> show index from sources;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| sources | 0 | PRIMARY | 1 | obj_id | A | 15 | NULL | NULL | | BTREE | |
| sources | 1 | author | 1 | author | A | 15 | NULL | NULL | YES | BTREE | |
| sources | 1 | author | 2 | title | A | 15 | 255 | NULL | | BTREE | |
| sources | 1 | author | 3 | subtitle | A | 15 | 255 | NULL | YES | BTREE | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.00 sec)
source_types table:
Contains all Source types records. Managed through the Source Types Management Page.
mysql> desc source_types;
+------------------+------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+-------------------+----------------+
| source_type_id | int(11) | NO | PRI | NULL | auto_increment |
| source_type_code | char(4) | NO | UNI | NULL | |
| source_type | varchar(60) | NO | UNI | NULL | |
| date_in_ref | enum('yes','no') | NO | | no | |
| last_modif | timestamp | NO | | CURRENT_TIMESTAMP | |
| last_mod_by | varchar(12) | NO | | NULL | |
| created | datetime | NO | | NULL | |
| created_by | varchar(12) | NO | | NULL | |
+------------------+------------------+------+-----+-------------------+----------------+
8 rows in set (0.00 sec)
mysql> show index from source_types;
+--------------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| source_types | 0 | PRIMARY | 1 | source_type_id | A | 6 | NULL | NULL | | BTREE | |
| source_types | 0 | source_type | 1 | source_type | A | 6 | NULL | NULL | | BTREE | |
| source_types | 0 | source_type_code | 1 | source_type_code | A | 6 | NULL | NULL | | BTREE | |
+--------------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)
srcref table:
Contains all Srcref records (links to Sources for the purpose of quoting them). Managed through the Object - Source Ref. Edition Page.
mysql> desc srcref; +-------------+-------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------------------+----------------+ | ref_id | int(11) | NO | PRI | NULL | auto_increment | | obj_id | int(11) | NO | | NULL | | | obj_type | char(3) | NO | | NULL | | | source_id | int(11) | NO | | NULL | | | quote_ref | varchar(60) | YES | | NULL | | | comment | mediumtext | YES | | NULL | | | last_modif | timestamp | NO | | CURRENT_TIMESTAMP | | | last_mod_by | varchar(12) | NO | | NULL | | | created | timestamp | NO | | 0000-00-00 00:00:00 | | | created_by | varchar(12) | NO | | NULL | | +-------------+-------------+------+-----+---------------------+----------------+ 10 rows in set (0.00 sec) mysql> show index from srcref; +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | srcref | 0 | PRIMARY | 1 | ref_id | A | 39 | NULL | NULL | | BTREE | | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 1 row in set (0.00 sec)
Events-related tables |
[ToC] |
events table:
Contains all Events records. Managed through the Events List and Events Edition Page.
MariaDB [hirtius]> desc events;
+----------------+------------------+------+-----+---------------------+-------------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------------------+-------------------------------+
| obj_id | int(11) | NO | PRI | NULL | auto_increment |
| location | varchar(40) | NO | MUL | NULL | |
| country | char(2) | NO | | NULL | |
| descript_1 | varchar(76) | NO | | NULL | |
| descript_2 | varchar(76) | YES | | NULL | |
| date_start | varchar(12) | YES | | NULL | |
| era_start | enum('J','G') | YES | | NULL | |
| date_s_key | int(11) | NO | MUL | -2147483648 | |
| date_end | varchar(12) | YES | | NULL | |
| era_end | enum('J','G') | YES | | NULL | |
| date_e_key | int(11) | YES | | NULL | |
| event_type_id | int(11) | NO | | NULL | |
| is_auto | enum('yes','no') | NO | | no | |
| owner_obj_id | int(11) | NO | | 0 | |
| owner_obj_type | char(3) | NO | | | |
| comment | mediumtext | YES | | NULL | |
| last_modif | timestamp | NO | | current_timestamp() | on update current_timestamp() |
| last_mod_by | varchar(12) | NO | | NULL | |
| created | timestamp | NO | | 0000-00-00 00:00:00 | |
| created_by | varchar(12) | NO | | NULL | |
| cloned_from | int(11) | NO | | 0 | |
+----------------+------------------+------+-----+---------------------+-------------------------------+
21 rows in set (0.01 sec)
MariaDB [hirtius]> show index from events;
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| events | 0 | PRIMARY | 1 | obj_id | A | 47 | NULL | NULL | | BTREE | | |
| events | 1 | location | 1 | location | A | 47 | NULL | NULL | | BTREE | | |
| events | 1 | location | 2 | country | A | 47 | NULL | NULL | | BTREE | | |
| events | 1 | location | 3 | descript_1 | A | 47 | NULL | NULL | | BTREE | | |
| events | 1 | location | 4 | descript_2 | A | 47 | NULL | NULL | YES | BTREE | | |
| events | 1 | date_s_key | 1 | date_s_key | A | 23 | NULL | NULL | | BTREE | | |
| events | 1 | date_s_key | 2 | obj_id | A | 47 | NULL | NULL | | BTREE | | |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.08 sec)
event_types table:
Contains all Event types records. Managed through the Event Types Management Page.
mysql> desc event_types;
+-----------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------------------+----------------+
| event_type_id | int(11) | NO | PRI | NULL | auto_increment |
| event_type_name | varchar(60) | NO | UNI | NULL | |
| event_type_code | char(4) | NO | UNI | NULL | |
| description | varchar(200) | YES | | NULL | |
| has_duration | enum('yes','no') | NO | | no | |
| is_gedcom | enum('yes','no') | NO | | no | |
| required | enum('yes','no') | NO | | no | |
| last_modif | timestamp | NO | | CURRENT_TIMESTAMP | |
| last_mod_by | varchar(12) | NO | | NULL | |
| created | timestamp | NO | | 0000-00-00 00:00:00 | |
| created_by | varchar(12) | NO | | NULL | |
+-----------------+------------------+------+-----+---------------------+----------------+
11 rows in set (0.00 sec)
mysql> show index from event_types;
+-------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| event_types | 0 | PRIMARY | 1 | event_type_id | A | 31 | NULL | NULL | | BTREE | |
| event_types | 0 | event_type_name | 1 | event_type_name | A | 31 | NULL | NULL | | BTREE | |
| event_types | 0 | event_type_code | 1 | event_type_code | A | 31 | NULL | NULL | | BTREE | |
+-------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)
obj_evt_map table:
Implements a many-to-many relationship between Events and all other supported fully-fledged object types. Managed through the Object - Event Edition Page.
mysql> desc obj_evt_map; +----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------+------+-----+---------+-------+ | obj_id | int(11) | NO | PRI | 0 | | | obj_type | char(3) | NO | PRI | NULL | | | event_id | int(11) | NO | PRI | 0 | | +----------+---------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> show index from obj_evt_map; +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | obj_evt_map | 0 | obj_id | 1 | obj_id | A | 116 | NULL | NULL | | BTREE | | | obj_evt_map | 0 | obj_id | 2 | obj_type | A | 116 | NULL | NULL | | BTREE | | | obj_evt_map | 0 | obj_id | 3 | event_id | A | 116 | NULL | NULL | | BTREE | | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 3 rows in set (0.00 sec)
rel_evt_map table:
Implements a many-to-many relationship between different Event records for the purpose of relative dating. Managed through the Events Edition Page and Event Selector.
mysql> desc rel_evt_map;
+-------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| older_event | int(11) | NO | PRI | NULL | |
| newer_event | int(11) | NO | PRI | NULL | |
| is_contemp | enum('yes','no') | NO | | no | |
+-------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> show index from rel_evt_map;
+-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| rel_evt_map | 0 | older_event | 1 | older_event | A | 7 | NULL | NULL | | BTREE | |
| rel_evt_map | 0 | older_event | 2 | newer_event | A | 7 | NULL | NULL | | BTREE | |
+-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)
sequences table:
Contains all Sequence records. Managed through the Sequences Management Page.
mysql> desc sequences; +-------------+-------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------------------+----------------+ | obj_id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(80) | NO | UNI | NULL | | | comment | mediumtext | YES | | NULL | | | last_modif | timestamp | NO | | CURRENT_TIMESTAMP | | | last_mod_by | varchar(12) | NO | | NULL | | | created | timestamp | NO | | 0000-00-00 00:00:00 | | | created_by | varchar(12) | NO | | NULL | | +-------------+-------------+------+-----+---------------------+----------------+ 7 rows in set (0.00 sec) mysql> show index from sequences; +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | sequences | 0 | PRIMARY | 1 | obj_id | A | 6 | NULL | NULL | | BTREE | | | sequences | 0 | name | 1 | name | A | 6 | NULL | NULL | | BTREE | | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec)
seq_evt_map table:
Implements a many-to-many relationship between Sequence and Event records (for sequence membership). Managed through the Sequences Management Page and Event Selector.
mysql> desc seq_evt_map; +----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------+------+-----+---------+-------+ | seq_id | int(11) | NO | PRI | NULL | | | event_id | int(11) | NO | PRI | NULL | | +----------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> show index from seq_evt_map; +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | seq_evt_map | 0 | seq_id | 1 | seq_id | A | 14 | NULL | NULL | | BTREE | | | seq_evt_map | 0 | seq_id | 2 | event_id | A | 29 | NULL | NULL | | BTREE | | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec)
Individuals-related tables |
[ToC] |
individuals table:
Contains all Individual records. Managed through the Individuals List and Individuals Edition Page.
mysql> desc individuals;
+--------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------------------+----------------+
| obj_id | int(11) | NO | PRI | NULL | auto_increment |
| prefix | varchar(15) | YES | | NULL | |
| first_name | varchar(60) | YES | | NULL | |
| middle_names | varchar(80) | YES | | NULL | |
| last_name | varchar(120) | NO | MUL | NULL | |
| cognomen | varchar(80) | YES | | NULL | |
| suffix | varchar(15) | YES | | NULL | |
| nickname | varchar(40) | YES | | NULL | |
| sex | enum('M','F') | NO | | NULL | |
| is_alive | enum('yes','no') | NO | | no | |
| birth_evt_id | int(11) | NO | | 0 | |
| death_evt_id | int(11) | NO | | 0 | |
| family_id | int(11) | NO | | 0 | |
| comment | mediumtext | YES | | NULL | |
| last_modif | timestamp | NO | | CURRENT_TIMESTAMP | |
| last_mod_by | varchar(12) | NO | | NULL | |
| created | timestamp | NO | | 0000-00-00 00:00:00 | |
| created_by | varchar(12) | NO | | NULL | |
| cloned_from | int(11) | NO | | 0 | |
+--------------+------------------+------+-----+---------------------+----------------+
19 rows in set (0.00 sec)
mysql> show index from individuals;
+-------------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| individuals | 0 | PRIMARY | 1 | obj_id | A | 25 | NULL | NULL | | BTREE | |
| individuals | 1 | last_name | 1 | last_name | A | 25 | NULL | NULL | | BTREE | |
| individuals | 1 | last_name | 2 | cognomen | A | 25 | NULL | NULL | YES | BTREE | |
| individuals | 1 | last_name | 3 | first_name | A | 25 | NULL | NULL | YES | BTREE | |
| individuals | 1 | last_name | 4 | middle_names | A | 25 | NULL | NULL | YES | BTREE | |
+-------------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)
alternate_names table:
Contains all Alternate name records. Managed through the Alternate Names Edition Page.
mysql> desc alternate_names; +-----------------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+-------------------+----------------+ | obj_id | int(11) | NO | PRI | NULL | auto_increment | | indiv_id | int(11) | NO | MUL | NULL | | | alt_name | varchar(200) | NO | | NULL | | | altname_type_id | int(11) | NO | | NULL | | | comment | mediumtext | YES | | NULL | | | last_modif | timestamp | NO | | CURRENT_TIMESTAMP | | | last_mod_by | varchar(12) | NO | | NULL | | | created | datetime | NO | | NULL | | | created_by | varchar(12) | NO | | NULL | | +-----------------+--------------+------+-----+-------------------+----------------+ 9 rows in set (0.00 sec) mysql> show index from alternate_names; +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | alternate_names | 0 | PRIMARY | 1 | obj_id | A | 11 | NULL | NULL | | BTREE | | | alternate_names | 0 | indiv_id | 1 | indiv_id | A | 11 | NULL | NULL | | BTREE | | | alternate_names | 0 | indiv_id | 2 | alt_name | A | 11 | NULL | NULL | | BTREE | | +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 3 rows in set (0.00 sec)
altname_types table:
Contains all Alternate name type records. Managed through the Alternate Name Types Management Page.
mysql> desc altname_types; +-------------------+-------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+-------------+------+-----+-------------------+----------------+ | altname_type_id | int(11) | NO | PRI | NULL | auto_increment | | altname_type_code | char(4) | NO | UNI | NULL | | | altname_type_name | varchar(60) | NO | UNI | NULL | | | last_modif | timestamp | NO | | CURRENT_TIMESTAMP | | | last_mod_by | varchar(12) | NO | | NULL | | | created | datetime | NO | | NULL | | | created_by | varchar(12) | NO | | NULL | | +-------------------+-------------+------+-----+-------------------+----------------+ 7 rows in set (0.00 sec) mysql> show index from altname_types; +---------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ | altname_types | 0 | PRIMARY | 1 | altname_type_id | A | 8 | NULL | NULL | | BTREE | | | altname_types | 0 | altname_type_code | 1 | altname_type_code | A | 8 | NULL | NULL | | BTREE | | | altname_types | 0 | altname_type_name | 1 | altname_type_name | A | 8 | NULL | NULL | | BTREE | | +---------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+ 3 rows in set (0.00 sec)
titles table:
Contains all Titles records. Managed through the Titles Edition Page.
MariaDB [hirtius]> desc titles;
+-------------+------------------+------+-----+---------------------+-------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------------------+-------------------------------+
| obj_id | int(11) | NO | PRI | NULL | auto_increment |
| indiv_id | int(11) | NO | MUL | NULL | |
| title | varchar(200) | NO | | NULL | |
| is_nobility | enum('yes','no') | NO | | no | |
| as_of | varchar(12) | YES | | NULL | |
| as_of_era | enum('J','G') | YES | | NULL | |
| as_of_key | int(11) | NO | | -2147483648 | |
| comment | mediumtext | YES | | NULL | |
| last_modif | timestamp | NO | | current_timestamp() | on update current_timestamp() |
| last_mod_by | varchar(12) | NO | | NULL | |
| created | datetime | NO | | NULL | |
| created_by | varchar(12) | NO | | NULL | |
+-------------+------------------+------+-----+---------------------+-------------------------------+
12 rows in set (0.00 sec)
mysql> show index from titles;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| titles | 0 | PRIMARY | 1 | obj_id | A | 10 | NULL | NULL | | BTREE | |
| titles | 0 | indiv_id | 1 | indiv_id | A | 10 | NULL | NULL | | BTREE | |
| titles | 0 | indiv_id | 2 | title | A | 10 | NULL | NULL | | BTREE | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)
Families-related tables |
[ToC] |
families table:
Contains all Family records. Managed through the Families List and Families Edition Page.
mysql> desc families; +----------------+-------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------+------+-----+-------------------+----------------+ | obj_id | int(11) | NO | PRI | NULL | auto_increment | | male_partner | int(11) | NO | MUL | 0 | | | female_partner | int(11) | NO | MUL | 0 | | | crea_evt_id | int(11) | NO | | 0 | | | comment | mediumtext | YES | | NULL | | | last_modif | timestamp | NO | | CURRENT_TIMESTAMP | | | last_mod_by | varchar(12) | NO | | NULL | | | created | datetime | NO | | NULL | | | created_by | varchar(12) | NO | | NULL | | +----------------+-------------+------+-----+-------------------+----------------+ 9 rows in set (0.01 sec) mysql> show index from families; +----------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +----------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+ | families | 0 | PRIMARY | 1 | obj_id | A | 8 | NULL | NULL | | BTREE | | | families | 1 | male_partner | 1 | male_partner | A | 8 | NULL | NULL | | BTREE | | | families | 1 | female_partner | 1 | female_partner | A | 8 | NULL | NULL | | BTREE | | +----------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+ 3 rows in set (0.00 sec)
Tiddlers-related tables |
[ToC] |
tiddlers table:
Contains all Tiddler records. Managed through the Projects List and Tiddlers (Embedded TiddlyWiki).
mysql> desc tiddlers;
+-------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------------------+----------------+
| obj_id | int(11) | NO | PRI | NULL | auto_increment |
| title | varchar(255) | NO | | | |
| body | mediumtext | NO | | NULL | |
| is_infra | enum('yes','no') | NO | | no | |
| last_modif | timestamp | NO | | CURRENT_TIMESTAMP | |
| last_mod_by | varchar(12) | NO | | NULL | |
| created | timestamp | NO | | 0000-00-00 00:00:00 | |
| created_by | varchar(12) | NO | | NULL | |
| version | int(11) | NO | | 0 | |
| tags | varchar(128) | NO | | | |
| proj_id | int(11) | NO | MUL | 0 | |
+-------------+------------------+------+-----+---------------------+----------------+
11 rows in set (0.00 sec)
mysql> show index from tiddlers;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tiddlers | 0 | PRIMARY | 1 | obj_id | A | 39 | NULL | NULL | | BTREE | |
| tiddlers | 0 | proj_id | 1 | proj_id | A | 4 | NULL | NULL | | BTREE | |
| tiddlers | 0 | proj_id | 2 | title | A | 39 | NULL | NULL | | BTREE | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)
tiddler_versions table:
Contains all past versions of Tiddler records. Managed through the Tiddlers (Embedded TiddlyWiki).
mysql> desc tiddler_versions;
+-------------+------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+-------------------+----------------+
| obj_id | int(11) | NO | PRI | NULL | auto_increment |
| orig_id | int(11) | NO | | 0 | |
| title | varchar(255) | NO | | | |
| body | mediumtext | NO | | NULL | |
| is_infra | enum('yes','no') | NO | | no | |
| last_modif | timestamp | NO | | CURRENT_TIMESTAMP | |
| last_mod_by | varchar(12) | NO | | NULL | |
| version | int(11) | NO | | 0 | |
| tags | varchar(128) | NO | | | |
| proj_id | int(11) | NO | | 0 | |
+-------------+------------------+------+-----+-------------------+----------------+
10 rows in set (0.01 sec)
mysql> show index from tiddler_versions;
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tiddler_versions | 0 | PRIMARY | 1 | obj_id | A | 105 | NULL | NULL | | BTREE | |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
projects table:
Contains all Project records. Managed through the Projects List and Projects Edition Page.
mysql> desc projects;
+-----------------------+------------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+------------------+------+-----+---------------------+-----------------------------+
| obj_id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(40) | NO | UNI | NULL | |
| short_desc | varchar(120) | YES | | NULL | |
| struct_container_node | varchar(120) | YES | | NULL | |
| content_lang | char(2) | NO | | en | |
| is_closed | enum('yes','no') | NO | | no | |
| comment | mediumtext | YES | | NULL | |
| last_modif | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| last_mod_by | varchar(12) | NO | | NULL | |
| created | timestamp | NO | | 0000-00-00 00:00:00 | |
| created_by | varchar(12) | NO | | NULL | |
+-----------------------+------------------+------+-----+---------------------+-----------------------------+
11 rows in set (0.00 sec)
mysql> show index from projects;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| projects | 0 | PRIMARY | 1 | obj_id | A | 2 | NULL | NULL | | BTREE | |
| projects | 0 | name | 1 | name | A | 2 | NULL | NULL | | BTREE | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.01 sec)
Books, Articles and Weblinks-related tables |
[ToC] |
books table:
Contains all Book records. Managed through the Books and Articles List and Books Edition Page.
mysql> desc books;
+---------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------------------+----------------+
| book_id | int(11) | NO | PRI | NULL | auto_increment |
| author | varchar(120) | YES | MUL | NULL | |
| title | varchar(400) | NO | | NULL | |
| subtitle | varchar(400) | YES | | NULL | |
| place | varchar(80) | YES | | NULL | |
| editor | varchar(120) | YES | | NULL | |
| edition | varchar(10) | YES | | NULL | |
| pub_year | smallint(6) | YES | | NULL | |
| is_periodical | enum('yes','no') | NO | | no | |
| available_at | int(11) | NO | | NULL | |
| udc_tag | varchar(40) | YES | | NULL | |
| url | varchar(400) | YES | | NULL | |
| last_modif | timestamp | NO | | CURRENT_TIMESTAMP | |
| last_mod_by | varchar(12) | NO | | NULL | |
| created | timestamp | NO | | 0000-00-00 00:00:00 | |
| created_by | varchar(12) | NO | | NULL | |
| comment | mediumtext | YES | | NULL | |
+---------------+------------------+------+-----+---------------------+----------------+
17 rows in set (0.00 sec)
mysql> show index from books;
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| books | 0 | PRIMARY | 1 | book_id | A | 28 | NULL | NULL | | BTREE | |
| books | 1 | book_author_title | 1 | author | A | 28 | NULL | NULL | YES | BTREE | |
| books | 1 | book_author_title | 2 | title | A | 28 | 255 | NULL | | BTREE | |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)
articles table:
Contains all Article records. Managed through the Books and Articles List and Article Edition Page.
mysql> desc articles; +---------------+--------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------------------+----------------+ | article_id | int(11) | NO | PRI | NULL | auto_increment | | author | varchar(120) | YES | | NULL | | | title | varchar(400) | NO | | NULL | | | in_book | int(11) | NO | | NULL | | | in_volume | varchar(10) | YES | | NULL | | | in_issue | varchar(10) | YES | | NULL | | | in_issue_date | varchar(20) | YES | | NULL | | | page_start | smallint(6) | NO | | NULL | | | page_end | smallint(6) | NO | | NULL | | | url | varchar(400) | YES | | NULL | | | last_modif | timestamp | NO | | CURRENT_TIMESTAMP | | | last_mod_by | varchar(12) | NO | | NULL | | | created | timestamp | NO | | 0000-00-00 00:00:00 | | | created_by | varchar(12) | NO | | NULL | | | comment | mediumtext | YES | | NULL | | +---------------+--------------+------+-----+---------------------+----------------+ 15 rows in set (0.00 sec) mysql> show index from articles; +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | articles | 0 | PRIMARY | 1 | article_id | A | 71 | NULL | NULL | | BTREE | | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 1 row in set (0.00 sec)
weblinks table:
Contains all Weblink records. Managed through the Weblinks List and Weblinks Edition Page.
mysql> desc weblinks;
+---------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------------------+----------------+
| link_id | int(11) | NO | PRI | NULL | auto_increment |
| title | varchar(400) | NO | MUL | NULL | |
| pub_year | smallint(6) | YES | | NULL | |
| url | varchar(400) | NO | | NULL | |
| link_is_query | enum('yes','no') | NO | | no | |
| is_alive | enum('yes','no') | NO | | yes | |
| last_seen | datetime | YES | | NULL | |
| last_modif | timestamp | NO | | CURRENT_TIMESTAMP | |
| last_mod_by | varchar(12) | NO | | NULL | |
| created | datetime | NO | | 0000-00-00 00:00:00 | |
| created_by | varchar(12) | NO | | NULL | |
| comment | mediumtext | YES | | NULL | |
+---------------+------------------+------+-----+---------------------+----------------+
12 rows in set (0.00 sec)
mysql> show index from weblinks;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| weblinks | 0 | PRIMARY | 1 | link_id | A | 261 | NULL | NULL | | BTREE | |
| weblinks | 1 | title | 1 | title | A | 261 | 255 | NULL | | BTREE | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.01 sec)
bibref table:
Contains all Bibref records (links to Books, Articles or Weblinks for the purpose of quoting them). Managed through the Object - Bib. Ref. Edition Page.
mysql> desc bibref; +----------------+--------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------------------+----------------+ | ref_id | int(11) | NO | PRI | NULL | auto_increment | | obj_id | int(11) | NO | | NULL | | | obj_type | char(3) | NO | | NULL | | | doc_id | int(11) | NO | | NULL | | | doc_type | char(3) | NO | | NULL | | | word_ref | varchar(120) | YES | | NULL | | | query_term | varchar(255) | YES | | NULL | | | rec_name | varchar(120) | YES | | NULL | | | rec_date | datetime | YES | | NULL | | | page_ref_start | smallint(6) | YES | | NULL | | | page_ref_end | smallint(6) | YES | | NULL | | | last_modif | timestamp | NO | | CURRENT_TIMESTAMP | | | last_mod_by | varchar(12) | NO | | NULL | | | created | timestamp | NO | | 0000-00-00 00:00:00 | | | created_by | varchar(12) | NO | | NULL | | | comment | mediumtext | YES | | NULL | | +----------------+--------------+------+-----+---------------------+----------------+ 16 rows in set (0.00 sec) mysql> show index from bibref; +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | bibref | 0 | PRIMARY | 1 | ref_id | A | 1107 | NULL | NULL | | BTREE | | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 1 row in set (0.00 sec)
libraries table:
Contains all Library records. Managed through the Libraries List and Libraries Edition Page.
mysql> desc libraries; +-------------+--------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------------------+----------------+ | library_id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(120) | NO | | NULL | | | address | varchar(120) | YES | | NULL | | | city | varchar(80) | YES | | NULL | | | zip | varchar(20) | YES | | NULL | | | country | char(2) | NO | | NULL | | | website | mediumtext | YES | | NULL | | | catalogue | mediumtext | YES | | NULL | | | last_modif | timestamp | NO | | CURRENT_TIMESTAMP | | | last_mod_by | varchar(12) | NO | | NULL | | | created | timestamp | NO | | 0000-00-00 00:00:00 | | | created_by | varchar(12) | NO | | NULL | | | comment | mediumtext | YES | | NULL | | +-------------+--------------+------+-----+---------------------+----------------+ 13 rows in set (0.01 sec) mysql> show index from libraries; +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | libraries | 0 | PRIMARY | 1 | library_id | A | 8 | NULL | NULL | | BTREE | | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 1 row in set (0.00 sec)
lib_cards table:
Contains all Library card records. Managed through the Library Cards List and Library Cards Edition Page.
mysql> desc lib_cards;
+-------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------------------+----------------+
| card_id | int(11) | NO | PRI | NULL | auto_increment |
| userid | varchar(12) | NO | | NULL | |
| library_id | int(11) | NO | MUL | NULL | |
| card_ref | varchar(40) | NO | | NULL | |
| comment | mediumtext | YES | | NULL | |
| watch_it | enum('yes','no') | NO | | yes | |
| last_modif | timestamp | NO | | CURRENT_TIMESTAMP | |
| last_mod_by | varchar(12) | NO | | NULL | |
| created | datetime | NO | | 0000-00-00 00:00:00 | |
| created_by | varchar(12) | NO | | NULL | |
+-------------+------------------+------+-----+---------------------+----------------+
10 rows in set (0.00 sec)
mysql> show index from lib_cards;
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| lib_cards | 0 | PRIMARY | 1 | card_id | A | 6 | NULL | NULL | | BTREE | |
| lib_cards | 0 | library_id | 1 | library_id | A | 6 | NULL | NULL | | BTREE | |
| lib_cards | 0 | library_id | 2 | card_ref | A | 6 | NULL | NULL | | BTREE | |
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)
lc_renewals table:
Contains all Library card renewal records. Managed through the Library Cards Edition Page and Library Card Renewal Edition Page.
mysql> desc lc_renewals; +-------------+--------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------------------+----------------+ | renewal_id | int(11) | NO | PRI | NULL | auto_increment | | card_id | int(11) | NO | | NULL | | | valid_from | datetime | NO | | NULL | | | valid_until | datetime | NO | | NULL | | | fee | decimal(9,2) | YES | | 0.00 | | | currency | char(3) | YES | | NULL | | | comment | mediumtext | YES | | NULL | | | last_modif | timestamp | NO | | CURRENT_TIMESTAMP | | | last_mod_by | varchar(12) | NO | | NULL | | | created | datetime | NO | | 0000-00-00 00:00:00 | | | created_by | varchar(12) | NO | | NULL | | +-------------+--------------+------+-----+---------------------+----------------+ 11 rows in set (0.01 sec) mysql> show index from lc_renewals; +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | lc_renewals | 0 | PRIMARY | 1 | renewal_id | A | 9 | NULL | NULL | | BTREE | | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 1 row in set (0.00 sec)
INXtree-related tables |
[ToC] |
inxtree table:
Contains all Category records that together build your INXtree. Managed through the Main Application Menu, INXtree Browser and Categories Edition Page.
mysql> desc inxtree; +-------------+--------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+-------------------+-------+ | tag | varchar(120) | NO | PRI | | | | inode | varchar(70) | NO | MUL | | | | comment | mediumtext | YES | | NULL | | | last_modif | timestamp | NO | | CURRENT_TIMESTAMP | | | last_mod_by | varchar(12) | NO | | NULL | | | created | datetime | NO | | NULL | | | created_by | varchar(12) | NO | | NULL | | +-------------+--------------+------+-----+-------------------+-------+ 7 rows in set (0.00 sec) mysql> show index from inxtree; +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | inxtree | 0 | PRIMARY | 1 | tag | A | 1671 | NULL | NULL | | BTREE | | | inxtree | 1 | inode | 1 | inode | A | 1671 | NULL | NULL | | BTREE | | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec)
inxlink table:
Implements a many-to-many relationship between Categories from your INXtree and all other supported fully-fledged object types. Managed through the Object - Category Edition Page.
MariaDB [hirtius]> desc inxlink; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | tag | varchar(120) | NO | PRI | | | | obj_type | char(3) | NO | PRI | | | | obj_id | int(11) | NO | PRI | 0 | | | sortkey | int(11) | NO | | 0 | | +----------+--------------+------+-----+---------+-------+ 4 rows in set (0.03 sec) MariaDB [hirtius]> show index from inxlink; +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | inxlink | 0 | tag | 1 | tag | A | 3144 | NULL | NULL | | BTREE | | | | inxlink | 0 | tag | 2 | obj_type | A | 3144 | NULL | NULL | | BTREE | | | | inxlink | 0 | tag | 3 | obj_id | A | 9434 | NULL | NULL | | BTREE | | | | inxlink | 1 | tag_and_sk | 1 | tag | A | 3144 | NULL | NULL | | BTREE | | | | inxlink | 1 | tag_and_sk | 2 | sortkey | A | 3144 | NULL | NULL | | BTREE | | | +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 5 rows in set (0.02 sec)
Generic metadata tables |
[ToC] |
attachments table:
Contains all Attachment records. Managed through the Attached Files List and Object - Attached File Edition Page.
mysql> desc attachments; +-------------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+-------------------+----------------+ | attach_id | mediumint(9) | NO | PRI | NULL | auto_increment | | obj_id | int(11) | NO | MUL | 0 | | | obj_type | char(3) | NO | | | | | last_modif | timestamp | NO | | CURRENT_TIMESTAMP | | | last_mod_by | varchar(12) | NO | | NULL | | | creation_ts | datetime | NO | | NULL | | | description | mediumtext | NO | | NULL | | | mimetype | mediumtext | NO | | NULL | | | filename | mediumtext | NO | | NULL | | | filesize | int(11) | YES | MUL | NULL | | | thedata | longblob | NO | | NULL | | | submitter | varchar(12) | NO | | | | +-------------+--------------+------+-----+-------------------+----------------+ 12 rows in set (0.00 sec) mysql> show index from attachments; +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | attachments | 0 | PRIMARY | 1 | attach_id | A | 1687 | NULL | NULL | | BTREE | | | attachments | 1 | obj_id | 1 | obj_id | A | 421 | NULL | NULL | | BTREE | | | attachments | 1 | obj_id | 2 | obj_type | A | 421 | NULL | NULL | | BTREE | | | attachments | 1 | obj_id | 3 | creation_ts | A | 562 | NULL | NULL | | BTREE | | | attachments | 1 | filesize | 1 | filesize | A | 562 | NULL | NULL | YES | BTREE | | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 5 rows in set (0.00 sec)
country_codes table:
Contains all Country Code records. Managed through the Country Codes Management Page.
mysql> desc country_codes; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | country | varchar(50) | NO | MUL | | | | code_a2 | char(2) | NO | MUL | | | | code_a3 | char(3) | NO | | | | | code_num | int(11) | NO | | 0 | | +----------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> show index from country_codes; +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | country_codes | 1 | country | 1 | country | A | 241 | NULL | NULL | | BTREE | | | country_codes | 1 | code_a2 | 1 | code_a2 | A | 241 | NULL | NULL | | BTREE | | +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.01 sec)
see_also table:
Implements a many-to-many relationship between any pair of supported fully-fledged objects. Managed through the Edition Page for the object type in question.
mysql> desc see_also; +------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------+------+-----+---------+-------+ | obj_id_1 | int(11) | NO | | 0 | | | obj_type_1 | char(3) | NO | | | | | obj_id_2 | int(11) | NO | | 0 | | | obj_type_2 | char(3) | NO | | | | +------------+---------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> show index from see_also; +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | see_also | 0 | obj_id_1 | 1 | obj_id_1 | A | 1627 | NULL | NULL | | BTREE | | | see_also | 0 | obj_id_1 | 2 | obj_type_1 | A | 1627 | NULL | NULL | | BTREE | | | see_also | 0 | obj_id_1 | 3 | obj_id_2 | A | 1627 | NULL | NULL | | BTREE | | | see_also | 0 | obj_id_1 | 4 | obj_type_2 | A | 1627 | NULL | NULL | | BTREE | | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 4 rows in set (0.00 sec)
Administrative tables |
[ToC] |
db_info table:
Contains a single record that acts as the "ID card" for the database. It's contents can be examined through the Database Statistics Page.
mysql> desc db_info; +--------------------+-------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+-------------+------+-----+-------------------+-------+ | schema_version | smallint(6) | NO | | 0 | | | data_last_modified | timestamp | NO | | CURRENT_TIMESTAMP | | | last_synchro | datetime | YES | | NULL | | +--------------------+-------------+------+-----+-------------------+-------+ 3 rows in set (0.00 sec) mysql> show index from db_info; Empty set (0.00 sec)
Please note: The third column ('last_synchro') is not used yet and has been included for future extension.
users table:
Contains all User records (Project Hirtius application front-end user account definitions). Also stores the user's preferences. Managed through the Users List and Users Edition Page.
MariaDB [hirtius]> desc users;
+-----------------+-------------------+------+-----+---------------------+-------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------------+------+-----+---------------------+-------------------------------+
| userid | varchar(12) | NO | PRI | | |
| fullname | varchar(120) | NO | | NULL | |
| level | enum('A','W','R') | NO | | R | |
| email | varchar(120) | YES | | NULL | |
| disabled | enum('Y','N') | NO | | N | |
| thsd_sep | int(11) | NO | | 32 | |
| decm_sep | int(11) | NO | | 44 | |
| prefd_ebh | int(11) | NO | | 15 | |
| query_limit | int(11) | NO | | 100 | |
| context_length | int(11) | NO | | 60 | |
| def_evt_type_id | int(11) | NO | | 0 | |
| hide_closed | enum('Y','N') | NO | | Y | |
| col_list_DIA | varchar(255) | YES | | NULL | |
| col_list_EVT | varchar(255) | YES | | NULL | |
| col_list_SRC | varchar(255) | YES | | NULL | |
| col_list_IND | varchar(255) | YES | | NULL | |
| col_list_FAM | varchar(255) | YES | | NULL | |
| col_list_TID | varchar(255) | YES | | NULL | |
| modified | timestamp | NO | | current_timestamp() | on update current_timestamp() |
| mod_by | varchar(12) | NO | | NULL | |
| created | timestamp | NO | | 0000-00-00 00:00:00 | |
| created_by | varchar(12) | NO | | NULL | |
| comment | mediumtext | YES | | NULL | |
+-----------------+-------------------+------+-----+---------------------+-------------------------------+
23 rows in set (0.00 sec)
mysql> show index from users;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| users | 0 | PRIMARY | 1 | userid | A | 5 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
event_log table:
Contains an immutable record of all significant actions taken by users inside the application. Can be consulted through the Event Log Viewer.
MariaDB [hirtius]> desc event_log; +-------------+--------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | who | varchar(12) | NO | | | | | ev_time | timestamp | NO | | current_timestamp() | | | severity | tinyint(4) | NO | | 0 | | | message | text | NO | | NULL | | | client_addr | varchar(255) | NO | | | | | client_ua | varchar(255) | NO | | | | +-------------+--------------+------+-----+---------------------+----------------+ 7 rows in set (0.00 sec) MariaDB [hirtius]> show index from event_log; +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | event_log | 0 | PRIMARY | 1 | id | A | 9 | NULL | NULL | | BTREE | | | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec)
Side notes |
[ToC] |
Below are various notes that either explain some aspect of the database layout design, or give further guidelines pertaining to the management of the database in general.
INXtree design |
[ToC] |
As can be seen above (and as I have already hinted at on the Concepts page), the location or path for all Categories in the INXtree is coded using a single character string: the column called 'tag' in the 'inxtree' table:
mysql> desc inxtree; +-------------+--------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+-------------------+-------+ | tag | varchar(120) | NO | PRI | | | [...]
So we needed to give this string some form of internal structure to enable it to store a tree. We decided initially to allocate 4 characters to code the unique IDs of all the Categories for a given level in the tree (within a given path) using base36. This value (expressing a length in characters) is what we call the TLL (Tag Level Length) on the Database Statistics Page.
Based on these figures, 4 characters per level by default, and a maximum length of 120 characters for the entire path (size of the 'tag' column as defined in the DB), we end up with the following size limitations for the tree:
The TLL value is defined in the Global Definitions File, and can be tuned by the administrator (once, at installation time only) to adapt the geometry of the INXtree to the local needs.
UTF-8 support |
[ToC] |
The database tables have been configured to store UTF-8 data (they are created with the 'DEFAULT CHARSET=utf8' parameter -- see the SQL script called ./docs/create_hirtius_DB.sql in the distribution archive).
But this is not enough. The application front-end also needs to be "multi-byte-safe" in the way it handles strings. Standard PHP string manipulation functions are not. So we had to use alternate versions of these functions, provided by the "mbstring" PHP module (this dependency is listed in the ./INSTALL document provided in the distribution archive).
When developing the application, we made sure to use "mbstring" functions wherever user textual data was concerned. However, those fields that are only used as internal references were not treated the same way (like tray name or event type code for instance). So please refrain from using multi-byte characters in those fields, as this might lead to unpredictable results.
Schema upgrade |
[ToC] |
Future releases of this application may include new features in the database layout as well (new tables, new columns, etc.). If this is the case, the expected schema version number as defined in the web-based front-end (see Global Definitions File) will be raised. So only installing the new application code won't be enough, you'll also need to upgrade to the new database schema (preferably without losing your current data ;-) ).
This will be handled through a (set of) SQL script(s) provided in the distribution archive, alongside the new code. These scripts are located below ./docs/db_update/, and their usage will be detailed in the upgrade documentation, of course.
But the basic principle will always be the same. Each script will be named "vX to vY" (where 'X' and 'Y' are the current and target database schema versions). Simply check your current database schema version before starting with the upgrade process (through the Database Statistics Page, for instance), and apply all DB upgrade scripts that target versions higher than your current one in increasing order (very important).
Applying one of these scripts is done in the following manner:
$ mysql -u hirtius -p hirtius < db_v36_to_v37.sql
The example command given above would upgrade your DB schema from version 36 to version 37. Please note that those upgrade scripts do not verify that you start from the intended DB schema version before executing themselves. Please be careful!
The syntax for this command assumes that you're running it from the appropriate folder within the distribution archive. The user name and database name given here are the standard ones -- they may have been customized locally. Please check your Global Definitions File.
I will always strive to provide scripts that preserve your data, but before performing a database layout upgrade like this, I strongly recommend that you always first:
For detailed guidelines that cover the entire upgrade process (including the required DB schema version jumps), please refer to the document called ./UPGRADE in the distribution archive root folder.
See also:
Project Hirtius, © Les Ateliers du Héron, 2012.
Last updated: Tuesday, Apr. 23, 2024.
| HelpSet Table of Contents | Back to Project Home Page |