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

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

looking good, Arno, a few questions...

On Wed, 7 Jun 2000, Arno Hollosi wrote:

> CREATE TABLE wiki (
> 	pagename VARCHAR(100) NOT NULL,
> 	version INT NOT NULL DEFAULT 1,
> 	flags INT NOT NULL DEFAULT 0,
> 	author VARCHAR(100),
> 	lastmodified INT NOT NULL,
> 	created INT NOT NULL,		# optional
> 	hits INT NOT NULL DEFAULT 0,	# optional
> 	content MEDIUMTEXT,
> 	PRIMARY KEY (pagename)
> 	);
> 
> Notes:
> lastmodified is a unix time stamp (seconds since 1.1.1972)

I wonder, is this really better than using whatever DATE type 
there is that's native to the RDBMS? I do agree it's more portable.
(Will it work on NT? I never expected to support NT ;-)

> hits counts how many times this pages has been accessed (optional)

Interesting... I had thoughts about using a seperate table for logging --
I've read about this somewhere, using MySQL for web server logging -- and
that means only doing inserts for the most part as opposed to updates,
which I think are more expensive (but that's probably implementation
dependent.)

Thoughts?

> flags contains a variety of indicators, possible flags are
> e.g. "locked page", "has references", ...

Another feature I'd like to see, which I think you just thought of, is
"this page has been checked out" but does not necessarily prevent it from
being edited. If you decide to edit a page you might get a message like
"this page was checked out at Thu Jun 8 00:04:26 EDT 2000 by 127.0.0.1".
That would clue the user that their edits might be lost, or they might
erase someone else's edits. 

> 
> CREATE TABLE wiki_refs (

more on this in a moment...

> 
> CREATE TABLE wiki_links (
> 	frompage VARCHAR(100) NOT NULL,
> 	topage VARCHAR(100) NOT NULL,
> 	PRIMARY KEY (frompage, topage)
> 	);

I see where this is going, and I'm down with it... (that is, 
I like it :-)

> CREATE TABLE archive (
> 	pagename VARCHAR(100) NOT NULL,
> 	content MEDIUMTEXT NOT NULL,
> 	PRIMARY KEY (pagename)
> 	);
> 
> Notes: archive can be either the bare minimum necessary (like above)
> or a complete copy of table wiki. A slightly extended version might
> include the following columns:
> 	author VARCHAR(100),
> 	lastmodified INT NOT NULL

I think we do need author, because the way pages go into the archive
is one of the defense mechanisms of the Wiki: 

User A saves, copy goes to archive
User B saves, copy goes to archive
User B erases page, laughs maniacally, but 
       since User B made the last save, it did not go into the archive
User A restores the page from archive

I have found this invaluable in restoring pages, although I have
encountered:

User A wipes page
User B wipes page
Steve tries to restore but only gets User A's wiped page

so it's not fullproof. I would like to see a Wiki where all previous
versions of the page are stored as diffs, but I have no desire to
reimplement "diff" in PHP. Perhaps the last ten versions? Five?
 
> I am not too happy about using pagename as foreign key in other
> tables. Indexing and looking up text is not fast. Instead a unique
> page id should be used. However, this creates problems with
> wiki_links, i.e. how do you store links to pages not yet created?

Well, how much traffic does a Wiki need to support? One of the appeals to
me of a Wiki is that it's a small thing, and to a degree we don't have to
worry much about performance. I still do though, I want it to be
efficient, but I'm not going to make it hard to code, maintain and extend
just to eek out more performance that might not be needed... if a Wiki
ever got the traffic Slashdot got it would probably be ruined in short
order as 20 people try to save changes to the FrontPage at the same time.
So I think using the page name is OK. 

 
> Optional feature: HotTopics
> 
> There are other ways to implement HotTopics as well. If you can come
> up with something better let me know.

I like this, and I think it's a good first cut. A feature I've been asked
for is "Categories," which is a feature of another Wiki (Zwiki I think)
which somehow helps searches.
 
> One way to have global persistent variables is to store them in the
> database as well. Something along the lines like:
> 
> CREATE TABLE globalvars (
> 	name varchar(100) NOT NULL,
> 	value varchar(255),
> 	PRIMARY KEY (name)
> 	);

If PHP cannot do this for us, then the database will do fine. I know 
mod_perl has some hook where you can load a module at server boot time
and it holds data structures that all processes can access (though I'd 
imagine there are concurrency problems with this arrangement.)

Also I think you mentioned somewhere about just throwing the references 
in one column, and I think that's satisfactory. It shouldn't take up
much of our coding time and if someone doesn't like it they can 
provide a patch :-)

We can just serialize/unserialize the refs as needed. Noone is going to
search them.

sw


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