Beispiel #1
0
        /*
         * 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);
                    }
                }
            }
        }
Beispiel #2
0
        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);
                    }
                }
            }
        }
Beispiel #3
0
        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);
        }
Beispiel #4
0
        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;
            }
        }
Beispiel #5
0
 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);
 }
Beispiel #6
0
        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);
        }
Beispiel #7
0
        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);
        }
Beispiel #8
0
        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);
        }
Beispiel #9
0
        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);
        }