工作记录(待续)

1,增加一个Sql远程登录用户

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
GRANT select ON endysis.* TO eshop@localhost IDENTIFIED BY 'abc';
GRANT select ON endysis.* TO eshop@"%" IDENTIFIED BY 'abc';

2,rails_sql_views
https://github.com/activewarehouse/rails_sql_views

  create_view "v_retail_sales", "select `retail_sales`.`id` AS `id`,`retail_sales`.`description` AS `description`,`retail_sales`.`commodity` AS `commodity`,`retail_sales`.`main_raw_material` AS `main_raw_material`,`retail_sales`.`main_raw_material_composition` AS `main_raw_material_composition`,`retail_sales`.`customer_code` AS `customer_code`,`retail_sales`.`product_part` AS `product_part`,`prices`.`value` AS `selling_price_value`,`prices`.`currency` AS `selling_price_currency`,`retail_sales`.`pantone_code` AS `pantone_code`,`retail_sales`.`quantity` AS `quantity`,`retail_sale_secondary_features`.`consumer` AS `consumer`,`retail_sale_secondary_features`.`size_group` AS `size_group`,`retail_sale_secondary_features`.`uom` AS `uom`,`retail_sale_marketings`.`brandname` AS `brandname`,`retail_sale_marketings`.`season` AS `season`,`retail_sale_marketings`.`year` AS `year`,`retail_sale_marketings`.`marketability` AS `marketability`,`retail_sale_marketings`.`sales_class` AS `sales_class`,`retail_sale_marketings`.`purchasing` AS `purchasing` from (`retail_sales` left join ((`retail_sale_secondary_features` join `retail_sale_marketings`) join `prices`) on(((`retail_sale_secondary_features`.`retail_sale_id` = `retail_sales`.`id`) and (`retail_sale_marketings`.`retail_sale_id` = `retail_sales`.`id`) and (`prices`.`id` = `retail_sales`.`selling_price`)))) where ((`retail_sales`.`status` = 'Available') and (`retail_sales`.`company_id` = 1))", :force => true do |v|
    v.column :id
    v.column :description
    v.column :commodity
    v.column :main_raw_material
    v.column :main_raw_material_composition
    v.column :customer_code
    v.column :product_part
    v.column :selling_price_value
    v.column :selling_price_currency
    v.column :pantone_code
    v.column :quantity
    v.column :consumer
    v.column :size_group
    v.column :uom
    v.column :brandname
    v.column :season
    v.column :year
    v.column :marketability
    v.column :sales_class
    v.column :purchasing
  end

require 'active_record/view'

class VRetailSale < ActiveRecord::View
  has_many :v_logistics_cycle_per_sizes
end

revoke all ON endysis.* from eshop@"%";
GRANT select ON endysis.v_retail_sales TO eshop@"%" IDENTIFIED BY 'abc';