解剖SQLSERVER 第五篇 OrcaMDF里读取Bits类型数据（译）

解剖SQLSERVER 第五篇 OrcaMDF里读取Bits类型数据（译） http://improve.dk/reading-bits-in-orcamdf/ Bits类型的存储跟SQLSERVER其他定长数据类型的存储很不一样。通常，所有定长列都会显示出来，一个条记录里定长数据部分的字段数据总是一个挨着一个 我们

解剖SQLSERVER 第五篇  OrcaMDF里读取Bits类型数据（译）

http://improve.dk/reading-bits-in-orcamdf/

Bits类型的存储跟SQLSERVER其他定长数据类型的存储很不一样。通常，所有定长列都会显示出来，一个条记录里定长数据部分的字段数据总是一个挨着一个

我们可以写入磁盘的最小数据单位是一个字节，存储位类型数据的天真的方法就是使用一整个（字节@）来存储每一个位，使用常用的格式去解释位类型数据是很简单的

，不过这会浪费一些空间 ，就像null位图，如果一个表只有3列，那么用一个字节来存储null位图会比较浪费，因为其他的5个位都没有用到

@：文章里是用位 ，这里应该是用字节吧

 

在记录的内部位类型是如何存储的？

一些位类型列的值是存储在一个字节中的，最大可以到8个位，通常，我们会有如下表定义

<span>CREATE</span> <span>TABLE</span><span> BitTest
(
    A </span><span>bit</span><span>
    B </span><span>bit</span><span>
    C </span><span>bit</span><span>
    D </span><span>int</span><span>
)</span>

记录的定长部分数据需要占用5个字节，4个字节存储int 列 ，而另一个字节存储A 、B、C这三列位类型的数据，只用了字节里面的3个位

我们再添加一些列

<span>CREATE</span> <span>TABLE</span><span> BitTest
(
    A </span><span>bit</span><span>
    B </span><span>bit</span><span>
    C </span><span>bit</span><span>
    D </span><span>int</span><span>
    E </span><span>bit</span><span>
    F </span><span>bit</span><span>
    G </span><span>bit</span><span>
    H </span><span>smallint</span><span>
    I </span><span>bit</span><span>
    J </span><span>bit</span><span>
    K </span><span>bit</span><span>
)</span>

E到G列按道理来说应该存储在D列的后面，但是他们会继续使用第一个 bit byte，直到第一个 bit byte使用完所有的位空间为止

下面的图显示了H列（smallint ）直接存储在D列的后面，而在D列后面是存储K列的新bit byte，因为第一个bit byte已经满了

 

当读取行记录里的位类型时我们需要知道的状态

很明显，我们一次不能只读取一个字段的值，我们读取固定长度数据类型的时候还需要读取定长数据偏移指针

我们需要一些能在读取的时候指示我们当前读取到字节中哪一个位属于哪一个字段的状态，然后我们读取一个新的bit byte

我来介绍一下RecordReadState类

