37i8zufk

1delimiter ;
2
3use foo_db;
4
5
6drop table if exists user_status;
7
8create table user_status
9(
10status_id tinyint not null primary key,
11name varchar(64) not null,
12msg varchar(255) default null
13)
14engine=InnoDB 
15default charset=latin1 collate latin1_swedish_ci;
16
17insert into user_status values 
18(-99,'banned', 'Account banned'), (-2,'closed', 'Account closed'),
19(-1,'unverified', 'Account not verified'), (0,'suspended','Account suspended'),
20(1,'active', null);
21
22
23drop table if exists users;
24
25create table users
26(
27user_id int unsigned not null auto_increment primary key,
28status_id tinyint not null default -1,
29username varbinary(32) not null,
30password char(32) not null,
31created_date datetime not null,
32last_updated timestamp default current_timestamp on update current_timestamp,
33unique key users_username_idx (username)
34)
35engine=InnoDB 
36default charset=latin1 collate latin1_swedish_ci;
37
38
39delimiter #
40
41create trigger users_before_ins_trig before insert on users
42for each row
43begin
44 set new.created_date = now();
45end#
46
47delimiter ;
48
49drop view if exists users_view;
50
51create view users_view as
52select
53 u.*,
54 s.name as status_name,
55 date_format(u.created_date, '%e-%b-%Y') as created_date_fmt,
56 date_format(u.last_updated, '%e-%b-%Y %H:%i') as last_update_fmt
57from
58 users u
59inner join user_status s on u.status_id = s.status_id;
60
61
62
63set @salt = uuid();
64set @pass = md5(concat('pass', @salt));
65
66insert into users (status_id, username, password) values 
67(1,'guest',@pass),(1,'droid',@pass), (1,'f00',@pass);
68
69
70delimiter ;
71
72drop procedure if exists list_users;
73
74delimiter #
75
76create procedure list_users()
77begin
78	select * from users_view order by user_id;
79end #
80
81delimiter ;
82
83
84
85delimiter ;
86
87drop procedure if exists get_user;
88
89delimiter #
90
91create procedure get_user
92(
93in p_user_id int unsigned
94)
95begin
96	select * from users_view where user_id = p_user_id;
97end #
98
99delimiter ;
100
101
102select @salt as salt;
103
104select * from user_status;
105select * from users;
106select * from users_view;
107
108/*
109
110
111call list_users();
112
113call get_user(1);
114
115call get_user(2);
116
117call get_user(10);
118
119*/