/* * -- Create sequence * create sequence S_YW_EQUIP_BAS_UNIT * minvalue 1 * maxvalue 999999999999999999999999999 * start with 421 * increment by 1 * cache 20 * cycle * order; */ public List <CreateSqlObject> GetCreateOracleSql(string tableSpace = null) { StringBuilder sb = new StringBuilder(); //sb.AppendLine("-- Create sequence"); sb.AppendLine("create sequence " + sequence_name); sb.AppendLine("minvalue " + min_value); sb.AppendLine("maxvalue " + max_value); sb.AppendLine("start with " + last_number); sb.AppendLine("increment by " + increment_by); int cache = 0; int.TryParse(Convert.ToString(cache_size), out cache); if (cache > 0) { sb.AppendLine("cache " + cache_size); } if (Convert.ToString(cycle_flag) == "Y") { sb.AppendLine("cycle"); } if (Convert.ToString(order_flag) == "Y") { sb.AppendLine("order"); } string str = sb.ToString().TrimEnd('\r', '\n'); // str += ";"; CreateSqlObject obj = new CreateSqlObject(str, "创建序列" + sequence_name); return(new List <CreateSqlObject>() { obj }); }
/* * create index IS_DEL on PT_CAMERA_INFO (IS_DEL) * tablespace PAHF * pctfree 10 * initrans 2 * maxtrans 255 * storage * ( * initial 64K * minextents 1 * maxextents unlimited * );*/ public List <CreateSqlObject> GetCreateOracleSql(string tableSpace = null) { DoLoadCulumns(); StringBuilder sb = new StringBuilder(); string cols = string.Join(",", _column_names); //uniqueness;//NONUNIQUE,UNIQUE,BITMAP string uniq = Convert.ToString(uniqueness); if (uniq == "UNIQUE") { sb.AppendLine("create unique index " + index_name + " on " + table_name + " (" + cols + ")"); } else if (uniq == "BITMAP") { sb.AppendLine("create bitmap index " + index_name + " on " + table_name + " (" + cols + ")"); } else { sb.AppendLine("create index " + index_name + " on " + table_name + " (" + cols + ")"); } sb.Append(GetNoTopLineOracleSql(tableSpace)); CreateSqlObject obj = new CreateSqlObject(sb.ToString(), "创建表" + table_name + "索引" + index_name); return(new List <CreateSqlObject> { obj }); }
public List <CreateSqlObject> GetCreateOracleSql(string tableSpace = null) { CreateSqlObject sqlobj = new CreateSqlObject(); sqlobj.comment = "创建触发器"; string sql = "CREATE OR REPLACE TRIGGER " + trigger_name + "\r\n"; string[] dds = Convert.ToString(description).Split('\n'); for (int i = 1; i < dds.Length; i++) { sql += dds[i].TrimEnd('\r', '\n') + "\r\n"; } //\"SAFECITY\".TR_JFJK_TOTALHOUSE_SYNC_AFTER\nAFTER INSERT OR DELETE OR UPDATE on YW_JFJK_TOTALHOUSE\nfor each row\n string body = Convert.ToString(trigger_body); if (body.IndexOf("\r\n") < 0) { body = body.Replace("\n", "\r\n"); } sql += body; sqlobj.sql = sql; return(new List <CreateSqlObject>() { sqlobj }); }
public List <CreateSqlObject> GetCreateOracleSql(string tableSpace = null) { CreateSqlObject obj = new CreateSqlObject(sql_body, "创建资源" + name); if (obj.sql.IndexOf("\r\n") < 0) { obj.sql = obj.sql.Replace("\n", "\r\n"); } return(new List <CreateSqlObject>() { obj }); }
public List<CreateSqlObject> GetCreateOracleSql(string tableSpace = null) { CreateSqlObject obj = new CreateSqlObject(sql_body, "创建资源" + name); if (obj.sql.IndexOf("\r\n")<0) { obj.sql = obj.sql.Replace("\n", "\r\n"); } return new List<CreateSqlObject>() { obj }; }
/* * alter table PT_CAMERA_INFO * add constraint PT_CAMERA_INFO_KEY primary key (ID); * alter table PT_CAMERA_INFO * add constraint CAMERA_NO_UNIQUE unique (CAMERA_NO); * alter table PT_CAMERA_INFO * add constraint PT_CAMERA_INFO_FOR foreign key (ORG_ID) * references PT_ORG_INFO (ID) on delete set null; * */ public List <CreateSqlObject> GetCreateOracleSql(string tableSpace = null) { DoLoadCols(); DoLoadIndex(); List <CreateSqlObject> list = new List <CreateSqlObject>(); if (_column_names.Count == 0) { return(list); } string type = Convert.ToString(constraint_type); string indexName = Convert.ToString(index_name); if (type == "R")//不是外键 { if (oracleConstraintClass == null) { return(list); } } StringBuilder sb = new StringBuilder(); sb.AppendLine("alter table " + table_name); string temp = " primary key "; if (type == "P") { temp = " primary key "; } else if (type == "U") { temp = " unique "; } else if (type == "R") { temp = " foreign key "; } else if (type == "C") { temp = " check "; } if (type == "C") { sb.Append(" add constraint " + constraint_name + temp + "(" + search_condition + ")"); } else { sb.Append(" add constraint " + constraint_name + temp + "(" + string.Join(",", _column_names) + ")"); } if (type == "R") { sb.AppendLine(); sb.Append(" references " + oracleConstraintClass.table_name + " (" + string.Join(",", oracleConstraintClass.Column_names) + ") on delete " + delete_rule); } else if (type == "P" && !string.IsNullOrWhiteSpace(indexName) && oracleIndexClass != null) { sb.AppendLine(); sb.AppendLine(" using index "); sb.Append(oracleIndexClass.GetNoTopLineOracleSql(tableSpace)); } CreateSqlObject obj = new CreateSqlObject(sb.ToString(), "创建表" + table_name + "约束" + constraint_name); list.Add(obj); return(list); }
public List<CreateSqlObject> GetCreateOracleSql(string tableSpace = null) { CreateSqlObject sqlobj = new CreateSqlObject(); sqlobj.comment = "创建触发器"; string sql = "CREATE OR REPLACE TRIGGER " + trigger_name + "\r\n"; string[] dds = Convert.ToString(description).Split('\n'); for (int i = 1; i < dds.Length; i++) { sql += dds[i].TrimEnd('\r', '\n')+"\r\n"; } //\"SAFECITY\".TR_JFJK_TOTALHOUSE_SYNC_AFTER\nAFTER INSERT OR DELETE OR UPDATE on YW_JFJK_TOTALHOUSE\nfor each row\n string body = Convert.ToString(trigger_body); if (body.IndexOf("\r\n") < 0) { body = body.Replace("\n", "\r\n"); } sql += body; sqlobj.sql = sql; return new List<CreateSqlObject>() { sqlobj }; }
public List <CreateSqlObject> GetCreateOracleSql(string tableSpace = null) { DoLoadCols(); List <CreateSqlObject> list = new List <CreateSqlObject>(); if (string.IsNullOrWhiteSpace(GetObjectString(table_name))) { return(list); } string tbsp = string.IsNullOrWhiteSpace(tableSpace) ? Convert.ToString(tablespace_name) : tableSpace; StringBuilder sb = new StringBuilder(); CreateSqlObject table = new CreateSqlObject(); table.comment = "创建表" + table_name; sb.AppendLine("create table " + table_name); sb.AppendLine("("); for (int i = 0; i < columns.Count; i++) { OracleTableColClass item = (OracleTableColClass)columns[i]; string dataType = Convert.ToString(item.data_type); string columnName = Convert.ToString(item.column_name); //避免Oracle关键字作为列名 if (OracleReserveKeys.Contains(columnName.ToUpper())) { columnName = "\"" + columnName + "\""; } string temp = " " + columnName + " "; if (dataType != "DATE" && dataType != "CLOB" && dataType != "NCLOB" && dataType != "BLOB" && dataType != "BFILE" && dataType != "ROWID" && dataType != "BINARY_DOUBLE" && dataType != "BINARY_FLOAT" && !dataType.StartsWith("INTERVAL DAY") && !dataType.StartsWith("INTERVAL YEAR") && !dataType.StartsWith("TIMESTAMP")) { string data_precision = Convert.ToString(item.data_precision); string data_scale = Convert.ToString(item.data_scale); string data_length = Convert.ToString(item.data_length); int precision = -1, scale = -1, length = -1; int.TryParse(data_precision, out precision); int.TryParse(data_scale, out scale); int.TryParse(data_length, out length); string tnum = ""; if (precision > 0) { tnum += precision; if (scale > 0) { tnum += "," + scale; } } else if (length > 0) { if (dataType == "NUMBER") { dataType = "INTEGER"; } else if (dataType == "NVARCHAR2") { tnum += (length / 2); } else { tnum += length; } } if (tnum != "") { tnum = "(" + tnum + ")"; } temp += dataType + tnum; } else { temp += dataType; } if (!string.IsNullOrWhiteSpace(GetObjectString(item.data_default))) { temp += " default " + item.data_default; } if (Convert.ToString(item.nullable) == "N") { temp += " not null"; } if (i != columns.Count - 1) { temp += ","; } sb.AppendLine(temp); } sb.AppendLine(")"); sb.AppendLine("tablespace " + tbsp); if (!string.IsNullOrWhiteSpace(GetObjectString(pct_free))) { sb.AppendLine(" pctfree " + pct_free); } if (!string.IsNullOrWhiteSpace(GetObjectString(ini_trans))) { sb.AppendLine(" initrans " + ini_trans); } if (!string.IsNullOrWhiteSpace(GetObjectString(max_trans))) { sb.AppendLine(" maxtrans " + max_trans); } sb.AppendLine(" storage"); sb.AppendLine(" ("); if (!string.IsNullOrWhiteSpace(GetObjectString(initial_extent))) { sb.AppendLine(" initial " + initial_extent); } if (!string.IsNullOrWhiteSpace(GetObjectString(min_extents))) { sb.AppendLine(" minextents " + min_extents); } int maxE = 0; int.TryParse(Convert.ToString(max_extents), out maxE); if (maxE == 0 || maxE == 2147483645) { sb.AppendLine(" maxextents unlimited"); } else { sb.AppendLine(" maxextents " + max_extents); } sb.AppendLine(" )"); table.sql = sb.ToString(); list.Add(table); sb.Clear(); if (!string.IsNullOrWhiteSpace(GetObjectString(comments))) { CreateSqlObject comontable = new CreateSqlObject(null, "创建表" + table_name + "的描述"); sb.AppendLine("comment on table " + table_name + " is '" + comments + "'"); comontable.sql = sb.ToString(); list.Add(comontable); } foreach (OracleTableColClass item in columns) { if (string.IsNullOrWhiteSpace(GetObjectString(item.comments))) { continue; } sb.Clear(); CreateSqlObject comonclum = new CreateSqlObject(null, "添加表" + table_name + ".列" + item.column_name + "的描述"); string columnName = Convert.ToString(item.column_name); //避免Oracle关键字作为列名 if (OracleReserveKeys.Contains(columnName.ToUpper())) { columnName = "\"" + columnName + "\""; } sb.AppendLine("comment on column " + table_name + "." + columnName + " is '" + item.comments + "'"); comonclum.sql = sb.ToString(); list.Add(comonclum); } return(list); }
/* alter table PT_CAMERA_INFO add constraint PT_CAMERA_INFO_KEY primary key (ID); alter table PT_CAMERA_INFO add constraint CAMERA_NO_UNIQUE unique (CAMERA_NO); alter table PT_CAMERA_INFO add constraint PT_CAMERA_INFO_FOR foreign key (ORG_ID) references PT_ORG_INFO (ID) on delete set null; * */ public List<CreateSqlObject> GetCreateOracleSql(string tableSpace = null) { DoLoadCols(); DoLoadIndex(); List<CreateSqlObject> list = new List<CreateSqlObject>(); if (_column_names.Count==0) { return list; } string type = Convert.ToString(constraint_type); string indexName = Convert.ToString(index_name); if (type == "R")//不是外键 { if (oracleConstraintClass == null) { return list; } } StringBuilder sb = new StringBuilder(); sb.AppendLine("alter table " + table_name); string temp = " primary key "; if (type == "P") { temp = " primary key "; } else if (type=="U") { temp = " unique "; } else if (type=="R") { temp = " foreign key "; } else if (type=="C") { temp = " check "; } if (type == "C") { sb.Append(" add constraint " + constraint_name + temp + "(" + search_condition + ")"); } else { sb.Append(" add constraint " + constraint_name + temp + "(" + string.Join(",", _column_names) + ")"); } if (type=="R") { sb.AppendLine(); sb.Append(" references " + oracleConstraintClass.table_name + " (" + string.Join(",", oracleConstraintClass.Column_names) + ") on delete " + delete_rule); } else if (type == "P" && !string.IsNullOrWhiteSpace(indexName) && oracleIndexClass != null) { sb.AppendLine(); sb.AppendLine(" using index "); sb.Append(oracleIndexClass.GetNoTopLineOracleSql(tableSpace)); } CreateSqlObject obj = new CreateSqlObject(sb.ToString(), "创建表" + table_name + "约束" + constraint_name); list.Add(obj); return list; }
/* -- Create sequence create sequence S_YW_EQUIP_BAS_UNIT minvalue 1 maxvalue 999999999999999999999999999 start with 421 increment by 1 cache 20 cycle order; */ public List<CreateSqlObject> GetCreateOracleSql(string tableSpace = null) { StringBuilder sb = new StringBuilder(); //sb.AppendLine("-- Create sequence"); sb.AppendLine("create sequence " + sequence_name); sb.AppendLine("minvalue " + min_value); sb.AppendLine("maxvalue " + max_value); sb.AppendLine("start with " + last_number); sb.AppendLine("increment by " + increment_by); int cache=0; int.TryParse(Convert.ToString(cache_size), out cache); if (cache>0) { sb.AppendLine("cache " + cache_size); } if (Convert.ToString(cycle_flag) == "Y") { sb.AppendLine("cycle"); } if (Convert.ToString(order_flag) == "Y") { sb.AppendLine("order"); } string str = sb.ToString().TrimEnd('\r', '\n'); // str += ";"; CreateSqlObject obj = new CreateSqlObject(str, "创建序列" + sequence_name); return new List<CreateSqlObject>() { obj }; }
public List<CreateSqlObject> GetCreateOracleSql(string tableSpace = null) { DoLoadCols(); List<CreateSqlObject> list = new List<CreateSqlObject>(); if (string.IsNullOrWhiteSpace(GetObjectString(table_name))) { return list; } string tbsp = string.IsNullOrWhiteSpace(tableSpace) ? Convert.ToString(tablespace_name) : tableSpace; StringBuilder sb = new StringBuilder(); CreateSqlObject table = new CreateSqlObject(); table.comment = "创建表" + table_name; sb.AppendLine("create table " + table_name); sb.AppendLine("("); for (int i = 0; i < columns.Count; i++) { OracleTableColClass item = (OracleTableColClass)columns[i]; string dataType = Convert.ToString(item.data_type); string columnName = Convert.ToString(item.column_name); //避免Oracle关键字作为列名 if (OracleReserveKeys.Contains(columnName.ToUpper())) { columnName = "\"" +columnName+ "\""; } string temp = " " + columnName + " "; if (dataType != "DATE" && dataType != "CLOB" && dataType != "NCLOB" && dataType != "BLOB" && dataType != "BFILE" && dataType != "ROWID" && dataType != "BINARY_DOUBLE"&& dataType != "BINARY_FLOAT"&& !dataType.StartsWith("INTERVAL DAY") && !dataType.StartsWith("INTERVAL YEAR") && !dataType.StartsWith("TIMESTAMP")) { string data_precision = Convert.ToString(item.data_precision); string data_scale = Convert.ToString(item.data_scale); string data_length = Convert.ToString(item.data_length); int precision = -1, scale = -1, length = -1; int.TryParse(data_precision, out precision); int.TryParse(data_scale, out scale); int.TryParse(data_length, out length); string tnum = ""; if (precision>0) { tnum += precision; if (scale>0) { tnum += "," + scale; } } else if (length > 0) { if (dataType=="NUMBER") { dataType = "INTEGER"; } else if (dataType == "NVARCHAR2") { tnum += (length / 2); } else { tnum += length; } } if (tnum!="") { tnum = "(" + tnum + ")"; } temp += dataType + tnum; } else { temp += dataType; } if (!string.IsNullOrWhiteSpace(GetObjectString(item.data_default))) { temp += " default " + item.data_default; } if (Convert.ToString(item.nullable) == "N") { temp += " not null"; } if (i != columns.Count - 1) { temp += ","; } sb.AppendLine(temp); } sb.AppendLine(")"); sb.AppendLine("tablespace " + tbsp); if (!string.IsNullOrWhiteSpace(GetObjectString(pct_free))) { sb.AppendLine(" pctfree " + pct_free); } if (!string.IsNullOrWhiteSpace(GetObjectString(ini_trans))) { sb.AppendLine(" initrans " + ini_trans); } if (!string.IsNullOrWhiteSpace(GetObjectString(max_trans))) { sb.AppendLine(" maxtrans " + max_trans); } sb.AppendLine(" storage"); sb.AppendLine(" ("); if (!string.IsNullOrWhiteSpace(GetObjectString(initial_extent))) { sb.AppendLine(" initial " + initial_extent); } if (!string.IsNullOrWhiteSpace(GetObjectString(min_extents))) { sb.AppendLine(" minextents " + min_extents); } int maxE = 0; int.TryParse(Convert.ToString(max_extents), out maxE); if (maxE == 0 || maxE == 2147483645) { sb.AppendLine(" maxextents unlimited"); } else { sb.AppendLine(" maxextents " + max_extents); } sb.AppendLine(" )"); table.sql = sb.ToString(); list.Add(table); sb.Clear(); if (!string.IsNullOrWhiteSpace(GetObjectString(comments))) { CreateSqlObject comontable = new CreateSqlObject(null, "创建表" + table_name + "的描述"); sb.AppendLine("comment on table " + table_name + " is '" + comments + "'"); comontable.sql = sb.ToString(); list.Add(comontable); } foreach (OracleTableColClass item in columns) { if (string.IsNullOrWhiteSpace(GetObjectString(item.comments))) { continue; } sb.Clear(); CreateSqlObject comonclum = new CreateSqlObject(null, "添加表" + table_name + ".列" + item.column_name + "的描述"); string columnName = Convert.ToString(item.column_name); //避免Oracle关键字作为列名 if (OracleReserveKeys.Contains(columnName.ToUpper())) { columnName = "\"" + columnName + "\""; } sb.AppendLine("comment on column " + table_name + "." + columnName + " is '" + item.comments + "'"); comonclum.sql = sb.ToString(); list.Add(comonclum); } return list; }
/* create index IS_DEL on PT_CAMERA_INFO (IS_DEL) tablespace PAHF pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );*/ public List<CreateSqlObject> GetCreateOracleSql(string tableSpace = null) { DoLoadCulumns(); StringBuilder sb = new StringBuilder(); string cols = string.Join(",", _column_names); //uniqueness;//NONUNIQUE,UNIQUE,BITMAP string uniq = Convert.ToString(uniqueness); if (uniq == "UNIQUE") { sb.AppendLine("create unique index " + index_name + " on " + table_name + " (" + cols + ")"); } else if (uniq == "BITMAP") { sb.AppendLine("create bitmap index " + index_name + " on " + table_name + " (" + cols + ")"); } else sb.AppendLine("create index " + index_name + " on " + table_name + " (" + cols + ")"); sb.Append(GetNoTopLineOracleSql(tableSpace)); CreateSqlObject obj = new CreateSqlObject(sb.ToString(), "创建表" + table_name + "索引" + index_name); return new List<CreateSqlObject> { obj }; }