转载

CodeSmith7代码生成器针对PostgreSQL数据库无法使用的Bug修复全过程

前言

最近公司需要将原来使用的MSSQL数据库整体迁移至pgsql，需要使用CodeSmith生成IBatisNet的配置文件。按照提示安装了Npgsql.dll后依然无法使用。引发了本次通过反编译修复相关的Bug。主要修复了一下错误：

1、未能加载文件或程序集“Npgsql, Version=2.2.0.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7”

2、无法查看列信息

3、缺少表、列自描述

一、使用ILSpy反编译SchemaExplorer.PostgreSQLSchemaProvider.dll

打开ILSpy，将SchemaExplorer.PostgreSQLSchemaProvider.dll附加至ILSpy，如下图所示：

CodeSmith7代码生成器针对PostgreSQL数据库无法使用的Bug修复全过程

二、针对反编译的代码进行错误修复（详细修复过程略过）。

三、主要修复函数。

GetTables、GetTableColumns、GetTablePrimaryKey、GetTableKeys。因为我所使用的功能仅限于这几个函数所以只针对该相关的方法错误进行了修复。

1、修复表架构缺少描述的Bug

GetTables反编译的关键源码

public TableSchema[] GetTables(string connectionString, DatabaseSchema database) {  List<TableSchema> list = new List<TableSchema>();  using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString))  {   npgsqlConnection.Open();   using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand("select tablename, tableowner from pg_catalog.pg_tables where schemaname = 'public' order by tablename", npgsqlConnection))   {    using (NpgsqlDataReader npgsqlDataReader = npgsqlCommand.ExecuteReader(CommandBehavior.CloseConnection))    {     while (npgsqlDataReader.Read())     {      if (npgsqlDataReader.GetString(0).ToUpper() != "CODESMITH_EXTENDED_PROPERTIES")      {       list.Add(new TableSchema(database, npgsqlDataReader.GetString(0), npgsqlDataReader.GetString(1), DateTime.MinValue));      }     }     if (!npgsqlDataReader.IsClosed)     {      npgsqlDataReader.Close();     }    }   }   if (npgsqlConnection.State != ConnectionState.Closed)   {    npgsqlConnection.Close();   }  }  return list.ToArray(); }

修复的源码

public TableSchema[] GetTables(string connectionString, DatabaseSchema database) {  List<TableSchema> list = new List<TableSchema>();  using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString))  {   npgsqlConnection.Open();   using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand("select tablename, tableowner,obj_description(relfilenode,'pg_class') as pg_description from pg_catalog.pg_tables as t left join pg_catalog.pg_class as c on t.tablename = c.relname where t.schemaname = 'public' order by t.tablename", npgsqlConnection))   {    using (NpgsqlDataReader npgsqlDataReader = npgsqlCommand.ExecuteReader(CommandBehavior.CloseConnection))    {     while (npgsqlDataReader.Read())     {      if (npgsqlDataReader.GetString(0).ToUpper() != "CODESMITH_EXTENDED_PROPERTIES")      {       list.Add(new TableSchema(database, npgsqlDataReader.GetString(0), npgsqlDataReader.GetString(1), DateTime.MinValue,new ExtendedProperty[]{       new ExtendedProperty("CS_Description", npgsqlDataReader.GetString(2) ?? string.Empty, DbType.String, PropertyStateEnum.ReadOnly)       }));      }     }    }   }  }  return list.ToArray(); }

修改前的源码与修改后的源码主要有2个区别：增加了查询表描述的字段、添加扩展属性CS_Description这个属性是关键将可以通过TableSchema.Description读取表描述信息，这里存在一个问题，第二个参数不能为null，如果为null需要转换为空串。

2、修复读取列信息的bug

CodeSmith7代码生成器针对PostgreSQL数据库无法使用的Bug修复全过程

修改前的代码

