| 83 |
rodolico |
1 |
create or replace view view_client_site_device as
|
|
|
2 |
select
|
|
|
3 |
client.client_id,
|
|
|
4 |
client.name Client,
|
|
|
5 |
client.notes Client_Notes,
|
|
|
6 |
site.site_id,
|
|
|
7 |
site.name Site,
|
|
|
8 |
site.notes Site_Notes,
|
|
|
9 |
device.device_id,
|
|
|
10 |
device.name Device,
|
|
|
11 |
device.notes Device_Notes,
|
|
|
12 |
device.serial Device_Serial,
|
|
|
13 |
device.restrictions Device_Restrictions,
|
|
|
14 |
device_type.device_type_id,
|
|
|
15 |
device_type.name Device_Type
|
|
|
16 |
from
|
|
|
17 |
client
|
|
|
18 |
join site using (client_id)
|
|
|
19 |
join device using (site_id)
|
|
|
20 |
join device_type using (device_type_id)
|
|
|
21 |
where
|
|
|
22 |
client.removed_date is null
|
|
|
23 |
and site.removed_date is null
|
|
|
24 |
and device.removed_date is null
|
|
|
25 |
and device_type.show_as_system = 'Y';
|
|
|
26 |
|
| 84 |
rodolico |
27 |
drop table if exists login_menu;
|
|
|
28 |
create table login_menu (
|
|
|
29 |
login_menu_id int unsigned not null auto_increment,
|
|
|
30 |
login_id int unsigned not null references login(login_id),
|
|
|
31 |
menu_id int unsigned not null references menu(menu_id),
|
|
|
32 |
primary key (login_menu_id),
|
|
|
33 |
unique key (login_id,menu_id)
|
|
|
34 |
) comment 'allows access to various menu options';
|
|
|
35 |
|
|
|
36 |
insert into login_menu select null,login_id,menu_id from login,menu where login.email != 'jbellah';
|
| 86 |
rodolico |
37 |
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);
|
| 84 |
rodolico |
38 |
|
|
|
39 |
|
| 83 |
rodolico |
40 |
alter table _system add constraint group_key unique (group_name,key_name);
|
|
|
41 |
|
|
|
42 |
insert into _system (_system_id, group_name, key_name, theValue, added_date, removed_date ) values (null,'database','version','1.0',now(),null)
|
|
|
43 |
on duplicate key update theValue = '1.0';
|