Arel Sample (5)

I continue to write sample code for Arel.

  it "distinct_sample1" do
    i = Arel::Table.new(:items)
    i.table_alias = "i"

    q = i.
      project(Arel.sql("DISTINCT i.*"))

#    We cannot write as following. It make string 'DISTINCT'.
#    q = i.
#      project(Arel.sql("DISTINCT i.*")).
#      distinct

    q.to_sql.should eq "SELECT DISTINCT i.*" +
      " FROM `items` `i` "
  end

  it "case_sample1" do
    od = Arel::Table.new(:order_details)
    od.table_alias = "od"

    # Arel does not support case expression.
    # So we have to write case expression using Arel.sql.
    q = od.
      project(od[:order_no],
        od[:item_no],
        Arel.sql("CASE" +
          " WHEN od.quantity > 20 THEN 'more than 20'" +
          " WHEN od.quantity > 10 THEN 'more than 10 and less then or equal to 20'" +
          " ELSE 'less than or equal to 10'" +
          " END").as("qty_desc"))

    q.to_sql.should eq "SELECT `od`.`order_no`," +
      " `od`.`item_no`," +
      " CASE" +
        " WHEN od.quantity > 20 THEN 'more than 20'" +
        " WHEN od.quantity > 10 THEN 'more than 10 and less then or equal to 20'" +
        " ELSE 'less than or equal to 10'" +
        " END AS qty_desc" +
      " FROM `order_details` `od` "
  end

  it "arithmetic_operations_sample1" do
    od = Arel::Table.new(:order_details)
    od.table_alias = "od"

    q = od.
      project(od[:quantity] + Arel.sql("10"),
        od[:quantity] - Arel.sql("10"),
        od[:quantity] * Arel.sql("2"),
        od[:quantity] / Arel.sql("2"))

    q.to_sql.should eq "SELECT (`od`.`quantity` + 10)," +
      " (`od`.`quantity` - 10)," +
      " `od`.`quantity` * 2," +
      " `od`.`quantity` / 2" +
      " FROM `order_details` `od` "
  end

  it "arithmetic_operations_sample2" do
    od = Arel::Table.new(:order_details)
    od.table_alias = "od"

    # This is seem to be so compilcated. Does exist simpler descriptions?
    q = od.
      project(Arel.sql((od[:quantity] + Arel.sql("10")).to_sql).as("qty_plus_10"),
        Arel.sql((od[:quantity] - Arel.sql("10")).to_sql).as("qty_minus_10"),
        Arel.sql((od[:quantity] * Arel.sql("2")).to_sql).as("qty_twice"),
        Arel.sql((od[:quantity] / Arel.sql("2")).to_sql).as("qty_half"))

    q.to_sql.should eq "SELECT (`od`.`quantity` + 10) AS qty_plus_10," +
      " (`od`.`quantity` - 10) AS qty_minus_10," +
      " `od`.`quantity` * 2 AS qty_twice," +
      " `od`.`quantity` / 2 AS qty_half" +
      " FROM `order_details` `od` "
  end

  it "subquery_sample1" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    od = Arel::Table.new(:order_details)
    od.table_alias = "od"

    q1 = od.
      group(od[:order_no]).
      project(od[:order_no],
        od[:quantity].sum.as("qty"))

    odsum = q1.as("od_sum")

    q = o.
      join(odsum).on(odsum[:order_no].eq(o[:order_no])).
      project(o[:order_no],
        odsum[:qty])

    q.to_sql.should eq "SELECT `o`.`order_no`," +
      " od_sum.`qty`" +
      " FROM `orders` `o`" +
      " INNER JOIN (SELECT `od`.`order_no`," +
        " SUM(`od`.`quantity`) AS qty" +
        " FROM `order_details` `od` " +
        " GROUP BY `od`.`order_no`) od_sum" +
      " ON od_sum.`order_no` = `o`.`order_no`"
  end

Arel Sample (4)

