Text 3977, 109 rader
Skriven 2010-02-10 22:01:44 av Carol Shenkenberger (569.ftsc_pub)
   Kommentar till text 3976 av Sergey Sokoloff (2:5063/88)
Ä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'v
 >  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 programme
 >  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
 >  CS> had a need but it's not that useful to fidonet as a whole.  If one of y
 >  CS> comes up with something that needs that, let me know ok?
 > 
 > Ok.

Hehe as i read down, I need to explain I am Tier 2 at best so far.  I can
however see some aspects wrong in the setup.

 
 > Inside http://www.shodtech.net/fido/fidonet_mysql_reader_v0.4.tar.bz2 there'
 > 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, 
 > WebBBS sits on top of a HPT echoprocessor, uses Perl to store all the proces
 > and scanned messages inside a MySQL database, and has a PHP front-end to rea
 > 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;

45 is way too short for our needs though past what current fido allows for an
areaname.  Needs to expand to at least 200.

 
 > --
 > -- 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,

These will truncate some names ,like Hernandezverinda Elisabeth if 
you add the fromaddr.  Field use becomes unusable truncatd at best.

 >     `subject` text character set latin1,
 >     `date` varchar(45) character set latin1 default NULL,

Datefield larger than needed.  Harmless but wasteful.

 >     `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?

It depends, if they want email support, larger is needed.

 > *) 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?

You'd need to key that in entry to the system and believe it or ot, tritle
tends to work better.

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

Sorry, above me there, I do not know.
 
 > *) What would you personally prefer as your Fidonet message storage ÄÄ
 >    a client-server DBMS like MySQL, or a serverless library like SQLite?

Smething free that cheap thart my BBS supports.  I dont want to hassle too
much when at home making things work.

                  xxcarol
--- SBBSecho 2.11-Win32
 * Origin: SHENK'S EXPRESS telnet://shenks.synchro.net (1:275/100)