public static void UpgradeSchemata()
{
int currentDbVersion = SwarmDb.DbVersion;
int expectedDbVersion = SwarmDb.DbVersionExpected;
string sql;
bool upgraded = false;
while (currentDbVersion < expectedDbVersion)
{
currentDbVersion++;
string fileName = String.Format("http://packages.swarmops.com/schemata/upgrade-{0:D4}.sql",
currentDbVersion);
using (WebClient client = new WebClient())
{
sql = client.DownloadString(fileName);
}
string[] sqlCommands = sql.Split('#');
// in the file, the commands are split by a single # sign. (Semicolons are an integral part of storedprocs, so they can't be used.)
foreach (string sqlCommand in sqlCommands)
{
try
{
SwarmDb.GetDatabaseForAdmin().ExecuteAdminCommand(sqlCommand.Trim());
}
catch (MySqlException exception)
{
SwarmDb.GetDatabaseForWriting()
.CreateExceptionLogEntry(DateTime.UtcNow, "DatabaseUpgrade", exception);
// Continue processing after logging error.
// TODO: Throw and abort? Tricky decision
}
}
upgraded = true;
SwarmDb.GetDatabaseForWriting()
.SetKeyValue("DbVersion", currentDbVersion.ToString(CultureInfo.InvariantCulture));
// Increment after each successful run
}
if (upgraded)
{
try
{
OutboundComm.CreateNotification(null, NotificationResource.System_DatabaseSchemaUpgraded);
}
catch (ArgumentException)
{
// this is ok - if we're in the install process, person 1 or other notification targets won't exist yet
}
}
}
/// <summary>
/// Feeds a newly-created database with an empty structure. Do not use on populated databases; it's like a Genesis
/// Device.
/// </summary>
public static void FirstInitialization()
{
// Security check: make sure we throw an exception if we try to get person 1.
bool initialized = false;
Person personOne = null;
string sql = string.Empty;
try
{
personOne = Person.FromIdentity(1);
initialized = true;
}
catch (Exception)
{
// Do nothing, the exception is expected
}
if (initialized && personOne != null)
// double checks in case Database behavior should change to return null rather than throw later
{
throw new InvalidOperationException(
"Cannot run Genesis Device style initalization on already-populated database");
}
using (WebClient client = new WebClient())
{
sql = client.DownloadString("http://packages.swarmops.com/schemata/initialize.sql");
// Hardcoded as security measure - don't want to pass arbitrary sql in from web layer
}
string[] sqlCommands = sql.Split('#');
// in the file, the commands are split by a single # sign. (Semicolons are an integral part of storedprocs, so they can't be used.)
foreach (string sqlCommand in sqlCommands)
{
SwarmDb.GetDatabaseForAdmin().ExecuteAdminCommand(sqlCommand.Trim());
}
SwarmDb.GetDatabaseForWriting().SetKeyValue("DbVersion", "1"); // We're at baseline
}
public static void UpgradeSchemata()
{
int currentDbVersion = SwarmDb.DbVersion;
int expectedDbVersion = SwarmDb.DbVersionExpected;
string sql;
bool upgraded = false;
if (currentDbVersion < expectedDbVersion)
{
Console.WriteLine("Swarmops: Current DB version is {0}, but expected is {1}. A schema upgrade will take place.", currentDbVersion, expectedDbVersion);
}
while (currentDbVersion < expectedDbVersion)
{
currentDbVersion++;
Console.Write("Schema {0} diff: Fetching...", currentDbVersion);
string fileName = String.Format("http://packages.swarmops.com/schemata/upgrade-{0:D4}.sql",
currentDbVersion);
try
{
using (WebClient client = new WebClient())
{
sql = client.DownloadString(fileName);
}
}
catch (Exception outerException)
{
Console.WriteLine(" trying fallback...");
// Because Mono installs with an insufficient certificate store, we must disable certificate checking before accessing github
SupportFunctions.DisableSslCertificateChecks();
fileName = String.Format("https://raw.githubusercontent.com/Swarmops/Swarmops/master/Database/Schemata/upgrade-{0:D4}.sql",
currentDbVersion);
try
{
using (WebClient client = new WebClient())
{
sql = client.DownloadString(fileName);
}
}
catch (Exception middleException)
{
try
{
OutboundComm.CreateNotification(null, NotificationResource.System_DatabaseUpgradeFailed);
}
catch (ArgumentException)
{
// if this happens during setup:
throw new Exception("Failed fetching upgrade packages:\r\n" + middleException.ToString() +
"\r\n" + outerException.ToString());
}
Console.WriteLine(" FAILED! Aborting.");
return;
}
}
string[] sqlCommands = sql.Split('#');
// in the file, the commands are split by a single # sign. (Semicolons are an integral part of storedprocs, so they can't be used.)
Console.Write(" applying...");
foreach (string sqlCommand in sqlCommands)
{
try
{
string trimmedCommand = sqlCommand.Trim().TrimEnd(';').Trim(); // removes whitespace first, then any ; at the end (if left in by mistake)
if (!String.IsNullOrWhiteSpace(trimmedCommand))
{
SwarmDb.GetDatabaseForAdmin().ExecuteAdminCommand(trimmedCommand);
}
}
catch (MySqlException exception)
{
SwarmDb.GetDatabaseForWriting()
.CreateExceptionLogEntry(DateTime.UtcNow, "DatabaseUpgrade",
new Exception(string.Format("Exception upgrading to Db{0:D4}", currentDbVersion),
exception));
Console.Write(" EXCEPTION (see log)!");
// Continue processing after logging error.
// TODO: Throw and abort? Tricky decision
}
}
upgraded = true;
SwarmDb.GetDatabaseForWriting()
.SetKeyValue("DbVersion", currentDbVersion.ToString(CultureInfo.InvariantCulture));
// Increment after each successful run
Console.WriteLine(" done.");
}
if (upgraded)
{
Console.WriteLine("Swarmops database schema upgrade completed.\r\n");
try
{
OutboundComm.CreateNotification(null, NotificationResource.System_DatabaseSchemaUpgraded);
}
catch (ArgumentException)
{
// this is ok - if we're in the install process, person 1 or other notification targets won't exist yet
}
}
}
public static void PrimeCountry(string countryCode)
{
Country country = Country.FromCode(countryCode);
if (country.GeographyId != Geography.RootIdentity)
{
// already initialized
return;
}
GeographyDataLoader geoDataLoader = new GeographyDataLoader();
// This next part has been hardened against transient network failures, up to 10 retries
int retries = 0;
bool networkSuccess = false;
MasterGeography geography = null;
MasterCity[] cities = null;
MasterPostalCode[] postalCodes = null;
while (!networkSuccess)
{
try
{
geography = geoDataLoader.GetGeographyForCountry(countryCode);
cities = geoDataLoader.GetCitiesForCountry(countryCode);
postalCodes = geoDataLoader.GetPostalCodesForCountry(countryCode);
networkSuccess = true;
}
catch (Exception)
{
if (retries >= 10)
{
throw;
}
retries++;
Thread.Sleep(5000); // wait five seconds for network conditions to clear
}
}
// ID Translation lists
Dictionary <int, int> geographyIdTranslation = new Dictionary <int, int>();
Dictionary <int, int> cityIdTranslation = new Dictionary <int, int>();
Dictionary <int, bool> cityIdsUsedLookup = new Dictionary <int, bool>();
// Create the country's root geography
int countryRootGeographyId = SwarmDb.GetDatabaseForWriting().CreateGeography(geography.Name,
Geography.RootIdentity);
geographyIdTranslation[geography.GeographyId] = countryRootGeographyId;
SwarmDb.GetDatabaseForWriting().SetCountryGeographyId(country.Identity,
countryRootGeographyId);
int count = 0;
int total = InitDatabaseThreadCountGeographyChildren(geography.Children);
InitDatabaseThreadCreateGeographyChildren(geography.Children, countryRootGeographyId,
ref geographyIdTranslation, ref count, total);
// Find which cities are actually used
foreach (MasterPostalCode postalCode in postalCodes)
{
cityIdsUsedLookup[postalCode.CityId] = true;
}
GuidCache.Set("DbInitProgress", "(finalizing)");
// Insert cities
int newCountryId = country.Identity;
int cityIdHighwater =
SwarmDb.GetDatabaseForAdmin().ExecuteAdminCommandScalar("SELECT Max(CityId) FROM Cities;");
StringBuilder sqlCityBuild =
new StringBuilder("INSERT INTO Cities (CityName, GeographyId, CountryId, Comment) VALUES ", 65536);
bool insertComma = false;
foreach (MasterCity city in cities)
{
if (!geographyIdTranslation.ContainsKey(city.GeographyId))
{
cityIdsUsedLookup[city.CityId] = false; // force non-use of invalid city
}
if ((cityIdsUsedLookup.ContainsKey(city.CityId) && cityIdsUsedLookup[city.CityId]) || country.PostalCodeLength == 0)
{
int newGeographyId = geographyIdTranslation[city.GeographyId];
if (insertComma)
{
sqlCityBuild.Append(",");
}
sqlCityBuild.Append("('" + city.Name.Replace("'", "\\'") + "'," + newGeographyId + "," +
newCountryId + ",'')");
insertComma = true;
cityIdTranslation[city.CityId] = ++cityIdHighwater; // Note that we assume the assigned ID here.
}
}
sqlCityBuild.Append(";");
SwarmDb.GetDatabaseForAdmin().ExecuteAdminCommand(sqlCityBuild.ToString());
// Inserts all cities in one bulk op, to save roundtrips
// Insert postal codes, if any
if (postalCodes.Length > 0)
{
StringBuilder sqlBuild =
new StringBuilder("INSERT INTO PostalCodes (PostalCode, CityId, CountryId) VALUES ", 65536);
insertComma = false;
foreach (MasterPostalCode postalCode in postalCodes)
{
if (cityIdsUsedLookup[postalCode.CityId] == false)
{
// Remnants of invalid pointers
continue;
}
int newCityId = cityIdTranslation[postalCode.CityId];
if (insertComma)
{
sqlBuild.Append(",");
}
sqlBuild.Append("('" + postalCode.PostalCode.Replace("'", "\\'") + "'," + newCityId + "," +
newCountryId + ")");
insertComma = true;
}
sqlBuild.Append(";");
// Insert all postal codes in one bulk op, to save roundtrips
SwarmDb.GetDatabaseForAdmin().ExecuteAdminCommand(sqlBuild.ToString());
}
}
/// <summary>
/// This function copies the schemas and geography data off an existing Swarmops installation. Runs in its own thread.
/// </summary>
public static void InitDatabaseThread()
{
// Ignore the session object, that method of sharing data didn't work, but a static variable did.
_initProgress = 1;
_initMessage = "Loading schema from Swarmops servers; creating tables and procs...";
Thread.Sleep(100);
try
{
// Get the schema and initialize the database structures. Requires ADMIN access to database.
Swarmops.Logic.Support.DatabaseMaintenance.FirstInitialization();
_initProgress = 6;
_initMessage = "Applying all post-baseline database schema upgrades...";
Swarmops.Logic.Support.DatabaseMaintenance.UpgradeSchemata();
Thread.Sleep(100);
_initProgress = 5;
_initMessage = "Getting list of countries from Swarmops servers...";
Thread.Sleep(100);
// Create translation lists
Dictionary <int, int> geographyIdTranslation = new Dictionary <int, int>();
Dictionary <int, int> cityIdTranslation = new Dictionary <int, int>();
Dictionary <string, int> countryIdTranslation = new Dictionary <string, int>();
Dictionary <int, bool> cityIdsUsedLookup = new Dictionary <int, bool>();
// Initialize the root geography (which becomes #1 if everything works)
int rootGeographyId = SwarmDb.GetDatabaseForWriting().CreateGeography("World", 0);
// Get the list of countries
Swarmops.Site.Automation.GetGeographyData geoDataFetcher = new GetGeographyData();
Swarmops.Site.Automation.Country[] countries = geoDataFetcher.GetCountries();
_initProgress = 7;
_initMessage = "Creating all countries on local server...";
Thread.Sleep(100);
int count = 0;
int total = countries.Length;
// Create all countries in our own database
foreach (Swarmops.Site.Automation.Country country in countries)
{
countryIdTranslation[country.Code] = SwarmDb.GetDatabaseForWriting().CreateCountry(country.Name,
country.Code,
country.Culture,
rootGeographyId, 5,
string.Empty);
count++;
_initMessage = String.Format("Creating all countries on local server... ({0}%)", count * 100 / total);
}
_initProgress = 10;
// Construct list of countries that have geographic data
List <string> initializableCountries = new List <string>();
foreach (Swarmops.Site.Automation.Country country in countries)
{
if (country.GeographyId != 1)
{
initializableCountries.Add(country.Code);
}
}
float initStepPerCountry = 90f / initializableCountries.Count;
int countryCount = 0;
// For each country...
foreach (string countryCode in initializableCountries)
{
// Get the geography layout
_initMessage = "Retrieving geography for " + countryCode + "...";
Thread.Sleep(100);
Swarmops.Site.Automation.Geography geography = geoDataFetcher.GetGeographyForCountry(countryCode);
_initProgress = 10 + (int)(countryCount * initStepPerCountry + initStepPerCountry / 6);
_initMessage = "Setting up geography for " + countryCode + "...";
Thread.Sleep(100);
// Create the country's root geography
int countryRootGeographyId = SwarmDb.GetDatabaseForWriting().CreateGeography(geography.Name,
rootGeographyId);
geographyIdTranslation[geography.GeographyId] = countryRootGeographyId;
SwarmDb.GetDatabaseForWriting().SetCountryGeographyId(countryIdTranslation[countryCode],
countryRootGeographyId);
count = 0;
total = InitDatabaseThreadCountGeographyChildren(geography.Children);
InitDatabaseThreadCreateGeographyChildren(geography.Children, countryRootGeographyId,
ref geographyIdTranslation, countryCode, ref count, total);
_initProgress = 10 + (int)(countryCount * initStepPerCountry + initStepPerCountry / 3);
_initMessage = "Retrieving cities for " + countryCode + "...";
Thread.Sleep(100);
// Get the postal codes and cities
Swarmops.Site.Automation.City[] cities = null;
try
{
cities = geoDataFetcher.GetCitiesForCountry(countryCode);
}
catch (Exception)
// This is a SoapHeaderException in VS debugging, but SOMETHING ELSE! in Mono runtime, so make it generic
{
// This is typically a country that isn't populated with cities yet. Ignore.
countryCount++;
continue;
}
_initProgress = 10 + (int)(countryCount * initStepPerCountry + initStepPerCountry / 2);
_initMessage = "Retrieving postal codes for " + countryCode + "...";
Thread.Sleep(100);
Swarmops.Site.Automation.PostalCode[] postalCodes = geoDataFetcher.GetPostalCodesForCountry(countryCode);
// Find which cities are actually used
foreach (Swarmops.Site.Automation.PostalCode postalCode in postalCodes)
{
cityIdsUsedLookup[postalCode.CityId] = true;
}
_initProgress = 10 + (int)(countryCount * initStepPerCountry + initStepPerCountry * 2 / 3);
// Insert cities
int newCountryId = countryIdTranslation[countryCode];
int cityIdHighwater = SwarmDb.GetDatabaseForAdmin().ExecuteAdminCommandScalar("SELECT Max(CityId) FROM Cities;");
_initMessage = string.Format("Setting up {0:N0} cities for {1}...", cities.Length, countryCode);
StringBuilder sqlCityBuild = new StringBuilder("INSERT INTO Cities (CityName, GeographyId, CountryId, Comment) VALUES ", 65536);
bool insertComma = false;
foreach (Swarmops.Site.Automation.City city in cities)
{
if (!geographyIdTranslation.ContainsKey(city.GeographyId))
{
cityIdsUsedLookup[city.CityId] = false; // force non-use of invalid city
}
if (cityIdsUsedLookup[city.CityId])
{
int newGeographyId = geographyIdTranslation[city.GeographyId];
if (insertComma)
{
sqlCityBuild.Append(",");
}
sqlCityBuild.Append("('" + city.Name.Replace("'", "\'") + "'," + newGeographyId.ToString() + "," +
newCountryId.ToString() + ",'')");
insertComma = true;
cityIdTranslation[city.CityId] = ++cityIdHighwater; // Note that we assume the assigned ID here.
}
}
sqlCityBuild.Append(";");
SwarmDb.GetDatabaseForAdmin().ExecuteAdminCommand(sqlCityBuild.ToString()); // Inserts all cities in one bulk op, to save roundtrips
// Insert postal codes
_initProgress = 10 + (int)(countryCount * initStepPerCountry + initStepPerCountry * 5 / 6);
_initMessage = string.Format("Setting up {0:N0} postal codes for {1}...", postalCodes.Length, countryCode);
StringBuilder sqlBuild = new StringBuilder("INSERT INTO PostalCodes (PostalCode, CityId, CountryId) VALUES ", 65536);
insertComma = false;
foreach (Swarmops.Site.Automation.PostalCode postalCode in postalCodes)
{
if (cityIdsUsedLookup[postalCode.CityId] == false)
{
// Remnants of invalid pointers
continue;
}
int newCityId = cityIdTranslation[postalCode.CityId];
if (insertComma)
{
sqlBuild.Append(",");
}
sqlBuild.Append("('" + postalCode.PostalCode.Replace("'", "\'") + "'," + newCityId.ToString() + "," +
newCountryId.ToString() + ")");
insertComma = true;
}
sqlBuild.Append(";");
SwarmDb.GetDatabaseForAdmin().ExecuteAdminCommand(sqlBuild.ToString()); // Inserts all postal codes in one bulk op, to save roundtrips
countryCount++;
_initProgress = 10 + (int)(countryCount * initStepPerCountry);
}
// Set Geography at baseline (TODO: Ask for what baseline we got)
Persistence.Key["LastGeographyUpdateId"] = "0";
// Set an installation ID
Persistence.Key["SwarmopsInstallationId"] = Guid.NewGuid().ToString();
// Create initial currencies (European)
Currency.Create("EUR", "Euros", "€");
Currency.Create("USD", "US Dollars", "$");
Currency.Create("CAD", "Canadian Dollars", "CA$");
Currency.Create("SEK", "Swedish Krona", string.Empty);
Currency.Create("NOK", "Norwegian Krona", string.Empty);
Currency.Create("DKK", "Danish Krona", string.Empty);
Currency.Create("ISK", "Icelandic Krona", string.Empty);
Currency.Create("CHF", "Swiss Franc", string.Empty);
Currency.Create("GBP", "Pounds Sterling", "£");
Currency.Create("BTC", "Bitcoin", "฿");
// Create the sandbox
Swarmops.Logic.Structure.Organization.Create(0, "Sandbox", "Sandbox", "Sandbox", "swarmops.com", "Ops",
rootGeographyId, true,
true, 0).EnableEconomy(Swarmops.Logic.Financial.Currency.FromCode("EUR"));
_initProgress = 100;
_initMessage = "Complete.";
}
catch (Exception failedException)
{
// Use initMessage to push info about what went wrong to the user
_initMessage = failedException.ToString();
}
Thread.Sleep(1000); // give some time for static var to stick and web interface to react before killing thread
}
public static void Migrate()
{
SwarmDb db = SwarmDb.GetDatabaseForAdmin();
using (DbConnection connection = db.GetSqlServerDbConnection())
{
connection.Open();
DbCommand command = connection.CreateCommand();
command.CommandText =
"SELECT PeopleOptionalData.PersonId, PersonOptionalDataTypes.Name AS PersonOptionalDataType, " +
"PeopleOptionalData.Data FROM PeopleOptionalData,PersonOptionalDataTypes " +
"WHERE PeopleOptionalData.PersonOptionalDataTypeId=PersonOptionalDataTypes.PersonOptionalDataTypeId ORDER BY PersonId";
using (DbDataReader reader = command.ExecuteReader())
{
int lastPersonId = 0;
while (reader.Read())
{
int personId = reader.GetInt32(0);
string personOptionalDataTypeString = reader.GetString(1);
string data = reader.GetString(2);
string displayData = data;
if (displayData.Length > 40)
{
displayData = displayData.Substring(0, 40);
}
displayData = displayData.Replace("\r\n", "#");
PersonOptionalDataKey key =
(PersonOptionalDataKey)
Enum.Parse(typeof(PersonOptionalDataKey), personOptionalDataTypeString);
// Display
Person person = null;
try
{
person = Person.FromIdentity(personId);
}
catch (Exception)
{
Console.WriteLine("PERSON #{0} IS NOT IN DATABASE", personId);
}
if (person != null)
{
if (personId != lastPersonId)
{
Console.WriteLine(person.Canonical + " -- ");
lastPersonId = personId;
}
ObjectOptionalDataType dataType =
(ObjectOptionalDataType)
Enum.Parse(typeof(ObjectOptionalDataType), personOptionalDataTypeString);
Console.WriteLine(" -- {0,-20} {1}", dataType.ToString(), displayData);
if (data.Trim().Length > 0)
{
db.SetObjectOptionalData(person, dataType, data);
}
}
}
}
}
}
