databse

databse.rb

require 'sqlite3'

class Database
  $db ||= SQLite3::Database.new('/Users/apprentice/Desktop/FTW/lib/connect4_dev.db', :results_as_hash => true)

  def self.instance
    $db
  end

  def self.load_schema
    $db.execute_batch File.open('/Users/apprentice/Desktop/FTW/db/schema.sql').read
  end

  def self.find_name_by_id(id)
    ($db.execute "select name from players where id='#{id}'").flatten.first
  end

  def self.games_played(id)
    return ($db.execute "select count(*) from games_players where player_id='#{id}'").flatten.first
  end

  def self.return_wins(id)
    return ($db.execute "select count(*) from games where winner_id='#{id}'").flatten.first
  end

  def self.return_losses(id)
    # not accounting for ties
    games_played = self.games_played(id)
    games_won = self.return_wins(id)
    games_lost = games_played - games_won
  end

  # def self.return_tie
  #       returns the amount of ties in total games
  #       needs amount of games player participated in
  #       needs amount of ties player had in games
  #     end

  def self.return_metrics(id)
    puts "Player: #{self.find_name_by_id(id)} | Total Games Played: #{self.games_played(id)} | Total Games Won: #{self.return_wins(id)}"
  end

end

# Database.instance
Database.load_schema

#
#
#
# puts Database.return_metrics(72)
# puts $db.execute "SELECT name FROM players"
# puts $db.execute "Select id from players where name='AI-vvmxdiqw'"
# puts $db.execute "select * from games"


=begin

DB Methods

 #############
| SCRATCHPAD |
#############

select winner from games where id=1;
=> Spits out the winners in the games table that have id 1

insert into players values('ferdi');
=> inserts a player into the players table with name value "ferdi"

select count(*) from games;
=> Counts the number of games in games

delete from games;
=> Deletes game from games (which one? no one knows)

insert into games values(?, 1, "04/12/2012", "04/12/2012","1.31,2.43,1.15,2.3");
=> inserts a game item into games [null=uniqid, 1=the id of the winner, date, date, game moves)

SELECT * FROM <database>;
=> returns all from database (basically a print out of the database)


select name from players join games on players.id=winner;
=> returns the names of the winners by matching winner id on games to players id

select player_id from games_players where game_id=1; ??? ASK SHADI HOW TO GET NAMES
=> returns the player ids of a specific game of id x

select p.name from players p join games_players gp on p.id = gp.player_id where gp.game_id = 1;
returns players of specified game, in this case 1

select count (*) from games where winner = 1;
returns the amount of times player with id 1 won



PLAYERS
id          name
----------  ----------
1           ferdi
2           ricky
3           julie
4           billy
5           HAL


GAMES_PLAYERS
id          player_id   game_id
----------  ----------  ----------
3           5           1
4           2           1
5           1           2
6           3           2


GAMES
id          winner      created_at  updated_at  moves
----------  ----------  ----------  ----------  ----------
1           5           0           0           -5.73
2           3           0           0           0.14732593
3           4           0           0           0
4           1           0           0           1.23-2.12-
5           1           0           0           1.23-2.12-
=end

schema

-- DROP TABLE IF EXISTS players;

CREATE TABLE if not exists players (
  `id` INTEGER PRIMARY KEY -- AUTOINCREMENT,
  `name` VARCHAR(255) unique NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP
 );


-- DROP TABLE IF EXISTS games_players;

 CREATE TABLE if not exists games_players (
    `id` INTEGER PRIMARY KEY -- AUTOINCREMENT,
    `player_id` INTEGER NOT NULL,
    `game_id` INTEGER AUTOINCREMENT,
    `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
    `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(player_id) REFERENCES players(id),
    FOREIGN KEY(game_id) REFERENCES games(id)
  );


 -- DROP TABLE IF EXISTS games;

 CREATE TABLE if not exists games (
   `id` INTEGER PRIMARY KEY -- AUTOINCREMENT,
   `winner_id` INTEGER NOT NULL,
   `moves` TEXT NOT NULL,
   `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
    `updated_at` datetime DEFAULT CURRENT_TIMESTAMP
 );