CAL Reference

CAL is the language that Crank speaks.

28 Nov 07 Changes of version 0.2

Quick Notes

Before we begin:
Comments begin with an # and proceed until the end of the line
# like this

Preprocessor directives begin with an @ symbol and also proceed until the end of the line.
@include other.cal

Entities

A CAL Entity is just on object that you are interested in. You mention an entity by putting it's name into the cal file. You can mention multiple entities separated by whitespace and mentioning the same entity more than once will still result in only one entity being created.
Entities.cal
    Customers
    Orders Products SalesReps Customers

Running crank.exe Entities.cal will result in 4 tables being created Customers, Orders, Products, SalesReps. Each new table will have an id column named: CustomerID, OrderID, ProductID and SalesRepID. These coulmns will be INT IDENTITY(1, 1). At the moment this is the default behavior and cannot be overridden. Notice that we can say Customers twice and still get only one Customers table. Currently entities can only be letters numbers and the underscore (_) character.

Attributes

An Attribute is a property of the Entity. They allow you to add a little more than just an ID column to the database tables that are produced. The syntax looks like this:
Attributes.cal
    Customers <- { FirstName, LastName } NVARCHAR(20)
                     <- { BirthDate } DATETIME
                     <- { LastPurchase } DATETIME

    Customer <- { AccountNumber } NVARCHAR(10)

Attribute are added to the entity with the <- operator and appear between curly braces { }. They are followed by a type which is at the moment just passed to the scripter (hence the SQL 2005 Syntax). Multiple Attributes of the same type may be added to the entity by separating them with commas inside the braces. Note that Attribute constructs can be chained allowing you to add attributes to the entity rapidly without having to mention the Entity name each time. Entites are always open so you can add attributes to any entity. Also note that the Customers entity is effectively mentioned twice in the file above so an Entity being mentioned for it's attributes counts.

Pluralization

A quick not on pluralization. The Script above had Customer and Customers. You might think that this is a typo and will cause two tables to be created. Crank is smart enough to be able to figure out that these refer to the same entity. The rules for pluralization are simple: If you mention the plural only, the singular will be guessed at. If you mention the plural and the singular they will resolve to the same thing. This is thanks (in part) to the Inflector class which has been ported from the ActiveSupport library (which is part of the Ruby on Rails framework). The cool thing about this is that if you create a People entity it will create a People table with a PersonID primary key. So use whatever pluralization you are comfortable with but if you are finding that it isn't giving you the right tables out the other end try putting a pluralized version of your entity name at the top of your cal file.

Links

Entities by themselves aren't very exciting. We need to be able to link them together. That is where links come in. An example:
Links.cal
    Customers < Orders <> Products
    SalesReps < Customers

In english this says "Customers are related to many Orders. Each Order is related to many Products. Each Product is related to many Orders. Each SalesRep is related to multiple Customers". The above cal file will resolve into 5 tables being generated: SalesReps, Customers, Orders, Products and OrderProducts. Each will still have it's primary ID column. But now we'll get a CustomerID column inside orders. And a SalesRepID inside Customers. Each of these comes with a foreign key constraint. Note that Crank generated a link-table between Orders and Products and even named it intelligently. Those foreign keys look pretty generic though so before we get to what each of the link operators do lets have a look at:

Link Annotations

You can follow each link with an annotation explaining the purpose of the link. Lets see an example:
Annotated.cal
    Customers <{MadeBy} Orders <> {LineItems} Products
    SalesReps < {AccountManager} Customers

That's a little better. Now it says "Orders are MadeBy Customers. Each Customer may make any number of Orders. Orders are made up of many LineItems each of which refers to a Product. Each Product may be contained in a number of different LineItems. Each Customer may have an Account Manager who is a SalesRep. Each SalesRep may be the Account Manager for any number of Customers." Even better, inside our Orders table we now have a MadeByCustomerID column and inside the Customers table we have an AccountManagerSalesRepID. These names are more descriptive and will allow us to more easily understand the model when it's in SQL form.

Links take 2

Directly from the Liftoff plugin site:
Operation Description
A > B A belongs to B, B has many A
A < B A has many B, B belongs to A
A <= B A belongs to B, B has one A
A => B A has one B, B belongs to A
A <> B A has and belongs to many B, B has and belongs to many A

The Crank tool currently recognizes the <= and => symbols but they are just synonyms for the > and < symbols respectively for the moment. This is intended to change eventually (as soon as I figure out how they should change).

Also A<>B is really just synonymous with A>C<B where C is generated for you.

Chaining

You probably noticed above that we chained a bunch of stuff together and it just worked. Each Link operator is actually considered entirely in isolation and whitespace is unimportant. An entity with attributes is just considered to be that entity so you can do the following if you like:
Chained.cal
    Customers <- { FirstName, LastName } NVARCHAR(20)
                <- { AccountNumber } NVARCHAR(10)
				
      <> { GroupMembers }

    Groups =>{Leader} Customer

Running Crank.exe Chained.cal produces the following file:
Chained.cal.sql
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_GroupMembers_Customers]') AND parent_object_id = OBJECT_ID(N'[GroupMembers]'))
ALTER TABLE [GroupMembers] DROP CONSTRAINT [FK_GroupMembers_Customers]
GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_GroupMembers_Groups]') AND parent_object_id = OBJECT_ID(N'[GroupMembers]'))
ALTER TABLE [GroupMembers] DROP CONSTRAINT [FK_GroupMembers_Groups]
GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_Groups_Customers_Leader]') AND parent_object_id = OBJECT_ID(N'[Groups]'))
ALTER TABLE [Groups] DROP CONSTRAINT [FK_Groups_Customers_Leader]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Customers]') AND type in (N'U'))
DROP TABLE [Customers]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[GroupMembers]') AND type in (N'U'))
DROP TABLE [GroupMembers]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Groups]') AND type in (N'U'))
DROP TABLE [Groups]
GO

CREATE TABLE [Customers] (

	CustomerID INT IDENTITY(1, 1),
	FirstName NVARCHAR(20),
	LastName NVARCHAR(20),
	AccountNumber NVARCHAR(10),
	CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
	(
		[CustomerID] ASC
	)
)
GO

CREATE TABLE [GroupMembers] (

	GroupMemberID INT IDENTITY(1, 1),
	CustomerID INT,
	GroupID INT,
	CONSTRAINT [PK_GroupMembers] PRIMARY KEY CLUSTERED
	(
		[GroupMemberID] ASC
	)
)
GO

CREATE TABLE [Groups] (

	GroupID INT IDENTITY(1, 1),
	LeaderCustomerID INT,
	CONSTRAINT [PK_Groups] PRIMARY KEY CLUSTERED
	(
		[GroupID] ASC
	)
)
GO

ALTER TABLE [GroupMembers] WITH CHECK ADD CONSTRAINT [FK_GroupMembers_Customers] FOREIGN KEY([CustomerID]) REFERENCES [Customers] ([CustomerID])
GO

ALTER TABLE [GroupMembers] WITH CHECK ADD CONSTRAINT [FK_GroupMembers_Groups] FOREIGN KEY([GroupID]) REFERENCES [Groups] ([GroupID])
GO

ALTER TABLE [Groups] WITH CHECK ADD CONSTRAINT [FK_Groups_Customers_Leader] FOREIGN KEY([LeaderCustomerID]) REFERENCES [Customers] ([CustomerID])
GO


I think we saved some typing there ;)

Last edited Nov 28, 2007 at 12:42 AM by michaelminutillo, version 6

Comments

No comments yet.