Exemplo n.º 1
0
        public void AddColumnDublicateTest()
        {
            var idx = new Index("IDX_TEST", "testTable");

            idx.AddColumn(new IndexColumn("Test"));
            Assert.Throws <DublicateColumnException>(() => idx.AddColumn(new IndexColumn("Test")));
        }
Exemplo n.º 2
0
        public void CreateIndexWithSameColumnTwoTimes_GetCorrectException()
        {
            // Arrange
            var index = new Index("MyIndexName", "MyTable");

            // Act
            index.AddColumn("MyColumn");

            // Assert
            var ex = Assert.Throws<ArgumentException>(() => index.AddColumn("MyColumn"));
            Assert.Equal("The column MyColumn is already added to index MyIndexName on table MyTable", ex.Message);
        }
Exemplo n.º 3
0
        public void CreateIndexWithTwoColumns_GetValidCreateScript()
        {
            // Arrange
            var index = new Index("MyIndexName", "MyTable");

            // Act
            index.AddColumn("MyColumn");
            index.AddColumn("MyOtherColumn");

            // Assert
            Assert.Equal(1, index.GetUpScript().Count);
            Assert.Equal(2, index.Columns.Count);
            Assert.Equal("CREATE INDEX MyIndexName \r\nON MyTable(MyColumn, MyOtherColumn)\r\n", index.GetUpScript().First());
        }
Exemplo n.º 4
0
        public void AddColumnSameObjectTest()
        {
            var idx  = new Index("IDX_TEST", "testTable");
            var idx2 = idx.AddColumn(new IndexColumn("testcol"));

            Assert.AreSame(idx, idx2);
        }
Exemplo n.º 5
0
        public void AddColumnTest()
        {
            var idx = new Index("IDX_TEST", "testTable");

            idx.AddColumn(new IndexColumn("Test"));
            Assert.AreEqual(1, idx.Columns.Count);
        }
Exemplo n.º 6
0
            public void The_Original_Index_Should_Be_Modified()
            {
                var db1 = TestDatabaseLoader.TestDatabase();

                IIndex originalIndex = db1.Tables[0].Indexes[0];

                originalIndex.Description = "old description";

                IIndex newIndex = new Index(originalIndex.Name);

                newIndex.Parent = new Table("Table1");
                newIndex.Parent.AddColumn(new Column("Column2"));
                newIndex.Description = "new description";
                newIndex.AddColumn("Column2");

                IndexChangeOperation op = new IndexChangeOperation(db1.Tables[0].Indexes[0], newIndex);

                op.RunOperation();
                op.RunSecondStep();

                IIndex index = db1.Tables[0].Indexes[0];

                Assert.That(db1.Tables[0].Indexes, Has.Count(1), "No new indexes should have been added");
                Assert.That(index, Is.SameAs(originalIndex), "Index object should still be the same");
                Assert.That(index.Description, Is.EqualTo("old description"));
                Assert.That(index.Columns, Has.Count(1));
                Assert.That(index.Columns[0].Name, Is.EqualTo("Column2"));
                Assert.That(index.Columns[0], Is.Not.SameAs(newIndex.Columns[0]));
                Assert.That(index.Columns[0], Is.SameAs(db1.Tables[0].Columns[1]), "The new Index should reference existing columns");
                Assert.That(index.Parent, Is.SameAs(db1.Tables[0]), "The new Index's parent should be Table1 in the existing database");
            }
Exemplo n.º 7
0
        public override List <Index> GetIndices(string schemaName)
        {
            var stmt = String.Format(
                @"select
                    sta.table_schema as schema_name,
                    sta.table_name,
                    sta.index_name,
                    sta.column_name,
                    sta.seq_in_index as column_index,
                    sta.non_unique
                  from   information_schema.statistics sta
                  where  sta.table_schema = '{0}'",
                schemaName);
            var ds    = this.ExecuteSelect(stmt);
            var cList = new Utils.ChainedList <Index>();

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                var index = new Index(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["index_name"].ToString());
                index.IsUnique = !Convert.ToBoolean(dr["non_unique"]);
                index.AddColumn(Convert.ToInt32(dr["column_index"]), dr["column_name"].ToString());
                Index tmp;
                if (!cList.Add(index, out tmp))
                {
                    tmp.AddCoumns(index.IndexOrderdColumnNames);
                }
            }
            return(cList.ToList());
        }
Exemplo n.º 8
0
        public override List <Index> GetIndices(string schemaName)
        {
            var stmt = String.Format(
                @"select
                    owner,
                    index_name,
                    ind.table_name,
                    ind.uniqueness,
                    col.column_name,
                    col.column_position
                from all_indexes ind
                inner join (select * from all_ind_columns where index_owner = '{0}') col using (index_name)
                where ind.owner = '{0}'
                and ind.table_type = 'TABLE'
                order by index_name, column_position", schemaName);

            using (var ds = this.ExecuteSelect(stmt))
            {
                var cList = new Utils.ChainedList <Index>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    var index = new Index(dr["owner"].ToString(), dr["table_name"].ToString(), dr["index_name"].ToString());
                    index.IsUnique = (dr["uniqueness"].ToString() == "UNIQUE");
                    index.AddColumn(Convert.ToInt32(dr["column_position"]), dr["column_name"].ToString());
                    Index tmp;
                    if (!cList.Add(index, out tmp))
                    {
                        tmp.AddCoumns(index.IndexOrderdColumnNames);
                    }
                }
                return(cList.ToList());
            }
        }
Exemplo n.º 9
0
        public void CreateIndexWithOneColumn_GetValidCreateScript()
        {
            // Arrange
            var index = new Index<MyTable>("MyIndexName", "MyTable");

            // Act
            index.AddColumn(m => m.MyColumn);

            // Assert
            Assert.Equal(1, index.GetUpScript().Count);
            Assert.Equal("CREATE INDEX MyIndexName \r\nON MyTable(MyColumn)\r\n", index.GetUpScript().First());
        }
Exemplo n.º 10
0
        override internal void LoadAll()
        {
            try
            {
                string select = @"SELECT current_database() as table_catalog, tab.relname AS table_name, " +
                                "n.nspname as TABLE_NAMESPACE, cls.relname as INDEX_NAME, idx.indisunique as UNIQUE, " +
                                "idx.indisclustered as CLUSTERED, a.amname as TYPE, indkey AS columns FROM pg_index idx " +
                                "JOIN pg_class cls ON cls.oid=indexrelid " +
                                "JOIN pg_class tab ON tab.oid=indrelid AND tab.relname = '" + this.Table.Name + "' " +
                                "JOIN pg_namespace n ON n.oid=tab.relnamespace AND n.nspname = '" + this.Table.Schema + "' " +
                                "JOIN pg_am a ON a.oid = cls.relam " +
                                "LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0') " +
                                "LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid) " +
                                "WHERE con.conname IS NULL ORDER BY cls.relname;";

                NpgsqlConnection cn = new Npgsql.NpgsqlConnection(this.dbRoot.ConnectionString);

                NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(select, cn);
                cn.Open();
                cn.ChangeDatabase(this.Table.Tables.Database.Name);
                DataTable metaData = new DataTable();

                adapter.Fill(metaData);
                cn.Close();

                PopulateArrayNoHookup(metaData);

                for (int i = 0; i < this.Count; i++)
                {
                    Index index = this[i] as Index;

                    if (null != index)
                    {
                        string   s          = index._row["columns"] as string;
                        string[] colIndexes = s.Split(' ');

                        foreach (string colIndex in colIndexes)
                        {
                            if (colIndex != "0")
                            {
                                int id = Convert.ToInt32(colIndex);

                                Column column = this.Table.Columns[id - 1] as Column;
                                index.AddColumn(column.Name);
                            }
                        }
                    }
                }
            }
            catch {}
        }
