Getting started with Crank: Entities and Attributes

As the latest release of Crank is a complete rewrite now seems like a good time to write a tutorial on how to use it. Before we get started, go to the release page and download the Crank-0.3-bin. Unzip the file somewhere useful and you should have a bunch of files including Crank.exe and default.cal. Rename default.cal to library.cal and create a new file called default.cal in the same directory as Crank.exe. Open up your brand new empty default.cal and we're ready to get started.

Crank deals with three things: Entities, Attributes, and Links. These are roughly equivalent to Tables, Columns, and Foreign Key Constraints in modern databases. In this post we are just going to have a look at Entities and Attributes and we'll get into Links in the next post. We are going to recreate the Libary sample which comes with the tool eventually.


Entities


To create a new entity you simply need to type it's name like this

Books
Members
This means that we want two entities, one called Books and the other called Members. They didn't need to be on separate lines they just need to be separated by whitespace. We could have done it this way:
Books Members # Everything after the # symbol is a comment
Save the file and double click Crank.exe. After Crank runs you should see default.cal.sql in the same folder, open it up and you'll see that your 2 lines easily readable of CAL have become 25 lines of SQL gobbledegook (specifically SQL2005 gobbledegook). If you run this against a database you'll get this:

Crank has created two tables and given each one a primary key (named after the singular form of the table name).

Note that default.cal.sql has a drop and create portion so you can re-run the script as many times as you need to. You should also be aware of this because it will dump any test data that you have in the database (DON'T USE CRANK ON A PRODUCTION DATABASE).

Attributes

We have two Entities but they don't really do a lot just yet. We need to flesh them out a little with some Attributes. You add Attributes to an Entity using the <- (has) operator. Lets add a First and Last Names to our members:

Member <- { FirstName } VARCHAR(25)
Member <- { LastName } VARCHAR(25)

Just add the above text to your file underneath the entity definitions given before. You might have noticed that we used Members and Member to refer to the same entity. This is okay because Crank infers that they are the same thing (it prefer the plural form for entities). You also might have noticed that the Member is mentioned 3 times. This is also okay, every mention of an entity merely extends it. This is a lot of repetitive typing so we can shorten this in either of the following ways:

Member <- { FirstName } VARCHAR(25) <- { LastName } VARCHAR(25)
# Or
Member <- { FirstName, LastName } VARCHAR(25)

You can chain as many <- operators onto an entity as you like. The VARCHAR(25) part is the "type" of the attribute. At the moment, Crank just forwards this type onto the scripting component. Lets update the Book Entity:

Book <- { ISBN } CHAR(13) <- { Title } VARCHAR(100)

Run Crank.exe again and it should update default.cal.sql. Run this against the database to get:

Looking better but do we really need that BookID if we have ISBN. ISBN, is after all, guaranteed to be unique for each book. We can turn ISBN into a "key attribute" by prefixing it with a * like this.

Book <- { *ISBN } CHAR(13)

Knowing this we might like to change MemberID to someting more application friendly (say MembershipNumber). What we can't see from the diagrams is that BookID and MemberID are autonumbered fields but ISBN certainly isn't. If we'd like for MembershipNumber to retain this quality we prefix it with two asterisks like this:

Member <- { **MembershipNumber } INT

The entire script now looks like this:

Books
Members

Member
<- { FirstName, LastName } VARCHAR(25) <- { **MembershipNumber } INT Book <- { *ISBN } CHAR(13) <- { Title } VARCHAR(100)

and produces the following:

At the last minute we'll decide that we might like to track Publishers as well.

Publishers, Members <- { EmailAddress } VARCHAR(100)  Publisher <- { Name } VARCHAR(100)

Note the comma syntax on the first line. The comma essentially joins Publishers and Members together for an operation. The effect here is that both Publishers and Members have an EmailAddress attribute.

As we've worked on a few projects we know that someone (at some point) is likely to ask us to "just add some auditing into the system now please". These kind of requests are pretty inevitable so using our comma notation we can do this:

Publishers, Members, Books <- { CreatedOn?, ModifiedOn? } DATETIME

The question mark is an optionality specifier which says that these attribute are optional (NULL in SQL-Speak). To say that something is required you'd use ! instead of ?. Attributes are assumed to be required unless otherwise specified. You can change this.

If you know you want to add attributes to every single entity in the project then you can use the special "all" group:

$ <- { CreatedOn?, ModifiedOn? } DATETIME 

Run Crank.exe again and let's have a look at the result:


Conclusion

Well that's enough for now. My default.cal is 12 lines long with some nice white-space but with that we've managed to: create 3 entities populated with 15 attributes of various types, specified and implied optionalities. We explored entity grouping and automatic keys. Next time we'll have a look at various options we have for linking Entities together. If you have any difficulties or any comments please leave a comment either here or join the discussion on the CodePlex site.

crank
Posted by: Mike Minutillo
Last revised: 27 May, 2011 02:42 PM History

Comments

No comments yet. Be the first!

No new comments are allowed on this post.