created_at_referencing_awards.rb

#!/usr/bin/env ../spec/dummy/script/rails runner
# if you use linux, please change previous line to the
# "#! ../spec/dummy/script/rails runner"

# Before running this example you should execute "bundle install" and "rake db:create".
# To run this example you should open 'example' directory and execute example with one of the following flags:
# -C    cleanup data in database and exit;
# -F    cleanup data in database before creating new data;
#
# For example:
# ./created_at_referencing_awards.rb - F

# Initial data:
#
#  Companies table is completed by four companies:
#
#  create table companies (
#        id               serial not null primary key,
#        created_at       timestamp not null default now(),
#        updated_at       timestamp,
#        name             text null
#  );
#
#  insert into companies (created_at,id,name) values
#    ('2012-03-13 13:26:52.184347',1,'Fluent Mobile, inc.'),
#    ('2012-03-13 13:26:52.184347',2,'Fiksu, inc.'),
#    ('2012-03-13 13:26:52.184347',3,'AppExchanger, inc.'),
#    ('2012-03-13 13:26:52.184347',4,'FreeMyApps, inc.');
#
#  id |         created_at         | updated_at |        name
#  ---+----------------------------+------------+---------------------
#   1 | 2012-03-11 13:26:52.184347 |            | Fluent Mobile, inc.
#   2 | 2012-03-11 13:26:52.184347 |            | Fiksu, inc.
#   3 | 2012-03-11 13:26:52.184347 |            | AppExchanger, inc.
#   4 | 2012-03-11 13:26:52.184347 |            | FreeMyApps, inc.
#
#  Employees table is associated with companies table via key - id:
#
#  create table employees (
#        id               serial not null primary key,
#        created_at       timestamp not null default now(),
#        updated_at       timestamp,
#        name             text not null,
#        salary           money not null,
#        company_id       integer not null
#  );
#
#   id | created_at | updated_at | name | salary | company_id
#  ----+------------+------------+------+--------+------------
#
#  Awards table is associated with employees table via key - employee_id:
#
#  create table awards (
#        id                    serial not null primary key,
#        created_at            timestamp not null default now(),
#        employee_created_at   timestamp not null,
#        awarded_on            date not null,
#        employee_id           integer not null,
#        award_title           text not null
#  );
#
#  id | created_at | employee_created_at | awarded_on | employee_id | award_title
# ----+------------+---------------------+------------+-------------+-------------
#
# Task:
#
#  To increase the speed of requests to the database and to reduce the time
#  of the request, need to split the Employees table and Awards table to the partition tables.
#  Break criterion for Employees table is a created date(created_at).
#  Break criterion for Awards table is a employee created date(employee_created_at).
#
# Implementation:
#
#  Class Employee inherits from the abstract class ByCreatedAt,
#  which supports partitioning.
#
#  class Employee < Partitioned::ByCreatedAt
#
#    Indicates a relationship to the companies table.
#    belongs_to :company, :class_name => 'Company'
#
#    Create a rules for each partition.
#    Id is a unique index. Foreign key is company_id.
#    This imposes a restriction on each of partition, that
#    the column company_id associated with the table of companies
#    and can not have values ​​that are not in the table companies.
#    In this example, set up only 4 records in the table companies,
#    so company_id can not be equal to 5 in any partition
#    until it is an established company with id = 5.
#
#    partitioned do |partition|
#      partition.index :id, :unique => true
#      partition.foreign_key :company_id
#    end
#  end
#
#  Class Award inherits from the abstract class ByEmployeeCreatedAt,
#  which supports partitioning.
#
#  class Award < ByEmployeeCreatedAt
#
#    Indicates a relationship to the employees table.
#    belongs_to :employee, :class_name => 'Employee'
#
#    partitioned do |partition|
#      partition.foreign_key lambda {|model, *partition_key_values|
#        return Configurator::Data::ForeignKey.
#                    new(:employee_id, Employee.partition_name(*partition_key_values), :id)
#      }
#    end
#  end
#
#  Create a schema employees_partitions, within which to store employees partitions:
#
#  Employee.create_infrastructure
#
#  Create the infrastructure for Awards table which includes the schema and partition tables:
#
#  Award.create_infrastructure
#
#  Create a partition tables with increments of one week:
#
#  dates = Employee.partition_generate_range(START_DATE, END_DATE)
#
#  Employee.create_new_partition_tables(dates)
#  Each of employees partition has the same structure as that of the employees table:
#
#   id | created_at | updated_at | name | salary | company_id
#  ----+------------+------------+------+--------+------------
#
#  CREATE TABLE "employees_partitions"."p20101227" (CHECK (created_at >= '2010-12-27' AND
#                                        created_at < '2011-01-03')) INHERITS (employees);
#  CREATE INDEX "p20101227_created_at_idx" ON "employees_partitions"."p20101227" ("created_at");
#  CREATE UNIQUE INDEX "p20101227_id_udx" ON "employees_partitions"."p20101227" ("id");
#  ALTER TABLE employees_partitions.p20101227 add foreign key (company_id) references companies(id);
#
#  CREATE TABLE "employees_partitions"."p20110103" (CHECK (created_at >= '2011-01-03' AND
#                                        created_at < '2011-01-10')) INHERITS (employees);
#  CREATE INDEX "p20110103_created_at_idx" ON "employees_partitions"."p20110103" ("created_at");
#  CREATE UNIQUE INDEX "p20110103_id_udx" ON "employees_partitions"."p20110103" ("id");
#  ALTER TABLE employees_partitions.p20110103 add foreign key (company_id) references companies(id);
#
#  CREATE TABLE "employees_partitions"."p20110110" (CHECK (created_at >= '2011-01-10' AND
#                                        created_at < '2011-01-17')) INHERITS (employees);
#  CREATE INDEX "p20110110_created_at_idx" ON "employees_partitions"."p20110110" ("created_at");
#  CREATE UNIQUE INDEX "p20110110_id_udx" ON "employees_partitions"."p20110110" ("id");
#  ALTER TABLE employees_partitions.p20110110 add foreign key (company_id) references companies(id);
#
#  ...
#
#  CREATE TABLE "employees_partitions"."p20111212" (CHECK (created_at >= '2011-12-12' AND
#                                        created_at < '2011-12-19')) INHERITS (employees);
#  CREATE INDEX "p20111212_created_at_idx" ON "employees_partitions"."p20111212" ("created_at");
#  CREATE UNIQUE INDEX "p20111212_id_udx" ON "employees_partitions"."p20111212" ("id");
#  ALTER TABLE employees_partitions.p20111212 add foreign key (company_id) references companies(id);
#
#  CREATE TABLE "employees_partitions"."p20111219" (CHECK (created_at >= '2011-12-19' AND
#                                     created_at < '2011-12-26')) INHERITS (employees);
#  CREATE INDEX "p20111219_created_at_idx" ON "employees_partitions"."p20111219" ("created_at");
#  CREATE UNIQUE INDEX "p20111219_id_udx" ON "employees_partitions"."p20111219" ("id");
#  ALTER TABLE employees_partitions.p20111219 add foreign key (company_id) references companies(id);
#
#  CREATE TABLE "employees_partitions"."p20111226" (CHECK (created_at >= '2011-12-26' AND
#                                     created_at < '2012-01-02')) INHERITS (employees);
#  CREATE INDEX "p20111226_created_at_idx" ON "employees_partitions"."p20111226" ("created_at");
#  CREATE UNIQUE INDEX "p20111226_id_udx" ON "employees_partitions"."p20111226" ("id");
#  ALTER TABLE employees_partitions.p20111226 add foreign key (company_id) references companies(id);
#
# You should have the following tables with increments of one week:
#  employees_partitions.p20101227
#  employees_partitions.p20110103
#  employees_partitions.p20110110
#  employees_partitions.p20110117
#  employees_partitions.p20110124
#  employees_partitions.p20110131
#  employees_partitions.p20110207
#  employees_partitions.p20110214
#  employees_partitions.p20110221
#  employees_partitions.p20110228
#  employees_partitions.p20110307
#  employees_partitions.p20110314
#  employees_partitions.p20110321
#  employees_partitions.p20110328
#  employees_partitions.p20110404
#  employees_partitions.p20110411
#  employees_partitions.p20110418
#  employees_partitions.p20110425
#  employees_partitions.p20110502
#  employees_partitions.p20110509
#  employees_partitions.p20110516
#  employees_partitions.p20110523
#  employees_partitions.p20110530
#  employees_partitions.p20110606
#  employees_partitions.p20110613
#  employees_partitions.p20110620
#  employees_partitions.p20110627
#  employees_partitions.p20110704
#  employees_partitions.p20110711
#  employees_partitions.p20110718
#  employees_partitions.p20110725
#  employees_partitions.p20110801
#  employees_partitions.p20110808
#  employees_partitions.p20110815
#  employees_partitions.p20110822
#  employees_partitions.p20110829
#  employees_partitions.p20110905
#  employees_partitions.p20110912
#  employees_partitions.p20110919
#  employees_partitions.p20110926
#  employees_partitions.p20111003
#  employees_partitions.p20111010
#  employees_partitions.p20111017
#  employees_partitions.p20111024
#  employees_partitions.p20111031
#  employees_partitions.p20111107
#  employees_partitions.p20111114
#  employees_partitions.p20111121
#  employees_partitions.p20111128
#  employees_partitions.p20111205
#  employees_partitions.p20111212
#  employees_partitions.p20111219
#  employees_partitions.p20111226
#
#  Each of partitions inherits from employees table,
#  thus a new row will automatically be added to the employees table .
#
#  To add data, we use the following constructions,
#  in which employees and employee_data - a random data:
#
#  create_many - allows you to add multiple records
#  Employee.create_many(employees)
#  create - allows you to add one record
#  Employee.create(employee_data)
#  new/save! - allows you to add one record without using "create" method
#  employee = Employee.new(employee_data)
#  employee.save!
#
#  For update data, we use the following constructions,
#  in which updates - a random data:
#
#  update_many - allows you to update multiple records.
#  :set_array - additional option, you may read the description
#  of the method in the file update_many bulk_methods_mixin.rb about this option.
#  Employee.update_many(updates, { :set_array => '"salary = #{table_name}.salary +
#                                                    datatable.salary, updated_at = now()"' })
#
#  This construction using for update one record. You also may use update method.
#  employee = Employee.from_partition(employee_record[:created_at]).find(employee_record[:id])
#  employee.save
#
#  The data get into the employees table ONLY through partition tables.
#  You can not do an insert row into a table employees directly.
#  For this purpose special restrictions are imposed on the table employees.
#
#  Award.create_new_partition_tables(dates)
#  Each of awards partition has the same structure as that of the awards table:
#
#   id  | created_at | employee_created_at | awarded_on | employee_id | award_title
#  -----+------------+---------------------+------------+-------------+-------------
#
#  CREATE TABLE "awards_partitions"."p20101227" (CHECK (employee_created_at >= '2010-12-27' AND
#                                                       employee_created_at < '2011-01-03')) INHERITS (awards);
#  CREATE INDEX "p20101227_employee_created_at_idx" ON "awards_partitions"."p20101227" ("employee_created_at");
#  ALTER TABLE awards_partitions.p20101227 add foreign key (employee_id) references employees_partitions.p20101227(id);
#
#  CREATE TABLE "awards_partitions"."p20110103" (CHECK (employee_created_at >= '2011-01-03' AND
#                                                       employee_created_at < '2011-01-10')) INHERITS (awards);
#  CREATE INDEX "p20110103_employee_created_at_idx" ON "awards_partitions"."p20110103" ("employee_created_at");
#  ALTER TABLE awards_partitions.p20110103 add foreign key (employee_id) references employees_partitions.p20110103(id);
#
#  CREATE TABLE "awards_partitions"."p20110110" (CHECK (employee_created_at >= '2011-01-10' AND
#                                                       employee_created_at < '2011-01-17')) INHERITS (awards);
#  CREATE INDEX "p20110110_employee_created_at_idx" ON "awards_partitions"."p20110110" ("employee_created_at");
#  ALTER TABLE awards_partitions.p20110110 add foreign key (employee_id) references employees_partitions.p20110110(id);
#
#  ...
#
#  CREATE TABLE "awards_partitions"."p20111212" (CHECK (employee_created_at >= '2011-12-12' AND
#                                                       employee_created_at < '2011-12-19')) INHERITS (awards);
#  CREATE INDEX "p20111212_employee_created_at_idx" ON "awards_partitions"."p20111212" ("employee_created_at");
#  ALTER TABLE awards_partitions.p20111212 add foreign key (employee_id) references employees_partitions.p20111212(id);
#
#  CREATE TABLE "awards_partitions"."p20111219" (CHECK (employee_created_at >= '2011-12-19' AND
#                                                       employee_created_at < '2011-12-26')) INHERITS (awards);
#  CREATE INDEX "p20111219_employee_created_at_idx" ON "awards_partitions"."p20111219" ("employee_created_at");
#  ALTER TABLE awards_partitions.p20111219 add foreign key (employee_id) references employees_partitions.p20111219(id);
#
#  CREATE TABLE "awards_partitions"."p20111226" (CHECK (employee_created_at >= '2011-12-26' AND
#                                                       employee_created_at < '2012-01-02')) INHERITS (awards);
#  CREATE INDEX "p20111226_employee_created_at_idx" ON "awards_partitions"."p20111226" ("employee_created_at");
#  ALTER TABLE awards_partitions.p20111226 add foreign key (employee_id) references employees_partitions.p20111226(id);
#
# You should have the following tables with increments of one week:
#  awards_partitions.p20101227
#  awards_partitions.p20110103
#  awards_partitions.p20110110
#  awards_partitions.p20110117
#  awards_partitions.p20110124
#  awards_partitions.p20110131
#  awards_partitions.p20110207
#  awards_partitions.p20110214
#  awards_partitions.p20110221
#  awards_partitions.p20110228
#  awards_partitions.p20110307
#  awards_partitions.p20110314
#  awards_partitions.p20110321
#  awards_partitions.p20110328
#  awards_partitions.p20110404
#  awards_partitions.p20110411
#  awards_partitions.p20110418
#  awards_partitions.p20110425
#  awards_partitions.p20110502
#  awards_partitions.p20110509
#  awards_partitions.p20110516
#  awards_partitions.p20110523
#  awards_partitions.p20110530
#  awards_partitions.p20110606
#  awards_partitions.p20110613
#  awards_partitions.p20110620
#  awards_partitions.p20110627
#  awards_partitions.p20110704
#  awards_partitions.p20110711
#  awards_partitions.p20110718
#  awards_partitions.p20110725
#  awards_partitions.p20110801
#  awards_partitions.p20110808
#  awards_partitions.p20110815
#  awards_partitions.p20110822
#  awards_partitions.p20110829
#  awards_partitions.p20110905
#  awards_partitions.p20110912
#  awards_partitions.p20110919
#  awards_partitions.p20110926
#  awards_partitions.p20111003
#  awards_partitions.p20111010
#  awards_partitions.p20111017
#  awards_partitions.p20111024
#  awards_partitions.p20111031
#  awards_partitions.p20111107
#  awards_partitions.p20111114
#  awards_partitions.p20111121
#  awards_partitions.p20111128
#  awards_partitions.p20111205
#  awards_partitions.p20111212
#  awards_partitions.p20111219
#  awards_partitions.p20111226
#
#  Each of partitions inherits from awards table,
#  thus a new row will automatically be added to the awards table .
#
#  To add data, we use the following constructions,
#  in which awards and award_data - a random data:
#
#  create_many - allows you to add multiple records
#  Award.create_many(awards)
#  create - allows you to add one record
#  Award.create(award_data)
#  new/save! - allows you to add one record without using "create" method
#  award = Award.new(award_data)
#  award.save!
#
#  For update data, we use the following constructions,
#  in which updates - a random data:
#
#  update_many - allows you to update multiple records.
#  :set_array - additional option, you may read the description
#  of the method in the file update_many bulk_methods_mixin.rb about this option.
#  Award.update_many(updates, { :set_array => '"award_title = datatable.award_title,
#                                                            awarded_on = now()"' })
#
#  This construction using for update one record. You also may use update method.
#  award = Award.from_partition(award_record[:employee_created_at]).find(award_record[:id])
#  award.save
#
#  The data get into the awards table ONLY through partition tables.
#  You can not do an insert row into a table awards directly.
#  For this purpose special restrictions are imposed on the table awards.
#
#  Result:
#
#  We have table companies:
#
#  id |         created_at         | updated_at |        name
#  ---+----------------------------+------------+---------------------
#   1 | 2012-03-11 13:26:52.184347 |            | Fluent Mobile, inc.
#   2 | 2012-03-11 13:26:52.184347 |            | Fiksu, inc.
#   3 | 2012-03-11 13:26:52.184347 |            | AppExchanger, inc.
#   4 | 2012-03-11 13:26:52.184347 |            | FreeMyApps, inc.
#
#  Table employees with random data from 1 to 2500:
#
#  id  |     created_at      |         updated_at         |              name               |   salary    | company_id
#------+---------------------+----------------------------+---------------------------------+-------------+------------
#    1 | 2011-02-27 09:30:12 |                            | Winston J. Sillypants, I        |  $89,653.00 |     2
#    2 | 2011-02-08 01:08:50 |                            | Winston J. Sillypants, II       |  $94,759.00 |     4
#    3 | 2011-12-08 19:54:21 | 2012-03-27 11:38:50.674123 | Winston J. Sillypants, III      |  $73,726.00 |     2
#  ...
# 2498 | 2011-01-18 01:43:42 | 2012-03-27 10:38:36.029443 | Picholine Pimplenad, MMCDXCVIII |  $61,107.00 |     3
# 2499 | 2011-11-03 21:07:05 | 2012-03-27 11:38:50.656815 | Picholine Pimplenad, MMCDXCIX   |  $62,638.00 |     4
# 2500 | 2011-12-26 04:03:57 | 2012-03-27 10:38:36.039419 | Picholine Pimplenad, MMD        | $102,855.00 |     1
#
#  Table awards with random data from 1 to 2500:
#
#  id  |         created_at         | employee_created_at | awarded_on | employee_id |           award_title
#------+----------------------------+---------------------+------------+-------------+---------------------------------
#    1 | 2012-03-27 10:38:39.728924 | 2011-10-17 13:44:07 | 2012-07-02 |         440 | You're the best I
#    2 | 2012-03-27 10:38:39.728924 | 2011-06-15 20:00:02 | 2012-06-07 |         921 | You're the best II
#    3 | 2012-03-27 10:38:39.728924 | 2011-11-20 06:30:34 | 2012-10-16 |         349 | You're the best III
#  ...
# 2498 | 2012-03-27 10:38:45.954887 | 2011-10-09 22:51:04 | 2012-07-10 |        1421 | You're the best MMCDXCVIII
# 2499 | 2012-03-27 10:38:45.959831 | 2011-01-07 02:25:00 | 2011-09-07 |        1717 | You're the best MMCDXCIX
# 2500 | 2012-03-27 10:38:45.964599 | 2011-08-23 08:02:50 | 2012-03-27 |         515 | You're the greatest DCCCXXXVIII
#
#  Partition employees_partitions.p20101227 - partition where
#  created_at >= '2010-12-27 00:00:00' AND created_at < '2011-01-03 00:00:00':
#
#  id  |     created_at      |         updated_at         |              name               |   salary    | company_id
#------+---------------------+----------------------------+---------------------------------+-------------+------------
#  641 | 2011-01-01 06:32:16 |                            | Winston J. Sillypants, DCXLI    | $103,834.00 |     4
#  644 | 2011-01-02 08:00:35 | 2012-03-27 11:38:50.70092  | Winston J. Sillypants, DCXLIV   |  $98,756.00 |     4
#  766 | 2011-01-01 18:22:34 | 2012-03-27 11:38:50.70092  | Winston J. Sillypants, DCCLXVI  | $133,384.00 |     1
#  ...
# 2058 | 2011-01-01 09:07:52 | 2012-03-27 10:38:33.742273 | Picholine Pimplenad, MMLVIII    | $130,422.00 |     3
# 2422 | 2011-01-01 07:44:38 | 2012-03-27 10:38:35.604894 | Picholine Pimplenad, MMCDXXII   |  $86,484.00 |     3
# 2482 | 2011-01-01 07:52:26 | 2012-03-27 10:38:35.890074 | Picholine Pimplenad, MMCDLXXXII | $113,265.00 |     2
#
#  Partition employees_partitions.p20110103 - partition where
#  created_at >= '2011-01-03 00:00:00' AND created_at < '2011-01-10 00:00:00':
#
#  id  |     created_at      |         updated_at         |              name               |   salary    | company_id
#------+---------------------+----------------------------+---------------------------------+-------------+------------
#   22 | 2011-01-09 03:20:24 | 2012-03-27 11:38:50.588609 | Winston J. Sillypants, XXII     | $139,538.00 |     2
#   63 | 2011-01-09 04:51:41 |                            | Winston J. Sillypants, LXIII    | $127,878.00 |     2
#  282 | 2011-01-03 09:19:16 | 2012-03-27 10:38:37.896505 | Winston J. Sillypants, CCLXXXII | $106,733.00 |     3
#  ...
# 2366 | 2011-01-07 19:18:54 | 2012-03-27 11:38:50.588609 | Picholine Pimplenad, MMCCCLXVI  | $112,775.00 |     4
# 2392 | 2011-01-08 21:19:03 | 2012-03-27 10:38:35.463625 | Picholine Pimplenad, MMCCCXCII  | $106,327.00 |     4
# 2399 | 2011-01-03 06:04:39 | 2012-03-27 10:38:35.497058 | Picholine Pimplenad, MMCCCXCIX  |  $77,089.00 |     2
#
#  Partition employees_partitions.p20110110 - partition where
#  created_at >= '2011-01-10 00:00:00' AND created_at < '2011-01-17 00:00:00':
#
#  id  |     created_at      |         updated_at         |              name               |   salary    | company_id
#------+---------------------+----------------------------+---------------------------------+-------------+------------
#   30 | 2011-01-15 11:53:36 |                            | Winston J. Sillypants, XXX      | $130,203.00 |     4
#   34 | 2011-01-16 20:01:25 | 2012-03-27 10:38:37.428899 | Winston J. Sillypants, XXXIV    |  $69,225.00 |     4
#  109 | 2011-01-12 08:17:06 |                            | Winston J. Sillypants, CIX      | $127,294.00 |     4
#  ...
# 2325 | 2011-01-13 04:17:58 | 2012-03-27 11:38:50.661251 | Picholine Pimplenad, MMCCCXXV   |  $98,912.00 |     2
# 2362 | 2011-01-14 06:48:52 | 2012-03-27 10:38:35.316907 | Picholine Pimplenad, MMCCCLXII  | $116,023.00 |     2
# 2403 | 2011-01-12 00:28:05 | 2012-03-27 10:38:38.022877 | Picholine Pimplenad, MMCDIII    | $106,252.00 |     3
#
# ...
#
#  Partition employees_partitions.p20111212 - partition where
#  created_at >= '2011-12-12 00:00:00' AND created_at < '2011-12-19 00:00:00':
#
#  id  |     created_at      |         updated_at         |               name                |   salary    | company_id
#------+---------------------+----------------------------+-----------------------------------+-------------+------------
#   85 | 2011-12-15 00:35:46 |                            | Winston J. Sillypants, LXXXV      | $110,469.00 |     2
#  137 | 2011-12-12 10:58:14 |                            | Winston J. Sillypants, CXXXVII    |  $96,201.00 |     1
#  222 | 2011-12-14 07:13:52 | 2012-03-27 11:38:50.556991 | Winston J. Sillypants, CCXXII     | $127,907.00 |     4
#  ...
# 2351 | 2011-12-17 17:04:37 | 2012-03-27 10:38:35.26375  | Picholine Pimplenad, MMCCCLI      |  $82,418.00 |     3
# 2383 | 2011-12-12 22:01:11 | 2012-03-27 10:38:35.420221 | Picholine Pimplenad, MMCCCLXXXIII |  $62,355.00 |     2
# 2391 | 2011-12-18 02:42:22 | 2012-03-27 10:38:35.458827 | Picholine Pimplenad, MMCCCXCI     | $116,403.00 |     4
#
#  Partition employees_partitions.p20111219 - partition where
#  created_at >= '2011-12-19 00:00:00' AND created_at < '2011-12-26 00:00:00':
#
#  id  |     created_at      |         updated_at         |               name               |   salary    | company_id
#------+---------------------+----------------------------+----------------------------------+-------------+------------
#    7 | 2011-12-20 04:40:07 | 2012-03-27 11:38:50.492916 | Winston J. Sillypants, VII       |  $89,608.00 |      4
#   84 | 2011-12-25 14:45:16 |                            | Winston J. Sillypants, LXXXIV    | $122,661.00 |      2
#  120 | 2011-12-19 21:18:09 |                            | Winston J. Sillypants, CXX       | $116,477.00 |      3
#  ...
# 2256 | 2011-12-22 22:28:13 | 2012-03-27 10:38:34.755228 | Picholine Pimplenad, MMCCLVI     | $106,724.00 |      1
# 2384 | 2011-12-23 13:27:09 | 2012-03-27 10:38:35.425007 | Picholine Pimplenad, MMCCCLXXXIV | $107,816.00 |      2
# 2434 | 2011-12-24 05:43:52 | 2012-03-27 10:38:35.65791  | Picholine Pimplenad, MMCDXXXIV   |  $69,897.00 |      1
#
#  Partition employees_partitions.p20111226 - partition where
#  created_at >= '2011-12-26 00:00:00' AND created_at < '2012-01-02 00:00:00':
#
#  id  |     created_at      |         updated_at         |             name              |   salary    | company_id
#------+---------------------+----------------------------+-------------------------------+-------------+------------
#    4 | 2011-12-28 09:59:33 |                            | Winston J. Sillypants, IV     |  $75,740.00 |     3
#   33 | 2011-12-26 00:15:53 |                            | Winston J. Sillypants, XXXIII |  $72,866.00 |     4
#  134 | 2011-12-30 22:29:57 |                            | Winston J. Sillypants, CXXXIV | $101,256.00 |     2
#  ...
# 2417 | 2011-12-26 14:49:13 | 2012-03-27 10:38:35.584125 | Picholine Pimplenad, MMCDXVII | $111,609.00 |     1
# 2420 | 2011-12-30 22:15:29 | 2012-03-27 10:38:35.596825 | Picholine Pimplenad, MMCDXX   | $105,180.00 |     2
# 2500 | 2011-12-26 04:03:57 | 2012-03-27 10:38:36.039419 | Picholine Pimplenad, MMD      | $102,855.00 |     1
#
#  Partition awards_partitions.p20101227 - partition where
#  employee_created_at >= '2010-12-27 00:00:00' AND employee_created_at < '2011-01-03 00:00:00':
#
#  id  |         created_at         | employee_created_at | awarded_on | employee_id |        award_title
#------+----------------------------+---------------------+------------+-------------+----------------------------
#   33 | 2012-03-27 10:38:39.728924 | 2011-01-02 05:58:21 | 2011-09-19 |        1811 | You're the best XXXIII
#  259 | 2012-03-27 10:38:39.728924 | 2011-01-02 10:54:32 | 2011-03-23 |         873 | You're the best CCLIX
#  261 | 2012-03-27 10:38:39.728924 | 2011-01-02 10:54:32 | 2011-12-10 |         873 | You're the best CCLXI
#  ...
# 2269 | 2012-03-27 10:38:44.743054 | 2011-01-02 05:58:21 | 2012-03-27 |        1811 | You're the greatest CDXV
# 2408 | 2012-03-27 10:38:45.458391 | 2011-01-01 06:32:16 | 2011-06-25 |         641 | You're the best MMCDVIII
# 2484 | 2012-03-27 10:38:45.887678 | 2011-01-02 05:58:21 | 2011-08-22 |        1811 | You're the best MMCDLXXXIV
#
#  Partition awards_partitions.p20110103 - partition where
#  employee_created_at >= '2011-01-03 00:00:00' AND employee_created_at < '2011-01-10 00:00:00':
#
#  id  |         created_at         | employee_created_at | awarded_on | employee_id |         award_title
#------+----------------------------+---------------------+------------+-------------+------------------------------
#   61 | 2012-03-27 10:38:39.728924 | 2011-01-07 19:18:54 | 2012-03-27 |        2366 | You're the greatest CCCXLIII
#  134 | 2012-03-27 10:38:39.728924 | 2011-01-07 19:33:11 | 2011-05-17 |         810 | You're the best CXXXIV
#  193 | 2012-03-27 10:38:39.728924 | 2011-01-04 06:58:09 | 2011-05-27 |         836 | You're the best CXCIII
#  ...
# 2218 | 2012-03-27 10:38:44.49816  | 2011-01-06 21:05:51 | 2011-07-16 |        2190 | You're the best MMCCXVIII
# 2245 | 2012-03-27 10:38:44.630179 | 2011-01-08 11:44:32 | 2011-04-17 |         841 | You're the best MMCCXLV
# 2499 | 2012-03-27 10:38:45.959831 | 2011-01-07 02:25:00 | 2011-09-07 |        1717 | You're the best MMCDXCIX
#
#  Partition employees_partitions.p20110110 - partition where
#  employee_created_at >= '2011-01-10 00:00:00' AND employee_created_at < '2011-01-17 00:00:00':
#
#  id  |         created_at         | employee_created_at | awarded_on | employee_id |        award_title
#------+----------------------------+---------------------+------------+-------------+---------------------------
#   36 | 2012-03-27 10:38:39.728924 | 2011-01-12 03:12:37 | 2011-06-23 |        1196 | You're the best XXXVI
#   44 | 2012-03-27 10:38:39.728924 | 2011-01-15 20:43:01 | 2011-10-09 |        1159 | You're the best XLIV
#   93 | 2012-03-27 10:38:39.728924 | 2011-01-16 14:45:36 | 2011-10-02 |         912 | You're the best XCIII
#  ...
# 2476 | 2012-03-27 10:38:45.848691 | 2011-01-10 23:23:24 | 2011-11-05 |        1314 | You're the best MMCDLXXVI
# 2481 | 2012-03-27 10:38:45.873229 | 2011-01-11 05:12:47 | 2011-08-10 |        2240 | You're the best MMCDLXXXI
# 2491 | 2012-03-27 10:38:45.921378 | 2011-01-12 04:18:22 | 2011-08-13 |        1595 | You're the best MMCDXCI
#
# ...
#
#  Partition awards_partitions.p20111212 - partition where
#  employee_created_at >= '2011-12-12 00:00:00' AND employee_created_at < '2011-12-19 00:00:00':
#
#  id  |         created_at         | employee_created_at | awarded_on | employee_id |          award_title
#------+----------------------------+---------------------+------------+-------------+--------------------------------
#   76 | 2012-03-27 10:38:39.728924 | 2011-12-17 11:12:41 | 2012-03-27 |        1548 | You're the greatest DXXIII
#  159 | 2012-03-27 10:38:39.728924 | 2011-12-17 17:04:37 | 2012-03-27 |        2351 | You're the greatest DCCCLXXXII
#  181 | 2012-03-27 10:38:39.728924 | 2011-12-18 14:25:29 | 2012-04-20 |        2031 | You're the best CLXXXI
#  ...
# 2197 | 2012-03-27 10:38:44.39818  | 2011-12-13 00:14:00 | 2012-06-11 |         555 | You're the best MMCXCVII
# 2216 | 2012-03-27 10:38:44.488553 | 2011-12-13 17:08:24 | 2011-12-26 |         596 | You're the best MMCCXVI
# 2217 | 2012-03-27 10:38:44.493389 | 2011-12-16 18:12:58 | 2012-12-14 |        1860 | You're the best MMCCXVII
#
#  Partition awards_partitions.p20111219 - partition where
#  employee_created_at >= '2011-12-19 00:00:00' AND employee_created_at < '2011-12-26 00:00:00':
#
#  id  |         created_at         | employee_created_at | awarded_on | employee_id |         award_title
#------+----------------------------+---------------------+------------+-------------+-----------------------------
#   16 | 2012-03-27 10:38:39.728924 | 2011-12-23 17:53:33 | 2012-05-06 |        1534 | You're the best XVI
#  132 | 2012-03-27 10:38:39.728924 | 2011-12-19 21:58:52 | 2012-08-28 |         979 | You're the best CXXXII
#  149 | 2012-03-27 10:38:39.728924 | 2011-12-24 06:28:33 | 2012-04-06 |         507 | You're the best CXLIX
#  ...
# 2451 | 2012-03-27 10:38:45.726195 | 2011-12-23 22:40:28 | 2012-11-05 |         914 | You're the best MMCDLI
# 2483 | 2012-03-27 10:38:45.882936 | 2011-12-20 13:54:57 | 2012-05-09 |        1615 | You're the best MMCDLXXXIII
# 2485 | 2012-03-27 10:38:45.892318 | 2011-12-25 06:54:49 | 2012-03-27 |        2017 | You're the greatest CLIII
#
#  Partition awards_partitions.p20111226 - partition where
#  employee_created_at >= '2011-12-26 00:00:00' AND employee_created_at < '2012-01-02 00:00:00':
#
#  id  |         created_at         | employee_created_at | awarded_on | employee_id |         award_title
#------+----------------------------+---------------------+------------+-------------+-----------------------------
#   15 | 2012-03-27 10:38:39.728924 | 2011-12-27 22:35:27 | 2012-03-27 |        1496 | You're the greatest CXXIII
#   62 | 2012-03-27 10:38:39.728924 | 2011-12-27 18:36:46 | 2012-03-27 |        1663 | You're the greatest CLXXXII
#   64 | 2012-03-27 10:38:39.728924 | 2011-12-26 14:49:13 | 2012-04-02 |        2417 | You're the best LXIV
#  ...
# 2211 | 2012-03-27 10:38:44.464965 | 2011-12-27 10:11:18 | 2012-03-27 |        2388 | You're the greatest XXX
# 2291 | 2012-03-27 10:38:44.908378 | 2011-12-26 08:07:37 | 2012-03-27 |         242 | You're the greatest LXXXVI
# 2497 | 2012-03-27 10:38:45.949948 | 2011-12-27 16:51:56 | 2012-11-10 |        1902 | You're the best MMCDXCVII
#

require File.expand_path(File.dirname(__FILE__) + "/lib/command_line_tool_mixin")
require File.expand_path(File.dirname(__FILE__) + "/lib/get_options")

include CommandLineToolMixin

$cleanup = false
$force = false
$create_many = 1500
$create_individual = 500
$new_individual = 500
$update_many = 500
$update_individual = 500

@options = {
  "--cleanup" => {
    :short => "-C",
    :argument => GetoptLong::NO_ARGUMENT,
    :note => "cleanup data in database and exit"
  },
  "--force" => {
    :short => "-F",
    :argument => GetoptLong::NO_ARGUMENT,
    :note => "cleanup data in database before creating new data"
  },
  "--create-many" => {
    :short => "-m",
    :argument => GetoptLong::REQUIRED_ARGUMENT,
    :note => "how many objects to create via create_many",
    :argument_note => "NUMBER"
  },
  "--create-individual" => {
    :short => "-i",
    :argument => GetoptLong::REQUIRED_ARGUMENT,
    :note => "how many objects to create via create",
    :argument_note => "NUMBER"
  },
  "--new-individual" => {
    :short => "-I",
    :argument => GetoptLong::REQUIRED_ARGUMENT,
    :note => "how many objects to create via new/save",
    :argument_note => "NUMBER"
  },
  "--update-individual" => {
    :short => "-u",
    :argument => GetoptLong::REQUIRED_ARGUMENT,
    :note => "how many objects to update indivudually",
    :argument_note => "NUMBER"
  },
  "--update-many" => {
    :short => "-U",
    :argument => GetoptLong::REQUIRED_ARGUMENT,
    :note => "how many objects to update via update_many",
    :argument_note => "NUMBER"
  },
}

