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.