private void Get_Fields()
{
string _sqltext = @"SELECT a.oid
,c.attnum as num
,c.attname as field
, (case when f.character_maximum_length is null then c.attlen else f.character_maximum_length end) as length
,c.attnotnull as notnull
,d.description as comment
,(case when e.typcategory ='G' then e.typname when e.typelem = 0 then e.typname else e2.typname end) as type
,(case when e.typelem = 0 then e.typtype else e2.typtype end) as data_type
,e.typcategory
,f.is_identity
from pg_class a
inner join pg_namespace b on a.relnamespace=b.oid
inner join pg_attribute c on attrelid = a.oid
LEFT OUTER JOIN pg_description d ON c.attrelid = d.objoid AND c.attnum = d.objsubid and c.attnum > 0
inner join pg_type e on e.oid=c.atttypid
left join pg_type e2 on e2.oid=e.typelem
inner join information_schema.columns f on f.table_schema = b.nspname and f.table_name=a.relname and column_name = c.attname
WHERE b.nspname='{0}' and a.relname='{1}';";
_sqltext = string.Format(_sqltext, this.schemaName, this.table.name);
PgSqlHelper.ExecuteDataReader(dr =>
{
FieldInfo fi = new FieldInfo();
fi.Oid = Convert.ToInt32(dr["oid"]);
fi.Field = dr["field"].ToString();
fi.Length = Convert.ToInt32(dr["length"].ToString());
fi.Is_not_null = Convert.ToBoolean(dr["notnull"]);
fi.Comment = dr["comment"].ToString();
fi.Data_Type = dr["data_type"].ToString();
fi.Db_type = dr["type"].ToString();
fi.Db_type = fi.Db_type.StartsWith("_") ? fi.Db_type.Remove(0, 1) : fi.Db_type;
fi.PgDbType = PgsqlType.SwitchToSql(fi.Data_Type, fi.Db_type);
fi.Is_identity = dr["is_identity"].ToString() == "YES";
fi.Is_array = dr["typcategory"].ToString() == "A";
fi.Is_enum = fi.Data_Type == "e";
fi.CsType = PgsqlType.SwitchToCSharp(fi.Db_type);
if (fi.Is_enum)
{
fi.CsType = fi.CsType.ToUpperPascal();
}
string _notnull = "";
if (
fi.CsType != "string" &&
fi.CsType != "byte[]" &&
fi.CsType != "JToken" &&
!fi.Is_array &&
fi.CsType != "System.Net.IPAddress" &&
fi.CsType != "System.Net.NetworkInformation.PhysicalAddress" &&
fi.CsType != "System.Xml.Linq.XDocument" &&
fi.CsType != "System.Collections.BitArray" &&
fi.CsType != "object"
)
{
_notnull = fi.Is_not_null ? "" : "?";
}
string _array = fi.Is_array ? "[]" : "";
fi.RelType = $"{fi.CsType}{_notnull}{_array}";
// dal
this.fieldList.Add(fi);
}, CommandType.Text, _sqltext);
}
private void GetFields(TableInfo table)
{
string _sqltext = @"SELECT a.oid
,c.attnum as num
,c.attname as field
,c.attnotnull as notnull
,d.description as comment
,(case when e.typcategory ='G' then e.typname when e.typelem = 0 then e.typname else e2.typname end) as type
,(case when e.typelem = 0 then e.typtype else e2.typtype end) as data_type
,COALESCE((
case
when (case when e.typcategory ='G' then e.typname when e.typelem = 0 then e.typname else e2.typname end) in ('numeric','int2','int4','int8','float4','float8') then f.numeric_precision
when (case when e.typcategory ='G' then e.typname when e.typelem = 0 then e.typname else e2.typname end) in ('timestamp','timestamptz','interval','time','date','timetz') then f.datetime_precision
when f.character_maximum_length is null then 0
else f.character_maximum_length
end
),0) as length
,COALESCE((
case
when (case when e.typcategory ='G' then e.typname when e.typelem = 0 then e.typname else e2.typname end) in ('numeric') then f.numeric_scale
else 0
end
),0) numeric_scale
,e.typcategory
,f.udt_schema
,f.column_default
from pg_class a
inner join pg_namespace b on a.relnamespace=b.oid
inner join pg_attribute c on attrelid = a.oid
LEFT OUTER JOIN pg_description d ON c.attrelid = d.objoid AND c.attnum = d.objsubid and c.attnum > 0
inner join pg_type e on e.oid=c.atttypid
left join pg_type e2 on e2.oid=e.typelem
inner join information_schema.columns f on f.table_schema = b.nspname and f.table_name=a.relname and column_name = c.attname
WHERE b.nspname='{0}' and a.relname='{1}';";
_sqltext = string.Format(_sqltext, table.Schema, table.Name);
dbContext.Execute.ExecuteDataReader(dr =>
{
DbFieldInfo fi = new DbFieldInfo
{
Oid = Convert.ToInt32(dr["oid"]),
Name = dr["field"].ToString(),
Length = Convert.ToInt32(dr["length"].ToString()),
NotNull = Convert.ToBoolean(dr["notnull"]),
Comment = dr["comment"].ToString(),
Numeric_scale = Convert.ToInt32(dr["numeric_scale"].ToString()),
ColumnDefault = dr["column_default"].ToString(),
};
var udt_schema = dr["udt_schema"].ToString();
var typcategory = dr["typcategory"].ToString();
var dbtype = dr["type"].ToString();
fi.DbType = typcategory == "E" ? udt_schema + "." + dbtype : dbtype;
fi.IsArray = typcategory == "A";
fi.CsType = PgsqlType.SwitchToCSharp(dbtype);
fi.AutoIncrement = fi.ColumnDefault != null && fi.ColumnDefault.StartsWith("nextval('") && fi.ColumnDefault.EndsWith("'::regclass)");
string _notnull = "";
if (
fi.CsType != "string" &&
fi.CsType != "byte[]" &&
fi.CsType != "JToken" &&
!fi.IsArray &&
fi.CsType != "System.Net.IPAddress" &&
fi.CsType != "System.Net.NetworkInformation.PhysicalAddress" &&
fi.CsType != "System.Xml.Linq.XDocument" &&
fi.CsType != "System.Collections.BitArray" &&
fi.CsType != "object"
)
{
_notnull = fi.NotNull ? "" : "?";
}
string _array = fi.IsArray ? "[]" : "";
fi.RelType = $"{fi.CsType}{_notnull}{_array}";
if (fi.RelType == "string" && (fi.Length != 0 && fi.Length != 255))
{
fi.DbTypeFull = $"{fi.DbType}({fi.Length})";
}
else if (fi.Numeric_scale > 0)
{
fi.DbTypeFull = ($"{fi.DbType}({fi.Length},{fi.Numeric_scale})");
}
else if (PgsqlType.ContrastType(fi.DbType) == null)
{
fi.DbTypeFull = fi.DbType;
}
table.Fields.Add(fi);
}, CommandType.Text, _sqltext);
if (table.Type == TableType.Table)
{
GetPrimarykey(table);
}
}
