private void CreateRoleProcedure()
{
string sql = FileHelper.ReadSqlResource("role.sql");
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
DatabaseHelper helper = new DatabaseHelper(this.DatabaseName, this.Password);
helper.ExecuteNonQuery(command);
}
}
private void AddOfficeScript()
{
string sql = FileHelper.ReadSqlResource("office.sql");
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
DatabaseHelper helper = new DatabaseHelper(this.DatabaseName, this.Password);
helper.ExecuteNonQuery(command);
}
}
private void DropOfficeScript()
{
string sql = FileHelper.ReadSqlResource("drop-office.sql");
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
DatabaseHelper helper = new DatabaseHelper(this.DatabaseName, "mix_erp", this.MixERPRolePassword);
helper.ExecuteNonQuery(command);
}
}
private void DropRoleProcedure()
{
const string sql = "DROP FUNCTION IF EXISTS add_role(text, text);";
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
DatabaseHelper helper = new DatabaseHelper(this.DatabaseName, this.Password);
helper.ExecuteNonQuery(command);
}
}
private void AllowLoginToRole(string roleName)
{
string sql = "ALTER ROLE {0} WITH LOGIN;";
sql = string.Format(CultureInfo.InvariantCulture, sql, roleName);
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
DatabaseHelper helper = new DatabaseHelper(this.DatabaseName, this.Password);
helper.ExecuteNonQuery(command);
}
}
public static bool DbExists(string database, string password)
{
DatabaseHelper helper = new DatabaseHelper(string.Empty, password);
const string sql = "SELECT COUNT(*) FROM pg_database WHERE datname=@Database;";
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
command.Parameters.AddWithValue("@Database", database);
object result = helper.GetScalarValue(command);
return result.ToString().Equals("1");
}
}
private void CreateOffice()
{
const string sql =
"SELECT * FROM add_office(@OfficeCode, @OfficeName, @NickName, @RegistrationDate, @CurrencyCode, @CurrencySymbol, @CurrencyName, @HundredthName, @AdminName, @UserName, @Password);";
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
command.Parameters.AddWithValue("@OfficeCode", this.Office.OfficeCode);
command.Parameters.AddWithValue("@OfficeName", this.Office.OfficeName);
command.Parameters.AddWithValue("@NickName", this.Office.NickName);
command.Parameters.AddWithValue("@RegistrationDate", this.Office.RegistrationDate);
command.Parameters.AddWithValue("@CurrencyCode", this.Office.CurrencyCode);
command.Parameters.AddWithValue("@CurrencySymbol", this.Office.CurrencySymbol);
command.Parameters.AddWithValue("@CurrencyName", this.Office.CurrencyName);
command.Parameters.AddWithValue("@HundredthName", this.Office.HundredthName);
command.Parameters.AddWithValue("@AdminName", this.Office.AdminName);
command.Parameters.AddWithValue("@UserName", this.Office.UserName);
command.Parameters.AddWithValue("@Password", this.Office.Password);
DatabaseHelper helper = new DatabaseHelper(this.DatabaseName, "mix_erp", this.MixERPRolePassword);
helper.ExecuteNonQuery(command);
}
}
private void RunDbScript(string scriptPath)
{
string path = FileHelper.CombineWithBaseDirectory(this.ExtractDirectory);
path = Path.Combine(path, scriptPath);
string sql = File.ReadAllText(path, Encoding.UTF8);
DatabaseHelper helper = new DatabaseHelper(this.DatabaseName, this.Password);
helper.ExecuteNonQuery(new NpgsqlCommand(sql));
}
private void CreateRole(string roleName, string password)
{
const string sql = "SELECT * FROM add_role(@RoleName, @Password);";
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
command.Parameters.AddWithValue("@RoleName", roleName);
command.Parameters.AddWithValue("@Password", password);
DatabaseHelper helper = new DatabaseHelper(this.DatabaseName, this.Password);
helper.ExecuteNonQuery(command);
}
}
private void TakeOwnership()
{
string sql = string.Format(CultureInfo.InvariantCulture, "ALTER DATABASE {0} OWNER TO mix_erp;",
this.DatabaseName);
DatabaseHelper helper = new DatabaseHelper(string.Empty, this.Password);
helper.ExecuteNonQuery(new NpgsqlCommand(sql));
}
private void CreateDatabase()
{
string sql = "CREATE DATABASE " + this.DatabaseName + " ENCODING='UTF8' LC_COLLATE='C' LC_CTYPE='C' TEMPLATE=template0;";
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
DatabaseHelper helper = new DatabaseHelper(string.Empty, this.Password);
helper.ExecuteNonQuery(command);
}
}