/* * Copyright (c) 2007 National Center for Toxicological Research, U.S. Food and Drug Administration * All rights reserved. * * Redistribution and use in source and binary forms, with or without * modification, are permitted provided that the following conditions are met: * * * Redistributions of source code must retain the above copyright notice, * this list of conditions and the following disclaimer. * * * Redistributions in binary form must reproduce the above copyright * notice, this list of conditions and the following disclaimer in the * documentation and/or other materials provided with the distribution. * * * Neither the name of the National Center for Toxicological Research nor the names of its contributors * may be used to endorse or promote products derived from this software * without specific prior written permission. * * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE * POSSIBILITY OF SUCH DAMAGE. */ /* * MovieRentalsQueries.cal * Created: Aug. 2007 * By: Stephen Harris */ /** * Provides examples of building and using {@link typeConstructor = MappedQuery@}'s. * * @author Stephen Harris, reachable on gmail.com as steveOfAR */ module NCTR.Cal.Examples.MovieRentals.MovieRentalsQueries; import Cal.Core.Prelude using typeConstructor = Boolean, Maybe, String; dataConstructor = False, True, Nothing, Just; function = fromJust, isJust; ; import Cal.Data.DataGems; import Cal.Data.SqlBuilder using function = derbySqlBuilder; ; import Cal.Data.Sql; import Cal.Data.DatabaseMetadata using function = makeTableReference; ; import Cal.Collections.List using function = head, map; ; import Cal.Core.Dynamic using typeConstructor = Dynamic; ; import NCTR.Cal.Data.MappedQueries using typeConstructor = MappedQuery, RightNotJoinable; function = branchInner2, chainInner1, chainInner2, chainInner2Left1, emptyQueryContext, innerJoin, innerJoinProjectLeft, leftJoin, leftJoinVia, makeInnerJoin, makeInnerJoinProjectLeft, makeRightJoin, queryTextForMappedQuery, runMappedQuery, toQuery; ; import NCTR.Cal.Data.RelatingEntities using function = innerJoinPathProjectingLastTable, innerJoinPathProjectingLastTableAsDynamic, pathsFromToInContext, shortestPathsFromToInContext, showPath; ; import NCTR.Cal.Examples.MovieRentals.GeneratedForeignKeyLinks using function = customers, discs, dynamicMappedForeignKeyLinks, employees, movies, rentals, stores; ; // Entity types import NCTR.Cal.Examples.MovieRentals.GeneratedEntities.Accounts using typeConstructor = Account;; import NCTR.Cal.Examples.MovieRentals.GeneratedEntities.Categories using typeConstructor = Category;; import NCTR.Cal.Examples.MovieRentals.GeneratedEntities.Customers using typeConstructor = Customer;; import NCTR.Cal.Examples.MovieRentals.GeneratedEntities.Discs using typeConstructor = Disc;; import NCTR.Cal.Examples.MovieRentals.GeneratedEntities.Employees using typeConstructor = Employee;; import NCTR.Cal.Examples.MovieRentals.GeneratedEntities.Movies using typeConstructor = Movie;; import NCTR.Cal.Examples.MovieRentals.GeneratedEntities.Rentals using typeConstructor = Rental;; import NCTR.Cal.Examples.MovieRentals.GeneratedEntities.Stores using typeConstructor = Store;; // Ex. 1: A single-table mapped query. /* Mapped queries have 3 type parameters - the first two are the left and right entity types that it exposes for * joining on either side, so that only compatible mapped foreign keys and queries may be joined. * The final type parameter is the type of the output values that it generates for each result row. */ rentalsMQ :: MappedQuery Rental Rental Rental; rentalsMQ = Rentals.mappedQuery; // Running the mapped query to obtain the results. allRentals :: [Rental]; allRentals = run rentalsMQ Nothing; // Nothing here means no restrictions /* Output: (module name qualifiers shortened) * [(Rentals.Rental 1 AB-12345 [2001, 11, 25] [2001, 11, 30] 1 1 1), * (Rentals.Rental 2 AB-67472 [2001, 11, 25] [2001, 11, 30] 3 2 2), * ...] */ /* Ex. 2: A join of two tables. * The foreign key link rentals.disc_id is defined in GeneratedForeignKeyLinks which is generated along with the * entity types during reverse engineering. * Many-to-one links (>--) have the form . . * One-to-many links (--<) would have the form .. . */ rentalsDiscsMQ :: MappedQuery Rental Disc (Maybe Rental, Disc); rentalsDiscsMQ = makeRightJoin rentals.disc_id; // The results. rentalsDiscs :: [(Maybe Rental, Disc)]; rentalsDiscs = run rentalsDiscsMQ Nothing; /* Output: * [[(Just (Rentals.Rental 1 AB-12345 [2001, 11, 25] [2001, 11, 30] 1 1 1)), (Discs.Disc AB-12345 1 (Just 2))], * [(Just (Rentals.Rental 2 AB-67472 [2001, 11, 25] [2001, 11, 30] 3 2 2)), (Discs.Disc AB-67472 1 (Just 5))], * ...] */ /* * Chains of foreign keys * fk1 -- fk2 -- ... -- fkN */ // Ex. 3: A chain consisting of two inner joins followed by a left join. rentalsCategories2MQ :: MappedQuery Rental Category (((Rental, Disc), Movie), Maybe Category); rentalsCategories2MQ = makeInnerJoin rentals.disc_id `innerJoin` discs.movie_id `leftJoin` movies.category_id; /* We could write the above more explicitly as: * ((makeInnerJoin rentals.disc_id) // MappedQuery Rental Disc (Rental,Disc) * `innerJoin` discs.movie_id) // MappedQuery Rental Movie ((Rental,Disc),Movie) * `leftJoin` movies.category_id; // MappedQuery Rental Category (((Rental, Disc), Movie), Maybe Category) * The first line generates an initial mapped query from the rentals.disc_id mapped foreign key, with endpoint * types Rental and Disc, and result type (Rental,Disc). * The next line then inner joins this query with Movies to generate a MappedQuery Rental Movie ((Rental,Disc),Movie). * The third line then left outer joins this query with Categories to produce the final mapped query. */ // The results. rentalsCategories2 :: [(((Rental, Disc), Movie), Maybe Category)]; rentalsCategories2 = run rentalsCategories2MQ Nothing; /*[[[[(Rentals.Rental 1 AB-12345 [2001, 11, 25] [2001, 11, 30] 1 1 1), (Discs.Disc AB-12345 1 (Just 2))], (Movies.Movie 1 The Godfather 2 (Just 175 min) (Just USA:R) (Just [1972, 3, 24]))], (Just (Categories.Category 2 Crime))], * [[[(Rentals.Rental 2 AB-67472 [2001, 11, 25] [2001, 11, 30] 3 2 2), (Discs.Disc AB-67472 1 (Just 5))], (Movies.Movie 1 The Godfather 2 (Just 175 min) (Just USA:R) (Just [1972, 3, 24]))], (Just (Categories.Category 2 Crime))], * ...] */ /* Ex. 4: * This is the same query again but this time we use a predefined function chainInner2Left1 to build the chain. * The predefined chain* functions are obviously less flexible than the incremental chain building functions * used above (which can be used to build arbitrary unlimited chains), but they do have a couple of practical * advantages: * (i) they generate flat tuples instead of nested right-pairings, and the flattened tuples can be more * convenient in pattern matching; * (ii) they generate simpler diagrams in the GemCutter. * * These functions have names of the form chainInner[N]Left[M] where N is the number of initial (foreign key) * arguments to join as inner joins, and M is the remaining number to join as left outer joins. (Left joins * always follow inner joins in chains since if we had a left joining preceeding an inner join, it would just * eliminate the rows with unmatched sides in the preceeding left join, making it equivalent to inner join in * that place). When M or N is 0, the name degenerates to just chainInner[N] or chainLeft[M]. */ rentalsCategoriesMQ :: MappedQuery Rental Category (Rental, Disc, Movie, Maybe Category); rentalsCategoriesMQ = chainInner2Left1 rentals.disc_id discs.movie_id movies.category_id; // The results. rentalsCategories :: [(Rental, Disc, Movie, Maybe Category)]; rentalsCategories = run rentalsCategoriesMQ Nothing; /* Output: * [[(Rentals.Rental 1 AB-12345 [2001, 11, 25] [2001, 11, 30] 1 1 1), (Discs.Disc AB-12345 1 (Just 2)), (Movies.Movie 1 The Godfather 2 (Just 175 min) (Just USA:R) (Just [1972, 3, 24])), (Just (Categories.Category 2 Crime))], * [(Rentals.Rental 2 AB-67472 [2001, 11, 25] [2001, 11, 30] 3 2 2), (Discs.Disc AB-67472 1 (Just 5)), (Movies.Movie 1 The Godfather 2 (Just 175 min) (Just USA:R) (Just [1972, 3, 24])), (Just (Categories.Category 2 Crime))], * ...] */ // It is easy to extract a Sql.Query from a mapped query, using the toQuery function. The emptyQueryContext // argument to toQuery below just means this query is not part of a larger query, with which auto-generated // table aliases would need to be coordinated. rentalsCategoriesQ = toQuery rentalsCategoriesMQ emptyQueryContext; // Here is the SQL text for the query. rentalCategoriesQueryText = Sql.queryText sqlBuilder True rentalsCategoriesQ; // or more directly, = queryTextForMappedQuery rentalsCategoriesMQ sqlBuilder; /* Output: SELECT "rentals"."RENTAL_ID" AS "rentals/RENTAL_ID", "rentals"."DISC_ID" AS "rentals/DISC_ID", "rentals"."RENTAL_DATE" AS "rentals/RENTAL_DATE", "rentals"."RETURN_DATE" AS "rentals/RETURN_DATE", "rentals"."CUSTOMER_ID" AS "rentals/CUSTOMER_ID", "rentals"."EMPLOYEE_ID" AS "rentals/EMPLOYEE_ID", "rentals"."STORE_ID" AS "rentals/STORE_ID", "discs"."DISC_ID" AS "discs/DISC_ID", "discs"."MOVIE_ID" AS "discs/MOVIE_ID", "discs"."RENTAL_DURATION_DAYS" AS "discs/RENTAL_DURATION_DAYS", "movies"."MOVIE_ID" AS "movies/MOVIE_ID", "movies"."TITLE" AS "movies/TITLE", "movies"."CATEGORY_ID" AS "movies/CATEGORY_ID", "movies"."MOVIE_DURATION" AS "movies/MOVIE_DURATION", "movies"."RATING" AS "movies/RATING", "movies"."RELEASE_DATE" AS "movies/RELEASE_DATE", "categories"."CATEGORY_ID" AS "categories/CATEGORY_ID", "categories"."NAME" AS "categories/NAME" FROM (("RENTALS" AS "rentals" INNER JOIN "DISCS" AS "discs" ON "rentals"."DISC_ID" = "discs"."DISC_ID") INNER JOIN "MOVIES" AS "movies" ON "discs"."MOVIE_ID" = "movies"."MOVIE_ID") LEFT OUTER JOIN "CATEGORIES" AS "categories" ON "movies"."CATEGORY_ID" = "categories"."CATEGORY_ID" */ // The result rows of the generated SQL as text, without mapping to entities (for exploration/debugging). rentalCategoriesResultsText = resultsText rentalsCategoriesMQ; /* Output: * rentals/RENTAL_ID rentals/DISC_ID rentals/RENTAL_DATE rentals/RETURN_DATE rentals/CUSTOMER_ID rentals/EMPLOYEE_ID rentals/STORE_ID discs/DISC_ID discs/MOVIE_ID discs/RENTAL_DURATION_DAYS movies/MOVIE_ID movies/TITLE movies/CATEGORY_ID movies/MOVIE_DURATION movies/RATING movies/RELEASE_DATE categories/CATEGORY_ID categories/NAME * ----------------- --------------- ------------------- ------------------- ------------------- ------------------- ---------------- ------------- -------------- -------------------------- --------------- ------------ ------------------ --------------------- ------------- ------------------- ---------------------- --------------- * 1 AB-12345 2001-11-25 2001-11-30 1 1 1 AB-12345 1 2 1 The Godfather 2 175 min USA:R 1972-03-24 2 Crime * 2 AB-67472 2001-11-25 2001-11-30 3 2 2 AB-67472 1 5 1 The Godfather 2 175 min USA:R 1972-03-24 2 Crime * ... */ /* Ex. 5: Similar query but with only Rentals projected, by using the xJoinProjectLeft functions, which only * project their left side's results. This is an easy way of seeing which records in one table (here Rentals) are related to * records in some other table (Categories), via a path of foreign keys between them. */ rentalsProjectedMQ :: MappedQuery Rental Category Rental; rentalsProjectedMQ = makeInnerJoinProjectLeft rentals.disc_id `innerJoinProjectLeft` discs.movie_id `innerJoinProjectLeft` movies.category_id; /* We'll add a restriction when running this mapped query. When building restrictions, the table aliases are * just the lowercase table names; except if a table occurs multiple times in a query, then its occurrence number * is appended to the alias, starting with 1 for the leftmost occurrence. Any table involved in the mapped query * may be used in the restriction, not just the exposed endpoints. */ categoryIsMystery :: Sql.TypedExpr Boolean; categoryIsMystery = Sql.eqExpr (Sql.stringField (Sql.makeQueryTable "categories") "NAME") (Sql.stringConstant "Mystery"); rentalsOfMysteries :: [Rental]; rentalsOfMysteries = run rentalsProjectedMQ (Just [categoryIsMystery]); /* Output: * [(Rentals.Rental 3 OW-41221 [2001, 11, 25] [2001, 11, 30] 1 3 3), * (Rentals.Rental 6 OW-41221 [2002, 1, 3] [2002, 1, 6] 4 6 2)] */ /* Ex. 6: Branching * * |-- fk1 -- mq1 * |-- fk2 -- mq2 * (leftTable)--| ... * | * |-- fkN -- mqN (for N <= 5 currently) * * When joining a single table to mapped queries on several branches, each branch is preceeded by the foreign key * link joining the left table to the branch's left exposed table. The common left table is implied by the foreign * keys which must all share the same left table (and which is guaraunteed by the type system so long as the * generated entity types are used). The resulting mapped query is joinable on the left to the common table, * and doesn't expose a joinable table on the right. */ branchFromRentalsMQ :: MappedQuery Rental RightNotJoinable (Rental,(Disc,Movie,Category),(Customer,Account)); branchFromRentalsMQ = branchInner2 rentals.disc_id (chainInner2 discs.movie_id movies.category_id) // Rentals--Discs-Movies-Categories rentals.customer_id (chainInner1 customers.account_id); // Rentals--Customers-Accounts // The results. branchFromRentals :: [(Rental,(Disc,Movie,Category),(Customer,Account))]; branchFromRentals = run branchFromRentalsMQ (Just [Sql.notExpr categoryIsMystery]); /* Output: * [[(Rentals.Rental 1 AB-12345 [2001, 11, 25] [2001, 11, 30] 1 1 1), [(Discs.Disc AB-12345 1 (Just 2)), (Movies.Movie 1 The Godfather 2 (Just 175 min) (Just USA:R) (Just [1972, 3, 24])), (Categories.Category 2 Crime)], [(Customers.Customer 1 Jones Henry (Just 555-1212) (Just [1970, 10, 10]) 3 (Just 0.00)), (Accounts.Account 3 1730 Abbey Pl # 1 Little Rock AR 72116 (Just -14.00))]], * [(Rentals.Rental 2 AB-67472 [2001, 11, 25] [2001, 11, 30] 3 2 2), [(Discs.Disc AB-67472 1 (Just 5)), (Movies.Movie 1 The Godfather 2 (Just 175 min) (Just USA:R) (Just [1972, 3, 24])), (Categories.Category 2 Crime)], [(Customers.Customer 3 Panky Henry (Just 555-1221) (Just [1968, 1, 21]) 4 (Just 0.00)), (Accounts.Account 4 130 Providence Rd Little Rock AR 72211 (Just -3.45))]], * ...] */ /* Ex. 7: General joins * The following demonstrates a general join of arbitrary mapped queries via a foreign key link between them, using * function leftJoinVia. * Here employees.rentals.employee_id is the foreign key link being used to join the two mapped queries. * It is a one-to-many link (ie. opposite in direction of the foreign key reference), which are written in the form * ... */ storeEmployeeRentalsBranchMQ :: MappedQuery Store RightNotJoinable ((Store,Employee), Maybe (Rental,(Disc,Movie,Category),(Customer,Account))); storeEmployeeRentalsBranchMQ = chainInner1 stores.employees.store_id // Left hand mapped query, ending with employees. `leftJoinVia` employees.rentals.employee_id $ // Linking foreign key, from employees to rentals. branchFromRentalsMQ; // Right hand mapped query, starting with rentals. // Example row processing for the above mapped query. // Flag suspicious employee rentals. isSuspicious :: ((Store,Employee), Maybe (Rental,(Disc,Movie,Category),(Customer,Account))) -> Boolean; isSuspicious !resRow = if isJust resRow.#2 then let (store,employee) = resRow.#1; (rental, discMovieCat, custAcc) = fromJust resRow.#2; (disc,movie,category) = discMovieCat; (customer,account) = custAcc; in if Customers.phone customer == Employees.phone employee || Categories.name category == "Romance" || Movies.title movie == "Steel Magnolias" then True else False else // employees without rentals are also suspicious True; // Find suspicious employees by their suspicious rentals. suspiciousEmployees :: [Employee]; suspiciousEmployees = List.map (\res -> res.#1.#2) $ // get the Employee List.filter isSuspicious $ run storeEmployeeRentalsBranchMQ Nothing; /* Output: * [(Employees.Employee 5 Smykowski Tom (Just 431331313) (Just 555-1221) (Just [1953, 8, 28]) 1 1), * (Employees.Employee 2 Bolton Michael (Just 431351111) (Just 555-1221) (Just [1967, 12, 21]) 2 2), * ...] */ /* Now we use the RelatingEntities modules to relate entities across the schema, without having to know their exact * relationship. This use of the RelatingEntities module (as well as its source code) illustrates a more * dynamically-typed approach, using many of the same types as before but with a uniform Dynamic type parameter. */ /* 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 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. // The version that yields a specific entity result type requires a type declaration or some context to determine // the result type via type inference. 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))] */ // Dynamic results. storesWithMysteriesDynamicResults :: [Dynamic]; // Dynamic representation of the Stores. storesWithMysteriesDynamicResults = run (innerJoinPathProjectingLastTableAsDynamic shortestCategoriesStoresPath) (Just [categoryIsMystery]); // [No output for this one since Dynamics are not outputable] // Connection information and convenience functions connection :: DataGems.JDBCConnection; public connection = DataGems.jdbcConnectionWithDriverCheck "org.apache.derby.jdbc.EmbeddedDriver" ("jdbc:derby:src/CAL/NCTR/Cal/Examples/MovieRentals/database/moviesdb") "" //username ""; //password sqlBuilder = derbySqlBuilder; // Utility functions // Convenience function for testing the produced queries (without mapping to entities) resultsText :: MappedQuery a b t -> String; resultsText !mq = let resultSet !mq = DataGems.jdbcQueryToResultSet connection (queryTextForMappedQuery mq sqlBuilder); in DataGems.resultSetText 0 $ resultSet mq; run :: MappedQuery a b t -> Maybe [Sql.TypedExpr Boolean] -> [t]; run !mq !restrictions = runMappedQuery mq restrictions sqlBuilder connection;