/* Here are the tables that make up the view */

CREATE TABLE `cans_agency_personnel_profile` (
  `person_id` int(11) NOT NULL,
  `username` varchar(40) default NULL,
  `password_hint_question` varchar(100) default NULL,
  `password_hint_response` blob,
  `status_flg` char(1) NOT NULL default 'A',
  `reset_password` tinyint(4) NOT NULL default '1',
  `updated_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `updated_by` int(11) NOT NULL,
  PRIMARY KEY  (`person_id`),
  KEY `IDX_FK_cans_agency_personnel_profile` (`person_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `cans_app_user_role` (
  `person_role_id` int(11) NOT NULL auto_increment,
  `role_id` int(11) NOT NULL,
  `person_id` int(11) NOT NULL,
  `updated_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `updated_by` int(11) NOT NULL,
  PRIMARY KEY  (`person_role_id`),
  KEY `IDX_FK_cans_agency_personnel_app_user_role` (`person_id`),
  KEY `IDX_FK_cans_app_role_app_user_role` (`role_id`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1

CREATE TABLE `cans_app_role` (
  `role_id` int(11) NOT NULL auto_increment,
  `role_name` varchar(100) default NULL,
  `role_descr` text,
  `app_name` varchar(40) NOT NULL,
  PRIMARY KEY  (`role_id`),
  KEY `IDX_FK_cans_app_config_cans_app_user_role` (`app_name`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1

CREATE TABLE `cans_app_role_permission` (
  `role_perm_id` int(11) NOT NULL auto_increment,
  `role_id` int(11) NOT NULL,
  `permission_id` int(11) NOT NULL,
  `updated_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `updated_by` int(11) default NULL,
  PRIMARY KEY  (`role_perm_id`),
  KEY `IDX_FK_cans_app_role_permission` (`role_id`),
  KEY `IDX_FK_cans_role_app_permission` (`permission_id`)
) ENGINE=MyISAM AUTO_INCREMENT=98 DEFAULT CHARSET=latin1


CREATE TABLE `cans_app_permission` (
  `permission_id` int(11) NOT NULL auto_increment,
  `permission_name` varchar(100) default NULL,
  `permission_description` text,
  PRIMARY KEY  (`permission_id`)
) ENGINE=MyISAM AUTO_INCREMENT=33 DEFAULT CHARSET=latin1 



/* This is the result of a SHOW CREATE VIEW user_role_permission_view */
CREATE ALGORITHM=UNDEFINED DEFINER=`steve`@`localhost` SQL SECURITY DEFINER VIEW `user_role_permission_view` AS select (case when isnull(`perm`.`permission_id`) then concat(`profile`.`person_id`,_latin1'_',`role`.`role_id`) else concat(concat(`profile`.`person_id`,_latin1'_',`role`.`role_id`),_latin1'_',`perm`.`permission_id`) end) AS `id`,`profile`.`person_id` AS `person_id`,`profile`.`username` AS `username`,`role`.`role_id` AS `role_id`,`role`.`role_name` AS `role`,`perm`.`permission_id` AS `permission_id`,`perm`.`permission_name` AS `permission`,`role`.`app_name` AS `app` from ((((`cans_agency_personnel_profile` `profile` join `cans_app_user_role` `userrole` on((`userrole`.`person_id` = `profile`.`person_id`))) join `cans_app_role` `role` on((`role`.`role_id` = `userrole`.`role_id`))) left join `cans_app_role_permission` `rolperm` on((`rolperm`.`role_id` = `role`.`role_id`))) left join `cans_app_permission` `perm` on((`perm`.`permission_id` = `rolperm`.`permission_id`))) where (`profile`.`status_flg` = _latin1'A') order by `profile`.`person_id`,`role`.`app_name`,`role`.`role_name`,`perm`.`permission_name`


/* Here is the SQL we used to initially create the view */
create or replace view user_role_permission_view as
select
        case
                when perm.permission_id is null then  concat(profile.person_id,'_',role.role_id)
                else concat(concat(profile.person_id,'_',role.role_id), '_', perm.permission_id)
        end as id,
        profile.person_id as person_id,
        profile.username as username,
        role.role_id,
        role.role_name as role,
        perm.permission_id,
        perm.permission_name as permission,
        role.app_name as app
from
        cans_agency_personnel_profile profile join cans_app_user_role userrole on (userrole.person_id = profile.person_id)
        join cans_app_role role on (role.role_id = userrole.role_id)
        left outer join cans_app_role_permission rolperm on (rolperm.role_id = role.role_id)
        left outer join cans_app_permission perm on (perm.permission_id = rolperm.permission_id)
where
        profile.status_flg = 'A'
order by person_id, app, role, permission;