# HG changeset patch # User Ivo Smits # Date 1405632257 -7200 # Node ID 372f4e195986e523cba1071abcfcbc3a72de3c32 # Parent cccd73f72bf66d585571ab1a10236461e3a025cb Added article blacklist function, switch to binary-safe latin1 character set for database diff -r cccd73f72bf6 -r 372f4e195986 common.php --- a/common.php Thu Jul 10 22:26:45 2014 +0200 +++ b/common.php Thu Jul 17 23:24:17 2014 +0200 @@ -72,6 +72,7 @@ function nntp_article_wanted($messageid) { global $db, $pnewss_hooks; if ($db->evalRow('SELECT `id` FROM `messages` WHERE `messageid` = ?', $messageid) !== FALSE) return FALSE; + if ($db->evalRow('SELECT * FROM `blacklist` WHERE `messageid` = ?', $messageid) !== FALSE) return FALSE; if (!pnewss_call_filter_hooks($pnewss_hooks['article_filter'], array(array('messageid' => $messageid)))) return FALSE; return TRUE; } @@ -136,6 +137,7 @@ $messageid = substr($messageid, 1, -1); $article = $db->evalRowAssoc('SELECT * FROM `messages` WHERE `messageid` = ?', $messageid); if ($article !== FALSE) throw new Exception('Duplicate'); + if ($db->evalRow('SELECT * FROM `blacklist` WHERE `messageid` = ?', $messageid) !== FALSE) throw new Exception('Blacklisted Message-ID'); $headers['PATH'] = 'pNewss.Core.UCIS.nl'.(isset($headers['PATH'])?'!'.$headers['PATH']:''); foreach (array('Path', 'From', 'Newsgroups', 'Subject', 'Date', 'Message-ID', 'Sender') as $headername) { if (isset($headers[strtoupper($headername)])) $header[] = $headername.': '.$headers[strtoupper($headername)]; @@ -143,7 +145,7 @@ if (!pnewss_call_filter_hooks($pnewss_hooks['article_filter'], array(array('messageid' => $messageid, 'headers' => $headers, 'body' => $lines)))) throw new Exception('Filtered'); $headertext = implode("\r\n", $header); $bodytext = implode("\r\n", $lines); - if (strlen($headertext) > 65535 || strlen($bodytext) > 16777215) throw new Exception('Message too big'); + if (strlen($messageid) > 255 || strlen($headertext) > 65535 || strlen($bodytext) > 16777215) throw new Exception('Message too big'); $id = $db->insert('INSERT INTO `messages` (`messageid`, `header`, `body`) VALUES (?, ?, ?)', array($messageid, $headertext, $bodytext)); foreach ($newsgroups as $groupid) $db->insert('INSERT INTO `groupmessages` (`group`, `message`) VALUES (?, ?)', array($groupid, $id)); pnewss_call_hooks($pnewss_hooks['article_stored'], array(array('messageid' => $messageid, 'headers' => $headers, 'body' => $lines, 'dbid' => $id))); diff -r cccd73f72bf6 -r 372f4e195986 database.mysql --- a/database.mysql Thu Jul 10 22:26:45 2014 +0200 +++ b/database.mysql Thu Jul 17 23:24:17 2014 +0200 @@ -1,66 +1,28 @@ --- phpMyAdmin SQL Dump --- version 3.3.9.2deb1 --- http://www.phpmyadmin.net --- --- Machine: localhost --- Genereertijd: 12 Apr 2011 om 01:45 --- Serverversie: 5.0.51 --- PHP-Versie: 5.3.3-7 - -SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; - --- --- Database: `ivo_pnewss` --- - --- -------------------------------------------------------- - --- --- Tabelstructuur voor tabel `groupmessages` --- - +CREATE TABLE IF NOT EXISTS `blacklist` ( + `messageid` varchar(255) character set ascii NOT NULL, + PRIMARY KEY (`messageid`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE IF NOT EXISTS `groupmessages` ( `group` int(10) unsigned NOT NULL, - `message` int(10) unsigned NOT NULL, + `message` int(10) unsigned default NULL, `number` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`group`,`number`), KEY `message` (`message`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; - --- -------------------------------------------------------- - --- --- Tabelstructuur voor tabel `groups` --- - CREATE TABLE IF NOT EXISTS `groups` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(255) character set ascii NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; - --- -------------------------------------------------------- - --- --- Tabelstructuur voor tabel `messages` --- - +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE IF NOT EXISTS `messages` ( `id` int(10) unsigned NOT NULL auto_increment, `messageid` varchar(255) character set ascii NOT NULL, - `header` text character set ascii NOT NULL, - `body` mediumtext character set ascii NOT NULL, + `header` text character set latin1 collate latin1_bin NOT NULL, + `body` mediumtext character set latin1 collate latin1_bin NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `messageid` (`messageid`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; - --- -------------------------------------------------------- - --- --- Tabelstructuur voor tabel `peergroups` --- - +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE IF NOT EXISTS `peergroups` ( `peer` int(10) unsigned NOT NULL, `group` int(10) unsigned NOT NULL, @@ -68,19 +30,12 @@ `high` int(10) unsigned default NULL, PRIMARY KEY (`peer`,`group`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; - --- -------------------------------------------------------- - --- --- Tabelstructuur voor tabel `peers` --- - CREATE TABLE IF NOT EXISTS `peers` ( `id` int(10) unsigned NOT NULL auto_increment, `address` varchar(255) collate utf8_unicode_ci NOT NULL, - `enabled` tinyiny(1) unsigned NOT NULL DEFAULT '1', + `enabled` tinyint(1) unsigned NOT NULL default '1', `post` tinyint(1) unsigned NOT NULL, `lastposted` int(10) unsigned default NULL, PRIMARY KEY (`id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; diff -r cccd73f72bf6 -r 372f4e195986 fetchnews.php --- a/fetchnews.php Thu Jul 10 22:26:45 2014 +0200 +++ b/fetchnews.php Thu Jul 17 23:24:17 2014 +0200 @@ -99,10 +99,6 @@ $messageid = substr($messageid, 1, -1); $message = $db->evalRowAssoc('SELECT * FROM `messages` WHERE `messageid` = ?', $messageid); if ($message) { - $groupmessage = $db->evalRowAssoc('SELECT * FROM `groupmessages` WHERE `group` = ? AND `message` = ?', array($group['id'], $message['id'])); - if (!$groupmessage) { - $db->insert('INSERT INTO `groupmessages` (`group`, `message`) VALUES (?, ?)', array($group['id'], $message['id'])); - } } else if (!nntp_article_wanted($messageid)) { print("Ignoring filtered article $messageid\n"); } else { diff -r cccd73f72bf6 -r 372f4e195986 server.php --- a/server.php Thu Jul 10 22:26:45 2014 +0200 +++ b/server.php Thu Jul 17 23:24:17 2014 +0200 @@ -103,25 +103,21 @@ $currentgroup = $group; $groupmessages = $db->evalRow('SELECT MIN(`number`), MAX(`number`), COUNT(`number`) FROM `groupmessages` WHERE `group` = ?', $group['id']); nntp_writeline(STDOUT, '211 '.intval($groupmessages[2]).' '.intval($groupmessages[0]).' '.intval($groupmessages[1]).' '.$group['name']); - if ($groupmessages[0] === NULL) { - $currentarticle = NULL; - } else { - $currentarticle = $db->evalRowAssoc('SELECT * FROM `groupmessages` WHERE `group` = ? AND `number` = ?', array($group['id'], $groupmessages[0])); - if ($currentarticle === FALSE) $currentarticle = NULL; - } + $currentarticle = $db->evalRowAssoc('SELECT * FROM `groupmessages` WHERE `group` = ? AND `message` IS NOT NULL ORDER BY `number` ASC LIMIT 1', $group['id']); + if ($currentarticle === FALSE) $currentarticle = NULL; if ($cmd == 'LISTGROUP') { $range = strtok(" \t"); if ($range !== FALSE) { $parts = explode('-', $range); if (count($parts) == 1 || !strlen($parts[1])) { - $query = 'SELECT `number` FROM `groupmessages` WHERE `group` = ? AND `number` >= ?'; + $query = 'SELECT `number` FROM `groupmessages` WHERE `group` = ? AND `number` >= ? AND `message` IS NOT NULL'; $queryargs = array($group['id'], $parts[0]); } else { - $query = 'SELECT `number` FROM `groupmessages` WHERE `group` = ? AND `number` BETWEEN ? AND ?'; + $query = 'SELECT `number` FROM `groupmessages` WHERE `group` = ? AND `number` BETWEEN ? AND ? AND `message` IS NOT NULL'; $queryargs = array($group['id'], $parts[0], $parts[1]); } } else { - $query = 'SELECT `number` FROM `groupmessages` WHERE `group` = ?'; + $query = 'SELECT `number` FROM `groupmessages` WHERE `group` = ? AND `message` IS NOT NULL'; $queryargs = array($group['id']); } foreach ($db->evalColumn($query, $queryargs) as $number) nntp_writeline_data(STDOUT, $number); @@ -162,8 +158,8 @@ break; } switch ($cmd) { - case 'LAST': $query = 'SELECT * FROM `groupmessages` WHERE `group` = ? AND `number` < ? ORDER BY `number` DESC LIMIT 1'; break; - case 'NEXT': $query = 'SELECT * FROM `groupmessages` WHERE `group` = ? AND `number` > ? ORDER BY `number` ASC LIMIT 1'; break; + case 'LAST': $query = 'SELECT * FROM `groupmessages` WHERE `group` = ? AND `number` < ? AND `message` IS NOT NULL ORDER BY `number` DESC LIMIT 1'; break; + case 'NEXT': $query = 'SELECT * FROM `groupmessages` WHERE `group` = ? AND `number` > ? AND `message` IS NOT NULL ORDER BY `number` ASC LIMIT 1'; break; default: throw new Exception('Internal error'); } $article = $db->evalRowAssoc($query, array($currentarticle['group'], $currentarticle['number'])); @@ -276,7 +272,7 @@ nntp_writeline(STDOUT, '412 no newsgroup has been selected'); return NULL; } - $article = $db->evalRowAssoc('SELECT * FROM `groupmessages` WHERE `group` = ? AND `number` = ?', array($currentgroup['id'], $article)); + $article = $db->evalRowAssoc('SELECT * FROM `groupmessages` WHERE `group` = ? AND `number` = ? AND `message` IS NOT NULL', array($currentgroup['id'], $article)); if ($article === FALSE) { nntp_writeline(STDOUT, '423 no such article number in this group'); return NULL;