Information-Rich Programming With F# 3.0

Modern functional programming languages such as F# reduce development time and make it easy to perform rapid prototyping for complex algorithms. One of the most interesting features of F# 3.0, which is now a first-class citizen in Visual Studio 2012, is its ability to do algorithmic analysis on large data sets. In particular, it offers a uniform approach for continuously changing data sources, which is termed Information Rich Programming. In this first article in a series dedicated to Information Rich Programming with F# 3.0, I'll provide an overview of the new F# Type Providers and how they can boost your productivity and solve common problems when accessing continuously changing data sources and Internet-scale information sources.

IntelliSense and a SQL Server Database

I'll start with a very simple example that enables you to understand the productivity boost that you can expect with the new type providers and learn their basic features. Even if you don't have previous experience with either F# or functional programming languages, you will be able to see why type providers are one of the most important features in F# 3.0.

Figure 1 shows a schema with the entities and the relationships of a Retrogames database that I've used in many data-oriented examples in the past. The database has the following entities:

• Game
• GameCategory
• Gender
• Player
• PlayerScore

Figure 1: Entities and their relationships for the Retrogames SQL Server database.

Each game must have a category. Each time a game ends for a player, a service inserts the score in the PlayerScore table. Each player must have a gender (male or female) defined in the Gender table). I'll use Retrogames as the database name. You can run the following script in SQL Server 2008 or 2012 in order to create the database structure for this example:

CREATE TABLE dbo.GameCategory
(
	GameCategoryId int NOT NULL,
	Name varchar(50) NOT NULL,
	CONSTRAINT PK_GameCategory PRIMARY KEY CLUSTERED (GameCategoryId ASC)
)
GO

CREATE UNIQUE INDEX IX_GameCategory ON dbo.GameCategory
(
	Name ASC
)
GO

CREATE TABLE dbo.Gender
(
	GenderId int NOT NULL,
	Description varchar(20) NOT NULL,
	CONSTRAINT PK_Genre PRIMARY KEY CLUSTERED (GenderId ASC)
)
GO

CREATE UNIQUE INDEX IX_Genre ON dbo.Gender
(
	Description ASC
)
GO

CREATE TABLE dbo.Game
(
	GameId int NOT NULL,
	Name varchar(50) NOT NULL,
	ReleaseDate date NOT NULL,
	GameCategoryId int NOT NULL,
	Played bit NOT NULL,
	CONSTRAINT PK_games PRIMARY KEY CLUSTERED (GameId ASC)
)
GO

CREATE UNIQUE INDEX IX_games ON dbo.Game
(
	Name ASC
)
GO

CREATE INDEX IX_games_1 ON dbo.Game
(
	ReleaseDate ASC
)
GO

CREATE INDEX IX_Game ON dbo.Game
(
	Played ASC
)
GO

ALTER TABLE dbo.Game ADD 
	CONSTRAINT FK_Game_GameCategory FOREIGN KEY (GameCategoryId)
		REFERENCES dbo.GameCategory(GameCategoryId)
		ON DELETE CASCADE
GO

CREATE TABLE dbo.Player
(
	PlayerId int NOT NULL,
	Name varchar(50) NOT NULL,
	GenderId int NOT NULL,
	CONSTRAINT PK_Player PRIMARY KEY CLUSTERED (PlayerId ASC)
)
GO

CREATE UNIQUE INDEX IX_Player ON dbo.Player
(
	Name ASC
)
GO

ALTER TABLE dbo.Player ADD 
	CONSTRAINT FK_Player_Genre FOREIGN KEY (GenderId)
		REFERENCES dbo.Gender(GenderId)
GO

CREATE TABLE dbo.PlayerScore
(
	PlayerScoreId int NOT NULL,
	PlayerId int NOT NULL,
	GameId int NOT NULL,
	Score bigint NOT NULL,
	ScoreDate date NOT NULL,
	CONSTRAINT PK_PlayerScore PRIMARY KEY CLUSTERED (PlayerScoreId ASC)
)
GO

ALTER TABLE dbo.PlayerScore ADD 
	CONSTRAINT FK_PlayerScore_Player FOREIGN KEY (PlayerId)
		REFERENCES dbo.Player(PlayerId)
GO


ALTER TABLE dbo.PlayerScore ADD 
	CONSTRAINT FK_PlayerScore_Game FOREIGN KEY (GameId)
		REFERENCES dbo.Game(GameId)
GO

If you use Server Explorer in Visual Studio 2012 (known as Database Explorer in the Express edition), you can easily establish a connection to the database and navigate through the database structure. It is easy to discover the tables and their columns (see Figure 2), even with the horrible monochrome icons that Visual Studio 2012 brought with its UI (colors are so useful for developers and architects). Of course, you always have the diagrams, but my focus is on how simple it is to review the schema in real-time in the IDE.

Figure 2: Server Explorer allowing you to navigate through the database structure.

If you use Microsoft SQL Server Management Studio to build queries and you have IntelliSense enabled, you can benefit for auto-completion. I must say that even the simplest IntelliSense included by default in Microsoft SQL Server Management Studio makes me more productive and reduces my error rate whenever I write a SQL query, because I don't make mistakes with either table or column names, and I reduce my memory consumption. There are more powerful tools that boost productivity when you write too many queries per hour, but that isn't the main topic for this article.

Thus, developers and architects are already used to taking advantage of IntelliSense when they write queries against a SQL Server database. Of course, the database schema must be kept up-to-date. However, you don't have to remember the exact field names because IntelliSense provides them to you. For example, if you need to calculate the maximum score for year 2012, with the help of IntelliSense, you don't need to remember either table or column names to generate the following query (see Figure 3).

SELECT MAX(P.Score)
FROM PlayerScore P
WHERE P.ScoreDate BETWEEN '2012-01-01' AND '2011-12-31'
GO

Figure 3: SQL Server Management Studio auto-completion features in action.