Edge Collective

P2P / DIY Server

P2P Farm server prototyping

Repo is here.

A useful associted 'PUT' script is here

ESP32 --> Server

Adding headers to esp32 explanation here

Making a PUT request here.

Using ArduinoJSON with HTTPClient here.

Creating nested json objects in ArduinoJSON here.

Work Log

21 OCT 2020

Fixed sqlite3 missing module.

Got an ESP32 to post data via JSON, here.

23 OCT 2020

Trying to use this fastify basic auth module as part of the codebase.

... implemented!

24 OCT 2020

Some next steps:

25 OCT 2020

Making a python PUT script.

Reference on python requests here.

Setting the authorization header in curl here.

Issue: when using basic auth in current basicAuth branch, it is putting the username password authentication method on top of every request -- need to do it just for the home page.

Guide to using basic auth here.

Combining fastify static with fastify basic auth here.

Using multiple authentication options with fastify here

addhook onresponse by route here

fastify and mongodb here

Might just need to find a way to serve up web page from directly in fastify, using basicAuthFix branch

Nice getting started with fastify document.

Currently the action is in 'main.js' ...

Looks like there is a way to addHook to specific routes here

latest idea: keep basicAuth branch as-is, but use multiple authentication methods in 'PUT' script

'ctrl shift i' will insert timestamp in VS Code given installation of an extension here

Adding multiple authorization headers via postman here

Combining multiple authorizations via curl here

Setting multiple headers in curl -- useful! -- here

issues:

  1. can't use multiple authorization schemes, in particular not if one of them is Basic Auth
  2. not sure how to combine basic auth with fastify-static

interesting angle mentioned in "Transform response of fastify-static served files", which led to fastify point-of-view templates idea.

Ah -- I think I've got it working with the basicAuth branch, now!

2020-11-29 08:51:40

Adding authentication to NodeJS, here

Using Auth0 and Fastify here -- ah, blocked --

But there's a repo, here.

2020-11-29 16:43:16

Jupyter dashboard via voila -- gallery of examples

gesis notebooks, here

guide to voila, here

Dashboarding options in Jupyter discussed here

User authentication with nodejs and passport here

Rolling your own authentication, here.

React and Auth0 ...

2020-12-05 14:21:37

Node + Express + Postgresql

react + node + express + postgresql -- short tutorial

react + node + express + postgresql -- thorough tutorial

express + postgresql -- from node guide

Creating a web application in your home with a Raspberry Pi, Express and PostgreSQL -- this looks like a great place to begin!

Getting started with authentication in Node.js with Passport and PostgresQL

Sequelize -- JSON that creates a database table

suggestion to use multi-row inserts to save time

designing a restful api with node and postgres

ahhh -- part of a great series that seems to do all the things! including graphing! -- the series is here

using mqtt vs REST

2020-12-05 20:47:42

following tutorial here, putting in Documents/able

sudo apt-get install postgresql

psuser psps

sensor2=# INSERT INTO users (username, password) VALUES ('user1', 'bubbles');

INSERT INTO sensors (name) values ('NodeMCU ESP32');

2020-12-15 08:53:18

Building a simple API with Nodejs, Expressjs, PostgreSQL DB, and JWT - 3

node + postgresql -- nice tutorial

node + postgresql + jwt

2020-12-15 15:12:26

Begin here:

then add security here:

Update: actually, begin with second tutorial ...

Installing postgresql on ubuntu here

React + node + express + mongo w/ login page here

2020-12-15 15:53:43

Okay, here's the real deal:

Node.js Express & PostgreSQL: CRUD Rest APIs example with Sequelize

Prior exapmel:

node, express, postgresql using sequelize here

Wow, really simple example here

postgres and ubuntu tutorial here

2020-12-15 17:12:48

nodejs express mongodb jwt here

2020-12-16 11:26:24

as another approach w/ node + postgres, trying out this tutorial

Nice intro tutorial on nodejs and postgres here

try out the github code here

worked.

New homework! This is the thing to try:

"creating a web application in your home using nodes, rpi, express, postgresql" -- here

NOTE: 'createdb -0 username dbname' should have -O instead of -0

Associated github repo is here

Grand. And then can add users / JWT with something like this post

The first post for that series is here

Full code for the latter is here

And perhaps this shows how to combine the above with a front end -- here

Good example!

The following pairing of backend and frontend seems to work as a baseline structure. Running both, they even find one another on the proper ports.

Backend w/ node + express + postgresql + JWT

Tutorial here with associated code on github here

Frontend

React front end here, with github repo here

Node + Express + Postgres + Sequelize

Tutorial here. Nice explanation.

Another nice tutorial here, with associated code on github here

Ahhhh, really nice tutorial on using sequelize, here

2020-12-16 13:31:56

using quasar, vue, and chartjs here

Key links

https://scotch.io/tutorials/getting-started-with-node-express-and-postgres-using-sequelize#toc-sequelize-setup

https://bezkoder.com/node-js-jwt-authentication-postgresql/

https://www.codementor.io/@olawalealadeusi896/building-a-simple-api-with-nodejs-expressjs-and-postgresql-db-masuu56t7

2020-12-17 08:04:23

arg for postgresql for iot here

(HICCUP IN NOTES FLOW)

2020-12-15 17:31:58

make your own iot data platform

mern stack here

mean stack jwt here

MERN full stack here

ooooooh this is good MERN! here

another good example here

this worked!

https://github.com/vishalnagda1/mern-jwt-auth

broader tutorial on MERN here

MERN CRUD app tutorial here

this looks like a nice example, using postgres -- here

MERN:

okay so this tutorial lets you create student records and view them -- here

whereas this tutorial does user login stuff.

2020-12-15 20:59:54

this pair of server (node + mongo + jwt) + client (angular) worked:

reading about fastify here

2020-12-16 09:25:10

this might be my starting place -- node express postgresql ubuntu here

also see post here

doing this in ~/Documents/postgres-express-react-node-tutorial

notes on postgres and user auth here

