File indexing completed on 2024-09-08 03:28:59

 #!/usr/bin/perl
 #
 # parse-sac-to-mysql.pl   Put DSO data from Saguaro Astronomy Club's database in the Fence format into a database
 #
 # CAUTION: Will truncate the table supplied!
 #
 # SPDX-FileCopyrightText: 2009 James Bowlin <bowlin@mindspring.com> and Akarsh Simha <akarsh.simha@kdemail.net>
 # SPDX-License-Identifier: GPL-2.0-or-later
 
 use strict;
 use DBI;
 
 my $ERROR;
 
 my $VERBOSE = 1;
 
 # For database handling
 my $db_db = shift;
 my $db_user = shift;
 !($db_db eq "") and !($db_user eq "") or print "USAGE: " . $0 . " <database name> <MySQL Username> [[MySQL Password [Table]]\n" and exit;
 my $db_pass = shift;
 my $db_tbl = shift or "SAC_DeepSky";
 
 my $db_query = qq/CREATE DATABASE IF NOT EXISTS `$db_db`;/;
 my $db_select_query = qq/USE `$db_db`/;
 
 my $tbl_query = qq/
 CREATE TABLE IF NOT EXISTS `$db_tbl` (
   `primary` varchar(30) NOT NULL COMMENT 'Primary Designation',
   `secondary` varchar(30) NOT NULL COMMENT 'Alternate Designation',
   `type` smallint NOT NULL COMMENT 'Object Type',
   `const` varchar(3) NOT NULL COMMENT 'Constellation',
   `RA` double NOT NULL COMMENT 'Right Ascension',
   `Dec` double NOT NULL COMMENT 'Declination',
   `mag` float NOT NULL COMMENT 'Magnitude',
   `subr` float NOT NULL COMMENT 'Surface Brightness',
   `U2K` mediumint(9) NULL COMMENT 'Uranometria 2000 chart number',
   `SA2K` smallint(6) NULL COMMENT'SkyAtlas 2000.0 chart number',
   `a` float NOT NULL COMMENT 'Major Axis',
   `b` float NOT NULL COMMENT 'Minor Axis',
   `pa` int NOT NULL COMMENT 'Position Angle',
   `class` varchar(15) NOT NULL COMMENT 'Classification detail',
   `ngc_desc` varchar(30) NOT NULL COMMENT 'NGC Description',
   `notes` varchar(50) NOT NULL COMMENT 'SAC notes',
   PRIMARY KEY  (`primary`),
   KEY `other` (`secondary`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;/;
 
 my $tbl_trunc_query = qq/TRUNCATE TABLE `$db_tbl`/;
 
 # Get the database handle
 my $dbh = DBI->connect("DBI:mysql:", $db_user, $db_pass, { RaiseError => 1, AutoCommit => 0 });
 
 my @fields = qw/primary secondary type const RA Dec mag subr U2K SA2K a b pa class ngc_desc notes/;
 my %objtypes = qw/1STAR 1 2STAR 17 3STAR 17 4STAR 17 8STAR 17 ASTER 13 OPNCL 3 GLOCL 4 CL+NB 5 G+C+N 5 GX+DN 5 GX+GC 4 BRTNB 5 DRKNB 15 PLNNB 6 SNREM 7 GALXY 8 GALCL 14 LMCCN 5 LMCDN 5 LMCGC 4 LMCOC 3 NONEX 18 SMCCN 5 SMCDN 5 SMCGC 4 SMCOC 3 QUASR 16/;
 
 $dbh->do($db_query);
 $dbh->do($db_select_query);
 $dbh->do($tbl_query);
 $dbh->do($tbl_trunc_query);
 $dbh->commit();
 
 while(<>) {
     m/^\s*#/ and do { print if $VERBOSE; next};
     m/\S/ or     do { print if $VERBOSE; next};
     chomp;
     my $dso = kstars_unpack($_) or do {
         warn sprintf("%4d: FORMAT ERROR ($ERROR):\n$_\n", $.);
         next;
     };
     $dso->{line} = $.;
 
     my $query ||= qq/INSERT INTO `$db_tbl` (/ .
     join(", ", map {"`$_`"} @fields) .
     qq/) VALUES (/ .
     join(", ", map {"?"} @fields) .
     qq/)/;
  
     my $sth ||= $dbh->prepare($query);
  
     $sth->execute(@$dso{@fields});
 
 }
 
 $dbh->commit();
 
 $dbh->disconnect();
 
 exit;
 
 #----------------------------------------------------------------------------
 #--- Subroutines ------------------------------------------------------------
 #----------------------------------------------------------------------------
 
 sub kstars_unpack {
     my $line = shift;
     chomp $line;
     $VERBOSE and print $line;
     $line =~ m{^\|\s*([^\s\|]+)\s       # Catalog 
              \s*([\d+-\.\sA-Za-z]*?)\s*\|    # Catalog Number / Designation
              (.*?)\s*\|              # Secondary Designation
             ([0-9A-Z+]{5})\|        # Type
              ([A-Z]{3})\|        # Constellation
              (\d\d\s\d\d\.\d)\|     # RA  [String: HH MM.M]
              ([+-]\d{2}\s\d{2})\|     # Dec [String: +/-DD MM]
              ([\s\d]\d\.\d)\|        # Magnitude
              (\d+\.?\d*)\s*\|        # Surface Brightness
              (\d+)\s*\|         # Uranometria 2000.0 Chart Number
              (\d+)\s*\|         # Tirion Sky Atlas 2000.0 Chart Number
              \s*(\d+\.?\d*\s*[ms])?\s*\| # Major Axis
              \s*(\d+\.?\d*\s*[ms])?\s*\| # Minor Axis
              (\d*)\s*\|              # Position Angle
              ([^|]*?)\s*\|           # Class
              ## NOTE: Ignoring fields NSTS, BRSTR and BCHM; TODO: Include them later
              [^\|]*\|[^\|]*\|[^\|]*\|     # Ignore NSTS, BRSTR, BCHM
              ([^|]*?)\s*\|           # NGC Description
              ([^|]*?)\s*\|           # Notes
           }x or do 
           {
           $ERROR = "Positional Error (0-59)";
           return;
           };
     my $object = {
         cat => $1,
     catno => $2,
     secondary => $3,
     type_str => $4,
     const => $5,
     ra_str => $6,
     dec_str => $7,
     mag => $8,
     subr => $9,
     U2K => $10,
     SA2K => $11,
     a_str => $12,
     b_str => $13,
     pa => $14,
     class => $15,
     ngc_desc => $16,
     notes => $17
     };
 
     $object->{primary} = $object->{cat} . " " . $object->{catno};
     $object->{secondary} =~ s/\s+/ /g;
     $object->{type} = $objtypes{$object->{type_str}};
     $object->{ra_str} =~ m/\s*(\d\d) (\d\d.\d)/;# or do { $ERROR="RA String format error: " . $object->{ra_str}; return; };
     $object->{RA} = $1 + $2 / 60.0;
     $object->{dec_str} =~ m/\s*([+-]?\d\d) (\d\d)/;# or do { $ERROR="Dec String format error: " . $object->{dec_str}; return; };
     $object->{Dec} = $1 + $2 / 60.0;
     $object->{a_str} =~ m/\s*(\d*\.?\d*)\s*([ms]?)\s*/;# or do { $ERROR="Major axis format error: " . $object->{a_str}; return; };
     $object->{a} = $1 / (($2 eq 's')? 60.0 : 1.0);
     $object->{b_str} =~ m/\s*(\d*\.?\d*)\s*([ms]?)\s*/;# or do { $ERROR="Minor axis format error: " . $object->{b_str}; return; };
     $object->{b} = $1 / (($2 eq 's')? 60.0 : 1.0);
 
     return $object;
 }