CREATE TABLE seven_stud_spread_stakes(bring_in int, fourth_street int check(fourth_street >= bring_in), fifth_street int check(fifth_street >= bring_in), sixth_street int check(sixth_street >= bring_in), seventh_street int check(seventh_street >= bring_in));

CREATE TABLE colorado_limit_stakes(small_blind int, big_blind int check(big_blind >= small_blind), preflop int check(preflop <= 100), flop int check(flop <= 100), turn int check(turn <= 100), river int check(river >= big_blind));

CREATE TABLE spread_limit_stakes(small_blind int, big_blind int check(big_blind >= small_blind), preflop int check(preflop >= small_blind), flop int check(flop >= small_blind), turn int check(turn >= small_blind), river int check(river >= small_blind));

CREATE TABLE seven_stud_stakes(bring_in int, fourth_street int check(fourth_street >= bring_in), fifth_street int check(fifth_street >= fourth_street), sixth_street int check(sixth_street >= fifth_street), seventh_street int check(seventh_street >= sixth_street));

CREATE TABLE stakes(small_blind int, big_blind int check(big_blind >= small_blind), preflop int check(preflop >= big_blind), flop int check(flop >= preflop), turn int check(turn >= flop), river int check(river >= turn));
INSERT INTO stakes VALUES(1, 3, 3, 3, 6, 9);

CREATE TABLE v_limits(v_limit TEXT PRIMARY KEY);
CREATE TABLE states(abbreviation TEXT PRIMARY KEY, state TEXT);
CREATE TABLE cities(city TEXT PRIMARY KEY);
CREATE TABLE games(game TEXT PRIMARY KEY);
CREATE TABLE hi_lows(hi_lo TEXT PRIMARY KEY);
CREATE TABLE kills(kill TEXT PRIMAY KEY);
CREATE TABLE stakes(stake TEXT PRIMARY KEY);
CREATE TABLE venues(venue TEXT PRIMAY KEY);

CREATE TABLE visits(id INT PRIMARY KEY, arrival_date DATE, departure_date DATE, arrival_time TIME, departure_time TIME, venue TEXT REFERENCES venues(venue), city TEXT REFERENCES cities(city), state TEXT REFERENCES states(abbreviation), game TEXT REFERENCES games(game), stake TEXT REFERENCES stakes(stake), kill TEXT REFERENCES kills(kill), hi_lo TEXT REFERENCES hi_lows(hi_lo), v_limit REFERENCES v_limits(v_limit), buy_in MONEY, cash_out MONEY);

arrival_date:   20180214
departure_date: 20180215
arrival_time:   1000
departure_time: 1800
venue:          "Binion's"
city:           "Las Vegas"
state:          "NV"
game:           "hold'em"
stake:          "4-8"
kill:           "no-kill"
hi_lo:          "hi"
limit:          "fixed"
buy_in:         200
cash_out:       400

#!/usr/bin/perl

use strict;
use warnings;
use v5.26;
use DBI; ## postgresql access via dbi
use YAML::Tiny; ## Used to call default answers
use IO::Prompt; ## Used to prompt for the current information
use POSIX;


######################################################################
## postgresql database login configurations                          #
######################################################################
my $driver = "Pg";
my $database = "venue_visits";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $username = "postgres";
my $password = "somepassword";

#####################################################################
## get user's visit inputs                                          #
#####################################################################
my @visits_array = get_visit_information();

#####################################################################
## Connect to postgresql database                                   #
#####################################################################
my %attr = (PrintError=>0,RaiseError=>1);
my $dbh = DBI->connect($dsn,$username,$password,\%attr);

#####################################################################
## Prepare to insert data into the appropriate postgresql table     #
#####################################################################
my $sql = "INSERT INTO visits(id, arrival_date, departure_date, arrival_time, departure_time, venue, city, state, game, stake, kill, hi_lo, v_limit, buy_in, cash_out)
    VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

my $stmt = $dbh->prepare($sql);

#####################################################################
## execute the query                                                #
#####################################################################
foreach my $visit(@visits_array) {
    if ( $stmt->execute($visit->{id}, $visit->{arrival_date}, $visit->{departure_date}, $visit->{arrival_time}, $visit->{departure_time}, $visit->{venue}, $visit->{city}, $visit->{state}, $visit->{game}, $visit->{stake}, $visit->{kill}, $visit->{hi_lo}, $visit->{v_limit}, $visit->{buy_in}, $visit->{cash_out})) {
        print( "visit $visit->{id} inserted successfully \n" );
    }
}
$stmt->finish();
 
#####################################################################
## disconnect from the postgresql database                          #
#####################################################################
$dbh->disconnect();

