#!/bin/sh

# This is a script to create a MySQL DB from the .txt files
# in <http://www.w3.org/2008/04/DiabeticPatientsDataSet/> .
# $Id: createDB,v 1.2 2010/05/17 07:11:55 eric Exp $

CREDS=${CREDS:-'-u root'}

# convert to CSV by
#if [ 0 -eq 1 ]; then
for f in MockItem_Medication.txt	MockMedication.txt  MockPerson.txt; do s=`echo $f | sed 's/\.txt//1'` && perl -pe '
  # convert (oddly unquoted) dates to ISO8601 and quote them.
  s|(\d+)/(\d+)/(\d+) ([0-9:]+)|"\3-\1-\2T\4"|g;
  # normalize the boolean values
  s|"Y"|"1"|g;s|"N"|"0"|g;
  # remove leading and trailing spaces
  s|" +|"|g;s| +"|"|g;
  # use unix line delimiters
  s|\r\n|\n|;
' < $s.txt > $s.csv; done
#fi

mysqladmin $CREDS -f drop DiabeticPatientsDataSet;
mysqladmin $CREDS create DiabeticPatientsDataSet;
mysql $CREDS DiabeticPatientsDataSet < CreateDB.sql