Working with Databases in C++: Creating a Social Following App

Exploring MySQL stored procedures and the SOCI library

Image for post
Image for post

Introduction

Application Walkthrough

Image for post
Image for post
Launching the application, selecting a user and inputting 1 as the next command.
Image for post
Image for post
Listing a user’ s followees and inputting 2 as the next command.
Image for post
Image for post
Listing a user’ s followers and inputting 3 as the next command.
Image for post
Image for post
Viewing a user’s messages and inputting 4 as the next command.
Image for post
Image for post
Sending a message to another user and inputting 5 as the next command.
Image for post
Image for post
Selecting another user and inputting 9 as the next command.
Image for post
Image for post
Displaying the application’s command list and inputting 0 as the next command.

Downloading Project Files

git clone https://github.com/danebulat/soci-follow-app.git

Creating a Database Environment

Database Schema

Image for post
Image for post
The database schema for our project’s backend.

Database Setup Script

The complete MySQL database setup script.
source /path/to/soci-follow-app/scripts/database_setup.sql

Database Setup Walkthrough

CREATE DATABASE social_db;
USE social_db;
/* Create a new user and set user's grants */
CREATE USER developer@localhost IDENTIFIED BY 'Secure123';
GRANT ALL ON social_db.* TO developer@localhost;
GRANT EXECUTE ON ocial_db.* TO developer@localhost;
SET DEFAULT ROLE ALL TO developer@localhost;
/* Verify the developer user's grants */
SHOW GRANTS FOR developer@localhost;
CREATE TABLE users ( 
id INT AUTO_INCREMENT,
email VARCHAR(128) UNIQUE NOT NULL,
first_name VARCHAR(128) NOT NULL,
last_name VARCHAR(128) NOT NULL,
active BOOL DEFAULT TRUE,
PRIMARY KEY(id)
) ENGINE=INNODB;
CREATE TABLE messages ( 
id INT AUTO_INCREMENT,
to_id INT NOT NULL,
from_id INT NOT NULL,
message TEXT NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY (to_id) REFERENCES users(id)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=INNODB;
CREATE TABLE followings ( 
follower INT NOT NULL,
followee INT NOT NULL,
PRIMARY KEY(follower, followee),
FOREIGN KEY (follower) REFERENCES users(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY (followee) REFERENCES users(id)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=INNODB;

Developing MySQL Stored Procedures

source /path/to/soci-follow-app/scripts/stored_procedures.sql

Stored Procedure for Seeding the Users Table

The full implementation of the SeedUsers stored procedure.
DELIMITER $$
...
END $$
DELIMITER ;
CREATE PROCEDURE SeedUsers(IN users_to_insert INT)
BEGIN
...
END $$
/* Declare stored procedure variables */
DECLARE next_id INT;
DECLARE i INT DEFAULT 0;
/* Set stored procedure variables */
IF (SELECT COUNT(id) FROM users) = 0 THEN
SET next_id = 1;
ELSE
SET next_id = (SELECT MAX(id) FROM users) + 1;
END IF;
user_loop: LOOP 
/* Leave loop if all users have been inserted */
IF i = users_to_insert THEN
LEAVE user_loop;
END IF;
/* Insert new user into users table */
INSERT INTO users(first_name, last_name, email)
VALUES('seed',
CONCAT('user_', next_id),
CONCAT('seed.user_', next_id, '@email.com'));
/* Increment i and next_id variables */
SET i = i + 1;
SET next_id = next_id + 1;
END LOOP;

Stored Procedure for Generating Two Random User IDs

The full implementation of the GetFollowIds stored procedure.
CREATE PROCEDURE GetFollowIds(
OUT follower_id INT, OUT followee_id INT)
BEGIN
...
END $$
/* Calculate number of users in table */
DECLARE max_id INT;
SET max_id = (SELECT MAX(id) FROM users);
/* Generate random integer between 1 and total_users */
SET follower_id = FLOOR(RAND() * (max_id) + (1));
SET followee_id = FLOOR(RAND() * (max_id) + (1));
/* If ids are the same, generate a new followee_id */ 
WHILE follower_id = followee_id DO
SET followee_id = FLOOR(RAND() * (max_id) + (1));
END WHILE;

Stored Procedure for Seeding the Followings Table

The full implementation of the SeedFollowings stored procedure.
CREATE PROCEDURE SeedFollowings(IN iterations INT)
BEGIN
....
END $$
/* We must generate two user ids to attempt a following */ 
DECLARE follower_id INT;
DECLARE followee_id INT;
DECLARE i INT DEFAULT 0;
outer_while: WHILE i < iterations DO
....
END WHILE outer_while;
REPEAT 
/* Get two random user ids */
CALL GetFollowIds(follower_id, followee_id);
/* Recalculate user ids if either one doesn't exist */
inner_while: WHILE (
(SELECT COUNT(id) FROM users WHERE id=follower_id) = 0 OR
(SELECT COUNT(id) FROM users WHERE id=followee_id) = 0) DO

CALL GetFollowIds(follower_id, followee_id);
END WHILE inner_while;
/* Repeat until the new following relationship is unique */
UNTIL ((SELECT COUNT(*) FROM followings
WHERE follower=follower_id
AND followee=followee_id) = 0)
END REPEAT;
/* Insert new following in database */ 
INSERT INTO followings(follower, followee)
VALUES(follower_id, followee_id);
/* Increment iterator */
SET i = i + 1;
/* Display new following ids */
SELECT follower_id, followee_id;

Invoking Stored Procedures

DELETE FROM users WHERE first_name='seed';
SELECT routine_name FROM information_schema.routines
WHERE routine_type='PROCEDURE'
AND routine_schema='social_db';
Image for post
Image for post
Listing the names of stored procedure accessible to the social_db database.
/* Call stored procedure */
CALL SeedUsers(500);
/* Verify user count */
SELECT COUNT(*) FROM users;
/* Verify columns */
SELECT * FROM users ORDER BY id DESC LIMIT 20;
Image for post
Image for post
Verifying the seed data in the users table.
/* Call stored procedure */
CALL SeedFollowings(5000);
/* Verify following count */
SELECT COUNT(*) FROM followings;
/* Verify columns */
SELECT * FROM followings LIMIT 200, 20;
Image for post
Image for post
Verifying the seed data in the followings table.
SELECT u2.id, u2.email, u2.first_name, u2.last_name, u2.active
FROM users u1
INNER JOIN followings f ON u1.id=f.followee
INNER JOIN users u2 ON u2.id=f.follower
WHERE u1.id=221;
SELECT u2.id, u2.email, u2.first_name, u2.last_name, u2.active
FROM users u1
INNER JOIN followings f ON u1.id=f.follower
INNER JOIN users u2 ON u2.id=f.followee
WHERE u1.id=221;

Developing a C++ Application

Source Files Overview

The main Function

try {
...
}
catch (const mysql_soci_error& e) {
cerr << "MySQL error: " << e.err_num_ << " "
<< e.what() << endl;
}
catch (const exception& e) {
cerr << "Exception: " << e.what() << " " << e.what() << endl;
}
// Connect to database
session sql(mysql, "db=social_db user=developer
password=Secure123");
// Select user from database
User user;
UserUtils::fetch_user_by_email(sql, user);
// Verify user was found and display user information
if (!UserUtils::verify_user(user)) {
return 1;
}
// Input options for user
AppInput::display_input_options();
// Start application loop for processing user input
bool exit = false;
while (!exit) {
exit = AppInput::process_user_input(sql, user);
}

Processing User Input with the AppInput Class

static void display_input_options() {
cout << endl << "\t1 - List followees (...)"
<< endl << "\t2 - List followers (...)"
<< endl << "\t3 - View messages"
<< endl << "\t4 - Send a message"
<< endl << "\t5 - Select another user"
<< endl << "\t9 - Display input options"
<< endl << "\t0 - Exit"
<< endl << endl;
}
enum InputOptions {
EXIT = 0,
FETCH_FOLLOWEES, // 1
FETCH_FOLLOWERS, // 2
VIEW_MESSAGES, // 3
SEND_MESSAGE, // 4
CHANGE_USER, // 5
DISPLAY_INPUT_OPTIONS = 9
};
static bool process_user_input(session& sql, User& user) {
// Get input
cout << "\t> ";
int input = 0;
cin >> input;
cout << "\tYou have selected: " << input << endl;
// Process input
switch (input) {
case EXIT: {
return true;
break;
}
case FETCH_FOLLOWEES: {
UserUtils::fetch_followees(sql, user);
break;
}
case FETCH_FOLLOWERS: {
UserUtils::fetch_followers(sql, user);
break;
}
case DISPLAY_INPUT_OPTIONS: {
display_input_options();
break;
}
case VIEW_MESSAGES: {
UserUtils::display_user_messages(sql, user);
break;
}
case SEND_MESSAGE: {
UserUtils::send_message_to_user(sql, user);
break;
}
case CHANGE_USER: {
UserUtils::fetch_user_by_email(sql, user);
UserUtils::verify_user(user);
display_input_options();
break;
}
default: {
cout << "\tError!" << endl;
break;
}
} // end switch
return false;
}

The User class and ORM (Object Relational Mapping)

class User {
public: // Maps to the 'users' table columns:
int id; // id INT
string email; // email VARCHAR(128)
string first_name; // first_name VARCHAR(128)
string last_name; // last_name VARCHAR(128)
int active; // active BOOL DEFAULT TRUE
private:
...
};
namespace soci {    template<>
struct type_conversion<User> {
// ORM requires that base_type is the values object
typedef values base_type;
...
// Specialization of soci::type_conversion structure
namespace soci {
template<>
struct type_conversion<User> {
// ORM requires that the base_type is values object
typedef values base_type;
// Database -> User Object
static void from_base(values const& v,
indicator ind, User& user) {
user.id = v.get<int>("id");
user.email = v.get<string>("email");
user.first_name = v.get<string>("first_name");
user.last_name = v.get<string>("last_name");
user.active = v.get<int>("active");
}
// User Object -> Database
static void to_base(const User& user, values& v,
indicator& ind) {
v.set("id", user.id);
v.set("email", user.email);
v.set("first_name", user.first_name);
v.set("last_name", user.last_name);
v.set("active", user.active);
}
};
};
User user;
string email_address = "dane.bulat@email.com";
sql << "SELECT * FROM users WHERE email=:e",
use(email_address, "e"), into(user);

Database Interaction with the UserUtils Class

rowset<row> rs = (sql.prepare << "SELECT ...
void UserUtils::fetch_followers(session& sql, const User& user) {
cout << endl;
rowset<row> rs = (sql.prepare <<
"SELECT u2.id, u2.email, u2.first_name, u2.last_name, "
"u2.active "
"FROM users u1 "
"INNER JOIN followings f ON u1.id=f.followee "
"INNER JOIN users u2 ON u2.id=f.follower "
"WHERE u1.id=:id",
use(user.id, "id"));
display_user_rowset(rs);
}
void UserUtils::fetch_followees(session& sql, const User& user) {
cout << endl;
rowset<row> rs = (sql.prepare <<
"SELECT u2.id, u2.email, u2.first_name, u2.last_name, "
"u2.active "
"FROM users u1 "
"INNER JOIN followings f ON u1.id=f.follower "
"INNER JOIN users u2 ON u2.id=f.followee "
"WHERE u1.id=:id",
use(user.id, "id"));
display_user_rowset(rs);
}
void UserUtils::display_user_rowset(const rowset<row>& rs) {

for (rowset<row>::const_iterator it = rs.begin();
it != rs.end(); ++it) {

const row& row = *it;

cout << "\tID: " << row.get<int>(0) << " (active: "
<< row.get<int>(4) << ")" << endl
<< "\tEmail: " << row.get<string>(1) << endl
<< "\tName: " << row.get<string>(2) << " "
<< row.get<string>(3) << endl
<< "\t------" << endl << endl;
}
}

Compiling and Running Our Application

UNIX Environments (Mac OS X, Linux, UNIX)

export CPATH="/usr/local/include:/usr/local/mysql/include:/path/to/soci-follow-app/include"
export LIBRARY_PATH="/usr/local/lib:/usr/local/mysql/lib"
export LD_LIBRARY_PATH="/usr/local/lib:/usr/local/mysql/lib"
g++ -std=c++11 src/main.cpp src/user.cpp src/user_utils.cpp 
-o bin/main -lsoci_core -lsoci_mysql -ldl -lmysqlclient
./bin/main
Image for post
Image for post
Running our final executable.

In Summary

MSc. Programmer and fan of open source software.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store