SQLite with Node-RED 

參考來源
https://randomnerdtutorials.com/sqlite-with-node-red-and-raspberry-pi/

database 的位置 在c:/Users/使用者/sqlite_2020_0720.db

利用 DB Browser for SQLite 來檢視資料庫的內容

https://sqlitebrowser.org/dl/

[{"id":"7b121f95.b0616","type":"inject","z":"ea7c92f3.931c3","name":"創建一個新表(只能執行一次)","topic":"CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, humidity NUMERIC, currentdate DATE, currenttime TIME, device TEXT)","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":80,"wires":[["7b2d78fb.d09568"]]},{"id":"a57530ed.20168","type":"inject","z":"ea7c92f3.931c3","name":"新增一筆資料","topic":"INSERT INTO dhtreadings(temperature, humidity, currentdate, currenttime, device) values(22.4, 48, date('now'), time('now'), \"manual\")","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":130,"y":120,"wires":[["7b2d78fb.d09568"]]},{"id":"8d9142a8.5e744","type":"inject","z":"ea7c92f3.931c3","name":"選擇一筆資料","topic":"SELECT * FROM dhtreadings","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":130,"y":160,"wires":[["7b2d78fb.d09568"]]},{"id":"ccba0fc8.f0199","type":"inject","z":"ea7c92f3.931c3","name":"刪除所有的資料","topic":"DELETE from dhtreadings","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":200,"wires":[["7b2d78fb.d09568"]]},{"id":"cd3a09d5.cb0708","type":"inject","z":"ea7c92f3.931c3","name":"SQLite 刪除表","topic":"DROP TABLE dhtreadings","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":130,"y":240,"wires":[["7b2d78fb.d09568"]]},{"id":"b6ea767d.af7298","type":"debug","z":"ea7c92f3.931c3","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":580,"y":180,"wires":[]},{"id":"7b2d78fb.d09568","type":"sqlite","z":"ea7c92f3.931c3","mydb":"e77b6c54.4f02","sqlquery":"msg.topic","sql":"","name":"SQLITE_ex","x":410,"y":180,"wires":[["b6ea767d.af7298"]]},{"id":"7ef18041.5341b","type":"inject","z":"ea7c92f3.931c3","name":"新增一筆資料(2)","topic":"INSERT INTO dhtreadings(temperature, humidity, currentdate, currenttime, device) values(26.4, 68, date('now'), time('now'), \"manual2\")","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":300,"wires":[["7b2d78fb.d09568"]]},{"id":"e77b6c54.4f02","type":"sqlitedb","z":"","db":"sqlite_2020_0720.db","mode":"RWC"}]

========================新增檢視資料庫 View Data=====================

