summaryrefslogtreecommitdiffstats
path: root/provisioning-model.lua
diff options
context:
space:
mode:
authorTed Trask <ttrask01@yahoo.com>2010-12-09 14:51:51 +0000
committerTed Trask <ttrask01@yahoo.com>2010-12-09 14:51:51 +0000
commitd4a505fd30aacfac933013eac17ea40256b72d90 (patch)
tree5f278d6f7fff7dd79cf8ad8da5c125721abf51ab /provisioning-model.lua
parent93dd90cec546136f54ea8c6ce137ba94c488819b (diff)
downloadacf-provisioning-d4a505fd30aacfac933013eac17ea40256b72d90.tar.bz2
acf-provisioning-d4a505fd30aacfac933013eac17ea40256b72d90.tar.xz
Added indexes to try to help query speed
Diffstat (limited to 'provisioning-model.lua')
-rw-r--r--provisioning-model.lua21
1 files changed, 20 insertions, 1 deletions
diff --git a/provisioning-model.lua b/provisioning-model.lua
index 074d253..7b92189 100644
--- a/provisioning-model.lua
+++ b/provisioning-model.lua
@@ -27,42 +27,61 @@ local table_creation_scripts = {
-- List of each device that we manage
provisioning_devices = {
"CREATE TABLE provisioning_devices (device_id SERIAL PRIMARY KEY, name VARCHAR(255) UNIQUE, label VARCHAR(255), template_id INTEGER)",
+ "CREATE INDEX devices_name_idx ON provisioning_devices (name)",
+ "CREATE INDEX devices_template_idx ON provisioning_devices (template_id)",
},
-- Multi-to-multi mapping of devices to classes
-- Need to enforce that devices do not contain more than one class with same name (using triggers)
devices_to_classes = {
"CREATE TABLE devices_to_classes (device_id INTEGER, class_id INTEGER)",
+ "CREATE INDEX d2c_device_idx ON devices_to_classes (device_id)",
+ "CREATE INDEX d2c_class_idx ON devices_to_classes (class_id)",
},
-- List of different device classes
--- Need to enforce that name/label combo is unique
provisioning_classes = {
"CREATE TABLE provisioning_classes (class_id SERIAL PRIMARY KEY, name VARCHAR(255), label VARCHAR(255))",
+ "CREATE INDEX classes_name_idx ON provisioning_classes (name)",
+ "CREATE INDEX classes_label_idx ON provisioning_classes (label)",
+ -- Need to enforce that name/label combo is unique
+ "CREATE UNIQUE INDEX classes_name_label_idx ON provisioning_classes (name, label)",
},
-- Multi-to-multi mapping of classes to parameter groups
-- Need to enforce that classes do not contain more than one parameter group with same name (using triggers)
classes_to_param_groups = {
"CREATE TABLE classes_to_param_groups (class_id INTEGER, group_id INTEGER)",
+ "CREATE INDEX c2g_class_idx ON classes_to_param_groups (class_id)",
+ "CREATE INDEX c2g_group_idx ON classes_to_param_groups (group_id)",
},
-- List of different parameter groups
provisioning_groups = {
"CREATE TABLE provisioning_groups (group_id SERIAL PRIMARY KEY, name VARCHAR(255), label VARCHAR(255) UNIQUE, seq INTEGER)",
+ "CREATE INDEX groups_name_idx ON provisioning_groups (name)",
},
-- Multi-to-multi mapping of parameter groups to parameters
param_groups_to_params = {
"CREATE TABLE param_groups_to_params (group_id INTEGER, param_id INTEGER, value VARCHAR(255), editable BOOLEAN)",
+ "CREATE INDEX g2p_group_idx ON param_groups_to_params (group_id)",
+ "CREATE INDEX g2p_param_idx ON param_groups_to_params (param_id)",
+ "CREATE INDEX g2p_editable_idx ON param_groups_to_params (editable)",
},
-- List of each parameter used in any way for any device - mostly for how to display
provisioning_params = {
"CREATE TABLE provisioning_params (param_id SERIAL PRIMARY KEY, name VARCHAR(255) UNIQUE, type VARCHAR(255), label VARCHAR(255), descr VARCHAR(255), value VARCHAR(255), seq INTEGER, regexp VARCHAR(255))",
+ "CREATE INDEX params_name_idx ON provisioning_params (name)",
},
-- All of the (non-default) parameter values for all devices are stored here
provisioning_values = {
-- device_id is a device id from provisioning_devices and param_id is a param id from provisioning_params
"CREATE TABLE provisioning_values (device_id INTEGER, group_name VARCHAR(255), param_id INTEGER, value VARCHAR(255))",
+ "CREATE INDEX values_device_idx ON provisioning_values (device_id)",
+ "CREATE INDEX values_group_idx ON provisioning_values (group_name)",
+ "CREATE INDEX values_param_idx ON provisioning_values (param_id)",
+ "CREATE INDEX values_device_group_param_idx ON provisioning_values (device_id, group_name, param_id)",
},
-- List of options for parameters
provisioning_options = {
"CREATE TABLE provisioning_options (param_id INTEGER, label VARCHAR(255), value VARCHAR(255), seq INTEGER)",
+ "CREATE INDEX options_param_idx ON provisioning_options (param_id)",
},
-- Where do we define the triggers to enforce the foreign keys? Triggers are only on updates, so don't have to worry about read speed
-- Need to define indices