The first step is to design the back-end database. This database contains two tables (as shown in Figure 10-1):
The Peers table stores a list of users, each of which has a unique e-mail address.
The Sessions table stores a list of currently active users, with the information needed to connect to them.
Strictly speaking, you only require one table to store the list of currently connected peers. However, by creating two tables you gain the ability to define a list of allowed users, and validate them at the server before allowing them to join the peer community. In addition, you might want to add more tables to define "buddy lists"—groups of contacts that determine who a peer can see online and interact with.
The Sessions table doesn't directly store an IP address and port number— instead, it stores a serialized ObjRef, which is Remoting's network pointer. When serialized, the ObjRef typically takes about 1,008 bytes. The Sessions table provides space for up to 1,500 bytes to be safe, and the code verifies that this constraint is met when serializing the ObjRef.
The database includes six stored procedures, as described here:
AddPeer creates a peer record for a newly registered user.
CreateSession inserts a new session record when an existing user logs in.
DeleteSession removes a session record.
GetPeers retrieves a list of all the peers who are currently logged in. In a large system, this would be adapted so that it retrieved a list of logged-in users according to a contact list.
GetPeerAndSessionInfo retrieves information about the peer, and the current contact information if the peer is logged in. This could be split into two stored procedures, but for simplicity's sake it's handled in one.
RefreshSession updates the expiration date on the current session record. Peer sessions that haven't been updated within three minutes will be ignored (and optionally, can be removed).
The stored procedure code is similar to what you saw in Chapter 8. For example, AddPeer wraps a simple SQL Insert statement:
CREATE Procedure AddPeer ( @EmailAddress nvarchar(50) ) AS INSERT INTO Peers ( EmailAddress ) VALUES ( @EmailAddress ) GO
The CreateSession stored procedure is more sophisticated. Before adding the session record, it removes any existing session records with the same e-mail address. It also performs a lookup to map the supplied peer e-mail address to the unique identity number the database uses in the Sessions table.
CREATE Procedure CreateSession ( @ID uniqueidentifier, @EmailAddress nvarchar(50), @ObjRef varbinary(1500) ) AS DECLARE @PeerID int SELECT @PeerID = ID FROM Peers WHERE EmailAddress = @EmailAddress DELETE FROM Sessions WHERE PeerID=@PeerID INSERT INTO Sessions (ID, PeerID, LastUpdate, ObjRef) VALUES (@ID, @PeerID, GETDATE(), @ObjRef) GO
Note that the unique identifier is generated by the server rather than the database engine, and as such it doesn't need to be returned using a parameter.
The RefreshSession stored procedure simply updates the LastUpdate field.
CREATE Procedure RefreshSession ( @ID uniqueidentifier ) AS UPDATE Sessions SET LastUpdate=GETDATE() WHERE [ID]=@ID GO
The DeleteSession stored procedure removes the session based on its unique identifier.
CREATE Procedure DeleteSession ( @ID uniqueidentifier ) AS DELETE FROM Sessions WHERE ID = @ID GO
The GetPeers method returns the e-mail addresses for all the currently logged-on users by joining the Sessions and Peers tables. At the same time, any entry that hasn't been updated in more than three minutes is ignored.
CREATE PROCEDURE GetPeers AS SELECT EmailAddress FROM Sessions INNER JOIN Peers ON Peers.ID = PeerID WHERE DATEDIFF(MINUTE, LastUpdate, GETDATE()) < 3 GO
Optionally, you could remove old sessions records, either by using a separate long-running application that periodically scans the database, or by adding the necessary code to a frequently invoked stored procedure such as CreateSession or DeleteSession. However, this additional step probably isn't necessary. The CreateSession stored procedure code already ensures that all of a user's old records are removed every time the user logs in.
Finally, the GetPeerAndSessionInfo stored procedure returns peer and session information. A left join is used to ensure that peer information is returned, even if the user isn't currently logged in and doesn't have a session record.
If the system is working correctly, this will return only a single record. But just to be defensive, this stored procedure returns only the first record that was most recently updated by using the TOP 1 and ORDER BY clauses.
As in Chapter 8, a class named P2PDatabase is used to wrap the stored procedure code with the required ADO.NET commands. Information about a peer and its current session can be packaged into a PeerInfo object, as shown here:
Public Class PeerInfo Public ID As Integer Public EmailAddress As String Public PublicKeyXml As String Public ObjRef() As Byte End Class
The P2PDatabase includes a method for each stored procedure. You won't see the full code here, but it's provided online with the Chapter 11 examples, and it's quite straightforward. However, there are two fine points worth identifying.
First of all, note how the CreateSession() method takes special care to validate that the ObjRef is less than the 1,500 bytes allocated for storage in the database. Because it's presumably impossible for an ObjRef to be larger, the code makes this check using a debug assertion. Alternatively, you might want to throw a custom error indicating the problem if the byte array is too large.
Public Function CreateSession(ByVal emailAddress As String, _ ByVal objRef() As Byte) As Guid ' Define command and connection. Dim SessionID As Guid = Guid.NewGuid() Dim con As New SqlConnection(ConnectionString) Dim cmd As New SqlCommand("CreateSession", con) cmd.CommandType = CommandType.StoredProcedure ' Add parameters. Dim param As SqlParameter param = cmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier) param.Value = SessionID param = cmd.Parameters.Add("@EmailAddress", SqlDbType.NVarChar, 300) param.Value = emailAddress Debug.Assert(objRef.Length < 1500) param = cmd.Parameters.Add("@ObjRef", SqlDbType.VarBinary, 1500) param.Value = objRef Try con.Open() cmd.ExecuteNonQuery() Finally con.Close() End Try Return SessionID End Function
The GetPeerInfo() method also requires special care. It calls the GetPeerAnd SessionInfo stored procedure, which may or may not return session information. To prevent a possible null reference exception, the code must check if session data is returned before trying to assign it to the properties of a PeerInfo object.
Public Function GetPeerInfo(ByVal email As String) As PeerInfo ' Define command and connection. Dim con As New SqlConnection(ConnectionString) Dim cmd As New SqlCommand("GetPeerAndSessionInfo", con) cmd.CommandType = CommandType.StoredProcedure ' Add parameters. Dim param As SqlParameter param = cmd.Parameters.Add("@EmailAddress", SqlDbType.VarChar, 50) param.Value = email Dim Peer As New PeerInfo() Try con.Open() Dim r As SqlDataReader = cmd.ExecuteReader() r.Read() Peer.EmailAddress = r("EmailAddress") Peer.ID = r("ID") If Not (r("ObjRef") Is DBNull.Value) Then Peer.ObjRef = r("ObjRef") End If Finally con.Close() End Try Return Peer End Function