Eonza automation software - free, open source cross-platform program for easy creation and management of scripts.

Eonza information system database

Let’s see how service information is stored in the Eonza information database. Since Eonza is a personal system in the first hand, we recommend creating an individual database for Eonza storage. By default, names of all system tables start with the enz_ prefix. The same prefix starts user tables without aliases, but in this case numeric identifiers are used. For example, enz_11.

enz_db

enz_db is the main system table.

CREATE TABLE IF NOT EXISTS `enz_db` ( 
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
`pass` char(32) NOT NULL, 
`ctime` datetime NOT NULL, 
`settings` text NOT NULL, 
PRIMARY KEY (`id`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The first field id is auto increment unique identifier. All tables have this field, so we will omit it further. The enz_db table consists of only one record having the identifier of 1.
pass - the result of the pass_md5( 'password', true ) function calculated from a randomly generated password. The password itself is stored in conf.inc.php as a constant CONF_PSW.
ctime - creation time of the table. Usually is the same as the installation time of the system.
settings - system configuration in the JSON format. Among other things Eonza settings are stored here.

enz_tables

This table holds information about existing user tables in the information storage.

CREATE TABLE IF NOT EXISTS `enz_tables` ( 
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
`_uptime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
`_owner` smallint(5) unsigned NOT NULL, 
`title` varchar(128) NOT NULL, 
`comment` varchar(128) NOT NULL, 
`alias` varchar(24) NOT NULL, 
`idparent` int(10) unsigned NOT NULL, 
`isfolder` tinyint(3) unsigned NOT NULL, 
`istree` tinyint(3) unsigned NOT NULL, 
PRIMARY KEY (`id`), 
KEY `idparent` (`idparent`,`isfolder`,`title`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

_uptime - last change time of the record.
_owner - the identifier of a user who created this record. Note, the same three fields: id, _uptime, _owner are automatically created for every user table.
title - table title.
comment - table comment, a short description of the table.
alias - table alias.
idparent - the enz_tables table is hierarchical, which means tables can be distributed across folders. This field specifies the identifier of the parent folder. If it is 0, the table is on the top level of the hierarchy.
isfolder - if it is 1, the record holds information about the folder, not about the table.
istree - if it is 1, the table is hierarchical. In this case, the table has one more field - _parent.

enz_columns

The enz_columns table contains information about columns of all user tables. One record holds
information about one field of a table.

CREATE TABLE IF NOT EXISTS `enz_columns` ( 
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
`idtable` smallint(5) unsigned NOT NULL, 
`idtype` tinyint(3) unsigned NOT NULL, 
`title` varchar(96) NOT NULL, 
`comment` varchar(128) NOT NULL, 
`sort` smallint(5) unsigned NOT NULL, 
`alias` varchar(24) NOT NULL, 
`visible` tinyint(3) unsigned NOT NULL, 
`align` tinyint(3) unsigned NOT NULL, 
`extend` varchar(250) NOT NULL, 
PRIMARY KEY (`id`), 
KEY `idtable` (`idtable`,`sort`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

idtable - identifier of a master-table from enz_tables.
idtype - field type. The list of types can be found in the lib/fields.php file – these are constants FT_*.
title - field title.
comment - field comment, a short description of the field.
sort - fields are sorted in ascending order by the value of this field.
alias - column name in the database table. If not specified, the column name is the same as the identifier.
visible - If it is 1, the column is displayed in the table view mode.
align - alignment of contents in a table cell. 1 – left, 2 - center, 3 - right.
extend - additional field parameters. They depend on the type of a field idtype. Parameters are stored in the JSON format and are the same as data type parameters.

enz_sets

The enz_sets table holds all record sets.

CREATE TABLE IF NOT EXISTS `enz_sets` ( 
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, 
`_owner` smallint(5) unsigned NOT NULL, 
`_uptime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
`idset` smallint(5) unsigned NOT NULL, 
`iditem` tinyint(3) unsigned NOT NULL, 
`title` varchar(48) NOT NULL, 
PRIMARY KEY (`id`), 
KEY `idset` (`idset`,`iditem`), 
KEY `idsetname` (`idset`,`title`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

idset - specifies the identifier of a set the given record is in. If the value is 0, the given record contains the name of the set. Therefore, if we select elements with idset equal to 0, we obtain the list of sets.
iditem - element identifier. Just like a set, it cannot hold more than 32 elements, and the value of this identifier can be only between 1 and 32.
title - element title. If idset is 0, this field contains the name of a set.

enz_menu

The enz_menu table is for storing elements of the menu bar.

CREATE TABLE IF NOT EXISTS `enz_menu` ( 
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
`title` varchar(64) NOT NULL, 
`sort` int(11) NOT NULL, 
`url` varchar(128) NOT NULL, 
`hint` varchar(128) NOT NULL, 
`idparent` int(10) unsigned NOT NULL, 
`isfolder` tinyint(3) unsigned NOT NULL, 
PRIMARY KEY (`id`), 
KEY `idparent` (`idparent`,`sort`,`title`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

title - the title of the menu item.
sort - this number defines order of menu elements. Menu elements are sorted in ascending order.
url - a link to follow when the given menu element is chosen.
hint - a hint displayed when the mouse cursor is over the element.
idparent - the identifier of an owner of the given element. Since the menu is hierarchical, this field points to the parent of the element. If idparent is 0, the menu element is on the top level of the hierarchy.
isfolder - if it equals 1, the current item is a folder, which contains other menu items.

enz_log

The table enz_log stores operations a user does with tables of the database. If you want logging, please enable log files in the settings. Each record in the log table corresponds to one operation. Note that the table doesn’t save removed or changed information, it only tracks the fact that such changes were done.

CREATE TABLE IF NOT EXISTS `enz_log` ( 
`idtable` smallint(5) unsigned NOT NULL, 
`idrow` int(10) unsigned NOT NULL, 
`iduser` smallint(5) unsigned NOT NULL, 
`uptime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
`action` set('create','edit','delete','') NOT NULL, 
KEY `uptime` (`uptime`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

idtable - the identifier of the table in enz_tables table.
idrow - the identifier of the record which has been changed.
iduser - the user's identifier in enz_users table, who changed this record.
uptime - change time of the record.
action - the type of the action (create, edit, delete).

enz_onemany

Values of Link to table fields with Multiple select are written in the enz_onemany table. If only one item is selected then its identifier is stored in the main table. This table stores next items (the second one etc.). For example, the item id=100 (and the field idcolumn=20) has items with 10, 14, 15 identifiers from the linked table. In this case, 10 is stored in the main table, but 14 and 15 are stored [idcolumn, iditem, idmulti] in enz_onemany table as [20, 100, 14] and [ 20, 100, 15].

CREATE TABLE IF NOT EXISTS `enz_onemany` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `idcolumn` smallint(5) unsigned NOT NULL,
  `iditem` int(10) unsigned NOT NULL,
  `idmulti` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idcolumn` (`idcolumn`,`iditem`),
  KEY `idcolumn_2` (`idcolumn`,`idmulti`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

idcolumn - the identifier of the field (column) of Link to table type in enz_columns table.
iditem - the identifier of the record in the main table.
idmulti - the identifier of the record in the linked table.