public static string GetInnerSqlDefinition(this TableDefinition tblDefinition)
{
var strBuilder = new StringBuilder();
tblDefinition.Columns().ToList().ForEach(c =>
{
var typ = c.PgType ?? PgSqlTypeManager.GetPostgresName(c.DotNetType);
var str = $"\"{c.GetNameForDb()}\" {typ}".Trim();
if (!String.IsNullOrWhiteSpace(c.DefaultValue))
{
if (c.DefaultValue.ToLower() == "bigserial")
{
str = $"{str.TrimEnd(typ.ToCharArray())} BIGSERIAL";
}
else
{
str = $"{str} DEFAULT {c.DefaultValue}".Trim();
}
}
if (c.MustBeUnique)
{
str = $"{str} UNIQUE".Trim();
}
if (!c.CanBeNull || c.IsPrimaryKey)
{
str = $"{str} NOT NULL".Trim();
}
strBuilder.AppendLine($" {str},");
});
if (tblDefinition.PrimaryKeys() != null)
{
var keys = String.Join(", ", tblDefinition.PrimaryKeys().Select(p => $"\"{p.GetNameForDb()}\""));
strBuilder.AppendLine($" PRIMARY KEY ({keys})");
}
return(strBuilder.ToString());
}
public static ColumnBuilder Build(string name, string typeName)
{
Type type = null;
string tName = typeName;
bool isArray = false;
if (tName.EndsWith("[]"))
{
isArray = true;
tName = tName.TrimEnd("[]".ToCharArray());
}
type = Type.GetType(tName, false, true);
if (type == null)
{
type = Type.GetType($"System.{tName}", false, true);
}
if (type == null)
{
type = PgSqlTypeManager.GetDotNetType(tName);
}
if (type == null)
{
throw new Exception($"Can't find Type '{typeName}'");
}
if (isArray)
{
type = typeof(List <>).MakeGenericType(type);
}
var col = Build(name, type);
return(col);
}
internal static TableDefinition<T> FromTable<T>(ITable table)
{
var td = TableDefinition.FromType<T>();
try
{
var columns = new DbExecuter(table.GetConnectionString()).ExecuteReader(SQLStatements.GetColumns(table.GetConnectionString().TableName, table.GetConnectionString().SchemaName)).ToArray();
foreach (var column in columns)
{
Column dbCol = column.ToObject<Column>();
var col = td.GetColumnByDbName(dbCol.DbName) ?? td.GetColumnByClrName(dbCol.DbName);
if (col == null)
{
dbCol.DotNetType = PgSqlTypeManager.GetDotNetType(dbCol.PgType);
td.AddColumn(dbCol);
}
else
{
col.DbName = dbCol.DbName;
col.CanBeNull = dbCol.CanBeNull;
col.DefaultValue = dbCol.DefaultValue;
col.Position = dbCol.Position;
col.IsPrimaryKey = dbCol.IsPrimaryKey;
col.MustBeUnique = dbCol.MustBeUnique;
}
}
}
catch (Exception e)
{
Logger.Error(e, "GetTableSchema");
throw;
}
return td;
}
public AddColumnAction(Column column)
{
Name = column.DbName;
DataType = PgSqlTypeManager.GetPostgresName(column.DotNetType);
}
internal NpgsqlDbType GetNpgSqlDbType() => (_npgsqlDbType ?? (_npgsqlDbType = !String.IsNullOrWhiteSpace(PgType) ? PgSqlTypeManager.GetNpgsqlDbType(PgType) : PgSqlTypeManager.GetNpgsqlDbType(DotNetType))).Value;
public static JToken ConvertFromDB(object data, NpgsqlDbColumn column)
{
if (data == null)
{
return(null);
}
if (data == DBNull.Value)
{
return(null);
}
var postgresType = column.DataTypeName;
if (column.DataType == typeof(Array))
{
postgresType = $"{postgresType}[]";
}
var type = PgSqlTypeManager.GetDotNetType(postgresType);
var npgsqlType = PgSqlTypeManager.GetNpgsqlDbType(postgresType);
JToken newObject = null;
switch (npgsqlType)
{
case NpgsqlDbType.Json:
case NpgsqlDbType.Jsonb:
{
if (type.IsListType())
{
var list = new JArray();
var enumerable = data as IEnumerable;
foreach (var o in enumerable)
{
var jt = JToken.Parse(o.ToString());
//var jo = jt.ToBasicDotNetObject();
list.Add(jt);
}
newObject = list;
}
else
{
newObject = JToken.Parse(data.ToString());
}
break;
}
case NpgsqlDbType.Json | NpgsqlDbType.Array:
case NpgsqlDbType.Jsonb | NpgsqlDbType.Array:
{
var list = new JArray();
var enumerable = data as IEnumerable;
foreach (var o in enumerable)
{
var jt = JToken.Parse(o.ToString());
list.Add(jt);
}
newObject = list;
break;
}
default:
newObject = JToken.FromObject(data);
break;
}
return(newObject);
}
public static NpgsqlParameter ToNpgsqlParameter(this PgSqlParameter param)
{
try
{
if (param.Value == null)
{
return(new NpgsqlParameter(param.UniqueId, DBNull.Value));
}
//if (Value.GetType().GetTypeInfo().IsEnum)
// Value = Json.ToJson(Value);
NpgsqlDbType type = NpgsqlDbType.Text;
bool findType = true;
if (param.Column?.DotNetType?.IsEnum == true)
{
switch (param.Value)
{
case int i:
{
var val = (Enum)Enum.ToObject(param.Column.DotNetType, i);
return(new NpgsqlParameter(param.UniqueId, type)
{
Value = val.GetName()
});
}
}
}
//var typeInfo = param.Value.GetType().GetTypeInfo();
//if (typeInfo.IsEnum)
//{
// type = NpgsqlDbType.Text;
// findType = false;
//}
if (findType)
{
if (!Enum.TryParse(param.OverrideType, true, out type))
{
type = param.Column.GetNpgSqlDbType();// PgSqlTypeManager.GetNpgsqlDbType(param.Column.DotNetType);
}
}
if (type == NpgsqlDbType.Json || type == NpgsqlDbType.Jsonb)
{
var valueType = PgSqlTypeManager.GetNpgsqlDbType(param.Value.GetType());
if (valueType != NpgsqlDbType.Json && valueType != NpgsqlDbType.Jsonb)
{
type = NpgsqlDbType.Text; //PgSqlTypeManager.GetNpgsqlDbType(param.Value.GetType());
}
}
object _value = null;
switch (type)
{
case NpgsqlDbType.Enum:
{
//TODO: Error: When specifying NpgsqlDbType.Enum, EnumType must be specified as well
type = NpgsqlDbType.Text;
_value = JSON.ToJson(param.Value);
break;
}
case NpgsqlDbType.Jsonb:
{
_value = JSON.ToJson(param.Value);
break;
}
case NpgsqlDbType.Array | NpgsqlDbType.Jsonb:
{
var objAr = new List <object>();
if (param.Value is string)
{
objAr.Add(param.Value.ToString());
}
else
{
var arr = param.Value as IEnumerable;
if (arr != null)
{
foreach (var o in arr)
{
objAr.Add(JSON.ToJson(o));
}
}
}
_value = objAr;
break;
}
case NpgsqlDbType.Array | NpgsqlDbType.Uuid:
{
var json = JSON.ToJson(param.Value);
_value = JSON.ToObject <List <Guid> >(json);
break;
}
case NpgsqlDbType.Uuid:
{
_value = new Guid(param.Value.ToString());
break;
}
default:
{
_value = param.Value;
break;
}
}
return(new NpgsqlParameter(param.UniqueId, type)
{
Value = _value
});
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