command_line_options(@options) do |option,argument|
  if option == '--cleanup'
    $cleanup = true
  elsif option == '--force'
    $force = true
  elsif option == '--create-many'
    $create_many = argument.to_i
  elsif option == '--create-individual'
    $create_individual = argument.to_i
  elsif option == '--new-individual'
    $new_individual = argument.to_i
  elsif option == '--update-individual'
    $update_individual = argument.to_i
  elsif option == '--update-many'
    $update_many = argument.to_i
  end
end

if $cleanup || $force
  ActiveRecord::Base.connection.drop_schema("awards_partitions", :cascade => true) rescue nil
  ActiveRecord::Base.connection.drop_table("awards") rescue nil
  ActiveRecord::Base.connection.drop_schema("employees_partitions", :cascade => true) rescue nil
  ActiveRecord::Base.connection.drop_table("employees") rescue nil
  ActiveRecord::Base.connection.drop_table("companies") rescue nil
  exit(0) if $cleanup
end

$total_records = ($create_many + $create_individual + $new_individual) * 2

puts "total records: #{$total_records}"

START_DATE = Date.parse('2011-01-01')
END_DATE = Date.parse('2011-12-31')

# the ActiveRecord classes

require File.expand_path(File.dirname(__FILE__) + "/lib/company")

