search

pihome-shc / pihome

PiHome - Smart Heating, Ventilation and Air Conditioning (HVAC)
http://www.pihome.eu
Other
52 stars 25 forks source link

Zone with more than 1 controller #368

Open twa127 opened 3 years ago

twa127 commented 3 years ago

Hi,

What do you think about the option to link more than 1 controller to a single zone. Thinking about switching on a number of lamps all using the same schedule, for example lounge lamps to switch on/off 3 individual lamps with the same voice command or schedule?

pihome-shc commented 3 years ago

I m thinking how that is going to work?

twa127 commented 3 years ago

No idea as yet :-)) just wondered if anyone thought it was worthwhile

twa127 commented 3 years ago

Some grey matter churn :-)

Comments please :-)))))

pihome-shc commented 3 years ago

i have been thinking about this whole afternoon, i think we are back to issue 247, but on same note what if we can offer some sort of groping on non heating zone?

twa127 commented 3 years ago

Hi,

Bit of an update

I've created a new table zone_controllers and migrated the controler_id, controler_child_id and zone_id from the existing zone table z_cont

The above basically works but need to do some work on the transition between off/on and schedule off/on

Comments welcome

pihome-shc commented 3 years ago

@twa127 can you share your final table structure ?

twa127 commented 3 years ago

DROP TABLE IF EXISTS zone_controllers; CREATE TABLE IF NOT EXISTS zone_controllers ( id int(11) NOT NULL AUTO_INCREMENT, sync tinyint(4) NOT NULL, purge tinyint(4) NOT NULL COMMENT 'Mark For Deletion', zone_id int(11), controler_id int(11), controler_child_id int(11), PRIMARY KEY (id), KEY FK_zone_controllers_nodes (controler_id), KEY FK_zone_controllers_zone (zone_id), CONSTRAINT FK_zone_controllers_nodes FOREIGN KEY (controler_id) REFERENCES nodes (id), CONSTRAINT FK_zone_controllers_zone FOREIGN KEY (zone_id) REFERENCES zone (id) ) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

I've left controler_id and controler_child_id in the zone table for now but will delete them later

Attached php to create new table and migrate data

migrate_controller.zip

pihome-shc commented 3 years ago

i m struggling to understand things here, i mean you are trying have one zone with multi controller to switch on multiple things at the same time. but how we are going to manage this while creating zone?

i would suggest to have option for group any number of things and then assign each zone under that group and in schedule and all other places we will have individual zone and group name appears as zone, think like group as distribution list. this way we have ability to control individual zone and also in group and we can create, edit and delete without much effort.

if my brain is processing in good condition then your final version of table structures as following:

zone_type
    id
    sync
    purge
    name
    cat

zone
    id
    sync
    purge
    status
    index_id
    name
    graph_it
    zone_type_id

zone_senosrs
    id
    sync
    purge
    max_c
    max_operation_time
    hysteresis_time
    sp_deadband
    zone_id
    sensors_id
    sensor_child_id

zone_controller
    id
    sync
    purge
    zone_id
    controler_id
    controler_child_id
twa127 commented 3 years ago

table structure is as I proposes, with the exception of max_operation_time which moves to the zone table

In zone add/edit for cat2 zones we have the option to add/edit more than 1 controller

pihome-shc commented 3 years ago

Ok i m happy with that approach as well, so understand schedule and other places it will be treated as one zone with multiple controller?

twa127 commented 3 years ago

yep that was my thinking, was just trying to avoid having to say setup 3 individual schedules to turn on 3 lamps at the same time. But more than happy to consider another approach if you have an alternative idea

pihome-shc commented 3 years ago

I m happy with this approach, I wasn’t sure how single zone will have multiple controller, i think we can have plus + icon beside controller field to have option for user to add/edit multiple controller.

twa127 commented 3 years ago

okay I'll carry on. As a first release how about changing the table structure, update boiler.php and zone.php but keeping the single zone controller until I workout the switching logic ?

pihome-shc commented 3 years ago

yes that can work as well.

twa127 commented 3 years ago

Attached phase1 update, migrate_controller.php will create the new zone_controllers table, migrate the date, drop controler-id, controler_child_id and FK from the zone table, it will also run migrate_controller_view.sql to update the zone_view. Copy other files to correct locations. Should carry on working as before but now boiler.php can handle more than 1 controller per zone, there is no GUI yet to add extra controllers but you can test by manually adding to the zone_controllers table and adding an associated record to the messages_out table.

Please let me know what you think Zone Controllers Update.zip

twa127 commented 3 years ago

I've done a bit more work on boiler.php and homelist.php to sort out the cat2 zone switching logic, the flow is now as below. Sill need to modify zone.php to allow for multiple controller ids

flow6

controllers.zip

twa127 commented 3 years ago

I've still got an issue with boiler.php when it finishes a schedule, it is currently leaving leaving with the zone in manual start mode - working on this

pihome-shc commented 3 years ago

@twa127 very detailed process,

