Chapter 5 ■ MySQL priMer
CREATE TABLE readings (
id int(11) NOT NULL,
moisture float DEFAULT NULL,
temperature float DEFAULT NULL,
event_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
soil_status enum ('DRY', 'OK', 'WET') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DELIMITER //
CREATE TRIGGER set_status BEFORE INSERT ON readings FOR EACH ROW
BEGIN
IF NEW.moisture < 250 THEN
SET NEW.soil_status = 1;
ELSEIF NEW.moisture < 400 THEN
SET NEW.soil_status = 2;
ELSE
SET NEW.soil_status = 3;
END IF;
END //
DELIMITER ;
Notice the trigger defined on the readings table. Recall from a previous section that a trigger can be
used to provide data for calculated columns, which is what we want to store in the soil_status column. In
this case, we set the low threshold for dry soil to 250 and wet for anything over 400. Thus, between 250 and
399, the plant soil is considered normal (Ok).
Also, notice the comments included in the SQL code. The double dash is a comment that the MySQL
client will ignore. While this example database is rather simple, it does not hurt to add short descriptions of
your database objects (tables, and so on) so that you can remember what each does should you forget or as
someone else to work with the database or your IOT solution (like for a bespoke application).
Notice also the location column in the plants table. This is an example of using enumerated values
so that you don’t have to repeatedly enter the string with each plant you add to the table. Values start at 1,
so you can specify 1 for inside and 2 for outside, as shown here:
mysql> INSERT INTO plants VALUES(NULL, 'Jerusalem Cherry', 'deck', 2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from plants;
+----+---------------------+----------+---------+
| id | name | location | climate |
+----+---------------------+----------+---------+
| 1 | Jerusalem Cherry | deck | outside |
+----+---------------------+----------+---------+
1 row in set (0.00 sec)
■Note i chose as descriptive names as i could for the columns. i also threw in some that are a bit fuzzy.
Can you tell which ones could use a better name? hint: what does climate mean to you? Clearly, choosing valid,
meaningful names for columns is a challenge and a bit of an art form.