1 #!/bin/bash
  2 
  3 # $Id: build-db.bash,v 1.6 2005/01/26 20:36:39 schwehr Exp $
  4 # Kurt Schwehr - Started 25-Jan-2005
  5 # This script will build the sqlite db for the 2004 Aug cruise to Santa Barbara
  6 
  7 DB=bpsio04.db
  8 
  9 #
 10 # Build the table for the core locations
 11 #
 12 if [ 0 == 1 ]; then
 13     # Make the table CoreLog
 14     sqlite ${DB} "create table CoreLoc (id INTEGER PRIMARY KEY, cruise VARCHAR(20), corenum INTEGER, coretype varchar(1), datecollected TIMESTAMP, lat REAL, lon REAL, depth REAL, sheetURL TEXT);"
 15 # 1
 16     sqlite ${DB} "insert into CoreLoc (cruise, corenum, coretype, datecollected, lat, lon, depth, sheetURL) values ('BPSIO04',1,'g','2004-08-04 21:09:15', 34.36116666, 120.108, 480, 'http://schwehr.org/Gaviota/bpsio-Aug04/bpsio-log/images/bpsio-log-35.jpg');"
 17 
 18 # 2  (time in UTC)
 19     sqlite ${DB} "insert into CoreLoc (cruise, corenum, coretype, datecollected, lat, lon, depth, sheetURL) values ('BPSIO04',2,'g','2004-08-04 23:06:23', 34.369999999999997, 120.1075, 439, 'http://schwehr.org/Gaviota/bpsio-Aug04/bpsio-log/images/bpsio-log-36.jpg');"
 20 
 21 # 3
 22     sqlite ${DB} "insert into CoreLoc (cruise, corenum, coretype, datecollected, lat, lon, depth, sheetURL) values ('BPSIO04',3,'g','2004-08-05 01:11:42', 34.365666666666669, 120.05883333333334, 452, 'http://schwehr.org/Gaviota/bpsio-Aug04/bpsio-log/images/bpsio-log-37.jpg');"
 23 
 24 
 25 # 4
 26     sqlite ${DB} "insert into CoreLoc (cruise, corenum, coretype, datecollected, lat, lon, depth, sheetURL) values ('BPSIO04',4,'g','2004-08-05 02:24:35', 34.378999999999998, 120.05716666666666, 322, 'http://schwehr.org/Gaviota/bpsio-Aug04/bpsio-log/images/bpsio-log-38.jpg');"
 27 
 28 
 29 # 5 
 30     sqlite ${DB} "insert into CoreLoc (cruise, corenum, coretype, datecollected, lat, lon, depth, sheetURL) values ('BPSIO04',5,'g','2004-08-05 16:10:00', 34.337049999999998, 120.02356666666667, 541, 'http://schwehr.org/Gaviota/bpsio-Aug04/bpsio-log/images/bpsio-log-42.jpg');"
 31 
 32 # 6
 33     sqlite ${DB} "insert into CoreLoc (cruise, corenum, coretype, datecollected, lat, lon, depth, sheetURL) values ('BPSIO04',6,'g','2004-08-05 17:51:30', 34.329983333333331, 119.9772333333333, 503, 'http://schwehr.org/Gaviota/bpsio-Aug04/bpsio-log/images/bpsio-log-43.jpg');"
 34 
 35 fi # End of build CoreLog table
 36 
 37 
 38 ######################################################################
 39 # Insert k15 data
 40 ######################################################################
 41 
 42 # I think people will hate us come to the end of this century since
 43 # we are using only 2 digits for the year.  NICE!
 44 
 45 # id            - Unique database id.  Has no meaning
 46 # samplename    - The user specified name in the k15 file
 47 # user          - Who did the measuring on the kappabridge
 48 # datemeasured  - Local time that the sample was measured on the KLY-2
 49 # cruise        - The cruise identifier (e.g. bp04 == bpsio-04)
 50 # corenum       - Number of the core for that cruise.  1 is the first core collected.
 51 # coretype      - g == gravity, m == multicore, b == boxcore, p == piston, t == trigger
 52 # corehalf      - w == working, a == archive half
 53 # section       - section 1 is at the top (closest to the water/bottom interface)
 54 # sectionoffset - cm offset down from the top of that section
 55 # counts        - This MAY be the average value read by the kappabridge in counts.  No range mult
 56 # sampleholder  - SI value of the sample holder
 57 # k1 .. k15   - The 15 Jelinek positions from kappabridge.  The raw data
 58 # s1 .. s6    - the results from k15_s
 59 # sigma       - AKA s[7] the sigma for the 6 s values
 60 
 61 if [ 1 == 1 ]; then
 62 
 63     if [ 1 == 1 ]; then 
 64 	sqlite ${DB} "create table ams (id INTEGER PRIMARY KEY, samplename VARCHAR(40), \
 65 user VARCHAR(40), datemeasured TIMESTAMP, cruise VARCHAR(20), \
 66 corenum INTEGER, coretype VARCHAR(1), corehalf VARCHAR(1), \
 67 section INTEGER, sectionoffset REAL, \
 68 counts INTEGER, sampleholder REAL,\
 69 k1 REAL, k2 REAL, k3 REAL, k4 REAL, k5 REAL, \
 70 k6 REAL, k7 REAL, k8 REAL, k9 REAL, k10 REAL, \
 71 k11 REAL, k12 REAL, k13 REAL, k14 REAL, k15 REAL, \
 72 s1 REAL, s2 REAL, s3 REAL, s4 REAL, s5 REAL, s6 REAL, sigma REAL \
 73 );"
 74     fi  # creat the ams table
 75 
 76 
 77     grep -v '#' bpsio04.k15 > tmp.15
 78 #grep -v '#' f.k15 > tmp.15
 79     split -l 4 tmp.15 tmpsamples_
 80 
 81     for file in tmpsamples_*; do
 82         # Talk about slow parsers!
 83 	line1=`head -1 $file`
 84 	line2=`head -2 $file | tail -1`
 85 	line3=`head -3 $file | tail -1`
 86 	line4=`tail -1 $file`
 87 	samplename=`echo $line1 | awk '{print$1}'`
 88 	user=`echo $line1 | awk '{print $2}'`  # Who took the measurement
 89 	date=`echo $line1 | awk '{print $3}'`
 90 	time=`echo $line1 | awk '{print $4}'`
 91 	ampm=`echo $line1 | awk '{print $5}'`
 92 	counts=`echo $line1 | awk '{print $6}'`
 93 	sampleholder=`echo $line1 | awk '{print $7}'`
 94 
 95 	sqldate="`./date-reformat.bash $date $time $ampm`"
 96 	#sqldate="'$sqldate'"
 97 	#echo "sqldate = $sqldate"
 98 
 99 	cruise=`echo $samplename | cut -d- -f1`