[{"id":"ea7c92f3.931c3","type":"tab","label":"SQlite","disabled":false,"info":""},{"id":"7b121f95.b0616","type":"inject","z":"ea7c92f3.931c3","name":"創建一個新表(只能執行一次)","topic":"CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, humidity NUMERIC, currentdate DATE, currenttime TIME, device TEXT)","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":80,"wires":[["7b2d78fb.d09568"]]},{"id":"a57530ed.20168","type":"inject","z":"ea7c92f3.931c3","name":"新增一筆資料","topic":"INSERT INTO dhtreadings(temperature, humidity, currentdate, currenttime, device) values(22.4, 48, date('now'), time('now'), \"manual\")","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":130,"y":120,"wires":[["7b2d78fb.d09568"]]},{"id":"ccba0fc8.f0199","type":"inject","z":"ea7c92f3.931c3","name":"刪除所有的資料","topic":"DELETE from dhtreadings","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":160,"wires":[["7b2d78fb.d09568"]]},{"id":"cd3a09d5.cb0708","type":"inject","z":"ea7c92f3.931c3","name":"SQLite 刪除表","topic":"DROP TABLE dhtreadings","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":130,"y":200,"wires":[["7b2d78fb.d09568"]]},{"id":"b6ea767d.af7298","type":"debug","z":"ea7c92f3.931c3","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":580,"y":180,"wires":[]},{"id":"7b2d78fb.d09568","type":"sqlite","z":"ea7c92f3.931c3","mydb":"e77b6c54.4f02","sqlquery":"msg.topic","sql":"","name":"SQLITE_ex","x":410,"y":180,"wires":[["b6ea767d.af7298"]]},{"id":"7ef18041.5341b","type":"inject","z":"ea7c92f3.931c3","name":"新增一筆資料(2)","topic":"INSERT INTO dhtreadings(temperature, humidity, currentdate, currenttime, device) values(26.4, 68, date('now'), time('now'), \"manual2\")","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":240,"wires":[["7b2d78fb.d09568"]]},{"id":"3f661adb.08c156","type":"ui_template","z":"ea7c92f3.931c3","group":"b165cbf1.03d608","name":"UI Table","order":2,"width":"6","height":"3","format":"<table style=\"width:100%\">\n  <tr>\n    <th>Time</th> \n    <th>Temp</th> \n    <th>Hum</th>\n  </tr>\n  <tr ng-repeat=\"x in msg.payload | limitTo:120\">\n    <td>{{msg.payload[$index].currenttime}}</td>\n    <td>{{msg.payload[$index].temperature}}</td> \n    <td>{{msg.payload[$index].humidity}}</td>\n  </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","x":480,"y":300,"wires":[[]]},{"id":"6e90c476.4aa5fc","type":"sqlite","z":"ea7c92f3.931c3","mydb":"e77b6c54.4f02","sqlquery":"msg.topic","sql":"","name":"SQLITE_ex","x":310,"y":300,"wires":[["3f661adb.08c156"]]},{"id":"14a97083.ef542f","type":"inject","z":"ea7c92f3.931c3","name":"選擇一筆資料","topic":"SELECT * FROM dhtreadings","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":130,"y":300,"wires":[["6e90c476.4aa5fc"]]},{"id":"e77b6c54.4f02","type":"sqlitedb","z":"","db":"sqlite_2020_0720.db","mode":"RWC"},{"id":"b165cbf1.03d608","type":"ui_group","z":"","name":"Data","tab":"7a15eb23.c05f34","order":3,"disp":true,"width":"6","collapse":false},{"id":"7a15eb23.c05f34","type":"ui_tab","z":"","name":"SQlite_2020_0720","icon":"dashboard","disabled":false,"hidden":false}]

<table style="width:100%">
  <tr>
    <th>Time</th>
    <th>Temp</th>
    <th>Hum</th>
  </tr>
  <tr ng-repeat="x in msg.payload | limitTo:120">
    <td>{{msg.payload[$index].currenttime}}</td>
    <td>{{msg.payload[$index].temperature}}</td>
    <td>{{msg.payload[$index].humidity}}</td>
  </tr>
</table>

CREATE inject node as follows

CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, humidity NUMERIC, currentdate DATE, currenttime TIME, device TEXT)

Configure your INSERT inject node

INSERT INTO dhtreadings(temperature, humidity, currentdate, currenttime, device) values(22.4, 48, date('now'), time('now'), "manual")

Configure your SELECT inject node with

SELECT * FROM dhtreadings

Configure your DROP inject node

DROP TABLE dhtreadings

Configure your DELETE inject node as follows

DELETE from dhtreadings

ESP32 mfrc522 RFID + MQTT Broker + Telegram bot + Line Notify

ESP32 mfrc522 RFID + MQTT Broker + Telegram bot + Line Notify

/*********
  Rui Santos
  Complete project details at https://randomnerdtutorials.com 
*********/

#include <WiFi.h>
#include <PubSubClient.h>
#include <SPI.h>
#include "MFRC522.h"
#include <WiFiClientSecure.h>
#include <UniversalTelegramBot.h>   // Universal Telegram Bot Library written by Brian Lough: https://github.com/witnessmenow/Universal-Arduino-Telegram-Bot
#include <ArduinoJson.h>
#include <TridentTD_LineNotify.h>


const int RST_PIN = 22; // Reset pin
const int SS_PIN = 21; // Slave select pin
//==========================
//esp32     mfrc522
//21        SDA
//18        SCK
//23        MOSI
//21        MISO
//22        RST
//GND       GND
//3.3v      3.3V
//==========================

// Update these with values suitable for your network.
//const char *ssid = "PTS-2F";
//const char *pass = "";
//const char *ssid = "WBR-2200";
//const char *pass = "0226452362";

const char *ssid = "74170287";
const char *pass = "24063173";
//const char *ssid =  "yourSSID";     // change according to your Network - cannot be longer than 32 characters!
//const char *pass =  "yourPASSWORD"; // change according to your Network

// Initialize Telegram BOT
//#define BOTtoken "XXXXXXXXXX:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"  // your Bot Token (Get from Botfather)
#define BOTtoken "925728551:AAGrTFQWW2NXqMs3uMJDW3891GUVJF-l13YtVc"  // your Bot Token (Get from Botfather)
// Use @myidbot to find out the chat ID of an individual or a group
// Also note that you need to click "start" on a bot before it can
// message you
//#define CHAT_ID "XXXXXXXXXX"
#define CHAT_ID "1143751158"