twa127 commented 3 years ago

Just a pity it don't work as it should :-)))

twa127 commented 3 years ago

I now have a cat2 zone switching process which appears to work okay, it allows for

I'm not sure how to handle the button on the Sonoff itself. The options are to control the state of the controller which has its button pressed or to set the sate of all the controllers associated to the zone. Any suggestions ?

The new process flow chart flo3

twa127 commented 3 years ago

Hi,

I'm trying to make zone.php work for multiple controllers, but am struggling with the code, does anyone have any suggestions, this is basically what I want it to look like zone1

I would have more hidden inputs across the page which I would make visible when a value was selected for the preceding one

pihome-shc commented 3 years ago

on form: is it because we have edit and add in same file? why not add + sign and keep that in loop? here is two example https://bootsnipp.com/snippets/ykXa https://stackoverflow.com/questions/35265094/dynamic-form-field-on-bootstrap

twa127 commented 3 years ago

No have add and edit in the same file does not make much of a difference, I have been able to place multiple inputs for controller id by using a row construct, looks okay on PC but no good on phone, I've attached what I've done so far, only the controller id and child id fields code work for add zone, I've not fixed the rest of the form yet. It might be better to go with your first example, I'll have a play. zone.zip

pihome-shc commented 3 years ago

just tested you zone file, there is not option to remove zone as in my examples. also another suggestion would be to go vertical instead of horizontal

twa127 commented 3 years ago

Hi,

I've now gone with a vertical layout a bit like the example https://bootsnipp.com/snippets/ykXa

I could not get the example to work properly within zone.php, so I've produced something similar. I think it is working okay in zone add mode but have not yet worked on zone edit mode. I've also added another field to the zone_controllers table as 'state' tinyint(4), I've modified the migrate code to add the field but for testing purposes you can just manually add to the table.

I'd be grateful if you could test and let me know how it goes, I'll now work on the zone edit.

update.zip

pihome-shc commented 3 years ago

i haven't done migration testing but zone add works, only one observation when i add controller the child id stacked below first child id, any way controller and child id stay together and when adding second controller it expand second controller and child id rather then controller 1 and controller 2 and child id 1 and child id 2.

image

twa127 commented 3 years ago

I'll see what I can do :-)

twa127 commented 3 years ago

Please give this a try and let me know what you think, edit zone partially works now, you can edit existing zones and add new controllers but deleting does not yet work..

zone.zip

pihome-shc commented 3 years ago

adding zone gives option for + and looks good, but when i go editing any existing zone i dont see + for adding extra zone, ps i haven't made any changes to database from your previous update as my dev system decided to take unplanned holidays :(

twa127 commented 3 years ago

The edit + function works okay for me, can you please test again after your able to update the database, I'm still work on the delete function when in edit mode

twa127 commented 3 years ago

Hi,

I think this is getting close to being a solution. Zone add/edit should work okay, with the ability to add/remove zone controllers in both add and edit mode. I've changed the way the zone_controllers and messages_out tables are updated, so that the update process removes any existing entries for the zone and then re-inserts the new values, this is to cope with the case when a zone controller is removed from a zone which has multiple zone controllers attached.

Can you please give it a try and let me know what you think.

update.zip

pihome-shc commented 3 years ago

migrate_controller file removed all controller relate from zone but then controller table is empty, looks like it didnt migrate data but only removed from zone table.

root@pihome:~# php /var/www/MySQL_Database/migrate_controller.php

2020-08-22 14:23:55 - PiHome Database Migration Script Started
---------------------------------------------------------------
2020-08-22 14:23:55 - php version 7.3.19-1~deb10u1 looks OK
Make Sure you have correct MySQL/MariaDB credentials as following
Hostname:     localhost
Database:     pihome
User Name:    pihomedbadmin
Password:     pihome2018
2020-08-22 14:23:55 - Testing Connection to MySQL/MariaDB Server.
2020-08-22 14:23:55 - Database Server Connection Successfull
2020-08-22 14:23:55 - Checking if Database Exits
2020-08-22 14:23:55 - Database pihome Found
2020-08-22 14:23:55 - Modifying Zone Table.
2020-08-22 14:23:56 - Zone Table Successfully Modified
2020-08-22 14:23:56 - Importing the data to the zone_sensors Table and Updating the zone Table.
2020-08-22 14:23:56 - Importing Migration SQL View File to Database, This could take few minuts.
2020-08-22 14:23:56 - DataBase Views File /var/www/MySQL_Database/migrate_controller_view.sql Imported Successfully
2020-08-22 14:23:56 - DataBase Updated Successfully
twa127 commented 3 years ago

Hi,

did you run it against the original zone table which has the controller data still in it?

twa127 commented 3 years ago

if you had already run the earlier version of migrate_controller.php to create the zone_controllers table all you needed to do was add the extra field to the zone_controllers table as 'state' tinyint(4)

pihome-shc commented 3 years ago

here is my zone table, also backup for database before making changes.

image

