Gladys V4 - Sensor display in the interface

Presentations
11

@bertrandda Unfortunately, your solution doesn’t work 100%. In fact, you’ve pointed out a bug (or a feature^^) in Sequelize!

In SQLite, ALTER TABLE doesn’t exist. The only way to update a table is to rename it, recreate it, and migrate the data.

Sequelize has abstracted this part and does it when you perform a changeColumn:

Executing (default): PRAGMA TABLE_INFO(`t_device`);
Executing (default): CREATE TABLE IF NOT EXISTS `t_device_backup` (`id` UUID NOT NULL PRIMARY KEY, `service_id` UUID NOT NULL, `room_id` UUID, `name` VARCHAR(255) NOT NULL, `selector` VARCHAR(255) NOT NULL, `model` VARCHAR(255), `external_id` VARCHAR(255) NOT NULL, `should_poll` TINYINT(1) NOT NULL DEFAULT 0, `poll_frequency` INTEGER, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL);
Executing (default): INSERT INTO `t_device_backup` SELECT `id`, `service_id`, `room_id`, `name`, `selector`, `model`, `external_id`, `should_poll`, `poll_frequency`, `created_at`, `updated_at` FROM `t_device`;
Executing (default): DROP TABLE `t_device`;
Executing (default): CREATE TABLE IF NOT EXISTS `t_device` (`id` UUID NOT NULL PRIMARY KEY, `service_id` UUID NOT NULL, `room_id` UUID, `name` VARCHAR(255) NOT NULL, `selector` VARCHAR(255) NOT NULL, `model` VARCHAR(255), `external_id` VARCHAR(255) NOT NULL, `should_poll` TINYINT(1) NOT NULL DEFAULT 0, `poll_frequency` INTEGER, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL);
Executing (default): INSERT INTO `t_device` SELECT `id`, `service_id`, `room_id`, `name`, `selector`, `model`, `external_id`, `should_poll`, `poll_frequency`, `created_at`, `updated_at` FROM `t_device_backup`;
Executing (default): DROP TABLE `t_device_backup`;

Or, if you read the code carefully, there is a bug in this implementation: foreign keys and constraints are not migrated. So yes, your name no longer has constraints: but all other columns have also lost their constraints, and the table ends up with no protections. The selector is no longer unique, the foreign keys are gone, etc…

See code:

After digging through Sequelize’s GitHub, there is an issue created in 2017 that references the problem:
https://github.com/sequelize/sequelize/issues/7078

But apparently, the bug is still not resolved..

Three options:

  • We use a « hack » that considers overriding one of Sequelize’s functions during this migration to re-add the constraints.
  • We find a way to fix Sequelize and propose a PR (though I’m not sure it will be merged before we need it)
  • We forget about doing a changeColumn and ask people to recreate the DB… which fixes it in the short term, but if in the long term we want to change a column, the problem is still there!