[usenet/newsstats.git] / install / install.pl

#! /usr/bin/perl
#
# install.pl
#
# This script will create database tables as necessary.
#
# It is part of the NewsStats package.
#
# Copyright (c) 2010-2013 Thomas Hochstein <thh@inter.net>
#
# It can be redistributed and/or modified under the same terms under
# which Perl itself is published.

BEGIN {
  our $VERSION = "0.01";
  use File::Basename;
  # we're in .../install, so our module is in ../lib
  push(@INC, dirname($0).'/../lib');
}
use strict;
use warnings;

use NewsStats qw(:DEFAULT);

use Cwd;

use DBI;
use Getopt::Long qw(GetOptions);
Getopt::Long::config ('bundling');

################################# Main program #################################

### read commandline options
my ($OptUpdate,$OptConfFile);
GetOptions ('u|update=s' => \$OptUpdate,
            'conffile=s' => \$OptConfFile,
            'h|help'     => \&ShowPOD,
            'V|version'  => \&ShowVersion) or exit 1;

### change working directory to .. (as we're in .../install)
chdir dirname($FullPath).'/..';
my $Path = cwd();

### read configuration
print("Reading configuration.\n");
my %Conf = %{ReadConfig($OptConfFile)};

##### --------------------------------------------------------------------------
##### Database table definitions
##### --------------------------------------------------------------------------

my $DBCreate = <<SQLDB;
CREATE DATABASE IF NOT EXISTS `$Conf{'DBDatabase'}` DEFAULT CHARSET=utf8;
SQLDB

my %DBCreate = ('DBTableRaw'  => <<RAW, 'DBTableGrps' => <<GRPS);
--
-- Table structure for table DBTableRaw
--

CREATE TABLE IF NOT EXISTS `$Conf{'DBTableRaw'}` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `day` date NOT NULL,
  `mid` varchar(250) character set ascii NOT NULL,
  `date` datetime NOT NULL,
  `timestamp` bigint(20) NOT NULL,
  `token` varchar(80) character set ascii NOT NULL,
  `size` bigint(20) NOT NULL,
  `peer` varchar(250) NOT NULL,
  `path` varchar(1000) NOT NULL,
  `newsgroups` varchar(1000) NOT NULL,
  `headers` longtext NOT NULL,
  `disregard` tinyint(1) default '0',
  PRIMARY KEY  (`id`),
  KEY `day` (`day`),
  KEY `mid` (`mid`),
  KEY `peer` (`peer`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Raw data';
RAW
--
-- Table structure for table DBTableGrps
--

CREATE TABLE IF NOT EXISTS `$Conf{'DBTableGrps'}` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `month` varchar(7) character set ascii NOT NULL,
  `newsgroup` varchar(100) NOT NULL,
  `postings` int(11) NOT NULL,
  `revision` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `month_newsgroup` (`month`,`newsgroup`),
  KEY `newsgroup` (`newsgroup`),
  KEY `postings` (`postings`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Postings per newsgroup';
GRPS

##### --------------------------------------------------------------------------
##### Installation / upgrade instructions
##### --------------------------------------------------------------------------

my $Install = <<INSTALL;
----------
Things left to do:

1) Setup an INN feed to feedlog.pl

   a) Edit your 'newsfeeds' file and insert something like

          ## gather statistics for NewsStats
          newsstats!\\
                  :!*,de.*\\
                  :Tc,WmtfbsPNH,Ac:$Path/feedlog.pl

      Please

      * check that you got the path to feedlog.pl right
      * check that feedlog.pl can be executed by the news user
      * adapt the pattern (here: 'de.*') to your needs

   b) Check your 'newsfeeds' syntax:

         # ctlinnd checkfile

      and reload 'newsfeeds':

         # ctlinnd reload newsfeeds 'Adding newsstats! feed'

   c) Watch your 'news.notice' and 'errlog' files:

         # tail -f /var/log/news/news.notice
         ...
         # tail -f /var/log/news/errlog

2) Watch your $Conf{'DBTableRaw'} table fill.

3) Read the documentation. ;)

Enjoy!

-thh <thh\@inter.net>
INSTALL

