Database Control - BitKnitting/should_I_water GitHub Wiki

The happyday-collect.service collects moisture readings from moisture pucks and stores them into the reading table of the garden_readings.db SQLite database.

happyday-collect.service runs collect_reading_tasks.py. collect_readings_task.py relies on the handle_moisture_packets_lib.py module to collect and store moisture readings. While storing the readings, the script checks to see if the reading is below the threshold set in the moisture_pucks table:

Adding a Row

• Go into the folder that has the garden_readings.db. On my Raspberry Pi, this is $HOME/myLadybugHelper/databases.
• Open up the database: sqlite3 garden_readings.db.
• (optional) Look at the contents of the valves table: select * from valves;.
• Add a row for the watering puck. Since this watering puck is at a different set of valves, it has a unique node ID. I randomly choose 5 for the node ID. To add a row, we'll need to know the column names:

sqlite> select sql from sqlite_master where type = 'table' and tbl_name='valves';
CREATE TABLE "valves" ("id" INTEGER NOT NULL PRIMARY KEY, "valveID" INTEGER NOT NULL, "moisturePuckID" INTEGER NOT NULL, "wateringPuckID" INTEGER NOT NULL, "description" VARCHAR(255) NOT NULL, "watering_time" INTEGER NOT NULL)

A SQLite Insert Tutorial provides us guidance on the SQLite command we'll use:

INSERT INTO valves (valveID,moisturePuckID,wateringPuckID,description,watering_time) VALUES (103,2,5,'front yard',8);

Now our valves table looks like:

sqlite> select * from valves;
1|102|1|4|Strawberries|8
2|101|1|4|Flowers near pond|8
3|103|2|5|front yard|8

The internal method _water_if_needed(self,measurement) of the handle_moisture_packets_lib.py module uses the valves table to determine which valves to tell the watering puck responsible for the moisture pucks to turn on and for how many minutes. The valve ids are defined within the moisture puck's firmware.