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); } } } } } }