my $Upgrade ='';
if ($OptUpdate) {
 $Upgrade = <<UPGRADE;
----------
Your installation was upgraded from $OptUpdate to $PackageVersion.

Don't forget to restart your INN feed so that it can pick up the new version:

   # ctlinnd begin 'newsstats!'

(or whatever you called your feed).
UPGRADE
}

##### --------------------------- End of definitions ---------------------------

### create DB, if necessary
if (!$OptUpdate) {
  print "----------\nStarting database creation.\n";
  # create database
  # we can't use InitDB() as that will use a table name of
  # the table that doesn't exist yet ...
  my $DBHandle = DBI->connect(sprintf('DBI:%s:host=%s',$Conf{'DBDriver'},
                                      $Conf{'DBHost'}), $Conf{'DBUser'},
                                      $Conf{'DBPw'}, { PrintError => 0 });
  my $DBQuery = $DBHandle->prepare($DBCreate);
  $DBQuery->execute() or &Bleat(2, sprintf("Can't create database %s: %s%\n",
                                           $Conf{'DBDatabase'}, $DBI::errstr));

  printf("Database table %s created succesfully.\n",$Conf{'DBDatabase'});
  $DBHandle->disconnect;
};

### DB init, read list of tables
print "Reading database information.\n";
my $DBHandle = InitDB(\%Conf,1);
my %TablesInDB =
   %{$DBHandle->table_info('%', '%', '%', 'TABLE')->fetchall_hashref('TABLE_NAME')};

if (!$OptUpdate) {
  ##### installation mode
  # check for tables and create them, if they don't exist yet
  foreach my $Table (keys %DBCreate) {
    &CreateTable($Table);
  };
  print "Database table generation done.\n";

  # Display install instructions
  print $Install;
} else {
  ##### upgrade mode
  print "----------\nStarting upgrade process.\n";
  $PackageVersion = '0.03';
  if ($OptUpdate < $PackageVersion) {
    if ($OptUpdate < 0.02) {
      # 0.01 -> 0.02
      # &DoMySQL('...;');
      # print "v0.02: Database upgrades ...\n";
      # &PrintInstructions('0.02',<<"      INSTRUCTIONS");
      # INSTRUCTIONS
    };
  };
  # Display general upgrade instructions
  print $Upgrade;
};

# close handle
$DBHandle->disconnect;

exit(0);

################################# Subroutines ##################################

sub CreateTable {
  my $Table = shift;
  if (defined($TablesInDB{$Conf{$Table}})) {
    printf("Database table %s.%s already exists, skipping ....\n",
           $Conf{'DBDatabase'},$Conf{$Table});
    return;
  };
  my $DBQuery = $DBHandle->prepare($DBCreate{$Table});
  $DBQuery->execute() or
    &Bleat(2, sprintf("Can't create table %s in database %s: %s%\n",$Table,
                      $Conf{'DBDatabase'},$DBI::errstr));
  printf("Database table %s.%s created succesfully.\n",
         $Conf{'DBDatabase'},$Conf{$Table});
  return;
};

sub DoMySQL {
  my $SQL = shift;
  my $DBQuery = $DBHandle->prepare($SQL);
  $DBQuery->execute() or &Bleat(1, sprintf("Database error: %s\n",$DBI::errstr));
  return;
};

sub PrintInstructions {
  my ($UpVersion,$Instructions) = @_;
  print "v$UpVersion: Upgrade Instructions >>>>>\n";
  my $Padding = ' ' x (length($UpVersion) + 3);
    $Instructions =~ s/^      /$Padding/mg;
    print $Instructions;
    print "<" x (length($UpVersion) + 29) . "\n";
};


__END__

################################ Documentation #################################

=head1 NAME

install - installation script

=head1 SYNOPSIS

