HoneyHoney

故事是这样的，如果我在创建一个实例以后，再去编辑类并增加一个方法，这个实例是能够发现新的方法的。

class Dog
  def name
    
  end
end

a_dog = Dog.new

p a_dog.methods

class Dog
  def age
    
  end
end

p a_dog.methods

同理，在已经included 的module里增加一个新的方法。

module Professor
  def lectures
    
  end
  
end

class Mathematician
  attr_accessor :first_name, :last_name
  include Professor
end

fett = Mathematician.new

p fett.methods


module Professor
  def primary_classroom
    
  end
  
end

p fett.methods # this will have new method

但是，如果在已经included的module里面include一个新的module，这样就不行了。

module Employee
  def hired_date
    
  end
  
end

module Professor
  include Employee
end

p fett.methods # this will not have hired_date method until Mathematician included Professor again

原因在于，前两者影响的是方法表而已，而实例对应的klass里面留下的只是方法表pointer，而不是具体的方法。所以，在方法表里面增加方法是可行的。
但是，include 一个新的module，是会改变super pointer。在第一次include的时候，就已经“复制”好module并设置好了super pointer，不会再次改变。除非，重新打开类再include一次。

给指定的硬币类型，用最少的硬币个数，找出指定的amount。比如，现在有[2, 5, 10, 20, 50]这几种硬币，找出21块钱出来。

这个其实是算法导论里面动态规划。无意间，发现还有其他的实现方法。记录下来。

首先是动态规划。原来是，21块钱，可以拆分成19+2， 16+5， 11+10， 1+20。19又可以继续拆分成17+2, 14+5, 9+10。以此类推下去。里面有个技术点就是，还可以用block去定义Hash，厉害了。代码如下：

 def change(coins, amount, results = [])
    return [] if amount == 0

    coins.sort! { |a, b| a <=> b }

    optimal_change = Hash.new do |hash, key|

      if key < coins.min
        hash[key] = []
      elsif coins.include?(key)
        hash[key] = [key]
      else
        hash[key] = coins
            .reject { |coin| coin > key }
            .map { |coin| [coin] + hash[key - coin] }
            .reject { |change| change.inject(&:+) != key }
            .min { |a, b| a.size <=> b.size } || []
      end

      puts hash
      hash[key]
    end

    optimal_change[amount].empty? ? -1 : optimal_change[amount].sort
end

另外一种方法是，创建一个数组，从1到amount，然后逐一用coin去替代，最后一个就是amount对应的coin了。

  def change(coins, total_change)
    return -1 if total_change < 0 || coins.any? { |x| x < 1 }
    m = [[]] + [nil] * total_change
    coins.size
         .times
         .to_a
         .product((1..m.size - 1).to_a)
         .each { |c, t|
           if coins[c] == t
             m[t] = [coins[c]]
           else
             (1..t - 1).select { |t2| coins[c] + t2 == t }
                       .reject { |t2| m[t2].nil? }
                       .each { |t2| 
                         m[t] = m[t2] + [coins[c]] if m[t].nil? || m[t2].size + 1 < m[t].size 
                       }
           end
         }
    m[-1].nil? ? -1 : m[-1]
  end

Ruby的include和prepend有一个重要的知识点，就是多重包含的时候，后面的Module会被ignore掉，只会包含一次。

module C
  
end

module M
  
end

class B
  include M
  include C
end

p B.ancestors
# [B, C, M, Object, Kernel, BasicObject]

class A
  prepend M
  prepend C
end

p A.ancestors
# [C, M, A, Object, Kernel, BasicObject]


class D
  include M
  prepend C
end

p D.ancestors
# [C, D, M, Object, Kernel, BasicObject]

module M
  include C
end

class E
  prepend C
  include M
end

p E.ancestors
# [C, E, M, Object, Kernel, BasicObject]

http://exercism.io/exercises/ruby/poker/readme
来玩玩德州扑克~
德州扑克里面，每人会有五张牌，然后两个人比点。规则是先比牌型，再是点数，最后是花色。牌型一共有9种，从高到低依次是：同花顺，四条，葫芦，同花，顺子，三条，两对，一对，散牌。

基本思路是，针对每一手牌hand，依次判断其是哪种牌型，再讲改牌型的点数按大小排序，方便后面比点的时候用。

目前来看，这道题稍微有点难，只有30多个人完成了。