used this technique to change postgres password to 'i8buddha': linked here

sequelize import is not a function' -- https://stackoverflow.com/questions/62917111/sequelize-import-is-not-a-function

associated github repo is here

2020-12-16 10:16:16

In the end, a nice codebase for postgres express and node is here. Note that this blog post is based on an earlier blog post by Michael Herman, here.

with the github repo here

used this technique to change postgres password to 'i8buddha': linked here

which needed to be changed in server/config/config.json as per:

{ "development": { "username": "postgres", "password": "i8buddha", "database": "todos-dev", "host": "127.0.0.1", "port": 5432, "dialect": "postgres" }, "test": { "username": "postgres", "password": "i8buddha", "database": "todos-test", "host": "127.0.0.1", "port": 5432, "dialect": "postgres" } }

this was the only change needed in the github repo above.

clone, then install with npm install, and run with npm run start:dev

result: working todo api w/ sequelize.

2020-12-16 11:26:24

as another approach w/ node + postgres, trying out this tutorial

Nice intro tutorial on nodejs and postgres here

try out the github code here

worked.

New homework! This is the thing to try:

"creating a web application in your home using nodes, rpi, express, postgresql" -- here

NOTE: 'createdb -0 username dbname' should have -O instead of -0

Associated github repo is here

Grand. And then can add users / JWT with something like this post

The first post for that series is here

Full code for the latter is here

And perhaps this shows how to combine the above with a front end -- here

Good example!

The following pairing of backend and frontend seems to work as a baseline structure. Running both, they even find one another on the proper ports.

Backend w/ node + express + postgresql + JWT

Tutorial here with associated code on github here

Frontend

React front end here, with github repo here

Node + Express + Postgres + Sequelize

Tutorial here. Nice explanation.

Another nice tutorial here, with associated code on github here

Ahhhh, really nice tutorial on using sequelize, here

2020-12-16 13:02:19

ingesting real time data w/ nodejs and timescale db here

rike in berlin here

getting a csv download from nodejs here

rike -- project -- maybe she'd help with corona -- here

multiple users and reflections here

this is a meaty post here

Key links

https://scotch.io/tutorials/getting-started-with-node-express-and-postgres-using-sequelize#toc-sequelize-setup

https://bezkoder.com/node-js-jwt-authentication-postgresql/

https://www.codementor.io/@olawalealadeusi896/building-a-simple-api-with-nodejs-expressjs-and-postgresql-db-masuu56t7

this adds content to the node + express + postgres + sequelize here w/ associated github repo here

follow this first!
and see if you can make it appropriate for sensors ...

and maybe if you can do a front end for it ... looks like they do have a front end in React example at the end here -- w/ associated github repo here

NOTE: have changed default port to something other than 8080, should go back to that ...

so, plan of attack:

  1. return to that .bio tutorial on sensors, and see how they set up the tables. use that as an inspiration for how to create the postgresql table structure.
  2. figure out best next thing to implement above; 'todo' or 'tutorials' examples; with or without jwt
  3. add jwt + users using the other tutorial.

postgres tutorial here

sudo -i -u postgres

psql

\q

2020-12-16 21:08:14

next steps:

latest running code experimented with was here.

going to experiment with the '.bio' code asap.

.bio tutorial

creating new tables

CREATE TABLE users ( user_id SERIAL PRIMARY KEY, username VARCHAR(50), password VARCHAR(255), is_admin BOOLEAN DEFAULT FALSE );

CREATE TABLE feeds( feed_id SERIAL PRIMARY KEY, name VARCHAR(255), private_key VARCHAR(255) );

CREATE TABLE measurements( id SERIAL PRIMARY KEY, feed_id INT, celcius FLOAT, humidity FLOAT, created TIMESTAMP DEFAULT NOW(), CONSTRAINT feed FOREIGN KEY(feed_id) REFERENCES feeds(feed_id) );

INSERT INTO users (username, password) VALUES ('bob', 'jones');

INSERT INTO feeds (name) values ('NodeMCU ESP32');

checking string status here

2020-12-17 20:03:39

newfeed branch is here

going to try for csv next using tutorial here

another tutorial on exporting csv w/ node and express here

nice bezcoder tutorial here

2020-12-17 22:09:41

'heltec' is the latest branch. accepts data from a heltec; includes firmware.

Server code is here; firmware is in 'firmware' directory.

2020-12-18 16:23:00

ah, interesting code snippet:

// https://github.com/auth0/express-jwt // Secure "protected" endpoints with JWT middleware app.use('/protected', jwtMiddleware({ secret: SECRET_TOKEN, // Use the same token that we used to sign the JWT above // Let's allow our clients to provide the token in a variety of ways getToken: function (req) { if (req.headers.authorization && req.headers.authorization.split(' ')[0] === 'Bearer') { // Authorization: Bearer g1jipjgi1ifjioj // Handle token presented as a Bearer token in the Authorization header return req.headers.authorization.split(' ')[1]; } else if (req.query && req.query.token) { // Handle token presented as URI param return req.query.token; } else if (req.cookies && req.cookies.token) { // Handle token presented as a cookie parameter return req.cookies.token; } // If we return null, we couldn't find a token. // In this case, the JWT middleware will return a 401 (unauthorized) to the client for this request return null; } }));

// A simple protected route for demo purposes app.get('/protected/data', function (req, res) { console.log(req.user); // => { _id: <S

2020-12-18 16:26:03

Secure Rest api in nodejs

using OAuth

node api key

api keys here

nice tutorial on development of the stack here

very nice tutorial on postgres + express dev here

basic passport api key here

passport and nestjs integration here

this looks like a good strategy here

nice strategy for token authentication and whatnot here

--

bezcode node + postgresql + express + react here

2020-12-19 11:35:42

inserting html form data into postgresql using nodejs here

2021-01-08 15:34:05

capturing form data with nodejs and express here

working with forms in express here

Forms example #1

even better version, with working code, here

github repo here

2021-01-08 15:57:43

