ActiveRecord raw SQL 覆寫 (Rails 5.1) + multi SQL


#1

首先,這篇算是非常實驗性質,且綁定 Rails 才能使用(類似 .stringify_keys! 是 Rails 擴充的,當然 code 很簡單 … 你可以寫回去 … 見文末),建議你非常熟悉 ORM / raw SQL 才來嘗試這篇的內容 … 而這邊使用 MySQL,其他的 DB 應該可以比照辦理,但建議必須大量測試就是

最近都在和 raw SQL 搏鬥,需要一些非常特例的東西,類似 mysql 中的 UNIX_TIMESTAMP 來取得最精準的時間之類的,且不能到秒數而已,希望得到毫秒等級甚至更高,所以測試到最後寫了類似這樣的覆寫

# [[%RAILS_APP%/config/initialize/active_record_create_by_atu.rb]]
class ActiveRecord::Base
  def self.create_by_atu(ori_data_hash)

    # part 1 取得 safe raw sql & inject function
    data_hash.stringify_keys! #必須先把 key 都轉成 string,否則後面同名會 match 不到
    data_hash['created_atu'] = -12345.1234
    obj = new(data_hash)
    raw_sql = obj.class.arel_table.create_insert.tap do |im|
      im.insert(
        obj.send(
          :arel_attributes_with_values_for_create , 
          data_hash.keys
        )
      )
    end.to_sql
    raise "QwQ , raw SQL replace[-12345.1234] fail : #{raw_sql}" unless raw_sql.scan('-12345.1234').count == 1
    raw_sql.gsub!('-12345.1234' , 'UNIX_TIMESTAMP(CURTIME(4))')

    # part 2 包裝回 ORM
    data_hash['id'] = ActiveRecord::Base.connection.create(raw_sql)
    data_hash.delete('created_atu')
    obj = instantiate(data_hash)
    obj.run_callbacks(:save)
    obj.run_callbacks(:create)
    return obj
  end
end

# [[migration]]
#   create_table :items do |t|
#     # 1234567890.1234 = 10 + 4 => 14 , 4
#     t.decimal :created_atu , default: 0 , precision: 14 , scale: 4
#   end
# [[useage]]
# item = Item.create_by_atu(kind: 9 , status: 9)
#=> INSERT INTO `items` (`kind`, `status`, `created_atu`) VALUES (9, 9, UNIX_TIMESTAMP(CURTIME(4)))
#=> #<Item:0x0 id: 99, kind: 9, status: 9>
# item.kind = 8 ; item.save
#=> UPDATE `items` SET `kind` = 8 WHERE `items`.`id` = 99
#=> #<Item:0x0 id: 99, kind: 8, status: 9>
# created_atu = Item.last.created_atu
# created_atu.class
#=> BigDeciaml
# created_atu.to_f
#=> 1515563980.8547
# Time.at(Item.last.created_atu).strftime('%Y-%m-%d %H:%M:%S.%4N')
#=> "2018-01-10 13:59:40.8547"

這邊是覆寫 ActiveRecord::Base,在任何一個 model 內增加一個叫做 create_by_atu 的 method,來讓新增時可以塞入 created_atu 這個欄位,用的是 UNIX_TIMESTAMP(CURTIME(4)) 這組 mysql function 來取得 unix timestamp + 4 個小數點

part1 在包 raw SQL,使用 -12345.1234 這個數字來暫時塞入 raw SQL 中,之後把 raw SQL 的該數字取代成 UNIX_TIMESTAMP(CURTIME(4)) 這邊其實相當直覺就是,不過要注意 … 你之後就不能用該數字了,包括 text 內都不能有,否則會額外複寫掉|||(當然怕安全性問題,有寫 exception 來查個數),中間就是 Rails 5 的包裝手法罷了(見 ref),其中有限定 data_hash.keys 才會被加入 raw SQL 的語法內,否則用 obj.attribute_names 會有太多空的欄位

之後就是執行 raw SQL,這邊注意的是一定要取得 LAST_INSERT_ID,所以類似 connection.query / connection.execute 的 method 並不會回這個回來,必須使用 connection.create 才可以,而 raw SQL 有趣的是類似你塞入 10 筆,只會回一個 id,因為一整個 SQL 都是 transaction,所以一定連號,因此自己照順序 -N 就知道目前 id 為多少,且只有 insert 動作有這個需求

最後最麻煩,你不能 return new(hash)(全等於 return Item.new(hash)) 因為這是尚未 create 的狀態,所以 save 略過 id 另外 insert 一次 … 而我嘗試覆寫 changed_attributes / previous_changes 或是之類的都無法成功,最後沒解才去翻 find_by_sql 的用法,找到可以用 instantiate({str_name => data ...}) 的方式來還原一個已經存在的 obj,且後續存檔也只會影響到變動的欄位

最後就是執行 callback … 似乎沒有 before / after 之類的可選,不過這邊先這樣唄,可能需要列為流程有問題的地方就是

anyway 以上,code 很少但有點複雜,而另外如果有 MULTI_SQL 需求的,我有發 Rails 的另外一個 issue 上去就是

用這個應該就可以解決所有所有 multi raw SQL 在 Rails 上面的問題才是

而這篇的 ref

最後附上如果要在 Ruby 上用的話,缺的 .stringify_keys! 覆寫的 code

doc in

https://apidock.com/rails/v4.2.7/Hash/stringify_keys!

https://apidock.com/rails/v4.2.7/Hash/transform_keys!

# minified
class Hash
  def stringify_keys!
    keys.each do |key|
      self[key.to_s] = delete(key)
    end
    self
  end
end
# {:wer => 123 , :sdf => 234 , 'sdf' => 345 , 'zxc' => 456}.stringify_keys!
#=> {"wer"=>123, "sdf"=>234, "zxc"=>456} #(same obj id)

