Creating the Database

The registration database consists of three tables, as shown in Figure 8-4. These tables include the following:

Figure 8-4: The registration database

1. The Peers table lists currently connected peers, each of which is assigned a unique GUID. The peer-connectivity information includes the numeric IP address (stored as a string in dotted notation) and port number. The Peers table also includes a LastUpdate time, which allows an expiration policy to be used to remove old peer registration records.

2. The Files table lists shared files, the peer that's sharing them, and the date stamp on the file. Each file has a unique GUID, thereby ensuring that they can be tracked individually.

3. The Keywords table lists a single-word descriptor for a file. You'll notice that the Keywords table is linked to both the Files table and the Peers table. This makes it easier to delete the keywords related to a peer if the peer registration expires, without having to retrieve a list of shared files.

Figure 8-5 shows the sample data that you would expect in the registration database after a single client has connected and registered two shared files (in this case, recordings of two classical compositions by Debussy).

Figure 8-5: Sample registration data

All GUID values are generated by the peer and submitted to the server. This allows the peer to keep track of its shared files and quickly validate download requests, as you'll see in the next chapter.

Stored Procedures

The next step is to define a set of stored procedures that encapsulate some of the most common database tasks.

The AddPeer stored procedure inserts a new peer registration record in the database. RefreshPeer updates the LastUpdated field in the peer record. Every peer must call this method periodically to prevent their registration record from expiring.

CREATE Procedure AddPeer
(
    @ID uniqueidentifier,
    @IP nvarchar(15),
    @Port smallint
)
AS
INSERT INTO Peers
(
    ID, IP, Port, LastUpdate
)
VALUES
(
    @ID, @IP, @Port, GETDATE()
)
GO


CREATE Procedure RefreshPeer
(
    @ID uniqueidentifier
)
AS
UPDATE Peers SET LastUpdate=GETDATE() WHERE ID=@ID
GO

Two more stored procedures, AddFile and AddKeyword, allow new catalog information to be added to the database.

CREATE Procedure AddFile
(
@ID           uniqueidentifier,
@PeerID       uniqueidentifier,
@FileName     nvarchar(50),
@FileCreated  datetime
)
AS
INSERT INTO Files
(
    ID, PeerID, FileName, FileCreated
)
VALUES
(
    @ID, @PeerID, @FileName, @FileCreated
)
GO


CREATE Procedure AddKeyword
(
    @FileID uniqueidentifier,
    @PeerID uniqueidentifier,
    @Keyword nvarchar(50)
)
AS
INSERT INTO Keywords
(
    FileID, PeerID, Keyword
)
VALUES
(
    @FileID, @PeerID, @Keyword
)
GO

Finally, a DeletePeersAndFiles stored procedure handles the unregistration process, removing related records from the Files, Peers, and Keywords tables. The DeleteFiles stored procedure provides a similar function, but leaves the peer record intact. Its primary use is when updating the catalog.

CREATE Procedure DeletePeerAndFiles
(
    @ID uniqueidentifier
)
AS
DELETE FROM Files WHERE PeerID = @ID
DELETE FROM Peers WHERE ID = @ID
DELETE FROM Keywords WHERE PeerID = @ID
GO


CREATE Procedure DeleteFiles
(
    @ID uniqueidentifier
)
AS
DELETE FROM Files WHERE PeerID = @ID
DELETE FROM Keywords WHERE PeerID = @ID
GO

The database doesn't include a stored procedure for performing queries, because this step is easier to accomplish with a dynamically generated SQL statement that uses a variable number of WHERE clauses.

Creating the Database Class

The next step is to create a class that encapsulates all the data-access logic. The web service will then make use of this class to perform database tasks, rather than connect with the database directly. This separation makes it easier to debug, enhance, and optimize the data-access logic.

For maximum reusability, the data-access code could be implemented as a separate assembly. In our example, however, it's a part of the web service project.

The database code includes a Peer and SharedFile class, which models a row from the Peers and Files tables, respectively. The SharedFile class also includes information about the related peer.

Public Class Peer
    Public Guid As Guid
    Public IP As String
    Public Port As Integer
End Class

Public Class SharedFile
    Public Guid As Guid
    Public FileName As String
    Public FileCreated As Date
    Public Peer As New Peer()
    Public Keywords() As String
End Class

