예제 #1
0
        protected override void LoadConstraints(TableStructure table)
        {
            base.LoadConstraints(table);

            TableAnalyser tad = new TableAnalyser();
            DataTable     fks = GetDbConn().GetSchema("ForeignKeys");

            foreach (DataRow row in fks.Rows)
            {
                TableAnalyser.Col col = new TableAnalyser.Col();
                col.colname    = row.SafeString("FKEY_FROM_COLUMN");
                col.dstcolname = row.SafeString("FKEY_TO_COLUMN");
                col.tblname    = row.SafeString("TABLE_NAME");
                col.dsttblname = row.SafeString("FKEY_TO_TABLE");
                if (col.dsttblname != null && col.dsttblname.StartsWith("\"") && col.dsttblname.EndsWith("\""))
                {
                    col.dsttblname = col.dsttblname.Substring(1, col.dsttblname.Length - 2);
                }
                col.ordinal = row.SafeString("FKEY_FROM_ORDINAL_POSITION");
                col.keyname = row.SafeString("CONSTRAINT_NAME");
                col.keytype = "FOREIGN KEY";
                tad.cols.Add(col);
            }
            tad.SaveConstraints(table, this);
        }
예제 #2
0
        protected override void LoadIndexes(TableStructure table)
        {
            base.LoadIndexes(table);
            if (m_indexCols == null)
            {
                m_indexCols = GetDbConn().GetSchema("IndexColumns");
            }

            TableAnalyser tadidx = new TableAnalyser();

            foreach (DataRow row in m_indexCols.Rows)
            {
                TableAnalyser.Col col = new TableAnalyser.Col();
                col.keytype     = "INDEX";
                col.keyname     = row["INDEX_NAME"].ToString();
                col.tblname     = row["TABLE_NAME"].ToString();
                col.tblschema   = row["TABLE_SCHEM"].ToString();
                col.ordinal     = row["ORDINAL_POSITION"].ToString();
                col.colname     = row["COLUMN_NAME"].ToString();
                col.keyisunique = (bool)row["UNIQUE_INDEX"];
                if (col.keyname.StartsWith("SYS_IDX"))
                {
                    continue;
                }
                if (col.tblschema.StartsWith("SYSTEM_"))
                {
                    continue;
                }
                tadidx.cols.Add(col);
            }

            tadidx.SaveConstraints(table, this);
        }
예제 #3
0
        private TableAnalyser GetRefsTA()
        {
            var res = AnalyserCache.GetTableAnalyser("refs");

            if (res != null)
            {
                return(res);
            }
            var ta = new TableAnalyser();

            foreach (var row in CachedQueryRows(SqlScripts.getrefs))
            {
                var key = new TableAnalyser.Key();
                key.keytype = "FOREIGN KEY";

                key.deleterule = row.SafeString("DELETE_RULE");

                key.keyname   = row["FK_CONSTRAINT"].ToString();
                key.tblname   = row["FK_TABLE"].ToString();
                key.tblschema = row["FK_OWNER"].ToString();

                key.dstpkschema = row["R_OWNER"].ToString();
                key.dstpkname   = row["R_CONSTRAINT"].ToString();

                key.dsttblschema = row["R_OWNER"].ToString();
                key.dsttblname   = row["R_TABLE"].ToString();

                ta.keys.Add(key);
            }

            foreach (var row in CachedQueryRows(SqlScripts.getrefcols))
            {
                var col = new TableAnalyser.Col();
                col.keytype = "FOREIGN KEY";

                col.keyname   = row["FK_CONSTRAINT"].ToString();
                col.tblname   = row["FK_TABLE"].ToString();
                col.tblschema = row["FK_OWNER"].ToString();
                col.colname   = row["FK_COLUMN_NAME"].ToString();

                col.dstcolname = row["R_COLUMN_NAME"].ToString();

                ta.cols.Add(col);
            }

            AnalyserCache.PutTableAnalyser("refs", ta);
            return(ta);
        }
