private static async Task <bool> CreateMapTable()
{
MySqlConnection connection = new MySqlConnection(_connection);
connection.Open();
// Construct the sql string based on the constants for table name, column names, and variable length values.
string sqlString = @$ "CREATE TABLE IF NOT EXISTS `{_mapSchema}`.`{Constants.MapDAOTableName}` (" +
$@"`{Constants.MapDAOHashColumn}` VARCHAR({Constants.DefaultHashCharacterLength}) NOT NULL," +
$@"`{Constants.MapDAOActualColumn}` LONGTEXT NOT NULL," +
$@"`{Constants.MapDAOoccurrencesColumn}` INT NOT NULL," +
$@"PRIMARY KEY(`{Constants.MapDAOHashColumn}`));" +
$@"INSERT INTO `{_mapSchema}`.`{Constants.MapDAOTableName}` (`{Constants.MapDAOHashColumn}`, `{Constants.MapDAOActualColumn}`, `{Constants.MapDAOoccurrencesColumn}`) VALUES('0', '1', '1');" +
$@"INSERT INTO `{_mapSchema}`.`{Constants.MapDAOTableName}` (`{Constants.MapDAOHashColumn}`, `{Constants.MapDAOActualColumn}`, `{Constants.MapDAOoccurrencesColumn}`) VALUES('1', '0', '1');";
// Create the commmand object and execute/dispose it asynchronously.
MySqlCommand command = new MySqlCommand(sqlString, connection);
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
await command.DisposeAsync().ConfigureAwait(false);
Environment.SetEnvironmentVariable("COUNT", (2).ToString(), EnvironmentVariableTarget.User);
connection.Close();
return(true);
}
/// <summary>
/// Executes the CREATE TABLE statement for the store table in the mysql schema.
/// </summary>
/// <returns>Task (bool)</returns>
private static async Task <bool> CreateStoreTable()
{
MySqlConnection connection = new MySqlConnection(_connection);
connection.Open();
// Construct the sql string based on the constants for table name, column names, and variable length values.
string sqlString = @$ "CREATE TABLE IF NOT EXISTS `{_exogredientSchema}`.`{Constants.StoreDAOTableName}` (" +
$@"`{Constants.StoreDAOStoreIdColumn}` INT NOT NULL AUTO_INCREMENT," +
$@"`{Constants.StoreDAOStoreNameColumn}` VARCHAR({Constants.StoreNameMaxCharacters}) NOT NULL," +
$@"`{Constants.StoreDAOLatitudeColumn}` DOUBLE NOT NULL," +
$@"`{Constants.StoreDAOLongitudeColumn}` DOUBLE NOT NULL," +
$@"`{Constants.StoreDAOPlaceIdColumn}` VARCHAR({Constants.PlaceIDCharacters}) NOT NULL," +
$@"`{Constants.StoreDAOStoreDescriptionColumn}` VARCHAR({Constants.StoreDescriptionMaxCharacters}) NOT NULL," +
$@"PRIMARY KEY(`{Constants.StoreDAOStoreIdColumn}`)," +
$@"UNIQUE INDEX geocode ({Constants.StoreDAOLatitudeColumn}, {Constants.StoreDAOLongitudeColumn}));";
// Create the commmand object and execute/dispose it asynchronously.
MySqlCommand command = new MySqlCommand(sqlString, connection);
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
await command.DisposeAsync().ConfigureAwait(false);
connection.Close();
return(true);
}
/// <summary>
/// Creates the ticket tables, including its enumeration tables
/// </summary>
/// <returns>Whether the tables were created successfully</returns>
private static async Task <bool> CreateTicketTables()
{
MySqlConnection connection = new MySqlConnection(_connection);
connection.Open();
string insertCategoriesQuery = BuildMultiSingleValueInsertStatement(Constants.TicketCategoryDAOTableName, new Constants.TicketCategories());
string insertStatusesQuery = BuildMultiSingleValueInsertStatement(Constants.TicketStatusDAOTableName, new Constants.TicketStatuses());
string insertFlagColorsQuery = BuildMultiSingleValueInsertStatement(Constants.TicketFlagColorDAOTableName, new Constants.TicketFlagColors());
StringBuilder sqlString = new StringBuilder($@"
-- Enumeration tables
CREATE TABLE IF NOT EXISTS `{Constants.TicketCategoryDAOTableName}` (
`{Constants.TicketDAOCategoryColumn}` VARCHAR({Constants.DefaultVarCharLength}) NOT NULL,
PRIMARY KEY (`{Constants.TicketDAOCategoryColumn}`)
);
CREATE TABLE IF NOT EXISTS `{Constants.TicketStatusDAOTableName}` (
`{Constants.TicketDAOStatusColumn}` VARCHAR({Constants.DefaultVarCharLength}) NOT NULL,
PRIMARY KEY (`{Constants.TicketDAOStatusColumn}`)
);
CREATE TABLE IF NOT EXISTS `{Constants.TicketFlagColorDAOTableName}` (
`{Constants.TicketDAOFlagColorColumn}` VARCHAR({Constants.DefaultVarCharLength}) NOT NULL,
PRIMARY KEY (`{Constants.TicketDAOFlagColorColumn}`)
);
CREATE TABLE IF NOT EXISTS `{Constants.TicketDAOTableName}` (
`{Constants.TicketDAOTicketIDColumn}` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`{Constants.TicketDAOSubmitTimestampColumn}` INT UNSIGNED NOT NULL,
`{Constants.TicketDAOCategoryColumn}` VARCHAR({Constants.DefaultVarCharLength}) NOT NULL,
`{Constants.TicketDAOStatusColumn}` VARCHAR({Constants.DefaultVarCharLength}) NOT NULL,
`{Constants.TicketDAOFlagColorColumn}` VARCHAR({Constants.DefaultVarCharLength}) NOT NULL,
`{Constants.TicketDAODescriptionColumn}` LONGTEXT NOT NULL,
`{Constants.TicketDAOIsReadColumn}` BOOLEAN NOT NULL DEFAULT 0,
PRIMARY KEY (`{Constants.TicketDAOTicketIDColumn}`),
FOREIGN KEY (`{Constants.TicketDAOCategoryColumn}`)
REFERENCES `{Constants.TicketCategoryDAOTableName}`(`{Constants.TicketDAOCategoryColumn}`),
FOREIGN KEY (`{Constants.TicketDAOStatusColumn}`)
REFERENCES `{Constants.TicketStatusDAOTableName}`(`{Constants.TicketDAOStatusColumn}`),
FOREIGN KEY (`{Constants.TicketDAOFlagColorColumn}`)
REFERENCES `{Constants.TicketFlagColorDAOTableName}`(`{Constants.TicketDAOFlagColorColumn}`)
);
{insertCategoriesQuery}
{insertStatusesQuery}
{insertFlagColorsQuery}
");
// Create the commmand object and execute/dispose it asynchronously.
MySqlCommand command = new MySqlCommand(sqlString.ToString(), connection);
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
await command.DisposeAsync().ConfigureAwait(false);
connection.Close();
return(true);
}
public City GetCity(string cityName, string cityZIP)
{
//Create a request to get the city
using MySqlCommand rqst = DataBaseManager.Connection.CreateCommand();
//Fill the request
rqst.CommandText = "SELECT ville_nom_reel, " +
"ville_code_postal, " +
"ville_longitude_deg, " +
"ville_latitude_deg " +
"FROM `villes_france_free` " +
$"WHERE UPPER(ville_nom_simple) = UPPER('{ cityName.Replace('\'', '-') }')" +
$"AND ville_code_postal = '{ cityZIP }'";
City city = null;
//Running the request
using (DbDataReader reader = rqst.ExecuteReader())
{
if (reader.HasRows)
{
//Count the number of records get by the request
//should be equal to 1
int rowCount = 1;
while (reader.Read())
{
//If rowCount >1
if (rowCount > 1)
{
//Free ressources
rqst.DisposeAsync();
reader.DisposeAsync();
//Raise error
throw new NotImplementedException("Too much records returned for the same City name and Zip code");
}
int cityNameOrd = reader.GetOrdinal("ville_nom_simple");
int longitudeOrd = reader.GetOrdinal("ville_nom_simple");
int latitudeOrd = reader.GetOrdinal("ville_nom_simple");
int zipOrd = reader.GetOrdinal("ville_code_postal");
string name = reader.GetString(cityNameOrd);
double longitude = reader.GetDouble(longitudeOrd);
double latitude = reader.GetDouble(latitudeOrd);
string cityZip = reader.GetString(zipOrd);
city = new City(name, cityZip, longitude, latitude);
rowCount++;
}
}
}
return(city);
}
/// <summary>
/// Executes the CREATE TABLE statement for the upload table in the mysql schema.
/// </summary>
/// <returns>Task (bool)</returns>
private static async Task <bool> CreateUploadTable()
{
MySqlConnection connection = new MySqlConnection(_connection);
connection.Open();
// Construct the sql string based on the constants for table name, column names, and variable length values.
string sqlString = @$ "CREATE TABLE `{_exogredientSchema}`.`{Constants.UploadDAOTableName}` (" +
$@"`{Constants.UploadDAOUploadIdColumn}` INT NOT NULL AUTO_INCREMENT," +
$@"`{Constants.UploadDAOPostTimeDateColumn}` TIMESTAMP NOT NULL," +
$@"`{Constants.UploadDAOUploaderColumn}` VARCHAR({Constants.MaximumUsernameCharacters}) NOT NULL," +
$@"`{Constants.UploadDAOStoreIdColumn}` INT NOT NULL," +
$@"`{Constants.UploadDAODescriptionColumn}` VARCHAR({Constants.MaximumUploadDescriptionCharacters}) NOT NULL," +
$@"`{Constants.UploadDAORatingColumn}` VARCHAR({Constants.MaxRatingDigits}) NOT NULL," +
$@"`{Constants.UploadDAOPhotoColumn}` VARCHAR({Constants.MaximumPhotoCharacters}) NOT NULL," +
$@"`{Constants.UploadDAOPriceColumn}` DOUBLE({Constants.MaximumPriceDigits},{Constants.PriceAccuracyDigits}) NOT NULL," +
$@"`{Constants.UploadDAOPriceUnitColumn}` VARCHAR({Constants.PriceUnitMaxCharacters}) NOT NULL," +
$@"`{Constants.UploadDAOIngredientNameColumn}` VARCHAR({Constants.MaximumIngredientNameCharacters}) NOT NULL," +
$@"`{Constants.UploadDAOUpvoteColumn}` INT NOT NULL," +
$@"`{Constants.UploadDAODownvoteColumn}` INT NOT NULL," +
$@"`{Constants.UploadDAOInProgressColumn}` TINYINT(1) NOT NULL," +
$@"`{Constants.UploadDAOCategoryColumn}` VARCHAR({Constants.MaximumCategoryCharacters}) NOT NULL," +
$@"PRIMARY KEY(`{Constants.UploadDAOUploadIdColumn}`)," +
$@"INDEX `{Constants.UploadDAOStoreIdColumn}_idx` (`{Constants.UploadDAOStoreIdColumn}` ASC) INVISIBLE," +
$@"CONSTRAINT `{Constants.UploadDAOUploaderColumn}`" +
$@" FOREIGN KEY (`{Constants.UploadDAOUploaderColumn}`)" +
$@" REFERENCES `{Constants.ExogredientSQLSchemaName}`.`{Constants.UserDAOtableName}` (`{Constants.UserDAOusernameColumn}`)" +
$@" ON DELETE NO ACTION" +
$@" ON UPDATE NO ACTION," +
$@"CONSTRAINT `{Constants.UploadDAOStoreIdColumn}`" +
$@" FOREIGN KEY (`{Constants.UploadDAOStoreIdColumn}`)" +
$@" REFERENCES `{Constants.ExogredientSQLSchemaName}`.`{Constants.StoreDAOTableName}` (`{Constants.StoreDAOStoreIdColumn}`)" +
$@" ON DELETE NO ACTION" +
$@" ON UPDATE NO ACTION);";
// Create the commmand object and execute/dispose it asynchronously.
MySqlCommand command = new MySqlCommand(sqlString, connection);
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
await command.DisposeAsync().ConfigureAwait(false);
return(true);
}
/// <summary>
/// Executes the CREATE TABLE statement for the anonymous_user table in the mysql schema.
/// </summary>
/// <returns>Task (bool)</returns>
private static async Task <bool> CreateAnonymousUserTable()
{
MySqlConnection connection = new MySqlConnection(_connection);
connection.Open();
// Construct the sql string based on the constants for table name, column names, and variable length values.
string sqlString = @$ "CREATE TABLE `{_exogredientSchema}`.`{Constants.AnonymousUserDAOTableName}` (" +
$@"`{Constants.AnonymousUserDAOIPColumn}` VARCHAR({(Constants.AnonymousUserDAOIsColumnMasked[Constants.AnonymousUserDAOIPColumn] ? Constants.DefaultHashCharacterLength : Constants.IPAddressLength)}) NOT NULL," +
$@"`{Constants.AnonymousUserDAOtimestampLockedColumn}` BIGINT NOT NULL," +
$@"`{Constants.AnonymousUserDAOregistrationFailuresColumn}` INT NOT NULL," +
$@"`{Constants.AnonymousUserDAOlastRegFailTimestampColumn}` BIGINT NOT NULL," +
$@"PRIMARY KEY(`{Constants.AnonymousUserDAOIPColumn}`));";
// Create the commmand object and execute/dispose it asynchronously.
MySqlCommand command = new MySqlCommand(sqlString, connection);
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
await command.DisposeAsync().ConfigureAwait(false);
return(true);
}
public async Task QueryAsync(string query, OrderedDictionary args = null)
{
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
try
{
await conn.OpenAsync();
using (var cmd = new MySqlCommand(query, conn))
{
if (args != null)
{
IDictionaryEnumerator reader = args.GetEnumerator();
while (reader.MoveNext())
{
cmd.Parameters.AddWithValue(reader.Key.ToString(), reader.Value);
}
}
await cmd.ExecuteNonQueryAsync();
await cmd.DisposeAsync();
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
finally
{
await conn.CloseAsync();
await conn.DisposeAsync();
}
}
}
/// <summary>
/// Executes the CREATE TABLE statement for the user table in the mysql schema.
/// </summary>
/// <returns>Task (bool)</returns>
private static async Task <bool> CreateUserTable()
{
MySqlConnection connection = new MySqlConnection(_connection);
connection.Open();
// Construct the sql string based on the constants for table name, column names, and variable length values.
string sqlString = @$ "CREATE TABLE IF NOT EXISTS `{_exogredientSchema}`.`{Constants.UserDAOtableName}` (" +
$@"`{Constants.UserDAOusernameColumn}` VARCHAR({(Constants.UserDAOIsColumnMasked[Constants.UserDAOusernameColumn] ? Constants.DefaultHashCharacterLength : Constants.MaximumUsernameCharacters)}) NOT NULL," +
$@"`{Constants.UserDAOnameColumn}` VARCHAR({(Constants.UserDAOIsColumnMasked[Constants.UserDAOnameColumn] ? Constants.DefaultHashCharacterLength : (Constants.MaximumFirstNameCharacters + Constants.MaximumLastNameCharacters + 1))}) NOT NULL," +
$@"`{Constants.UserDAOemailColumn}` VARCHAR({(Constants.UserDAOIsColumnMasked[Constants.UserDAOemailColumn] ? Constants.DefaultHashCharacterLength : Constants.MaximumEmailCharacters)}) NOT NULL," +
$@"`{Constants.UserDAOphoneNumberColumn}` VARCHAR({(Constants.UserDAOIsColumnMasked[Constants.UserDAOphoneNumberColumn] ? Constants.DefaultHashCharacterLength : Constants.PhoneNumberCharacterLength)}) NOT NULL," +
$@"`{Constants.UserDAOpasswordColumn}` VARCHAR({Constants.DefaultHashCharacterLength}) NOT NULL," +
$@"`{Constants.UserDAOdisabledColumn}` TINYINT(1) NOT NULL," +
$@"`{Constants.UserDAOuserTypeColumn}` VARCHAR({(Constants.UserDAOIsColumnMasked[Constants.UserDAOuserTypeColumn] ? Constants.DefaultHashCharacterLength : Constants.MaximumUserTypeLength)}) NOT NULL," +
$@"`{Constants.UserDAOsaltColumn}` VARCHAR({Constants.DefaultSaltLength}) NOT NULL," +
$@"`{Constants.UserDAOtempTimestampColumn}` BIGINT NOT NULL," +
$@"`{Constants.UserDAOemailCodeColumn}` VARCHAR({(Constants.UserDAOIsColumnMasked[Constants.UserDAOemailCodeColumn] ? Constants.DefaultHashCharacterLength : Constants.EmailCodeLength)}) NOT NULL," +
$@"`{Constants.UserDAOemailCodeTimestampColumn}` BIGINT NOT NULL," +
$@"`{Constants.UserDAOloginFailuresColumn}` INT NOT NULL," +
$@"`{Constants.UserDAOlastLoginFailTimestampColumn}` BIGINT NOT NULL," +
$@"`{Constants.UserDAOemailCodeFailuresColumn}` INT NOT NULL," +
$@"`{Constants.UserDAOphoneCodeFailuresColumn}` INT NOT NULL," +
$@"PRIMARY KEY (`{Constants.UserDAOusernameColumn}`)," +
$@"UNIQUE INDEX `{Constants.UserDAOemailColumn}_UNIQUE` (`{Constants.UserDAOemailColumn}` ASC) VISIBLE," +
$@"UNIQUE INDEX `{Constants.UserDAOphoneNumberColumn}_UNIQUE` (`{Constants.UserDAOphoneNumberColumn}` ASC) VISIBLE);";
// Create the commmand object and execute/dispose it asynchronously.
MySqlCommand command = new MySqlCommand(sqlString, connection);
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
await command.DisposeAsync().ConfigureAwait(false);
connection.Close();
return(true);
}