This is the code to use and modify for habitat, here, when trying to get forms up and running.

Next step is to get habitat up and running -- the 'heltec' code -- which will likely require reconnecting / reconfiguring the postgres database ...

2021-01-08 19:32:35

Forms exmaple with SQLITE3

Read HTML Form Data Using GET and POST Method in Node.js

forking their example here

issue with sqlite3 -- covered here

seems that this approach worked, i.e.:

npm install -g node-pre-gyp npm install sqlite3 --save-dev

2021-01-09 16:40:05

Capturing which button was pressed in form submit here

Got basic forms working in the 'form' branch:

https://github.com/edgecollective/habitat/tree/form

Going to try templates next. Also:

understandin gnodejs express templating engines

2021-01-09 17:44:55

Got basic templating working: https://github.com/edgecollective/habitat/tree/template

TODOS:

Going to need to figure out how to create a new table, and new fields. Follow tutorial by Trickett (habitat creator) here

2021-01-10 10:57:12

setting up postgresql server ... creating tables ...

then follow instructions here

sudo -i -u postgres psql

can exit via \q

CREATE TABLE users ( user_id SERIAL PRIMARY KEY, username VARCHAR(50), password VARCHAR(255), is_admin BOOLEAN DEFAULT FALSE );

CREATE TABLE feeds( feed_id SERIAL PRIMARY KEY, name VARCHAR(255), public_key VARCHAR(255), private_key VARCHAR(255) );

CREATE TABLE measurements( id SERIAL PRIMARY KEY, feed_id INT, co2 FLOAT, tempC FLOAT, humidity FLOAT, mic FLOAT, auxPressure FLOAT, auxTempC FLOAT, aux001 FLOAT, aux002 FLOAT,
created TIMESTAMP DEFAULT NOW(), CONSTRAINT feed FOREIGN KEY(feed_id) REFERENCES feeds(feed_id) );

INSERT INTO users (username, password) VALUES ('bob', 'jones');

INSERT INTO feeds (name) values ('NodeMCU ESP32');

pcat999

how to handle: relation does not exist -- here

2021-01-10 12:09:36

update: relation didn't exist b/c hadn't created the tables in the proper database.

PORT=3000 DB_NAME=hab1 DB_USER=postgres DB_PASSWORD=pcat999 DB_HOST=localhost DB_PORT=5432

2021-01-10 12:30:59

Created a 'feed creation' landing page in the 'createfeed-landing' branch of habitat on github.com/edgecollective

2021-01-10 12:48:01

using serial primary keys postgresql here

postgresql random primary key here

unique identifier in your postgresql database here

advice here is not to use the public key as the unique identifier -- here

2021-01-11 11:06:05

error handling in express here

TODO: refactor things based on the suggestions in that post.

in particular:

app.get('/user/:id', async function (req, res, next) {
  var user = await getUserById(req.params.id)
  res.send(user)
})

2021-01-11 11:16:08

notes on api keys:

nice article here

carto's policy here

maybe should now have authorization key in the header. should just be a matter of checking on the "POST" ... getting a header. can implement it after this round.

2021-01-11 11:25:41

changing name of table in postgres

ALTER TABLE table_name 
RENAME COLUMN column_name TO new_column_name;

tests:

2021-01-11 12:23:53

writing an async function here

guide to async programming here

2021-01-11 12:32:41

'use-pubkey' is the latest branch.

using 'async await' in express here

and: definition of 'async / await' here

w/ postgresql here

started to use promises ...

even better now would be to create a 'getFeedPrivateKey() function and use that in postNewMeasurement()

2021-01-11 17:13:28

have now started some 'async' code in branch 'async'. that's the latest working branch.

TODO: the 'new feed' api should return JSON; the page we land on after creating a new feed in the form should leverage that api.

2021-01-11 17:25:43

beginner's guide to pug here

cami -- simon's kid

2021-01-12 19:02:12

instructions for setting up postgres for habitat on digitalocean

install postgres

sudo apt update
sudo apt install postgresql postgresql-contrib

sudo -i -u postgres createdb bay1 psql \c bay1

then:

CREATE TABLE users ( user_id SERIAL PRIMARY KEY, username VARCHAR(50), password VARCHAR(255), is_admin BOOLEAN DEFAULT FALSE );

CREATE TABLE feeds( feed_id SERIAL PRIMARY KEY, name VARCHAR(255), public_key VARCHAR(255), private_key VARCHAR(255) );

CREATE TABLE measurements( id SERIAL PRIMARY KEY, feed_id INT, co2 FLOAT, tempC FLOAT, humidity FLOAT, mic FLOAT, auxPressure FLOAT, auxTempC FLOAT, aux001 FLOAT, aux002 FLOAT,
created TIMESTAMP DEFAULT NOW(), CONSTRAINT feed FOREIGN KEY(feed_id) REFERENCES feeds(feed_id) );

check that all is well:

\dt

change the user password:

ALTER USER postgres WITH PASSWORD 'pcat999'

\q exit

see better guide here

need to make sure .env file is proper:

PORT=3000 DB_NAME=hab1 DB_USER=postgres DB_PASSWORD=pcat999 DB_HOST=localhost DB_PORT=5432

key guide here for deploying nodejs server on digitalocean.

has section for adding additional apps on other ports!

setting up nginx on digitalocean guide here

how to set up a firewall on ubuntu here

2021-01-13 08:05:11

Its creation was motivated by recent work (e.g. by Jimenez et al.) indicating that monitoring indoor CO2 levels can (in some contexts) help to assess the risk of airborne SARS-CoV-2 transmission.

2021-01-13 11:59:26

html to jade thing here

2021-01-13 19:20:23

Interesting way to do a drop-down menu via Pug, here:

div(class="choices") h2 p I'm voting for: select(class="form-control") option 1 option 2 option 3 option 4 option 5

2021-01-14 08:49:52

good example of chart setup here: view-source:https://www.chartjs.org/samples/latest/charts/line/basic.html