class Poker

  RANKING_CATEGORIES = {
    :straight_flush => 9,
    :square => 8,
    :full => 7,
    :flush => 6,
    :straight => 5,
    :three => 4,
    :two_pairs => 3,
    :one_pair => 2,
    :high_card => 1
  }

  CARD_RANKING = {
    '1' => 1,
    '2' => 2,
    '3' => 3,
    '4' => 4,
    '5' => 5,
    '6' => 6,
    '7' => 7,
    '8' => 8,
    '9' => 9,
    '10' => 10,
    'J' => 11,
    'Q' => 12,
    'K' => 13,
    'A' => 14
  }

  def initialize(hands)
    @hands = hands.map { |e| Hand.new(e).tap(&:hand_nubmer_groups) }
  end

  def best_hand
    largest_rank_hands = @hands.group_by { |e| e.rank }.sort.last.last #get the largest rank hands
    return largest_rank_hands.map(&:hand) if largest_rank_hands.size == 1
    largest_rank_hands.group_by { |e| e.point }.sort.last.last.map(&:hand)
  end


  class Hand
    attr_reader :hand
    def initialize(hand)
      @hand = hand
    end

    # 找出最高的rank，比如同花顺，既是同花，又是顺子，但只能够取最高rank的，即同花顺
    def rank
      RANKING_CATEGORIES.map { |k, v| self.send(:"#{k}?") ? v : 0 }.max
    end

    # 根据不同牌型，返回对应的点数以便后面比点。比如，四条就会返回是4张相同牌的数字，三条/葫芦就会范围3张相同牌的点数
    # 两对的时候，就需要将点数顺序颠倒一次，大的放到前面
    # 顺子就返回最小的那个点，因为‘A2345'是小于'910JQK'
    # 同花或者散牌，就直接将点数顺序颠倒，在比点
    def point
      return hand_nubmer_groups.find { |k, v| v.size == 4 }.first if square? # #find will convert Hash to Array
      return hand_nubmer_groups.find { |k, v| v.size == 3 }.first if three? || full?
      return hand_nubmer_groups.select { |k, v| v.size == 2 }.keys.reverse if two_pairs? || one_pair?
      return hand_nubmers.min if straight? # straight, compare the lowest number to avoid 'A2345' > '910JQK'
      hand_nubmers.reverse # flush or high card, compare the points from high to low
    end

    # e[0..-2]是为了避免'10S'这种情况
    def hand_nubmers
      @hand_nubmers ||= @hand.map { |e| CARD_RANKING[e[0..-2]] }.sort
    end

    # 将手里的牌进行分组，相同点数的分到一组，方便统计是否为对子/三条/四条
    def hand_nubmer_groups
      @hand_nubmer_groups ||= hand_nubmers.group_by { |i| i }
    end

    def straight_flush?
      straight? && flush?
    end

    def flush?
      @hand.map { |e| e[-1] }.uniq.size == 1
    end

    def straight?
      all_5_different_values? && ((hand_nubmers.max - hand_nubmers.min == 4) || ( hand_nubmers == [2,3,4,5,14] ))
    end

    def square?
      hand_nubmer_groups.any? { |k, v| v.size == 4 }
    end

    def full?
      hand_nubmer_groups.any? { |k, v| v.size == 3 } && hand_nubmer_groups.any? { |k, v| v.size == 2 }
    end

    def three?
      hand_nubmer_groups.any? { |k, v| v.size == 3 } && hand_nubmer_groups.keys.size == 3
    end

    def two_pairs?
      hand_nubmer_groups.any? { |k, v| v.size == 2 } && hand_nubmer_groups.keys.size == 3
    end

    def one_pair?
      hand_nubmer_groups.keys.size == 4
    end

    def high_card?
      all_5_different_values? && !straight?
    end

    def all_5_different_values?
      hand_nubmers.uniq.size == 5
    end

  end

end

最近在做exercise.io上面的题练习Ruby，顺便分享一下自己的代码。
http://exercism.io/exercises/ruby/kindergarten-garden/readme

这道题相对比较简单，主要是用each_slice进行一次分组，并且通过Hash做一个key-value转换。最后就只需要按照students的序号，找到之前分好组里对应需要的plants就行了。有趣的是，测试用例里面是通过student的名字在找其拥有的plants，那就需要点元编程的技巧了。这里需要用到define_singleton_method去动态定义以student命名的方法。

class Garden
  DEFAULT_STUDENTS = %w(alice bob charlie david eve fred ginny harriet ileana joseph kincaid larry)

  PLANTS = {
    'R' => :radishes,
    'C' => :clover,
    'G' => :grass,
    'V' => :violets
  }

  def initialize(plants, students=DEFAULT_STUDENTS)
    @plants = plants
    @students = students.sort
    distribute_plants
  end

  def distribute_plants
    # 将两行plants拆分成每两个一组，并对每个plant进行映射转换('R'=>:radishes)
    plants_grouped = @plants.split("\n")
                            .map { |r| r.chars #每一行plants
                                        .each_slice(2) #每两个一组
                                        .map { |plants|
                                          plants.map { |plant| PLANTS[plant] } #每个plant转换一次
                                            }
                                          }
    # 给每个student分配plants
    @students.each_with_index do |s, i|
      break if plants_grouped.first.size < i + 1 # plants不够，就不再分配给student了
      #创建singleton method，因为每个garden对应的plants分配是不一样的。define_method是对整个Class创建实例方法
      define_singleton_method(s.downcase) do
        plants_grouped.map { |e| e[i] }.flatten # 通过索引i，找到student对应的那组plant
      end
    end
  end

end

SQL SERVER的index是按照B+tree来存储的。在dm_db_database_page_allocations可以看到这类page的type是2，即index page。
每个index page存储着key - pointer关系，其childPage可能还是intermediate page，也可能是leaf page。leaf page就存储着具体的data。

如下图中的page 623876就是一个intermediate page。

其数据即存储着ChildPageId和key pointer。比如这个例子中，id小于477的，都存在page 623874上。其中level表示该page是root node page。

用fn_PhysLocCracker来实验一下，就可以验证上面的观点。

DROP TABLE LargeTable

CREATE TABLE LargeTable (keyval int, 
    dataval int,
    constraint pk_largetable primary key (keyval)
    )

INSERT INTO LargeTable(keyval, dataval)
select n, rand(10) 
from dbo.GetNums(10000000)


SELECT allocated_page_file_id as PageFID, allocated_page_page_id as PagePID,
       object_id as ObjectID, partition_id AS PartitionID,
       allocation_unit_type_desc as AU_type, page_type as PageType
FROM sys.dm_db_database_page_allocations(db_id('event_service'), object_id('LargeTable'),
                                          1, null, 'DETAILED');
GO

DBCC TRACEON (3604); 
GO 
DBCC PAGE ('event_service', 1, 623876, 3); 
GO

DBCC TRACEON (3604); 
GO 
DBCC PAGE ('event_service', 1, 608307, 3); 
GO

select *
from LargeTable t
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS flc
WHERE keyval < 478

同事问了一个问题，在drop default constraint之后，是否会引起大的IO。答案是，No。

其实两年我就问过这个问题，结果还是被无情地打脸了，忘记完了。
https://dba.stackexchange.com/questions/90771/default-value-stays-after-i-dropped-the-default-constraint

长话短说，在每次创建default constraint的时候，在system_internals_partition_columns表内都会记录default value，从而实现所谓的metadata change。即sql server 2012以后，增加一个NOT NULL WITH DEFAULT CONSTRAINT是metadata change。随后删除这个constraint并不会改变system_internals_partition_columns里面值，也不会引起大的IO。


