Monitoring health data - populating Food tables

This article continue the discussion about the building of the system to collect my health numbers.

There are already many data stored in LibreOffice Calc tables and other LibreOffice documents.
Because of that when/where possible macro will be used to generate the mySQL commands to populate the tables.

Populate T_FOOD_TYPE

Populating this table it requires more work because it is necessary to find the information about each specific food used. In order to simplify the input mechanism, a new spreadsheet that collect the information to be put in the database, is created.
Unfortunately there is no easy way to populate the spreadsheet, i.e. it must be done manually, copying the data from the existing LibreOffice word documents as described in the Monitoring health data - my way  #2 article.

Let's examine the spreadsheet used to format the insert statement.

The spreadsheet has two tabs:

• Food type
• Food

The Food type tab, is structured as the database, raw.
A hash key is prepared as index, using the Food name, the brand and the number of carbs.
This should allow to generate a unique key for the each record.

Here the fields of the tab:

Index (food type)

Name

1 Serving

Serving unit

Serv X cont

Carbs

Calories

Protein

Fat

Sodium

Potassium

Brand

Notes

The index in the cell A2 use the Hash macro (it is implied the third Party Hash macro is installed):

=TEXTHASH(CONCATENATE(B2,L2,F2),"MD5")

The goal is to have a unique index and to do so the hash is calculated using the name of the food (B2), the value in carbs (F2) and the brand (L2).
The field in the table are mostly following the SQL table structure (see Monitoring health data - an improvement).
For extra info there are these fields:

• Serv X cont
  Serving per container - the number of serving you can expect from the container or how many servings can be obtained from a recipe
• Notes
  Just extra notes about the food

After the table, there is the formula for the formatting of the SQL insert statement.

First of all, a cell (O1) contains the starting structure of the statement :

