/// <summary>
/// 根据参数生成修改SQL语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition">条件</param>
/// <param name="updateFiles">参数</param>
/// <returns></returns>
public override string UpdateSqlCreate <T>(string condition = "", List <ColumInfo> updateFiles = null)
{
Predicate <ColumInfo> predicate = null;
string empty = string.Empty;
TableInfo dataFields = ModelHepper.GetDataFields <T>(default(T));
if ((condition ?? "").Trim() == "")
{
List <ColumInfo> arg_59_0 = dataFields.Colums;
if (predicate == null)
{
predicate = ((ColumInfo x) => x.ColIsKey);
}
ColumInfo columInfo = arg_59_0.Find(predicate);
condition = string.Format(" {0}=@{0} ", columInfo.ColName);
}
if (updateFiles == null || updateFiles.Count == 0)
{
updateFiles = dataFields.Colums.FindAll((ColumInfo x) => !x.ColIsKey && !x.ColIsIgnore && x.ColCanWrite);
}
string arg = string.Join(",", (from a in updateFiles
select a.ColName + " = @" + a.ColName).ToList <string>());
string format = " update {0} set {1} where {2} ";
return(string.Format(format, dataFields.TableName, arg, condition));
}
public void GetDdlTest1()
{
var dbVm = new DbViewModel();
var table = new TableViewModel("TEST");
dbVm.Tables.Add(table);
var col1 = new ColumInfo("COL1", new FieldType(8, null, null, null, null, 4), new ColumConstraintsInfo(ConstraintsKind.Primary), "RDB$1", false, true, "");
table.Colums.Add(new ColumViewMoodel(col1));
var col2 = new ColumInfo("COL2", new FieldType(37, null, 100, null, null, 400), new ColumConstraintsInfo(ConstraintsKind.None), "RDB$2", false, true, "");
table.Colums.Add(new ColumViewMoodel(col2));
var col3 = new ColumInfo("COL3", new FieldType(35, null, null, null, null, 8), new ColumConstraintsInfo(ConstraintsKind.None), "RDB$3", false, true, "");
table.Colums.Add(new ColumViewMoodel(col3));
var col4 = new ColumInfo("COL4", new FieldType(261, null, null, null, null, 8), new ColumConstraintsInfo(ConstraintsKind.None), "RDB$4", true, true, "");
table.Colums.Add(new ColumViewMoodel(col4));
var idx = new IndexInfo();
idx.Name = "RDB$PRIMARYKEY1";
idx.TableName = table.TableName;
idx.Kind = ConstraintsKind.Primary;
idx.FieldNames.Add(col1.ColumName);
var idxVm = new IndexViewModel(idx);
dbVm.Indexes.Add(idxVm);
table.Indexs.Add(idxVm);
table.GetDdl(dbVm).Is(
"CREATE TABLE TEST (" + Environment.NewLine +
" COL1 INTEGER NOT NULL," + Environment.NewLine +
" COL2 VARCHAR(100) NOT NULL," + Environment.NewLine +
" COL3 TIMESTAMP NOT NULL," + Environment.NewLine +
" COL4 BLOB," + Environment.NewLine +
" PRIMARY KEY (COL1)" + Environment.NewLine +
")");
}
private TableInfo getTableInfo(string tableName)
{
TableInfo tableInfo = new TableInfo();
tableInfo.TableName = tableName;
try
{
SqlCommand Cmd = this.getSQLConnect();
Cmd.CommandText = "SELECT COLUMN_NAME,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLE_NAME ";
Cmd.Parameters.Add("TABLE_NAME", SqlDbType.NVarChar).Value = tableName;
DataTable objTable = findAll(Cmd);
SQLClose();
foreach (DataRow objRow in objTable.Rows)
{
ColumInfo objColum = new ColumInfo();
objColum.ColumName = objRow["COLUMN_NAME"].ToString();
objColum.ColumType = objRow["DATA_TYPE"].ToString().ToLower();
objColum.ColumDefault = objRow["COLUMN_DEFAULT"].ToString();
try
{
objColum.ColumLeng = (int)objRow["CHARACTER_MAXIMUM_LENGTH"];
}
catch { }
tableInfo.ListColums.Add(objColum.ColumName, objColum);
}
Cmd = this.getSQLConnect();
Cmd.CommandText = "SELECT TOP 1 COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = @TABLE_NAME AND CONSTRAINT_NAME LIKE 'PK%' ";
Cmd.Parameters.Add("TABLE_NAME", SqlDbType.NVarChar).Value = tableName;
try
{
tableInfo.PrimaryKey = Cmd.ExecuteScalar().ToString();
((ColumInfo)tableInfo.ListColums[tableInfo.PrimaryKey]).isPrimaryKy = true;
}
catch { }
SQLClose();
}
catch (Exception ex)
{
throw ex;
}
return(tableInfo);
}
public static int Insert(ColumInfo item)
{
string sql = string.Format("INSERT INTO MDS_IMP_COLUM_INFO(FID,PM_TASK_TIME_ID,TABLE_NAME,COL_NAME,REMARK,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_IP,VERSION) ");
sql += string.Format("VALUES('{0}','{1}','{2}','{3}','{4}','{5}',to_date('{6}','yyyy/mm/dd hh24:mi:ss'),'{7}',to_date('{8}','yyyy/mm/dd hh24:mi:ss'),'{9}',{10})",
item.FID, item.TaskTimeID, item.TableName, item.ColName, item.Remark, item.CreatedBy,
item.CreationDate, item.LastUpdatedBy, item.LastUpdateDate, item.LastUpdateIp, item.Version);
return(OracleHelper.Excuse(sql));
}
public void GetDdlTest2()
{
var dbVm = new DbViewModel();
var table = new TableViewModel("TEST");
dbVm.Tables.Add(table);
var col1 = new ColumInfo("COL1", new FieldType(8, null, null, null, null), new ConstraintsInfo(ConstraintsKind.Primary), "RDB$1", false);
table.Colums.Add(new ColumViewMoodel(col1));
var col2 = new ColumInfo("COL2", new FieldType(37, null, 100, null, null), new ConstraintsInfo(ConstraintsKind.Primary), "RDB$2", false);
table.Colums.Add(new ColumViewMoodel(col2));
var col3 = new ColumInfo("COL3", new FieldType(35, null, null, null, null), new ConstraintsInfo(ConstraintsKind.None), "RDB$3", false);
table.Colums.Add(new ColumViewMoodel(col3));
var col4 = new ColumInfo("COL4", new FieldType(261, null, null, null, null), new ConstraintsInfo(ConstraintsKind.None), "RDB$4", true);
table.Colums.Add(new ColumViewMoodel(col4));
var col5 = new ColumInfo("COL5", new FieldType(7, 1, null, 4, 0), new ConstraintsInfo(ConstraintsKind.None), "RDB$4", true);
table.Colums.Add(new ColumViewMoodel(col5));
var col6 = new ColumInfo("COL6", new FieldType(8, 2, null, 4, -2), new ConstraintsInfo(ConstraintsKind.None), "RDB$4", true);
table.Colums.Add(new ColumViewMoodel(col6));
var col7 = new ColumInfo("COL7", new FieldType(16, 2, null, 10, -4), new ConstraintsInfo(ConstraintsKind.None), "RDB$4", true);
table.Colums.Add(new ColumViewMoodel(col7));
var idx = new IndexInfo();
idx.Name = "COMPLEXKEY";
idx.TableName = table.TableName;
idx.Kind = ConstraintsKind.Primary;
idx.FieldNames.Add(col1.ColumName);
idx.FieldNames.Add(col2.ColumName);
var idxVm = new IndexViewModel(idx);
dbVm.Indexes.Add(idxVm);
table.Indexs.Add(idxVm);
table.GetDdl(dbVm).Is(
"CREATE TABLE TEST (" + Environment.NewLine +
" COL1 INTEGER NOT NULL," + Environment.NewLine +
" COL2 VARCHAR(100) NOT NULL," + Environment.NewLine +
" COL3 TIMESTAMP NOT NULL," + Environment.NewLine +
" COL4 BLOB," + Environment.NewLine +
" COL5 NUMERIC(4)," + Environment.NewLine +
" COL6 DECIMAL(4,2)," + Environment.NewLine +
" COL7 DECIMAL(10,4)," + Environment.NewLine +
" CONSTRAINT COMPLEXKEY PRIMARY KEY (COL1, COL2)" + Environment.NewLine +
")");
}
public void GetDdlTest()
{
var table = new TableViewModel(new TableInfo("test"));
var col1 = new ColumInfo("col1", 8, null, FAManagementStudio.Common.ConstraintsKind.Primary);
table.Colums.Add(new ColumViewMoodel(col1));
var col2 = new ColumInfo("col2", 37, 100, FAManagementStudio.Common.ConstraintsKind.NotNull);
table.Colums.Add(new ColumViewMoodel(col2));
var col3 = new ColumInfo("col3", 35, null, FAManagementStudio.Common.ConstraintsKind.NotNull);
table.Colums.Add(new ColumViewMoodel(col3));
var col4 = new ColumInfo("col4", 261, null, FAManagementStudio.Common.ConstraintsKind.None);
table.Colums.Add(new ColumViewMoodel(col4));
table.GetDdl().Is("CREATE TABLE TEST (COL1 INTEGER PRIMARY KEY, COL2 VARCHAR(100) NOT NULL, COL3 TIMESTAMP NOT NULL, COL4 BLOB)");
}
public void GetDdlTest2()
{
var dbVm = new DbViewModel();
var table = new TableViewModel("TEST");
dbVm.Tables.Add(table);
var col1 = new ColumInfo("COL1", new FieldType(8, null, null, null, null, 4), new ColumConstraintsInfo(ConstraintsKind.Primary), "RDB$1", false, true, "");
table.Colums.Add(new ColumViewMoodel(col1));
var col2 = new ColumInfo("COL2", new FieldType(37, null, 100, null, null, 400), new ColumConstraintsInfo(ConstraintsKind.Primary), "RDB$2", false, true, "");
table.Colums.Add(new ColumViewMoodel(col2));
var col3 = new ColumInfo("COL3", new FieldType(35, null, null, null, null, 8), new ColumConstraintsInfo(ConstraintsKind.None), "RDB$3", false, true, "");
table.Colums.Add(new ColumViewMoodel(col3));
var col4 = new ColumInfo("COL4", new FieldType(261, null, null, null, null, 8), new ColumConstraintsInfo(ConstraintsKind.None), "RDB$4", true, true, "");
table.Colums.Add(new ColumViewMoodel(col4));
var col5 = new ColumInfo("COL5", new FieldType(7, 1, null, 4, 0, 4), new ColumConstraintsInfo(ConstraintsKind.None), "RDB$4", true, true, "");
table.Colums.Add(new ColumViewMoodel(col5));
var col6 = new ColumInfo("COL6", new FieldType(8, 2, null, 4, -2, 4), new ColumConstraintsInfo(ConstraintsKind.None), "RDB$4", true, true, "");
table.Colums.Add(new ColumViewMoodel(col6));
var col7 = new ColumInfo("COL7", new FieldType(16, 2, null, 10, -4, 8), new ColumConstraintsInfo(ConstraintsKind.None), "RDB$4", true, true, "");
table.Colums.Add(new ColumViewMoodel(col7));
var idx = new IndexInfo();
idx.Name = "COMPLEXKEY";
idx.TableName = table.TableName;
idx.Kind = ConstraintsKind.Primary;
idx.FieldNames.Add(col1.ColumName);
idx.FieldNames.Add(col2.ColumName);
var idxVm = new IndexViewModel(idx);
dbVm.Indexes.Add(idxVm);
table.Indexs.Add(idxVm);
table.GetDdl(dbVm).Is(
"CREATE TABLE TEST (" + Environment.NewLine +
" COL1 INTEGER NOT NULL," + Environment.NewLine +
" COL2 VARCHAR(100) NOT NULL," + Environment.NewLine +
" COL3 TIMESTAMP NOT NULL," + Environment.NewLine +
" COL4 BLOB," + Environment.NewLine +
" COL5 NUMERIC(4)," + Environment.NewLine +
" COL6 DECIMAL(4,2)," + Environment.NewLine +
" COL7 DECIMAL(10,4)," + Environment.NewLine +
" CONSTRAINT COMPLEXKEY PRIMARY KEY (COL1, COL2)" + Environment.NewLine +
")");
}
/// <summary>
/// 分页查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition">条件</param>
/// <param name="orderby">排序</param>
/// <param name="startIndex">开始Index</param>
/// <param name="endIndex">结束Index</param>
/// <returns></returns>
public override string SelectByPageSqlCreate <T>(string condition = "", string orderby = "", int startIndex = 0, int endIndex = 0)
{
Func <string, string> func = null;
Predicate <ColumInfo> predicate = null;
string empty = string.Empty;
TableInfo dataFields = ModelHepper.GetDataFields <T>(default(T));
if ((condition ?? "").Trim() != "")
{
condition = " where " + condition;
}
if ((orderby ?? "").Trim() != "")
{
string[] array = orderby.Split(new char[]
{
','
});
IEnumerable <string> arg_9D_0 = array;
if (func == null)
{
func = ((string a) => "T." + a.Trim());
}
List <string> values = arg_9D_0.Select(func).ToList <string>();
orderby = " order by " + string.Join(",", values);
}
else
{
List <ColumInfo> arg_DF_0 = dataFields.Colums;
if (predicate == null)
{
predicate = ((ColumInfo x) => x.ColIsKey);
}
ColumInfo columInfo = arg_DF_0.Find(predicate);
orderby = string.Format(" order by T.{0} desc ", columInfo.ColName.Trim());
}
string text = " SELECT * FROM ( \r\n SELECT ROW_NUMBER() OVER ({1}) AS Row, T.* \r\n from {0} T \r\n {2} \r\n ) TT ";
if (startIndex * endIndex > 0)
{
text += string.Format(" WHERE TT.Row between {0} and {1} ", startIndex, endIndex);
}
return(string.Format(text, dataFields.TableName, orderby, condition));
}
public void GetDdlTest3()
{
var dbVm = new DbViewModel();
var table = new TableViewModel("TEST");
dbVm.Tables.Add(table);
var col1 = new ColumInfo("COL1", new FieldType(8, null, null, null, null), new ConstraintsInfo(ConstraintsKind.Primary), "SARARY", false);
table.Colums.Add(new ColumViewMoodel(col1));
var col2 = new ColumInfo("COL2", new FieldType(37, null, 100, null, null), new ConstraintsInfo(ConstraintsKind.Primary), "NAME", false);
table.Colums.Add(new ColumViewMoodel(col2));
var col3 = new ColumInfo("COL3", new FieldType(35, null, null, null, null), new ConstraintsInfo(ConstraintsKind.None), "RDB$3", false);
table.Colums.Add(new ColumViewMoodel(col3));
var col4 = new ColumInfo("COL4", new FieldType(261, null, null, null, null), new ConstraintsInfo(ConstraintsKind.None), "RDB$4", true);
table.Colums.Add(new ColumViewMoodel(col4));
var idx = new IndexInfo();
idx.Name = "COMPLEXKEY";
idx.TableName = table.TableName;
idx.Kind = FAManagementStudio.Common.ConstraintsKind.Primary;
idx.FieldNames.Add(col1.ColumName);
idx.FieldNames.Add(col2.ColumName);
var idxVm = new IndexViewModel(idx);
dbVm.Indexes.Add(idxVm);
table.Indexs.Add(idxVm);
table.GetDdl(dbVm).Is(
"CREATE DOMAIN SARARY AS INTEGER;" + Environment.NewLine +
"CREATE DOMAIN NAME AS VARCHAR(100);" + Environment.NewLine +
"CREATE TABLE TEST (" + Environment.NewLine +
" COL1 SARARY NOT NULL," + Environment.NewLine +
" COL2 NAME NOT NULL," + Environment.NewLine +
" COL3 TIMESTAMP NOT NULL," + Environment.NewLine +
" COL4 BLOB," + Environment.NewLine +
" CONSTRAINT COMPLEXKEY PRIMARY KEY (COL1, COL2)" + Environment.NewLine +
")");
}
public static void Insert(String tableName, string taskID, string userid, List <Structure> structures)
{
foreach (var item in structures)
{
ColumInfo info = new ColumInfo();
info.FID = Guid.NewGuid().ToString().Replace("-", "");
info.TaskTimeID = taskID;
info.TableName = tableName;
info.ColName = item.ColumnName;
info.Remark = item.Comments;
info.CreatedBy = userid;
info.CreationDate = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
info.LastUpdateDate = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
info.LastUpdatedBy = userid;
info.LastUpdateIp = "";
Insert(info);
}
}
public void GetDdlTest4()
{
var dbVm = new DbViewModel();
var table = new TableViewViewModel("SNOW_LINE", "SELECT CITY, STATE, ALTITUDE FROM CITIES WHERE ALTITUDE > 5000");
dbVm.Tables.Add(table);
var col1 = new ColumInfo("CITY", new FieldType(8, null, null, null, null), new ConstraintsInfo(ConstraintsKind.Primary), "SARARY", false);
table.Colums.Add(new ColumViewMoodel(col1));
var col2 = new ColumInfo("STATE", new FieldType(37, null, 100, null, null), new ConstraintsInfo(ConstraintsKind.Primary), "NAME", false);
table.Colums.Add(new ColumViewMoodel(col2));
var col3 = new ColumInfo("SNOW_ALTITUDE", new FieldType(35, null, null, null, null), new ConstraintsInfo(ConstraintsKind.None), "RDB$3", false);
table.Colums.Add(new ColumViewMoodel(col3));
table.GetDdl(dbVm).Is(
"CREATE VIEW SNOW_LINE (CITY, STATE, SNOW_ALTITUDE) AS" + Environment.NewLine +
"SELECT CITY, STATE, ALTITUDE FROM CITIES WHERE ALTITUDE > 5000");
}
/// <summary>
/// 根据参数生成删除SQL语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition">条件</param>
/// <returns></returns>
public override string DeleteSqlCreate <T>(string condition = "")
{
Predicate <ColumInfo> predicate = null;
string empty = string.Empty;
TableInfo dataFields = ModelHepper.GetDataFields <T>(default(T));
if ((condition ?? "").Trim() == "")
{
List <ColumInfo> arg_59_0 = dataFields.Colums;
if (predicate == null)
{
predicate = ((ColumInfo x) => x.ColIsKey);
}
ColumInfo columInfo = arg_59_0.Find(predicate);
condition = string.Format(" {0}=@{0} ", columInfo.ColName);
}
//string format = " delete from {0} where {1} ";
string format = " update {0} set Deleted = 1 where {1} ";
return(string.Format(format, dataFields.TableName, condition));
}
public void GetDdlTest1()
{
var dbVm = new DbViewModel();
var table = new TableViewModel("TEST");
dbVm.Tables.Add(table);
var col1 = new ColumInfo("COL1", new FieldType(8, null, null, null, null), new ConstraintsInfo(ConstraintsKind.Primary), "RDB$1", false);
table.Colums.Add(new ColumViewMoodel(col1));
var col2 = new ColumInfo("COL2", new FieldType(37, null, 100, null, null), new ConstraintsInfo(ConstraintsKind.None), "RDB$2", false);
table.Colums.Add(new ColumViewMoodel(col2));
var col3 = new ColumInfo("COL3", new FieldType(35, null, null, null, null), new ConstraintsInfo(ConstraintsKind.None), "RDB$3", false);
table.Colums.Add(new ColumViewMoodel(col3));
var col4 = new ColumInfo("COL4", new FieldType(261, null, null, null, null), new ConstraintsInfo(ConstraintsKind.None), "RDB$4", true);
table.Colums.Add(new ColumViewMoodel(col4));
var idx = new IndexInfo();
idx.Name = "RDB$PRIMARYKEY1";
idx.TableName = table.TableName;
idx.Kind = ConstraintsKind.Primary;
idx.FieldNames.Add(col1.ColumName);
var idxVm = new IndexViewModel(idx);
dbVm.Indexes.Add(idxVm);
table.Indexs.Add(idxVm);
table.GetDdl(dbVm).Is(
"CREATE TABLE TEST (" + Environment.NewLine +
" COL1 INTEGER NOT NULL," + Environment.NewLine +
" COL2 VARCHAR(100) NOT NULL," + Environment.NewLine +
" COL3 TIMESTAMP NOT NULL," + Environment.NewLine +
" COL4 BLOB," + Environment.NewLine +
" PRIMARY KEY (COL1)" + Environment.NewLine +
")");
}
public void GetDdlTest_ForignKey1()
{
var dbVm = new DbViewModel();
var table = new TableViewModel("TEST");
dbVm.Tables.Add(table);
var col1 = new ColumInfo("COL1", new FieldType(8, null, null, null, null, 4), new ColumConstraintsInfo(ConstraintsKind.Primary), "RDB$1", false, true, "");
table.Colums.Add(new ColumViewMoodel(col1));
var col2 = new ColumInfo("COL2", new FieldType(37, null, 100, null, null, 400), new ColumConstraintsInfo(ConstraintsKind.Foreign), "RDB$2", false, true, "");
table.Colums.Add(new ColumViewMoodel(col2));
var col3 = new ColumInfo("COL3", new FieldType(35, null, null, null, null, 8), new ColumConstraintsInfo(ConstraintsKind.Foreign), "RDB$3", false, true, "");
table.Colums.Add(new ColumViewMoodel(col3));
var idx = new IndexInfo();
idx.Name = "RDB$PRIMARYKEY1";
idx.TableName = table.TableName;
idx.Kind = ConstraintsKind.Primary;
idx.FieldNames.Add(col1.ColumName);
var idxVm = new IndexViewModel(idx);
dbVm.Indexes.Add(idxVm);
table.Indexs.Add(idxVm);
var fpIdx1 = new IndexInfo();
fpIdx1.Name = "RDB$PRIMARYKEY2";
fpIdx1.TableName = "MASTER";
fpIdx1.Kind = ConstraintsKind.Primary;
fpIdx1.FieldNames.Add("M_COL1");
var fpIdxVm1 = new IndexViewModel(fpIdx1);
dbVm.Indexes.Add(fpIdxVm1);
var idx1 = new IndexInfo();
idx1.Name = "RDB$FOREIGNKEY1";
idx1.TableName = table.TableName;
idx1.Kind = ConstraintsKind.Foreign;
idx1.ForigenKeyName = "RDB$PRIMARYKEY2";
idx1.FieldNames.Add(col2.ColumName);
var idxVm1 = new IndexViewModel(idx1);
dbVm.Indexes.Add(idxVm1);
table.Indexs.Add(idxVm1);
var fpIdx2 = new IndexInfo();
fpIdx2.Name = "RDB$PRIMARYKEY3";
fpIdx2.TableName = "MASTER";
fpIdx2.Kind = ConstraintsKind.Primary;
fpIdx2.FieldNames.Add("M_COL2");
var fpIdxVm2 = new IndexViewModel(fpIdx2);
dbVm.Indexes.Add(fpIdxVm2);
var idx2 = new IndexInfo();
idx2.Name = "C_FOREIGNKEY";
idx2.TableName = table.TableName;
idx2.Kind = ConstraintsKind.Foreign;
idx2.ForigenKeyName = "RDB$PRIMARYKEY3";
idx2.DeleteRule = "SET DEFAULT";
idx2.UpdateRule = "CASCADE";
idx2.FieldNames.Add(col3.ColumName);
var idxVm2 = new IndexViewModel(idx2);
dbVm.Indexes.Add(idxVm2);
table.Indexs.Add(idxVm2);
table.GetDdl(dbVm).Is(
"CREATE TABLE TEST (" + Environment.NewLine +
" COL1 INTEGER NOT NULL," + Environment.NewLine +
" COL2 VARCHAR(100) NOT NULL," + Environment.NewLine +
" COL3 TIMESTAMP NOT NULL," + Environment.NewLine +
" PRIMARY KEY (COL1)," + Environment.NewLine +
" FOREIGN KEY (COL2) REFERENCES MASTER (M_COL1)," + Environment.NewLine +
" CONSTRAINT C_FOREIGNKEY FOREIGN KEY (COL3) REFERENCES MASTER (M_COL2) ON DELETE SET DEFAULT ON UPDATE CASCADE" + Environment.NewLine +
")");
}
public void GetDdlTest4()
{
var dbVm = new DbViewModel();
var table = new TableViewViewModel("SNOW_LINE", "SELECT CITY, STATE, ALTITUDE FROM CITIES WHERE ALTITUDE > 5000");
dbVm.Tables.Add(table);
var col1 = new ColumInfo("CITY", new FieldType(8, null, null, null, null, 4), new ColumConstraintsInfo(ConstraintsKind.Primary), "SARARY", false, true, "");
table.Colums.Add(new ColumViewMoodel(col1));
var col2 = new ColumInfo("STATE", new FieldType(37, null, 100, null, null, 400), new ColumConstraintsInfo(ConstraintsKind.Primary), "NAME", false, true, "");
table.Colums.Add(new ColumViewMoodel(col2));
var col3 = new ColumInfo("SNOW_ALTITUDE", new FieldType(35, null, null, null, null, 8), new ColumConstraintsInfo(ConstraintsKind.None), "RDB$3", false, true, "");
table.Colums.Add(new ColumViewMoodel(col3));
table.GetDdl(dbVm).Is(
"CREATE VIEW SNOW_LINE (CITY, STATE, SNOW_ALTITUDE) AS" + Environment.NewLine +
"SELECT CITY, STATE, ALTITUDE FROM CITIES WHERE ALTITUDE > 5000");
}
public void GetDdlTest3()
{
var dbVm = new DbViewModel();
var table = new TableViewModel("TEST");
dbVm.Tables.Add(table);
var col1 = new ColumInfo("COL1", new FieldType(8, null, null, null, null, 4), new ColumConstraintsInfo(ConstraintsKind.Primary), "SARARY", false, true, "");
table.Colums.Add(new ColumViewMoodel(col1));
var col2 = new ColumInfo("COL2", new FieldType(37, null, 100, null, null, 400), new ColumConstraintsInfo(ConstraintsKind.Primary), "NAME", false, true, "");
table.Colums.Add(new ColumViewMoodel(col2));
var col3 = new ColumInfo("COL3", new FieldType(35, null, null, null, null, 8), new ColumConstraintsInfo(ConstraintsKind.None), "RDB$3", false, true, "");
table.Colums.Add(new ColumViewMoodel(col3));
var col4 = new ColumInfo("COL4", new FieldType(261, null, null, null, null, 8), new ColumConstraintsInfo(ConstraintsKind.None), "RDB$4", true, true, "");
table.Colums.Add(new ColumViewMoodel(col4));
var col5 = new ColumInfo("COL5", new FieldType(37, null, 100, null, null, 400), new ColumConstraintsInfo(ConstraintsKind.None), "NAME", true, true, "");
table.Colums.Add(new ColumViewMoodel(col5));
var col6 = new ColumInfo("COL6", new FieldType(37, null, 100, null, null, 400), new ColumConstraintsInfo(ConstraintsKind.None), "MEMO", true, true, "DEFAULT 'HOGE'");
table.Colums.Add(new ColumViewMoodel(col6));
var col7 = new ColumInfo("COL7", new FieldType(37, null, 100, null, null, 400), new ColumConstraintsInfo(ConstraintsKind.None), "MEMO2", true, false, "DEFAULT 'HOGE2'");
table.Colums.Add(new ColumViewMoodel(col7));
var idx = new IndexInfo();
idx.Name = "COMPLEXKEY";
idx.TableName = table.TableName;
idx.Kind = FAManagementStudio.Common.ConstraintsKind.Primary;
idx.FieldNames.Add(col1.ColumName);
idx.FieldNames.Add(col2.ColumName);
var idxVm = new IndexViewModel(idx);
dbVm.Indexes.Add(idxVm);
table.Indexs.Add(idxVm);
table.GetDdl(dbVm).Is(
"CREATE DOMAIN SARARY AS INTEGER;" + Environment.NewLine +
"CREATE DOMAIN NAME AS VARCHAR(100);" + Environment.NewLine +
"CREATE DOMAIN MEMO AS VARCHAR(100) DEFAULT 'HOGE';" + Environment.NewLine +
"CREATE DOMAIN MEMO2 AS VARCHAR(100) NOT NULL DEFAULT 'HOGE2';" + Environment.NewLine +
"CREATE TABLE TEST (" + Environment.NewLine +
" COL1 SARARY NOT NULL," + Environment.NewLine +
" COL2 NAME NOT NULL," + Environment.NewLine +
" COL3 TIMESTAMP NOT NULL," + Environment.NewLine +
" COL4 BLOB," + Environment.NewLine +
" COL5 NAME," + Environment.NewLine +
" COL6 MEMO," + Environment.NewLine +
" COL7 MEMO2," + Environment.NewLine +
" CONSTRAINT COMPLEXKEY PRIMARY KEY (COL1, COL2)" + Environment.NewLine +
")");
}
public ColumViewMoodel(ColumInfo inf)
{
_inf = inf;
}
public ColumViewMoodel(ColumInfo inf)
{
_inf = inf;
}
