| 1 |
rodolico |
1 |
connect camp2;
|
|
|
2 |
|
|
|
3 |
drop table if exists users;
|
|
|
4 |
create table users (
|
|
|
5 |
id int unsigned not null auto_increment,
|
|
|
6 |
username varchar(64) unique not null comment 'login user name',
|
|
|
7 |
password char(32) not null comment 'encrypted password',
|
|
|
8 |
group_id int unsigned references groups(id),
|
|
|
9 |
where_clause text comment 'appended to all queries used to limit the access the user has to records',
|
|
|
10 |
email varchar(64) comment 'email address so we can contact them',
|
|
|
11 |
notes text comment 'Any notes you want',
|
|
|
12 |
created datetime not null,
|
|
|
13 |
removed datetime default null comment 'Date removed',
|
|
|
14 |
primary key (id)
|
|
|
15 |
) comment 'logins to system';
|
|
|
16 |
|
|
|
17 |
|
|
|
18 |
drop table if exists groups;
|
|
|
19 |
create table groups (
|
|
|
20 |
id int unsigned not null auto_increment,
|
|
|
21 |
name varchar(100) unique not null comment 'name of this group',
|
|
|
22 |
created datetime not null,
|
|
|
23 |
modified datetime default null,
|
|
|
24 |
primary key (id)
|
|
|
25 |
) comment 'holds users groups for the ACL code';
|
|
|
26 |
|
|
|
27 |
drop table if exists user_group;
|
|
|
28 |
create table user_group (
|
|
|
29 |
user_id int unsigned not null references users(id),
|
|
|
30 |
group_id int unsigned not null references groups(id),
|
|
|
31 |
primary key (user_id,group_id)
|
|
|
32 |
) comment 'maps users to groups';
|
|
|
33 |
|
|
|
34 |
drop table if exists owners;
|
|
|
35 |
create table owners (
|
|
|
36 |
id int unsigned not null auto_increment,
|
|
|
37 |
name varchar(64) not null unique comment 'Name of owner',
|
|
|
38 |
notes text comment 'Any notes you want',
|
|
|
39 |
created datetime not null,
|
|
|
40 |
removed datetime default null comment 'Date removed',
|
|
|
41 |
primary key (id)
|
|
|
42 |
) comment 'holds owners information';
|
|
|
43 |
|
|
|
44 |
|
|
|
45 |
drop table if exists sites;
|
|
|
46 |
create table sites (
|
|
|
47 |
id int unsigned not null auto_increment,
|
|
|
48 |
name varchar(64) not null unique comment 'Name of site',
|
|
|
49 |
notes text comment 'Any notes you want',
|
|
|
50 |
created datetime not null,
|
|
|
51 |
removed datetime default null comment 'Date removed',
|
|
|
52 |
primary key (id)
|
|
|
53 |
) comment 'holds site information';
|
|
|
54 |
|
|
|
55 |
|
|
|
56 |
drop table if exists device_types;
|
|
|
57 |
create table device_types (
|
|
|
58 |
id int unsigned not null auto_increment,
|
|
|
59 |
name varchar(64) not null unique comment 'short name for display',
|
|
|
60 |
system tinyint(1) comment 'If true (1) this is a system. Set to 0 for printers etc...',
|
|
|
61 |
notes text comment 'Any notes you want',
|
|
|
62 |
created datetime not null,
|
|
|
63 |
removed datetime default null comment 'Date removed',
|
|
|
64 |
primary key (id)
|
|
|
65 |
) comment 'holds types of devices, such as computer, router, etc...';
|
|
|
66 |
|
|
|
67 |
drop table if exists attributes;
|
|
|
68 |
create table attributes (
|
|
|
69 |
id int unsigned not null auto_increment,
|
|
|
70 |
name varchar(64) not null unique comment 'short name for display',
|
|
|
71 |
notes text comment 'Any notes you want',
|
|
|
72 |
created datetime not null,
|
|
|
73 |
removed datetime default null comment 'Date removed',
|
|
|
74 |
primary key (id)
|
|
|
75 |
) comment 'lookup table of possible attributes to devices';
|
|
|
76 |
|
|
|
77 |
drop table if exists devices;
|
|
|
78 |
create table devices (
|
|
|
79 |
id int unsigned not null auto_increment,
|
|
|
80 |
serial_number varchar(64) null comment 'serial number of device',
|
|
|
81 |
name varchar(255) not null comment 'name of device',
|
|
|
82 |
parent_id int unsigned null references devices(id),
|
|
|
83 |
device_type_id int unsigned not null references device_types(id),
|
|
|
84 |
site_id int unsigned null references sites(id),
|
|
|
85 |
owner_id int unsigned not null references owners(id),
|
|
|
86 |
notes text comment 'Any notes you want',
|
|
|
87 |
created datetime not null,
|
|
|
88 |
removed datetime default null comment 'Date removed',
|
|
|
89 |
primary key (id)
|
|
|
90 |
) comment 'holds computers, cards that make them up, etc...';
|
|
|
91 |
|
|
|
92 |
drop table if exists device_aliases;
|
|
|
93 |
create table device_aliases (
|
|
|
94 |
id int unsigned not null auto_increment,
|
|
|
95 |
name varchar(64) not null unique comment 'short name for display',
|
|
|
96 |
device_id int unsigned not null references devices(id),
|
|
|
97 |
notes text comment 'Any notes you want',
|
|
|
98 |
created datetime not null,
|
|
|
99 |
removed datetime default null comment 'Date removed',
|
|
|
100 |
primary key (id)
|
|
|
101 |
|
|
|
102 |
) comment 'aliases for devices';
|
|
|
103 |
|
|
|
104 |
drop table if exists attributes_devices;
|
|
|
105 |
create table attributes_devices (
|
|
|
106 |
id int unsigned not null auto_increment,
|
|
|
107 |
device_id int unsigned not null references devices(id),
|
|
|
108 |
attributes_id int unsigned not null references attributes(id),
|
|
|
109 |
attribute_value text comment 'the actual value of the attribute in question',
|
|
|
110 |
created datetime not null,
|
|
|
111 |
removed datetime default null comment 'Date removed',
|
|
|
112 |
primary key (id)
|
|
|
113 |
) comment 'Each device can have multiple attributes and values';
|
|
|
114 |
|
|
|
115 |
drop table if exists files;
|
|
|
116 |
create table files (
|
|
|
117 |
id int unsigned not null auto_increment,
|
|
|
118 |
original_name varchar(64) not null comment 'original file name',
|
|
|
119 |
name_on_disk varchar(64) not null unique comment 'name of file on disk',
|
|
|
120 |
mime_type_id int unsigned default null references mime_types(id),
|
|
|
121 |
device_id int unsigned not null references devices(id),
|
|
|
122 |
site_id int unsigned null references sites(id),
|
|
|
123 |
owner_id int unsigned not null references owners(id),
|
|
|
124 |
created datetime not null,
|
|
|
125 |
removed datetime default null comment 'Date removed',
|
|
|
126 |
primary key (id)
|
|
|
127 |
) comment 'Files which can be associated with an owner site or device';
|
|
|
128 |
|
|
|
129 |
|
|
|
130 |
drop table if exists mime_types;
|
|
|
131 |
create table mime_types (
|
|
|
132 |
id int unsigned not null auto_increment,
|
|
|
133 |
name varchar(64) not null comment 'the web definition of the mime type',
|
|
|
134 |
embed tinyint(1) default 0 comment 'If true (1) can be viewed on web page',
|
|
|
135 |
extension varchar(5) comment 'standard file extension used for this MIME type',
|
|
|
136 |
primary key (id)
|
|
|
137 |
) comment 'just keeps a list of standard MIME types for use with filess';
|
|
|
138 |
|
|
|
139 |
drop table if exists menus;
|
|
|
140 |
create table menus (
|
|
|
141 |
id int(10) unsigned not null auto_increment,
|
|
|
142 |
parent_id int(10) unsigned default null comment 'If this is a submenu the id of the parent',
|
|
|
143 |
caption varchar(20) not null comment 'The actual caption displayed',
|
|
|
144 |
url varchar(120) default null comment 'the url of the page/script to call or null if this contains sub-options',
|
|
|
145 |
primary key (id)
|
|
|
146 |
) comment='We keep the entire menu structure here so modules can modify';
|
|
|
147 |
|
|
|
148 |
drop table if exists menu_group;
|
|
|
149 |
create table menu_group (
|
|
|
150 |
menu_id int unsigned not null references menus(id),
|
|
|
151 |
group_id int unsigned not null references groups(id),
|
|
|
152 |
primary key (menu_id,group_id)
|
|
|
153 |
) comment 'gives a group access to a menu';
|