大家都知道我隸屬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