Neither of these classes uses full property procedures, because they aren't fully supported in a web service. If you were to add property procedure code, it might be used on the server side. However, it would be ignored on the client side, thus limiting its usefulness.

The database code could be separated into multiple classes (for example, a PeersDB, FilesDB, and KeywordsDB database). However, because there's a relatively small set of tasks that will be performed with the registration database, you can implement all methods in a single class without any confusion. Here's the basic framework for the class:

Public Class P2PDatabase

    Private ConnectionString As String

    Public Sub New()
        ConnectionString = ConfigurationSettings.AppSettings("DBConnection")
    End Sub

    Public Sub AddPeer(ByVal peer As Peer)
        ' (Code omitted.)
    End Sub

    Public Sub RefreshPeer(ByVal peer As Peer)
        ' (Code omitted.)
    End Sub

    Public Sub DeletePeerAndFiles(ByVal peer As Peer)
        ' (Code omitted.)
    End Sub

    Public Sub AddFileInfo(ByVal files() As SharedFile, ByVal peer As Peer)
        ' (Code omitted.)
    End Sub

    Public Function GetFileInfo(ByVal keywords() As String) As SharedFile()
        ' (Code omitted.)
    End Function
End Class

When a P2PDatabase instance is created, the connection string is retrieved from a configuration file. This will be the configuration associated with the application that's using the P2PDatabase class. In our example, this is the web.config file used by the web service.

<?xml version="1.0" encoding="utf-8" ?>

<configuration>
  <appSettings>
    <add key="DBConnection"
        value="Data Source=localhost;Initial Catalog=P2P;user ID=sa" />
    </appSettings>
  <system.web>
    <!-- Other settings omitted. -->
  </system.web>
</configuration>

The actual database code is quite straightforward. The basic pattern is to create a command for the corresponding stored procedure, add the required information as parameters, and execute the command directly. For example, here's the code used to register, update, and remove peer information:

Public Sub AddPeer(ByVal peer As Peer)

    ' Define command and connection.
    Dim con As New SqlConnection(ConnectionString)
    Dim cmd As New SqlCommand("AddPeer", con)
    cmd.CommandType = CommandType.StoredProcedure

    ' Add parameters.
    Dim param As SqlParameter
    param = cmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier)
    param.Value = peer.Guid
    param = cmd.Parameters.Add("@IP", SqlDbType.NVarChar, 15)
    param.Value = peer.IP
    param = cmd.Parameters.Add("@Port", SqlDbType.SmallInt)
    param.Value = peer.Port

    Try
        con.Open()
        cmd.ExecuteNonQuery()
    Finally
        con.Close()
    End Try

End Sub

Public Sub RefreshPeer(ByVal peer As Peer)

    ' Define command and connection.
    Dim con As New SqlConnection(ConnectionString)
    Dim cmd As New SqlCommand("RefreshPeer", con)
    cmd.CommandType = CommandType.StoredProcedure
    ' Add parameters.
    Dim param As SqlParameter
    param = cmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier)
    param.Value = peer.Guid

    Try
        con.Open()
        cmd.ExecuteNonQuery()
    Finally
        con.Close()
    End Try

End Sub

Public Sub DeletePeerAndFiles(ByVal peer As Peer)

    ' Define command and connection.
    Dim con As New SqlConnection(ConnectionString)
    Dim cmd As New SqlCommand("DeletePeerAndFiles", con)
    cmd.CommandType = CommandType.StoredProcedure

    ' Add parameters.
    Dim param As SqlParameter
    param = cmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier)
    param.Value = peer.Guid

    Try
        con.Open()
        cmd.ExecuteNonQuery()
    Finally
        con.Close()
    End Try

End Sub

Note that if an error occurs, the connection is closed, but the error isn't handled. Instead, it's allowed to propagate back to the caller (in this case, the web service), which will handle it accordingly. Another option would be to catch all errors and throw a higher-level exception, such as an ApplicationException, with the original exception wrapped inside.

The code for the AddFileInfo() method is lengthier because it adds multiple records: one new file record, and one keyword record for each keyword string in the File.Keywords array. All the work is performed with the same open connection, thereby reducing the overhead required for the whole process. The AddFileInfo() method also clears the current registration information before it begins by calling the DeleteFiles stored procedure. This ensures that the same peer can't accidentally register the same file twice.

