The Eonza database. Part 2

In the first part we described the structure of the half of service tables used in the Eonza system. Now we review the remaining tables that store user information, access permissions and uploaded files.

enz_group

The enz_group table contains user group configured by the owner of the storage.

CREATE TABLE IF NOT EXISTS `enz_group` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

name - user group name.

enz_users

The enz_users table contains the list of users added by the owner of the storage to give them access to the data.

CREATE TABLE IF NOT EXISTS `enz_users` ( 
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, 
`login` varchar(32) NOT NULL, 
`pass` binary(16) NOT NULL, 
`email` varchar(32) NOT NULL, 
`idgroup` smallint(5) unsigned NOT NULL, 
`name` varchar(64) NOT NULL, 
`lang` varchar(10) NOT NULL, 
`uptime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
PRIMARY KEY (`id`), 
KEY `login` (`login`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

All fields of this table are explained on this page Users.

enz_access

The enz_access table contains all user group access permissions to tables of the database.

CREATE TABLE IF NOT EXISTS `enz_access` ( 
`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, 
`idgroup` smallint(5) unsigned NOT NULL, 
`idtable` int(10) unsigned NOT NULL, 
`mask` varchar(32) NOT NULL, 
`active` tinyint(3) NOT NULL, 
`read` tinyint(3) unsigned NOT NULL, 
`create` tinyint(3) unsigned NOT NULL, 
`edit` tinyint(3) unsigned NOT NULL, 
`del` tinyint(3) unsigned NOT NULL, 
PRIMARY KEY (`id`), 
KEY `_uptime` (`_uptime`), 
KEY `idgroup` (`idgroup`,`idtable`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

All fields of this table are described on this page Access Rights. Here we merely specify possible values of read, edit and del fields.
0 – this group does not have the corresponding access permissions.
1 – a user of this group has access only to records he or she created.
2 – users of this group have access permissions to all records of the given table or tables.

enz_mimes

The enz_mimes table stores information about types of all uploaded files.

CREATE TABLE IF NOT EXISTS `enz_mimes` ( 
`id` int(10) NOT NULL AUTO_INCREMENT, 
`name` varchar(64) NOT NULL, 
`ext` varchar(64) NOT NULL, 
PRIMARY KEY (`id`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

name - information about the MIME-type of the data. For instance:

image/png
application/vnd.ms-excel
audio/mpeg

ext - reserved.

enz_files

The enz_files table stores the list of all uploaded files. If some table has the type of Images or Files/Documents, the information of those uploaded files will be stored here.

CREATE TABLE IF NOT EXISTS `enz_files` ( 
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
`_owner` smallint(5) unsigned NOT NULL, 
`_uptime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
`idtable` smallint(5) unsigned NOT NULL, 
`idcol` int(10) unsigned NOT NULL, 
`iditem` int(10) unsigned NOT NULL, 
`folder` tinyint(3) unsigned NOT NULL, 
`filename` varchar(128) NOT NULL, 
`size` int(10) unsigned NOT NULL, 
`comment` text NOT NULL, 
`storage` longblob NOT NULL, 
`w` mediumint(8) unsigned NOT NULL, 
`h` mediumint(8) unsigned NOT NULL, 
`sort` tinyint(3) unsigned NOT NULL, 
`preview` blob NOT NULL, 
`mime` tinyint(4) NOT NULL, 
`ispreview` tinyint(3) unsigned NOT NULL, 
PRIMARY KEY (`id`), 
KEY `idtable` (`idtable`,`idcol`,`iditem`,`sort`), 
KEY `folder` (`idtable`,`folder`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

idtable - the identifier of the table a file belongs to
idcol - the identifier of the field (column) where the file is uploaded.
iditem - the identifier of the record the file is uploaded to. These three fields explicitly define where the file belongs to.
folder - if the file is stored on the disk, this field specifies the identifier (name) of the directory. Files on the disk are stored as /storage/[idtable]/[folder]/[id], where [id] – is the filename matching the identifier in this table.
filename - the original filename.
size - the size of the file.
comment - comments to the file.
storage - if the file is stored in the database, it is put into this field.
w - contains picture width, if the field type of the file is Images.
h - contains picture height, if the field type of the file is Images.
sort - sort order of the file.
preview - if the file is stored in the database, has the field type of Images, and the thumbnail preview is enabled, the thumbnail is recorded to this field. If images are stored to the disk, the path to the thumbnail would be /storage/[idtable]/[folder]/_[id].
mime - the identifier of the MIME-type from the enz_mimes table.
ispreview - defines if a thumbnail is stored for the image.