/// <summary>
/// 静态构造器
/// </summary>
static PgSqlSeedProvider()
{
string connectionString = null;
#region # 验证
if (!string.IsNullOrWhiteSpace(SerialNumberSection.Setting.ConnectionString.Name))
{
connectionString = ConfigurationManager.ConnectionStrings[SerialNumberSection.Setting.ConnectionString.Name]?.ConnectionString;
}
if (string.IsNullOrWhiteSpace(connectionString))
{
connectionString = SerialNumberSection.Setting.ConnectionString.Value;
}
if (string.IsNullOrWhiteSpace(connectionString))
{
throw new NullReferenceException("序列号生成器连接字符串未配置！");
}
#endregion
//初始化SQL工具
_SqlHelper = new PgSqlHelper(connectionString);
//初始化数据表
InitTable();
}
/// <summary>
/// 静态构造器
/// </summary>
static PgSqlLogger()
{
string connectionString = null;
#region # 验证
if (!string.IsNullOrWhiteSpace(AopSection.Setting.ConnectionString.Name))
{
connectionString = ConfigurationManager.ConnectionStrings[AopSection.Setting.ConnectionString.Name]?.ConnectionString;
}
if (string.IsNullOrWhiteSpace(connectionString))
{
connectionString = AopSection.Setting.ConnectionString.Value;
}
if (string.IsNullOrWhiteSpace(connectionString))
{
throw new NullReferenceException("连接字符串未配置！");
}
#endregion
//初始化SQL工具
_SqlHelper = new PgSqlHelper(connectionString);
//初始化日志数据表
InitTable();
}
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);
}
/// <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);
}
static void Main(string[] args)
{
if (args == null || args.Length == 0)
{
Console.WriteLine("try --help");
return;
}
if (args[0] == "--help")
{
Console.WriteLine("Use the following parameters to create a project using Mystaging.App,The parameter name ignore case");
Console.WriteLine("-h [host/ip] required");
Console.WriteLine("-p [port] required");
Console.WriteLine("-u [postgresql database access username] required");
Console.WriteLine("-a [postgresql database auth password] required");
Console.WriteLine("-d [postgresql database] required");
Console.WriteLine("-pool [maxinum pool size numbric,default 32] optional");
Console.WriteLine("-proj [the project build name] required");
Console.WriteLine("-0 [the project output path] required");
return;
}
Console.OutputEncoding = System.Text.Encoding.UTF8;
string projName = string.Empty, outPutPath = string.Empty;
StringBuilder connection = new StringBuilder();
for (int i = 0; i < args.Length; i++)
{
var item = args[i].ToLower();
switch (item)
{
case "-h": connection.Append($"host={args[i + 1]};"); break;
case "-p": connection.Append($"port={args[i + 1]};"); break;
case "-u": connection.Append($"username={args[i + 1]};"); break;
case "-a": connection.Append($"password={args[i + 1]};"); break;
case "-d": connection.Append($"database={args[i + 1]};"); break;
case "-pool": connection.Append($"maximum pool size={args[i + 1]};"); break;
case "-proj": projName = args[i + 1]; break;
case "-o": outPutPath = args[i + 1]; break;
}
i++;
}
//outPutPath = @"E:\my\";
//projName = "Crmmt";
//PgSqlHelper.InitConnection(null, "Host=127.0.0.1;Port=5432;Username=postgres;Password=postgres;Database=crmmt;Pooling=true;Maximum Pool Size=100");
PgSqlHelper.InitConnection(null, connection.ToString());
GeneralFactory.Build(outPutPath, projName);
Console.WriteLine("success.....");
}
public void ToPipe()
{
var user = User.Context.OrderBy(f => f.Createtime).Where(f => f.Age == 18).ToPipe();
var user2 = User.Context.OrderBy(f => f.Createtime).Where(f => f.Age == 38).ToPipe();
var articles = Article.Context.ToPipe();
var result = PgSqlHelper.ExecutePipeLine(true, user, user2, articles);
Assert.Equal("5d6e36bc953f702910000007", result[2][0].Userid);
}
public static void Init(StagingOptions options)
{
PgSqlHelper.InitConnection(options);
Type[] jsonTypes = { typeof(JToken), typeof(JObject), typeof(JArray) };
NpgsqlNameTranslator translator = new NpgsqlNameTranslator();
NpgsqlConnection.GlobalTypeMapper.UseJsonNet(jsonTypes);
NpgsqlConnection.GlobalTypeMapper.MapEnum <Et_data_state>("public.et_data_state", translator);
NpgsqlConnection.GlobalTypeMapper.MapEnum <Et_role>("public.et_role", translator);
}
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);
}
static void Main(string[] args)
{
if (args == null || args.Length == 0)
{
Console.WriteLine("try --help");
return;
}
if (args[0] == "--help")
{
Console.WriteLine("Use the following parameters to create a project using Mystaging.App,The parameter name ignore case");
Console.WriteLine("-h [host/ip] required");
Console.WriteLine("-p [port] required");
Console.WriteLine("-u [postgresql database access username] required");
Console.WriteLine("-a [postgresql database auth password] required");
Console.WriteLine("-d [postgresql database] required");
Console.WriteLine("-pool [maxinum pool size numbric,default 32] optional");
Console.WriteLine("-proj [the project build name] required");
Console.WriteLine("-0 [the project output path] required");
return;
}
string projName = string.Empty, outPutPath = string.Empty;
StringBuilder connection = new StringBuilder();
for (int i = 0; i < args.Length; i++)
{
var item = args[i].ToLower();
switch (item)
{
case "-h": connection.Append($"host={args[i + 1]};"); break;
case "-p": connection.Append($"port={args[i + 1]};"); break;
case "-u": connection.Append($"username={args[i + 1]};"); break;
case "-a": connection.Append($"password={args[i + 1]};"); break;
case "-d": connection.Append($"database={args[i + 1]};"); break;
case "-pool": connection.Append($"maximum pool size={args[i + 1]};"); break;
case "-proj": projName = args[i + 1]; break;
case "-o": outPutPath = args[i + 1]; break;
}
i++;
}
//var outPutPath = @"D:\MyGitHub\mystaging";
//var projName = "MyStaging.SuperApp";
//MyStaging.Common.StagingOptions options = new Common.StagingOptions()
//{
// ConnectionMaster = "Host=127.0.0.1;Port=5432;Username=postgres;Password=123456;Database=mystaging;Pooling=true;Maximum Pool Size=10;"
//};
//PgSqlHelper.InitConnection(options);
var options = new Common.StagingOptions()
{
ConnectionMaster = connection.ToString()
};
PgSqlHelper.InitConnection(options);
GeneralFactory factory = new GeneralFactory(outPutPath, projName);
factory.Build();
Console.WriteLine("success.....");
}
public static void Init(ILogger logger, string connectionMaster, string[] connectionSlaves = null, int slavesMaxPool = -1)
{
PgSqlHelper.InitConnection(logger, connectionMaster, connectionSlaves, slavesMaxPool);
}
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);
}
/// <summary>
/// 静态构造器
/// </summary>
static ExceptionLogRepository()
{
//初始化SQL工具
_SqlHelper = new PgSqlHelper(GlobalSetting.ReadConnectionString);
}
public static void Init(StagingOptions options)
{
PgSqlHelper.InitConnection(options);
}
public static void Init(ILogger logger, string connectionString)
{
PgSqlHelper.InitConnection(logger, connectionString);
}
