public override void LoadStructure()
{
tables.Clear();
using (QMySql s = new QMySql())
{
s.Open("select table_name, table_type from information_schema.tables where table_type in ('BASE TABLE','VIEW') and table_schema='" + databaseName + "'");
while (s.GetRow())
{
DbTable t = new DbTable("", s[0], s[1]);
tables.Add(t.name, t);
using (QMySql sFields = new QMySql())
{
sFields.Open("describe " + t.name);
// List<string> tableFieldNames = new List<string>();
while (sFields.GetRow())
{
DbColumn c = new DbColumn(t, sFields[0], sFields[1],
sFields[3] == "PRI",
sFields[2] == "YES", sFields[4]);
t.columns.Add(c.name, c);
tablesByColumnName.Add(c.name, t);
}
}
}
}
}
void InsertAttributes(QMySql s, string table, List <string> values, ref string sqlList)
{
foreach (string v in values)
{
sqlList = sqlList.AppendTo("'" + v + "'", ",");
s.Execute("insert ignore into " + table + " (name) values (@1)", v);
}
}
public Plant GetPlant(int id)
{
using (QMySql s = new QMySql())
{
s.Open(@"
select * from plants where id=@1", id);
if (s.GetRow())
{
Plant p = new Plant();
QObject.PopulateFromRow(s, p);
p.habitats = QMySql.SqlList(@"
select h.name
from habitats h
inner join plantHabitat p on p.habitat_id=h.id
where p.plant_id=" + id).ToList();
p.uses = QMySql.SqlList(@"
select h.name
from uses h
inner join plantUse p on p.use_id=h.id
where p.plant_id=" + id).ToList();
p.soilPreferences = QMySql.SqlList(@"
select h.name
from soilPreferences h
inner join plantSoilPreference p on p.soilPreference_id=h.id
where p.plant_id=" + id).ToList();
p.shadePreferences = QMySql.SqlList(@"
select h.name
from shadePreferences h
inner join plantShadePreference p on p.shadePreference_id=h.id
where p.plant_id=" + id).ToList();
p.images = QMySql.SqlList(@"
select p.image
from plantImages p
where p.plant_id=" + id + " order by p.seq").ToList();
p.heatMapData = GetHeatMapData(p.habitats);
return(p);
}
}
throw new ArgumentException();
}
public IEnumerable <PlantSummary> GetPlantSummaries(string sql, string typ)
{
List <PlantSummary> result = new List <PlantSummary>();
using (QMySql s = new QMySql())
{
s.Open(sql, typ);
while (s.GetRow())
{
PlantSummary p = new PlantSummary();
QObject.PopulateFromRow(s, p);
p.image = GetFirstImage(p.id);
result.Add(p);
}
}
return(result);
}
static void Main(string[] args)
{
QMySql.Connect("192.168.0.105", "plants", "dev", "willys");
QMySql.Exec("delete from plantImages;delete from plantShadePreference;delete from plantSoilPreference;delete from plantHabitat;delete from plants");
Parse g = new Parse();
g.GetUrls();
// Console.ReadLine();
foreach (string u in g.urls.Keys)
{
g.ParsePlant(u);
g.SavePlant();
Console.WriteLine("");
// Console.ReadLine();
}
foreach (string s in g.soils)
{
Console.WriteLine(s);
}
Console.ReadLine();
}
public void Connect(string host, string name, string user, string password)
{
QMySql.ConnectString = "Server=" + host + ";Database=" + name + ";Uid=" + user + ";Pwd=" + password + ";Allow User Variables=True;";
QMySql.SqlInt("select count(*) FROM INFORMATION_SCHEMA.TABLES");
databaseName = name;
}
public override int SqlInt(bool useRawValues, string sql, params object[] args)
{
return(QMySql.SqlInt(useRawValues, sql, args));
}
public override int SqlInt(string sql)
{
return(QMySql.SqlInt(sql));
}
public static void Main(string[] args)
{
QMySql.Connect("192.168.0.105", "plants", "dev", "willys");
BuildWebHost(args).Run();
}
public void SavePlant()
{
string habitats = "";
string shades = "";
string soils = "";
string uses = "";
using (QMySql s = new QMySql())
{
InsertAttributes(s, "habitats", GetContentValues("habitat"), ref habitats);
InsertAttributes(s, "shadePreferences", GetContentValues("shadePreference"), ref shades);
InsertAttributes(s, "soilPreferences", GetSoilPreferences(Content("soilTolerance")), ref soils);
InsertAttributes(s, "uses", GetPlantUses(Content("specialUses")), ref uses);
int id = s.Execute(@"
insert into plants (habitatDescription, scientificName, description, specialUses, soilTolerance, matureHeight,
commonName,plantType) values
(@1, @2, @3,
@4, @5, @6,
@7,@8)
", Content("habDescription"), Content("scientificName"), Content("description"),
Content("specialUses"), Content("soilTolerance"), Content("matureHeight"),
Content("commonName"), Content("plantType"));
int minHeight = 0;
int maxHeight = 0;
if (GetHeights(Content("matureHeight"), ref minHeight, ref maxHeight))
{
s.Execute(@"
update plants
set matureHeightMin=@1,matureHeightMax=@2
where id=@3", minHeight, maxHeight, id);
}
if (habitats != "")
{
s.Execute(@"
insert ignore into plantHabitat (plant_id, habitat_id)
select @1 as plant_id, id from habitats where name in (" + habitats + ")", id);
}
if (shades != "")
{
s.Execute(@"
insert ignore into plantShadePreference (plant_id, shadePreference_id)
select @1 as plant_id, id from shadePreferences where name in (" + shades + ")", id);
}
if (soils != "")
{
s.Execute(@"
insert ignore into plantSoilPreference (plant_id, soilPreference_id)
select @1 as plant_id, id from soilPreferences where name in (" + soils + ")", id);
}
if (uses != "")
{
s.Execute(@"
insert ignore into plantUse (plant_id, use_id)
select @1 as plant_id, id from uses where name in (" + uses + ")", id);
}
int i = 0;
List <string> images = GetContentValues("image");
foreach (string u in images)
{
i++;
string fileName = u.Substring(u.LastIndexOf('/') + 1);
string filePath = @"C:\dev\GetPlants\GetPlants\images\" + fileName;
try
{
WebClient webClient = new WebClient();
webClient.DownloadFile(u, filePath);
s.Execute(@"
insert ignore into plantImages (plant_id, image, seq) values (@1, @2, @3)", id, fileName, i);
}
catch (Exception e)
{
}
}
}
}
public IEnumerable <string> GetShadePreferences()
{
return(QMySql.SqlList(@"
select name from shadePreferences order by 1"));
}
public IEnumerable <string> GetUses()
{
return(QMySql.SqlList(@"
select name from uses order by 1"));
}
public PlantAttributeList GetHabitats()
{
return(new PlantAttributeList(QMySql.SqlList(@"
select name from habitats order by 1")));
}
public PlantAttributeList GetPlantTypes()
{
return(new PlantAttributeList(QMySql.SqlList(@"
select distinct plantType from plants order by 1")));
}
public string GetFirstImage(int plantId)
{
return(QMySql.SqlString("select image from plantImages where plant_id=" + plantId + " order by seq"));
}
