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

[Arno H. <aho...@in...>]

'lo,

I've spent some time on the new database schema.
This is my first proposal.


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)
created is the creation date of the page (optional)
hits counts how many times this pages has been accessed (optional)
flags contains a variety of indicators, possible flags are
e.g. "locked page", "has references", ...


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)
	);

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

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


Optional feature: HotTopics
HotTopics are pages that were edited frequently over the past days.
Time span can be anything from one week to one month, depending on how 
active the wiki is.

CREATE TABLE hottopics (
	pagename VARCHAR(100) NOT NULL,
	lastmodified INT NOT NULL
	PRIMARY KEY (pagename, lastmodified)
	);

How this works: every time a page is edited an entry in hottopics is
created. The page HotTopics then makes the SQL query
"select pagename, count(*) as edits from hottopics group by pagename"
additionaly a limit can be added to the query e.g. "limit 25".
This table has to be pruned. As we don't want to use an outside
facility like a crontab entry, we use HotTopics as well. Everytime
the page is accessed we perform a
"delete from hottopics where lastmodified < $time"
$time being the unix time stamp that defines the time span.
If deleting is expensive and HotTopics is accessed a lot, then
some kind of global persistent variable holds the time hottopics was
last pruned and pruning is limited to once a day - the check for this
remains in HotTopics.

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

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)
	);


I guess this is enough food for thought.
Comments?

/Arno