예제 #4
0
        protected override void LoadIndexes(TableStructure table)
        {
            try
            {
                string tblname = table.FullName.Name;
                string schema  = table.FullName.Schema;

                DataTable                 indexlist = GetCachedTableColumnsTable("indexes", LoadIndexesTable, table.FullName);
                DataTable                 indexcols = GetCachedTableColumnsTable("indexcols", LoadIndexColsTable, table.FullName);
                TableAnalyser             tadidx    = new TableAnalyser();
                Dictionary <string, bool> dct       = new Dictionary <string, bool>();
                foreach (DataRow row in indexlist.Rows)
                {
                    TableAnalyser.Key key = new TableAnalyser.Key();
                    key.keytype      = "INDEX";
                    key.keyisunique  = Int32.Parse(row["IsUnique"].ToString()) == 1;
                    key.keyname      = row["Name"].ToString();
                    key.tblname      = tblname;
                    key.tblschema    = schema;
                    dct[key.keyname] = true;
                    tadidx.keys.Add(key);
                }
                foreach (DataRow row in indexcols.Rows)
                {
                    TableAnalyser.Col col = new TableAnalyser.Col();
                    col.keytype   = "INDEX";
                    col.keyname   = row["IndexName"].ToString();
                    col.tblname   = tblname;
                    col.tblschema = schema;
                    col.ordinal   = row["KeyOrder"].ToString();
                    col.colname   = row["ColumnName"].ToString();
                    if (!dct.ContainsKey(col.keyname))
                    {
                        continue;
                    }
                    tadidx.cols.Add(col);
                }
                tadidx.SaveConstraints(table, this);
            }
            catch (Exception err)
            {
                Logging.Warning("Error loading MSSQL indexes:" + err.ToString());
            }
            //table.FilledMembers |= TableStructureMembers.Indexes;
        }
예제 #5
0
        private TableAnalyser GetIndexesTA()
        {
            var res = AnalyserCache.GetTableAnalyser("indexes");

            if (res != null)
            {
                return(res);
            }
            var ta = new TableAnalyser();

            ta.AllowDeduceFromColumns = false;
            foreach (var row in CachedQueryRows(SqlScripts.getindexes))
            {
                var key = new TableAnalyser.Key();
                key.keytype = "INDEX";

                key.keyname   = row["INDEX_NAME"].ToString();
                key.keyschema = row["TABLE_OWNER"].ToString();

                key.tblname   = row["TABLE_NAME"].ToString();
                key.tblschema = row["TABLE_OWNER"].ToString();

                key.keyisunique = row["UNIQUENESS"].SafeToString() == "UNIQUE";

                ta.keys.Add(key);
            }

            foreach (var row in CachedQueryRows(SqlScripts.getindexcols))
            {
                var col = new TableAnalyser.Col();
                col.keytype = "INDEX";

                col.keyschema = row["USERNAME"].ToString();
                col.keyname   = row["INDEX_NAME"].ToString();
                col.colname   = row["COLUMN_NAME"].ToString();

                col.ordinal = row["COLUMN_POSITION"].ToString();

                ta.cols.Add(col);
            }

            AnalyserCache.PutTableAnalyser("indexes", ta);
            return(ta);
        }
