Commits

Inside Zhou committed 946ba5c

OracleDataHelper自动生成cache、log表的功能现在可以自动同步原表中新增的列;生成log表时,补上遗漏的update trigger。

  • Participants
  • Parent commits ef50b9d

Comments (0)

Files changed (3)

File Inside.Util/Properties/AssemblyInfo.cs

  * 当第二位数字变化时,后两位数字重置为0。
  * 除非有特殊声明,否则前三位数字变化表示均不保证向后兼容。
  */
-[assembly: AssemblyVersion("2.0.3.3")]
+[assembly: AssemblyVersion("2.0.3.4")]

File Inside.Util/Sql/InputPlan.cs

 
 				foreach(dynamic col in this._columns) {
 					dynamic meta = new DyObj();
+					meta.name = col.name;
 					meta.type = col.type;
+
 					meta.nullable = col.nullable;
 					meta.default_value = col.default_value;
 					meta.has_default = col.has_default;
 
+					meta.data_type = col.data_type;
+					meta.length = col.length;
+					meta.char_length = col.char_length;
+					meta.precision = col.precision;
+					meta.scale = col.scale;
+
 					result[col["name"] as string] = meta;
 				}
 
 			var pk_col_names = (from c in sql.Init(@"SELECT column_name FROM user_cons_columns t0
 JOIN user_constraints t1 ON t0.constraint_name = t1.constraint_name
 WHERE t1.constraint_type = 'P' AND t0.table_name = upper(:relationName)").AddParameter("relationName", this.RelationName).GetDataTable().AsEnumerable() select c["column_name"] as string).ToList();
+
 			foreach(DataColumn col in dt.Columns) {
 				var c = new DyObj();
 				c["name"] = col.ColumnName;
 				}
 			}
 
-			var cols = sql.Init("select column_name, data_default, data_length, case nullable when 'Y' then 1 when 'N' then 0 end as nullable from user_tab_columns where table_name = upper(:relationName)").AddParameter("relationName", this.RelationName).GetDataTable().ToDyObjs();
+			var cols = sql.Init("select column_name, data_type, data_default, data_length, char_length, data_precision, data_scale, case nullable when 'Y' then 1 when 'N' then 0 end as nullable from user_tab_columns where table_name = upper(:relationName)").AddParameter("relationName", this.RelationName).GetDataTable().ToDyObjs();
 			foreach(dynamic c in cols) {
 				dynamic col = (from dynamic column in this._columns where column.name == c.column_name select column).Single();
 
 				col.nullable = 1 == c.nullable;
 				col.has_default = null != c.data_default;
 				col.default_value = c.data_default;
+				col.data_type = c.data_type;
 				col.length = c.data_length;
+				col.char_length = c.char_length;
+				col.precision = c.data_precision;
+				col.scale = c.data_scale;
 			}
 		}
 
 						p.ParameterName = c.name;
 						p.Direction = ParameterDirection.Output;
 
