relational-record-examples (empty) → 0.1.0.0

raw patch · 25 files changed

+1174/−0 lines, 25 filesdep +HDBCdep +HDBC-sessiondep +HDBC-sqlite3setup-changedbinary-added

Dependencies added: HDBC, HDBC-session, HDBC-sqlite3, base, names-th, persistable-record, relational-query, relational-query-HDBC, template-haskell, time

Files

• LICENSE +30/−0
• Setup.hs +2/−0
• examples.db binary
• relational-record-examples.cabal +50/−0
• sql/4.3.3a.sh +7/−0
• sql/4.3.3b.sh +9/−0
• sql/4.3.3c.sh +8/−0
• sql/5.1.2a.sh +7/−0
• sql/5.3a.sh +7/−0
• sql/6.4.1a.sh +12/−0
• sql/8.1a.sh +8/−0
• sql/add.sql +641/−0
• src/Account.hs +7/−0
• src/Branch.hs +7/−0
• src/Business.hs +7/−0
• src/Customer.hs +7/−0
• src/DataSource.hs +35/−0
• src/Department.hs +7/−0
• src/Employee.hs +7/−0
• src/Individual.hs +7/−0
• src/Officer.hs +7/−0
• src/Product.hs +9/−0
• src/ProductType.hs +7/−0
• src/Transaction.hs +11/−0
• src/examples.hs +275/−0

@@ -0,0 +1,30 @@+Copyright (c) 2014, Shohei Murayama++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 Shohei Murayama nor the names of other+      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.

@@ -0,0 +1,2 @@+import Distribution.Simple+main = defaultMain

binary file changed (absent → 69632 bytes)

@@ -0,0 +1,50 @@+name:                relational-record-examples+version:             0.1.0.0+synopsis:            Examples of Haskell Relationa Record+description:         Provides examples of Haskell Relational Record+license:             BSD3+license-file:        LICENSE+author:              Kazu Yamamoto <kazu@iij.ad.jp>+                     Shohei Murayama <shohei.murayama@gmail.com>+maintainer:          Shohei Murayama <shohei.murayama@gmail.com>+category:            Database+build-type:          Simple+cabal-version:       >=1.10++extra-source-files:    sql/4.3.3a.sh+                       sql/4.3.3b.sh+                       sql/4.3.3c.sh+                       sql/5.1.2a.sh+                       sql/5.3a.sh+                       sql/6.4.1a.sh+                       sql/8.1a.sh+                       sql/add.sql+                       examples.db++executable examples+  hs-source-dirs:      src+  main-is:             examples.hs+  other-modules:       Account+                       Branch+                       Business+                       Customer+                       DataSource+                       Department+                       Employee+                       Individual+                       Officer+                       Product+                       ProductType+                       Transaction+  build-depends:       base <5+                     , HDBC+                     , HDBC-session+                     , HDBC-sqlite3+                     , names-th+                     , persistable-record+                     , relational-query+                     , relational-query-HDBC+                     , template-haskell+                     , time+  default-language:    Haskell2010+

@@ -0,0 +1,7 @@+#! /bin/bash++sqlite3 examples.db "+SELECT account_id, product_cd, cust_id, avail_balance+FROM account+WHERE product_cd IN ('CHK', 'SAV', 'CD', 'MM')+;"

@@ -0,0 +1,9 @@+#! /bin/bash++sqlite3 examples.db "+SELECT account_id, product_cd, cust_id, avail_balance+FROM account+WHERE product_cd IN (SELECT product_cd FROM product+WHERE product_type_cd = 'ACCOUNT')+;"+

@@ -0,0 +1,8 @@+#! /bin/bash++sqlite3 examples.db "+SELECT account_id, product_cd, cust_id, avail_balance+FROM account+WHERE product_cd NOT IN ('CHK', 'SAV', 'CD', 'MM')+;"+

@@ -0,0 +1,7 @@+#! /bin/bash++sqlite3 examples.db "+SELECT e.fname, e.lname, d.name+FROM employee e INNER JOIN department d+USING (dept_id)+;"

@@ -0,0 +1,7 @@+#! /bin/bash++sqlite3 exmaples.db "+SELECT e.fname, e.lname, e_mgr.fname mgr_fname, e_mgr.lname mgr_lname+FROM employee e INNER JOIN employee e_mgr+ON e.superior_emp_id = e_mgr.emp_id+;"

@@ -0,0 +1,12 @@+#! /bin/bash++sqlite3 examples.db "+SELECT emp_id, assigned_branch_id+FROM employee+WHERE title = 'Teller'+UNION+SELECT open_emp_id, open_branch_id+FROM account+WHERE product_cd = 'SAV'+ORDER BY emp_id+;"

@@ -0,0 +1,8 @@+#! /bin/bash++sqlite3 examples.db "+SELECT open_emp_id, COUNT(*) how_many+FROM account+GROUP BY open_emp_id+ORDER BY open_emp_id+;"

