使用Oracle資料表保存GUID屬性
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