#!/usr/bin/perl -w
use strict;
use vars qw($VERSION);
$VERSION = '0.03';
$|++;
=head1 NAME
cpanstats-createdb - DB creation code for CPAN Testers.
=head1 SYNOPSIS
perl cpanstats-createdb.pl
=head1 DESCRIPTION
Generates the databases used to drive the various CPAN Testers websites and
toolsets.
There are 3 databases created by this tool:
* CPANSTATS - MySQL cpanstats database
* LITESTATS - SQLite cpanstats database
* METABASE - MySQL Metabase database
If you require the Uploads database, please see the distribution
CPAN::Testers::Data::Uploads.
NOTE: Any existing database will be destroyed.
=cut
# -------------------------------------
# Library Modules
use Config::IniFiles;
use CPAN::Testers::Common::DBUtils;
use Getopt::Long;
# -------------------------------------
# Variables
# -------------------------------------
# Routines
sub init_options {
GetOptions( \%options,
'config=s',
'help|h',
'version|v'
);
help(1) if($options{help});
help(0) if($options{version});
help(1,"Must specific the configuration file") unless( $options{config});
help(1,"Configuration file [$options{config}] not found") unless(-f $options{config});
# load configuration
my $cfg = Config::IniFiles->new( -file => $options{config} );
# configure databases
for my $db (qw(CPANSTATS LITESTATS METABASE)) {
die "No configuration for $db database\n" unless($cfg->SectionExists($db));
my %opts = map {$_ => $cfg->val($db,$_);} qw(driver database dbfile dbhost dbport dbuser dbpass);
unlink $opts{database} if($opts{driver} =~ /SQLite/i);
$options{$db} = CPAN::Testers::Common::DBUtils->new(%opts);
die "Cannot configure $db database\n" unless($options{$db});
}
}
sub process {
my @sql;
push @sql,
'DROP TABLE IF EXISTS cpanstats',
'CREATE TABLE cpanstats (
id int(10) unsigned NOT NULL,
guid char(36) NOT NULL,
state varchar(32),
postdate varchar(8),
tester varchar(255),
dist varchar(255),
version varchar(255),
platform varchar(255),
perl varchar(255),
osname varchar(255),
osvers varchar(255),
fulldate varchar(32),
PRIMARY KEY (id,guid))',
'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
)',
'DROP TABLE IF EXISTS release_data',
'CREATE TABLE release_data (
dist varchar(255) NOT NULL,
version varchar(255) NOT NULL,
id int(10) unsigned NOT NULL,
guid char(36) NOT NULL,
oncpan tinyint(4) default 0,
distmat tinyint(4) default 0,
perlmat tinyint(4) default 0,
patched tinyint(4) default 0,
pass int(10) default 0,
fail int(10) default 0,
na int(10) default 0,
unknown int(10) default 0,
PRIMARY KEY (id,guid),
INDEX (dist,version)
)',
'DROP TABLE IF EXISTS release_summary',
'CREATE TABLE release_summary (
dist varchar(255) NOT NULL,
version varchar(255) NOT NULL,
id int(10) unsigned NOT NULL,
guid char(36) NOT NULL,
oncpan tinyint(4) default 0,
distmat tinyint(4) default 0,
perlmat tinyint(4) default 0,
patched tinyint(4) default 0,
pass int(10) default 0,
fail int(10) default 0,
na int(10) default 0,
unknown int(10) default 0,
PRIMARY KEY (id,guid),
INDEX (dist,version)
)',
'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)
) ENGINE=MyISAM',
'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)
) ENGINE=MyISAM',
'DROP TABLE IF EXISTS summary',
'CREATE TABLE summary (
type varchar(8) NOT NULL,
name varchar(255) NOT NULL,
lastid int(10) unsigned NOT NULL,
dataset blob,
PRIMARY KEY (type,name)
) ENGINE=MyISAM',
'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,
id int(10) unsigned DEFAULT 0
) ENGINE=MyISAM',
'DROP TABLE IF EXISTS osname',
'CREATE TABLE osname (
id int(10) unsigned NOT NULL auto_increment,
osname varchar(255),
ostitle varchar(255),
PRIMARY KEY (id)
)'
;
$options{CPANSTATS}->do_query($_) for(@sql);
@sql = ();
push @sql,
'PRAGMA auto_vacuum = 1',
'CREATE TABLE cpanstats (
id INTEGER PRIMARY KEY,
guid TEXT,
state TEXT,
postdate TEXT,
tester TEXT,
dist TEXT,
version TEXT,
platform TEXT,
perl TEXT,
osname TEXT,
osvers TEXT,
date TEXT)',
'CREATE INDEX distverstate ON cpanstats (dist, version, state)',
'CREATE INDEX ixperl ON cpanstats (perl)',
'CREATE INDEX ixplat ON cpanstats (platform)',
'CREATE INDEX ixdate ON cpanstats (postdate)';
$options{LITESTATS}->do_query($_) for(@sql);
@sql = ();
push @sql,
'PRAGMA auto_vacuum = 1',
'DROP TABLE IF EXISTS metabase',
'CREATE TABLE metabase (
guid char(36) NOT NULL,
id int(10) unsigned NOT NULL,
updated varchar(32) default NULL,
report longblob NOT NULL,
PRIMARY KEY (guid),
INDEX (id),
INDEX (updated)
)',
'DROP TABLE IF EXISTS testers_email',
'CREATE TABLE testers_email (
id int(10) unsigned NOT NULL auto_increment,
resource varchar(64) NOT NULL,
fullname varchar(255) NOT NULL,
email varchar(255) default NULL,
PRIMARY KEY (id),
KEY resource (resource)
)'
;
$options{METABASE}->do_query($_) for(@sql);
}
sub help {
my ($full,$mess) = @_;
print "\n$mess\n\n" if($mess);
if($full) {
print <<HERE;
Usage: $0 \\
[-config=<file>] [-h] [-v]
--config=<file> database configuration file
-h this help screen
-v program version
HERE
}
print "$0 v$VERSION\n";
exit(0);
}
__END__
=back
=head1 BUGS, PATCHES & FIXES
There are no known bugs at the time of this release. However, if you spot a
bug or are experiencing difficulties, that is not explained within the POD
documentation, please send bug reports and patches to the RT Queue (see below).
Fixes are dependant upon their severity and my availablity. Should a fix not
be forthcoming, please feel free to (politely) remind me.
RT Queue -
http://rt.cpan.org/Public/Dist/Display.html?Name=CPAN-Testers-Data-Generator
=head1 SEE ALSO
L<CPAN::Testers::WWW::Reports>,
L<CPAN::Testers::WWW::Statistics>
F<http://www.cpantesters.org/>,
F<http://stats.cpantesters.org/>,
F<http://wiki.cpantesters.org/>
=head1 AUTHOR
Barbie, <barbie@cpan.org>
for Miss Barbell Productions <http://www.missbarbell.co.uk>.
=head1 COPYRIGHT AND LICENSE
Copyright (C) 2008-2011 Barbie for Miss Barbell Productions.
This module is free software; you can redistribute it and/or
modify it under the same terms as Perl itself.
=cut