class Employee < Partitioned::ByCreatedAt
  belongs_to :company, :class_name => 'Company'
  attr_accessible :created_at, :salary, :name, :company_id

  partitioned do |partition|
    partition.index :id, :unique => true
    partition.foreign_key :company_id
  end
    
  connection.execute <<-SQL
    create table employees
    (
        id               serial not null primary key,
        created_at       timestamp not null default now(),
        updated_at       timestamp,
        name             text not null,
        salary           money not null,
        company_id       integer not null
    );
  SQL
end

class ByEmployeeCreatedAt < Partitioned::ByWeeklyTimeField
  self.abstract_class = true

  def self.partition_time_field
    return :employee_created_at
  end
end

class Award < ByEmployeeCreatedAt
  belongs_to :employee, :class_name => 'Employee'
  attr_accessible :award_title, :employee_created_at, :employee_id, :awarded_on

  partitioned do |partition|
    partition.foreign_key lambda {|model, *partition_key_values|
      return Configurator::Data::ForeignKey.new(:employee_id, Employee.partition_name(*partition_key_values), :id)
    }
  end
  
  connection.execute <<-SQL
    create table awards
    (
        id                    serial not null primary key,
        created_at            timestamp not null default now(),
        employee_created_at   timestamp not null,
        awarded_on            date not null,
        employee_id           integer not null,
        award_title           text not null
    );
  SQL
