The physical grid NGrid.Superfluid includes a custom disk cache management system. Such system relies on two components: a set of heuristics to decide what should be put on disk, and a persistent (aka disk-based) hashtable. In this post, I will discuss on possible persistent hashtable implementation via SQL Server.
Note that the initial release of NGrid.Superfluid was limited to Berkeley DB. The incoming release will include several persistent hashtable implementations based on various external tools (Berkeley DB, .Net Firebird, Microsoft SQL Server) in order to fit the various platforms.
The most natural choice to implement a persitent hashtable in .Net is to wrap the Berkeley DB because Berkeley DB is basically a persistent hashtable by design whereas the other tools are complete relational databases.
Nevertheless, in this post, I present the SQL Server-based persistent hashtable because it’s certainly the most simple and neat implementation. It’s also a nice illustration of basic SQL syntax.
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace NGrid.Superfluid
{
public class SqlCacheTable : IDisposable
{
bool isDisposed;
SqlConnection connection;
IFormatter formatter;
public SqlCacheTable(string connectionString)
{
connection = new SqlConnection(connectionString);
connection.Open();
InitializeTable();
InitializeFormatter(); // snipped
}
private void InitializeTable()
{
// Table creation
string createTable =
"CREATE TABLE SUPERFLUID "
+ "(ID UNIQUEIDENTIFIER, DATA IMAGE);";
SqlCommand command =
new SqlCommand(createTable, connection);
command.ExecuteNonQuery();
}
public void Dispose()
{
if (!isDisposed)
{
isDisposed = true;
string dropTable = "DROP TABLE SUPERFLUID;";
SqlCommand command =
new SqlCommand(dropTable, connection);
command.ExecuteNonQuery();
connection.Dispose();
}
}
The constructor of SqlCacheTable assumes implicitely that a SQL database already exists. Based on the provided connection string, we connect to this database and, in the method InitializeTable we create a table SUPERFLUID with two columns named ID and DATA. Note that the .Net corresponding type equivalent to SQL_UNIQUEINDENTIFIER is System.Guid, similarly SQL_IMAGE is associated to byte[]. At disposal, the table SUPERFLUID is deleted.
The table creation/deletion behavior fits the disk cache requirement of NGrid.Superfluid. If the objective was to implement of persistent hashtable, such behavior would requires to be adjusted.
public void Add(Guid key, object value)
{
MemoryStream stream = new MemoryStream();
formatter.Serialize(stream, value);
string insertObject =
@"INSERT INTO SUPERFLUID VALUES (@Id, @Data);";
SqlCommand command =
new SqlCommand(insertObject, connection);
command.Parameters.Add(
@"@Id", SqlDbType.UniqueIdentifier).Value = key;
command.Parameters.Add(
@"@Data", SqlDbType.Image).Value = stream.ToArray();
command.ExecuteNonQuery();
}
public object GetItem(Guid key)
{
string selectObject =
"SELECT DATA FROM SUPERFLUID WHERE ID = @Id;";
SqlCommand command =
new SqlCommand(selectObject, connection);
command.Parameters.AddWithValue("@Id", key);
MemoryStream stream = null;
using (SqlDataReader reader = command.ExecuteReader())
{
if (!reader.Read())
throw new InvalidOperationException(
"#E00 Key no present in DB.");
byte[] buffer = (byte[])reader.GetValue(0);
stream = new MemoryStream(buffer);
}
return formatter.Deserialize(stream);
}
public void Remove(Guid key)
{
string deleteObject =
"DELETE FROM SUPERFLUID WHERE ID = @Id;";
SqlCommand command =
new SqlCommand(deleteObject, connection);
command.Parameters.AddWithValue("@Id", key);
command.ExecuteNonQuery();
}
}
}
The three methods Add, GetItem and Remove illustrate basic SQL commands. Note that the MemoryStream is used as an intermediate between the original object and its byte[] serialized version.
As a final word, the .Net ObjectSpaces technology, whenever available (not in VS2005 anyway), might provide an even simpler design.