http://rusanu.com/2011/07/13/online-non-null-with-values-column-add-in-sql-server-11/

IF OBJECT_ID('LobData') IS NOT NULL
    DROP TABLE dbo.LobData

create table dbo.LobData
(
    ID int not null
);

insert into dbo.LobData(ID) 
values (1);


SELECT allocated_page_file_id as PageFID, allocated_page_page_id as PagePID,
       object_id as ObjectID, partition_id AS PartitionID,
       allocation_unit_type_desc as AU_type, page_type as PageType
FROM sys.dm_db_database_page_allocations(db_id('event_service'), object_id('LobData'),
                                          null, null, 'DETAILED');
GO

DBCC TRACEON (3604); 
GO 
DBCC PAGE ('event_service', 1, 610871, 3); 
GO


ALTER TABLE LobData ADD DATA VARCHAR(20) NOT NULL constraint df_lobdata default('yes')

insert into dbo.LobData(ID) 
values (2);

select * from LobData

ALTER TABLE LOBDATA DROP CONSTRAINT df_lobdata;


ALTER TABLE LobData ADD constraint df_lobdata default('no') for data;

insert into dbo.LobData(ID) 
values (3);

select * from LobData


select pc.* 
from sys.system_internals_partitions p
join sys.system_internals_partition_columns pc on p.partition_id = pc.partition_id
where p.object_id = object_id('lobdata');

同事瞎折腾，问了一个问题：
起100个线程连接数据库，如何保证同一个query得到100条不同的结果。

在SQL SERVER下，是可以通过这么实现的。每次读的时候，强制加一个U/X锁，这个时候，再用HINT READPAST，即可跳过已经被锁住的记录，读取下一条记录。

BEGIN TRANSACTION

SELECT TOP 1 * FROM T1 WITH(READPAST, UPDLOCK)

微软官网这么解释
READPAST
Specifies that the Database Engine not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped but page-level locks are not skipped.

但是这里还是会发生LOCK escalate的，即key lock会升级成为page lock，或者table lock。

https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table

1. def...end, class...end, module...end都是作用域门，进入他们就会出现新的scope
2. block是特殊的scope，block中initialize的局部变量在外部没法访问
3. 扁平作用域可以用，define_method, Class.new, Module.new来实现。

钩子们，来吧

https://www.sitepoint.com/rubys-important-hook-methods/

# included
module Person
  
  def name
    puts "My name is Person"
  end

  module ClassMethods
    def class_method_1
      puts "Class method is called"
    end
  end

  module InstanceMethods
    def instance_method_1
      puts "instance method is called"
    end
  end

  def self.included(receiver)
    receiver.extend         ClassMethods
    receiver.send :include, InstanceMethods
    puts "#{receiver} included #{self}"
  end
end


class User
  include Person
end

user = User.new
user.name
user.instance_method_1
user.class.class_method_1
p User.ancestors

puts "="*16
# extended
module Animal
  def name
    puts "I'm an animal"
  end

  def self.extended(receiver)
    puts "#{receiver} extended #{self}"
  end
end

class Dog
  extend Animal
end

Dog.name

puts "="*16
# prepended
module Ship
  def name
    puts "I'm a ship"
  end

  module ClassMethods
    def class_method_1
      puts "Class method is called"
    end
  end

  module InstanceMethods
    def instance_method_1
      puts "instance method is called"
    end
  end

  def self.prepended(receiver)
    receiver.extend         ClassMethods
    receiver.send :prepend, InstanceMethods
    puts "#{receiver} prepended #{self}"
  end

end

class Boat
  prepend Ship

  def name
    puts "I'm a boat. I should be overried by Ship so you won't see me."
  end
end

Boat.new.name
Boat.new.instance_method_1
p Boat.ancestors
Boat.class_method_1

puts "="*16
# inherited
class Parent

  def self.inherited(child_class)
    puts "#{child_class} inherits #{self}"
  end

  def name
     "My name is Parent"
  end
end

class Child < Parent
end

puts Child.new.name # => My name is Parent
p Child.ancestors

puts "="*16
# method_missing
class Coffee
  def price
    2
  end

  def name
    "I'm a coffee"
  end
end

class Milk
  def initialize(coffee)
    @coffee = coffee
  end
  
  def price
    super + 0.2
  end
  
  def method_missing(meth, *args)
    "#{meth} not defined on #{self}"
    if @coffee.respond_to?(meth)
      @coffee.send(meth, *args)
    else
      super
    end
  end

end

coffee = Coffee.new
coffee_with_milk = Milk.new(coffee)
p coffee_with_milk.price
p coffee_with_milk.name
p coffee_with_milk.class.instance_methods(false) #you won't see #name method

Ruby中的继承不仅仅可以继承实例方法，还可以继承类方法。但是，对于MIXIN的类方法，只能够用BASE.EXTEND来实现了。

class Person
  def self.method_1
    puts "I'm Person class method 1"
  end

  class << self
    def method_2
      puts "I'm Person class method 2"
    end
  end

  def method_3
    puts "I'm instance method 3"
  end


  protected

  def protected_method_1
    puts "I'm protected_method_1"
  end

  private

  def private_method_1
    puts "I'm private_method_1"
  end


end

class Phoenix < Person
  
  def method_4
    puts "I'm instance method 4"
  end

  def call_protected_method_1
    puts "going to call protected_method_1 from Phoenix"
    protected_method_1
  end

  def call_private_method_1
    puts "going to call private_method_1 from Phoenix"
    private_method_1
  end
end

Phoenix.method_1 #ok to inherit class methods
Phoenix.method_2 #ok to inherit class methods

phoenix = Phoenix.new

phoenix.method_3 #ok to inherit instance methods
phoenix.method_4 #ok

phoenix.call_protected_method_1 #ok to call protected method in parent class
# phoenix.protected_method_1 # failed. you could not call it directly
phoenix.call_private_method_1 #ok to call private method in parent class
# phoenix.private_method_1 #failed

