2020年10月30日 星期五

SQLite管理工具DB Browser for SQLite

 SQLite管理工具DB Browser for SQLite

源自於 http://blog.bod.idv.tw/2019/09/sqlitedb-browser-for-sqlite.html
SQLite-tools雖然是官方網站提供的管理工具,但文字界面的操作方式,操作上總有親和力不足的狀況,還好除了SQLite-tools,並不是別無選擇,還可以有DB Browser for SQLite, SQLite Studio...,這裡先介紹DB Browser for SQLite。

DB Browser for SQLite官方網站:https://sqlitebrowser.org/
DB Browser for SQLite的下載網址:https://sqlitebrowser.org/dl/
提供各種作業系統(Windows, Mac, Linux ...) 安裝、免安裝、可攜程式的下載,或線上更新安裝的指令做法等。


以.zip (no installer) for 64-bit Windows 為例:(解壓縮後,可以直接使用)


DB Browser 主要的功能簡介:
  • 主畫面→Database Structure
  • 主畫面→Browse Data
  • 主畫面→Edit Pragmas
    選項:Auto Vacuum / Automatic Index / Case Sensitive Like / Checkpoint Full FSYNC / Foreign Keys / Full FSYNC / Ignore Check Contraints / Journal Mode / Journal Size Limit / Locking Mode / Max Page Count / Page Size / Recursive Triggers / Secure Delete / Sychronous / Temp Store / User Version / WAL Auto Checkpoint
  • 主畫面→Execute SQL
  • 檔案→新建資料庫
  • 檔案→New In-Memory Database
  • 檔案→打開資料庫
  • 檔案→Open Database Read Only ...
  • 檔案→Attach Database ...
  • 檔案→關閉資料庫
  • 檔案→Write Changes
  • 檔案→Revert Changes
  • 檔案→匯入→Database from SQL file ...
  • 檔案→匯入→Table from CSV file ...
  • 檔案→匯出→Database to SQL file ...
  • 檔案→匯出→Table(s) as CSV file ...
  • 檔案→匯出→Table(s) to JSON ...
  • 檔案→Open Project...
  • 檔案→Save Project...
  • 檔案→最近開啟過的五個資料庫檔案
  • 檔案→退出
  • 編輯→Create Table...
  • 編輯→Modify Table...
  • 編輯→Delete Table...
  • 編輯→Create Index...
  • 編輯→偏好選項...
  • 編輯→偏好選項...→General
  • 編輯→偏好選項...→資料庫
    SQL to execute after opening database,這個功能可以紀錄資料庫的開啟歷程
  • 編輯→偏好選項...→Data Browser
  • 編輯→偏好選項...→SQL
  • 編輯→偏好選項...→擴充套件
  • 編輯→偏好選項...→Remote
  • 查看→SQL Log
  • 查看→Plot
  • 查看→DB Schema
  • 查看→Edit Database Cell
  • 查看→Remote
  • 查看→資料庫工具列
  • 查看→Extra DB Toolbar
  • 查看→Project Toolbar
  • Tools→Compact Database...
  • Tools→Load Extension...
  • Tools→Integrity...
  • Tools→Quick Integrity Check
  • Tools→Foreign-Key Check
  • Tools→Optimize
  • Help→What's This?
  • Help→Website
  • Help→Wiki
  • Help→Bug Report
  • Help→Feature Request...
  • Help→Nonate on Patreon...
  • Help→About

RFID 實驗2-1 Node-RED , MQTT , ESP32 LED

 RFID 實驗2-1 Node-RED , MQTT , ESP32 LED 



1. Arduino 程式說明

     載點 

http://www.mediafire.com/file/jxcn89rrpsnudmd/%25E5%25AF%25A6%25E9%25A9%25972_Arduino%25E8%25A7%25A3%25E8%25AA%25AA.pdf/file


2. 實驗2-1 系統說明

     載點

http://www.mediafire.com/file/qtpluqziw9bude9/%25E5%25AF%25A6%25E9%25A9%25972-1_%25E7%25B3%25BB%25E7%25B5%25B1%25E8%25AA%25AA%25E6%2598%258E.pdf/file


3. Node-Red 程式說明

      載點

http://www.mediafire.com/file/auknrjvuc3rejl6/%25E5%25AF%25A6%25E9%25A9%25972-1__NodeRed_%25E7%25A8%258B%25E5%25BC%258F%25E8%25AA%25AA%25E6%2598%258E.pdf/file

4. DB SQLite & DB Browser 說明