Reference for p2p-farm-server chartjs setup here

perhaps-useful chartjs formatting here

2021-01-21 11:20:38

Structure for a basic todo list app here: https://codeburst.io/creating-a-todo-app-with-node-js-express-and-postgresql-database-6dbf80c3fe9c

Associated code on github here: https://github.com/chisom5/nodejs-todoList

Idea:

Idea: add nodemon for auto restart of bayou while developing

Using React-like library 'Riot' here: https://hub.packtpub.com/simple-todo-list-web-application-nodejs-express-and-riot/, with associated code here: https://github.com/revington/riot-express-todo-list}

Drawing a grid on a canvas here https://codereview.stackexchange.com/questions/114702/drawing-a-grid-on-canvas

postgres tutorial again here: https://able.bio/rhett/creating-a-web-application-in-your-home-with-a-raspberry-pi-express-and-postgresql--3c90a372

2021-01-21 11:38:40

Simpler todo app approach here -- these are the elements I think I need -- https://medium.com/@atingenkay/creating-a-todo-app-with-node-js-express-8fa51f39b16f

And on github: https://github.com/missating/nodejs-todo

Okay -- TODO: redo that last repo, using pug. Then create feedmaps -- the equiv of bayou but by pulling in new feeds from various sources. depending on the type of feed, can have various functionality. so, add a 'feed type' variable -- e.g. bayou-co2 is one such type.

2021-01-21 12:10:58

Iteration in pug here: https://pugjs.org/language/iteration.html

2021-01-21 12:42:26

feed-map can now display a simple list of feeds here: https://github.com/edgecollective/feed-map/commit/4d3a522ba01f0bae15721fe0420100dcf76fd1a8

2021-01-21 17:22:56

Creating dynamic ides in a loop with pug here https://www.dev-tips-and-tricks.com/create-dynamic-ids-in-a-loop-with-pug-jade

Update: this commit shows the basic functionality!

https://github.com/edgecollective/feed-map/commit/765a9eec9b101e0e507075ec3b0e6aae8b9fec3b

Added nicer formatting:

Associated git commit is here: https://github.com/edgecollective/feed-map/commit/6f449c02024013035f1d42cd264505fb0066018e

2021-01-21 18:28:59

Nice reference for doing the map, via p2p-server code, here:

https://gitlab.com/dwblair/p2p-farm-server/-/blob/mappin/public/console.html

2021-01-21 18:39:13

Got image map working! at this commit -- https://github.com/edgecollective/feed-map/commit/72e90916c5bf5b21d748a5e4e54451f93a25c654

2021-01-21 18:46:53

Got markers working! commit -- https://github.com/edgecollective/feed-map/commit/2e4281fc405246c231ce817b35ee4f99f7d9b80c

2021-01-21 19:01:20

More formatting tweaks -- https://github.com/edgecollective/feed-map/commit/2b81f2f1154ddf1e6ffa11d2d5cd1b8ecb0260ce

2021-01-22 11:16:31

Created a 'checkbox' functionality, and also have charts graphed separately, in this commit and branch: https://github.com/edgecollective/feed-map/commit/e75252d43b287c398a6e82fa02e1599dfe307711

2021-01-22 14:14:58

Note: this is the nodejs-todo app I built feedmap upon, which has a nice 'delete note' functionality: https://github.com/missating/nodejs-todo

This code might be useful re: deleting items in postgres: https://codeburst.io/creating-a-todo-app-with-node-js-express-and-postgresql-database-6dbf80c3fe9c

Here's a nice guide on using knex -- https://medium.com/@tobie.tsuzuki/getting-started-with-node-js-express-and-knex-5640f595df98

Interesting post on sequelize vs knex vs objection.js here: https://stackoverflow.com/questions/56028287/how-is-node-js-knex-similar-different-to-sequelize

And an interesting link here: https://www.jakso.me/blog/objection-to-orm-hatred -- looks like objection.js might be a great idea.

2021-01-22 14:31:57

Overlay line on chart.js https://stackoverflow.com/questions/28076525/overlay-line-on-chart-js-graph

2021-01-22 14:43:27

Good example of chartjs datasets: https://tobiasahlin.com/blog/chartjs-charts-to-get-you-started/#2-line-chart

2021-01-22 18:34:46

Now plotting multiple co2 plots! at this commit: https://github.com/edgecollective/feed-map/commit/91adae48079a21fcf9fcc286ccd85a7ac2a68e91

2021-01-22 18:42:23

Hosting via google drive here: https://stackoverflow.com/questions/10311092/displaying-files-e-g-images-stored-in-google-drive-on-a-website

2021-01-22 18:56:25

Add asylum map:

git commit: https://github.com/edgecollective/feed-map/commit/fce79dc266a8ee5696a7da75c2a7177722128f80

2021-01-23 10:15:25

Revisiting my own documentation / repo for a minimal node + sqlite endpoint here https://github.com/edgecollective/nodejs-sqlite-endpoint

2021-01-23 15:38:16

Good tutorial for sqlite3: https://www.sqlitetutorial.net/sqlite-nodejs/query/

2021-01-23 15:52:07

Using sqlite3 with async await here: https://www.scriptol.com/sql/sqlite-async-await.php

2021-01-23 16:12:29

Switching back to postgres:

Installing postgres:

sudo apt update
sudo apt install postgresql postgresql-contrib

Then:

sudo -i -u postgres
createdb feedmap1;
psql feedmap1

then:

CREATE TABLE feedmaps(
    feedmap_id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    public_key VARCHAR(255),
    private_key VARCHAR(255),
    map_url VARCHAR(255)
);

CREATE TABLE feeds(
    id SERIAL PRIMARY KEY,
    feedmap_id INT,
    feed_base_url VARCHAR(255),
    feed_public_key VARCHAR(255),
    added TIMESTAMP DEFAULT NOW(),
    CONSTRAINT feedmap
        FOREIGN KEY(feedmap_id)
            REFERENCES feedmaps(feedmap_id)
);

