private static void SetupPg()
{
using (var connection = new NpgsqlConnection(String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};", "localhost", "5432", "postgres", "postgrespass", "postgres")))
{
connection.Open();
// drop database
connection.Execute("DROP DATABASE IF EXISTS testdb;");
connection.Execute("CREATE DATABASE testdb WITH OWNER = postgres ENCODING = 'UTF8' CONNECTION LIMIT = -1;");
}
System.Threading.Thread.Sleep(1000);
using (var connection = new NpgsqlConnection(String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};", "localhost", "5432", "postgres", "postgrespass", "testdb")))
{
connection.Open();
connection.Execute(@" create table Users (Id SERIAL PRIMARY KEY, Name varchar not null, Age int not null, ScheduledDayOff int null, CreatedDate date not null default CURRENT_DATE) ");
connection.Execute(@" create table Car (CarId SERIAL PRIMARY KEY, Id int null, Make varchar not null, Model varchar not null, Color varchar not null, AgeInYears int not null) ");
connection.Execute(@" create table BigCar (CarId BIGSERIAL PRIMARY KEY, Make varchar not null, Model varchar not null) ");
connection.Execute(@" alter sequence bigcar_carid_seq RESTART WITH 2147483650");
connection.Execute(@" create table City (Name varchar not null, Population int not null) ");
connection.Execute(@" CREATE SCHEMA Log; ");
connection.Execute(@" create table Log.CarLog (Id SERIAL PRIMARY KEY, LogNotes varchar NOT NULL) ");
connection.Execute(@" CREATE TABLE GUIDTest(Id uuid PRIMARY KEY,name varchar NOT NULL)");
connection.Execute(@" create table StrangeColumnNames (ItemId Serial PRIMARY KEY, word varchar not null, colstringstrangeword varchar) ");
connection.Execute(@" create table UserWithoutAutoIdentity (Id int PRIMARY KEY, Name varchar not null, Age int not null) ");
}
}
private static void CreateDatabase()
{
var cxBuilder = new NpgsqlConnectionStringBuilder(ConnectionString);
var database = cxBuilder.Database;
cxBuilder.Database = null;
var db = new NpgsqlConnection(cxBuilder.ToString());
db.Execute($"DROP DATABASE IF EXISTS \"{database}\"");
db.Execute($"CREATE DATABASE \"{database}\"");
}
private void Cleanup()
{
using (var connection = new NpgsqlConnection(ConnectionString))
{
connection.Open();
foreach (var stream in _streams)
{
Catch(() => connection.Execute($"drop table if exists {TableBuilder.EventsName(stream)};"));
Catch(() => connection.Execute($"drop table if exists {TableBuilder.SnapshotsName(stream)};"));
}
}
}
public void Dispose()
{
using (var connection = new NpgsqlConnection(PostgresFixture.ConnectionString))
{
connection.Open();
connection.Execute($"drop table if exists {TableBuilder.EventsName(DefaultStream.StreamName)};");
connection.Execute($"drop table if exists {TableBuilder.SnapshotsName(DefaultStream.StreamName)};");
connection.Execute($"drop table if exists {TableBuilder.EventsName(SnapshotStream.StreamName)};");
connection.Execute($"drop table if exists {TableBuilder.SnapshotsName(SnapshotStream.StreamName)};");
}
}
public static int Delete(IEnumerable<int> malAnimeIds, NpgsqlConnection conn, NpgsqlTransaction transaction)
{
string idList = string.Join(", ", malAnimeIds.Select(id => id.ToString(CultureInfo.InvariantCulture)));
string deleteSql = string.Format("DELETE FROM mal_anime_synonym WHERE mal_anime_id IN ({0})", idList);
int numRowsDeleted = conn.Execute(deleteSql, transaction: transaction);
return numRowsDeleted;
}
private static void RecreateSchemaAndInstallObjects()
{
using (var connection = new NpgsqlConnection(
ConnectionUtils.GetMasterConnectionString()))
{
bool databaseExists = connection.Query<bool?>(
@"select true :: boolean from pg_database where datname = @databaseName;",
new
{
databaseName = ConnectionUtils.GetDatabaseName()
}
).SingleOrDefault() ?? false;
if (!databaseExists)
{
connection.Execute($@"CREATE DATABASE ""{ConnectionUtils.GetDatabaseName()}""");
}
}
using (var connection = new NpgsqlConnection(ConnectionUtils.GetConnectionString()))
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
PostgreSqlObjectsInstaller.Install(connection);
PostgreSqlTestObjectsInitializer.CleanTables(connection);
}
}
public static void Init()
{
StoreEvents = Wireup.Init()
//.UsingInMemoryPersistence()
.UsingSqlPersistence("EventStore") // Connection string is in app.config
.WithDialect(new PostgreSqlDialect())
.EnlistInAmbientTransaction() // two-phase commit
.InitializeStorageEngine()
//.TrackPerformanceInstance("example")
.UsingJsonSerialization()
//.Compress()
//.EncryptWith(EncryptionKey)
//.HookIntoPipelineUsing(new[] { new AuthorizationPipelineHook() })
//.UsingSynchronousDispatchScheduler()
//.DispatchTo(new DelegateMessageDispatcher(DispatchCommit))
.Build();
var connectionString = ConfigurationManager.ConnectionStrings["EventStore"].ConnectionString;
var connection = new NpgsqlConnection(connectionString);
connection.Open();
var sql = @"
CREATE TABLE IF NOT EXISTS ""LigaView""
(
""Id"" uuid NOT NULL,
""Name"" varchar NOT NULL
);";
connection.Execute(sql);
connection.Close();
}
private void Run()
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
var orders = conn.Query<Order>("select * from Orders where IsMatchSearched <> true or IsMatchSearched is null").ToList();
Console.WriteLine("finding matches for " + orders.Count + " orders");
foreach(var order in orders)
{
var minAmount = 0.9 * order.Amount;
var maxAmount = 1.1 * order.Amount;
var minPrice = 0.9 * order.Price;
var maxPrice = 1.1 * order.Price;
var type = order.OrderType == "Purchase" ? "Sell" : "Purchase";
var matchedOrders = conn.Query<Order>("select * from Orders where IsMatchSearched = true and Price < @MaxPrice and Price > @MinPrice and Amount < @MaxAmount and Amount > @MinAmount and OrderType = @Type and CommodityType = @CommodityType and fkUserId <> @UserId",
new {
MinAmount = minAmount,
MaxAmount = maxAmount,
MinPrice = minPrice,
MaxPrice = maxPrice,
CommodityType = order.CommodityType,
Type = type,
UserId = order.fkUserId
}).ToList();
Console.WriteLine("order " + order.Id + " has "+matchedOrders.Count+" matches");
foreach(var matchedOrder in matchedOrders)
{
String message = "Kami menemukan order yang cocok. {0} {1} {2} sebesar {3} kg dengan harga Rp. {4}";
conn.Execute("insert into orders_matches(first_id, second_id) values (@FirstId, @SecondId)", new { FirstId = order.Id, SecondId = matchedOrder.Id });
conn.Execute("insert into orders_matches(first_id, second_id) values (@FirstId, @SecondId)", new { FirstId = matchedOrder.Id, SecondId = order.Id });
var verb = order.OrderType == "Purchase" ? "menjual" : "membeli";
var commodity = conn.Query<String>("select Name from CommodityType where Id = @Id", new { Id = order.CommodityType }).FirstOrDefault();
var user = conn.Query<User>("select Name, PhoneNumber from Users where Id = @Id", new { Id = matchedOrder.fkUserId }).FirstOrDefault();
message = String.Format(message, user.Name, verb, commodity, matchedOrder.Amount, matchedOrder.Price);
conn.Execute("insert into Outboxes(PhoneNumber, Message) values (@PhoneNumber, @Message)",
new
{
PhoneNumber = user.PhoneNumber,
Message = message
}
);
}
conn.Execute("update Orders set IsMatchSearched = true where Id = @Id", new { Id = order.Id });
}
};
}
public void PostGresInsertModelFillingId()
{
var conn = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings["postgres"].ConnectionString);
var scriptBuilder = conn.GetScriptBuild();
var cliente = new Cliente()
{
Nome = "Miranda"
};
var createTableScript = scriptBuilder.GetCreateTableCommand <Cliente>();
conn.Execute(createTableScript);
conn.Insert <Cliente>(cliente);
Assert.AreEqual(1, cliente.Id);
conn.Execute("drop table \"Cliente\"");
}
public static void CleanTables(NpgsqlConnection connection)
{
if (connection == null) throw new ArgumentNullException("connection");
var script = GetStringResource(
typeof (PostgreSqlTestObjectsInitializer).Assembly,
"Hangfire.PostgreSql.Tests.Clean.sql").Replace("'hangfire'", ConnectionUtils.GetSchemaName());
connection.Execute(script);
}
public void PostGresDeleteModel()
{
var conn = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings["postgres"].ConnectionString);
var scriptBuilder = conn.GetScriptBuild();
var cliente = new Cliente()
{
Nome = "Miranda"
};
var createTableScript = scriptBuilder.GetCreateTableCommand <Cliente>();
conn.Execute(createTableScript);
var id = conn.InsertReturningId <Cliente>(cliente);
cliente.Id = id;
conn.Delete(cliente);
conn.Execute("drop table \"Cliente\"");
}
public void Execute(string stream)
{
var sql = Sql
.Replace("{events-table}", stream + "_events")
.Replace("{snapshots-table}", stream + "_snapshots");
using (var connection = new NpgsqlConnection(_connectionString))
{
connection.Open();
connection.Execute(sql);
}
}
public void PostGresSelectModel()
{
var conn = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings["postgres"].ConnectionString);
var scriptBuilder = conn.GetScriptBuild();
var cliente = new Cliente()
{
Nome = "Miranda"
};
var createTableScript = scriptBuilder.GetCreateTableCommand <Cliente>();
conn.Execute(createTableScript);
var id = conn.InsertReturningId <Cliente>(cliente);
var clientes = conn.GetAll <Cliente>();
Assert.AreEqual(1, clientes.Count());
Assert.AreEqual("Miranda", clientes.ToList()[0].Nome);
conn.Execute("drop table \"Cliente\"");
}
public void Execute(string stream)
{
var sql = Sql
.Replace("{events-table}", TableBuilder.EventsName(stream))
.Replace("{snapshots-table}", TableBuilder.SnapshotsName(stream));
using (var connection = new NpgsqlConnection(_connectionString))
{
connection.Open();
connection.Execute(sql);
}
}
public void OnPost()
{
// send information to the database
var connectionString = "Server = 127.0.0.1; Port = 5432; Database = bookish; User Id = Bookish; Password = password; ";;
NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString);
connection.Open();
connection.Execute("INSERT INTO \"Books\" (\"Title\", \"Author\", \"ISBN\",\"AvailableCopies\", \"TotalCopies\") VALUES(@Title, @Author, @ISBN, @TotalCopies, @TotalCopies)", this);
}
public void CheckoutBook(int BookID, string DueDate)
{
var connectionString = "Server = 127.0.0.1; Port = 5432; Database = bookish; User Id = Bookish; Password = password; ";
var UserID = AccessDB.FindUserIDByEmail(Email, connectionString);
var anyCopies = AccessDB.AreThereAvailableCopies(BookID, connectionString);
var DateTimeDue = DateTime.Parse(DueDate);
if (anyCopies == true)
{
NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString);
connection.Open();
using (var transaction = connection.BeginTransaction())
{
connection.Execute("INSERT INTO \"Books Taken Out\" (\"UserID\",\"BookID\", \"DueDate\") VALUES (@UserID, @BookID, @DateTimeDue)", new { UserID, BookID, DateTimeDue });
connection.Execute("UPDATE \"Books\" SET \"AvailableCopies\" = \"AvailableCopies\" - 1 WHERE \"BookID\" = @BookID", new { BookID });
transaction.Commit();
}
connection.Close();
}
}
public void PostGresTestOperators()
{
var conn = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings["postgres"].ConnectionString);
var scriptBuilder = conn.GetScriptBuild();
var cliente1 = new Cliente()
{
Nome = "Miranda"
};
var cliente2 = new Cliente()
{
Nome = "Moshe"
};
var cliente3 = new Cliente()
{
Nome = "Moshe"
};
var createTableScript = scriptBuilder.GetCreateTableCommand <Cliente>();
conn.Execute(createTableScript);
var id = conn.InsertReturningId <Cliente>(cliente1);
conn.Insert(cliente2);
conn.Insert(cliente3);
var clientesWithIdGreaterThan1 = conn.Select <Cliente>(c => c.Id > 1);
var clientesIdOtherThan3 = conn.Select <Cliente>(c => c.Id != 3);
var clientesWithIdLessThan2 = conn.Select <Cliente>(c => c.Id < 2);
Assert.AreEqual(2, clientesWithIdGreaterThan1.Count());
Assert.AreEqual("Miranda", clientesIdOtherThan3.ToList()[0].Nome);
Assert.AreEqual(2, clientesIdOtherThan3.ToList()[1].Id);
Assert.AreEqual(1, clientesWithIdLessThan2.Count());
conn.Execute("drop table \"Cliente\"");
}
public static void Install(NpgsqlConnection connection, string schemaName = "hangfire")
{
if (connection == null) throw new ArgumentNullException("connection");
Log.Info("Start installing Hangfire SQL objects...");
int version = 3; //We started at version 3
bool scriptFound = true;
do
{
try
{
var script = GetStringResource(
typeof (PostgreSqlObjectsInstaller).Assembly,
string.Format("Hangfire.PostgreSql.Install.v{0}.sql",
version.ToString(CultureInfo.InvariantCulture)));
if (schemaName != "hangfire")
{
script = script.Replace("'hangfire'", string.Format("'{0}'", schemaName))
.Replace(@"""hangfire""", string.Format(@"""{0}""", schemaName));
}
try
{
connection.Execute(script, commandTimeout: 120);
}
catch (NpgsqlException ex)
{
if((ex.BaseMessage ?? "") != "version-already-applied")
{
throw;
}
}
}
catch
{
scriptFound = false;
}
version++;
} while (scriptFound);
Log.Info("Hangfire SQL objects installed.");
}
public static void DeleteOldestUsers(long numUsers, NpgsqlConnection conn, NpgsqlTransaction transaction)
{
if (numUsers == 0)
{
return;
}
if (numUsers < 0)
{
throw new ArgumentOutOfRangeException("numUsers", numUsers, string.Format("Cannot delete {0} oldest users", numUsers));
}
string deleteSql = @"DELETE FROM mal_user WHERE mal_user_id IN
(SELECT mal_user_id FROM mal_user
ORDER BY time_added
LIMIT :NumToDelete)";
conn.Execute(deleteSql, new { NumToDelete = numUsers }, transaction);
}
public void Insert(NpgsqlConnection conn, NpgsqlTransaction transaction)
{
string sql = "INSERT INTO mal_user (mal_user_id, mal_name, time_added) VALUES (:MalUserId, :MalName, :TimeAdded)";
conn.Execute(sql, new { MalUserId = mal_user_id, MalName = mal_name, TimeAdded = time_added }, transaction);
}
public void CopyToRedshift(string dataPrefix, string dataFileExtension,
string processingPrefix, string completedPrefix, string errorPrefix,string delimiter, string tableName, string[] columnList, string connectionStringName,
int headerRowCount,Action<Exception,string> errorLog, Action<string> debugLog)
{
debugLog(string.Format("Getting files with extension {0} from {1}", dataFileExtension, dataPrefix));
var files = ListFiles(dataPrefix)
.Where(x => x.EndsWith(dataFileExtension))
.ToList();
if (files.Count == 0)
{
debugLog("No Files to copy");
return;
}
var batchId = Guid.NewGuid().ToString("N");
var batchProcessDir = S3.Combine(processingPrefix, batchId + "/");
try
{
debugLog(string.Format("Moving {0} files to {1}", files.Count, batchProcessDir));
// create a batch id and store files in specific folder for processing only this batch's files
foreach (var file in files)
{
var pFile = file.Replace(dataPrefix, batchProcessDir);
MoveFile(file, _connectionInfo.BucketName, pFile, _connectionInfo.BucketName);
}
debugLog("Building sql query and primary key check");
var sql = EmbeddedResource.Get("copyToRedshift.sql")
.Replace("$COLUMNLIST$", string.Join(",", columnList))
.Replace("$ACCESSKEY$", _connectionInfo.AccessKey)
.Replace("$SECRETKEY$", _connectionInfo.SecretKey)
.Replace("$BUCKET$", _connectionInfo.BucketName)
.Replace("$TABLENAME$", tableName)
.Replace("$DELIMETER$", delimiter)
.Replace("$HEADERROWS$", headerRowCount.ToString(CultureInfo.InvariantCulture))
.Replace("$COLUMNLIST$", string.Join(",", columnList))
.Replace("$PATH$", batchProcessDir);
debugLog("Copying files to redshift");
using (var connection = new NpgsqlConnection(ConfigUtilities.GetConnectionString(connectionStringName)))
{
debugLog("Running SQL: " + sql);
connection.Execute(sql, commandTimeout: 1000*60*15);
}
debugLog("Moving files to completed directory");
foreach (var file in files)
{
var processedFile = file.Replace(dataPrefix, batchProcessDir);
var completeFile = file.Replace(dataPrefix, S3.Combine(completedPrefix, batchId + "/"));
MoveFile(processedFile, _connectionInfo.BucketName, completeFile, _connectionInfo.BucketName);
}
}
catch (Exception exception)
{
errorLog(exception, "Error in copy to redshift");
debugLog("Moving files to error directory");
var filesInProcessing = ListFiles(batchProcessDir)
.Where(x => x.EndsWith(dataFileExtension))
.ToList();
foreach (var file in filesInProcessing)
{
var errorFile = file.Replace(batchProcessDir, Combine(errorPrefix,batchId + "/") );
//var processedFile = Combine(processingPrefix, file);
MoveFile(file, _connectionInfo.BucketName, errorFile, _connectionInfo.BucketName);
}
}
}
/// <summary>
/// Copy from s3 to redshift
/// </summary>
/// <param name="dataPrefix">the s3 location where the data files are located</param>
/// <param name="dataFileExtension">the extension of the files to insert</param>
/// <param name="processingPrefix">the s3 location where the data files are moved during processing</param>
/// <param name="completedPrefix">the s3 location where the data files are moved after copy success</param>
/// <param name="errorPrefix">the s3 location where the data files are moved if there is an error</param>
/// <param name="delimeter"></param>
/// <param name="tableName">the redshift table to copy to</param>
/// <param name="columnList">the columns of the table to copy into, same order as the data files</param>
/// <param name="primaryKeyColumns">the columns that are primary keys and should be used to figure out how to merge</param>
/// <param name="connectionStringName">the connection string or connection string name of the redshift database</param>
/// <param name="headerRowCount">how many of the rows in the text file are header rows</param>
/// <param name="errorLog">function to log errors</param>
/// <param name="debugLog">function for debug logging</param>
public void MergeToRedshift(string dataPrefix, string dataFileExtension,
string processingPrefix, string completedPrefix, string errorPrefix, string delimeter,string tableName, string[] columnList, string[] primaryKeyColumns, string connectionStringName,
int headerRowCount,Action<Exception,string> errorLog, Action<string> debugLog, bool nullCheckPrimaryKeys)
{
debugLog(string.Format("Getting files with extension {0} from {1}", dataFileExtension, dataPrefix));
var files = ListFiles(dataPrefix)
.Where(x => x.EndsWith(dataFileExtension))
.ToList();
if (files.Count == 0)
{
debugLog("No Files to copy");
return;
}
try
{
debugLog(string.Format("Moving {0} files to {1}", files.Count, processingPrefix));
foreach (var file in files)
{
var pFile = file.Replace(dataPrefix, processingPrefix);
MoveFile(file, _connectionInfo.BucketName, pFile, _connectionInfo.BucketName);
}
debugLog("Building sql query and primary key check");
var stagingTableName = GenerateRandom.String(6).ToLower() + "_staging";
var sql = EmbeddedResource.Get("mergeToRedshift.sql")
.Replace("$COLUMNLIST$", string.Join(",", columnList))
.Replace("$ACCESSKEY$", _connectionInfo.AccessKey)
.Replace("$SECRETKEY$", _connectionInfo.SecretKey)
.Replace("$BUCKET$", _connectionInfo.BucketName)
.Replace("$TEMPTABLE$", stagingTableName)
.Replace("$TABLENAME$", tableName)
.Replace("$DELIMETER$", delimeter)
.Replace("$HEADERROWS$", headerRowCount.ToString(CultureInfo.InvariantCulture))
.Replace("$COLUMNLIST$", string.Join(",", columnList))
.Replace("$PATH$", processingPrefix);
var primaryKeyCheck = new List<string>();
foreach (var pkey in primaryKeyColumns)
{
var tableKey = tableName + ".[" + pkey + "]";
var stageKey = stagingTableName + ".[" + pkey + "]";
var input = nullCheckPrimaryKeys ? "{tableKey} = {stageKey} or ({tableKey} is null and {stageKey} is null)" : "{tableKey} = {stageKey}";
input = Regex.Replace(input, "{tableKey}", tableKey);
input = Regex.Replace(input, "{stageKey}", stageKey);
primaryKeyCheck.Add(input);
}
var primaryKeySql = string.Join(" and ", primaryKeyCheck);
sql = sql.Replace("$PRIMARYKEYCHECK$", primaryKeySql);
debugLog("Copying files to redshift");
using (var connection = new NpgsqlConnection(ConfigUtilities.GetConnectionString(connectionStringName)))
{
debugLog("Running SQL: " + sql);
connection.Execute(sql, commandTimeout: 1000*60*15);
}
debugLog("Moving files to completed directory");
foreach (var file in files)
{
var processedFile = file.Replace(dataPrefix, processingPrefix);
var completeFile = file.Replace(dataPrefix, completedPrefix);
MoveFile(processedFile, _connectionInfo.BucketName, completeFile, _connectionInfo.BucketName);
}
}
catch (Exception exception)
{
errorLog(exception, "Error in copy to redshift");
debugLog("Moving files to error directory");
var filesInProcessing = ListFiles(processingPrefix)
.Where(x => x.EndsWith(dataFileExtension))
.ToList();
foreach (var file in filesInProcessing)
{
var errorFile = file.Replace(processingPrefix, errorPrefix);
//var processedFile = Combine(processingPrefix, file);
MoveFile(file, _connectionInfo.BucketName, errorFile, _connectionInfo.BucketName);
}
}
}
public void Insert(NpgsqlConnection conn, NpgsqlTransaction transaction)
{
string sql = @"INSERT INTO mal_anime
(mal_anime_id, title, mal_anime_type_id, num_episodes, mal_anime_status_id, start_year, start_month, start_day,
end_year, end_month, end_day, image_url, last_updated)
VALUES
(:MalAnimeId, :Title, :MalAnimeTypeId, :NumEpisodes, :MalAnimeStatusId, :StartYear, :StartMonth, :StartDay,
:EndYear, :EndMonth, :EndDay, :ImageUrl, :LastUpdated)";
conn.Execute(sql,
new
{
MalAnimeId = mal_anime_id,
Title = title,
MalAnimeTypeId = mal_anime_type_id,
NumEpisodes = num_episodes,
MalAnimeStatusId = mal_anime_status_id,
StartYear = start_year,
StartMonth = start_month,
StartDay = start_day,
EndYear = end_year,
EndMonth = end_month,
EndDay = end_day,
ImageUrl = image_url,
LastUpdated = last_updated
},
transaction);
}
public static void Install(NpgsqlConnection connection, string schemaName = "hangfire")
{
if (connection == null) throw new ArgumentNullException(nameof(connection));
Log.Info("Start installing Hangfire SQL objects...");
// starts with version 3 to keep in check with Hangfire SqlServer, but I couldn't keep up with that idea after all;
int version = 3;
int previousVersion = 1;
do
{
try
{
string script = null;
try
{
script = GetStringResource(
typeof (PostgreSqlObjectsInstaller).Assembly,
$"Hangfire.PostgreSql.Install.v{version.ToString(CultureInfo.InvariantCulture)}.sql");
}
catch(MissingManifestResourceException)
{
break;
}
if (schemaName != "hangfire")
{
script = script.Replace("'hangfire'", $"'{schemaName}'").Replace(@"""hangfire""", $@"""{schemaName}""");
}
using (var transaction = connection.BeginTransaction(IsolationLevel.Serializable))
using (var command = new NpgsqlCommand(script, connection, transaction))
{
command.CommandTimeout = 120;
try
{
command.ExecuteNonQuery();
// Due to https://github.com/npgsql/npgsql/issues/641 , it's not possible to send
// CREATE objects and use the same object in the same command
// So bump the version in another command
connection.Execute($@"UPDATE ""{schemaName}"".""schema"" SET ""version"" = @version WHERE ""version"" = @previousVersion", new {version, previousVersion}, transaction);
transaction.Commit();
}
catch (PostgresException ex)
{
if ((ex.MessageText ?? "") != "version-already-applied")
{
throw;
}
}
}
}
catch (Exception ex)
{
if (ex.Source.Equals("Npgsql"))
{
Log.ErrorException("Error while executing install/upgrade", ex);
}
}
previousVersion = version;
version++;
} while (true);
Log.Info("Hangfire SQL objects installed.");
}
public void Update(NpgsqlConnection conn, NpgsqlTransaction transaction)
{
string sql = @"UPDATE mal_anime
SET title = :Title, mal_anime_type_id = :MalAnimeTypeID, num_episodes = :NumEpisodes,
mal_anime_status_id = :MalAnimeStatusId, start_year = :StartYear, start_month = :StartMonth, start_day = :StartDay,
end_year = :EndYear, end_month = :EndMonth, end_day = :EndDay, image_url = :ImageUrl, last_updated = :LastUpdated
WHERE mal_anime_id = :MalAnimeId";
conn.Execute(sql,
new
{
MalAnimeId = mal_anime_id,
Title = title,
MalAnimeTypeId = mal_anime_type_id,
NumEpisodes = num_episodes,
MalAnimeStatusId = mal_anime_status_id,
StartYear = start_year,
StartMonth = start_month,
StartDay = start_day,
EndYear = end_year,
EndMonth = end_month,
EndDay = end_day,
ImageUrl = image_url,
LastUpdated = last_updated
},
transaction);
}
public void TestPostresqlArrayParameters()
{
using (var conn = new NpgsqlConnection("Server=localhost;Port=5432;User Id=dappertest;Password=dapperpass;Database=dappertest;Encoding=UNICODE"))
{
conn.Open();
IDbTransaction transaction = conn.BeginTransaction();
conn.Execute("create table tcat ( id serial not null, breed character varying(20) not null, name character varying (20) not null);");
conn.Execute("insert tcat(breed, name) values(:breed, :name) ", Cats);
var r = conn.Query<Cat>("select * from tcat where id=any(:catids)", new { catids = new[] { 1, 3, 5 } });
r.Count().IsEqualTo(3);
r.Count(c => c.Id == 1).IsEqualTo(1);
r.Count(c => c.Id == 3).IsEqualTo(1);
r.Count(c => c.Id == 5).IsEqualTo(1);
transaction.Rollback();
}
}
public DashboardModule()
{
const string sunburstQuery = "select string_agg ( commoditytype.name || '-' || ordertype || ',' || amount , '\\n' ) from orders inner join commoditytype on orders.commoditytype = commoditytype.id";
Get["/dashboard"] = parameters =>
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
var datas = conn.Query<String>(sunburstQuery).ToList();
var data = datas[0];
return View["dashboard",data];
}
};
Get["/sunburst"] = parameters =>
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
var datas = conn.Query<String>(sunburstQuery).ToList();
var data = datas[0];
return View["sunburst", data];
};
};
Get["/verify"] = parameters =>
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
var datas = conn.Query<Registration>("select * from Registrations where IsVerified <> true or IsVerified is null").ToList();
return View["verifylist", datas];
};
};
Get["/verify/{id}"] = parameters =>
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
var data = conn.Query<Registration>("select * from Registrations where (IsVerified <> true or IsVerified is null) and Id = @Id", new { Id = parameters.id }).FirstOrDefault();
return View["verify", data];
};
};
Post["/verify"] = parameters =>
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
dynamic form = Request.Form;
conn.Execute("insert into Users(Name, KtpNumber, PhoneNumber, Address, Longitude, Latitude) values (@Name, @KtpNumber, @PhoneNumber, @Address, @Longitude, @Latitude)",
new
{
Name = form.Name,
KtpNumber = form.KtpNumber,
PhoneNumber = form.PhoneNumber,
Address = form.Address,
Longitude = form.Longitude,
Latitude = form.Latitude,
}
);
conn.Execute("update Registrations set IsVerified = true where Id = @Id", new { Id = form.id });
return Response.AsRedirect("/verify");
};
};
Get["/orders/{start}"] = parameters =>
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
var data = conn.Query<OrderUser>("select * from Orders_Users where Id > @Start", new { Start = parameters.start }).ToList();
return JsonConvert.SerializeObject(data);
};
};
Get["/sunburstdata"] = parameters =>
{
using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
{
var datas = conn.Query<String>(sunburstQuery).ToList();
return datas[0];
};
};
}