#####################################################################
# The below sub obtains the information required for the above      #
# actions                                                           #
#####################################################################
sub get_visit_information {
	
    ######################################################################
    ## Read the visit_configuration file to retrieve the default answers #
    ######################################################################
    my $visit_configuration = YAML::Tiny->read( 'visit_configuration.yaml' );

    #####################################################################
    # Create the id which will go into the venue_visits database        #
    # visits table                                                      #
    #####################################################################
    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time) or die "Unable to obtaing from localtime: $!";
    my $id = "$year$yday$hour$min";
    $id =~ s/^\s+|\s+$//g;

    my $cmd = '';
    my @visit_sub_array;
    ## get visits from the command line
    my($arrival_date, $departure_date, $arrival_time, $departure_time,
       $venue, $city, $state, $game, $stake, $kill, $hi_lo, $v_limit,
       $buy_in, $cash_out);

    ## repeatedly ask for visit data from command line
    $arrival_date = prompt -d => "$visit_configuration->[0]->{arrival_date}", "Arrival Date: " or die "Unable to obtain arrival date: $!";
    $arrival_date =~ s/^\s+|\s+$//g;
    $departure_date = prompt -d => "$visit_configuration->[0]->{departure_date}", "Departure Date: " or die "Unable to obtaian departure date: $!";
    $departure_date =~ s/^\s+|\s+$//g;
    $arrival_time = prompt -d => "$visit_configuration->[0]->{arrival_time}", "Arrival Time: " or die "Unable to obtain arrival time: $!";
    $arrival_time =~ s/^\s+|\s+$//g;
    $departure_time = prompt -d => "$visit_configuration->[0]->{departure_time}", "Departure Time: " or die "Unable to obtain departure time: $!";
    $departure_time =~ s/^\s+|\s+$//g;
    $venue = prompt -d => "$visit_configuration->[0]->{venue}", "Venue: " or die "Unable to obtain venue: $!";
    $venue =~ s/^\s+|\s+$//g;
    $city = prompt -d => "$visit_configuration->[0]->{city}", "City: ", or die "Unable to obtain city: $!";
    $city =~ s/^\s+|\s+$//g;
    $state = prompt -d => "$visit_configuration->[0]->{state}", "State: ", or die "Unable to obtain state: $!";
    $state =~ s/^\s+|\s+$//g;
    $game = prompt -d => "$visit_configuration->[0]->{game}", "Game: ", or die "Unable to obtain game: $!";
    $game =~ s/^\s+|\s+$//g;
    $stake = prompt -d => "$visit_configuration->[0]->{stake}", "Stake: ", or die "Unable to obtain stake: $!";
    $stake =~ s/^\s+|\s+$//g;
    $kill = prompt -d => "$visit_configuration->[0]->{kill}", "Kill: ", or die "Unable to obtain kill: $!";
    $kill =~ s/^\s+|\s+$//g;
    $hi_lo = prompt -d => "$visit_configuration->[0]->{hi_lo}", "Hi Lo: " or die "Unable to obtain hi_lo: $!";
    $hi_lo =~ s/^\s+|\s+$//g;
    $v_limit = prompt -d => "$visit_configuration->[0]->{limit}", "Limit: " or die "Unable to obtain limit: $!";
    $v_limit =~ s/^\s+|\s+$//g;
    $buy_in = prompt -d => "$visit_configuration->[0]->{buy_in}", "Buy In: " or die "Unable to obtain buy_in: $!";
    $buy_in =~ s/^\s+|\s+$//g;
    $cash_out = prompt -d => "$visit_configuration->[0]->{cash_out}", "Cash Out: " or die "Unable to obtain cash_out: $!";
    $cash_out =~ s/^\s+|\s+$//g;

    my %visit_sub_hash = (id=>$id, arrival_date=>$arrival_date, departure_date=>$departure_date,
	                  arrival_time=>$arrival_time, departure_time=>$departure_time,
		          venue=>$venue, city=>$city, state=>$state, game=>$game,
			  stake=>$stake, kill=>$kill, hi_lo=>$hi_lo, v_limit=>$v_limit,
			  buy_in=>$buy_in, cash_out=>$cash_out );

    push(@visit_sub_array,\%visit_sub_hash);

    return @visit_sub_array;
}

1;

__END__

=head1 NAME

 build_sql_entries 

=cut

=head1 SYNOPSIS

 The build_sql_entries file allows a user to enter poker statistics into a postgresql database. The user is prompeted for the correct information. The configuration file is visit_configuration.yaml.

=cut

=head1 DESCRIPTION

    The information is formatted as necessary. More description tbd.

=cut

=head1 AUTHOR

   Sherman L. Willden
=cut

=head1 BUGS

 TDB 

=cut

=head1 SEE ALSO

  Any PERL book and regular expression book

=cut

=head1 COPYRIGHT

build_sql_entries: Copyright (C) 2018, Sherman Willden.  The file is licensed under the terms of the GNU Lesser General Public License 2.1. See <http://www.gnu.org/licenses/lgpl-2.1.html>.

This program is free software; you can redistribute it and/or modify it under the terms of the Artistic License 2.0. You can redistribute it and/or modify it under the same terms as Perl 5.26.0

This program is distributed in the hope that it will be useful, but it is provided “as is” and without any express or implied warranties. 

=cut

https://forumserver.twoplustwo.com/191/programming/recommended-number-fields-table-1624249/?highlight=

create table author (
    author_id integer not null primary,
    author_name varchar(200)
)

create table book (
    book_id integer not null primary key
    book_name varchar(200),
    author integer references authors(author_id)
)

select book_name, author_name
from books
join authors using (author_id)