Added scripts for updating database with USDA Standard Reference update files

6 files changed, 579 insertions, 0 deletions

diff --git a/db_update_scripts/config.php b/db_update_scripts/config.php
new file mode 100755
index 0000000..da259ce
--- /dev/null
+++ b/db_update_scripts/config.php
@@ -0,0 +1,37 @@
+<?php
+
+/**
+ * Copyright (c) 2007 Nathan Kinkade
+ * 
+ * This code is offered under an MIT (X11) license.  For more information
+ * about the terms of this license see the file LICENSE included with this
+ * software or visit: http://www.opensource.org/licenses/mit-license.php
+ */
+
+# Directory where USDA update files are located
+$updatesDir = "./sr20_updates";
+
+# Where to log errors and stats
+$fh_log = fopen("./sr20_updates.log", "a");
+
+# Fields are delimited with this character
+$delimiter = "^";
+
+# Fields are optionally enclosed between this character
+$enclosure = "~";
+
+# site constants that don't need to be interpolated in strings and/or
+# are more sensitive will be setup as constants
+define("DBHOST", "localhost"); # database host
+define("DBNAME", "nutridb_sr20"); # database name
+define("DBUSER", "root"); # database user
+define("DBPASS", ""); # database password
+
+define("ADODBDIR", "../lib/adodb"); # adodb db abastractions libs - adodb.sourceforge.net
+
+require("../lib/database.class.php"); # database class  
+
+# instantiate the database object
+$db = new Database();
+
+?>
diff --git a/db_update_scripts/update_ALL.sh b/db_update_scripts/update_ALL.sh
new file mode 100755
index 0000000..3203ff1
--- /dev/null
+++ b/db_update_scripts/update_ALL.sh
@@ -0,0 +1,6 @@
+#!/bin/bash
+
+./update_foodDescs.php
+./update_footnotes.php
+./update_nutrientData.php
+./update_weights.php
diff --git a/db_update_scripts/update_foodDescs.php b/db_update_scripts/update_foodDescs.php
new file mode 100755
index 0000000..09a40b6
--- /dev/null
+++ b/db_update_scripts/update_foodDescs.php
@@ -0,0 +1,136 @@
+#!/usr/bin/php
+
+<?php
+
+/**
+ * Script to update nutridb table foodDescs which corresponds
+ * to USDA file FOOD_DES, which corresponds to update files
+ * DEL_FOOD.txt and ADD_FOOD.txt and CHG_FOOD.txt.
+ *
+ * NOTE: This script relies on the fact that updates files
+ * are named as they were from the SR19 -> SR20 update.
+ */
+ 
+require("config.php");
+
+$fh_del = fopen("{$updatesDir}/DEL_FOOD.txt", "r");
+$fh_add = fopen("{$updatesDir}/ADD_FOOD.txt", "r");
+$fh_chg = fopen("{$updatesDir}/CHG_FOOD.txt", "r");
+
+# First handle deletions
+fwrite($fh_log, "### PROCESSING DEL_FOOD.txt ###\n");
+$idx = 0;
+while ( ($row = fgetcsv($fh_del, 0, $delimiter, $enclosure)) !== FALSE ) {
+	$sql = sprintf("
+		UPDATE foodDescs
+		SET usda_status = 'deleted'
+		WHERE ndb_no = '%s'
+		",
+		$row[0]
+	);
+	$db->Modify($sql);
+	if ( $db->_error ) {
+		fwrite($fh_log, "\tERROR deactivating ndb_no {$row[0]}: $db->_error\n");
+	} elseif ( $db->_affectedRows == 0 ) {
+		fwrite($fh_log, "\tWARNING: ndb_no {$row[0]} was not found in the database.\n");
+	} else {
+		$idx++;
+	}
+}
+fwrite($fh_log, "\tDeactivated $idx records from table foodDescs.\n");
+
+# Now handle additions
+fwrite($fh_log, "### PROCESSING ADD_FOOD.txt ###\n");
+$idx = 0;
+while ( ($row = fgetcsv($fh_add, 0, $delimiter, $enclosure)) !== FALSE ) {
+	# Don't add the record if this ndb_no already exists
+	$sql = sprintf("
+		SELECT ndb_no FROM foodDescs
+		WHERE ndb_no = '%s'
+		",
+		$row[0]
+	);
+	$db->Select($sql);
+	if ( $db->_rowCount != 0 ) {
+		fwrite($fh_log, "\tWARNING not adding ndb_no {$row[0]} because it already exists.\n");
+		continue;
+	}
+	$sql = sprintf("
+		INSERT INTO foodDescs(
+			ndb_no, fdgrp_cd, long_desc, shrt_desc, comname, manufacname, survey,
+			ref_desc, refuse, sciname, n_factor, pro_factor, fat_factor, cho_factor
+		)
+		VALUES('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')
+		",
+		$row[0],
+		$row[1],
+		addslashes($row[2]),
+		addslashes($row[3]),
+		addslashes($row[4]),
+		addslashes($row[5]),
+		$row[6],
+		addslashes($row[7]),
+		$row[8],
+		addslashes($row[9]),
+		$row[10],
+		$row[11],
+		$row[12],
+		$row[13]
+	);
+	$db->Modify($sql);
+	if ( $db->_error ) {
+		fwrite($fh_log, "\tERROR adding ndb_no {$row[0]}: $db->_error\n");
+	} else {
+		$idx++;
+	}
+}
+fwrite($fh_log, "\tAdded $idx records to table foodDescs.\n");
+
+# Now make updates
+fwrite($fh_log, "### PROCESSING CHG_FOOD.txt ###\n");
+$idx = 0;
+while ( ($row = fgetcsv($fh_chg, 0, $delimiter, $enclosure)) !== FALSE ) {
+	$sql = sprintf("
+		UPDATE foodDescs set
+			fdgrp_cd = '%s',
+			long_desc = '%s',
+			shrt_desc = '%s',
+			comname = '%s',
+			manufacname = '%s',
+			survey = '%s',
+			ref_desc = '%s',
+			refuse = '%s',
+			sciname = '%s',
+			n_factor = '%s',
+			pro_factor = '%s',
+			fat_factor = '%s',
+			cho_factor = '%s'
+		WHERE ndb_no = '%s'
+		",
+		$row[1],
+		addslashes($row[2]),
+		addslashes($row[3]),
+		addslashes($row[4]),
+		addslashes($row[5]),
+		$row[6],
+		addslashes($row[7]),
+		$row[8],
+		addslashes($row[9]),
+		$row[10],
+		$row[11],
+		$row[12],
+		$row[13],
+		$row[0]
+	);
+	$db->Modify($sql);
+	if ( $db->_error ) {
+		fwrite($fh_log, "\tERROR modifying ndb_no {$row[0]}: $db->_error\n");
+	} elseif ( $db->_affectedRows != 1 ) {
+		fwrite($fh_log, "\tWARNING: nothing modified for ndb_no {$row[0]}.\n");
+	} else {
+		$idx++;
+	}
+}
+fwrite($fh_log, "\tUpdated $idx records in table foodDescs.\n");
+
+?>
diff --git a/db_update_scripts/update_footnotes.php b/db_update_scripts/update_footnotes.php
new file mode 100755
index 0000000..57de2c2
--- /dev/null
+++ b/db_update_scripts/update_footnotes.php
@@ -0,0 +1,118 @@
+#!/usr/bin/php
+
+<?php
+
+/**
+ * Script to update nutridb table footnotes which corresponds
+ * to USDA file FOOTNOTE, which corresponds to update files
+ * DEL_FTNT.txt and ADD_FTNT.txt and CHG_FTNT.txt.
+ *
+ * NOTE: This script relies on the fact that updates files
+ * are named as they were from the SR19 -> SR20 update.
+ */
+
+require("config.php");
+
+$fh_del = fopen("{$updatesDir}/DEL_FTNT.txt", "r");
+$fh_add = fopen("{$updatesDir}/ADD_FTNT.txt", "r");
+$fh_chg = fopen("{$updatesDir}/CHG_FTNT.txt", "r");
+
+# First handle deletions
+fwrite($fh_log, "### PROCESSING DEL_FTNT.txt ###\n");
+$idx = 0;
+while ( ($row = fgetcsv($fh_del, 0, $delimiter, $enclosure)) !== FALSE ) {
+	$sql = sprintf("
+		UPDATE footnotes
+		SET usda_status = 'deleted'
+		WHERE ndb_no = '%s'
+			AND footnt_no = '%s'
+			AND footnt_typ = '%s'
+		",
+		$row[0],
+		$row[1],
+		$row[2]
+	);
+	$db->Modify($sql);
+	if ( $db->_error ) {
+		fwrite($fh_log, "\tERROR: deactivating ndb_no {$row[0]}: $db->_error\n");
+	} elseif ( $db->_affectedRows == 0 ) {
+		fwrite($fh_log, "\tWARNING: ndb_no {$row[0]} was not found in the database.\n");
+	} else {
+		$idx++;
+	}
+}
+fwrite($fh_log, "\tDeactivated $idx records from table footnotes.\n");
+
+# Now handle additions
+fwrite($fh_log, "### PROCESSING ADD_FTNT.txt ###\n");
+$idx = 0;
+while ( ($row = fgetcsv($fh_add, 0, $delimiter, $enclosure)) !== FALSE ) {
+	# Don't add the record if this ndb_no already exists
+	$sql = sprintf("
+		SELECT ndb_no FROM footnotes
+		WHERE ndb_no = '%s'
+			AND footnt_no = '%s'
+		",
+		$row[0],
+		$row[1]
+	);
+	$db->Select($sql);
+	if ( $db->_rowCount != 0 ) {
+		fwrite($fh_log, "\tWARNING not adding footnote for ndb_no {$row[0]} because it already exists\n");
+		continue;
+	}
+	$sql = sprintf("
+		INSERT INTO footnotes(ndb_no, footnt_no, footnt_typ, nutr_no, footnt_txt)
+		VALUES('%s','%s','%s','%s','%s')
+		",
+		$row[0],
+		$row[1],
+		$row[2],
+		$row[3],
+		addslashes($row[4])
+	);
+	$db->Modify($sql);
+	if ( $db->_error ) {
+		fwrite($fh_log, "\tERROR adding footnote for ndb_no {$row[0]}: $db->_error\n");
+	} else {
+		$idx++;
+	}
+}
+fwrite($fh_log, "\tAdded $idx records to table footnotes\n");
+
+# Now make updates
+fwrite($fh_log, "### PROCESSING CHG_FTNT.txt ###\n");
+$idx = 0;
+while ( ($row = fgetcsv($fh_chg, 0, $delimiter, $enclosure)) !== FALSE ) {
+	$sql = sprintf("
+		UPDATE footnotes set
+			ndb_no = '%s',
+			footnt_no = '%s',
+			footnt_typ = '%s',
+			nutr_no = '%s',
+			footnt_txt = '%s'
+		WHERE ndb_no = '%s'
+			AND footnt_no = '%s'
+			AND footnt_typ = '%s'
+		",
+		$row[0],
+		$row[1],
+		$row[2],
+		$row[3],
+		addslashes($row[4]),
+		$row[0],
+		$row[1],
+		$row[2]
+	);
+	$db->Modify($sql);
+	if ( $db->_error ) {
+		fwrite($fh_log, "\tERROR modifying ndb_no {$row[0]}: $db->_error\n");
+	} elseif ( $db->_affectedRows != 1 ) {
+		fwrite($fh_log, "\tWARNING: nothing modified for ndb_no {$row[0]}.\n");
+	} else {
+		$idx++;
+	}
+}
+fwrite($fh_log, "\tUpdated $idx records in table footnotes.\n");
+
+?>
diff --git a/db_update_scripts/update_nutrientData.php b/db_update_scripts/update_nutrientData.php
new file mode 100755
index 0000000..e4c81a7
--- /dev/null
+++ b/db_update_scripts/update_nutrientData.php
@@ -0,0 +1,154 @@
+#!/usr/bin/php
+
+<?php
+
+/**
+ * Script to update nutridb table nutrientData which corresponds
+ * to USDA file NUT_DATA, which corresponds to update files
+ * DEL_NUTR.txt and ADD_NUTR.txt and CHG_NUTR.txt.
+ *
+ * NOTE: This script relies on the fact that updates files
+ * are named as they were from the SR19 -> SR20 update.
+ */
+
+require("config.php");
+
+$fh_del = fopen("{$updatesDir}/DEL_NUTR.txt", "r");
+$fh_add = fopen("{$updatesDir}/ADD_NUTR.txt", "r");
+$fh_chg = fopen("{$updatesDir}/CHG_NUTR.txt", "r");
+
+# First handle deletions
+fwrite($fh_log, "### PROCESSING DEL_NUTR.txt ###\n");
+$idx = 0;
+while ( ($row = fgetcsv($fh_del, 0, $delimiter, $enclosure)) !== FALSE ) {
+	$sql = sprintf("
+		UPDATE nutrientData
+		SET usda_status = 'deleted'
+		WHERE ndb_no = '%s'
+			AND nutr_no = '%s'
+		",
+		$row[0],
+		$row[1]
+	);
+	$db->Modify($sql);
+	if ( $db->_error ) {
+		fwrite($fh_log, "\tERROR: deactivating ndb_no {$row[0]}: $db->_error\n");
+	} elseif ( $db->_affectedRows == 0 ) {
+		fwrite($fh_log, "\tWARNING: ndb_no {$row[0]} was not found in the database.\n");
+	} else {
+		$idx++;
+	}
+}
+fwrite($fh_log, "\tDeactivated $idx records from table nutrientData.\n");
+
+# Now handle additions
+fwrite($fh_log, "### PROCESSING ADD_NUTR.txt ###\n");
+$idx = 0;
+while ( ($row = fgetcsv($fh_add, 0, $delimiter, $enclosure)) !== FALSE ) {
+	# Don't add the record if this ndb_no already exists
+	$sql = sprintf("
+		SELECT ndb_no FROM nutrientData
+		WHERE ndb_no = '%s'
+			AND nutr_no = '%s'
+		",
+		$row[0],
+		$row[1]
+	);
+	$db->Select($sql);
+	if ( $db->_rowCount != 0 ) {
+		fwrite($fh_log, "\tWARNING not adding ndb_no {$row[0]} because it already exists.\n");
+		continue;
+	}
+
+	$sql = sprintf("
+		INSERT INTO nutrientData(
+			ndb_no, nutr_no, nutr_val, num_data_pts, std_error, src_cd, deriv_cd, ref_ndb_no,
+			add_nutr_mark, num_studies, min, max, df, low_eb, up_eb, stat_cmt, cc
+		)
+		VALUES('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')
+		",
+		$row[0],
+		$row[1],
+		$row[2],
+		$row[3],
+		$row[4],
+		$row[5],
+		$row[6],
+		$row[7],
+		$row[8],
+		$row[9],
+		$row[10],
+		$row[11],
+		$row[12],
+		$row[13],
+		$row[14],
+		addslashes($row[15]),
+		$row[16]
+	);
+	$db->Modify($sql);
+	if ( $db->_error ) {
+		fwrite($fh_log, "\tERROR adding ndb_no {$row[0]}: $db->_error\n");
+	} else {
+		$idx++;
+	}
+}
+fwrite($fh_log, "\tAdded $idx records to table nutrientData.\n");
+
+# Now make updates
+fwrite($fh_log, "### PROCESSING CHG_NUTR.txt ###\n");
+$idx = 0;
+while ( ($row = fgetcsv($fh_chg, 0, $delimiter, $enclosure)) !== FALSE ) {
+	$sql = sprintf("
+		UPDATE nutrientData set
+			ndb_no = '%s',
+			nutr_no = '%s',
+			nutr_val = '%s',
+			num_data_pts = '%s',
+			std_error = '%s',
+			src_cd = '%s',
+			deriv_cd = '%s',
+			ref_ndb_no = '%s',
+			add_nutr_mark = '%s',
+			num_studies = '%s',
+			min = '%s',
+			max = '%s',
+			df = '%s',
+			low_eb = '%s',
+			up_eb = '%s',
+			stat_cmt = '%s',
+			cc = '%s'
+		WHERE ndb_no = '%s'
+			and nutr_no = '%s'
+		",
+		$row[0],
+		$row[1],
+		$row[2],
+		$row[3],
+		$row[4],
+		$row[5],
+		$row[6],
+		$row[7],
+		$row[8],
+		$row[9],
+		$row[10],
+		$row[11],
+		$row[12],
+		$row[13],
+		$row[14],
+		addslashes($row[15]),
+		$row[16],
+		$row[0],
+		$row[1]
+	);
+	$db->Modify($sql);
+	if ( $db->_error ) {
+		fwrite($fh_log, "\tERROR modifying ndb_no {$row[0]}: $db->_error\n");
+	} elseif ( $db->_affectedRows != 1 ) {
+		fwrite($fh_log, "\tWARNING: nothing modified for ndb_no {$row[0]}.\n");
+	} else {
+		$idx++;
+	}
+}
+fwrite($fh_log, "\tUpdated $idx records in table nutrientData.\n");
+
+?>
diff --git a/db_update_scripts/update_weights.php b/db_update_scripts/update_weights.php
new file mode 100755
index 0000000..e1429fd
--- /dev/null
+++ b/db_update_scripts/update_weights.php
@@ -0,0 +1,128 @@
+#!/usr/bin/php
+
+<?php
+
+/**
+ * Script to update nutridb table footnotes which corresponds
+ * to USDA file FOOTNOTE, which corresponds to update files
+ * DEL_FTNT.txt and ADD_FTNT.txt and CHG_FTNT.txt.
+ *
+ * NOTE: This script relies on the fact that updates files
+ * are named as they were from the SR19 -> SR20 update.
+ */
+
+require("config.php");
+
+$fh_del = fopen("{$updatesDir}/DEL_WGT.txt", "r");
+$fh_add = fopen("{$updatesDir}/ADD_WGT.txt", "r");
+$fh_chg = fopen("{$updatesDir}/CHG_WGT.txt", "r");
+
+# First handle deletions
+fwrite($fh_log, "### PROCESSING DEL_WGT.txt ###\n");
+$idx = 0;
+while ( ($row = fgetcsv($fh_del, 0, $delimiter, $enclosure)) !== FALSE ) {
+	$sql = sprintf("
+		UPDATE weights
+		SET usda_status = 'deleted'
+		WHERE ndb_no = '%s'
+			AND seq = '%s'
+		",
+		$row[0],
+		$row[1]
+	);
+	$db->Modify($sql);
+	if ( $db->_error ) {
+		fwrite($fh_log, "\tERROR deactivating ndb_no {$row[0]}: $db->_error\n");
+	} elseif ( $db->_affectedRows == 0 ) {
+		fwrite($fh_log, "\tWARNING: ndb_no {$row[0]} was not found in the database.\n");
+	} else {
+		$idx++;
+	}
+}
+fwrite($fh_log, "\tDeactivated $idx records from table weights.\n");
+
+# Now handle additions
+fwrite($fh_log, "### PROCESSING ADD_WGT.txt ###\n");
+$idx = 0;
+while ( ($row = fgetcsv($fh_add, 0, $delimiter, $enclosure)) !== FALSE ) {
+	# Don't add the record if this ndb_no already exists
+	$sql = sprintf("
+		SELECT ndb_no FROM weights
+		WHERE ndb_no = '%s'
+			AND seq = '%s'
+			AND amount = '%s'
+			AND msre_desc = '%s'
+			AND gm_wgt = '%s'
+		",
+		$row[0],
+		$row[1],
+		$row[2],
+		$row[3],
+		$row[4]
+	);
+	$db->Select($sql);
+	if ( $db->_rowCount != 0 ) {
+		fwrite($fh_log, "\tWARNING not adding weight for ndb_no {$row[0]} because it already exists\n");
+		continue;
+	}
+
+	$sql = sprintf("
+		INSERT INTO weights(ndb_no, seq, amount, msre_desc, gm_wgt, num_data_pts, std_dev)
+		VALUES('%s','%s','%s','%s','%s','%s','%s')
+		",
+		$row[0],
+		$row[1],
+		$row[2],
+		addslashes($row[3]),
+		$row[4],
+		$row[5],
+		$row[6]
+	);
+	$db->Modify($sql);
+	if ( $db->_error ) {
+		fwrite($fh_log, "\tERROR adding footnote for ndb_no {$row[0]}: $db->_error\n");
+	} else {
+		$idx++;
+	}
+}
+fwrite($fh_log, "\tAdded $idx records to table footnotes\n");
+
+# Now make updates
+fwrite($fh_log, "### PROCESSING CHG_WGT.txt ###\n");
+$idx = 0;
+while ( ($row = fgetcsv($fh_chg, 0, $delimiter, $enclosure)) !== FALSE ) {
+	$sql = sprintf("
+		UPDATE weights
+		SET
+			ndb_no = '%s',
+			seq = '%s',
+			amount = '%s',
+			msre_desc = '%s',
+			gm_wgt = '%s',
+			num_data_pts = '%s',
+			std_dev = '%s'
+		WHERE ndb_no = '%s'
+			AND seq = '%s'
+		",
+		$row[0],
+		$row[1],
+		$row[2],
+		addslashes($row[3]),
+		$row[4],
+		$row[5],
+		$row[6],
+		$row[0],
+		$row[1]
+	);
+	$db->Modify($sql);
+	if ( $db->_error ) {
+		fwrite($fh_log, "\tERROR modifying ndb_no {$row[0]}: $db->_error\n");
+	} elseif ( $db->_affectedRows != 1 ) {
+		fwrite($fh_log, "\tWARNING: nothing modified for ndb_no {$row[0]}.\n");
+	} else {
+		$idx++;
+	}
+}
+fwrite($fh_log, "\tUpdated $idx records in table weights.\n");
+
+?>