Created
August 23, 2013 02:11
-
-
Save schotime/6314871 to your computer and use it in GitHub Desktop.
PostgreSQL NPoco configuration with HStore and DateTimeOffset support
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
public class HStore : Dictionary<string, string> | |
{ | |
private static readonly Regex QuotedString = new Regex(@"""[^""\\]*(?:\\.[^""\\]*)*""", RegexOptions.Compiled); | |
private static readonly Regex UnquotedString = new Regex(@"(?:\\.|[^\s,])[^\s=,\\]*(?:\\.[^\s=,\\]*|=[^,>])*", RegexOptions.Compiled); | |
private static readonly Regex HstoreKeyPairMatch = new Regex(string.Format(@"({0}|{1})\s*=>\s*({0}|{1})", QuotedString, UnquotedString), RegexOptions.Compiled); | |
private static readonly Regex KeyReg = new Regex(@"^""(.*)""$", RegexOptions.Compiled); | |
private static readonly Regex KeyReg2 = new Regex(@"\\(.)", RegexOptions.Compiled); | |
private static readonly Regex EscapeRegex = new Regex(@"[=\s,>]", RegexOptions.Compiled); | |
private static readonly Regex EscapeRegex2 = new Regex(@"([""\\])", RegexOptions.Compiled); | |
public static Dictionary<Type, Func<object, string>> Formats = new Dictionary<Type, Func<object, string>>(); | |
public void Add(string key, object value) | |
{ | |
Func<object, string> format; | |
if (value != null && Formats.TryGetValue(value.GetType(), out format)) | |
{ | |
base.Add(key, format(value)); | |
} | |
else | |
{ | |
base.Add(key, value == null ? null : value.ToString()); | |
} | |
} | |
public string ToSqlString() | |
{ | |
return Create(this); | |
} | |
public static string Create(Dictionary<string, string> value) | |
{ | |
var keypairs = value.Select(keypair => Escape(keypair.Key) + "=>" + Escape(keypair.Value)); | |
return string.Join(",", keypairs); | |
} | |
public static HStore Create(string value) | |
{ | |
var matches = HstoreKeyPairMatch.Matches(value); | |
var hstore = new HStore(); | |
foreach (Match match in matches) | |
{ | |
string key = match.Groups[1].Value; | |
string val = match.Groups[2].Value; | |
key = KeyReg2.Replace(KeyReg.Replace(key, "$1"), "$1"); | |
val = val.ToUpper() == "NULL" ? null : KeyReg2.Replace(KeyReg.Replace(val, "$1"), "$1"); | |
hstore.Add(key, val); | |
} | |
return hstore; | |
} | |
public static string Escape(string value) | |
{ | |
return value == null ? "NULL" | |
: EscapeRegex.IsMatch(value) ? string.Format("\"{0}\"", EscapeRegex2.Replace(value, "\\\\$1")) | |
: value == "" ? "\"\"" | |
: EscapeRegex2.Replace(value, "\\\\$1"); | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
public class Mapper : DefaultMapper | |
{ | |
public override Func<object, object> GetFromDbConverter(Type DestType, Type SourceType) | |
{ | |
if (DestType == typeof(DateTimeOffset) || DestType == typeof(DateTimeOffset?) | |
|| DestType == typeof(DateTime) || DestType == typeof(DateTime?)) | |
{ | |
return x => | |
{ | |
if (x is NpgsqlTimeStampTZ) | |
{ | |
if (DestType == typeof(DateTime)) | |
return (DateTime)((NpgsqlTimeStampTZ)x); | |
if (DestType == typeof(DateTime?)) | |
return (DateTime?)((NpgsqlTimeStampTZ)x); | |
if (DestType == typeof(DateTimeOffset)) | |
return (DateTimeOffset)((NpgsqlTimeStampTZ)x); | |
if (DestType == typeof(DateTimeOffset?)) | |
return (DateTimeOffset?)((NpgsqlTimeStampTZ)x); | |
} | |
if (x is NpgsqlTimeStamp) | |
{ | |
if (DestType == typeof(DateTime)) | |
return (DateTime)((NpgsqlTimeStamp)x); | |
if (DestType == typeof(DateTime?)) | |
return (DateTime?)((NpgsqlTimeStamp)x); | |
} | |
if (x is NpgsqlDate) | |
{ | |
if (DestType == typeof(DateTime)) | |
return (DateTime)((NpgsqlDate)x); | |
if (DestType == typeof(DateTime?)) | |
return (DateTime?)((NpgsqlDate)x); | |
} | |
return x; | |
}; | |
} | |
if (DestType == typeof(HStore) && SourceType == typeof(string)) | |
{ | |
return x => HStore.Create((string)x); | |
} | |
return base.GetFromDbConverter(DestType, SourceType); | |
} | |
public override Func<object, object> GetToDbConverter(Type DestType, Type SourceType) | |
{ | |
if (SourceType == typeof(HStore)) | |
{ | |
return x => ((HStore)x).ToSqlString(); | |
} | |
return base.GetToDbConverter(DestType, SourceType); | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DatabaseFactory = DatabaseFactory.Config(x => { | |
x.WithMapper(new Mapper()); | |
x.UsingDatabase(() => new Database("conn")); | |
}); | |
var db = DatabaseFactory.GetDatabase(); | |
var user = db.SingleById<User>(1); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Can we include jsonb in the same way ?