post_database_challenge.rb

require "sqlite3"

db_file_path = 'posts_spec.db'

DB = SQLite3::Database.new(db_file_path)

DB.execute('DROP TABLE IF EXISTS `posts`;')
create_statement = "
    CREATE TABLE `posts` (
      `id`  INTEGER PRIMARY KEY AUTOINCREMENT,
      `title` TEXT,
      `url` TEXT,
      `votes`  INTEGER
    );"
DB.execute(create_statement)

DB.execute("""
  INSERT INTO posts (id,title,url,votes)
  VALUES (1, 'Coder Academy', 'www.coderacademy.com', 200);
""")

DB.execute("""
  INSERT INTO posts (id,title,url,votes)
  VALUES (2, 'Herald Sun', 'www.heraldsun.com', 300);
""")

DB.execute("""
  INSERT INTO posts (id,title,url,votes)
  VALUES (3, 'Hacker News', 'www.hackernews.com', 400);
""")

DB.execute("""
  INSERT INTO posts (id,title,url,votes)
  VALUES (4, 'The Age', 'www.theage.com', 200);
""")

DB.execute("""
  INSERT INTO posts (id,title,url,votes)
  VALUES (5, 'AFL', 'www.afl.com.au', 600);
""")

post_test.rb

require_relative 'quering_database_posts.rb.rb'

# get the post with an id of two back
p Post.find(3)

# get all the posts
p Post.all

quering_database_posts.rb

require 'sqlite3'

DB = SQLite3::Database.new('posts_spec.db')

class Post 
    def initialize(attributes = {})
        # we have to use an attributes hash to ensure that when we do a .new we can just pass in the atttributes as keys and values 
        @id = attributes[:id]
        @title = attributes[:title]
        @url = attributes[:url]
        @votes = attributes[:votes]
    end 

    def self.find(id) 
        result = DB.execute("""
            SELECT *
            FROM posts
            WHERE posts.id == #{id}
            """)
        result = result[0]
        Post.new(id: result[0], title: result[1], url: result[2], votes: result[3])
    end 

    def self.all
        result = DB.execute("""
            SELECT *
            FROM posts
            """)
        result.map do |attribute|
            Post.new(id: attribute[0], title: attribute[1], url: attribute[2], votes: attribute[3])
        end
    end
end