| 1 | #! /usr/bin/perl -W\r |
| 2 | #\r |
| 3 | # install.pl\r |
| 4 | #\r |
| 5 | # This script will create database tables as necessary.\r |
| 6 | # \r |
| 7 | # It is part of the NewsStats package.\r |
| 8 | #\r |
| 9 | # Copyright (c) 2010 Thomas Hochstein <thh@inter.net>\r |
| 10 | #\r |
| 11 | # It can be redistributed and/or modified under the same terms under \r |
| 12 | # which Perl itself is published.\r |
| 13 | \r |
| 14 | BEGIN {\r |
| 15 | our $VERSION = "0.01";\r |
| 16 | use File::Basename;\r |
| 17 | # we're in .../install, so our module is in ..\r |
| 18 | push(@INC, dirname($0).'/..');\r |
| 19 | }\r |
| 20 | use strict;\r |
| 21 | \r |
| 22 | use NewsStats qw(:DEFAULT);\r |
| 23 | \r |
| 24 | use Cwd;\r |
| 25 | \r |
| 26 | use DBI;\r |
| 27 | \r |
| 28 | ################################# Main program #################################\r |
| 29 | \r |
| 30 | ### read commandline options\r |
| 31 | my %Options = &ReadOptions('');\r |
| 32 | \r |
| 33 | ### change working directory to .. (as we're in .../install)\r |
| 34 | chdir '..';\r |
| 35 | \r |
| 36 | ### read configuration\r |
| 37 | print("Reading configuration.\n");\r |
| 38 | my %Conf = %{ReadConfig('newsstats.conf')};\r |
| 39 | \r |
| 40 | ##### --------------------------------------------------------------------------\r |
| 41 | ##### Database table definitions\r |
| 42 | ##### --------------------------------------------------------------------------\r |
| 43 | \r |
| 44 | my %DBCreate = ('DBTableRaw' => <<RAW, 'DBTableGrps' => <<GRPS);\r |
| 45 | -- \r |
| 46 | -- Table structure for table DBTableRaw\r |
| 47 | -- \r |
| 48 | \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 |
| 62 | PRIMARY KEY (`id`),\r |
| 63 | KEY `day` (`day`),\r |
| 64 | KEY `mid` (`mid`),\r |
| 65 | KEY `peer` (`peer`)\r |
| 66 | ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Raw data';\r |
| 67 | RAW\r |
| 68 | -- \r |
| 69 | -- Table structure for table DBTableGrps\r |
| 70 | -- \r |
| 71 | \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 |
| 78 | PRIMARY KEY (`id`),\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 |
| 83 | GRPS\r |
| 84 | \r |
| 85 | ##### --------------------------- End of definitions ---------------------------\r |
| 86 | \r |
| 87 | ### create database tables\r |
| 88 | print "-----\nStarting database table generation.\n";\r |
| 89 | # DB init\r |
| 90 | my $DBHandle = InitDB(\%Conf,1);\r |
| 91 | \r |
| 92 | # read tables\r |
| 93 | my %TablesInDB = %{$DBHandle->table_info('%', '%', '%', 'TABLE')->fetchall_hashref('TABLE_NAME')};\r |
| 94 | \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 |
| 99 | next;\r |
| 100 | };\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 |
| 104 | };\r |
| 105 | \r |
| 106 | # close handle\r |
| 107 | $DBHandle->disconnect;\r |
| 108 | print "Database table generation done.\n";\r |
| 109 | \r |
| 110 | ### output information on other necessary steps\r |
| 111 | my $Path = cwd();\r |
| 112 | print <<TODO;\r |
| 113 | -----\r |
| 114 | Things left to do:\r |
| 115 | \r |
| 116 | 1) Setup an INN feed to feedlog.pl\r |
| 117 | \r |
| 118 | a) Edit your 'newsfeeds' file and insert something like\r |
| 119 | \r |
| 120 | ## gather statistics for NewsStats\r |
| 121 | newsstats!\\r |
| 122 | :!*,de.*\\r |
| 123 | :Tc,WmtfbsPNH,Ac:$Path/feedlog.pl\r |
| 124 | \r |
| 125 | Please\r |
| 126 | \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 |
| 130 | \r |
| 131 | b) Check your 'newsfeeds' syntax:\r |
| 132 | \r |
| 133 | # ctlinnd checkfile\r |
| 134 | \r |
| 135 | and reload 'newsfeeds':\r |
| 136 | \r |
| 137 | # ctlinnd reload newsfeeds 'Adding newsstats! feed'\r |
| 138 | \r |
| 139 | c) Watch your 'news.notice' and 'errlog' files:\r |
| 140 | \r |
| 141 | # tail -f /var/log/news/news.notice\r |
| 142 | ...\r |
| 143 | # tail -f /var/log/news/errlog\r |
| 144 | \r |
| 145 | 2) Watch your $Conf{'DBTableRaw'} table fill.\r |
| 146 | \r |
| 147 | 3) Read the documentation. ;)\r |
| 148 | \r |
| 149 | Enjoy!\r |
| 150 | \r |
| 151 | -thh <thh\@inter.net>\r |
| 152 | TODO\r |