public static long GetLastInsertId(IDbConnection conn, IDbTransaction transaction, string sIdentityName)
{
switch (ConnectionManager.Dialect)
{
#if MySQL
case DatabaseDialect.MySql:
object objLastInsertId = DataCopy.ExecuteScalar(conn, transaction, "SELECT last_insert_id()");
return(Convert.ToInt64(objLastInsertId));
#endif
case DatabaseDialect.MsSql:
case DatabaseDialect.PgSql:
object objLastInsertId = DataCopy.ExecuteScalar(conn, transaction, "SELECT lastval()");
return(Convert.ToInt64(objLastInsertId));
#if SQLite
case DatabaseDialect.LtSql:
#endif
default:
Debug.Assert(false);
break;
}
return(0);
}
public void ATestModeMatchesCreationTest()
{
ConfigurationManager.AppSettings["CreateDatabase"] = "1";
StationSettingsUtils.m_sStartupPath = Path.GetDirectoryName(Assembly.GetAssembly(typeof(DatabaseTests)).Location);
string sDatabaseResourcesFolderName = StationSettingsUtils.StartupPath;
string sTestMatchesFileName = Path.Combine(sDatabaseResourcesFolderName, "CreateTestMatches.config");
string CreateTestMatchesStatement = System.IO.File.ReadAllText(sTestMatchesFileName);
DatabaseManager.DropDatabase(false);
DatabaseManager.EnsureDatabase(false);
try
{
DataCopy.ExecuteScalar(CreateTestMatchesStatement);
}
catch
{
}
long count;
var sql = "select count(" + MatchLn.TableSpec.IdentityNames.First() + ") FROM " + MatchLn.TableSpec.TableName + " where updateid = 71";
long.TryParse(DataCopy.ExecuteScalar(sql).ToString(), out count);
Assert.IsTrue(count > 0);
DatabaseManager.DropDatabase(false);
}
public void CreateTestMatches()
{
string sDatabaseResourcesFolderName = StationSettingsUtils.StartupPath;
string sTestMatchesFileName = Path.Combine(sDatabaseResourcesFolderName, "DatabaseResources\\CreateTestMatches.config");
string CreateTestMatchesStatement = System.IO.File.ReadAllText(sTestMatchesFileName);
//XmlDocument TestMatchesDocument = new XmlDocument();
//TestMatchesDocument.Load(sTestMatchesFileName);
//string CreateTestMatchesStatement = XmlHelper.GetElementInnerText(TestMatchesDocument, "configuration/createTestSoccerMatch");
try
{
using (var conn2 = ConnectionManager.GetConnection())
{
using (IDbTransaction transaction = conn2.BeginTransaction())
{
DataCopy.ExecuteScalar(conn2, transaction, CreateTestMatchesStatement);
transaction.Commit();
}
}
}
catch (Exception e)
{
m_logger.Error(e.Message, e);
}
}
public void SetVesrion(string version)
{
try
{
using (var conn2 = ConnectionManager.GetConnection())
{
DataCopy.ExecuteScalar(conn2, null, "Insert into version (id,version) values (1,'{0}')", version);
}
}
catch (Exception)
{
}
}
public void ClearDatabase()
{
ConfigurationManager.AppSettings["CreateDatabase"] = "1";
StationSettingsUtils.m_sStartupPath = Path.GetDirectoryName(Assembly.GetAssembly(typeof(DatabaseTests)).Location);
DatabaseManager.EnsureDatabase(false);
DatabaseCache.EnsureDatabaseCache();
LineSr.EnsureFromCache();
UpdateFileEntrySr updateFileEntrySr = new UpdateFileEntrySr();
updateFileEntrySr.DataSyncCacheType = eDataSyncCacheType.Statistic.ToString();
UpdateFileEntrySr updateFileEntrySr2 = new UpdateFileEntrySr();
updateFileEntrySr2.DataSyncCacheType = eDataSyncCacheType.Match.ToString();
updateFileEntrySr.Save();
updateFileEntrySr2.Save();
long count;
var sql = "select count(" + UpdateFileEntrySr.TableSpec.IdentityNames.First() + ") FROM " + UpdateFileEntrySr.TableSpec.TableName + " where DataSyncCacheType = '" + eDataSyncCacheType.Statistic.ToString() + "'";
long.TryParse(DataCopy.ExecuteScalar(sql).ToString(), out count);
Assert.IsTrue(count > 0);
sql = "select count(" + UpdateFileEntrySr.TableSpec.IdentityNames.First() + ") FROM " + UpdateFileEntrySr.TableSpec.TableName + " where DataSyncCacheType = '" + eDataSyncCacheType.Match.ToString() + "'";
long.TryParse(DataCopy.ExecuteScalar(sql).ToString(), out count);
Assert.IsTrue(count > 0);
DbManager.Instance.DeleteOldObjects();
sql = "select count(" + UpdateFileEntrySr.TableSpec.IdentityNames.First() + ") FROM " + UpdateFileEntrySr.TableSpec.TableName + " where DataSyncCacheType = '" + eDataSyncCacheType.Statistic.ToString() + "'";
long.TryParse(DataCopy.ExecuteScalar(sql).ToString(), out count);
Assert.IsTrue(count > 0);
sql = "select count(" + UpdateFileEntrySr.TableSpec.IdentityNames.First() + ") FROM " + UpdateFileEntrySr.TableSpec.TableName + " where DataSyncCacheType = '" + eDataSyncCacheType.Match.ToString() + "'";
long.TryParse(DataCopy.ExecuteScalar(sql).ToString(), out count);
Assert.AreEqual(0, count);
DatabaseManager.DropDatabase(false);
}
public static void DropDatabase(bool isTestMode)
{
string sDatabaseName = ConfigurationManager.AppSettings["database_name"];
string sConnectionString = ConfigurationManager.AppSettings["database_connection_string"];
if (isTestMode)
{
sConnectionString.Replace(sDatabaseName, "test_" + sDatabaseName);
sDatabaseName = "test_" + sDatabaseName;
}
try
{
using (IDbConnection conn = new NpgsqlConnection(sConnectionString.Replace(sDatabaseName, ConnectionManager.SystemDatabaseName)))
{
//conn.Open();
eExistResult eerDatabase = DatabaseManager.DoesDatabaseExist(conn, sDatabaseName);
ExcpHelper.ThrowIf(eerDatabase == eExistResult.Error, "Cannot recognize if database '{0}' exists.", sDatabaseName);
if (eerDatabase == eExistResult.Exists)
{
try
{
DataCopy.ExecuteScalar(conn, null, "select pg_terminate_backend(procpid) from pg_stat_activity where datname='{0}';", sDatabaseName);
using (IDbConnection conn2 = new NpgsqlConnection(sConnectionString.Replace(sDatabaseName, ConnectionManager.SystemDatabaseName)))
{
DataCopy.ExecuteScalar(conn2, null, DatabaseManager.Schema.DeleteDatabaseStatement, sDatabaseName);
}
m_logger.InfoFormat("Database '{0}' Successfully deleted", sDatabaseName);
}
catch (Exception excp)
{
m_logger.Error(ExcpHelper.FormatException(excp, "EnsureDatabase() ERROR - cannot delete {0} Database '{1}'", ConnectionManager.Dialect, sDatabaseName), excp);
throw;
}
}
}
}
catch (System.Exception excpInner)
{
string sError = string.Format("Cannot verify PostGreSQL Server. Either Server is not installed or invalid priveleges ({0}).\r\n{1}\r\n{2}", sConnectionString, excpInner.Message, excpInner.StackTrace);
//MessageBox.Show(sError, "Station Start Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
throw new Exception(sError, excpInner);
}
}
public static eExistResult DoesDatabaseExist(IDbConnection conn, string sDatabaseName)
{
try
{
object objCount = DataCopy.ExecuteScalar(conn, null, DatabaseManager.Schema.DatabaseExistsStatement, sDatabaseName.ToLowerInvariant());
int iCount = Convert.ToInt32(objCount);
m_logger.InfoFormat("DatabaseManager.DoesDatabaseExist(DatabaseName='{0}') result = {1}", sDatabaseName, iCount > 0 ? "Exists" : "DOES NOT Exist");
return(iCount > 0 ? eExistResult.Exists : eExistResult.DoesNotExist);
}
catch (Exception excp)
{
m_logger.Excp(excp, "DoesDatabaseExist(DatabaseName='{0}') ERROR", sDatabaseName);
}
return(eExistResult.Error);
}
public override void Save(IDbConnection conn, IDbTransaction transaction)
{
ExcpHelper.ThrowIf(this.BtrCompetitorID == 0, "BtrCompetitorID is Invalid");
if (this.IsModified)
{
base.Save(conn, transaction);
if (this.CompetitorID == 0)
{
object objCompetitorId = DataCopy.ExecuteScalar(conn, transaction, "SELECT CompetitorId FROM Competitor WHERE BtrCompetitorID = {0}", this.BtrCompetitorID);
this.CompetitorID = Convert.ToInt64(objCompetitorId);
Debug.Assert(this.CompetitorID > 0);
}
this.IsModified = false;
}
}
public bool DeleteOldObjects()
{
try
{
using (IDbConnection conn = new NpgsqlConnection(DalStationSettings.Instance.ConnectionString.Replace(DalStationSettings.Instance.DatabaseName, ConnectionManager.SystemDatabaseName)))
{
conn.Open();
if (DatabaseManager.DoesDatabaseExist(conn, DalStationSettings.Instance.DatabaseName) == DatabaseManager.eExistResult.Exists)
{
if (DatabaseManager.OneOfRequiredTablesExist() != DatabaseManager.eExistResult.DoesNotExist)
{
ExcpHelper.ThrowIf(DatabaseManager.DeleteFromTables(), "DeleteOldObjects() ERROR. Cannot clear tables.");
ExcpHelper.ThrowIf(!DatabaseManager.DropTables(), "DeleteOldObjects() ERROR. Cannot drop tables.");
}
else
{
using (var conn2 = ConnectionManager.GetConnection())
{
//foreach (var sqlDelete in DatabaseManager.Schema.DeleteFromTablesStatement.Split(';'))
//{
// DataCopy.ExecuteScalar(conn2, null, sqlDelete);
//}
DataCopy.ExecuteScalar(conn2, null, "Delete From UpdateFileEntry Where DataSyncCacheType not in('" + eDataSyncCacheType.Resources + "','" + eDataSyncCacheType.Statistic + "')");
foreach (var sqlDelete in DatabaseManager.Schema.DropTablesStatement.Split(';'))
{
DataCopy.ExecuteScalar(conn2, null, sqlDelete);
}
}
}
}
}
return(true);
}
catch (Exception e)
{
m_logger.Error(e.Message, e);
}
return(false);
}
public static bool DeleteFromTables()
{
bool bResult = false;
ConnectionManager.ProcessTransaction(delegate(IDbConnection conn, IDbTransaction transaction)
{
try
{
DataCopy.ExecuteScalar(conn, transaction, DatabaseManager.Schema.DeleteFromTablesStatement);
bResult = true;
}
catch (Exception excp)
{
m_logger.Error(ExcpHelper.FormatException(excp, "DeleteFromTables() ERROR"), excp);
throw;
}
});
return(bResult);
}
private void FillFromDatabase()
{
try
{
using (IDbConnection conn = ConnectionManager.GetConnection())
{
//FillFromDataTable<RelatedStringLn>(conn, "SELECT * FROM RelatedString", m_diAll.RelatedStringsByKey);
FillFromDataTable <TaggedStringLn>(conn, "SELECT * FROM strings", m_diAll.TaggedStrings);
FillFromDataTable <TimeTypeLn>(conn, "SELECT * FROM time_type", m_diAll.TimeTypes);
FillFromDataTable <ScoreTypeLn>(conn, "SELECT * FROM score_type", m_diAll.ScoreTypes);
FillFromDataTable <BetTypeLn>(conn, "SELECT * FROM bet_type", m_diAll.BetTypes);
FillFromDataTable <BetDomainTypeLn>(conn, "SELECT * FROM betdomain_type", m_diAll.BetDomainTypes);
FillFromDataTable <GroupLn>(conn, "SELECT * FROM Groups", m_diAll.Groups);
FillFromDataTable <CompetitorLn>(conn, "SELECT * FROM Competitor", m_diAll.Competitors);
FillFromDataTable <MatchLn>(conn, "SELECT * FROM Matches", m_diAll.Matches);
FillFromDataTable <CompetitorToOutrightLn>(conn, "SELECT * FROM competitor_to_outright", m_diAll.CompetitorsToOutright);
FillFromDataTable <LiveMatchInfoLn>(conn, "SELECT * FROM LiveMatchInfo", m_diAll.LiveMatchInfos);
FillFromDataTable <MatchToGroupLn>(conn, "SELECT * FROM MatchToGroup", m_diAll.MatchesToGroups);
FillFromDataTable <BetDomainLn>(conn, "SELECT * FROM BetDomain", m_diAll.BetDomains);
FillFromDataTable <OddLn>(conn, "SELECT * FROM Odd", m_diAll.Odds);
FillFromDataTable <MatchResultLn>(conn, "SELECT * FROM MatchResult", m_diAll.MatchResults);
FillFromDataTable <ResourceRepositoryLn>(conn, "SELECT * FROM ResourseRepository", m_diAll.Resources);
FillFromDataTable <ResourceAssignmentLn>(conn, "SELECT * FROM ResourceAssignment", m_diAll.ResourceAssignments);
FillFromDataTable <CompetitorInfosLn>(conn, "SELECT * FROM competitorinfos", m_diAll.CompetitorInfos);
FillFromDataTable <MatchInfosLn>(conn, "SELECT * FROM matchinfos", m_diAll.MatchInfos);
FillFromDataTable <TournamentInfosLn>(conn, "SELECT * FROM tournamentinfos", m_diAll.TournamentInfos);
FillFromDataTable <LiabilityLn>(conn, "SELECT * FROM conffactor", m_diAll.Liabilities);
FillFromDataTable <TournamentMatchLocksLn>(conn, "SELECT * FROM tournament_match_lock", m_diAll.TournamentMatchLocks);
FillFromDataTable <LanguageLn>(conn, "SELECT * FROM languages", m_diAll.Languages);
FillFromDataTable <MultistringGroupLn>(conn, "SELECT * FROM MultistringGroup", m_diAll.MultistringGroups);
}
long.TryParse(DataCopy.ExecuteScalar("select MAX(GroupId) FROM Groups").ToString(), out m_lNextGroupId);
}
catch (Exception excp)
{
m_logger.Excp(excp, "DatabaseCache.FillFromDatabase() ERROR");
throw;
}
}
private static void EnsureDatabaseImp(bool isTestMode)
{
string sDatabaseName = "";
string sConnectionString = "";
if (isTestMode)
{
sDatabaseName = "test_" + ConnectionManager.DatabaseName;
sConnectionString = ConnectionManager.ConnectionString.Replace(ConnectionManager.DatabaseName, sDatabaseName);
ConnectionManager.DatabaseName = sDatabaseName;
ConnectionManager.ConnectionString = sConnectionString;
}
else
{
sDatabaseName = ConnectionManager.DatabaseName;
sConnectionString = ConnectionManager.ConnectionString;
}
try
{
int counter = 0;
while (true)
{
try
{
using (IDbConnection conn = new NpgsqlConnection(sConnectionString.Replace(sDatabaseName, ConnectionManager.SystemDatabaseName)))
{
conn.Open();
}
break;
}
catch (Exception)
{
Thread.Sleep(1000);
counter++;
if (counter > 15)
{
throw;
}
}
}
using (IDbConnection conn = new NpgsqlConnection(sConnectionString.Replace(sDatabaseName, ConnectionManager.SystemDatabaseName)))
{
conn.Open();
eExistResult eerDatabase = DatabaseManager.DoesDatabaseExist(conn, sDatabaseName);
ExcpHelper.ThrowIf(eerDatabase == eExistResult.Error, "Cannot recognize if database '{0}' exists.", sDatabaseName);
if (eerDatabase == eExistResult.DoesNotExist)
{
try
{
DataCopy.ExecuteScalar(conn, null, DatabaseManager.Schema.CreateDatabaseStatement, sDatabaseName);
m_logger.InfoFormat("Database '{0}' Successfully created", sDatabaseName);
}
catch (Exception excp)
{
m_logger.Excp(excp, "EnsureDatabase() ERROR - cannot create {0} Database '{1}'", ConnectionManager.Dialect, sDatabaseName);
throw;
}
}
}
foreach (TableStatement ts in DatabaseManager.Schema.TableStatements)
{
using (IDbConnection conn = ConnectionManager.GetConnection())
{
eExistResult eerTable = DoesTableExist(conn, ts.TableName);
ExcpHelper.ThrowIf(eerTable == eExistResult.Error, "Cannot recognize if table '{0}' exists.", ts.TableName);
foreach (SqlStatement statement in ts.Statements)
{
if (statement.SqlExecuteType == eSqlExecuteType.Always || // Always
(eerTable == eExistResult.Exists && statement.SqlExecuteType == eSqlExecuteType.Exists) || // Table exists
(eerTable == eExistResult.DoesNotExist && statement.SqlExecuteType == eSqlExecuteType.DoesNotExist)) // Table does not exist
{
try
{
DataCopy.ExecuteScalar(statement.Statement);
m_logger.InfoFormat("{0} Successfully executed", statement);
}
catch (Exception excp)
{
m_logger.Excp(excp, "EnsureDatabase() ERROR - cannot execute {0} for {1} Database '{2}'", statement, ConnectionManager.Dialect, sDatabaseName);
throw;
}
}
else
{
m_logger.InfoFormat("{0} is skipped. Table {1}.", statement, eerTable);
}
}
}
}
}
catch (System.Exception excpInner)
{
string sError = string.Format("Cannot verify Server. Either Server is not installed or invalid priveleges ({0}).\r\n{1}\r\n{2}", sConnectionString, excpInner.Message, excpInner.StackTrace);
//MessageBox.Show(sError, "Station Start Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
throw new Exception(sError, excpInner);
}
}