예제 #6
0
        protected override void LoadConstraints(TableStructure table)
        {
            if (m_members.TableMembers.ContainsAny(TableStructureMembers.ConstraintsNoIndexesNoRefs))
            {
                TableAnalyser ta = new TableAnalyser();

                foreach (DataRow row in GetCachedTableColumnsTable("oracle.constraints", LoadConstraintsTable, table.FullName).Rows)
                {
                    TableAnalyser.Key key = new TableAnalyser.Key();
                    key.keytype   = OracleConstraintToDatAdmin(row["CONSTRAINT_TYPE"].ToString());
                    key.keyname   = row["CONSTRAINT_NAME"].ToString();
                    key.tblname   = row["TABLE_NAME"].ToString();
                    key.tblschema = row["OWNER"].ToString();
                    key.checkexpr = row["SEARCH_CONDITION"].SafeToString();
                    ta.keys.Add(key);
                }

                foreach (DataRow row in GetCachedTableColumnsTable("oracle.constraintcols", LoadConstraintsColsTable, table.FullName).Rows)
                {
                    TableAnalyser.Col col = new TableAnalyser.Col();
                    col.keyname   = row["CONSTRAINT_NAME"].ToString();
                    col.tblname   = row["TABLE_NAME"].ToString();
                    col.tblschema = row["OWNER"].ToString();
                    col.ordinal   = row["POSITION"].ToString();
                    col.colname   = row["COLUMN_NAME"].ToString();
                    ta.cols.Add(col);
                }

                ta.SaveConstraints(table, this);
            }

            if (m_members.TableMembers.ContainsAny(TableStructureMembers.ForeignKeys | TableStructureMembers.ReferencedFrom))
            {
                GetRefsTA().SaveConstraints(table, this);
            }

            if (m_members.TableMembers.ContainsAny(TableStructureMembers.Indexes))
            {
                GetIndexesTA().SaveConstraints(table, this);
            }
        }
예제 #7
0
        protected override void LoadConstraints(TableStructure table)
        {
            OleDbConnection c = (OleDbConnection)GetDbConn();

            TableAnalyser tad = new TableAnalyser();
            DataTable     pks = c.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, null);

            foreach (DataRow row in pks.Rows)
            {
                if (row["TABLE_NAME"].ToString() != table.FullName.Name)
                {
                    continue;
                }
                TableAnalyser.Col col = new TableAnalyser.Col();
                col.keytype = "PRIMARY KEY";
                col.ordinal = row["ORDINAL"].ToString();
                col.keyname = row["PK_NAME"].ToString();
                col.colname = row["COLUMN_NAME"].ToString();
                col.tblname = row["TABLE_NAME"].ToString();
                tad.cols.Add(col);
            }

            DataTable fks = c.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, null);

            foreach (DataRow row in fks.Rows)
            {
                TableAnalyser.Col col = new TableAnalyser.Col();
                col.keytype    = "FOREIGN KEY";
                col.keyname    = row["FK_NAME"].ToString();
                col.ordinal    = row["ORDINAL"].ToString();
                col.colname    = row["FK_COLUMN_NAME"].ToString();
                col.tblname    = row["FK_TABLE_NAME"].ToString();
                col.dstcolname = row["PK_COLUMN_NAME"].ToString();
                col.dsttblname = row["PK_TABLE_NAME"].ToString();
                tad.cols.Add(col);
            }

            tad.SaveConstraints(table, this);
            //table.FilledMembers |= m_members.TableMembers.FilterConstraints(false);
        }
예제 #8
0
        protected override void LoadIndexes(TableStructure table)
        {
            base.LoadIndexes(table);

            TableAnalyser tad = new TableAnalyser();

            DataTable idxs = GetDbConn().GetSchema("Indexes");

            foreach (DataRow row in idxs.Rows)
            {
                TableAnalyser.Key key = new TableAnalyser.Key();
                if ((bool)row["PRIMARY_KEY"])
                {
                    continue;
                }
                key.tblname = row["TABLE_NAME"].ToString();
                key.keyname = row["INDEX_NAME"].ToString();
                key.keytype = "INDEX";
                tad.keys.Add(key);
            }

            DataTable idxcols = GetDbConn().GetSchema("IndexColumns");

            foreach (DataRow row in idxcols.Rows)
            {
                TableAnalyser.Col col = new TableAnalyser.Col();
                col.colname = row["COLUMN_NAME"].ToString();
                col.tblname = row["TABLE_NAME"].ToString();
                col.keyname = row["CONSTRAINT_NAME"].ToString();
                col.ordinal = row["ORDINAL_POSITION"].ToString();
                col.keytype = "INDEX";
                tad.cols.Add(col);
            }
            tad.SaveConstraints(table, this);
            table._Constraints.RemoveIf(cnt => cnt.Name != null && cnt.Name.StartsWith("sqlite_"));
        }