描述

車庫(kù)水培

專業(yè)水培數(shù)據(jù)庫(kù)管理

因?yàn)槟阏娴牟恢滥愕乃嘞到y(tǒng)發(fā)生了什么而感到沮喪？

然后使用您的水培控制系統(tǒng)收集數(shù)據(jù)，并使用它來(lái)監(jiān)控和做出更明智的決策。查看趨勢(shì)并了解實(shí)際情況！

在讀取溫度、濕度、土壤濕度或任何水培數(shù)據(jù)后，我們需要首先使用 WiFi 連接將數(shù)據(jù)發(fā)布到數(shù)據(jù)庫(kù)。這是通過(guò)使用簡(jiǎn)單的 REST API 完成的。REST 或具象狀態(tài)傳輸是使用帶有 JSON（JavaScript 對(duì)象表示法）格式的有效負(fù)載的 HTTP 方法 (POST)。

void HttpPost(const char *url, String &post_data)
{
  HTTPClient http;
  http.begin(url);
  http.addHeader("Content-Type", "application/x-www-form-urlencoded");
 
  int http_code = http.POST(post_data);   // Send the request
  String payload = http.getString();      // Get the response payload
 
  SerialDebug.println(http_code);         // Print HTTP return code
  SerialDebug.println(payload);           // Print request response payload
 
  if (payload.length() > 0) {
    int index = 0;
    do
    {
      if (index > 0) index++;
      int next = payload.indexOf('\n', index);
      if (next == -1) break;
      String request = payload.substring(index, next);
      if (request.substring(0, 9).equals(")) break;
 
      SerialDebug.println(request);
      StaticJsonDocument<100> doc;
      DeserializationError error = deserializeJson(doc, request);
      if (!error) {
        if (doc["OVERRIDE_LIGHTS_TIME"])   OVERRIDE_LIGHTS_TIME = doc["OVERRIDE_LIGHTS_TIME"];
        if (doc["OVERRIDE_LIGHTS"])        OVERRIDE_LIGHTS = doc["OVERRIDE_LIGHTS"];
        if (doc["OVERRIDE_VENT_FAN_TIME"]) OVERRIDE_VENT_FAN_TIME = doc["OVERRIDE_VENT_FAN_TIME"];
        if (doc["OVERRIDE_VENT_FAN"])      OVERRIDE_VENT_FAN = doc["OVERRIDE_VENT_FAN"];
      }
      index = next;
    } while (index >= 0);
  }
 
  http.end();                             // Close connection
}
 
...
 
void loop() {
 
...
 
        char buffer[80];
        strftime(buffer, sizeof(buffer), "%m/%d/%Y %H:%M:%S", &rtc);
 
        // Allocate JsonDocument
        // Use arduinojson.org/assistant to compute the capacity
        StaticJsonDocument<500> doc;
 
        // Create the root object
        doc["ReadingTime"] = buffer;
        doc["InsideTemp"] = (inside.error) ? ERROR_READ : inside.temp;
        doc["InsideRelative"] = (inside.error) ? ERROR_READ : inside.relative;
        doc["InsideAbsolute"] = (inside.error) ? ERROR_READ : inside.absolute;
        doc["OutsideTemp"] = (outside.error) ? ERROR_READ : outside.temp;
        doc["OutsideRelative"] = (outside.error) ? ERROR_READ : outside.relative;
        doc["OutsideAbsolute"] = (outside.error) ? ERROR_READ : outside.absolute;
        doc["VentFan"] = vent_fan;
        doc["Lights"] = lights;
        doc["Power"] = power;
        doc["DailyCost"] = cost;
        doc["ColorTemp"] = color_temp;
        doc["Lux"] = lux;
        doc["CO2"] = co2;
        doc["CO2Temp"] = co2_temp;
        doc["CO2Relative"] = co2_relative;
        doc["GerminationTemp"] = germination_temp;
        doc["ChillerTemp"] = chiller_temp;
        doc["pH"] = pH;
        doc["DO"] = DO;
        JsonArray array = doc.createNestedArray("GrowBed");
        for (i = 0; i < sizeof(grow_bed_table) / sizeof(GROWBED_t); i++) {
          JsonObject object = array.createNestedObject();
          object["WaterTemp"] = (grow_bed_table[i].water_temp_error) ? ERROR_READ : grow_bed_table[i].water_temp;
          object["WaterTDS"] = grow_bed_table[i].water_tds;
          object["WaterLevel"] = grow_bed_table[i].water_level;
        }
        String json_data;
        serializeJson(doc, json_data);
        post_data = "data=" json_data;
        SerialDebug.println(post_data);
 
#ifdef MySQL
        HttpPost(mysql_url, post_data);
#endif
#ifdef MSSQL
        HttpPost(mssql_url, post_data);
#endif
 
...
 
}

服務(wù)器/數(shù)據(jù)庫(kù)

您可以使用兩種不同的流行的用戶控制的數(shù)據(jù)庫(kù)平臺(tái)。

• 使用 Microsoft SQL 數(shù)據(jù)庫(kù)服務(wù)器的 Windows 虛擬主機(jī)。
• 使用 MySQL 數(shù)據(jù)庫(kù)服務(wù)器的本地 Raspberry Pi 4 服務(wù)器。

您還可以使用 MQTT 客戶端使用任何 IoT 服務(wù)，但此處不解釋該方法。

使用 Microsoft SQL 數(shù)據(jù)庫(kù)服務(wù)器的 Windows 虛擬主機(jī)

對(duì)于 Windows 虛擬主機(jī)，我一直在使用WinHost。他們提供每月只需 3.95 美元的基本計(jì)劃，應(yīng)該適合包括我自己在內(nèi)的大多數(shù)家庭愛(ài)好者的預(yù)算，但您可以使用任何您喜歡的 Windows 虛擬主機(jī)。我已經(jīng)使用了多年，對(duì)他們的產(chǎn)品和服務(wù)非常滿意。

優(yōu)點(diǎn)

• 基于網(wǎng)絡(luò)，可在任何計(jì)算機(jī)網(wǎng)絡(luò)瀏覽器的任何地方訪問(wèn)。
• 無(wú)需維護(hù)硬件。
• 使用 Windows/數(shù)據(jù)庫(kù)安全性更安全。
• 比 MySQL 快。
• 可擴(kuò)展。需要更多性能訂閱更大的平臺(tái)，甚至專用服務(wù)器。

缺點(diǎn)

• 在 Web 上，如果未實(shí)施安全性（SSL 等），則可被黑客入侵。安全成本為 $$$。
• 如果您的互聯(lián)網(wǎng)連接中斷，您將無(wú)法訪問(wèn)您的系統(tǒng)。
• 基本計(jì)劃限制為 500MB SQL。電源計(jì)劃最大為 10GB。較大的費(fèi)用為$$。

使用SQL Server Management Studio連接到您的 Microsoft SQL Server 并使用以下 SQL 腳本創(chuàng)建數(shù)據(jù)庫(kù)和表。

CREATE TABLE [dbo].[Hydroponics](
    [ReadingTime] [datetime] NOT NULL,
    [InsideTemp] [DECIMAL](9, 2) NULL,
    [InsideRelative] [DECIMAL](9, 2) NULL,
    [InsideAbsolute] [DECIMAL](9, 2) NULL,
    [OutsideTemp] [DECIMAL](9, 2) NULL,
    [OutsideRelative] [DECIMAL](9, 2) NULL,
    [OutsideAbsolute] [DECIMAL](9, 2) NULL,
    [VentFan] [bit] NULL,
    [Lights] [bit] NULL,
    [POWER] [SMALLINT] NULL,
    [GrowBed1WaterTemp] [DECIMAL](9, 2) NULL,
    [GrowBed1WaterTDS] [SMALLINT] NULL,
    [GrowBed1WaterLevel] [bit] NULL,
    [GrowBed2WaterTemp] [DECIMAL](9, 2) NULL,
    [GrowBed2WaterTDS] [SMALLINT] NULL,
    [GrowBed2WaterLevel] [bit] NULL,
    [DailyCost] [DECIMAL](9, 2) NULL,
    [ColorTemp] [INT] NULL,
    [Lux] [INT] NULL,
    [CO2] [DECIMAL](9, 2) NULL,
    [CO2Temp] [DECIMAL](9, 2) NULL,
    [CO2Relative] [DECIMAL](9, 2) NULL,
    [GerminationTemp] [DECIMAL](9,2) NULL,
    [ChillerTemp] [DECIMAL](9,2) NULL,
    [pH] [DECIMAL](9,2) NULL,
    [DO] [DECIMAL](9,2) NULL,
PRIMARY KEY CLUSTERED
(
    [ReadingTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
CREATE TABLE [dbo].[Request](
    [RequestTime] [DATETIME] NOT NULL,
    [JsonData] [VARCHAR](MAX) NULL,
    [Processed] [BIT] NULL,
PRIMARY KEY CLUSTERED
(
    [RequestTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

使用Microsoft IIS 管理器設(shè)置和管理您的網(wǎng)站。使用以下 Active Server Page Extended 'adddata.aspx' 連接和傳輸數(shù)據(jù)負(fù)載。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Script;
using System.Web.Script.Serialization;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
 
namespace Hydroponics
{
    public partial class adddata : System.Web.UI.Page
    {
        class GrowBed
        {
            public decimal WaterTemp { get; set; }
            public int WaterTDS { get; set; }
            public bool WaterLevel { get; set; }
        }
 
        class HydroponicsData
        {
            public DateTime ReadingTime { get; set; }
            public decimal InsideTemp { get; set; }
            public decimal InsideRelative { get; set; }
            public decimal InsideAbsolute { get; set; }
            public decimal OutsideTemp { get; set; }
            public decimal OutsideRelative { get; set; }
            public decimal OutsideAbsolute { get; set; }
            public bool VentFan { get; set; }
            public bool Lights { get; set; }
            public int Power { get; set; }
            public decimal DailyCost { get; set; }
            public int ColorTemp { get; set; }
            public int Lux { get; set; }
            public decimal CO2 { get; set; }
            public decimal CO2Temp { get; set; }
            public decimal CO2Relative { get; set; }
            public decimal GerminationTemp { get; set; }
            public decimal ChillerTemp { get; set; }
            public List GrowBed { get; set; }
 
 
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            var data = new JavaScriptSerializer().Deserialize(Request["data"].ToString());
 
            String strSQL = "INSERT INTO Hydroponics (ReadingTime,"
                "InsideTemp,InsideRelative,InsideAbsolute,"
                "OutsideTemp,OutsideRelative,OutsideAbsolute,"
                "VentFan,Lights,Power,DailyCost,"
                "ColorTemp,Lux,"
                "CO2,CO2Temp,CO2Relative,"
                "GerminationTemp,ChillerTemp,"
                "GrowBed1WaterTemp,GrowBed1WaterTDS,GrowBed1WaterLevel,"
                "GrowBed2WaterTemp,GrowBed2WaterTDS,GrowBed2WaterLevel)"
                " VALUES ("
                "'" data.ReadingTime.ToString() "',";
                if (data.InsideTemp >= 0) strSQL += data.InsideTemp.ToString() "," data.InsideRelative.ToString() "," data.InsideAbsolute.ToString() ",";
                else strSQL += "NULL,NULL,NULL,";
                if (data.OutsideTemp >= 0) strSQL += data.OutsideTemp.ToString() "," data.OutsideRelative.ToString() "," data.OutsideAbsolute.ToString() ",";
                else strSQL += "NULL,NULL,NULL,";
                strSQL += ((data.VentFan) ? "1" : "0") "," ((data.Lights) ? "1" : "0") "," data.Power.ToString() "," data.DailyCost.ToString() ",";
                if (data.ColorTemp >= 0) strSQL += data.ColorTemp.ToString() "," data.Lux.ToString() ",";
                else strSQL += "NULL,NULL,";
                if (data.CO2 >= 0) strSQL += data.CO2.ToString() "," data.CO2Temp.ToString() "," data.CO2Relative.ToString() ",";
                else strSQL += "NULL,NULL,NULL,";
                if (data.GerminationTemp >= 0) strSQL += data.GerminationTemp.ToString() ",";
                else strSQL += "NULL,";
                if (data.ChillerTemp >= 0) strSQL += data.ChillerTemp.ToString() ",";
                else strSQL += "NULL,";
                if (data.GrowBed[0].WaterTemp >= 0) strSQL += data.GrowBed[0].WaterTemp.ToString() ",";
                else strSQL += "NULL,";
                if (data.GrowBed[0].WaterTDS >= 0) strSQL += data.GrowBed[0].WaterTDS.ToString() ",";
                else strSQL += "NULL,";
                strSQL += ((data.GrowBed[0].WaterLevel) ? "1" : "0") ",";
                if (data.GrowBed[1].WaterTemp >= 0) strSQL += data.GrowBed[1].WaterTemp.ToString() ",";
                else strSQL += "NULL,";
                if (data.GrowBed[1].WaterTDS >= 0) strSQL += data.GrowBed[1].WaterTDS.ToString() ",";
                else strSQL += "NULL,";
                strSQL += ((data.GrowBed[1].WaterLevel) ? "1" : "0") ")";
 
            try
            {
                SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ServerConnectionString"].ConnectionString);
                con.Open();
 
                SqlCommand cmd = new SqlCommand(strSQL, con);
                cmd.ExecuteNonQuery();
 
                strSQL = "SELECT * FROM Request WHERE Processed IS NULL ORDER BY RequestTime ASC";
                SqlCommand req = new SqlCommand(strSQL, con);
                SqlDataAdapter sda = new SqlDataAdapter(req);
                DataTable dtRequest = new DataTable();
                sda.Fill(dtRequest);
 
                if (dtRequest.Rows.Count > 0)
                {
                    for (int rows = 0; rows < dtRequest.Rows.Count; rows++)
                    {
                        if (dtRequest.Rows[0]["JsonData"] != DBNull.Value)
                        {
                            Response.Write(dtRequest.Rows[0]["JsonData"].ToString());
                            Response.Write("\n");
                        }
                    }
 
                    DateTime dt = (DateTime)dtRequest.Rows[dtRequest.Rows.Count-1]["RequestTime"];
                    strSQL = "UPDATE Request SET Processed=1 WHERE Processed IS NULL AND RequestTime <='" dt.ToString("yyyy-MM-dd HH:mm:ss.fff") "'";
                    SqlCommand upd = new SqlCommand(strSQL,con);
                    upd.ExecuteNonQuery();
                }
 
                con.Close();
            }
            catch (SqlException sqlex)
            {
                Response.Write(sqlex.Message.ToString() "\r\n");
            }
 
 
        }
    }
}

使用 IIS 管理器添加一個(gè)連接字符串“ServerConnectionString”，它將允許 .aspx 網(wǎng)頁(yè)連接到您的數(shù)據(jù)庫(kù)。

到目前為止，我們應(yīng)該每分鐘都在捕獲數(shù)據(jù)。為了動(dòng)態(tài)查看數(shù)據(jù)庫(kù)數(shù)據(jù)，我們將使用Grafana。 這個(gè)動(dòng)態(tài)圖形工具將允許您修改和添加圖表，幫助您查看時(shí)間序列數(shù)據(jù)以管理您的水培系統(tǒng)。

?

在您的計(jì)算機(jī)上下載并安裝 Grafana或獲取免費(fèi)的托管 Grafana 實(shí)例并創(chuàng)建 localhost 服務(wù)器。

登錄到 Grafana 后，使用 MSSQL 創(chuàng)建數(shù)據(jù)庫(kù)連接，然后導(dǎo)入以下腳本以創(chuàng)建 Garage Hydroponics 儀表板。

格拉法納 MSSQL v1.0

要允許來(lái)自 grafana 的請(qǐng)求，例如打開(kāi)燈或風(fēng)扇，請(qǐng)?zhí)砑右韵隆癮ddrequest.aspx”，以便在下次發(fā)送有效負(fù)載時(shí)做出響應(yīng)。這意味著一旦選擇了該選項(xiàng)，可能需要一分鐘的時(shí)間才能處理該操作。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
 
namespace Hydroponics
{
    public partial class addrequest : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (Request["data"] != null)
            {
                String strSQL = "INSERT INTO Request (RequestTime, JsonData) VALUES (GETUTCDATE(),'" + Request["data"].ToString() + "')";
                try
                {
                    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ServerConnectionString"].ConnectionString);
                    con.Open();
 
                    SqlCommand cmd = new SqlCommand(strSQL, con);
                    cmd.ExecuteNonQuery();
 
                    con.Close();
                }
                catch (SqlException sqlex)
                {
                    Response.Write(sqlex.Message.ToString() + "\r\n");
                }
            }
 
            ClientScript.RegisterStartupScript(typeof(Page), "closePage", "window.close();", true);
        }
    }
}

使用 MySQL 數(shù)據(jù)庫(kù)服務(wù)器的本地 Raspberry Pi 4 服務(wù)器

使用新的 Raspberry Pi 4 作為服務(wù)器。隨著 4GB RAM、USB 3.0 和 1Gb 以太網(wǎng)的加入，Raspberry Pi 4 終于成為低成本家庭服務(wù)器的真正競(jìng)爭(zhēng)者。

優(yōu)點(diǎn)

• 在本地家庭網(wǎng)絡(luò)上。所有數(shù)據(jù)都被收集并留在家中。
• 不依賴互聯(lián)網(wǎng)連接來(lái)收集數(shù)據(jù)。
• 數(shù)據(jù)庫(kù)大小受限于您??的驅(qū)動(dòng)器大小。
• 沒(méi)有月費(fèi)。

缺點(diǎn)

• 在您的本地家庭網(wǎng)絡(luò)上，因此除非打開(kāi)，否則無(wú)法通過(guò)網(wǎng)絡(luò)訪問(wèn)它。
• 硬件維護(hù)。
• 數(shù)據(jù)庫(kù)性能比 Microsoft SQL 慢。
• 不可擴(kuò)展。
• 安全性不強(qiáng)。

?

服務(wù)器部件

x1 CanaKit Raspberry Pi2 4GB 入門(mén)套件 - 4GB RAM。
x1東芝 HDTB410EK3AA 1TB 2.5" USB 3.0 黑色。

服務(wù)器軟件安裝

下載并安裝最新版本的 Raspberry Pi OS。
確保操作系統(tǒng)從 USB 3.0 硬盤(pán)啟動(dòng)。
使用 PHP 下載并安裝 Apache Web 服務(wù)器。
下載并安裝 MySQL 數(shù)據(jù)庫(kù)服務(wù)器。
下載并安裝 PHPMyAdmin 以輕松管理 MySQL。
下載并安裝 Grafana 服務(wù)器。
將以太網(wǎng)設(shè)置為您的家庭靜態(tài) IP 地址。
下載并安裝用于遠(yuǎn)程訪問(wèn)的 VNC 查看器。
將您的 Raspberry Pi 4 設(shè)置為無(wú)頭服務(wù)器（無(wú)鍵盤(pán)/鼠標(biāo)/顯示器）。

使用 phpMyAdmin 創(chuàng)建一個(gè)名為“mydata”的新數(shù)據(jù)庫(kù)，并使用 SQL 腳本創(chuàng)建下表。

CREATE TABLE Hydroponics(
    ReadingTime DATETIME NOT NULL,
    InsideTemp DECIMAL(9, 2) NULL,
    InsideRelative DECIMAL(9, 2) NULL,
    InsideAbsolute DECIMAL(9, 2) NULL,
    OutsideTemp DECIMAL(9, 2) NULL,
    OutsideRelative DECIMAL(9, 2) NULL,
    OutsideAbsolute DECIMAL(9, 2) NULL,
    VentFan BIT NULL,
    Lights BIT NULL,
    Power SMALLINT NULL,
    GrowBed1WaterTemp DECIMAL(9, 2) NULL,
    GrowBed1WaterTDS SMALLINT NULL,
    GrowBed1WaterLevel BIT NULL,
    GrowBed2WaterTemp DECIMAL(9, 2) NULL,
    GrowBed2WaterTDS SMALLINT NULL,
    GrowBed2WaterLevel BIT NULL,
    DailyCost INT NULL,
    ColorTemp INT NULL,
    Lux INT NULL,
    CO2 DECIMAL(9, 2) NULL,
    CO2Temp DECIMAL(9, 2) NULL,
    CO2Relative DECIMAL(9, 2) NULL,
    GerminationTemp DECIMAL(9,2) NULL,
    ChillerTemp DECIMAL(9,2) NULL,
    pH DECIMAL(9,2) NULL,
    DO DECIMAL(9,2) NULL,
    PRIMARY KEY(ReadingTime)
);
 
CREATE TABLE Request(
    RequestTime DATETIME NOT NULL,
    JsonData VARCHAR(4096) NULL,
    Processed BIT NULL,
    PRIMARY KEY(RequestTime)
);

在 Apache Web 服務(wù)器中使用以下 PHP 文件“adddata.php”連接到 MySQL 并注入 JSON 數(shù)據(jù)負(fù)載。

 
$servername = "localhost";
$dbname = "mydata";
$username = "admin";
$password = "password";
 
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $data = json_decode($_POST["data"]);
 
    // Create database connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
 
    ini_set("date.timezone", "UTC");
 
    $sql = "INSERT INTO Hydroponics (ReadingTime," .
    "InsideTemp,InsideRelative,InsideAbsolute," .
    "OutsideTemp,OutsideRelative,OutsideAbsolute," .
    "VentFan,Lights,Power,DailyCost," .
    "ColorTemp,Lux," .
    "CO2,CO2Temp,CO2Relative," .
    "GerminationTemp,ChillerTemp," .
    "pH,DO," .
    "GrowBed1WaterTemp,GrowBed1WaterTDS,GrowBed1WaterLevel," .
    "GrowBed2WaterTemp,GrowBed2WaterTDS,GrowBed2WaterLevel) " .
    "VALUES (" .
    "STR_TO_DATE('" . $data->ReadingTime . "','%m/%d/%Y %H:%i:%s'),";
    if ($data->InsideTemp >= 0) $sql .= $data->InsideTemp . "," . $data->InsideRelative . "," . $data->InsideAbsolute . ",";
    else $sql .= "NULL,NULL,NULL,";
    if ($data->OutsideTemp >= 0) $sql .= $data->OutsideTemp . "," . $data->OutsideRelative . "," . $data->OutsideAbsolute . ",";
    else $sql .= "NULL,NULL,NULL,";
    $sql .= ($data->VentFan ? "1":"0") . "," . ($data->Lights ? "1":"0") . "," . $data->Power . "," . $data->DailyCost . ",";
    if ($data->ColorTemp >= 0) $sql .= $data->ColorTemp . "," . $data->Lux . ",";
    else $sql .= "NULL,NULL,";    
    if ($data->CO2 >= 0) $sql .= $data->CO2 . "," . $data->CO2Temp . "," . $data->CO2Relative . ",";
    else $sql .= "NULL,NULL,NULL,";
    if ($data->GerminationTemp >= 0) $sql .= $data->GerminationTemp . ",";
    else $sql .= "NULL,";
    if ($data->ChillerTemp >= 0) $sql .= $data->ChillerTemp . ",";
    else $sql .= "NULL,";
    if ($data->pH >= 0) &sql .= $data->pH . ",";
    else $sql .= "NULL,";
    if ($data->DO >= 0) &sql .= $data->DO . ",";
    else $sql .= "NULL,";
    if ($data->GrowBed[0]->WaterTemp >= 0) $sql .= $data->GrowBed[0]->WaterTemp . ",";
    else $sql .= "NULL,";
    if ($data->GrowBed[0]->WaterTDS >= 0) $sql .= $data->GrowBed[0]->WaterTDS . ",";
    else $sql .= "NULL,";
    $sql .= ($data->GrowBed[0]->WaterLevel ? "1":"0") . ",";
    if ($data->GrowBed[1]->WaterTemp >= 0) $sql .= $data->GrowBed[1]->WaterTemp . ",";
    else $sql .= "NULL,";
    if ($data->GrowBed[1]->WaterTDS >= 0) $sql .= $data->GrowBed[1]->WaterTDS . ",";
    else $sql .= "NULL,";
    $sql .= ($data->GrowBed[1]->WaterLevel ? "1":"0") . ")";
 
    if ($conn->query($sql) == FALSE) {
        echo "Error: " . $sql . "\r\n" . $conn->error . "\r\n";
    }
 
    $sql = "SELECT * FROM Request WHERE Processed IS NULL ORDER BY RequestTime ASC";
    $result = $conn->query($sql);  
    if ($result->num_rows > 0) {
        $lasttime = "";
        while($row = $result->fetch_assoc()) {
            echo $row["JsonData"] . "\n";
            $lasttime = $row["RequestTime"];  
        }
 
        $sql = "UPDATE Request SET Processed=1 WHERE Processed IS NULL AND RequestTime <= '" . $lasttime . "'";
        $conn->query($sql);
    }
 
    $conn->close();
}
else {
    echo "No data posted with HTTP POST.";
}

在 Pi 4 上的端口 3000 登錄 Grafana 后，創(chuàng)建與 MySQL 的數(shù)據(jù)庫(kù)連接，然后導(dǎo)入以下腳本以創(chuàng)建 Garage Hydroponics 儀表板。

格拉法納 MySQL v1.0

注意：為了讓 Grafana 檢索正確的時(shí)間序列數(shù)據(jù)，請(qǐng)確保將 MySQL 中的默認(rèn)時(shí)區(qū)設(shè)置為 UTC。

要允許來(lái)自 grafana 的請(qǐng)求，例如打開(kāi)燈或風(fēng)扇，請(qǐng)?zhí)砑右韵?PHP 文件“addrequest.php”，該文件將允許在下次發(fā)送有效負(fù)載時(shí)做出響應(yīng)。這意味著一旦選擇了該選項(xiàng)，可能需要一分鐘的時(shí)間才能處理該操作。

 
$servername = "localhost";
$dbname = "mydata";
$username = "admin";
$password = "mysql";
 
if ($_SERVER["REQUEST_METHOD"] == "GET") {
 
    // Create database connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
 
    ini_set("date.timezone", "UTC");
 
    $sql = "INSERT INTO Request (RequestTime,JsonData) " .
    "VALUES (" .
    "UTC_TIMESTAMP(),'" . $_GET["data"] . "')";
 
    if ($conn->query($sql) == FALSE) {
        echo "Error: " . $sql . "\r\n" . $conn->error;
    }
 
    $conn->close();
 
    echo "";
}
else {
    echo "No data posted with HTTP POST.";
}
 
echo "\r\n";

有關(guān)完整的車庫(kù)水培解決方案，請(qǐng)參閱我們的其他項(xiàng)目

車庫(kù)水培 水
培 深水培養(yǎng) 斗系統(tǒng)
水培 種植傳感器/顯示模塊
水培 冷水機(jī)
水培 水/養(yǎng)分控制
水培 數(shù)據(jù)庫(kù)管理
水培 發(fā)芽控制
水培 CO2 監(jiān)測(cè)
水培 光照監(jiān)測(cè)
水培 pH 和 DO 監(jiān)測(cè)