Simple script to write data to a SQL seed file for db assignment.

generate_data

#!/usr/bin/env ruby

require 'faker'

#
# Usage:
#    chmox +x generate_data
#    ./generate_data > seed.sql
#
# First, set up the schema using the provided sql:
#    db2 -f schema.sql
#
# Then, insert data:
#    db2 -f seed.sql
#

# Class for generating data for Databases assignment
class DataGenerator
  attr_reader :prng

  def initialize(args)
    @prng      = Random.new
    @person_id = args[:person_id]
    @wrote_id  = args[:wrote_id]
  end

  def author
    @person_id += 1

    case prng.rand(2)
    when 0
      { aid: @person_id, name: "'#{Faker::Name.unique.name}'" }
    when 1
      { aid: @person_id, name: "'#{Faker::Name.unique.name}'", url: "'#{Faker::Internet.url}'" }
    end
  end

  def publication
    { pubid: "'#{Faker::Number.hexadecimal(4)}'", title: "'#{Faker::Book.unique.title}'" }
  end

  def wrote(author, publication)
    @wrote_id += 1
    { aid: author[:aid], pubid: publication[:pubid], aorder: @wrote_id }
  end

  def proceeding(publication)
    { pubid: publication[:pubid], year: "'#{Faker::Number.between(1960, 2017)}'" }
  end

  def journal(publication)
    {
      pubid: publication[:pubid],
      volume: 1,
      number: 1,
      year: "'#{Faker::Number.between(1960, 2017)}'"
    }
  end

  def book(publication)
    {
      pubid: publication[:pubid],
      publisher: "'#{Faker::Book.publisher}'",
      year: "'#{Faker::Number.between(1960, 2017)}'"
    }
  end

  def article(publication)
    {
      pubid: publication[:pubid],
      appearsin: "book",
      startpage: 1,
      endpage: 2
    }
  end
end

# Helper module for building SQL values tuples
module StringBuilder
  def self.join_values!(hash_array)
    hash_array.map { |hash| "(#{hash.values.join(', ')})" }.join(', ')
  end
end

# Create sample data
data = DataGenerator.new(person_id: 2, wrote_id: 2)

authors      = Array.new(10) { data.author }
publications = Array.new(10) { data.publication }
writes       = Array.new(10) { data.wrote(authors.sample, publications.sample) }
proceedings  = Array.new(10) { data.proceeding(publications.sample) }
journals     = Array.new(10) { data.journal(publications.sample) }
books        = Array.new(10) { data.book(publications.sample) }
articles     = Array.new(10) { data.article(publications.sample) }

# Write data to stdout or file
puts %(
  connect to cs348
  insert into author values #{StringBuilder::join_values!(authors)}
  insert into publication values #{StringBuilder::join_values!(publications)}
  insert into wrote values #{StringBuilder::join_values!(writes)}
  insert into wrote values #{StringBuilder::join_values!(proceedings)}
  insert into wrote values #{StringBuilder::join_values!(journals)}
  insert into wrote values #{StringBuilder::join_values!(books)}
  insert into wrote values #{StringBuilder::join_values!(articles)}
  commit work
)