search

pihome-shc / pihome

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

Remove Default Values from Database #166

Closed pihome-shc closed 4 years ago

pihome-shc commented 4 years ago

Remove all default values from database and add those values in code so system do not rely on default values where information isnt added via code. fixing this issue will help #57 Process to update PiHome and Database and streamline the code for updates and it will remove MySQL/MariaDB version dependency.

twa127 commented 4 years ago

Sounds okay to me, you would need to populate the user table via code otherwise you could not get in to the application at all

dvdcut commented 4 years ago

in my experience with update_db.php is strange i thought its something to do with my setup even though i m using img file from pihome website. but it keep asking me to update default values.

twa127 commented 4 years ago

I'd also not display the the Boiler icon on the home screen until there was an entry in the boiler table

twa127 commented 4 years ago

dvdcut - had a similar problem in the past was due to using an older version of MySQL/MariaDB, so this change would fix that problem.

twa127 commented 4 years ago

I'll post an update for the boiler model to enable creation of the initial boiler record

twa127 commented 4 years ago

Getting quite close to a solution for this, a couple of points:

  1. The user table need to be populated to allow initial access to the WEB interface
  2. The system table needs to be populated to identify version and build values

These could be populate using a couple of INSERT queries in setup_db.php The initial setup of the system could then be performed from the WEB interface -

  1. Configure nodes either automatically or manually for GPIO and I2C
  2. Configure boiler from boiler model
  3. Configure Zones
  4. Configure schedules
pihome-shc commented 4 years ago

@twa127 yes you are right, basic database we have at the moment with some basic entries would be the default ones, i.e user and system related information and i think no need to insert them during setup_db.php.

or you are thinking to making web based installer without any information in database file? but in either case lots of code needs to be changed to remove default values, for example right now nodes related info fully populated from gateway script. and same for holiday schedule left null.

twa127 commented 4 years ago

I think the latest commit will allow us to use a completely empty database file, with version and build numbers in the config.ini file, setupdb.php will be used to populate user and system tables.

Please give the attached a try

setup_db.zip

pihome-shc commented 4 years ago

@twa127 looks good, i think having blank database will help updating database easy and clean, also future pull request can only update db_config.ini with build number and this will limit messing up database file.

twa127 commented 4 years ago

Still need to add create default away and holidays tables to zones.php when the first zone is created, I'll add that to the next commit

twa127 commented 4 years ago

Do you want me to commit this update

pihome-shc commented 4 years ago

@twa127, did you already commit this update?

twa127 commented 4 years ago

No, I’ll do it in the morning if your happy with it

Sent from my iPhone

On 16 Mar 2020, at 21:32, PiHomeHVAC notifications@github.com wrote:

 @twa127, did you already commit this update?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

pihome-shc commented 4 years ago

Second Part of this updated was to eliminate any default values from database, for instance boiler table have some default values,

CREATE TABLE boiler ( id INT(11) NOT NULL AUTO_INCREMENT, sync TINYINT(4) NOT NULL DEFAULT '0', purge TINYINT(4) NOT NULL DEFAULT '0' COMMENT 'Mark For Deletion', status TINYINT(4) NULL DEFAULT '0', fired_status TINYINT(4) NULL DEFAULT '0', name CHAR(50) NULL DEFAULT 'Gas Boiler' COLLATE 'utf16_bin', node_id INT(11) NULL DEFAULT NULL, node_child_id INT(11) NULL DEFAULT '1', hysteresis_time TINYINT(4) NULL DEFAULT '3', max_operation_time TINYINT(4) NULL DEFAULT '60', datetime TIMESTAMP NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (id) USING BTREE, INDEX FK_boiler_zone (node_id) USING BTREE, CONSTRAINT FK_boiler_zone FOREIGN KEY (node_id) REFERENCES pihome.nodes (id) ON UPDATE RESTRICT ON DELETE RESTRICT ) COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=2 ;

removing these values will help me to further work on #24 PiConnect Improvements and #57 Process to update PiHome and Database.

twa127 commented 4 years ago

okay I will work on that before I commit the empty database, I think the date related defaults will be the biggest problem as some of the UPDATE queries rely on the default.

twa127 commented 4 years ago

