Database

There are 9 main (default) tables prefixed by er_ in ErtikazOS which are:

Settings table

The settings table is for storing the system settings.

CREATE TABLE IF NOT EXISTS `er_settings` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `value` varchar(100) DEFAULT NULL,
  `sort` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
);
ALTER TABLE `er_settings`
  ADD PRIMARY KEY (`id`),
  ADD KEY `name` (`name`);

Applications table

The applications table is for storing all the system applications(functionalities).

CREATE TABLE `er_apps` (
  `app_id` int(11) UNSIGNED NOT NULL,
  `app_path` varchar(45) NOT NULL,
  `app_icon` varchar(45) NOT NULL,
  `app_sort` int(3) DEFAULT '0',
  `app_menu` int(1) NOT NULL,
  `app_access` int(1) NOT NULL,
  `app_create_by` int(11) NOT NULL DEFAULT '0',
  `app_update_by` int(11) NOT NULL DEFAULT '0',
  `app_create_at` datetime DEFAULT NULL,
  `app_update_at` datetime DEFAULT NULL,
  `app_status` int(2) NOT NULL
);
ALTER TABLE `er_apps`
  ADD PRIMARY KEY (`app_id`),
  ADD KEY `app_path` (`app_path`);

Groups table

The groups table is for storing all the groups in the system.

CREATE TABLE `er_groups` (
  `group_id` int(11) UNSIGNED NOT NULL,
  `group_name` varchar(64) NOT NULL,
  `group_create_by` int(11) NOT NULL DEFAULT '0',
  `group_update_by` int(11) NOT NULL DEFAULT '0',
  `group_create_at` datetime DEFAULT NULL,
  `group_update_at` datetime DEFAULT NULL,
  `group_status` int(2) NOT NULL DEFAULT '0'
);
ALTER TABLE `er_groups`
  ADD PRIMARY KEY (`group_id`);

Users table

The users table is for storing all the users in the system.

CREATE TABLE `er_users` (
  `user_id` int(11) UNSIGNED NOT NULL,
  `user_type` int(2) NOT NULL,
  `user_name` varchar(64) NOT NULL,
  `user_email` varchar(64) NOT NULL,
  `user_pass` varchar(64) NOT NULL,
  `user_mobile` varchar(15) NOT NULL DEFAULT '0',
  `user_avatar` varchar(32) NOT NULL DEFAULT '0',
  `user_options` text,
  `user_code` int(11) NOT NULL DEFAULT '0',
  `user_create_by` int(11) NOT NULL DEFAULT '0',
  `user_update_by` int(11) NOT NULL DEFAULT '0',
  `user_create_at` datetime DEFAULT NULL,
  `user_update_at` datetime DEFAULT NULL,
  `user_status` int(2) NOT NULL DEFAULT '0'
);
ALTER TABLE `er_users`
  ADD PRIMARY KEY (`user_id`),
  ADD KEY `user_name` (`user_name`),
  ADD KEY `user_email` (`user_email`);

User relations

The relations table is a many to many relationship between the user and the group.

CREATE TABLE `er_users_rels` (
  `rel_id` int(11) UNSIGNED NOT NULL,
  `rel_user_id` int(11) NOT NULL,
  `rel_group_id` int(11) NOT NULL,
  `rel_create_by` int(11) NOT NULL DEFAULT '0',
  `rel_create_at` datetime DEFAULT NULL
);
ALTER TABLE `er_users_rels`
  ADD PRIMARY KEY (`rel_id`),
  ADD KEY `rel_user_id` (`rel_user_id`),
  ADD KEY `rel_group_id` (`rel_group_id`);

Users Sessions

The sessions table is for storing the user sessions.

CREATE TABLE `er_users_ses` (
  `id` varchar(40) NOT NULL,
  `timestamp` int(11) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `data` blob NOT NULL
);
ALTER TABLE `er_users_ses`
  ADD PRIMARY KEY (`id`),
  ADD KEY `id` (`id`);

User Permissions

The permissions table is a many to many relationship between the user and the group and the application.

CREATE TABLE `er_permissions` (
  `perm_id` int(11) UNSIGNED NOT NULL,
  `perm_app_id` int(11) NOT NULL,
  `perm_group_id` int(11) DEFAULT NULL,
  `perm_user_id` int(11) DEFAULT NULL,
  `perm_create_by` int(11) NOT NULL DEFAULT '0',
  `perm_create_at` datetime DEFAULT NULL
);
ALTER TABLE `er_permissions`
  ADD PRIMARY KEY (`perm_id`),
  ADD KEY `perm_app_id` (`perm_app_id`),
  ADD KEY `perm_group_id` (`perm_group_id`),
  ADD KEY `perm_user_id` (`perm_user_id`);

System Migration

The migrations table for storing migration version and seed version.

CREATE TABLE `er_migrations` (
  `version` bigint(20) NOT NULL,
  `file_path` varchar(64) DEFAULT NULL
)

System Logs

The logs table is for storing all requests in the system.

CREATE TABLE `er_logs` (
  `log_id` int(11) UNSIGNED NOT NULL,
  `log_date` datetime NOT NULL,
  `log_type` varchar(8) NOT NULL,
  `log_ip` varchar(15) NOT NULL,
  `log_app_id` int(11) NOT NULL,
  `log_user_id` int(11) NOT NULL,
  `log_variables` text NOT NULL,
  `log_time` int(11) NOT NULL
);
ALTER TABLE `er_logs`
  ADD PRIMARY KEY (`log_id`),
  ADD KEY `log_type` (`log_type`),
  ADD KEY `log_app_id` (`log_app_id`),
  ADD KEY `log_user_id` (`log_user_id`),
  ADD KEY `log_time` (`log_time`);