Text 3976, 87 rader
Skriven 2010-02-09 17:04:24 av Sergey Sokoloff (2:5063/88)
   Kommentar till text 3960 av Carol Shenkenberger (555.ftsc_pub)
Ärende: SQL skills
==================
And about 00:49 10 Dec 09 it was written from Carol Shenkenberger to Michiel
van der Vlist:

 CS> Here's another one.  I have never claimed to be a programmer here.  I've
 CS> often said i cant program my way out of a wet paper bag with 3 sides
 CS> ripped open.  Reason?  My skill set doesnt apply.  I am a SQL programmer.
 CS> I have no skills in C+ , pascal, or even xml etc.

 CS> I could do some pretty nifty stuff with the comma delimited nodelist if i
 CS> had a need but it's not that useful to fidonet as a whole.  If one of you
 CS> comes up with something that needs that, let me know ok?

Ok.

Inside http://www.shodtech.net/fido/fidonet_mysql_reader_v0.4.tar.bz2 there's
an open code of an alpha version of a WebBBS called 'Fidonet MySQL Reader'.
Andrey Bykanov (2:5022/63) is the author of that soft. As far as I can see, his
WebBBS sits on top of a HPT echoprocessor, uses Perl to store all the processed
and scanned messages inside a MySQL database, and has a PHP front-end to read
the areas with a Web interface. The SQL statements defining the tables are
the following:

======= Clipboard starts =======

--
-- Table structure for table `area_desc`
--

CREATE TABLE `area_desc` (
  `area` varchar(45) NOT NULL,
  `description` text
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


--
-- Table structure for table `areadata`
--

CREATE TABLE `areadata` (
    `area` varchar(45) character set latin1 default NULL,
    `fromname` varchar(45) character set latin1 default NULL,
    `fromaddr` varchar(45) character set latin1 default NULL,
    `toname` varchar(45) character set latin1 default NULL,
    `subject` text character set latin1,
    `date` varchar(45) character set latin1 default NULL,
    `text` text character set latin1,
    `msgid_addr` varchar(45) character set latin1 default NULL,
    `msgid_num` varchar(45) character set latin1 default NULL,
    `id_msg` bigint(20) unsigned NOT NULL auto_increment,
    `reply_addr` varchar(45) character set latin1 default NULL,
    `reply_num` varchar(45) character set latin1 default NULL,
     PRIMARY KEY  (`id_msg`)
) ENGINE=MyISAM AUTO_INCREMENT=46013 DEFAULT CHARSET=utf8;

======= Clipboard ends =======

Could you tell me a couple of ways to improve that structure? For example:

*) How much should the varchar lengths really be? 45 seems so arbitrary.
   Is it 35 characters for `fromname` and `toname`, like in FTS-0001.016?

*) Andrey Bykanov's WebBBS just displays either the list of messages' titles,
   (see http://www.shodtech.net/fidonet_echo_message/read.php?area=RU.ANIME
   for example), or a single message, like that:

   http://www.shodtech.net/fidonet_echo_message/read.php?msg=66564

   But what if some developer wants a WebBBS that displays trees of replies?
   Would the reply_num ÄÄ> msgid_num relations suffice, or something like a
   bidirectional list with branches (like in JAM message base) may greatly
   improve things?

*) What is the best way to store a set of kludges for each message (if we plan
   to use those kludges later to filter messages)? Maybe a one-to-many relation
   to some table where each row is a kludge with message ID equal to its parent
   message's areadata.id_msg?

*) What would you personally prefer as your Fidonet message storage ÄÄ
   a client-server DBMS like MySQL, or a serverless library like SQLite?


With best Fidonet 2.0 regards,
Mithgol the Webmaster.

... Tell a computer to WIN and you lose.                (Bugzilla Quip System)
--- Something is rotten in the state of Denmark.  (Shakespeare, Hamlet, I, IV)
 * Origin: Be careful, the paranoid ones are always wathing you!.. (2:5063/88)