Do you want to remove all the default values from every table

Sent from my iPhone

On 16 Mar 2020, at 23:18, PiHomeHVAC notifications@github.com wrote:

 Second Part of this updated was to eliminate any default values from database, for instance boiler table have some default values,

CREATE TABLE boiler ( id INT(11) NOT NULL AUTO_INCREMENT, sync TINYINT(4) NOT NULL DEFAULT '0', purge TINYINT(4) NOT NULL DEFAULT '0' COMMENT 'Mark For Deletion', status TINYINT(4) NULL DEFAULT '0', fired_status TINYINT(4) NULL DEFAULT '0', name CHAR(50) NULL DEFAULT 'Gas Boiler' COLLATE 'utf16_bin', node_id INT(11) NULL DEFAULT NULL, node_child_id INT(11) NULL DEFAULT '1', hysteresis_time TINYINT(4) NULL DEFAULT '3', max_operation_time TINYINT(4) NULL DEFAULT '60', datetime TIMESTAMP NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (id) USING BTREE, INDEX FK_boiler_zone (node_id) USING BTREE, CONSTRAINT FK_boiler_zone FOREIGN KEY (node_id) REFERENCES pihome.nodes (id) ON UPDATE RESTRICT ON DELETE RESTRICT ) COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=2 ;

removing these values will help me to further work on #24 PiConnect Improvements and #57 Process to update PiHome and Database.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

pihome-shc commented 4 years ago

Yes where possible. I can work with you if you wand to devide work with me.

On 17 Mar 2020, at 19:36, twa127 notifications@github.com wrote:

Do you want to remove all the default values from every table

Sent from my iPhone

On 16 Mar 2020, at 23:18, PiHomeHVAC notifications@github.com wrote:

 Second Part of this updated was to eliminate any default values from database, for instance boiler table have some default values,

CREATE TABLE boiler ( id INT(11) NOT NULL AUTO_INCREMENT, sync TINYINT(4) NOT NULL DEFAULT '0', purge TINYINT(4) NOT NULL DEFAULT '0' COMMENT 'Mark For Deletion', status TINYINT(4) NULL DEFAULT '0', fired_status TINYINT(4) NULL DEFAULT '0', name CHAR(50) NULL DEFAULT 'Gas Boiler' COLLATE 'utf16_bin', node_id INT(11) NULL DEFAULT NULL, node_child_id INT(11) NULL DEFAULT '1', hysteresis_time TINYINT(4) NULL DEFAULT '3', max_operation_time TINYINT(4) NULL DEFAULT '60', datetime TIMESTAMP NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (id) USING BTREE, INDEX FK_boiler_zone (node_id) USING BTREE, CONSTRAINT FK_boiler_zone FOREIGN KEY (node_id) REFERENCES pihome.nodes (id) ON UPDATE RESTRICT ON DELETE RESTRICT ) COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=2 ;

removing these values will help me to further work on #24 PiConnect Improvements and #57 Process to update PiHome and Database.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or unsubscribe.

twa127 commented 4 years ago

I’ll plod on for now, away, boiler, holidays and gateway are done

Sent from my iPhone

On 17 Mar 2020, at 19:38, PiHomeHVAC notifications@github.com wrote:

 Yes where possible. I can work with you if you wand to devide work with me.

On 17 Mar 2020, at 19:36, twa127 notifications@github.com wrote:

Do you want to remove all the default values from every table

Sent from my iPhone

On 16 Mar 2020, at 23:18, PiHomeHVAC notifications@github.com wrote:

 Second Part of this updated was to eliminate any default values from database, for instance boiler table have some default values,

CREATE TABLE boiler ( id INT(11) NOT NULL AUTO_INCREMENT, sync TINYINT(4) NOT NULL DEFAULT '0', purge TINYINT(4) NOT NULL DEFAULT '0' COMMENT 'Mark For Deletion', status TINYINT(4) NULL DEFAULT '0', fired_status TINYINT(4) NULL DEFAULT '0', name CHAR(50) NULL DEFAULT 'Gas Boiler' COLLATE 'utf16_bin', node_id INT(11) NULL DEFAULT NULL, node_child_id INT(11) NULL DEFAULT '1', hysteresis_time TINYINT(4) NULL DEFAULT '3', max_operation_time TINYINT(4) NULL DEFAULT '60', datetime TIMESTAMP NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (id) USING BTREE, INDEX FK_boiler_zone (node_id) USING BTREE, CONSTRAINT FK_boiler_zone FOREIGN KEY (node_id) REFERENCES pihome.nodes (id) ON UPDATE RESTRICT ON DELETE RESTRICT ) COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=2 ;