@@ -0,0 +1,641 @@+/* LearningSQLExample.sql+   from http://examples.oreilly.com/9780596007270/LearningSQLExample.sql+   Modified for SQLite3.++   % sqlite3 examples.db < add.sql+*/++/* begin table creation */++create table department+ (dept_id integer primary key autoincrement not null,+  name varchar(20) not null+ );++create table branch+ (branch_id integer primary key autoincrement not null,+  name varchar(20) not null,+  address varchar(30),+  city varchar(20),+  state varchar(2),+  zip varchar(12)+ );++create table employee+ (emp_id integer primary key autoincrement not null,+  fname varchar(20) not null,+  lname varchar(20) not null,+  start_date date not null,+  end_date date,+  superior_emp_id integer,+  dept_id integer,+  title varchar(20),+  assigned_branch_id integer,+  constraint fk_e_emp_id +    foreign key (superior_emp_id) references employee (emp_id),+  constraint fk_dept_id+    foreign key (dept_id) references department (dept_id),+  constraint fk_e_branch_id+    foreign key (assigned_branch_id) references branch (branch_id)+ );++create table product_type+ (product_type_cd varchar(10) not null,+  name varchar(50) not null,+  constraint pk_product_type primary key (product_type_cd)+ );++create table product+ (product_cd varchar(10) not null,+  name varchar(50) not null,+  product_type_cd varchar(10) not null,+  date_offered date,+  date_retired date,+  constraint fk_product_type_cd foreign key (product_type_cd) +    references product_type (product_type_cd),+  constraint pk_product primary key (product_cd)+ );++create table customer+ (cust_id integer primary key autoincrement not null,+  fed_id varchar(12) not null,+  cust_type_cd text not null,+  address varchar(30),+  city varchar(20),+  state varchar(20),+  postal_code varchar(10),+  check (cust_type_cd = 'I' or cust_type_cd = 'B')+ );++create table individual+ (cust_id integer primary key autoincrement not null,+  fname varchar(30) not null,+  lname varchar(30) not null,+  birth_date date,+  constraint fk_i_cust_id foreign key (cust_id)+    references customer (cust_id)+ );++create table business+ (cust_id integer primary key autoincrement not null,+  name varchar(40) not null,+  state_id varchar(10) not null,+  incorp_date date,+  constraint fk_b_cust_id foreign key (cust_id)+    references customer (cust_id)+ );++create table officer+ (officer_id integer primary key autoincrement not null,+  cust_id integer not null,+  fname varchar(30) not null,+  lname varchar(30) not null,+  title varchar(20),+  start_date date not null,+  end_date date,+  constraint fk_o_cust_id foreign key (cust_id)+    references business (cust_id)+ );++create table account+ (account_id integer primary key autoincrement not null,+  product_cd varchar(10) not null,+  cust_id integer not null,+  open_date date not null,+  close_date date,+  last_activity_date date,+  status text not null,+  open_branch_id integer,+  open_emp_id integer,+  avail_balance float(10,2),+  pending_balance float(10,2),+  check(status = 'ACTIVE' or status = 'CLOSED' or status = 'FROZEN')+  constraint fk_product_cd foreign key (product_cd)+    references product (product_cd),+  constraint fk_a_cust_id foreign key (cust_id)+    references customer (cust_id),+  constraint fk_a_branch_id foreign key (open_branch_id)+    references branch (branch_id),+  constraint fk_a_emp_id foreign key (open_emp_id)+    references employee (emp_id)+ );++create table transaction0+ (txn_id integer primary key autoincrement not null,+  txn_date datetime not null,+  account_id integer not null,+  txn_type_cd text not null,+  amount double(10,2) not null,+  teller_emp_id integer,+  execution_branch_id integer,+  funds_avail_date datetime,+  check (txn_type_cd = 'DBT' or txn_type_cd = 'CDT'),+  constraint fk_t_account_id foreign key (account_id)+    references account (account_id),+  constraint fk_teller_emp_id foreign key (teller_emp_id)+    references employee (emp_id),+  constraint fk_exec_branch_id foreign key (execution_branch_id)+    references branch (branch_id)+ );++/* end table creation */++/* begin data population */++/* department data */+insert into department (dept_id, name)+values (null, 'Operations');+insert into department (dept_id, name)+values (null, 'Loans');+insert into department (dept_id, name)+values (null, 'Administration');++/* branch data */+insert into branch (branch_id, name, address, city, state, zip)+values (null, 'Headquarters', '3882 Main St.', 'Waltham', 'MA', '02451');+insert into branch (branch_id, name, address, city, state, zip)+values (null, 'Woburn Branch', '422 Maple St.', 'Woburn', 'MA', '01801');+insert into branch (branch_id, name, address, city, state, zip)+values (null, 'Quincy Branch', '125 Presidential Way', 'Quincy', 'MA', '02169');+insert into branch (branch_id, name, address, city, state, zip)+values (null, 'So. NH Branch', '378 Maynard Ln.', 'Salem', 'NH', '03079');++/* employee data */+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'));+insert into employee (emp_id, fname, lname, start_date, +  dept_id, title, assigned_branch_id)+values (null, 'Susan', 'Barker', '2002-09-12', +  (select dept_id from department where name = 'Administration'), +  'Vice President', +  (select branch_id from branch where name = 'Headquarters'));+insert into employee (emp_id, fname, lname, start_date, +  dept_id, title, assigned_branch_id)+values (null, 'Robert', 'Tyler', '2000-02-09',+  (select dept_id from department where name = 'Administration'), +  'Treasurer', +  (select branch_id from branch where name = 'Headquarters'));+insert into employee (emp_id, fname, lname, start_date, +  dept_id, title, assigned_branch_id)+values (null, 'Susan', 'Hawthorne', '2002-04-24', +  (select dept_id from department where name = 'Operations'), +  'Operations Manager', +  (select branch_id from branch where name = 'Headquarters'));+insert into employee (emp_id, fname, lname, start_date, +  dept_id, title, assigned_branch_id)+values (null, 'John', 'Gooding', '2003-11-14', +  (select dept_id from department where name = 'Loans'), +  'Loan Manager', +  (select branch_id from branch where name = 'Headquarters'));+insert into employee (emp_id, fname, lname, start_date, +  dept_id, title, assigned_branch_id)+values (null, 'Helen', 'Fleming', '2004-03-17', +  (select dept_id from department where name = 'Operations'), +  'Head Teller', +  (select branch_id from branch where name = 'Headquarters'));+insert into employee (emp_id, fname, lname, start_date, +  dept_id, title, assigned_branch_id)+values (null, 'Chris', 'Tucker', '2004-09-15', +  (select dept_id from department where name = 'Operations'), +  'Teller', +  (select branch_id from branch where name = 'Headquarters'));+insert into employee (emp_id, fname, lname, start_date, +  dept_id, title, assigned_branch_id)+values (null, 'Sarah', 'Parker', '2002-12-02', +  (select dept_id from department where name = 'Operations'), +  'Teller', +  (select branch_id from branch where name = 'Headquarters'));+insert into employee (emp_id, fname, lname, start_date, +  dept_id, title, assigned_branch_id)+values (null, 'Jane', 'Grossman', '2002-05-03', +  (select dept_id from department where name = 'Operations'), +  'Teller', +  (select branch_id from branch where name = 'Headquarters'));+insert into employee (emp_id, fname, lname, start_date, +  dept_id, title, assigned_branch_id)+values (null, 'Paula', 'Roberts', '2002-07-27', +  (select dept_id from department where name = 'Operations'), +  'Head Teller', +  (select branch_id from branch where name = 'Woburn Branch'));+insert into employee (emp_id, fname, lname, start_date, +  dept_id, title, assigned_branch_id)+values (null, 'Thomas', 'Ziegler', '2000-10-23', +  (select dept_id from department where name = 'Operations'), +  'Teller', +  (select branch_id from branch where name = 'Woburn Branch'));+insert into employee (emp_id, fname, lname, start_date, +  dept_id, title, assigned_branch_id)+values (null, 'Samantha', 'Jameson', '2003-01-08', +  (select dept_id from department where name = 'Operations'), +  'Teller', +  (select branch_id from branch where name = 'Woburn Branch'));+insert into employee (emp_id, fname, lname, start_date, +  dept_id, title, assigned_branch_id)+values (null, 'John', 'Blake', '2000-05-11', +  (select dept_id from department where name = 'Operations'), +  'Head Teller', +  (select branch_id from branch where name = 'Quincy Branch'));+insert into employee (emp_id, fname, lname, start_date, +  dept_id, title, assigned_branch_id)+values (null, 'Cindy', 'Mason', '2002-08-09', +  (select dept_id from department where name = 'Operations'), +  'Teller', +  (select branch_id from branch where name = 'Quincy Branch'));+insert into employee (emp_id, fname, lname, start_date, +  dept_id, title, assigned_branch_id)+values (null, 'Frank', 'Portman', '2003-04-01', +  (select dept_id from department where name = 'Operations'), +  'Teller', +  (select branch_id from branch where name = 'Quincy Branch'));+insert into employee (emp_id, fname, lname, start_date, +  dept_id, title, assigned_branch_id)+values (null, 'Theresa', 'Markham', '2001-03-15', +  (select dept_id from department where name = 'Operations'), +  'Head Teller', +  (select branch_id from branch where name = 'So. NH Branch'));+insert into employee (emp_id, fname, lname, start_date, +  dept_id, title, assigned_branch_id)+values (null, 'Beth', 'Fowler', '2002-06-29', +  (select dept_id from department where name = 'Operations'), +  'Teller', +  (select branch_id from branch where name = 'So. NH Branch'));+insert into employee (emp_id, fname, lname, start_date, +  dept_id, title, assigned_branch_id)+values (null, 'Rick', 'Tulman', '2002-12-12', +  (select dept_id from department where name = 'Operations'), +  'Teller', +  (select branch_id from branch where name = 'So. NH Branch'));++/* create data for self-referencing foreign key 'superior_emp_id' */+create temporary table emp_tmp as+select emp_id, fname, lname from employee;++update employee set superior_emp_id =+ (select emp_id from emp_tmp where lname = 'Smith' and fname = 'Michael')+where ((lname = 'Barker' and fname = 'Susan')+  or (lname = 'Tyler' and fname = 'Robert'));+update employee set superior_emp_id =+ (select emp_id from emp_tmp where lname = 'Tyler' and fname = 'Robert')+where lname = 'Hawthorne' and fname = 'Susan';+update employee set superior_emp_id =+ (select emp_id from emp_tmp where lname = 'Hawthorne' and fname = 'Susan')+where ((lname = 'Gooding' and fname = 'John')+  or (lname = 'Fleming' and fname = 'Helen')+  or (lname = 'Roberts' and fname = 'Paula') +  or (lname = 'Blake' and fname = 'John') +  or (lname = 'Markham' and fname = 'Theresa')); +update employee set superior_emp_id =+ (select emp_id from emp_tmp where lname = 'Fleming' and fname = 'Helen')+where ((lname = 'Tucker' and fname = 'Chris') +  or (lname = 'Parker' and fname = 'Sarah') +  or (lname = 'Grossman' and fname = 'Jane'));  +update employee set superior_emp_id =+ (select emp_id from emp_tmp where lname = 'Roberts' and fname = 'Paula')+where ((lname = 'Ziegler' and fname = 'Thomas')  +  or (lname = 'Jameson' and fname = 'Samantha'));   +update employee set superior_emp_id =+ (select emp_id from emp_tmp where lname = 'Blake' and fname = 'John')+where ((lname = 'Mason' and fname = 'Cindy')   +  or (lname = 'Portman' and fname = 'Frank'));    +update employee set superior_emp_id =+ (select emp_id from emp_tmp where lname = 'Markham' and fname = 'Theresa')+where ((lname = 'Fowler' and fname = 'Beth')   +  or (lname = 'Tulman' and fname = 'Rick'));    ++drop table emp_tmp;++/* product type data */+insert into product_type (product_type_cd, name)+values ('ACCOUNT','Customer Accounts');+insert into product_type (product_type_cd, name)+values ('LOAN','Individual and Business Loans');+insert into product_type (product_type_cd, name)+values ('INSURANCE','Insurance Offerings');++/* product data */+insert into product (product_cd, name, product_type_cd, date_offered)+values ('CHK','checking account','ACCOUNT','2000-01-01');+insert into product (product_cd, name, product_type_cd, date_offered)+values ('SAV','savings account','ACCOUNT','2000-01-01');+insert into product (product_cd, name, product_type_cd, date_offered)+values ('MM','money market account','ACCOUNT','2000-01-01');+insert into product (product_cd, name, product_type_cd, date_offered)+values ('CD','certificate of deposit','ACCOUNT','2000-01-01');+insert into product (product_cd, name, product_type_cd, date_offered)+values ('MRT','home mortgage','LOAN','2000-01-01');+insert into product (product_cd, name, product_type_cd, date_offered)+values ('AUT','auto loan','LOAN','2000-01-01');+insert into product (product_cd, name, product_type_cd, date_offered)+values ('BUS','business line of credit','LOAN','2000-01-01');+insert into product (product_cd, name, product_type_cd, date_offered)+values ('SBL','small business loan','LOAN','2000-01-01');++/* residential customer data */+insert into customer (cust_id, fed_id, cust_type_cd,+  address, city, state, postal_code)+values (null, '111-11-1111', 'I', '47 Mockingbird Ln', 'Lynnfield', 'MA', '01940');+insert into individual (cust_id, fname, lname, birth_date)+select cust_id, 'James', 'Hadley', '1972-04-22' from customer+where fed_id = '111-11-1111';+insert into customer (cust_id, fed_id, cust_type_cd,+  address, city, state, postal_code)+values (null, '222-22-2222', 'I', '372 Clearwater Blvd', 'Woburn', 'MA', '01801');+insert into individual (cust_id, fname, lname, birth_date)+select cust_id, 'Susan', 'Tingley', '1968-08-15' from customer+where fed_id = '222-22-2222';+insert into customer (cust_id, fed_id, cust_type_cd,+  address, city, state, postal_code)+values (null, '333-33-3333', 'I', '18 Jessup Rd', 'Quincy', 'MA', '02169');+insert into individual (cust_id, fname, lname, birth_date)+select cust_id, 'Frank', 'Tucker', '1958-02-06' from customer+where fed_id = '333-33-3333';+insert into customer (cust_id, fed_id, cust_type_cd,+  address, city, state, postal_code)+values (null, '444-44-4444', 'I', '12 Buchanan Ln', 'Waltham', 'MA', '02451');+insert into individual (cust_id, fname, lname, birth_date)+select cust_id, 'John', 'Hayward', '1966-12-22' from customer+where fed_id = '444-44-4444';+insert into customer (cust_id, fed_id, cust_type_cd,+  address, city, state, postal_code)+values (null, '555-55-5555', 'I', '2341 Main St', 'Salem', 'NH', '03079');+insert into individual (cust_id, fname, lname, birth_date)+select cust_id, 'Charles', 'Frasier', '1971-08-25' from customer+where fed_id = '555-55-5555';+insert into customer (cust_id, fed_id, cust_type_cd,+  address, city, state, postal_code)+values (null, '666-66-6666', 'I', '12 Blaylock Ln', 'Waltham', 'MA', '02451');+insert into individual (cust_id, fname, lname, birth_date)+select cust_id, 'John', 'Spencer', '1962-09-14' from customer+where fed_id = '666-66-6666';+insert into customer (cust_id, fed_id, cust_type_cd,+  address, city, state, postal_code)+values (null, '777-77-7777', 'I', '29 Admiral Ln', 'Wilmington', 'MA', '01887');+insert into individual (cust_id, fname, lname, birth_date)+select cust_id, 'Margaret', 'Young', '1947-03-19' from customer+where fed_id = '777-77-7777';+insert into customer (cust_id, fed_id, cust_type_cd,+  address, city, state, postal_code)+values (null, '888-88-8888', 'I', '472 Freedom Rd', 'Salem', 'NH', '03079');+insert into individual (cust_id, fname, lname, birth_date)+select cust_id, 'Louis', 'Blake', '1977-07-01' from customer+where fed_id = '888-88-8888';+insert into customer (cust_id, fed_id, cust_type_cd,+  address, city, state, postal_code)+values (null, '999-99-9999', 'I', '29 Maple St', 'Newton', 'MA', '02458');+insert into individual (cust_id, fname, lname, birth_date)+select cust_id, 'Richard', 'Farley', '1968-06-16' from customer+where fed_id = '999-99-9999';++/* corporate customer data */+insert into customer (cust_id, fed_id, cust_type_cd,+  address, city, state, postal_code)+values (null, '04-1111111', 'B', '7 Industrial Way', 'Salem', 'NH', '03079');+insert into business (cust_id, name, state_id, incorp_date)+select cust_id, 'Chilton Engineering', '12-345-678', '1995-05-01' from customer+where fed_id = '04-1111111';+insert into officer (officer_id, cust_id, fname, lname,+  title, start_date)+select null, cust_id, 'John', 'Chilton', 'President', '1995-05-01'+from customer+where fed_id = '04-1111111';+insert into customer (cust_id, fed_id, cust_type_cd,+  address, city, state, postal_code)+values (null, '04-2222222', 'B', '287A Corporate Ave', 'Wilmington', 'MA', '01887');+insert into business (cust_id, name, state_id, incorp_date)+select cust_id, 'Northeast Cooling Inc.', '23-456-789', '2001-01-01' from customer+where fed_id = '04-2222222';+insert into officer (officer_id, cust_id, fname, lname,+  title, start_date)+select null, cust_id, 'Paul', 'Hardy', 'President', '2001-01-01'+from customer+where fed_id = '04-2222222';+insert into customer (cust_id, fed_id, cust_type_cd,+  address, city, state, postal_code)+values (null, '04-3333333', 'B', '789 Main St', 'Salem', 'NH', '03079');+insert into business (cust_id, name, state_id, incorp_date)+select cust_id, 'Superior Auto Body', '34-567-890', '2002-06-30' from customer+where fed_id = '04-3333333';+insert into officer (officer_id, cust_id, fname, lname,+  title, start_date)+select null, cust_id, 'Carl', 'Lutz', 'President', '2002-06-30'+from customer+where fed_id = '04-3333333';+insert into customer (cust_id, fed_id, cust_type_cd,+  address, city, state, postal_code)+values (null, '04-4444444', 'B', '4772 Presidential Way', 'Quincy', 'MA', '02169');+insert into business (cust_id, name, state_id, incorp_date)+select cust_id, 'AAA Insurance Inc.', '45-678-901', '1999-05-01' from customer+where fed_id = '04-4444444';+insert into officer (officer_id, cust_id, fname, lname,+  title, start_date)+select null, cust_id, 'Stanley', 'Cheswick', 'President', '1999-05-01'+from customer+where fed_id = '04-4444444';++/* residential account data */+insert into account (account_id, product_cd, cust_id, open_date,+  last_activity_date, status, open_branch_id,+  open_emp_id, avail_balance, pending_balance)+select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',+  e.branch_id, e.emp_id, a.avail, a.pend+from customer c cross join + (select b.branch_id, e.emp_id +  from branch b inner join employee e on e.assigned_branch_id = b.branch_id+  where b.city = 'Woburn' limit 1) e+  cross join+ (select 'CHK' prod_cd, '2000-01-15' open_date, '2005-01-04' last_date,+    1057.75 avail, 1057.75 pend union all+  select 'SAV' prod_cd, '2000-01-15' open_date, '2004-12-19' last_date,+    500.00 avail, 500.00 pend union all+  select 'CD' prod_cd, '2004-06-30' open_date, '2004-06-30' last_date,+    3000.00 avail, 3000.00 pend) a+where c.fed_id = '111-11-1111';+insert into account (account_id, product_cd, cust_id, open_date,+  last_activity_date, status, open_branch_id,+  open_emp_id, avail_balance, pending_balance)+select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',+  e.branch_id, e.emp_id, a.avail, a.pend+from customer c cross join + (select b.branch_id, e.emp_id +  from branch b inner join employee e on e.assigned_branch_id = b.branch_id+  where b.city = 'Woburn' limit 1) e+  cross join+ (select 'CHK' prod_cd, '2001-03-12' open_date, '2004-12-27' last_date,+    2258.02 avail, 2258.02 pend union all+  select 'SAV' prod_cd, '2001-03-12' open_date, '2004-12-11' last_date,+    200.00 avail, 200.00 pend) a+where c.fed_id = '222-22-2222';+insert into account (account_id, product_cd, cust_id, open_date,+  last_activity_date, status, open_branch_id,+  open_emp_id, avail_balance, pending_balance)+select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',+  e.branch_id, e.emp_id, a.avail, a.pend+from customer c cross join + (select b.branch_id, e.emp_id +  from branch b inner join employee e on e.assigned_branch_id = b.branch_id+  where b.city = 'Quincy' limit 1) e+  cross join+ (select 'CHK' prod_cd, '2002-11-23' open_date, '2004-11-30' last_date,+    1057.75 avail, 1057.75 pend union all+  select 'MM' prod_cd, '2002-12-15' open_date, '2004-12-05' last_date,+    2212.50 avail, 2212.50 pend) a+where c.fed_id = '333-33-3333';+insert into account (account_id, product_cd, cust_id, open_date,+  last_activity_date, status, open_branch_id,+  open_emp_id, avail_balance, pending_balance)+select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',+  e.branch_id, e.emp_id, a.avail, a.pend+from customer c cross join + (select b.branch_id, e.emp_id +  from branch b inner join employee e on e.assigned_branch_id = b.branch_id+  where b.city = 'Waltham' limit 1) e+  cross join+ (select 'CHK' prod_cd, '2003-09-12' open_date, '2005-01-03' last_date,+    534.12 avail, 534.12 pend union all+  select 'SAV' prod_cd, '2000-01-15' open_date, '2004-10-24' last_date,+    767.77 avail, 767.77 pend union all+  select 'MM' prod_cd, '2004-09-30' open_date, '2004-11-11' last_date,+    5487.09 avail, 5487.09 pend) a+where c.fed_id = '444-44-4444';+insert into account (account_id, product_cd, cust_id, open_date,+  last_activity_date, status, open_branch_id,+  open_emp_id, avail_balance, pending_balance)+select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',+  e.branch_id, e.emp_id, a.avail, a.pend+from customer c cross join + (select b.branch_id, e.emp_id +  from branch b inner join employee e on e.assigned_branch_id = b.branch_id+  where b.city = 'Salem' limit 1) e+  cross join+ (select 'CHK' prod_cd, '2004-01-27' open_date, '2005-01-05' last_date,+    2237.97 avail, 2897.97 pend) a+where c.fed_id = '555-55-5555';+insert into account (account_id, product_cd, cust_id, open_date,+  last_activity_date, status, open_branch_id,+  open_emp_id, avail_balance, pending_balance)+select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',+  e.branch_id, e.emp_id, a.avail, a.pend+from customer c cross join + (select b.branch_id, e.emp_id +  from branch b inner join employee e on e.assigned_branch_id = b.branch_id+  where b.city = 'Waltham' limit 1) e+  cross join+ (select 'CHK' prod_cd, '2002-08-24' open_date, '2004-11-29' last_date,+    122.37 avail, 122.37 pend union all+  select 'CD' prod_cd, '2004-12-28' open_date, '2004-12-28' last_date,+    10000.00 avail, 10000.00 pend) a+where c.fed_id = '666-66-6666';+insert into account (account_id, product_cd, cust_id, open_date,+  last_activity_date, status, open_branch_id,+  open_emp_id, avail_balance, pending_balance)+select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',+  e.branch_id, e.emp_id, a.avail, a.pend+from customer c cross join + (select b.branch_id, e.emp_id +  from branch b inner join employee e on e.assigned_branch_id = b.branch_id+  where b.city = 'Woburn' limit 1) e+  cross join+ (select 'CD' prod_cd, '2004-01-12' open_date, '2004-01-12' last_date,+    5000.00 avail, 5000.00 pend) a+where c.fed_id = '777-77-7777';+insert into account (account_id, product_cd, cust_id, open_date,+  last_activity_date, status, open_branch_id,+  open_emp_id, avail_balance, pending_balance)+select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',+  e.branch_id, e.emp_id, a.avail, a.pend+from customer c cross join + (select b.branch_id, e.emp_id +  from branch b inner join employee e on e.assigned_branch_id = b.branch_id+  where b.city = 'Salem' limit 1) e+  cross join+ (select 'CHK' prod_cd, '2001-05-23' open_date, '2005-01-03' last_date,+    3487.19 avail, 3487.19 pend union all+  select 'SAV' prod_cd, '2001-05-23' open_date, '2004-10-12' last_date,+    387.99 avail, 387.99 pend) a+where c.fed_id = '888-88-8888';+insert into account (account_id, product_cd, cust_id, open_date,+  last_activity_date, status, open_branch_id,+  open_emp_id, avail_balance, pending_balance)+select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',+  e.branch_id, e.emp_id, a.avail, a.pend+from customer c cross join + (select b.branch_id, e.emp_id +  from branch b inner join employee e on e.assigned_branch_id = b.branch_id+  where b.city = 'Waltham' limit 1) e+  cross join+ (select 'CHK' prod_cd, '2003-07-30' open_date, '2004-12-15' last_date,+    125.67 avail, 125.67 pend union all+  select 'MM' prod_cd, '2004-10-28' open_date, '2004-10-28' last_date,+    9345.55 avail, 9845.55 pend union all+  select 'CD' prod_cd, '2004-06-30' open_date, '2004-06-30' last_date,+    1500.00 avail, 1500.00 pend) a+where c.fed_id = '999-99-9999';++/* corporate account data */+insert into account (account_id, product_cd, cust_id, open_date,+  last_activity_date, status, open_branch_id,+  open_emp_id, avail_balance, pending_balance)+select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',+  e.branch_id, e.emp_id, a.avail, a.pend+from customer c cross join + (select b.branch_id, e.emp_id +  from branch b inner join employee e on e.assigned_branch_id = b.branch_id+  where b.city = 'Salem' limit 1) e+  cross join+ (select 'CHK' prod_cd, '2002-09-30' open_date, '2004-12-15' last_date,+    23575.12 avail, 23575.12 pend union all+  select 'BUS' prod_cd, '2002-10-01' open_date, '2004-08-28' last_date,+    0 avail, 0 pend) a+where c.fed_id = '04-1111111';+insert into account (account_id, product_cd, cust_id, open_date,+  last_activity_date, status, open_branch_id,+  open_emp_id, avail_balance, pending_balance)+select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',+  e.branch_id, e.emp_id, a.avail, a.pend+from customer c cross join + (select b.branch_id, e.emp_id +  from branch b inner join employee e on e.assigned_branch_id = b.branch_id+  where b.city = 'Woburn' limit 1) e+  cross join+ (select 'BUS' prod_cd, '2004-03-22' open_date, '2004-11-14' last_date,+    9345.55 avail, 9345.55 pend) a+where c.fed_id = '04-2222222';+insert into account (account_id, product_cd, cust_id, open_date,+  last_activity_date, status, open_branch_id,+  open_emp_id, avail_balance, pending_balance)+select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',+  e.branch_id, e.emp_id, a.avail, a.pend+from customer c cross join + (select b.branch_id, e.emp_id +  from branch b inner join employee e on e.assigned_branch_id = b.branch_id+  where b.city = 'Salem' limit 1) e+  cross join+ (select 'CHK' prod_cd, '2003-07-30' open_date, '2004-12-15' last_date,+    38552.05 avail, 38552.05 pend) a+where c.fed_id = '04-3333333';+insert into account (account_id, product_cd, cust_id, open_date,+  last_activity_date, status, open_branch_id,+  open_emp_id, avail_balance, pending_balance)+select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',+  e.branch_id, e.emp_id, a.avail, a.pend+from customer c cross join + (select b.branch_id, e.emp_id +  from branch b inner join employee e on e.assigned_branch_id = b.branch_id+  where b.city = 'Quincy' limit 1) e+  cross join+ (select 'SBL' prod_cd, '2004-02-22' open_date, '2004-12-17' last_date,+    50000.00 avail, 50000.00 pend) a+where c.fed_id = '04-4444444';++/* put $100 in all checking/savings accounts on date account opened */+insert into transaction0 (txn_id, txn_date, account_id, txn_type_cd,+  amount, funds_avail_date)+select null, a.open_date, a.account_id, 'CDT', 100, a.open_date+from account a+where a.product_cd IN ('CHK','SAV','CD','MM');++/* end data population */