end

# You should have the following tables:
#  public.companies
#  public.employees

# create the infrastructure for EMPLOYEES table which includes the schema and partition tables

Employee.create_infrastructure

# create the infrastructure for Awards table which includes the schema and partition tables

Award.create_infrastructure

# You should have the following schema:
#  employees_partitions
#  awards_partitions

dates = Employee.partition_generate_range(START_DATE, END_DATE)

Employee.create_new_partition_tables(dates)

# You should have the following tables with increments of one week:
#  employees_partitions.p20101227
#  employees_partitions.p20110103
#  employees_partitions.p20110110
#  employees_partitions.p20110117
#  employees_partitions.p20110124
#  employees_partitions.p20110131
#  employees_partitions.p20110207
#  employees_partitions.p20110214
#  employees_partitions.p20110221
#  employees_partitions.p20110228
#  employees_partitions.p20110307
#  employees_partitions.p20110314
#  employees_partitions.p20110321
#  employees_partitions.p20110328
#  employees_partitions.p20110404
#  employees_partitions.p20110411
#  employees_partitions.p20110418
#  employees_partitions.p20110425
#  employees_partitions.p20110502
#  employees_partitions.p20110509
#  employees_partitions.p20110516
#  employees_partitions.p20110523
#  employees_partitions.p20110530
#  employees_partitions.p20110606
#  employees_partitions.p20110613
#  employees_partitions.p20110620
#  employees_partitions.p20110627
#  employees_partitions.p20110704
#  employees_partitions.p20110711
#  employees_partitions.p20110718
#  employees_partitions.p20110725
#  employees_partitions.p20110801
#  employees_partitions.p20110808
#  employees_partitions.p20110815
#  employees_partitions.p20110822
#  employees_partitions.p20110829
#  employees_partitions.p20110905
#  employees_partitions.p20110912
#  employees_partitions.p20110919
#  employees_partitions.p20110926
#  employees_partitions.p20111003
#  employees_partitions.p20111010
#  employees_partitions.p20111017
#  employees_partitions.p20111024
#  employees_partitions.p20111031
#  employees_partitions.p20111107
#  employees_partitions.p20111114
#  employees_partitions.p20111121
#  employees_partitions.p20111128
#  employees_partitions.p20111205
#  employees_partitions.p20111212
#  employees_partitions.p20111219
#  employees_partitions.p20111226

