Subversion Repositories computer_asset_manager_v2

Rev

Rev 63 | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 63 Rev 64
Line 88... Line 88...
88 
drop table if exists owner;
 88 
drop table if exists owner;
89 
create table owner (
 89 
create table owner (
90 
   owner_id       int unsigned not null auto_increment,
 90 
   owner_id       int unsigned not null auto_increment,
91 
   name           varchar(64) comment 'name of owner',
 91 
   name           varchar(64) comment 'name of owner',
92 
   uuid           varchar(36) comment 'unique id of this owner, normally uuid',
 92 
   uuid           varchar(36) comment 'unique id of this owner, normally uuid',
93 
   created        date comment 'date record was created',
 93 
   created        date default current_timestamp comment 'date record was created',
94 
   removed        date comment 'date record was removed',
 94 
   removed        date comment 'date record was removed',
95 
   primary key    (owner_id)
 95 
   primary key    (owner_id)
96 
) comment 'hold owner information';
 96 
) comment 'hold owner information';
97 
 
 97 
 
98 
/*
 98 
/*
Line 101... Line 101...
101 
drop table if exists location;
 101 
drop table if exists location;
102 
create table location (
 102 
create table location (
103 
   location_id    int unsigned not null auto_increment,
 103 
   location_id    int unsigned not null auto_increment,
104 
   name           varchar(64) comment 'name of location',
 104 
   name           varchar(64) comment 'name of location',
105 
   uuid           varchar(36) comment 'unique id of this location, normally uuid',
 105 
   uuid           varchar(36) comment 'unique id of this location, normally uuid',
106 
   created        date comment 'date record was created',
 106 
   created        date default current_timestamp comment 'date record was created',
107 
   removed        date comment 'date record was removed',
 107 
   removed        date comment 'date record was removed',
108 
   primary key    (location_id)
 108 
   primary key    (location_id)
109 
) comment 'hold location information';
 109 
) comment 'hold location information';
110 
 
 110 
 
111 
/*
 111 
/*
Line 114... Line 114...
114 
*/
 114 
*/
115 
drop table if exists device_type;
 115 