# However, mixin class methods could not be inherited unless call base.extend

修饰，是设计模式里面提到的，不影响原始的对象，给对象附上多个组件，让其能够支持多个方法。https://robots.thoughtbot.com/evaluating-alternative-decorator-implementations-in提到了几种方法，对比了一下，觉得SimpleDelegator还不错。对于简单的case，还可以直接用Forwardable。

相对于继承，修饰的好处是不需要像继承一样创建很多方法，直接就可以转到原始对象上去。

require 'delegate'

class Coffee
  def cost
    2
  end

  def origin
    "Colombia"
  end
end


module DecoratorClass
  def class
    __getobj__.class
  end
end

class Milk < SimpleDelegator
  include DecoratorClass

  def cost
    super + 0.4
  end
end

class Sugar < SimpleDelegator
  include DecoratorClass

  def cost
    super + 0.2
  end
end

p Milk.ancestors
coffee = Coffee.new
p Sugar.new(Milk.new(coffee)).cost   # 2.6
p Sugar.new(Sugar.new(coffee)).cost  # 2.4
p Milk.new(coffee).origin            # Colombia
p Sugar.new(Milk.new(coffee)).class  # Coffee

最近在http://exercism.io/上面刷题玩，遇到字母算术题。这个应该是目前最难的一道题了。实在不知道如何解，于是在网上搜了一下。找到一个算法，其实就是穷举法。
http://www.poboke.com/study/python-solve-alphametics.html
简单换成Ruby，还可以工作。

其实内容和Python一样的，只是换成ruby而已。刚好Ruby里面也有permutation这样的方法，直接就可以用了。但是性能不是很好，虽然代码行数比较少。

题外话：说Python比Ruby简洁的，你们试试把Ruby的end抹掉以后再对比行数吧。

module Alphametics
  def self.solve(input)
    words = input.scan(/\w+/)
    uniq_characters = words.join.chars.uniq
    answer_list = []
    (0..9).to_a.permutation(uniq_characters.size).each do |e|
      next if words.any? { |word| word.tr(uniq_characters.join, e.join(''))[0] == '0' }
      if instance_eval(input.tr(uniq_characters.join, e.join('')))
        answer_list = e
        break
      end
    end
    return {} if answer_list.empty?
    Hash[Hash[uniq_characters.zip answer_list].sort]
  end
end

https://www.toptal.com/ruby/ruby-concurrency-and-parallelism-a-practical-primer
https://www.igvita.com/2008/11/13/concurrency-is-a-myth-in-ruby/
http://blog.ifyouseewendy.com/blog/2016/02/16/ruby-concurrency-in-theory/#concurrency-vs-paralelism

并发 V.S. 并行
concurrency v.s. parallelism
如果理解没有偏差，并行就像是4个进程各自利用一个CPU执行任务。有的进程快些，有的进程慢些，但都无所谓，至少是可以一块执行的。
并发，只是说要同时处理很多事情，无关乎并行与否。如果在单核上面，启动多个线程，CPU的调度会频繁在线程之间切换，看上去是并行，实际上不是的。

在Ruby 1.9之后，可以支持多个native thread。可是，由于GIL的存在，同一时刻只允许一个线程在执行，多线程没法充分利用多核。但是，Ruby是可以利用多核的，那就是你启动多个进程，那么就可以利用多核了。

最近数据库服务器很慢，跑了一下whoIsActive，发现有很多waittype是RESOURCE_SEMAPHORE，发现很是好玩，整理一下。

一个成功的查询操作会引入3个内存的使用，即查询语句的编译，查询计划的缓存以及数据的缓存。

如何查看编译需要的内存，数据缓存，以及cached plan size

1. 通过Actual query plan是最快的方法。第一个就是cached plan size(KB), 此外还有CompileMemory(B)和MemoryGrantInfo下的GrantedMemory(KB)。
   
2. 在执行计划中查看
   
3. 用SQL profiler捕获show plan XML for query compile event，进而在XML查看。其实拿到的数据和第二步一样。

如何模拟RESOURCE_SEMAPHORE

1. https://www.brentozar.com/archive/2013/08/query-plans-what-happens-when-row-estimates-get-high/ 上例举了两个query。在SSMS里面，多开几个窗口，同时跑第二个query就行了。

RESOURCE_SEMAPHORE情况下，有哪些有趣的数据

1. sp_whoIsActive 执行sp_whoIsActive 就可以直观看到哪个session已经相应的query
   
2. sys.dm_exec_query_resource_semaphores 可以看到当前的内存情况
   
3. sys.dm_exec_query_memory_grants 可以看到session获取内存的情况
   

RESOURCE_SEMAPHORE情况下，所有query都会suspend么？

1. 不会。如果你的query还是像Brent Ozar提到的第二个query一样需要很多的内存，那就妥妥地等着吧。如果你的query需要的内存很少，那SQL server会直接执行。

引用

https://www.brentozar.com/archive/2013/08/query-plans-what-happens-when-row-estimates-get-high/
https://blogs.msdn.microsoft.com/sqlqueryprocessing/2010/02/16/understanding-sql-server-memory-grant/

熊在微信上找我，问解数独的三种方法。我第一反应是写段代码去解，就哼哧哼哧地写了。后来才发现，她只是要方法而已，就直接丢给链接过去了。（http://www.conceptispuzzles.com/zh/index.aspx?uri=puzzle/sudoku/techniques）

但自己还是把这段代码写完了，试了一下，能够解出唯一结果，如果有多个可能就不行了（暂时把可能性列出来了而已）。
最新代码已经上传到 https://github.com/zhongxiao37/zhongxiao37.github.com/blob/master/sudo/sudo.rb

Python 和 C 一样，有形参和实参的概念。但是我用ruby的时候，却没有这样的感觉。我每次都会data = function(data)，这样的话，我就不需要去担心到底是传递值还是传递指针。但是，就Ruby而言，传递的是对象的引用。详情可以参考：http://www.iteye.com/topic/1117575

今天发现个新问题。发现在循环中声明变量并不会reset 变量值。

DECLARE @i INT = 0;

WHILE @i < 10
BEGIN
    DECLARE @b int

    print CONCAT('PREV:', @b)

    SET @b = @i

    PRINT CONCAT('NOW:', @b)

    SET @i = @i+1

END

执行上面的query，你会发现PREV的输出并不一致是空，而是有值的。顺手搜了一下，发现很多人都发现这个问题，但是却没有一个官方文档解释这个。立即创建一个规则去避免这坑。

Antlr的全程是ANother Tool for Language Recognition，可以用来实现编程语言的解析。由于项目的需要，最近一个月和同事完成了一个针对TSQL的语法分析，进而创建一些规则去审查代码（连这都自动化掉，我实在是太懒了！）。
Antlr是一个比较老的项目了，最新的版本是4.7。个人觉得，这个项目现在之所以成功，还是因为开源以后，很多人都来贡献Antlr的语法文件。在github上，你可以找到主流编程语言的语法文件（https://github.com/antlr/grammars-v4/）。有了语法文件，你就可以直接通过简单的教程使用Antlr。
我们在使用Antlr的时候，已经有了TSQL.g4文件了。语法不是很完整，但我们可以自己贡献代码去帮助其更加完整。可以预见的是，这些语法文件会越来越完整的。

学习Antlr

最经典的还是这个工具开发者自己写的书《The Definitive ANTLR 4 Reference》。快速入门，可以参考https://tomassetti.me/antlr-mega-tutorial/#working-with-a-listener 以及 http://jakubdziworski.github.io/java/2016/04/01/antlr_visitor_vs_listener.html
此外，中文的可以参考https://dohkoos.gitbooks.io/antlr4-short-course/content/getting-started.html。

分析TSQL

这个项目的目的就是分析TSQL，进而审查代码，以便开发避免一些坏的编程习惯。要分析TSQL，我们第一反应都是用正则表达式。很不幸，在这个事情上，正则表达式简直是弱爆了！我找到一个比较成熟的语法分析工具，不过是针对pg数据库的。https://pganalyze.com/blog/parse-postgresql-queries-in-ruby.html。文中作者在无数次尝试以后，也发现正则表达式的无力。
通常，我们会看微软自己有没有开发的工具或者API用来分析TSQL。最好的情况就是SSMS里面有个语法分析的组件单独分离出来使用。其实后来我也找到了这样的工具，叫 microsoft.sqlserver.transactsql.scriptdom。但是后来我们考虑到可能还要支持其他类型的数据库，所以我还是选择了Antlr。
在Github上面找到tsql的语法文件，再按照快速入门的步骤，配置好环境，执行下面的命令，就可以拿到整个语法树了。

antlr4 tsql.g4 && javac *.java && grun tsql tsql_file -gui test.sql

Vistior 和 Listener之争

这只是分析语法的两种不同方式而已，看自己的情况选择。一般来说，如果你只想分析某一个rule以及他下面的rule，可以考虑用Visitor。用Listener的话，可以自动的遍历所有的rule。个人而言，还是喜欢Listener。
比如，我们要求在存储过程中，不能够使用PRINT语句，因为我们不希望存储过程返回除了respond code之外的信息。用Listener的话，我只需要在进入和退出存储过程的时候，设置一个flag。然后再判断所有的PRINT语句，如果当前flag为true，就表明这个PRINT是在存储过程里面。

计算某列的和，可以直接用SUM。但如果和SIGN结合起来，事情就有可能复杂了。比如，SUM可以替换成下面一行

SUM((1 - SIGN(Amount)) * Amount / 2) + SUM((1 + SIGN(Amount)) * Amount / 2)

其中，前一部分是算所有正数和，比CASE WHEN 巧妙。

SELECT SUM((1 + SIGN(Amount)) * Amount / 2)

后一部分是算负数和的绝对值。

SELECT SUM((1 - SIGN(Amount)) * Amount / 2)

巧妙+巧妙却不一定等于巧妙！

此外，还有求绝对值的和

SUM((1 + SIGN(Amount)) * Amount / 2) - SUM((1 - SIGN(Amount)) * Amount / 2)

如果要计算昨天的时间，第一反应都是

SELECT DATEADD(DAY, -1, GETUTCDATE())

但还有另外一种写法。因为SQL SERVER的base date是1900-01-01，所以，你可以试着写成下面这样，同样可以得到昨天。

SELECT GETUTCDATE() + '1899-12-31'

可能时间长了，大家会免疫这种写法，你还可以改成，1899-12-30，即-2天。

SELECT DATEADD(DAY, 1, GETUTCDATE()) + '1899-12-30'

还可以这样

SELECT GETUTCDATE() - 1

有这么一个需求，查某个customer在某个时间段内是否买个产品。一般情况下，都会用JOIN，然后再去重即可。比如

SELECT DISTINCT c.id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.order_dt BETWEEN '2017-02-01' AND '2017-03-01'
AND c.id=43849

这样做有个问题，如果customer对应的orders比较多的时候，IO开销就不一样了。因为执行计划会每次读取所有的orders，然后再去重。

相反，如果用EXISTS，执行计划就会去判断是否买过产品，买过即停止，不会继续扫描。

SELECT c.id
FROM customers c
WHERE EXISTS(SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id AND o.order_dt BETWEEN '2017-02-01' AND '2017-03-01'
    )
AND c.id=43849

对比IO开销，前者比后者开销更多。在复杂query和数据量比较多的情况下，这种差距更加明显。

此外，如果query一直这么简单，那么SQL SERVER会自动优化，会在执行计划中加入TOP操作，这样就不会把所有记录都查出来再去重。

所以，尽所有可能，把EXISTS都替换成JOIN，增加IO开销吧！

use master;

IF NOT EXISTS(SELECT 1 FROM SYS.DATABASES WHERE NAME = 'test_db')
    CREATE DATABASE test_db;
