/*
* 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 override void LoadStructure()
{
tables.Clear();
using (QSqlLite s = new QSqlLite(file))
{
s.Open("SELECT name FROM sqlite_master WHERE type='table'");
while (s.GetRow())
{
DbTable t = new DbTable("", s[0], "BASE TABLE");
tables.Add(t.name, t);
}
foreach (DbTable t in tables.Values)
{
s.Open("PRAGMA table_info(@1)", t.name);
while (s.GetRow())
{
string s0 = s[0];
string s1 = s[1];
string s2 = s[2];
string s3 = s[3];
string s4 = s[4];
string s5 = s[5];
// string s6 = s[6];
DbColumn c = new DbColumn(t, s[1], s[2],
s.GetBool(5),
!s.GetBool(3), s[4]);
t.columns.Add(c.name, c);
tablesByColumnName.Add(c.name, t);
}
}
}
}
public static List <QueryInfo> GetQueryInfo(IEnumerable <int> ids)
{
List <QueryInfo> result = new List <QueryInfo>();
string idList = ids.Join();
if (idList != "")
{
using (QSqlLite s = new QSqlLite())
{
// this order will put the newest versions at the bottom of the results
// so if multiple version are opened, it will end up with the latest version anyways
s.Open(@"
select t.id, t.name, q.expr, t.visible, q.id
from queries q
inner join tabs t on t.id=q.tab_id
where q.id in (" + idList + ") order by q.id");
while (s.GetRow())
{
result.Add(new QueryInfo {
queryVersionId = s.GetInt(4), visible = s.GetBool(3), queryId = s.GetInt(0), tabLabel = s.GetString(1), expr = s.GetString(2)
});
}
}
}
return(result);
}
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 void Connect(string filePath)
{
using (QSqlLite s = new QSqlLite(filePath))
{
s.Open("SELECT name FROM sqlite_master WHERE type='table'");
s.GetRow();
}
file = filePath;
databaseName = T.GetFileNameFromFilePath(filePath, true);
}
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 static int GetQueries(List <Query> result)
{
if (A.dbId == 0)
{
return(0);
}
QSqlLite.Exec("delete from queries where expr=''");
QSqlLite.Exec("delete from tabs where databaseId=@1 and id not in (select tab_id from queries)", A.dbId);
using (QSqlLite s = new QSqlLite())
{
s.Open("select t.id, t.name, (select q.id from queries q where q.tab_id=t.id order by q.id desc limit 1) from tabs t where t.databaseId=@1 and t.visible<>0 order by t.pos", A.dbId);
while (s.GetRow())
{
result.Add(new Query(s.GetInt(0), s[1], s.GetInt(2)));
}
}
return(result.Count);
}
public static List <QueryHistory> GetQueryHistory()
{
List <QueryHistory> result = new List <QueryHistory>();
string sql = "t.databaseId=@1";
if (S.Get("ShowClosedQueries", false))
{
sql = T.AppendTo(sql, "t.visible<>0", " and ");
}
if (sql != "")
{
sql = " where " + sql;
}
sql = @"
select q.id, q.tab_id, q.expr, q.when_changed, t.visible, t.name, q.failed, q.rows, q.ms
from queries q
inner join tabs t on t.id=q.tab_id " + sql + " order by q.when_changed desc limit 100";
using (QSqlLite s = new QSqlLite())
{
s.Open(sql, A.dbId);
while (s.GetRow())
{
string z = s.GetString(6);
result.Add(new QueryHistory
{
queryVersionId = s.GetInt(0),
id = s.GetInt(1),
expr = s.GetString(2),
whenChanged = Convert.ToDouble(s.GetString(3)).ToDateTime(),
visible = s.GetBool(4),
label = s.GetString(5),
failed = s.GetBool(6),
rows = s.GetInt(7),
ms = (s.GetString(8) == "" ? 0 : Convert.ToInt32(s.GetString(8)))
});
}
}
return(result);
}
public static List <KeyValuePair <string, int> > GetConnections(out int selectedIndex)
{
selectedIndex = -1;
List <KeyValuePair <string, int> > result = new List <KeyValuePair <string, int> >();
using (QSqlLite s = new QSqlLite())
{
s.Open("select databaseId, host, database, type from databases order by database, host");
while (s.GetRow())
{
string nam = s[2];
if (s[3] == "SQLite")
{
nam = T.GetFileNameFromFilePath(nam, true);
}
result.Add(new KeyValuePair <string, int>(s[1] + " / " + nam, s.GetInt(0)));
if (s.GetInt(0) == initSettings.databaseId)
{
selectedIndex = result.Count - 1;
}
}
}
return(result);
}
