/// <summary> /// Recupera as chaves estrangeiras da tabela. /// http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm/oledbtable_constraints_rowset.asp /// Restriction columns: PK_TABLE_CATALOG, PK_TABLE_SCHEMA, PK_TABLE_NAME, /// FK_TABLE_CATALOG, FK_TABLE_SCHEMA, FK_TABLE_NAME /// Schema columns: FK_COLUMN_NAME, FK_COLUMN_GUID, FK_COLUMN_PROPID, UPDATE_RULE, /// DELETE_RULE, PK_NAME, FK_NAME, DEFERRABILITY /// </summary> private DataTable GetForeignKeys(string tableName) { OleDbConnection conn = ProviderConfiguration.CreateConnection() as OleDbConnection; GDAConnectionManager.NotifyConnectionCreated(conn); if (conn.State != ConnectionState.Open) { conn.Open(); GDAConnectionManager.NotifyConnectionOpened(conn); } try { return(conn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new object[] { null, null, null, null, null, tableName })); } finally { conn.Close(); } }
/// <summary> /// Efetua a analise do banco de dados. /// </summary> /// <param name="tableName"></param> public override void Analyze(string tableName) { bool isSingleRun = tableName != null; IDbConnection conn = ProviderConfiguration.CreateConnection(); IDbCommand cmd = conn.CreateCommand(); cmd.Connection = conn; cmd.CommandText = "show tables"; IDbDataAdapter da = ProviderConfiguration.Provider.CreateDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet(); if (conn.State != ConnectionState.Open) { conn.Open(); } try { try { da.Fill(ds); } finally { conn.Close(); } DataTable dt = ds.Tables[0]; for (int i = 0; i < dt.Rows.Count; i++) { string dbTableName = dt.Rows[i][0].ToString(); if (!isSingleRun || tableName.ToLower().Equals(dbTableName.ToLower())) { TableMap map = GetTableMap(dbTableName); if (map == null) { map = new TableMap(ProviderConfiguration, dbTableName); tablesMaps[dbTableName.ToLower()] = map; } GetColumnData(map); GetConstraintData(map); if (isSingleRun) { break; } } } } catch (Exception ex) { throw new GDAException("An error occurred while analyzing the database schema.", ex); } }
/// <summary> /// Recupera os dados das colunas da tabela. /// </summary> /// <param name="map">Tabela</param> private void GetColumnData(TableMap map) { IDbConnection conn = ProviderConfiguration.CreateConnection(); IDbCommand cmd = conn.CreateCommand(); cmd.Connection = conn; string sql = String.Format(@"SELECT Column_Name AS Field, Column_Type AS Type, Is_Nullable, Column_Key, Column_Default, Extra, Column_Comment FROM Information_Schema.Columns WHERE Table_Schema='{0}' AND Table_Name='{1}';", conn.Database, map.TableName); cmd.CommandText = sql; if (conn.State != ConnectionState.Open) { conn.Open(); } try { IDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { string columnName = dr["Field"].ToString(); FieldMap fm = map.GetFieldMapFromColumn(columnName); if (fm == null) { fm = new FieldMap(map, columnName); map.Fields.Add(fm); } string typeinfo = dr["Type"].ToString(); bool isUnsigned; fm.SetDbType(ExtractType(typeinfo, out isUnsigned), isUnsigned); fm.Size = ExtractSize(typeinfo); fm.IsNullable = (dr["Is_Nullable"].ToString() == "YES"); fm.IsPrimaryKey = (dr["Column_Key"].ToString() == "PRI"); if (fm.IsPrimaryKey) { fm.IsAutoGenerated = (dr["Extra"].ToString() == "auto_increment"); } fm.Comment = dr.GetString(dr.GetOrdinal("Column_Comment")); } } finally { conn.Close(); } }
/// <summary> /// Recupera as informações sobre a chave identidade da tabela. /// </summary> /// <param name="map"></param> protected void GetIdentityInformation(TableMap map) { if (map != null) { IDbConnection conn = ProviderConfiguration.CreateConnection(); GDAConnectionManager.NotifyConnectionCreated(conn); IDbCommand cmd = conn.CreateCommand(); cmd.Connection = conn; if (conn.State != ConnectionState.Open) { conn.Open(); GDAConnectionManager.NotifyConnectionOpened(conn); } try { foreach (FieldMap fm in map.Fields) { if (fm.IsPrimaryKey) { if (!map.IsView) { cmd.CommandText = String.Format("select COLUMNPROPERTY(OBJECT_ID('{0}'), '{1}', 'IsIdentity') as IsIdentity ", map.TableName, fm.ColumnName); using (IDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { fm.IsAutoGenerated = (reader["IsIdentity"].ToString() == "1"); } } } } } } catch (Exception ex) { GDAOperations.CallDebugTrace(this, String.Format("Unable to determine whether PK column of table {0} is an identity column.", map.TableName)); GDAOperations.CallDebugTrace(this, ex.Message); } finally { conn.Close(); conn.Dispose(); } } }
/// <summary> /// Recupera as informações sobre as chaves primárias da tabela. /// </summary> private DataTable GetPrimaryKeyInfo(string tableName) { OleDbConnection conn = ProviderConfiguration.CreateConnection() as OleDbConnection; GDAConnectionManager.NotifyConnectionCreated(conn); if (conn.State != ConnectionState.Open) { conn.Open(); GDAConnectionManager.NotifyConnectionOpened(conn); } try { OleDbCommand cmd = new OleDbCommand("SELECT * FROM " + tableName, conn); OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.KeyInfo); return(dr.GetSchemaTable()); } finally { conn.Close(); } }
/// <summary> /// Please refer to the <see cref="DatabaseAnalyzer"/> class and the <see cref="DatabaseAnalyzer"/> /// interface it implements a description of this method. /// </summary> public override void Analyze(string tableName, string tableSchema) { try { bool isSingleRun = tableName != null; IDbConnection conn = ProviderConfiguration.CreateConnection(); GDAConnectionManager.NotifyConnectionCreated(conn); IDataReader reader = null; if (conn.State != ConnectionState.Open) { conn.Open(); GDAConnectionManager.NotifyConnectionOpened(conn); } try { IDbCommand fkCmd = conn.CreateCommand(); if (isSingleRun) { if (!string.IsNullOrEmpty(tableSchema)) { fkCmd.CommandText = foreignKeysTables + string.Format("\r\nWHERE FK.TABLE_NAME='{0}' AND FK.TABLE_SCHEMA = '{1}'", tableName, tableSchema); } else { fkCmd.CommandText = foreignKeysTables + string.Format("\r\nWHERE FK.TABLE_NAME='{0}'", tableName); } } else { fkCmd.CommandText = foreignKeysTables; } using (reader = fkCmd.ExecuteReader()) { while (reader.Read()) { var fk = ForeignKeys[reader["Constraint_Name"].ToString()]; if (fk == null) { ForeignKeys.Add(new ForeignKeyMap() { ConstraintName = reader["Constraint_Name"].ToString(), ConstraintSchema = reader["Constraint_Schema"].ToString(), ForeignKeyTable = reader["FK_Table"].ToString(), ForeignKeyTableSchema = reader["FK_Schema"].ToString(), ForeignKeyColumn = reader["FK_Column"].ToString(), PrimaryKeyTable = reader["PK_Table"].ToString(), PrimaryKeyTableSchema = reader["PK_Schema"].ToString(), PrimaryKeyColumn = reader["PK_Column"].ToString() }); } } } IDbCommand cmd = conn.CreateCommand(); cmd.Connection = conn; if (isSingleRun) { if (!string.IsNullOrEmpty(tableSchema)) { cmd.CommandText = select + String.Format(" and t.TABLE_NAME = '{0}' and t.TABLE_SCHEMA = '{1}'", tableName, tableSchema); } else { cmd.CommandText = select + String.Format(" and t.TABLE_NAME = '{0}'", tableName); } } else { cmd.CommandText = select; } using (reader = cmd.ExecuteReader()) { while (reader.Read()) { string dbTableName = reader["tablename"].ToString(); string dbTableSchema = (reader["tableschema"] ?? "").ToString(); if (!isSingleRun || (string.Compare(tableName, dbTableName, true) == 0 && (string.IsNullOrEmpty(tableSchema) || string.Compare(tableSchema, dbTableSchema) == 0))) { TableMap map = GetTableMap(dbTableName, dbTableSchema); if (map == null) { map = new TableMap(ProviderConfiguration, dbTableName, dbTableSchema); tablesMaps[dbTableName.ToLower()] = map; } map.IsView = reader["TableType"].ToString() == "VIEW"; string columnName = reader["ColumnName"].ToString(); FieldMap fm = map.GetFieldMapFromColumn(columnName); if (fm == null) { fm = new FieldMap(map, columnName); map.Fields.Add(fm); } fm.SetDbType(reader["Type"].ToString(), false); fm.DbTypeName = reader["Type"].ToString(); fm.IsNullable = GetBoolean(reader["IsNullable"].ToString()); if ((reader["Size"] != null && reader["Size"] != DBNull.Value) && fm.DbType != (long)SqlDbType.Text) { fm.Size = int.Parse(reader["Size"].ToString()); } if (reader["ConstraintName"] != null && reader["ConstraintName"] != DBNull.Value) { string type = reader["ConstraintType"].ToString(); if (type.ToLower().Equals("primary key")) { fm.IsPrimaryKey = true; } else if (type.ToLower().Equals("foreign key")) { string conref = reader["ConstraintReference"].ToString(); fm.ForeignKeyConstraintName = reader["ConstraintName"].ToString(); if (conref.StartsWith("IDX")) { string fkRef = reader["ConstraintName"].ToString(); if (fkRef != null && fkRef.StartsWith("FK")) { conref = fkRef; } } IDbConnection conn2 = ProviderConfiguration.CreateConnection(); GDAConnectionManager.NotifyConnectionCreated(conn2); IDbCommand cmd2 = conn2.CreateCommand(); cmd2.CommandText = String.Format("select c.TABLE_NAME as TableName, c.TABLE_SCHEMA as TableSchema, c.COLUMN_NAME as ColumnName " + "from INFORMATION_SCHEMA.COLUMNS c " + "inner join INFORMATION_SCHEMA.TABLES t " + " on c.TABLE_NAME = t.TABLE_NAME " + "left join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu " + " on c.TABLE_NAME = ccu.TABLE_NAME and c.COLUMN_NAME = ccu.COLUMN_NAME " + "where t.TABLE_TYPE = 'BASE TABLE' and ccu.CONSTRAINT_NAME = '{0}'", conref); try { if (conn2.State != ConnectionState.Open) { conn2.Open(); GDAConnectionManager.NotifyConnectionOpened(conn2); } using (IDataReader reader2 = cmd2.ExecuteReader()) { if (reader2.Read()) { fm.ForeignKeyTableName = reader2["TableName"].ToString(); fm.ForeignKeyTableSchema = (reader2["TableSchema"] ?? "").ToString(); fm.ForeignKeyColumnName = reader2["ColumnName"].ToString(); } } } catch (Exception ex) { throw new GDAException(string.Format("Unable to obtain foreign key information for column {0} of table {1}.", fm.ColumnName, map.TableName), ex); } finally { if (conn2.State == ConnectionState.Open) { conn2.Close(); conn2.Dispose(); } } } } fm.IsAutoGenerated = (reader["DefaultValue"].ToString().Length > 0 && fm.IsPrimaryKey ? true : false); if (map.IsView) { } } } } } finally { conn.Close(); conn.Dispose(); } } catch (Exception ex) { throw new GDAException("An error occurred while analyzing the database schema.", ex); } }
public override void Analyze(string tableName) { IDbConnection conn = ProviderConfiguration.CreateConnection(); IDbCommand cmd = conn.CreateCommand(); IDbDataAdapter da = ProviderConfiguration.Provider.CreateDataAdapter(); da.SelectCommand = cmd; cmd.Connection = conn; conn.Open(); try { bool isSingleRun = tableName != null; cmd.CommandText = @"select VERSION ""Version"" from PRODUCT_COMPONENT_VERSION where lower(product) like ('%oracle%')"; string ver = cmd.ExecuteScalar().ToString(); int indexOfDot = ver.IndexOf("."); if (indexOfDot < 0) { throw new GDAException("Unable to determine Oracle database version."); } int version = Convert.ToInt32(ver.Substring(0, indexOfDot)); string select; string selectReferences; if (version < 9) { if (ver.Substring(0, 5).CompareTo("8.1.6") == 0) { selectReferences = @"select O.NAME ""ParentTable"", " + @" COL.NAME ""ParentColumn"", "+ @" OC.NAME ""ConstraintName"", "+ @" RC.NAME ""ConstraintReference"", "+ @" BASE.NAME ""ChildTable"", " + @" IDC.NAME ""ChildColumn"" "+ @"from SYS.CON$ OC, SYS.CON$ RC, SYS.CDEF$ C, SYS.OBJ$ O, SYS.COL$ COL, " + @" SYS.CCOL$ CC, SYS.OBJ$ IDX, SYS.IND$ I, SYS.ICOL$ IC, SYS.OBJ$ BASE, SYS.COL$ IDC " + @"where OC.CON# = C.CON# and C.RCON# = RC.CON# and C.TYPE# = 4 and C.OBJ# = O.OBJ# " + @" and C.CON# = CC.CON# and CC.OBJ# = COL.OBJ# and CC.INTCOL# = COL.INTCOL# "+ @" and IDX.OWNER# = O.OWNER# and RC.NAME = IDX.NAME and IDX.OBJ# = I.OBJ# "+ @" and I.TYPE# IN (1, 2, 3, 4, 6, 7, 9) and IC.OBJ# = IDX.OBJ# and IC.BO# = BASE.OBJ# "+ @" and IDC.OBJ# = BASE.OBJ# and IC.INTCOL# = IDC.INTCOL# and O.OWNER# = USERENV('SCHEMAID') " + @" and RC.NAME = '{0}' "; } else { selectReferences = @"select CO.TABLE_NAME ""ParentTable"", " + @" CO1.COLUMN_NAME ""ParentColumn"", " + @" CO.CONSTRAINT_NAME ""ConstraintName"", " + @" CO.R_CONSTRAINT_NAME ""ConstraintReference"", " + @" IDX2.TABLE_NAME ""ChildTable"", " + @" IDX2.COLUMN_NAME ""ChildColumn"" " + @"from USER_CONSTRAINTS CO, USER_CONS_COLUMNS CO1, USER_IND_COLUMNS IDX2 " + @"where CO.TABLE_NAME = CO1.TABLE_NAME AND " + @" CO.CONSTRAINT_NAME = CO1.CONSTRAINT_NAME AND " + @" CO.R_CONSTRAINT_NAME = IDX2.INDEX_NAME AND " + @" CO1.POSITION = IDX2.COLUMN_POSITION AND " + @" CO.CONSTRAINT_TYPE = 'R' AND " + @" CO.R_CONSTRAINT_NAME = '{0}'"; } select = @"select TAB.TABLE_NAME ""TableName"", " + @" TAB.COLUMN_NAME ""ColumnName"", " + @" TAB.DATA_TYPE ""Type"", " + @" TAB.DATA_LENGTH ""Size"", " + @" TAB.NULLABLE ""IsNullable"", " + @" TAB.DATA_DEFAULT ""DefaultValue"", " + @" CO.CONSTRAINT_NAME ""ConstraintName"", " + @" CO.CONSTRAINT_TYPE ""ConstraintType"", " + @" CO.R_CONSTRAINT_NAME ""ConstraintReference"", " + @" CO.DELETE_RULE ""DeleteRule"" " + @"from USER_TAB_COLUMNS TAB, USER_CONSTRAINTS CO, USER_CONS_COLUMNS CO1 " + @"where (TAB.TABLE_NAME = CO1.TABLE_NAME(+)) and " + @" (TAB.COLUMN_NAME = CO1.COLUMN_NAME(+)) and " + @" (CO1.constraint_name = CO.constraint_name(+)) and " + @" (CO1.TABLE_NAME = CO.TABLE_NAME(+)) " + @"order by TAB.TABLE_NAME, TAB.COLUMN_NAME, CO1.CONSTRAINT_NAME"; } else { select = @"select TAB.TABLE_NAME ""TableName"", " + @" TAB.COLUMN_NAME ""ColumnName"", "+ @" TAB.DATA_TYPE ""Type"", "+ @" TAB.DATA_LENGTH ""Size"", "+ @" TAB.NULLABLE ""IsNullable"", "+ @" TAB.DATA_DEFAULT ""DefaultValue"", "+ @" CO.CONSTRAINT_NAME ""ConstraintName"", "+ @" CO.CONSTRAINT_TYPE ""ConstraintType"", "+ @" CO.R_CONSTRAINT_NAME ""ConstraintReference"", "+ @" CO.DELETE_RULE ""DeleteRule"", "+ @" CM.COMMENTS ""TableComment"", "+ @" CC.COMMENTS ""ColumnComment"" "+ @"from USER_TAB_COLUMNS TAB LEFT OUTER JOIN " + @" (USER_CONSTRAINTS CO INNER JOIN USER_CONS_COLUMNS CO1 ON " + @" CO.TABLE_NAME = CO1.TABLE_NAME AND CO.CONSTRAINT_NAME = CO1.CONSTRAINT_NAME) ON "+ @" TAB.TABLE_NAME = CO.TABLE_NAME AND TAB.COLUMN_NAME = CO1.COLUMN_NAME "+ @" LEFT JOIN USER_TAB_COMMENTS CM ON TAB.TABLE_NAME = CM.TABLE_NAME " + @" LEFT JOIN USER_COL_COMMENTS CC ON TAB.TABLE_NAME = CC.TABLE_NAME AND TAB.COLUMN_NAME = CC.COLUMN_NAME " + @"order by TAB.TABLE_NAME, TAB.COLUMN_NAME, CO1.CONSTRAINT_NAME"; selectReferences = @"select CO.TABLE_NAME ""ParentTable"", " + @" CO1.COLUMN_NAME ""ParentColumn"", "+ @" CO.CONSTRAINT_NAME ""ConstraintName"", "+ @" CO.R_CONSTRAINT_NAME ""ConstraintReference"", "+ @" IDX2.TABLE_NAME ""ChildTable"", "+ @" IDX2.COLUMN_NAME ""ChildColumn"" "+ @"from (USER_CONSTRAINTS CO INNER JOIN USER_CONS_COLUMNS CO1 ON " + @" CO.TABLE_NAME = CO1.TABLE_NAME AND CO.CONSTRAINT_NAME = CO1.CONSTRAINT_NAME) "+ @" LEFT OUTER JOIN USER_IND_COLUMNS IDX2 ON "+ @" CO.R_CONSTRAINT_NAME = IDX2.INDEX_NAME AND CO1.POSITION = IDX2.COLUMN_POSITION "+ @"where CO.CONSTRAINT_TYPE = 'R' AND " + @" CO.R_CONSTRAINT_NAME = '{0}'"; } da.SelectCommand.CommandText = select; DataSet ds = new DataSet(); da.Fill(ds); DataTable dt = ds.Tables[0]; for (int i = 0; i < dt.Rows.Count; i++) { try { string dbTableName = (string)dt.Rows[i]["TableName"]; if (!isSingleRun || tableName.ToLower() == dbTableName.ToLower()) { TableMap map = GetTableMap(dbTableName); if (map == null) { map = new TableMap(ProviderConfiguration, dbTableName); if (dt.Rows[i]["TableComment"] != DBNull.Value) { map.Comment = (string)dt.Rows[i]["TableComment"]; } this.tablesMaps[dbTableName.ToLower()] = map; } string columnName = (string)dt.Rows[i]["ColumnName"]; FieldMap fm = map.GetFieldMapFromColumn(columnName); if (fm == null) { fm = new FieldMap(map, columnName); if (dt.Rows[i]["ColumnComment"] != DBNull.Value) { fm.Comment = (string)dt.Rows[i]["ColumnComment"]; } map.Fields.Add(fm); } string typeInfo = (string)dt.Rows[i]["Type"]; int pos = typeInfo.IndexOf("("); if (pos != -1) { typeInfo = typeInfo.Substring(0, pos); } fm.SetDbType(typeInfo, false); fm.IsNullable = GetBoolean((string)dt.Rows[i]["IsNullable"]); if (dt.Rows[i]["Size"] != DBNull.Value) { fm.Size = Convert.ToInt32(dt.Rows[i]["Size"]); } if (dt.Rows[i]["ConstraintName"] != DBNull.Value) { string typ = (string)dt.Rows[i]["ConstraintType"]; if (typ.ToLower() == "p") { fm.IsPrimaryKey = true; } else if (typ.ToLower() == "r") { string conref = (string)dt.Rows[i]["ConstraintReference"]; cmd.CommandText = String.Format(selectReferences, conref); using (IDataReader dr = cmd.ExecuteReader()) { if (dr.Read()) { fm.ForeignKeyTableName = dr.GetString(dr.GetOrdinal("ChildTable")); fm.ForeignKeyColumnName = dr.GetString(dr.GetOrdinal("ChildColumn")); } } } } } } catch (Exception fe) { } } } catch (Exception e) { throw new GDAException("An error occurred while analyzing the database schema.", e); } finally { conn.Close(); } }
/// <summary> /// Recupera os dados das contraints da tabela. /// </summary> /// <param name="map"></param> private void GetConstraintData(TableMap map) { IDbConnection conn = ProviderConfiguration.CreateConnection(); IDbCommand cmd = conn.CreateCommand(); cmd.Connection = conn; cmd.CommandText = String.Format("show create table `{0}`", map.TableName); if (conn.State != ConnectionState.Open) { conn.Open(); } try { IDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { string comment = dr.GetFieldType(1) == typeof(byte[]) ? Encoding.Default.GetString((byte[])dr[1]) : dr.GetString(1); if (comment != null && comment.Length > 5) { string[] comments = comment.Split(','); foreach (string cmt in comments) { string tmp = cmt.Trim(); string pattern = @"\(`?(?<column>\w+)`?\) REFER .*/(?<fkTable>\w+)[`\s]+\(`?(?<fkColumn>\w+)`?\)"; Regex regex = new Regex(pattern, RegexOptions.ExplicitCapture | RegexOptions.Compiled); Match m = regex.Match(tmp); if (m.Success) { FieldMap fm = map.GetFieldMapFromColumn(m.Groups["column"].Value); if (fm != null) { fm.ForeignKeyTableName = m.Groups["fkTable"].Value; fm.ForeignKeyColumnName = m.Groups["fkColumn"].Value; } } else { pattern = @"[\s\w]FOREIGN KEY\s\(`?(?<column>\w+)`?\) REFERENCES `?(?<fkTable>\w+)`? \(`?(?<fkColumn>\w+)`?\)"; Regex regexNew = new Regex(pattern, RegexOptions.ExplicitCapture | RegexOptions.Compiled); Match mNew = regexNew.Match(tmp); if (mNew.Success) { FieldMap fm = map.GetFieldMapFromColumn(mNew.Groups["column"].Value); if (fm != null) { fm.ForeignKeyTableName = mNew.Groups["fkTable"].Value; fm.ForeignKeyColumnName = mNew.Groups["fkColumn"].Value; } } else if (tmp != null) { int index = tmp.IndexOf("REFER"); if (index > 0) { string columnName = ExtractColumn(tmp.Substring(0, index - 1)); tmp = tmp.Substring(index + 5, tmp.Length - index - 5).Trim(); index = tmp.IndexOf("/"); int start = tmp.IndexOf("("); int end = tmp.IndexOf(")"); if (index > 0 && start > 0 && end > start) { string foreignTable = tmp.Substring(index + 1, start - index - 1); string foreignColumn = tmp.Substring(start + 1, end - start - 1); FieldMap fm = map.GetFieldMapFromColumn(columnName); fm.ForeignKeyTableName = foreignTable; fm.ForeignKeyColumnName = foreignColumn; } } } } } } } } finally { conn.Close(); } }