Gladys V4 - Affichage de capteurs dans l'interface

pierre-gilles · Septembre 30, 2019, 9:09

@bertrandda malheureusement ta solution ne fonctionne pas à 100%, en fait tu as mis le doigt sur un bug (ou une feature^^) de sequelize!

En SQlite ALTER TABLE n’existe pas. La seule façon de mettre à jour une table est de la renommer, de la recréer et de migrer les données.

Sequelize a abstrait cette partie, et fait ça quand tu fais ton 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, si tu lis bien le code, il y a un bug dans cette implémentation: les foreign key + les contraints ne sont pas migrées. Donc oui, ton name n’a plus de contraintes: mais toutes les autres columns ont aussi perdu leurs contraintes aussi, et la table se retrouve sans aucunes protections. Le selector n’est plus unique, les foreign keys ont sautés, etc…

Cf code :

github.com

sequelize/sequelize/blob/efd2f40620107cef01e56856457ba1b607af79f5/lib/dialects/sqlite/query-interface.js#L46

    
      
          
          
    const sql = this.QueryGenerator.removeColumnQuery(tableName, fields);
              const subQueries = sql.split(';').filter(q => q !== '');
          
          
    return Promise.each(subQueries, subQuery => this.sequelize.query(subQuery + ';', _.assign({raw: true}, options)));
            });
          }
          exports.removeColumn = removeColumn;
          
          
/**
            A wrapper that fixes SQLite's inability to change columns from existing tables.
            It will create a backup of the table, drop the table afterwards and create a
            new table with the same name but with a modified version of the respective column.
          
          
  @method changeColumn
            @for    QueryInterface
          
          
  @param  {String} tableName The name of the table.
            @param  {Object} attributes An object with the attribute's name as key and its options as value object.
            @param  {Object} options
            @param  {Boolean|Function} [options.logging] A function that logs the sql queries, or false for explicitly not logging these queries

Après fouille du GitHub de Sequelize, il y a une issue créé en 2017 qui référence le problème:

github.com/sequelize/sequelize

Sqlite remove/change/rename column don't pass foreign key references along in the new table

opened 12:33PM - 06 Jan 17 UTC

closed 04:22AM - 28 Jun 20 UTC

francescoinfante

type: bug type: refactor released status: awaiting investigation

## What you are doing? I'm trying to rename a column on an Sqlite db. I use `re…nameColumn` on the `QueryInterface` that, in the Sqlite case, is going to call `renameColumn` on the `SQLiteQueryInterface`. ## What do you expect to happen? I expect that: 1) a temporary table is created equal to the first one (except the renamed column) including foreign key references. 2) data is copied over to the temporary table 3) old table is deleted 4) a new table is created equal to the first one (except the renamed column) including foreign key references. 5) data is copied over to the new table ## What is actually happening? 1) a temporary table is created equal to the first one (except the renamed column) **NOT including foreign key references**. 2) data is copied over to the temporary table 3) old table is deleted 4) a new table is created equal to the first one (except the renamed column) **NOT including foreign key references**. 5) data is copied over to the new table This is the original table: ```SQL CREATE TABLE `matches` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `hash` VARCHAR(200) NOT NULL UNIQUE, `alertId` INTEGER REFERENCES `alerts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ``` This is the table after the renameColumn on 'hash' -> 'foo' ```SQL CREATE TABLE `matches` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `foo` VARCHAR(200) NOT NULL, `alertId` INTEGER) ``` __Dialect:__ sqlite __Database version:__ 3.9.x __Sequelize version:__ 3.24.x

Mais visiblement le bug n’est toujours pas résolu…

Trois options:

On utilise un “hack” qui considère à override une des fonctions de Sequelize lors de cette migration pour re-rajouter les contraintes.
On trouve un moyen de fixer Sequelize et on propose une PR (après pas sur que ça soit mergé avant qu’on en ait besoin)
On oublie de faire un changeColumn et on demande aux gens de re-créer la DB… ce qui fixe à court terme, mais bon si à long terme on veut changer une column le problème est toujours là !