/*
* public static IEnumerable<string> GetQueries()
* {
* List<string> result = new List<string>();
* using (QSqlLite s = new QSqlLite())
* {
* s.Open("select expr from queries limit 10");
* while (s.GetRow())
* yield return s.GetString(0);
* }
* }
*/
#endregion
#region connections
public static void Load()
{
BindingFlags flags = BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.GetField | BindingFlags.GetProperty | BindingFlags.Instance;
settings.Clear();
using (QSqlLite s = new QSqlLite())
{
s.Open("select name,value from settings");
while (s.GetRow())
{
settings.Add(s[0], s[1]);
}
if (A.dbId > 0)
{
s.Open("select * from tables where databaseId=@1", A.dbId);
while (s.GetRow())
{
QObject.PopulateFromRow(s, A.db.tables[s["name"]], flags);
}
s.Open("select * from table_columns where databaseId=@1", A.dbId);
while (s.GetRow())
{
QObject.PopulateFromRow(s, A.db.tables[s["tableName"]].columns[s["columnName"]], flags);
}
s.Open("select * from table_aliases where databaseId=@1", A.dbId);
while (s.GetRow())
{
DbAlias a = new DbAlias();
QObject.PopulateFromRow(s, a, flags);
A.db.tables[a.table.name].aliases.Add(a.alias, a);
A.db.tablesByAlias.AddIfNotExists(a.alias, a.table);
}
}
}
}
public static void Initialize()
{
initSettings = new InitSettings();
try
{
QSqlLite.databaseName = "sqrach.sqlite";
if (!File.Exists(QSqlLite.databaseName))
{
CreateSettingsDatabase();
}
using (QSqlLite s = new QSqlLite())
{
s.Open("select * from init_settings");
if (s.GetRow(true))
{
QObject.PopulateFromRow(s, initSettings);
if (initSettings.shutDownClean == false)
{
initSettings.databaseId = 0;
initSettings.loadDatabase = false;
}
initSettings.shutDownClean = false;
}
}
}
catch (Exception e)
{
initSettings.databaseId = 0;
initSettings.loadDatabase = false;
initSettings.initError = e.Message;
}
}
public static ConnectSettings GetConnection(int id)
{
using (QSqlLite s = new QSqlLite())
{
s.Open("select * from databases where databaseId=" + id);
if (s.GetRow())
{
ConnectSettings result = new ConnectSettings();
QObject.PopulateFromRow(s, result);
return(result);
}
}
return(null);
}
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);
}
