private PostgresDatabaseFactory()
{
const string connectionString = "Server=localhost;Port=5432;" +
"User Id=postgres;Password=postgres;Database=test;Timeout=300";
_database = new PostgresDatabase(connectionString);
}
private async Task GetLine(string inModel)
{
PostgresDatabase database = new PostgresDatabase();
string sSQL = "select distinct line from processtbl where model = @model order by line asc";
var modelpara = new NpgsqlParameter("@model", inModel);
await database.ExecuteReaderAsync(SQLConnectString, sSQL, modelpara)
.ContinueWith(t => this.Invoke((Action)(() => { LineResult(t.Result); })));
}
private async Task GetModel()
{
PostgresDatabase database = new PostgresDatabase();
List <processtbl> listModel = new List <processtbl>();
string sSQL = "select distinct model from processtbl order by model asc";
await database.ExecuteReaderAsync(SQLConnectString, sSQL)
.ContinueWith(t => this.Invoke((Action)(() => { ModelResult(t.Result); })));
}
static void Main(string[] args)
{
IDatabase postgresDatabase = new PostgresDatabase();
IInteractor interactor = new Interactor(postgresDatabase);
IController controller = new Controller(interactor);
controller.ShowMainMenu();
}
public static void CreatePostgresDatabase()
{
bool created = PostgresDatabase.TryCreate("chumsql2", "daoref", new PostgresCredentials {
UserId = "postgres", Password = "******"
});
Expect.IsTrue(created);
}
public static void OpenPostgresConnection()
{
PostgresDatabase postgresDatabase = new PostgresDatabase("chumsql2", "daoref", new PostgresCredentials {
UserId = "postgres", Password = "******"
});
postgresDatabase.GetOpenDbConnection();
}
private async Task GetInspect(string inModel, string inProcess)
{
PostgresDatabase database = new PostgresDatabase();
string sSQL = "select distinct inspect from procinsplink where model = @model and process = @process order by inspect asc";
var modelpara = new NpgsqlParameter("@model", inModel);
var processpara = new NpgsqlParameter("@process", inProcess);
await database.ExecuteReaderAsyncIns(SQLConnectString, sSQL, modelpara, processpara)
.ContinueWith(t => this.Invoke((Action)(() => { InspectResult(t.Result); })));
}
public void Start_And_Stop_PostgreSQL_latest()
{
var db = new PostgresDatabase(
databaseName: "PostgreSQL_latest",
postgresUser: "******",
password: "******");
var result = db.StartDatabase();
_output.WriteLine(result);
db.StopDatabase();
}
public void Start_And_Stop_PostgreSQL_alpine()
{
var db = new PostgresDatabase(
"PostgreSQL_alpine",
"Db_user",
"Password1",
tagName: "alpine");
var result = db.StartDatabase();
_output.WriteLine(result);
db.StopDatabase();
}
public void Start_And_Delete_NonOfficialPostgreSQL_latest()
{
var db = new PostgresDatabase(
databaseName: "Bitnami_PostgreSQL_latest",
postgresUser: "******",
password: "******",
imageName: "bitnami/postgresql");
var result = db.StartDatabase();
_output.WriteLine(result);
db.DeleteDatabase();
}
//-------------------------------------------------------------------------
// Entry point: Create a gateway connection, return 1 if ok else set up message
int Connect(string options)
{
//pg_elog(ElogLevel.NOTICE, $"Connect: '{options}'");
Logger.Open(0);
try {
var opts = Regex.Split(options, @"\s*,\s*");
foreach (var opt in opts)
{
if (opt == "Debug")
{
Debugger.Launch();
}
else
{
var o = Regex.Split(opt, @"\s*\=\s*");
if (o.Length == 2)
{
_settings[o[0]] = o[1];
}
else
{
Logger.WriteLine(1, $"Invalid option ignored: {opt}");
}
}
}
var gateway = GatewayFactory.Create(_databasename, _settings);
// send tracing as NOTICE so it won't get lost on a crash
if (Logger.Level >= 2)
{
Logger.Open(5, new ElogWriter());
}
Logger.WriteLine(1, $">Connect: {_databasename}, {options}");
// this is the function call interface
_database = SqlTarget.Current.Database as PostgresDatabase; // must be set up by now
// this is to install any initial functions
var pgconn = PostgresConnect.Create(gateway, _database);
Logger.Assert(pgconn != null, "pg function");
_pgfunction = pgconn;
SqlTarget.Current.FunctionCreator = pgconn;
return(1);
} catch (Exception ex) {
_error_message = ex.ToString();
return(0);
}
}
public static IDatabase GetDatabaseFor(DatabaseType dbType, string connectionString)
{
IDatabaseDefinition dbDefinition;
switch (dbType)
{
case DatabaseType.Sqlite:
dbDefinition = new SqliteDatabase();
break;
case DatabaseType.Oracle:
dbDefinition = new OracleDatabase();
break;
case DatabaseType.Postgres:
dbDefinition = new PostgresDatabase();
break;
case DatabaseType.MariaDb:
case DatabaseType.MySql:
dbDefinition = new MySqlDatabase();
break;
default:
dbDefinition = new SqlServerDatabase();
break;
}
var db = new Database.DataCoreDatabase(dbDefinition, connectionString);
db.DropTableIfExists <TestClass>();
db.DropTableIfExists <TestClass2>();
db.DropTableIfExists <TestClass3>();
db.DropTableIfExists <TestClass4>();
db.DropTableIfExists <TestIgnore>();
db.DropTableIfExists <TestOverride>();
db.DropTableIfExists <TestClassRef2>();
db.DropTableIfExists <TestClassRef1>();
db.DropTableIfExists <TestClassNoReference>();
db.DropTableIfExists <TestClassOnlyIdentity>();
db.DropTableIfExists <TestNullableProperty>();
return(db);
}
public static HashSet <Database> Setup(Action <Database> initializer = null, string databaseName = "DaoRef")
{
if (initializer == null)
{
initializer = db => Db.TryEnsureSchema <TestTable>(db);
}
/*MsSqlDatabase msDatabase = new MsSqlDatabase("Chumsql2", databaseName, new MsSqlCredentials { UserId = "mssqluser", Password = "******" });
* initializer(msDatabase);
* _testDatabases.Add(msDatabase);*/
NpgsqlDatabase npgsqlDatabase = new NpgsqlDatabase("chumsql2", databaseName, new NpgsqlCredentials {
UserId = "postgres", Password = "******"
});
initializer(npgsqlDatabase);
_testDatabases.Add(npgsqlDatabase);
PostgresDatabase postgresDatabase = new PostgresDatabase("chumsql2", databaseName, new PostgresCredentials {
UserId = "postgres", Password = "******"
});
initializer(postgresDatabase);
_testDatabases.Add(postgresDatabase);
/*SQLiteDatabase sqliteDatabase = new SQLiteDatabase("./chumsql2", databaseName);
* initializer(sqliteDatabase);
* _testDatabases.Add(sqliteDatabase);
*
* OracleDatabase oracleDatabase = new OracleDatabase("chumsql2", databaseName, new OracleCredentials { UserId = "C##ORACLEUSER", Password = "******" });
* initializer(oracleDatabase);
* _testDatabases.Add(oracleDatabase);*/
/*MySqlDatabase mySqlDatabase = new MySqlDatabase("chumsql2", databaseName, new MySqlCredentials { UserId = "mysql", Password = "******" }, false);
* initializer(mySqlDatabase);
* _testDatabases.Add(mySqlDatabase);*/
return(_testDatabases);
}
public async Task TestDynamic()
{
var database = new PostgresDatabase(connectionString);
await DynamicUnitTest.TestDatabase(database, Assembly.GetExecutingAssembly(), dbSql);
}
private async Task GetDataTable()
{
try
{
PostgresDatabase database = new PostgresDatabase();
string table = DefineTableName();
string sSQL = "SELECT a.serno, a.lot, a.model, a.site, a.factory, a.line, a.process, a.inspectdate, a.tjudge ";
sSQL += "FROM " + table + " a WHERE 1=1 ";
//sSQL += "FROM " + table + " a LEFT JOIN " + table + "data b on a.serno = b.serno and a.inspectdate = b.inspectdate WHERE 1=1 ";
if (cbLine.Checked)
{
sSQL += "and line ='" + cmbLine.Text + "' ";
}
if (cbProcess.Checked)
{
sSQL += "and process ='" + cmbProcess.Text + "' ";
}
if (!string.IsNullOrEmpty(txtbarcode.Text))
{
string serno = SerialString();
if (rbLot.Checked)
{
sSQL += "and lot in (" + serno + ") ";
}
if (rbSerial.Checked)
{
sSQL += "and serno in (" + serno + ") ";
}
}
if (cbCheckDate.Checked)
{
sSQL += "and inspectdate >= '" + dtpFromDate.Value.ToString("yyyy-MM-dd HH:mm:ss");
sSQL += "' and inspectdate <= '" + dtpToDate.Value.ToString("yyyy-MM-dd HH:mm:ss") + "' ";
}
sSQL += "ORDER BY inspectdate ASC, serno ASC";
DataSet results1 = await database.GetDatasetAsync(SQLConnectString, sSQL);
dt1 = results1.Tables[0];
if (rbData.Checked)
{
sSQL = "SELECT b.serno, b.inspectdate, b.inspect, b.inspectdata ";
}
if (rbJudge.Checked)
{
sSQL = "SELECT b.serno, b.inspectdate, b.inspect, b.judge ";
}
sSQL += "FROM " + table + "data b WHERE 1=1 ";
if (!string.IsNullOrEmpty(txtbarcode.Text))
{
string serno = SerialString();
if (rbLot.Checked)
{
sSQL += "and lot in (" + serno + ") ";
}
if (rbSerial.Checked)
{
sSQL += "and serno in (" + serno + ") ";
}
}
if (cbInspect.Checked)
{
string inspect = InspectString();
sSQL += "and inspect in (" + inspect + ") ";
}
if (cbCheckDate.Checked)
{
sSQL += "and inspectdate >= '" + dtpFromDate.Value.ToString("yyyy-MM-dd HH:mm:ss");
sSQL += "' and inspectdate <= '" + dtpToDate.Value.ToString("yyyy-MM-dd HH:mm:ss") + "' ";
}
sSQL += "ORDER BY inspectdate ASC, serno ASC";
DataSet results2 = await database.GetDatasetAsync(SQLConnectString, sSQL);
dt2 = results2.Tables[0];
DataTable pivot = new DataTable();
pivot = LinQ_Class.Pivot(dt2, dt2.Columns["inspect"], dt2.Columns[3]);
PQMDataTable = LinQ_Class.Joined(dt1, pivot);
MessageBox.Show("Completed!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
// Set up the authentication
services.AddAuthentication(JwtBearerDefaults.AuthenticationScheme).AddJwtBearer(opts => {
opts.TokenValidationParameters = new TokenValidationParameters()
{
ValidateLifetime = true,
ValidateIssuerSigningKey = true,
ValidateIssuer = false,
ValidateAudience = false,
RequireSignedTokens = true,
ValidIssuer = Configuration["AuthenticationToken:Issuer"],
IssuerSigningKey = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(Configuration["AuthenticationToken:Secret"]))
};
opts.Events = new JwtBearerEvents()
{
OnTokenValidated = async(c) => {
// Figure out the user ID the token belongs to.
Claim subjectClaim = c.Principal.Claims.First(claim => claim.Type == ClaimTypes.NameIdentifier);
int userId = Convert.ToInt32(subjectClaim.Value);
// Retrieve the user, and cache the identity.
IDatabase db = c.HttpContext.RequestServices.GetService <IDatabase>();
using (var connection = db.GetConnection()) {
IUserRepo userRepo = db.GetRepo <IUserRepo>(connection);
IIdentity identity = c.Principal.Identity;
User?u = await userRepo.FindById(userId);
if (u != null)
{
u.AddIdentity(identity as ClaimsIdentity);
/*
* Don't attempt to set this via c.HttpContext.Principal, ASP.NET seems to overwrite this later on...
*/
c.Principal = u;
}
}
}
};
});
IDatabase db = new PostgresDatabase(Configuration.GetSection("Database").Get <DatabaseConfig>());
db.RegisterRepo <IUserRepo, UserRepo>();
db.RegisterRepo <IPostRepo, PostRepo>();
db.RegisterRepo <ICommentRepo, CommentRepo>();
db.RegisterRepo <ISpaceRepo, SpaceRepo>();
db.RegisterRepo <ISubscriptionRepo, SubscriptionRepo>();
db.RegisterRepo <IVoteRepo, VoteRepo>();
services.AddSingleton <IDatabase>(db);
services.ConfigurePoco <IDatabaseConfig, DatabaseConfig>(Configuration.GetSection("Database"));
services.ConfigurePoco <IAuthenticationTokenConfig, AuthenticationTokenConfig>(Configuration.GetSection("AuthenticationToken"));
services.ConfigurePoco <IAdminConfig, AdminConfig>(Configuration.GetSection("Admin"));
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
services.AddSingleton <IAuthenticationTokenHandler, JsonWebTokenHandler>();
services.AddSingleton <IPasswordHasher, BCryptPasswordHasher>();
services.AddSingleton <IUserViewMapper, UserViewMapper>();
services.AddSingleton <IUserRecordMapper, UserRecordMapper>();
services.AddTransient <UserFinderByUsername>();
services.AddTransient <UserLoginInteractor>();
services.AddTransient <UserRegistrar>();
services.AddTransient <UserUpdater>();
services.AddTransient <UserPasswordUpdater>();
services.AddTransient <AdminRegistrar>();
services.AddSingleton <IPostViewMapper, PostViewMapper>();
services.AddSingleton <PermissionHandler <Post>, PostPermissionHandler>();
services.AddSingleton <IPostRecordMapper, PostRecordMapper>();
services.AddTransient <PostCreator>();
services.AddTransient <PostFinderById>();
services.AddTransient <PostFinderBySpace>();
services.AddTransient <PostFinderByUser>();
services.AddTransient <PostFinderByNew>();
services.AddTransient <PostDeleter>();
services.AddTransient <PostUpdater>();
services.AddSingleton <PermissionHandler <Comment>, CommentPermissionHandler>();
services.AddSingleton <ICommentViewMapper, CommentViewMapper>();
services.AddSingleton <ICommentRecordMapper, CommentRecordMapper>();
services.AddTransient <CommentCreator>();
services.AddTransient <CommentFinderById>();
services.AddTransient <CommentFinderByPost>();
services.AddTransient <CommentFinderByUser>();
services.AddTransient <CommentDeleter>();
services.AddTransient <CommentUpdater>();
services.AddSingleton <PermissionHandler <Space>, SpacePermissionHandler>();
services.AddSingleton <ISpaceViewMapper, SpaceViewMapper>();
services.AddTransient <ISpaceRecordMapper, SpaceRecordMapper>();
services.AddTransient <SpaceFinderDefault>();
services.AddTransient <SpaceFinderByName>();
services.AddTransient <SpaceFinder>();
services.AddTransient <SpaceCreator>();
services.AddTransient <SpaceUpdater>();
services.AddSingleton <ISubscriptionViewMapper, SubscriptionViewMapper>();
services.AddSingleton <ISubscriptionRecordMapper, SubscriptionRecordMapper>();
services.AddTransient <SubscriptionFinderByUser>();
services.AddTransient <SubscriptionCreator>();
services.AddTransient <SubscriptionDeleter>();
services.AddSingleton <IVoteViewMapper, VoteViewMapper>();
services.AddTransient <PostVoter>();
services.AddTransient <CommentVoter>();
}