twa127 commented 3 years ago

that looks okay, can you post a database dump please

pihome-shc commented 3 years ago

i have to revers back the changes, and image in previous comment is before running migrate_controller.php dump attached.

pihome_temp.zip

twa127 commented 3 years ago

Hi,

for some reason I can't load , can you please send me a mysqldump copy

twa127 commented 3 years ago

sorry I had a cut and pasted problem :-( I've loaded your copy now

pihome-shc commented 3 years ago

dump from phpmyadmin pihome_tst.zip

twa127 commented 3 years ago

ran migrate_controller.php against your copy and it created the zone_controllers table okay zone3

pihome-shc commented 3 years ago

can you attach all updated in one zip file.

twa127 commented 3 years ago

hi,

Having problems dumping the database LOCK TABLES issue, perhaps time to re-install my test system

twa127 commented 3 years ago

okay think I sorted my problem, attached your database after running migrate_controller.php dump.zip

twa127 commented 3 years ago

found a bug in the migration script, fixed version attached migrate_controller.zip

twa127 commented 3 years ago

Attached latest versions of all development updated files, boiler.php has a few extra echo statements I'm using for debugging.

update.zip

twa127 commented 3 years ago

Attached the latest complete set of files for the multiple controllers change, I've added another field to the zone_controllers table and updated the migration script.

I think this is now at the serious testing stage, the one outstanding issue is how to manage the manual button on the Sonoff itself, I need to give that some more thought.

Please give it a try if you get chance. update.zip

pihome-shc commented 3 years ago

if i run migrate controller script on my live database: 

root@pihome:/var/www/MySQL_Database# php migrate_controller.php

   _____    _   _    _
  |  __ \  (_) | |  | |
  | |__) |  _  | |__| |   ___    _ __ ___     ___
  |  ___/  | | |  __  |  / _ \  | |_  \_ \   / _ \
  | |      | | | |  | | | (_) | | | | | | | |  __/
  |_|      |_| |_|  |_|  \___/  |_| |_| |_|  \___|

      S M A R T   H E A T I N G   C O N T R O L
****************************************************************
*   PiHome Migration Script Version 0.02 Build Date 31/07/2020 *
*   Last Modified on 31/07/2020                                *
*                                      Have Fun - PiHome.eu    *
****************************************************************
2020-08-24 00:54:15 - PiHome Database Migration Script Started
---------------------------------------------------------------
2020-08-24 00:54:15 - php version 7.3.19-1~deb10u1 looks OK
Make Sure you have correct MySQL/MariaDB credentials as following
Hostname:     localhost
Database:     pihome
User Name:    pihomedbadmin
Password:     pihome2018
2020-08-24 00:54:15 - Testing Connection to MySQL/MariaDB Server.
2020-08-24 00:54:15 - Database Server Connection Successfull
2020-08-24 00:54:15 - Checking if Database Exits
2020-08-24 00:54:15 - Database pihome Found
2020-08-24 00:54:15 - Modifying Zone Table.
2020-08-24 00:54:16 - Zone Table Successfully Modified
2020-08-24 00:54:16 - Importing the data to the zone_controllers Table and Updating the zone Table.
2020-08-24 00:54:16 - Importing Migration SQL View File to Database, This could take few minuts.
MySQL Database Error with Query CREATE VIEW zone_view AS
select zone.status, zone.zone_state, zone.sync, zone.id, zone.index_id, zone.name, ztype.type, ztype.category, zone.graph_it, zs.max_c, max_operation_time, zs.hysteresis_time,
zs.sp_deadband, sid.node_id as sensors_id, zs.sensor_child_id,
ctype.`type` AS controller_type, cid.node_id as controler_id, zc.controler_child_id,
IFNULL(lasts.last_seen, lasts_2.last_seen) as last_seen, IFNULL(msv.ms_version, msv_2.ms_version) as ms_version, IFNULL(skv.sketch_version, skv_2.sketch_version) as sketch_version
from zone
LEFT join zone_sensors zs on zone.id = zs.zone_id
LEFT join zone_controllers zc on zone.id = zc.zone_id
join zone_type ztype on zone.type_id = ztype.id
LEFT join nodes sid on zs.sensor_id = sid.id
join nodes ctype on zc.controler_id = ctype.id
join nodes cid on zc.controler_id = cid.id
LEFT join nodes lasts on zs.sensor_id = lasts.id
LEFT join nodes lasts_2 on zc.controler_id = lasts_2.id
LEFT join nodes msv on zs.sensor_id = msv.id
LEFT join nodes msv_2 on zc.controler_id = msv_2.id
LEFT join nodes skv on zs.sensor_id = skv.id
LEFT join nodes skv_2 on zc.controler_id = skv_2.id
where zone.`purge` = '0';
:Column 'max_operation_time' in field list is ambiguous
2020-08-24 00:54:16 - DataBase Views File /var/www/MySQL_Database/migrate_controller_view.sql Imported Successfully
2020-08-24 00:54:16 - DataBase Updated Successfully
root@pihome:/var/www/MySQL_Database#

