Getting started with Crank: Linking Entities

Note: this is part 2 of Getting started with Crank. In the first part we explored Entities and Attributes. We saw Entity grouping with the comma operator, Attribute optionality, Key Attributes and Artificial (Auto-Numbered) Keys. We got started producing the Library sample and our code currently looks like this:

Books
Members
Member <- { FirstName, LastName } VARCHAR(25)
<- { **MembershipNumber } INT
Book <- { *ISBN } CHAR(13)
<- { Title } VARCHAR(100)
Publishers, Members <- { EmailAddress } VARCHAR(100)
Publisher <- { Name } VARCHAR(100)
$ <- { CreatedOn?, ModifiedOn? } DATETIME


When executed the resulting database looks like this:



 Libray System Model without Links





This is all well and good but our tables are all islands of data with no links to each other, it doesn't look very relational does it. It turns out that Crank can help us here as well by defining Links between Entities. Let's start with an easy one, "Every Book must have a publisher":



Book => Publisher


Easy. Run this script against the database and you'll get this:



Librabry Data Model with Book=>Publisher Link



Notice that Book now has a PublisherID field that was automatically added to facilitate the link. When Crank encounters a Link it will:




  1. Identify the two Entities involved and associate an "end" to them (i.e. To and From)


  2. Copy the primary key column(s) from the "To Entity" into the "From Entity"


  3. Script the Foreign Key as a database Constraint.



This has a number of advantages:




  1. There is a naming convention for Foreign Keys


  2. Types are kept in synch from foreign to primary keys automatically without user involvement


  3. Relationships will be represented as DB Constraints ensuring referential integrity.



You can qualify a Link with a Link Name if you want:



Book => { PublishedBy } Publisher

Now the column created in the Books table will be called PublishedByPublisherID. If you don't want the Primary Key name tacked on the end you can enclose the Link Name in square brackets like this:

Book => { [PublishedBy] } Publisher




Link Names are actually Attribute definitions so you can use the key specifier (*) and Optionality specifiers (? and !) on them as well



Book => { PublishedBy? } Publisher

roughly translates into "Books may be published by a Publisher". If you want to specify an optionality (or key-ness) but want to keep the default naming convention you can use a single underscore (_) as a Link Name.

Book => { _? } Publisher




=> is just one of 5 link operators. The following are all equivalent:



Book => { Publishedby } Publisher
Book > { PublishedBy } Publisher
Publisher <= { PublishedBy } Book
Publisher < { PublishedBy } Book

Which ones you use is really a matter of preference. All links are transformed into => Links before the scripting components get a hold of them anyway. There is one more link operator with slightly different semantics but we'll get to that in a minute.



The comma operator still works for Entity Grouping when creating Links. Let's add Loans to our model.



Loans => Book, Member

This just says that Loans need links to Book and Member. Adding this to our script and running it yields the following SQL:



Library Data Model with Loans => Books, Members Link added



Remember that a Link may be a key so you can create an associative entity between Books and Members (say Ratings) like this:



Ratings => { *BookID } Book
Ratings => { *MemberID } Member

And with Auto-naming and the Entity Grouping we can have

Ratings => { *_ } Book, Member

This is such a common operation that the 5th Link Operator is devoted to it:

Books <> { Ratings } Members

Roughly speaking "Books may have many Members, Members may have many Books, call this association Ratings". You don't even have to give it a name

Books <> Members

will result in the automatic creation of a BookMembers Entity. NOTE: This means that Books <> Members is not the same thing as Members <> Books but if you name the link, it is.



Frequently Supplied Answers



Q: What does A, B => C, D => E, F do?




A: Chaining Links across grouped entities works just fine. The example above is the equivalent of the following.



A => C
A => D
B => C
B => D
C => E
C => F
D => E
D => F

Q: What if I do this:

A => B => A

A: You can. You'll get A.BID and B.AID





Q: How about

A => { *_ } B => { *_ } A

A: Crank.exe will fail because it cannot determine the types of the keys involved. I'm not even sure if you can achieve this effect with SQL if you want to either. Anybody know?





Q: Can I do this?

A => A

A: While this is perfectly valid CAL (for the moment) you'll end up with duplicate columns in the resultant SQL script and it will fail. If you qualify the Link with a Link Name then it works fine and is quite common. i.e.

Employee => { ManagedBy? } Employee

Q: Can I create Links with the "All Entity" ($)?





A: Yes you can but the semantics are slightly different.

$ => A # Every Entity gets a link to A except for A itself, if you want to also have A => A then you must explicitly add this
A => $ # A gets a link to every other Entity except for A itself. As above you can explicitly add A => A if you want.
$ => $ # Now you are getting silly but you can do this. It does do what you'd expect and provide a Link between every pair of Entities in the model.




Conclusion


After adding in some properties and another entity using the techniques we've already explored we end up with the following CAL script:

# Library Management System
Books
Members

Loans => Book, Member

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

Member <- { **MembershipNumber } INT
<- { FirstName, LastName } VARCHAR(25)
<- { EmailAddress } VARCHAR(255)

Loan <- { StartDate, DueDate, ActualReturnDate? } DATETIME

Reviews, Ratings => {*_} Book, Member

Rating <- { Value } INT
Review <- { FullText } Text

Publishers

Book => Publisher <- { Name } VARCHAR(100)

$ <- { CreatedBy?, ModifiedBy? } VARCHAR(50)
<- { CreatedOn?, ModifiedOn? } DATETIME

And produces the following Database Schema:



Final Library Data Model (60% Zoom)



As usual if you are having any difficulties drop a comment here or over on the Crank Codeplex Forums. Next time we'll look at the Preprocessor instructions and the Command-Line options.

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

Comments

03 Jul, 2008 01:36 PM @ version 0

Thanks Kevin.

In fact the version running on CodePlex is out of date because I've been using a Crank on my current project. As soon as my project is finished I have an updated version to put together and a whole heap of new features and bug fixes to implement.

The most recent re-write of Crank was focused on splitting the parsing, model-building and script generation processes up to support that kind of operation in the future.

I'm still messing with the CAL language itself though and until that is nailed down the whole tool is pretty much in flux.

Thanks for your interest. It's nice to know I'm not the only who thinks the idea has merit.

02 Jul, 2008 07:58 PM @ version 0

Great idea. I havent seen much in the .NET world for this sort of thing. Mostly tools that generate code from a DB.

Have you considered the possibility of using templates for the generated SQL? Just thinking about situations where people choose different DB object naming conventions.

No new comments are allowed on this post.