The registration database stores a list of all the peers that are currently available and the information needed to connect to them. It also uses a basic cataloging system, whereby each peer uploads a catalog of available resources shortly after logging in. When a peer needs a specific resource, it calls a web-service method. The web service attempts to find peers that can provide the resource and returns a list of search matches with the required peer-connectivity information.
In this case, the resources are files that a peer is willing to exchange. The catalog stores file names, but that isn't enough. File names can be changed arbitrarily and have little consistency among users, so searching based on file names isn't a desirable option. Instead, file names are indexed using multiple content descriptors. In the case of an MP3 file, these content descriptors might include information such as the artist name, song title, and so on. The file-sharing application can use more than one possible method to retrieve this information, but the most likely choice is to retrieve it from the file. For example, MP3 files include a header that stores song data. A file-sharing application could use this information to create a list of keywords for a file, and submit that to the server. This is the approach taken in our sample registration database.
In order to index a file, a peer must understand the file format and know how to extract the required information. The server does not deal with the file data, and can't perform this task.
The registration database consists of three tables, as shown in Figure 8-4. These tables include the following:
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.
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.
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).
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.
If you want to test this database on your own system, you can use the SQL script that's included with the samples for this chapter. It automatically creates the database and the stored procedures described in the next section, provided you are using SQL Server 2000.
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.
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.
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
Even if you're new to ADO.NET coding, the previous code sample is fairly self-explanatory. There are numerous books dedicated to the basics of ADO.NET programming, including several titles from Apress.
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.
You might want to use the SQL statement SET ROWCOUNT before you execute the query. This way, you can limit the total number of requests and ensure that the discovery service won't be swamped by returning tens of thousands of results to a poorly worded query. For example, the SQL statement SET ROWCOUNT 100 caps search results to the first 100 rows that match the query.