0

大家都知道我隸屬GUID PK幫 .NET分舵，最近寫了個小模組，Model理所當然地使用GUID當作Primary Key，由於想同時支援SQL Server跟Oracle，第一次挑戰SQL跟Oracle共用Model。先前的GUID PK經驗都在SQL，SQL有Uniqueidentifier型別，跟C#端的Guid型別能整到天衣無縫；同樣的情場搬到Oracle，就需要動點腦筋解決。

歷經一番摸索，心得如下：

該用什麼型別？

Oracle沒有Uniqueidentifier可用，我心中的兩個選項是CHAR(32)或是RAW(16)。

CHAR(32)比較直覺，手工SQL查詢時可以直接寫WHERE SomeKey = 'd467c0d30d5b44fdb38fe3275685e43e'，但CHAR(32)長度足足是RAW(16)的兩倍，撇開資料儲存空間不談，若考慮Index效能，30公分32 Byte與16 Byte的差別很難被無視。基於效能理由，我決定使用RAW(16)，至於手工查詢時可以用HEXTORAW()或RAWTOHEX()搞定，寫成WHERE SomeKey = HEXTORAW('d467c0d30d5b44fdb38fe3275685e43e')，不算複雜。

GUID與RAW(16)轉換

以C#的角度，RAW(16)等同byte[16]，Guid.ToByteArray()可將Guid轉為byte[16]，而new Guid(byte[])則可將byte[16]轉為Guid，雙向轉換易如反掌，但隱藏一個問題。例如：

            Guid g = Guid.NewGuid();
            Console.WriteLine("Orig Guid : {0}", g);
            byte[] b = g.ToByteArray();
            Console.WriteLine("ToByteArray : {0}", BitConverter.ToString(b));
            Guid r = new Guid(b);
            Console.WriteLine("new Guid(byte[]) : {0}", r);
            string s = g.ToString("N");
            r = new Guid(s);
            Console.WriteLine("new Guid(\"{0}\") : {1}", s, r);

執行結果：           
Orig Guid : d467c0d3-0d5b-44fd-b38f-e3275685e43e
ToByteArray : D3-C0-67-D4-5B-0D-FD-44-B3-8F-E3-27-56-85-E4-3E
new Guid(byte[]) : d467c0d3-0d5b-44fd-b38f-e3275685e43e
new Guid("d467c0d30d5b44fdb38fe3275685e43e") : d467c0d3-0d5b-44fd-b38f-e3275685e43e

Guid先轉成byte[]，用new Guid(byte[])就能再轉回一模一樣的Guid，但仔細一看，byte[]的位元組順序與xxxxxxxx-xxxx-…寫法不同，d467c0d3-0d5b-44fd-b38f-e3275685e43e被轉成"D3-C0-67-D4"-"5B-0D"-"FD-44"-"B3-8F"-E3-27-56-85-E4-3E，其中d467c0d3、0d5b、44fd的位元組順序前顛倒（採Little Endian），b38f-e3275685e43eb部分則順序相同，一切是Guid規格使然。

理論上只要byte[]能再還原成原本的Guid值，倒也不需要去堅持byte[]儲存的順序。但依實務經驗，這個順序差異一定會帶來困擾。例如，在Oracle SELECT取得RAW(16)內容直接複雜貼上當成URL參數是偵錯時常見的場景，若RAW(16)儲存的是ToByteArray()的內容，交給C#用new Guid("D10E1D815C2340F98FDAF3656C237E5C")會變成d10e1d81-5c23-40f9-8fda-f3656c237e5c，而當初存入的811d0ed1-235c-f940-8fda-f3656c237e5c。

為克服Guid.ToByteArray()結果無法直接以字串方式轉成Guid，我借用Stackoverflow上網友分享的轉換函式做成ToRaw16()與FromRaw16()方法，藉此確保C#的Guid表示字串與Oracle RAW(16)查詢結果一致，以利偵錯。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace Afet.Attachment
{
    public static class GuidConverter
    {
        //REF: http://stackoverflow.com/a/17168469/4335757
        /// <summary>
        /// A CLSCompliant method to convert a big-endian Guid to little-endian
        /// and vice versa.
        /// The Guid Constructor (UInt32, UInt16, UInt16, Byte, Byte, Byte, Byte,
        ///  Byte, Byte, Byte, Byte) is not CLSCompliant.
        /// </summary>
        public static Guid FlipEndian(this Guid guid)
        {
            var newBytes = new byte[16];
            var oldBytes = guid.ToByteArray();
 
            for (var i = 8; i < 16; i++)
                newBytes[i] = oldBytes[i];
 
            newBytes[3] = oldBytes[0];
            newBytes[2] = oldBytes[1];
            newBytes[1] = oldBytes[2];
            newBytes[0] = oldBytes[3];
            newBytes[5] = oldBytes[4];
            newBytes[4] = oldBytes[5];
            newBytes[6] = oldBytes[7];
            newBytes[7] = oldBytes[6];
 
            return new Guid(newBytes);
        }
 
        public static byte[] ToRaw16(this Guid guid)
        {
            return guid.FlipEndian().ToByteArray();
        }
 
        public static Guid FromRaw16(byte[] raw)
        {
            return new Guid(raw).FlipEndian();
        }
 
    }
}

 

SQL與Oracel共用Model

使用Dapper時，Model的Guid屬性與SQL Uniqueidentifier能自動對應轉換，在Oracle卻會遇上麻煩，cn.Query("… WHERE SomeRaw16Col = :d", new { id = Guid.NewGuid() })會導致以下錯誤：

System.ArgumentException was unhandled; Message=Value does not fall within the expected range.Source=Oracle.ManagedDataAccess.

在Stackoverflow找到網友自訂OracleGuid型別再配合先前提過的SqlMapper.TypeHandler<T>完成轉換。考量將Guid型別換成古怪的OracleGuid會讓其他開發團隊成員迷惑，二則面對一些依型別進行不同處理的邏輯，增加自訂型別將造成困擾，第三，這個Mode為SQL與Oracle共用，還得留意SQL及Oracle使用不同TypeHandler設定的陷阱。

最後，我採行與處理列舉轉VARCHAR相同的做法，另外增加一個byte[] IdRaw欄位，與Guid Id 100％對應，面對SQL時用Uniqueidentifier對應Guid Id，遇到Oracle時則改用RAW(16)對應byte[] IdRaw。範例如下：

    /// <summary> 
    /// 附件容器 
    /// </summary> 
    public class AttachmentContainer 
    { 
        /// <summary> 
        /// 附件所屬項目識別碼 
        /// </summary> 
        public Guid OwnerId { get; set; }
 
        /// <summary> 
        /// 附件所屬項目識別碼（Byte[]版本） 
        /// </summary> 
        [JsonIgnore] 
        public byte[] OwnerIdRaw { 
            get 
            { 
                return OwnerId.ToRaw16(); 
            } 
             set 
            { 
                OwnerId = GuidConverter.FromRaw16(value); 
            } 
        } 
 
        
        
以上就是在Oracle資料表儲存GUID的一些小技巧，提供大家參考。

Comments

Be the first to post a comment

Post a comment