In my daily work, I find myself writing a lot of one-off SQL scripts to handle various data issues. In particular, I've had to tweak existing sets of metadata with scripts that look something like the following:
DECLARE @SomeId INT
DECLARE @SomeOtherId INT
SELECT @SomeId = SomeTableId
FROM SomeTable
WHERE SomeName = 'Something'
SELECT @SomeOtherId = SomeOtherTableid
FROM SomeOtherTable
WHERE SomeOtherName = 'SomethingElse'
IF NOT EXISTS(
SELECT 1
FROM SomeMetadataTable
WHERE Name = 'MetadataName')
BEGIN
INSERT INTO SomeMetadataTable
(Name, Property1, Property2)
VALUES
('MetadataName', @SomeId, @SomeOtherId)
END
ELSE
BEGIN
UPDATE SomeMetadataTable
SET Property1 = @SomeId
, Property2 = @SomeOtherId
WHERE Name = 'MetadataName'
END
Just writing a few of these gets to be tedious, and master scripts to populate entire tables are large, difficult to maintain blobs of SQL.
At the end of last year, I nabbed myself some copies (digital and physical) of Ayende's excellent DSLs in Boo. Having thought before that I might use some of the tools in the SQL Server Modeling space to build a DSL to simplify the scripting of such metadata, I decided to see how easily something workable could be built with Boo. There were a few stumbling blocks along way, mostly regarding my inexperience with Boo and my inability to download and compile Rhino DSL (I'm not used to git, and a directoy with a space in the name wasted some of my time). Once I was past those problems, I found the going very easy.
I wrote this exploratory example before the NFL championship games this year, so I chose to create a language that might describe the attributes of a football team. Here is the final script I ended with:
define "New York Jets":
defense "Superb"
pass_offense "Game Management", with_qb_type("Rookie")
run_offense "Power"
define "Minnesota Vikings":
defense "Solid"
pass_offense "High-Powered", with_qb_type("Aging Veteran")
run_offense "Dynamic"
define "Indianapolis Colts":
defense "Speedy"
pass_offense "High-Octane", with_qb_type("Elite Veteran")
run_offense "Anemic"
define "New Orleans Saints":
defense "Opportunistic"
pass_offense "Explosive", with_qb_type("Rising Star")
run_offense "Serviceable"
The easiest way to define 'keywords' like define and defense is through meta methods. Meta methods process AST nodes and return AST nodes during compilation. The meta method for define looks like this:
[Meta]
public static def define(expression as StringLiteralExpression, block as BlockExpression):
return [|
_currentTeam.Name = $(expression.Value)
DefineTeam($block)
|]
I'm using quasi-quotation here, which basically means that Boo will turn that block of code that I'm returning into an appropriate AST node. So, in this case, when the compiler encounters 'define', it will take the following string and the block that follows that and transform it into a call that sets a property (Name) on a class variable (_currentTeam) with the string, followed by a call to a class method (DefineTeam) that takes the block as an argument. Yeahhhhhhhhhhh, that's confusing. It takes a little bit of time to get your head around exactly what is going on, but once you do, you can crank out meta methods like mad.
So where did that field and method come from? I'm using Ayende's Rhino DSL library to create an 'implicit base class' for the script. The meta methods ultimately call fields and methods that are defined on this base class from which the script inherits. In this case, I'm doing something that might be unorthodox or bad practice by combining all the team definitions together and storing them in a list. In most examples I've seen, the team definitions would be in four different script. For this spike, I went with something quick and dirty. The SQL I'm generating isn't being written to file anywhere or executed; I'm just kicking it out to the console. Here's the script's base class in its entirety:
import System
import Boo.Lang.Compiler.Ast
public abstract class TeamDefinitionScript:
protected currentNumber as int
public def constructor():
CurrentTeam = TeamModel()
[Property(Teams)]
protected _teams as List[of TeamModel] = List[of TeamModel]()
[Property(CurrentTeam)]
protected _currentTeam as TeamModel
abstract def Prepare():
pass
protected def DefineTeam(block as callable):
block()
Teams.Add(CurrentTeam)
currentNumber++
print "DECLARE @DefenseId${currentNumber} INT"
print "SELECT @DefenseId${currentNumber} = DefenseId FROM Defense WHERE DefenseTypeName = '${CurrentTeam.Defense}'"
print ""
print "DECLARE @RunOffenseId${currentNumber} INT"
print "SELECT @RunOffenseId${currentNumber} = RunOffenseId FROM RunOffense WHERE RunOffenseTypeName = '${CurrentTeam.RunOffense}'"
print ""
print "DECLARE @PassOffenseId${currentNumber} INT"
print "DECLARE @QbTypeId${currentNumber} INT"
print "SELECT @QbTypeId${currentNumber} = QbTypeId FROM QbType WHERE QbTypeName = '${CurrentTeam.QbType}'"
print "SELECT @PassOffenseId${currentNumber} = PassOffenseId FROM PassOffense WHERE PassOffenseTypeName = '${CurrentTeam.PassOffense}' AND QbTypeId = @QbTypeId"
print ""
print "IF NOT EXISTS (SELECT 1 FROM Teams WHERE TeamName = '${CurrentTeam.Name}')"
print "BEGIN"
print " INSERT INTO Teams (TeamName, DefenseId, RunOffenseId, PassOffenseId)"
print " VALUES (${CurrentTeam.Name}, @DefenseId${currentNumber}, @RunOffenseId${currentNumber}, @PassOffenseId${currentNumber})"
print "END"
print "ELSE"
print "BEGIN"
print " UPDATE Teams"
print " SET DefenseId = @DefenseId${currentNumber}"
print " , RunOffenseId = @RunOffenseId${currentNumber}"
print " , PassOffenseId = @PassOffenseId${currentNumber}"
print " WHERE TeamName = '${CurrentTeam.Name}'"
print "END"
print ""
[Meta]
public static def define(expression as StringLiteralExpression, block as BlockExpression):
return [|
_currentTeam.Name = $(expression.Value)
DefineTeam($block)
|]
[Meta]
public static def defense(expression as StringLiteralExpression):
return [|
_currentTeam.Defense = $(expression.Value)
|]
[Meta]
public static def pass_offense(expression as StringLiteralExpression, withQbType as MethodInvocationExpression):
return [|
_currentTeam.PassOffense = $(expression.Value)
$withQbType
|]
[Meta]
public static def run_offense(expression as StringLiteralExpression):
return [|
_currentTeam.RunOffense = $(expression.Value)
|]
[Meta]
public static def with_qb_type(qbType as StringLiteralExpression):
return [|
_currentTeam.QbType = $(qbType.Value)
|]
(NOTE: Whitespace is significant by default in Boo, and I've tinkered with it a bit here, so this wouldn't actually compile.)
That's really all there is to it. This class contains the bulk of the work. The meta methods are static, and don't need to be members on the base class, but it's a little easier to follow the flow of method calls this way. Now all I need to do is wire up the implicit base class and run the script. Rhino DSL does this for me:
import System import System.IO import Rhino.DSL factory = DslFactory() factory.BaseDirectory = System.AppDomain.CurrentDomain.BaseDirectory factory.Register[of TeamDefinitionScript](TeamDefinitionDslEngine()) scriptPath = Path.Combine(factory.BaseDirectory, "Scripts\\TeamDefinitions.boo") script = factory.Create[of TeamDefinitionScript](scriptPath) script.Prepare()
(NOTE: This script runner console is a different assembly than the one with the script base class.)
I run it, and this is the output (for the last declaration only):
DECLARE @DefenseId4 INT SELECT @DefenseId4 = DefenseId FROM Defense WHERE DefenseTypeName = 'Opportunistic' DECLARE @RunOffenseId4 INT SELECT @RunOffenseId4 = RunOffenseId FROM RunOffense WHERE RunOffenseTypeName = 'Serviceable' DECLARE @PassOffenseId4 INT DECLARE @QbTypeId4 INT SELECT @QbTypeId4 = QbTypeId FROM QbType WHERE QbTypeName = 'Rising Star' SELECT @PassOffenseId4 = PassOffenseId FROM PassOffense WHERE PassOffenseTypeName = 'Explosive' AND QbTypeId = @QbTypeId IF NOT EXISTS (SELECT 1 FROM Teams WHERE TeamName = 'New Orleans Saints') BEGIN INSERT INTO Teams (TeamName, DefenseId, RunOffenseId, PassOffenseId) VALUES (New Orleans Saints, @DefenseId4, @RunOffenseId4, @PassOffenseId4)END ELSE BEGIN UPDATE Teams SET DefenseId = @DefenseId4 , RunOffenseId = @RunOffenseId4 , PassOffenseId = @PassOffenseId4 WHERE TeamName = 'New Orleans Saints' END
Pretty simple. Boo makes everything so easy, you can concentrate on developing syntax and language features instead of hacking out code.
Comments