Relating Entities

[Steve Harris]

I added one new module called "RelatingEntities" to the experimental
data access framework in the version posted this morning. I wanted to
post an example usage, since it might have been totally missed
otherwise (except for by the very patient), being at the end of the
examples module, MovieRentalsQueries.cal.

It also illustrates that I was wrong to worry about the strong typing
of the query building framework becoming an obstacle when more dynamic
data access is needed - Cal's type system actually worked beautifully
for the dynamic case (thanks to Dynamic and Typeable), even without
introducing any new types to wrap the existing types. Thus no
separate "dynamic access" library or set of types was necessary.

[From MovieRentalsQueries.cal:]

/* Now we use the RelatingEntities modules to relate entities across
the schema, without having to know their exact relationship. ... */

/*
First we find all paths(*) of foreign key links connecting the
Categories table with the Stores table.
(*)-The default paths filter ignores paths that pass through the same
table twice; For more control, the filteredPathsFromToInContext
function can be used.
*/

// Show all paths of foreign key links between tables Categories and
Stores.
allPaths_Str = map showPath $
pathsFromToInContext
(makeTableReference "CATEGORIES")
(makeTableReference "STORES")
dynamicMappedForeignKeyLinks;
/* Output:
[CATEGORIES --["CATEGORY_ID"]--< MOVIES --["MOVIE_ID"]--< DISCS --
["DISC_ID"]--< RENTALS >--["EMPLOYEE_ID"]-- EMPLOYEES >--
["STORE_ID"]-- STORES,

CATEGORIES --["CATEGORY_ID"]--< MOVIES --["MOVIE_ID"]--< DISCS --
["DISC_ID"]--< RENTALS >--["STORE_ID"]-- STORES]

We could interpret these paths as:
1) Categories have member Movies, which are instantiated in Discs,
which have Rentals, which are handled by Employees, which are assigned
to Stores.
2) Categories have member Movies, which are instantiated in Discs,
which have Rentals, which occur in Stores.
*/

// The shortestPathsFromToInContext will select only the shortest
paths for us, in this case the latter path.
shortestCategoriesStoresPath =
head $ shortestPathsFromToInContext
(makeTableReference "CATEGORIES")
(makeTableReference "STORES")
dynamicMappedForeignKeyLinks;

/*
Now we "push" a restriction on Categories through the (shortest) path
to see what comes out on the Stores side. This answers the question,
"which Stores are related to the Mystery category?", ie. "Which stores
have rented mysteries?". We can get results either as Dynamics or
inferenced to some specific type.
*/

storesWithMysteries :: [Store]; // This type decl. is necessary here
to determine the result type.
storesWithMysteries = run (innerJoinPathProjectingLastTable
shortestCategoriesStoresPath)
(Just [categoryIsMystery]);
/* Output:
* [(Stores.Store 3 7403 Cantrell Road Little Rock AR 72207 (Just
11:00 AM) (Just 11:00 PM)),
* (Stores.Store 2 8521 Geyer Springs Road Little Rock AR 72209 (Just
11:00 AM) (Just 09:00 PM))]
*/

(Same thing with [Dynamic] output snipped)

Steve

[Steve Harris]

My code formatting horrible as usual, here's the essentials
of the example, reformatted (if these short lines wrap,
I give up!):

storesWithMysteries :: [Store];
storesWithMysteries = run
(innerJoinPathProjectingLastTable

head $ shortestPathsFromToInContext
(makeTableReference "CATEGORIES")
(makeTableReference "STORES")

dynamicMappedForeignKeyLinks))
(Just [categoryIsMystery]);

/* Output:
* [(Stores.Store 3 7403 Cantrell Road Little Rock AR ...),
* (Stores.Store 2 8521 Geyer Springs Road Little Rock AR...)]
*/