The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
BARBIE / CPAN-Testers-Data-Uploads-0.21 / t / 05setup_db-uploads.t 
#!/usr/bin/perl -w
use strict;

#----------------------------------------------------------------------------
# Library Modules

use CPAN::Testers::Common::DBUtils;
use File::Path;
use Test::More;

#----------------------------------------------------------------------------
# Tests

mkpath( 't/_DBDIR' );

eval "use Test::Database";
if($@)  { plan skip_all => "Test::Database required for DB testing"; }
else    { plan tests    => 2 }

my $td;
if($td = Test::Database->handle( 'mysql' )) {
    create_mysql_databases($td);
} elsif($td = Test::Database->handle( 'SQLite' )) {
    create_sqlite_databases($td);
}

SKIP: {
    skip "No supported databases available", 2  unless($td);

    my %opts;
    ($opts{dsn}, $opts{dbuser}, $opts{dbpass}) =  $td->connection_info();
    ($opts{driver})    = $opts{dsn} =~ /dbi:([^;:]+)/;
    ($opts{database})  = $opts{dsn} =~ /database=([^;]+)/;
    ($opts{database})  = $opts{dsn} =~ /dbname=([^;]+)/     unless($opts{database});
    ($opts{dbhost})    = $opts{dsn} =~ /host=([^;]+)/;
    ($opts{dbport})    = $opts{dsn} =~ /port=([^;]+)/;
    my %options = map {my $v = $opts{$_}; defined($v) ? ($_ => $v) : () }
                        qw(driver database dbfile dbhost dbport dbuser dbpass);

    create_config(\%options);

    # create new instance from Test::Database object
    my $ct = CPAN::Testers::Common::DBUtils->new(%options);
    isa_ok($ct,'CPAN::Testers::Common::DBUtils');

    # insert records
    my $sql = 'INSERT INTO uploads (type,author,dist,version,filename,released) VALUES (?,?,?,?,?,?)';
    while(<DATA>){
        s/\s+$//;
        $ct->do_query( $sql, split(/\|/,$_) );
    }

    my @rows = $ct->get_query('hash','select count(*) as count from uploads');
    is($rows[0]->{count}, 63, "row count for uploads");
}

sub create_config {
    my $options = shift;

    # main config
    my $f = 't/_DBDIR/test-config.ini';
    unlink $f if -f $f;

    my $dbcfg = join("\n", map { "$_=$options->{$_}" } grep { $options->{$_}} qw(driver database dbfile dbhost dbport dbuser dbpass) );

    my $fh = IO::File->new($f,'w+') or return;
    print $fh <<PRINT;
[MASTER]
lastfile=t/_DBDIR/lastid.txt
BACKPAN=t/_DBDIR/BACKPAN/authors/id
CPAN=t/_DBDIR/CPAN/authors/id
journal=t/_DBDIR/journal.sql
logfile=t/_DBDIR/upload.log
logclean=1

; database configuration

[UPLOADS]
$dbcfg

[BACKUPS]
drivers=<<EOT
SQLite
CSV
EOT

[SQLite]
driver=SQLite
database=t/_DBDIR/uploads.db

[CSV]
driver=CSV
dbfile=t/_DBDIR/uploads.csv

PRINT

    $fh->close;
}

sub create_sqlite_databases {
    my $db = shift;

    my @create_cpanstats = (
        'PRAGMA auto_vacuum = 1',
	    'DROP TABLE IF EXISTS `uploads`',
        'CREATE TABLE `uploads` (
            `type`      text    NOT NULL,
            `author`    text    NOT NULL,
            `dist`      text    NOT NULL,
            `version`   text    NOT NULL,
            `filename`  text    NOT NULL,
            `released`  int     NOT NULL,
            PRIMARY KEY  (`author`,`dist`,`version`)
        )',
	    'DROP TABLE IF EXISTS `uploads_failed`',
        'CREATE TABLE `uploads_failed` (
            `source`    text    NOT NULL,
            `type`      text,
            `dist`      text,
            `version`   text,
            `file`      text,
            `pause`     text,
            `created`   int,
            PRIMARY KEY  (`source`)
        )',
	    'DROP TABLE IF EXISTS `ixlatest`',
        'CREATE TABLE `ixlatest` (
            `dist`      text    NOT NULL,
            `version`   text    NOT NULL,
            `released`  int     NOT NULL,
            `author`    text    NOT NULL,
            `oncpan`    int     DEFAULT 0,
            PRIMARY KEY (`dist`,`author`)
        )',
	    'DROP TABLE IF EXISTS `page_requests`',
        'CREATE TABLE `page_requests` (
            `type`      text        NOT NULL,
            `name`      text        NOT NULL,
            `weight`    int         NOT NULL,
            `created`   timestamp   NOT NULL DEFAULT CURRENT_TIMESTAMP,
            `id`        int         DEFAULT 0
        )'
    );

    dosql($db,\@create_cpanstats);
}