-						if(typeof(decimal) == c.type
-							|| typeof(Int64) == c.type || typeof(Int32) == c.type || typeof(Int16) == c.type
-							|| typeof(byte) == c.type || typeof(SByte) == c.type
-							|| typeof(UInt64) == c.type || typeof(UInt32) == c.type || typeof(UInt16) == c.type
-							|| typeof(double) == c.type || typeof(Single) == c.type) {
+						if(c.data_type.StartsWith("VARCHAR", StringComparison.CurrentCultureIgnoreCase)
+							|| c.data_type.StartsWith("CHAR", StringComparison.CurrentCultureIgnoreCase)
+							) {
 
-							p.DbType = DbType.Decimal;
-						}
-						else if(typeof(DateTime) == c.type) {
-							p.DbType = DbType.DateTime;
-						}
-						else if(typeof(string) == c.type) {
 							p.DbType = DbType.String;
 							p.Size = (int)c.length;
 						}
+						else if(c.data_type.StartsWith("NUMBER", StringComparison.CurrentCultureIgnoreCase)) {
+							p.DbType = DbType.Decimal;
+						}
+						else if(c.data_type.StartsWith("TIMESTAMP", StringComparison.CurrentCultureIgnoreCase)
+							|| c.data_type.StartsWith("DATE", StringComparison.CurrentCultureIgnoreCase)
+							) {
+
+							p.DbType = DbType.DateTime;
+						}
 						else {
-							throw new NotSupportedException(string.Format("暂不支持此类型的默认值 : {0} : {1}", c.name, c.type));
+							throw new NotSupportedException(string.Format("暂不支持此类型的默认值 : {0} : {1}", c.name, c.data_type));
 						}
 
 						outParam.Add(p);

File Inside.Util/Sql/OracleDataHelper.cs

 
 		public static void GenerateCacheTable(string cacheRelationName, string relationName, SqlHelper sql) {
 			using(var trans = sql.BeginTrans()) {
-				if(0 == (decimal)sql.Init("select count(*) from user_tables where table_name = upper(:table_name)").AddParameter("table_name", cacheRelationName).ExecuteScalarAuto()) {
+				var isCacheTableExist = 0 != (decimal)sql.Init("select count(*) from user_tables where table_name = upper(:table_name)").AddParameter("table_name", cacheRelationName).ExecuteScalarAuto();
+
+				if(isCacheTableExist) {
+					_SyncColumn(relationName, cacheRelationName, sql);
+				}
+				else {
 					sql.Init("create table {0} as select * from {1} where 1 = 2".Fmt(cacheRelationName, relationName)).ExecuteNonQueryAuto();
-					foreach(var item in InputPlan.CreateInstance(relationName, sql).ColumnMeta) {
-						dynamic meta = item.Value;
-						if(!ObjectHelper.IsNil(meta.default_value)) {
-							sql.Init(@"alter table {0} modify(""{1}"" default {2})".Fmt(cacheRelationName, item.Key.ToUpper(), meta.default_value as object)).ExecuteNonQueryAuto();
-						}
-					}
 
 					sql.Init(@"
 alter table {0} add(
   , ""CACHE_TYPE"" VARCHAR2(64 CHAR)
 )
 ".Fmt(cacheRelationName)).ExecuteNonQueryAuto();
+
+					foreach(var item in InputPlan.CreateInstance(relationName, sql).ColumnMeta) {
+						dynamic meta = item.Value;
+						if(!ObjectHelper.IsNil(meta.default_value)) {
+							sql.Init(@"alter table {0} modify(""{1}"" default {2})".Fmt(cacheRelationName, item.Key.ToUpper(), meta.default_value as object)).ExecuteNonQueryAuto();
+						}
+					}
 				}
+
 				trans.Commit();
 			}
 		}
 
+		static void _SyncColumn(string sourceName, string targetName, SqlHelper sql) {
+			var colMeta = InputPlan.CreateInstance(sourceName, sql).ColumnMeta;
+			var logColMeta = InputPlan.CreateInstance(targetName, sql).ColumnMeta;
+
+			var adding = (from meta in colMeta where !logColMeta.Attrs.Exists(colName => string.Equals(colName, meta.Key)) select meta);
+			foreach(var item in adding) {
+				dynamic meta = item.Value;
+
+				string dataType = null;
+
+				if(meta.data_type.StartsWith("VARCHAR", StringComparison.CurrentCultureIgnoreCase)
+					|| meta.data_type.StartsWith("CHAR", StringComparison.CurrentCultureIgnoreCase)
+					) {
+
+					dataType = string.Format("{0}({1} CHAR)", meta.data_type, meta.char_length);
+				}
+				else if(meta.data_type.StartsWith("NUMBER", StringComparison.CurrentCultureIgnoreCase)) {
+					if(ObjectHelper.IsNil(meta.precision) || ObjectHelper.IsNil(meta.scale)) {
+						dataType = meta.data_type;
+					}
+					else {
+						dataType = string.Format("{0}({1},{2})", meta.data_type, meta.precision, meta.scale);
+					}
+				}
+				else if(meta.data_type.StartsWith("TIMESTAMP", StringComparison.CurrentCultureIgnoreCase)
+					|| meta.data_type.StartsWith("DATE", StringComparison.CurrentCultureIgnoreCase)
+					) {
+
+					dataType = meta.data_type;
+				}
+
+				string nullable = meta.nullable ? string.Empty : "NOT NULL";
+				string defaultClause = meta.has_default ? string.Format("DEFAULT {0}", meta.default_value) : string.Empty;
+
+				sql.Init(@"
+alter table {0} add(
+  ""{1}"" {2} {3} {4}
+)
+".Fmt(targetName, item.Key, dataType, defaultClause, nullable)).ExecuteNonQueryAuto();
+			}
+		}
+
 		public static void GenerateArchiveTable(string relationArchiveName, string relationLogName, string relationName, SqlHelper sql) {
 			using(var trans = sql.BeginTrans()) {
-				if(0 == (decimal)sql.Init("select count(*) from user_tables where table_name = upper(:table_name)").AddParameter("table_name", relationArchiveName).ExecuteScalarAuto()) {
-					sql.Init("create global temporary table {0} as select * from {1} where 1 = 2".Fmt(relationArchiveName, relationName)).ExecuteNonQueryAuto();
+				if(0 != (decimal)sql.Init("select count(*) from user_tables where table_name = upper(:table_name)").AddParameter("table_name", relationArchiveName).ExecuteScalarAuto()) {
+					sql.Init("drop table {0}".Fmt(relationArchiveName)).ExecuteNonQueryAuto();
 				}
+				sql.Init("create global temporary table {0} as select * from {1} where 1 = 2".Fmt(relationArchiveName, relationName)).ExecuteNonQueryAuto();
 
-				if(0 == (decimal)sql.Init("select count(*) from user_tables where table_name = upper(:table_name)").AddParameter("table_name", relationLogName).ExecuteScalarAuto()) {
+				var isLogTableExist = 0 != (decimal)sql.Init("select count(*) from user_tables where table_name = upper(:table_name)").AddParameter("table_name", relationLogName).ExecuteScalarAuto();
+
+				if(isLogTableExist) {
+					_SyncColumn(relationName, relationLogName, sql);
+				}
+				else {
 					sql.Init("create table {0} as select * from {1} where 1 = 2".Fmt(relationLogName, relationName)).ExecuteNonQueryAuto();
 					sql.Init(@"
 alter table {0} add(
-  ""LOG_ON"" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
+  ""LOG_ON"" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
   , ""LOG_TYPE"" VARCHAR2(64 CHAR)
 )
 ".Fmt(relationLogName)).ExecuteNonQueryAuto();
 
 				sql.Init(triggerTemplate.Fmt(relationLogName, columnTemplate, valuesTemplate.Fmt(":new."), relationName, "insert")).ExecuteNonQueryAuto();
 				sql.Init(triggerTemplate.Fmt(relationLogName, columnTemplate, valuesTemplate.Fmt(":old."), relationName, "delete")).ExecuteNonQueryAuto();
+				sql.Init(triggerTemplate.Fmt(relationLogName, columnTemplate, valuesTemplate.Fmt(":old."), relationName, "update")).ExecuteNonQueryAuto();
 
 				var rowVariableName = "myrow";
 				if(inputPlan.PKColumnNames.Count > 0) {