PiConnect Improvements

[pihome-shc]

Hi all, At this stage PiConnect works over http request which is very CPU intensive i m looking for ideas to improve PiConnect i m by no mean expert but after reading few blogs and watching some YouTube videos i think websocket is better options and MQTT on public is domain is another option, what you guys thing and which way PiConnect should go?

[dvdcut]

i see what you mean by using HTTP to send updated to PiConnect site, i think HTTP is non runner in my humble opinion, only handful people would ever enjoy with these many transaction to web server. i think Jason is far better option. on client side collect all data as Jason format and submit to server loop through the data and save to database.

remember one thing even commercial products have down time and their up time isnt 99.99% just for reference have look here https://outage.report/nest for nest outage.

[pihome-shc]

@dvdcut i was thinking the same and i have done some coding to produce jason here is client side code but server side code non existent :(

<?php 
#!/usr/bin/php

require_once(__DIR__.'../../st_inc/connection.php');
require_once(__DIR__.'../../st_inc/functions.php');

$date_time = date('Y-m-d H:i:s');
$line = "------------------------------------------------------------------\n";
//Set php script execution time in seconds
ini_set('max_execution_time', 40);

    //Start Syncing Frost Protection Table with PiConnect. 
    $query = "SELECT * FROM frost_protection where sync = 0 order by id asc limit 1;";
    $result = $conn->query($query);
    $frost_protection = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $frost_protection[]=$row;
    }

    //Start Syncing Frost Protection Table with PiConnect. 
    $query = "SELECT * FROM system where sync = 0 order by id asc limit 1;";
    $result = $conn->query($query);
    $system = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $system[]=$row;
    }

    //start syncing away table with PiConnect. 
    $query = "SELECT * FROM away where sync = 0 order by id asc;";
    $result = $conn->query($query);
    $away = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $away[]=$row;
    }

    //Start Syncing Gateway Table with PiConnect. 
    $query = "SELECT * FROM gateway where sync = 0 order by id asc limit 1;";
    $result = $conn->query($query);
    $gateway = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $gateway[]=$row;
    }

    //Start Syncing Gateway Logs Table with PiConnect. 
    $query = "SELECT * FROM gateway_logs where sync = 0 order by id asc;";
    $result = $conn->query($query);
    $gateway_logs = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $gateway_logs[]=$row;
    }

    //start syncing nodes table with PiConnect. 
    $query = "SELECT * FROM nodes where sync = 0 order by id asc;";
    $result = $conn->query($query);
    $nodes = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $nodes[]=$row;
    }

    //Start Syncing Nodes Battery Table with PiConnect. 
    $query = "SELECT * FROM nodes_battery where sync = 0 order by id asc;";
    $result = $conn->query($query);
    $nodes_battery = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $nodes_battery[]=$row;
    }

    //Start syncing boiler table with PiConnect. 
    $query = "SELECT * FROM boiler where sync = 0 order by id asc;";
    $result = $conn->query($query);
    $boiler = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $boiler[]=$row;
    }

    //start syncing boiler Logs table with PiConnect. 
    $query = "SELECT * FROM boiler_logs where sync = 0 AND stop_datetime IS NOT NULL order by id asc;";
    $result = $conn->query($query);
    $boiler_logs = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $boiler_logs[]=$row;
    }

    //start syncing zone table with PiConnect. 
    $query = "SELECT * FROM zone where sync = 0 order by id asc;";
    $result = $conn->query($query);
    $zone = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $zone[]=$row;
    }

    //start syncing Zone Logs table with PiConnect. 
    $query = "SELECT * FROM zone_logs where sync = 0 order by id asc;";
    $result = $conn->query($query);
    $zone_logs = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $zone_logs[]=$row;
    }

    //start syncing Schedul Time table with PiConnect. 
    $query = "SELECT * FROM schedule_daily_time where sync = 0 order by id asc;";
    $result = $conn->query($query);
    $schedule_daily_time = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $schedule_daily_time[]=$row;
    }

    $query = "SELECT * FROM schedule_daily_time_zone where sync = 0 order by id asc;";
    $result = $conn->query($query);
    $schedule_daily_time_zone = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $schedule_daily_time_zone[]=$row;
    }

    //start syncing Override table with PiConnect. 
    $query = "SELECT * FROM override where sync = 0 order by id asc;";
    $result = $conn->query($query);
    $override = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $override[]=$row;
    }

    //start syncing Boost table with PiConnect. 
    $query = "SELECT * FROM boost where sync = 0 order by id asc;";
    $result = $conn->query($query);
    $boost = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $boost[]=$row;
    }

    //start syncing Night Climate Time table with PiConnect. 
    $query = "SELECT * FROM schedule_night_climate_time where sync = 0 order by id asc;";
    $result = $conn->query($query);
    $schedule_night_climate_time = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $schedule_night_climate_time[]=$row;
    }

    //start syncing Schedul Night Climate Zone table with PiConnect. 
    $query = "SELECT * FROM schedule_night_climat_zone where sync = 0 order by id asc;";
    $result = $conn->query($query);
    $schedule_night_climat_zone = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $schedule_night_climat_zone[]=$row;
    }

    //start syncing messages_in (temperature) readings with PiConnect. Do not Sync Data older then 24 hours as these will be discarded. 
    $query = "SELECT * FROM messages_in WHERE sync = 0 AND datetime > DATE_SUB(NOW(), INTERVAL 24 HOUR);";
    $result = $conn->query($query);
    $messages_in = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $messages_in[]=$row;
    }

    //Start syncing Weather with PiConnect.
    $query = "SELECT * FROM weather WHERE sync = 0;";
    $result = $conn->query($query);
    $weather = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $weather[]=$row;
    }

