5 # This script will create database tables as necessary.
\r
7 # It is part of the NewsStats package.
\r
9 # Copyright (c) 2010 Thomas Hochstein <thh@inter.net>
\r
11 # It can be redistributed and/or modified under the same terms under
\r
12 # which Perl itself is published.
\r
15 our $VERSION = "0.01";
\r
17 # we're in .../install, so our module is in ..
\r
18 push(@INC, dirname($0).'/..');
\r
22 use NewsStats qw(:DEFAULT);
\r
28 ################################# Main program #################################
\r
30 ### read commandline options
\r
31 my %Options = &ReadOptions('');
\r
33 ### change working directory to .. (as we're in .../install)
\r
36 ### read configuration
\r
37 print("Reading configuration.\n");
\r
38 my %Conf = %{ReadConfig('newsstats.conf')};
\r
40 ##### --------------------------------------------------------------------------
\r
41 ##### Database table definitions
\r
42 ##### --------------------------------------------------------------------------
\r
44 my %DBCreate = ('DBTableRaw' => <<RAW, 'DBTableGrps' => <<GRPS);
\r
46 -- Table structure for table DBTableRaw
\r
49 CREATE TABLE IF NOT EXISTS `$Conf{'DBTableRaw'}` (
\r
50 `id` bigint(20) unsigned NOT NULL auto_increment,
\r
51 `day` date NOT NULL,
\r
52 `mid` varchar(250) character set ascii NOT NULL,
\r
53 `date` datetime NOT NULL,
\r
54 `timestamp` bigint(20) NOT NULL,
\r
55 `token` varchar(80) character set ascii NOT NULL,
\r
56 `size` bigint(20) NOT NULL,
\r
57 `peer` varchar(250) NOT NULL,
\r
58 `path` varchar(1000) NOT NULL,
\r
59 `newsgroups` varchar(1000) NOT NULL,
\r
60 `headers` longtext NOT NULL,
\r
61 `disregard` tinyint(1) default '0',
\r
66 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Raw data';
\r
69 -- Table structure for table DBTableGrps
\r
72 CREATE TABLE IF NOT EXISTS `$Conf{'DBTableGrps'}` (
\r
73 `id` bigint(20) unsigned NOT NULL auto_increment,
\r
74 `month` varchar(7) character set ascii NOT NULL,
\r
75 `newsgroup` varchar(100) NOT NULL,
\r
76 `postings` int(11) NOT NULL,
\r
77 `revision` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
\r
79 UNIQUE KEY `month_newsgroup` (`month`,`newsgroup`),
\r
80 KEY `newsgroup` (`newsgroup`),
\r
81 KEY `postings` (`postings`)
\r
82 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Postings per newsgroup';
\r
85 ##### --------------------------- End of definitions ---------------------------
\r
87 ### create database tables
\r
88 print "-----\nStarting database table generation.\n";
\r
90 my $DBHandle = InitDB(\%Conf,1);
\r
93 my %TablesInDB = %{$DBHandle->table_info('%', '%', '%', 'TABLE')->fetchall_hashref('TABLE_NAME')};
\r
95 # check for tables and create them, if they don't exist yet
\r
96 foreach my $Table (keys %DBCreate) {
\r
97 if (defined($TablesInDB{$Conf{$Table}})) {
\r
98 printf("Database table %s.%s already exists, skipping ....\n",$Conf{'DBDatabase'},$Conf{$Table});
\r
101 my $DBQuery = $DBHandle->prepare($DBCreate{$Table});
\r
102 $DBQuery->execute() or die sprintf("$MySelf: E: Can't create table %s in database %s: %s%\n",$Table,$Conf{'DBDatabase'},$DBI::errstr);
\r
103 printf("Database table %s.%s created succesfully.\n",$Conf{'DBDatabase'},$Conf{$Table});
\r
107 $DBHandle->disconnect;
\r
108 print "Database table generation done.\n";
\r
110 ### output information on other necessary steps
\r
116 1) Setup an INN feed to feedlog.pl
\r
118 a) Edit your 'newsfeeds' file and insert something like
\r
120 ## gather statistics for NewsStats
\r
123 :Tc,WmtfbsPNH,Ac:$Path/feedlog.pl
\r
127 * check that you got the path to feedlog.pl right
\r
128 * check that feedlog.pl can be executed by the news user
\r
129 * adapt the pattern (here: 'de.*') to your needs
\r
131 b) Check your 'newsfeeds' syntax:
\r
133 # ctlinnd checkfile
\r
135 and reload 'newsfeeds':
\r
137 # ctlinnd reload newsfeeds 'Adding newsstats! feed'
\r
139 c) Watch your 'news.notice' and 'errlog' files:
\r
141 # tail -f /var/log/news/news.notice
\r
143 # tail -f /var/log/news/errlog
\r
145 2) Watch your $Conf{'DBTableRaw'} table fill.
\r
147 3) Read the documentation. ;)
\r
151 -thh <thh\@inter.net>
\r