#!/usr/bin/perl
# Preqrequisites: Perl DBI & DBD::msyql modules. Installation:
## Debian? sudo aptitude install libdbi-perl libdbd-mysql-perl
## Elsewhere? perl -MCPAN -e 'install DBD::mysql DBI'
#
# Usage: Run this as ./convert_to_sql.pl to see the syntax of it.
# This script is (c) Jørgen P. Tjernø, and is under the GPL license.
# For questions, bugs, problems:
#   Email me at daxxar-removeTHIStext@daxxar.com
#
# LICENSE:
#    convert_to_sql.pl, a script to convert glftpd userfiles to mysql rows.
#    (For Nicon's mysql backend for glftpd)
#
#    Copyright (C) 2007 Jørgen Pedersen Tjernø
#
#    This program is free software; you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation, version 2.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License
#    along with this program; if not, write to the Free Software
#    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA

use strict;
use warnings;

use DBI;
use POSIX;

# DEBUG=1 ./convert_to_sql.pl /glftpd/ftp-data/users 
# ^-- gives you debug mode. :-)
my $debug = (defined($ENV{DEBUG}) && length($ENV{DEBUG}));

if ((@ARGV < 1 && $debug) || (@ARGV < 3 && !$debug))
{
    print STDERR <<SYNTAX;
Not enough arguments. Syntax:
$0 <path> <database> <username> [host] [table] [passwd]

 Explanation of arguments:
  * path -- The path to the userfiles, normally /glftpd/ftp-data/users
  * database -- Name of the database the user table is in.
  * username -- Username for the mysql database
  * host -- (OPTIONAL) Host to connect to, defaults to 'localhost'
  * port -- (OPTIONAL) Port to connect to, default is 3306
  * table -- (OPTIONAL) Table name, defaults to 'users'
  * passwd -- (OPTIONAL) Path to passwd file to read, defaults to \$path/../../etc/passwd

 To get debug output (printing of the executed queries, instead of sending them to a db), run:
   DEBUG=1 $0 <path>
 NOTE: Debug output does not print 100% valid queries, values might not be escaped properly / miss \\-es.
SYNTAX
    exit 1;
}

my ($path, $database, $username, $host, $port, $table, $passwd) = @ARGV;

$host ||= 'localhost';
$table ||= 'users';
$passwd ||= "$path/../../etc/passwd";

if (! -d $path || ! -r $path)
{
    print STDERR "Invalid path '$path' to userfiles. (Not a directory, or not readable)\n";
    exit 1;
}
if (! -f $passwd || ! -r $passwd)
{
    print STDERR "Invalid path '$passwd' to passwd file. (Not a file, or not readable)\n";
    exit 1;
}

my $dbh;
if (!$debug)
{
    my $password = input_noecho("Password for $username\@$host:$database, please: ");

    my $dsn = "DBI:mysql:database=$database;host=$host";
    $dsn .= ";port=$port" if defined($port);

    $dbh = DBI->connect($dsn, $username, $password);
    if (!defined($dbh))
    {
        # DBI->connect() spews an error if we fail, so no need to explain.
        print STDERR "Could not connect to database, exiting.\n";
        exit 1;
    }
}
else
{
    # We define $dbh to be an instance of our emulating DB class,
    # DBEmu. Using this, we can see what would've been executed.
    # (We only use do() and quote(), add more methods if needed)
    package DBEmu;  

    # This is a simplistic implementation, and
    # do not assume that the data it returns is valid.
    sub quote {
        my ($self, $string) = @_;
        $string =~ s/\\//g;
        $string =~ s/'/\\'/g;
        return "'$string'";
    }

    sub do {
        my ($self, $query) = @_;
        print "$query\n";

        return -1;
    }
    my $emu = {};
    bless $emu;

    $dbh = $emu;
}

# These are what the "types" (part of the mysql column name) of
# the there numbers associated with each section on the sectiontriples,
# see sub handleSectionSpecificTriples at the end. ;-)
my @nukeTypes = qw(LAST TIMES MB);
my @normalTypes = qw(FILES KB TIME);

