Some thoughts on the gnuheter_mysql storage module
MC <mc@hack.org>

* Introduction

Some time ago, I was tempted to write some sort of NNTP frontend for
Gnuheter, the Swedish Slashdot like news site based on PHPnuke. I
first thought about using the INN news server and write a new storage
backend, but nothing happened for a long time.

Some time later I got my hands on Papercut, a Python based NNTP server
with existing MySQL backends, and was once again inspired to create
the Gnuheter backend. I only spent a few hours on writing actual code
until my old friend Apathy once again showed its ugly face. Here are
some of my thoughts and the code for what I have done so far. Please
finish it.

* Mapping Gnuheter to News

The rows in the table 'topics' are mapped to newsgroups on the NNTP
side. For example:

  select * from topics;
  +---------+---------------+------------------+-----------------+---------+
  | topicid | topicname     | topicimage       | topictext       | counter |
  +---------+---------------+------------------+-----------------+---------+
  |       1 | linux         | linux.png        | Linux           |       0 |
  |      42 | business      | business.png     | Business        |       0 |
  |      41 | musik         | musik.png        | Musik (MP3)     |       0 |
  |      39 | read          | read.png         | Lsvrt         |       0 |
  +---------+---------------+------------------+-----------------+---------+

will become the (moderated) groups:

  gnuheter.linux
  gnuheter.business
  gnuheter.read

XXX Remember to add a Followups-To: line to the header when showing
these over NNTP! XXX

All the followups in the above groups will end up in the corresponding
discussion groups:

  gnuheter.linux.d
  gnuheter.business.d
  gnuheter.read.d

as per usual in announcement groups on Usenet.

The followups to a 'story' is in 'comments' table. A comment is
connected to a story with a story ID, 'sid', that is also available in
the comments table. 'tid' is an ID for comments.

XXX Add the user diaries as newsgroups? XXX

* NNTP commands in the Gnuheter backend module

** LIST

First list all the moderated groups:

  select 
    topicname, count(sid)
  from 
    stories, topics 
  where
    stories.topic=topics.topicid 
  group by 
    topicname;

then list the discussion groups:

  select 
    topicname, count(tid) 
  from 
    comments, stories, topics 
  where
    stories.topic=topics.topicid and comments.sid=stories.sid 
  group by 
    topicname;

then the diaries:

  pass

** GROUP

Results for a given group as in GROUPNAME

  select 
    count(sid) 
  from 
    stories, topics 
  where 
    topicname='GROUPNAME';

How do we count the corresponding comments?

If the group has a name ending with ".d", we know we should look in
the 'comments' table. A kluge, I know, but what is the Right Thing?

  select 
    count(tid) 
  from 
    comments, topics 
  where 
    topics.topicname='GROUPNAME';

** ARTICLE n:

If we are in a moderated 'stories' group:

  select 
    sid, aid, title, time, hometext, bodytext 
  from 
    stories 
  where 
    sid=n;

If we are in a 'comments' group:

  select 
    tid, pid, sid, date, name, email, url, host_name, subject,
    comment, score,  reason 
  from 
    comments 
  where 
    tid=n;

** HEAD n:

If asked like this:

  head <4@papercut.test>

the response includes the local id of the article

  221 4 <ggg@papercut.test> article retrieved - head follows

  select 
    sid, aid, title, time 
  from 
    stories 
  where 
    sid=n;

** XOVER x-y:

What do we want? Something like this:

  xover 5-5

  224 Overview information follows
  5       Re: Test 1      Mikael Cardell <mc@hack.org>    Mon, 17 Feb 2003 16:59:23 MET   <5@papercut.test>       <4@papercut.test>       63      5       Xref: localhost papercut.test:5
.

** POST:

Useable for followup discussion groups (ending with ".d") only?

To start a new thread you have to do something else? Send e-mail? How
does the PHP code do it?

Use (Eeek!) the world wide wait?

Also update the corresponding row in 'stories' to add a comment:
