HoneyHoney

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