GO

use test_db;
GO


IF OBJECT_ID(N'customers') IS NOT NULL
    DROP TABLE customers;

CREATE TABLE customers (
    id int,
    CONSTRAINT pk_customers PRIMARY KEY CLUSTERED (ID)
)

INSERT INTO customers(id)
SELECT n
FROM dbo.getnums(500000)


IF OBJECT_ID(N'orders') IS NOT NULL
    DROP TABLE orders;

CREATE TABLE orders (
    id int identity(1, 1),
    customer_id int,
    order_dt datetime,
    CONSTRAINT pk_orders PRIMARY KEY CLUSTERED (ID),
    CONSTRAINT fk_orders__customer_id FOREIGN KEY (customer_id) REFERENCES customers(id)
)


INSERT INTO orders(customer_id, order_dt)
SELECT n/100+1, DATEADD(DAY, n/700, '2000-01-01')
FROM dbo.getnums(5000000)

INSERT INTO orders(customer_id, order_dt)
SELECT 43849, DATEADD(DAY, n/70, '2000-01-01')
FROM dbo.getnums(500)


SELECT DISTINCT c.id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.order_dt BETWEEN '2017-02-01' AND '2017-03-01'
AND c.id=43849
OPTION(RECOMPILE, MAXDOP 1)

SELECT c.id
FROM customers c
WHERE EXISTS(SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id AND o.order_dt BETWEEN '2017-02-01' AND '2017-03-01'
    )
AND c.id=43849
OPTION(MAXDOP 1)

背景是，有两个report的金额不平，需要找出里面不平的原因。很早以前是用这个工具去实现的。
新建一个excel文件，在文件里A、B栏输入report A的key和value，在D、E栏输入report B的key和value，然后执行下面的命令就行了。



代码

require 'slop'
require 'logger'
require 'simple_xlsx_reader'


# Usage
# ruby discrepancy_finder.rb [options]
# options:
# -v print the version
# -d enable debug mode
# -m model:
#     model = 1 check both keys and values
#     model = 2 only check keys
# -s sheet index: 0 will be the first sheet
# example:
# ruby discrepancy_finder.rb
# ruby discrepancy_finder.rb -d
# ruby discrepancy_finder.rb -m 2
# ruby discrepancy_finder.rb -d -m 2 -s 0


opts = Slop.parse do |o|
  o.integer '-m', '--model', 'model', default: 1
  o.integer '-s', '--sheet_index', 'specific sheet number', default: 0
  o.bool '-d', '--debug', 'enable debug mode'
  o.on '-v', 'print the version' do
    puts '0.0.1'
    exit
  end
end

def logger
  @logger ||= Logger.new(STDOUT)
end

def load_col_data(wb, col_index)

  _hash = Hash.new

  lastrow = 1

  while wb.rows[lastrow] && wb.rows[lastrow][col_index]

      _hash["#{wb.rows[lastrow][col_index]}"] = (_hash["#{wb.rows[lastrow][col_index]}"].nil? ? 0 : _hash["#{wb.rows[lastrow][col_index]}"]) + ( wb.rows[lastrow][col_index+1].nil? ? 0 : wb.rows[lastrow][col_index+1].to_f.round(2) )
      lastrow = lastrow + 1
  end

  _hash

end

doc = SimpleXlsxReader.open("revenue_report_diff.xlsx")

wb = doc.sheets[opts[:sheet_index]]

logger.debug "Running in model #{opts[:model]}" if opts.debug?

#remove duplicate records
left_hash = load_col_data(wb, 0)
right_hash = load_col_data(wb, 3)


leftSum = left_hash.values.inject(:+).round(2)
rightSum = right_hash.values.inject(:+).round(2)


revenueDiff = leftSum - rightSum
puts '##########REVENUE DIFF(LEFT - RIGHT)##############'
if opts[:model] == 2
  puts "LEFT: " + leftSum.to_s
  puts "RIGHT: " + rightSum.to_s
  puts "DIFF: " + revenueDiff.round(2).to_s
end

lostRecordsInRight =  left_hash.keys - right_hash.keys
lostRecordsInLeft = right_hash.keys - left_hash.keys

unless lostRecordsInRight.empty?
  puts 'Missing records in RIGHT side:'
  lostRecordsInRight.each do |t|
      puts t + "\t" + left_hash["#{t}"].round(2).to_s
  end
end

unless lostRecordsInLeft.empty?
  puts 'Missing records in LEFT side:'
  lostRecordsInLeft.each do |t|
      puts t + "\t" + right_hash["#{t}"].round(2).to_s
  end
end

if opts[:model] == 2
  puts 'Discrepancy:'
  left_hash.each do |k, v|
      if right_hash["#{k}"].nil?
        # puts k + " is missing."
      elsif left_hash["#{k}"].round(2) != right_hash["#{k}"].round(2)
          puts k + "\t" + left_hash["#{k}"].round(2).to_s + "\t" + right_hash["#{k}"].round(2).to_s
      end
  end
end

之前介绍过通过Extended Event观察锁，这次介绍通过dm_tran_locks来观察锁。

BEGIN TRANSACTION

DELETE TOP (1) p FROM tbl p 

EXEC ##TranLocks

ROLLBACK TRANSACTION

##TranLocks SP

CREATE PROCEDURE ##TranLocks
    @objectName NVARCHAR(255) = '%',
    @requestMode VARCHAR(10) = '%',
    @processID INT = @@SPID,
    @ignoreSharedDatabaseLocks BIT = 1
AS
SELECT 
    DB_NAME() AS database_name,
    CASE 
        WHEN dt1.resource_type = 'OBJECT' THEN object_name(dt1.resource_associated_entity_id, resource_database_id) 
        WHEN dt1.resource_type IN('KEY', 'PAGE') THEN object_name(pa1.object_id) 
        ELSE dt1.resource_description
        END AS object_name,
    in1.name AS index_name,
    pa1.partition_number,
    dt1.resource_type,
    dt1.request_mode,
    COUNT(*) AS locks,
    dt1.request_owner_id,
    dt1.request_session_id
