Generate a Bulk of Data into Postgre

generate_db.rb

require 'pg'

conn = PG::Connection.open(dbname: 'mydb',
                            host: 'localhost',
                            user: 'testuser')

# create the first table (cup_matches)
conn.exec("CREATE TABLE cup_matches (
           mid serial PRIMARY KEY,
           round varchar(20),
           year integer,
           num_ratings integer,
           rating real);")

# create the second table (played_in)
conn.exec("create table played_in(
           mid integer references cup_matches(mid),
           name varchar(80),
           year integer,
           position integer,
           PRIMARY KEY(mid,name));")

# generate data for cup_matches

# array for match rounds
round_array = ['32th', '16th', '8th', 'quarter_final', 'semi_final', 'final']

for i in (1..2680) do
  round_i = rand(0..5)
  year_value = rand(2000..2015)
  num_ratings_value = rand(1..205)
  rating_value = rand(1.0..5.0)

  # insert this row
  conn.prepare("ins#{i}" ,"INSERT INTO cup_matches(round, year, num_ratings,
               rating) VALUES ($1, $2, $3, $4)")

  conn.exec_prepared("ins#{i}", [round_array[round_i], year_value,
                                 num_ratings_value, rating_value])
end

# generate data for played_in

# array of random names
name_array = ['Shinichi', 'Nitrogen', 'Yan', 'Itachi', 'Kurosan', 'Tamashii',
              'Ben', 'Owen', 'Ror', 'Bo', 'Yama', 'Kaminari',
              'Kintama', 'Sasuke', 'Aasare', 'Leila', 'Xou', 'Xubuntu',
              'Gnomy', 'Lolly', 'Gintoki', 'Kogami', 'Kurotora', 'Matz',
              'Yuki', 'Yumi', 'Kakashi', 'Naruto', 'K.O.', 'Gohan',
              'Kagura', 'Kuroon', 'Ran', 'Aurora', 'Conan', 'Kudo',
              'Lol', 'Yami', 'Sabasa', 'Namik', 'Goku', 'Tintin',
              'Gara', 'Aiomi']

for i in (1..58960) do
  while true
    name_i = rand(0..43)
    year_value = rand(2000..2015)
    position_value = rand(1..11)
    mid_value = rand(1..2680)

    # SELECT query that searches for the pair (mid_value, name_array[name_i])
    result = conn.exec( "SELECT * FROM played_in WHERE mid=#{mid_value} AND
                       name='#{name_array[name_i]}' " )

    # loop until we have result array empty
    # i.e., (mid_value, name_array[name_i]) is not used
    break unless result.any?
  end

  conn.prepare("inse#{i}" ,"INSERT INTO played_in(mid, name, year, position)
                 VALUES ($1, $2, $3, $4)")

  conn.exec_prepared("inse#{i}", [mid_value, name_array[name_i], year_value,
                                    position_value])
end

# this is where we make 118 rows have the name 'pele'
for i in (1..118) do
  while true
    mid_value = rand(1..2680)

    # make sure that we don't have a (mid_value, 'pele') pair in the database
    result = conn.exec( "SELECT * FROM played_in WHERE mid='#{mid_value}' AND
                       name='pele' " )

    # break if mid_value is valid to use
    break unless result.any?
  end

  # retrieve all rows that have mid_value
  result = conn.exec( "SELECT * FROM played_in WHERE mid='#{mid_value}'" )

  # result is an array of rows
  # retrieve the name (row[1]) from the first row of the result (result[0])
  target_name = result.getvalue(0, 1)

  # update this row to have name='pele'
  conn.exec_params("UPDATE played_in SET name = $1 WHERE mid=#{mid_value} AND
                   name='#{target_name}'",  ['pele'])
end