Mercurial > hg > pnewss

changeset 14:372f4e195986 draft default tip

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added article blacklist function, switch to binary-safe latin1 character set for database
author Ivo Smits <Ivo@UCIS.nl>
date Thu, 17 Jul 2014 23:24:17 +0200
parents cccd73f72bf6
children
files common.php database.mysql fetchnews.php server.php
diffstat 4 files changed, 22 insertions(+), 73 deletions(-) [+]
line wrap: on
line diff
--- 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)));
--- 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;
 
--- 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 {
--- 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;