Insert into t_food_type values (

Then below, this formula is applied :

=IF(B2="","",(CONCATENATE($O$1,"""",A2,"""", ",", """", B2, """", ",", C2, ",", """", D2, """", ",", F2, ",", G2, ",", H2, ",", I2, ",", J2, ",", K2, ",",  """", L2, """", ",", """", M2, """", ");")))

The meaning is :

• if the field "Name" exists (not blank) then 
• create a string using the field O1, plus the other fields, starting from the field A2 (hash key) and ending with the field M2 (Notes)
• otherwise create a blank entry

The formula is then copied in all subsequent cells.

Here an example of the insert statement generation starting with the data on the table :

Insert into t_food_type values ("ce7993e549b4d010c825766dfd034a99","Pork Patties",2,"patty",2,270,11,24,500,0,"Jimmy Dean","");
Insert into t_food_type values ("981a21f5a752cad048a834b9fd6cd258","Cooked Ham",4,"slices",1,60,8,2,580,0,"Land O'Frost","");
Insert into t_food_type values ("1a52451ea65def964ddb3c2be50811ce","Big Cup Noodles Homestyle Beef Flavor Ramen Noodle",1,"1/2 container",24,190,3,8,760,0,"Nissin","");
Insert into t_food_type values ("74ccbb6a8f0e507375ca5f727074a4e1","Texas Style Angus Beef Chili with Black Beans Soup",1,"cup",21,210,15,5,790,0,"Campbell","");
Insert into t_food_type values ("76d5f224ea4a78e3d737caa4eba0d1e","iesta Chicken Lime Tortilla Soup with White Meat Chicken",1,"cup",13,110,9,1.5,790,0,"Campbell","");

.............................................................

.............................................................

After the table is manually populated, we end up with a list of insert capable to fill up the T_FOOD_TYPE table.

Populate T_FOOD

The same mechanism used  to populate the T_FOOD_TYPE table, is used to populate the T_FOOD table too.

Like before, a new spreadsheet that collect the information to be put in the database is created (still refer to the Monitoring health data - my way  #2 article)

Let's examine the spreadsheet used to format the insert statement.

The spreadsheet has two tabs:

• Food type
• Food

The Food tab, is structured as the database, raw.

Here the table structure:

Index

Date Meal

Type Meal

FoodIndex

Food name

Servings

Calculated carbs

Calculated salt

The Calculated carbs and Calculated Salt are populated using data from the previous table.

Calculated carbs is :

=VLOOKUP(D2,'Food type'.$A$2:$M$156, 6, 0)*F2

The meaning is to look in the all previous tab (Food type) of the spreadsheet for the FoodIndex key, retrieve the number of carbs per serving and multiply for the number of serving (field Servings on this tab).

Similarly for the Calculated salt :

=VLOOKUP(D2,'Food type'.$A$2:$M$156, 10, 0)*F2

The meaning is to look in the all previous tab (Food type) of the spreadsheet for the FoodIndex key, retrieve the number of mg of salt per serving and multiply for the number of serving (field Servings on this tab).
Just to have a quick idea about the amount of salt of the selected food.

The Index is an hash key using the Date Meal, the FoodIndex and the Food name fields. 

This should allow to generate a unique key for the each record.
The cell A2 contains this formula :

=TEXTHASH(CONCATENATE(B2,C2,D2,E2),"MD5")

Again the purpose is to obtain a unique key for each entry, to do so it used the date of the meal (B2), the type of the meal (C2, e.g. Breakfast or Lunch or Dinner or Snack), the food index (D2, foreign key) and the food name (E2).
Note that the food name field is populated from the other Calc table (see above).

After the table, there is the formula for the formatting of the SQL insert statement.

First of all, a cell (I1) contains the starting structure of the statement :

Insert into t_food values (

Then below, this formula is applied :

=IF(B2="","",(CONCATENATE($H$1, """", A2, """", ", str_to_date('",(TEXT(B2,"mm/dd/yy")), "', ", """", "%m/%d/%Y", """", "), ",        """",C2,"""",", ","""",D2,"""", ", " ,F2, ");")))

The meaning is :

• if the field "Date Meal" exists (not blank) then 
• create a string using the field H1, plus the other fields, starting from the field A2 (hash key) and ending with the field F2 (Servings)
• otherwise create a blank entry

The formula is then copied in all subsequent cells.

Here an example of the insert statement generation starting with the data on the table :

Insert into t_food values (84280b208c8c61a7aab5c4be5fb25385, str_to_date('12/29/14', "%m/%d/%Y"), "Breakfast", "ac118ebecb401ae2e9fe42b8f7b1da8c", 2);
Insert into t_food values (16df74f3237c526a71186884b0cd7aa1, str_to_date('12/29/14', "%m/%d/%Y"), "Breakfast", "254a86d2153bfbc149f51d63c7113952", 1);
Insert into t_food values (532061254f4761e69bcf627c4916f3c2, str_to_date('12/29/14', "%m/%d/%Y"), "Lunch", "1b4f22d0d92b9185406cf6d815fd2c4c", 1);
Insert into t_food values (34c6041c3126488e599aa467e8a0d4de, str_to_date('12/29/14', "%m/%d/%Y"), "Lunch", "af3a803a27460603422bf47322ba6d4", 2);
Insert into t_food values (532061254f4761e69bcf627c4916f3c2, str_to_date('12/29/14', "%m/%d/%Y"), "Dinner", "1b4f22d0d92b9185406cf6d815fd2c4c", 2);
....................................................................
....................................................................

Basically after the table is manually populated, we end up with a list of insert capable to fill up the T_FOOD table.

Monitoring health data - populating measurement tables

This article continue the discussion about the building of the system to collect my health numbers.

There are already many data stored in LibreOffice Calc tables and other LibreOffice documents.
Because of that when/where possible macro will be used to generate the mySQL commands to populate the tables.

The T_MES_TYPE table will be populated manually, but T_MES, T_FOOD_TYPE and T_FOOD can use some kind of automation mechanism to do so.

Populate T_MES_TYPE

Here a list of mySQL commands to populate the T_MES_TYPE table.
It is easy to populate the table, just open a mySQL session in a terminal, connect to the database and then cut & paste the lines below.

connect name_database;
insert into t_mes_type values (1, "Glicemy", "ml/dg");
insert into t_mes_type values (2, "Weight", "lb");
insert into t_mes_type values (3, "Carbs", "count");
insert into t_mes_type values (4, "Blood Systolic", "mmHg");
insert into t_mes_type values (5, "Blood Diastolic", "mmHg"); 
insert into t_mes_type values (6, "Heart beat", "bpm"); 

insert into t_mes_type values (7, "Ketons", "count"); 

insert into t_mes_type values (8, "Exercise elliptical", "min"); 

insert into t_mes_type values (9, "Exercise walking", "miles"); 

insert into t_mes_type values (10, "Exercise hiking", "miles"); 

Populate T_MES table from Spreadsheet

Since already exists many data stored in the LibreOffice spreadsheet, here there is  a procedure to populate the data stored in the tables. 

Populate T_MES - glucose level

• Open the spreadsheet
• Create a separate tab and rename it to give a meaning (i.e. Export SQL glucose)
• In the cell A1 copy this fixed string : Insert into t_mes values ( 
• In the cell J2 use the Hash macro (it is implied the third Party Hash macro is installed):
  
  =TEXTHASH(CONCATENATE('All times'.A2,'All times'.F2,'All times'.G2),"MD5")
• Copy in the other Jx fields the formula above
• In the cell A2 copy this formula :
  
  =IF (OR('All times'.G2 = 0, 'All times'.G2 = ""), "",(CONCATENATE($A$1, """", J2,"""", ", str_to_date('", (TEXT('All times'.A2,"mm/dd/yy")), "',", """", "%m/%d/%Y", """", ")", ", str_to_date('", (TEXT('All times'.F2,"hh:mm:ss")), "',", """", "%H:%i:%s", """", ")", ", 1, ", 'All times'.G2, ");")))
• Copy all the result strings  in the mysql

Here an example of a correct entry generated :

Insert into t_mes values ("31ba5af2e19e9c8e9bc35bbd1f588fb1", str_to_date('10/28/14',"%m/%d/%Y"), str_to_date('19:45:00',"%H:%i:%s"), 1, 150);

Invalid entries will be blanket

Populate T_MES - weight

• Open the spreadsheet
• Create a separate tab and rename it to give a meaning (i.e. Export SQL weight)
• In the cell A1 copy this fixed string : Insert into t_mes values ( 
• In the cell J2 use the Hash macro (it is implied the third Party Hash macro is installed):
  
  =TEXTHASH(CONCATENATE('All times'.A2,'All times'.F2,'All times'.H2),"MD5")
• Copy in the other Jx fields the formula above
• In the cell A2 copy this formula :
  
  =IF ( OR ('All times'.H2 = 0, 'All times'.H2 = ""), "", (CONCATENATE($A$1, ,"""",J2,"""", ", str_to_date('", (TEXT('All times'.A2,"mm/dd/yy")), "',", """", "%m/%d/%Y", """", ")", ", str_to_date('", (TEXT("08:00:00","hh:mm:ss")), "',", """", "%H:%i:%s", """", ")", ", 2, ", 'All times'.H2, ");")))
• Copy the content of the cell A2 for the remains rows up tp the number of rows present in the tab All times (see the limit in the tab All times)
• Copy all the result strings  in the mysql

Here an example of  a correct entry generated :

Insert into t_mes values ("4af7c8660faee64b3e126c3c1717b510", str_to_date('10/28/14',"%m/%d/%Y"), str_to_date('08:00:00',"%H:%i:%s"), 2, 177.6);


Invalid entries will be blanketed

Populate T_MES - Ketons

• Open the spreadsheet
• Go in the tab of the month (to be repeated for each month)
• In the cell U1 copy this fixed string : Insert into t_mes values ( 
• In the cell T2 column create the index using the hash formula:
  
  =TEXTHASH(CONCATENATE(A2,F2,K2),"MD5")
• Copy in the other Tx fields the formula above
• In the cell U2 copy this formula :
  
  =IF (   K2 = "",    "",       (CONCATENATE($U$1,                 """",T2,"""",         ", str_to_date('", (TEXT(A2,"mm/dd/yy")), "',", """", "%m/%d/%Y", """", ")",         ", str_to_date('", (TEXT("09:00:00","hh:mm:ss")), "',", """", "%H:%i:%s", """", ")",         ", 7, ", K2, ");")))
• Copy the content of the cell U2 for the remains rows up tp the number of rows present in the monthly tab 
• Copy all the result strings  in the mysql

Here an example of a generated entry (invalid entries are blanketed).

Insert into t_mes values ("fe8565c8659775e73957e9c0485101f4", str_to_date('03/01/15',"%m/%d/%Y"), str_to_date('09:00:00',"%H:%i:%s"), 7, 0);

Alternatively (I prefer this way) is possible to prepare ASCII files with the generated commands and feed mySQL with these files.
In this way, the files allows to recreate the database from the scratch if necessary, a form of backup, and also is easy to duplicate the database on different machines.
The files can be edited to add comments and explain the purpose of specific commands/fields.

MSP430 - generating a PWM signal

This article discuss some basic ways to generate a PWM signal using a MSP430.

Theory of operations

A PWM signal is a digital signal with fixed frequency but varying duty cycle.

If the duty cycle of the PWM signal is varied with time, and the PWM signal is filtered, the output of the filter will be an analog signal.

There  are mostly 3 ways  to generate a PWM signal using a MSP430 :

1. loop
2. timer
3. interrupt

Loop

The easiest way to generate a PWM signal is to create an infinite loop and inside the loop, set a I/O pin ON or OFF, using a counter to determine when to change the ON vs. OFF time, comparing it to a "dutycycle" value. Also in the loop must be present a delay to determine the frequency of the signal.
The pro is obvious, is really easy to generate a PWM signal in this way.
But there are big cons with this method.

Let see some of them:

• all the resources of the processor are devoted to generate the signal
• is extremely difficult to calculate a specific timing
• any extra operation added in the loop go to alter the frequency of the generated signal
• if interrupts are used the result signal is extremely instable

So the loop is not really something to consider for real use.

Timer

The second way to generate a PWM signal is to use a timer.

The MSP430 timer is capable to generate a PWM signal, it means that is possible to program a MSP430 timer specifically for the purpose to generate a PWM signal.

Because the signal in the end is generated by a piece of hardware, the result is extremely stable and precise.

Pro:

• very stable PWM signal
• extremely precise timing is possible

Cons:

• the timer is devoted only to the PWM signal generation since the I/O pin is connected to it
• only 1 PWM signal per timer is available
• setting up the timer can be complicated

Interrupt

The third alternative is to soft-generate the PWM, like in the loop solution, but using a timer interrupt instead a deal loop.

A generic timer interrupt is generated and every time the interrupt is fired, a function handle one or more I/O pins to generate the PWM signal.

Pro:

• stable PWM signal
• precise timing (but less than the full timer solution)
• possibility to have more than one PWM signals
• possibility to use the timer resource for other purposes
• easy to calculate the time

Cons:

• limited timing range

PWM generated by a timer

Two MSP430 subsystems are involved in generating a PWM signal with the timer, the clock subsystem and the timer subsystem.
The output pin of the PWM signal, is directly connected to the timer.
Once programmed the timer, the generation of the PWM signal doesn't require any additional software.

Clock

The timer needs to be "fed" with a clock and there are different choices, depending the time required and the precision needed.

PWM using a generic time interrupt

In this way, a timer is still involved, but instead to generate the PWM signal, it generate interrupts.
Any time the interrupt is fired, a piece of software provide to change the state of an I/O pin depending some counter values compared with a threshold, generating in this way the PWM signal.
The frequency of the signal is determined by the timer setting and the precision is depending by the number of "steps" of the counter.

For example, if we want a 100 Hz signal, we need to set up an interrupt timing "at least"  set to fire every 1/(100 Hz * 2)  i.e. 0.005 seconds.
It means that every 0.005 seconds we have an interrupt and change the state of the output, obtaining a square wave of 100 Hz.
But we want to control the duty cycle, and so we need  to have a smaller interrupt time.
Let say we want to divide our 100 Hz in 10 "steps", so we need to set the timer to fire every 1/(100 Hz/10) = 0.001 seconds or 1 ms.
Every ms we have an interrupt and then we decide if having the output ON or OFF.
Our period is so divided in 10 1 ms steps, i.e. 0.001 * 10 = 0.1 sec. that converted is 100 Hz.

But only 10 steps is not good, so better to split the time more, at least 50 or 100.
Of course we have some limits depending the input clock and how long the interrupt function requires to operate.

So we have to play with the clock in order to have the micro go faster and really optimize the interrupt function.

The interrupt function must be executed in a time much shorter than 1 ms, and we need to decide if the output is high or low depending a counter value in that timeframe.

Here a flow chart to better explain the basics.

On the left a flow of the main code.
It simply initialize the system and enter in an infinite loop. Eventually is possible to have code to perform settings/change of the parameters in the loop.

On the right the flow of the timer interrupt.
Every time the interrupt is fired, it uses a counter compared with a threshold to determine if turn the signal ON or OFF and then update the counter restarting the timer.
To measure exactly how long on average the function last, a debug signal is used.
The code turn this debug signal ON just after started the interrupt and OFF just before to exit.
With an oscilloscope is then possible to measure how long the signal remain ON. That more or less would be the time used by the interrupt function.
Here a couple of pictures from my oscilloscope during some tests.

Debug signal in the timer interrupt - DCO set for 16 Mhz selection.
The irregular wave indicates different execution timing, approx 3.5 us average execution time

PWM signal generated, 10 steps, approx 10Khz frequency - DCO set for 16 Mhz selection

MSP430 - setting up the clock

All the microcontrollers needs a clock signal to work.
The MSP430 family is not an exception and actually there are many choices when we talk about the clock.
It is important ALWAYS to have handy the LATEST MSP430 family document and the specific chipset documentation, for example the MSP430F20x2 family (an entry level).
Some schematics/pictures shown here are copied from the MSP430 family document.

Let see the MSP430 clock system

The first thing that come up from the picture above, is that we have three different clock signal generated:

• MCLK
• SMCLK
• ACLK

Let start from here.

MCLK

The most important signal generated is the MCLK, the Main System Clock.
As the name imply, this is the clock signal used by the core of the MSP430 chipset, the CPU and basic signals (memory bus/I/O bus/ecc.).

SMCLK

The SMCLK signal is the Sub system clock.
It is normally connected to the peripherals, like the Timer, Watchdog and every peripheral that can require a clock.

ACLK

The ACLK is an Auxiliary clock.
Sometime it can be necessary to have peripherals using different clocks. For example a UART can require a special timing if compared with the other peripherals or a specific timer needs a low and precise timing.
The Auxiliary clock cannot use the DCO oscillator. It can use only the crystal oscillator.

Clock sources

When the chip is turned ON, i.e. after a hard reset, the clock subsystem is set to generate an approx. 800 kHz signal on the MCLK and SMCLK pin.
This allows the chipset to be able to start to execute instructions. A default clock mechanism must be present otherwise it would be impossible to "program the clock subsystem" if no instructions can be executed.
By default, the source for the SMCLK is the same of the MCLK, the DCO generator.
There are four  possible sources for the clock:

• Very low frequency clock
• DCO
• Crystal oscillator 1
• Crystal oscillator 2

Some chipset could have only one crystal oscillator, so is important to know what chipset you are using.

Very low frequency clock

This clock runs around 12 kHz and it can be used when the chipset is put in low power mode.
It is reasonably precise.

DCO

The DCO (Digitally Controlled Oscillator) is the default source of clock for the MCLK and SMCLK.

As the name imply, it is possible to set up this oscillator simply writing the setting in some registers.

Different frequencies can be generated simply changing some register settings.
There are Pro and Cons using the DCO.

Pro:

• flexible frequency generator
• doesn't require ANY external hardware
• fast

Cons:

• less stable and precise than crystal
• it require a tuning procedure

DCO calibrated values

The DCO uses resistors, external or internal, to  set up a specific frequency.
If the precision of the clock is not an issue, there is no need to worry about using calibrated values for the DCO.
Because the tolerance of the components, each MSP430 needs a different set of resistor values to set up the clock for specific frequencies.
It would be a very time consuming task to use external resistors, figuring out what is the correct value for a specific frequency, but fortunately TI though about that.
Every MSP430 chip has a table containing the calibrated  (values) for the internal resistor array.
Is enough to read the table and acquire the value for the setting needed.
It is also possible to re-write the table for specific needs, as described in this article.

To determine what setting is needed for specific frequency, requires to study the specific chipset datasheet.
For example, for the MSP430f2012 the datasheet has a table indicating what frequency, with quite a tolerance, correspond to different settings of some specific registers.

Looking at the table, the frequencies generated by the DCO have a quite big tolerance.
This why it could be necessary to re-calibrate the DCO values if a more precise frequency is needed.

Crystal Oscillators

As the name imply, these circuits allows to generate a frequency dependent by a crystal.
They are less flexible than the DCO but they can be very precise and stable.
At least one crystal oscillator is present on every MSP430 chipset.

Programming examples

Here some examples about how is possible to set up the MSP430 clock.
The examples are using the MSP430F2012.

Select DCO for ~8 MHz

After a reset, the main source for the clock, especially MCLK, is the DCO.
The default setting is for a middle/low frequency, around 800kHz (800kHz to 1.5 MHz), but the DCO can reach the 16 MHz in some chipsets.
Unless specific needs, is not a good idea to set the DCO for the maximum frequency.
Mainly because more fast the chip run, more current use and more heat is generated, but also because higher is the generated frequency, lower is the stability and precision.
Is a good idea to use a an external crystal if high speed and precision is required.

To determine what frequency to use it is mandatory to read the data sheet of the specific component, because each device has an internal table with some values to use for the DCO.

Said so, a typical DCO setting for about 8 Mhz is this one.

   DCOCTL = 0x60;   

   BCSCTL1 = 0xB3;

   BCSCTL2 = 0x00;

   BCSCTL3 = 0x0C;

The DCOCTL (Digitally Controlled Oscillator Control register) after a reset is set for:

• Frequency select middle range (value from 0 to 7, set to 3)
• Modulator section = 0

The setting to 0x60, according to the table above, set the frequency between 6 MHz to 9MHz.

The BCSCTL1 register (Basic Clock Control Register 1) after the reset has the value of 84 hex (1000 0100), i.e. :

• Crystal Oscillator 2 disabled (OFF)
• Crystal low frequency mode
• ACLK signal divided by 1 (i.e. no division)
• DCO Resistor select = 4 (nominal frequency for DCO)

After the selection (BCSCTL1 = 0xB3 - 1011 0011):

• Crystal Oscillator 2 disabled (OFF)
• Crystal low frequency mode
• ACLK signal divided by 8
• DCO Resistor select = 3

The BCSCTL2 after the reset is set to zero and no change is made, meaning:

• source for MCLK = DCO
• no divider for MCLK (divider = 1)
• source for SMCLK  = DCO
• no divider for SMCLK (divider = 1)
• use internal resistor for frequency selection

The BCSCTL3 after the reset is set to zero:

• Crystal oscillator 2 range 0 to 4 MHz (not used anyway)
• 3.2768 kHz crystal selected (not used anyway)
• 6 pf capacitor selected for crystal (not used anyway)

After the writing (0x0C - 0000 0011)

• Crystal oscillator 2 range 0 to 4 MHz (not used anyway)
• 3.2768 kHz crystal selected (not used anyway)
• 1 pf capacitor selected for crystal (not used anyway)

Note that the first two bits are only reading.

 Classic watch generator

When is necessary to count time for a clock, timer or watch, normally it is used a crystal of 32768-Hz (3.2768 kHz) because is easy to divide this frequency to obtain 1 second.
Of course we don't want the main clock system signal be so slow, this is the typical setting for the ACLK signal.
Let see what we need to be aware and what to do to have the basic clock set on that.
First of all some hardware is required.

1. connect the crystal
   A 32768-Hz crystal need to be connected to the chipset (pin XIN and XOUT)
   Usually this require also some capacitors other the crystal, but the chipset has them inside
2. setting the pins
   Because the crystal is physically connected to some chip pins, we need to program the I/O subsystem accordingly.
   i.e. we need to program the pins connected to the crystal to be rerouted to the clock subsystem.

By default, i.e. after a reset, assuming we are using an MSP430F2012, the XIN and XOUT pins are enabled, i.e. by default these pins are used for the crystal oscillator 1.

So no specific programming on the I/O subsystem is necessary to use a crystal.

Then the software.
We need to set up some chipset registers in order to have the clock up and running.

BCSCTL1 |= DIVA_3; /* ACLK/8 */

BCSCTL3 |= XCAP_3; /* 12.5pF cap */

Let see some details.
The BCSCTL1 register (Basic Clock Control Register 1) after the reset has the value of 84 hex (1000 0100), i.e. :

• Crystal Oscillator 2 disabled (OFF)
• Crystal low frequency mode
• ACLK signal divided by 1 (i.e. no division)
• Resistor select = 4 (nominal frequency for DCO)

So the register controls different parts of the clock subsystem.

The line BCSCTL1 |= DIVA_3; set only the DIVA bits, changing the original value from 0 to 3, setting the divisor of the ACLK signal to 8. i.e. the generated frequency of the ACLK will be divided by 8.  

The BCSCTL3 register after the reset has the value of 00x (the first two bits are to indicate some fault condition so they can have random setting after a reset).

i.e.:

• Range for Crystal oscillator 2 : 0 to 1 MHz crystal
• 3.2768 kHz crystal on oscillator 1
• 1 pf internal capacitor

The bit 2 and 3 select an internal capacitor to be used with an external crystal.

In our example, we use a 12.5 pF internal capacitor so the selection is 3 (11) for these bits, forced with the line : BCSCTL3 |= XCAP_3;

This is enough to have the ACLK signal generated.
The ACLK signal will have a 32768 Hz / 8 = 4096 Hz.

This value is easily used by the timer in order to generate precise and low periods.

Monitoring health data - an improvement

The purpose of the article is to start a discussion  about a "system" capable to store and retrieve/analyze health data.
More articles will follow on specific issues.  This article is the continuation of the previous articles "Monitoring health data - my way #1 and #2".

The use of the spreadsheet as main tool to collect data for now is preserved, because it is  easy to store the information in that way, but retrieve them in a meaningful way can become tricky.
This why I started to think a better way to do this "daily chore", in a way that will easily allow me to create better reports and analyze the data more deeply, creating also reports "on the fly".

The system

The system is based on a database that collect all the information and a set of macro/applications/programs to facilitate the data entry and the retrieval.
The database is based on mySQL.
Macro in the spreadhseet (Libreoffice Calc) allows to export the data stored in the SQL tables.

The database

The database is based on some tables that collects the measurements and other related values.
At the moment I created 4 tables:

• Measurement table
• Measurement type table
• Food table
• Food type table

Measurement tables

T_MES

These are the main tables used to store the health measurements.
The main table (T_MES) contains the actual readings, and the T_MES_TYPE contains specific information about the type of measurement.
Each measure is stamped with date and time, however some measurements will have a fake time.
For example the weight. Usually there is an entry per day, so the time is forced at 8:00 am regardless when actually was taken, because not always I can measure at the same time.

Let see in details the fields of T_MES:

• mesIndex
  Unique ID for the record, it is a MD5 Hash of the date, time and value measured
• dateMeasure
  It is the date of the measurement
• timeMeasure
  It is the time of the measurement. Weight and Ketons have a fake time entry. 8:00 am for Weight, 9:00 am for Ketons
• typeMeasure
  Foreign key for the table T_MES_TYPE
• noteMeasure
  Contains specific measurement note. The nature of the note depends about the measurement.
• valueMeasure
  Value of the measurement

The T_MES_TYPE table contains information about the measure.

• typeMeasure
  Unique ID for the record. Incremental number
• description
  A brief description of the measurement. Explains what is measured
• units
  Measurement unit of the measurement

Here the data to load in the T_MES_TYPE table :

typeMeasure code	units	description
1	ml/dg	Glycemy
2	lb	Weight
3	count	Carbs
4	mmHg	Blood pressure systolic
5	mmHg	Blood pressure diastolic
6	bpm	Heart beat (beat per minute)
7	count	Ketons
8	minutes	Exercise elliptical
9	miles	Exercise walking
10	miles	Exercise hiking

Here the mySQL commands to create the two tables :

create table if not exists t_mes (mesIndex varchar(40) not null primary key, dateMeasure date, timeMeasure time, typeMeasure numeric, valueMeasure numeric);

create table if not exists t_mes_type (typeMeasure numeric not null primary key, description varchar(20), unit varchar(10));

T_FOOD

The T_FOOD tables (T_FOOD and T_FOOD_TYPE) stores information about the food eaten.

Let see in details the fields of T_FOOD:

• foodIndex
  Unique ID for the record, it is a MD5 Hash of the date, time and value measured
• dateMeal
  It is the date of the meal
• typeMeal
  It is the time of the meal according to a fixed schedule.
  It is a fixed value like this :
• Breakfast
• Lunch
• Dinner
• Snack
• foodType
  Foreign key for the table T_FOOD_TYPE
• serving
  Number of serving consumed

The T_FOOD_TYPE table contains information about the food.

• foodType
  Unique ID for the record. Hash of the food name, brand and carbs
• name
  Name of the food
• servings
  Number of servings in a product (if applicable)
• servingUnits
  Measurement unit used for the servings
• carbs 
  Net Carbs in grams contained in 1 serving
• calories
  Calories contained in 1 serving
• protein
  Proteins in grams contained in 1 serving
• totalFat
  Total fat in grams contained in 1 serving
• sodium
  Salt in milligrams contained in 1 serving
• potassium
  Potassium in milligrams contained in 1 serving
• brand
  Name/description brand of the food. For vegetables/fruit use the word "raw".
• notes
  Notes about the food

Here the mySQL commands to create the two tables :

create table if not exists t_food (foodIndex varchar(40) not null primary key, dateMeal Date, typeMeal varchar(30), foodType varchar(40), serving Numeric);

create table if not exists t_food_type( foodType varchar(40) primary key not null, name varchar(40), servings Float, servingUnits varchar(15), carbs Float, calories Float, protein Float, totalFat Float, sodium Float, potassium Float, brand varchar(40), notes varchar(40));