J F W

HOW TO : CORE

database schema

The classes that perform the authentication and authorization process need a database like this:

users (users)
1. user_id: user unique id
2. user_name: the username
3. user_password: the password
4. user_desc: user description

usergroup (groups)
1. group_id: group unique id
2. group_name: group name
3. group_desc: group description

user_group (relation users/usergroup)
1. user_id: user unique id
2. group_id: group unique id

service (services/actions)
1. service_id: service/action unique id
2. service_name: service unique name
3. service_desc: service description
4. service_free: bypass authorization if 1

user_service (relation users/service)
1. user_id: user unique id
2. service_id: service/action unique id

service_group (relation service/usergroup)
1. service_id: service/action unique id
2. group_id: group unique id

language (languages)
1. language_id: language unique id
2. language: language description
3. code: language code

menu (menus and relation menu/service)
1. menu_id: menu unique id
2. menu_name: menu name
3. menu_order: menu order
4. menu_parent: the menu_id of the parent menu
5. service_id: service/action unique id
6. visible: set menu visibility
7. stick: menu always visible

menu_locale (relation menu/language)
1. menu_id: menu unique id
2. language_id: language unique id
3. menu_name: the menu name relative of the language
4. menu_desc: the menu description relative of the language

This is the sql script for creating the database under MySQL for schema jfw and user jfw:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; -- -- Create schema jfw -- CREATE DATABASE IF NOT EXISTS jfw; USE jfw; -- -- Definition of table `language` -- DROP TABLE IF EXISTS `language`; CREATE TABLE `language` ( `LANGUAGE` varchar(30) NOT NULL, `CODE` varchar(5) NOT NULL, `LANGUAGE_ID` int(11) NOT NULL auto_increment, PRIMARY KEY (`LANGUAGE_ID`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; -- -- Dumping data for table `language` -- /*!40000 ALTER TABLE `language` DISABLE KEYS */; INSERT INTO `language` (`LANGUAGE`,`CODE`,`LANGUAGE_ID`) VALUES ('ENGLISH','EN',1), ('ITALIANO','IT',2), ('DEUTSCH','DE',3), ('ΕΛΛΗΝΙΚΑ','EL',4); /*!40000 ALTER TABLE `language` ENABLE KEYS */; -- -- Definition of table `menu` -- DROP TABLE IF EXISTS `menu`; CREATE TABLE `menu` ( `MENU_ID` int(11) NOT NULL, `MENU_NAME` varchar(50) default NULL, `MENU_ORDER` int(11) default NULL, `MENU_PARENT` int(11) default NULL, `SERVICE_ID` int(11) default NULL, `VISIBLE` varchar(1) NOT NULL, `STICK` varchar(1) default NULL, PRIMARY KEY (`MENU_ID`), KEY `FK_menu_1` (`SERVICE_ID`), CONSTRAINT `FK_menu_1` FOREIGN KEY (`SERVICE_ID`) REFERENCES `service` (`SERVICE_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `menu` -- /*!40000 ALTER TABLE `menu` DISABLE KEYS */; /*!40000 ALTER TABLE `menu` ENABLE KEYS */; -- -- Definition of table `menu_locale` -- DROP TABLE IF EXISTS `menu_locale`; CREATE TABLE `menu_locale` ( `LANGUAGE_ID` int(11) NOT NULL, `MENU_ID` int(11) NOT NULL, `MENU_NAME` varchar(50) NOT NULL, `MENU_DESC` varchar(100) default NULL, KEY `FK_menu_locale_1` (`MENU_ID`), KEY `FK_menu_locale_2` (`LANGUAGE_ID`), CONSTRAINT `FK_menu_locale_2` FOREIGN KEY (`LANGUAGE_ID`) REFERENCES `language` (`LANGUAGE_ID`), CONSTRAINT `FK_menu_locale_1` FOREIGN KEY (`MENU_ID`) REFERENCES `menu` (`MENU_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `menu_locale` -- /*!40000 ALTER TABLE `menu_locale` DISABLE KEYS */; /*!40000 ALTER TABLE `menu_locale` ENABLE KEYS */; -- -- Definition of table `service` -- DROP TABLE IF EXISTS `service`; CREATE TABLE `service` ( `SERVICE_ID` int(11) NOT NULL, `SERVICE_NAME` varchar(120) NOT NULL, `SERVICE_DESC` varchar(300) default NULL, `SERVICE_FREE` int(11) NOT NULL, PRIMARY KEY (`SERVICE_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `service` -- /*!40000 ALTER TABLE `service` DISABLE KEYS */; /*!40000 ALTER TABLE `service` ENABLE KEYS */; -- -- Definition of table `service_group` -- DROP TABLE IF EXISTS `service_group`; CREATE TABLE `service_group` ( `SERVICE_ID` int(11) NOT NULL, `GROUP_ID` int(11) NOT NULL, KEY `FK_service_group_1` (`SERVICE_ID`), KEY `FK_service_group_2` (`GROUP_ID`), CONSTRAINT `FK_service_group_2` FOREIGN KEY (`GROUP_ID`) REFERENCES `usergroup` (`GROUP_ID`), CONSTRAINT `FK_service_group_1` FOREIGN KEY (`SERVICE_ID`) REFERENCES `service` (`SERVICE_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `service_group` -- /*!40000 ALTER TABLE `service_group` DISABLE KEYS */; /*!40000 ALTER TABLE `service_group` ENABLE KEYS */; -- -- Definition of table `user_group` -- DROP TABLE IF EXISTS `user_group`; CREATE TABLE `user_group` ( `USER_ID` int(11) NOT NULL, `GROUP_ID` int(11) NOT NULL, KEY `FK_user_group_1` (`USER_ID`), KEY `FK_user_group_2` (`GROUP_ID`), CONSTRAINT `FK_user_group_2` FOREIGN KEY (`GROUP_ID`) REFERENCES `usergroup` (`GROUP_ID`), CONSTRAINT `FK_user_group_1` FOREIGN KEY (`USER_ID`) REFERENCES `users` (`USER_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `user_group` -- /*!40000 ALTER TABLE `user_group` DISABLE KEYS */; /*!40000 ALTER TABLE `user_group` ENABLE KEYS */; -- -- Definition of table `user_service` -- DROP TABLE IF EXISTS `user_service`; CREATE TABLE `user_service` ( `USER_ID` int(11) NOT NULL, `SERVICE_ID` int(11) NOT NULL, KEY `FK_user_service_1` (`USER_ID`), KEY `FK_user_service_2` (`SERVICE_ID`), CONSTRAINT `FK_user_service_2` FOREIGN KEY (`SERVICE_ID`) REFERENCES `service` (`SERVICE_ID`), CONSTRAINT `FK_user_service_1` FOREIGN KEY (`USER_ID`) REFERENCES `users` (`USER_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `user_service` -- /*!40000 ALTER TABLE `user_service` DISABLE KEYS */; /*!40000 ALTER TABLE `user_service` ENABLE KEYS */; -- -- Definition of table `usergroup` -- DROP TABLE IF EXISTS `usergroup`; CREATE TABLE `usergroup` ( `GROUP_ID` int(11) NOT NULL, `GROUP_NAME` varchar(30) NOT NULL, `GROUP_DESC` varchar(400) default NULL, PRIMARY KEY (`GROUP_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `usergroup` -- /*!40000 ALTER TABLE `usergroup` DISABLE KEYS */; /*!40000 ALTER TABLE `usergroup` ENABLE KEYS */; -- -- Definition of table `users` -- DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `USER_ID` int(11) NOT NULL, `USER_NAME` varchar(30) default NULL, `USER_PASSWORD` varchar(30) default NULL, `USER_DESC` varchar(320) default NULL, PRIMARY KEY (`USER_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `users` -- /*!40000 ALTER TABLE `users` DISABLE KEYS */; /*!40000 ALTER TABLE `users` ENABLE KEYS */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;