· 8 years ago · Jul 28, 2018, 03:40 PM
1/**
2 * Creates the DB tables for the service delivery plugin
3 */
4
5--
6-- Table boundary_type
7--
8CREATE TABLE IF NOT EXISTS `boundary_type` (
9 `id` INT NOT NULL AUTO_INCREMENT,
10 `boundary_type_name` VARCHAR(45) NOT NULL ,
11 `parent_id` INT NOT NULL DEFAULT 0 ,
12 `creation_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
13 PRIMARY KEY (`id`)
14) COMMENT = 'Types of administrative boundaries e.g. province, district, ward, constituency etc';
15
16--
17-- Table adminstrative_boundary
18--
19CREATE TABLE IF NOT EXISTS `boundary` (
20 `id` INT NOT NULL AUTO_INCREMENT,
21 `boundary_name` VARCHAR(45) NOT NULL ,
22 `boundary_type_id` INT NOT NULL ,
23 `parent_id` INT NOT NULL DEFAULT 0,
24 `creation_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
25 PRIMARY KEY (`id`) ,
26 INDEX `boundary_fk1` (`boundary_type_id` ASC) ,
27 CONSTRAINT `boundary_fk1` FOREIGN KEY (`boundary_type_id` ) REFERENCES `boundary_type` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION
28) COMMENT = 'List of administrative boundaries (specific names of the various admin boundaries)';
29
30--
31-- Table agency
32--
33CREATE TABLE IF NOT EXISTS `agency` (
34 `id` INT NOT NULL AUTO_INCREMENT,
35 `agency_name` VARCHAR(50) NOT NULL ,
36 `description` VARCHAR(200) NULL ,
37 `category_id` INT NOT NULL ,
38 `parent_id` INT NOT NULL DEFAULT 0 ,
39 `boundary_id` INT NULL ,
40 `creation_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
41 PRIMARY KEY (`id`)
42) COMMENT = 'Groups for the monitors responsible for attending to tickets';
43
44--
45-- Table static_entity_type
46--
47CREATE TABLE IF NOT EXISTS `static_entity_type` (
48 `id` INT NOT NULL AUTO_INCREMENT,
49 `category_id` INT NOT NULL ,
50 `type_name` VARCHAR(50) NOT NULL ,
51 `entity_type_color` VARCHAR(15) NOT NULL ,
52 `entity_type_image` VARCHAR(100) NULL ,
53 `entity_type_image_thumb` VARCHAR(100) NULL ,
54 `metadata` TEXT NULL ,
55 PRIMARY KEY (`id`)
56) COMMENT = 'Types of static entities e.g Dispensary, School, Hospital etc';
57
58--
59-- Table static_entity_type_metadata
60--
61CREATE TABLE IF NOT EXISTS `static_entity_type_metadata` (
62 `id` INT NOT NULL AUTO_INCREMENT,
63 `static_entity_type_id` INT NOT NULL,
64 `metadata_item` VARCHAR(100) NOT NULL,
65 `description` VARCHAR(255),
66 PRIMARY KEY (`id`)
67) COMMENT = 'Stores static entity type metadata - defines the compulsory metadata for a static entity';
68
69--
70-- Table static_entity
71--
72CREATE TABLE IF NOT EXISTS `static_entity` (
73 `id` INT NOT NULL AUTO_INCREMENT,
74 `static_entity_type_id` INT NOT NULL ,
75 `boundary_id` INT NOT NULL ,
76 `agency_id` INT,
77 `entity_name` VARCHAR(150) NOT NULL ,
78 `latitude` DOUBLE NOT NULL ,
79 `longitude` DOUBLE NOT NULL ,
80 `creation_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
81 PRIMARY KEY (`id`),
82 INDEX `se_fk_entity_type` (`static_entity_type_id` ASC) ,
83 INDEX `se_fk_boundary` (`boundary_id` ASC) ,
84 CONSTRAINT `se_fk_entity_type` FOREIGN KEY (`static_entity_type_id` ) REFERENCES `static_entity_type` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION,
85 CONSTRAINT `se_fk_boundary` FOREIGN KEY (`boundary_id` ) REFERENCES `boundary` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION
86) COMMENT = 'List of static entities';
87
88--
89-- Table static_entity_metadata
90--
91CREATE TABLE IF NOT EXISTS `static_entity_metadata` (
92 `id` INT NOT NULL AUTO_INCREMENT,
93 `static_entity_id` INT NOT NULL,
94 `item_label` VARCHAR(50) NOT NULL,
95 `item_value` VARCHAR(255),
96 `as_of_year` VARCHAR(4),
97 PRIMARY KEY (`id`),
98 CONSTRAINT `sem_fk_static_entity` FOREIGN KEY(`static_entity_id`) REFERENCES `static_entity` (`id`)
99) COMMENT = 'Metadata for the static entities';
100
101--
102-- Table static_entity_metadata_log
103--
104CREATE TABLE IF NOT EXISTS `static_entity_metadata_log` (
105 `id` INT NOT NULL AUTO_INCREMENT,
106 `static_entity_id` INT NOT NULL,
107 `metadata` TEXT NOT NULL,
108 `modification_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
109 `dashboard_user_id` INT NOT NULL, -- User id of the person updating the static entity metadata
110 PRIMARY KEY (`id`)
111) COMMENT = 'Maintains a log of updates to the metadata of a given static entity';
112
113--
114-- Table dashboard_role
115--
116CREATE TABLE IF NOT EXISTS `dashboard_role` (
117 `id` INT NOT NULL AUTO_INCREMENT,
118 `name` VARCHAR(35) NOT NULL,
119 `description` VARCHAR(255),
120 `service_agency_id` INT NOT NULL DEFAULT 0,
121 PRIMARY KEY(`id`)
122) COMMENT = 'Roles for the dashboard users';
123
124
125--
126-- Table dashboard_user_privileges
127--
128CREATE TABLE IF NOT EXISTS `dashboard_role_privileges` (
129 `dashboard_role_id` INT NOT NULL,
130 `static_entity_id` INT NOT NULL DEFAULT 0,
131 `boundary_id` INT NOT NULL DEFAULT 0,
132 `category_id` INT NOT NULL DEFAULT 0
133) COMMENT = 'Privileges for the dashboard roes';
134
135--
136-- Table dashboard_user
137--
138CREATE TABLE IF NOT EXISTS `dashboard_user` (
139 `id` INT NOT NULL AUTO_INCREMENT,
140 `name` VARCHAR(100) NOT NULL,
141 `email` VARCHAR(50) NOT NULL,
142 `username` VARCHAR(10) NOT NULL,
143 `password` VARCHAR(80) NOT NULL,
144 `is_active` TINYINT(1) NOT NULL DEFAULT 1,
145 `belongs_to_service_agency` TINYINT(1) NOT NULL DEFAULT 1, -- 1 means that the user is associated with a service agency
146 `service_agency_id` INT NOT NULL DEFAULT 0, -- 0 means the user has access to all service agencies
147 `dashboard_role_id` INT NOT NULL,
148 `session_key` VARCHAR(255),
149 `logins` INT NOT NULL DEFAULT 0,
150 `last_login` TIMESTAMP,
151 `last_updated` TIMESTAMP,
152 PRIMARY KEY(`id`)
153) COMMENT = 'Maintains a list of users for the frontend dashboard';
154
155--
156-- Add extra columns to the comment and incident tables
157--
158ALTER TABLE `comment` ADD COLUMN `static_entity_id` INT;
159ALTER TABLE `comment` ADD COLUMN `dashboard_user_id` INT;
160ALTER TABLE `incident` ADD COLUMN `boundary_id` INT NOT NULL;
161ALTER TABLE `incident` ADD COLUMN `static_entity_id` INT;