Subj : Loading a nodelist table
To   : All
From : Jame Clay
Date : Sat Jul 07 2001 07:40 am

Hi All!


========<cut>=========================================

#!/usr/bin/perl -w
#
# nl2sqlv1.pl
# version 1.0 - 7 July 2001 - R.J. Clay
# Initial load of a particular FTN St. Louis Format Nodelist
# into an SQL (Mysql) based database.   Uses standard Perl.
# See README for license information.

use Getopt::Std;

if($#ARGV < 0) {
    print STDERR "usage: nl2sqlv1.pl -n nodelist -d [domain]...\n";
    print STDERR "nodelist = path and filename of nodelist file...\n";
    print STDERR "[domain] = nodelist domain;  defaults to fidonet.\n";
    exit 1;
}

getopts('n:d:x');

# note that "opt_x" is the debug variable
if ($opt_x) {
    print "Debug flag is set ...\n";
}

#  setup nodelist file variable
if ($opt_n) {
    $nodelistfile=$opt_n;
} else {
    $nodelistfile='nodelist.tst';
}
if ($opt_x) {
    print "Nodelist file is '$nodelistfile' ..\n";
}

open (NODELIST, $nodelistfile)
    or die ("Cannot open nodelist file:  $nodelistfile");

#  set up domain variable
if ($opt_d) {
    $domain=$opt_d;
} else {
    $domain='fidonet';    # domain defaults to fidonet
#    $domain = 'stn';
}
if ($opt_x) {
    print "Domain is '$domain' ..\n";
}

#set defaults
$zone = 1;
$net  = 0;
$node = 0;
$point = 0;
$region = 0;


# connect to database
#   Assumes that $dbname, $dbuser, & $dbpass are set as follows
# in the database.  (see createftndbtst.pl)

$dbname = 'ftndbtst';
$dbuser = 'sysop';
$dbpass = 'ftntstpw';

use DBI;

( $dbh = DBI->connect("dbi:mysql:dbname=$dbname", $dbuser, $dbpass) )
    or  die $DBI::errstr;

#   remove any and all entries where domain = $domain
$delete_stmt = "DELETE FROM nodelist ";
$delete_stmt .= "WHERE domain = '$domain' ";

if ($opt_x) {
    print "The delete statment is:  $delete_stmt ";
}
$dbh->do( "$delete_stmt " )
    or $insert_ok = "0";


while(<NODELIST>) {
    if ( /^;/ || /^\cZ/ ) {
#       print;
        next;
    }

    ($type,$num,$name,$loc,$sysop,$phone,$bps,$flags) = split(',', $_, 8);

# originally took care of these by deleteing them
    $name =~ tr/'//d;   #take care of single quotes in system name fields
    $loc =~ tr/'//d;   #take care of single quotes in location fields
    $sysop =~ tr/'//d;   #take care of single quotes in sysop name fields

    if($type eq "Zone") {       # Zone line
        $zone = $num;
        $net  = $num;
        $node = 0;
    }                           #
    elsif($type eq "Region") {  # Region line
        $region  = $num;
        $net  = $num;
        $node = 0;
    }
    elsif($type eq "Host") {    # Host line
        $net = $num;
        $node = 0;
    }
    else {
        $node = $num;
    }

if ($opt_x) {
# display where in the nodelist we are
    print "$type,";
    printf "%-16s", "$zone:$net/$node";
    print "$sysop\n";
}
    $insert_stmt = "INSERT INTO nodelist ";

    $insert_stmt .= "(type,zone,net,node,point,region,name,";
    $insert_stmt .= "location,sysop,phone,baud,flags,domain) ";

    $insert_stmt .= "VALUES (";

    $insert_stmt .= "'$type', ";
    $insert_stmt .= "'$zone', ";
    $insert_stmt .= "'$net', ";
    $insert_stmt .= "'$node', ";
    $insert_stmt .= "'$point', ";
    $insert_stmt .= "'$region', ";
    $insert_stmt .= "'$name', ";
    $insert_stmt .= "'$loc', ";
    $insert_stmt .= "'$sysop', ";
    $insert_stmt .= "'$phone', ";
    $insert_stmt .= "'$bps', ";
    $insert_stmt .= "'$flags', ";
    $insert_stmt .= "'$domain') ";

#    if ($opt_x) {
#        print " $insert_stmt ";
#    }
    $dbh->do( "$insert_stmt " );

}

# disconnect from database

( $dbh->disconnect )
        or  die $DBI::errstr;

close NODELIST;

exit();
========<cut>=========================================

--- BBBS/LiI v4.01 Flag-3
 * Origin: https://rocasa.org/bbbs (1:120/544.2)

.