relational-record-examples 0.2.0.0 → 0.2.0.2
raw patch · 10 files changed
+284/−27 lines, 10 files
Files
- relational-record-examples.cabal +9/−2
- sql/3.7.1.sh +9/−0
- sql/3.7.3.sh +9/−0
- sql/3.7.sh +9/−0
- sql/4.1.2.sh +9/−0
- sql/4.3.2.sh +9/−0
- sql/5.1.3.sh +12/−0
- sql/sql-memo +69/−0
- src/DataSource.hs +0/−1
- src/examples.hs +149/−24
relational-record-examples.cabal view
@@ -1,5 +1,5 @@ name: relational-record-examples-version: 0.2.0.0+version: 0.2.0.2 synopsis: Examples of Haskell Relationa Record description: Provides examples of Haskell Relational Record license: BSD3@@ -11,14 +11,21 @@ build-type: Simple cabal-version: >=1.10 -extra-source-files: sql/4.3.3a.sh+extra-source-files: sql/3.7.1.sh+ sql/3.7.3.sh+ sql/3.7.sh+ sql/4.1.2.sh+ sql/4.3.2.sh+ sql/4.3.3a.sh sql/4.3.3b.sh sql/4.3.3c.sh sql/5.1.2a.sh+ sql/5.1.3.sh sql/5.3a.sh sql/6.4.1a.sh sql/8.1a.sh sql/add.sql+ sql/sql-memo examples.db executable examples
+ sql/3.7.1.sh view
@@ -0,0 +1,9 @@+#! /bin/bash++# Descending sort order++sqlite3 examples.db "+SELECT account_id, product_cd, open_date, avail_balance+FROM account+ORDER BY avail_balance DESC+;"
+ sql/3.7.3.sh view
@@ -0,0 +1,9 @@+#! /bin/bash++# Sorting via numeric placeholders++sqlite3 examples.db "+SELECT emp_id, title, start_date, fname, lname+FROM employee+ORDER BY 2,5+;"
+ sql/3.7.sh view
@@ -0,0 +1,9 @@+#! /bin/bash++# The order by clause++sqlite3 examples.db "+SELECT open_emp_id, product_cd+FROM account+ORDER BY open_emp_id, product_cd+;"
+ sql/4.1.2.sh view
@@ -0,0 +1,9 @@+#! /bin/bash++# Using the not operator++sqlite3 examples.db "+SELECT *+FROM employee+WHERE end_date IS NULL AND (title = 'Teller' OR start_date < '2003-01-01')+;"
+ sql/4.3.2.sh view
@@ -0,0 +1,9 @@+#! /bin/bash++# Range condition with the between operator++sqlite3 examples.db "+SELECT emp_id, fname, lname, start_date FROM employee+WHERE start_date+BETWEEN date('2001-01-01') AND date('2002-12-31')+;"
+ sql/5.1.3.sh view
@@ -0,0 +1,12 @@+#! /bin/bash++# Complex join++sqlite3 examples.db "+SELECT a.account_id, a.cust_id, a.open_date, a.product_cd+FROM account a INNER JOIN employee e ON a.open_emp_id = e.emp_id+INNER JOIN branch b ON e.assigned_branch_id = b.branch_id+WHERE e.start_date <= date('2004-01-01') AND+ (e.title = 'Teller' OR e.title = 'Head Teller') AND+ b.name = 'Woburn Branch'+;"
+ sql/sql-memo view
@@ -0,0 +1,69 @@+# 9.1 What is a subquery?++SELECT account_id, product_cd, cust_id, avail_balance+FROM account+WHERE account_id = (SELECT MAX(account_id)+ FROM account);++# 9.4 Correlated Subqueries++SELECT c.cust_id, c.cust_type_cd, c.city+FROM customer c+WHERE 2 = (SELECT COUNT(*)+ FROM account a+ WHERE a.cust_id = c.cust_id);++----------------------------------------++# (original) Deleting data++DELETE FROM account+WHERE account_id = 2;++# (original) Data modification using equality conditions++DELETE FROM account+WHERE account_id >= 10 AND account_id <= 20;++# 9.4.2 Data manipulation using correlated subqueries++DELETE FROM department d+WHERE NOT EXISTS (SELECT 1+FROM employee e+WHERE e.dept_id = d.dept_id);++----------------------------------------++# (original) Updating data++UPDATE employee+SET lname = 'Bush',+ dept_id = 3+WHERE emp_id = 10;++# 9.4.2 Data Manipulation Using Correlated Subqueries++UPDATE account+SET last_activity_date =+ (SELECT MAX(t.txn_date)+ FROM transaction0 t+ WHERE t.account_id = account.account_id)+WHERE EXISTS (SELECT 1+ FROM transaction0 t+ WHERE t.account_id = account.account_id);++----------------------------------------++# (from script) The insert statement++INSERT INTO branch (branch_id, name, address, city, state, zip)+VALUES (null, 'Headquarters', '3882 Main St.', 'Waltham', 'MA', '02451');++# (from script) The insert statement++INSERT INTO employee (emp_id, fname, lname, start_date, + dept_id, title, assigned_branch_id)+VALUES (null, 'Michael', 'Smith', '2001-06-22', + (SELECT dept_id FROM department WHERE name = 'Administration'), + 'President', + (SELECT branch_id FROM branch WHERE name = 'Headquarters'));
src/DataSource.hs view
@@ -11,7 +11,6 @@ import Database.HDBC.Sqlite3 (Connection, connectSqlite3) import Database.Record.TH (derivingShow) import Language.Haskell.TH (Q, Dec, TypeQ)-import Language.Haskell.TH.Name.CamelCase (ConName) connect :: IO Connection connect = connectSqlite3 "examples.db"
src/examples.hs view
@@ -20,14 +20,14 @@ import Account (Account, account, tableOfAccount) import qualified Branch import Branch (Branch, branch, tableOfBranch)---import qualified Business---import Business (Business, business)+import qualified Business+import Business (business) import qualified Customer import Customer (Customer, customer) import qualified Department import Department (Department, department, tableOfDepartment)---import qualified Individual---import Individual (Individual, individual)+import qualified Individual+import Individual (individual) --import qualified Officer --import Officer (Officer, Officer) import qualified Product@@ -374,11 +374,10 @@ return (phProductCd, ar) product_4_3_3b :: Relation String String-product_4_3_3b = relation' $ do+product_4_3_3b = relation' . placeholder $ \ph -> do p <- query product- (phProductCd,()) <- placeholder (\ph -> wheres $ p ! Product.productTypeCd' .=. ph)- let productCd = p ! Product.productCd'- return (phProductCd, productCd)+ wheres $ p ! Product.productTypeCd' .=. ph+ return $ p ! Product.productCd' -- | sql/4.3.3c.sh --@@ -562,13 +561,12 @@ -- @ -- employee_4_1_2P :: Relation Day Employee-employee_4_1_2P = relation' $ do+employee_4_1_2P = relation' . placeholder $ \ph -> do e <- query employee wheres $ isNothing (e ! Employee.endDate')- (phDay,()) <- placeholder (\ph ->- wheres $ e ! Employee.title' .=. just (value "Teller")- `or'` e ! Employee.startDate' .<. ph)- return (phDay, e)+ wheres $ e ! Employee.title' .=. just (value "Teller")+ `or'` e ! Employee.startDate' .<. ph+ return e -- | sql/4.3.2 --@@ -613,15 +611,15 @@ -- values in order that they appear on the generated SQL. -- employee_4_3_2P :: Relation (Day,Day) Employee2-employee_4_3_2P = relation' $ do+employee_4_3_2P = relation' . placeholder $ \ph -> do e <- query employee- (phDay1,()) <- placeholder (\ph -> wheres $ e ! Employee.startDate' .>=. ph)- (phDay2,()) <- placeholder (\ph -> wheres $ e ! Employee.startDate' .<=. ph)- return (phDay1 >< phDay2,- Employee2 |$| e ! Employee.empId'+ let date = e ! Employee.startDate'+ wheres $ date .>=. ph ! fst'+ wheres $ date .<=. ph ! snd'+ return $ Employee2 |$| e ! Employee.empId' |*| e ! Employee.fname' |*| e ! Employee.lname'- |*| e ! Employee.startDate')+ |*| date data Employee2 = Employee2 { e2EmpId :: Int64@@ -676,10 +674,10 @@ |*| a ! Account.productCd' data Account3 = Account3- { accountId :: Int64- , custId :: Int64- , openDate :: Day- , productCd :: String+ { a3AccountId :: Int64+ , a3CustId :: Int64+ , a3OpenDate :: Day+ , a3ProductCd :: String } deriving (Show) $(makeRecordPersistableDefault ''Account3)@@ -744,7 +742,7 @@ wheres $ a ! Account.custId' .=. c ! Customer.custId' return (a ! Account.accountId') ) id' count- wheres $ just (value 2) .=. ca+ wheres $ just (value (2 :: Int64)) .=. ca return (customer1 c) data Customer1 = Customer1@@ -782,6 +780,25 @@ wheres $ proj ! Account.accountId' .=. value 2 -- |+-- Placeholder version of Generated SQL:+--+-- @+-- DELETE FROM MAIN.account WHERE (account_id = ?)+-- @+--+-- Note: This function is equal to the following:+--+-- @+-- deleteAccount_o1P :: Delete Int64+-- deleteAccount_o1P = typedDelete tableOfAccount . restriction' $ \proj -> do+-- fmap fst $ placeholder (\ph -> wheres $ proj ! Account.accountId' .=. ph)+-- @+--+deleteAccount_o1P :: Delete Int64+deleteAccount_o1P = derivedDelete $ \proj -> do+ fmap fst $ placeholder (\ph -> wheres $ proj ! Account.accountId' .=. ph)++-- | -- (original) Data modification using equality conditions -- -- Handwritten SQL:@@ -803,6 +820,19 @@ wheres $ proj ! Account.accountId' .>=. value 10 wheres $ proj ! Account.accountId' .<=. value 20 +-- |+-- Placeholder version of Generated SQL:+--+-- @+-- DELETE FROM MAIN.account WHERE ((account_id >= ?) AND (account_id <=+-- ?))+-- @+--+deleteAccount_o2P :: Delete (Int64,Int64)+deleteAccount_o2P = derivedDelete $ \proj -> do+ (phMin,()) <- placeholder (\ph -> wheres $ proj ! Account.accountId' .>=. ph)+ (phMax,()) <- placeholder (\ph -> wheres $ proj ! Account.accountId' .<=. ph)+ return (phMin >< phMax) -- | -- 9.4.2 Data manipulation using correlated subqueries@@ -857,6 +887,31 @@ wheres $ proj ! Employee.empId' .=. value 10 -- |+-- Placeholder version of Generated SQL:+--+-- @+-- UPDATE MAIN.employee SET lname = ?, dept_id = ? WHERE (emp_id = ?)+-- @+--+-- Note: This function is equal to the following:+--+-- @+-- updateEmployee_o3P :: Update ((String,Int64),Int64)+-- updateEmployee_o3P = typedUpdate tableOfEmployee . updateTarget' $ \proj -> do+-- (phLname,()) <- placeholder (\ph -> Employee.lname' <-# ph)+-- (phDeptId,()) <- placeholder (\ph -> Employee.deptId' <-# just ph)+-- (phEmpId,()) <- placeholder (\ph -> wheres $ proj ! Employee.empId' .=. ph)+-- return (phLname >< phDeptId >< phEmpId)+-- @+--+updateEmployee_o3P :: Update ((String,Int64),Int64)+updateEmployee_o3P = derivedUpdate $ \proj -> do+ (phLname,()) <- placeholder (\ph -> Employee.lname' <-# ph)+ (phDeptId,()) <- placeholder (\ph -> Employee.deptId' <-# just ph)+ (phEmpId,()) <- placeholder (\ph -> wheres $ proj ! Employee.empId' .=. ph)+ return (phLname >< phDeptId >< phEmpId)++-- | -- 9.4.2 Data Manipulation Using Correlated Subqueries -- -- Handwritten SQL:@@ -1101,7 +1156,72 @@ , e4Title = Just "President" } +-- |+-- Left Outer Join --+-- Handwritten SQL:+--+-- @+-- SELECT a.account_id, a.cust_id, i.fname, i.lname+-- FROM account a LEFT OUTER JOIN individual i+-- ON a.cust_id = i.cust_id+-- @+--+-- Generated SQL:+-- @+-- SELECT ALL T0.account_id AS f0, T0.cust_id AS f1, T1.fname AS f2,+-- T1.lname AS f3 FROM MAIN.account T0 LEFT JOIN MAIN.individual T1 ON+-- (T0.cust_id = T1.cust_id)+-- @+--+account_LeftOuterJoin :: Relation () Account4+account_LeftOuterJoin = relation $ do+ a <- query account+ i <- queryMaybe individual+ on $ just (a ! Account.custId') .=. i ?! Individual.custId'+ return $ Account4 |$| a ! Account.accountId'+ |*| a ! Account.custId'+ |*| i ?! Individual.fname'+ |*| i ?! Individual.lname'++data Account4 = Account4+ { a4AccountId :: Int64+ , a4CustId :: Int64+ , a4Fname :: Maybe String+ , a4Lname :: Maybe String+ } deriving (Show)++$(makeRecordPersistableDefault ''Account4)++-- |+-- Right Outer Join+--+-- Handwritten SQL:+--+-- @+-- SELECT c.cust_id, b.name+-- FROM customer c RIGHT OUTER JOIN business b+-- ON c.cust_id = b.cust_id+-- @+--+-- Generated SQL:+--+-- @+-- SELECT ALL T0.cust_id AS f0, T1.name AS f1 FROM MAIN.customer T0 RIGHT+-- JOIN MAIN.business T1 ON (T0.cust_id = T1.cust_id)+-- @+-- +-- Note: A function using right-out-join can be defined, but unfortunately+-- SQLite3 does not support it.+--+business_RightOuterJoin :: Relation () (Maybe Int64, String)+business_RightOuterJoin = relation $ do+ c <- queryMaybe customer+ b <- query business+ on $ c ?! Customer.custId' .=. just (b ! Business.custId')+ return (c ?! Customer.custId' >< b ! Business.name')++-- -- run and print sql -- @@ -1177,13 +1297,18 @@ run conn () account_9_1 run conn () customer_9_4 runD conn () deleteAccount_o1+ runD conn 2 deleteAccount_o1P runD conn () deleteAccount_o2+ runD conn (10,20) deleteAccount_o2P runD conn () deleteEmployee_9_4_2 runU conn () updateEmployee_o3+ runU conn (("Bush",3),10) updateEmployee_o3P runU conn () updateAccount_9_4_2 runIQ conn () insertBranch_s1 runI conn branch1 insertBranch_s1P runIQ conn () insertEmployee_s2 runIQ conn () insertEmployee_s2U runIQ conn employee4 insertEmployee_s2P+ run conn () account_LeftOuterJoin+ putStrLn $ "SQL: " ++ show business_RightOuterJoin -- run conn () business_RightOuterJoin