http://www.mediafire.com/file/01f3p60cfcj5w5i/%25E5%25AF%25A6%25E9%25A9%25972-1__DB_Browser_for_SQLite_%25E8%25A6%2596%25E8%25A6%25BA%25E5%258C%2596%25E7%259A%2584_SQLite_%25E7%25AE%25A1%25E7%2590%2586%25E5%25B7%25A5%25E5%2585%25B7%25E8%2588%2587NodeRed_SQLite.pdf/file


4. Node-Red 程式

      

 http://www.mediafire.com/file/llxphigolay1gna/flows_MQTT_LED%25E6%258E%25A7%25E5%2588%25B6.json/file

http://www.mediafire.com/file/guaqvze8bfbig64/flows_MQTT_LED%25E6%258E%25A7%25E5%2588%25B6.json/file

5. Arduino 程式

      

http://www.mediafire.com/file/c4zwijc2kd08lwk/ESP32_RFID_MQTT_NODE_RED_LED%25282%2529.ino/file




=================================================================

http://www.mediafire.com/file/qtpluqziw9bude9/%25E5%25AF%25A6%25E9%25A9%25972-1_%25E7%25B3%25BB%25E7%25B5%25B1%25E8%25AA%25AA%25E6%2598%258E.pdf/file

http://www.mediafire.com/file/auknrjvuc3rejl6/%25E5%25AF%25A6%25E9%25A9%25972-1__NodeRed_%25E7%25A8%258B%25E5%25BC%258F%25E8%25AA%25AA%25E6%2598%258E.pdf/file

http://www.mediafire.com/file/01f3p60cfcj5w5i/%25E5%25AF%25A6%25E9%25A9%25972-1__DB_Browser_for_SQLite_%25E8%25A6%2596%25E8%25A6%25BA%25E5%258C%2596%25E7%259A%2584_SQLite_%25E7%25AE%25A1%25E7%2590%2586%25E5%25B7%25A5%25E5%2585%25B7%25E8%2588%2587NodeRed_SQLite.pdf/file

http://www.mediafire.com/file/jxcn89rrpsnudmd/%25E5%25AF%25A6%25E9%25A9%25972_Arduino%25E8%25A7%25A3%25E8%25AA%25AA.pdf/file

http://www.mediafire.com/file/c4zwijc2kd08lwk/ESP32_RFID_MQTT_NODE_RED_LED%25282%2529.ino/file

2020年10月29日 星期四

有關 pubsubclient.h 問題 (PubSubClient Version 2.6.0版本)

 有關 pubsubclient.h 問題 (PubSubClient Version 2.6.0版本)

pubsubclient.h 採用 third parity方式安裝


1)    若是已經安裝者pubsubclient 程式庫者 請將 C:\Users\使用者\Documents\Arduino\libraries 中的目錄 pubsubclient刪除


2)    pubsunclient-master解壓縮後 整個目錄 copy C:\Users\使用者\Documents\Arduino\libraries 中的目錄。


解壓縮copy C:\Users\使用者\Documents\Arduino\libraries 中的目錄


3)    打開Arduino 組譯 是否有錯誤  ???

  // Once connected, publish an announcement...

  client.publish("alex9ufo/outTopic/RFID/json", jsonChar, MQTTpubQos, true); // true means retain

  // ... and resubscribe

  client.subscribe("alex9ufo/inTopic", MQTTsubQos);

4)    沒有錯誤代表PubSubClient.h 程式庫正確


載點1

http://www.mediafire.com/file/haitnpho847mj4r/MQTT_RFID%25E4%25B8%25AD%25E7%259A%2584_PubSubClient_%25E7%25A8%258B%25E5%25BC%258F%25E5%25BA%25AB%25E5%25AE%2589%25E8%25A3%259D%25E5%2595%258F%25E9%25A1%258C.docx/file

載點2

http://www.mediafire.com/file/haitnpho847mj4r/MQTT_RFID%25E4%25B8%25AD%25E7%259A%2584_PubSubClient_%25E7%25A8%258B%25E5%25BC%258F%25E5%25BA%25AB%25E5%25AE%2589%25E8%25A3%259D%25E5%2595%258F%25E9%25A1%258C.docx/file

pubsunclient-master載點

http://www.mediafire.com/file/ndfqkgl45m0m31b/pubsubclient-master.rar/file

 

2020年10月27日 星期二

AUTOMATE YOUR LIFE WITH NODE-RED (PLUS A DASH OF MQTT)

源自於https://hackaday.com/2020/01/15/automate-your-life-with-node-red-plus-a-dash-of-mqtt/

AUTOMATE YOUR LIFE WITH NODE-RED (PLUS A DASH OF MQTT)

For years we’ve seen a trickle of really interesting home automation projects that use the Node-RED package. Each time, the hackers behind these projects have raved about Node-RED and now I’ve joined those ranks as well.