// 修改成上述寄到登入郵箱的 Token號碼
//#define LINE_TOKEN "123456789012345678901234567890123456789012"
#define LINE_TOKEN "saWPsiHeQ033bcZ32DaRkqE2G7a4nU014EN2L3e57bJMNCkvv"


#define MQTTid              ""                           //id of this mqtt client
#define MQTTip              "broker.mqtt-dashboard.com"  //ip address or hostname of the mqtt broker
#define MQTTport            1883                         //port of the mqtt broker
#define MQTTuser            "alex9ufo"                   //username of this mqtt client
#define MQTTpsw             "alex1234"                   //password of this mqtt client
//#define MQTTuser          "your_username"              //username of this mqtt client
//#define MQTTpsw           "your_password"              //password of this mqtt client
#define MQTTpubQos          2                            //qos of publish (see README)
#define MQTTsubQos          1                            //qos of subscribe

WiFiClientSecure client1;
UniversalTelegramBot bot(BOTtoken, client1);

// Checks for new messages every 1 second.
int botRequestDelay = 1000;
unsigned long lastTimeBotRan;

MFRC522 mfrc522(SS_PIN, RST_PIN); // Create MFRC522 instance

//Variables
long lastMsg = 0;
char jsonChar[100];
String IDNo_buf="";
const int BUILTIN_LED = 2; //D2
const int ledPin = 2; //D2
bool Flash = false;  //true
bool ledState = LOW;
String IPaddress;


//=============================================================================
boolean pendingDisconnect = false;
void mqttConnectedCb(); // on connect callback
void mqttDisconnectedCb(); // on disconnect callback
void mqttDataCb(char* topic, byte* payload, unsigned int length); // on new message callback

WiFiClient wclient;
PubSubClient client(MQTTip, MQTTport, mqttDataCb, wclient);

//=============================================================================
String printHex(byte *buffer, byte bufferSize) {
      String id = "";
      for (byte i = 0; i < bufferSize; i++) {
        id += buffer[i] < 0x10 ? "0" : "";
        id += String(buffer[i], HEX);
      }
      return id;
    }
//=============================================================================
void mqttConnectedCb() {
  Serial.println("connected");
 
  // Once connected, publish an announcement...
  client.publish("alex9ufo/outTopic/RFID/json", jsonChar, MQTTpubQos, true); // true means retain
  // ... and resubscribe
  client.subscribe("alex9ufo/inTopic", MQTTsubQos);

}
//=============================================================================
void mqttDisconnectedCb() {
  Serial.println("disconnected");
}
//=============================================================================
void mqttDataCb(char* topic, byte* payload, unsigned int length) {

  /*
  you can convert payload to a C string appending a null terminator to it;
  this is possible when the message (including protocol overhead) doesn't
  exceeds the MQTT_MAX_PACKET_SIZE defined in the library header.
  you can consider safe to do so when the length of topic plus the length of
  message doesn't exceeds 115 characters
  */
  char* message = (char *) payload;
  message[length] = 0;

  Serial.print("Message arrived [");
  Serial.print(topic);
  Serial.print("] ");
  Serial.println(message);
  // Switch on the LED if an 1 was received as first character
  if (message[0] == '0') {
     digitalWrite(BUILTIN_LED, LOW);   // Turn the LED on (Note that LOW is the voltage level
     // but actually the LED is on; this is because
     Serial.println("Received 0 , Send LOW TO BuildIn_LED off LED");
     Flash = false;
     //==============================
     LINE.setToken(LINE_TOKEN);
     // 先換行再顯示
     LINE.notify("\nSend LOW TO BuildIn_LED off LED");
     //===============================
      bot.sendMessage(CHAT_ID, "LED state set to ON", "");       
    }
   if (message[0] == '1') {
     digitalWrite(BUILTIN_LED, HIGH);   // Turn the LED off (Note that HIGH is the voltage level
     // but actually the LED is on; this is because
     Serial.println("Received 1 , Send HIGH TO BuildIn_LED on LED");
     Flash = false;
     //==============================
     LINE.setToken(LINE_TOKEN);
     // 先換行再顯示
     LINE.notify("\nSend HIGH TO BuildIn_LED on LED");
     //===============================   
     bot.sendMessage(CHAT_ID, "LED state set to OFF", "");
   }
    if (message[0] == '2') {
     digitalWrite(BUILTIN_LED, HIGH);   // Turn the LED off (Note that HIGH is the voltage level
     // but actually the LED is on; this is because
     Serial.println("Received 2 , Flashing BuildIn_LED ");
   
     bot.sendMessage(CHAT_ID, "LED state set to Flash", ""); 
     Flash = true;
     //==============================
     LINE.setToken(LINE_TOKEN);
     // 先換行再顯示
     LINE.notify("\nFlashing BuildIn_LED");
     //=============================== 

   } //if (message[0] == '2')
}

