def create_table_device_status(Registry, **kwargs): """ Defines the device status table. Stores device status information. """ table = """CREATE TABLE `device_status` ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `status_id` TEXT NOT NULL, `device_id` TEXT NOT NULL, /* device_id */ `command_id` TEXT, `gateway_id` TEXT, `set_at` REAL NOT NULL, `energy_usage` INTEGER NOT NULL, `energy_type` TEXT, `human_status` TEXT NOT NULL, `human_message` TEXT NOT NULL, `machine_status` TEXT NOT NULL, `machine_status_extra` TEXT, `requested_by` TEXT NOT NULL, `reported_by` TEXT NOT NULL, `request_id` TEXT, `uploaded` INTEGER NOT NULL DEFAULT 0, `uploadable` INTEGER NOT NULL DEFAULT 0 /* For security, only items marked as 1 can be sent externally */ );""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery( create_index('device_status', 'status_id', unique=True)) yield Registry.DBPOOL.runQuery(create_index('device_status', 'device_id')) yield Registry.DBPOOL.runQuery(create_index('device_status', 'uploaded'))
def create_table_device_commands(Registry, **kwargs): """ Defines the device command table to store command history and various info. """ table = """CREATE TABLE `device_commands` ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `request_id` TEXT NOT NULL, `persistent_request_id` TEXT, `source_gateway_id` TEXT NOT NULL, `device_id` TEXT NOT NULL, `command_id` TEXT NOT NULL, `inputs` TEXT, `created_at` FLOAT NOT NULL, `broadcast_at` FLOAT, `sent_at` FLOAT, `received_at` FLOAT, `pending_at` FLOAT, `finished_at` FLOAT, `not_before_at` FLOAT, `not_after_at` FLOAT, `command_status_received` INT NOT NULL DEFAULT 0, `history` TEXT NOT NULL, `status` TEXT NOT NULL, `requested_by` TEXT, `uploaded` INTEGER NOT NULL DEFAULT 0, `uploadable` INTEGER NOT NULL DEFAULT 0 /* For security, only items marked as 1 can be sent externally */ );""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery( create_index('device_commands', 'request_id', unique=True)) # yield Registry.DBPOOL.runQuery("CREATE INDEX IF NOT EXISTS device_command_id_nottimes_idx ON device_command (device_id, not_before_at, not_after_at)") # yield Registry.DBPOOL.runQuery("CREATE INDEX IF NOT EXISTS device_command_id_nottimes_idx ON device_command (device_id, not_before_at, not_after_at)") yield Registry.DBPOOL.runQuery( create_index('device_commands', 'finished_at'))
def create_table_users(Registry, **kwargs): """ """ table = """CREATE TABLE `users` ( `id` TEXT NOT NULL, `gateway_id` TEXT NOT NULL, `user_id` TEXT NOT NULL, `email` TEXT NOT NULL, `updated_at` INTEGER NOT NULL, `created_at` INTEGER NOT NULL );""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('users', 'id', unique=True)) yield Registry.DBPOOL.runQuery(create_index('users', 'email'))
def create_table_sqldict(Registry, **kwargs): """ """ # Defines the SQL Dict table. Used by the :class:`SQLDict` class to maintain persistent dictionaries. table = """CREATE TABLE `sqldict` ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `component` TEXT NOT NULL, `dict_name` INTEGER NOT NULL, `dict_data` BLOB, `created_at` INTEGER NOT NULL, `updated_at` INTEGER NOT NULL);""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('sqldict', 'dict_name')) yield Registry.DBPOOL.runQuery(create_index('sqldict', 'component'))
def create_table_webinterface_sessions(Registry, **kwargs): """ """ # Defines the web interface session store. Used by the :class:`WebInterface` class to maintain session information table = """CREATE TABLE `webinterface_sessions` ( `id` TEXT NOT NULL, /* moduleUUID */ `is_valid` INTEGER NOT NULL, `gateway_id` TEXT NOT NULL, `permissions` TEXT, `session_data` TEXT NOT NULL, `created_at` INTEGER NOT NULL, `last_access` INTEGER NOT NULL, `updated_at` INTEGER NOT NULL, PRIMARY KEY(id));""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('webinterface_sessions', 'created_at')) yield Registry.DBPOOL.runQuery(create_index('webinterface_sessions', 'updated_at'))
def create_table_gpg_keys(Registry, **kwargs): """ Used for quick access to GPG keys instead of key ring. """ table = """CREATE TABLE `gpg_keys` ( `keyid` TEXT NOT NULL, `fullname` TEXT, `comment` TEXT, `email` TEXT NOT NULL, `endpoint_id` TEXT NOT NULL, `endpoint_type` TEXT NOT NULL, `fingerprint` TEXT NOT NULL, `length` INTEGER NOT NULL, `expires_at` INTEGER NOT NULL, `sigs` TEXT, `subkeys` TEXT, `ownertrust` TEXT NOT NULL, `trust` TEXT NOT NULL, `algo` TEXT NOT NULL, `type` TEXT NOT NULL, `uids` TEXT, `publickey` TEXT NOT NULL, `have_private` INTEGER NOT NULL, `created_at` INTEGER NOT NULL );""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('gpg_keys', 'keyid'))
def create_table_modules(Registry, **kwargs): """ """ # Stores module information table = """CREATE TABLE `modules` ( `id` TEXT NOT NULL, /* moduleUUID */ `gateway_id` TEXT NOT NULL, `machine_label` TEXT NOT NULL, `module_type` TEXT NOT NULL, `label` TEXT NOT NULL, `short_description` TEXT, `description` TEXT, `description_formatting` TEXT, `see_also` TEXT, `repository_link` TEXT, `issue_tracker_link` TEXT, `install_count` INTEGER DEFAULT 0, `doc_link` TEXT, `git_link` TEXT, `install_branch` TEXT NOT NULL, `prod_branch` TEXT NOT NULL, `dev_branch` TEXT, `prod_version` TEXT, `dev_version` TEXT, `public` INTEGER NOT NULL, `status` INTEGER NOT NULL, /* disabled, enabled, deleted */ `created_at` INTEGER NOT NULL, `updated_at` INTEGER NOT NULL, PRIMARY KEY(id));""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('modules', 'machine_label'))
def create_table_gpg_keys(Registry, **kwargs): """ Used for quick access to GPG keys instead of key ring. """ table = """CREATE TABLE `gpg_keys` ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `notes` TEXT, `endpoint` TEXT NOT NULL, `keyid` TEXT NOT NULL, `fingerprint` TEXT NOT NULL, `length` INTEGER NOT NULL, `expires` INTEGER NOT NULL, `publickey` TEXT NOT NULL, `have_private` INTEGER NOT NULL, `created_at` INTEGER NOT NULL );""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('gpg_keys', 'endpoint')) yield Registry.DBPOOL.runQuery(create_index('gpg_keys', 'fingerprint'))
def create_table_webinterface_api_auth(Registry, **kwargs): """ """ # Nearly the same as webinterface_sessions, but for api keys table = """CREATE TABLE `webinterface_api_auth` ( `id` TEXT NOT NULL, /* moduleUUID */ `label` TEXT NOT NULL, `description` TEXT NOT NULL, `permissions` TEXT NOT NULL, `is_valid` INTEGER NOT NULL, `auth_data` TEXT NOT NULL, `created_at` INTEGER NOT NULL, `last_access` INTEGER NOT NULL, `updated_at` INTEGER NOT NULL, PRIMARY KEY(id));""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('webinterface_api_auth', 'created_at')) yield Registry.DBPOOL.runQuery(create_index('webinterface_api_auth', 'updated_at'))
def create_table_permissions(Registry, **kwargs): """ """ table = """CREATE TABLE `permissions` ( `id` TEXT NOT NULL, `gateway_id` TEXT NOT NULL, `updated_at` INTEGER NOT NULL, `created_at` INTEGER NOT NULL );""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('permissions', 'id', unique=True))
def create_table_commands(Registry, **kwargs): """ Defines the commands table. Lists all possible commands a local or remote gateway can perform. """ table = """CREATE TABLE `commands` ( `id` TEXT NOT NULL, /* commandUUID */ `machine_label` TEXT NOT NULL, `voice_cmd` TEXT, `label` TEXT NOT NULL, `description` TEXT, `always_load` INTEGER DEFAULT 0, `public` INTEGER NOT NULL, `status` INTEGER NOT NULL, `created_at` INTEGER NOT NULL, `updated_at` INTEGER NOT NULL, PRIMARY KEY(id) );""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('commands', 'id', unique=True)) yield Registry.DBPOOL.runQuery(create_index('commands', 'machine_label', unique=True)) yield Registry.DBPOOL.runQuery(create_index('commands', 'voice_cmd'))
def create_table_device_type_commands(Registry, **kwargs): """ All possible commands for a given device type. For examples, appliances are on and off. """ table = """CREATE TABLE `device_type_commands` ( `id` TEXT NOT NULL, `device_type_id` TEXT NOT NULL, `command_id` TEXT NOT NULL, `created_at` INTEGER NOT NULL, UNIQUE (device_type_id, command_id) ON CONFLICT IGNORE);""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('device_type_commands', 'device_type_id'))
def create_table_module_installed(Registry, **kwargs): """ """ # Tracks what versions of a module is installed, when it was installed, and last checked for new version. table = """CREATE TABLE `module_installed` ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `module_id` TEXT NOT NULL, /* module.id */ `installed_version` TEXT NOT NULL, `install_at` INTEGER NOT NULL, `last_check` INTEGER NOT NULL);""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('module_installed', 'module_id'))
def create_table_nodes(Registry, **kwargs): """ """ # Defines the statistics data table. Stores node items. table = """CREATE TABLE `nodes` ( `id` TEXT NOT NULL, `parent_id` TEXT, `gateway_id` TEXT NOT NULL, `node_type` TEXT NOT NULL, `weight` INTEGER NOT NULL, `label` TEXT, `machine_label` TEXT, `always_load` INTEGER NOT NULL, `destination` TEXT NOT NULL, `data` BLOB, `data_content_type` TEXT NOT NULL, `status` INTEGER NOT NULL, /* Timestemp when msg was ack'd by the user. */ `updated_at` INTEGER NOT NULL, `created_at` INTEGER NOT NULL );""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('nodes', 'id')) yield Registry.DBPOOL.runQuery(create_index('nodes', 'parent_id'))
def create_table_device_types(Registry, **kwargs): """ Device types defines the features of a device. For example, all X10 appliances or Insteon Lamps. """ table = """CREATE TABLE `device_types` ( `id` TEXT NOT NULL, `machine_label` TEXT NOT NULL, `label` TEXT NOT NULL, `description` TEXT, `category_id` TEXT, `platform` TEXT, `public` INTEGER, `status` INTEGER, `always_load` INTEGER DEFAULT 0, `created_at` INTEGER, `updated_at` INTEGER, UNIQUE (label) ON CONFLICT IGNORE, UNIQUE (machine_label) ON CONFLICT IGNORE, PRIMARY KEY(id) ON CONFLICT IGNORE);""" yield Registry.DBPOOL.runQuery(table) # yield Registry.DBPOOL.runQuery("CREATE UNIQUE INDEX IF NOT EXISTS device_types_machine_label_idx ON device_types (machine_label) ON CONFLICT IGNORE") # yield Registry.DBPOOL.runQuery("CREATE UNIQUE INDEX IF NOT EXISTS device_types_label_idx ON device_types (label) ON CONFLICT IGNORE") yield Registry.DBPOOL.runQuery(create_index('device_types', 'id', unique=True)) yield Registry.DBPOOL.runQuery(create_index('device_types', 'machine_label', unique=True))
def create_table_devices(Registry, **kwargs): """ Defines the devices table. Lists all possible devices for local gateway and related remote gateways. """ table = """CREATE TABLE `devices` ( `id` TEXT NOT NULL, `gateway_id` TEXT NOT NULL, `device_type_id` TEXT NOT NULL, `location_id` TEXT NOT NULL, `area_id` TEXT NOT NULL, `machine_label` TEXT NOT NULL, `label` TEXT NOT NULL, `description` TEXT, `statistic_label` TEXT, `statistic_lifetime` INTEGER DEFAULT 0, `statistic_type` TEXT, `statistic_bucket_size` TEXT, `data` BLOB, `notes` TEXT, `attributes` TEXT, `voice_cmd` TEXT, `voice_cmd_order` TEXT, `voice_cmd_src` TEXT, `energy_type` TEXT, `energy_tracker_source` TEXT, `energy_tracker_device` TEXT, `energy_map` TEXT, `pin_code` TEXT, `pin_required` INTEGER NOT NULL, `pin_timeout` INTEGER DEFAULT 0, `status` INTEGER NOT NULL, `created_at` INTEGER NOT NULL, `updated_at` INTEGER NOT NULL, /* FOREIGN KEY(device_type_id) REFERENCES artist(device_types) */ PRIMARY KEY(id));""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('devices', 'id', unique=True)) yield Registry.DBPOOL.runQuery(create_index('devices', 'device_type_id')) yield Registry.DBPOOL.runQuery(create_index('devices', 'gateway_id'))
def create_table_categories(Registry, **kwargs): """ System categories """ table = """CREATE TABLE `categories` ( `id` TEXT NOT NULL, /* commandUUID */ `parent_id` TEXT NOT NULL, `category_type` TEXT NOT NULL, `machine_label` TEXT NOT NULL, `label` TEXT NOT NULL, `description` TEXT, `status` INTEGER NOT NULL, `created_at` INTEGER NOT NULL, `updated_at` INTEGER NOT NULL, PRIMARY KEY(id) );""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('categories', 'id', unique=True))
def create_table_locations(Registry, **kwargs): """ All locations configured for an account. """ table = """CREATE TABLE `locations` ( `id` TEXT NOT NULL, `location_type` TEXT NOT NULL, `machine_label` TEXT NOT NULL, `environment` TEXT, `label` TEXT NOT NULL, `description` TEXT, `updated_at` INTEGER NOT NULL, `created_at` INTEGER NOT NULL);""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('locations', 'id')) yield Registry.DBPOOL.runQuery("CREATE UNIQUE INDEX IF NOT EXISTS locations_machinelabel_idx ON locations (location_type, machine_label)") yield Registry.DBPOOL.runQuery("CREATE UNIQUE INDEX IF NOT EXISTS locations_label_idx ON locations (location_type, label)")
def create_table_states(Registry, **kwargs): """ """ # Defines the tables used to store state information. table = """CREATE TABLE `states` ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `gateway_id` TEXT NOT NULL, `name` TEXT NOT NULL, `value_type` TEXT, `value` INTEGER, `live` INTEGER NOT NULL, `created_at` INTEGER NOT NULL, `updated_at` INTEGER NOT NULL);""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery("CREATE INDEX IF NOT EXISTS name_gateway_id_IDX ON states (name, gateway_id)") yield Registry.DBPOOL.runQuery(create_index('states', 'created_at'))
def create_table_tasks(Registry, **kwargs): """ """ # Used by the tasks library to start various tasks. table = """CREATE TABLE `tasks` ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `run_section` INTEGER NOT NULL, `run_once` INTEGER, `run_interval` INTEGER, `task_component` TEXT NOT NULL, `task_name` TEXT NOT NULL, `task_arguments` BLOB, `source` TEXT NOT NULL, `created_at` INTEGER NOT NULL );""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('tasks', 'id'))
def create_table_user_permission(Registry, **kwargs): """ """ table = """CREATE TABLE `user_permission` ( `id` TEXT NOT NULL, `user_id` TEXT NOT NULL, `gateway_id` TEXT NOT NULL, `permission_id` TEXT NOT NULL, `updated_at` INTEGER NOT NULL, `created_at` INTEGER NOT NULL, CONSTRAINT fk_user_permission FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE );""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('user_permission', 'id', unique=True))
def create_table_statistics(Registry, **kwargs): """ """ # Defines the statistics data table. Stores statistics. table = """CREATE TABLE `statistics` ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `bucket_time` DECIMAL(13,3) NOT NULL, `bucket_size` INTEGER NOT NULL, `bucket_lifetime` INTEGER, `bucket_type` TEXT NOT NULL, `bucket_name` TEXT NOT NULL, `bucket_value` REAL NOT NULL, `bucket_average_data` TEXT, `anon` INTEGER NOT NULL DEFAULT 0, /* anon data */ `uploaded` INTEGER NOT NULL DEFAULT 0, `finished` INTEGER NOT NULL DEFAULT 0, `updated_at` INTEGER NOT NULL);""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('statistics', 'bucket_type')) yield Registry.DBPOOL.runQuery("CREATE UNIQUE INDEX IF NOT EXISTS table_b_t_IDX ON statistics (bucket_name, bucket_type, bucket_time)") yield Registry.DBPOOL.runQuery("CREATE INDEX IF NOT EXISTS table_t_n_t_IDX ON statistics (finished, uploaded, anon)")
def create_table_gateways(Registry, **kwargs): """ All gateways in the current cluster. """ table = """CREATE TABLE `gateways` ( `id` TEXT NOT NULL, `is_master` BOOLEAN, `master_gateway` TEXT, `machine_label` TEXT NOT NULL, `label` TEXT NOT NULL, `description` TEXT, `mqtt_auth` TEXT, `mqtt_auth_prev` TEXT, `mqtt_auth_next` TEXT, `mqtt_auth_last_rotate` TEXT, `fqdn` TEXT, `internal_ipv4` TEXT, `external_ipv4` TEXT, `internal_ipv6` TEXT, `external_ipv6` TEXT, `internal_port` INTEGER, `external_port` INTEGER, `internal_secure_port` INTEGER, `external_secure_port` INTEGER, `internal_mqtt` INTEGER, `internal_mqtt_le` INTEGER, `internal_mqtt_ss` INTEGER, `internal_mqtt_ws` INTEGER, `internal_mqtt_ws_le` INTEGER, `internal_mqtt_ws_ss` INTEGER, `external_mqtt` INTEGER, `external_mqtt_le` INTEGER, `external_mqtt_ss` INTEGER, `external_mqtt_ws` INTEGER, `external_mqtt_ws_le` INTEGER, `external_mqtt_ws_ss` INTEGER, `status` INTEGER NOT NULL, `created_at` INTEGER NOT NULL, `updated_at` INTEGER NOT NULL, PRIMARY KEY(id));""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('gateways', 'id', unique=True))
def create_table_notifications(Registry, **kwargs): """ """ # Defines the statistics data table. Stores statistics. table = """CREATE TABLE `notifications` ( `id` TEXT NOT NULL, `gateway_id` TEXT NOT NULL, `type` TEXT NOT NULL, /* system, user, etc */ `priority` TEXT NOT NULL, /* debug, low, normal, high, urgent */ `source` TEXT NOT NULL, /* where this message was created_at */ `always_show` INTEGER NOT NULL, /* If notification should always show until user clears it. */ `always_show_allow_clear` INTEGER NOT NULL, /* User allowed to clear notification form always_show. */ `acknowledged` INTEGER NOT NULL, /* Timestemp when msg was ack'd by the user. */ `acknowledged_at` INTEGER, /* Timestemp when msg was ack'd by the user. */ `user` TEXT, `title` TEXT, `message` TEXT, `meta` TEXT, /* Any extra meta data. JSON format */ `local` BOOL, `expire_at` INTEGER, /* timestamp when msg should expire */ `created_at` INTEGER NOT NULL);""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('notifications', 'id'))
def create_table_variable_fields(Registry, **kwargs): """ """ table = """CREATE TABLE `variable_fields` ( `id` TEXT NOT NULL, /* field_id */ `group_id` TEXT NOT NULL, `field_machine_label` TEXT NOT NULL, `field_label` TEXT NOT NULL, `field_description` TEXT NOT NULL, `field_help_text` TEXT NOT NULL, `field_weight` INTEGER DEFAULT 0, `value_required` INTEGER NOT NULL, `value_max` INTEGER, `value_min` INTEGER, `value_casing` TEXT NOT NULL, `encryption` TEXT NOT NULL, `input_type_id` TEXT NOT NULL, `default_value` TEXT NOT NULL, `multiple` INTEGER NOT NULL, `updated_at` INTEGER NOT NULL, `created_at` INTEGER NOT NULL);""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('variable_fields', 'group_id'))
def create_table_device_command_inputs(Registry, **kwargs): """ All possible inputs for a given device type/command/input. """ table = """CREATE TABLE `device_command_inputs` ( `id` TEXT NOT NULL, `device_type_id` TEXT NOT NULL, `command_id` TEXT NOT NULL, `input_type_id` TEXT NOT NULL, `label` TEXT NOT NULL, `machine_label` TEXT NOT NULL, `live_update` INTEGER NOT NULL, `value_required` INTEGER NOT NULL, `value_max` INTEGER NOT NULL, `value_min` INTEGER NOT NULL, `value_casing` TEXT NOT NULL, `encryption` TEXT NOT NULL, `notes` TEXT, `always_load` INTEGER DEFAULT 0, `updated_at` INTEGER NOT NULL, `created_at` INTEGER NOT NULL, UNIQUE (device_type_id, command_id, input_type_id) ON CONFLICT IGNORE);""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('device_command_inputs', 'device_type_id'))
def upgrade(Registry, **kwargs): # Handles version tracking for the database schema yield Registry.DBPOOL.runQuery('CREATE TABLE schema_version(table_name TEXT NOT NULL, version INTEGER NOT NULL, PRIMARY KEY(table_name))') yield Registry.DBPOOL.runQuery('INSERT INTO schema_version(table_name, version) VALUES ("core", 1)') # Defines the commands table. Lists all possible commands a local or remote gateway can perform. table = """CREATE TABLE `categories` ( `id` TEXT NOT NULL, /* commandUUID */ `parent_id` TEXT NOT NULL, `category_type` TEXT NOT NULL, `machine_label` TEXT NOT NULL, `label` TEXT NOT NULL, `description` TEXT, `status` INTEGER NOT NULL, `created` INTEGER NOT NULL, `updated` INTEGER NOT NULL, PRIMARY KEY(id) );""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('categories', 'id', unique=True)) yield Registry.DBPOOL.runQuery("CREATE UNIQUE INDEX IF NOT EXISTS categoires_type_machine_label_IDX ON categories (machine_label, category_type)") # Defines the commands table. Lists all possible commands a local or remote gateway can perform. table = """CREATE TABLE `commands` ( `id` TEXT NOT NULL, /* commandUUID */ `machine_label` TEXT NOT NULL, `voice_cmd` TEXT, `label` TEXT NOT NULL, `description` TEXT, `always_load` INTEGER DEFAULT 0, `public` INTEGER NOT NULL, `status` INTEGER NOT NULL, `created` INTEGER NOT NULL, `updated_srv` INTEGER DEFAULT 0, `updated` INTEGER NOT NULL, PRIMARY KEY(id) );""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('commands', 'id', unique=True)) yield Registry.DBPOOL.runQuery(create_index('commands', 'machine_label', unique=True)) yield Registry.DBPOOL.runQuery(create_index('commands', 'voice_cmd')) # Defines the devices table. Lists all possible devices for local gateway and related remote gateways. table = """CREATE TABLE `devices` ( `id` TEXT NOT NULL, `gateway_id` TEXT NOT NULL, `device_type_id` TEXT NOT NULL, `label` TEXT NOT NULL, `description` TEXT, `statistic_label` TEXT, `notes` TEXT, `voice_cmd` TEXT, `voice_cmd_order` TEXT, `voice_cmd_src` TEXT, `energy_type` TEXT, `energy_tracker_source` TEXT, `energy_tracker_device` TEXT, `energy_map` TEXT, `pin_code` TEXT, `pin_required` INTEGER NOT NULL, `pin_timeout` INTEGER DEFAULT 0, `status` INTEGER NOT NULL, `created` INTEGER NOT NULL, `updated_srv` INTEGER DEFAULT 0, `updated` INTEGER NOT NULL, /* FOREIGN KEY(device_type_id) REFERENCES artist(device_types) */ PRIMARY KEY(id));""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('devices', 'id', unique=True)) yield Registry.DBPOOL.runQuery(create_index('devices', 'device_type_id')) yield Registry.DBPOOL.runQuery(create_index('devices', 'gateway_id')) # All possible inputs for a given device type/command/input. table = """CREATE TABLE `device_command_inputs` ( `id` TEXT NOT NULL, `device_type_id` TEXT NOT NULL, `command_id` TEXT NOT NULL, `input_type_id` TEXT NOT NULL, `live_update` INTEGER NOT NULL, `value_required` INTEGER NOT NULL, `value_max` INTEGER NOT NULL, `value_min` INTEGER NOT NULL, `value_casing` TEXT NOT NULL, `encryption` TEXT NOT NULL, `notes` TEXT, `always_load` INTEGER DEFAULT 0, `updated` INTEGER NOT NULL, `created` INTEGER NOT NULL, UNIQUE (device_type_id, command_id, input_type_id) ON CONFLICT IGNORE);""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('device_command_inputs', 'device_type_id')) # Defines the device status table. Stores device status information. table = """CREATE TABLE `device_status` ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `device_id` TEXT NOT NULL, /* device_id */ `set_time` REAL NOT NULL, `energy_usage` INTEGER NOT NULL, `human_status` TEXT NOT NULL, `machine_status` TEXT NOT NULL, `machine_status_extra` TEXT, `source` TEXT NOT NULL, `uploaded` INTEGER NOT NULL DEFAULT 0, `uploadable` INTEGER NOT NULL DEFAULT 0 /* For security, only items marked as 1 can be sent externally */ );""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('device_status', 'device_id')) yield Registry.DBPOOL.runQuery(create_index('device_status', 'uploaded')) # Device types defines the features of a device. For example, all X10 appliances or Insteon Lamps. table = """CREATE TABLE `device_types` ( `id` TEXT NOT NULL, `machine_label` TEXT NOT NULL, `label` TEXT NOT NULL, `description` TEXT, `category_id` TEXT, `public` INTEGER, `status` INTEGER, `always_load` INTEGER DEFAULT 0, `created` INTEGER, `updated_srv` INTEGER DEFAULT 0, `updated` INTEGER, UNIQUE (label) ON CONFLICT IGNORE, UNIQUE (machine_label) ON CONFLICT IGNORE, PRIMARY KEY(id) ON CONFLICT IGNORE);""" yield Registry.DBPOOL.runQuery(table) # yield Registry.DBPOOL.runQuery("CREATE UNIQUE INDEX IF NOT EXISTS device_types_machine_label_idx ON device_types (machine_label) ON CONFLICT IGNORE") # yield Registry.DBPOOL.runQuery("CREATE UNIQUE INDEX IF NOT EXISTS device_types_label_idx ON device_types (label) ON CONFLICT IGNORE") yield Registry.DBPOOL.runQuery(create_index('device_types', 'id', unique=True)) yield Registry.DBPOOL.runQuery(create_index('device_types', 'machine_label', unique=True)) # All possible commands for a given device type. For examples, appliances are on and off. table = """CREATE TABLE `device_type_commands` ( `id` TEXT NOT NULL, `device_type_id` TEXT NOT NULL, `command_id` TEXT NOT NULL, UNIQUE (device_type_id, command_id) ON CONFLICT IGNORE);""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('device_type_commands', 'device_type_id')) # yield Registry.DBPOOL.runQuery(create_index('command_device_types', 'command_id')) # yield Registry.DBPOOL.runQuery("CREATE INDEX IF NOT EXISTS command_device_types_command_id_device_type_id_IDX ON command_device_types (command_id, device_type_id)") # Used for quick access to GPG keys instead of key ring. table = """CREATE TABLE `gpg_keys` ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `endpoint` TEXT NOT NULL, `fingerprint` TEXT NOT NULL, `length` INTEGER NOT NULL, `expires` INTEGER NOT NULL, `created` INTEGER NOT NULL );""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('gpg_keys', 'endpoint')) yield Registry.DBPOOL.runQuery(create_index('gpg_keys', 'fingerprint')) # Device types defines the features of a device. For example, all X10 appliances or Insteon Lamps. table = """CREATE TABLE `input_types` ( `id` TEXT NOT NULL, `category_id` TEXT NOT NULL, `machine_label` TEXT NOT NULL, `label` TEXT NOT NULL, `description` TEXT, `input_regex` TEXT, `public` INTEGER, `always_load` INTEGER DEFAULT 0, `status` INTEGER, `created` INTEGER, `updated_srv` INTEGER DEFAULT 0, `updated` INTEGER, UNIQUE (label) ON CONFLICT IGNORE, UNIQUE (machine_label) ON CONFLICT IGNORE, PRIMARY KEY(id) ON CONFLICT IGNORE);""" yield Registry.DBPOOL.runQuery(table) # To be completed table = """CREATE TABLE `logs` ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `type` TEXT NOT NULL, /* system, user, etc */ `priority` TEXT NOT NULL, /* debug, low, normal, high, urgent */ `source` TEXT NOT NULL, /* where this message was created */ `message` TEXT, /* Message data */ `meta` TEXT, /* Any extra meta data. JSON format */ `created` INTEGER NOT NULL);""" # Defines the config table for the local gateway. table = """CREATE TABLE `meta` ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `meta_key` TEXT NOT NULL, `meta_value` TEXT NOT NULL, `created` INTEGER NOT NULL, `updated` INTEGER NOT NULL);""" # yield Registry.DBPOOL.runQuery(table) # yield Registry.DBPOOL.runQuery(create_index('meta', 'meta_key')) # yield Registry.DBPOOL.runQuery("CREATE UNIQUE INDEX IF NOT EXISTS configs_config_key_config_key_IDX ON configs (config_path, config_key)") # Stores module information table = """CREATE TABLE `modules` ( `id` TEXT NOT NULL, /* moduleUUID */ `gateway_id` TEXT NOT NULL, `machine_label` TEXT NOT NULL, `module_type` TEXT NOT NULL, `label` TEXT NOT NULL, `short_description` TEXT, `description` TEXT, `description_formatting` TEXT, `see_also` TEXT, `repository_link` TEXT, `issue_tracker_link` TEXT, `install_count` INTEGER DEFAULT 0, `doc_link` TEXT, `git_link` TEXT, `install_branch` TEXT NOT NULL, `prod_branch` TEXT NOT NULL, `dev_branch` TEXT, `prod_version` TEXT, `dev_version` TEXT, `always_load` INTEGER DEFAULT 0, `public` INTEGER NOT NULL, `status` INTEGER NOT NULL, /* disabled, enabled, deleted */ `created` INTEGER NOT NULL, `updated_srv` INTEGER DEFAULT 0, `updated` INTEGER NOT NULL, PRIMARY KEY(id));""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('modules', 'machine_label')) # Tracks what versions of a module is installed, when it was installed, and last checked for new version. table = """CREATE TABLE `module_installed` ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `module_id` TEXT NOT NULL, /* module.id */ `installed_version` TEXT NOT NULL, `install_time` INTEGER NOT NULL, `last_check` INTEGER NOT NULL);""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('module_installed', 'module_id')) ## Create view for modules ## view = """CREATE VIEW modules_view AS SELECT modules.*, module_installed.installed_version, module_installed. install_time, module_installed.last_check FROM modules LEFT OUTER JOIN module_installed ON modules.id = module_installed.module_id""" yield Registry.DBPOOL.runQuery(view) # Defines the statistics data table. Stores statistics. table = """CREATE TABLE `notifications` ( `id` TEXT NOT NULL, `type` TEXT NOT NULL, /* system, user, etc */ `priority` TEXT NOT NULL, /* debug, low, normal, high, urgent */ `source` TEXT NOT NULL, /* where this message was created */ `expire` INTEGER NOT NULL, /* timestamp when msg should expire */ `always_show` INTEGER NOT NULL, /* If notification should always show until user clears it. */ `always_show_allow_clear` INTEGER NOT NULL, /* User allowed to clear notification form always_show. */ `acknowledged` INTEGER NOT NULL, /* Timestemp when msg was ack'd by the user. */ `title` TEXT, /* Message data */ `message` TEXT, /* Message data */ `meta` TEXT, /* Any extra meta data. JSON format */ `created` INTEGER NOT NULL);""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('notifications', 'id')) # Defines the SQL Dict table. Used by the :class:`SQLDict` class to maintain persistent dictionaries. table = """CREATE TABLE `sqldict` ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `component` TEXT NOT NULL, `dict_name` INTEGER NOT NULL, `dict_data` BLOB, `created` INTEGER NOT NULL, `updated` INTEGER NOT NULL);""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('sqldict', 'dict_name')) yield Registry.DBPOOL.runQuery(create_index('sqldict', 'component')) # Defines the tables used to store state information. table = """CREATE TABLE `states` ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `name` TEXT NOT NULL, `value_type` TEXT, `value` INTEGER NOT NULL, `live` INTEGER NOT NULL, `created` INTEGER NOT NULL);""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('states', 'name')) yield Registry.DBPOOL.runQuery(create_index('states', 'created')) # Defines the statistics data table. Stores statistics. table = """CREATE TABLE `statistics` ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `bucket` INTEGER NOT NULL, `type` TEXT NOT NULL, `name` TEXT NOT NULL, `value` REAL NOT NULL, `averagedata` BLOB, `anon` INTEGER NOT NULL DEFAULT 0, /* anon data */ `uploaded` INTEGER NOT NULL DEFAULT 0, `updated` INTEGER NOT NULL);""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('statistics', 'bucket')) yield Registry.DBPOOL.runQuery(create_index('statistics', 'name')) yield Registry.DBPOOL.runQuery(create_index('statistics', 'type')) table = """CREATE TABLE `users` ( `id` TEXT NOT NULL, `gateway_id` TEXT NOT NULL, `user_id` TEXT NOT NULL, `email` TEXT NOT NULL, `updated` INTEGER NOT NULL, `created` INTEGER NOT NULL );""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('users', 'id', unique=True)) yield Registry.DBPOOL.runQuery(create_index('users', 'email')) table = """CREATE TABLE `user_permission` ( `id` TEXT NOT NULL, `user_id` TEXT NOT NULL, `gateway_id` TEXT NOT NULL, `permission_id` TEXT NOT NULL, `updated` INTEGER NOT NULL, `created` INTEGER NOT NULL );""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('user_permission', 'id', unique=True)) table = """CREATE TABLE `permissions` ( `id` TEXT NOT NULL, `gateway_id` TEXT NOT NULL, `updated` INTEGER NOT NULL, `created` INTEGER NOT NULL );""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('permissions', 'id', unique=True)) # Defines the web interface session store. Used by the :class:`WebInterface` class to maintain session information table = """CREATE TABLE `webinterface_sessions` ( `id` TEXT NOT NULL, /* moduleUUID */ `session_data` TEXT NOT NULL, `created` INTEGER NOT NULL, `last_access` INTEGER NOT NULL, `updated` INTEGER NOT NULL, PRIMARY KEY(id));""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('webinterface_sessions', 'created')) yield Registry.DBPOOL.runQuery(create_index('webinterface_sessions', 'updated')) # The following three tables and following views manages the variables set for devices and modules. table = """ CREATE TABLE `variable_groups` ( `id` TEXT NOT NULL, /* group_id */ `relation_id` TEXT NOT NULL, `relation_type` TEXT NOT NULL, `group_machine_label` TEXT NOT NULL, `group_label` TEXT NOT NULL, `group_description` TEXT NOT NULL, `group_weight` INTEGER DEFAULT 0, `status` INTEGER NOT NULL, /* disabled, enabled, deleted */ `updated_srv` INTEGER DEFAULT 0, `updated` INTEGER NOT NULL, `created` INTEGER NOT NULL, PRIMARY KEY(id));""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery("CREATE INDEX IF NOT EXISTS variable_groups_relation_id_type_idx ON variable_groups (relation_id, relation_type)") table = """ CREATE TABLE `variable_fields` ( `id` TEXT NOT NULL, /* field_id */ `group_id` TEXT NOT NULL, `field_machine_label` TEXT NOT NULL, `field_label` TEXT NOT NULL, `field_description` TEXT NOT NULL, `field_weight` INTEGER DEFAULT 0, `value_required` INTEGER NOT NULL, `value_max` INTEGER NOT NULL, `value_min` INTEGER NOT NULL, `value_casing` TEXT NOT NULL, `encryption` TEXT NOT NULL, `input_type_id` TEXT NOT NULL, `default_value` TEXT NOT NULL, `help_text` TEXT NOT NULL, `multiple` INTEGER NOT NULL, `updated_srv` INTEGER DEFAULT 0, `updated` INTEGER NOT NULL, `created` INTEGER NOT NULL );""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery(create_index('variable_fields', 'group_id')) # yield Registry.DBPOOL.runQuery("CREATE UNIQUE INDEX IF NOT EXISTS device_types_machine_label_idx ON device_types (machine_label) ON CONFLICT IGNORE") table = """ CREATE TABLE `variable_data` ( `id` TEXT NOT NULL, /* field_id */ `gateway_id` TEXT DEFAULT 0, `field_id` TEXT NOT NULL, `relation_id` TEXT NOT NULL, `relation_type` TEXT NOT NULL, `data` TEXT NOT NULL, `data_weight` INTEGER DEFAULT 0, `updated_srv` INTEGER DEFAULT 0, `updated` INTEGER NOT NULL, `created` INTEGER NOT NULL, PRIMARY KEY(id));""" yield Registry.DBPOOL.runQuery(table) yield Registry.DBPOOL.runQuery("CREATE INDEX IF NOT EXISTS variable_data_id_type_idx ON variable_data (field_id, relation_id)") ## Create view for easily consuming the above tables during gateway startup # view = """CREATE VIEW variables_view AS # SELECT modules.*, module_installed.installed_version, module_installed. install_time, module_installed.last_check # FROM modules LEFT OUTER JOIN module_installed ON modules.id = module_installed.module_id""" # yield Registry.DBPOOL.runQuery(view) # Stores variables for modules and devices. Variables are set by the server, and read here. Not a two-way sync (yet?). # table = """CREATE TABLE `variables` ( # `id` TEXT NOT NULL, /* field_id */ # `variable_type` TEXT NOT NULL, # `variable_id` TEXT NOT NULL, # `foreign_id` TEXT NOT NULL, # `weight` INTEGER DEFAULT 0, # `data_weight` INTEGER DEFAULT 0, # `machine_label` TEXT NOT NULL, # `label` TEXT NOT NULL, # `value` TEXT NOT NULL, # `updated_srv` INTEGER NOT NULL DEFAULT 0, # `updated` INTEGER NOT NULL, # `created` INTEGER NOT NULL, # PRIMARY KEY(id));""" # yield Registry.DBPOOL.runQuery(table) # yield Registry.DBPOOL.runQuery("CREATE INDEX IF NOT EXISTS variables_foreign_id_variable_type_idx ON variables (variable_type, foreign_id)") ## Create views ## view = """CREATE VIEW devices_view AS SELECT devices.*, device_types.machine_label AS device_type_machine_label, categories.machine_label as category_machine_label FROM devices JOIN device_types ON devices.device_type_id = device_types.id JOIN categories ON device_types.category_id = categories.id """ yield Registry.DBPOOL.runQuery(view) view = """CREATE VIEW variable_data_view AS SELECT variable_data.id, variable_data.gateway_id, variable_data.field_id, variable_data.relation_id, variable_data.relation_type, variable_data.data, variable_data.data_weight, variable_data.updated as data_updated, variable_data.created as data_created, variable_fields.field_machine_label,variable_fields.field_label, variable_fields.field_description, variable_fields.field_weight, variable_fields.encryption, variable_fields.input_type_id, variable_fields. default_value, variable_fields.help_text, variable_fields.value_required, variable_fields.value_min, variable_fields.value_max,variable_fields.value_casing, variable_fields.multiple, variable_fields.created as field_created, variable_fields.updated as field_updated FROM variable_data JOIN variable_fields ON variable_data.field_id = variable_fields.id""" yield Registry.DBPOOL.runQuery(view)