# %handlers is a hash where the key is the 'command' (first word of a line) in the userfile
# and the value is a CODE ref to a sub that takes in the command name and the data (the rest of
# the line), and returns two array references. The array references are field names and values,
# respectively (field names are mysql columns, field values are the data the column should have in the
# new row). We use some reusable subs, and they are defined at the bottom of the code, to save clutter.
my %handlers;
$handlers{HOMEDIR} = \&handleQuoted;
$handlers{TAGLINE} = \&handleQuoted;
$handlers{FLAGS} = \&handleQuoted;
$handlers{TIMEFRAME} = \&handleQuoted;

$handlers{USER} = sub { handleQuoted('COMMENT', @_[1 .. $#_]); };
$handlers{DIR} = sub { handleQuoted("STARTUP_DIR", @_[1 .. $#_]); };

$handlers{ALLUP} = \&handleSectionSpecificTriples;
$handlers{ALLDN} = \&handleSectionSpecificTriples;
$handlers{WKUP} = \&handleSectionSpecificTriples;
$handlers{WKDN} = \&handleSectionSpecificTriples;
$handlers{DAYUP} = \&handleSectionSpecificTriples;
$handlers{DAYDN} = \&handleSectionSpecificTriples;
$handlers{MONTHUP} = \&handleSectionSpecificTriples;
$handlers{MONTHDN} = \&handleSectionSpecificTriples;
$handlers{NUKE}  = \&handleSectionSpecificTriples;

$handlers{CREDITS} = \&handleSectionSpecificInteger;
$handlers{RATIO} = \&handleSectionSpecificInteger;

$handlers{LOGINS} = sub { handleIntegers(shift, shift, qw(MAX_LOGINS MAX_IP_LOGINS MAX_SIM_UP MAX_SIM_DOWN)); };
$handlers{EXPIRES} = sub { handleIntegers(shift, shift, qw(EXPIRES)); };
$handlers{TIME} = sub { handleIntegers(shift, shift, qw(LOGIN_TIMES LAST_LOGIN_TIME TIME_LIMIT TIME_TODAY)); };

$handlers{ADDED} = sub {
    my ($setting, $data) = @_;

    my @dataParts = split /\s+/, $data;
    if (@dataParts > 1 && $dataParts[0] =~ /(\d+)/)
    {
        return ['ADDED_DATE', 'ADDED_USER'], [$1, $dbh->quote($dataParts[1])];
    }

    return [], [];
};

$handlers{GENERAL} = sub {
    my ($setting, $data) = @_;

    my @dataParts = split /\s+/, $data;
    my @fieldNames = qw(WKLY_ALLOTMENT IDLE_TIME MAX_DLSPEED MAX_ULSPEED);
    my (@fields, @values);

    if (@dataParts < @fieldNames)
    {
        return [], [];
    }

    push(@fields, $fieldNames[0]);
    push(@values, $dbh->quote($dataParts[0]));
    for my $i (1 .. $#fields)
    {
        if ($dataParts[$i] =~ /([+-]?\d+)/)
        {
            push(@fields, $fieldNames[$i]);
            push(@values, $1);
        }
        else
        {
            print STDERR "\$handler{GENERAL}->($setting, $data): Integer-fields for $fieldNames[$i] did not contain a valid integer, skipping.\n";
        }
    }

    return \@fields, \@values;
};

# First we cache all the data from the passwd file.
my %passwdInfo = parsePasswd($passwd);
if (!keys(%passwdInfo))
{
    print STDERR "Could not parse passwd! Exiting.";
    exit 1;
}

# Then we go over each file in the specified directory,
# check if the user is in the passwd cache, and if so - 
# call parseUserfile on it (to generate a mysql query) and
# send this to $dbh->do();
opendir(my $handle, $path)
  or die("Could not open directory '$path' for reading.\n");
while (my $entry = readdir($handle))
{
    next if not -f $entry;
    next if $entry =~ /^\./;

    if (!exists($passwdInfo{$entry}))
    {
        print STDERR "Skipping '$entry', because there's no matching passwd entry.\n";
        next;
    }

    my $query = parseUserfile($entry, %{$passwdInfo{$entry}});
    if (!defined($query))
    {
        print STDERR "Could not parse userfile for '$entry'. Skipping.\n";
        next;
    }

    if (!defined $dbh->do($query))
    {
        print STDERR "Could not execute query for '$entry': $dbh->errstr.\n";
        print STDERR "Query: $query\n";
    }
}
closedir($handle);

# This sub parses a passwd-file into a hash-of-hashes,
# each key corresponds to a username in the file,
# and the values are key-value pairs from the passwd file.
# (Keys: username, password, uid, gid, date,
# homedir, shell)
sub parsePasswd {
    my $path = shift;
    return [] if not defined open(my $filehandle, "<$path");
    
    my @fields = qw(username password uid gid date homedir shell);
    my %info;
    while (<$filehandle>)
    {
        my %user;

        my @values = split /:/;
        if (@values != @fields)
        {
            print STDERR "Not the 'right' amount of values in the passwd-file '$path', line $.. (". scalar @values .' vs '. scalar @fields .')';
            next;
        }

        @user{@fields} = @values;
        $info{$user{username}} = \%user;
    }

    return %info;
}

# This sub goes through each line in a userfile, and
# generates a valid SQL query by calling the handler for each
# 'command' in the file, and parts of the SQL 
sub parseUserfile {
    my ($path, %passwd) = @_;
    return if not defined open(my $filehandle, "<$path");

    # These there variables contain all the fields and values
    # we'll need to insert for this user, after we've parsed the userfile.
    my (@queryFields, @queryValues, %commaSeparated);

    # The important entries from the passwd-file.
    for my $key (qw(username password date homedir))
    {
        push(@queryFields, $key);
        push(@queryValues, $dbh->quote($passwd{$key}));
    }
    push(@queryFields, qw(uid gid));
    push(@queryValues, $passwd{uid}, $passwd{gid});

    while (<$filehandle>)
    {
        # First thing we do is delete comments, and remove 
        # whitespace before and after the line. We skip
        # lines that end up empty (whitespace-only lines,
        # comment-only lines)
        s/#.*//g; s/^\s+|\s+$//g;
        next if not length;

        my ($command, $data) = split /\s+/, $_, 2;

        # If there is no handler, we check if it's one of three special
        # commands (that're comma-separated in the DB), or emit an error,
        # and continue to the next stanza.
        if (!exists($handlers{$command}))
        {
            if ($command eq 'IP' || $command eq 'GROUPS' || $command eq 'PRIVATE')
            {
                push(@{$commaSeparated{$command} ||= []}, $data);
            }
            else
            {
                print STDERR "Unhandled command '$command', ignoring.\n";
            }
            next;
        }

        # We get what DB-columns & DB-data this stanza represents by the handlers
        # defined above.
        my ($fields, $values) = $handlers{$command}->($command, $data);
        if (@$fields != @$values)
        {
            print STDERR "Handler for '$command' on userfile '$path' returned differing number of fields and values. Ignoring.\n";
            next;
        }

        push(@queryFields, @$fields);
        push(@queryValues, @$values);
    }

    # These are the previously mentioned three special comma-separated 
    # DB-values. We join the arrays, and add them to the fields & values.
    for my $special (qw(IP GROUPS PRIVATE))
    {
        push(@queryFields, $special);
        push(@queryValues, $dbh->quote(join(',', @{$commaSeparated{$special} || []})));
    }
    close($filehandle);

    return 'INSERT INTO users ('. join(', ', @queryFields) .') VALUES ('. join(', ', @queryValues) .')';
}

# Here we defined reusable handlers.
# * There's one for the section-triplet commands (up & download-stats, plus nukes).
# * There's one for the section-specific integers (ratio, credits)
# * There's one for generic strings that need to be escaped.
# * There's one for generic integers that need to be sent through untouched. ;)
sub handleSectionSpecificTriples {
    my ($setting, $data) = @_;

    my (@fields, @values);

    my $section = 0;
    while ($data =~ /([+-]?\d+)\s+([+-]?\d+)\s+([+-]?\d+)/goc)
    {
        push(@values, $1, $2, $3);
        $section++;
    }

    for my $i (0 .. ($section - 1))
    {
        if ($setting eq 'NUKE')
        {
            push(@fields, "${setting}_$nukeTypes[0]_${i}");
            push(@fields, "${setting}_$nukeTypes[1]_${i}");
            push(@fields, "${setting}_$nukeTypes[2]_${i}");
        }
        else
        {
            push(@fields, "${setting}_$normalTypes[0]_${i}");
            push(@fields, "${setting}_$normalTypes[1]_${i}");
            push(@fields, "${setting}_$normalTypes[2]_${i}");
        }
    }

    return \@fields, \@values;
}
sub handleSectionSpecificInteger {
    my ($setting, $data) = @_;
    my @integers = split /\s+/, $data;

    my (@fields, @values);
    for my $i (0 .. $#integers)
    {
        if ($integers[$i] =~ /([+-]?\d+)/)
        {
            push(@fields, "${setting}_$i");
            push(@values, $1);
        }
        else
        {
            print STDERR "handleSectionSpecificInteger($setting, $data): Integer-field for section $i did not contain a valid integer, skipping.\n";
        }
    }
    return \@fields, \@values;
}
sub handleQuoted {
    my ($setting, $data) = @_;
    return [$setting], [$dbh->quote($data)];
}
sub handleIntegers {
    my ($setting, $data, @fieldNames) = @_;

    my @dataParts = split /\s+/, $data;
    if (@dataParts != @fieldNames)
    {
        print STDERR "handleIntegers($setting, $data): Differing amount of fieldnames and dataparts (".
                        scalar @fieldNames ." and ". scalar @dataParts . "\n";
        return [], [];
    }
    
    my (@fields, @values);

    for my $i (0 .. $#fieldNames)
    {
        if ($dataParts[$i] =~ /([+-]?\d+)/)
        {
            push(@fields, $fieldNames[$i]);
            push(@values, $1);
        }
        else
        {
            print STDERR "handleIntegers($setting, $data): field $i did not contain an integer, ignoring. :|\n";
        }
    }

    return \@fields, \@values;
}

# Taken from mkpass (Copyright Noah S. Friedman)
# Licensed under GPLv2, original source at http://www.splode.com/~friedman/software/scripts/src/mkpass
sub input_noecho
{
    my $interactivep = -t STDIN;
    my ($prompt) = @_;
    my $tty;
    my $c_lflag;
    my %trap_sigs = ( HUP  =>  1,
            INT  =>  2,
            QUIT =>  3,
            TERM => 15);
    my %sig_orig;

    # If stdin is a tty, disable echo while reading password.
    if (-t STDIN)
    {
        $tty = POSIX::Termios->new;
        my $fd = fileno (STDIN);

        $tty->getattr ($fd);
        $c_lflag = $tty->getlflag;

        # Set up handlers to restore tty on typical signals
        my $restore = sub {
            $tty->setlflag ($c_lflag);
            $tty->setattr (fileno (STDIN));
            my $signum = $trap_sigs{$_[0]};
            print STDERR "\nExiting on signal $signum (SIG$_[0])\n";
            # 7th bit set indicates lower 6 bits represent a
            # signal number (0x80 == 2**7)
            exit (0x80 | $signum);
        };
        map { $sig_orig{$_} = $SIG{$_} || 'DEFAULT';
            $SIG{$_} = $restore } keys %trap_sigs;

            $tty->setlflag ($c_lflag & ~&POSIX::ECHO);
            $tty->setattr ($fd);
    }

    # Temporarily disable buffering on stderr, which is where prompt is printed.
    my $fh_orig = select (STDERR);
    my $stderr_bufp = $|;
    $| = 1;
    $prompt = "Password:" unless defined $prompt;
    print $prompt;
    my $input = <STDIN>;
    chomp $input if defined $input;
    $| = $stderr_bufp;
    select ($fh_orig);

    # Restore echo afterward, if it was originally on;
    # and restore signal handlers
    print STDERR "\n" if $interactivep;
    if ($tty)
    {
        $tty->setlflag ($c_lflag);
        $tty->setattr (fileno (STDIN));
        map { $SIG{$_} = $sig_orig{$_} } keys %trap_sigs;
    }

    return $input;
}