removing these values will help me to further work on #24 PiConnect Improvements and #57 Process to update PiHome and Database.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or unsubscribe. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

twa127 commented 4 years ago

Also boost, email and frost_protection are done, working on the message queues

Sent from my iPhone

On 17 Mar 2020, at 19:38, PiHomeHVAC notifications@github.com wrote:

 Yes where possible. I can work with you if you wand to devide work with me.

On 17 Mar 2020, at 19:36, twa127 notifications@github.com wrote:

Do you want to remove all the default values from every table

Sent from my iPhone

On 16 Mar 2020, at 23:18, PiHomeHVAC notifications@github.com wrote:

 Second Part of this updated was to eliminate any default values from database, for instance boiler table have some default values,

CREATE TABLE boiler ( id INT(11) NOT NULL AUTO_INCREMENT, sync TINYINT(4) NOT NULL DEFAULT '0', purge TINYINT(4) NOT NULL DEFAULT '0' COMMENT 'Mark For Deletion', status TINYINT(4) NULL DEFAULT '0', fired_status TINYINT(4) NULL DEFAULT '0', name CHAR(50) NULL DEFAULT 'Gas Boiler' COLLATE 'utf16_bin', node_id INT(11) NULL DEFAULT NULL, node_child_id INT(11) NULL DEFAULT '1', hysteresis_time TINYINT(4) NULL DEFAULT '3', max_operation_time TINYINT(4) NULL DEFAULT '60', datetime TIMESTAMP NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (id) USING BTREE, INDEX FK_boiler_zone (node_id) USING BTREE, CONSTRAINT FK_boiler_zone FOREIGN KEY (node_id) REFERENCES pihome.nodes (id) ON UPDATE RESTRICT ON DELETE RESTRICT ) COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=2 ;

removing these values will help me to further work on #24 PiConnect Improvements and #57 Process to update PiHome and Database.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or unsubscribe. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

pihome-shc commented 4 years ago

Thank you.

twa127 commented 4 years ago

I think that I've now changed all the INSERT INTO queries. I've setup my test system successfully from an empty database which has no default clauses.

Please give the attached a try

setup_db.zip

pihome-shc commented 4 years ago

@twa127 Great work, couple of things

Boiler: Enable Boiler checkbox is disabled but after i add boiler records it set that to enable Schedule: schedulelist.php have two extra

at line 139 and 140 when adding schedule it leaves holidays_id column as null, may be add 0 instead of null value Boost: (sb-admin-2.css) .chat li.left .chat-body padding-top: 10px; 987 canceling out 233 Holidays holidaylist.php have extra
on line 156 enabling and disable schedule does not reload holidayslist.php file but it works on holiday dates.

twa127 commented 4 years ago

Hi

Can you please explain what you mean:

Schedule: schedulelist.php have two extra at line 139 and 140

Sent from my iPhone

On 20 Mar 2020, at 00:27, PiHomeHVAC notifications@github.com wrote:

Schedule: schedulelist.php have two extra at line 139 and 140

pihome-shc commented 4 years ago

@twa127

Schedule: schedulelist.php have two extra div at line 139 and 140

Holidays holidaylist.php have extra div on line 156

twa127 commented 4 years ago

I see and agree that they are both wrong, but they have not been changed in this latest batch of updates, so the errors are historical.

I use grep -o '<div' schedulelist.php | wc -l to count the number of opening div and then grep -o '</div' schedulelist.php | wc -l to count the number of closing div, the numbers should obviously match.

I will investigate and fix both files if you want?

pihome-shc commented 4 years ago

@twa127 that is great way of calculating. with those extra div panel footer isnt in its correct place. can you push these changes please.

twa127 commented 4 years ago

okay will do

twa127 commented 4 years ago