Public Sub AddFileInfo(ByVal files() As SharedFile, ByVal peer As Peer)

    ' Define commands and connection.
    Dim con As New SqlConnection(ConnectionString)
    Dim cmdDelete As New SqlCommand("DeleteFiles", con)
    cmdDelete.CommandType = CommandType.StoredProcedure

    Dim cmdFile As New SqlCommand("AddFile", con)
    cmdFile.CommandType = CommandType.StoredProcedure

    Dim cmdKeyword As New SqlCommand("AddKeyword", con)
    cmdKeyword.CommandType = CommandType.StoredProcedure

    Dim param As SqlParameter

    Try
        con.Open()

        ' Delete current registration information.
        param = cmdDelete.Parameters.Add("@ID", SqlDbType.UniqueIdentifier)
        param.Value = peer.Guid
        cmdDelete.ExecuteNonQuery()

        Dim File As SharedFile
        For Each File In files

            ' Add parameters.
            cmdFile.Parameters.Clear()
            param = cmdFile.Parameters.Add("@ID", SqlDbType.UniqueIdentifier)
            param.Value = File.Guid
            param = cmdFile.Parameters.Add("@PeerID", SqlDbType.UniqueIdentifier)
            param.Value = peer.Guid
            param = cmdFile.Parameters.Add("@FileName", SqlDbType.NVarChar, 50)
            param.Value = File.FileName
            param = cmdFile.Parameters.Add("@FileCreated", SqlDbType.DateTime)
            param.Value = File.FileCreated

            cmdFile.ExecuteNonQuery()

            ' Add keywords for this file.
            ' Note that the lack of any keywords isn't considered
            ' to be an error condition (although it could be).
            Dim Keyword As String
            For Each Keyword In File.Keywords
                cmdKeyword.Parameters.Clear()
                param = cmdKeyword.Parameters.Add("@FileID", _
                        SqlDbType.UniqueIdentifier)
                param.Value = File.Guid
                param = cmdKeyword.Parameters.Add("@PeerID", _
                        SqlDbType.UniqueIdentifier)
                param.Value = peer.Guid
                param = cmdKeyword.Parameters.Add("@Keyword", _
                        SqlDbType.NVarChar, 50)
                param.Value = Keyword
                cmdKeyword.ExecuteNonQuery()
            Next
        Next

    Finally
        con.Close()
    End Try

End Sub

Finally, the GetFileInfo() method creates a dynamic SQL query based on a list of search keywords. The query joins the Files, Peers, and Keywords tables in order to retrieve all the required peer-connectivity and file information. For each keyword, a WHERE clause is appended to the SQL expression. For maximum performance, this process is performed with a StringBuilder object instead of through ordinary string concatenation.

Public Function GetFileInfo(ByVal keywords() As String) As SharedFile()

    ' Build dynamic query string.
    Dim DynamicSQL As New System.Text.StringBuilder( _
      "SELECT DISTINCT Files.ID AS FileID, Peers.ID AS PeerID, " & _
      "FileName, FileCreated, IP, Port " & _
      "FROM Files, Keywords, Peers " & _
      "WHERE Files.ID = keywords.FileID AND Files.PeerID = Peers.ID AND ")

    Dim i As Integer
    For i = 1 To keywords.Length
        DynamicSQL.Append("Keyword LIKE '%" + keywords(i - 1) + "%' ")
        If Not (i = keywords.Length) Then DynamicSQL.Append("OR ")
    Next

    ' Define command and connection.
    Dim con As New SqlConnection(ConnectionString)
    Dim cmd As New SqlCommand(DynamicSQL.ToString(), con)
    Dim r As SqlDataReader
    Dim Files As New ArrayList()

    Try
        con.Open()
        r = cmd.ExecuteReader()
        Do While (r.Read())
            Dim File As New SharedFile()
            File.Guid = r("FileID")
            File.FileName = r("FileName")
            File.FileCreated = r("FileCreated")
            File.Peer.IP = r("IP")
            File.Peer.Port = r("Port")
            File.Peer.Guid = r("PeerID")
            Files.Add(File)
        Loop
    Finally
        con.Close()
    End Try

    ' Convert the generic ArrayList to an array of SharedFile objects.
    Return CType(Files.ToArray(GetType(SharedFile)), SharedFile())

End Function

Results from the query are retrieved using a DataReader. Each time a matching file is found, a new SharedFile object is created and added to an ArrayList. Once all the matching files are found, the ArrayList is converted to a strongly typed SharedFile array, and returned.