This graphic-based coding platform lets you quickly put together useful operations and graphic user interfaces (GUIs), whether you’re the freshest greenhorn or a seasoned veteran. You can use it to switch your internet-connected lights on schedule, or at the touch of a button through a web-app available to any device on your home network. You can use it as an information dashboard for the weather forecast, latest Hackaday articles, bus schedules, or all of them at once. At a glance it abstracts away the complexity of writing Javascript, while also making it simple to dive under hood and use your 1337 haxor skills to add your own code.

You can get this up and running in less than an hour and I’m going to tackle that as well as examples for playing with MQTT, setting up a web GUI, and writing to log files. To make Node-RED persistent on your network you need a server, but it’s lean enough to run from a Raspberry Pi without issue, and it’s even installed by default in BeagleBone distributions. Code for all examples in this guide can be found in the tutorial repository. Let’s dive in!

WHAT IT IS

Node-RED is a graphical programming language built on Node.js. It implements a server and runs what are called “Flows”: programs based on Javascript. Why would you want to run a server-side IDE for your programs? Because Node-RED also makes it dead simple to spin up web apps and use them as your online information and control system.

INSTALLATION

To make your Node-RED programs persistent you do need a server, however, if you just want to play for now you can run locally. Your server can be as simple as installing the platform on a Raspberry Pi or an always-on computer on your LAN. Prerequisites include Node.js and npm (the Node.js package manager) which on a Linux system are an easy install.

sudo apt install nodejs

Now we can install Node-RED and, to follow the examples below, you should also install the dashboard package:

npm install node-red
npm install node-red-dashboard

To run locally you can just type node-red in the terminal. However, the more eloquent way to run this is as a systemd service. Copy the contents of the nodered.service file to /etc/systemd/system/nodered.service and update the UserGroup, and WorkingDirectory variables in that file to match an actual user on your system. With that in place, just enable and start the service. It will now restart on a crash or system reboot from here on out.

1
2
systemctl enable nodered.service
systemctl start nodered.service

You can now load up the Node-RED IDE simply by visiting localhost:1880 in a web browser.

HELLO WORLD

The simplest thing to do as your first “flow” in Node-RED is: click button, get timestamp. To make the image above I did nothing more than drag the “Inject” and “Debug” nodes from the left column into the center, then drag the line that connects the two nodes. You need to click the “Deploy” button on the upper right any time you make changes, and then clicking the button hanging off the left side of the inject node, which has the “timestamp” label by default, to spit out the time in the debug window. Click the bug icon in above the right window if you’re not seeing the debug output.

This example isn’t very useful, but that’s not the point of Hello World code. This drives home the power of the graphical code system. What’s also interesting is that flows can be exported as json files. Here’s what this Hello World looks like and it can be imported to your own Node-RED installation.

MQTT QUICKSTART

Node-RED feels like it’s made specifically to be used with MQTT, the popular Internet of Things protocol for which Elliot Williams has written a fantastic guide. It feels that way because an MQTT client is built in and most of the nodes have “topics” as well as message payloads which is all you really need to communicate with an MQTT broker.

As you can see above, I’m doing the exact same inject/debug trick but now I’ve dragged an “mqtt in” and “mqtt out” node from the “Network” column of possible nodes.

There’s slightly more setup here as we need to choose an MQTT server and select a topic to publish to and listen for. But the interface makes this very easy, just double-click one of the MQTT nodes. Here I’m using the mosquitto test server (test.mosquitto.org)and the topic Hackaday/nodered/test. Just realize that anyone looking at messages on that server can see this and if you use the exact same topic you may see other readers sending test messages. Node-RED can actually be used as an MQTT broker as well.

Try double-clicking the inject node and changing the payload from timestamp to a string and you can send your own custom messages. For the most part I find it easy to find my way around Node-RED and playing with settings is low-effort. Just make sure to hit the deploy button — your changes won’t actually be in place until you do.

WEB GUI HELLO WORLD

Let’s get to the really exciting part of Node-Red, the ability to spin up a web app with very little effort.

Here you can see a smartphone displaying our app. The only really useful part here is the button. Click it and you’ll get “Hello Hackaday!” in the debug window of Node-RED as seen above. All it took to create this page was to install the dashboard package for Node-RED and then drag a button onto the canvas. Once deployed, your web app will be located at localhost:1880/ui

Installation of the package is a simple one-liner:

npm install node-red-dashboard

Dragging the button onto the canvas and hooking it to a debug node is also simple, but you need to do just a bit of configuration. Double-clicking on the button node you can change the payload to affect what message is sent to the debug window, but you also need to set a Group, and within the group edit dialog you’ll need to set a Tab. This affects the web app, with Groups organizing blocks on each page of the web app, and Tabs selecting different pages from the hamburger menu at the upper left. You can name groups and tabs however you like.