drop table if exists device_type;
116 
create table device_type (
 116 
create table device_type (
117 
   device_type_id int unsigned not null auto_increment,
 117 
   device_type_id int unsigned not null auto_increment,
118 
   name           varchar(64) comment 'name of device type',
 118 
   name           varchar(64) comment 'name of device type',
119 
   created        date comment 'date record was created',
 119 
   created        date default current_timestamp comment 'date record was created',
120 
   removed        date comment 'date record was removed',
 120 
   removed        date comment 'date record was removed',
121 
   primary key    (device_type_id)
 121 
   primary key    (device_type_id)
122 
) comment 'simple child table to determine the type of device we have';
 122 
) comment 'simple child table to determine the type of device we have';
123
123
124 
/*
 124 
/*
Line 137... Line 137...
137 
create table device (
 137 
create table device (
138 
   device_id      int unsigned not null auto_increment,
 138 
   device_id      int unsigned not null auto_increment,
139 
   uuid           varchar(36) comment 'unique id of this device, normally uuid',
 139 
   uuid           varchar(36) comment 'unique id of this device, normally uuid',
140 
   serial         varchar(32) comment 'serial number of this device, if we have it',
 140 
   serial         varchar(32) comment 'serial number of this device, if we have it',
141 
   name           varchar(64) comment 'name of device',
 141 
   name           varchar(64) comment 'name of device',
142 
   created        date comment 'date record was created',
 142 
   created        date default current_timestamp comment 'date record was created',
143 
   removed        date comment 'date record was removed',
 143 
   removed        date comment 'date record was removed',
144 
   unique key     unique_uuid( uuid, serial ),
 144 
   unique key     unique_uuid( uuid, serial ),
145 
   primary key    (device_id)
 145 
   primary key    (device_id)
146 
) comment 'holds individual devices';
 146 
) comment 'holds individual devices';
147 
 
 147 
 
Line 166... Line 166...
166 
drop table if exists owner_location;
 166 
drop table if exists owner_location;
167 
create table owner_location (
 167 
create table owner_location (
168 
   owner_location_id int unsigned not null auto_increment,
 168 
   owner_location_id int unsigned not null auto_increment,
169 
   owner_id      int unsigned not null references owner( owner_id ),
 169 
   owner_id      int unsigned not null references owner( owner_id ),
170 
   location_id        int unsigned not null references location( location_id ),
 170 
   location_id        int unsigned not null references location( location_id ),
171 
   created        date comment 'date record was created',
 171 
   created        date default current_timestamp comment 'date record was created',
172 
   removed        date comment 'date record was removed',
 172 
   removed        date comment 'date record was removed',
173 
   index          location_device ( owner_id,location_id ),
 173 
   index          location_device ( owner_id,location_id ),
174 
   primary key    (owner_location_id)
 174 
   primary key    (owner_location_id)
175 
) comment 'links ownership of a location to a owner';
175 
) comment 'links ownership of a location to a owner';
176 
 
 176 
 
Line 182... Line 182...
182 
drop table if exists location_device;
 182 
drop table if exists location_device;
183 
create table location_device (
 183 
create table location_device (
184 
   location_device_id int unsigned not null auto_increment,
 184 
   location_device_id int unsigned not null auto_increment,
185 
   location_id        int unsigned not null references location( location_id ),
 185 
   location_id        int unsigned not null references location( location_id ),
186 
   device_id      int unsigned not null references device( device_id ),
 186 
   device_id      int unsigned not null references device( device_id ),
187 
   created        date comment 'date record was created',
 187 
   created        date default current_timestamp comment 'date record was created',
188 
   removed        date comment 'date record was removed',
 188 
   removed        date comment 'date record was removed',
189 
   index          location_device ( location_id,device_id ),
 189 
   index          location_device ( location_id,device_id ),
190 
   primary key    (location_device_id)
 190 
   primary key    (location_device_id)
191 
) comment 'links a device to its location';
 191 
) comment 'links a device to its location';
192 
 
 192 
 
Line 198... Line 198...
198 
drop table if exists owner_device;
 198 
drop table if exists owner_device;
199 
create table owner_device (
 199 
create table owner_device (
200 
   owner_device_id  int unsigned not null auto_increment,
 200 
   owner_device_id  int unsigned not null auto_increment,
201 
   owner_id      int unsigned not null references owner( owner_id ),
 201 
   owner_id      int unsigned not null references owner( owner_id ),
202 
   device_id      int unsigned not null references device( device_id ),
 202 
   device_id      int unsigned not null references device( device_id ),
203 
   created        date comment 'date record was created',
 203 
   created        date default current_timestamp comment 'date record was created',
204 
   removed        date comment 'date record was removed',
 204 
   removed        date comment 'date record was removed',
205 
   index          owner_device( owner_id, device_id ),
 205 
   index          owner_device( owner_id, device_id ),
206 
   primary key    ( owner_device_id )
 206 
   primary key    ( owner_device_id )
207 
) comment 'links a device to its owner';
 207 
) comment 'links a device to its owner';
208 
 
 208 
 
Line 215... Line 215...
215 
drop table if exists device_device;
 215 
drop table if exists device_device;
216 
create table device_device (
 216 
create table device_device (
217 
   device_device_id int unsigned not null auto_increment,
 217 
   device_device_id int unsigned not null auto_increment,
218 
   device_id      int unsigned not null references device( device_id ),
 218 
   device_id      int unsigned not null references device( device_id ),
219 
   parent_id      int unsigned not null references device( device_id ),
 219 
   parent_id      int unsigned not null references device( device_id ),
220 
   created        date comment 'date record was created',
 220 
   created        date default current_timestamp comment 'date record was created',
221 
   removed        date comment 'date record was removed',
 221 
   removed        date comment 'date record was removed',
222 
   index          device_device( device_id, parent_id ),
 222 
   index          device_device( device_id, parent_id ),
223 
   primary key    ( device_device_id )
 223 
   primary key    ( device_device_id )
224 
) comment 'links a device to another device';
 224 
) comment 'links a device to another device';
225 
 
 225 
 
Line 320... Line 320...
320 
      join location using (location_id)
 320 
      join location using (location_id)
321 
      join owner_location on (location.location_id = owner_location.location_id)
 321 
      join owner_location on (location.location_id = owner_location.location_id)
322 
      join owner loc_owner on (loc_owner.owner_id = owner_location.owner_id)
 322 
      join owner loc_owner on (loc_owner.owner_id = owner_location.owner_id)
323 
      left outer join view_device_device parent using (device_id)
 323 
      left outer join view_device_device parent using (device_id)
324 
   where
 324 
   where
325 
      parent.removed is null;
 325 
      parent.removed is null
- 
 
 326 
      and owner_device.removed is null
- 
 
 327 
      and location_device.removed is null
- 
 
 328 
      and owner_location.removed is null;
326 
 
 329 
 
327 
 
 330 
 
328 
insert into _system ( key_value, group_name, key_name ) values ( '0.1','database','version' )
331 
insert into _system ( key_value, group_name, key_name ) values ( '0.1','database','version' )
329 
   on duplicate key update key_value = '0.1';
 332 
   on duplicate key update key_value = '0.1';
330 
insert into _system ( key_value, group_name, key_name ) values ( '', 'program', 'motd' );
 333 
insert into _system ( key_value, group_name, key_name ) values ( '', 'program', 'motd' );