when adding schedule it leaves holidays_id column as null, may be add 0 instead of null value -

will be NULL to indicate that it is a normal schedule or the holidays table ID if it is a holidays schedule and will never be 0

twa127 commented 4 years ago

Boost: (sb-admin-2.css) .chat li.left .chat-body padding-top: 10px; 987 canceling out 233

You committed this change on 14 Dec 2019, I'm not sure which one is correct

pihome-shc commented 4 years ago

@twa127, i m working on PiConnect script and null values are problematic thats why it has to be 0 if schedule isnt belongs to holiday.

twa127 commented 4 years ago

okay, I'll give the problem some thought :-)

twa127 commented 4 years ago

Okay, now changed

Sent from my iPhone

On 20 Mar 2020, at 17:18, PiHomeHVAC notifications@github.com wrote:

 @twa127, i m working on PiConnect script and null values are problematic thats why it has to be 0 if schedule isnt belongs to holiday.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

dvdcut commented 4 years ago

@twa127 my gateway is keep failing with error in log file 

### 2020-03-26 14:38:50,570 - ERROR ###
Traceback (most recent call last):
  File "/var/www/cron/gateway.py", line 245, in <module>
    timestamp = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
NameError: name 'datetime' is not defined

### 2020-03-26 14:42:37,333 - ERROR ###
Traceback (most recent call last):
  File "/var/www/cron/gateway.py", line 258, in <module>
    timestamp = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
NameError: name 'datetime' is not defined

### 2020-03-26 14:43:18,794 - ERROR ###
Traceback (most recent call last):
  File "/var/www/cron/gateway.py", line 258, in <module>
    timestamp = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
NameError: name 'datetime' is not defined

### 2020-03-26 14:44:31,136 - ERROR ###
Traceback (most recent call last):
  File "/var/www/cron/gateway.py", line 258, in <module>
    timestamp = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
NameError: name 'datetime' is not defined
pihome-shc commented 4 years ago

@twa127 sorry if i missed anything but i still see database have all the values, i.e its not blank.

twa127 commented 4 years ago

Not committed the empty one yet as I was not sure you were happy with it, just let me know and I’ll do it

Sent from my iPhone

On 29 Mar 2020, at 22:03, PiHomeHVAC notifications@github.com wrote:

 @twa127 sorry if i missed anything but i still see database have all the values, i.e its not blank.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

pihome-shc commented 4 years ago

@twa127 let me test this one last time and after this you can do pull request, i'll be using reference to this attachment. https://github.com/pihome-shc/pihome/issues/166#issuecomment-601154249

twa127 commented 4 years ago

Okay

Sent from my iPhone

On 30 Mar 2020, at 13:19, PiHomeHVAC notifications@github.com wrote:

 @twa127 let me test this one last time and after this you can do pull request, i'll be using reference to this attachment.

166 (comment)

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

pihome-shc commented 4 years ago

@twa127 Setting->Gateway Language missing, some extra fields are showing but when gateway type is select then right fields are displayed. toggle isn't working as it suppose to work. everything works as it should.

image you can do pull request for database.

twa127 commented 4 years ago

Hi

Is this problem fixed now?

pihome-shc commented 4 years ago

@twa127 yes, language issue is fixed i think you can do pull request for database. i'll change documentation to include extra steps.

twa127 commented 4 years ago

Okay will do

Sent from my iPhone

On 31 Mar 2020, at 12:31, PiHomeHVAC notifications@github.com wrote:

 @twa127 yes, language issue is fixed i think you can do pull request for database. i'll change documentation to include extra steps.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

dvdcut commented 4 years ago

can i suggest here something: can we keep some defaults data in database, like boiler and two zone, one heating second hotwater.

pihome-shc commented 4 years ago

@dvdcut is there any reason you want to have two zone and boiler record in database?

dvdcut commented 4 years ago

when i was installing my dev system from github i had some issues, may be adding boiler and one zone will make few things out of the box work and make it more plug and play type

pihome-shc commented 4 years ago

@twa127 did great job on clean-up work on database, i know it is confusing as documentation is missing for fresh install, specially if some one is trying to install from scratch.

adding boiler record wouldn't hurt but it has to added from setup.php and setup_db.php files.