2021-01-23 22:14:02

New branch -- postgres -- mimics features of Bayou-CO2.

Basic working setup here: https://github.com/edgecollective/feed-map/commit/6dd02a33311bcdbaed57a3bdf54b0070a342d6a1

Notes:

Flow:

2021-01-26 14:53:50

Adding x,y coord display over image, here: https://stackoverflow.com/questions/7414984/how-could-i-display-x-y-coordinates-on-image-in-real-time-to-the-user-when-the

2021-01-26 20:59:05

Getting close w/ feedmaps! https://gitlab.com/p-v-o-s/co2/feedmap/-/commit/15b5d91c469c17b3fbbd03ba5ab6b6eaf0b70869

The issue now is that we need to add "shortname" and "coords" to each feed listed in the feedmap database.
Need to modify the database.

Also: need to remove "name" from the bayou-co2. Perhaps just use the first few chars as the "name".

2021-01-27 11:14:20

2021-01-27 19:55:39

Feedmap hacking: http://192.168.1.163:4006/feedmap/manage/7a66feb586010296bb03fc01967a89ac7b1f9b41388db6ff/1f79e0a2d7159495e9be3cb545c83911147035130a2b9fba

2021-01-29 22:14:01

Setting up postgres on the Pi

Installing

sudo apt update sudo apt install postgresql postgresql-contrib

Creating DB

sudo -i -u postgres postgres@raspberrypi:~$ createdb hab1 postgres@raspberrypi:~$ psql hab1

