/*
* -- 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 };
}
