Chapter 4 ■ Data transformation
strData += String(newVal, 2);
strData += ", Delta: ";
strData += String(newVal-oldVal,3);
oldVal = newVal;
log_file.println(strData);
log_file.close();
} else {
Serial.println("Cannot open file for reading.");
}
}
■Note the following code is not representative of an actual solution; rather, it is designed to show the
concepts of generating derived and calculated columns.
Notice once again we add the new data as columns by separating them by commas. The following
shows a sample set of rows from this code:
Blood oxygen: 94.88, Calibrated: 95.67, Weight: 46.07, Avg weight: 11.5169, Volts: 68.42,
Delta: 1.300
Blood oxygen: 95.23, Calibrated: 96.02, Weight: 46.56, Avg weight: 11.6394, Volts: 68.52,
Delta: 0.100
Blood oxygen: 94.97, Calibrated: 95.76, Weight: 46.42, Avg weight: 11.6044, Volts: 68.62,
Delta: 0.100
Blood oxygen: 95.46, Calibrated: 96.25, Weight: 46.49, Avg weight: 11.6219, Volts: 69.62,
Delta: 1.000
Blood oxygen: 94.96, Calibrated: 95.75, Weight: 46.18, Avg weight: 11.5444, Volts: 70.12,
Delta: 0.500
Blood oxygen: 94.62, Calibrated: 95.41, Weight: 46.11, Avg weight: 11.5269, Volts: 70.52,
Delta: 0.400
Notice here we see the blood oxygen sensor, and its calibrated value is stored along with the weight and
average weight of four objects and then volts and the change since the last value read is stored. Let’s now see
how the database server can make each of these annotations easier.
Database Considerations
By now you should be thinking the database server is a powerful tool, and it is. In fact, it is great at doing
the derivations and calculations like those shown earlier. Moreover, depending on the calculation needed,
you have several options for how to implement the derivation or calculation. This section discusses three
common alternatives. There are other alternatives,^9 but these are among the most common methods.
You could add a trigger, which is a special procedure that is executed (triggered) when data is added; to
add the new data in a special column, you could put the calculations in the SELECT statement (the method
for retrieving rows) so that the new data is generated on the fly; or you could put the calculations in the
INSERT statement (the method for saving data). Let’s look at each of these starting with why you may choose
one over the other.
(^9) Such as stored events, stored procedures, and so on.