CREATE TABLE feeds( feed_id SERIAL PRIMARY KEY, name VARCHAR(255), public_key VARCHAR(255), private_key VARCHAR(255) ;

CREATE TABLE measurements( id SERIAL PRIMARY KEY, feed_id INT, co2 FLOAT, tempC FLOAT, humidity FLOAT, mic FLOAT, auxPressure FLOAT, auxTempC FLOAT, aux001 FLOAT, aux002 FLOAT,
created TIMESTAMP DEFAULT NOW(), CONSTRAINT feed FOREIGN KEY(feed_id) REFERENCES feeds(feed_id) ;

(exit psql and postgres with "exit" )

Changing password

sudo -i -u postgres psql postgres=# ALTER USER postgres WITH PASSWORD 'pcat999'

2021-02-04 09:23:33

"Part 1: Create an application using PostgreSQL, Express, React and Node.js stack"

https://medium.com/javascript-in-plain-english/create-a-fullstack-banking-application-using-react-e8c96d74cd39

2021-02-07 11:54:58

DATA STRUCTURE REDESIGN NOTES:

Here's a slight database redesign (I think I can do this in a day or so) that, based on your investigations thus far, might improve usability.

A 'gaggle' consists of the data feeds coming from a wifi-lora gateway, plus up to 8 remote nodes that are associated with it.

Node IDs are fixed on the remote nodes using hardware switches that admit of 8 possible states.

Each node ID will always get the same 'color' in the graph; i.e. Node 0 is 'blue', Node 1 is 'red'.

Instead of creating individual 'feeds' on Bayou, you will now create 'gaggles'. You'll get a pub + priv key pair, which goes into that gaggles' gateway node's configuration file ("config.h").

We now also introduce the concept of "local lora group ID" -- a short, random key of 3 chars, which the gateway and lora nodes both share, independent of the gaggle pub + priv key.

Gateways only process lora data from their own "lora group", rejecting any other lora info coming in. (This avoids lora conflicts at Library HQ, when setting up multiple 'gaggles').

When you want to switch a gateway to a new gaggle, you create a new gaggle on Bayou, and reprogram the gateway with the new gaggle pub_key and priv_key, but you keep the lora group_key ... so that you don't need to reprogram the associated lora nodes at all.

In other words, the setup sequence is:

  1. Create a new 'gaggle' on Bayou, giving you a new gaggle pub_key and priv_key pair.
  2. Choose a local 'lora group' shortkey to identify your local lora network.
  3. Program the gaggle pub_key and priv_key, as well as the lora group key, into the gateway.
  4. Program the lora group key into all of the remote nodes.
  5. Set the 'hardware switches' on the remote nodes to indicate which node number they are on the network.

If you want to create a new gaggle, you just create a new gaggle on Bayou, and reprogram the gateway with the new gaggle pub_key and priv_key, but keep the lora group key the same. That way the remote nodes don't need to be reprogrammed.

Each remote node, on boot, 'measures' its local hardware node ID number. When it broadcasts, it sends its lora group key and its node ID along with its sensor data.

The gateway only listens for lora messages from lora nodes within its lora group. When it receives a lora message from its group, it sends it along to Bayou.

So the gateway config file will look like:

const char SSID = "pvosPi"; const char WiFiPassword = "12345678"; const char bayou_base_url = "192.168.4.1:3000/co2/data"; const char gaggle_pub_key = "62be3698ed7917a7da588750de28d0ac2030c1c12a5c1c6d"; const char* gaggle_priv_key = "f02d003674065fb2e9199bf39d602532324fdc1d5a041ab4"; char char* lora_groupkey = "a1c"; const int interval_sec = 10; const int forcePPM = 400;

And the remote config files (all identical to one another, but specific to this lora group) will look like:

char char* lora_group_key = "a1c"; const int interval_sec = 10; const int forcePPM = 400;

sudo -i -u postgres
createdb feedmap1;
psql feedmap1

then:

CREATE TABLE feeds(
    gaggle_id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    public_key VARCHAR(255),
    private_key VARCHAR(255)
);

CREATE TABLE feeds(
    id SERIAL PRIMARY KEY,
    gaggle_id INT,
    node_id INT,
    added TIMESTAMP DEFAULT NOW(),
    CONSTRAINT gaggle
        FOREIGN KEY(gaggle_id)
            REFERENCES gaggles(gaggle_id)
);


CREATE TABLE measurements(
id SERIAL PRIMARY KEY,
feed_id INT,
co2 FLOAT,
tempC FLOAT,
humidity FLOAT,
mic FLOAT,
auxPressure FLOAT,
auxTempC FLOAT,
aux001 FLOAT,
aux002 FLOAT, 
log VARCHAR(255),   
created TIMESTAMP DEFAULT NOW(),
CONSTRAINT feed
 FOREIGN KEY(feed_id)
REFERENCES feeds(feed_id)
;

Retrying ...

CREATE TABLE feeds(
    gaggle_id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    public_key VARCHAR(255),
    private_key VARCHAR(255)
);

CREATE TABLE feeds(
    id SERIAL PRIMARY KEY,
    gaggle_id INT,
    node_id INT,
    added TIMESTAMP DEFAULT NOW(),
    CONSTRAINT gaggle
        FOREIGN KEY(gaggle_id)
            REFERENCES gaggles(gaggle_id)
);


CREATE TABLE measurements(
id SERIAL PRIMARY KEY,
feed_id INT,
co2 FLOAT,
tempC FLOAT,
humidity FLOAT,
mic FLOAT,
auxPressure FLOAT,
auxTempC FLOAT,
aux001 FLOAT,
aux002 FLOAT, 
log VARCHAR(255),   
created TIMESTAMP DEFAULT NOW(),
CONSTRAINT feed
 FOREIGN KEY(feed_id)
REFERENCES feeds(feed_id)
;


CREATE TABLE feeds(
    feed_id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    public_key VARCHAR(255),
    private_key VARCHAR(255)
;

CREATE TABLE measurements(
id SERIAL PRIMARY KEY,
feed_id INT,
co2 FLOAT,
tempC FLOAT,
humidity FLOAT,
mic FLOAT,
auxPressure FLOAT,
auxTempC FLOAT,
aux001 FLOAT,
aux002 FLOAT,    
created TIMESTAMP DEFAULT NOW(),
CONSTRAINT feed
 FOREIGN KEY(feed_id)
REFERENCES feeds(feed_id)
;

2021-02-07 18:11:34

Nano ID here: https://medium.com/javascript-in-plain-english/you-might-not-need-uuid-v4-for-generating-random-identifiers-89e8a28a7d77

short uuid -- https://www.npmjs.com/package/short-uuid

https://gist.github.com/6174/6062387 -- generate random string in short and fast way

nice collection of resources here

short unique id here

this seems v good! https://shortunique.id/

https://betterexplained.com/articles/the-quick-guide-to-guids/

user-facing unique uuids -- https://dev.to/yeo_alexandra/creating-user-facing-short-unique-ids-what-are-the-options-8mn

generate a unique short id here: https://gnugat.github.io/2018/06/15/short-identifier.html

oooh this is a pretty good argument: https://neilmadden.blog/2018/08/30/moving-away-from-uuids/

good list of unique id thingies: https://github.com/grantcarthew/awesome-unique-id

nanoid -- https://github.com/ai/nanoid/

https://stackoverflow.com/questions/54338808/how-to-generate-unique-numeric-short-id-in-nodejs

good reading here: https://stackoverflow.com/questions/6248666/how-to-generate-short-uid-like-ax4j9z-in-js

looks like using nanoid w/ 11 characters would be the thing!

https://zelark.github.io/nano-id-cc/

using the unique constraint: https://www.postgresqltutorial.com/postgresql-unique-constraint/

using indexes: https://www.postgresql.org/docs/9.1/indexes-intro.html

2021-02-08 09:11:55

back to basics ...

Creating DB

sudo -i -u postgres
postgres@raspberrypi:~$ createdb hab3
postgres@raspberrypi:~$ psql hab3


CREATE TABLE feeds(
    feed_id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    public_key VARCHAR(255) UNIQUE,
    private_key VARCHAR(255) 
);

CREATE TABLE measurements(
id SERIAL PRIMARY KEY,
feed_id INT,
co2 FLOAT,
tempC FLOAT,
humidity FLOAT,
mic FLOAT,
auxPressure FLOAT,
auxTempC FLOAT,
aux001 FLOAT,
aux002 FLOAT,
log VARCHAR(255),    
created TIMESTAMP DEFAULT NOW(),
CONSTRAINT feed
 FOREIGN KEY(feed_id)
REFERENCES feeds(feed_id)
);

2021-02-08 09:35:02

installing nano-id: https://github.com/ai/nanoid

ah, here's an async approach:

https://stackoverflow.com/questions/49938266/how-to-return-values-from-async-functions-using-async-await-from-function

deleting rows in the table!

https://www.postgresqltutorial.com/postgresql-delete/

with some condition here: https://stackoverflow.com/questions/5170546/how-do-i-delete-a-fixed-number-of-rows-with-sorting-in-postgresql

2021-02-08 13:52:07

Shortened keys, and added ability to delete a feed of historical data!

2021-02-08 15:15:31

adding feedmaps2

CREATE TABLE feedmaps(
    feedmap_id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    public_key VARCHAR(255) UNIQUE,
    private_key VARCHAR(255),
    map_url VARCHAR(255)
);

CREATE TABLE feeds(
    id SERIAL PRIMARY KEY,
    feedmap_id INT,
    feed_base_url VARCHAR(255),
    feed_public_key VARCHAR(255),
    added TIMESTAMP DEFAULT NOW(),
    CONSTRAINT feedmap
        FOREIGN KEY(feedmap_id)
            REFERENCES feedmaps(feedmap_id)
);

okay, latest to play with is here:

http://192.168.1.163:4000/feedmap/manage/bda90e5b319c2e8b15e882ca950e7baf96bc362366828283/5693cc45409f6d89adc293489e1154f0430c7d723ccb2a05

2021-02-08 21:02:13

2021-02-09 12:27:25

Setting up a nodejs server for production https://www.digitalocean.com/community/tutorials/how-to-set-up-a-node-js-application-for-production-on-ubuntu-16-04

2021-02-09 12:30:29

Additional entry points via nginx here: https://snapshooter.io/blog/how-to-run-nodejs-server-with-nginx

2021-02-09 13:03:10

setting up server with nodejs

first added an A record for 'subdomain' at pvos, pointing to IP address of new droplet.

then on new droplet:

  1. ufw: https://www.digitalocean.com/community/tutorials/how-to-set-up-a-firewall-with-ufw-on-ubuntu-18-04

  2. set up nginx https://www.digitalocean.com/community/tutorials/how-to-install-nginx-on-ubuntu-20-04

  3. set up nvm https://github.com/nvm-sh/nvm; then 'nvm install 12'

  4. setting up nodejs for production https://www.digitalocean.com/community/tutorials/how-to-set-up-a-node-js-application-for-production-on-ubuntu-20-04

  5. generating a new ssh key https://docs.gitlab.com/ee/ssh/README.html#generate-an-ssh-key-pair

set up pm2

after running nginx, i basically just tweaked the default

sudo vi /etc/nginx/sites-available/default

server {
        listen 80 default_server;
        listen [::]:80 default_server;

        # SSL configuration
        #
        # listen 443 ssl default_server;
        # listen [::]:443 ssl default_server;
        #
        # Note: You should disable gzip for SSL traffic.
        # See: https://bugs.debian.org/773332
        #
        # Read up on ssl_ciphers to ensure a secure configuration.
        # See: https://bugs.debian.org/765782
        #
        # Self signed certs generated by the ssl-cert package
        # Don't use them in a production server!
        #
        # include snippets/snakeoil.conf;

        root /var/www/html;

        # Add index.php to the list if you are using PHP
        index index.html index.htm index.nginx-debian.html;

        server_name _;

        location / {
                # First attempt to serve request as file, then
                # as directory, then fall back to displaying a 404.
                #try_files $uri $uri/ =404;
                proxy_pass http://localhost:4000;
                proxy_http_version 1.1;
                proxy_set_header Upgrade $http_upgrade;
                proxy_set_header Connection 'upgrade';
                proxy_set_header Host $host;
                proxy_cache_bypass $http_upgrade;
        }

        # pass PHP scripts to FastCGI server
        #
        #location ~ \.php$ {
        #       include snippets/fastcgi-php.conf;
        #
        #       # With php-fpm (or other unix sockets):
        #       fastcgi_pass unix:/var/run/php/php7.4-fpm.sock;
        #       # With php-cgi (or other tcp sockets):
        #       fastcgi_pass 127.0.0.1:9000;
        #}

2021-02-09 15:03:51

working demo here https://gitlab.com/p-v-o-s/co2/feedmap/-/tree/4698789d79c3dd6b9c3a8fa88651ba4909c66e9d

2021-02-10 16:03:05

Fixing nodemon issue with stepping on ports here: https://stackoverflow.com/questions/58605392/port-3000-is-already-in-use-nodemon-app-crashed-waiting-for-file-changes-bef

2021-02-10 16:12:22

Handling failed errors with fetch: https://www.tjvantoll.com/2015/09/13/fetch-and-errors/

2021-02-10 21:22:55

demo of bayou-co2 and feedmap here: [https://youtu.be/_9c5-HOngBk]

bayou-co2 https://gitlab.com/p-v-o-s/co2/bayou-co2/-/tree/447cbeb5de42ec6b3ae5e7b3521c207624cda6e8

feedmap https://gitlab.com/p-v-o-s/co2/feedmap/-/tree/af0e1aa0e82fd80de31a9945e44d263c092ecc40

2021-02-11 12:15:10

creating a .ico image https://graphicdesign.stackexchange.com/questions/77359/how-to-convert-a-square-svg-to-all-size-ico

2021-02-18 13:25:49

sudo -i -u postgres
postgres@raspberrypi:~$ createdb feedmap3
postgres@raspberrypi:~$ psql feedmap3

CREATE TABLE feedmaps(
    feedmap_id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    public_key VARCHAR(255) UNIQUE,
    private_key VARCHAR(255),
    map_url VARCHAR(255)
);

CREATE TABLE feeds(
    id SERIAL PRIMARY KEY,
    feedmap_id INT,
    feed_base_url VARCHAR(255),
    feed_public_key VARCHAR(255),
    feed_nickname VARCHAR(255),
    added TIMESTAMP DEFAULT NOW(),
    CONSTRAINT feedmap
        FOREIGN KEY(feedmap_id)
            REFERENCES feedmaps(feedmap_id)
);

artisan's feeds

b2srm27dragm red CSV | JSON ertvughsgqzs blue CSV | JSON jdwktcu9xt5c green CSV | JSON t8uic9wh4q82 purple

feedmap is: 104.248.54.245 bayou is:

2021-02-24 09:39:08

belfast-feed

sudo -i -u postgres
postgres@raspberrypi:~$ createdb belfast2
postgres@raspberrypi:~$ psql belfast2


CREATE TABLE feeds(
    feed_id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    public_key VARCHAR(255) UNIQUE,
    private_key VARCHAR(255) 
);

CREATE TABLE measurements(
id SERIAL PRIMARY KEY,
feed_id INT,
temperature_c FLOAT,
humidity_rh FLOAT,
distance_meters FLOAT,
pressure_mbar FLOAT,
battery_volts FLOAT,
gps_lat FLOAT,
gps_lon FLOAT,
gps_alt FLOAT,
distance_meters_1 FLOAT,
distance_meters_2 FLOAT,
distance_meters_3 FLOAT,
temperature_c_1 FLOAT,
temperature_c_2 FLOAT,
temperature_c_3 FLOAT,
voltage_1 FLOAT,
voltage_2 FLOAT,
voltage_3 FLOAT,
aux_1 FLOAT,
aux_2 FLOAT,
aux_3 FLOAT,
log VARCHAR(255),    
created TIMESTAMP DEFAULT NOW(),
CONSTRAINT feed
 FOREIGN KEY(feed_id)
REFERENCES feeds(feed_id)
);

sudo apt install postgresql postgresql-contrib


setting password for user postgres:

sudo -u postgres psql ALTER USER postgres PASSWORD 'myPassword';


need to install nvm first ...

and note:  i've been using npm version 12!

now need to set up server creds ...

1. add subdomain on dreamhost as 'A' record ...
2. manage firewall on digital ocean drople to allow the specific port to get access [https://www.digitalocean.com/community/tutorials/how-to-set-up-a-firewall-with-ufw-on-ubuntu-18-04]
3. set up nginx on digital ocean droplet [https://www.digitalocean.com/community/tutorials/how-to-install-nginx-on-ubuntu-20-04]
4. set up nodejs server blocks [https://www.digitalocean.com/community/tutorials/how-to-install-nginx-on-ubuntu-20-04]

---
2021-02-24 11:15:42

python http posts:

https://www.w3schools.com/python/ref_requests_post.asp

---
2021-02-25 10:52:48

modifying the name of a column [https://www.postgresqltutorial.com/postgresql-rename-column/](https://www.postgresqltutorial.com/postgresql-rename-column/)

---
2021-02-25 16:32:02

getting url params in express: [https://stackoverflow.com/questions/20089582/how-to-get-a-url-parameter-in-express](https://stackoverflow.com/questions/20089582/how-to-get-a-url-parameter-in-express)

or [https://coderwall.com/p/huraaa/checking-if-a-parameter-exists-with-expressjs](https://coderwall.com/p/huraaa/checking-if-a-parameter-exists-with-expressjs)

----
2021-03-10 20:08:43

New version of Bayou ...



## setting up server with nodejs

first added an A record for 'subdomain' at pvos, pointing to IP address of new droplet.

then on new droplet:

1. ufw: [https://www.digitalocean.com/community/tutorials/how-to-set-up-a-firewall-with-ufw-on-ubuntu-18-04](https://www.digitalocean.com/community/tutorials/how-to-set-up-a-firewall-with-ufw-on-ubuntu-18-04)

2. set up nginx [https://www.digitalocean.com/community/tutorials/how-to-install-nginx-on-ubuntu-20-04](https://www.digitalocean.com/community/tutorials/how-to-install-nginx-on-ubuntu-20-04)

3. set up nvm [https://github.com/nvm-sh/nvm](https://github.com/nvm-sh/nvm); then 'nvm install 12'

4. setting up nodejs for production [https://www.digitalocean.com/community/tutorials/how-to-set-up-a-node-js-application-for-production-on-ubuntu-20-04](https://www.digitalocean.com/community/tutorials/how-to-set-up-a-node-js-application-for-production-on-ubuntu-20-04)

5. generating a new ssh key [https://docs.gitlab.com/ee/ssh/README.html#generate-an-ssh-key-pair](https://docs.gitlab.com/ee/ssh/README.html#generate-an-ssh-key-pair)

set up pm2

after running nginx, i basically just tweaked the default

sudo vi /etc/nginx/sites-available/default

server { listen 80 default_server; listen [::]:80 default_server;

    # SSL configuration
    #
    # listen 443 ssl default_server;
    # listen [::]:443 ssl default_server;
    #
    # Note: You should disable gzip for SSL traffic.
    # See: https://bugs.debian.org/773332
    #
    # Read up on ssl_ciphers to ensure a secure configuration.
    # See: https://bugs.debian.org/765782
    #
    # Self signed certs generated by the ssl-cert package
    # Don't use them in a production server!
    #
    # include snippets/snakeoil.conf;

    root /var/www/html;

    # Add index.php to the list if you are using PHP
    index index.html index.htm index.nginx-debian.html;

    server_name _;

    location / {
            # First attempt to serve request as file, then
            # as directory, then fall back to displaying a 404.
            #try_files $uri $uri/ =404;
            proxy_pass http://localhost:4000;
            proxy_http_version 1.1;
            proxy_set_header Upgrade $http_upgrade;
            proxy_set_header Connection 'upgrade';
            proxy_set_header Host $host;
            proxy_cache_bypass $http_upgrade;
    }

    # pass PHP scripts to FastCGI server
    #
    #location ~ \.php$ {
    #       include snippets/fastcgi-php.conf;
    #
    #       # With php-fpm (or other unix sockets):
    #       fastcgi_pass unix:/var/run/php/php7.4-fpm.sock;
    #       # With php-cgi (or other tcp sockets):
    #       fastcgi_pass 127.0.0.1:9000;
    #}



## installing postgres

sudo apt install postgresql postgresql-contrib


setting password for user postgres:

sudo -u postgres psql ALTER USER postgres PASSWORD 'myPassword';

sudo -i -u postgres postgres@raspberrypi:~$ createdb bayou3 postgres@raspberrypi:~$ psql bayou3

CREATE TABLE feeds( feed_id SERIAL PRIMARY KEY, name VARCHAR(255), public_key VARCHAR(255) UNIQUE, private_key VARCHAR(255) );

CREATE TABLE measurements( id SERIAL PRIMARY KEY, feed_id INT, temperature_c FLOAT, humidity_rh FLOAT, co2_ppm FLOAT, light_lux FLOAT, distance_meters FLOAT, pressure_mbar FLOAT, battery_volts FLOAT, rssi FLOAT, gps_lat FLOAT, gps_lon FLOAT, gps_alt FLOAT, node_id INT, next_hop INT, next_rssi FLOAT, distance_meters_1 FLOAT, distance_meters_2 FLOAT, distance_meters_3 FLOAT, temperature_c_1 FLOAT, temperature_c_2 FLOAT, temperature_c_3 FLOAT, voltage_1 FLOAT, voltage_2 FLOAT, voltage_3 FLOAT, aux_1 FLOAT, aux_2 FLOAT, aux_3 FLOAT, log VARCHAR(255),
timestamp TIMESTAMP DEFAULT NOW(), CONSTRAINT feed FOREIGN KEY(feed_id) REFERENCES feeds(feed_id) );


up and running at root@104.248.50.193 !

ALTER TABLE measurements RENAME COLUMN created TO timestamp;