public ColumnSchema[] GetTableColumns(string connectionString, TableSchema table) {  List<ColumnSchema> list = new List<ColumnSchema>();  using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString))  {   npgsqlConnection.Open();   string text = string.Format("select column_name, is_nullable, character_maximum_length, numeric_precision, numeric_scale, data_type, udt_name from information_schema.columns where table_schema = 'public' and table_name='{0}'", table.Name);   using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection))   {    using (NpgsqlDataReader npgsqlDataReader = npgsqlCommand.ExecuteReader(CommandBehavior.CloseConnection))    {     while (npgsqlDataReader.Read())     {      bool allowDBNull = npgsqlDataReader.IsDBNull(1) || npgsqlDataReader.GetString(1) == "YES";      byte precision = (byte)(npgsqlDataReader.IsDBNull(3) ? 0 : npgsqlDataReader.GetInt32(3));      int size = npgsqlDataReader.IsDBNull(2) ? 0 : npgsqlDataReader.GetInt32(2);      int scale = npgsqlDataReader.IsDBNull(4) ? 0 : npgsqlDataReader.GetInt32(4);      string name = npgsqlDataReader.IsDBNull(0) ? string.Empty : npgsqlDataReader.GetString(0);      string text2 = npgsqlDataReader.IsDBNull(5) ? string.Empty : npgsqlDataReader.GetString(5);      string type = npgsqlDataReader.IsDBNull(6) ? string.Empty : npgsqlDataReader.GetString(6);      list.Add(new ColumnSchema(table, name, PostgreSQLSchemaProvider.GetDbType(type), text2, size, precision, scale, allowDBNull, new ExtendedProperty[]      {       new ExtendedProperty("NpgsqlDbType", PostgreSQLSchemaProvider.GetNativeDbType(text2), DbType.String)      }));     }     if (!npgsqlDataReader.IsClosed)     {      npgsqlDataReader.Close();     }    }   }   if (npgsqlConnection.State != ConnectionState.Closed)   {    npgsqlConnection.Close();   }  }  return list.ToArray(); }

修改后的代码

public ColumnSchema[] GetTableColumns(string connectionString, TableSchema table) {  List<ColumnSchema> list = new List<ColumnSchema>();  using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString))  {   npgsqlConnection.Open();   string text = string.Format("select column_name, is_nullable, character_maximum_length, numeric_precision, numeric_scale, data_type, udt_name,col_description(b.attrelid,b.attnum) as pg_description from information_schema.columns as a join pg_attribute as b on a.column_name=b.attname join pg_class as c on a.table_name=c.relname and b.attrelid = c.oid where b.attnum>0 and a.table_schema = 'public' and a.table_name='{0}'", table.Name);   using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection))   {    using (NpgsqlDataReader npgsqlDataReader = npgsqlCommand.ExecuteReader(CommandBehavior.CloseConnection))    {     while (npgsqlDataReader.Read())     {      bool allowDBNull = npgsqlDataReader.IsDBNull(1) || npgsqlDataReader.GetString(1) == "YES";      byte precision = (byte)(npgsqlDataReader.IsDBNull(3) ? 0 : npgsqlDataReader.GetInt32(3));      int size = npgsqlDataReader.IsDBNull(2) ? 0 : npgsqlDataReader.GetInt32(2);      int scale = npgsqlDataReader.IsDBNull(4) ? 0 : npgsqlDataReader.GetInt32(4);      string name = npgsqlDataReader.IsDBNull(0) ? string.Empty : npgsqlDataReader.GetString(0);      string text2 = npgsqlDataReader.IsDBNull(5) ? string.Empty : npgsqlDataReader.GetString(5);      string type = npgsqlDataReader.IsDBNull(6) ? string.Empty : npgsqlDataReader.GetString(6);      list.Add(new ColumnSchema(table, name, PostgreSQLSchemaProvider.GetDbType(type), text2, size, precision, scale, allowDBNull, new ExtendedProperty[]    {     new ExtendedProperty("NpgsqlDbType", PostgreSQLSchemaProvider.GetNativeDbType(text2), DbType.String),     new ExtendedProperty("CS_Description",npgsqlDataReader.GetString(7)?? string.Empty, DbType.String)    }));     }     if (!npgsqlDataReader.IsClosed)     {      npgsqlDataReader.Close();     }    }   }  }  return list.ToArray(); }

修改前的源码与修改后的源码主要有2个区别：增加了查询表描述的字段、添加扩展属性CS_Description这个属性是关键将可以通过ColumnSchema.Description读取表描述信息，这里存在一个问题，第二个参数不能为null，如果为null需要转换为空串。

三、修复主键信息

因为在页面加载列时，同时会根据表属性去加载列的主键，外键属性。

public TableKeySchema[] GetTableKeys(string connectionString, TableSchema table) {  List<TableKeySchema> list = new List<TableKeySchema>();  using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString))  {   npgsqlConnection.Open();   string text = string.Format("SELECT constraint_name as constrname FROM information_schema.table_constraints WHERE table_name = '{0}' AND constraint_type = 'FOREIGN KEY' AND constraint_schema='public'", table.Name);   using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection))   {    string text2 = string.Format("SELECT px.conname as constrname, att.attname as colname, fore.relname as reftabname, fatt.attname as refcolname, CASE px.confupdtype WHEN 'a' THEN 'NO ACTION' WHEN 'r' THEN 'RESTRICT' WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' END AS on_update, CASE px.confdeltype WHEN 'a' THEN 'NO ACTION' WHEN 'r' THEN 'RESTRICT' WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' END AS on_delete, CASE px.contype WHEN 'p' THEN true WHEN 'f' THEN false END as IsPrimaryKey from pg_constraint px left join pg_class home on (home.oid = px.conrelid) left join pg_class fore on (fore.oid = px.confrelid) left join pg_attribute att on (att.attrelid = px.conrelid AND att.attnum = ANY(px.conkey)) left join pg_attribute fatt on (fatt.attrelid = px.confrelid AND fatt.attnum = ANY(px.confkey)) where (home.relname = '{0}') and px.contype = 'f' order by constrname", table.Name);    using (NpgsqlCommand npgsqlCommand2 = new NpgsqlCommand(text2, npgsqlConnection))    {     NpgsqlDataAdapter npgsqlDataAdapter = new NpgsqlDataAdapter(npgsqlCommand);     DataSet dataSet = new DataSet();     npgsqlDataAdapter.Fill(dataSet, "constraint");     npgsqlDataAdapter.SelectCommand = npgsqlCommand2;     npgsqlDataAdapter.Fill(dataSet, "keys");     if (dataSet.Tables[0].Rows.Count > 0)     {      dataSet.Relations.Add("Contraint_to_Keys", dataSet.Tables[0].Columns["constrname"], dataSet.Tables[1].Columns["constrname"]);      foreach (DataRow dataRow in dataSet.Tables[0].Rows)      {       string name = dataRow["constrname"].ToString();       DataRow[] childRows = dataRow.GetChildRows("Contraint_to_Keys");       string[] array = new string[childRows.Length];       string[] array2 = new string[childRows.Length];       string name2 = table.Name;       string primaryKeyTable = childRows[0]["reftabname"].ToString();       for (int i = 0; i < childRows.Length; i++)       {        array2[i] = childRows[i]["colname"].ToString();        array[i] = childRows[i]["refcolname"].ToString();       }       list.Add(new TableKeySchema(table.Database, name, array2, name2, array, primaryKeyTable));      }     }    }   }   string text3 = string.Format("SELECT px.conname as constrname FROM pg_constraint px left join pg_class fore on fore.oid = px.confrelid where fore.relname = '{0}'", table.Name);   using (NpgsqlCommand npgsqlCommand3 = new NpgsqlCommand(text3, npgsqlConnection))   {    string text4 = string.Format("SELECT px.conname as constrname, fatt.attname as colname, home.relname as reftabname, att.attname as refcolname, CASE px.confupdtype WHEN 'a' THEN 'NO ACTION' WHEN 'r' THEN 'RESTRICT' WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' END AS on_update, CASE px.confdeltype WHEN 'a' THEN 'NO ACTION' WHEN 'r' THEN 'RESTRICT' WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' END AS on_delete, CASE px.contype WHEN 'p' THEN true WHEN 'f' THEN false END as IsPrimaryKey from pg_constraint px left join pg_class home on (home.oid = px.conrelid) left join pg_class fore on (fore.oid = px.confrelid) left join pg_attribute att on (att.attrelid = px.conrelid AND att.attnum = ANY(px.conkey)) left join pg_attribute fatt on (fatt.attrelid = px.confrelid AND fatt.attnum = ANY(px.confkey)) where (fore.relname = '{0}') order by constrname", table.Name);    using (NpgsqlCommand npgsqlCommand4 = new NpgsqlCommand(text4, npgsqlConnection))    {     NpgsqlDataAdapter npgsqlDataAdapter2 = new NpgsqlDataAdapter();     DataSet dataSet2 = new DataSet();     npgsqlDataAdapter2.SelectCommand = npgsqlCommand3;     npgsqlDataAdapter2.Fill(dataSet2, "constraint");     npgsqlDataAdapter2.SelectCommand = npgsqlCommand4;     npgsqlDataAdapter2.Fill(dataSet2, "keys");     if (dataSet2.Tables[0].Rows.Count > 0)     {      dataSet2.Relations.Add("Contraint_to_Keys", dataSet2.Tables[0].Columns["constrname"], dataSet2.Tables[1].Columns["constrname"]);      foreach (DataRow dataRow2 in dataSet2.Tables[0].Rows)      {       string name3 = dataRow2["constrname"].ToString();       DataRow[] childRows2 = dataRow2.GetChildRows("Contraint_to_Keys");       string[] array3 = new string[childRows2.Length];       string[] array4 = new string[childRows2.Length];       string foreignKeyTable = childRows2[0]["reftabname"].ToString();       string name4 = table.Name;       for (int j = 0; j < childRows2.Length; j++)       {        array4[j] = childRows2[j]["refcolname"].ToString();        array3[j] = childRows2[j]["colname"].ToString();       }       list.Add(new TableKeySchema(table.Database, name3, array4, foreignKeyTable, array3, name4));      }     }    }   }  }  return list.ToArray(); } public PrimaryKeySchema GetTablePrimaryKey(string connectionString, TableSchema table) {  PrimaryKeySchema result = null;  DataSet ds = new DataSet();  using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString))  {   npgsqlConnection.Open();   string text = string.Format("select constraint_name from information_schema.table_constraints where constraint_schema='public' and table_name='{0}' and constraint_type='PRIMARY KEY'", table.Name);   using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection))   {    using (NpgsqlDataAdapter nda = new NpgsqlDataAdapter(npgsqlCommand))    {     nda.Fill(ds, "table_constraints");    }   }   string text2 = string.Format("select px.conname as ConstraintName, att.attname as ColumnName from pg_constraint px inner join pg_class home on (home.oid = px.conrelid) left join pg_attribute att on (att.attrelid = px.conrelid AND att.attnum = ANY(px.conkey)) where (home.relname = '{0}') and px.contype = 'p'", table.Name);   using (NpgsqlCommand npgsqlCommand2 = new NpgsqlCommand(text2, npgsqlConnection))   {    using (NpgsqlDataAdapter nda = new NpgsqlDataAdapter(npgsqlCommand2))    {     nda.Fill(ds, "pg_constraint");    }   }   foreach (DataRow item in ds.Tables["table_constraints"].Rows)   {    List<string> list = new List<string>();    foreach (DataRow item2 in ds.Tables["pg_constraint"].Rows)    {     list.Add(item2.Field<string>("ColumnName"));    }    result = new PrimaryKeySchema(table, item.Field<string>("constraint_name"), list.ToArray());   }  }  return result; }