| 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 |
|
|
|
27 |
alter table _system add constraint group_key unique (group_name,key_name);
|
|
|
28 |
|
|
|
29 |
insert into _system (_system_id, group_name, key_name, theValue, added_date, removed_date ) values (null,'database','version','1.0',now(),null)
|
|
|
30 |
on duplicate key update theValue = '1.0';
|