[Phpwiki-talk] Re: MySQL schema (fwd)

[Steve W. <sw...@wc...>]

Arno,
Here's your reply to my initial suggestion. I wanted to forward it so it
goes in the mail archive.

sw

...............................ooo0000ooo.................................
   Hear FM quality freeform radio through the Internet: http://wcsb.org/
                     home page: www.wcsb.org/~swain 

---------- Forwarded message ----------
Date: Wed, 26 Apr 2000 00:01:32 +0200 (MEST)
From: Arno Hollosi <aho...@in...>
To: Steve Wainstead <sw...@wc...>
Subject: Re: MySQL schema


Hi Steve,

about your mySQL schema:
(quoting the whole thing for easier reading)

 > CREATE TABLE wiki (
 >    pagename VARCHAR(100) PRIMARY KEY,
 >    version INT,
 >    stamp VARCHAR(40),
 >    author VARCHAR(100),
 >    content MEDIUMTEXT,
 >    refs TINYINT
 > );
 > 
 > CREATE TABLE archive (
 >    pagename VARCHAR(100) PRIMARY KEY,
 >    version INT,
 >    stamp VARCHAR(40),
 >    author VARCHAR(100),
 >    content MEDIUMTEXT,
 >    refs TINYINT
 > );
 > 
 > CREATE TABLE wiki_refs (
 >    refnum INT NOT NULL,
 >    pagename VARCHAR(100) NOT NULL,
 >    ref VARCHAR(255) NOT NULL,
 >    UNIQUE INDEX wiki_refs_indx (refnum, pagename)
 > );

You need a "version INT" in wiki_refs too.
E.g. "FrontPage" version 4 (in wiki) has references and
"FrontPage" version 3 (in archive) has refs too. There's no way to
distinguish the two in your schema.

wiki_refs could look like:
	CREATE TABLE wiki_refs (
	   pagename VARCHAR(100) NOT NULL,
	   version INT NOT NULL,
	   refnum INT NOT NULL,
	   ref VARCHAR(255) NOT NULL,
	   PRIMARY KEY (pagename, version, refnum)
	);

On a side note, I don't think that "refs TINYINT" in wiki/archive is
necessary. Strictly speaking your schema won't be normalized (in a higher 
normal form - 3NF or 4NF) if you add this column. But it's not much to
worry about. Advantages: it safes a little time when displaying the page.

But speaking of references: I don't like them. Editing them takes many 
steps: "edit page" -> "save" -> "ok" -> "edit page" -> "edit links" -> "save"
Instead I would suggest a markup of the kind "[1:http://host.com/page.html]"
or something similar. I know this is not part of the original Wiki.
I don't know how important being a 100% clone of the original is to
you. I would change this without hesitation - but that is me.


 > Regarding the timestamp, you're right about Unix timestamps being better.
 > I think that we could even use some database dependent way, like using
 > native MySQL date types.

I wouldn't use a database dependent way. This makes it a hassle to
create advanced features based on the date stamp. Unix timestamps are
fine, because basically they are integers (easy calculations and
comparisons) and can be converted easily using PHP functions.
Changing this in the current Wiki would only require some minor
updates. Of course, unix timestamps have this pesky year 2037 problem ;o)


 > it's now got me thinking about normalized
 > schemas and a more flexible architecture, which is a bit further than I
 > wanted, but as long as it's fun I will persist :-)

great :o)

Actually, I have a lot of nifty ideas on how to improve navigation
within wiki. It requires additional database magic and is resource
intensive (execution time) to duplicate with gdbm. If you are
interested anyway I write a short summary of features I would like to
see (and which I will implement in one of my future Wikis).


 > I rewrote UpdateRecentChanges completely yesterday. 
 > I also updated the documentation; your new INSTALL.mysql is included. I
 > pages that come with PhpWiki are in the correct format with \r

is the changed version already online?

/Arno