//======================================================
// Handle what happens when you receive new messages
void handleNewMessages(int numNewMessages) {
  Serial.println("handleNewMessages");
  Serial.println(String(numNewMessages));

  for (int i=0; i<numNewMessages; i++) {
    // Chat id of the requester
    String chat_id = String(bot.messages[i].chat_id);
    if (chat_id != CHAT_ID){
      bot.sendMessage(chat_id, "Unauthorized user", "");
      continue;
    }
   
    // Print the received message
    String text = bot.messages[i].text;
    Serial.println(text);

    String from_name = bot.messages[i].from_name;

    if (text == "/start") {
      String welcome = "Welcome, " + from_name + ".\n";
      welcome += "Use the following commands to control your outputs.\n\n";
      welcome += "/led_on to turn GPIO ON \n";
      welcome += "/led_off to turn GPIO OFF \n";
      welcome += "/state to request current GPIO state \n";
      bot.sendMessage(chat_id, welcome, "");
    }

    if (text == "/led_on") {
      bot.sendMessage(chat_id, "LED state set to ON", "");
      ledState = HIGH;
      digitalWrite(ledPin, ledState);
      Flash = false;
    }
   
    if (text == "/led_off") {
      bot.sendMessage(chat_id, "LED state set to OFF", "");
      ledState = LOW;
      digitalWrite(ledPin, ledState);
      Flash = false;
    }

    if (text == "/led_flash") {
      bot.sendMessage(chat_id, "LED state set to Flash", "");
      ledState = LOW;
      digitalWrite(ledPin, ledState);
      Flash = true;
    }
   
    if (text == "/state") {
      if (Flash==true){
        bot.sendMessage(chat_id, "LED is Flash", "");
      }
      else
      if (digitalRead(ledPin)){
        bot.sendMessage(chat_id, "LED is ON", "");
      }
      else{
        bot.sendMessage(chat_id, "LED is OFF", "");
      }
    }
  }
}

//======================================================
void setup_wifi() {
  delay(10);
  // We start by connecting to a WiFi network
  Serial.println();
  Serial.print("Connecting to ");
  Serial.println(ssid);

  WiFi.begin(ssid, pass);

  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }

  Serial.println("");
  Serial.println("WiFi connected");
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP());
 
  bot.sendMessage(CHAT_ID, "Bot started up", "");
  bot.sendMessage(CHAT_ID, "/start" , "");
}
//======================================================
void setup() {
  Serial.begin(115200);
  setup_wifi();
  pinMode(BUILTIN_LED, OUTPUT);
  Serial.println(F("Booting...."));
  //===================================
  // 顯示 Line版本
  Serial.println(LINE.getVersion());
  //=================================== 
  SPI.begin();           // Init SPI bus
  mfrc522.PCD_Init();    // Init MFRC522
  Serial.println(F("Ready!"));
  Serial.println(F("======================================================"));
  Serial.println(F("Scan for Card and print UID:"));
}
//======================================================
void process_mqtt() {
  if (WiFi.status() == WL_CONNECTED) {
    if (client.connected()) {
      client.loop();
    } else {
    // client id, client username, client password, last will topic, last will qos, last will retain, last will message
      if (client.connect(MQTTid, MQTTuser, MQTTpsw, MQTTid "/status", 2, true, "0")) {
          pendingDisconnect = false;
          mqttConnectedCb();
      }
    }
  } else {
    if (client.connected())
      client.disconnect();
  }
  if (!client.connected() && !pendingDisconnect) {
    pendingDisconnect = true;
    mqttDisconnectedCb();
  }
}
//======================================================
void loop() {
  process_mqtt();
  long now = millis();
  if (Flash)
  {
    digitalWrite(BUILTIN_LED, !digitalRead(BUILTIN_LED));
    delay(500);
  }

  //======================================================
  if (millis() > lastTimeBotRan + botRequestDelay)  {
    int numNewMessages = bot.getUpdates(bot.last_message_received + 1);

    while(numNewMessages) {
      Serial.println("got response");
      handleNewMessages(numNewMessages);
      numNewMessages = bot.getUpdates(bot.last_message_received + 1);
    }
    lastTimeBotRan = millis();
  }
  //======================================================
 
  if (mfrc522.PICC_IsNewCardPresent() && mfrc522.PICC_ReadCardSerial()) { // 如果出現新卡片就讀取卡片資料
     delay(100);
     String IDNo = printHex(mfrc522.uid.uidByte, mfrc522.uid.size);
     // Show some details of the PICC (that is: the tag/card)
     if ((IDNo != IDNo_buf) || (now - lastMsg > 5000)) {  //不同卡片 或是 等5秒
         lastMsg = now;
         Serial.print(F("Card UID:"));
         Serial.println(IDNo);
         //Serial.println(IDNo_buf);
   
         IDNo_buf="";
         IDNo_buf=IDNo;
         // Convert data to JSON string
         String json =
         "{\"data\":{"
         "\"RFID_No\": \"" + IDNo + "\"}"
         "}";
         // Convert JSON string to character array
         json.toCharArray(jsonChar, json.length()+1);
   
         if  (client.connected()) {
              Serial.print("Publish message: ");
              Serial.println(json);
              // Publish JSON character array to MQTT topic
             client.publish("alex9ufo/outTopic/RFID/json",jsonChar);
             //========================================
             bot.sendMessage(CHAT_ID, jsonChar , "");
             //========================================
             LINE.setToken(LINE_TOKEN);
             // 先換行再顯示
             String temp="\n卡號:"+String(jsonChar);
             LINE.notify(temp);
             //========================================             
         }
      } // if ((IDNo != IDNo_buf) || (now - lastMsg > 5000))
  }  // if (mfrc522.PICC_IsNewCardPresent()

}   //Loop
//======================================================

     

