Skip to content

Database Stored Procedures

Jump to bottom
Allen Jiang edited this page Jul 22, 2019 · 17 revisions

The following stored procedures are accessible to server user execute privilege from the MySQL retirement-simulation-study database.

Table of Contents

User Authentication

Session Authentication

auth_salt

CALL auth_salt(
    <{IN user_email varchar(255)}>
);

Given a registered user email, returns the salt for the password associated with that account.

auth_login

CALL auth_login(
    <{IN user_email varchar(255)}>,
    <{IN user_password char(60)}>
);

Given a valid user email and BCrypt password hash combination, returns a client ID and corresponding authentication code for token use.

auth_logout

CALL auth_logout(
    <{IN token_client_id int(10) unsigned}>,
    <{IN token_auth_code char(40)}>
);

Given a valid client ID and corresponding authentication code, revokes the authentication validity of that combination.

auth_purge

CALL auth_purge(
    <{IN user_email varchar(255)}>,
    <{IN user_password char(60)}>
);

Given a valid user email and BCrypt password hash combination, revokes all client ID/authentication code combinations.

User Accounts

user_create

CALL user_create(
    <{IN user_email varchar(255)}>,
    <{IN user_password char(60)}>,
    <{IN user_title varchar(20)}>,
    <{IN user_fname varchar(60)}>,
    <{IN user_initial varchar(1)}>,
    <{IN user_lname varchar(60)}>
);

Creates a new user. Does nothing if the user email is already registered.

user_delete

CALL user_delete(
    <{IN user_email varchar(255)}>,
    <{IN user_password varchar(60)}>
);

Given a valid user email and BCrypt password hash combination, deletes a user's data completely in a manner compliant with GDPR deletion recommendations.

user_exists

CALL user_exists(<{IN user_email varchar(255)}>);

Given an email, returns whether or not that email address is registered.

user_read

CALL user_read(
    <{IN user_email varchar(255)}>,
    <{IN user_password varchar(60)}>
);

Given a valid user email and BCrypt password hash combination, returns user basic information (email, name fields).

user_update_email

CALL user_update_email(
    <{IN user_email varchar(255)}>,
    <{IN user_password varchar(60)}>,
    <{IN user_new_email varchar(60)}>
);

Given a valid user email and BCrypt password hash combination, as well as a new email address that is not already registered to another account, changes the user's email to the new email address.

user_update_name

CALL user_update_name(
    <{IN user_email varchar(255)}>,
    <{IN user_password varchar(60)}>,
    <{IN user_title varchar(20)}>,
    <{IN user_fname varchar(60)}>,
    <{IN user_initial varchar(1)}>,
    <{IN user_lname varchar(60)}>
);

Given a valid user email and BCrypt password hash combination, as well as fields for new name, changes the registered name of that user.

user_update_password

CALL user_update_password(
    <{IN user_email varchar(255)}>,
    <{IN user_password varchar(60)}>,
    <{IN user_new_password varchar(60)}>
);

Given a valid user email and BCrypt password hash combination, and a new BCrypt password hash, changes the user password hash to the new hash.

Surveys

Participant Data Survey

survey_pd_write

CALL survey_pd_write(
    <{IN token_client_id int(10) unsigned}>,
    <{IN token_auth_code char(40)}>,
    <{IN survey_pd_address_1 varchar(255)}>,
    <{IN survey_pd_address_2 varchar(255)}>,
    <{IN survey_pd_addr_city varchar(40)}>,
    <{IN survey_pd_addr_state varchar(2)}>,
    <{IN survey_pd_addr_zip varchar(10)}>,
    <{IN survey_pd_birthdate date}>,
    <{IN survey_pd_income_min decimal(15,2) unsigned}>,
    <{IN survey_pd_income_max decimal(15,2) unsigned}>,
    <{IN survey_pd_marital varchar(45)}>
);

Given a valid client ID and corresponding authentication code, and responses to participant data survey, records those responses for the corresponding user. Inserts a new record if the user's participant data survey record does not already exist.

survey_pd_read

