Flask blog app tutorial : Appendix (Tables & MySQL stored procedures / functions)
bogotobogo.com site search:
SQL Tables
Here are the tables used in Flask blog app:
mysql> show tables; +------------------------+ | Tables_in_FlaskBlogApp | +------------------------+ | blog_user | | tbl_blog | | tbl_likes | +------------------------+ 3 rows in set (0.00 sec)
SQL scripts for the tables look like the following.
- blog:
CREATE TABLE `FlaskBlogApp`.`blog_user` ( `user_id` BIGINT NOT NULL AUTO_INCREMENT, `user_name` VARCHAR(45) NULL, `user_username` VARCHAR(45) NULL, `user_password` VARCHAR(85) NULL, PRIMARY KEY (`user_id`)); mysql> desc blog_user; +---------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+----------------+ | user_id | bigint(20) | NO | PRI | NULL | auto_increment | | user_name | varchar(45) | YES | | NULL | | | user_username | varchar(45) | YES | | NULL | | | user_password | varchar(85) | YES | | NULL | | +---------------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
- tbl_blog:
CREATE TABLE `tbl_blog` ( `blog_id` int(11) NOT NULL AUTO_INCREMENT, `blog_title` varchar(45) DEFAULT NULL, `blog_description` varchar(5000) DEFAULT NULL, `blog_user_id` int(11) DEFAULT NULL, `blog_date` datetime DEFAULT NULL, PRIMARY KEY (`blog_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; ALTER TABLE `FlaskBlogApp`.`tbl_blog` ADD COLUMN `blog_file_path` VARCHAR(200) NULL AFTER `blog_date`, ADD COLUMN `blog_accomplished` INT NULL DEFAULT 0 AFTER `blog_file_path`, ADD COLUMN `blog_private` INT NULL DEFAULT 0 AFTER `blog_accomplished`; mysql> desc tbl_blog; +-------------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------+------+-----+---------+----------------+ | blog_id | int(11) | NO | PRI | NULL | auto_increment | | blog_title | varchar(45) | YES | | NULL | | | blog_description | varchar(5000) | YES | | NULL | | | blog_user_id | int(11) | YES | | NULL | | | blog_date | datetime | YES | | NULL | | | blog_file_path | varchar(200) | YES | | NULL | | | blog_accomplished | int(11) | YES | | 0 | | | blog_private | int(11) | YES | | 0 | | +-------------------+---------------+------+-----+---------+----------------+ 8 rows in set (0.01 sec)
- tbl_likes:
CREATE TABLE `FlaskBlogApp`.`tbl_likes` ( `blog_id` INT NOT NULL, `like_id` INT NOT NULL AUTO_INCREMENT, `user_id` INT NULL, `blog_like` INT NULL DEFAULT 0, PRIMARY KEY (`like_id`)); mysql> desc tbl_likes; +-----------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+----------------+ | blog_id | int(11) | NO | | NULL | | | like_id | int(11) | NO | PRI | NULL | auto_increment | | user_id | int(11) | YES | | NULL | | | blog_like | int(11) | YES | | 0 | | +-----------+---------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
SQL stored procedures
Here is the list of the stored procedures:
mysql> SHOW PROCEDURE STATUS WHERE db = 'FlaskBlogApp'; +--------------+-------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ | Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation | +--------------+-------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ | FlaskBlogApp | sp_addBlog | PROCEDURE | root@localhost | 2016-12-12 08:22:42 | 2016-12-12 08:22:42 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_AddUpdateLikes | PROCEDURE | root@localhost | 2016-12-13 21:41:21 | 2016-12-13 21:41:21 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_createUser | PROCEDURE | root@localhost | 2016-12-13 11:20:21 | 2016-12-13 11:20:21 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_deleteBlog | PROCEDURE | root@localhost | 2016-12-05 16:28:27 | 2016-12-05 16:28:27 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_GetAllBlogs | PROCEDURE | root@localhost | 2016-12-13 08:46:38 | 2016-12-13 08:46:38 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_GetBlogById | PROCEDURE | root@localhost | 2016-12-11 13:01:14 | 2016-12-11 13:01:14 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_GetBlogByUser | PROCEDURE | root@localhost | 2016-12-10 08:35:08 | 2016-12-10 08:35:08 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_getLikeStatus | PROCEDURE | root@localhost | 2016-12-13 08:52:50 | 2016-12-13 08:52:50 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_updateBlog | PROCEDURE | root@localhost | 2016-12-11 13:03:07 | 2016-12-11 13:03:07 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_validateLogin | PROCEDURE | root@localhost | 2016-12-04 04:09:38 | 2016-12-04 04:09:38 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | +--------------+-------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ 10 rows in set (0.08 sec)
Or we can get simpler output:
mysql> select db,name,definer,modified from mysql.proc WHERE db = 'FlaskBlogApp'; +--------------+-------------------+----------------+---------------------+ | db | name | definer | modified | +--------------+-------------------+----------------+---------------------+ | FlaskBlogApp | getSum | root@localhost | 2016-12-12 08:23:03 | | FlaskBlogApp | hasLiked | root@localhost | 2016-12-13 08:35:27 | | FlaskBlogApp | sp_addBlog | root@localhost | 2016-12-12 08:22:42 | | FlaskBlogApp | sp_AddUpdateLikes | root@localhost | 2016-12-13 21:41:21 | | FlaskBlogApp | sp_createUser | root@localhost | 2016-12-13 11:20:21 | | FlaskBlogApp | sp_deleteBlog | root@localhost | 2016-12-05 16:28:27 | | FlaskBlogApp | sp_GetAllBlogs | root@localhost | 2016-12-13 08:46:38 | | FlaskBlogApp | sp_GetBlogById | root@localhost | 2016-12-11 13:01:14 | | FlaskBlogApp | sp_GetBlogByUser | root@localhost | 2016-12-10 08:35:08 | | FlaskBlogApp | sp_getLikeStatus | root@localhost | 2016-12-13 08:52:50 | | FlaskBlogApp | sp_updateBlog | root@localhost | 2016-12-11 13:03:07 | | FlaskBlogApp | sp_validateLogin | root@localhost | 2016-12-04 04:09:38 | +--------------+-------------------+----------------+---------------------+ 12 rows in set (0.00 sec)
To see a specific PROCEDURE:
MariaDB [FlaskBlogApp]> SHOW CREATE PROCEDURE sp_deleteBlog; +---------------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation | +---------------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | sp_deleteBlog | | CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_deleteBlog`( IN p_blog_id bigint, IN p_user_id bigint ) BEGIN delete from tbl_blog where blog_id = p_blog_id and blog_user_id = p_user_id; END | utf8 | utf8_general_ci | latin1_swedish_ci | +---------------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1 row in set (0.00 sec)
To see a specific FUNCTION:
MariaDB [FlaskBlogApp]> SHOW CREATE FUNCTION getSum;
Here are the stored procedures:
- getSum:
USE `FlaskBlogApp`; DROP FUNCTION IF EXISTS getSum; DELIMITER $$ CREATE DEFINER=`root`@`localhost` FUNCTION `getSum`( p_blog_id int ) RETURNS int(11) BEGIN select sum(blog_like) into @sm from tbl_likes where blog_id = p_blog_id; RETURN @sm; END$$ DELIMITER ; - hasLiked:
USE `FlaskBlogApp`; DROP Function IF EXISTS `hasLiked`; DELIMITER $$ CREATE DEFINER=`root`@`localhost` FUNCTION `hasLiked`( p_blog int, p_user int ) RETURNS int(11) BEGIN select blog_like into @myval from tbl_likes where blog_id = p_blog and user_id = p_user; RETURN @myval; END$$ DELIMITER ; - sp_addBlog:
USE `FlaskBlogApp`; DROP procedure IF EXISTS `sp_addBlog`; DELIMITER $$ USE `FlaskBlogApp`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_addBlog`( IN p_title varchar(45), IN p_description varchar(1000), IN p_user_id bigint, IN p_file_path varchar(200), IN p_is_private int, IN p_is_done int ) BEGIN insert into tbl_blog( blog_title, blog_description, blog_user_id, blog_date, blog_file_path, blog_private, blog_accomplished ) values ( p_title, p_description, p_user_id, NOW(), p_file_path, p_is_private, p_is_done ); END$$ DELIMITER ; - sp_createUser:
USE `FlaskBlogApp`; DROP procedure IF EXISTS `sp_createUser`; DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_createUser`( IN p_name VARCHAR(20), IN p_username VARCHAR(20), IN p_password VARCHAR(85) ) BEGIN IF ( select exists (select 1 from blog_user where user_username = p_username) ) THEN select 'Username Exists !!'; ELSE insert into blog_user ( user_name, user_username, user_password ) values ( p_name, p_username, p_password ); END IF; END$$ DELIMITER ; - sp_deleteBlog:
USE `FlaskBlogApp`; DROP procedure IF EXISTS `sp_deleteBlog`; DELIMITER $$ USE `FlaskBlogApp`$$ CREATE PROCEDURE `sp_deleteBlog` ( IN p_blog_id bigint, IN p_user_id bigint ) BEGIN delete from tbl_blog where blog_id = p_blog_id and blog_user_id = p_user_id; END$$ DELIMITER ;
- sp_GetAllBlogs:
USE `FlaskBlogApp`; DROP procedure IF EXISTS `sp_GetAllBlogs`; DELIMITER $$ USE `FlaskBlogApp`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_GetAllBlogs`() BEGIN select blog_id,blog_title,blog_description,blog_file_path from tbl_blog where blog_private = 0; END$$ DELIMITER ; - sp_GetBlogById:
USE `FlaskBlogApp`; DROP procedure IF EXISTS `sp_GetBlogById`; DELIMITER $$ USE `FlaskBlogApp`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_GetBlogById`( IN p_blog_id bigint, In p_user_id bigint ) BEGIN select blog_id,blog_title,blog_description,blog_file_path,blog_private,blog_accomplished from tbl_blog where blog_id = p_blog_id and blog_user_id = p_user_id; END$$ DELIMITER ;
- sp_GetBlogByUser:
USE `FlaskBlogApp`; DROP procedure IF EXISTS `sp_GetBlogByUser`; DELIMITER $$ USE `FlaskBlogApp`$$ CREATE PROCEDURE `sp_GetBlogByUser` ( IN p_user_id bigint ) BEGIN select * from tbl_blog where blog_user_id = p_user_id; END$$ DELIMITER ; - sp_getLikeStatus:
USE `FlaskBlogApp`; DROP procedure IF EXISTS `sp_getLikeStatus`; DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_getLikeStatus`( IN p_blog_id int, IN p_user_id int ) BEGIN select getSum(p_blog_id),hasLiked(p_blog_id,p_user_id); END$$ DELIMITER ; - sp_AddUpdateLikes:
USE `FlaskBlogApp`; DROP procedure IF EXISTS `sp_AddUpdateLikes`; DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_AddUpdateLikes`( p_blog_id int, p_user_id int, p_like int ) BEGIN if (select exists (select 1 from tbl_likes where blog_id = p_blog_id and user_id = p_user_id)) then update tbl_likes set blog_like = p_like where blog_id = p_blog_id and user_id = p_user_id; else insert into tbl_likes( blog_id, user_id, blog_like ) values( p_blog_id, p_user_id, p_like ); end if; END$$ DELIMITER ; - sp_updateBlog:
USE `FlaskBlogApp`; DROP procedure IF EXISTS `sp_updateBlog`; DELIMITER $$ USE `FlaskBlogApp`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_updateBlog`( IN p_title varchar(45), IN p_description varchar(1000), IN p_blog_id bigint, In p_user_id bigint, IN p_file_path varchar(200), IN p_is_private int, IN p_is_done int ) BEGIN update tbl_blog set blog_title = p_title, blog_description = p_description, blog_file_path = p_file_path, blog_private = p_is_private, blog_accomplished = p_is_done where blog_id = p_blog_id and blog_user_id = p_user_id; END$$ DELIMITER ; - sp_validateLogin:
USE `FlaskBlogApp`; DROP procedure IF EXISTS `sp_validateLogin`; DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_validateLogin`( IN p_username VARCHAR(20) ) BEGIN select * from blog_user where user_username = p_username; END$$ DELIMITER ;
Ph.D. / Golden Gate Ave, San Francisco / Seoul National Univ / Carnegie Mellon / UC Berkeley / DevOps / Deep Learning / Visualization
My YouTube channel