ESP8266發送DHT-11的溫濕度值到Line通知

https://atceiling.blogspot.com/2019/07/arduino39dht-11line.html

#include <ESP8266WiFi.h>
#include <WiFiClient.h>
#include <DHT.h>
#include <TridentTD_LineNotify.h>

#define DHTTYPE DHT11
#define DHTPIN  2

// 修改成上述寄到登入郵箱的 Token號碼
#define LINE_TOKEN "123456789012345678901234567890123456789012"

// 設定無線基地台SSID跟密碼
const char* ssid     = "MyHome";
const char* password = "12345678";

DHT dht(DHTPIN, DHTTYPE, 11);    // 11 works fine for ESP8266
 
float humidity, temp_f;   // 從 DHT-11 讀取的值

unsigned long previousMillis = 0;        // will store last temp was read
const long interval = 2000;              // interval at which to read sensor

// 用不到以下兩個變數
// const char* host = "notify-api.line.me";
// const int httpsPort = 443;
 
void setup(void)
{
  Serial.begin(9600);  // 設定速率 感測器
  dht.begin();           // 初始化

  WiFi.mode(WIFI_STA);
  // 連接無線基地台
  WiFi.begin(ssid, password);
  Serial.print("\n\r \n\rWorking to connect");

  // 等待連線，並從 Console顯示 IP
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  Serial.println("");
  Serial.println("DHT Weather Reading Server");
  Serial.print("Connected to ");
  Serial.println(ssid);
  Serial.print("IP address: ");
  Serial.println(WiFi.localIP());
}
 
void loop(void)
{
  // 量測間等待至少 2 秒
  unsigned long currentMillis = millis();
 
  if(currentMillis - previousMillis >= interval) {
    // 將最後讀取感測值的時間紀錄下來 
    previousMillis = currentMillis;   

    // 讀取溫度大約 250 微秒!
    humidity = dht.readHumidity();          // 讀取濕度(百分比)
    temp_f = dht.readTemperature(true);     // 讀取溫度(華氏)
    
 
    // 檢查兩個值是否為空值
    if (isnan(humidity) || isnan(temp_f)) {
       Serial.println("Failed to read from DHT sensor!");
       return;
    }
  }

  String tempe="溫度:"+String((int)(temp_f-32)*5/9)+"℃";   
  String humid="濕度:"+String((int)humidity)+"％";

  // 顯示 Line版本
  Serial.println(LINE.getVersion());
 
  LINE.setToken(LINE_TOKEN);

  // 先換行再顯示
  LINE.notify("\n" + tempe + " ；" + humid);
      
  // 每2分鐘發送一次
  delay(120000);
}