B<install> [B<-Vh> [--update I<version>] [--conffile I<filename>]

=head1 REQUIREMENTS

See L<doc/README>.

=head1 DESCRIPTION

This script will create database tables as necessary and configured.

=head2 Configuration

B<install> will read its configuration from F<newsstats.conf> via
Config::Auto.

See L<doc/INSTALL> for an overview of possible configuration options.

=head1 OPTIONS

=over 3

=item B<-V>, B<--version>

Print out version and copyright information and exit.

=item B<-h>, B<--help>

Print this man page and exit.

=item B<-u>, B<--update> I<version>

Don't do a fresh install, but update from I<version>.

=item B<--conffile> I<filename>

Load configuration from I<filename> instead of F<newsstats.conf>.

=back

=head1 FILES

=over 4

=item F<install/install.pl>

The script itself.

=item F<lib/NewsStats.pm>

Library functions for the NewsStats package.

=item F<etc/newsstats.conf>

Runtime configuration file.

=back

=head1 BUGS

Please report any bugs or feature requests to the author or use the
bug tracker at L<http://bugs.th-h.de/>!

=head1 SEE ALSO

=over 2

=item -

L<doc/README>

=item -

L<doc/INSTALL>

=back

This script is part of the B<NewsStats> package.

=head1 AUTHOR

Thomas Hochstein <thh@inter.net>

=head1 COPYRIGHT AND LICENSE

Copyright (c) 2010-2012 Thomas Hochstein <thh@inter.net>

This program is free software; you may redistribute it and/or modify it
under the same terms as Perl itself.

=cut
Commit	Line	Data
	1	#! /usr/bin/perl
	2	#
	3	# install.pl
	4	#
	5	# This script will create database tables as necessary.
	6	#
	7	# It is part of the NewsStats package.
	8	#
	9	# Copyright (c) 2010-2013 Thomas Hochstein <thh@inter.net>
	10	#
	11	# It can be redistributed and/or modified under the same terms under
	12	# which Perl itself is published.
	13
	14	BEGIN {
	15	our $VERSION = "0.01";
	16	use File::Basename;
	17	# we're in .../install, so our module is in ../lib
	18	push(@INC, dirname($0).'/../lib');
	19	}
	20	use strict;
	21	use warnings;
	22
	23	use NewsStats qw(:DEFAULT);
	24
	25	use Cwd;
	26
	27	use DBI;
	28	use Getopt::Long qw(GetOptions);
	29	Getopt::Long::config ('bundling');
	30
	31	################################# Main program #################################
	32
	33	### read commandline options
	34	my ($OptUpdate,$OptConfFile);
	35	GetOptions ('u\|update=s' => \$OptUpdate,
	36	'conffile=s' => \$OptConfFile,
	37	'h\|help' => \&ShowPOD,
	38	'V\|version' => \&ShowVersion) or exit 1;
	39
	40	### change working directory to .. (as we're in .../install)
	41	chdir dirname($FullPath).'/..';
	42	my $Path = cwd();
	43
	44	### read configuration
	45	print("Reading configuration.\n");
	46	my %Conf = %{ReadConfig($OptConfFile)};
	47
	48	##### --------------------------------------------------------------------------
	49	##### Database table definitions
	50	##### --------------------------------------------------------------------------
	51
	52	my $DBCreate = <<SQLDB;
	53	CREATE DATABASE IF NOT EXISTS `$Conf{'DBDatabase'}` DEFAULT CHARSET=utf8;
	54	SQLDB
	55
	56	my %DBCreate = ('DBTableRaw' => <<RAW, 'DBTableGrps' => <<GRPS);
	57	--
	58	-- Table structure for table DBTableRaw
	59	--
	60
	61	CREATE TABLE IF NOT EXISTS `$Conf{'DBTableRaw'}` (
	62	`id` bigint(20) unsigned NOT NULL auto_increment,
	63	`day` date NOT NULL,
	64	`mid` varchar(250) character set ascii NOT NULL,
	65	`date` datetime NOT NULL,
	66	`timestamp` bigint(20) NOT NULL,
	67	`token` varchar(80) character set ascii NOT NULL,
	68	`size` bigint(20) NOT NULL,
	69	`peer` varchar(250) NOT NULL,
	70	`path` varchar(1000) NOT NULL,
	71	`newsgroups` varchar(1000) NOT NULL,
	72	`headers` longtext NOT NULL,
	73	`disregard` tinyint(1) default '0',
	74	PRIMARY KEY (`id`),
	75	KEY `day` (`day`),
	76	KEY `mid` (`mid`),
	77	KEY `peer` (`peer`)
	78	) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Raw data';
	79	RAW
	80	--
	81	-- Table structure for table DBTableGrps
	82	--
	83
	84	CREATE TABLE IF NOT EXISTS `$Conf{'DBTableGrps'}` (
	85	`id` bigint(20) unsigned NOT NULL auto_increment,
	86	`month` varchar(7) character set ascii NOT NULL,
	87	`newsgroup` varchar(100) NOT NULL,
	88	`postings` int(11) NOT NULL,
	89	`revision` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
	90	PRIMARY KEY (`id`),
	91	UNIQUE KEY `month_newsgroup` (`month`,`newsgroup`),
	92	KEY `newsgroup` (`newsgroup`),
	93	KEY `postings` (`postings`)
	94	) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Postings per newsgroup';
	95	GRPS
	96
	97	##### --------------------------------------------------------------------------
	98	##### Installation / upgrade instructions
	99	##### --------------------------------------------------------------------------
	100
	101	my $Install = <<INSTALL;
	102	----------
	103	Things left to do:
	104
	105	1) Setup an INN feed to feedlog.pl
	106
	107	a) Edit your 'newsfeeds' file and insert something like
	108
	109	## gather statistics for NewsStats
	110	newsstats!\\
	111	:!,de.\\
	112	:Tc,WmtfbsPNH,Ac:$Path/feedlog.pl
	113
	114	Please
	115
	116	* check that you got the path to feedlog.pl right
	117	* check that feedlog.pl can be executed by the news user
	118	* adapt the pattern (here: 'de.*') to your needs
	119
	120	b) Check your 'newsfeeds' syntax:
	121
	122	# ctlinnd checkfile
	123
	124	and reload 'newsfeeds':
	125
	126	# ctlinnd reload newsfeeds 'Adding newsstats! feed'
	127
	128	c) Watch your 'news.notice' and 'errlog' files:
	129
	130	# tail -f /var/log/news/news.notice
	131	...
	132	# tail -f /var/log/news/errlog
	133
	134	2) Watch your $Conf{'DBTableRaw'} table fill.
	135
	136	3) Read the documentation. ;)
	137
	138	Enjoy!
	139
	140	-thh <thh\@inter.net>
	141	INSTALL
	142
	143	my $Upgrade ='';
	144	if ($OptUpdate) {
	145	$Upgrade = <<UPGRADE;
	146	----------
	147	Your installation was upgraded from $OptUpdate to $PackageVersion.
	148
	149	Don't forget to restart your INN feed so that it can pick up the new version:
	150
	151	# ctlinnd begin 'newsstats!'
	152
	153	(or whatever you called your feed).
	154	UPGRADE
	155	}
	156
	157	##### --------------------------- End of definitions ---------------------------
	158
	159	### create DB, if necessary
	160	if (!$OptUpdate) {
	161	print "----------\nStarting database creation.\n";
	162	# create database
	163	# we can't use InitDB() as that will use a table name of
	164	# the table that doesn't exist yet ...
	165	my $DBHandle = DBI->connect(sprintf('DBI:%s:host=%s',$Conf{'DBDriver'},
	166	$Conf{'DBHost'}), $Conf{'DBUser'},
	167	$Conf{'DBPw'}, { PrintError => 0 });
	168	my $DBQuery = $DBHandle->prepare($DBCreate);
	169	$DBQuery->execute() or &Bleat(2, sprintf("Can't create database %s: %s%\n",
	170	$Conf{'DBDatabase'}, $DBI::errstr));
	171
	172	printf("Database table %s created succesfully.\n",$Conf{'DBDatabase'});
	173	$DBHandle->disconnect;
	174	};
	175
	176	### DB init, read list of tables
	177	print "Reading database information.\n";
	178	my $DBHandle = InitDB(\%Conf,1);
	179	my %TablesInDB =
	180	%{$DBHandle->table_info('%', '%', '%', 'TABLE')->fetchall_hashref('TABLE_NAME')};
	181
	182	if (!$OptUpdate) {
	183	##### installation mode
	184	# check for tables and create them, if they don't exist yet
	185	foreach my $Table (keys %DBCreate) {
	186	&CreateTable($Table);
	187	};
	188	print "Database table generation done.\n";
	189
	190	# Display install instructions
	191	print $Install;
	192	} else {
	193	##### upgrade mode
	194	print "----------\nStarting upgrade process.\n";
	195	$PackageVersion = '0.03';
	196	if ($OptUpdate < $PackageVersion) {
	197	if ($OptUpdate < 0.02) {
	198	# 0.01 -> 0.02
	199	# &DoMySQL('...;');
	200	# print "v0.02: Database upgrades ...\n";
	201	# &PrintInstructions('0.02',<<" INSTRUCTIONS");
	202	# INSTRUCTIONS
	203	};
	204	};
	205	# Display general upgrade instructions
	206	print $Upgrade;
	207	};
	208
	209	# close handle
	210	$DBHandle->disconnect;
	211
	212	exit(0);
	213
	214	################################# Subroutines ##################################
	215
	216	sub CreateTable {
	217	my $Table = shift;
	218	if (defined($TablesInDB{$Conf{$Table}})) {
	219	printf("Database table %s.%s already exists, skipping ....\n",
	220	$Conf{'DBDatabase'},$Conf{$Table});
	221	return;
	222	};
	223	my $DBQuery = $DBHandle->prepare($DBCreate{$Table});
	224	$DBQuery->execute() or
	225	&Bleat(2, sprintf("Can't create table %s in database %s: %s%\n",$Table,
	226	$Conf{'DBDatabase'},$DBI::errstr));
	227	printf("Database table %s.%s created succesfully.\n",
	228	$Conf{'DBDatabase'},$Conf{$Table});
	229	return;
	230	};
	231
	232	sub DoMySQL {
	233	my $SQL = shift;
	234	my $DBQuery = $DBHandle->prepare($SQL);
	235	$DBQuery->execute() or &Bleat(1, sprintf("Database error: %s\n",$DBI::errstr));
	236	return;
	237	};
	238
	239	sub PrintInstructions {
	240	my ($UpVersion,$Instructions) = @_;
	241	print "v$UpVersion: Upgrade Instructions >>>>>\n";
	242	my $Padding = ' ' x (length($UpVersion) + 3);
	243	$Instructions =~ s/^ /$Padding/mg;
	244	print $Instructions;
	245	print "<" x (length($UpVersion) + 29) . "\n";
	246	};
	247
	248
	249	__END__
	250
	251	################################ Documentation #################################
	252
	253	=head1 NAME
	254
	255	install - installation script
	256
	257	=head1 SYNOPSIS
	258
	259	B<install> [B<-Vh> [--update I<version>] [--conffile I<filename>]
	260
	261	=head1 REQUIREMENTS
	262
	263	See L<doc/README>.
	264
	265	=head1 DESCRIPTION
	266
	267	This script will create database tables as necessary and configured.
	268
	269	=head2 Configuration
	270
	271	B<install> will read its configuration from F<newsstats.conf> via
	272	Config::Auto.
	273
	274	See L<doc/INSTALL> for an overview of possible configuration options.
	275
	276	=head1 OPTIONS
	277
	278	=over 3
	279
	280	=item B<-V>, B<--version>
	281
	282	Print out version and copyright information and exit.
	283
	284	=item B<-h>, B<--help>
	285
	286	Print this man page and exit.
	287
	288	=item B<-u>, B<--update> I<version>
	289
	290	Don't do a fresh install, but update from I<version>.
	291
	292	=item B<--conffile> I<filename>
	293
	294	Load configuration from I<filename> instead of F<newsstats.conf>.
	295
	296	=back
	297
	298	=head1 FILES
	299
	300	=over 4
	301
	302	=item F<install/install.pl>
	303
	304	The script itself.
	305
	306	=item F<lib/NewsStats.pm>
	307
	308	Library functions for the NewsStats package.
	309
	310	=item F<etc/newsstats.conf>
	311
	312	Runtime configuration file.
	313
	314	=back
	315
	316	=head1 BUGS
	317
	318	Please report any bugs or feature requests to the author or use the
	319	bug tracker at L<http://bugs.th-h.de/>!
	320
	321	=head1 SEE ALSO
	322
	323	=over 2
	324
	325	=item -
	326
	327	L<doc/README>
	328
	329	=item -
	330
	331	L<doc/INSTALL>
	332
	333	=back
	334
	335	This script is part of the B<NewsStats> package.
	336
	337	=head1 AUTHOR
	338
	339	Thomas Hochstein <thh@inter.net>
	340
	341	=head1 COPYRIGHT AND LICENSE
	342
	343	Copyright (c) 2010-2012 Thomas Hochstein <thh@inter.net>
	344
	345	This program is free software; you may redistribute it and/or modify it
	346	under the same terms as Perl itself.
	347
	348	=cut