Subversion Repositories computer_asset_manager_v2

connect camp2;

drop table if exists users;

create table users (

   id             int unsigned not null auto_increment,

   username       varchar(64) unique not null comment 'login user name',

   password       char(32) not null comment 'encrypted password',

   group_id       int unsigned references groups(id),

   where_clause   text comment 'appended to all queries used to limit the access the user has to records',

   email          varchar(64) comment 'email address so we can contact them',

   notes          text comment 'Any notes you want',

   created        datetime not null,

   removed        datetime default null comment 'Date removed',

   primary key    (id)

) comment 'logins to system';

drop table if exists groups;

create table groups (

   id             int unsigned not null auto_increment,

   name           varchar(100) unique not null comment 'name of this group',

   created 	      datetime not null,

   modified       datetime default null,

   primary key    (id)

) comment 'holds users groups for the ACL code';

drop table if exists user_group;

create table user_group (

  user_id         int unsigned not null references users(id),

  group_id        int unsigned not null references groups(id),

  primary key     (user_id,group_id)

) comment 'maps users to groups';

drop table if exists owners;

create table owners (

   id             int unsigned not null auto_increment,

   name           varchar(64) not null unique comment 'Name of owner',

   notes          text comment 'Any notes you want',

   created        datetime not null,

   removed        datetime default null comment 'Date removed',

   primary key    (id)

) comment 'holds owners information';

drop table if exists sites;

create table sites (

   id             int unsigned not null auto_increment,

   name           varchar(64) not null unique comment 'Name of site',

   notes          text comment 'Any notes you want',

   created        datetime not null,

   removed        datetime default null comment 'Date removed',

   primary key    (id)

) comment 'holds site information';

drop table if exists device_types;

create table device_types (

   id             int unsigned not null auto_increment,

   name           varchar(64) not null unique comment 'short name for display',

   system         tinyint(1) comment 'If true (1) this is a system. Set to 0 for printers etc...',

   notes          text comment 'Any notes you want',

   created        datetime not null,

   removed        datetime default null comment 'Date removed',

   primary key    (id)

) comment 'holds types of devices, such as computer, router, etc...';

drop table if exists attributes;

create table attributes (

   id             int unsigned not null auto_increment,

   name           varchar(64) not null unique comment 'short name for display',

   notes          text comment 'Any notes you want',

   created        datetime not null,

   removed        datetime default null comment 'Date removed',

   primary key    (id)

) comment 'lookup table of possible attributes to devices';

drop table if exists devices;

create table devices (

   id             int unsigned not null auto_increment,

   serial_number  varchar(64) null comment 'serial number of device',

   name           varchar(255) not null comment 'name of device',

   parent_id      int unsigned null references devices(id),

   device_type_id int unsigned not null references device_types(id),

   site_id        int unsigned null references sites(id),

   owner_id       int unsigned not null references owners(id),

   notes          text comment 'Any notes you want',

   created     	datetime not null,

   removed        datetime default null comment 'Date removed',

   primary key    (id)

) comment 'holds computers, cards that make them up, etc...';

drop table if exists device_aliases;

create table device_aliases (

   id             int unsigned not null auto_increment,

   name           varchar(64) not null unique comment 'short name for display',

   device_id      int unsigned not null references devices(id),

   notes          text comment 'Any notes you want',

   created        datetime not null,

   removed        datetime default null comment 'Date removed',

   primary key    (id)

) comment 'aliases for devices';

drop table if exists attributes_devices;

create table attributes_devices (

   id             int unsigned not null auto_increment,

   device_id      int unsigned not null references devices(id),

   attributes_id  int unsigned not null references attributes(id),

   attribute_value text comment 'the actual value of the attribute in question',

   created        datetime not null,

   removed        datetime default null comment 'Date removed',

   primary key    (id)

) comment 'Each device can have multiple attributes and values';

drop table if exists files;

create table files (

   id             int unsigned not null auto_increment,

   original_name  varchar(64) not null comment 'original file name',

   name_on_disk   varchar(64) not null unique comment 'name of file on disk',

   mime_type_id   int unsigned default null references mime_types(id),      

   device_id      int unsigned not null references devices(id),

   site_id        int unsigned null references sites(id),

   owner_id       int unsigned not null references owners(id),

   created        datetime not null,

   removed        datetime default null comment 'Date removed',

   primary key    (id)

) comment 'Files which can be associated with an owner site or device';

drop table if exists mime_types;

create table mime_types (

   id             int unsigned not null auto_increment,

   name           varchar(64) not null comment 'the web definition of the mime type',

   embed          tinyint(1) default 0 comment 'If true (1) can be viewed on web page',

   extension      varchar(5) comment 'standard file extension used for this MIME type',

   primary key    (id)

) comment 'just keeps a list of standard MIME types for use with filess';

drop table if exists menus;

create table menus (

  id              int(10) unsigned not null auto_increment,

  parent_id       int(10) unsigned default null comment 'If this is a submenu the id of the parent',

  caption         varchar(20) not null comment 'The actual caption displayed',

  url             varchar(120) default null comment 'the url of the page/script to call or null if this contains sub-options',

  primary key     (id)

) comment='We keep the entire menu structure here so modules can modify';

drop table if exists menu_group;

create table menu_group (

   menu_id        int unsigned not null references menus(id),

   group_id       int unsigned not null references groups(id),

   primary key    (menu_id,group_id)

) comment 'gives a group access to a menu';