/// <summary>
/// Creates the tables on the database
/// </summary>
private void CreateDatabaseTables()
{
databaseDriver.Query(
@"CREATE TABLE IF NOT EXISTS `users` (" +
@"`userid` INTEGER PRIMARY KEY AUTOINCREMENT," +
@"`email` TEXT NOT NULL," +
@"`password` TEXT NOT NULL," +
@"`userstatus` INTEGER DEFAULT(0)" +
@")"
);
databaseDriver.Query(
@"CREATE TABLE IF NOT EXISTS `profiles` (" +
@"`profileid` INTEGER PRIMARY KEY AUTOINCREMENT," +
@"`userid` INTEGER NOT NULL," +
@"`sesskey` INTEGER NOT NULL," +
@"`uniquenick` TEXT NOT NULL," +
@"`nick` TEXT NOT NULL," +
@"`firstname` TEXT DEFAULT NULL," +
@"`lastname` TEXT DEFAULT NULL," +
@"`publicmask` TEXT DEFAULT 0," +
@"`latitude` REAL," +
@"`longitude` REAL," +
@"`aim` TEXT DEFAULT NULL," +
@"`picture` INTEGER DEFAULT 0," +
@"`occupationid` INTEGER DEFAULT 0," +
@"`incomeid` INTEGER DEFUALT 0," +
@"`industryid` INTEGER DEFAULT 0," +
@"`marriedid` INTEGER DEFAULT 0" +
@"`childcount` INTEGER DEFAULT 0," +
@"`interests1` INTEGER DEFAULT 0," +
@"`ownership1` INTEGER DEFAULT 0," +
@"`connectiontype` INTEGER DEFAULT 0," +
@"`sex` TEXT DEFAULT('PAT')," +
@"`zipcode` TEXT DEFAULT('00000')," +
@"`countrycode` TEXT DEFAULT NULL," +
@"`homepage` TEXT DEFAULT NULL," +
@"`birthday` INTEGER DEFAULT 0," +
@"`birthmonth` INTEGER DEFAULT 0," +
@"`birthyear` INTEGER DEFAULT 0," +
@"`location` TEXT DEFAULT NULL," +
@"`icq` INTEGER DEFAULT 0," +
@"`status` INTEGER DEFAULT 0," +
@"`lastip` TEXT," +
@"`lastonline` INTEGER" +
@")"
);
databaseDriver.Query(@"INSERT INTO users(id, email, password, status) VALUES(1, '*****@*****.**', '4c3cbcadf7b8a9ae2932afc00560a0d6', 1)");
databaseDriver.Query(@"INSERT INTO `profiles` (`profileid`, `userid`, `uniquenick`, `nick`, `firstname`, `lastname`, `publicmask`, `deleted`, `latitude`, `longitude`, `aim`, `picture`, `occupationid`, `incomeid`, `industryid`, `marriedid`, `childcount`, `interests1`, `ownership1`, `connectiontype`, `sex`, `zipcode`, `countrycode`, `homepage`, `birthday`, `birthmonth`, `birthyear`, `location`, `icq`) VALUES
(1, 1, 'SpyGuy', 'SpyGuy', 'Spy', 'Guy', 0, 0, 40.7142, -74.0064, 'spyguy', 0, 0, 0, 0, 0, 0, 0, 0, 3, 'MALE', '10001', 'US', 'https://www.gamespy.com/', 20, 3, 1980, 'New York', 0)" );
}
/// <summary>
/// Gets the country code for a string IP address
/// </summary>
/// <param name="IP"></param>
/// <returns></returns>
public static string GetCountryCode(IPAddress IP)
{
// Return default config Country Code
if (IPAddress.IsLoopback(IP) || HttpServer.LocalIPs.Contains(IP))
{
return(Program.Config.ASP_LocalIpCountryCode);
}
try
{
using (DatabaseDriver Driver = new DatabaseDriver(DatabaseEngine.Sqlite, ConnectionString))
{
// Fetch country code from Ip2Nation
Driver.Connect();
List <Dictionary <string, object> > Rows = Driver.Query(
"SELECT country FROM ip2nation WHERE ip < @P0 ORDER BY ip DESC LIMIT 1",
Networking.IP2Long(IP.ToString())
);
string CC = (Rows.Count == 0) ? "xx" : Rows[0]["country"].ToString();
// Fix country!
return((CC == "xx" || CC == "01") ? Program.Config.ASP_LocalIpCountryCode : CC);
}
}
catch
{
return(Program.Config.ASP_LocalIpCountryCode);
}
}
public void VerifyOrdersSqliteNoDriverString()
{
using (DatabaseDriver driver = new DatabaseDriver("SQLITE", $"Data Source={ GetDByPath() }"))
{
var orders = driver.Query("select * from orders").ToList();
Assert.AreEqual(11, orders.Count);
}
}
public void VerifyProceduresQueryWithoutResultNoDriver()
{
DatabaseDriver driver = new DatabaseDriver(DatabaseConfig.GetProviderTypeString(), DatabaseConfig.GetConnectionString());
var result = driver.Query("getStateAbbrevMatch", new { StateAbbreviation = "ZZ" }, commandType: CommandType.StoredProcedure);
Assert.AreEqual(0, result.Count(), "Expected 0 state abbreviation to be returned.");
}
public void VerifyStateTableExistsNoDriver()
{
DatabaseDriver driver = new DatabaseDriver();
var table = driver.Query("SELECT * FROM information_schema.tables");
Assert.IsTrue(table.Any(n => n.TABLE_NAME.Equals("States")));
}
public void VerifyOrdersSqliteNoDriverDefault()
{
using (DatabaseDriver driver = new DatabaseDriver())
{
var orders = driver.Query("select * from orders").ToList();
Assert.AreEqual(11, orders.Count);
}
}
public void VerifyStateTableHasCorrectNumberOfRecordsNoDriver()
{
DatabaseDriver driver = new DatabaseDriver();
var table = driver.Query("SELECT * FROM States").ToList();
// Our database only has 49 states
Assert.AreEqual(49, table.Count, "Expected 49 states.");
}
/// <summary>
/// Do database setup for test run
/// </summary>
// [ClassInitialize] - Disabled because this step will fail as the template does not include access to a test database
public static void TestSetup(TestContext context)
{
// Do database setup
using (DatabaseDriver wrapper = new DatabaseDriver(DatabaseConfig.GetProviderTypeString(), DatabaseConfig.GetConnectionString()))
{
var result = wrapper.Query("getStateAbbrevMatch", new { StateAbbreviation = "MN" }, commandType: CommandType.StoredProcedure);
Assert.AreEqual(1, result.Count(), "Expected 1 state abbreviation to be returned.");
}
}
public void VerifyDataTableColumnsCount()
{
var states = DatabaseDriver.Query("SELECT * FROM States").ToList();
var statesTable = DatabaseUtils.ToDataTable(states);
// Validate Column Count
Assert.AreEqual(3, statesTable.Columns.Count);
Assert.AreEqual(((IDictionary <string, object>)states.First()).Keys.Count, statesTable.Columns.Count);
}
public void VerifyDataTableCountMatch()
{
var states = DatabaseDriver.Query("SELECT * FROM States").ToList();
var statesTable = DatabaseUtils.ToDataTable(states);
// Our database only has 49 states
Assert.AreEqual(states.Count, statesTable.Rows.Count, "Expected 49 states.");
Assert.AreEqual(49, statesTable.Rows.Count, "Expected 49 states.");
}
public static Dictionary <string, object> GetProfileInfo(DatabaseDriver databaseDriver, uint id)
{
var Rows = databaseDriver.Query("SELECT profiles.profileid, profiles.firstname, profiles.lastname, profiles.publicmask, profiles.latitude, profiles.longitude, " +
"profiles.aim, profiles.picture, profiles.occupationid, profiles.incomeid, profiles.industryid, profiles.marriedid, profiles.childcount, profiles.interests1, " +
@"profiles.ownership1, profiles.connectiontype, profiles.sex, profiles.zipcode, profiles.countrycode, profiles.homepage, profiles.birthday, profiles.birthmonth, " +
@"profiles.birthyear, profiles.location, profiles.icq, profiles.status, profiles.nick, profiles.uniquenick, users.email FROM profiles " +
@"INNER JOIN users ON profiles.userid = users.userid WHERE profileid=@P0", id);
return((Rows.Count == 0) ? null : Rows[0]);
}
protected static Dictionary <string, object> GetUserDataReal(DatabaseDriver databaseDriver, string AppendFirst, string SecondAppend, string _P0, string _P1)
{
var Rows = databaseDriver.Query("SELECT profiles.profileid, profiles.firstname, profiles.lastname, profiles.publicmask, profiles.latitude, profiles.longitude, " +
"profiles.aim, profiles.picture, profiles.occupationid, profiles.incomeid, profiles.industryid, profiles.marriedid, profiles.childcount, profiles.interests1, " +
@"profiles.ownership1, profiles.connectiontype, profiles.sex, profiles.zipcode, profiles.countrycode, profiles.homepage, profiles.birthday, profiles.birthmonth, " +
@"profiles.birthyear, profiles.location, profiles.icq, profiles.status, users.password, users.userstatus " + AppendFirst +
" FROM profiles INNER JOIN users ON profiles.userid = users.userid WHERE " + SecondAppend, _P0, _P1);
return((Rows.Count == 0) ? null : Rows[0]);
}
/// <summary>
/// Creates a new Gamespy Account
/// </summary>
/// <remarks>Used by the login server when a create account request is made</remarks>
/// <param name="databaseDriver">The database connection to use</param>
/// <param name="Nick">The Account Name</param>
/// <param name="Pass">The UN-HASHED Account Password</param>
/// <param name="Email">The Account Email Address</param>
/// <param name="Country">The Country Code for this Account</param>
/// <param name="UniqueNick">The unique nickname for this Account</param>
/// <returns>Returns the Player ID if sucessful, 0 otherwise</returns>
public static uint CreateUser(DatabaseDriver databaseDriver, string Nick, string Pass, string Email, string Country, string UniqueNick)
{
databaseDriver.Execute("INSERT INTO users(email, password) VALUES(@P0, @P1)", Email, StringExtensions.GetMD5Hash(Pass));
var Rows = databaseDriver.Query("SELECT userid FROM users WHERE email=@P0 and password=@P1", Email, Pass);
if (Rows.Count < 1)
{
return(0);
}
databaseDriver.Execute("INSERT INTO profiles(userid, nick, uniquenick, countrycode) VALUES(@P0, @P1, @P2, @P3)", Rows[0]["userid"], Nick, UniqueNick, Country);
Rows = databaseDriver.Query("SELECT profileid FROM profiles WHERE uniquenick=@P0", UniqueNick);
if (Rows.Count < 1)
{
return(0);
}
return(uint.Parse(Rows[0]["profileid"].ToString()));
}
public void VerifyStateTableHasCorrectNumberOfRecordsNoDriverWithModels()
{
DatabaseDriver driver = new DatabaseDriver();
var states = driver.Query <States>("SELECT * FROM States").ToList();
// Our database only has 49 states
Assert.AreEqual(49, states.Count, "Expected 49 states.");
Assert.AreNotEqual(string.Empty, states.First().StateAbbreviation, "Expected nonempty state abbreviation.");
}
public void VerifyOrdersSqliteNoDriverFunction()
{
using (SqliteConnection connection = new SqliteConnection(DatabaseConfig.GetConnectionString()))
{
SQLitePCL.Batteries.Init();
connection.Open();
DatabaseDriver driver = new DatabaseDriver(connection);
var orders = driver.Query("select * from orders").ToList();
Assert.AreEqual(11, orders.Count);
}
}
public void VerifyOrdersSqliteNoDriverString()
{
// Override the configuration
var overrides = new Dictionary <string, string>()
{
{ "DataBaseProviderType", "SQLITE" },
{ "DataBaseConnectionString", $"Data Source={ this.GetDByPath() }" },
};
Config.AddTestSettingValues(overrides, "DatabaseMaqs", true);
DatabaseDriver driver = new DatabaseDriver(DatabaseConfig.GetProviderTypeString(), DatabaseConfig.GetConnectionString());
var orders = driver.Query("select * from orders").ToList();
Assert.AreEqual(11, orders.Count);
}
public void VerifyDataTableDataTypesMatch()
{
var datatypes = DatabaseDriver.Query("SELECT * FROM DataType").ToList();
var datatypesTable = DatabaseUtils.ToDataTable(datatypes);
Assert.AreEqual(datatypesTable.Rows[0].ItemArray[0].GetType(), typeof(long));
Assert.AreEqual(datatypesTable.Rows[0].ItemArray[1].GetType(), typeof(bool));
Assert.AreEqual(datatypesTable.Rows[0].ItemArray[2].GetType(), typeof(string));
Assert.AreEqual(datatypesTable.Rows[0].ItemArray[3].GetType(), typeof(DateTime));
Assert.AreEqual(datatypesTable.Rows[0].ItemArray[4].GetType(), typeof(DateTime));
Assert.AreEqual(datatypesTable.Rows[0].ItemArray[5].GetType(), typeof(double));
Assert.AreEqual(datatypesTable.Rows[0].ItemArray[6].GetType(), typeof(int));
Assert.AreEqual(datatypesTable.Rows[0].ItemArray[7].GetType(), typeof(string));
Assert.AreEqual(datatypesTable.Rows[0].ItemArray[8].GetType(), typeof(string));
Assert.AreEqual(datatypesTable.Rows[0].ItemArray[9].GetType(), typeof(string));
Assert.AreEqual(datatypesTable.Rows[0].ItemArray[10].GetType(), typeof(decimal));
Assert.AreEqual(datatypesTable.Rows[0].ItemArray[11].GetType(), typeof(DBNull));
}
public void VerifyDataTableColumnsMatch()
{
var states = DatabaseDriver.Query("SELECT * FROM States").ToList();
IDictionary <string, object> statesColumns = states.First();
var statesTable = DatabaseUtils.ToDataTable(states);
// Loop through rows and validate
for (int i = 0; i < statesTable.Columns.Count; i++)
{
var dynamicRowKey = statesColumns.Keys.ToList()[i];
var dynamicRowType = statesColumns[dynamicRowKey].GetType();
// Check name
Assert.AreEqual(statesTable.Columns[i].ColumnName, dynamicRowKey);
// Check Data Type
Assert.AreEqual(statesTable.Columns[i].DataType, dynamicRowType);
}
}
public void VerifyDataTableValuesMatch()
{
var states = DatabaseDriver.Query("SELECT * FROM States").ToList();
var statesTable = DatabaseUtils.ToDataTable(states);
// Loop through rows and validate
for (int i = 0; i < states.Count; i++)
{
DataRow dataTableRow = statesTable.Rows[i];
IDictionary <string, object> dynamicRow = states[i];
foreach (var key in dynamicRow.Keys)
{
var dynamicValue = dynamicRow[key];
var dataTablevalue = dataTableRow[key];
Assert.AreEqual(dynamicValue, dataTablevalue);
}
}
}
/// <summary>
/// Gets the country code for a string IP address
/// </summary>
/// <param name="IP"></param>
/// <returns></returns>
public static string GetCountryCode(IPAddress IP)
{
try
{
using (DatabaseDriver Driver = new DatabaseDriver("Sqlite", ConnectionString))
{
// Fetch country code from Ip2Nation
Driver.Connect();
List <Dictionary <string, object> > Rows = Driver.Query(
"SELECT country FROM ip2nation WHERE ip < @P0 ORDER BY ip DESC LIMIT 1",
IP2Long(IP.ToString())
);
return((Rows.Count == 0) ? "??" : Rows[0]["country"].ToString());
}
}
catch
{
return("??");
}
}
/// <summary>
/// Fethces the full country name from a country code supplied from GetCountryCode()
/// </summary>
/// <param name="Code"></param>
/// <returns></returns>
public static string GetCountyNameFromCode(string Code)
{
try
{
using (DatabaseDriver Driver = new DatabaseDriver("Sqlite", ConnectionString))
{
// Fetch country code from Ip2Nation
Driver.Connect();
List <Dictionary <string, object> > Rows = Driver.Query(
"SELECT country FROM ip2nationcountries WHERE iso_code_2 = @P0", Code.ToUpper()
);
return((Rows.Count == 0) ? Code: Rows[0]["country"].ToString());
}
}
catch
{
return(Code);
}
}
public void VerifyDataTableColumnsNullDecimalValue()
{
var states = DatabaseDriver.Query("select City.CityPopulation from States left Join Cities as City on City.CityName like 'St. Paul' and City.CityId = States.StateId").ToList();
var statesTable = DatabaseUtils.ToDataTable(states);
var found = false;
// Loop through rows and validate
for (int i = 0; i < statesTable.Rows.Count; i++)
{
// Check Data Type
if (statesTable.Rows[i].ItemArray[0] != null && !(statesTable.Rows[i].ItemArray[0] is DBNull))
{
Type dataType = statesTable.Rows[i].ItemArray[0].GetType();
Assert.AreEqual(dataType, typeof(decimal));
found = true;
}
}
Assert.IsTrue(found, "Did not find any non null values. Verify the query is correct.");
}
public void VerifyOrdersSqliteNoDriverFunction()
{
// Override the configuration
var overrides =
new Dictionary <string, string>()
{
{ "DataBaseConnectionString", $"Data Source={this.GetDByPath()}" },
};
Config.AddTestSettingValues(overrides, "DatabaseMaqs");
using (SqliteConnection connection = new SqliteConnection(DatabaseConfig.GetConnectionString()))
{
SQLitePCL.Batteries.Init();
connection.Open();
DatabaseDriver driver = new DatabaseDriver(connection);
var orders = driver.Query("select * from orders").ToList();
Assert.AreEqual(11, orders.Count);
}
}
private void KeepAliveQuery(object sender, ElapsedEventArgs e)
{
_db.Query("SELECT COUNT(*) FROM users");
Logging.LogWriter.Log.Write("[DB] keep alive sended!", Logging.LogLevel.Info);
}
public static bool UserExists(DatabaseDriver databaseDriver, string Nick)
{
return(databaseDriver.Query("SELECT profileid FROM profiles WHERE `nickname`=@P0", Nick).Count != 0);
}
/// <summary>
/// Performs an SQL query on the Database directly. This is used internally, but can be also used
/// </summary>
/// <param name="query">The SQL Query</param>
/// <returns>DatabaseResult</returns>
public async Task <DatabaseResult <T> > Query <T>(string query) where T : new()
{
object data = await DatabaseDriver.Query(query);
return(new DatabaseResult <T>(data));
}
