The Extensible Storage Engine (ESE) that comes with Microsoft Windows is a data storage technology that is a good match for F# and domains like technical computing. ESE has a really impressive set of features [1] [2]. I like that it provides a “highly concurrent database access”, can scale from a 1 MB database to a 16 TB one, and the types map well to .NET.
The ESE native library can be found on your Windows systems at %systemroot%\system32\esent.dll. A .NET assembly, Esent.Interop.dll, wraps the native library to provides a managed interface. It was first released to CodePlex [3] on 2008-12-22 by Laurion Burchall [4] and is at version 1.5 release on 2009-09-12. I wrapped it and exposed the functions I needed for this blog in a F# module.
ESE is not a relational database management system, it is a “indexed sequential access method” (ISAM) [5] system. I like to think of it as storing sequences of records and that makes it a good match for F#. I created an F# record type for storing a Product based on the Product table from the Microsoft SQL Server sample database AdventureWorksLT2008. I made all the fields in the record be of the option type in order to make the code safer and more reusable.
type Product =
{
ID: Int32 option;
Name: String option;
Number: String option;
Color: String option;
StandardCost: Single option;
ListPrice: Single option;
Size: String option;
Weight: Single option;
CategoryID: Int32 option;
ModelID: Int32 option;
SellStartDate: DateTime option;
SellEndDate: DateTime option;
DiscontinuedDate: DateTime option;
ThumbNailPhoto: Byte[] option;
ThumbnailPhotoFileName: String option;
RowGuid: Guid option;
ModifiedDate: DateTime option;
}
With the help of a small MsSqlReader.fs module, the 255 products in the SQL Server database can be read into a lazy sequence of products records like so:
let readAll() =
seq {
use cmd = createCommand cn "select ProductID, Name, ProductNumber, Color, StandardCost, ListPrice, Size, Weight, ProductCategoryID, ProductModelID, SellStartDate, SellEndDate, DiscontinuedDate, ThumbNailPhoto, ThumbnailPhotoFileName, rowguid, ModifiedDate from SalesLT.Product"
use r = cmd.ExecuteReader()
while r.Read() do
yield
{
ID = readInt32 r 0;
Name = readString r 1;
Number = readString r 2;
Color = readNullableString r 3;
StandardCost = readNullableDecimalAsSingle r 4;
ListPrice = readNullableDecimalAsSingle r 5;
Size = readNullableString r 6;
Weight = readNullableDecimalAsSingle r 7;
CategoryID = readNullableInt32 r 8;
ModelID = readNullableInt32 r 9;
SellStartDate = readDateTime r 10;
SellEndDate = readNullableDateTime r 11;
DiscontinuedDate = readNullableDateTime r 12;
ThumbNailPhoto = readNullableBytes r 13;
ThumbnailPhotoFileName = readNullableString r 14;
RowGuid = readGuid r 15;
ModifiedDate = readDateTime r 16;
}
}
I create the schema for the ESE database file in the AdventureWorksEse class type:
type AdventureWorksEse(file) =
let fileExists = File.Exists file
let ese = getInstance file
do
ese.Parameters.Recovery <- false // useful to turn off during development
ese.Init()
// create table and columns for a new file
do
if fileExists <> true then
use sn = openSession ese
let db = createDatabase sn file
let t = createTable sn db Names.Product
addColumnInt32NotNull sn t Names.ID
addColumnTextUnicodeNotNull sn t Names.Name 100 // bytes, 50 Unicode characters
addColumnTextUnicode sn t Names.Number 50 // bytes, 25 Unicode characters
addColumnTextUnicode sn t Names.Color 30 // bytes, 15 Unicode characters
addColumnSingleNotNull sn t Names.StandardCost
addColumnSingleNotNull sn t Names.ListPrice
addColumnTextUnicode sn t Names.Size 10 // bytes, 5 Unicode characters
addColumnSingle sn t Names.Weight
addColumnInt32 sn t Names.CategoryID
addColumnInt32 sn t Names.ModelID
addColumnDateTimeNotNull sn t Names.SellStartDate
addColumnDateTime sn t Names.SellEndDate
addColumnDateTime sn t Names.DiscontinuedDate
addColumnBytes sn t Names.ThumbNailPhoto 8000 // SQL Server varbinary max
addColumnTextUnicode sn t Names.ThumbnailPhotoFileName 100 // bytes, 50 Unicode characters
addColumnGuidNotNull sn t Names.RowGuid
addColumnDateTimeNotNull sn t Names.ModifiedDate
createIndexPrimary sn t Names.ID
createIndexUnique sn t Names.Name
createIndexUnique sn t Names.Number
createIndexUnique sn t Names.RowGuid
member x.File with get() = file
member x.Ese with get() = ese
interface IDisposable with
member x.Dispose() =
ese.Dispose()
The ProductServiceMsEse class type can be used to insert or read products from the ESE database.
// wraps a ESE session, so create a new instance for each thread
// http://msdn.microsoft.com/en-us/library/aa964734(EXCHG.10).aspx
type ProductServiceMsEse(ese:AdventureWorksEse) =
let sn = openSession ese.Ese
let table =
let db =
attachDatabase sn ese.File
openDatabase sn ese.File
getTable sn db Names.Product
// get columns
let getColumn name =
MsEse.getColumn sn table name
let columnID = getColumn Names.ID
let columnName = getColumn Names.Name
let columnNumber = getColumn Names.Number
let columnColor = getColumn Names.Color
let columnStandardCost = getColumn Names.StandardCost
let columnListPrice = getColumn Names.ListPrice
let columnSize = getColumn Names.Size
let columnWeight = getColumn Names.Weight
let columnCategoryID = getColumn Names.CategoryID
let columnModelID = getColumn Names.ModelID
let columnSellStartDate = getColumn Names.SellStartDate
let columnSellEndDate = getColumn Names.SellEndDate
let columnDiscontinuedDate = getColumn Names.DiscontinuedDate
let columnThumbNailPhoto = getColumn Names.ThumbNailPhoto
let columnThumbnailPhotoFileName = getColumn Names.ThumbnailPhotoFileName
let columnRowGuid = getColumn Names.RowGuid
let columnModifiedDate = getColumn Names.ModifiedDate
let insert sn (p:Product) =
let t = table
let setColumns() =
writeInt32 sn t columnID p.ID
writeString sn t columnName p.Name
writeString sn t columnNumber p.Number
writeString sn t columnColor p.Color
writeSingle sn t columnStandardCost p.StandardCost
writeSingle sn t columnListPrice p.ListPrice
writeString sn t columnSize p.Size
writeSingle sn t columnWeight p.Weight
writeInt32 sn t columnCategoryID p.CategoryID
writeInt32 sn t columnModelID p.ModelID
writeDateTime sn t columnSellStartDate p.SellStartDate
writeDateTime sn t columnDiscontinuedDate p.DiscontinuedDate
writeBytes sn t columnThumbNailPhoto p.ThumbNailPhoto
writeString sn t columnThumbnailPhotoFileName p.ThumbnailPhotoFileName
writeGuid sn t columnRowGuid p.RowGuid
writeDateTime sn t columnModifiedDate p.ModifiedDate
MsEse.insert sn t setColumns
let read() =
let t = table
{
ID = readInt32 sn t columnID;
Name = readString sn t columnName;
Number = readString sn t columnNumber;
Color = readString sn t columnColor;
StandardCost = readSingle sn t columnStandardCost;
ListPrice = readSingle sn t columnListPrice;
Size = readString sn t columnSize;
Weight = readSingle sn t columnWeight;
CategoryID = readInt32 sn t columnCategoryID;
ModelID = readInt32 sn t columnModelID;
SellStartDate = readDateTime sn t columnSellStartDate;
SellEndDate = readDateTime sn t columnSellEndDate;
DiscontinuedDate = readDateTime sn t columnDiscontinuedDate;
ThumbNailPhoto = readBytes sn t columnThumbNailPhoto;
ThumbnailPhotoFileName = readString sn t columnThumbNailPhoto;
RowGuid = readGuid sn t columnRowGuid;
ModifiedDate = readDateTime sn t columnModifiedDate;
}
member x.Insert product =
let fn() = insert sn product
transact sn fn
member x.InsertAll products =
let fn() = products |> Seq.iter (insert sn)
transact sn fn
member x.ReadAll() =
setCurrentIndexToPrimary sn table
readAll sn table read
member x.ReadAllByName() =
setCurrentIndex sn table Names.Name
readAll sn table read
member x.ReadAllByNumber() =
setCurrentIndex sn table Names.Number
readAll sn table read
interface IDisposable with
member x.Dispose() =
table.Dispose()
sn.Dispose()
The demo application runs this code:
/// Establishes a connection to SQL Server. Creates the ESE database file. Fills the ESE database.
let transferProducts() =
use ese = new AdventureWorksEse("AdventureWorks.db")
use svcEse = new ProductServiceMsEse(ese)
use cn = MsSqlReader.openConnection @"server=.\sqlexpress; Integrated Security=true; Initial Catalog=AdventureWorksLT2008R2"
let svcSql = ProductServiceMsSql cn
svcSql.ReadAll() |> svcEse.InsertAll
/// print the first n products indexed by name
let printProducts n =
let print (p:Product) =
printfn "%s %s" p.Number.Value p.Name.Value
use ese = new AdventureWorksEse("AdventureWorks.db")
use svc = new ProductServiceMsEse(ese)
svc.ReadAllByName()
|> Seq.take n
|> Seq.iter print
let main() =
transferProducts()
printProducts 10
Console.ReadKey false |> ignore
You can browse the source code or check out the source and build the program using Visual Studio 2010 (beta 2). A couple of things like the GUID column type require Windows Vista or above. Modifying it to run on Windows XP shouldn’t be very difficult.
svn checkout http://ctaggart.googlecode.com/svn/tags/blog-2010/2010-02-01/fese/
References & Links