Vote Database Queries

README.md

Overview

For Voting App

Need MySQL queries for the following:

1. Statement: create voter. Register a unique username of reasonable lengths (3-64 characters, such as "tonypujals" and password to be able to vote. Password field will store bcrypt-encrypted strings, specifically:

• CHAR(60)
• CHARACTER SET latin1
• COLLATE latin1_bin

2. Query: fetch encrypted password for voter username. Will be used to compare encrypted strings for login.

3. Statement: create a poll. A poll is a unique name, such as "cats_vs_dogs" and identifies the names of the available choices to vote for (eg, "cats", "dogs"). The choices should have a simple incrementing unique ID (1, 2, 3, etc.)

4. Statement: cast ballot. A ballot has a voter, a poll, and vote.

5. Query: fetch voter's vote for a particular poll (ex: "cats").

6. Query: fetch vote results for a particular poll (ex: cats: 5, dogs: 3)

Run on Aurora.

0. Create database

create database vote;
use vote

1. Statement: create voter

create table voters (
	voter_id bigint(20) not null auto_increment,
	username varchar(64) not null,
	encrypted_password  char(60) character set latin1 collate latin1_bin,
	primary key (voter_id),
	unique index idx_voter_username (username)
);

2. Query: fetch encrypted password for voter username

select encrypted_password 
from voters
where username = ?;

3. Statement: create a poll.

create table polls (
	poll_id bigint(20) not null auto_increment,
	pollname varchar(64) not null,
	primary key (poll_id),
	unique index idx_poll_pollname (pollname)
);

create table choices (
	poll_id bigint(20) not null,
	choice_id bigint(20) not null auto_increment,
	choices_description varchar(64),
	primary key (choice_id)
);

4. Statement: cast ballot. A ballot has a voter, a poll, and vote.

create table ballots (
	ballot_id bigint(20) not null auto_increment,
	voter_id bigint(20) not null,
	poll_id bigint(20) not null,
	choice_id bigint(20) not null,
	primary key (ballot_id),
	unique index idx_ballot (voter_id,poll_id, choice_id)
);

--create some test data
#
insert into voters (username, encrypted_password) values ('tony', 'abc');
insert into voters (username, encrypted_password) values ('tenny', 'xxx');
insert into voters (username, encrypted_password) values ('kitty', 'aaa');

insert into polls (pollname) values ('cats_vs_dogs');

insert into choices (poll_id,choices_description) values (1,'cats');
insert into choices (poll_id,choices_description) values (1,'dogs');

insert into ballots (voter_id,poll_id,choice_id) values (1,1,1);
insert into ballots (voter_id,poll_id,choice_id) values (2,1,2);
insert into ballots (voter_id,poll_id,choice_id) values (3,1,1);

select * from voters;
select * from polls;
select * from choices;
select * from ballots;

5. Query: retrieve voter's vote for a particular poll.

select v.username, c.choices_description
from ballots b 
join polls p on b.poll_id = p.poll_id 
join voters v on v.voter_id = b.voter_id 
join choices c on p.poll_id = c.poll_id and c.choice_id = b.choice_id 
where v.username = 'tony'
and p.pollname = 'cats_vs_dogs';

6. Query: return the vote results for a particular poll (ex: cats: 5, dogs: 3)

select c.choices_description, count(*) as number_votes
from ballots b 
join polls p on b.poll_id = p.poll_id 
join choices c on p.poll_id = c.poll_id and c.choice_id = b.choice_id 
where p.pollname = 'cats_vs_dogs'
group by c.choices_description;

1. Create a bridge network to use for communication between a MySQL CLI and database.

    $ docker network create --driver bridge sqlnet

2. Start a MySQL database container.

    $ docker run -d --name mysql --network sqlnet -e MYSQL_ROOT_PASSWORD=secret mysql:latest

3. Start a MySQL command-line client.

    $ docker run -it --rm --name mysqlcli --network sqlnet mysql:latest sh -c 'exec mysql -h mysql -uroot -p'
    Enter password: _