<span>public</span> <span>class</span><span> RecordReadState
{
    </span><span>//</span><span> We start out having consumed all bits as none have been read</span>
    <span>private</span> <span>int</span> currentBitIndex = <span>8</span><span>;
    </span><span>private</span> <span>byte</span><span> bits;

    </span><span>public</span> <span>void</span> LoadBitByte(<span>byte</span><span> bits)
    {
        </span><span>this</span>.bits =<span> bits;
        currentBitIndex </span>= <span>0</span><span>;
    }

    </span><span>public</span> <span>bool</span><span> AllBitsConsumed
    {
        </span><span>get</span> { <span>return</span> currentBitIndex == <span>8</span><span>; }
    }

    </span><span>public</span> <span>bool</span><span> GetNextBit()
    {
        </span><span>return</span> (bits & (<span>1</span> 0<span>;
    }
}</span>

RecordReadState 类当前只需要处理bits，但是将来我可能还要创建一个BitReadState 类用来保存读取状态

RecordReadState 类保存了一个字节用来当作指针指出下一个可用的位在字节的哪个地方，如果字节已经用完了存储满了所有的位数据

(currentBixIndex = 8 (0-7 being the available bits))，方法AllBitsConsumed 就会返回true，指示我们需要读取一个新的 bit byte

GetNextBit方法只是简单的从 bit byte中读取当前的bit ，然后将currentBitIndex（bit index）的值加1

demo

<span>using</span><span> NUnit.Framework;
</span><span>using</span><span> OrcaMDF.Core.Engine.Records;
</span><span>namespace</span><span> OrcaMDF.Core.Tests.Engine.Records
{
    [TestFixture]
</span><span>public</span> <span>class</span><span> RecordReadStateTests
{
        [Test]
</span><span>public</span> <span>void</span><span> General()
{
</span><span>var</span> state = <span>new</span><span> RecordReadState();
</span><span>//</span><span> No bits available</span>
<span>Assert.IsTrue(state.AllBitsConsumed);
state.LoadBitByte(</span><span>0xD2</span>); <span>//</span><span> 11010010
</span><span>//</span><span> Bits available</span>
<span>Assert.IsFalse(state.AllBitsConsumed);
</span><span>//</span><span> Reading bit values</span>
<span>Assert.IsFalse(state.GetNextBit());
Assert.IsTrue(state.GetNextBit());
Assert.IsFalse(state.GetNextBit());
Assert.IsFalse(state.GetNextBit());
Assert.IsTrue(state.GetNextBit());
Assert.IsFalse(state.GetNextBit());
Assert.IsTrue(state.GetNextBit());
</span><span>//</span><span> One bit left</span>
<span>Assert.IsFalse(state.AllBitsConsumed);
Assert.IsTrue(state.GetNextBit());
</span><span>//</span><span> Bits exhausted, ready for next byte</span>
<span>Assert.IsTrue(state.AllBitsConsumed);
}
}
}</span>

 

SqlBit实现

一旦我们实现了状态的读取，我们就可以实现SqlBit 类型

<span>public</span> <span>class</span><span> SqlBit : ISqlType
{
    </span><span>private</span> <span>readonly</span><span> RecordReadState readState;

    </span><span>public</span><span> SqlBit(RecordReadState readState)
    {
        </span><span>this</span>.readState =<span> readState;
    }

    </span><span>public</span> <span>bool</span><span> IsVariableLength
    {
        </span><span>get</span> { <span>return</span> <span>false</span><span>; }
    }

    </span><span>public</span> <span>short</span>?<span> FixedLength
    {
        </span><span>get</span><span>
        {
            </span><span>if</span><span> (readState.AllBitsConsumed)
                </span><span>return</span> <span>1</span><span>;

            </span><span>return</span> <span>0</span><span>;
        }
    }

    </span><span>public</span> <span>object</span> GetValue(<span>byte</span><span>[] value)
    {
        </span><span>if</span>(readState.AllBitsConsumed && value.Length != <span>1</span><span>)
            </span><span>throw</span> <span>new</span> ArgumentException(<span>"</span><span>All bits consumed, invalid value length: </span><span>"</span> +<span> value.Length);

        </span><span>if</span> (value.Length == <span>1</span><span>)
            readState.LoadBitByte(value[</span><span>0</span><span>]);

        </span><span>return</span><span> readState.GetNextBit();
    }
}</span>

SqlBit 在构造函数里传入一个read state，read state指示当前记录读取操作的范围。需要注意的是固定长度需要依据read state里的当前AllBitsConsumed值

如果字节里面所有位都被占用，那么意味着需要读取整个字节，如果if (readState.AllBitsConsumed)返回0表示不需要读取整个字节，但是GetValue方法依然会被调用

GetValue方法会验证一种情况：readState.AllBitsConsumed 返回真，证明 bit byte是有数据存储在里面，但是value.Length返回的长度是0，那证明有问题了

如果我们读到一个值，我们会请求 read state 去装载一个新的bit byte ，之后，我们可以调用GetNextBit 方法返回 read state的当前bit

相关测试

<span>using</span><span> NUnit.Framework;
</span><span>using</span><span> OrcaMDF.Core.Engine.Records;
</span><span>using</span><span> OrcaMDF.Core.Engine.SqlTypes;

</span><span>namespace</span><span> OrcaMDF.Core.Tests.Engine.SqlTypes
{
    [TestFixture]
    </span><span>public</span> <span>class</span><span> SqlBitTests
    {
        [Test]
        </span><span>public</span> <span>void</span><span> GetValue()
        {
            </span><span>var</span> readState = <span>new</span><span> RecordReadState();
            </span><span>var</span> type = <span>new</span><span> SqlBit(readState);

            </span><span>//</span><span> No bytes read - length is one</span>
            Assert.AreEqual(<span>1</span><span>, type.FixedLength);

            </span><span>//</span><span> Load byte and check length is 0</span>
            readState.LoadBitByte(<span>0xD2</span><span>);
            Assert.AreEqual(</span><span>0</span><span>, type.FixedLength);

            Assert.IsFalse((</span><span>bool</span>)type.GetValue(<span>new</span> <span>byte</span>[<span>0</span><span>]));
            Assert.IsTrue((</span><span>bool</span>)type.GetValue(<span>new</span> <span>byte</span>[<span>0</span><span>]));
            Assert.IsFalse((</span><span>bool</span>)type.GetValue(<span>new</span> <span>byte</span>[<span>0</span><span>]));
            Assert.IsFalse((</span><span>bool</span>)type.GetValue(<span>new</span> <span>byte</span>[<span>0</span><span>]));
            Assert.IsTrue((</span><span>bool</span>)type.GetValue(<span>new</span> <span>byte</span>[<span>0</span><span>]));
            Assert.IsFalse((</span><span>bool</span>)type.GetValue(<span>new</span> <span>byte</span>[<span>0</span><span>]));
            Assert.IsTrue((</span><span>bool</span>)type.GetValue(<span>new</span> <span>byte</span>[<span>0</span><span>]));

            </span><span>//</span><span> One bit left - length should still be 0</span>
            Assert.AreEqual(<span>0</span><span>, type.FixedLength);

            Assert.IsTrue((</span><span>bool</span>)type.GetValue(<span>new</span> <span>byte</span>[<span>0</span><span>]));

            </span><span>//</span><span> All bits consumed - length should be 1</span>
            Assert.AreEqual(<span>1</span><span>, type.FixedLength);
        }
    }
}</span>

 

第五篇完