@@ -0,0 +1,7 @@+{-# LANGUAGE TemplateHaskell, MultiParamTypeClasses, FlexibleInstances #-}++module Account where++import DataSource (defineTable)++$(defineTable "account")

@@ -0,0 +1,7 @@+{-# LANGUAGE TemplateHaskell, MultiParamTypeClasses, FlexibleInstances #-}++module Branch where++import DataSource (defineTable)++$(defineTable "branch")

@@ -0,0 +1,7 @@+{-# LANGUAGE TemplateHaskell, MultiParamTypeClasses, FlexibleInstances #-}++module Business where++import DataSource (defineTable)++$(defineTable "business")

@@ -0,0 +1,7 @@+{-# LANGUAGE TemplateHaskell, MultiParamTypeClasses, FlexibleInstances #-}++module Customer where++import DataSource (defineTable)++$(defineTable "customer")

@@ -0,0 +1,35 @@+{-# LANGUAGE TemplateHaskell #-}++module DataSource (+    connect, convTypes, defineTable+  ) where++import Data.Time (Day, LocalTime)+import Database.HDBC.Query.TH (defineTableFromDB)+import Database.HDBC.Schema.Driver (typeMap)+import Database.HDBC.Schema.SQLite3 (driverSQLite3)+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"++convTypes :: [(String, TypeQ)]+convTypes =+    [ ("float", [t|Double|])+    , ("date", [t|Day|])+    , ("datetime", [t|LocalTime|])+    , ("double", [t|Double|])+    , ("varchar", [t|String|])+    ]++defineTable :: String -> Q [Dec]+defineTable tableName =+  defineTableFromDB+    connect+    (driverSQLite3 { typeMap = convTypes }) -- overwrite the default type map with yours+    "main" -- schema name, ignored by SQLite+    tableName+    [derivingShow]

@@ -0,0 +1,7 @@+{-# LANGUAGE TemplateHaskell, MultiParamTypeClasses, FlexibleInstances #-}++module Department where++import DataSource (defineTable)++$(defineTable "department")

@@ -0,0 +1,7 @@+{-# LANGUAGE TemplateHaskell, MultiParamTypeClasses, FlexibleInstances #-}++module Employee where++import DataSource (defineTable)++$(defineTable "employee")

@@ -0,0 +1,7 @@+{-# LANGUAGE TemplateHaskell, MultiParamTypeClasses, FlexibleInstances #-}++module Individual where++import DataSource (defineTable)++$(defineTable "individual")

@@ -0,0 +1,7 @@+{-# LANGUAGE TemplateHaskell, MultiParamTypeClasses, FlexibleInstances #-}++module Officer where++import DataSource (defineTable)++$(defineTable "officer")

@@ -0,0 +1,9 @@+{-# LANGUAGE TemplateHaskell, MultiParamTypeClasses, FlexibleInstances #-}++module Product where++import DataSource (defineTable)+import Prelude hiding (product)++$(defineTable "product")+

@@ -0,0 +1,7 @@+{-# LANGUAGE TemplateHaskell, MultiParamTypeClasses, FlexibleInstances #-}++module ProductType where++import DataSource (defineTable)++$(defineTable "product_type")

@@ -0,0 +1,11 @@+{-# LANGUAGE TemplateHaskell, MultiParamTypeClasses, FlexibleInstances #-}++module Transaction where++import DataSource (defineTable)++$(defineTable "transaction0")++type Transaction = Transaction0++transaction = transaction0

@@ -0,0 +1,275 @@+{-# LANGUAGE MonadComprehensions #-}+{-# LANGUAGE FlexibleContexts #-}+{-# LANGUAGE TemplateHaskell #-}+{-# LANGUAGE MultiParamTypeClasses #-}+{-# LANGUAGE FlexibleInstances #-}++import Database.Record++import Database.Relational.Query+import Database.HDBC (IConnection, SqlValue)+import Database.HDBC.Query.TH (makeRecordPersistableDefault)+import Data.Int (Int64)++import qualified Account+import Account (Account, account)+--import qualified Customer+--import Customer (Customer, customer)+--import qualified Individual+--import Individual (Individual, individual)+import qualified Product+import Product (product)+--import qualified ProductType+--import ProductType (ProductType, productType)+--import qualified Branch+--import Branch (Branch, Branch)+--import qualified Officer+--import Officer (Officer, Officer)+--import qualified Transaction+--import Transaction (Transaction, transaction)+--import qualified Business+--import Business (Business, business)+import qualified Department+import Department (Department, department)+--import qualified Product+--import Product (Product, product)+import qualified Employee+import Employee (Employee, employee)++import DataSource (connect)+import Database.HDBC.Record.Query (runQuery)+import Database.HDBC.Session (withConnectionIO, handleSqlError')++import Prelude hiding (product)++allAccount :: Relation () Account+allAccount = relation $ query account++-- sql/4.3.3a.sh+--+-- @+--   SELECT account_id, product_cd, cust_id, avail_balance+--   FROM LEARNINGSQL.account+--   WHERE product_cd IN ('CHK', 'SAV', 'CD', 'MM')+-- @+--+account1 :: Relation () Account+account1 = relation $ do+  a  <- query account+  wheres $ a ! Account.productCd' `in'` values ["CHK", "SAV", "CD", "MM"]+  return a++account1T :: Relation () (((Int64, String), Int64), Maybe Double)+account1T = relation $ do+  a  <- query account+  wheres $ a ! Account.productCd' `in'` values ["CHK", "SAV", "CD", "MM"]+  return $ a ! Account.accountId' >< a ! Account.productCd' >< a ! Account.custId' >< a ! Account.availBalance'++account1R :: Relation () Account1+account1R = relation $ do+  a  <- query account+  wheres $ a ! Account.productCd' `in'` values ["CHK", "SAV", "CD", "MM"]+  return $ Account1 |$| a ! Account.accountId'+                    |*| a ! Account.productCd'+                    |*| a ! Account.custId'+                    |*| a ! Account.availBalance'++data Account1 = Account1+  { a1AccountId :: Int64+  , a1ProductCd :: String+  , a1CustId :: Int64+  , a1AvailBalance :: Maybe Double+  } deriving (Show)++$(makeRecordPersistableDefault ''Account1)++-- | sql/5.1.2a.sh+--+-- @+--   SELECT e.fname, e.lname, d.name+--   FROM LEARNINGSQL.employee e INNER JOIN LEARNINGSQL.department d+--   USING (dept_id)+-- @+--+join1 :: Relation () (Employee, Department)+join1 = relation $ do+  e  <- query employee+  d  <- query department+  on $ e ! Employee.deptId' .=. just (d ! Department.deptId')+  return $ e >< d++join1' :: Relation () ((String, String), String)+join1' = relation $ do+  e  <- query employee+  d  <- query department+  on $ e ! Employee.deptId' .=. just (d ! Department.deptId')+  return $ e ! Employee.fname' >< e ! Employee.lname' >< d ! Department.name'++-- | sql/5.3a.sh+--+-- @+--   SELECT e.fname, e.lname, e_mgr.fname mgr_fname, e_mgr.lname mgr_lname+--   FROM LEARNINGSQL.employee e INNER JOIN LEARNINGSQL.employee e_mgr+--   ON e.superior_emp_id = e_mgr.emp_id+-- @+--+selfJoin1 :: Relation () (Employee, Employee)+selfJoin1 = relation $ do+  e  <- query employee+  m  <- query employee+  on $ e ! Employee.superiorEmpId' .=. just (m ! Employee.empId')+  return $ e >< m++selfJoin1' :: Relation () ((String, String), (String, String))+selfJoin1' = relation $ do+  e  <- query employee+  m  <- query employee+  on $ e ! Employee.superiorEmpId' .=. just (m ! Employee.empId')+  let emp = e ! Employee.fname' >< e ! Employee.lname'+  let mgr = m ! Employee.fname' >< m ! Employee.lname'+  return $ emp >< mgr++-- | sql/6.4.1a.sh+--+-- @+--   SELECT emp_id, assigned_branch_id+--   FROM LEARNINGSQL.employee+--   WHERE title = 'Teller'+--   UNION+--   SELECT open_emp_id, open_branch_id+--   FROM LEARNINGSQL.account+--   WHERE product_cd = 'SAV'+--   ORDER BY open_emp_id+-- @+--+employee1 :: Relation () (Maybe Int64, Maybe Int64)+employee1 = relation $ do+  e  <- query employee+  wheres $ e ! Employee.title' .=. just (value "Teller")+  return $ just (e ! Employee.empId') >< e ! Employee.assignedBranchId'++account2 :: Relation () (Maybe Int64, Maybe Int64)+account2 = relation $ do+  a  <- query account+  wheres $ a ! Account.productCd' .=. value "SAV"+  return $ a ! Account.openEmpId' >< a ! Account.openBranchId'++union1 :: Relation () (Maybe Int64, Maybe Int64)+union1 = relation $ do+  ea <- query $ employee1 `union` account2+  asc $ ea ! fst'+  return ea++union1' :: Relation () (Maybe Int64, Maybe Int64)+union1' = relation (do+    e  <- query employee+    wheres $ e ! Employee.title' .=. just (value "Teller")+    return $ just (e ! Employee.empId') >< e ! Employee.assignedBranchId'+  ) `union` relation (do+    a  <- query account+    wheres $ a ! Account.productCd' .=. value "SAV"+    return $ a ! Account.openEmpId' >< a ! Account.openBranchId'+  )++-- | sql/8.1a.sh+--+-- @+--   SELECT open_emp_id, COUNT(*) how_many+--   FROM LEARNINGSQL.account+--   GROUP BY open_emp_id+--   ORDER BY open_emp_id+-- @+--+group1 :: Relation () (Maybe Int64, Int64)+group1 = aggregateRelation $ do+  a  <- query account+  g  <- groupBy $ a ! Account.openEmpId'+  asc $ g ! id'+  return $ g >< count (a ! Account.accountId')++-- | sql/4.3.3b.sh+--+-- @+--   SELECT account_id, product_cd, cust_id, avail_balance+--   FROM account+--   WHERE product_cd IN (SELECT product_cd FROM product+--   WHERE product_type_cd = 'ACCOUNT')+-- @++product1 :: Relation String String+product1 = relation' $ do+  p <- query product+  (phProductCd,()) <- placeholder (\ph -> wheres $ p ! Product.productTypeCd' .=. ph)+  let productCd = p ! Product.productCd'+  return (phProductCd, productCd)++account3 :: Relation String Account+account3 = relation' $ do+  a <- query account+  (phProductCd,p) <- queryList' product1+  wheres $ a ! Account.productCd' `in'` p+  return (phProductCd, a)++account3T :: Relation String (((Int64, String), Int64), Maybe Double)+account3T = relation' $ do+  a <- query account+  (phProductCd,p) <- queryList' product1+  wheres $ a ! Account.productCd' `in'` p+  let at = a ! Account.accountId' >< a ! Account.productCd' >< a ! Account.custId' >< a ! Account.availBalance'+  return (phProductCd, at)++account3R :: Relation String Account1+account3R = relation' $ do+  a <- query account+  (phProductCd,p) <- queryList' product1+  wheres $ a ! Account.productCd' `in'` p+  let ar = Account1 |$| a ! Account.accountId'+                    |*| a ! Account.productCd'+                    |*| a ! Account.custId'+                    |*| a ! Account.availBalance'+  return (phProductCd, ar)++-- sql/4.3.3c.sh+--+-- @+--   SELECT account_id, product_cd, cust_id, avail_balance+--   FROM LEARNINGSQL.account+--   WHERE product_cd NOT IN ('CHK', 'SAV', 'CD', 'MM')+-- @+--+account4 :: Relation () Account+account4 = relation $ do+  a  <- query account+  wheres $ not' (a ! Account.productCd' `in'` values ["CHK", "SAV", "CD", "MM"])+  return a++--+-- run and print sql+--++run :: (Show a, IConnection conn, FromSql SqlValue a, ToSql SqlValue p)+       => conn -> p -> Relation p a -> IO ()+run conn param rel = do+  putStrLn $ "SQL: " ++ show rel+  records <- runQuery conn (relationalQuery rel) param+  mapM_ print records+  putStrLn ""++main :: IO ()+main = handleSqlError' $ withConnectionIO connect $ \conn -> do+  run conn () allAccount+  run conn () account1+  run conn () account1T+  run conn () account1R+  run conn () join1+  run conn () join1'+  run conn () selfJoin1+  run conn () selfJoin1'+  run conn () union1+  run conn () union1'+  run conn () group1+  run conn "ACCOUNT" account3+  run conn "ACCOUNT" account3T+  run conn "ACCOUNT" account3R+  run conn () account4+