以上


#2

先填一個數字再取代有什麼好處嗎?

為何不用 sanitize_sql_array?

sanitize_sql_array 使用範例:

sanitize_sql_array([<<-SQL, { id: '123' }])
SELECT id
FROM items
WHERE id = :id
SQL

# => "SELECT id FROM items WHERE id = '123'"

#3

hmm … 我有想過用那個,不過那個是 SELECT 專用的 + field only 沒有完整的語法,且用途是 condition 而非塞值(那個出來的結果是放在 WHERE 區段內的,而非 SET 段,其實 SELECT 很好解,因為全都支援 raw SQL … 但寫入系列一直很難搞),而用我那個方式連 INSERT INTO 之類的 SQL 語法都會整票拼好給你哩,包括 table name 有的沒的整票都有

這邊主要的目的是 INSERT + RDBMS function 的實現就是,作用在於 Amazon 上 RDS 的 INSERT 時間的正確性,如果取用的是 Rails 會出現前後的時間差哩,尤其是對時間非常敏感的場合時,且因為需求必須擴充到毫秒等級

當然它有另外一個姐妹可選,叫 sanitize_sql_hash_for_assignment,一樣只有 field 區段但是是放在 SET 內的,雖然我可以另外自己包頭尾 … but … 目前堪用不想換 X"DD


#4

我都用來塞值啊,不是 SELECT 專用的哦 >.0

還能塞 SQL function 都沒有問題


#5

SELECT * FROM items WHERE kind = 9 AND status = 9

vs

INSERT INTO items (kind , status) VALUES (9 , 9)

… 你是要怎樣塞啦 … 對了我忘記說,通常我都會用上面的寫法,因為一般我都會碰到大量 INSERT 的需求,所以很常不是一兩筆而已(不過這種狀況通常都用 connection.execute 就好了 X"D,這案例比較麻煩因為 obj 需要能後續使用,且 id 必須正確),然而寫成 field1 = xxx , field1 = yyy ... 的用法如果可以用也是後來擴充的唄?因為我學 SQL 的歷程中沒看過那種寫法就是 |||

附上的 INSERT 段的 doc?(好啦我不確定這篇是不是 ANSI SQL 的解說 X"D)


#6
sanitize_sql_array([<<-SQL, { kind: 'foo', status: 'bar' }])
INSERT INTO items (kind, status) VALUES (:kind, :status)
SQL

# => INSERT INTO items (kind, status) VALUES ('foo', 'bar')

然後我是用 pgsql 系的,所以我通常都是這樣用:

insert_hash = [{ kind: 'foo', status: 'bar' }, { ... }, ...]

sanitize_sql_array([<<-SQL, { kinds: insert_hash.map { |it| it[:kind] } , statuses: insert_hash.map { |it| it[:status] } }])
INSERT INTO items (kind, status)
SELECT map.kind, map.status
FROM unnest(ARRAY[:kinds], ARRAY[:statuses]) AS map(kind, status)
SQL

# => INSERT INTO items (kind, status)
#    SELECT map.kind, map.status
#    FROM unnest(ARRAY['foo', ...], ARRAY['bar', ...]) AS map(kind, status)

#7

yoo~ okay~ 我重新看後懂你的意思了,說不定還不錯,不過你還是要自己寫 SQL 的包裝語法哩,這個覆寫是在全域下作用的,包括 field 之類的都要拉出來塞進去還要確認個數 ==3== 那樣寫有點囉唆哩,要那樣包不如全部寫 raw_sql 算了 Orz"

and 你寫 INSERT 多弄一個 SELECT 空包裝做啥啦啦啦啦啦啦啦啦啦啦,你乖乖用 VALUES 用逗號分格就好了啊啊啊啊啊啊啊 X"D


#8

如果是不定筆數的話這樣會快很多哦,把 unpack 交給 pgsql 幫我做會比 Ruby 下去作來的快


#9

因為 pgsql 支援從另一個 select-query 來 insert data

INSERT INTO items (kind, amount)
SELECT 'cookie', cookies.amount
FROM cookies
WHERE cookies.amount > 0

將所有 cookies 裡面 amount > 0 的資料以 (‘cookie’, amount) 來 insert 到 items

然後支援從 array-pair 解成臨時 table

SELECT id, val
FROM unnest(ARRAY[1, 2, 3], ARRAY['a', 'b', 'c']) AS tmp(id, val)
id val
1 ‘a’
2 ‘b’
3 ‘c’

#10

我完全知道你在做啥 … 每個 sub select / subquery 其實都是 temporary table,所以盡可能不用就不用,包括 JOIN 都是,temporary table 也是 JOIN 速度爆慢 & 被人詬病的主因(非 physical table ) … 所以不管如何包裝 N 次都是下下策的啊 … 如果你家有 DBA 會敲你的頭唄 |||

第二個,我寧願使用 Ruby 而非 RDBMS 下做,因為 Ruby / Rails 大多可以開成多台來分擔效能問題,包括但不限於 multi thread / multi process 等等,甚至之後還能改成 go lang,而你把這類的東西歸給 RDBMS,會造成嚴重的 side effect,是全系統變慢,而非單一個 bottleneck 而已 ||| 因為你把 bottleneck 丟到一個不能有其他人能分擔的傢伙上了 |||

最後,INSERT 這指令一定有 full table lock,因為需要配置硬碟空間與 auto increment pkey … 所以會需要最短的時間內完成 … 附加上面原因 … 你當然可以用,你開心就好,但對我而言都是下下策哩|||


#11

pgsql 的 temp table 速度爆快……我覺得他內部應該是有一個 pre-compiler 在加速整個語句