mysql database structure

The MySQL database table structure is (to a certain degree) compatible
with that of the well known »PHPWiki« v1.2.x (you only need to change
EWIKI_DB_TABLE_NAME+ to "wiki" to use it). This is the MySQL statement
which creates our database table (you can find it at the bottom of the
"ewiki.php" script):
    CREATE TABLE ewiki (
        pagename VARCHAR(160) NOT NULL,
        version INTEGER UNSIGNED NOT NULL DEFAULT 0,
        flags INTEGER UNSIGNED DEFAULT 0,
        content MEDIUMTEXT,
        author VARCHAR(100) DEFAULT 'ewiki',
        created INTEGER UNSIGNED DEFAULT 0,
        lastmodified INTEGER UNSIGNED DEFAULT 0,
        refs MEDIUMTEXT,
        meta MEDIUMTEXT,
        hits INTEGER UNSIGNED DEFAULT 0,
        PRIMARY KEY id (pagename, version)
    )

I didn't like the column name {pagename} but as I've seen this was
the only difference I renamed it, therefore now the ewiki_database()
function translates it from "pagename" to "id" and vice versa most of
the time - else this would be really slim and nice code :)

The columns {version} holds the different saved page versions. Other
Wikis require a secondary "backup" or "history" table for old versions,
but as I couldn't imagine what this is for, there is just one table
in ewiki; and it seems this is really enough. The first {version} of
a wiki page is always numbered 1. An existing page {version} will
never get overwritten => very secure MySQL usage.

For what's in the {flags}, see the README section about constants. The
{content} of course holds the wiki pages source. The {created} and
{lastmodified} should be clear too.

{refs} contain a "\n" separated list of referenced WikiPages+. The
code to generate that list is rather unclean, so it often contains
GhostPages+. However this does not hurt ewiki and the few functions
that utilize {refs}, so there is currently no need to slow it down
by fixing this.

{meta} can hold additional informations, which allows to extend ewiki
without requiring to ALTER and convert the ewiki database table. It
currently holds some mime headers for binary content and some other
useful informations for images and uploaded files.

{hits} should have gone into {meta} really. But having it separate
allows us to use the very fast mysql UPDATE function.

Note, that the ewiki database table can hold other things than wiki
pages - binary content (images) for example, depending on the setting
of the {flags} field.

And last comment about this, the one-table-concept also made it really easy
to implement the flat file based "database backend".



   ------------------------------------------------------------------ 6 ---

prev << "mysql database structure"
next >> "Just using the wiki source transformation"

---

You cannot modify the INTERNALS file, but anyhow any ideas or suggestion should as usually get filed on BugReports, UserSuggestions or even better the INTERNALS.Discussion.