CALL survey_pd_read(
    <{IN token_client_id int(10) unsigned}>,
    <{IN token_auth_code char(40)}>
);

Given a valid client ID and corresponding authentication code, returns a user's participant data survey response if exists, beginning with a bit field indicating whether the data has been prerecorded.

Retirement Survey

survey_rt_write

CALL survey_rt_write(
    <{IN token_client_id int(10) unsigned}>,
    <{IN token_auth_code char(40)}>,
    <{IN survey_rt_age int(10) unsigned}>,
    <{IN survey_rt_goal decimal(15,2) unsigned}>,
    <{IN survey_rt_curr_savings decimal(15,2) unsigned}>,
    <{IN survey_rt_employer_deposit double unsigned}>,
    <{IN survey_rt_lifetime_concern bit(1)}>,
    <{IN survey_rt_ss bit(1)}>
);

Given a valid client ID and corresponding authentication code, and responses to retirement survey, records those responses for the corresponding user. Inserts a new record if the user's retirement survey record does not already exist.

survey_rt_read

CALL survey_rt_read(
    <{IN token_client_id int(10) unsigned}>,
    <{IN token_auth_code char(40)}>
);

Given a valid client ID and corresponding authentication code, returns a user's retirement survey response if exists, beginning with a bit field indicating whether the data has been prerecorded.

Simulation

Session Management

sim_session_create

CALL sim_session_create(
    <{IN token_client_id int(10) unsigned}>,
    <{IN token_auth_code char(40)}>,
    <{IN session_initial_balance decimal(15,2)}>,
    <{IN session_deposit_personal decimal(15,2)}>,
    <{IN session_deposit_work decimal(15,2)}>,
    <{IN session_deposit_salary decimal(15,2)}>,
    <{IN session_retirement_goal decimal(15,2)}>
);

Given a valid client ID and corresponding authentication code, and initial parameters, initializes a new active retirement simulation simulation session for a user provided no active retirement simulation session exists.

sim_session_status

CALL sim_session_status(
    <{IN token_client_id int(10) unsigned}>,
    <{IN token_auth_code char(40)}>
);

Given a valid client ID and corresponding authentication code, returns returns a 2-field row result: the total number of retirement simulation sessions recorded for the user and if there is an active retirement simulation session.

sim_session_close

CALL sim_session_close(
    <{IN token_client_id int(10) unsigned}>,
    <{IN token_auth_code char(40)}>
);

Given a valid client ID and corresponding authentication code, finalizes and deactivates the user's active retirement simulation provided one exists.

State

sim_session_read

CALL sim_session_read(
    <{IN token_client_id int(10) unsigned}>,
    <{IN token_auth_code char(40)}>
);

Given a valid client ID and corresponding authentication code, returns the simulation parameter state of the user's active retirement simulation provided one exists.

sim_session_update

CALL sim_session_update(
    <{IN token_client_id int(10) unsigned}>,
    <{IN token_auth_code char(40)}>,
    <{IN user_inv_large_cap double unsigned}>,
    <{IN user_inv_small_cap double unsigned}>,
    <{IN user_inv_real_estate double unsigned}>,
    <{IN user_inv_international double unsigned}>,
    <{IN user_inv_bonds double unsigned}>,
    <{IN user_inv_cash double unsigned}>,
    <{IN user_inv_gold double unsigned}>
);

Given a valid client ID and corresponding authentication code, and user input parameters, advances the state of the user's active retirement simulation provided one exists.

Market

sim_market_read

CALL sim_market_read(
    <{IN token_client_id int(10) unsigned}>,
    <{IN token_auth_code char(40)}>,
    <{IN session_id INT(10) unsigned}>,
    <{IN max_history_size int(10) unsigned}>
);

Given a valid client ID, corresponding authentication code, and simulation session id assigned to that user, reads back a specified number of market years for the session provided such a session exists.

Administration

Metadata

metadata_user_count

CALL metadata_user_count(
    <{IN token_client_id int(10) unsigned}>,
    <{IN token_auth_code char(40)}>
);

Given a valid client ID and corresponding authentication code assigned to a listed administrator user account, returns the total registered user count.

Clone this wiki locally