Award.create_new_partition_tables(dates)

# You should have the following tables with increments of one week:
#  awards_partitions.p20101227
#  awards_partitions.p20110103
#  awards_partitions.p20110110
#  awards_partitions.p20110117
#  awards_partitions.p20110124
#  awards_partitions.p20110131
#  awards_partitions.p20110207
#  awards_partitions.p20110214
#  awards_partitions.p20110221
#  awards_partitions.p20110228
#  awards_partitions.p20110307
#  awards_partitions.p20110314
#  awards_partitions.p20110321
#  awards_partitions.p20110328
#  awards_partitions.p20110404
#  awards_partitions.p20110411
#  awards_partitions.p20110418
#  awards_partitions.p20110425
#  awards_partitions.p20110502
#  awards_partitions.p20110509
#  awards_partitions.p20110516
#  awards_partitions.p20110523
#  awards_partitions.p20110530
#  awards_partitions.p20110606
#  awards_partitions.p20110613
#  awards_partitions.p20110620
#  awards_partitions.p20110627
#  awards_partitions.p20110704
#  awards_partitions.p20110711
#  awards_partitions.p20110718
#  awards_partitions.p20110725
#  awards_partitions.p20110801
#  awards_partitions.p20110808
#  awards_partitions.p20110815
#  awards_partitions.p20110822
#  awards_partitions.p20110829
#  awards_partitions.p20110905
#  awards_partitions.p20110912
#  awards_partitions.p20110919
#  awards_partitions.p20110926
#  awards_partitions.p20111003
#  awards_partitions.p20111010
#  awards_partitions.p20111017
#  awards_partitions.p20111024
#  awards_partitions.p20111031
#  awards_partitions.p20111107
#  awards_partitions.p20111114
#  awards_partitions.p20111121
#  awards_partitions.p20111128
#  awards_partitions.p20111205
#  awards_partitions.p20111212
#  awards_partitions.p20111219
#  awards_partitions.p20111226