100 	core=`echo $samplename | cut -d- -f2`
101 	section=`echo $samplename | cut -d- -f3`
102 	section=`echo $section | cut -c2-`
103 	sectionoffset=`echo $samplename | cut -d- -f4`
104 
105 	corenum=`echo $core|cut -c1`
106 	coretype=`echo $core|cut -c2`
107 	corehalf=`echo $core|cut -c3`
108 
109   # FIX: this should be an array...
110 	k1=`echo $line2 | awk '{print $1}'`; k1=`printf "%f" $k1`
111 	#k1=`python -c "print $k1"`
112 	echo k1 = $k1
113 	k2=`echo $line2 | awk '{print $2}'`; k2=`printf "%f" $k2`
114 	k3=`echo $line2 | awk '{print $3}'`; k3=`printf "%f" $k3`
115 	k4=`echo $line2 | awk '{print $4}'`; k4=`printf "%f" $k4`
116 	k5=`echo $line2 | awk '{print $5}'`; k5=`printf "%f" $k5`
117 
118 	k6=`echo $line3 | awk '{print $1}'`; k6=`printf "%f" $k6`
119 	k7=`echo $line3 | awk '{print $2}'`; k7=`printf "%f" $k7`
120 	k8=`echo $line3 | awk '{print $3}'`; k8=`printf "%f" $k8`
121 	k9=`echo $line3 | awk '{print $4}'`; k9=`printf "%f" $k9`
122 	k10=`echo $line3 | awk '{print $5}'`; k10=`printf "%f" $k10`
123 
124 	k11=`echo $line4 | awk '{print $1}'`; k11=`printf "%f" $k11`
125 	k12=`echo $line4 | awk '{print $2}'`; k12=`printf "%f" $k12`
126 	k13=`echo $line4 | awk '{print $3}'`; k13=`printf "%f" $k13`
127 	k14=`echo $line4 | awk '{print $4}'`; k14=`printf "%f" $k14`
128 	k15=`echo $line4 | awk '{print $5}'`; k15=`printf "%f" $k15`
129 
130   # FIX: convert date to yyyy-mm-dd
131   # FIX: convert time  + am/pm to 24 hour - separate program?
132   # FIX: handle sectionoffset LR Ids for left right samples
133 
134 	# printf to change -.1234 to -0.1234
135 	#s7values=`k15_s < $file | tr ' ' ','`
136 	s7values=`k15_s < $file`
137 	s7values=`printf "%f,%f,%f,%f,%f,%f,%f" $s7values`
138 
139 	echo
140 	echo LINE1: $line1
141 	echo LINE2: $line2
142 	echo LINE3: $line3
143 	echo LINE4: $line4
144 	if [ 1 == 0 ]; then
145 	    echo samplename $samplename
146 	    echo user $user
147 	    echo date $date
148 	    echo time $time
149 	    echo ampm $ampm
150 	    echo counts $counts
151 	    echo sampleholder $sampleholder
152 	    echo sqldate $sqldate
153 
154 	    echo "corenum  = $corenum"
155 	    echo "coretype = $coretype"
156 	    echo "corehalf = $corehalf"
157 	    echo "cruise   = $cruise"
158 	    echo "core     = $core"
159 	    echo "section  = $section"
160 	    echo "sectionoffset = $sectionoffset"
161 
162 	    if [ 1 == 1 ]; then
163 		echo k1 = $k1
164 		echo k2 = $k2
165 		echo k3 = $k1
166 		echo k4 = $k4
167 		echo k5 = $k5
168 
169 		echo k6 = $k6
170 		echo k7 = $k7
171 		echo k8 = $k8
172 		echo k9 = $k9
173 		echo k10 = $k10
174 
175 		echo k11 = $k11
176 		echo k12 = $k12
177 		echo k13 = $k13
178 		echo k14 = $k14
179 		echo k15 = $k15
180 	    fi
181 	    echo s7values = $s7values
182 	fi # End of debugging prints
183 
184   # FIX: wouldn't it be nice to have some error checking on values?
185 
186  	sqlite ${DB} "insert into ams \
187 (samplename, user, datemeasured, cruise, corenum, coretype, corehalf, section, sectionoffset, \
188 counts, sampleholder, \
189 k1 , k2, k3, k4, k5, k6, k7, k8, k9, k10, k11, k12, k13, k14, k15, \
190 s1, s2, s3, s4, s5, s6, sigma) \
191 values \
192 ('$samplename', '$user', '$sqldate', '$cruise', $corenum, '$coretype', '$corehalf', $section, $sectionoffset,\
193 $counts, $sampleholder,\
194 $k1 , $k2, $k3, $k4, $k5, $k6, $k7, $k8, $k9, $k10, $k11, $k12, $k13, $k14, $k15, \
195 $s7values \
196 ); \
197 "
198 
199     done
200 
201 fi # END OF AMS/k15 SECTION
202 
203 
204 rm -f tmpsamples_*


syntax highlighted by Code2HTML, v. 0.9.1