Jan 14 2023
NR MQTT Local & Remote SQLite DB
Posted by kll · In RPI · 385 Reads ·
rework LOCAL HIST TREND
REMOTE trend
PICO_W 2
REMOTE trend MULTI SITE
CP800 out of beta?
system OVVW
code
work
project INA 50A
rework LOCAL HIST TREND
it all starts with SQLite3 or generally SQL does not work nice with JSON
so as soon i run into this i started using not { "a":1, "b":"ttt" } but
{ 'a':1, 'b':'ttt' } as that can be stored into a SQL TEXT variable directly.
with a STRING.replace the ' can be changed back to " and make JSON object again to get the variables out of it.
well i overdo that starting with it already in PICO_W mqtt.py send string
and that is the start of this revision v1.1.2 a overhaul of the PICO_W mqtt.payload.
also i had to clean lots of Node-Red function JS up to the point where i store to DB
( still at RAM-DISK but will now change to SSD drive storage on RPI )
so the new database has a column 'payloads' with that { 'a':1, 'b':'ttt' } type string
( after replace " to ' for this.
and to avoid doing all backward like read it , ' to ", convert JSON , separate to extra variables, use in SQL HIST trend...
i make a BAD database!!!
-a- for similar reason and different usage ( HIST LIST and HIST TREND ( now 2 pages ))
store timedate as EPOCH and timedates as STRING to show it in HIST_LIST
-b- use the payloads string as is just to show it in HIST_LIST
-c- before i make it i separate from mqtt.payload JSON the variables DC_Volt, DC_Amp, DC_Watt and also store them as REAL columns, and that i use to make the HIST_TREND graphs via SQL readouts
- - that is kind of redundant data storage insofar "BAD"
so you see that is a lot of changes in the background ( you would not notice )
but i needed to do it before i start to make better graphs / HIST_TREND operation..
one thing for HIST_TREND is to try OPERATION BUTTONS
[ -d ] [ -h ] [ +h ] [ +d ]
select to show a hour or a day
move back and forth one hour OR day.
( here not sure Node-Red internal Trend graph takes 1440 values? )
and i read there might be a DATE picker i can use to select a certain date...
ok, to play with all this i need data for several days ( from SSD disk SQLIte3 db)
but first look:
tomorrow know more if that works.
datetimes: is the string version of epoch made in RPI3 at the moment when a PICO_W mqtt comes in
( this PICO_W does not know what time it is. )
and is stored as epoch and string in database
here now when i execute a SQL and get 60 or 1440 long array
i get the first [0] and last [1439] || [59] datetimes and show it in operation header
as the Trend X range only say Day Hour:Min ( ok try to change later )
ok, first time i run that long ( but no DC power over night )
-1- RPI BRIDGE python tool running
-2- RPI Node-Red SSD SQLite3 database 1040 records
my new UI HIST_TREND
+ has also the date picker: start date ++ 1440 (day trend )
+ a H-ome button ( jump to LAST 60 samples ( last hour ) )
+ trend X axis YY-MM-DD HH:mm
+ control structure
- my SQL ( for the buttons) still in work
also i like to support the UTC || LOCAL time problem,
now i store time ( in local RPI3 ) as UTC ( number and string NOW changed to YYYY-MM-DD HH-mm-ss format )
if i use in HIST TREND a function to show UTC and LOCAL ( of the shown data time range )
( and add the actual local time )
it might help to understand the TREND
BUT
if the SITE UTC record is send to REMOTE
you never again can recover the LOCAL time at SITE.. (MQTT not know where it is... )
what can make some communication to LOCALS difficult:
"WHAT YOU FUCKED UP AT 7.oo?"
should i send LOCAL TIME OFFSET ( that are minutes !! ) in the BRIDGE record?
REMOTE trend
in 1.1.2.b2 i updated the PICO W to directly name DC_Volt DC_Amp DC_Watt in mqtt
and needed that in REMOTE BRIDGE python tool also
and i changed add:
{ "id": 1, "minute": 36, "datetime": 1672724160, "datetimes": "2023_01_03_19_35_21", "AC_Volt": 233.000 , "AC_Amp": 0.051 , "AC_Watt": 3.000 , "PS_Temp": 29.480 , "DC_Volt": 4.016 , "DC_Amp": -0.000 , "DC_Watt": 0.000 }
where now
+ + datetime is UTC epoch
+ + datetimes is local time and as string YYYY_MM_DD_hh_mm_ss
so in remote location "HOME" can see SITE local time v.s. trending UTC time
now in the project zip is a Node-Red backup for 2 projects:
* PICO_W flows and
* PICO_W_REMOTE flow
for transport...
but for use it is much better to have it on 2 separate systems.. running parallel
( well one at SITE and one at HOME... )
( this NR code is just a temporary proof of concept )
i just have a RPI4 32 bit ( pinn not noobs ) 32GB USB stick for other tests
here i try Node-Red ( but no mosquitto broker needed )
with the REMOTE project ( to see the data from RPI3 BRIDGE via REMOTE broker HIVEMQ )
just finished i think, RPI4 as HOME station? why not, but actually
you work on SITE, install a data collection..., start a BRIDGE..
you should be able to test it before you fly HOME...
so let's try it on a win laptop? see here
try: node-v18.12.1-x64.msi
[yes] chocolatey
+ python 3.11.0
+ visual studio
+ build tools
some problems as windows not up to date??
from [cmd]
npm install -g --unsafe-perm node-red
node-red
laptop browser:
http://127.0.0.1:1880
manage palette install node-red dashboard !! hangs !!
but works after a reboot and retry!!
well in case you not want to wait days to see and check a TREND
i try a simulation, where node red writes every second a new minute value to a database in RamDisk: flows.json
now this HOME / CENTRAL / REMOTE HIST APP
has to grow
+ + for 2 SITE need the RPI3 BRIDGE send their combined data to a different TOPIC
+ + + but now create a new common top topic: / related to this projects data structure / ++ change python BRIDGE code /
PICOW-S31/Site1
PICOW-S31/Site2
now a mqtt_IN with a topic
PICOW-S31/#
will catch all and can save it to a database..
while i keep as much as possible same as for RPI3 ( LOCAL RPI )
that file will be
/home/pi/projects/PICO_W/data.sqlite3
but for if you run the Node-Red REMOTE app on a local RPI ( like for test )
MUST use a different table name ( from table: mqtt_in )
remote_mqtt_in
as this contains the AC data via BRIDGE ( also stored as REAL columns ) it has a different structure from local mqttt_in
the REMOTE CURRENT TREND for the second Site needs a copy of the first to a new TAB
and change the TOPIC ( now by a inject "subscribe to" node and dynamic subscription in 'mqtt_in' node )
again i got confused with the underlying dashboard TAB / Group structure..
and a naming conflict with all Groups and TAB for the new SITE
i did it, i keep it, but it is NOT a concept for MULTI SITE show.
but how to get a easy overview over multi site data?
ok, i try one easy TAB what show the last mqtt msg of the 2 sites..
and is with copy / paste / change site name / deploy in 10 sec
extendable for a new site ( in a new line ).
but when i tested in a simulation,
2 RPI sending BRIDGE data to REMOTE
the REMOTE APP only got ONE ( the second started BRIDGE )
( and the FIRST BRIDGE suddenly could not read from LOCAL broker )
i tried to reboot both but happened again.
when 2 broker hiccup like that it has to do with the client connection from the second started code,
here BRIDGE ( does not matter on same computer, different computer same LAN or different continents )
the latest client takes over the communication and kicks out the first one, IF IT HAS SAME NAME.
so just changing the TOPIC for 'SITE2' is not enough, also client-name must contain that UNIQUE site-name
so with
remote_mtopic = ''PICOW-S31"
remote_dtopic = "Site2"
used in topic AND client name our multi SITE network should work.
PICO_W 2
i ordered a second PICO_W ( pre-soldered headers / bread-board / USB cable ( nice beginner set ))
because there the same problem needs to be addressed like with the BRIDGE.
( second PICO would kick out first PICO from RPI3 LOCAL mosquitto broker .. )
also i want test further the MICRO concept,
where, for a smaller project, a PICO_W directly talks with the REMOTE broker ( 6$ telemetry )
( or should i buy a ESP32 C6 ? )
now with the needed test for a
+ + MULTI SITE REMOTE TREND
+ + MULTI Client LOCAL SITE
+ + test MICRO SITE ( PICO_W directly talk to REMOTE BROKER )
i ordered a second PICO_W but got delayed by communication problems / weekend /
( not CYTRON, local DHL confusion )
-A- unpack,
+ click PICO_W ( presoldered header ) into bread-board
+ click mikro USB into PICO
+ click USB into PC and see root drive
( only with new board, usually must press white button to get to that drive )
-B- drag and drop CP800b6 newest
adafruit-circuitpython-raspberry_pi_pico_w-en_US-20230114-691d6d8.uf2
see CIRCUITPY drive:
( and now already contains a ( empty) settings.toml file )
-C- drag and drop all needed new /lib/ from
adafruit-circuitpython-bundle-8.x-mpy-20230115.zip
-D1- copy test code :
my: settings.toml
pms1w.py
Mu-editor: by REPL >>> import pms1w
to test wifi login / webserver and OSCI code
so, the above copied adafruit_httpserver give again error
about the response . send . body see here
change:
#return HTTPResponse(content_type=MIMEType.TYPE_HTML, body=html_index_page() )
response = HTTPResponse(request)
with response:
response.send(body=html_index_page(), content_type=MIMEType.TYPE_HTML)
and work again!
change THAT in PMS1W and in WEB_WIFI file...
-D2- copy test all project code :
hm.. ok, it starts ( or from pms1w use [ctrl][c] [ctrl][d] )
but at boot have failure about NO INA on I2C Link
and at job3 code fails.
BUT
if you type [3][enter] ( in the first minute )
you disable JOB 3 and code can run incl mqtt ... but with 0 in DC_power data ( from INA )
so my second PICO_W is running ( more or less )
but i should have ordered also a second INA ( or i set this code for PICO_W I/O only ( in jobs, ina and mqtt)
so for test i start original S31 and PICO_W and watch BRIDGE data:
while S31 come in fast,
i wait 2 minutes until the OLD PICO_W send data ( replacing the new PICO_W 2 )
i could see it in the data-content and the id number start from 0
so need to solve the "same" client name problem!
but yes, it makes anyhow no sense, that the second PICO_W
- - send with same TOPIC!
- - and run on same IP
( so what i see is not very valid )
-C- start work:
in adafruit minimqtt example no client name is used.
# https://docs.circuitpython.org/projects/minimqtt/en/latest/api.html
# client_id (str) – Optional client identifier, defaults to a unique, generated string.
so we can first try again without:
--1+ client name local broker
--2+ client name remote broker
but in settings.toml and web_wifi.py
already prepare
# remote_mtopic = os.getenv('remote_mtopic') # THIS data scheme
# remote_dtopic = os.getenv('remote_dtopic') # THIS SITE
+3+ broker connect fail timer
now if the broker is down the code loops and try again, so never start measuring,
( this was a prior fix of to never try again if broker connection failed )
need some counter/timer..
like wait 10 sec, try, wait 10+10 sec, try
but there might be a other problem i can not fix...
i see a ERROR "out of sockets" after ? try
after tested OK run it in PICO_W 1 hardware setup
and nuke PICO_W 1
setup CP800b6+
copy new /lib/
copy settings.toml and edit
214 IP and topic
Site2 remote topic
copy all py files
and start edit:
ina.py line 10: useINA = False
jobs.py line 30: JOB3en = False
mqtt.py line 27: hereuse_INA = False
mqtt.py line 33: hereuse_PICOW_io = True
and run both to test mqtt stream of 2 clients to local broker
test in terminal with
mosquitto_sub -h localhost -u "u213S31" -P "p213S31" -v -t "#"
and see
S31/P01S31/STATE
S31/P01S31/SENSOR
P213/P01
P214/P01
that looks good
BUT there is NO LOCAL Node-Red config for this,
i want concentrate on a REMOTE Node-Red
MULTI SITE
and so we change the P214 PICO_W to a 6$ MICRO Telemetry system
by let it talk NOT with RPI3 broker,
instead with remote broker directly ( as SITE2 )
so like on an other island have WIFI,
but no money for 60$ RPI3 .. MINI Telemetry system.
( yes there the PICO_W might have IP 213 again, but not here inside my home LAN with both systems running)
well, actually i run that already but now can not connect??
Error: MQTT connect and send hello
negative number of bytes to read: -115
i might try that again with the old adafruit_minimqtt lib
or wait a day and try a newer one..
adafruit-circuitpython-bundle-8.x-mpy-20230117.zip
difficult to see but there seems to be a change between 15. and 17. version
adafruit-circuitpython-raspberry_pi_pico_w-en_US-20230118-1c1cf1c.uf2
ok NUKE it again ...
BAD LUCK, same, TLS connect to REMOTE broker OK
publish something get negative number of bytes to read
i rework the settins.toml and web_wifi.py
with a new use_REMOTE_broker = "y" switch and
to separate the login steps with 3 try except
now get
___+++ setup MQTT
___+++ setup MQTTclient
___++++ use TLS
___ Attempting to connect to xxx.hivemq.cloud
___ Connected to MQTT Broker!
___ Flags: 0 RC: 0
___ Publishing to PICOW-S31
Hello Broker: i am a PICO W
___ Published to PICOW-S31 with PID 0
___ Subscribing to PICOW-S31/Site2/set tuning
Error: MQTT subscribe tuning
negative number of bytes to read: -115
so the error is from the subscribe and then connection is BAD
.next.
disable that subscribe for test
and now get
Error: MQTT loop
negative number of bytes to read: -4294967284
......RESTART MQTT connection at sec: 9727.77
++ confirm, all same but replace /lib/minimqtt/
from adafruit-circuitpython-bundle-8.x-mpy-20221221.zip
works, so the error is from new minimqtt
now check if i see the data on REMOTE broker
i had a bad moment again, as i see ONLY the new Site2 ( PICO_W micro ) data,
but after restart the RPI BRIDGE i see both
PICOW-S31/Site1 { "id": 1, "minute": 15, "datetime": 1673993700, "datetimes": "2023_01_18_12_15_00", "AC_Volt": 236.000 , "AC_Amp": 0.053 , "AC_Watt": 4.000 , "PS_Temp": 27.140 , "DC_Volt": 1.584 , "DC_Amp": 0.527 , "DC_Watt": 0.836 }
PICOW-S31/Site2 { "id": 42, "dev":"P01", "Temp": 31.35, "DC_Volt": 0.017, "DC_Amp": 0.702, "DC_Watt": 0.012 }
PICOW-S31/Site1 { "id": 2, "minute": 16, "datetime": 1673993760, "datetimes": "2023_01_18_12_15_00", "AC_Volt": 237.000 , "AC_Amp": 0.037 , "AC_Watt": 4.000 , "PS_Temp": 27.140 , "DC_Volt": 1.584 , "DC_Amp": 0.527 , "DC_Watt": 0.836 }
PICOW-S31/Site2 { "id": 43, "dev":"P01", "Temp": 31.35, "DC_Volt": 0.017, "DC_Amp": 0.785, "DC_Watt": 0.013 }
now with one switch in 'settings.toml' i change BROKER / TLS and TOPIC,
but the data structure still very different from BRIDGE ( as no AC data... )
AND PICO_W not have a timestamp!!!
that i have to clean up before i can think of a
REMOTE database for MULTI SITE and MINI / MICRO Telemetry mix
in mqtt.py also use that switch and make a new string, send as Site2
pi@RPI3:~/projects/PICO_W $ mqttsubR
PICOW-S31/Site2 { "id": 2, "minute": 0, "datetime": 0, "datetimes": "0", "AC_Volt": 0.0, "AC_Amp": 0.0, "AC_Watt": 0.0, "PS_Temp": 32.29, "DC_Volt": 0.017, "DC_Amp": 0.392, "DC_Watt": 0.007 }
PICOW-S31/Site1 { "id": 104, "minute": 58, "datetime": 1673999880, "datetimes": "2023_01_18_13_18_52", "AC_Volt": 212.000 , "AC_Amp": 0.054 , "AC_Watt": 4.000 , "PS_Temp": 30.880 , "DC_Volt": 1.584 , "DC_Amp": 0.527 , "DC_Watt": 0.836 }
hardware setup without the RPI 3 and 4
also the new python bridge code on RPI4 started as 'Site3' show up good
even the timeline is wrong it better fits here:
after finish next JOB about REMOTE TREND...
i use that PICO_W code
---- still INA off as i work on second breadboard
---- still /lib/ hack about NOT use that new minimqtt
still TLS MQTT probelm so use /lib/
from adafruit-circuitpython-bundle-8.x-mpy-20221221.zip
instead
from adafruit-circuitpython-bundle-8.x-mpy-20230122.zip
with adafruit-circuitpython-raspberry_pi_pico_w-en_US-20230120-9c06682.uf2
+++ change back to 213 ( IP and TOPIC )
+++ change back to LOCAL broker
+ topic structure now use Site and device ( even if local )
JOB5 MQTT make json every 60 sec
rec { "id": 25, "dev":"Site1/P213", "PS_Temp": 32.76, "DC_Volt": 1.031, "DC_Amp": 3.300, "DC_Watt": 3.402 } filtered: True Va 0.05, Vb 0.95, Ia 0.05, Ib 0.95, Wa 0.05, Wb 0.95
___ Published to PICOW-S31/Site1/P213 with PID 1
for some strategic changes in the PICO_W project code.
+ erase menu.py all now inside jobs.py
+ JOB0 rename JOBSYS
+ add JOB0 as a 1/2 sec PICO_W DIO job
+ + add DO1 on GP1 ( test use a ext LED on 3v3 use inverted output)
+ + add PB2 on GP2 ( connect to GND input pull high )
+ + LOCAL OPERATION: PB press ( for 0.5 sec ) >> toggle DO1 ( ext LED or RELAY )
+ + rework all .py to get switches from settings.toml
+ PYTHON Bridge
+ + get .env new TOPIC to see data from PICOW-S31/Site1/P213
+ + read 'PS_Temp' instead 'Temp'
in v1.2.3.b5
+ + BRIDGE send OLD data after device goes OFFLINE, confusing:
reset the analog data to -0.007 after REMOTE mqtt send
to indicate that the device ( PICO W or S31 ) did not send data in the last minute
not very good, but helps loop-check from REMOTE ( like: BRIDGE OK, S31 OK, PICO_W OFF or lost contact local broker )
+ Node-Red
+ + STEP1: rework LOCAL Node-Red for this changes ( just to get it working again )
+ + + topic PICOW-S31/Site1/P213 and PICOW-S31/Site1/P213/set for tuning
+ + + PS_Temp renaming and new column in db
+ operation DO1 from PICO_W website ( and some styling )
very funny, yesterday it worked, today see:
wifi served dynamic data.html
....POST /data HTTP/1.1
Host: 192.168.1.213
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:109.0) Gecko/20100101 Firefox/109.0
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,*/*;q=0.8
Accept-Language: en-US,en;q=0.5
Accept-Encoding: gzip, deflate
Content-Type: application/x-www-form-urlencoded
Content-Length: 11
Origin: http://192.168.1.213
Connection: keep-alive
Referer: http://192.168.1.213/data
Upgrade-Insecure-Requests: 1
LED+OFF=OFF
+ operation DO1 from MQTT
+ report PB2 to MQTT
+ + STEP2: rework LOCAL Node-Red
+++ with local project database MULTI PS: ( P213 P214 ..)
+++ LOCAL hist trend now can select PS ( via topic from database )
+++ save PICOW-S31/Site1/# to db ! all PS aka P213 P214...
OPEN:
LOCAL P214 (+- S31 ) via BRIDGE to REMOTE
S31 not included in multi PS thinking...
- - - up to now the S31 not stored LOCAL
- - - NR P214 S31 not configured
- - - also multiple S31 must do again via BRIDGE ...
REMOTE trend MULTI SITE
REMOTE show now Site1 Site2 and a page with the last MQTT msg from both sites
the PICO_W to REMOTE ( MICRO Site2 )
and a peek into the database ( of RPI4 )
also ( not exactly needed ) add a new database table and UI list of it
about projects ( and their used TOPIC )
now also with a complete database management ADD EDIT DELETE ( CreateReadUpdateDelete )
the EDIT not need a button: as after you select a record [UP][DOWN],
the 3 lines for NAME , TOPIC , REMARK are INPUT windows,
just change there and press [ENTER], the list will be refreshed.
( or press [DEL] to kill it / the ID stays empty as SQL style )
if need a new record press [ADD], get a new default record you can now select and edit / overwrite default content
it works, even sometimes not see it in the list ( internal scroll problem / slider ) so might do a browser screen update
as with the CP /lib/ problems the release might take some time,
a extra Node-Red Dashboard SQLite db CRUD sample code
here looks like
and for start with HIST_TREND
use the projects list to build a UI selector what give the 'selected' TOPIC needed to make the SQL
to retrieve the HIST records for ONE SITE.
( the select options are handed in by msg.options )
so, today 21.1.2023 is the day for REMOTE TREND
the REMOTE HIST TREND gets a copy of the LOCAL HIST TREND
( esp with the MENU [-d] [-h] [d_date] [h_ome] [+h] [+d] functionality )
in Flow5 i have the SITE SELECTOR
here i import Flow3 from above LOCAL TREND
and the detail work start:
-a- UI TAB and group names
-b- database
file same: /home/pi/projects/PICO_W/data.sqlite3
table name: remote_mqtt_in
-c- the LIST
( UI table one hour NO TOPIC SELECT ) now for 2 sites shows 120 records,
possible i keep it that way ( same like you check on the database file with the linux tools )
-d- the TREND
here still without using the Site / Topic selector shows DC_Volt DC_Amp DC_Watt
( and works directly as these names now same in local and remote database )
but mixes both Sites, so this TREND is not usable:
now i store the selected TOPIC to a FLOW Variable ( with a startup init on Site1 )
and read it in the SQL function for the TREND
var getSite = flow.get('getSite');
msg.topic = "SELECT * FROM remote_mqtt_in WHERE topic = \""+getSite+"\" AND timedate BETWEEN " + hstart + " AND " + hend + " ;"
that's it:
CP800 out of beta?
when i check 30.1.2023 on CP re-visioning find
a fluid situation, missed the RC0, see already RC1 available, but not in the download page.
so i now have to test:
adafruit-circuitpython-raspberry_pi_pico-en_US-8.0.0-rc.1.uf2
adafruit-circuitpython-raspberry_pi_pico_w-en_US-8.0.0-rc.1.uf2
adafruit-circuitpython-bundle-8.x-mpy-20230129.zip
( as expected the 'minimqtt' lib problem with TLS broker still same problem )
but up to now no issues or change requirements.
system OVVW
PICO_W circuit: INA + reset + DO1 ( ext LED ) + PB2
component view
functional view
code
use the adafruit-circuitpython-bundle-8.x-mpy-20221221.zip /lib/ and CP800b6 daily
but still same problem minimqtt lib, git when talk to a remote broker with TLS
same with new CP800RC1
learned something about Node-Red
while my Flow ( select all nodes ) export download rename LOCAL_Flow1_flows.json
is good for incremental update..
for a new setup there are some things missing, mainly about the UI / dashboard setup
* dark custom mode.. ( named UI_base node )
* TABs
* groups
so a complete copy of the /.node-red/projects/PICO_W/flow.json is needed
( OR i find a way to make that settings a extra file? they can not be selected on editor? )
ok, import the flow.json into a 'new project',
delete all nodes of al FlowX, deploy
and copy file flow.json ( of 'new project' ) back
it contains UI_base, UI_tab, UI_group, UI_spacer...
but also SQL db & MQTT broker (but not the passwords )
update:
7.2.2023 see beta / RC ... over now, we have the real CP800
download:
adafruit-circuitpython-raspberry_pi_pico_w-en_US-8.0.0.uf2
adafruit-circuitpython-bundle-8.x-mpy-20230206.zip
code rev 1.2.3
so we now have re-tested the setup of RPI3 and download and install THE project on it ( compared to we used the ZIP for update )
and see a view pitfalls...
! it is a big project
-a- setup and code for
+++ PICO W
+++ S31
-b- Mosquitto setup
-c- Node-Red setup ( with projects ) and import flows.json
-d- python setup: BRIDGE code and autostart
best is use all same
+ (install) PATH RPI
/home/pi/projects/PICO_W/( * from ZIP )
+ device IP's
RPI3: 192.168.1.103
PICOW: 192.168.1.213
+ passwords and TOPICS
( LOCAL and < YOUR- >REMOTE broker )
until you know where in the nested project what is used.
work
that will be in next revision:
for beauty use the Node-Red grouping of nodes ( and color mark the groups )
there was a very ugly problem in the CRUD app for the LOCAL PS LIST
when you press UP 5000 times you see only record '[]'
must press down 5000 times ( blindly ) until see a valid record again
now i save the max ID while i generate the table list ( as a result of start webpage or edit refresh )
into a FLOW variable
and read it when do the [UP] [DOWN] buttons to limit the [UP]s to last record
( pls read about global / flow / context variables of NR here and here)
and a other problem occurred, looks like a build in UNZIP of WIN 11
does not like my very informative project file name ( as ? TOO LONG? inside path of the PICO_W code )
so i will rename from
/PICO_W_CP800_Multi_Task_DIO_Ains_Temp_DHT_INA_ADS_USB_MENU_dynamicWebserver_MQTT_PMS1WdotPY_V1.2.3/
to
PICO_W_V123b(.zip)
( well, win10 PC Total Commander and RPI Midnight Commander / or better their build in zip tools /
never had problems like that, and i have no use for other, OS dependent, tools )
also see there is a CP802 out:
adafruit-circuitpython-raspberry_pi_pico_w-en_US-8.0.2.uf2
adafruit-circuitpython-bundle-8.x-mpy-20230216.zip
let's test it, looks OK
ok, looks like i got some help,
after long wait now there is some digging going on into the
minimqtt adafruit lib
and the 'TLS' use to HIVEMQ
( when i want to use the PICO_W not talk to a LOCAL RPI mosquitto broker,
but to a online account at HIVEMQ using TLS / 8883
+ + the so called MICRO / 6$ telemetry / concept )
( the original lib from 12/2022 is OK, newer get ERROR like -115 bytes at subscribe )
GIT ISSUE here
i tested the adafruit_NTP lib and build it in this project
-1- after login WIFI call network time server NTP and save to RTC
-2- show it in the webserver
-3- show it in mqtt as "datetimes"
-4- pick it in BRIDGE from mqtt and send to REMOTE
project INA 50A
on special request:
now while above work is ongoing, we want dig into this
INA 219 with a add SHUNT
to increase the current measuring range to 50A
better make like a 'git fork' we can merge later
to not confuse the other tests we use:
* a extra PICO_W setup .215 P215
* INA219 we start with default internal shunt 0.1 ohm
we use the original lib, but not adafruit_ina219.mpy file, the source
adafruit_ina219.py
and start editing it for use of range tuning variables from settings.toml ? if possible?
* a external SHUNT ( parallel to the internal if possible ) like type: 50A 75mV are commonly used,
we should also look for a cheap manganat shunt what the industry uses for devices measuring current
( we need to calibrate it anyway.. )
* we use a PS 50A
* and a LOAD for this : 0.5 .. 1kW resistors / 1 OHM / 3 or 4 parallel
we call it
PICO_W_V123d_INA50_V01(.zip)
work littlebit on the INA init first
at the end of the lib
adafruit_ina219.py
i find prepared functions:
def set_calibration_32V_2A
def set_calibration_32V_1A
def set_calibration_16V_400mA
def set_calibration_16V_5A !!! 0.02 ohm shunt
i start making a new one
def set_calibration_32V_50A !!! 0.0015 ohm ( 75mV 50 A ) shunt
there is a lot of talk about using this ( measuring ) shunt type and range with INA,
but actually not find a clear answer ( add mostly use Arduino code... )
also i think there is no need to desolder the existing shunt ( as suggested on some BLOG )
-a- internal shunt 0.1 ohm +- 1% ?
-b- external shunt 0.00150 ohm ( 0.075 / 50 )
if we not desolder internal shunt we have:
-a- parallel -b- = a * b / ( a + b ) = 0.00148 ohm ( so for delta 0.00002 ohm i not destroy a INA )
if we not succeed we will go for ADS1115 + ACS758 ( HALL sensor ) + V-resistor-divider
