public static void OpenQuery(int id, string sql = "")
{
QSqlLite.Exec("update tabs set visible=1 where id=" + id);
if (sql != "")
{
SaveQuery(id, 0, sql, false);
}
}
public static int AddQuery(string name = "", string expr = "")
{
int pos = 1 + QSqlLite.SqlInt("select max(pos) from tabs where visible<>0 and databaseId=@1", A.dbId);
//int result = QSqlLite.Exec("update tabs set pos=pos+1;insert into tabs (name) values ('" + QSqlLite.n(name) + "');");
int result = QSqlLite.Exec("update tabs set pos=pos+1 where databaseId=@1;insert into tabs (name, pos,databaseId) values (@2,@3,@1);", A.dbId, name, pos);
if (expr != "")
{
SaveQuery(result, 0, expr, false);
}
return(result);
}
public static void SetTabPositions(Dictionary <int, int> positions)
{
string sql = "";
foreach (int tabId in positions.Keys)
{
sql += "update tabs set pos=" + positions[tabId] + " where id=" + tabId + ";";
}
if (sql != "")
{
QSqlLite.Exec(sql);
}
}
public static void SaveSettings(bool shuttingDown)
{
lock (settings)
{
A.AddToLog("saving settings");
string sql = "delete from settings;";
foreach (string key in settings.Keys)
{
sql += "insert into settings (name,value) values ('" + key + "', '" + QSqlLite.n(settings[key]) + "');";
}
QSqlLite.Exec(sql);
dirty = false;
BindingFlags flags = BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetField | BindingFlags.GetProperty;
if (A.dbId > 0)
{
sql = "delete from tables;delete from table_columns;delete from table_aliases;";
foreach (DbTable table in A.db.tables.Values)
{
if (table.hasSettings)
{
sql += QObject.RenderInsert(table, "tables", false, flags);
foreach (DbColumn column in table.columns.Values)
{
if (column.hasSettings)
{
sql += QObject.RenderInsert(column, "table_columns", false, flags);
}
}
foreach (DbAlias a in table.aliases.Values)
{
if (a.hasSettings)
{
sql += QObject.RenderInsert(a, "table_aliases", false, flags);
}
}
}
}
QSqlLite.Exec(sql);
if (shuttingDown)
{
initSettings.shutDownClean = true;
sql = "delete from init_settings;" + QObject.RenderInsert(initSettings, "init_settings", false);
QSqlLite.Exec(sql);
}
}
}
}
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 int SaveConnection(string type, string host, string database, string userId,
string password, bool savePassword, bool makeDef)
{
int id = QSqlLite.SqlInt(@"select databaseId from databases where type=@3 and host=@2 and database=@1;
", database, host, type, userId, password);
if (id == 0)
{
id = QSqlLite.Exec(@"
insert into databases (databaseId, type, host, database, user, password)
values ((select 1 + coalesce(max(databaseId),0) from databases), @3, @2, @1, @4, @5);
select databaseId from databases where type=@3 and host=@2 and database=@1;
", database, host, type, userId, password);
}
else
{
QSqlLite.Exec("update databases set password=@1 where databaseId=@2", password, id);
}
initSettings.loadDatabase = makeDef;
return(id);
}
public static int SaveQuery(int tabId, int currentQueryId, string expr, bool executing)
{
// string now = "'" + DateTime.Now.ToString("u") + "'";
double now = DateTime.Now.ToEpoch();
expr = QSqlLite.n(expr);
bool justUpdate = (currentQueryId != 0 && 0 != QSqlLite.SqlInt("select count(*) from queries where id=" + currentQueryId + " and expr='" + expr + "'"));
string sql;
if (justUpdate)
{
sql = "update queries set expr='" + expr + "',when_changed=" + now;
sql += " where id = " + currentQueryId;
QSqlLite.Exec(sql);
}
else
{
sql = "insert into queries (tab_id, expr,when_changed) values (" + tabId + ",'" + expr + "'," + now + ")";
currentQueryId = QSqlLite.Exec(sql);
}
return(currentQueryId);
}
public static void CloseQuery(int id, bool makeOpen = false)
{
QSqlLite.Exec("update tabs set visible=0 where id=" + id);
}
public static void SetQueryName(int id, string name)
{
QSqlLite.Exec("update tabs set name='" + QSqlLite.n(name) + "' where id=" + id);
}
public static void ClearSettings()
{
settings.Clear();
initSettings = new InitSettings();
QSqlLite.Exec("delete from settings;delete from tables;delete from table_columns;delete from table_aliases;");
}
public static void DeleteConnection(int id)
{
QSqlLite.Exec("delete from databases where databaseId=@1;delete from tables where databaseId=@1;delete from table_aliases where databaseId=@1;delete from table_columns where databaseId=@1;delete from tabs where databaseId=@1", id);
}
public static void SaveQueryDiagnostics(int queryId, bool succeeded, int rows, long ms)
{
QSqlLite.Exec("update queries set ms=" + ms.ToString() + ",rows=" + rows + " where id=" + queryId);
}
public static void SaveQueryFailed(int queryId)
{
QSqlLite.Exec("update queries set failed=1 where id=" + queryId);
}
public static void DeleteQueryVersion(int queryId)
{
QSqlLite.Exec("delete from queries where id=" + queryId);
}
