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

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

Hi there,

Steve wrote:
 > > lastmodified is a unix time stamp (seconds since 1.1.1970)
 > 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 ;-)

It works on NT, because PHP's time() returns a unix time stamp.
Better than using DATE: I think yes. mySQL only recently supports date 
arithmetic, I don't know about mSQL. At least one server I deal with
has the slightly older mySQL version without date arithmetic.
Consider this example from HotTopics: pages are removed when
lastmodified < time() - 60*60*24*7  -- looks pretty, no?

 > > hits counts how many times this pages has been accessed (optional)
 > Interesting... I had thoughts about using a seperate table for logging --
 > Thoughts?

Might be actually better using a separate table, also I would do
something like this and use update instead of insert:

CREATE TABLE hottopics (
	pagename VARCHAR(100) NOT NULL,
	hits INT DEFAULT 0,
	PRIMARY KEY (pagename)
	);

We don't need to scale beyond 1 mio hits/month, so the performance
difference between update & insert is moot. I would use a different
table, as updating causes table locks. Don't know if this could be an
issue. If it is, I don't want to lock the main table.


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

Edits are no longer lost - we already have a patch in place.
Notification would be possible. Although, what are the chances that
edits are lost? How often has this happend on your wikis?
Anyway, I guess this needs another table (or column in table wiki)
Time limit: 15min?

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

You should have a look at your own code sometime :o)
This defence mechanism relies on the author in table wiki not in archive.
However, if archive stores more than one version I guess it would be
intersting to have the additonal information provided by author and
lastmodified.


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

Yea, diff within PHP would be nifty. Of course, one could call the
shell tool, but that makes it less portable. I even had a look at
GNU diff's sources: ouch - much too complicated to do even an easy
version in PHP.


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

I assume that the EditText page would have a separate input field for
the category? I wouldn't make the category logic too complex. Basic
stuff is sufficient. I assume that part of wiki's appeal lies in its
chaotic nature - a too rigid structure wouldn't fit well.
Also, I would like to see 'Paths' or tracks/trails or whatever you call them.
Prev/next/up buttons should be dynamically created.


 > Also I think you mentioned somewhere about just throwing the references 
 > in one column, and I think that's satisfactory.

Fine.

/Arno