<commit_before># encoding: utf-8

namespace :db do
  task :require_spec_helper do
    require File.expand_path("../spec_helper", __FILE__)
  end

  desc "Create test database tables"
  task :create_tables => :require_spec_helper do
    puts "==> Creating tables for test data"
    ActiveRecord::Schema.define do

      create_table :time, :force => true do |t|
        t.datetime    :the_date
        t.string      :the_day, :limit => 30
        t.string      :the_month, :limit => 30
        t.integer     :the_year
        t.integer     :day_of_month
        t.integer     :week_of_year
        t.integer     :month_of_year
        t.string      :quarter, :limit => 30
      end

      create_table :products, :force => true do |t|
        t.integer     :product_class_id
        t.string      :brand_name, :limit => 60
        t.string      :product_name, :limit => 60
      end

      create_table :product_classes, :force => true do |t|
        t.string      :product_subcategory, :limit => 30
        t.string      :product_category, :limit => 30
        t.string      :product_department, :limit => 30
        t.string      :product_family, :limit => 30
      end

      create_table :customers, :force => true do |t|
        t.string      :country, :limit => 30
        t.string      :state_province, :limit => 30
        t.string      :city, :limit => 30
        t.string      :fname, :limit => 30
        t.string      :lname, :limit => 30
        t.string      :fullname, :limit => 60
        t.string      :gender, :limit => 30
        t.integer     :promotion_id
        t.string      :related_fullname, :limit => 60
        # Mondrian does not support properties with Oracle CLOB type
        # as it tries to GROUP BY all columns when loading a dimension table
        if MONDRIAN_DRIVER == 'oracle'
          t.string      :description, :limit => 4000
        else
          t.text        :description
        end
      end

      if MONDRIAN_DRIVER == 'oracle'

        execute "DROP TABLE PROMOTIONS" rescue nil

        execute <<-SQL
          CREATE TABLE PROMOTIONS(
            ID NUMBER(*,0) NOT NULL,
            PROMOTION VARCHAR2(30 CHAR),
            SEQUENCE NUMBER(38,0),
            PRIMARY KEY ("ID")
          )
        SQL
      else
        create_table :promotions, :force => true do |t|
          t.string      :promotion, :limit => 30
          t.integer     :sequence
        end
      end

      case MONDRIAN_DRIVER
      when /mysql/
        execute "ALTER TABLE customers MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT"
      when /postgresql/
        execute "ALTER TABLE customers ALTER COLUMN id SET DATA TYPE bigint"
      when /mssql|sqlserver/
        sql = "SELECT name FROM sysobjects WHERE xtype = 'PK' AND parent_obj=OBJECT_ID('customers')"
        primary_key_constraint = select_value(sql)
        execute "ALTER TABLE customers DROP CONSTRAINT #{primary_key_constraint}"
        execute "ALTER TABLE customers ALTER COLUMN id BIGINT"
        execute "ALTER TABLE customers ADD CONSTRAINT #{primary_key_constraint} PRIMARY KEY (id)"
      end

      create_table :sales, :force => true, :id => false do |t|
        t.integer     :product_id
        t.integer     :time_id
        t.integer     :customer_id
        t.integer     :promotion_id
        t.decimal     :store_sales, :precision => 10, :scale => 4
        t.decimal     :store_cost, :precision => 10, :scale => 4
        t.decimal     :unit_sales, :precision => 10, :scale => 4
      end
    end
  end

  task :define_models => :require_spec_helper do
    class TimeDimension < ActiveRecord::Base
      self.table_name = "time"
      validates_presence_of :the_date
      before_create do
        self.the_day = the_date.strftime("%A")
        self.the_month = the_date.strftime("%B")
        self.the_year = the_date.strftime("%Y").to_i
        self.day_of_month = the_date.strftime("%d").to_i
        self.week_of_year = the_date.strftime("%W").to_i
        self.month_of_year = the_date.strftime("%m").to_i
        self.quarter = "Q#{(month_of_year-1)/3+1}"
      end
    end
    class Product < ActiveRecord::Base
      belongs_to :product_class
    end
    class ProductClass < ActiveRecord::Base
    end
    class Customer < ActiveRecord::Base
    end
    class Promotion < ActiveRecord::Base
    end
    class Sales < ActiveRecord::Base
      self.table_name = "sales"
      belongs_to :time_by_day
      belongs_to :product
      belongs_to :customer
    end
  end

  desc "Create test data"
  task :create_data => [:create_tables] + ( %w(vertica snowflake).include?(ENV['MONDRIAN_DRIVER']) ? [:import_data] :
    [ :create_time_data, :create_product_data, :create_promotion_data, :create_customer_data, :create_sales_data ] )

  task :create_time_data  => :define_models do
    puts "==> Creating time dimension"
    TimeDimension.delete_all
    start_time = Time.utc(2010,1,1)
    (2*365).times do |i|
      TimeDimension.create!(:the_date => start_time + i.day)
    end
  end

  task :create_product_data => :define_models do
    puts "==> Creating product data"
    Product.delete_all
    ProductClass.delete_all
    families = ["Drink", "Food", "Non-Consumable"]
    (1..100).each do |i|
      product_class = ProductClass.create!(
        :product_family => families[i % 3],
        :product_department => "Department #{i}",
        :product_category => "Category #{i}",
        :product_subcategory => "Subcategory #{i}"
      )
      Product.create!(
        :product_class_id => ProductClass.where(:product_category => "Category #{i}").to_a.first.id,
        :brand_name => "Brand #{i}",
        :product_name => "Product #{i}"
      )
    end
  end

  task :create_promotion_data => :define_models do
    puts "==> Creating promotion data"
    Promotion.delete_all
    (1..10).each do |i|
      Promotion.create!(id: i, promotion: "Promotion #{i}", sequence: i)
    end
  end

  task :create_customer_data => :define_models do
    puts "==> Creating customer data"
    Customer.delete_all
    promotions = Promotion.order("id").to_a
    i = 0
    [
      ["Canada", "BC", "Burnaby"],["Canada", "BC", "Cliffside"],["Canada", "BC", "Haney"],["Canada", "BC", "Ladner"],
      ["Canada", "BC", "Langford"],["Canada", "BC", "Langley"],["Canada", "BC", "Metchosin"],["Canada", "BC", "N. Vancouver"],
      ["Canada", "BC", "Newton"],["Canada", "BC", "Oak Bay"],["Canada", "BC", "Port Hammond"],["Canada", "BC", "Richmond"],
      ["Canada", "BC", "Royal Oak"],["Canada", "BC", "Shawnee"],["Canada", "BC", "Sooke"],["Canada", "BC", "Vancouver"],<commit_msg>Don’t insert ID column value<commit_after># encoding: utf-8

namespace :db do
  task :require_spec_helper do
    require File.expand_path("../spec_helper", __FILE__)
  end

  desc "Create test database tables"
  task :create_tables => :require_spec_helper do
    puts "==> Creating tables for test data"
    ActiveRecord::Schema.define do

      create_table :time, :force => true do |t|
        t.datetime    :the_date
        t.string      :the_day, :limit => 30
        t.string      :the_month, :limit => 30
        t.integer     :the_year
        t.integer     :day_of_month
        t.integer     :week_of_year
        t.integer     :month_of_year
        t.string      :quarter, :limit => 30
      end

      create_table :products, :force => true do |t|
        t.integer     :product_class_id
        t.string      :brand_name, :limit => 60
        t.string      :product_name, :limit => 60
      end

      create_table :product_classes, :force => true do |t|
        t.string      :product_subcategory, :limit => 30
        t.string      :product_category, :limit => 30
        t.string      :product_department, :limit => 30
        t.string      :product_family, :limit => 30
      end

      create_table :customers, :force => true do |t|
        t.string      :country, :limit => 30
        t.string      :state_province, :limit => 30
        t.string      :city, :limit => 30
        t.string      :fname, :limit => 30
        t.string      :lname, :limit => 30
        t.string      :fullname, :limit => 60
        t.string      :gender, :limit => 30
        t.integer     :promotion_id
        t.string      :related_fullname, :limit => 60
        # Mondrian does not support properties with Oracle CLOB type
        # as it tries to GROUP BY all columns when loading a dimension table
        if MONDRIAN_DRIVER == 'oracle'
          t.string      :description, :limit => 4000
        else
          t.text        :description
        end
      end

      if MONDRIAN_DRIVER == 'oracle'

        execute "DROP TABLE PROMOTIONS" rescue nil

        execute <<-SQL
          CREATE TABLE PROMOTIONS(
            ID NUMBER(*,0) NOT NULL,
            PROMOTION VARCHAR2(30 CHAR),
            SEQUENCE NUMBER(38,0),
            PRIMARY KEY ("ID")
          )
        SQL
      else
        create_table :promotions, :force => true do |t|
          t.string      :promotion, :limit => 30
          t.integer     :sequence
        end
      end

      case MONDRIAN_DRIVER
      when /mysql/
        execute "ALTER TABLE customers MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT"
      when /postgresql/
        execute "ALTER TABLE customers ALTER COLUMN id SET DATA TYPE bigint"
      when /mssql|sqlserver/
        sql = "SELECT name FROM sysobjects WHERE xtype = 'PK' AND parent_obj=OBJECT_ID('customers')"
        primary_key_constraint = select_value(sql)
        execute "ALTER TABLE customers DROP CONSTRAINT #{primary_key_constraint}"
        execute "ALTER TABLE customers ALTER COLUMN id BIGINT"
        execute "ALTER TABLE customers ADD CONSTRAINT #{primary_key_constraint} PRIMARY KEY (id)"
      end

      create_table :sales, :force => true, :id => false do |t|
        t.integer     :product_id
        t.integer     :time_id
        t.integer     :customer_id
        t.integer     :promotion_id
        t.decimal     :store_sales, :precision => 10, :scale => 4
        t.decimal     :store_cost, :precision => 10, :scale => 4
        t.decimal     :unit_sales, :precision => 10, :scale => 4
      end
    end
  end

  task :define_models => :require_spec_helper do
    class TimeDimension < ActiveRecord::Base
      self.table_name = "time"
      validates_presence_of :the_date
      before_create do
        self.the_day = the_date.strftime("%A")
        self.the_month = the_date.strftime("%B")
        self.the_year = the_date.strftime("%Y").to_i
        self.day_of_month = the_date.strftime("%d").to_i
        self.week_of_year = the_date.strftime("%W").to_i
        self.month_of_year = the_date.strftime("%m").to_i
        self.quarter = "Q#{(month_of_year-1)/3+1}"
      end
    end
    class Product < ActiveRecord::Base
      belongs_to :product_class
    end
    class ProductClass < ActiveRecord::Base
    end
    class Customer < ActiveRecord::Base
    end
    class Promotion < ActiveRecord::Base
    end
    class Sales < ActiveRecord::Base
      self.table_name = "sales"
      belongs_to :time_by_day
      belongs_to :product
      belongs_to :customer
    end
  end

  desc "Create test data"
  task :create_data => [:create_tables] + ( %w(vertica snowflake).include?(ENV['MONDRIAN_DRIVER']) ? [:import_data] :
    [ :create_time_data, :create_product_data, :create_promotion_data, :create_customer_data, :create_sales_data ] )

  task :create_time_data  => :define_models do
    puts "==> Creating time dimension"
    TimeDimension.delete_all
    start_time = Time.utc(2010,1,1)
    (2*365).times do |i|
      TimeDimension.create!(:the_date => start_time + i.day)
    end
  end

  task :create_product_data => :define_models do
    puts "==> Creating product data"
    Product.delete_all
    ProductClass.delete_all
    families = ["Drink", "Food", "Non-Consumable"]
    (1..100).each do |i|
      product_class = ProductClass.create!(
        :product_family => families[i % 3],
        :product_department => "Department #{i}",
        :product_category => "Category #{i}",
        :product_subcategory => "Subcategory #{i}"
      )
      Product.create!(
        :product_class_id => ProductClass.where(:product_category => "Category #{i}").to_a.first.id,
        :brand_name => "Brand #{i}",
        :product_name => "Product #{i}"
      )
    end
  end

  task :create_promotion_data => :define_models do
    puts "==> Creating promotion data"
    Promotion.delete_all
    (1..10).each do |i|
      Promotion.create!(promotion: "Promotion #{i}", sequence: i)
    end
  end

  task :create_customer_data => :define_models do
    puts "==> Creating customer data"
    Customer.delete_all
    promotions = Promotion.order("id").to_a
    i = 0
    [
      ["Canada", "BC", "Burnaby"],["Canada", "BC", "Cliffside"],["Canada", "BC", "Haney"],["Canada", "BC", "Ladner"],
      ["Canada", "BC", "Langford"],["Canada", "BC", "Langley"],["Canada", "BC", "Metchosin"],["Canada", "BC", "N. Vancouver"],
      ["Canada", "BC", "Newton"],["Canada", "BC", "Oak Bay"],["Canada", "BC", "Port Hammond"],["Canada", "BC", "Richmond"],
      ["Canada", "BC", "Royal Oak"],["Canada", "BC", "Shawnee"],["Canada", "BC", "Sooke"],["Canada", "BC", "Vancouver"],
{'fraction_non_alphanumeric': 0.1245257246926696, 'fraction_numerical': 0.01198967976931249, 'mean_word_length': 2.4563335955940206, 'pattern_counts': {'":': 0, '<': 19, '<?xml version=': 0, '>': 109, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2312817', 'n_tokens_mistral': 4372, 'n_tokens_neox': 4115, 'n_words': 1065}