sub create_mysql_databases {
    my $db = shift;

    my @create_cpanstats = (
	    'DROP TABLE IF EXISTS `uploads`',
        'CREATE TABLE `uploads` (
            `type`      varchar(10)         NOT NULL,
            `author`    varchar(32)         NOT NULL,
            `dist`      varchar(255)        NOT NULL,
            `version`   varchar(255)        NOT NULL,
            `filename`  varchar(255)        NOT NULL,
            `released`  int(16)             NOT NULL,
            PRIMARY KEY (`author`,`dist`,`version`)
        )',
	    'DROP TABLE IF EXISTS `uploads_failed`',
        'CREATE TABLE `uploads_failed` (
            `source`    varchar(255)        NOT NULL,
            `type`      varchar(255)        DEFAULT NULL,
            `dist`      varchar(255)        DEFAULT NULL,
            `version`   varchar(255)        DEFAULT NULL,
            `file`      varchar(255)        DEFAULT NULL,
            `pause`     varchar(255)        DEFAULT NULL,
            `created`   int(11) unsigned    DEFAULT NULL,
            PRIMARY KEY (`source`)
        )',
	    'DROP TABLE IF EXISTS `ixlatest`',
        'CREATE TABLE `ixlatest` (
            `dist`      varchar(255)        NOT NULL,
            `version`   varchar(255)        NOT NULL,
            `released`  int(16)             NOT NULL,
            `author`    varchar(32)         NOT NULL,
            `oncpan`    tinyint(4)          DEFAULT 0,
            PRIMARY KEY (`dist`,`author`),
            KEY `IXDISTX` (`dist`),
            KEY `IXAUTHX` (`author`)
        )',
	    'DROP TABLE IF EXISTS `page_requests`',
        'CREATE TABLE `page_requests` (
            `type`      varchar(8)          NOT NULL,
            `name`      varchar(255)        NOT NULL,
            `weight`    int(2) unsigned     NOT NULL,
            `created`   timestamp           NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            `id`        int(10) unsigned    DEFAULT 0,
            KEY `IXNAME` (`name`),
            KEY `IXTYPE` (`type`),
            KEY `IXID` (`id`)
        )'
    
    );

    dosql($db,\@create_cpanstats);
}

sub dosql {
    my ($db,$sql) = @_;

    for(@$sql) {
        #diag "SQL: [$db] $_";
        eval { $db->dbh->do($_); };
        if($@) {
            diag $@;
            return 1;
        }
    }

    return 0;
}


