Split Zone Table to separate out sensor related information

[pihome-shc]

before user can migrate database user must upgrade to version 1.75 otherwise they will have problems with database.

[twa127]

true as the update on 16th July altered the zone table, I suppose I could have added a bit more complexity to the new zone table creation code so that it could have coped with older versions of the zone table

[twa127]

I've had a look at the ver 1.61 zone table, if you think it is needed I can change migrate_db.php so that it can cope with either format of the zone table

[pihome-shc]

There was boiler id foreign key in zone table, i had to remove that manually, how much work is that?

[twa127]

The current migration copies the zone table data to an array, then modifies the existing zone table structure, removing the unwanted columns and then populates the new zone_sensors table from the array. First thoughts would be again copy the existing data to the array, then drop the existing zone table and FKs, then create the new zone, zone_type and zone_sensors tables and finally populate the tables from the array

[pihome-shc]

what if you copy only required column to array and drop table and re-create it, this way we'll have clean zone table?

[twa127]

it should work with all data to array, then create empty zone, zone_type and zone_sensors, the populate with data from array ignoring any fields not needed

[twa127]

this is looking more complex than I first thought as the zone_type, add_on_logs and add_on_zone_logs tables are not present in the older database

[pihome-shc]

may be best to leave ? and let user to update first and then migrate ? once changes get merged into sql file what it be then?

[twa127]

give me until tomorrow to give it a bit more thought

[twa127]

actually the more I look at it the messier it gets :-( I think as you say it might be better to let the user upgrade to 1.75 and then migrate to 1.76

[twa127]

I've added to migrate.sql to remove the boiler FK and the boiler_id and gpio_pin columns if they exist

[pihome-shc]

ok, i think its best to leave it like that.

[twa127]

okay by me

[twa127]

This should work with older database

[pihome-shc]

can you check if Instructions in #355 are correct ?

[twa127]

looks okay to me :-)

[twa127]

Tested with ver 1.73 okay, anything earlier it has problems with the nodes table

[pihome-shc]

I don’t think we need to go that far, 1.73 is already very old now. Lets finalise this. Do you need me to any testing from 1.75?

[twa127]

I think it is ready to go, I've tested with 1.73 and your 1.75 and my 1.75, it looks okay

[pihome-shc]

355