SWAT - A simple Web-based Anomalies Tracker - Part 1

• Download SQL scripts - 1.5 Kb

Fig.1 Swat's Main Editing Page

Introduction

As a great philosopher of our times has so eloquently put it, it�s �deja vu all over again�. Remember OLE? Remember COM? Technologies which in their turn, was each supposed to be the foundation for everything to come from Microsoft. Remember how hard it was to understand them? Worse yet, implement them? So when I started hearing about the technology du jour '.NET' I must admit it was with mixed feelings.

As with the previous technologies you can only put it off for so long before it overcomes you. In the .NET case I decided to take a pre-emptive approach and try to learn it before I actually had to use it. Fortunately, I was pleasantly surprised with what I found. At first glance it seemed to me to be a 'mish-mash' (if there isn't such a word there should be) of VB, Java, and C++ while providing support for both desktop and WEB based applications in a unified environment. There appeared to be a lot of new concepts (not new as invented, but new to the development environment) and I kept hearing myself say 'well it's about time'.

In general, I found it to be a much more enjoyable experience than I've had with previous technologies. It seems to be a lot more complete and intuitive than other initiatives that Microsoft has 'bestowed' on us in the past. The documentation is, as will always be, 'lacking'. But, considering the number of classes that make up '.NET' (and we thought the original Windows API was daunting, hah!), it's not bad. And of course it is also another 'paradign shift'. We went from being in complete control under DOS, to 'we'll call you' in Windows, to now operating in a 'virtual play pen'! Talk about insecurity complex. In my opinion, developing under the .NET environment requires a mindset change not unlike the DOS to Windows shift.

So what follows is an account of my experience in learning to develop in the .NET environment. As I alluded to above, .NET is actually a two-headed creature and my immediate goal was to tackle the WEB based development, specifically ASP.NET. Even though it's all under the .NET umbrella, desktop development still remained pretty much as before and seemed to me to be a less drastic change. There�s still a learning curve there, but at least for me, seemed to be more evolutionary.

Even though the prose may sound like a tutorial at times, it is not my intention. There are probably better approaches to some of the solutions that I describe. Having said that however, some readers may benefit from seeing how I �figured-out� how to do certain things. Others may find the application useful on its own. Still others may use it as an example of a �real-world� application and can benefit from seeing the mechanics of the application. As for me, I will benefit by having thousands of code reviewers provide me with constructive comments so I can continue learning.

Here's some of the topic areas that will be covered in the course of these articles:

• How to add multiple columns to DataList
• Performing database operations using DataList
• Paging a DataList
• Sorting in a DataList
• Using ViewState variables
• Dynamic control visualization
• User customizable display
• Detecting events from embedded controls
• Automatic page control movement
• Creating a user control
• Adding Email functionality to an application
• Rendering dynamically generated images
• Persisting user preferences using cookies
• Client-side scripting
• Using XML and DOM

On With The Show

Normally, when learning a new technology I think most of us search out a tutorial book and try to follow along with the sample code presented. The sample code usually consists of some form of the obligatory 'hello world' app and code snippets to demonstrate selective areas of the API. I don't know about you, but as for me, the only way to 'really learn� a new technology is to apply it. Reading about the syntax and the hello world sample just doesn't do much for me. Since the applications I was developing for my employer were not using .NET, I decided that I needed to come up with an idea for an application that I could write which would allow me to get experience developing in the new environment. I wanted something that was not trivial and that I could fully define the requirements. At the same time I wanted it to be a useful application not just something that would exercise the new API.

So I thought, 'what is an application that I can define completely, that I can use (as a developer), and that can benefit from being WEB enabled'. What is dear and near to any developer (or at least should be)? A bug tracker! Being the end user of a bug tracking application I would certainly be able to define (and understand) the requirements. I would also have the capability of being able to analyze the features provided by commercially available packages. And, I thought that it would be challenging enough yet not be overwhelming (the project would be done at nights and weekends and I have a wife and kids competing for my time).

One of the main benefits that I saw of making the application WEB-enabled was that it could be accessed from anywhere. In a meeting, from the lab, or while traveling (OK, maybe not the developer but the manager would certainly like to know the status while traveling). The other was that it could be (potentially) platform agnostic. All that was required was a compatible browser, and a project being developed on a MAC using Fortran and a project developed on Unix using Ada could both use this as their bug tracking system.

Application Requirements

After thinking about it for a little bit I came up with a short list of product requirements. This would be something that marketing would put together or perhaps an end user's description. It provides no details as to how it's going to be done just some 20,000 feet level description. So the application is going to be a bug tracking application that will be:

• Easy to use and administer
• Completely web based
• Have a 'Desktop-like' UI
• And be full featured with notification, analysis and reporting capabilities

Since I am the user I can certainly state exactly what I want as the end product. So I want the application to be easy to the point of being trivial to use. Administration requirements should be minimal, no need to have a dedicated IS person just to maintain the application. Administration should also be as trivial as using it. There should be a minimum amount of configuration required.

Because I'm learning WEB development, the application needs to be WEB based. Well no, that's not really a product requirement, but as I mentioned above there are some real benefits associated with making this application WEB based.

The application needs to look and function like a desktop application. What I really want is the best of both worlds, the benefits of the WEB and the UI that we�ve been used to for desktop apps.

Finally, even though the application is being developed as a learning project, I want it to have all of the functionality available in commercial applications. This includes customizable Email notifications, graphical project status analysis tools, and flexible reporting options.

Functional Requirements

The primary function of the application is to allow users to add/edit and keep track of bugs and their state. That's simple enough and to the point. Everything else is secondary or is required only to support the primary function. I think it�s important for any project to keep the primary purpose visible during the whole development process. Why is it that we�re standing in the middle of the swamp filled with alligators? This allows the design team to focus on a goal and also reduces (can never eliminate) �feature creep�.

Since the application is to be WEB based, some form of user authentication must be provided in order to allow access to the application (more importantly deny access to everybody else).

A negative side-effect of making an application WEB based is that it will incur delays that are not apparent on a desktop counterpart. So one of the design objectives is to reduce WEB effects (delays). One of the �traps� that I noticed when developing in ASP.NET is that because you can do just about everything on the server, we do. I think we need to be more diligent in analyzing what we�re doing in order to try to optimize the application. One of the optimizations we�ll implement is to use stored procedures for our database access. In other areas we�ll look at using client side script where appropriate.

The application is to be administered through the WEB as well. All configuration information required by the application will be accessible using the same UI (as opposed to requiring a separate desktop app that would run on the server to configure the database or other application data). And in order to protect the application�s configuration information the application will implement �roles� based access. As such only users that have �Admin� role will be able to administer the database and application data.

That's the core functional description of the application. An additional feature that I want is user notification capabilities. The application should send an Email notifying the user(s) when a bug changes state. There are certain basic rules that an administrator should be able to define to control who and when gets notified as a result of a change in bug state.

The application will also provide facilities that allow the user to analyze the state of the project by providing a graphical representation of the data. The user should be able to select the project/module to analyze as well as to filter by severity. This will allow the user to see the current state of the project (how many bugs open/closed/fixed). The analysis tool will also provide trending analysis of the project by providing historical data in a graphical format. Using the graphical tools the user will be able to easily determine how stable the application is and more importantly how close is the project to being on schedule.

Finally the application will provide facilities to allow the user to make customizable queries on the database to generate user definable reports.

Database design

At this point it's probably a good idea to take a look at the requirements and attempt to map out the database schema. Since the primary function of the application deals with �bugs� we need to define what a bug is from the application�s perspective. Bugs have the following properties:

• a title
• a description
• they have an owner (person assigned to fix the bug)
• they are assigned a creation date and a 'creator' (person who opened the bug)
• they are assigned a closed date and a 'closer' (person who closed the bug, verified that it was indeed fixed)
• they can be in one of three states (which are sequential) open, fixed, closed.
• a bug is assigned a priority (how quickly we need to get on it)
• a bug is assigned a severity (how bad is the bug)
• a bug is defined by the module/project where it was found
• a bug is assigned a project revision
• a bug has a resolution (not a bug, how it was fixed, etc.)

The properties can be translated into the following table definition for our bugs:

To support the above table we need a table that will hold a list of users (the user field in the above table is a foreign key) and a list of projects. Additionally, each project is composed of a list of modules. So let�s define those tables now.

That�s it. Pretty simple database. When we get to the code you�ll see why each table has a common �ItemName� field. A note on the �Active� field, I�ve included it to indicate whether a user is active or has (been) moved on to greener pastures. This can be used to filter users so that only active users will be displayed in selection lists. At the same time the user stays in the database to preserve historical information.

Here�s a script for creating the database and tables.

Create Database SwatBugs
GO
USE SwatBugs
GO
/****** Create login ASPNET ******/
EXEC sp_addlogin N'ASPNET',, N'SWATBUGS'
GO
/****** Create User ASPNET ******/
EXEC sp_adduser N'ASPNET'
GO
/****** Create Table BUGS ******/
CREATE TABLE [SWATBUGS].[dbo]. (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ItemName] [nvarchar] (50) NOT NULL ,
[Description] [nvarchar] (1024) NULL ,
[Project] [int] NOT NULL ,
[Module] [int] NOT NULL ,
[Revision] [nvarchar] (10) NOT NULL ,
[Status] [tinyint] NOT NULL ,
[EnteredDate] [datetime] NOT NULL ,
[FixedDate] [datetime] NULL ,
[ClosedDate] [datetime] NULL ,
[FixedBy] [int] NULL ,
[ClosedBy] [int] NULL ,
[AssignedTo] [int] NOT NULL ,
[EnteredBy] [int] NOT NULL ,
[Severity] [tinyint] NOT NULL ,
[Priority] [tinyint] NOT NULL ,
) ON [PRIMARY]
GO
/****** Permissions for ASPNET on bugs ******/
GRANT SELECT, INSERT, UPDATE, DELETE
ON bugs
TO ASPNET
GO
/****** Create Table MODULES ******/
CREATE TABLE [SWATBUGS].[dbo].[MODULES] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Project] [int] NOT NULL ,
[ItemName] [nvarchar] (50) NOT NULL
) ON [PRIMARY]
GO
/****** Permissions for ASPNET on modules ******/
GRANT SELECT, INSERT, UPDATE, DELETE
ON modules
TO ASPNET
GO
/****** Create Table PROJECTS ******/
CREATE TABLE [SWATBUGS].[dbo].[PROJECTS] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ItemName] [nvarchar] (50) NOT NULL
) ON [PRIMARY]
GO
/****** Permissions for ASPNET on projects ******/
GRANT SELECT, INSERT, UPDATE, DELETE
ON projects
TO ASPNET
GO
/****** Create Table USERS ******/
CREATE TABLE [SWATBUGS].[dbo].[USERS] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ItemName] [nvarchar] (64) NOT NULL ,
[Password] [nvarchar] (128) NOT NULL ,
[Email] [nvarchar] (50) NULL ,
[Roles] [tinyint] NULL ,
[Active] [tinyint] NOT NULL
) ON [PRIMARY]
GO
/****** Permissions for ASPNET on users ******/
GRANT SELECT, INSERT, UPDATE, DELETE
ON users
TO ASPNET
GO
ALTER TABLE [SWATBUGS].[dbo]. WITH NOCHECK ADD
CONSTRAINT [DF_BUGS_Revision] DEFAULT (0) FOR [Revision]
GO
ALTER TABLE [SWATBUGS].[dbo].[USERS] WITH NOCHECK ADD
CONSTRAINT [DF_USERS_Active] DEFAULT (1) FOR [Active]
GO

You can either run the script from SQL Server�s Query Analyzer (see �Platform Requirements� below), or you can use osql.exe from a command line as follows (you can use �/?� to see all osql parameter options):

osql �E �i swat.sql

Note: To run the script you�ll need to be a member of the sysadmin and securityadmin fixed server roles.

Platform Requirements

Of course you�ll need to have the .Net Framework installed on the server that you�ll be using to host the application. If IIS is not installed, install and configure it before you install the framework so it sets up the default ASPNET user for you. If you�re already using SQL Server then you�re almost there. If you don�t, you can download a free copy of MSDE from Microsoft (http://www.asp.net/webmatrix and look for MSDE download). MSDE is completely compatible with SQL server. The main restrictions are the number of users (up to five (5) concurrent users) and the size of the database (only 2 GB). For this application those are not significant restrictions. If a larger number of users needs to be supported then all that�s required is to purchase and install SQL Server. The other main deficiency of using MSDE is that it doesn�t have an administrator UI. If you need to (or want to) modify the database there are a couple of free utilities out there that you can use.

Phased Development

I think there are three key ingredients to successful development. I didn't invent them and they are not rocket science. They are more common sense than anything else. However, I've seen enough failed projects to appreciate their importance. Each one deserves more space than I can devote here so I'll just briefly mention them.

Remember when your mother used to tell you not to put more in your mouth than you could chew? Well the same applies to software development. We took great strides in the late 80's and early 90's with the advancements made in object oriented design and development tools. These helped us to break things down into bite size pieces that we could easily chew. Unfortunately something happened on the way to the Internet. We didn't need any 'stinkin methodologies', all we needed was Notepad and we were in business. There was a big rush, and of course we all know what happened to that balloon. I think we've made a comeback and .Net is an excellent facilitator. The first key ingredient, then, is to use a design methodology. Even if it�s just some block diagrams. The important thing is to use one (it forces us think before we jump).

I'm a firm believer that our profession is no different than any other profession and they are all governed by processes and procedures. Do you think there isn't a process for successfully building a house, or that personnel in an operating room don't follow a 'strictly enforced' procedure. Just try to think of one profession that is not defined by a process. Unfortunately very few companies have an 'enforced' formal software development process. Which is the main reason why most projects are late and overrun their budget. Some people contend that following a formal development process restricts or somehow inhibits creativity. I believe that a process can actually encourage creativity and innovation by having it be an integral part of development. In the initial phase of the project we want to make sure that we do plenty of brainstorming and prototyping. Explore and be creative, that�s the time to do it. What most often happens is that we bypass the creativity in the initial phase (because there�s never enough time) and then at the tail end we�re trying to figure things out and wind up �shoe-horning� something just to get it to work. That�s not innovation nor is it enjoyable. The second ingredient then is to follow a process. It doesn't have to be complicated but it is important that it have a feedback mechanism so mistakes will be elliminated (not repeated) and improvements can be incorporated.

The third and final ingredient is to develop in phases. Break down the project into testable/releasable phases. This has many benefits. First, you�ll have more control with what you�re doing and testing. It acts as checkpoints to gauge your progress and allows you to compare to your schedule. It allows testers and documentation to get into the act earlier in the development process. And it provides an early means for feedback from marketing and/or users (it�s better to know you�re way off base at the beginning than at the end). Granted, this approach will require more regression testing but you will find that the benefits will greatly outweigh the drawbacks.

Here�s a list of the phases we�ll follow in developing the project and the functionality that will be implemented in each phase.

• Phase 1 - logon, bug editing, admin functions
• Phase 2 - email notification support
• Phase 3 - analysis
• Phase 4 - reporting

That's as far as we'll go for now. The second article in this series will continue by starting to implement the functionality described for phase 1.