Exemplo n.º 11
0
        public override List <Index> GetIndices(string schemaName)
        {
            var stmt = String.Format(
                @"select
	                ixs.schemaname as schema_name,
	                ixs.tablename as table_name,
	                ixs.indexname as index_name,
	                jo.attname as column_name,
	                jo.attnum as column_index,
	                jo.indkey as index_order,
	                jo.indisunique as is_unique
                from pg_catalog.pg_indexes as ixs
                inner join (
	                select a.attname, a.attnum, ix.indkey, ix.indisunique, i.relname
	                from pg_catalog.pg_class as t,
		                pg_catalog.pg_class as i,
		                pg_catalog.pg_index as ix,
		                pg_catalog.pg_attribute as a
	                where a.attnum > 0
                    and t.oid = ix.indrelid
	                and i.oid = ix.indexrelid
	                and a.attrelid = t.oid
	                and a.attnum = ANY(ix.indkey)) as jo
                on ixs.indexname = jo.relname
                where ixs.schemaname = '{0}'",
                schemaName);
            var ds    = this.ExecuteSelect(stmt);
            var cList = new Utils.ChainedList <Index>();

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                var index = new Index(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["index_name"].ToString());
                index.IsUnique = Convert.ToBoolean(dr["is_unique"]);
                var indexPos = dr["index_order"].ToString().Split(' ').ToArray();
                for (int i = 0; i < indexPos.Length; i++)
                {
                    if (Convert.ToInt32(dr["column_index"].ToString()) == Convert.ToInt32(indexPos[i]))
                    {
                        index.AddColumn(i + 1, dr["column_name"].ToString());
                        break;
                    }
                }
                Index tmp;
                if (!cList.Add(index, out tmp))
                {
                    tmp.AddCoumns(index.IndexOrderdColumnNames);
                }
            }
            return(cList.ToList());
        }
Exemplo n.º 12
0
        public void CreateIndexWithOneColumn_GetOneColumnAdded()
        {
            // Arrange
            var index = new Index<MyTable>("MyIndexName", "MyTable");

            // Act
            index.AddColumn(m => m.MyColumn);

            // Assert
            Assert.Equal("MyIndexName", index.Name);
            Assert.Equal("MyTable", index.TableName);
            Assert.Equal(1, index.Columns.Count);
            Assert.Equal("MyColumn", index.Columns.First());
        }
Exemplo n.º 13
0
        internal void GetTableConstraintIndexes(Table table)
        {
            DataRow[] indexRows = connector.Indexes.Select(string.Format("TABLE_NAME = '{0}'", table.Name.Replace("'", "''")));
            //foreach (DataRow indexRow in indexRows)
            for (int i = 0; i < indexRows.Length; i++)
            {
                DataRow           indexRow  = indexRows[i];
                DatabaseIndexType indexType = GetIndexType(indexRow["CONSTRAINT_TYPE"].ToString());

                var indexColumnRows = new List <DataRow>();

                indexColumnRows.AddRange(connector.Columns.Select(string.Format("TABLE_NAME = '{0}' AND COLUMN_NAME  = '{1}'", table.Name.Replace("'", "''"), indexRow["COLUMN_NAME"])));

                while ((i < indexRows.Length - 1) && (string)indexRows[i + 1]["TABLE_NAME"] == table.Name && (string)indexRows[i + 1]["INDEX_NAME"] == (string)indexRow["INDEX_NAME"])
                {
                    i++;
                    indexRow = indexRows[i];
                    indexColumnRows.AddRange(connector.Columns.Select(string.Format("TABLE_NAME = '{0}' AND COLUMN_NAME  = '{1}'", table.Name.Replace("'", "''"), indexRow["COLUMN_NAME"])));
                }
                bool  isUnique    = (bool)indexRow["ISUNIQUE"];
                bool  isClustered = (bool)indexRow["ISCLUSTERED"];
                Index index       = new Index
                {
                    Name          = indexRow["INDEX_NAME"].ToString(),
                    IsUserDefined = false,
                    IndexType     = indexType,
                    Parent        = table,
                    IsUnique      = isUnique,
                    IsClustered   = isClustered
                };

                // Fill Columns
                foreach (DataRow indexColumnRow in indexColumnRows)
                {
                    var column = index.AddColumn((string)indexColumnRow["COLUMN_NAME"]);

                    if (index.IsUnique && indexColumnRows.Count == 1)
                    {
                        // Columns are only unique if the index has one column
                        column.IsUnique = true;
                    }
                }
                index.ResetDefaults();
                table.AddIndex(index);
            }
        }
Exemplo n.º 14
0
        public void Should_Return_This()
        {
            const string expectedXML = IndexWithColumnsXml;

            Table table1 = new Table("Table1");

            table1.AddColumn(new Column("Column1"));
            Index index = new Index("Index1");

            index.Parent = table1;
            index.AddColumn("Column1");

            string outputXML = index.Serialise(new DatabaseSerialisationScheme());

            outputXML = XmlSqueezer.RemoveWhitespaceBetweenElements(outputXML);
            Assert.That(outputXML, Is.EqualTo(expectedXML));
        }
Exemplo n.º 15
0
        public override List <DBObjects.Index> GetIndices(string schemaName)
        {
            var stmt = String.Format(
                @"SELECT 
                  i.rdb$relation_name AS TableName,
                  s.rdb$field_name AS FieldName,
                  i.rdb$index_name AS IndexName,
                  rc.rdb$constraint_name AS AltIndexName,
                  i.rdb$foreign_key AS IsForeignKey,
                  i.rdb$unique_flag AS IsUnique,
                  s.rdb$field_position+1 AS FieldPosition,
                  rc.rdb$deferrable AS Deferrable,
                  rc.rdb$initially_deferred AS InitiallyDeferred
                FROM rdb$indices i
                  LEFT JOIN rdb$index_segments s ON (i.rdb$index_name = s.rdb$index_name)
                  LEFT JOIN rdb$relation_constraints rc ON (i.rdb$index_name = rc.rdb$index_name)
                WHERE 
                  i.rdb$system_flag = 0 AND
                  s.rdb$field_name IS NOT NULL");

            using (var ds = this.ExecuteSelect(stmt))
            {
                var cList = new Utils.ChainedList <Index>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    string index_name;
                    if (dr["AltIndexName"] == DBNull.Value)
                    {
                        index_name = dr["IndexName"].ToString().Trim();
                    }
                    else
                    {
                        index_name = dr["AltIndexName"].ToString().Trim();
                    }
                    var ix = new Index(this._schemaName, dr["TableName"].ToString().Trim(), index_name);
                    ix.IsUnique = (dr["IsUnique"] != DBNull.Value) ? true : false;
                    ix.AddColumn(Convert.ToInt32(dr["FieldPosition"]), dr["FieldName"].ToString().Trim());
                    Index tmp;
                    if (!cList.Add(ix, out tmp))
                    {
                        tmp.AddCoumns(ix.IndexOrderdColumnNames);
                    }
                }
                return(cList.ToList());
            }
        }
Exemplo n.º 16
0
        internal void GetTableConstraintIndexes(Table table)
        {
            List <DataRow> indexRows = connector.GetIndexRows(table.Schema, table.Name.Replace("'", "''"));

            for (int i = 0; i < indexRows.Count; i++)
            {
                DataRow           indexRow  = indexRows[i];
                DatabaseIndexType indexType = GetIndexType(indexRow["CONSTRAINT_TYPE"].ToString());

                var indexColumnRows = new List <DataRow>();

                indexColumnRows.AddRange(connector.ColumnsBySchema[table.Schema][table.Name.Replace("'", "''")].Where(c => c[OrdColumn].ToString() == indexRow["COLUMN_NAME"].ToString()));

                while ((i < indexRows.Count - 1) && (string)indexRows[i + 1]["TABLE_NAME"] == table.Name && (string)indexRows[i + 1]["INDEX_NAME"] == (string)indexRow["INDEX_NAME"])
                {
                    i++;
                    indexRow = indexRows[i];
                    indexColumnRows.AddRange(connector.ColumnsBySchema[table.Schema][table.Name.Replace("'", "''")].Where(c => c[OrdColumn].ToString() == indexRow["COLUMN_NAME"].ToString()));
                }
                bool  isUnique    = Convert.ToInt32(indexRow["IS_UNIQUE"]) == 1;
                bool  isClustered = Convert.ToInt32(indexRow["IS_CLUSTERED"]) == 1;
                Index index       = new Index
                {
                    Name          = indexRow["INDEX_NAME"].ToString(),
                    IsUserDefined = false,
                    IndexType     = indexType,
                    Parent        = table,
                    IsUnique      = isUnique,
                    IsClustered   = isClustered
                };

                // Fill Columns
                foreach (DataRow indexColumnRow in indexColumnRows)
                {
                    var column = index.AddColumn((string)indexColumnRow["COLUMN_NAME"]);

                    if (index.IsUnique && indexColumnRows.Count == 1)
                    {
                        // Columns are only unique if the index has one column
                        column.IsUnique = true;
                    }
                }
                index.ResetDefaults();
                table.AddIndex(index);
            }
        }
