private void CreateGameweek(DateTime date)
{
var start = date.Date;
var end = start.AddDays(1).Subtract(new TimeSpan(TimeSpan.TicksPerSecond));
_connection.Execute(mergeSql, new { start, end });
}
public void Save(Fixture fixture)
{
_connection.Execute(saveFixture, fixture);
_connection.Execute(saveCompetition, new { fixture.CompetitionId, fixture.Competition.BookieId });
_connection.Execute(saveTeam, new { fixture.HomeTeam.Id, fixture.HomeTeam.Name, fixture.HomeTeam.BookieName });
_connection.Execute(saveTeam, new { fixture.AwayTeam.Id, fixture.AwayTeam.Name, fixture.AwayTeam.BookieName });
}
private static string SaveTransactionPoint(this IDatabaseConnection This)
{
var savePoint = $"S{_rand.Value.Next(short.MaxValue)}";
This.Execute(SQLBuilder.SavePoint(savePoint));
return(savePoint);
}
protected void AddColumn(IDatabaseConnection connection, string table, string columnName, string type, List <string> existingColumnNames)
{
if (existingColumnNames.Contains(columnName, StringComparer.OrdinalIgnoreCase))
{
return;
}
connection.Execute("alter table " + table + " add column " + columnName + " " + type + " NULL");
}
private void CreateTriggers(Type ty, IDatabaseConnection connection)
{
try
{
var map = manager.GetMapping(ty);
// Create the 3 triggers on table
// Insert Trigger
// Update Trigger
// Delete Trigger
var lstNewClauses = map.PrimaryKeys.Select(primaryKey => " new." + primaryKey.Name).ToList();
string pkeysNames = String.Join(", ", map.PrimaryKeys.Select(column => column.Name));
string pkeysNewNames = String.Join(", ", lstNewClauses);
var declWhere =
map.PrimaryKeys.Select(primaryKey => String.Format("\"{0}\" = old.\"{0}\" ", primaryKey.Name))
.ToList();
string updateOrWherePkeysName = String.Join(" and ", declWhere);
String triggerInsertQuery =
String.Format(SQLiteConstants.CreateTriggerAfterInsert,
map.TableName, pkeysNames, pkeysNewNames);
connection.Execute(triggerInsertQuery);
String triggerUpdateQuery =
String.Format(SQLiteConstants.CreateTriggerAfterUpdate,
map.TableName, updateOrWherePkeysName);
connection.Execute(triggerUpdateQuery);
String triggerDeleteQuery =
String.Format(SQLiteConstants.CreateTriggerAfterDelete,
map.TableName, updateOrWherePkeysName);
connection.Execute(triggerDeleteQuery);
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
throw;
}
}
public void Save(Standing standing)
{
var team = _teamDb.RetrieveBySourceId(standing.SourceTeamId).Result;
standing.TeamId = team.Id;
var competition = _competitionDb.RetrieveBySourceId(standing.SourceCompetitionId).Result;
standing.CompetitionId = competition.Id;
_connection.Execute(mergeSql, standing);
}
private void DisableTriggers(TableMapping map, IDatabaseConnection connection)
{
try
{
String triggerDeleteQuery = String.Format(SQLiteConstants.DeleteTriggerAfterDelete, map.TableName);
String triggerInsertQuery = String.Format(SQLiteConstants.DeleteTriggerAfterInsert, map.TableName);
String triggerUpdateQuery = String.Format(SQLiteConstants.DeleteTriggerAfterUpdate, map.TableName);
connection.Execute(triggerDeleteQuery);
connection.Execute(triggerInsertQuery);
connection.Execute(triggerUpdateQuery);
//await connection.ExecuteStatementAsync(triggerDeleteQuery).AsTask().ConfigureAwait(false);
//await connection.ExecuteStatementAsync(triggerInsertQuery).AsTask().ConfigureAwait(false);
//await connection.ExecuteStatementAsync(triggerUpdateQuery).AsTask().ConfigureAwait(false);
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
throw;
}
}
private static void MigrateTable(this IDatabaseConnection This, TableMapping tableMapping)
{
var existingCols = This.GetTableInfo(tableMapping.TableName);
var toBeAdded = new List <KeyValuePair <string, ColumnMapping> > ();
foreach (var p in tableMapping.Columns)
{
if (!existingCols.ContainsKey(p.Key))
{
toBeAdded.Add(p);
}
}
foreach (var p in toBeAdded)
{
This.Execute(SQLBuilder.AlterTableAddColumn(tableMapping.TableName, p.Key, p.Value));
}
}
public void Save(Competition competition)
{
_connection.Execute(mergeSql, competition);
}
internal static void CreateIndex(this IDatabaseConnection This, string indexName, string tableName, IEnumerable <string> columnNames, bool unique)
{
This.Execute(SQLBuilder.CreateIndex(indexName, tableName, columnNames, unique));
}
/*
* /// <summary>
* /// Returns metadata about a specific column of a specific database table,
* /// </summary>
* /// <param name="This">The database connection.</param>
* /// <param name="columnInfo">The ColumnInfo of the column whose metadata should be retrieved.</param>
* /// <returns>The metadata of the column specified by columnInfo.</returns>
* public static TableColumnMetadata GetTableColumnMetadata(this IDatabaseConnection This, ColumnInfo columnInfo)
* {
* Contract.Requires(This != null);
* Contract.Requires(columnInfo != null);
*
* return This.GetTableColumnMetadata(columnInfo.DatabaseName, columnInfo.TableName, columnInfo.OriginName);
* }*/
/// <summary>
/// Executes the SQLite VACUUM command
/// </summary>
/// <param name="This">The database connection.</param>
/// <seealso href="https://www.sqlite.org/lang_vacuum.html"/>
public static void Vacuum(this IDatabaseConnection This) =>
This.Execute(SQLBuilder.Vacuum);
private static void RollbackTransaction(this IDatabaseConnection This) => This.Execute(SQLBuilder.RollbackTransaction);
private static void RollbackTransactionTo(this IDatabaseConnection This, string savepoint) => This.Execute(SQLBuilder.RollbackTransactionTo(savepoint));
/// <inheritdoc/> protected override void ApplyCore(IDatabaseConnection connection) => connection.Execute(createVersionTableSql);
/// <summary>
/// Deletes all rows in a given table.
/// </summary>
/// <param name="This">The database connection.</param>
/// <typeparam name="T">The mapped type.</typeparam>
public static void DeleteAllRows <T>(this IDatabaseConnection This)
{
var tableMapping = TableMapping.Get <T>();
This.Execute(SQLBuilder.DeleteAll(tableMapping.TableName));
}
private static void BeginTransaction(this IDatabaseConnection This, TransactionMode mode)
{
var beginTransactionString = SQLBuilder.BeginTransactionWithMode(mode);
This.Execute(beginTransactionString);
}
internal static void CreateTableIfNotExists(this IDatabaseConnection conn, string tableName, CreateFlags createFlags, IReadOnlyDictionary <string, ColumnMapping> columns)
{
var query = SQLBuilder.CreateTableIfNotExists(tableName, createFlags, columns);
conn.Execute(query);
}
public void Save(MatchOdds odds)
{
_connection.Execute(mergesql, odds);
}
public void Save(Team team)
{
_connection.Execute(mergeSql, team);
}
/// <summary>
/// Drops the table if it exists. Otherwise this is a no-op.
/// </summary>
/// <param name="This">The database connection.</param>
/// <typeparam name="T">The mapped type.</typeparam>
/// <seealso href="https://www.sqlite.org/lang_droptable.html"/>
public static void DropTableIfExists <T>(this IDatabaseConnection This)
{
var tableMapping = TableMapping.Get <T>();
This.Execute(SQLBuilder.DropTableIfExists(tableMapping.TableName));
}
private static void ReleaseTransaction(this IDatabaseConnection This, string savepoint) => This.Execute(SQLBuilder.Release(savepoint));
private static void CommitTransaction(this IDatabaseConnection This) => This.Execute(SQLBuilder.CommitTransaction);
public void Save(Region region)
{
_connection.Execute(mergeSql, region);
}
