Chapter 5 ■ MySQL priMerWe will also limit the columns in the result to just the id. The following query shows an example SELECT
statement to determine which readings indicate which plant or plants need watering today:
mysql> SELECT id FROM soil_status_today WHERE ((num_events/max_samples_today(id)) > 0.50)
AND soil_status = 1;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
We’re almost there. Once again, we will use the previous query and join against the plant table to
determine which plants need monitoring.
SELECT name, location FROM plants JOIN (SELECT id FROM soil_status_today WHERE ((num_events/
max_samples_today(id)) > 0.50) AND soil_status = 1) as sub_query on plants.id = sub_query.id;
And now we know the plant that needs watering today. Notice we simply used our logical breakdown of
how to find the data starting from the most basic and moving outward (as in narrowing our results). As you
can see, this query wasn’t nearly that difficult, and since we created a function and a view to help us, queries
similar to this one will be much easier to write.
Step 4: Testing the Database
Now that we have the database designed, implemented, and the queries decided upon (or at least all of the
ones we can think of ), we are ready to start building our sensor network and code the application, right?
Nope. We need to test the queries not only to make sure they work (that is, have the correct syntax) but also
to make sure we are getting the results we expect. This requires having a known data set to work with. It will
do you little good if you find out after your application has gone viral that one of the queries doesn’t return
the correct data.
At this point, the data may not be completely accurate nor does it require actual, live data. You can use
made-up data so long as you make data that is representative of the range of values for each column in the
table. That is, make sure you know what the min and max values are that your sensors can read. Listing 5-10
shows some sample data I created to test the queries for the plant-monitoring solution.
Listing 5-10. Sample Data
INSERT INTO plant_monitoring.plants VALUES (NULL, 'Jerusalem Cherry', 'deck', 2);
INSERT INTO plant_monitoring.plants VALUES (NULL, 'Moses in the Cradle', 'patio', 2);
INSERT INTO plant_monitoring.plants VALUES (NULL, 'Peace Lilly', 'porch', 1);
INSERT INTO plant_monitoring.plants VALUES (NULL, 'Thanksgiving Cactus', 'porch', 1);
INSERT INTO plant_monitoring.plants VALUES (NULL, 'African Violet', 'porch', 1);
INSERT INTO plant_monitoring.readings VALUES (1, 235, 39.9, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (1, 235, 38.7, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (1, 230, 38.8, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (1, 230, 39.1, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (1, 215, 39.2, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (1, 215, 39.5, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (1, 225, 39.2, NULL, NULL);