Rolling Your Own Plaintext Wiki (Wiki inside a DB)

Question

Anyone know of an API (php preferable but I'd be interested in any language) for creating wiki-like data storage?

How about any resources on rolling your own plaintext wiki? How do other plaintext wikis handle the format of the text file?

I understand I can use Markdown or Textile for the formatting. But what I'm most interested in is how to approach the plaintext storage of multi-user edits.

I'm writing a web application that is primarily database driven. I want at least one text field of this database to be in a wiki-like format. Specifically, this text can be edited by multiple users with the ability to roll back to any version. Think the wiki/bio section of Last.FM (almost the entire site is strictly structured by a database except for this one section per artist).

So far, my approach of taking apart MediaWiki and wedging it into a database seems like overkill. I'm thinking it would be much easier to roll my own plaintext wiki, and store this file in the database's appropriate text field.

Answer 1

So, basically this is a "how do I version text information in my DB".

Well, the simplest way is simply copying the data.

Simply, create a "version" table that holds "old versions" of the data, and link it back to your main table.

create table docs {
    id integer primary key not null,
    version integer not null,
    create_date date,
    change_date date,
    create_user_id integer not null references users(id),
    change_user_id integer references users(id),
    text_data text
}

create table versions {
    id integer primary key not null,
    doc_id integer not null references docs(id),
    version integer,
    change_date date,
    change_user integer not null references users(id),
    text_data text
}

Whenever you update your original document, you copy the old text value in to this table, copy the user and change date and bump the version.

select version, change_date, change_user, text_data 
    into l_version, l_change_data, l_change_user, l_text_data 
from docs where id = l_doc_id;

insert into versions values (newid, l_doc_id, l_version, 
    l_change_date, l_change_user, l_text_data);

update docs set version = version + 1, change_date = now, 
    change_user = cur_user, text_data = l_new_text where id = l_doc_id;

You could even do this in a trigger if your DB supports those.

Faults with this method are that its a full copy of the data (so if you have a large document, the version stay large). You can mitigate that by using something like diff(1) and patch(1).

For example:

diff version2.txt version1.txt > difffile

Then you can store that difffile as "version 1".

In order to recover version 1 from version 2, you grab the version 2 data, run patch on it using the diff file data, and that gives you v1.

If you want to go from v3 to v1, you need to do this twice (once to get v2, and then again to get v1).

This lowers your storage burden, but increases your processing (obviously), so you'll have to judge how you want to do this.