here are the results of updating database:

root@pihome:/var/www/MySQL_Database# php update_db.php

   _____    _   _    _
  |  __ \  (_) | |  | |
  | |__) |  _  | |__| |   ___    _ __ ___     ___
  |  ___/  | | |  __  |  / _ \  | |_  \_ \   / _ \
  | |      | | | |  | | | (_) | | | | | | | |  __/
  |_|      |_| |_|  |_|  \___/  |_| |_| |_|  \___|

      S M A R T   H E A T I N G   C O N T R O L
***************************************************************
*   PiHome Datase Script Version 0.02 Build Date 15/09/2019   *
*   Last Modified on 05/04/2020                               *
*                                      Have Fun - PiHome.eu   *
***************************************************************
2020-08-24 00:56:02 - PiHome Database Update Script Started
---------------------------------------------------------------
2020-08-24 00:56:02 - php version 7.3.19-1~deb10u1 looks OK
Make Sure you have correct MySQL/MariaDB credentials as following
Hostname:     localhost
Database:     pihome
User Name:    pihomedbadmin
Password:     pihome2018
2020-08-24 00:56:02 - Testing Connection to MySQL/MariaDB Server.
2020-08-24 00:56:02 - Database Server Connection Successfull
2020-08-24 00:56:02 - Checking if Database Exits
2020-08-24 00:56:02 - Database pihome Found
2020-08-24 00:56:02 - Checking GITHUB for Database Update
2020-08-24 00:56:03 - Database Update Found on GITHUB
2020-08-24 00:56:03 - The Following Updates will be Applied to the Database.
2020-08-24 00:56:03 - ALTER TABLE `add_on_logs` MODIFY `expected_end_date_time` timestamp NULL;
2020-08-24 00:56:03 - ALTER TABLE `add_on_logs` MODIFY `start_cause` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `add_on_logs` MODIFY `start_datetime` timestamp NULL;
2020-08-24 00:56:03 - ALTER TABLE `add_on_logs` MODIFY `stop_cause` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `add_on_logs` MODIFY `stop_datetime` timestamp NULL;
2020-08-24 00:56:03 - ALTER TABLE `add_on_zone_logs` MODIFY `add_on_log_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `add_on_zone_logs` MODIFY `status` int(11);
2020-08-24 00:56:03 - ALTER TABLE `add_on_zone_logs` MODIFY `zone_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `away` MODIFY `away_button_child_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `away` MODIFY `away_button_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `away` MODIFY `end_datetime` timestamp NULL;
2020-08-24 00:56:03 - ALTER TABLE `away` MODIFY `start_datetime` timestamp NULL ON UPDATE current_timestamp();
2020-08-24 00:56:03 - ALTER TABLE `away` MODIFY `status` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `boiler` MODIFY `datetime` timestamp NULL ON UPDATE current_timestamp();
2020-08-24 00:56:03 - ALTER TABLE `boiler` MODIFY `fired_status` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `boiler` MODIFY `hysteresis_time` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `boiler` MODIFY `max_operation_time` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `boiler` MODIFY `name` char(50) CHARACTER SET utf16 COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `boiler` MODIFY `node_child_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `boiler` MODIFY `node_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `boiler` MODIFY `overrun` SMALLINT(6) NULL DEFAULT NULL;
2020-08-24 00:56:03 - ALTER TABLE `boiler` MODIFY `status` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `boiler_logs` MODIFY `expected_end_date_time` timestamp NULL;
2020-08-24 00:56:03 - ALTER TABLE `boiler_logs` MODIFY `start_cause` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `boiler_logs` MODIFY `start_datetime` timestamp NULL;
2020-08-24 00:56:03 - ALTER TABLE `boiler_logs` MODIFY `stop_cause` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `boiler_logs` MODIFY `stop_datetime` timestamp NULL;
2020-08-24 00:56:03 - ALTER TABLE `boost` MODIFY `boost_button_child_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `boost` MODIFY `boost_button_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `boost` MODIFY `minute` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `boost` MODIFY `status` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `boost` MODIFY `temperature` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `boost` MODIFY `time` timestamp NOT NULL ON UPDATE current_timestamp();
2020-08-24 00:56:03 - ALTER TABLE `boost` MODIFY `zone_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `crontab` MODIFY `command` char(50);
2020-08-24 00:56:03 - ALTER TABLE `crontab` MODIFY `comments` varchar(50);
2020-08-24 00:56:03 - ALTER TABLE `crontab` MODIFY `day` char(50);
2020-08-24 00:56:03 - ALTER TABLE `crontab` MODIFY `hour` char(50);
2020-08-24 00:56:03 - ALTER TABLE `crontab` MODIFY `min` char(50);
2020-08-24 00:56:03 - ALTER TABLE `crontab` MODIFY `month` char(50);
2020-08-24 00:56:03 - ALTER TABLE `crontab` MODIFY `output` char(50);
2020-08-24 00:56:03 - ALTER TABLE `crontab` MODIFY `status` char(50);
2020-08-24 00:56:03 - ALTER TABLE `crontab` MODIFY `weekday` char(50);
2020-08-24 00:56:03 - ALTER TABLE `email` MODIFY `from` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `email` MODIFY `password` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `email` MODIFY `smtp` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `email` MODIFY `status` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `email` MODIFY `to` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `email` MODIFY `username` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `frost_protection` MODIFY `datetime` timestamp NULL;
2020-08-24 00:56:03 - ALTER TABLE `gateway` MODIFY `find_gw` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `gateway` MODIFY `pid_running_since` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `gateway` MODIFY `pid` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `gateway` MODIFY `reboot` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `gateway` MODIFY `version` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `gateway_logs` MODIFY `location` char(50) COLLATE utf16_bin COMMENT 'ip address or serial port location i.e. /dev/ttyAMA0';
2020-08-24 00:56:03 - ALTER TABLE `gateway_logs` MODIFY `pid_datetime` timestamp NULL;
2020-08-24 00:56:03 - ALTER TABLE `gateway_logs` MODIFY `pid_start_time` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `gateway_logs` MODIFY `pid` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `gateway_logs` MODIFY `port` char(50) COLLATE utf16_bin COMMENT 'port number or baud rate for serial gateway';
2020-08-24 00:56:03 - ALTER TABLE `gateway_logs` MODIFY `type` char(50) COLLATE utf16_bin COMMENT 'serial or wifi';
2020-08-24 00:56:03 - ALTER TABLE `holidays` MODIFY `end_date_time` datetime;
2020-08-24 00:56:03 - ALTER TABLE `holidays` MODIFY `start_date_time` datetime;
2020-08-24 00:56:03 - ALTER TABLE `holidays` MODIFY `status` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `http_messages` MODIFY `command` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `http_messages` MODIFY `message_type` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `http_messages` MODIFY `node_id` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `http_messages` MODIFY `parameter` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `http_messages` MODIFY `zone_name` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `messages_in` MODIFY `child_id` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `messages_in` MODIFY `datetime` timestamp NOT NULL;
2020-08-24 00:56:03 - ALTER TABLE `messages_in` MODIFY `node_id` char(15) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `messages_in` MODIFY `payload` decimal(10,2);
2020-08-24 00:56:03 - ALTER TABLE `messages_in` MODIFY `sub_type` int(11);
2020-08-24 00:56:03 - ALTER TABLE `messages_out` MODIFY `datetime` timestamp NOT NULL ON UPDATE current_timestamp() COMMENT 'Current datetime';
2020-08-24 00:56:03 - ALTER TABLE `network_settings` MODIFY `dns1_address` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `network_settings` MODIFY `dns2_address` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `network_settings` MODIFY `gateway_address` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `network_settings` MODIFY `hostname` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `network_settings` MODIFY `interface_num` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `network_settings` MODIFY `interface_type` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `network_settings` MODIFY `ip_address` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `network_settings` MODIFY `mac_address` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `network_settings` MODIFY `net_mask` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `network_settings` MODIFY `primary_interface` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `nodes` MODIFY `last_seen` timestamp NULL ON UPDATE current_timestamp();
2020-08-24 00:56:03 - ALTER TABLE `nodes` MODIFY `min_value` int(11);
2020-08-24 00:56:03 - ALTER TABLE `nodes` MODIFY `ms_version` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `nodes` MODIFY `name` char(50) CHARACTER SET utf8 COLLATE utf8_bin;
2020-08-24 00:56:03 - ALTER TABLE `nodes` MODIFY `repeater` tinyint(4) COMMENT 'Repeater Feature Enabled=1 or Disable=0';
2020-08-24 00:56:03 - ALTER TABLE `nodes` MODIFY `sketch_version` char(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `nodes` MODIFY `status` char(50) CHARACTER SET utf8 COLLATE utf8_bin;
2020-08-24 00:56:03 - ALTER TABLE `nodes` MODIFY `type` CHAR(50) NOT NULL COLLATE 'utf8_bin';
2020-08-24 00:56:03 - ALTER TABLE `nodes_battery` MODIFY `bat_level` decimal(10,2);
2020-08-24 00:56:03 - ALTER TABLE `nodes_battery` MODIFY `bat_voltage` decimal(10,2);
2020-08-24 00:56:03 - ALTER TABLE `nodes_battery` MODIFY `node_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `nodes_battery` MODIFY `update` timestamp NULL;
2020-08-24 00:56:03 - ALTER TABLE `notice` MODIFY `datetime` timestamp NULL;
2020-08-24 00:56:03 - ALTER TABLE `notice` MODIFY `message` varchar(200) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `notice` MODIFY `status` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `override` MODIFY `status` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `override` MODIFY `temperature` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `override` MODIFY `time` timestamp NULL ON UPDATE current_timestamp();
2020-08-24 00:56:03 - ALTER TABLE `override` MODIFY `zone_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `schedule_daily_time` MODIFY `end` time;
2020-08-24 00:56:03 - ALTER TABLE `schedule_daily_time` MODIFY `sch_name` varchar(200) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `schedule_daily_time` MODIFY `start` time;
2020-08-24 00:56:03 - ALTER TABLE `schedule_daily_time` MODIFY `status` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `schedule_daily_time_zone` MODIFY `holidays_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `schedule_daily_time_zone` MODIFY `schedule_daily_time_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `schedule_daily_time_zone` MODIFY `status` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `schedule_daily_time_zone` MODIFY `zone_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `schedule_night_climat_zone` MODIFY `schedule_night_climate_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `schedule_night_climat_zone` MODIFY `status` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `schedule_night_climat_zone` MODIFY `zone_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `schedule_night_climate_time` MODIFY `end_time` time;
2020-08-24 00:56:03 - ALTER TABLE `schedule_night_climate_time` MODIFY `start_time` time;
2020-08-24 00:56:03 - ALTER TABLE `schedule_night_climate_time` MODIFY `status` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `system` MODIFY `backup_email` char(100) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `system` MODIFY `build` varchar(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `system` MODIFY `city` char(100) CHARACTER SET latin1;
2020-08-24 00:56:03 - ALTER TABLE `system` MODIFY `country` char(2) CHARACTER SET latin1;
2020-08-24 00:56:03 - ALTER TABLE `system` MODIFY `language` char(10) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `system` MODIFY `name` varchar(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `system` MODIFY `openweather_api` char(100) CHARACTER SET latin1;
2020-08-24 00:56:03 - ALTER TABLE `system` MODIFY `ping_home` bit(1);
2020-08-24 00:56:03 - ALTER TABLE `system` MODIFY `reboot` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `system` MODIFY `shutdown` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `system` MODIFY `timezone` varchar(50) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `system` MODIFY `update_alias` char(100) CHARACTER SET latin1;
2020-08-24 00:56:03 - ALTER TABLE `system` MODIFY `update_file` char(100) CHARACTER SET latin1;
2020-08-24 00:56:03 - ALTER TABLE `system` MODIFY `update_location` char(250) CHARACTER SET latin1;
2020-08-24 00:56:03 - ALTER TABLE `system` MODIFY `version` varchar(50) CHARACTER SET latin1;
2020-08-24 00:56:03 - ALTER TABLE `system` MODIFY `zip` char(100) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `user` MODIFY `account_date` timestamp NOT NULL;
2020-08-24 00:56:03 - ALTER TABLE `user` MODIFY `account_enable` tinyint(1);
2020-08-24 00:56:03 - ALTER TABLE `user` MODIFY `backup` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `user` MODIFY `cpdate` timestamp NOT NULL ON UPDATE current_timestamp();
2020-08-24 00:56:03 - ALTER TABLE `user` MODIFY `settings` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `user` MODIFY `support` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `user` MODIFY `users` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `userhistory` MODIFY `audit` tinytext;
2020-08-24 00:56:03 - ALTER TABLE `userhistory` MODIFY `date` datetime;
2020-08-24 00:56:03 - ALTER TABLE `userhistory` MODIFY `ipaddress` tinytext;
2020-08-24 00:56:03 - ALTER TABLE `userhistory` MODIFY `password` varchar(50);
2020-08-24 00:56:03 - ALTER TABLE `userhistory` MODIFY `username` varchar(50);
2020-08-24 00:56:03 - ALTER TABLE `weather` MODIFY `c` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `weather` MODIFY `description` varchar(50) COLLATE utf8_bin;
2020-08-24 00:56:03 - ALTER TABLE `weather` MODIFY `img` varchar(50) COLLATE utf8_bin;
2020-08-24 00:56:03 - ALTER TABLE `weather` MODIFY `last_update` timestamp NOT NULL ON UPDATE current_timestamp() COMMENT 'Last weather update';
2020-08-24 00:56:03 - ALTER TABLE `weather` MODIFY `location` varchar(50) COLLATE utf8_bin;
2020-08-24 00:56:03 - ALTER TABLE `weather` MODIFY `sunrise` varchar(50) COLLATE utf8_bin;
2020-08-24 00:56:03 - ALTER TABLE `weather` MODIFY `sunset` varchar(50) COLLATE utf8_bin;
2020-08-24 00:56:03 - ALTER TABLE `weather` MODIFY `title` varchar(50) COLLATE utf8_bin;
2020-08-24 00:56:03 - ALTER TABLE `weather` MODIFY `wind_speed` varchar(50) COLLATE utf8_bin;
2020-08-24 00:56:03 - ALTER TABLE `zone` MODIFY `controler_child_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `zone` MODIFY `controler_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `zone` MODIFY `index_id` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `zone` MODIFY `max_operation_time` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `zone` MODIFY `name` char(50) COLLATE utf8_bin;
2020-08-24 00:56:03 - ALTER TABLE `zone` MODIFY `status` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `zone` MODIFY `type_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `zone` MODIFY `zone_state` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `zone_current_state` MODIFY `controler_fault` int(1);
2020-08-24 00:56:03 - ALTER TABLE `zone_current_state` MODIFY `controler_seen_time` timestamp NULL;
2020-08-24 00:56:03 - ALTER TABLE `zone_current_state` MODIFY `mode` int(11);
2020-08-24 00:56:03 - ALTER TABLE `zone_current_state` MODIFY `overrun` tinyint(1);
2020-08-24 00:56:03 - ALTER TABLE `zone_current_state` MODIFY `sensor_fault` int(1);
2020-08-24 00:56:03 - ALTER TABLE `zone_current_state` MODIFY `sensor_reading_time` timestamp NULL;
2020-08-24 00:56:03 - ALTER TABLE `zone_current_state` MODIFY `sensor_seen_time` timestamp NULL;
2020-08-24 00:56:03 - ALTER TABLE `zone_current_state` MODIFY `status` tinyint(1);
2020-08-24 00:56:03 - ALTER TABLE `zone_current_state` MODIFY `temp_cut_in` decimal(4,1);
2020-08-24 00:56:03 - ALTER TABLE `zone_current_state` MODIFY `temp_cut_out` decimal(4,1);
2020-08-24 00:56:03 - ALTER TABLE `zone_current_state` MODIFY `temp_reading` decimal(4,1);
2020-08-24 00:56:03 - ALTER TABLE `zone_current_state` MODIFY `temp_target` decimal(4,1);
2020-08-24 00:56:03 - ALTER TABLE `zone_graphs` MODIFY `category` int(11);
2020-08-24 00:56:03 - ALTER TABLE `zone_graphs` MODIFY `child_id` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `zone_graphs` MODIFY `datetime` timestamp NOT NULL;
2020-08-24 00:56:03 - ALTER TABLE `zone_graphs` MODIFY `name` char(50) COLLATE utf8_bin;
2020-08-24 00:56:03 - ALTER TABLE `zone_graphs` MODIFY `node_id` char(15) COLLATE utf16_bin;
2020-08-24 00:56:03 - ALTER TABLE `zone_graphs` MODIFY `payload` decimal(10,2);
2020-08-24 00:56:03 - ALTER TABLE `zone_graphs` MODIFY `sub_type` int(11);
2020-08-24 00:56:03 - ALTER TABLE `zone_graphs` MODIFY `type` char(50) COLLATE utf8_bin;
2020-08-24 00:56:03 - ALTER TABLE `zone_graphs` MODIFY `zone_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `zone_logs` MODIFY `boiler_log_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `zone_logs` MODIFY `status` int(11);
2020-08-24 00:56:03 - ALTER TABLE `zone_logs` MODIFY `zone_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `zone_sensors` MODIFY `hysteresis_time` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `zone_sensors` MODIFY `max_c` tinyint(4);
2020-08-24 00:56:03 - ALTER TABLE `zone_sensors` DROP `max_operation_time`;
2020-08-24 00:56:03 - ALTER TABLE `zone_sensors` MODIFY `sensor_child_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `zone_sensors` MODIFY `sensor_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `zone_sensors` MODIFY `zone_id` int(11);
2020-08-24 00:56:03 - ALTER TABLE `zone_type` MODIFY `category` int(11);
2020-08-24 00:56:03 - ALTER TABLE `zone_type` MODIFY `type` char(50) COLLATE utf8_bin;
2020-08-24 00:56:03
Existing Database Table Structures will be amended.
Are you sure you want to do this?  Type 'yes' to continue?
---------------------------------------------------------------
yes
2020-08-24 00:56:05 - Creating Dump File for Exiting Database.
2020-08-24 00:56:06 - Compressing Database Dump File pihome_2020-08-24_00-56-05.sql
2020-08-24 00:56:07 - Compressed Database Dump File pihome_2020-08-24_00-56-06.zip
2020-08-24 00:56:07 - Applying Updates to Database.
2020-08-24 00:56:08 - Database Updates Applied
2020-08-24 00:56:08 - Importing SQL Table View File to Database, This could take few minuts.
MySQL Database Error with Query CREATE VIEW zone_view AS
select zone.status, zone.zone_state, zone.sync, zone.id, zone.index_id, zone.name, ztype.type, ztype.category, zone.graph_it, zs.max_c, max_operation_time, zs.hysteresis_time,
zs.sp_deadband, sid.node_id as sensors_id, zs.sensor_child_id,
ctype.`type` AS controller_type, cid.node_id as controler_id, zc.controler_child_id,
IFNULL(lasts.last_seen, lasts_2.last_seen) as last_seen, IFNULL(msv.ms_version, msv_2.ms_version) as ms_version, IFNULL(skv.sketch_version, skv_2.sketch_version) as sketch_version
from zone
LEFT join zone_sensors zs on zone.id = zs.zone_id
LEFT join zone_controllers zc on zone.id = zc.zone_id
join zone_type ztype on zone.type_id = ztype.id
LEFT join nodes sid on zs.sensor_id = sid.id
join nodes ctype on zc.controler_id = ctype.id
join nodes cid on zc.controler_id = cid.id
LEFT join nodes lasts on zs.sensor_id = lasts.id
LEFT join nodes lasts_2 on zc.controler_id = lasts_2.id
LEFT join nodes msv on zs.sensor_id = msv.id
LEFT join nodes msv_2 on zc.controler_id = msv_2.id
LEFT join nodes skv on zs.sensor_id = skv.id
LEFT join nodes skv_2 on zc.controler_id = skv_2.id
where zone.`purge` = '0';
:Table 'pihome.zone_controllers' doesn't exist
2020-08-24 00:56:09 - DataBase File /var/www/MySQL_Database/MySQL_View.sql Imported Successfully
2020-08-24 00:56:09 - Update Version: 1.76
2020-08-24 00:56:09 - Update Build: 020820
2020-08-24 00:56:09 - MySQL Update DataBase Script Ended
****************************************************************************************
root@pihome:/var/www/MySQL_Database#

after updating database the migrate controller scripts run ok without any error, it delete controller id and child id from zone table and create zone table but do not migrate controller related info into controller table. i have attached the database as well if you want to experiment pihome_post_migration.zip

root@pihome:/var/www/MySQL_Database# php migrate_controller.php

   _____    _   _    _
  |  __ \  (_) | |  | |
  | |__) |  _  | |__| |   ___    _ __ ___     ___
  |  ___/  | | |  __  |  / _ \  | |_  \_ \   / _ \
  | |      | | | |  | | | (_) | | | | | | | |  __/
  |_|      |_| |_|  |_|  \___/  |_| |_| |_|  \___|

      S M A R T   H E A T I N G   C O N T R O L
****************************************************************
*   PiHome Migration Script Version 0.02 Build Date 31/07/2020 *
*   Last Modified on 31/07/2020                                *
*                                      Have Fun - PiHome.eu    *
****************************************************************
2020-08-24 00:57:08 - PiHome Database Migration Script Started
---------------------------------------------------------------
2020-08-24 00:57:08 - php version 7.3.19-1~deb10u1 looks OK
Make Sure you have correct MySQL/MariaDB credentials as following
Hostname:     localhost
Database:     pihome
User Name:    pihomedbadmin
Password:     pihome2018
2020-08-24 00:57:08 - Testing Connection to MySQL/MariaDB Server.
2020-08-24 00:57:08 - Database Server Connection Successfull
2020-08-24 00:57:08 - Checking if Database Exits
2020-08-24 00:57:08 - Database pihome Found
2020-08-24 00:57:08 - Modifying Zone Table.
2020-08-24 00:57:08 - Zone Table Successfully Modified
2020-08-24 00:57:08 - Importing the data to the zone_controllers Table and Updating the zone Table.
2020-08-24 00:57:08 - Importing Migration SQL View File to Database, This could take few minuts.
2020-08-24 00:57:08 - DataBase Views File /var/www/MySQL_Database/migrate_controller_view.sql Imported Successfully
2020-08-24 00:57:08 - DataBase Updated Successfully
twa127 commented 3 years ago

Found the problem - the migration script was expecting the database to have been created with the latest version of migrate_db.php which has the max_operation_time left in the zone table. The attached should now work okay, please let me know. migrate_controller.zip

pihome-shc commented 3 years ago

@twa127 i believe my live database is up-to date, is there anything i missed in db update? last migrate_controller.php worked as expected. 

2020-08-24 22:18:06 - PiHome Database Migration Script Started
---------------------------------------------------------------
2020-08-24 22:18:06 - php version 7.3.19-1~deb10u1 looks OK
Make Sure you have correct MySQL/MariaDB credentials as following
Hostname:     localhost
Database:     pihome
User Name:    pihomedbadmin
Password:     pihome2018
2020-08-24 22:18:06 - Testing Connection to MySQL/MariaDB Server.
2020-08-24 22:18:06 - Database Server Connection Successfull
2020-08-24 22:18:06 - Checking if Database Exits
2020-08-24 22:18:06 - Database pihome Found
2020-08-24 22:18:06 - Modifying Zone Table.
2020-08-24 22:18:07 - Zone Table Successfully Modified
2020-08-24 22:18:07 - Modifying Zone Sensors Table.
2020-08-24 22:18:07 - Zone Sensors Table Successfully Modified
2020-08-24 22:18:07 - Importing the data to the zone_controllers Table and Updating the zone Table.
2020-08-24 22:18:07 - Importing Migration SQL View File to Database, This could take few minuts.
2020-08-24 22:18:08 - DataBase Views File /var/www/MySQL_Database/migrate_controller_view.sql Imported Successfully
2020-08-24 22:18:08 - DataBase Updated Successfully
root@pihome:/var/www/MySQL_Database#