FROM
    sys.dm_tran_locks dt1
    LEFT OUTER JOIN sys.partitions pa1 ON(dt1.resource_associated_entity_id = pa1.hobt_id)
    LEFT OUTER JOIN sys.indexes in1 ON(pa1.object_id = in1.object_id AND pa1.index_id = in1.index_id)
WHERE 
    (request_session_id = @processID OR @processID = 0) 
    AND request_mode LIKE @requestMode
    AND(NOT(dt1.resource_type = 'DATABASE' AND dt1.request_mode = 'S') OR @ignoreSharedDatabaseLocks = 0)
    AND dt1.resource_database_id = DB_ID()
    AND 
        CASE 
            WHEN dt1.resource_type = 'OBJECT' THEN object_name(dt1.resource_associated_entity_id, resource_database_id) 
            WHEN dt1.resource_type IN('KEY', 'PAGE') THEN object_name(pa1.object_id) 
            ELSE dt1.resource_description
            END LIKE @objectName
GROUP BY
    dt1.resource_type,
    CASE 
        WHEN dt1.resource_type = 'OBJECT' THEN object_name(dt1.resource_associated_entity_id, resource_database_id) 
        WHEN dt1.resource_type IN('KEY', 'PAGE') THEN object_name(pa1.object_id) 
        ELSE dt1.resource_description
        END,
    in1.name,
    pa1.partition_number,
    dt1.request_mode,
    dt1.request_session_id,
    dt1.request_owner_id
ORDER BY database_name, object_name, index_name, partition_number, request_mode, locks;
GO

直接上测试脚本。LOB可以存储VARCHAR(MAX), TEXT, IMAGE这些数据类型。但是各个类型的behavior又不太一样。

和OVERFLOW DATA 一样，数据页会有一个pointer指向LOB root structure，在LOB root structure上，又会分别指向其他LOB data page。如果数据超过32KB，LOB root structure又会引入中间层，像INDEX中的B tree一样。

NVARCHAR(MAX)

IF OBJECT_ID('LobData') IS NOT NULL
    DROP TABLE dbo.LobData

create table dbo.LobData
(
    ID int not null,
    Col1 nvarchar(max) null
);

insert into dbo.LobData(ID, Col1) 
values (1, replicate(convert(varchar(max),'a'),16000));


SELECT allocated_page_file_id as PageFID, allocated_page_page_id as PagePID,
       object_id as ObjectID, partition_id AS PartitionID,
       allocation_unit_type_desc as AU_type, page_type as PageType
FROM sys.dm_db_database_page_allocations(db_id('test_db'), object_id('LobData'),
                                          null, null, 'DETAILED');
GO

DBCC TRACEON (3604); 
GO 
DBCC PAGE ('test_db', 1, 623876, 3); 
GO

当Column为NVARCHAR(MAX)且16000字符串（实际长度为32000）的时候，SQL server不会单独开辟一个新的page去存储LOB root structure。相反，LOB pointer会存在当前page。1个IN_ROW数据页，4个LOB 数据页。指针留在第一个数据页。

DBCC PAGE第一个数据页，可以看到BLOB Inline Root。其中，LOB pointer和ROW_OVERFLOW的pointer类似，如下

1. 04 00000001 00000020 1c0000 metadata
2. 68 1f0000 length
3. 01 850900 page number
4. 01 00 file number
5. 0000 slow number

page number按照前一个文章的方法，实际应该是0x98501，即623873。同理可以拿到剩下3个pointer。

d0 3e000002 850900
01 000000
38 5e000003 850900
01 000000
00 7d000037 520900
01 000000

TEXT

可能是对deprecated 数据类型支持不好，对于TEXT直接就开辟一个新的page为LOB root structure。DBCC PAGE LOB root structure，可以看到LOB pointer。

VARCHAR(MAX)

和NVARCHAR(MAX)一样的行为，当数据比较短的时候，存为BLOB Inline Root。当数据更短的时候，直接就存在当前IN_ROW page。

IF OBJECT_ID('LobData') IS NOT NULL
    DROP TABLE dbo.LobData

create table dbo.LobData
(
    ID int not null,
    Col1 varchar(max) null
);

insert into dbo.LobData(ID, Col1) 
values (1, replicate(convert(varchar(max),'a'),400));

在这个例子中，只有两个page。一个是IAM，另外一个就是数据页，没有单独的LOB page。
DBCC page数据页，可以看到BLOB Inline Data。



参考:

1. http://aboutsqlserver.com/2013/11/05/sql-server-storage-engine-lob-storage/
2. http://improve.dk/what-is-the-size-of-the-lob-pointer-for-max-types-like-varchar-varbinary-etc/

SQL Server有IN_ROW，ROW_OVERFLOW和LOB 3种page。

首先，来看看ROW_OVERFLOW是怎么存储的吧。

首先创建一个Table，插入一下数据。ID和Col1会存储在第一个page上，即IN_ROW page。

IF OBJECT_ID('RowOverflow') IS NOT NULL
    DROP TABLE RowOverflow;
GO
create table dbo.RowOverflow 
( 
    ID int not null, 
    Col1 varchar(8000) null, 
    Col2 varchar(8000) null 
);

insert into dbo.RowOverflow(ID, Col1, Col2) 
values (1,replicate('a',8000),replicate('b',8000));

验证一下表的数据页

SELECT allocated_page_file_id as PageFID, allocated_page_page_id as PagePID,
       object_id as ObjectID, partition_id AS PartitionID,
       allocation_unit_type_desc as AU_type, page_type as PageType
FROM sys.dm_db_database_page_allocations(db_id('test_db'), object_id('RowOverflow'),
                                          null, null, 'DETAILED');
GO

结果如图。一共有4个page。两个IAM，一个数据页，另外一个LOB或者Overflow page。

执行DBCC PAGE，可以看到第一页的存储情况。

