public override void Drop() { OutputStreams.WriteLine("Dropping database..."); List <int> spidList = new List <int>(); using (DbDataReader dbDataReader = ExecuteReader("EXEC sp_who2")) { while (dbDataReader.Read() == true) { string name = dbDataReader["DBName"].ToString(); int spid = Convert.ToInt32(dbDataReader["SPID"]); if ((name.ToUpper() == Name.ToUpper()) && (spidList.Contains(spid) == false)) { spidList.Add(spid); } } } foreach (int spid in spidList) { ExecuteNonQuery($"KILL {spid}"); } ExecuteNonQuery($"DROP DATABASE {Name};"); OutputStreams.WriteLine("Database dropped."); }
/// <summary> /// Executes SQL against the underlying data source. /// </summary> /// <param name="sql">The SQL to execute.</param> /// <param name="commandTimeout">Specifies the amount of time in seconds the command is permitted to wait before throwing an exception.</param> /// <returns>Returns the number of rows affected</returns> public int ExecuteScript(string sql, int commandTimeout) { int executeScript = 0; if (String.IsNullOrEmpty(sql) == false) { try { using (SqlConnection sqlConnection = SqlConnectionFactory.NewSqlConnetion()) { SqlMapper.Execute(sqlConnection, sql, commandType: CommandType.Text, commandTimeout: commandTimeout); } } catch (Exception exception) { OutputStreams.WriteLine(); OutputStreams.WriteLine(exception.Message); OutputStreams.WriteLine(); OutputStreams.WriteLine(sql); OutputStreams.WriteLine(); } } return(executeScript); }
private void Commit(List <Crime> crimes) { OutputStreams.WriteLine("Beginning commit..."); // This takes too long with massive lists // crimes = crimes.Distinct().ToList(); while (crimes.Count > 0) { int toTake = 100000; List <Crime> crimesToCommit = crimes.Take(toTake).ToList(); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendLine(); stringBuilder.AppendLine("BEGIN TRAN"); stringBuilder.AppendLine(); crimesToCommit.ForEach(m => stringBuilder.AppendLine($"EXEC Crime.spAddCrime @LocalGovernmentAreaID = {m.LocalGovernmentAreaID}, @OffenceID = {m.OffenceID}, @Count = {m.Count}, @Month = {m.Month}, @Year = {m.Year}")); stringBuilder.AppendLine(); stringBuilder.AppendLine("COMMIT"); stringBuilder.AppendLine(); OutputStreams.WriteLine($"Commiting {crimesToCommit.Count} records, {(crimes.Count - crimesToCommit.Count)} left"); DataProvider.AdhocScriptRepository.ExecuteScript(stringBuilder.ToString()); crimes.RemoveRange(0, crimesToCommit.Count); } OutputStreams.WriteLine("Commit completed"); }
/// <summary> /// Performs parsing operations and constructs a list of Coordinate objects as the result. /// </summary> /// <param name="fileName">The path to the file containing the KML information to parse.</param> /// <param name="coordinates">The list of Coordinate objects to serialise the KML information into.</param> protected override void OnParse(string fileName, List <Coordinate> coordinates) { OutputStreams.WriteLine($"Parsing {ACT} data..."); XmlDocument xmlDocument = new XmlDocument(); xmlDocument.Load(fileName); XmlNodeList xmlNodeList = xmlDocument.SelectNodes("/Document/Placemark"); foreach (XmlNode xmlNode in xmlNodeList) { string name = xmlNode.SelectSingleNode("name").InnerText; OutputStreams.WriteLine($"Processing {name}..."); XmlNode coordinateXmlNode = xmlNode.SelectSingleNode("Polygon/outerBoundaryIs/LinearRing/coordinates"); string coordinateValues = coordinateXmlNode.InnerText; coordinates.Clear(); string[] coordinateLines = coordinateValues.Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries); foreach (string coordinateLine in coordinateLines) { string[] coordinateParts = coordinateLine.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); coordinates.Add(Coordinate.FromValues(Double.Parse(coordinateParts[1]), Double.Parse(coordinateParts[0]))); } base.Commit(coordinates, name); } base.OnParse(fileName, coordinates); }
public override void Drop() { OutputStreams.WriteLine("Dropping database..."); ExecuteNonQuery($"SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '{Name}';"); ExecuteNonQuery($"DROP DATABASE IF EXISTS {Name};"); OutputStreams.WriteLine("Database dropped."); }
public void ExecuteMigrations() { CheckDatabase(); int migrationState = GetMigrationState(); List <Migration> migrations = new List <Migration>(); _assembly.GetExportedTypes() .Where(m => ( (m.BaseType == typeof(Migration)) && (m.GetCustomAttributes().Select(n => (n.GetType())).Contains(typeof(MigrationAttribute)) == true) )) .ToList() .ForEach(m => migrations.Add((Migration)(_assembly.CreateInstance(m.FullName)))); migrations = migrations.Where(m => (m != null)).ToList(); OutputStreams.WriteLine($"Found {migrations.Count} migrations."); foreach (Migration migration in migrations) { IEnumerable <Attribute> customAttributes = migration.GetType().GetCustomAttributes(); MigrationAttribute migrationAttribute = customAttributes.OfType <MigrationAttribute>().FirstOrDefault(); DataPlatformAttribute dataPlatformAttribute = customAttributes.OfType <DataPlatformAttribute>().FirstOrDefault(); DataPlatformType dataPlatformType = ((dataPlatformAttribute == null) ? DataPlatformType.Unknown : dataPlatformAttribute.DataPlatformType); OutputStreams.WriteLine($"Found migration {migrationAttribute.Value}"); if (dataPlatformType != DataPlatformType.Unknown) { if ((migrationAttribute.Value > migrationState) && (migrationAttribute.IsIgnore == false)) { try { OutputStreams.WriteLine($"Applying migration {migrationAttribute.Value}..."); migration.Apply(dataPlatformType); migrationState = BumpMigrationState(); } catch { migration.Rollback(dataPlatformType); throw; } } else { OutputStreams.WriteLine($"Skipping migration {migrationAttribute.Value}..."); } } } }
/// <summary> /// Performs parsing operations and constructs a list of Coordinate objects as the result. /// </summary> /// <param name="fileName">The path to the file containing the XML information to parse.</param> /// <param name="crimes">The list of Crime objects to serialise the XML information into.</param> protected override void OnParse(string fileName, List <Crime> crimes) { OutputStreams.WriteLine($"Parsing {SA} data..."); XmlDocument xmlDocument = new XmlDocument(); xmlDocument.Load(fileName); State state = DataProvider.StateRepository.GetStateByAbbreviatedName(SA); List <LocalGovernmentArea> localGovernmentAreas = DataProvider.LocalGovernmentAreaRepository.GetLocalGovernmentAreasByStateID(state.ID); Dictionary <string, Offence> offences = new Dictionary <string, Offence>(); DataProvider.OffenceRepository.GetOffences().ForEach(m => offences.Add(m.Name.ToUpper(), m)); XmlNodeList workSheetXmlNodeList = xmlDocument.SelectNodes("/Workbook/Worksheet"); foreach (XmlNode workSheetXmlNode in workSheetXmlNodeList) { string localGovernmentAreaName = workSheetXmlNode.Attributes["Name"].Value; if (localGovernmentAreaName.Contains("(") == true) { localGovernmentAreaName = localGovernmentAreaName.Substring(0, localGovernmentAreaName.IndexOf("(")).Trim(); } LocalGovernmentArea localGovernmentArea = localGovernmentAreas.Where(m => (m.Name.EqualsIgnoreCase(localGovernmentAreaName) == true)).FirstOrDefault(); XmlNodeList xmlNodeList = workSheetXmlNode.SelectNodes("Table/Row[count(Cell) = 6]"); foreach (XmlNode xmlNode in xmlNodeList) { string offenceName = xmlNode.ChildNodes[0].InnerText.ToUpper(); if ((String.IsNullOrEmpty(offenceName) == false) && (offenceName.StartsWith("-") == true)) { offenceName = offenceName.Substring(offenceName.IndexOf("-") + 1).Trim(); Offence offence = null; if ((String.IsNullOrEmpty(offenceName) == false) && (offences.ContainsKey(offenceName) == true)) { offence = offences[offenceName]; } for (int i = 1, j = 2009; i < 6; i++, j++) { int count = Convert.ToInt32(xmlNode.ChildNodes[i].InnerText); crimes.Add(new Crime(count, localGovernmentArea.ID, 1, offence.ID, j)); } } } } base.OnParse(fileName, crimes); }
private static void CheckGeography(SqlConnection sqlConnection) { OutputStreams.WriteLine($"Checking geography..."); StringBuilder stringBuilder = new StringBuilder(); // Automatically correct any geography nasties - this could cause some geography to shift slightly stringBuilder.AppendLine("UPDATE Location.LocalGovernmentArea SET Area = Area.MakeValid() WHERE Area.STIsValid() = 0;"); ExecuteNonQuery(sqlConnection, stringBuilder.ToString()); OutputStreams.WriteLine("Check completed"); }
/// <summary> /// Performs parsing operations and constructs a list of Coordinate objects as the result. /// </summary> /// <param name="fileName">The path to the file containing the KML information to parse.</param> /// <param name="coordinates">The list of Coordinate objects to serialise the KML information into.</param> protected override void OnParse(string fileName, List <Coordinate> coordinates) { OutputStreams.WriteLine($"Parsing {VIC} data..."); XmlDocument xmlDocument = new XmlDocument(); xmlDocument.Load(fileName); string[] reverseLocalGovernmentAreaNames = new string[] { "Alpine", "Baw Baw", "Mansfield" }; XmlNodeList xmlNodeList = xmlDocument.SelectNodes("/Document/Placemark"); foreach (XmlNode xmlNode in xmlNodeList) { string name = xmlNode.SelectSingleNode("name").InnerText; OutputStreams.WriteLine($"Processing {name}..."); XmlNodeList coordinateXmlNodes = xmlNode.SelectNodes("Polygon/outerBoundaryIs/LinearRing/coordinates | MultiGeometry/Polygon/outerBoundaryIs/LinearRing/coordinates"); string coordinateValues = ""; foreach (XmlNode coordinateXmlNode in coordinateXmlNodes) { coordinateValues = $"{coordinateValues} {coordinateXmlNode.InnerText}"; } coordinates.Clear(); string[] coordinateLines = coordinateValues.Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries); foreach (string coordinateLine in coordinateLines) { string[] coordinateParts = coordinateLine.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); coordinates.Add(Coordinate.FromValues(Double.Parse(coordinateParts[1]), Double.Parse(coordinateParts[0]))); } if (reverseLocalGovernmentAreaNames.Contains(name) == false) { coordinates.Reverse(); } base.Commit(coordinates, name); } base.OnParse(fileName, coordinates); }
/// <summary> /// Performs parsing operations and constructs a list of Coordinate objects as the result. /// </summary> /// <param name="fileName">The path to the file containing the XML information to parse.</param> /// <param name="crimes">The list of Crime objects to serialise the XML information into.</param> protected override void OnParse(string fileName, List <Crime> crimes) { OutputStreams.WriteLine($"Parsing {NSW} data..."); XmlDocument xmlDocument = new XmlDocument(); xmlDocument.Load(fileName); XmlNodeList xmlNodeList = xmlDocument.SelectNodes("/Workbook/Worksheet/Table/Row[position() > 1]"); int year = 1995; State state = DataProvider.StateRepository.GetStateByAbbreviatedName(NSW); List <LocalGovernmentArea> localGovernmentAreas = DataProvider.LocalGovernmentAreaRepository.GetLocalGovernmentAreasByStateID(state.ID); Dictionary <string, Offence> offences = new Dictionary <string, Offence>(); DataProvider.OffenceRepository.GetOffences().ForEach(m => offences.Add(m.Name.ToUpper(), m)); foreach (XmlNode xmlNode in xmlNodeList) { string localGovernmentAreaName = xmlNode.ChildNodes[0].InnerText.Trim(); string offenceName = xmlNode.ChildNodes[1].InnerText.Trim().ToUpper(); string suboffenceName = xmlNode.ChildNodes[2].InnerText.Trim().ToUpper(); Offence offence = null; LocalGovernmentArea localGovernmentArea = localGovernmentAreas.Where(m => (m.Name.EqualsIgnoreCase(localGovernmentAreaName) == true)).FirstOrDefault(); if ((String.IsNullOrEmpty(offenceName) == false) && (offences.ContainsKey(offenceName) == true)) { offence = offences[offenceName]; } if ((String.IsNullOrEmpty(suboffenceName) == false) && (offences.ContainsKey(offenceName) == true)) { offence = offences[suboffenceName]; } DateTime dateTime = new DateTime(year, 1, 1); for (int i = 3; i < xmlNode.ChildNodes.Count; i++) { int count = Convert.ToInt32(xmlNode.ChildNodes[i].InnerText); crimes.Add(new Crime(count, localGovernmentArea.ID, dateTime.Month, offence.ID, dateTime.Year)); dateTime = dateTime.AddMonths(1); } } base.OnParse(fileName, crimes); }
/// <summary> /// Performs parsing operations and constructs a list of Coordinate objects as the result. /// </summary> /// <param name="fileName">The path to the file containing the XML information to parse.</param> /// <param name="crimes">The list of Crime objects to serialise the XML information into.</param> protected override void OnParse(string fileName, List <Crime> crimes) { OutputStreams.WriteLine($"Parsing {ACT} data..."); XmlDocument xmlDocument = new XmlDocument(); xmlDocument.Load(fileName); XmlNodeList xmlNodeList = xmlDocument.SelectNodes("/Workbook/Worksheet/Table"); State state = DataProvider.StateRepository.GetStateByAbbreviatedName(ACT); List <LocalGovernmentArea> localGovernmentAreas = DataProvider.LocalGovernmentAreaRepository.GetLocalGovernmentAreasByStateID(state.ID); Dictionary <string, Offence> offences = new Dictionary <string, Offence>(); DataProvider.OffenceRepository.GetOffences().ForEach(m => offences.Add(m.Name.ToUpper(), m)); foreach (XmlNode xmlNode in xmlNodeList) { XmlNode localGovermentAreaXmlNode = xmlNode.SelectSingleNode("Row[position() = 1]"); string localGovernmentAreaName = localGovermentAreaXmlNode.InnerText.Trim(); LocalGovernmentArea localGovernmentArea = localGovernmentAreas.Where(m => (m.Name.EqualsIgnoreCase(localGovernmentAreaName) == true)).FirstOrDefault(); List <DateTime> dateTimeList = new List <DateTime>(); XmlNodeList datesXmlNodeList = xmlNode.SelectNodes("Row[position() = 3]/Cell"); datesXmlNodeList.OfType <XmlNode>().ToList().ForEach(m => dateTimeList.Add(DateTime.Parse(m.InnerText))); XmlNodeList offenceXmlNodeList = xmlNode.SelectNodes("Row[position() > 3]"); foreach (XmlNode offenceXmlNode in offenceXmlNodeList) { Offence offence = null; string offenceName = offenceXmlNode.ChildNodes[0].InnerText.Trim().ToUpper(); if ((String.IsNullOrEmpty(offenceName) == false) && (offences.ContainsKey(offenceName) == true)) { offence = offences[offenceName]; } for (int i = 0, j = 1; j < offenceXmlNode.ChildNodes.Count; i++, j++) { int count = Convert.ToInt32(offenceXmlNode.ChildNodes[j].InnerText); DateTime dateTime = dateTimeList[i]; crimes.Add(new Crime(count, localGovernmentArea.ID, dateTime.Month, offence.ID, dateTime.Year)); } } } base.OnParse(fileName, crimes); }
public override void Create() { OutputStreams.WriteLine("Creating database..."); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendLine($"CREATE DATABASE {Name}"); stringBuilder.AppendLine(" WITH OWNER = postgres"); stringBuilder.AppendLine(" ENCODING = 'UTF8'"); stringBuilder.AppendLine(" TABLESPACE = pg_default"); stringBuilder.AppendLine(" CONNECTION LIMIT = -1;"); ExecuteNonQuery(stringBuilder.ToString()); OutputStreams.WriteLine("Database created."); }
/// <summary> /// Performs parsing operations and constructs a list of Coordinate objects as the result. /// </summary> /// <param name="fileName">The path to the file containing the XML information to parse.</param> /// <param name="crimes">The list of Crime objects to serialise the XML information into.</param> protected override void OnParse(string fileName, List <Crime> crimes) { OutputStreams.WriteLine($"Parsing {VIC} data..."); XmlDocument xmlDocument = new XmlDocument(); xmlDocument.Load(fileName); XmlNodeList xmlNodeList = xmlDocument.SelectNodes("/Workbook/Worksheet/Table/Row[position() > 1]"); // The dataset I'm working with has a single year - 2014 int year = 2014; State state = DataProvider.StateRepository.GetStateByAbbreviatedName(VIC); List <LocalGovernmentArea> localGovernmentAreas = DataProvider.LocalGovernmentAreaRepository.GetLocalGovernmentAreasByStateID(state.ID); Dictionary <string, Offence> offences = new Dictionary <string, Offence>(); DataProvider.OffenceRepository.GetOffences().ForEach(m => offences.Add(m.Name.ToUpper(), m)); foreach (XmlNode xmlNode in xmlNodeList) { string localGovernmentAreaName = xmlNode.ChildNodes[2].InnerText.Trim(); string offenceName = xmlNode.ChildNodes[3].InnerText.Trim().ToUpper(); string suboffenceName = xmlNode.ChildNodes[4].InnerText.Trim().ToUpper(); int count = Convert.ToInt32(xmlNode.ChildNodes[5].InnerText); LocalGovernmentArea localGovernmentArea = localGovernmentAreas.Where(m => (m.Name.EqualsIgnoreCase(localGovernmentAreaName) == true)).FirstOrDefault(); Offence offence = null; if ((String.IsNullOrEmpty(offenceName) == false) && (offences.ContainsKey(offenceName) == true)) { offence = offences[offenceName]; } if ((String.IsNullOrEmpty(suboffenceName) == false) && (offences.ContainsKey(offenceName) == true)) { offence = offences[suboffenceName]; } // We only have crime data per year, so it will always be added in on 01/01/YYYY crimes.Add(new Crime(count, localGovernmentArea.ID, 1, offence.ID, year)); } base.OnParse(fileName, crimes); }
/// <summary> /// Performs parsing operations and constructs a list of Coordinate objects as the result. /// </summary> /// <param name="fileName">The path to the file containing the KML information to parse.</param> /// <param name="coordinates">The list of Coordinate objects to serialise the KML information into.</param> protected override void OnParse(string fileName, List <Coordinate> coordinates) { OutputStreams.WriteLine($"Parsing {TAS} data..."); string name = "Tasmania"; OutputStreams.WriteLine($"Processing {name}..."); XmlDocument xmlDocument = new XmlDocument(); xmlDocument.Load(fileName); XmlNodeList xmlNodeList = xmlDocument.SelectNodes("/Document/Placemark"); foreach (XmlNode xmlNode in xmlNodeList) { XmlNodeList coordinateXmlNodes = xmlNode.SelectNodes("Polygon/outerBoundaryIs/LinearRing/coordinates"); string coordinateValues = ""; foreach (XmlNode coordinateXmlNode in coordinateXmlNodes) { coordinateValues = $"{coordinateValues} {coordinateXmlNode.InnerText}"; } string[] coordinateLines = coordinateValues.Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries); foreach (string coordinateLine in coordinateLines) { string coordinateLineValue = coordinateLine .Replace(Environment.NewLine, "") .Replace("\n", "") .Replace("\t", ""); if (String.IsNullOrEmpty(coordinateLineValue) == false) { string[] coordinateParts = coordinateLineValue.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); coordinates.Add(Coordinate.FromValues(Double.Parse(coordinateParts[1]), Double.Parse(coordinateParts[0]))); } } base.Commit(coordinates, name); } base.OnParse(fileName, coordinates); }
/// <summary> /// Performs parsing operations and constructs a list of Coordinate objects as the result. /// </summary> /// <param name="fileName">The path to the file containing the KML information to parse.</param> /// <param name="coordinates">The list of Coordinate objects to serialise the KML information into.</param> protected override void OnParse(string fileName, List <Coordinate> coordinates) { OutputStreams.WriteLine($"Parsing {QLD} data..."); XmlDocument xmlDocument = new XmlDocument(); xmlDocument.Load(fileName); XmlNodeList xmlNodeList = xmlDocument.SelectNodes("/Document/Placemark"); foreach (XmlNode xmlNode in xmlNodeList) { string name = xmlNode.SelectSingleNode("ExtendedData/Data[@name = 'Region']").InnerText; OutputStreams.WriteLine($"Processing {name}..."); XmlNodeList coordinateXmlNodes = xmlNode.SelectNodes("Polygon/outerBoundaryIs/LinearRing/coordinates | MultiGeometry/Polygon/outerBoundaryIs/LinearRing/coordinates"); string coordinateValues = ""; foreach (XmlNode coordinateXmlNode in coordinateXmlNodes) { coordinateValues = $"{coordinateValues} {coordinateXmlNode.InnerText}"; } coordinates.Clear(); string[] coordinateLines = coordinateValues.Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries); foreach (string coordinateLine in coordinateLines) { string[] coordinateParts = coordinateLine.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); coordinates.Add(Coordinate.FromValues(Double.Parse(coordinateParts[1]), Double.Parse(coordinateParts[0]))); } // For some unknown reason, QLD coordinates have reversed winding direction, so we need to unreverse it coordinates.Reverse(); base.Commit(coordinates, name); } base.OnParse(fileName, coordinates); }
public IActionResult Home(HomeModel homeModel) { ElementRepository elementRepository = new ElementRepository(); homeModel.ElementModels = elementRepository.SelectElements().Select(m => (ElementModel.ToElementModel(m))).ToList(); OutputStreams.WriteLine($"[HttpPost] Home: {String.Join(", ", homeModel.Symbols)}"); if (ModelState.IsValid == true) { List <Element> elements = homeModel.Symbols.Select(m => (Element.FromString(m))).ToList(); List <WebService> webServices = new List <WebService>(new WebService[] { new AFlowWebService(), new MaterialsProjectWebService() }); List <SearchResult> searchResults = new List <SearchResult>(); OutputStreams.WriteLine($"Elements: {String.Join(", ", elements)}"); foreach (WebService webService in webServices) { try { OutputStreams.WriteLine($"Searching {webService.BaseUrl}..."); List <SearchResult> webServiceSearchResults = webService.Search(elements); OutputStreams.WriteLine($"{webServiceSearchResults.Count} search results"); if (webServiceSearchResults.Count > 0) { searchResults.AddRange(webServiceSearchResults); } } catch (Exception exception) { OutputStreams.WriteLine(); OutputStreams.WriteLine(exception.Message); } } homeModel.SearchResults = searchResults.OrderBy(m => (m.Compound)).ThenByDescending(m => (m.BandGap)).ToList(); } return(View(homeModel)); }
public virtual void Create() { OutputStreams.WriteLine("Creating database..."); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendLine($"CREATE DATABASE {_name}"); stringBuilder.AppendLine(" WITH OWNER = postgres"); stringBuilder.AppendLine(" ENCODING = 'UTF8'"); stringBuilder.AppendLine(" TABLESPACE = pg_default"); // Commenting these lines for the moment // stringBuilder.AppendLine(" LC_COLLATE = 'English_Australia.1252'"); // stringBuilder.AppendLine(" LC_CTYPE = 'English_Australia.1252'"); stringBuilder.AppendLine(" CONNECTION LIMIT = -1;"); ExecuteNonQuery(_dbConnection, stringBuilder.ToString()); OutputStreams.WriteLine("Database created."); }
/// <summary> /// Commits records to the underlying data source. /// </summary> /// <param name="coordinates">A list of Coordinate objects containing the information used to update.</param> /// <param name="name">The name of the local government area.</param> protected void Commit(List <Coordinate> coordinates, string name) { OutputStreams.WriteLine("Beginning commit..."); if (coordinates[0] != coordinates[coordinates.Count - 1]) { // The first and last point must be the same coordinates.Add(coordinates[0]); } StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendLine($"DECLARE @@StateID INT = (SELECT ID FROM [Location].State WHERE AbbreviatedName = '{_state}')"); stringBuilder.AppendLine("DECLARE @@LocalGovernmentAreaID INT"); stringBuilder.AppendLine("DECLARE @@Area GEOGRAPHY"); stringBuilder.AppendLine(); stringBuilder.AppendLine($"SET @@LocalGovernmentAreaID = (SELECT ID FROM [Location].LocalGovernmentArea WHERE StateID = @@StateID AND Name = '{name}')"); stringBuilder.AppendLine(); stringBuilder.AppendLine("BEGIN TRAN"); stringBuilder.AppendLine(); stringBuilder.Append("SET @@Area = GEOGRAPHY::STGeomFromText('POLYGON(("); coordinates.ForEach(m => stringBuilder.Append($"{m.Longitude} {m.Latitude}{((m == coordinates[coordinates.Count - 1]) ? "" : ", ")} ")); stringBuilder.AppendLine($"))', {WGS84Datum});"); // https://msdn.microsoft.com/en-us/library/hh564259.aspx for anyone who is interested in this number stringBuilder.AppendLine(); stringBuilder.AppendLine("UPDATE [Location].LocalGovernmentArea SET Area = @@Area WHERE ID = @@LocalGovernmentAreaID"); stringBuilder.AppendLine(); stringBuilder.AppendLine("COMMIT"); stringBuilder.AppendLine(); OutputStreams.WriteLine($"Commiting {coordinates.Count} records"); DataProvider.AdhocScriptRepository.ExecuteScript(stringBuilder.ToString()); }
private static void ProcessXmlDataSources(string dataSources) { OutputStreams.WriteLine("Processing XML data sources..."); List <string> dataSourceNames = new List <string>(); List <XmlDataSourceParser> parsers = new List <XmlDataSourceParser>(); if (dataSources.EqualsIgnoreCase("ALL") == true) { dataSourceNames.AddRange(ParserFactory.SupportedXmlParserNames); } else { dataSources.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList().ForEach(m => dataSourceNames.Add(m.ToUpper().Trim())); } dataSourceNames = dataSourceNames.Distinct().OrderBy(m => (m)).ToList(); dataSourceNames.ForEach(m => parsers.Add(ParserFactory.NewXmlParser(Path.Combine(ApplicationConfiguration.Default.MigrationDataSourceDirectory, "XML Data Sources"), m))); parsers.ForEach(m => m.Parse()); OutputStreams.WriteLine("Processing complete."); }
public override void Create() { OutputStreams.WriteLine("Creating database..."); StringBuilder stringBuilder = new StringBuilder(); ExecuteNonQuery($"CREATE DATABASE [{Name}]"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET ANSI_NULL_DEFAULT OFF"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET ANSI_NULLS OFF"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET ANSI_PADDING OFF"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET ANSI_WARNINGS OFF"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET ARITHABORT OFF"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET AUTO_CLOSE OFF"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET AUTO_SHRINK OFF"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET AUTO_UPDATE_STATISTICS ON"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET CURSOR_CLOSE_ON_COMMIT OFF"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET CURSOR_DEFAULT GLOBAL"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET CONCAT_NULL_YIELDS_NULL OFF"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET NUMERIC_ROUNDABORT OFF"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET QUOTED_IDENTIFIER OFF"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET RECURSIVE_TRIGGERS OFF"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET DISABLE_BROKER"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET AUTO_UPDATE_STATISTICS_ASYNC OFF"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET DATE_CORRELATION_OPTIMIZATION OFF"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET TRUSTWORTHY OFF"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET ALLOW_SNAPSHOT_ISOLATION OFF"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET PARAMETERIZATION SIMPLE"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET READ_COMMITTED_SNAPSHOT OFF"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET HONOR_BROKER_PRIORITY OFF"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET RECOVERY FULL"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET MULTI_USER"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET PAGE_VERIFY CHECKSUM"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET DB_CHAINING OFF"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET FILESTREAM(NON_TRANSACTED_ACCESS = OFF)"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET TARGET_RECOVERY_TIME = 60 SECONDS"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET DELAYED_DURABILITY = DISABLED"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET QUERY_STORE = OFF"); ExecuteNonQuery($"ALTER DATABASE [{Name}] SET READ_WRITE"); OutputStreams.WriteLine("Database created."); }
/// <summary> /// Performs parsing operations and constructs a list of Coordinate objects as the result. /// </summary> /// <param name="fileName">The path to the file containing the XML information to parse.</param> /// <param name="crimes">The list of Crime objects to serialise the XML information into.</param> protected override void OnParse(string fileName, List <Crime> crimes) { OutputStreams.WriteLine($"Parsing {QLD} data..."); XmlDocument xmlDocument = new XmlDocument(); xmlDocument.Load(fileName); XmlNode qldXmlNode = xmlDocument.SelectSingleNode("/Workbook/Worksheet/Table/Row[position() = 1]"); State state = DataProvider.StateRepository.GetStateByAbbreviatedName(QLD); List <LocalGovernmentArea> localGovernmentAreas = DataProvider.LocalGovernmentAreaRepository.GetLocalGovernmentAreasByStateID(state.ID); Dictionary <string, Offence> offences = new Dictionary <string, Offence>(); DataProvider.OffenceRepository.GetOffences().ForEach(m => offences.Add(m.Name.ToUpper(), m)); List <string> offenceNames = new List <string>(); qldXmlNode.ChildNodes.OfType <XmlNode>().Skip(2).ToList().ForEach(m => offenceNames.Add(m.InnerText)); XmlNodeList xmlNodeList = xmlDocument.SelectNodes("/Workbook/Worksheet/Table/Row[position() > 1]"); foreach (XmlNode xmlNode in xmlNodeList) { string localGovernmentAreaName = xmlNode.ChildNodes[0].InnerText.Trim(); string dateTimeValue = xmlNode.ChildNodes[1].InnerText.Trim(); DateTime dateTime = DateTime.MinValue; if (DateTime.TryParse(dateTimeValue, out dateTime) == false) { // Can't really use this, but should the whole thing fall over because of it? throw new Exception($"Date time parse choked: {dateTimeValue}"); } for (int i = 0, j = 2; i < offenceNames.Count; i++, j++) { string offenceName = offenceNames[i].ToUpper(); double countDouble = 0; int count = 0; // These must be some kind of averaged data - you can't have half a crime for example. if (Double.TryParse(xmlNode.ChildNodes[j].InnerText, out countDouble) == true) { count = Convert.ToInt32(countDouble); } LocalGovernmentArea localGovernmentArea = localGovernmentAreas.Where(m => (m.Name.EqualsIgnoreCase(localGovernmentAreaName) == true)).FirstOrDefault(); Offence offence = null; if ((String.IsNullOrEmpty(offenceName) == false) && (offences.ContainsKey(offenceName) == true)) { offence = offences[offenceName]; } crimes.Add(new Crime(count, localGovernmentArea.ID, dateTime.Month, offence.ID, dateTime.Year)); } } base.OnParse(fileName, crimes); }
public static void Main(string[] arguments) { OutputStreams.WriteLine(); OutputStreams.WriteLine("Hadoken Database Migrator"); OutputStreams.WriteLine(); try { CommandLineParser commandLineParser = new CommandLineParser(((arguments.Length == 0) ? ApplicationNamespace : arguments[0]), arguments); SqlConnectionStringBuilder sqlConnectionStringBuilder = new SqlConnectionStringBuilder(ApplicationConfiguration.HadokenConnectionString); string targetDatabase = sqlConnectionStringBuilder.InitialCatalog; // Switch over to master db to do admin type stuff sqlConnectionStringBuilder.InitialCatalog = "master"; using (DbConnection dbConnection = ConnectionFactory.NewDbConnection(sqlConnectionStringBuilder.ConnectionString)) { using (Database database = new SqlDatabase(targetDatabase, dbConnection)) { bool isExists = database.IsExists(); if ((isExists == true) && (commandLineParser.IsDrop == true)) { database.Drop(); isExists = false; } if (isExists == false) { database.Create(); } } } // Switch back to target db to run migrations sqlConnectionStringBuilder.InitialCatalog = targetDatabase; using (DbConnection dbConnection = ConnectionFactory.NewDbConnection(sqlConnectionStringBuilder.ConnectionString)) { RunMigration(dbConnection); } Environment.ExitCode = ErrorSuccess; } catch (Exception exception) { while (exception != null) { OutputStreams.WriteLine(exception.Message); OutputStreams.WriteLine(exception.StackTrace); OutputStreams.WriteLine(); exception = exception.InnerException; } Environment.ExitCode = ErrorInvalidFunction; } OutputStreams.WriteLine(); OutputStreams.WriteLine("Process complete."); OutputStreams.WriteLine(); }
public static void Main(string[] arguments) { OutputStreams.WriteLine(); OutputStreams.WriteLine("CPT331 Database Migrator"); OutputStreams.WriteLine(); try { if (CommandLineParser.Default.ParseArguments(arguments, _options) == true) { SqlConnectionStringBuilder sqlConnectionStringBuilder = new SqlConnectionStringBuilder(ApplicationConfiguration.Default.CPT331ConnectionString); string targetDatabase = sqlConnectionStringBuilder.InitialCatalog; // Switch over to master db to do admin type stuff sqlConnectionStringBuilder.InitialCatalog = "master"; using (SqlConnection sqlConnection = new SqlConnection(sqlConnectionStringBuilder.ConnectionString)) { sqlConnection.Open(); bool isDrop = false; if ((Boolean.TryParse(_options.Drop, out isDrop) == true) && (isDrop == true)) { OutputStreams.WriteLine("Dropping database..."); DropDatabase(sqlConnection, targetDatabase); OutputStreams.WriteLine("Database dropped."); } CreateDatabase(sqlConnection, targetDatabase); } // Switch back to target db to run migrations sqlConnectionStringBuilder.InitialCatalog = targetDatabase; using (SqlConnection sqlConnection = new SqlConnection(sqlConnectionStringBuilder.ConnectionString)) { RunMigration(sqlConnection); } // Import KML data if required if (String.IsNullOrEmpty(_options.Kml) == false) { ProcessKmlDataSources(_options.Kml); using (SqlConnection sqlConnection = new SqlConnection(sqlConnectionStringBuilder.ConnectionString)) { sqlConnection.Open(); CheckGeography(sqlConnection); } } // Import XML data if required if (String.IsNullOrEmpty(_options.Xml) == false) { ProcessXmlDataSources(_options.Xml); } } Environment.ExitCode = ErrorSuccess; } catch (Exception exception) { while (exception != null) { OutputStreams.WriteLine(exception.Message); OutputStreams.WriteLine(exception.StackTrace); OutputStreams.WriteLine(); exception = exception.InnerException; } Environment.ExitCode = ErrorInvalidFunction; } OutputStreams.WriteLine(); OutputStreams.WriteLine("Process complete."); OutputStreams.WriteLine(); }