#select * from uploads where dist in ('AEAE', 'AI-NeuralNet-BackProp', 'AI-NeuralNet-Mesh', 'AI-NeuralNet-SOM', 'AOL-TOC', 'Abstract-Meta-Class', 'Acme', 'Acme-Anything', 'Acme-BOPE', 'Acme-Brainfuck', 'Acme-Buffy', 'Acme-CPANAuthors-Canadian', 'Acme-CPANAuthors-CodeRepos', 'Acme-CPANAuthors-French', 'Acme-CPANAuthors-Japanese');
#type|author|dist|version|filename|released
__DATA__
cpan|LBROCARD|Acme-Buffy|1.3|Acme-Buffy-1.3.tar.gz|1017236268
cpan|LBROCARD|Acme-Buffy|1.5|Acme-Buffy-1.5.tar.gz|1177769034
cpan|LBROCARD|Acme-Buffy|1.4|Acme-Buffy-1.4.tar.gz|1157733085
backpan|LBROCARD|Acme-Buffy|1.1|Acme-Buffy-1.1.tar.gz|990548103
backpan|LBROCARD|Acme-Buffy|1.2|Acme-Buffy-1.2.tar.gz|997617194
cpan|DRRHO|AI-NeuralNet-SOM|0.04|AI-NeuralNet-SOM-0.04.tar.gz|1182080003
cpan|DRRHO|AI-NeuralNet-SOM|0.06|AI-NeuralNet-SOM-0.06.tar.gz|1211531083
cpan|DRRHO|AI-NeuralNet-SOM|0.05|AI-NeuralNet-SOM-0.05.tar.gz|1200513667
cpan|DRRHO|AI-NeuralNet-SOM|0.01|AI-NeuralNet-SOM-0.01.tar.gz|1181057025
cpan|DRRHO|AI-NeuralNet-SOM|0.03|AI-NeuralNet-SOM-0.03.tar.gz|1181848391
cpan|DRRHO|AI-NeuralNet-SOM|0.07|AI-NeuralNet-SOM-0.07.tar.gz|1211612835
cpan|DRRHO|AI-NeuralNet-SOM|0.02|AI-NeuralNet-SOM-0.02.tar.gz|1181487612
cpan|VOISCHEV|AI-NeuralNet-SOM|0.01|AI-NeuralNet-SOM-0.01.tar.gz|970252633
cpan|VOISCHEV|AI-NeuralNet-SOM|0.02|AI-NeuralNet-SOM-0.02.tar.gz|970684892
cpan|INGY|Acme|1.11111|Acme-1.11111.tar.gz|1137626100
backpan|INGY|Acme|1.111|Acme-1.111.tar.gz|1079905156
backpan|INGY|Acme|1.11|Acme-1.11.tar.gz|1079870865
backpan|INGY|Acme|1.00|Acme-1.00.tar.gz|1079868743
backpan|INGY|Acme|1.1111|Acme-1.1111.tar.gz|1111906013
cpan|ISHIGAKI|Acme-CPANAuthors-Japanese|0.071226|Acme-CPANAuthors-Japanese-0.071226.tar.gz|1198658704
cpan|ISHIGAKI|Acme-CPANAuthors-Japanese|0.080522|Acme-CPANAuthors-Japanese-0.080522.tar.gz|1211389830
cpan|ISHIGAKI|Acme-CPANAuthors-CodeRepos|0.080522|Acme-CPANAuthors-CodeRepos-0.080522.tar.gz|1211390902
cpan|SAPER|Acme-CPANAuthors-French|0.04|Acme-CPANAuthors-French-0.04.tar.gz|1221955693
backpan|SAPER|Acme-CPANAuthors-French|0.01|Acme-CPANAuthors-French-0.01.tar.gz|1221268256
cpan|SAPER|Acme-CPANAuthors-French|0.05|Acme-CPANAuthors-French-0.05.tar.gz|1222119306
backpan|SAPER|Acme-CPANAuthors-French|0.02|Acme-CPANAuthors-French-0.02.tar.gz|1221355420
backpan|SAPER|Acme-CPANAuthors-French|0.03|Acme-CPANAuthors-French-0.03.tar.gz|1221696260
cpan|SAPER|Acme-CPANAuthors-French|0.06|Acme-CPANAuthors-French-0.06.tar.gz|1225315698
upload|SAPER|Acme-CPANAuthors-French|0.07|Acme-CPANAuthors-French-0.07.tar.gz|1225662681
upload|ZOFFIX|Acme-CPANAuthors-Canadian|0.0101|Acme-CPANAuthors-Canadian-0.0101.tar.gz|1225664601
cpan|GARU|Acme-BOPE|0.01|Acme-BOPE-0.01.tar.gz|1222060546
backpan|JESSE|Acme-Buffy|1.3|Acme-Buffy-1.3.tar.gz|1065349193
cpan|JETEVE|AEAE|0.02|AEAE-0.02.tar.gz|1139566791
cpan|JETEVE|AEAE|0.01|AEAE-0.01.tar.gz|1138724959
backpan|JJORE|Acme-Anything|0.01|Acme-Anything-0.01.tar.gz|1186005823
cpan|JJORE|Acme-Anything|0.02|Acme-Anything-0.02.tar.gz|1194827066
cpan|JBRYAN|AI-NeuralNet-Mesh|0.43|AI-NeuralNet-Mesh-0.43.zip|968921615
cpan|JBRYAN|AI-NeuralNet-BackProp|0.40|AI-NeuralNet-BackProp-0.40.zip|964250318
cpan|JBRYAN|AI-NeuralNet-Mesh|0.31|AI-NeuralNet-Mesh-0.31.zip|967191936
cpan|JBRYAN|AI-NeuralNet-BackProp|0.77|AI-NeuralNet-BackProp-0.77.zip|966067868
cpan|JBRYAN|AI-NeuralNet-BackProp|0.42|AI-NeuralNet-BackProp-0.42.zip|964604318
cpan|JBRYAN|AI-NeuralNet-Mesh|0.44|AI-NeuralNet-Mesh-0.44.zip|968964981
cpan|JBRYAN|AI-NeuralNet-BackProp|0.89|AI-NeuralNet-BackProp-0.89.zip|966496907
cpan|JBRYAN|AI-NeuralNet-Mesh|0.20|AI-NeuralNet-Mesh-0.20.zip|967009309
backpan|JALDHAR|Acme-Brainfuck|1.1.0|Acme-Brainfuck-1.1.0.tar.gz|1081229428
cpan|JALDHAR|Acme-Brainfuck|1.1.1|Acme-Brainfuck-1.1.1.tar.gz|1081268735
backpan|JALDHAR|Acme-Brainfuck|1.0.0|Acme-Brainfuck-1.0.0.tar.gz|1031080554
cpan|JHARDING|AOL-TOC|0.32|AOL-TOC-0.32.tar.gz|962207388
cpan|JHARDING|AOL-TOC|0.340|AOL-TOC-0.340.tar.gz|966917420
cpan|JHARDING|AOL-TOC|0.33|AOL-TOC-0.33.tar.gz|962694743
cpan|ADRIANWIT|Abstract-Meta-Class|0.09|Abstract-Meta-Class-0.09.tar.gz|1212364076
backpan|ADRIANWIT|Abstract-Meta-Class|0.07|Abstract-Meta-Class-0.07.tar.gz|1212267288
backpan|ADRIANWIT|Abstract-Meta-Class|0.04|Abstract-Meta-Class-0.04.tar.gz|1211589222
cpan|ADRIANWIT|Abstract-Meta-Class|0.08|Abstract-Meta-Class-0.08.tar.gz|1212345949
backpan|ADRIANWIT|Abstract-Meta-Class|0.01|Abstract-Meta-Class-0.01.tar.gz|1210001395
backpan|ADRIANWIT|Abstract-Meta-Class|0.05|Abstract-Meta-Class-0.05.tar.gz|1211645127
cpan|ADRIANWIT|Abstract-Meta-Class|0.10|Abstract-Meta-Class-0.10.tar.gz|1212962154
cpan|ADRIANWIT|Abstract-Meta-Class|0.12|Abstract-Meta-Class-0.12.tar.gz|1224423414
cpan|ADRIANWIT|Abstract-Meta-Class|0.11|Abstract-Meta-Class-0.11.tar.gz|1220826243
backpan|ADRIANWIT|Abstract-Meta-Class|0.03|Abstract-Meta-Class-0.03.tar.gz|1210105676
backpan|ADRIANWIT|Abstract-Meta-Class|0.06|Abstract-Meta-Class-0.06.tar.gz|1211732184
upload|ADRIANWIT|Abstract-Meta-Class|0.13|Abstract-Meta-Class-0.13.tar.gz|1227483540
cpan|ISHIGAKI|Acme-CPANAuthors-Japanese|0.090101|Acme-CPANAuthors-Japanese-0.090101.tar.gz|1230748955