Subversion Repositories computer_asset_manager_v1

create or replace view view_client_site_device as
   select 
      client.client_id, 
      client.name Client, 
      client.notes Client_Notes,
      site.site_id,
      site.name Site,
      site.notes Site_Notes,
      device.device_id,
      device.name Device,
      device.notes Device_Notes,
      device.serial Device_Serial,
      device.restrictions Device_Restrictions,
      device_type.device_type_id,
      device_type.name Device_Type
   from
      client
      join site using (client_id)
      join device using (site_id)
      join device_type using (device_type_id)
   where
      client.removed_date is null
      and site.removed_date is null
      and device.removed_date is null
      and device_type.show_as_system = 'Y';

drop table  if exists login_menu;
create table login_menu (
   login_menu_id int unsigned not null auto_increment,
   login_id      int unsigned not null references login(login_id),
   menu_id       int unsigned not null references menu(menu_id),
   primary key   (login_menu_id),
   unique key    (login_id,menu_id)
) comment 'allows access to various menu options';

insert into login_menu select null,login_id,menu_id from login,menu where login.email != 'jbellah';
insert into login_menu select null,login_id,menu_id from login,menu where login.email = 'jbellah' and menu.menu_id in (1,8,9,10,12,15,16);


alter table _system add constraint group_key unique (group_name,key_name);

insert into _system (_system_id, group_name, key_name, theValue, added_date, removed_date ) values (null,'database','version','1.0',now(),null)
on duplicate key update theValue = '1.0';