DBCC TRACEON (3604); 
GO 
DBCC PAGE ('test_db', 1, 610871, 3); 
GO

数据的最后一共有24bytes，这些都是字节交换的，所以需要倒着读。

1. 02000000 01000000 29000000 401f0000 是metadata attribute。
2. 31520900 是page number
3. 0001 是file number
4. 0000 是slot number

在Kalen写的SQL SERVER 2012 internals里面提到了metadata attribute。

The first 16 bytes of a row-overflow pointer

Bytes	Hex value	Decimal value	Meaning
0	0x02	2	Type of special field: 1 = LOB2 = overflow
1–2	0x0000	0	Level in the B-tree (always 0 for overflow)
3	0x00	0	Unused
4–7	0x00000001	1	Sequence: a value used by optimistic concurrency control for cursors that increases every time a LOB or overflow column is updated
8–11	0x00007fc3	32707	Timestamp: a random value used by DBCC CHECKTABLE that remains unchanged during the lifetime of each LOB or overflow column
12–15	0x00000834	2100	Length

31520900应该读作0X00095231，换做十进制的610865。


继续往下看，你可以看到Col2的RowId记录的page number。这个也印证了我们从dm_db_database_page_allocations里看到的情况。
来看看ROW_OVERFLOW page的情况。就可以看到Col2的数据了。

DBCC PAGE ('test_db', 1, 610871, 3); 
GO

参考

1. http://aboutsqlserver.com/2013/11/05/sql-server-storage-engine-lob-storage/
2. https://www.microsoftpressstore.com/articles/article.aspx?p=2225060

总是不喜欢UI，点得慢死了~

USE Master; 
GO  
SET NOCOUNT ON 

-- 1 - Variable declaration 
DECLARE @dbName sysname 
DECLARE @backupPath NVARCHAR(500) 
DECLARE @dbDataPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500) 
DECLARE @fileList TABLE (backupFile NVARCHAR(255)) 
DECLARE @lastFullBackup NVARCHAR(500) 
DECLARE @backupFile NVARCHAR(500) 
DECLARE @NoExec bit
DECLARE @debug bit


-- 2 - Initialize variables 
SET @dbName = 'KEY_WORD_IN_YOUR_BACKUP_FILE' 
SET @backupPath = 'D:\' 
SET @dbDataPath = 'D:\Data\'
SET @NoExec = 1
SET @debug = 1

-- 3 - get list of files 
SET @cmd = 'DIR /b ' + @backupPath 

INSERT INTO @fileList(backupFile) 
EXEC master.sys.xp_cmdshell @cmd 

-- 4 - Find latest full backup 
SELECT @lastFullBackup = MAX(backupFile)  
FROM @fileList  
WHERE backupFile LIKE '%.BAK'  
   AND backupFile LIKE 'Servlet.' + @dbName + '%' 

SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''  
       + @backupPath + @lastFullBackup + '''' + CHAR(10) + 'WITH RECOVERY, REPLACE,'  + CHAR(10)
  + 'MOVE N''RECNETSTARTUP_dat'' TO N''' + @dbDataPath + @dbName + '.mdf'','   + CHAR(10)
  + 'MOVE N''RECNETSTARTUP_log'' TO N''' + @dbDataPath + @dbName + '_log.LDF'',  NOUNLOAD,  STATS = 5'

IF @debug = 1
PRINT @cmd 
IF @NoExec <> 1
EXEC (@cmd)

可能大家都习惯了SELECT * FROM TABLE，或者SELECT DATA FROM TABLE WHERE ID = 1。一般来说，无非就是INDEX SEEK，或者CLUSTERED INDEX SCAN。但下面这个例子，却两者都不是。

IF OBJECT_ID('DBO.T1') IS NOT NULL
    DROP TABLE t1

CREATE TABLE T1(
    ID BIGINT,
    ANOTHER_ID INT,
    DATA VARCHAR(200),
    CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED (ID)
)

INSERT INTO T1(ID, ANOTHER_ID,DATA)
SELECT n,
    n%4, 
    CASE WHEN n % 4 = 1 THEN 'pHoEnIx'
                    WHEN n % 4 = 2 THEN 'eRiK'
                    WHEN n % 4 = 3 THEN 'dBe'
                    ELSE 'Papa'
                    END
FROM dbo.getnums(50000)

CREATE NONCLUSTERED INDEX idx_t1_another_id ON t1(another_id)

SELECT ID FROM T1
SELECT DATA FROM T1

对于第二个SELECT query，很明显，DATA不被包含在non-clustered index里面，所以只能够用CLUSTERED INDEX SCAN。但是第一个SELECT query，却是INDEX SCAN。WHY？是错误的执行计划？不是。
事实是，很明显non-clustered index占用的空间比clustered index小，而且non-clustered index的最后一个节点就是ID，不用它用谁。此外，如果一个表上面有多个non-clustered index，SQL SERVER会用INDEX占用最小的INDEX。



如果一切这么简单就好，这样的query单独列出来可能稍微仔细看就发现问题了。继续上面的SQL script

CREATE TABLE T2(
    ID INT
)

INSERT INTO T2(ID)
SELECT n*4
FROM dbo.getnums(50000)

DELETE FROM T2 WHERE NOT EXISTS(SELECT 1 FROM T1 WHERE T1.ID = t2.ID)

当T2的数据级和T1的数据级差不多的时候，SQL SERVER就不会SCAN T2再去T1里面进行CLUSTERED INDEX SEEK。相反，SQL SERVER会对两个表都进行SCAN，再HASH MATCH。这里很隐晦地把SELECT ID FROM TABLE转换成SELECT 1 FROM TABLE WHERE ID ...。满心以为这个很明显的是CLUSTERED INDEX SEEK啊， WHERE ID = 啊。等你看执行计划的时候，你就傻眼了。啥？！INDEX SCAN？？


一旦是INDEX SCAN，一般都是PAGE级别的锁，放在non-clustered index上，一不留神就会导致长时间BLOCKING。