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