LET’S BUILD A WEB APP!

Enough with the Hello World code, let’s build something useful. I’ve been using Node-RED for a month or so and have built up a couple of useful apps, one interacts with my MQTT broker to control and monitor my front porchlight, the other I use as a simple button-press to keep track of the days I exercise. Let’s build up the exercise app bit by bit because there’s more to it than merely sending MQTT packets back and forth.

Here is the current state of the exercise app, which includes a button that records today’s date to a log file and a gauge that reads the log file to display how many of the last seven days have included exercise. Let’s build it up one block at a time.

GUI Button Writing to Files

This is where the flow begins. It consists of a button from the Dashboard package that sends a timestamp when clicked. This message will be logged to two “file” nodes, the first is exerciselog-raw.txt which simply records one UNIX timestamp for each line. That’s not human readable, so the second file node has a function node which translates the timestamp using the following JavaScript snippet. There’s a bit of magic in there to make sure the month and day are always two digits.

1
2
3
4
5
6
7
8
9
var date;
date = new Date();
var year = date.getFullYear();
var month = date.getMonth();
month = (month < 9 ? '0' : '') + (month+1)
var day = date.getDate();
day = (day < 10 ? '0' : '') + day
msg.payload = year + '-' + month + '-' + day;
return msg;

Adding a User Notification

The button works as expected, but it gives no feedback to the user. To improve upon this I added a notification node from the dashboard package. It is connected after the file node to confirm that the date had been written to the log file.

Reading a File, Displaying Data, Refreshing at Startup

This last part of the flow uses the tan “file in” node to read UNIX timestamps from the raw log file and displays it on the teal “gauge” node from the dashboard package. It is activated by two different triggers, one updates after a new date is written to the log files. The other is the lavender “inject” node which has an “index once after n seconds” option to populate the initial data when Node-RED starts.

The gauge is just looking for a number to populate and this is fed by a function node (I called it Magic). The following code reads in the logfile as an array, figures out the UNIX date code for seven days ago, and then iterates back through the last seven timestamps in the log file.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
//Turn incoming timestamps log into an array:
var exercisearray = msg.payload.split("\n");
if (exercisearray.slice(-1)[0] === "") exercisearray.length = exercisearray.length-1
 
//Get timestamp for week ago to compare against
var thismorning = new Date()
thismorning.setHours(0)
thismorning.setMinutes(0)
thismorning.setSeconds(0)
thismorning.setMilliseconds(0)
var sixdays = 1000*60*60*24*6
var oneweekago = thismorning.getTime()-sixdays
 
//Iterate and count past week of exercise
var count = 0
var secondsinday = 60*24*7
for (var i=1; i<8; i++) {
  if (i>exercisearray.length) break;
  var testval = parseInt(exercisearray.slice(-i)[0]);
  if (testval >= oneweekago) ++count;
}
 
//Store our answer as the payload and pass along
msg.payload = count;
return msg;

GIVE NODE-RED A TRY!

One of my first concerns with the platform was version control but that’s available too. There is git integration built-in called Node-RED projects but it’s not enabled by default. I’m not accustomed to using a GUI for git, but then again I’m not accustomed to graphical programming interfaces so it doesn’t hurt to try something new.

The examples I’ve shown here are really the tip of the iceberg. Look around and you’ll find a ton of enthusiasm for Node-RED which translates to incredible flows and awesome web apps. For instance, I’ve been reading Scargill’s Tech Blog for years and there you’ll find a ton examples of what can be accomplished. Here we see Scargill’s thermostat control panel that has all kinds of customization to give it a special look. Finding examples that you like isn’t hard, and copying their code is even easier.

You can easily pick up Node-RED in an afternoon and end up with something useful. For those who want to spend more time, the sky’s the limit. If you have any kind of home automation, it’s something you must try as it unlocks the ability for anyone on your LAN to access information and control without installing an app. You can easily pull a disused smartphone out of the drawer and turn it into a dedicated control panel, something I did for the image at the top of this article with the help of an Android app called Fully Kiosk Browser Lockdown for a true fullscreen browser experience not provided by Chrome or Firefox for Android. Give it a try with your own surplus gear!


Resources:

2024年4月24日 星期三 Node-Red Dashboard UI Template + AngularJS 參考 AngularJS教學 --2

 2024年4月24日 星期三 Node-Red Dashboard UI Template + AngularJS 參考 AngularJS教學 --2 AngularJS 實例 <!DOCTYPE html> <html> <head> &...