Skip to content

Instantly share code, notes, and snippets.

@schotime
Created August 23, 2013 02:11
Show Gist options
  • Save schotime/6314871 to your computer and use it in GitHub Desktop.
Save schotime/6314871 to your computer and use it in GitHub Desktop.
PostgreSQL NPoco configuration with HStore and DateTimeOffset support
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");
}
}
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);
}
}
DatabaseFactory = DatabaseFactory.Config(x => {
x.WithMapper(new Mapper());
x.UsingDatabase(() => new Database("conn"));
});
var db = DatabaseFactory.GetDatabase();
var user = db.SingleById<User>(1);
@AjmalVh
Copy link

AjmalVh commented Mar 5, 2015

Can we include jsonb in the same way ?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment