專業(yè)水培數(shù)據(jù)庫管理開源分享

描述

車庫水培

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

因為你真的不知道你的水培系統(tǒng)發(fā)生了什么而感到沮喪？

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

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

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

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

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

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

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

優(yōu)點

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

缺點

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

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

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ù)負載。

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 管理器添加一個連接字符串“ServerConnectionString”，它將允許 .aspx 網(wǎng)頁連接到您的數(shù)據(jù)庫。

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

?

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

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

格拉法納 MSSQL v1.0

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

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ù)庫服務(wù)器的本地 Raspberry Pi 4 服務(wù)器

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

優(yōu)點

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

缺點

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

?

服務(wù)器部件

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

服務(wù)器軟件安裝

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

使用 phpMyAdmin 創(chuàng)建一個名為“mydata”的新數(shù)據(jù)庫，并使用 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ù)負載。

 
$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ù)庫連接，然后導(dǎo)入以下腳本以創(chuàng)建 Garage Hydroponics 儀表板。

格拉法納 MySQL v1.0

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

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

 
$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)完整的車庫水培解決方案，請參閱我們的其他項目

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