wiki Database

The DDL script is available for you to download. This includes the sql necessary to create the tables, triggers, sequences and view. If the owner of the schema is going to be different from the user used to connect to the database (which is best practice) then you will need to create synonyms for that user (or public synonyms).

You will also need to edit the WIKI_USERS_VIEW to show your own users. A good solution would be to show users in the OID, but I've not had the time to work out how to do this.

WIKI_PAGE

This lists the pages in the wiki. Each page has a unique title so we can use the page name as a unique identifier. I decided not to use this as the primary key because it would have been too unwieldy as a foriegn key and in joins. Each page has an owner who is reponsible for setting the moderation level for the page. There are three levels of moderation:

• unmoderated: all changes are instantly acceppted
• moderated: anyone can propose changes to be authorised by the page owner
• read-only: only the page owner can make changes

WIKI_SECTION

Each page is divided into sections. This way the whole page doesn't need to logged in an audit table because of a single edit: we just log the section that changed.

WIKI_TEXT

This is where the text is stored. We track who updated it and when.

WIKI_COMMENT

This allows people to discuss the wiki page and support or oppose proposals relating to it.

WIKI_COMMENT_TYPE

There are four types of comment:

• Topic
• Comment - what it says.
• Support - a vote of support for a suggestion.
• Oppose - a vote against a suggestion.

WIKI_SEARCH

Each time a search is performed it is stored. This is to make it easier to scroll backwards and forwards through the result list without having to repeat a complex, expensive query each time.

WIKI_SEARCH_RESULTS

Each match to the search term is stored here. If a term is found in several places in the same page then there will be several entries in this page, with a score assigned in each. A hit in the page title scores 100 points, a hit in a section title scores 30, whilst a hit in the text scores only 10. The result list totals these to give a total score, allowing the searches to be ranked in descending order.

When a user creates a new search, their previous search results are deleted.

WIKI_USERS_VIEW

This is a view is a list of the users. It needs to have two columns: user_id (which matches the user name of the OID) and user_name. You can write your own select statement to populate this. It is used to allow a page owner to assign the ownership of a page to someone else.