Exemplo n.º 17
0
        private void GetUniqueIndexes(Table table)
        {
            DataRow[] indexRows = connector.GetUniqueIndexes().Select(string.Format("TABLE_NAME = '{0}'", table.Name.Replace("'", "''")));

            for (int i = 0; i < indexRows.Length; i++)
            {
                DataRow indexRow = indexRows[i];

                Index index = new Index
                {
                    Name          = (string)indexRow["INDEX_NAME"],
                    IsUserDefined = false,
                    IsUnique      = true,
                    IsClustered   = (bool)indexRow["ISCLUSTERED"],
                    IndexType     = DatabaseIndexType.Unique,
                    Parent        = table
                };

                List <DataRow> indexColumnRows = new List <DataRow>();

                indexColumnRows.AddRange(connector.Columns.Select(string.Format("TABLE_NAME = '{0}' AND COLUMN_NAME  = '{1}'", table.Name.Replace("'", "''"), indexRow["COLUMN_NAME"])));

                while ((i < indexRows.Length - 1) && (string)indexRows[i + 1]["TABLE_NAME"] == table.Name && (string)indexRows[i + 1]["INDEX_NAME"] == (string)indexRow["INDEX_NAME"])
                {
                    i++;
                    indexRow = indexRows[i];
                    indexColumnRows.AddRange(connector.Columns.Select(string.Format("TABLE_NAME = '{0}' AND COLUMN_NAME  = '{1}'", table.Name.Replace("'", "''"), indexRow["COLUMN_NAME"])));
                }

                // Fill Columns
                foreach (DataRow indexColumnRow in indexColumnRows)
                {
                    var column = index.AddColumn((string)indexColumnRow["COLUMN_NAME"]);

                    if (index.IsUnique && indexColumnRows.Count == 1)
                    {
                        // Columns are only unique if the index has one column
                        column.IsUnique = true;
                    }
                }

                index.ResetDefaults();
                table.AddIndex(index);
            }
        }
Exemplo n.º 18
0
        public override List <Index> GetIndices(string schemaName)
        {
            var stmt = String.Format(
                @"select
	                '{0}' as schema_name,
	                tab.name as table_name,
	                inx.name as index_name,
	                col.name as column_name,
	                ixcol.key_ordinal as column_index,
	                inx.is_unique
                from sys.objects as obj
                inner join sys.indexes as inx
                on obj.object_id = inx.object_id
                inner join sys.index_columns as ixcol
                on inx.object_id = ixcol.object_id
	                and inx.index_id = ixcol.index_id
                inner join sys.columns as col
                on obj.object_id = col.object_id
	                and ixcol.column_id = col.column_id
                inner join sys.tables as tab
                on obj.object_id = tab.object_id
                where obj.schema_id = (select schema_id from sys.schemas where name = '{0}')
	                and inx.is_disabled = 0
	                and inx.name is not Null
                    and ixcol.is_included_column = 0",
                schemaName);
            var ds    = this.ExecuteSelect(stmt);
            var cList = new Utils.ChainedList <Index>();

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                var index = new Index(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["index_name"].ToString());
                index.IsUnique = Convert.ToBoolean(dr["is_unique"]);
                index.AddColumn(Convert.ToInt32(dr["column_index"]), dr["column_name"].ToString());
                Index tmp;
                if (!cList.Add(index, out tmp))
                {
                    tmp.AddCoumns(index.IndexOrderdColumnNames);
                }
            }
            return(cList.ToList());
        }
Exemplo n.º 19
0
        public override List <DBObjects.Index> GetIndices(string schemaName)
        {
            var stmt = String.Format(
                @"select
	                inx.tabschema as schema_name,
	                inx.tabname as table_name,
	                inx.indname as index_name,
	                inxcol.colname as column_name,
	                inxcol.colseq as column_position,
	                case (inx.uniquerule)
	                    when 'P' then 'Y'
	                    when 'U' then 'Y'
	                    when 'D' then 'N'
	                end as is_unique
                from
	                syscat.indexes as inx,
	                syscat.indexcoluse inxcol
                where
	                inxcol.indschema = inx.tabschema
	                and inxcol.indname = inx.indname
	                and inx.tabschema = '{0}'
	                and inx.indextype in ('BLOK', 'CLUS', 'DIM', 'REG')
	                order by inxcol.indname, inxcol.colseq"    , schemaName);
            var ds    = this.ExecuteSelect(stmt);
            var cList = new Utils.ChainedList <Index>();

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                var index = new Index(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["index_name"].ToString());
                index.IsUnique = dr["is_unique"].ToString().ToLower() == "y";
                index.AddColumn(Convert.ToInt32(dr["column_position"]), dr["column_name"].ToString());
                Index tmp;
                if (!cList.Add(index, out tmp))
                {
                    tmp.AddCoumns(index.IndexOrderdColumnNames);
                }
            }
            return(cList.ToList());
        }
Exemplo n.º 20
0
            public void The_Original_Database_Should_Contain_A_Copy_Of_The_New_Index()
            {
                var db1 = TestDatabaseLoader.TestDatabase();

                // Setup new key information
                Table parentTable = new Table("Table1");

                parentTable.AddColumn(new Column("Column3"));
                Index newIndex = new Index("UQ_Column3");

                newIndex.Parent = parentTable;
                newIndex.AddColumn("Column3");

                IndexAdditionOperation op = new IndexAdditionOperation(db1.Tables[0], newIndex);

                op.RunOperation();
                op.RunSecondStep();

                Assert.That(db1.Tables[0].Indexes, Has.Count(2));
                Assert.That(db1.Tables[0].Indexes[1].Name, Is.EqualTo(newIndex.Name));
                Assert.That(db1.Tables[0].Indexes[1], Is.Not.SameAs(newIndex), "The added Index should be a copy, not the original object.");
                Assert.That(db1.Tables[0].Indexes[1].Columns[0], Is.SameAs(db1.Tables[0].Columns[2]), "The new Index should reference existing columns, not the new ones");
                Assert.That(db1.Tables[0].Indexes[1].Parent, Is.SameAs(db1.Tables[0]), "The new Index's parent should be Table1 in the existing database");
            }
Exemplo n.º 21
0
        private Model.Table GetNewTable(SQLDMO.Table dmoTable)
        {
            Model.Table table = new Model.Table(dmoTable.Name, Script.GetSingluar(dmoTable.Name), false);

            // Columns
            int ordinalPosition             = 0;
            List <SQLDMO.Column> dmoColumns = new List <SQLDMO.Column>();

            foreach (SQLDMO.Column dmoColumn in dmoTable.Columns)
            {
                dmoColumns.Add(dmoColumn);
            }

            foreach (SQLDMO.Column dmoColumn in dmoColumns)
            {
                if (UnsupportedDataTypes.ToLower().IndexOf("'" + dmoColumn.PhysicalDatatype.ToLower() + "'") >= 0)
                {
                    continue;
                }

                Column column = new Column(dmoColumn.Name, Script.GetSingluar(dmoColumn.Name), false, dmoColumn.Name, table, ordinalPosition, dmoColumn.AllowNulls, dmoColumn.PhysicalDatatype, dmoColumn.Length,
                                           dmoColumn.InPrimaryKey, dmoColumn.Identity, dmoColumn.Default, dmoColumn.IsComputed);
                table.AddColumn(column);
                ordinalPosition++;
            }

            // Index
            foreach (SQLDMO.Index dmoIndex in dmoTable.Indexes)
            {
                string indexType;
                if (dmoIndex.Type == SQLDMO.SQLDMO_INDEX_TYPE.SQLDMOIndex_DRIPrimaryKey)
                {
                    continue;
                }
                if (dmoIndex.Type == SQLDMO.SQLDMO_INDEX_TYPE.SQLDMOIndex_DRIUniqueKey)
                {
                    continue;
                }
                else
                {
                    continue;
                    //throw new Exception("IndexType " + dmoIndex.Type + " Not Defined");
                }

                // Create Alias
                string indexAlias            = indexType + "_";
                SQLDMO.SQLObjectList indexes = dmoIndex.ListIndexedColumns();

                for (int i = 1; i <= indexes.Count; i++)
                {
                    SQLDMO._Column dmoColumn = (SQLDMO._Column)indexes.Item(i);

                    indexAlias += dmoColumn.Name;
                    if (i < indexes.Count)
                    {
                        indexAlias += "And";
                    }
                }

                Index index = new Index(dmoIndex.Name, Script.GetSingluar(indexAlias), false, indexType, table);

                // Fill Columns
                for (int i = 1; i <= indexes.Count; i++)
                {
                    SQLDMO._Column dmoColumn = (SQLDMO._Column)indexes.Item(i);

                    Column indexColumn = new Column(dmoColumn.Name, Script.GetSingluar(dmoColumn.Name), false);
                    index.AddColumn(indexColumn);
                }

                table.AddIndex(index);
            }

            // Indexes -- that should be keys
            foreach (SQLDMO.Index dmoIndex in dmoTable.Indexes)
            {
                string keyType;
                if (dmoIndex.Type == SQLDMO.SQLDMO_INDEX_TYPE.SQLDMOIndex_DRIPrimaryKey)
                {
                    keyType = DatabaseConstant.KeyType.Primary;
                }
                if (dmoIndex.Type == SQLDMO.SQLDMO_INDEX_TYPE.SQLDMOIndex_DRIUniqueKey)
                {
                    keyType = DatabaseConstant.KeyType.Unique;
                }
                else
                {
                    continue;
                    //throw new Exception("KeyType " + dmoIndex.Type + " Not Defined");
                }

                // Create Alias
                string keyAlias = keyType + "_";
                SQLDMO.SQLObjectList indexes = dmoIndex.ListIndexedColumns();

                for (int i = 1; i <= indexes.Count; i++)
                {
                    SQLDMO._Column dmoColumn = dmoTable.Columns.Item(indexes.Item(i));

                    keyAlias += dmoColumn.Name;
                    if (i < indexes.Count)
                    {
                        keyAlias += "And";
                    }
                }

                Key key = new Key(dmoIndex.Name, Script.GetSingluar(keyAlias), false, keyType, table);

                // Fill Columns
                for (int i = 1; i <= indexes.Count; i++)
                {
                    Column keyColumn = new Column(indexes.Item(i).ToString(), Script.GetSingluar(indexes.Item(i).ToString()), false);
                    key.AddColumn(keyColumn);
                }

                table.AddKey(key);
            }

            // Keys
            foreach (SQLDMO.Key dmoKey in dmoTable.Keys)
            {
                string keyType;
                if (dmoKey.Type == SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Primary)
                {
                    keyType = DatabaseConstant.KeyType.Primary;
                }
                else if (dmoKey.Type == SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Foreign)
                {
                    keyType = DatabaseConstant.KeyType.Foreign;
                }
                else if (dmoKey.Type == SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Unique)
                {
                    keyType = DatabaseConstant.KeyType.Unique;
                }
                else if (dmoKey.Type == SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Unknown)
                {
                    continue;
                }
                else
                {
                    throw new Exception("KeyType " + dmoKey.Type.ToString() + " Not Defined");
                }

                // Create Alias
                string keyAlias = keyType + "_";
                for (int i = 1; i <= dmoKey.KeyColumns.Count; i++)
                {
                    SQLDMO._Column dmoColumn = dmoTable.Columns.Item(dmoKey.KeyColumns.Item(i));

                    keyAlias += dmoColumn.Name;
                    if (i < dmoKey.KeyColumns.Count)
                    {
                        keyAlias += "And";
                    }
                }

                Key key = new Key(dmoKey.Name, Script.GetSingluar(keyAlias), false, keyType, table);

                // Fill Columns
                for (int i = 1; i <= dmoKey.KeyColumns.Count; i++)
                {
                    Column keyColumn = new Column(dmoKey.KeyColumns.Item(i), Script.GetSingluar(dmoKey.KeyColumns.Item(i)), false);
                    key.AddColumn(keyColumn);
                }

                if (keyType == DatabaseConstant.KeyType.Foreign)
                {
                    // Fill References
                    key.ReferencedTable = new Model.Table(dmoKey.ReferencedTable, Script.GetSingluar(dmoKey.ReferencedTable), false);

                    if (dmoKey.ReferencedKey != null)
                    {
                        key.ReferencedKey = new Key(dmoKey.ReferencedKey, Script.GetSingluar(dmoKey.ReferencedKey), false);
                    }

                    // Fill Referenced Columns
                    for (int i = 1; i <= dmoKey.KeyColumns.Count; i++)
                    {
                        Column referencedKeyColumn = new Column(dmoKey.ReferencedColumns.Item(i), Script.GetSingluar(dmoKey.ReferencedColumns.Item(i)), false);
                        key.AddReferencedColumn(referencedKeyColumn);
                    }
                }

                table.AddKey(key);
            }

            return(table);
        }
            public void The_Original_Database_Should_Contain_A_Copy_Of_The_New_Index()
            {
                var db1 = TestDatabaseLoader.TestDatabase();

                // Setup new key information
                Table parentTable = new Table("Table1");
                parentTable.AddColumn(new Column("Column3"));
                Index newIndex = new Index("UQ_Column3");
                newIndex.Parent = parentTable;
                newIndex.AddColumn("Column3");

                IndexAdditionOperation op = new IndexAdditionOperation(db1.Tables[0], newIndex);
                op.RunOperation();
                op.RunSecondStep();

                Assert.That(db1.Tables[0].Indexes, Has.Count(2));
                Assert.That(db1.Tables[0].Indexes[1].Name, Is.EqualTo(newIndex.Name));
                Assert.That(db1.Tables[0].Indexes[1], Is.Not.SameAs(newIndex), "The added Index should be a copy, not the original object.");
                Assert.That(db1.Tables[0].Indexes[1].Columns[0], Is.SameAs(db1.Tables[0].Columns[2]), "The new Index should reference existing columns, not the new ones");
                Assert.That(db1.Tables[0].Indexes[1].Parent, Is.SameAs(db1.Tables[0]), "The new Index's parent should be Table1 in the existing database");
            }
            public void The_Original_Index_Should_Be_Modified()
            {
                var db1 = TestDatabaseLoader.TestDatabase();

                IIndex originalIndex = db1.Tables[0].Indexes[0];
                originalIndex.Description = "old description";

                IIndex newIndex = new Index(originalIndex.Name);
                newIndex.Parent = new Table("Table1");
                newIndex.Parent.AddColumn(new Column("Column2"));
                newIndex.Description = "new description";
                newIndex.AddColumn("Column2");

                IndexChangeOperation op = new IndexChangeOperation(db1.Tables[0].Indexes[0], newIndex);
                op.RunOperation();
                op.RunSecondStep();

                IIndex index = db1.Tables[0].Indexes[0];

                Assert.That(db1.Tables[0].Indexes, Has.Count(1), "No new indexes should have been added");
                Assert.That(index, Is.SameAs(originalIndex), "Index object should still be the same");
                Assert.That(index.Description, Is.EqualTo("old description"));
                Assert.That(index.Columns, Has.Count(1));
                Assert.That(index.Columns[0].Name, Is.EqualTo("Column2"));
                Assert.That(index.Columns[0], Is.Not.SameAs(newIndex.Columns[0]));
                Assert.That(index.Columns[0], Is.SameAs(db1.Tables[0].Columns[1]), "The new Index should reference existing columns");
                Assert.That(index.Parent, Is.SameAs(db1.Tables[0]), "The new Index's parent should be Table1 in the existing database");
            }
Exemplo n.º 24
0
        private Model.Table GetNewTable(string tableName)
        {
            ArchAngel.Interfaces.ProjectHelper.RaiseObjectBeingProcessedEvent(tableName, "Table");
            //_columns = null; // Reset the columns
            //_indexColumns = null;
            //_indexes = null;
            //_indexReferencedColumns = null;
            //_referencedColumns = null;
            Model.Table table = new Model.Table(tableName, false);

            #region Columns

            DataRow[] columnRows = Columns.Select(string.Format("TABLE_NAME = '{0}'", tableName));

            foreach (DataRow columnRow in columnRows)
            {
                Column column = new Column(
                    (string)columnRow["COLUMN_NAME"],
                    false,
                    table,
                    (int)columnRow["ORDINAL_POSITION"],
                    Slyce.Common.Utility.StringsAreEqual((string)columnRow["IS_NULLABLE"], "YES", false),
                    (string)columnRow["DATA_TYPE"],
                    columnRow.IsNull("CHARACTER_MAXIMUM_LENGTH") ? 0 : (int)columnRow["CHARACTER_MAXIMUM_LENGTH"],
                    (int)columnRow["InPrimaryKey"] == 1,
                    columnRow.IsNull("IsIdentity") ? false : (int)columnRow["IsIdentity"] == 1,
                    columnRow.IsNull("COLUMN_DEFAULT") ? "" : (string)columnRow["COLUMN_DEFAULT"],
                    columnRow.IsNull("IsComputed") ? false : (int)columnRow["IsComputed"] == 1);

                if (IsSupported(column))
                {
                    table.AddColumn(column);
                }
            }

            #endregion

            #region Indexes

            DataRow[] indexRows = Indexes.Select(string.Format("TABLE_NAME = '{0}'", tableName));
            foreach (DataRow indexRow in indexRows)
            {
                string indexType;
                string indexKeyType = indexRow["CONSTRAINT_TYPE"].ToString();
                if (indexKeyType == "PRIMARY KEY")
                {
                    continue;
                }
                else if (indexKeyType == "FOREIGN KEY")
                {
                    continue;
                }
                else if (indexKeyType == "UNIQUE")
                {
                    continue;
                    //indexType = DatabaseConstant.IndexType.Unique;
                }
                else if (indexKeyType == "CHECK")
                {
                    indexType = DatabaseConstant.IndexType.Check;
                }
                else if (indexKeyType == "NONE")    //TODO check is NONE
                {
                    indexType = DatabaseConstant.IndexType.None;
                }
                else
                {
                    //continue;
                    throw new Exception("IndexType " + indexKeyType + " Not Defined");
                }
                DataRow[] indexColumnRows;// = IndexColumns.Select(string.Format("TABLE_NAME = '{0}' AND CONSTRAINT_NAME  = '{1}'", tableName, indexRow["CONSTRAINT_NAME"]));

                if (indexKeyType == "NONE")
                {
                    indexColumnRows = Columns.Select(string.Format("TABLE_NAME = '{0}' AND COLUMN_NAME  = '{1}'", tableName, indexRow["ColumnName"]));
                }
                else
                {
                    indexColumnRows = IndexColumns.Select(string.Format("TABLE_NAME = '{0}' AND CONSTRAINT_NAME  = '{1}'", tableName, indexRow["CONSTRAINT_NAME"]));
                }
                Index index = new Index(indexRow["CONSTRAINT_NAME"].ToString(), false, indexType, table);

                // Fill Columns
                foreach (DataRow indexColumnRow in indexColumnRows)
                {
                    Column indexColumn = new Column(indexColumnRow["COLUMN_NAME"].ToString(), false);
                    index.AddColumn(indexColumn);
                }
                index.ResetDefaults();
                table.AddIndex(index);
            }

            // Indexes -- that should be keys
            foreach (DataRow keyRow in indexRows)
            {
                string keyType;
                string indexKeyType = keyRow["CONSTRAINT_TYPE"].ToString();
                if (indexKeyType == "PRIMARY KEY")
                {
                    keyType = DatabaseConstant.KeyType.Primary;
                }
                else if (indexKeyType == "FOREIGN KEY")
                {
                    keyType = DatabaseConstant.KeyType.Foreign;
                }
                else if (indexKeyType == "UNIQUE")
                {
                    keyType = DatabaseConstant.KeyType.Unique;
                }
                else if (indexKeyType == "CHECK")
                {
                    continue;
                }
                else if (indexKeyType == "NONE")
                {
                    continue;
                    //keyType = DatabaseConstant.KeyType.None;
                }
                else
                {
                    //continue;
                    throw new Exception("KeyType " + indexKeyType + " Not Defined");
                }

                // Create Alias
                string    keyAlias      = keyType + "_";
                DataRow[] keyColumnRows = IndexColumns.Select(string.Format("TABLE_NAME = '{0}' AND CONSTRAINT_NAME = '{1}'", tableName, keyRow["CONSTRAINT_NAME"]));
                Key       key           = new Key(keyRow["CONSTRAINT_NAME"].ToString(), false, keyType, table, false);

                // Fill Columns
                foreach (DataRow keyColumnRow in keyColumnRows)
                {
                    Column keyColumn = new Column(keyColumnRow["COLUMN_NAME"].ToString(), false);
                    keyColumn.DataType = (string)keyColumnRow["DATA_TYPE"];
                    key.AddColumn(keyColumn);
                }
                if (keyType == DatabaseConstant.KeyType.Foreign)
                {
                    DataRow[] keyReferencedColumnRows     = IndexReferencedColumns.Select(string.Format("ForeignKey = '{0}'", keyRow["CONSTRAINT_NAME"]));
                    DataRow   firstKeyReferencedColumnRow = keyReferencedColumnRows[0];
                    // Fill References
                    key.ReferencedTable = new Model.Table(firstKeyReferencedColumnRow["ReferencedTable"].ToString(), false);

                    //if (dmoKey.ReferencedKey != null)
                    //{
                    key.ReferencedKey = new Key(firstKeyReferencedColumnRow["ReferencedKey"].ToString(), false, true);
                    //}

                    // Fill Referenced Columns
                    foreach (DataRow keyReferencedColumnRow in keyReferencedColumnRows)
                    {
                        Column keyReferencedColumn = new Column(keyReferencedColumnRow["ReferencedColumn"].ToString(), false);
                        key.AddReferencedColumn(keyReferencedColumn);
                    }
                }
                key.ResetDefaults();
                table.AddKey(key);
            }

            #endregion

            return(table);
        }
Exemplo n.º 25
0
        private void GetUniqueIndexes(Table table)
        {
            DataRow[] indexRows = connector.GetUniqueIndexes().Select(string.Format("TABLE_NAME = '{0}' AND TABLE_SCHEMA = '{1}'", table.Name.Replace("'", "''"), table.Schema));

            for (int i = 0; i < indexRows.Length; i++)
            {
                DataRow indexRow = indexRows[i];

                Index index = new Index
                {
                    Name = (string)indexRow["INDEX_NAME"],
                    IsUserDefined = false,
                    IsUnique = true,
                    IsClustered = (long)indexRow["IS_CLUSTERED"] == 1,
                    IndexType = DatabaseIndexType.Unique,
                    Parent = table
                };

                List<DataRow> indexColumnRows = new List<DataRow>();

                indexColumnRows.AddRange(connector.Columns.Select(string.Format("TABLE_NAME = '{0}' AND TABLE_SCHEMA = '{1}' AND COLUMN_NAME  = '{2}'", table.Name.Replace("'", "''"), table.Schema, indexRow["COLUMN_NAME"])));

                while ((i < indexRows.Length - 1) && (string)indexRows[i + 1]["TABLE_NAME"] == table.Name && (string)indexRows[i + 1]["INDEX_NAME"] == (string)indexRow["INDEX_NAME"])
                {
                    i++;
                    indexRow = indexRows[i];
                    indexColumnRows.AddRange(connector.Columns.Select(string.Format("TABLE_NAME = '{0}' AND TABLE_SCHEMA = '{1}' AND COLUMN_NAME  = '{2}'", table.Name.Replace("'", "''"), table.Schema, indexRow["COLUMN_NAME"])));
                }

                // Fill Columns
                foreach (DataRow indexColumnRow in indexColumnRows)
                {
                    var column = index.AddColumn((string)indexColumnRow["COLUMN_NAME"]);

                    if (index.IsUnique && indexColumnRows.Count == 1)
                    {
                        // Columns are only unique if the index has one column
                        column.IsUnique = true;
                    }
                }

                index.ResetDefaults();
                table.AddIndex(index);
            }
        }
Exemplo n.º 26
0
        internal void GetTableConstraintIndexes(Table table)
        {
            DataRow[] indexRows = connector.Indexes.Select(string.Format("TABLE_NAME = '{0}' AND TABLE_SCHEMA = '{1}'", table.Name.Replace("'", "''"), table.Schema));

            for (int i = 0; i < indexRows.Length; i++)
            {
                DataRow indexRow = indexRows[i];
                DatabaseIndexType indexType = GetIndexType(indexRow["CONSTRAINT_TYPE"].ToString());

                var indexColumnRows = new List<DataRow>();

                indexColumnRows.AddRange(connector.Columns.Select(string.Format("TABLE_NAME = '{0}' AND TABLE_SCHEMA = '{1}' AND COLUMN_NAME  = '{2}'", table.Name.Replace("'", "''"), table.Schema, indexRow["COLUMN_NAME"])));

                while ((i < indexRows.Length - 1) && (string)indexRows[i + 1]["TABLE_NAME"] == table.Name && (string)indexRows[i + 1]["INDEX_NAME"] == (string)indexRow["INDEX_NAME"])
                {
                    i++;
                    indexRow = indexRows[i];
                    indexColumnRows.AddRange(connector.Columns.Select(string.Format("TABLE_NAME = '{0}' AND TABLE_SCHEMA = '{1}' AND COLUMN_NAME  = '{2}'", table.Name.Replace("'", "''"), table.Schema, indexRow["COLUMN_NAME"])));
                }
                bool isUnique = (int)indexRow["IS_UNIQUE"] == 1;
                bool isClustered = Convert.ToInt32(indexRow["IS_CLUSTERED"]) == 1;
                Index index = new Index
                {
                    Name = indexRow["INDEX_NAME"].ToString(),
                    IsUserDefined = false,
                    IndexType = indexType,
                    Parent = table,
                    IsUnique = isUnique,
                    IsClustered = isClustered
                };

                // Fill Columns
                foreach (DataRow indexColumnRow in indexColumnRows)
                {
                    var column = index.AddColumn((string)indexColumnRow["COLUMN_NAME"]);

                    if (index.IsUnique && indexColumnRows.Count == 1)
                    {
                        // Columns are only unique if the index has one column
                        column.IsUnique = true;
                    }
                }
                index.ResetDefaults();
                table.AddIndex(index);
            }
        }
Exemplo n.º 27
0
        private void GetUniqueIndexes(Table table)
        {
            List<DataRow> indexRows = connector.GetIndexRows(table.Schema, table.Name.Replace("'", "''"));

            for (int i = 0; i < indexRows.Count; i++)
            {
                DataRow indexRow = indexRows[i];

                Index index = new Index
                {
                    Name = (string)indexRow["INDEX_NAME"],
                    IsUserDefined = false,
                    IsUnique = true,
                    IsClustered = Convert.ToInt32(indexRow["IS_CLUSTERED"]) == 1,
                    IndexType = DatabaseIndexType.Unique,
                    Parent = table
                };

                List<DataRow> indexColumnRows = new List<DataRow>();

                indexColumnRows.AddRange(connector.ColumnsBySchema[table.Schema][table.Name.Replace("'", "''")].Where(c => c[OrdColumn].ToString() == indexRow["COLUMN_NAME"].ToString()));

                while ((i < indexRows.Count - 1) && (string)indexRows[i + 1]["TABLE_NAME"] == table.Name && (string)indexRows[i + 1]["INDEX_NAME"] == (string)indexRow["INDEX_NAME"])
                {
                    i++;
                    indexRow = indexRows[i];
                    indexColumnRows.AddRange(connector.ColumnsBySchema[table.Schema][table.Name.Replace("'", "''")].Where(c => c[OrdColumn].ToString() == indexRow["COLUMN_NAME"]));
                }

                // Fill Columns
                foreach (DataRow indexColumnRow in indexColumnRows)
                {
                    var column = index.AddColumn((string)indexColumnRow["COLUMN_NAME"]);

                    if (index.IsUnique && indexColumnRows.Count == 1)
                    {
                        // Columns are only unique if the index has one column
                        column.IsUnique = true;
                    }
                }

                index.ResetDefaults();
                table.AddIndex(index);
            }
        }
Exemplo n.º 28
0
        public override sealed void Refresh()
        {
            using (IDbConnection cnx = GetConnection())
            {
                cnx.Open();

                Tables.Clear();

                using (IDbCommand cmd = cnx.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;

                    //Case Sensibility
                    cmd.CommandText = CaseSensitivityTestQuery;
                    using (IDataReader reader = cmd.ExecuteReader())
                    {
                        reader.Read();
                        IsCaseSensitive = new CaseSensitivity(reader.Read());
                    }

                    //Tables
                    cmd.CommandText = TableQuery;
                    using (IDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Table table = CreateTable(reader);
                            Tables.Add(table.ToString(), table);
                        }
                    }

                    //Columns & Primary Keys
                    foreach (Table table in Tables.Values.Cast <Table>())
                    {
                        cmd.CommandText = string.Format(ColumnQuery, table.Name);
                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Column column = CreateColumn(reader, table);
                                table.AddColumn(column);

                                int pkpos = (int)reader.GetInt64OrDefault(5);
                                if (pkpos > 0)
                                {
                                    if (table.PrimaryKey == null)
                                    {
                                        table.SetPrimaryKey(CreatePrimaryKey(table));
                                    }

                                    PrimaryKey primaryKey = table.PrimaryKey as PrimaryKey;
                                    // ReSharper disable PossibleNullReferenceException
                                    primaryKey.AddColumn(pkpos, column);
                                    // ReSharper restore PossibleNullReferenceException
                                }
                            }
                        }
                    }

                    //Indexes
                    foreach (Table table in Tables.Values.Cast <Table>())
                    {
                        cmd.CommandText = string.Format(IndexListQuery, table.Name);
                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Index index = CreateIndex(reader, table);
                                table.AddIndex(index);
                            }
                        }

                        foreach (Index index in table.Indexes().Cast <Index>())
                        {
                            cmd.CommandText = string.Format(IndexInfoQuery, index.Name);
                            using (IDataReader reader = cmd.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    IColumn column = table.GetColumn(reader.GetStringOrDefault(2));
                                    index.AddColumn(new ColumnForIndex {
                                        Column = column, Position = (int)reader.GetInt64OrDefault(0)
                                    });
                                }
                            }
                        }
                    }
                    //ALERT: Foreign Key List TO BE CODED
                }
            }
        }
Exemplo n.º 29
0
        private Model.Table GetNewTable(string schema, string tableName)
        {
            Interfaces.Events.RaiseObjectBeingProcessedEvent(tableName, "Table");
            Model.Table table = new Model.Table(tableName, false);

            #region Columns
            DataRow[] columnRows = Columns.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'", schema, tableName));

            foreach (DataRow columnRow in columnRows)
            {
                bool isReadOnly = false;

                if (!columnRow.IsNull("IsIdentity") && (int)columnRow["IsIdentity"] == 1)
                {
                    isReadOnly = true;
                }
                else if (!columnRow.IsNull("IsComputed") && (int)columnRow["IsComputed"] == 1)
                {
                    isReadOnly = true;
                }
                else if (Slyce.Common.Utility.StringsAreEqual((string)columnRow["DATA_TYPE"], "timestamp", false))
                {
                    isReadOnly = true;
                }
                Column column = new Column(
                    (string)columnRow["COLUMN_NAME"],
                    false,
                    table,
                    (int)columnRow["ORDINAL_POSITION"],
                    Slyce.Common.Utility.StringsAreEqual((string)columnRow["IS_NULLABLE"], "YES", false),
                    (string)columnRow["DATA_TYPE"],
                    columnRow.IsNull("CHARACTER_MAXIMUM_LENGTH") ? 0 : Convert.ToInt32(columnRow["CHARACTER_MAXIMUM_LENGTH"]),
                    (int)columnRow["InPrimaryKey"] == 1,
                    columnRow.IsNull("IsIdentity") ? false : Convert.ToInt32(columnRow["IsIdentity"]) == 1,
                    columnRow.IsNull("COLUMN_DEFAULT") ? "" : (string)columnRow["COLUMN_DEFAULT"],
                    isReadOnly,
                    columnRow.IsNull("IsComputed") ? false : Convert.ToInt32(columnRow["IsComputed"]) == 1,
                    columnRow.IsNull("NUMERIC_PRECISION") ? 0 : Convert.ToInt32(columnRow["NUMERIC_PRECISION"]),
                    columnRow.IsNull("NUMERIC_SCALE") ? 0 : Convert.ToInt32(columnRow["NUMERIC_SCALE"]));

                if (IsSupported(column))
                {
                    table.AddColumn(column);
                }
            }

            #endregion

            #region Indexes
            DataRow[] indexRows = Indexes.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'", schema, tableName));
            //foreach (DataRow indexRow in indexRows)
            for (int i = 0; i < indexRows.Length; i++)
            {
                DataRow indexRow = indexRows[i];
                string  indexType;
                string  indexKeyType = indexRow["CONSTRAINT_TYPE"].ToString();

                if (indexKeyType == "PRIMARY KEY")
                {
                    indexType = DatabaseConstant.IndexType.PrimaryKey;
                    //continue;
                }
                else if (indexKeyType == "FOREIGN KEY")
                {
                    indexType = DatabaseConstant.IndexType.ForeignKey;
                    //continue;
                }
                else if (indexKeyType == "UNIQUE")
                {
                    //continue;
                    indexType = DatabaseConstant.IndexType.Unique;
                }
                else if (indexKeyType == "CHECK")
                {
                    indexType = DatabaseConstant.IndexType.Check;
                }
                else if (indexKeyType == "NONE")    //TODO check is NONE
                {
                    indexType = DatabaseConstant.IndexType.None;
                }
                else
                {
                    //continue;
                    throw new Exception("IndexType " + indexKeyType + " Not Defined");
                }
                List <DataRow> indexColumnRows = new List <DataRow>();// = IndexColumns.Select(string.Format("TABLE_NAME = '{0}' AND CONSTRAINT_NAME  = '{1}'", tableName, indexRow["CONSTRAINT_NAME"]));

                indexColumnRows.AddRange(Columns.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME  = '{2}'", schema, tableName, indexRow["COLUMN_NAME"])));

                while ((i < indexRows.Length - 1) && (string)indexRows[i + 1]["TABLE_NAME"] == tableName && (string)indexRows[i + 1]["CONSTRAINT_NAME"] == (string)indexRow["CONSTRAINT_NAME"])
                {
                    i++;
                    indexRow = indexRows[i];
                    indexColumnRows.AddRange(Columns.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME  = '{2}'", schema, tableName, indexRow["COLUMN_NAME"])));
                }
                bool  isUnique    = (int)indexRow["IS_UNIQUE"] == 1 ? true : false;
                bool  isClustered = (int)indexRow["IS_CLUSTERED"] == 1 ? true : false;
                Index index       = new Index(indexRow["CONSTRAINT_NAME"].ToString(), false, indexType, table, isUnique, isClustered);

                // Fill Columns
                foreach (DataRow indexColumnRow in indexColumnRows)
                {
                    Column indexColumn = new Column(indexColumnRow["COLUMN_NAME"].ToString(), false);
                    index.AddColumn(indexColumn);
                }
                index.ResetDefaults();
                table.AddIndex(index);
            }

            // Indexes -- that should be keys
            DataRow[] keyRows = Indexes.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'", schema, tableName));

            //foreach (DataRow keyRow in indexRows)
            for (int i = 0; i < keyRows.Length; i++)
            {
                DataRow keyRow = keyRows[i];
                string  keyType;
                string  indexKeyType = keyRow["CONSTRAINT_TYPE"].ToString();

                if (indexKeyType == "PRIMARY KEY")
                {
                    keyType = DatabaseConstant.KeyType.Primary;
                }
                else if (indexKeyType == "FOREIGN KEY")
                {
                    keyType = DatabaseConstant.KeyType.Foreign;
                }
                else if (indexKeyType == "UNIQUE")
                {
                    keyType = DatabaseConstant.KeyType.Unique;
                }
                else
                {
                    continue;
                }
                Key key = new Key(keyRow["CONSTRAINT_NAME"].ToString(), false, keyType, table, false);

                // Fill Columns
                List <DataRow> keyColumnRows = new List <DataRow>();
                keyColumnRows.AddRange(Columns.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'", schema, tableName, keyRow["COLUMN_NAME"])));

                while ((i < keyRows.Length - 1) && (string)keyRows[i + 1]["TABLE_NAME"] == tableName && (string)keyRows[i + 1]["CONSTRAINT_NAME"] == (string)keyRow["CONSTRAINT_NAME"])
                {
                    i++;
                    keyRow = keyRows[i];
                    keyColumnRows.AddRange(Columns.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'", schema, tableName, keyRow["COLUMN_NAME"])));
                }
                // Fill Columns
                foreach (DataRow keyColumnRow in keyColumnRows)
                {
                    Column keyColumn = new Column(keyColumnRow["COLUMN_NAME"].ToString(), false);
                    keyColumn.DataType = (string)keyColumnRow["DATA_TYPE"];
                    key.AddColumn(keyColumn);
                }
                if (keyType == DatabaseConstant.KeyType.Foreign)
                {
                    DataRow[] keyReferencedColumnRows     = IndexReferencedColumns.Select(string.Format("ForeignKeySchema = '{0}' AND ForeignKeyTable = '{1}' AND ForeignKey = '{2}'", schema, tableName, keyRow["CONSTRAINT_NAME"]));
                    DataRow   firstKeyReferencedColumnRow = keyReferencedColumnRows[0];

                    // Fill References
                    key.ReferencedTable = new Model.Table(firstKeyReferencedColumnRow["ReferencedTable"].ToString(), false)
                    {
                        Schema = firstKeyReferencedColumnRow["ReferencedSchema"].ToString()
                    };
                    key.ReferencedKey = new Key(firstKeyReferencedColumnRow["ReferencedKey"].ToString(), false, true);

                    // Fill Referenced Columns
                    foreach (DataRow keyReferencedColumnRow in keyReferencedColumnRows)
                    {
                        Column keyReferencedColumn = new Column(keyReferencedColumnRow["ReferencedColumn"].ToString(), false);
                        key.AddReferencedColumn(keyReferencedColumn);
                    }
                }
                key.ResetDefaults();
                table.AddKey(key);
            }

            #endregion

            return(table);
        }
        public IIndex ProcessIndexNode(XmlNode node, ITable parent, IDatabase db)
        {
            IIndex index = new Index();
            index.Parent = parent;
            ProcessScriptBase(index, node);
            /*
                <IsUnique>True</IsUnique>
                <IsClustered>False</IsClustered>
                <Datatype>Unique</Datatype>
                <Columns>
                  <ColumnName>ColumnT13</ColumnName>
                </Columns>
             */
            NodeProcessor proc = new NodeProcessor(node);

            index.IsUnique = proc.GetBool("IsUnique");
            index.IsClustered = proc.GetBool("IsClustered");
            index.IndexType = proc.GetEnum<DatabaseIndexType>("Datatype");

            var columnNodeList = node.SelectNodes("Columns/ColumnName");

            if (columnNodeList != null)
                foreach (XmlNode columnRef in columnNodeList)
                    index.AddColumn(columnRef.InnerText);

            return index;
        }
        public void Should_Return_This()
        {
            const string expectedXML =	IndexWithColumnsXml;

            Table table1 = new Table("Table1");
            table1.AddColumn(new Column("Column1"));
            Index index = new Index("Index1");
            index.Parent = table1;
            index.AddColumn("Column1");

            string outputXML = index.Serialise(new DatabaseSerialisationScheme());
            outputXML = XmlSqueezer.RemoveWhitespaceBetweenElements(outputXML);
            Assert.That(outputXML, Is.EqualTo(expectedXML));
        }
Exemplo n.º 32
0
        private Model.Table GetNewTable(string tableName)
        {
            ArchAngel.Interfaces.Events.RaiseObjectBeingProcessedEvent(tableName, "Table");
            //_columns = null; // Reset the columns
            //_indexes = null;
            //_dtReferencedColumns = null;
            //return new Model.Table();
            Model.Table table = new Model.Table(tableName, false);

            #region Columns
            DataRow[] columnRows = Columns.Select(string.Format("TABLE_NAME = '{0}'", tableName));

            foreach (DataRow row in columnRows)
            {
                bool isReadOnly = false;

                if (!row.IsNull("IsIdentity") && (int)row["IsIdentity"] == 1)
                {
                    isReadOnly = true;
                }
                else if (!row.IsNull("IsComputed") && (int)row["IsComputed"] == 1)
                {
                    isReadOnly = true;
                }
                else if (Slyce.Common.Utility.StringsAreEqual((string)row["DATA_TYPE"], "timestamp", false))
                {
                    isReadOnly = true;
                }
                // Check whether we have added this column before. Columns are repeated if they are both a PRIMARY_KEY and a FOREIGN_KEY
                Column column = new Column(
                    (string)row["COLUMN_NAME"],
                    false,
                    table,
                    (int)row["ORDINAL_POSITION"],
                    Slyce.Common.Utility.StringsAreEqual((string)row["IS_NULLABLE"], "YES", false),
                    (string)row["DATA_TYPE"],
                    row.IsNull("CHARACTER_MAXIMUM_LENGTH") ? 0 : Convert.ToInt32(row["CHARACTER_MAXIMUM_LENGTH"]),
                    (int)row["InPrimaryKey"] == 1,
                    row.IsNull("IsIdentity") ? false : Convert.ToInt32(row["IsIdentity"]) == 1,
                    row.IsNull("COLUMN_DEFAULT") ? "" : (string)row["COLUMN_DEFAULT"],
                    isReadOnly,
                    row.IsNull("IsComputed") ? false : Convert.ToInt32(row["IsComputed"]) == 1,
                    row.IsNull("NUMERIC_PRECISION") ? 0 : Convert.ToInt32(row["NUMERIC_PRECISION"]),
                    row.IsNull("NUMERIC_SCALE") ? 0 : Convert.ToInt32(row["NUMERIC_SCALE"]));

                table.AddColumn(column);
                //ordinalPosition++;
            }
            #endregion

            #region Indexes

            DataRow[] indexRows = Indexes.Select(string.Format("TABLE_NAME = '{0}'", tableName));
            foreach (DataRow indexRow in indexRows)
            {
                string indexType;
                string indexKeyType = indexRow["CONSTRAINT_TYPE"].ToString();
                if (indexKeyType == "PRIMARY KEY")
                {
                    continue;
                }
                else if (indexKeyType == "FOREIGN KEY")
                {
                    continue;
                }
                else if (indexKeyType == "UNIQUE")
                {
                    continue;
                    //indexType = DatabaseConstant.IndexType.Unique;
                }
                else if (indexKeyType == "CHECK")
                {
                    indexType = DatabaseConstant.IndexType.Check;
                }
                else if (indexKeyType == "NONE")    //TODO check is NONE
                {
                    indexType = DatabaseConstant.IndexType.None;
                }
                else
                {
                    //continue;
                    throw new Exception("IndexType " + indexKeyType + " Not Defined");
                }
                DataRow[] indexColumnRows;// = IndexColumns.Select(string.Format("TABLE_NAME = '{0}' AND CONSTRAINT_NAME  = '{1}'", tableName, indexRow["CONSTRAINT_NAME"]));

                if (indexKeyType == "NONE")
                {
                    indexColumnRows = Columns.Select(string.Format("TABLE_NAME = '{0}' AND COLUMN_NAME  = '{1}'", tableName, indexRow["ColumnName"]));
                }
                else
                {
                    indexColumnRows = IndexColumns.Select(string.Format("TABLE_NAME = '{0}' AND CONSTRAINT_NAME  = '{1}'", tableName, indexRow["CONSTRAINT_NAME"]));
                }
                Index index = new Index(indexRow["CONSTRAINT_NAME"].ToString(), false, indexType, table, (bool)indexRow["IS_UNIQUE"], (bool)indexRow["IS_CLUSTERED"]);

                // Fill Columns
                foreach (DataRow indexColumnRow in indexColumnRows)
                {
                    Column indexColumn = new Column(indexColumnRow["COLUMN_NAME"].ToString(), false);
                    index.AddColumn(indexColumn);
                }
                index.ResetDefaults();
                table.AddIndex(index);
            }

            // Indexes -- that should be keys
            foreach (DataRow keyRow in indexRows)
            {
                string keyType;
                string indexKeyType = keyRow["CONSTRAINT_TYPE"].ToString();
                if (indexKeyType == "PRIMARY KEY")
                {
                    keyType = DatabaseConstant.KeyType.Primary;
                }
                else if (indexKeyType == "FOREIGN KEY")
                {
                    keyType = DatabaseConstant.KeyType.Foreign;
                }
                else if (indexKeyType == "UNIQUE")
                {
                    keyType = DatabaseConstant.KeyType.Unique;
                }
                else if (indexKeyType == "CHECK")
                {
                    continue;
                }
                else if (indexKeyType == "NONE")
                {
                    continue;
                    //keyType = DatabaseConstant.KeyType.None;
                }
                else
                {
                    //continue;
                    throw new Exception("KeyType " + indexKeyType + " Not Defined");
                }
                Key       key           = new Key(keyRow["CONSTRAINT_NAME"].ToString(), false, keyType, table, false);
                DataRow[] keyColumnRows = IndexColumns.Select(string.Format("TABLE_NAME = '{0}' AND CONSTRAINT_NAME = '{1}'", tableName, keyRow["CONSTRAINT_NAME"]));

                // Fill Columns
                foreach (DataRow keyColumnRow in keyColumnRows)
                {
                    Column keyColumn = new Column(keyColumnRow["COLUMN_NAME"].ToString(), false);
                    keyColumn.DataType = (string)keyColumnRow["DATA_TYPE"];
                    key.AddColumn(keyColumn);
                }

                if (keyType == DatabaseConstant.KeyType.Foreign)
                {
                    DataRow[] keyReferencedColumnRows     = IndexReferencedColumns.Select(string.Format("ForeignKey = '{0}'", keyRow["CONSTRAINT_NAME"]));
                    DataRow   firstKeyReferencedColumnRow = keyReferencedColumnRows[0];
                    // Fill References
                    key.ReferencedTable = new Model.Table(firstKeyReferencedColumnRow["ReferencedTable"].ToString(), false);

                    //if (dmoKey.ReferencedKey != null)
                    //{
                    key.ReferencedKey = new Key(firstKeyReferencedColumnRow["ReferencedKey"].ToString(), false, true);
                    //}

                    // Fill Referenced Columns
                    foreach (DataRow keyReferencedColumnRow in keyReferencedColumnRows)
                    {
                        Column keyReferencedColumn = new Column(keyReferencedColumnRow["ReferencedColumn"].ToString(), false);
                        key.AddReferencedColumn(keyReferencedColumn);
                    }
                }
                key.ResetDefaults();
                table.AddKey(key);
            }

            #endregion

            //#region Indexes -- that should be keys
            //string prevConstraintName = "";
            //Key key = null;
            //DataRow[] indexRows = DtIndexes.Select(string.Format("TABLE_NAME = '{0}'", tableName));

            //for (int rowCounter = 0; rowCounter < indexRows.Length; rowCounter++)
            //{
            //    DataRow row = indexRows[rowCounter];
            //    for (int colCounter = 0; colCounter < DtIndexes.Columns.Count; colCounter++)
            //    {
            //        string colName = DtIndexes.Columns[colCounter].ColumnName;
            //    }
            //    string keyType;
            //    string indexKeyType = row.IsNull("Constraint_Type") ? "NONE" : (string)row["Constraint_Type"];
            //    string constraintName = row.IsNull("Constraint_Name") ? "" : (string)row["Constraint_Name"];
            //    string columnName = (string)row["COLUMN_NAME"];

            //    if (indexKeyType == "NONE")
            //    {
            //        keyType = DatabaseConstant.KeyType.None;
            //    }
            //    else if (indexKeyType == "PRIMARY KEY")
            //    {
            //        keyType = DatabaseConstant.KeyType.Primary;
            //    }
            //    else if (indexKeyType == "UNIQUE")
            //    {
            //        keyType = DatabaseConstant.KeyType.Unique;
            //    }
            //    else if (indexKeyType == "CHECK") // TODO: was 'None' for SMO
            //    {
            //        keyType = DatabaseConstant.KeyType.None;
            //    }
            //    else if (indexKeyType == "FOREIGN KEY") // TODO: was 'None' for SMO
            //    {
            //        keyType = DatabaseConstant.KeyType.Foreign;
            //    }
            //    else
            //    {
            //        throw new Exception("KeyType " + indexKeyType + " Not Defined");
            //    }
            //    // Create Alias
            //    if (string.Format("{0}{1}", constraintName, keyType) != prevConstraintName)
            //    {
            //        if (key != null)
            //        {
            //            // Reset the alias, because it is based on the Columns collection which has just finished being modified.
            //            key.ResetDefaults();
            //        }
            //        // Create a new Key
            //        key = new Key(constraintName, false, keyType, table, false);
            //        table.AddKey(key);
            //        prevConstraintName = string.Format("{0}{1}", constraintName, keyType);
            //        Column keyColumn = new Column(columnName, false);
            //        key.AddColumn(keyColumn);
            //    }
            //    else
            //    {
            //        // We are processing another column of the same Index as the previous index
            //        Column keyColumn = new Column(columnName, false);
            //        key.AddColumn(keyColumn);
            //    }
            //    if (keyType == DatabaseConstant.KeyType.Foreign)
            //    {
            //        DataRow[] referencedColumnRows = DtReferencedColumns.Select(string.Format("FOREIGN_KEY = '{0}'", key.Name));

            //        foreach (DataRow refColRow in referencedColumnRows)
            //        {
            //            // Fill References
            //            if (key.ReferencedTable == null)
            //            {
            //                string referencedTableName = (string)refColRow["Referenced_Table_Name"];
            //                string referencedKeyName = (string)refColRow["Referenced_Key"];

            //                key.ReferencedTable = new Model.Table(referencedTableName, false);
            //                key.ReferencedKey = new Key(referencedKeyName, false, true);
            //            }
            //            string referencedColumnName = (string)refColRow["Referenced_Column_Name"];

            //            // Fill Referenced Columns
            //            Column referencedKeyColumn = new Column(referencedColumnName, false);
            //            key.AddReferencedColumn(referencedKeyColumn);
            //        }
            //    }
            //    key.ResetDefaults();
            //}
            //#endregion

            return(table);
        }