// Combine both arrays in a new variable
$all_data['frost_protection'] = $frost_protection;
/*

$all_data['system'] = $system;
$all_data['away'] = $away;
$all_data['gateway'] = $gateway;
$all_data['gateway_logs'] = $gateway_logs;
$all_data['nodes'] = $nodes;
$all_data['nodes_battery'] = $nodes_battery;
$all_data['boiler'] = $boiler;
$all_data['boiler_logs'] = $boiler_logs;
$all_data['zone'] = $zone;
$all_data['zone_logs'] = $zone_logs;
$all_data['schedule_daily_time'] = $schedule_daily_time;
$all_data['schedule_daily_time_zone'] = $schedule_daily_time_zone;
$all_data['override'] = $override;
$all_data['boost'] = $boost;
$all_data['schedule_night_climate_time'] = $schedule_night_climate_time;
$all_data['schedule_night_climat_zone'] = $schedule_night_climat_zone;
$all_data['messages_in'] = $messages_in;
$all_data['weather'] = $weather;

header("Content-Type: application/json; charset=UTF-8");
echo json_encode($all_data);
*/
//header("Content-Type: application/json; charset=UTF-8");
//https://stackoverflow.com/questions/4342926/how-can-i-send-json-data-to-server

$url='http://192.168.99.2/mypihome_v3.php';
$content = json_encode($all_data);
$curl = curl_init($url);
curl_setopt($curl, CURLOPT_HEADER, false);
curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
curl_setopt($curl, CURLOPT_HTTPHEADER,
        array("Content-Type: application/json; charset=UTF-8"));
curl_setopt($curl, CURLOPT_POST, true);
curl_setopt($curl, CURLOPT_POSTFIELDS, $content);
//curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false); //curl error SSL certificate problem, verify that the CA cert is OK

$result     = curl_exec($curl);
$response   = json_decode($result);
var_dump($response);
curl_close($curl);

/*
//API URL
$url='http://192.168.99.2/mypihome_v3.php';
//create a new cURL resource
$ch = curl_init($url);

$payload = json_encode($all_data);
//attach encoded JSON string to the POST fields
curl_setopt($ch, CURLOPT_POSTFIELDS, $payload);
//set the content type to application/json
curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type:application/json'));
//return response instead of outputting
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
//execute the POST request
$result = curl_exec($ch);
//close cURL resource
curl_close($ch);
*/

if(isset($conn)) { $conn->close();}
?>

[dvdcut]

@pihome-shc any further work completed on this?

[pihome-shc]

yes i have basic framework completed on client and server one rpi is as client and another as server working ok so far but i need to test it in production. I need input from your guys, may be one private repository as i don't want to disclose server side jut in case there is bug and some one try to exploit it.