2bUwcqLp

1drop table if exists payments;
2drop table if exists payment_types;
3drop table if exists customers;
4
5create table payment_types
6(
7pay_type_id tinyint unsigned not null auto_increment primary key,
8name varchar(255) unique not null
9)
10engine=innodb;
11
12create table customers
13(
14cust_id int unsigned not null auto_increment primary key,
15email varchar(512) unique not null,
16total_amount_paid decimal(10,2) not null default 0
17)
18engine=innodb;
19
20create table payments
21(
22pay_id int unsigned not null auto_increment primary key,
23cust_id int unsigned not null,
24pay_type_id tinyint unsigned not null,
25pay_date datetime not null,
26amount decimal(10,2) not null default 0,
27key (pay_date),
28foreign key (cust_id) references customers(cust_id),
29foreign key (pay_type_id) references payment_types(pay_type_id)
30)
31engine=innodb;
32
33drop view if exists payments_view;
34create view payments_view as 
35select
36 p.pay_id,
37 p.pay_date,
38 p.pay_type_id,
39 pt.name as pay_type_name,
40 p.amount,
41 c.cust_id, 
42 c.email
43from
44 customers c
45inner join payments p on c.cust_id = p.cust_id
46inner join payment_types pt on p.pay_type_id = pt.pay_type_id;
47
48delimiter ;
49
50drop procedure if exists insert_payment;
51
52delimiter #
53
54create procedure insert_payment
55(
56in p_email varchar(512),
57in p_pay_type_id tinyint unsigned,
58in p_amount decimal(10,2)
59)
60begin
61
62declare v_cust_id int unsigned default 0;
63
64  if not exists (select 1 from customers where email = p_email) then
65    insert into customers (email) values (p_email);
66    set v_cust_id = last_insert_id();
67  else
68    select cust_id into v_cust_id from customers where email = p_email;
69  end if;
70
71  insert into payments (cust_id, pay_type_id, amount) 
72   values (v_cust_id, p_pay_type_id, p_amount);
73  
74  select last_insert_id() as new_pay_id;
75  
76end#
77
78create trigger payments_before_ins_trig before insert on payments
79for each row
80begin
81 set new.pay_date = now();
82 
83 update customers set total_amount_paid = total_amount_paid + new.amount
84  where cust_id = new.cust_id;
85end#
86
87delimiter ;
88
89insert into payment_types (name) values ('visa'),('mastercard'),('cash');
90
91insert into customers (email) values ('foo@bar.com'),('bar@foo.com'),('pants@elis.com');
92
93call insert_payment('foo@bar.com',1,100);
94call insert_payment('bar@foo.com',2,200);
95call insert_payment('pants@elis.com',3,300);
96call insert_payment('another@customer.com',1,400);
97call insert_payment('another@customer.com',2,500);
98
99
100 mysql> select * from payment_types order by pay_type_id;
101    +-------------+------------+
102    | pay_type_id | name       |
103    +-------------+------------+
104    |           1 | visa       |
105    |           2 | mastercard |
106    |           3 | cash       |
107    +-------------+------------+
108    3 rows in set (0.00 sec)
109    
110    mysql> select * from customers order by cust_id;
111    +---------+----------------+-------------------+
112    | cust_id | email          | total_amount_paid |
113    +---------+----------------+-------------------+
114    |       1 | foo@bar.com    |            600.00 |
115    |       2 | bar@foo.com    |            900.00 |
116    |       3 | pants@elis.com |            600.00 |
117    +---------+----------------+-------------------+
118    3 rows in set (0.00 sec)
119    
120    mysql> select * from payments order by pay_id;
121    +--------+---------+-------------+---------------------+--------+
122    | pay_id | cust_id | pay_type_id | pay_date            | amount |
123    +--------+---------+-------------+---------------------+--------+
124    |      1 |       1 |           1 | 2011-03-19 01:04:49 | 100.00 |
125    |      2 |       1 |           2 | 2011-03-19 01:04:49 | 200.00 |
126    |      3 |       1 |           3 | 2011-03-19 01:04:49 | 300.00 |
127    |      4 |       2 |           3 | 2011-03-19 01:04:49 | 400.00 |
128    |      5 |       2 |           2 | 2011-03-19 01:04:49 | 500.00 |
129    |      6 |       3 |           1 | 2011-03-19 01:04:49 | 600.00 |
130    +--------+---------+-------------+---------------------+--------+
131    6 rows in set (0.00 sec)
132
133    mysql> select * from payments_view order by pay_id desc;
134    +--------+---------------------+-------------+---------------+--------+---------+----------------+
135    | pay_id | pay_date            | pay_type_id | pay_type_name | amount | cust_id| email          |
136    +--------+---------------------+-------------+---------------+--------+---------+----------------+
137    |      6 | 2011-03-19 01:04:49 |           1 | visa          | 600.00 |       3| pants@elis.com |
138    |      5 | 2011-03-19 01:04:49 |           2 | mastercard    | 500.00 |       2| bar@foo.com    |
139    |      4 | 2011-03-19 01:04:49 |           3 | cash          | 400.00 |       2| bar@foo.com    |
140    |      3 | 2011-03-19 01:04:49 |           3 | cash          | 300.00 |       1| foo@bar.com    |
141    |      2 | 2011-03-19 01:04:49 |           2 | mastercard    | 200.00 |       1| foo@bar.com    |
142    |      1 | 2011-03-19 01:04:49 |           1 | visa          | 100.00 |       1| foo@bar.com    |
143    +--------+---------------------+-------------+---------------+--------+---------+----------------+
144    6 rows in set (0.00 sec)