# add some companies

Company.create_many(COMPANIES)
company_ids = Company.all.map(&:id)

# now add some employees across the year.

employees = []

require File.expand_path(File.dirname(__FILE__) + "/lib/roman")

# generates data for employees_partitions and employees tables

base = 0
(1..$create_many).each do |i|
  employees << {
    :name => "Winston J. Sillypants, #{to_roman(base+i)}",
    :created_at => START_DATE + rand(END_DATE - START_DATE) + rand(1.day.seconds).seconds,
    :salary => rand(80000) + 60000,
    :company_id => company_ids[rand company_ids.length]
  }
end

puts "creating many employees #{$create_many}"
Employee.create_many(employees)
base += $create_many

puts "creating individual employees #{$create_individual}"
(1..$create_individual).each do |i|
  employee_data = {
    :name => "Jonathan Crabapple, #{to_roman(base+i)}",
    :created_at => START_DATE + rand(END_DATE - START_DATE) + rand(1.day.seconds).seconds,
    :salary => rand(80000) + 60000,
    :company_id => company_ids[rand company_ids.length]
  }
  employees << Employee.create(employee_data)
end
base += $create_individual

puts "new individual employees #{$new_individual}"
(1..$new_individual).each do |i|
  employee_data = {
    :name => "Picholine Pimplenad, #{to_roman(base+i)}",
    :created_at => START_DATE + rand(END_DATE - START_DATE) + rand(1.day.seconds).seconds,
    :salary => rand(80000) + 60000,
    :company_id => company_ids[rand company_ids.length]
  }
  employee = Employee.new(employee_data)
  employee.save
  employees << employee
