Context Navigation

source: trunk/htdocs/perl/find_archives.pl @ 208

Last change on this file since 208 was 208, checked in by MatthewWhiting, 16 years ago
Working find_archives.pl script (removed the temporary new_find_archives one). Renamed the old observations and cals tables in the DB, so changed names in the sql scripts. Changed the ordering key in the new tables, so that we can have more than one distinct entry with the same path/filename
Property svn:executable set to ``*
File size: 27.4 KB

Line
1	#! /usr/bin/perl -w
2
3	# find_archives.pl
4	# Author: Matthew Whiting
5	# Date: January 2008
6	# ATNF, CSIRO
7
8	# A modified version of Albert's find_archives.pl that hopefully works
9	# a bit more consistently for the different data types.
10
11	# Crawls through data archive directories (given as an input parameter),
12	# searches for all observation and/or calibration files, enquires about
13	# their pulsar parameters and inputs these details as well as their
14	# location into a mySQL database
15
16	use strict;
17	use lib "/DATA/SITAR_1/whi550/VO/PulsarVO/SQL-Abstract-1.22/blib/lib";
18	use File::Find ();
19	use DBI qw(:sql_types);
20	use SQL::Abstract;
21	use POSIX;
22
23	# foreach (@{ $DBI::EXPORT_TAGS{sql_types} }) {
24	# printf "%s=%d\n", $_, &{"DBI::$_"};
25	# }
26
27	# for the convenience of &wanted calls, including -eval statements:
28	use vars qw/name dir *prune @archive_dirs $archive_dir @files
29	$vap_cmd $vap_out $params $num_vap_params $db_handle
30	$filename $raj $decj $dm $period $bw $cnfg
31	$freq $inst $mjdint $mjdfrac $npol $nchan $nbin $nsub
32	$rcvr $site $length $obsrvr $data_loc $data_type
33	$file_size $MJD $raH $raM $raS $decD $decM $decS $rajd $decjd $decstring
34	$pi $pion180 $NGP_RA $NGP_DEC $ASC_NODE $draR $decR
35	$sinb $sinl $cosl $gb $gl $gl_raw
36	$bmaj $bmin $bpa $hdrver $survey $nbeam
37	$output $vap $archive_extensions $ext
38	$default
39	@result_params $FILE_SIZE_INDEX $FB $DFB $WBC $CPSR2 $NULLFIELD
40	$params_NAME
41	$params_PROJID
42	$params_RAJ
43	$params_DECJ
44	$params_FREQ
45	$params_BW
46	$params_LENGTH
47	$params_DATE
48	$params_TIME
49	$params_MJD
50	$params_BMAJ
51	$params_BMIN
52	$params_BPA
53	$params_DM
54	$params_PERIOD
55	$params_NCHAN
56	$params_NPOL
57	$params_NBIN
58	$params_NSUB
59	$params_NBITS
60	$params_TSAMP
61	$params_NBEAM
62	$params_CNFG
63	$params_INST
64	$params_RCVR
65	$params_HDRVER
66	$params_TELESCOP
67	$params_SITE
68	$params_OBSRVR
69	$params_FILENAME
70	/; # $machine
71
72	name = File::Find::name;
73	dir = File::Find::dir;
74	prune = File::Find::prune;
75
76	# The data types
77	$FB = "FB";
78	$WBC = "WBC";
79	$DFB = "DFB";
80	$CPSR2 = "CPSR2";
81
82	# filename extensions
83	$archive_extensions = "rf cf cfb fb";
84
85	# How to write NULL fields to the database
86	#$NULLFIELD = undef;
87	$NULLFIELD = "NULL";
88
89	# Pulsar parameters (listed in "vap -H")
90	$params = "name projid ra dec freq bw length stt_date stt_time mjd bmaj bmin bpa dm period nchan npol nbin nsub nbits tsamp nbeam beconfig backend rcvr hdrver telescop asite observer";
91
92	# The location of vap
93	$vap = "/pulsar/psr/linux/bin/vap -n";
94
95	# vap result parameter indices. Used to locate the resulting
96	# parameter's location
97	# Note that we start at 1, since the vap output has the filename in the first position
98	$params_NAME = 1;
99	$params_PROJID = 2;
100	$params_RAJ = 3;
101	$params_DECJ = 4;
102	$params_FREQ = 5;
103	$params_BW = 6;
104	$params_LENGTH = 7;
105	$params_DATE = 8;
106	$params_TIME = 9;
107	$params_MJD = 10;
108	$params_BMAJ = 11;
109	$params_BMIN = 12;
110	$params_BPA = 13;
111	$params_DM = 14;
112	$params_PERIOD = 15;
113	$params_NCHAN = 16;
114	$params_NPOL = 17;
115	$params_NBIN = 18;
116	$params_NSUB = 19;
117	$params_NBITS = 20;
118	$params_TSAMP = 21;
119	$params_NBEAM = 22;
120	$params_CNFG = 23;
121	$params_INST = 24;
122	$params_RCVR = 25;
123	$params_HDRVER = 26;
124	$params_TELESCOP= 27;
125	$params_SITE = 28;
126	$params_OBSRVR = 29;
127	$params_FILENAME= 30;
128
129
130	#####################
131	# Begin main method
132	#
133	my @vap_params = split(/ /, $params);
134	$num_vap_params = scalar(@vap_params);
135
136	# The column number (starting from 0) of the file
137	# size when calling ls -l
138	$FILE_SIZE_INDEX = 4;
139
140	# Use the default operation which is to firstly delete any
141	# stale records, then crawl through and insert new records
142	$default = 1;
143
144	print "ARGV = @ARGV\n";
145
146	if (scalar(@ARGV) == 0) {
147	print "find_archives.pl: A crawler that populates a relational database\n
148	with indexes to observational data along with their respective\n
149	cal files.\n\n
150	Usage: find_archives.pl [options] directory1 [directory2 ...]\n\n
151	If no options are provided, it will default to do both -d and -p\n
152	Options:\n
153	\t-d Delete stale records\n
154	\t-p Populate database with new records\n";
155
156	}
157
158	# Connect to the database
159	$db_handle = connectdb();
160
161	#########################################################
162	# Step 1. First remove any stale entries in the database
163	# (remove file locations that no longer exist)
164	#########################################################
165
166	if ($ARGV[0] eq "-d") {
167	# deleteStaleRecords();
168	$default = 0;
169	shift @ARGV;
170	}
171
172
173	#########################################################
174	# Step 2. Crawl through each input directory and insert
175	# any new archives
176
177	if ($ARGV[0] eq "-p") {
178	shift @ARGV;
179	@archive_dirs = @ARGV;
180
181	foreach $archive_dir (@archive_dirs) {
182
183	# Traverse desired filesystems
184	File::Find::find({wanted => \&wanted}, $archive_dir);
185	}
186	$default = 0;
187	}
188
189	# Else do both
190	if ($default) {
191
192	# deleteStaleRecords();
193
194	@archive_dirs = @ARGV;
195
196	foreach $archive_dir (@archive_dirs) {
197
198	# Check that the input directory is legit
199
200	# Traverse desired filesystems
201	File::Find::find({wanted => \&wanted}, $archive_dir);
202	}
203
204	}
205	#if ($db_handle->disconnect()) {
206	# print "Successfully Disconnected from database\n";
207	#}
208	#else {
209	# print "Error: Failed to disconnect from the database\n";
210	# exit;
211	#}
212
213	######################################################################
214
215	######################################################################
216	# Subroutine definitions
217	#
218	######################################################################
219
220	#######################################################
221
222	sub wanted {
223
224
225	if (-d and /^[a-zA-Z]{0,1}[0-9]{4}[+-][0-9]{4}.*/s ) {
226
227	if ($File::Find::name !~ /.*S$/) { # ignore scrunch directories
228
229	print "\n\nIn directory $_\n\n";
230	print "\$File::Find::name = $File::Find::name\n";
231
232	my $search_str;
233	foreach $ext (split (/\s+/, $archive_extensions) ) {
234	$search_str .= $File::Find::name . "/*.$ext "
235	}
236
237	my @all_files = glob($search_str);
238
239	if (scalar(@all_files) == 0) {
240	# No data files means it's likely to be a CPSR2 directory
241
242	# Foreach observation
243	my @cpsr2_obs_dirs = glob($File::Find::name . "/????-??-??-??:??:??");
244
245	my $cpsr2_obs_dir;
246	foreach $cpsr2_obs_dir (@cpsr2_obs_dirs) {
247	print "dir is $cpsr2_obs_dir\n";
248
249	# Need to check if this entry already exists
250	$cpsr2_obs_dir =~ s/\s//g;
251
252	if (isDuplicate($cpsr2_obs_dir) == 1) {
253	print "$cpsr2_obs_dir is a duplicate entry. Skipping....\n\n";
254	next;
255	}
256
257	print "$cpsr2_obs_dir NOT a duplicate. Would extract data here...\n";
258	find_data_cpsr2($cpsr2_obs_dir);
259
260	}
261
262	}
263	else{
264
265	my $file;
266	foreach $file (@all_files) {
267
268	# Need to check if this entry already exists
269	$file =~ s/\s//g;
270
271	if (isDuplicate($file) == 1) {
272	print "$file is a duplicate entry. Skipping....\n\n";
273	next;
274	}
275
276	print "$file NOT a duplicate. Would insert into database here ...\n";
277	find_data($file);
278
279	}
280
281	}
282
283	}
284
285	}
286	}
287
288
289
290	#######################################################
291
292	sub find_data {
293
294	# Usage: find_data(filename)
295	# Runs vap on the filename, and extracts parameters, then sends them to populate_database() to be written to the database
296	#
297	# NOTE: this is for non-CPSR2 data
298
299	my $file = $_[0];
300
301	my $vap_cmd = "$vap -c \"$params\" $file \| grep -v filename \| grep -v -x \"\" \|";
302	print "Calling vap...$vap_cmd\n";
303	open(VAP,$vap_cmd);
304	while ($vap_out = <VAP>) {
305	$vap_out =~ s/^\s+//g; #remove any leading space
306	my @result_params = split(/\s+/, $vap_out);
307
308	populate_database(@result_params,$file);
309	# if (scalar(@result_params) == ($num_vap_params + 1)) { # +1 because first column is filename
310	# print "@result_params\n";
311	# populate_observations($file);
312	# }
313	# else {
314	# print "Incorrect number of cols = ".scalar(@result_params).". Expected " . $num_vap_params+1 ."\n";
315	# print "@result_params\n";
316	# }
317	}
318	print "\n";
319
320	}
321
322	#######################################################
323
324	sub find_data_cpsr2 {
325
326	# Usage: find_data_cpsr2(filename) where filename = ????-??-??-??:??:??
327	# Extracts parameters for the observation using vap, then sends them to populate_database() to be written to the database
328	#
329	# NOTE: this is only for CPSR2 data
330
331	my $dir = $_[0];
332	my $ext;
333	my @result_params;
334	my @tmp_params;
335	my $prefix = "m n o";
336
337	my @all_files_fb = glob($dir."/*.fb");
338	my @all_files_cfb = glob($dir."/*.cfb");
339	if ( scalar(@all_files_fb)!=0 ){
340	$ext = ".fb";
341	}
342	else{
343	$ext = ".cfb";
344	}
345	print "Searching in $dir for $ext files\n";
346
347	my $pre;
348	foreach $pre (split (/\s+/, ($prefix))) {
349
350	my @all_files;
351	my $search_str = $dir."/".$pre."*".$ext;
352	print "$pre: Searching for files like: $search_str\n";
353	@all_files = glob($search_str);
354	print "Found ". scalar(@all_files) . "\n";
355
356	if(scalar(@all_files) != 0) {
357
358	# Get most parameters from first file
359	my $vap_cmd = "$vap -n -c \"$params\" $all_files[0] \| grep -v filename \| grep -v -x \"\" \|";
360	open(VAP,$vap_cmd);
361	my $vap_out;
362	while ($vap_out = <VAP>) {
363	$vap_out =~ s/^\s+//g; #remove any leading space
364	@result_params = split(/\s+/, $vap_out);
365	print "RESULT_PARAMS:\n@result_params\n";
366	}
367
368	my $total_length=0;
369	my $file;
370	foreach $file (@all_files) { #loop over all files in directory to get total obs time.
371	my @tmp_params;
372	$vap_cmd = "$vap -n -c \"$params\" $file \| grep -v filename \| grep -v -x \"\" \|";
373	open(VAP,$vap_cmd);
374	while ($vap_out = <VAP>) {
375	$vap_out =~ s/^\s+//g; #remove any leading space
376	@tmp_params = split(/\s+/, $vap_out);
377	}
378	$total_length += $tmp_params[$params_LENGTH];
379	}
380
381	print "total length = $total_length\n";
382	$result_params[$params_LENGTH] = $total_length;
383
384
385
386
387	populate_database(@result_params,$dir);
388
389	}
390	}
391
392	}
393
394	#######################################################
395
396	sub populate_database {
397
398	# Usage: populate_database(file, $result_params), where file has the full path to the file/directory in question,
399	# and $result_params have been filled with the vap results
400	#
401	# This subroutine calculates all necessary parameters (such as data type, decimal positions, beam, file size, ...)
402	# and writes them to the database.
403
404	my @results = @_;
405	my $full_filename = $results[$#results];
406	# @results = shift @results;
407	print "full_filename = $full_filename\nRESULT_PARAMS (POP):\n@results\n";
408
409	my @dirs = split(/\//, $full_filename);
410	my $path = $dirs[0];
411	my $i;
412	for ($i=1; $i < $#dirs; $i++){
413	$path .= "/".$dirs[$i];
414	}
415	my $filename = $dirs[$#dirs];
416	print "path = $path, filename = $filename\n";
417
418	if( $filename =~ /[0-9]{4}-[0-9]{2}-[0-9]{2}-[0-9]{2}:[0-9]{2}:[0-9]{2}/ ) {
419	# Case of CPSR2 data file
420	$data_type = $CPSR2;
421
422	my @time_date = split('-', $filename);
423	$results[$params_DATE] = $time_date[0]."-".$time_date[1]."-".$time_date[2];
424	$results[$params_TIME] = $time_date[3];
425
426	my $prefix = substr $results[0], 0, 1;
427	print "vap filename = $results[0] and the prefix = $prefix\n";
428
429	my $du = `du -sb $full_filename/$prefix*fb \| awk 'BEGIN{sum=0}{sum+=\$1}END{print sum}'`;
430	print "Running du: -> du -sb $full_filename/$prefix*fb \| awk 'BEGIN{sum=0}{sum+=\$1}END{print sum}'\n";
431	my @file_details = split(/\s+/, $du);
432	$file_size = $file_details[0];
433	}
434	else{
435	if ($filename =~ /^a.\.rf/ \|\| $filename =~ /^a.\.cf/ \|\|
436	$filename =~ /^r.\.rf/ \|\| $filename =~ /^r.\.cf/) {
437	$data_type = $DFB;
438	}
439	elsif ($filename =~ /^w.\.rf/ \|\| $filename =~ /^w.\.cf/) {
440	$data_type = $WBC;
441	}
442	else {
443	$data_type = $NULLFIELD;
444	}
445
446	my $ls = `ls -l $full_filename`;
447	my @file_details = split(/\s+/, $ls);
448	$file_size = $file_details[$FILE_SIZE_INDEX];
449	}
450
451	print "RESULT_PARAMS (POP 2):\n@results\n";
452
453	#### Calculate the RA and Dec in decimal degrees.
454
455	($raH,$raM,$raS) = split(':', $results[$params_RAJ]);
456	$rajd = ($raH + $raM/60. + $raS/3600.) * 15.;
457
458	($decD,$decM,$decS) = split(':', $results[$params_DECJ]);
459	$decjd = (abs($decD) + $decM/60. + $decS/3600.);
460	my @decstring = split(/ */,$results[$params_DECJ]);
461	if($decstring[0] eq '-'){
462	$decjd = -1. * $decjd;
463	}
464
465	### Calculate the Galactic Longitude and latitude
466	$pi=asin(1) * 2.;
467	$pion180 = $pi/180.;
468	$NGP_RA = 192.859508 * $pion180; # location of NGP
469	$NGP_DEC= 27.128336 * $pion180;
470	$ASC_NODE=32.932;
471
472	$draR = $rajd*$pion180 - $NGP_RA;
473	$decR = $decjd*$pion180;
474	$sinb = cos($decR) * cos($NGP_DEC) * cos($draR) + sin($decR) * sin($NGP_DEC);
475	$gb = asin($sinb); # this is the latitude, but in radians.
476
477	$sinl = (sin($decR) * cos($NGP_DEC) - cos($decR) * cos($draR) * sin($NGP_DEC)) / cos($gb);
478	$cosl = cos($decR) * sin($draR) / cos($gb);
479
480	# Need to get the correct quadrant, as this isn't preserved by
481	# atan, which returns angle between -90 and 90.
482	$gl_raw = atan($sinl/$cosl);
483	if($sinl > 0){
484	if($cosl > 0 ){ $gl = $gl_raw; }
485	else { $gl = $gl_raw + $pi; }
486	}
487	else {
488	if($cosl > 0){ $gl = $gl_raw + 2.*$pi; }
489	else{ $gl = $gl_raw + $pi; }
490	}
491	# Now put them into degrees.
492	$gb = $gb / $pion180;
493	$gl = ($gl / $pion180) + $ASC_NODE;
494
495	print "rajd = $rajd, decjd=$decjd, gl=$gl, gb=$gb\n";
496
497
498	# Beam information
499	$bmaj = $results[$params_BMAJ];
500	$bmin = $results[$params_BMIN];
501	$bpa = $results[$params_BPA];
502	if($bmaj eq "UNDEF" \|\| $bmaj eq "*" \|\| $bmaj == 0.){
503	# could not get beam info from vap output.
504	# Need to calculate manually.
505	if( $results[$params_FREQ] != 0.){
506	# if the frequency is zero, do not calculate these...
507	$bmaj = (1.2(299792458./($results[$params_FREQ] 1.e6))/64.) / $pion180;
508	$bmin = (1.2(299792458./($results[$params_FREQ] 1.e6))/64.) / $pion180;
509	$bpa = 0.;
510	}
511	else{
512	$bmaj = $NULLFIELD;
513	$bmin = $NULLFIELD;
514	$bpa = $NULLFIELD;
515	}
516	}
517
518	if($results[$params_NBITS] eq "UNDEF" \|\| $results[$params_NBITS] == 0){
519	$results[$params_NBITS] = $NULLFIELD;
520	}
521	if($results[$params_TSAMP] eq "UNDEF" \|\| $results[$params_TSAMP] == 0){
522	$results[$params_TSAMP] = $NULLFIELD;
523	}
524
525
526	# Dud things that aren't required for this set of data.
527	$survey = $NULLFIELD;
528	$nbeam = 1;
529
530	# Clean up any NULL parameters
531	for ($i = 0; $i <= $#results; $i++) {
532	if ($results[$i] eq "N/A" \|\| $results[$i] eq "UNDEF" \|\|
533	$results[$i] eq "INVALID" \|\|
534	$results[$i] eq "error" \|\| $results[$i] eq "*") {
535
536	$results[$i] = $NULLFIELD;
537
538	}
539	}
540
541
542	# print out the list of parameters
543	print "filename = $filename\n";
544	print "src_name = " . $results[$params_NAME] . "\n";
545	print "projid = " . $results[$params_PROJID] . "\n";
546	print "raj = " . $results[$params_RAJ] . "\n";
547	print "dec = " . $results[$params_DECJ] . "\n";
548	print "data_type = $data_type\n";
549	print "freq = " . $results[$params_FREQ] . "\n";
550	print "bw = " . $results[$params_BW] . "\n";
551	print "scanlen = " . $results[$params_LENGTH] . "\n";
552	print "date = " . $results[$params_DATE] ."\n";
553	print "ut = " . $results[$params_TIME] ."\n";
554	print "MJD = " . $results[$params_MJD] ."\n";
555	print "rajd = " . $rajd ."\n";
556	print "decjd = " . $decjd ."\n";
557	print "gl = " . $gl ."\n";
558	print "gb = " . $gb ."\n";
559	print "bmaj = " . $bmaj ."\n";
560	print "bmin = " . $bmin ."\n";
561	print "bpa = " . $bpa ."\n";
562	print "dm = " . $results[$params_DM] . "\n";
563	print "period = " . $results[$params_PERIOD] . "\n";
564	print "nchan = " . $results[$params_NCHAN] . "\n";
565	print "npol = " . $results[$params_NPOL] . "\n";
566	print "nbin = " . $results[$params_NBIN] . "\n";
567	print "nsub = " . $results[$params_NSUB] . "\n";
568	print "tsamp = " . $results[$params_TSAMP] . "\n";
569	print "nbits = " . $results[$params_NBITS] . "\n";
570	print "nbeam = " . $nbeam . "\n";
571	print "cnfg = " . $results[$params_CNFG] . "\n";
572	print "inst = " . $results[$params_INST] . "\n";
573	print "rcvr = " . $results[$params_RCVR] . "\n";
574	print "hdrver = " . $results[$params_HDRVER] . "\n";
575	print "survey = " . $survey . "\n";
576	print "telescope = " . $results[$params_TELESCOP] . "\n";
577	print "site = " . $results[$params_SITE] . "\n";
578	print "obsrvr = " . $results[$params_OBSRVR] . "\n";
579	print "data_loc = $path\n";
580	print "file_size = $file_size bytes\n";
581
582	# Clean up any NULL parameters ready for SQL import
583	for ($i = 0; $i <= $#results; $i++) {
584	if ($results[$i] eq $NULLFIELD) {
585	$results[$i] = undef;
586	}
587	}
588
589	######### Now write the data to the mySQL database
590
591	my $sql;
592
593	my %data = ( filename => $filename,
594	src_name => $results[$params_NAME],
595	project_id => $results[$params_PROJID],
596	raj => $results[$params_RAJ],
597	decj => $results[$params_DECJ],
598	data_type => $data_type,
599	obsfreq => $results[$params_FREQ],
600	bw => $results[$params_BW],
601	scanlen => $results[$params_LENGTH],
602	date => $results[$params_DATE],
603	ut => $results[$params_TIME],
604	MJD => $results[$params_MJD],
605	rajd => $rajd,
606	decjd => $decjd,
607	gl => $gl,
608	gb => $gb,
609	BMAJ => $bmaj,
610	BMIN => $bmin,
611	BPA => $bpa,
612	dm => $results[$params_DM],
613	period => $results[$params_PERIOD],
614	nchan => $results[$params_NCHAN],
615	npol => $results[$params_NPOL],
616	nbin => $results[$params_NBIN],
617	nsub => $results[$params_NSUB],
618	tsamp => $results[$params_TSAMP],
619	nbits => $results[$params_NBITS],
620	nbeam => $nbeam,
621	cnfg => $results[$params_CNFG],
622	inst => $results[$params_INST],
623	rcvr => $results[$params_RCVR],
624	hdrver => $results[$params_HDRVER],
625	survey => $survey,
626	telescope => $results[$params_TELESCOP],
627	site => $results[$params_SITE],
628	obsrvr => $results[$params_OBSRVR],
629	data_loc => $path,
630	file_size_bytes => $file_size
631	);
632
633	my $sql = SQL::Abstract->new;
634	my $tableName;
635	if ($path =~ /\_R/ ) {
636	$tableName = 'cals';
637	}
638	else {
639	$tableName = 'observations';
640	}
641	my($stmt, @bind) = $sql->insert($tableName, \%data);
642
643	my $sth = $db_handle->prepare($stmt);
644	$sth->execute(@bind);
645
646
647
648
649	# if ($path =~ /\_R/ ) {
650	# print "Inserting cal file $filename\n";
651	#
652	# $sql = qq{ INSERT INTO cals
653	# (filename, src_name, project_id,
654	# raj, decj, data_type, obsfreq, bw,
655	# scanlen, date, ut, MJD,
656	# rajd, decjd, gl, gb, BMAJ, BMIN, BPA,
657	# dm, period, nchan, npol, nbin, nsub,
658	# tsamp, nbits, nbeam,
659	# cnfg, inst, rcvr, hdrver, survey,
660	# telescope, site, obsrvr,
661	# data_loc, file_size_bytes
662	# )
663	#
664	# VALUES
665	# ( ?, ?, ?
666	# ?, ?, ?, CAST(? AS DECIMAL), CAST(? AS DECIMAL),
667	# CAST(? AS DECIMAL), ?, ?, CAST(? AS DECIMAL),
668	# CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS DECIMAL),
669	# CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS UNSIGNED), CAST(? AS UNSIGNED), CAST(? AS UNSIGNED), CAST(? AS UNSIGNED),
670	# CAST(? AS DECIMAL), CAST(? AS UNSIGNED), CAST(? AS UNSIGNED),
671	# ?, ?, ?, ?, ?,
672	# ?, ?, ?,
673	# ?, CAST(? AS UNSIGNED)
674	# )
675	## ( ?, ?, ?
676	## ?, ?, ?, ?, ?,
677	## ?, ?, ?, ?,
678	## ?, ?, ?, ?, ?, ?, ?,
679	## ?, ?, ?, ?, ?, ?,
680	## ?, ?, ?,
681	## ?, ?, ?, ?, ?,
682	## ?, ?, ?,
683	## ?, ?,
684	## )
685	# };
686	# }
687	# else {
688	#
689	# print "Inserting obs file $filename\n";
690	#
691	# $sql = qq{ INSERT INTO observations
692	# (filename, src_name, project_id,
693	# raj, decj, data_type, obsfreq, bw,
694	# scanlen, date, ut, MJD,
695	# rajd, decjd, gl, gb, BMAJ, BMIN, BPA,
696	# dm, period, nchan, npol, nbin, nsub,
697	# tsamp, nbits, nbeam,
698	# cnfg, inst, rcvr, hdrver, survey,
699	# telescope, site, obsrvr,
700	# data_loc, file_size_bytes
701	# )
702	#
703	# VALUES
704	# ( ?, ?, ?
705	# ?, ?, ?, CAST(? AS DECIMAL), CAST(? AS DECIMAL),
706	# CAST(? AS DECIMAL), ?, ?, CAST(? AS DECIMAL),
707	# CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS DECIMAL),
708	# CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS UNSIGNED), CAST(? AS UNSIGNED), CAST(? AS UNSIGNED), CAST(? AS UNSIGNED),
709	# CAST(? AS DECIMAL), CAST(? AS UNSIGNED), CAST(? AS UNSIGNED),
710	# ?, ?, ?, ?, ?,
711	# ?, ?, ?,
712	# ?, CAST(? AS UNSIGNED)
713	# )
714	## ( ?, ?, ?
715	## ?, ?, ?, ?, ?,
716	## ?, ?, ?, ?,
717	## ?, ?, ?, ?, ?, ?, ?,
718	## ?, ?, ?, ?, ?, ?,
719	## ?, ?, ?,
720	## ?, ?, ?, ?, ?,
721	## ?, ?, ?,
722	## ?, ?,
723	## )
724	# };
725	# }
726	# my $sth = $db_handle->prepare( $sql );
727	##DEBUGGING
728	## my $sth;
729	#
730	# #for( @records ) {
731	# eval {
732	#
733	## $sth->bind_param( 1, $filename);
734	## $sth->bind_param( 2, $results[$params_NAME]);
735	## $sth->bind_param( 3, $results[$params_PROJID]);
736	## $sth->bind_param( 4, $results[$params_RAJ]);
737	## $sth->bind_param( 5, $results[$params_DECJ]);
738	## $sth->bind_param( 6, $data_type);
739	## $sth->bind_param( 7, $results[$params_FREQ]);
740	## $sth->bind_param( 8, $results[$params_BW]);
741	## $sth->bind_param( 9, $results[$params_LENGTH]);
742	## $sth->bind_param( 10, $results[$params_DATE]);
743	## $sth->bind_param( 11, $results[$params_TIME]);
744	## $sth->bind_param( 12, $results[$params_MJD]);
745	## $sth->bind_param( 13, $rajd);
746	## $sth->bind_param( 14, $decjd);
747	## $sth->bind_param( 15, $gl);
748	## $sth->bind_param( 16, $gb);
749	## $sth->bind_param( 17, $bmaj);
750	## $sth->bind_param( 18, $bmin);
751	## $sth->bind_param( 19, $bpa);
752	## $sth->bind_param( 20, $results[$params_DM]);
753	## $sth->bind_param( 21, $results[$params_PERIOD]);
754	## $sth->bind_param( 22, $results[$params_NCHAN]);
755	## $sth->bind_param( 23, $results[$params_NPOL]);
756	## $sth->bind_param( 24, $results[$params_NBIN]);
757	## $sth->bind_param( 25, $results[$params_NSUB]);
758	## $sth->bind_param( 26, $results[$params_TSAMP]);
759	## $sth->bind_param( 27, $results[$params_NBITS]);
760	## $sth->bind_param( 28, $nbeam);
761	## $sth->bind_param( 29, $results[$params_CNFG]);
762	## $sth->bind_param( 30, $results[$params_INST]);
763	## $sth->bind_param( 31, $results[$params_RCVR]);
764	## $sth->bind_param( 32, $results[$params_HDRVER]);
765	## $sth->bind_param( 33, $survey);
766	## $sth->bind_param( 34, $results[$params_TELESCOP]);
767	## $sth->bind_param( 35, $results[$params_SITE]);
768	## $sth->bind_param( 36, $results[$params_OBSRVR]);
769	## $sth->bind_param( 37, $path);
770	## $sth->bind_param( 38, $file_size);
771	#
772	## $sth->bind_param( 1, $filename, {TYPE => SQL_VARCHAR } );
773	## $sth->bind_param( 2, $results[$params_NAME], {TYPE => SQL_VARCHAR } );
774	## $sth->bind_param( 3, $results[$params_PROJID], {TYPE => SQL_VARCHAR } );
775	## $sth->bind_param( 4, $results[$params_RAJ], {TYPE => SQL_VARCHAR } );
776	## $sth->bind_param( 5, $results[$params_DECJ], {TYPE => SQL_VARCHAR } );
777	## $sth->bind_param( 6, $data_type, {TYPE => SQL_VARCHAR } );
778	## $sth->bind_param( 7, $results[$params_FREQ], {TYPE => SQL_DOUBLE } );
779	## $sth->bind_param( 8, $results[$params_BW], {TYPE => SQL_DOUBLE } );
780	## $sth->bind_param( 9, $results[$params_LENGTH], {TYPE => SQL_DOUBLE } );
781	## $sth->bind_param( 10, $results[$params_DATE], {TYPE => SQL_VARCHAR } );
782	## $sth->bind_param( 11, $results[$params_TIME], {TYPE => SQL_VARCHAR } );
783	## $sth->bind_param( 12, $results[$params_MJD], {TYPE => SQL_DOUBLE } );
784	## $sth->bind_param( 13, $rajd, {TYPE => SQL_DOUBLE } );
785	## $sth->bind_param( 14, $decjd, {TYPE => SQL_DOUBLE } );
786	## $sth->bind_param( 15, $gl, {TYPE => SQL_DOUBLE } );
787	## $sth->bind_param( 16, $gb, {TYPE => SQL_DOUBLE } );
788	## $sth->bind_param( 17, $bmaj, {TYPE => SQL_FLOAT } );
789	## $sth->bind_param( 18, $bmin, {TYPE => SQL_FLOAT } );
790	## $sth->bind_param( 19, $bpa, {TYPE => SQL_FLOAT } );
791	## $sth->bind_param( 20, $results[$params_DM], {TYPE => SQL_DOUBLE } );
792	## $sth->bind_param( 21, $results[$params_PERIOD], {TYPE => SQL_DOUBLE } );
793	## $sth->bind_param( 22, $results[$params_NCHAN], {TYPE => SQL_INTEGER } );
794	## $sth->bind_param( 23, $results[$params_NPOL], {TYPE => SQL_INTEGER } );
795	## $sth->bind_param( 24, $results[$params_NBIN], {TYPE => SQL_INTEGER } );
796	## $sth->bind_param( 25, $results[$params_NSUB], {TYPE => SQL_INTEGER } );
797	## $sth->bind_param( 26, $results[$params_TSAMP], {TYPE => SQL_FLOAT } );
798	## $sth->bind_param( 27, $results[$params_NBITS], {TYPE => SQL_TINYINT } );
799	## $sth->bind_param( 28, $nbeam, {TYPE => SQL_TINYINT } );
800	## $sth->bind_param( 29, $results[$params_CNFG], {TYPE => SQL_VARCHAR } );
801	## $sth->bind_param( 30, $results[$params_INST], {TYPE => SQL_VARCHAR } );
802	## $sth->bind_param( 31, $results[$params_RCVR], {TYPE => SQL_VARCHAR } );
803	## $sth->bind_param( 32, $results[$params_HDRVER], {TYPE => SQL_VARCHAR } );
804	## $sth->bind_param( 33, $survey, {TYPE => SQL_VARCHAR } );
805	## $sth->bind_param( 34, $results[$params_TELESCOP], {TYPE => SQL_VARCHAR } );
806	## $sth->bind_param( 35, $results[$params_SITE], {TYPE => SQL_VARCHAR } );
807	## $sth->bind_param( 36, $results[$params_OBSRVR], {TYPE => SQL_VARCHAR } );
808	## $sth->bind_param( 37, $path, {TYPE => SQL_VARCHAR } );
809	## $sth->bind_param( 38, $file_size, {TYPE => SQL_INTEGER } );
810	#
811	# $sth->execute();
812	##DEBUGGING
813	## DBI::dump_results($sth);
814	## $rows = $sth->dump_results();
815	## print "$rows\n";
816	# print "$sth\n";
817	#
818	# # $db_handle->commit(); # Autocommit is already ON so no need for a manual commit
819	# };
820
821	if( $@ ) {
822	print "warning statement\n";
823	warn "Database error: $DBI::errstr\n";
824	$db_handle->rollback(); #just die if rollback is failing
825	}
826
827
828	#DEBUGGING
829	$sth->finish();
830
831	}
832
833	#######################################################
834
835	sub isDuplicate {
836
837	#DEBUGGING
838	# return 0;
839
840	my $filename = $_[0];
841	my $search_data_loc;
842	my $search_filename;
843	my $pulsar_name;
844
845	if ( $filename =~ m/(.(J[0-9]{4}[+-][0-9]{1,4}.))[\/]+([a-z][0-9]{6}_[0-9]{6}\.([a-z]{2,}))$/i ) {
846	$search_data_loc = $1;
847	$search_filename = $3;
848	$pulsar_name = $2;
849	}
850	elsif ($filename =~ m/(.(J[0-9]{4}[+-][0-9]{1,4}.))[\/]+([0-9]{4}-[0-9]{2}-[0-9]{2}-[0-9]{2}:[0-9]{2}:[0-9]{2})$/i ) {
851	$search_data_loc = $1;
852	$search_filename = $3;
853	$pulsar_name = $2;
854	}
855	else {
856	print "$filename is an oddball -- not doing...";
857	return 1;
858	}
859
860	my $sql;
861	if ($pulsar_name =~ m/.*_R$/) {
862
863	$sql = qq{ SELECT * from cals
864	WHERE data_loc LIKE ? AND
865	filename = ?
866	};
867	}
868	else {
869	print "This is an observation\n";
870	$sql = qq{ SELECT * from observations
871	WHERE data_loc LIKE ? AND
872	filename = ?
873	};
874	}
875
876	my $sth = $db_handle->prepare( $sql );
877
878	eval {
879	print "like $search_data_loc and filename = $search_filename \n";
880
881	$sth->bind_param( 1, $search_data_loc, SQL_VARCHAR );
882	$sth->bind_param( 2, $search_filename, SQL_VARCHAR );
883	$sth->execute();
884
885	};
886
887	if( $@ ) {
888	warn "Database error: $DBI::errstr\n";
889	$db_handle->rollback(); #just die if rollback is failing
890	}
891
892	# read the records
893	my @data = $sth->fetchrow_array();
894
895	$sth->finish();
896
897	if (scalar(@data) > 0) {
898	return 1;
899	}
900
901	return 0;
902
903	}
904	#######################################################
905
906	sub connectdb {
907
908	# Connect to the MySQL server
909	my $dbh = DBI->connect("dbi:mysql:database=psrchive;host=localhost", "psrdba", "lighthouse")
910	or die "Couldn't connect to database: $DBI::errstr\n";
911
912	print "Successfully connected to db\n" if $dbh;
913	return $dbh;
914
915	# DEBUGGING
916	# print "We would normally connect to the DB here\n";
917	# return 1;
918	}

Note: See TracBrowser for help on using the repository browser.

Download in other formats: