This solution requires three tables: Users, Sessions, and Tasks. Figure 18-3 shows the table relationships. Figure 18-3. The SuperCompute tables
The Users table contains the logic information for all the users who will access the rendering service. The password is stored in plain text, and all password comparisons default to SQL's standard non-case-sensitive behavior. For increased security, this field could hold encrypted binary information. In addition, the Users table includes an optional NotifyQueue column, which is designed to hold the path to a Message Queuing private queue on the user's computer, which can be used for notification. This feature isn't currently implemented, but is designed for possible future enhancements. If you are installing this case study from the code download, you will find only a single defined user, with the user name testuser and the password secret. The Sessions table is used for the ticket-based authentication. It stores a record for each session currently in progress. Sessions are identified by their unique GUID column, which is passed back to the client. The Sessions table also indicates the date the session was started, which is useful if you need to implement an expiration policy, and the client who is authorized to perform actions with this session ticket. Finally, the Tasks table lists the jobs that have been submitted for rendering. The Tasks table includes file information, a numeric status that indicates whether the task is new (0), in progress (1), completed (3), or halted with an error (2), and the date the task was submitted and completed. The SourceFile corresponds to the original name of the source file, which is retained to help the client identify the task later. The RenderedFileURL provides a UNC or URL path to the rendered file after the rendering has been completed. Stored ProceduresThe basic implementation of the SuperCompute system requires 11 stored procedures, as described in Table 18-1. These stored procedures represent the tasks required by the rendering service and task processor. They don't include any additional functionality to add new user records or profile the system's overall performance.
Listing 18-1 shows the stored procedure code required to validate a user. If a match is found, the unique numeric ID is returned. Listing 18-1 Stored procedures for validating a userCREATE PROCEDURE GetUser ( @UserName varchar(25), @Password varchar(25) ) AS SELECT [ID] FROM Users WHERE UserName = @UserName AND [Password]=@Password Listing 18-2 presents the four stored procedures used to manage session records. The GUID is generated using the NEWID function built into SQL Server. Listing 18-2 Stored procedures for managing sessionsCREATE Procedure AddSession ( @UserID int, @GUID uniqueidentifier OUTPUT ) AS SELECT @GUID = NEWID() INSERT INTO Sessions ( [ID], UserID, CreateDate ) VALUES ( @Guid, @UserID, GETDATE() ) CREATE PROCEDURE GetSession ( @GUID uniqueidentifier, @UserID int ) AS SELECT CreateDate FROM Sessions WHERE [ID] = @GUID AND UserID=@UserID ORDER BY CreateDate DESC CREATE PROCEDURE DeleteSession ( @GUID uniqueidentifier ) AS DELETE FROM Sessions WHERE [ID] = @GUID CREATE PROCEDURE DeleteOldSessions AS DELETE FROM Sessions WHERE DATEDIFF(day, CreateDate, GETDATE()) > 5 The GetSession stored procedure sorts the retrieved rows so that the largest creation date (representing the most recent session) is at the top of the list. It does this because the XML Web service assumes that there is only a single session per user and reads the first record from the list. Typically, there should be only one session record for a given user at a time, but it is possible for an orphaned session record to exist if a user disconnects without calling the Logout XML Web service method. In this case, the session remains until the database administrator executes the DeleteOldSessions stored procedure, which removes any session record created more than five days earlier. The remainder of the stored procedures deal with tasks. The GetTasksByUser and AddTask stored procedures shown in Listing 18-3 are the two stored procedures indirectly available to the user through the render XML Web service. Listing 18-3 User-specific stored procedures for tasksCREATE PROCEDURE GetTasksByUser ( @UserID int ) AS SELECT * FROM Tasks WHERE UserID = @UserID CREATE Procedure AddTask ( @UserID int, @Status int, @SourceName varchar(50), @GUID uniqueidentifier OUTPUT ) AS SELECT @GUID = NEWID() INSERT INTO Tasks ( [ID], UserID, State, SourceName, SubmitDate ) VALUES ( @Guid, @UserID, @Status, @SourceName, GETDATE() ) The task processor uses the GetAvailableTasks, UpdateTaskStatus, and CompleteTask stored procedures to retrieve the tasks that should be processed, to manage the status for each task, and to update the record when the rendering process completes, as shown in Listing 18-4. Listing 18-4 Processor-specific stored procedures for tasksCREATE PROCEDURE GetAvailableTasks AS SELECT * FROM Tasks WHERE State = 0 ORDER BY SubmitDate CREATE Procedure UpdateTaskStatus ( @GUID uniqueidentifier, @Status int ) AS UPDATE Tasks SET State = @Status WHERE [ID] = @GUID CREATE Procedure CompleteTask ( @GUID uniqueidentifier, @RenderedFileURL nvarchar(100), @Status int ) AS UPDATE Tasks SET State = @Status, RenderedFileURL = @RenderedFileURL, CompleteDate = GETDATE() WHERE [ID] = @GUID Note that the GetAvailableTasks stored procedure sorts the results so that the earliest dates are first. This ensures that tasks are processed in first-in-first-out (FIFO) order. The Data ComponentAs with the previous case studies (and every well-designed enterprise application), database access takes place solely through a dedicated database component. Both the rendering XML Web service and the server-side task processor use this database component. Listing 18-5 shows the entity portion of the database component, which defines classes to represent the rows in each table. Listing 18-5 Data entitiesPublic Class UserDetails Public UserID As Integer Public UserName As String Public Password As String Public Queue As String End Class Public Class SessionDetails Public CreateDate As DateTime Public UserID As Integer Public Ticket As Guid End Class Public Enum TaskStatus NewAdded = 0 InProgress = 1 HaltedWithError = 2 Complete = 3 End Enum Public Class TaskDetails Public SourceName As String Public TaskGUID As Guid Public Status As TaskStatus Public SubmitDate As DateTime Public RenderedFileUrl As String Public UserID As Integer Public CompleteDate As Date End Class Note You can add property procedures to the entity classes, but they will not be usable in an XML Web service client. When an XML Web service client creates a proxy class, the generated code includes all the required entity classes but the property procedures are converted to public member variables. However, property procedures can still prove useful when you're executing server-side code. The UserDB ClassThe UserDB class, shown in Listing 18-6, is the simplest part of the database component. It exposes a single AuthenticateUser method, which accepts login information and returns the corresponding numeric user ID if a matching record can be found. Listing 18-6 The UserDB classPublic Class UserDB Private ConnectionString As String Friend Sub New(ByVal connectionString As String) Me.ConnectionString = connectionString End Sub ' Verify that the specified information is correct. ' If it is, return the unique UserID. ' Otherwise, return 0. Public Function AuthenticateUser(ByVal userName As String, _ ByVal password As String) As Integer Dim con As New SqlConnection(ConnectionString) Dim cmd As New SqlCommand("GetUser", con) Dim r As SqlDataReader cmd.CommandType = CommandType.StoredProcedure Dim Param As SqlParameter Param = cmd.Parameters.Add( _ "@UserName", SqlDbType.NVarChar, 25) Param.Value = userName Param = cmd.Parameters.Add( _ "@Password", SqlDbType.NVarChar, 25) Param.Value = password Dim UserID As Integer = 0 Try con.Open() r = cmd.ExecuteReader() If r.Read() Then UserID = r("ID") Else ' No matching user. UserID = 0 End If r.Close() Finally con.Close() End Try Return UserID End Function End Class Notice that the database classes use exception handling to ensure that the database connection is always closed, even if an error occurs. However, they do not catch the exception or rethrow a higher-level exception (such as an ApplicationException). This is because this component is consumed by server-side code (such as the XML Web service) that might need to be informed of lower-level errors. However, this also means that the XML Web service should handle these exceptions. Otherwise, they will be propagated back to the client. The SessionDB ClassThe SessionDB class wraps the logic for creating, verifying, and removing sessions, in the structure shown here: Public Class SessionDB Private ConnectionString As String Friend Sub New(ByVal connectionString As String) Me.ConnectionString = connectionString End Sub Public Function CreateSession(ByVal userID As Integer) As Guid ' (Code omitted) End Function ' Removes the indicated session. Public Sub RemoveSession(ByVal ticket As Guid) ' (Code omitted) End Sub ' Returns a null reference if no matching session is found. Public Function GetSession(ByVal ticket As Guid, _ ByVal userID As Integer) As SessionDetails ' (Code omitted) End Function End Class The CreateSession generates a session record for the specified user and returns the GUID ticket. The RemoveSession method deletes a session record. Listings 18-7 and 18-8 show these methods. Listing 18-7 The CreateSession methodPublic Function CreateSession(ByVal userID As Integer) As Guid Dim con As New SqlConnection(ConnectionString) Dim cmd As New SqlCommand("AddSession", con) cmd.CommandType = CommandType.StoredProcedure Dim Param As SqlParameter Param = cmd.Parameters.Add("@UserID", SqlDbType.Int) Param.Value = userID ' Add the output parameter. Param = cmd.Parameters.Add("@GUID", SqlDbType.UniqueIdentifier) Param.Direction = ParameterDirection.Output Try con.Open() cmd.ExecuteNonQuery() Finally con.Close() End Try ' Return the unique identifier. Return Param.Value End Function Listing 18-8 The RemoveSession methodPublic Sub RemoveSession(ByVal ticket As Guid) Dim con As New SqlConnection(ConnectionString) Dim cmd As New SqlCommand("DeleteSession", con) cmd.CommandType = CommandType.StoredProcedure Dim Param As SqlParameter Param = cmd.Parameters.Add("@GUID", SqlDbType.UniqueIdentifier) Param.Value = ticket Try con.Open() cmd.ExecuteNonQuery() Finally con.Close() End Try End Sub Finally, the GetSession method, shown in Listing 18-9, retrieves the session that matches the specified GUID and user ID. By requiring these two criteria, the code ensures that a ticket can't be used for an ID other than the one that was used to log in. Listing 18-9 The GetSession method' Returns a null reference if no matching session is found. Public Function GetSession(ByVal ticket As Guid, _ ByVal userID As Integer) As SessionDetails Dim con As New SqlConnection(ConnectionString) Dim cmd As New SqlCommand("GetSession", con) Dim r As SqlDataReader cmd.CommandType = CommandType.StoredProcedure Dim Param As SqlParameter Param = cmd.Parameters.Add("@GUID", SqlDbType.UniqueIdentifier) Param.Value = ticket Param = cmd.Parameters.Add("@UserID", SqlDbType.Int) Param.Value = userID Dim Session As SessionDetails = Nothing Try con.Open() r = cmd.ExecuteReader If r.Read() Then Session = New SessionDetails() Session.CreateDate = r("CreateDate") Session.UserID = userID Session.Ticket = ticket End If Finally con.Close() End Try Return Session End Function The TaskDB ClassThe TaskDB class contains five methods, which allow tasks to be retrieved and updated in different ways. It is possible to shorten the TaskDB class by consolidating some methods for example, by replacing UpdateTaskStatus and CompleteTask with a more generic UpdateTask method. However, more specific, limited stored procedures can generally be optimized more efficiently by the SQL Server engine. In addition, using carefully targeted methods helps focus the application code, preventing it from being used for tasks that haven't been anticipated beforehand (and therefore not optimized or tested for security). The structure for the TaskDB class is shown here: Public Class TaskDB Private ConnectionString As String Friend Sub New(ByVal connectionString As String) Me.ConnectionString = connectionString End Sub Public Function GetTasks(ByVal userID As Integer, _ ByVal completedOnly As Boolean) As DataSet ' (Code omitted.) End Function Public Function GetAvailableTasks() As TaskDetails() ' (Code omitted.) End Function Public Function AddTask(ByVal task As TaskDetails) As Guid ' (Code omitted.) End Function Public Sub UpdateTaskStatus(ByVal taskGuid As Guid, _ ByVal status As TaskStatus) ' (Code omitted.) End Sub Public Sub CompleteTask(ByVal taskGuid As Guid, _ ByVal renderedFileUrl As String) ' (Code omitted.) End Sub End Class Note Tasks are identified using a statistically unique GUID. Alternatively, you can use an autoincrementing identity column for the same purpose. However, autoincrementing numbers are "guessable"; for example, if you submit a task that is assigned the number 402, it's quite likely that you might find another user's task with an ID such as 400 or 401. A GUID solves this problem because each value is generated independently. Knowing one task GUID can't help a user guess another task's GUID. Listings 18-10 and 18-11 show the GetTasks method and the GetAvailableTasks method, respectively. Listing 18-10 The GetTasks methodPublic Function GetTasks(ByVal userID As Integer, _ ByVal completedOnly As Boolean) As DataSet Dim con As New SqlConnection(ConnectionString) Dim cmd As New SqlCommand("", con) Dim Adapter As New SqlDataAdapter(cmd) Dim ds As New DataSet() If completedOnly Then cmd.CommandText = "GetCompletedTasksByUser" Else cmd.CommandText = "GetTasksByUser" End If cmd.CommandType = CommandType.StoredProcedure Dim Param As SqlParameter Param = cmd.Parameters.Add("@UserID", SqlDbType.Int) Param.Value = userID Dim Tasks As New ArrayList() Try con.Open() Adapter.Fill(ds, "Tasks") Finally con.Close() End Try Return ds End Function Listing 18-11 The GetAvailableTasks methodPublic Function GetAvailableTasks() As TaskDetails() Dim con As New SqlConnection(ConnectionString) Dim cmd As New SqlCommand("GetAvailableTasks", con) Dim r As SqlDataReader Dim Tasks As New ArrayList() Try con.Open() r = cmd.ExecuteReader() Do While r.Read() Dim Task As New TaskDetails() Task.TaskGUID = r("ID") Task.SourceName = r("SourceName") Task.Status = r("State") Task.SubmitDate = r("SubmitDate") ' Here we use ToString() to defend against a null value, ' which would otherwise cause an exception. Task.RenderedFileUrl = r("RenderedFileURL").ToString() Tasks.Add(Task) Loop Finally con.Close() End Try Return Tasks.ToArray(GetType(TaskDetails)) End Function One interesting fact about the TaskDB class is that the GetTasks method returns a DataSet, whereas the GetAvailableTasks method returns an array of TaskDetails objects. Generally, the second option is better for encapsulation because it hides the database details from the client. However, the second option can't be used in conjunction with client-side data binding because the TaskDetails class doesn't provide property procedures. In fact, even if you add property procedures to the TaskDetails class, this information will not be added to the automatically generated proxy class that the client uses, and data binding still won't be allowed. The only workaround is to either create a client that doesn't use data binding or return a DataSet rather than an array of custom objects. In the interest of simplifying development, the TaskDB class adopts the second approach. The AddTask method (shown in Listing 18-12) is quite straightforward. It just calls the AddTask stored procedure and returns the generated GUID. This code is typical of a multitier distributed application; if all the components of your system are properly isolated, you'll find that their code is straightforward, logical, and unsurprising. Listing 18-12 The AddTask methodPublic Function AddTask(ByVal task As TaskDetails) As Guid Dim con As New SqlConnection(ConnectionString) Dim cmd As New SqlCommand("AddTask", con) cmd.CommandType = CommandType.StoredProcedure Dim Param As SqlParameter Param = cmd.Parameters.Add("@UserID", SqlDbType.Int) Param.Value = task.UserID Param = cmd.Parameters.Add("@Status", SqlDbType.SmallInt) Param.Value = task.Status Param = cmd.Parameters.Add("@SourceName", _ SqlDbType.NVarChar, 50) Param.Value = task.SourceName ' Add the output parameter. Param = cmd.Parameters.Add("@GUID", SqlDbType.UniqueIdentifier) Param.Direction = ParameterDirection.Output Try con.Open() cmd.ExecuteNonQuery() Finally con.Close() End Try ' Return the unique identifier. Return Param.Value End Function Finally, the TaskDB class provides two methods for updating database information. The UpdateTaskStatus method (shown in Listing 18-13) allows the internal processor to modify the task status when it is placed in progress or when an error occurs. When the task completes, the CompleteTask method (shown in Listing 18-14) modifies the status accordingly and sets the corresponding file path for the RenderedFileUrl. Listing 18-13 The UpdateTaskStatus methodPublic Sub UpdateTaskStatus(ByVal taskGuid As Guid, _ ByVal status As TaskStatus) Dim con As New SqlConnection(ConnectionString) Dim cmd As New SqlCommand("UpdateTaskStatus", con) cmd.CommandType = CommandType.StoredProcedure Dim Param As SqlParameter Param = cmd.Parameters.Add("@GUID", SqlDbType.UniqueIdentifier) Param.Value = taskGuid Param = cmd.Parameters.Add("@Status", SqlDbType.SmallInt) Param.Value = status Try con.Open() cmd.ExecuteNonQuery() Finally con.Close() End Try End Sub Listing 18-14 The CompleteTask methodPublic Sub CompleteTask(ByVal taskGuid As Guid, _ ByVal renderedFileUrl As String) Dim con As New SqlConnection(ConnectionString) Dim cmd As New SqlCommand("CompleteTask", con) cmd.CommandType = CommandType.StoredProcedure Dim Param As SqlParameter Param = cmd.Parameters.Add("@GUID", SqlDbType.UniqueIdentifier) Param.Value = taskGuid Param = cmd.Parameters.Add("@Status", SqlDbType.SmallInt) Param.Value = TaskStatus.Complete Param = cmd.Parameters.Add("@RenderedFileURL", _ SqlDbType.NVarChar, 150) Param.Value = renderedFileUrl Try con.Open() cmd.ExecuteNonQuery() Finally con.Close() End Try End Sub The SuperComputeTables ClassThe SuperComputeTables class provides a gateway to the other database classes. It accepts a connection string argument and passes it to a new instance of the UserDB, SessionDB, and TaskDB classes. It also retrieves the connection string information. This approach (shown in Listing 18-15) is the same pattern used in the Chapter 16 and Chapter 17 case studies. Listing 18-15 Composition with SuperComputeTablesPublic Class SuperComputeTables Public Users As UserDB Public Sessions As SessionDB Public Tasks As TaskDB Public Sub New() ' Retrieve the connection string. Dim Connection As String = ConfigurationSettings.AppSettings( _ "SuperComputeConnection") ' Create the table classes. Users = New UserDB(Connection) Sessions = New SessionDB(Connection) Tasks = New TaskDB(Connection) End Sub End Class |