Introduction
This article describes using a Wiki for knowledge sharing and database schema documentation.
In a departure from normal CodeProject articles this is less about the code and more about improving development practices. We will discuss what a Wiki is and how you might use one. Hopefully from this overview you'll be able to see how it might benefit you and your team to share information.
After the summary of what a Wiki is, I'll discuss customising a free Microsoft Active Server Pages (ASP) implementation and adding a custom macro to the Wiki for displaying table information from a SQL Server 2000 database. Following that how to install the code and setup your own wiki.
It should be noted, I did not write the Wiki implementation and in this article I only set forward some minor customisation and additions; I will not be looking at how this Wiki is implemented internally.
We will be using Open Wiki, created by Laurens Pit, as the basis for our Wiki; it runs on Microsoft Internet Information Server (IIS). Also note that I use the Open Wiki code under the license terms as Laurens Pit set out and that he does not endorse or promote the changes presented here. The standard Open Wiki license still applies, including, � this software is provided by the copyright holders and contributors "as is" and any express or implied warranties, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose are disclaimed. In no event shall the regents or contributors be liable for any direct, indirect, incidental, special, exemplary, or consequential damages (including, but not limited to, procurement of substitute goods or services; loss of use, data, or profits; or business interruption) however caused and on any theory of liability, whether in contract, strict liability, or tort (including negligence or otherwise) arising in any way out of the use of this software, even if advised of the possibility of such damage.�
Now that's over, back on with the article...
What is a Wiki?
To paraphrase,
�A Wiki is an online collaboration tool - a web site where the pages can be changed and published instantly using only a web browser (no programming knowledge required). Pages are automatically created and linked to each other.
A Wiki is a collaborative open content discussion. What does this mean?
- It's collaborative because it relies on contributions from its readers - the more the better!
- It's 'open content' because anyone can make a contribution to the site's content.
It allows anyone to improve a web page, and it removes the limitation of the Web as a one-sided communication medium.�
So what does that really mean to us? Well I have used a Wiki for sharing information on a number of different projects that I've worked on and it has proved to be a much better repository for requirements and development information than traditional documentation.
Normally one person writes up how a particular feature works and then saves this as a Microsoft Word document on a file share somewhere. The problems arise when people don't know where to find this information and keeping it up-to-date seems like a lot of work.
By using a Wiki, you create one location where all documentation on a project resides and you can share it with people by just giving them the URL. The best part is then they can update and add to it if they have any new information or find a mistake. We'll cover how this works in practice in the coming sections.
Why do Wikis work?
Again paraphrasing,
�The concept of a Wiki Wiki (or 'Wiki' for short) is that the quality of content improves when everyone is allowed, even encouraged, to author and refactor any page.
Wikis don't work if people aren't bold . You've got to get out there and make those changes, correct that grammar, add those facts, and make that language precise. It's okay - it's what everyone expects. So you should never ask, "Why aren't these pages copyedited?" - you should just fix it. It does require some amount of politeness, but amazingly it works.
Let's be absolutely clear: if someone writes an inferior article, don't worry about their feelings. Correct it, add to it, and if it's a total waste of time, outright replace it with something better. That's the whole point of a Wiki! And nothing's lost anyway - it's all kept in the page's version history, so can be recovered if need be. And of course, others will boldly and mercilessly edit what you write. Don't take it personally. They, like all of us, just want to make the content of the Wiki the best it can be.�
The original WikiWikiWeb was established by Ward Cunningham, who invented and named the Wiki concept, and produced the first implementation of a Wiki Wiki server. But many others exist, both free and commercial. A quick search on Google will provide lots more information.
Both Snipsnap (Java SourceForge) and FlexWiki (ASP.NET) are other open implementations and Atlassian have recently released Confluence an impressive commercial Wiki system.
Even a Wiki encyclopedia is available on the Internet for people to contribute to.
Although there are a number of Wikis on the Internet and people don't seem to abuse them; I would recommend starting one in your team and get used to it internally with trusted readers first.
If anyone has read the Microsoft Press book, Analyzing Requirements and Defining Microsoft� .NET Solution Architectures, MCSD Exam 70-300, they will have heard of the term living documents.
The Microsoft Solution Framework (MSF) Process Model puts forward best practises and ideas for software development. One of these is to create living documentation and for all parties involved in the project to share information and timescales. Using a Wiki can definitely help with this process and you could use the MSF templates available as a starting point for many of the pages and sections.
Microsoft Technet has a good overview of what MSF is and how it can help your development processes.
I'm all for great documentation on projects; some developers tend to like to keep little bits of information to themselves thinking it makes them indispensable. But the way I like to think of it is, if I can share my domain specific knowledge hopefully I'll be able to move onto other new and exciting projects without taking away the development knowledge.
Strangely perhaps, I actually �enjoy� writing up documentation as part of the different aspects of software development; the requirements gathering, design, planning, coding, testing and rollout. When a system I've worked on goes out, whether internally to the company or externally as a shipping project it should be thought of as an entire piece of work with the actual application, training, documentation and support elements all produced to the highest level.
So maybe that is why I think Wikis can help enormously at keeping all parties informed.
The Open Wiki implemention which we are going to use provides all the basic Wiki features and is the code base that we will customise. You can try Open Wiki online at http://www.openwiki.com/.
rom there you can download the latest version which comes with an automated setup package. If you want any information on the actual Open Wiki functionality, the website is a very good place to start, then later you might want to look directly at the ASP source code. There are not that many ASP pages as most of the functionality is the XSL transforms that build up the individual pages from the XML stored in the database.
From the original Open Wiki code we have made number of slight alterations.
In the different teams I have worked with we found that although Wikis are meant to be clean and simple, adding a touch of colour, design and layout does go along way with getting users onboard initially.
So from the clean white look above we added a few images and altered a couple of the XSL files for the pages, mainly ow\xsl\ow.xsl.
Let's talk about the layout of the pages. At the top is a banner, you can alter the two images replacing the project title and company logo as necessary, ow\images\banner_logo.gif and banner_title.gif.
Next we have the main sections, �Home� obviously brings you back to the start page, �Recent Changes� will show you a history of alterations to the different pages of the site.
If you need to search the information contained in the site, you can use either the �Find Page� section or the search box on the right.
A full �Title Index� of all the pages in the site is always available and so is a �Sand Box� area where you can play around with how the Wiki page markup works.
You can setup your �Preferences� and access �Help� on all aspects of the Wiki editing. These sections are discussed fully later in this article.
Under the section banner is a breadcrumb trail, allowing you to navigate back to pages you were recently viewing.
Finally there are options to �edit the page�, �view revision history�, �print� or �compare differences� between versions of the page as well as the date the page as last edited.
Again at the bottom of the page these links are repeated.
Now let's see how all this in-place editing and updating content works.
To edit a page you don't need to access the web page HTML or anything like that, you just edit the content directly in the site.
You can either double-click anywhere on the page body or click the edit link.
Before we do that though, navigate to the �Sand Box� page, here you can play around with learning how editing a Wiki works.
So double-click on the page and it will change to the editing layout, as shown below:
You just type directly into the editing text area and mark up the information using special Wiki styles and macros.
For example, two **s around a word makes it appear bold in the page.
Special macros are available also, like <TableOfContents> which writes out all of the headings in a page.
If you click on the link �Help On Formatting� you can find out all about the different styles that are available to use.
Try typing in the editing area like this:
Many people wonder why not just use HTML tags, eg. <b> <i> etc. but these do take quite a bit of learning for non-technical people and the Wiki formatting is simpler.
After clicking the �Save� button the page will redisplay and should look like this:
The real benefit of using a Wiki to share knowledge is that it is so easy for people to change the content, and because of this people tend to make the effort keep it up to date especially if they find a mistake while reading it.
We can see how the page has changed before it was edited by clicking on difference (diff) link. The red crossed-out text has been removed and the new blue text inserted. All revisions are kept, meaning we can see changes to documentation easily over time and revert back if necessary.
Now we have edited a page, let's see how to create a new one. This is the task that quite often people have problems with because there isn't a new page command exactly.
The main way of creating new pages is to actually type the name of the �new page� into the �current page� you are wanting to link from and it will handled automatically if the name matches a set pattern.
The pattern is one or more uppercase letters, then one or more lowercase letters, then one uppercase letter, then any letters (either upper or lowercase).
In other words, simply SmashWordsTogetherLikeSo.
Note that a name conforming to this pattern is not hyperlinked if that name is the same as the current page title you are viewing.
Once you submit a page with a new linked name in it you'll notice that a question mark appears after the name (and the name is still in the 'smashed together' form).
This means the page hasn't been defined yet but if you click on the question mark it will be automatically generated (the question mark will disappear from the referencing page and turn into a proper link - with the text 'unsmashed').
If you want to write a smashed-together word but don't want it to be linked simply do one of the following:
- Use a tilde (~) character in front of the name, e.g. ~BatchJobs
- Use the <nowiki> tag, e.g. <nowiki>BatchJobs</nowiki>
Follow the question mark hyperlink and it will take you to the newly created page ready for you to add the content.
Let's look at some other ways you can link to new pages.
Sometimes it is useful to have the link appear with a different name to the destination page. For instance you may wish the link to appear as �click here� rather than the page name. You can do this by enclosing the link in square brackets and appending the name you wish to appear:
[TutorialInfo How to use the system]
To link to a single-word page use two square brackets, like this:
[[Rollout]]
Should look like this:
Another way is on the Find Page section, you can search for a page and the Wiki will create it if the page doesn't already exist.
So as you have seen it is very easy to maintain existing content and to create new content. These two elements combine together to encourage people to keep information relevant and correct.
Let's now take a more in-depth look at the different sections of the Wiki site which we discussed earlier.
This page shows the last 7 days, 30 days or 90 days of revisions to the site. It is useful for finding out what the latest additions or alterations are to the site or project.
When you only have a few pages in a Wiki it seems quite easy to navigate about, but as always it is useful to be able to search for specific words or pages.
You can search by the page title, perform a full-text search (including regular expressions) or just type the name of the page directly. The full-text search is always available at the right hand side of the banner.
Pages are also listed in alphabetical order on this so you can locate information this way too.
This is where you can test content and play around with how the different Wiki markup elements affect the page layout.
Although edits to a page can be anonymous it often helps to know who has edited the page, so here you should set your username and other preferences.
Because editing and formatting a Wiki uses a new type of markup you might need to refer to the Help pages when you first start using it. These pages detail how to format text and tables and how to create and link pages together.
A great feature you get for free using Open Wiki is an RSS feed built-in which allows users to take a feed of the latest changes to the site.
By clicking on the RSS image in the bottom-right of the site you can view the RSS XML content.
RSS is a Web content syndication format; its name is an acronym for Really Simple Syndication.
RSS is a dialect of XML and all RSS files must conform to the XML 1.0 specification, as published on the World Wide Web Consortium (W3C) website.
But the real benefits of RSS are when you use an aggregator to combine and browse multiple feeds.
I use SharpReader, which is written using the .NET framework and was created by Luke Hutteman, as an RSS news feed reader.
It will automatically poll the subscribed feeds for new changes and allow you to view content from multiple providers in one convenient location.
You may have also heard of blogs (weblogs) which are a type of on-line diary that quite a few people publish now. These tend to also provide RSS feeds of changes as well; see http://blogs.msdn.com/ for a list of Microsoft bloggers.
I can recommend reading Raymond Chen for historical entries of why Windows works the way it does and Tim Sneath for news on the Microsoft UK . NET happenings.
If you create a new page ending in the word �Template� then it automatically becomes available as a template you can use for other new pages by just clicking on the links shown below:
For example I created a meeting template, so that the minutes of meetings can be written up in a standard format easily. Note templates do not support inheriting changes later, but they can be a handy start for page content.
Open Wiki includes special operations called �macros� which are recognized if the name appears between a pair of left and right angle bracket <...> characters. For instance, <TableOfContents>.
Some macros accept one or more parameters. These are given directly following the macro name and are enclosed between a pair of left and right parenthesis (...) characters, separated by commas. For instance, <FullSearch("^Help")> , <RecentChanges(10,30)>.
See the Help > Macros page for more information on the built-in commands.
For help on writing your own macros see the comments at the top of the file, ow\my\mymacros.asp.
A couple of useful extra macros we have added are the ability to include images in pages and a progressbar, see the Open Wiki website for more examples.
Sub MacroImageP(pParam)
gMacroReturn = "<div style=""width:100%;overflow-x:auto"">" & _
"<img src=""images/" & pParam & """ border=""0"" alt=""" & _
pParam & """/></div>"
End Sub
Which you can then use by calling it from your page:
If you are using the Wiki to track progress on different tasks in your project, a quick and simple solution is to use this little macro. The developer can update it easily to give an indication if they have started or are nearly finished the task.
Sub MacroProgressBarPP(pWidth, pPercent)
If pPercent < 0 Or pPercent > 100 Then
gMacroReturn = "<ow:error>ProgressBar error: pPercent must be " &_
"[0..100]</ow:error>"
Else
gMacroReturn = "<ow:progressbar pbWidth=""" & pWidth &_
""" pbPercent=""" & _
pPercent & """ pbPercentLeft=""" & 100-pPercent & """ />"
End If
End Sub
<xsl:template match="ow:progressbar">
<table border="1" cellpadding="0" cellspacing="0">
<xsl:attribute name="width"><xsl:value-of select="@pbWidth"/>
</xsl:attribute>
<tr>
<td height="10" bgColor="#0000ff"><xsl:attribute name="width">
<xsl:value-of select="@pbPercent"/>%</xsl:attribute> </td>
<xsl:if test="@pbPercentLeft!=0">
<td height="10" bgColor="#ffffff"><xsl:attribute name="width">
<xsl:value-of select="@pbPercentLeft"/>%</xsl:attribute> </td>
</xsl:if>
</tr>
</table>
</xsl:template>
Which would appear like this on the page:
Finally we get to some code and new functionality!
Often on systems development documenting the database schema and relationships is one of those jobs that falls behind the development but is so important for future maintenance.
To help with this I wrote a custom macro, much like the <Include(pageName)> macros build into Open Wiki. The difference being this one reads the SQL Server database schema to create the page information as needed.
So you can type into a page, <DatabaseTable(authors)>, and if the table exists in the same database as the wiki tables, the macro will build a page detailing the schema information.
This works fine for my needs, as I tend to include the Wiki base tables in the database of each project I setup a Wiki for, but can edit the macro for your needs. Now when the page is accessed it generates the information shown below, detailing the columns, indexes and stored procedures dependencies and the top five rows from the table.
This takes a lot of the work out of trying to keep the documentation in-sync with your database schema information; obviously you can also add extra comments or links to other pages in the Wiki with more detailed information as needed.
I'll briefly discuss the internals of the macro, but it isn't meant to be the best designed function ever so apologies now.
I added the following code to ow/my/mymacros.asp. It basically builds up a large HTML string (efficiently!) with the information on the table.
Sub MacroDatabaseTableP(pParam)
Dim oConn
Dim oCmd
Dim oParam
Dim oRsColumns
Dim oRsPrimary
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open OPENWIKI_DB
oConn.CursorLocation = adUseClient
Set oCmd = Server.CreateObject("ADODB.Command")
Set oCmd.ActiveConnection = oConn
oCmd.CommandText = "wikidbschema"
oCmd.CommandType = adCmdStoredProc
Set oParam = Server.CreateObject("ADODB.Parameter")
With oParam
.Name = "tablename"
.Type = 8
.Size = 255
.Direction = 1
.Value = pParam
End With
Call oCmd.Parameters.Append(oParam)
Set oRsColumns = oCmd.Execute
If oRsColumns.EOF = True Then
gMacroReturn = FormatXMLString("Table '" & pParam & "' does not exist.")
Exit Sub
End If
gMacroReturn = gMacroReturn & "<table><tr style=""font:8pt""><td> <b>"
gMacroReturn = gMacroReturn & "Column</b> </td><td> <b>Data Type</b> "
gMacroReturn = gMacroReturn & "</td><td> <b><nobr>Allow NULLs</nobr></b> "
gMacroReturn = gMacroReturn & "</td><td><b>Default</b></td></tr>"
Do Until oRsColumns.EOF
gMacroReturn = gMacroReturn & "<tr valign=""TOP"" style=""font:8pt"">"
gMacroReturn = gMacroReturn & "<td>" & oRsColumns("column_name") & "</td>"
gMacroReturn = gMacroReturn & "<td><nobr>" & oRsColumns("data_type") & _
" (" & oRsColumns("length") & ")"
gMacroReturn = gMacroReturn & "</nobr></td>"
If oRsColumns("is_nullable") = "Y" Then
gMacroReturn = gMacroReturn & "<td align=""MIDDLE"">" & _
"<img src=""images/wiki/tick.gif""></img></td>"
Else
gMacroReturn = gMacroReturn & "<td align=""MIDDLE"">" & _
"<img src=""images/wiki/notick.gif""></img></td>"
End If
If IsNull(oRsColumns("default_value")) Then
gMacroReturn = gMacroReturn & "<td align=""MIDDLE""> </td>"
Else
gMacroReturn = gMacroReturn & "<td align=""MIDDLE""><nobr>" & _
oRsColumns("default_value") & "</nobr></td>"
End if
gMacroReturn = gMacroReturn & "</tr>"
oRsColumns.MoveNext
Loop
gMacroReturn = gMacroReturn & "</table>"
...
Exit Sub
It could pull back more schema information on the table; but I just kept with column names, primary key, indexes and the first five rows of data.
When working out how best to extract the schema information I looked at sp_help and also used the SQL Server tracing profiler to see which system commands SQL Enterprise Manager used to get the information when looking at the properties of a table.
Exec sp_MShelpcolumns N'[dbo].[discounts]'
Exec sp_MStablekeys N'[dbo].[discount]'
But in the end I decided to use a database documentation script from SQLServerCentral and a little extra SQL. There is definately more security checks and efficiency that could be added by rewriting the macro but it does the job for my needs.
For non-technical people it can be quite daunting trying to setup Open Wiki. You need to make sure you have certain software installed, decide which database back-end to use as the storage and configure the basic settings; but it honestly isn't that difficult.
I'm going to assume a certain level of knowledge, given this article is for the CodeProject website. You can download an installer from http://www.openwiki.com/ that automatically creates the VDirectory under IIS etc. but I'm going to explain it manually instead.
To run Open Wiki on your own machine you'd need:
- Microsoft PWS (Personal Web Server) or Microsoft IIS 4.0 or higher.
- VBScript 5.5 or higher installed.
- MSXML Parser 3.0 SP2 or higher installed.
- a database like Microsoft Access, Microsoft SQL Server (or MSDE) or Oracle.
If you have Windows XP Professional (or Windows 2000/3 Server) then you can install IIS via Control Panel > Add or Remove Programs > Add / Remove Windows components and choose IIS. Remember if you install IIS you should be aware about checking for and installing any IIS hotfixes or other patches regularly as well as any normal critical Windows updates.
Next if you have installed the Internet Explorer 5.5 SP2 or IE 6 browser, then you should have the MDAC drivers for Access, SQL Server and Oracle. Note that I have only ever used Access or SQL Server for the Wiki datastore.
If you have MSXML Parser 4.0 installed, then you should change the constant MSXML_VERSION in the file ow/owconfig_default.asp to value 4, ie. MSXML_VERSION = 4.
Now we have all the required components installed, create a new directory under the root of your web server, ie. c:\inetpub\wwwroot\ called wiki. You can change this later, but let's just get things up and running first.
Copy the files from the article download to this new directory. Then open the file ow/owconfig_default.asp with notepad or another text editor.
Here are some of the lines you might want to edit in the config file, including the project title.
<%
"DBQ=e:\data\openwiki\OpenWiki.mdb"
"pwd=openwiki;database=OpenWiki"
"Uid=laurens;Pwd=aphex2twin;"
OPENWIKI_DB = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;Initial Catalog=wiki;" & _
"Data Source=(local)"
OPENWIKI_DB_SYNTAX = DB_SQLSERVER
OPENWIKI_TITLE = "Project Title"
...
OPENWIKI_MAXTEXT = 204800
...
OPENWIKI_DAYSTOKEEP = 30
MSXML_VERSION = 3
...
gReadPassword = ""
gEditPassword = ""
...
gDefaultBookmarks = "Home RecentChanges FindPage TitleIndex SandBox" & _
" UserPreferences Help"
cReadOnly = 0
...
cFreeLinks = 1
cWikiLinks = 1
...
cCacheXSL = 0
cCacheXML = 0
...
cAllowRSSExport = 1
...
cEmbeddedMode = 0
...
%>
Also you change the two logo files in the banner by altering images, ow\images\banner_logo.gif and banner_title.gif.
You must configure what database you are going to use to store the page content, I have included the Access .mdb file and a SQL Server backup file in a directory called data. So update OPENWIKI_DB and OPENWIKI_DB_SYNTAX as necessary.
On the one hand it is easier to setup Access because you can just copy the file and do not require SQL Server, but getting the permissions correct so the IIS process can read/write to the file is sometimes a pain. Have a look at the installation advice on the Open Wiki website for further discussion. I'm going to assume if you are using SQL Server you know how to restore a database using SQL Server Enterprise manager.
Then we should be in business, make sure IIS is started, and SQL Server (if being used), and browse to http://localhost/wiki and away you go.
If people have problems setting it all up, leave a comment or e-mail me and I'll update this article with a more step-by-step guide.
The changes made in this article to the original Open Wiki are minor; we enhanced the page design and added a couple of macros, including one for documenting SQL Server 2000 database schema information.
But further enhancements could include:
- Allowing better incorporation of images, rather than having to save them directly to the webserver and link to them by a macro, ie. just being able to paste from the clipboard.
- Integrated spellchecking, although using IESpell does work well.
- Rich-text editing, although not sure if this does away with a key simplicity.
- Printing of the entire site as one document for reference.
- Ability to add comments to a page that are not part of the main body of the page.
- Finding orphaned pages, ie. pages which are not linked to from other pages.
- Expand the macro support documenting Microsoft Access or Oracle database designs as well.
- Document additional database information including stored procedures and triggers, etc.
- Team and role based security for editing and viewing pages.
- Adding colour support for marking up text.
- Site usage statistics, ie. most popular pages, number of users, etc.
- E-mail notification of changes, on an individual or per day watch criteria.
- Non-reliance on IIS/VBScript/MSXML to make it more portable and easier to host.
- Ability to upload attachments without having to install additional COM objects.
As this article hasn't been about coding as such, I still hope you will have found it useful and can see how you could apply the techniques to your team or company to better make use of knowledge.
Full credit has to go to the developer of Open Wiki, Laurens Pit, for the making the code available in the first place and making it easy to customise. As an example of XML and XSL usage it is outstanding, normally I'm not the biggest champion of XSLT.
I didn't want the article not to have any code written by myself, so I thought I would write how the additional macro can help document database schema information.
By tracing the SQL Server while using SQL Server Enterprise Manager, I could capture how it accessed the table schema information itself from this I was able to work out how best to interrogate automatically the data.
Once you change to using a Wiki you won't want to go back to having Microsoft Word documents on a file share where no-one knows if it's the latest document or when and what the last changes were.
Using a central website for knowledge sharing has become quite a popular technique and products such as Microsoft Sharepoint Portal Server can help you achieve this as well. Now Ward Cunningham, the inventor of the Wiki, is employed by Microsoft we might well in the future see some more elements of Wiki-type functionality in their new products.
- Wiki, where it was all first started by Ward Cunningham.
- Open Wiki, a free implementation of a Wiki, which this article is based on.
- Microsoft Solutions Framework (MSF) which provides an in-depth methodology for systems design.
- SharpReader, an RSS news aggregator and reader.
- Apple, for the icons on some of the standard pages.
It is very easy in the Internet age, to miss changing trends and to think everyone has heard of Wikis and blogs but that isn't true.
I hope this article has given a good overview to people what a Wiki is and how they might benefit from using one to share knowledge. It can be just for yourself, your team or your entire company. But next time someone comes and asks you about your project and what you are working on, wouldn't it be great to give them a URL to all your documentation knowing it was up to date.
Finally I'd like to stress again that I did not write the code for this Wiki implementation and I only present here a brief overview of how to use it, the changed look and the custom macro for the database schema documentation.
Thanks and I hope this wasn't too long an article!
8th February 2004 Initial release to the Code Project.
