/// <summary>
/// generate schema name list
/// </summary>
/// <returns></returns>
public static List <string> Get_List()
{
List <string> schemaList = new List <string>();
string[] notin =
{
"'geometry_columns'",
"'raster_columns'",
"'spatial_ref_sys'",
"'raster_overviews'",
"'us_gaz'",
"'topology'",
"'zip_lookup_all'",
"'pg_toast'",
"'pg_temp_1'",
"'pg_toast_temp_1'",
"'pg_catalog'",
"'information_schema'",
"'tiger'",
"'tiger_data'"
};
string sql = $@"SELECT schema_name FROM information_schema.schemata WHERE SCHEMA_NAME NOT IN({string.Join(",", notin)}) ORDER BY SCHEMA_NAME; ";
PgSqlHelper.ExecuteDataReader(dr =>
{
schemaList.Add(dr[0].ToString());
}, CommandType.Text, sql);
return(schemaList);
}
protected void Get_Constraint()
{
string _sqltext = string.Format(@"
SELECT(select attname from pg_attribute where attrelid = a.conrelid and attnum = any(a.conkey)) as conname
,b.relname,c.nspname,d.attname as ref_column,e.typname
FROM pg_constraint a
left JOIN pg_class b on b.oid= a.confrelid
inner join pg_namespace c on b.relnamespace = c.oid
INNER JOIN pg_attribute d on d.attrelid =a.confrelid and d.attnum=any(a.confkey)
inner join pg_type e on e.oid = d.atttypid
WHERE conrelid in
(
SELECT a.oid FROM pg_class a
inner join pg_namespace b on a.relnamespace=b.oid
WHERE b.nspname='{0}' and a.relname='{1}');"
, this.schemaName, this.table.name);
PgSqlHelper.ExecuteDataReader(dr =>
{
string conname = dr["conname"].ToString();
string contype = dr["typname"].ToString();
string ref_column = dr["ref_column"].ToString();
string relname = dr["relname"].ToString();
string nspname = dr["nspname"].ToString();
consList.Add(new ConstraintInfo()
{
conname = conname,
contype = contype,
ref_column = ref_column,
table_name = relname,
nspname = nspname
});
}, CommandType.Text, _sqltext);
}
public List <SchemaViewModel> List()
{
string[] filters = new string[this.Filters.Count];
for (int i = 0; i < Filters.Count; i++)
{
filters[i] = $"'{Filters[i]}'";
}
List <SchemaViewModel> schemas = new List <SchemaViewModel>();
string sql = $@"SELECT schema_name FROM information_schema.schemata WHERE SCHEMA_NAME NOT IN({string.Join(",", filters)}) ORDER BY SCHEMA_NAME; ";
PgSqlHelper.ExecuteDataReader(dr =>
{
var schema = new SchemaViewModel {
Name = dr[0].ToString()
};
schemas.Add(schema);
}, CommandType.Text, sql);
foreach (var item in schemas)
{
Console.WriteLine("正在生成模式：{0}", item.Name);
item.Tables = GetTables(item.Name);
}
return(schemas);
}
public static void Generate(string rootpath, string modelpath, string projName)
{
rootPath = rootpath;
modelPath = modelpath;
projectName = projName;
string _sqltext = @"
select a.oid,a.typname,b.nspname from pg_type a
INNER JOIN pg_namespace b on a.typnamespace = b.oid
where a.typtype = 'e' order by oid asc";
List <EnumTypeInfo> list = new List <EnumTypeInfo>();
PgSqlHelper.ExecuteDataReader(dr =>
{
list.Add(new EnumTypeInfo()
{
Oid = Convert.ToInt32(dr["oid"]),
TypeName = dr["typname"].ToString(),
NspName = dr["nspname"].ToString()
});
}, System.Data.CommandType.Text, _sqltext);
string _fileName = Path.Combine(modelpath, "_Enums.cs");
using (StreamWriter writer = new StreamWriter(File.Create(_fileName), System.Text.Encoding.UTF8))
{
writer.WriteLine("using System;");
writer.WriteLine();
writer.WriteLine($"namespace {projectName}.Model");
writer.WriteLine("{");
for (int i = 0; i < list.Count; i++)
{
var item = list[i];
writer.WriteLine($"\tpublic enum {item.TypeName.ToUpperPascal()}");
writer.WriteLine("\t{");
string sql = $"select oid,enumlabel from pg_enum WHERE enumtypid = {item.Oid} ORDER BY oid asc";
PgSqlHelper.ExecuteDataReader(dr =>
{
string c = i < list.Count ? "," : "";
writer.WriteLine($"\t\t{dr["enumlabel"]}{c}");
}, System.Data.CommandType.Text, sql);
writer.WriteLine("\t}");
}
writer.WriteLine("}");
}
GenerateMapping(list);
}
protected void Get_Primarykey(int oid)
{
string _sqltext = $@"SELECT b.attname, format_type(b.atttypid, b.atttypmod) AS data_type
FROM pg_index a
INNER JOIN pg_attribute b ON b.attrelid = a.indrelid AND b.attnum = ANY(a.indkey)
WHERE a.indrelid = '{schemaName}.{table.name}'::regclass AND a.indisprimary;
";
PgSqlHelper.ExecuteDataReader(dr =>
{
PrimarykeyInfo pk = new PrimarykeyInfo();
pk.Field = dr["attname"].ToString();
pk.Typname = dr["data_type"].ToString();
pkList.Add(pk);
}, CommandType.Text, _sqltext);
}
private static List <TableViewModel> GetTables(string schema)
{
string _sqltext = $@"SELECT table_name,'table' as type FROM INFORMATION_SCHEMA.tables WHERE table_schema='{schema}' AND table_type='BASE TABLE'
UNION ALL
SELECT table_name,'view' as type FROM INFORMATION_SCHEMA.views WHERE table_schema = '{schema}'";
List <TableViewModel> tableList = new List <TableViewModel>();
PgSqlHelper.ExecuteDataReader(dr =>
{
TableViewModel model = new TableViewModel()
{
name = dr["table_name"].ToString(), type = dr["type"].ToString()
};
tableList.Add(model);
}, CommandType.Text, _sqltext);
return(tableList);
}
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);
}
