public void Escape_Characters()
{
IEngine engine = new MySQLEngine();
Assert.Equal('`', engine.Options.EscapeStart);
Assert.Equal('`', engine.Options.EscapeEnd);
}
public static WPExportDTO RunAllQueries(
WPConfigurationSourceDTO configurationSource,
WPConfigurationPluginExportDTO configurationPlugin)
{
MySQLEngine engine = new MySQLEngine(
configurationSource.DB_HOST,
configurationSource.DB_NAME,
configurationSource.DB_USER,
configurationSource.DB_PASSWORD
);
WPQuery queries = new WPQuery(configurationSource.TABLE_PREFIX);
WPExportDTO result = new WPExportDTO();
result.WPPosts = engine.Select <WPPostDTO>(queries.GetWPPosts);
result.WPPostChildren = engine.Select <WPPostDTO>(queries.GetWPPostChildren);
result.WPTags = engine.Select <WPTagDTO>(queries.GetWPTags);
result.WPCategories = engine.Select <WPCategoryDTO>(queries.GetWPCategories);
result.WPUsers = engine.Select <WPUserDTO>(queries.GetWPUsers);
result.WPPostsMeta = engine.Select <WPPostMetaDTO>(queries.GetWPPostMeta);
if (configurationPlugin.WooCommerce)
{
result.WPProducts = engine.Select <WPProductDTO>(queries.GetWPProducts);
result.WPProductChildren = engine.Select <WPProductDTO>(queries.GetWPProductChildren);
}
return(result);
}
public void DoWork()
{
WPExportDTO exportDTO = WPExportEngine.RunAllQueries(
_wpSource,
_wpConfigurationPluginExportDTO);
string json = string.Empty;
if (string.IsNullOrEmpty(_configurationOUTFile.DirtyExportFile) == false)
{
DirtyWPToJson dirtyWPToJson = new DirtyWPToJson(exportDTO);
json = dirtyWPToJson.CreateJSON(Newtonsoft.Json.Formatting.None);
FileHelper.WriteToFile(_configurationOUTFile.DirtyExportFile, json);
}
WPToJson wPToJson = new WPToJson(exportDTO)
{
ExportSeoWithYoast = _wpConfigurationPluginExportDTO.Yoast
};
json = wPToJson.CreateJSON(Newtonsoft.Json.Formatting.Indented);
if (string.IsNullOrEmpty(_configurationOUTFile.ExportFile) == false)
{
FileHelper.WriteToFile(_configurationOUTFile.ExportFile, json);
}
WPExportResult wp = Newtonsoft.Json.JsonConvert.DeserializeObject <WPExportResult>(json);
ISQLEngine engine = null;
if (string.IsNullOrEmpty(_configurationOUTFile.SQLServerConnection) == false)
{
engine = new SQLServerEngine(_configurationOUTFile.SQLServerConnection);
ExportToDatabase export = new ExportToDatabase(engine);
var result = export.Run(wp).Result;
}
if (string.IsNullOrEmpty(_configurationOUTFile.MySQLServerConnection) == false)
{
engine = new MySQLEngine(_configurationOUTFile.MySQLServerConnection);
ExportToDatabase export = new ExportToDatabase(engine);
var result = export.Run(wp).Result;
}
}
public async Task <IActionResult> ConvertJSONToSQL(ImportToMySQLDTO values)
{
if (values.contents.Length == 0)
{
return(null);
}
WPExportResult wp = new WPExportResult();
using (var ms = new MemoryStream())
{
values.contents.CopyTo(ms);
var fileBytes = ms.ToArray();
string json = Encoding.ASCII.GetString(fileBytes);
wp = Newtonsoft.Json.JsonConvert.DeserializeObject <WPExportResult>(json);
}
if (wp == null)
{
return(null);
}
ISQLEngine engine = new MySQLEngine(values.ConnectionString);
ExportToDatabase export = new ExportToDatabase(engine);
var result = await export.Run(wp);
StringBuilder sb = new StringBuilder();
foreach (var item in result)
{
sb.AppendFormat("{0}: {1} {2} | ", item.Key, item.Value, Environment.NewLine);
}
string log = sb.ToString();
return(View("Index"));
}
public void Engine_Name()
{
IEngine engine = new MySQLEngine();
Assert.Equal(EngineName.MySQL, engine.Options.Name);
}
public MainWindow()
{
InitializeComponent();
bool IsMySQLDB = true;
DataSet busDataSet = AccessEngine.Get("SELECT DISTINCT(bus_id) FROM [IntervalData2014-2015]");
DataSet intervalDataSet = AccessEngine.Get("SELECT * FROM [IntervalData2014-2015]");
DataSet routeDataSet = AccessEngine.Get("SELECT * FROM [Route ID]");
DataSet stopDataSet = AccessEngine.Get("SELECT * FROM [Stop ID]");
DataSet weatherDataSet = AccessEngine.Get("SELECT * FROM [Weather Data]");
List <Bus> buses = new List <Bus>();
foreach (DataRow row in busDataSet.Tables[0].Rows)
{
Bus bus = new Bus();
bus.ID = SQLUtil.ParseID(row[0]);
bus.Description = string.Empty;
buses.Add(bus);
}
List <Route> routes = new List <Route>();
foreach (DataRow row in routeDataSet.Tables[0].Rows)
{
Route route = new Route();
route.ID = SQLUtil.ParseID(row["ID"]);
string info = SQLUtil.ParseString(row["Route ID"]);
string[] infoArray = info.Split(' ');
if (infoArray.Length > 1 && infoArray[0].Length == 1)
{
route.Name = infoArray[0];
}
else
{
route.Name = info;
}
route.RouteDetail = info;
if (route.RouteDetail.Contains("M-R"))
{
route.IsMtoR = true;
}
else
{
route.IsMtoR = false;
}
routes.Add(route);
}
List <RouteDetail> routeDetails = new List <RouteDetail>();
List <string> lines = new List <string>();
//Build each route.
lines = CSVUtil.ReadCSV("A.csv");
int ARouteID = routes.First(o => o.Name == lines[0].Substring(0, 1) && o.IsMtoR == true).ID.Value;
for (int i = 2; i < lines.Count; i++) // Line 0 is route name and Line 1 is header
{
string[] line = lines[i].Split(',');
RouteDetail rd = new RouteDetail();
rd.RouteID = ARouteID;
rd.Sequence = i - 1;
rd.FromBusStopID = int.Parse(line[0]);
rd.ToBusStopID = int.Parse(line[1]);
if (rd.FromBusStopID == rd.ToBusStopID)
{
rd.IsStationary = true;
}
else
{
rd.IsStationary = false;
}
rd.Distance = double.Parse(line[2]);
rd.IdealTravelTime = int.Parse(line[3]);
rd.ScheduledTravelTime = int.Parse(line[3]);
if (rd.IsStationary)
{
rd.ScheduledStopTime = int.Parse(line[3]);
}
routeDetails.Add(rd);
}
lines.Clear();
lines = CSVUtil.ReadCSV("B.csv");
int BRouteID = routes.First(o => o.Name == lines[0].Substring(0, 1) && o.IsMtoR == true).ID.Value;
for (int i = 2; i < lines.Count; i++) // Line 0 is route name and Line 1 is header
{
string[] line = lines[i].Split(',');
RouteDetail rd = new RouteDetail();
rd.RouteID = BRouteID;
rd.Sequence = i - 1;
rd.FromBusStopID = int.Parse(line[0]);
rd.ToBusStopID = int.Parse(line[1]);
if (rd.FromBusStopID == rd.ToBusStopID)
{
rd.IsStationary = true;
}
else
{
rd.IsStationary = false;
}
rd.Distance = double.Parse(line[2]);
rd.IdealTravelTime = int.Parse(line[3]);
rd.ScheduledTravelTime = int.Parse(line[3]);
if (rd.IsStationary)
{
rd.ScheduledStopTime = int.Parse(line[3]);
}
routeDetails.Add(rd);
}
lines.Clear();
lines = CSVUtil.ReadCSV("E.csv");
int ERouteID = routes.First(o => o.Name == lines[0].Substring(0, 1) && o.IsMtoR == true).ID.Value;
for (int i = 2; i < lines.Count; i++) // Line 0 is route name and Line 1 is header
{
string[] line = lines[i].Split(',');
RouteDetail rd = new RouteDetail();
rd.RouteID = ERouteID;
rd.Sequence = i - 1;
rd.FromBusStopID = int.Parse(line[0]);
rd.ToBusStopID = int.Parse(line[1]);
if (rd.FromBusStopID == rd.ToBusStopID)
{
rd.IsStationary = true;
}
else
{
rd.IsStationary = false;
}
rd.Distance = double.Parse(line[2]);
rd.IdealTravelTime = int.Parse(line[3]);
rd.ScheduledTravelTime = int.Parse(line[3]);
if (rd.IsStationary)
{
rd.ScheduledStopTime = int.Parse(line[3]);
}
routeDetails.Add(rd);
}
lines.Clear();
lines = CSVUtil.ReadCSV("X.csv");
int XRouteID = routes.First(o => o.Name == lines[0].Substring(0, 1) && o.IsMtoR == true).ID.Value;
for (int i = 2; i < lines.Count; i++) // Line 0 is route name and Line 1 is header
{
string[] line = lines[i].Split(',');
RouteDetail rd = new RouteDetail();
rd.RouteID = ERouteID;
rd.Sequence = i - 1;
rd.FromBusStopID = int.Parse(line[0]);
rd.ToBusStopID = int.Parse(line[1]);
if (rd.FromBusStopID == rd.ToBusStopID)
{
rd.IsStationary = true;
}
else
{
rd.IsStationary = false;
}
rd.Distance = double.Parse(line[2]);
rd.IdealTravelTime = int.Parse(line[3]);
rd.ScheduledTravelTime = int.Parse(line[3]);
if (rd.IsStationary)
{
rd.ScheduledStopTime = int.Parse(line[3]);
}
routeDetails.Add(rd);
}
List <BusStop> busStops = new List <BusStop>();
//foreach (DataRow row in stopDataSet.Tables[0].Rows)
//{
// BusStop busStop = new BusStop();
// busStop.ID = SQLUtil.ParseID(row["ID"]);
// busStop.Name = SQLUtil.ParseString(row["Stop"]);
// busStop.IsMajorStop = false;
// busStops.Add(busStop);
//}
lines.Clear();
lines = CSVUtil.ReadCSV("Combined GPS Data.csv");
for (int i = 1; i < lines.Count; i++) // Line 0 is header
{
string[] line = lines[i].Split(',');
BusStop busStop = new BusStop();
busStop.ID = int.Parse(line[0]);
busStop.Name = line[1];
busStop.Latitude = double.Parse(line[2]);
busStop.Longitude = double.Parse(line[3]);
busStop.Buddy = line[4];
if (line[5].ToLower() == "yes")
{
busStop.Visible = true;
}
else
{
busStop.Visible = false;
}
if (line[6].ToLower() == "yes")
{
busStop.Announce = true;
}
else
{
busStop.Announce = false;
}
busStops.Add(busStop);
}
List <Weather> weathers = new List <Weather>();
foreach (DataRow row in weatherDataSet.Tables[0].Rows)
{
Weather weather = new Weather();
weather.ID = SQLUtil.ParseID(row["ID"]);
weather.Date = SQLUtil.ParseDateTime(row["EDT"]);
weather.MinTemp = SQLUtil.ParseInt(row["MinTemp"]);
weather.Precipitation = SQLUtil.ParseString(row["Precipitation"]);
weather.Events = SQLUtil.ParseString(row["Events"]);
weathers.Add(weather);
}
List <Interval> intervals = new List <Interval>();
foreach (DataRow row in intervalDataSet.Tables[0].Rows)
{
Interval interval = new Interval();
//interval.ID = SQLUtil.ParseID(row["ID1"]);
interval.BusID = SQLUtil.ParseInt(row["bus_id"]);
string routeName = SQLUtil.ParseString(row["route_id"]);
if (routeName == "354")
{
routeName = "A";
}
interval.RouteObj = routes.First(o => o.Name == routeName && o.IsMtoR == true);
interval.FromBusStopID = SQLUtil.ParseInt(row["from"]);
string toBusStop = SQLUtil.ParseString(row["to"]);
interval.ToBusStopID = busStops.First(o => o.Name.ToLower().Contains(toBusStop.ToLower())).ID.Value;
if (interval.FromBusStopID == interval.ToBusStopID)
{
interval.IsStationary = true;
}
else
{
interval.IsStationary = false;
}
interval.TimeInterval = SQLUtil.ParseInt(row["time"]);
interval.Timestamp = SQLUtil.ParseDateTime(row["when"]);
Weather temp = weathers.FirstOrDefault(o => o.DateString == interval.Timestamp.ToShortDateString());
if (temp != null)
{
interval.MinTemp = temp.MinTemp;
interval.Precipitation = temp.Precipitation;
interval.Events = temp.Events;
}
intervals.Add(interval);
}
DataTable rdDataTable;
if (IsMySQLDB)
{
MySQLEngine.OpenDBConnection();
MySqlCommand myCmd = new MySqlCommand();
foreach (Bus bus in buses)
{
myCmd.CommandText = "INSERT INTO TBL_Bus(ID, Description) VALUES (" + bus.ID + ", '" + bus.Description + "')";
MySQLEngine.ExecuteCommandText(myCmd);
}
foreach (Bus bus in buses)
{
myCmd.CommandText = "INSERT INTO TBL_Bus(ID, Description) VALUES (" + bus.ID + ", '" + bus.Description + "')";
MySQLEngine.ExecuteCommandText(myCmd);
}
foreach (BusStop busStop in busStops)
{
myCmd.CommandText = "INSERT INTO TBL_BusStop(ID, Name, IsMajorStop, Latitude, Longitude, Buddy, Visible, Announce) VALUES ("
+ busStop.ID + ", '" + busStop.Name.Replace("'", "''") + "', '" + busStop.IsMajorStop + "', "
+ busStop.Latitude + ", " + busStop.Longitude + ", '" + busStop.Buddy + "', '"
+ busStop.Visible + "', '" + busStop.Announce + "')";
MySQLEngine.ExecuteCommandText(myCmd);
}
foreach (Route route in routes)
{
myCmd.CommandText = "INSERT INTO TBL_Route(ID, Name, RouteDetail, IsMtoR) VALUES (" + route.ID + ", '"
+ route.Name + "', '" + route.RouteDetail + "', '" + route.IsMtoR.ToString() + "')";
MySQLEngine.ExecuteCommandText(myCmd);
}
foreach (RouteDetail rd in routeDetails)
{
myCmd.CommandText = "INSERT INTO TBL_RouteDetail(RouteID, Sequence, FromBusStopID, ToBusStopID, IsStationary, "
+ "Distance, IdealTravelTime, ScheduleStopTime, ScheduleTravelTime) VALUES ("
+ rd.RouteID + ", " + rd.Sequence + ", " + rd.FromBusStopID + ", " + rd.ToBusStopID
+ ", '" + rd.IsStationary + "', " + rd.Distance + ", " + rd.IdealTravelTime
+ ", " + rd.ScheduledStopTime + ", " + rd.ScheduledTravelTime + ")";
MySQLEngine.ExecuteCommandText(myCmd);
myCmd.CommandText = "SELECT MAX(ID) FROM TBL_RouteDetail";
rdDataTable = MySQLEngine.GetDataTableCommandText(myCmd);
rd.ID = SQLUtil.ParseID(rdDataTable.Rows[0][0]);
}
foreach (Interval interval in intervals)
{
interval.RouteDetailObj = routeDetails.FirstOrDefault(o => o.FromBusStopID == interval.FromBusStopID &&
o.ToBusStopID == interval.ToBusStopID && o.RouteID == interval.RouteID);
myCmd.CommandText = "INSERT INTO TBL_Interval(BusID, RouteID, RouteDetailID, FromBusStopID, ToBusStopID, "
+ "IsStationary, TimeInterval, Timestamp, Day, MinTemp, Precipitation, Events) VALUES ("
+ interval.BusID + ", " + interval.RouteID + ", " + interval.RouteDetailID + ", "
+ interval.FromBusStopID + ", " + interval.ToBusStopID + ", '" + interval.IsStationary + "', "
+ interval.TimeInterval + ", '" + interval.Timestamp.ToString() + "', '" + interval.Day + "', "
+ interval.MinTemp + ", '" + interval.Precipitation + "', '" + interval.Events + "')";
MySQLEngine.ExecuteCommandText(myCmd);
}
MySQLEngine.CloseDBConnection();
}
else
{
SQLEngine.OpenDBConnection();
SqlCommand cmd = new SqlCommand();
foreach (Bus bus in buses)
{
cmd.CommandText = "INSERT INTO TBL_Bus(ID, Description) VALUES (" + bus.ID + ", '" + bus.Description + "')";
SQLEngine.ExecuteCommandText(cmd);
}
foreach (Bus bus in buses)
{
cmd.CommandText = "INSERT INTO TBL_Bus(ID, Description) VALUES (" + bus.ID + ", '" + bus.Description + "')";
SQLEngine.ExecuteCommandText(cmd);
}
foreach (BusStop busStop in busStops)
{
cmd.CommandText = "INSERT INTO TBL_BusStop(ID, Name, IsMajorStop, Latitude, Longitude, Buddy, Visible, Announce) VALUES ("
+ busStop.ID + ", '" + busStop.Name.Replace("'", "''") + "', '" + busStop.IsMajorStop + "', "
+ busStop.Latitude + ", " + busStop.Longitude + ", '" + busStop.Buddy + "', '"
+ busStop.Visible + "', '" + busStop.Announce + "')";
SQLEngine.ExecuteCommandText(cmd);
}
foreach (Route route in routes)
{
cmd.CommandText = "INSERT INTO TBL_Route(ID, Name, RouteDetail, IsMtoR) VALUES (" + route.ID + ", '"
+ route.Name + "', '" + route.RouteDetail + "', '" + route.IsMtoR.ToString() + "')";
SQLEngine.ExecuteCommandText(cmd);
}
foreach (RouteDetail rd in routeDetails)
{
cmd.CommandText = "INSERT INTO TBL_RouteDetail(RouteID, Sequence, FromBusStopID, ToBusStopID, IsStationary, "
+ "Distance, IdealTravelTime, ScheduleStopTime, ScheduleTravelTime) VALUES ("
+ rd.RouteID + ", " + rd.Sequence + ", " + rd.FromBusStopID + ", " + rd.ToBusStopID
+ ", '" + rd.IsStationary + "', " + rd.Distance + ", " + rd.IdealTravelTime
+ ", " + rd.ScheduledStopTime + ", " + rd.ScheduledTravelTime + ")";
SQLEngine.ExecuteCommandText(cmd);
cmd.CommandText = "SELECT MAX(ID) FROM TBL_RouteDetail";
rdDataTable = SQLEngine.GetDataTableCommandText(cmd);
rd.ID = SQLUtil.ParseID(rdDataTable.Rows[0][0]);
}
foreach (Interval interval in intervals)
{
interval.RouteDetailObj = routeDetails.FirstOrDefault(o => o.FromBusStopID == interval.FromBusStopID &&
o.ToBusStopID == interval.ToBusStopID && o.RouteID == interval.RouteID);
cmd.CommandText = "INSERT INTO TBL_Interval(BusID, RouteID, RouteDetailID, FromBusStopID, ToBusStopID, "
+ "IsStationary, TimeInterval, Timestamp, Day, MinTemp, Precipitation, Events) VALUES ("
+ interval.BusID + ", " + interval.RouteID + ", " + interval.RouteDetailID + ", "
+ interval.FromBusStopID + ", " + interval.ToBusStopID + ", '" + interval.IsStationary + "', "
+ interval.TimeInterval + ", '" + interval.Timestamp.ToString() + "', '" + interval.Day + "', "
+ interval.MinTemp + ", '" + interval.Precipitation + "', '" + interval.Events + "')";
SQLEngine.ExecuteCommandText(cmd);
}
SQLEngine.CloseDBConnection();
}
}