end
base += $new_individual

updates = {}
puts "update many employees #{$update_many}"
(1..$update_many).each do |i|
  index = rand(employees.length)
  employee_record = employees[index]
  updates[{
            :id => employee_record[:id],
            :created_at => employee_record[:created_at]
          }] = {
    :salary => 100
  }
end

Employee.update_many(updates, {:set_array => '"salary = #{table_name}.salary + datatable.salary, updated_at = now()"'})

puts "update individual employees #{$update_individual}"
(1..$update_individual).each do |i|
  index = rand(employees.length)
  employee_record = employees[index]
  employee = Employee.from_partition(employee_record[:created_at]).find(employee_record[:id])
  employee.salary += 1000
  employee.save
end

# generates data for awards_partitions and awards tables

awards = []

base = 0
(1..$create_many).each do |i|
  employee_record = employees[rand(employees.length)]
  awards << {
    :award_title => "You're the best #{to_roman(base+i)}",
    :awarded_on => employee_record[:created_at] + rand(1.year.seconds).seconds,
    :employee_created_at => employee_record[:created_at],
    :employee_id => employee_record[:id]
  }
end

puts "creating many awards #{$create_many}"
Award.create_many(awards)
base += $create_many

puts "creating individual awards #{$create_individual}"
(1..$create_individual).each do |i|
  employee_record = employees[rand(employees.length)]
  award_data = {
    :award_title => "You're the best #{to_roman(base+i)}",
    :awarded_on => employee_record[:created_at] + rand(1.year.seconds).seconds,
    :employee_created_at => employee_record[:created_at],
    :employee_id => employee_record[:id]
  }
  awards << Award.create(award_data)
end
base += $create_individual

puts "new individual awards #{$new_individual}"
(1..$new_individual).each do |i|
  employee_record = employees[rand(employees.length)]
  award_data = {
    :award_title => "You're the best #{to_roman(base+i)}",
    :awarded_on => employee_record[:created_at] + rand(1.year.seconds).seconds,
    :employee_created_at => employee_record[:created_at],
    :employee_id => employee_record[:id]
  }
  award = Award.new(award_data)
  award.save
  awards << award
end

base = 0
updates = {}
puts "update many awards #{$update_many}"
(1..$update_many).each do |i|
  award_record = awards[rand(awards.length)]
  updates[{
            :id => award_record[:id],
            :employee_created_at => award_record[:employee_created_at]
          }] = {
    :award_title => "You're the greatest #{to_roman(base+i)}"
  }
end

Award.update_many(updates, {:set_array => '"award_title = datatable.award_title, awarded_on = now()"'})
base += $update_many

puts "update individual #{$update_individual}"
(1..$update_individual).each do |i|
  award_record = awards[rand(awards.length)]
  award = Award.from_partition(award_record[:employee_created_at]).find(award_record[:id])
  award.awarded_on = Date.parse(Time.now.to_s)
  award.award_title = "You're the greatest #{to_roman(base+i)}"
  award.save
end