I continue to write sample code for Arel.

  it "exists_sample1" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    od = Arel::Table.new(:order_details)
    od.table_alias = "od"

    q1 = od.
      where(od[:order_no].eq(o[:order_no])).
      where(od[:quantity].gt(10)).
      project(Arel.sql("1"))
        
    q = o.
      where(q1.exists).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE EXISTS (SELECT 1" +
        " FROM `order_details` `od` " +
        " WHERE `od`.`order_no` = `o`.`order_no`" +
        " AND `od`.`quantity` > 10)"
  end

  it "not_exists_sample1" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    od = Arel::Table.new(:order_details)
    od.table_alias = "od"

    q1 = od.
      where(od[:order_no].eq(o[:order_no])).
      where(od[:quantity].gt(10)).
      project(Arel.sql("1"))
        
    # 'not_exists' does not exists. We use 'not' for 'exists'.
    q = o.
      where(q1.exists.not).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE NOT (EXISTS (SELECT 1" +
        " FROM `order_details` `od` " +
        " WHERE `od`.`order_no` = `o`.`order_no`" +
        " AND `od`.`quantity` > 10))"
  end

  it "between_sample1" do
    i = Arel::Table.new(:items)
    i.table_alias = "i"

    date = Date.new(2013,7,24)
    # Arel does not support BETWEEN expression directly.
    # So we have to use Arel.sql. 
    # We can use 'ActiveRecord::Base.sanitize' method for sanitize data.
    # The Model class, for example Item, can be used instead of ActiveRecord::Base.
    # ex.) Item.sanitize(date)
    q = i.
      where(Arel.sql("#{ActiveRecord::Base.sanitize(date)} BETWEEN i.start_date AND i.end_date")).
      project(i[Arel.star])

    q.to_sql.should eq "SELECT `i`.*" +
      " FROM `items` `i` " +
      " WHERE '2013-07-24' BETWEEN i.start_date AND i.end_date"
  end

  it "max_min_avg_alias_sample1" do
    od = Arel::Table.new(:order_details)
    od.table_alias = "od"

    q = od.
      group(od[:order_no]).
      project(od[:order_no],
        od[:quantity].maximum.as("max_qty"),
        od[:quantity].minimum.as("min_qty"),
        od[:quantity].average.as("avg_qty"))

    q.to_sql.should eq "SELECT `od`.`order_no`," +
      " MAX(`od`.`quantity`) AS max_qty," +
      " MIN(`od`.`quantity`) AS min_qty," +
      " AVG(`od`.`quantity`) AS avg_qty" +
      " FROM `order_details` `od` " +
      " GROUP BY `od`.`order_no`"
  end

  it "skip_take_sample1" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    q = o.
      project(o[Arel.star]).
      skip(30).take(10)

    q.to_sql.should eq "SELECT  `o`.*" +
      " FROM `orders` `o` " +
      " LIMIT 10 OFFSET 30"
  end

  it "in_subquery_sample1" do
    i = Arel::Table.new(:items)
    i.table_alias = "i"

    i2 = Arel::Table.new(:items)
    i2.table_alias = "i2"

    q1 = i2.
      where(i2[:item_no].eq(i[:item_no])).
      where(i2[:start_date].lteq(Date.new(2013,7,24))).
      project(i2[:start_date].maximum.as("max_start_date"))

    # This case can be written as 'start_date = (SELECT MAX(start_date)...) in SQL'
    # But Arel does not support eq(q1) and we write the statement using 'in'.
    q = i.
      where(i[:start_date].in(q1)).
      project(i[Arel.star])

    q.to_sql.should eq "SELECT `i`.*" +
      " FROM `items` `i` " +
      " WHERE `i`.`start_date` IN (SELECT MAX(`i2`.`start_date`) AS max_start_date" +
        " FROM `items` `i2` " +
        " WHERE `i2`.`item_no` = `i`.`item_no`" +
        " AND `i2`.`start_date` <= '2013-07-24')"
  end

Arel Sample (3)

I continue to write sample code for Arel.

  it "group_by_having_count_sample1" do
    o = Arel::Table.new(:order)
    o.table_alias = "o"

    q = o.
      group(o[:order_date]).
      having(Arel.star.count.gt(5)).
      project(o[:order_date],
        Arel.star.count)

    q.to_sql.should eq "SELECT `o`.`order_date`," +
      " COUNT(*)" +
      " FROM `order` `o` " +
      " GROUP BY `o`.`order_date`" +
      " HAVING COUNT(*) > 5"
  end

  it "group_by_having_sum_sample1" do
    od = Arel::Table.new(:order_details)
    od.table_alias = "od"

    q = od.
      group(od[:item_no]).
      having(Arel.sql("SUM(od.quantity)").gt(5)).
      project(od[:item_no],
        Arel.star.count,
        od[:quantity].sum)

    q.to_sql.should eq "SELECT `od`.`item_no`," +
      " COUNT(*)," +
      " SUM(`od`.`quantity`) AS sum_id" +
      " FROM `order_details` `od` " +
      " GROUP BY `od`.`item_no`" +
      " HAVING SUM(od.quantity) > 5"

=begin *.sum cannot be used in having clause because it is aliased like 'AS sum_id'
    q = od.
      group(od[:item_no]).
      having(od[:quantity].sum.gt(5)).
      project(od[:item_no],
        Arel.star.count,
        od[:quantity].sum)

    q.to_sql.should eq "SELECT `od`.`item_no`," +
      " COUNT(*)," +
      " SUM(`od`.`quantity`) AS sum_id" +
      " FROM `order_details` `od` " +
      " GROUP BY `od`.`item_no`" +
      " HAVING SUM(`od`.`quantity`) AS sum_id > 5"
=end
  end

  it "eq_sample2" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    order_no_list = ["000101", "000102", "000103"]
    # We can use 'eq_any' for 'OR'-joined conditions
    q = o.
      where(o[:order_no].eq_any(order_no_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_no` = '000101'" +
        " OR `o`.`order_no` = '000102'" +
        " OR `o`.`order_no` = '000103')"
  end

  it "eq_sample3" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    order_no_list = ["000100", "000100", "000100"]
    # We can use 'eq_all' for 'AND'-joined conditions
    q = o.
      where(o[:order_no].eq_all(order_no_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_no` = '000100'" +
        " AND `o`.`order_no` = '000100'" +
        " AND `o`.`order_no` = '000100')"
  end

  it "not_eq_sample2" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    order_no_list = ["000100", "000100", "000100"]
    # We can use 'not_eq_any' for 'OR'-joined conditions
    q = o.
      where(o[:order_no].not_eq_any(order_no_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_no` != '000100'" +
        " OR `o`.`order_no` != '000100'" +
        " OR `o`.`order_no` != '000100')"
  end

  it "not_eq_sample3" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    order_no_list = ["000101", "000102", "000103"]
    # We can use 'not_eq_all' for 'AND'-joined conditions
    q = o.
      where(o[:order_no].not_eq_all(order_no_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_no` != '000101'" +
        " AND `o`.`order_no` != '000102'" +
        " AND `o`.`order_no` != '000103')"
  end

  it "gt_sample2" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    date_list = [Date.new(2013,7,24),
      Date.new(2013,7,25),
      Date.new(2013,7,26)]
    # We can use 'gt_any' for 'OR'-joined conditions
    q = o.
      where(o[:order_date].gt_any(date_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_date` > '2013-07-24'" +
        " OR `o`.`order_date` > '2013-07-25'" +
        " OR `o`.`order_date` > '2013-07-26')"
  end

  it "gt_sample3" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    date_list = [Date.new(2013,7,24),
      Date.new(2013,7,25),
      Date.new(2013,7,26)]
    # We can use 'gt_all' for 'AND'-joined conditions
    q = o.
      where(o[:order_date].gt_all(date_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_date` > '2013-07-24'" +
        " AND `o`.`order_date` > '2013-07-25'" +
        " AND `o`.`order_date` > '2013-07-26')"
  end

  it "lt_sample2" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    date_list = [Date.new(2013,7,24),
      Date.new(2013,7,25),
      Date.new(2013,7,26)]
    # We can use 'lt_any' for 'OR'-joined conditions
    q = o.
      where(o[:order_date].lt_any(date_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_date` < '2013-07-24'" +
        " OR `o`.`order_date` < '2013-07-25'" +
        " OR `o`.`order_date` < '2013-07-26')"
  end

  it "lt_sample3" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    date_list = [Date.new(2013,7,24),
      Date.new(2013,7,25),
      Date.new(2013,7,26)]
    # We can use 'lt_all' for 'AND'-joined conditions
    q = o.
      where(o[:order_date].lt_all(date_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_date` < '2013-07-24'" +
        " AND `o`.`order_date` < '2013-07-25'" +
        " AND `o`.`order_date` < '2013-07-26')"
  end

  it "gteq_sample2" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    date_list = [Date.new(2013,7,24),
      Date.new(2013,7,25),
      Date.new(2013,7,26)]
    # We can use 'gteq_any' for 'OR'-joined conditions
    q = o.
      where(o[:order_date].gteq_any(date_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_date` >= '2013-07-24'" +
        " OR `o`.`order_date` >= '2013-07-25'" +
        " OR `o`.`order_date` >= '2013-07-26')"
  end

  it "gteq_sample3" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    date_list = [Date.new(2013,7,24),
      Date.new(2013,7,25),
      Date.new(2013,7,26)]
    # We can use 'gteq_all' for 'AND'-joined conditions
    q = o.
      where(o[:order_date].gteq_all(date_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_date` >= '2013-07-24'" +
        " AND `o`.`order_date` >= '2013-07-25'" +
        " AND `o`.`order_date` >= '2013-07-26')"
  end

  it "lteq_sample2" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    date_list = [Date.new(2013,7,24),
      Date.new(2013,7,25),
      Date.new(2013,7,26)]
    # We can use 'lteq_any' for 'OR'-joined conditions
    q = o.
      where(o[:order_date].lteq_any(date_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_date` <= '2013-07-24'" +
        " OR `o`.`order_date` <= '2013-07-25'" +
        " OR `o`.`order_date` <= '2013-07-26')"
  end

  it "lteq_sample3" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    date_list = [Date.new(2013,7,24),
      Date.new(2013,7,25),
      Date.new(2013,7,26)]
    # We can use 'lteq_all' for 'AND'-joined conditions
    q = o.
      where(o[:order_date].lteq_all(date_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_date` <= '2013-07-24'" +
        " AND `o`.`order_date` <= '2013-07-25'" +
        " AND `o`.`order_date` <= '2013-07-26')"
  end

  it "like_sample2" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    order_no_list = ["%101%", "%102%", "%103%"]
    # We can use 'matches_any' for 'OR'-joined conditions
    q = o.
      where(o[:order_no].matches_any(order_no_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_no` LIKE '%101%'" +
        " OR `o`.`order_no` LIKE '%102%'" +
        " OR `o`.`order_no` LIKE '%103%')"
  end

  it "like_sample3" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    order_no_list = ["%10%", "%", "%20%"]
    # We can use 'matches_all' for 'AND'-joined conditions
    q = o.
      where(o[:order_no].matches_all(order_no_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_no` LIKE '%10%'" +
        " AND `o`.`order_no` LIKE '%'" +
        " AND `o`.`order_no` LIKE '%20%')"
  end

  it "in_sample2" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    order_no_list = [ ["000100", "000101"],
      ["000200", "000201"],
      ["000300", "000301"] ]
    # We can use 'in_any' for 'OR'-joined conditions
    q = o.
      where(o[:order_no].in_any(order_no_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_no` IN ('000100', '000101')" +
        " OR `o`.`order_no` IN ('000200', '000201')" +
        " OR `o`.`order_no` IN ('000300', '000301'))"
  end

  it "in_sample3" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    order_no_list = [ ["000100", "000101"],
      ["000100", "000102"],
      ["000100", "000103"] ]
    # We can use 'in_all' for 'AND'-joined conditions
    q = o.
      where(o[:order_no].in_all(order_no_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_no` IN ('000100', '000101')" +
        " AND `o`.`order_no` IN ('000100', '000102')" +
        " AND `o`.`order_no` IN ('000100', '000103'))"
  end

Arel Sample (2)

I continue to write sample code for Arel.

  it "order_by_sample1" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    q = o.
      project(o[Arel.star])
      .order(o[:order_date].desc,
        o[:order_no].asc)

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " ORDER BY `o`.`order_date` DESC," +
      " `o`.`order_no` ASC"
  end

  it "not_eq_sample1" do
    i = Arel::Table.new(:items)
    i.table_alias = "i"

    q = i.
      where(i[:item_no].not_eq("10000001")).
      project(i[Arel.star])

    q.to_sql.should eq "SELECT `i`.*" +
      " FROM `items` `i` " +
      " WHERE `i`.`item_no` != '10000001'"
  end

  it "gteq_lteq_and_sample1" do
    i = Arel::Table.new(:items)
    i.table_alias = "i"

    date = Date.new(2013,7.24)
    q = i.
      where(i[:start_date].lteq(date)).
      where(i[:end_date].gteq(date)).
      project(i[Arel.star])
    # where functions more than 1 make 'AND'

    q.to_sql.should eq "SELECT `i`.*" +
      " FROM `items` `i` " +
      " WHERE `i`.`start_date` <= '2013-07-01'" +
      " AND `i`.`end_date` >= '2013-07-01'"
  end

  it "gt_lt_or_sample1" do
    i = Arel::Table.new(:items)
    i.table_alias = "i"

    date = Date.new(2013,7.24)
    q = i.
      where(i[:start_date].gt(date).
        or(i[:end_date].lt(date))).
      project(i[Arel.star])
    # where functions more than 1 make 'AND'

    q.to_sql.should eq "SELECT `i`.*" +
      " FROM `items` `i` " +
      " WHERE (`i`.`start_date` > '2013-07-01'" +
        " OR `i`.`end_date` < '2013-07-01')"
  end

  it "and_or_sample1" do
    i = Arel::Table.new(:items)
    i.table_alias = "i"

    dates = [ [Date.new(2013,7,24), Date.new(2013,7,28)],
      [Date.new(2013,8,1), Date.new(2013,8,5)],
      [Date.new(2013,8,10), Date.new(2013,8,12)] ]
    
    q = i
    dates.each do |pair|
      q = q.
        where(i[:start_date].gt(pair[0]).
          or(i[:start_date].lt(pair[1])))
    end
    q = q.project(i[Arel.star])

    q.to_sql.should eq "SELECT `i`.*" +
      " FROM `items` `i` " +
      " WHERE (`i`.`start_date` > '2013-07-24'" +
        " OR `i`.`start_date` < '2013-07-28')" +
      " AND (`i`.`start_date` > '2013-08-01'" +
        " OR `i`.`start_date` < '2013-08-05')" +
      " AND (`i`.`start_date` > '2013-08-10'" +
        " OR `i`.`start_date` < '2013-08-12')"
  end

  it "like_sample1" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    order_no = "101"
    q = o.
      where(o[:order_no].matches("%#{order_no}%")).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE `o`.`order_no` LIKE '%101%'"
  end

  it "in_sample1" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    order_no_list = ["000101", "000102", "000103"]
    q = o.
      where(o[:order_no].in(order_no_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +" WHERE `o`.`order_no` IN ('000101', '000102', '000103')"
  end

  it "not_like_sample1" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    # We can use 'does_not_match' for 'NOT LIKE' in SQL.
    order_no = "101"
    q = o.
      where(o[:order_no].does_not_match("%#{order_no}%")).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE `o`.`order_no` NOT LIKE '%101%'"
  end

  it "not_in_sample1" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    order_no_list = ["000101", "000102", "000103"]
    q = o.
      where(o[:order_no].not_in(order_no_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE `o`.`order_no` NOT IN ('000101', '000102', '000103')"
  end

Arel Sample (1)

I study Ruby on Rails and I have to use Arel directly.
So I write some SQL patterns on this page for my memo.

The database schema is the following diagram.

I write using of Arel as Rspec.

  it "where_sample1" do
    order = Arel::Table.new(:orders)
    q = order.
      where(order[:order_date].eq(Date.new(2013, 7, 24))).
      project(Arel.star)

    q.to_sql.should eq "SELECT *" +
      " FROM `orders` " +
      " WHERE `orders`.`order_date` = '2013-07-24'"
  end

  it "table_alias_sample1" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    q = o.
      where(o[:order_date].eq(Date.new(2013, 7, 24))).
      project(Arel.star)

    q.to_sql.should eq "SELECT *" +
      " FROM `orders` `o` " +
      " WHERE `o`.`order_date` = '2013-07-24'"
  end

  it "join_sample1" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    od = Arel::Table.new(:order_details)
    od.table_alias = "od"

    q = o.
      join(od).on(od[:order_no].eq(o[:order_no])).
      where(o[:order_date].eq(Date.new(2013, 7, 24))).
      where(od[:detail_no].lteq(3)).
      project(o[Arel.star],
        od[Arel.star])

    q.to_sql.should eq "SELECT `o`.*," +
      " `od`.*" +
      " FROM `orders` `o`" +
      " INNER JOIN `order_details` `od`" +
      " ON `od`.`order_no` = `o`.`order_no`" +
      " WHERE `o`.`order_date` = '2013-07-24'" +
      " AND `od`.`detail_no` <= 3"
  end

  it "outer_join_sample1" do
    od = Arel::Table.new(:order_details)
    od.table_alias = "od"

    i = Arel::Table.new(:items)
    i.table_alias = "i"

    q = od.
      join(i, Arel::Nodes::OuterJoin).
      on(i[:item_no].eq(od[:item_no])).
      project(od[Arel.star],
        i[Arel.star])

    q.to_sql.should eq "SELECT `od`.*," +
      " `i`.*" +
      " FROM `order_details` `od`" +
      " LEFT OUTER JOIN `items` `i`" +
      " ON `i`.`item_no` = `od`.`item_no`"
  end

  it "join_sample2" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    od = Arel::Table.new(:order_details)
    od.table_alias = "od"

    i = Arel::Table.new(:items)
    i.table_alias = "i"

    q = o.
      join(od).on(od[:order_no].eq(o[:order_no])).
      join(i).on(i[:item_no].eq(od[:item_no])).
      project(o[Arel.star],
        od[Arel.star],
        i[Arel.star])

    q.to_sql.should eq "SELECT `o`.*," +
      " `od`.*," +
      " `i`.*" +
      " FROM `orders` `o`" +
      " INNER JOIN `order_details` `od`" +
      " ON `od`.`order_no` = `o`.`order_no`" +
      " INNER JOIN `items` `i`" +
      " ON `i`.`item_no` = `od`.`item_no`"
  end

An idea for generating test data (3)

I told the progress making the tool was 10% but it is wrong and it is not easy.

I got some tips. I describe the tip for labeled for statement.

We can write the for statement with label and the example is following:

for1: for(int i = 0; i < 10; i++) {
  for2: for(int j = 0; j < 5; j++) {
    int k = getValue(i, j);
    if (k == 1)
      continue for1;
    else if (k == 2)
      break for1;
  }
}

But the labeled for statement is not allowed in C#. So we need to write it :

for (int i = 0; i < 10; i++) {
  for (int j = 0; j < 5; j++) {
    int k = getValue(i, j);
    if (k == 1)
      goto continue_for_for1;
    else if (k == 2)
      goto break_for_for1;
  }
continue_for_for1:
}
break_for_for1:

An idea for generating test data (2)

I decided to use Irony to make the tool for converting Java sources to C# sources.

It is difficult for me to use ANTLR because I cannot understand building Java AST by using ANTLR.
And I can understand building Java AST by using Irony.

I am making the tool and the progress is about 10% toward completion.

An idea for generating test data

An idea come to me and it is that we convert the Java source to C# source and apply Pex the C# source and the generated test data is usable for original Java source.

But I don't know the tools that convert Java source to C# source.

I will make the tool by ANTLR or Irony. Which is easier to make tools?

The arrows in Haskell

Can the arrows in Haskell considered by a category?
I will investigate it later.

The kinds of Categories usable for computer science

I want to systematize the categories but will think later.

• cartesian closed category
• freyd category
• kleisli category
• monoidal category
• premonoidal category
• traced monoidal category
• braided monoidal category
• balanced monoidal category
• symmetric monoidal category
• ribbon category = tortile monoidal category
• compact closed category

Should the copy of variable be less?

The lambda calculus with types equals to intuitionistic logic. (from the Curry-Howard Isomorphism)
Intuitionistic logic is asymmetric and is constructive.
If we want the logic to be symmetric and constructive we should restrict the copy of assumption from the linear logic.
So we can introduce the symmetry to the calculus by the copy of variable.
I think that a symmetric structure is more beautiful than asymmetric one and symmetric one is  more maintenanceable.
So I think that it is good that we restrict the copy of variable or wrap that by a library. Is it right?

Imperative vs Declarative (2)

My ideas are following:

1. The events encode the operations some senses.
2. Do we think it is convenient which the events come from external of the system or are in internal of the system. 
3. If the events are in internal of the system how the functions relates the events.

Imperative vs Declarative

I'm thinking and I leave a memo now.

Reading the book for the Alloy Analyzer

I am reading the book. It is interesting.
This book teaches me Alloy Analyzer method and it is useful to design my design supporting tool.
For example, Alloy analyzer does not distinguish an object and a set containing that one. I apply the approach to my tool.

Design Memo

I think that the functional specification should be declarative.
But I do not know the tool supporting to declare specification.
So I am making the tool, but the progress is slow...

The proof assistant and the model finder

Are they the dual relation?

The proof assistant is the top down.
The model finder is the bottom up.

I think it is interesting that we iteratively use them.

Advantage of the object oriented programming

I think the advantages of the object oriented programming are following.

1. We can easily modularize programs with the class. 
2. We can easily commonalize the code by the inheritance.
3. We can easily commonalize with the inversion of control by the polymorphism.
4. We can easily read the code for the order of words with S + V + O.
5. The type system built by the class is usable.

These are achieved effectively by thinking that the all are the objects.

Memo of my tool for aiding design of web applications (2)

It is important that we consider the constraints of the resource items.
Those must be the definition of validation for user input and other request parameters.
I want my tool aiding the viewpoint.

Memo of my tool for aiding design of web applications

I am making the tool for aiding design  of web applications.
I think it is good that Web applications have REST architecture.
So I want my tool make easy to design in REST architecture.

I tried re-mix.

I tried re-mix. It's interesting!
I show the example using re-mix.

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Remotion.Mixins;

namespace ReMixSample
{
  [TestClass]
  public class ReMixSampleTest1
  {
    [TestMethod]
    public void TestMethod1()
    {
      ReMixSample s = ObjectFactory.Create<ReMixSample>();
      Assert.AreEqual("A", s.A());
      Assert.AreEqual(3, s.B());
    }
  }
  public class MixinSample1<T> : Mixin<T>
    where T : class
  {
    [OverrideTarget]
    public string A()
    {
      return "A";
    }
  }
  public class MixinSample2<T> : Mixin<T>
    where T : class
  {
    [OverrideTarget]
    public int B()
    {
      return 3;
    }
  }
  [Uses(typeof(MixinSample1<ReMixSample>))]
  [Uses(typeof(MixinSample2<ReMixSample>))]
  public class ReMixSample
  {
    public virtual string A() { return default(string); }
    public virtual int B() { return default(int); }
  }
}

But sadly(perhaps for the limitation of the .NET framework), the methods A and B of ReMixSample class cannot be abstract methods.
Therefore the methods need the body and it will not be significant.
So I think it's easy to understand that the methods are mixed-in if we write the body is the following unified form:
virtual {Return type} {Method name}(...) { return default({Return type}); }