//public static string CreateTable(SysTable table, SysColumn[] columns)
//{
// var pKey = columns.ToList().Find(c => c.primary_key != null);
// return string.Join("\n", new string[]
// {
// $"create table {ToObjectName(table)} (",
// string.Join(",\n",
// columns.Select(column => {
// bool
// isString = column.data_type.Contains("varchar"),
// isNullable = Convert.ToBoolean(column.is_nullable),
// isIdentity = Convert.ToBoolean(column.is_identity);
// string maxLength = column.max_length == "-1"? "max": column.max_length;
// return $"\t[{column.name}] [{column.data_type}]{(isString? $"({maxLength})": "")} {(isIdentity? "identity(1, 1) ": "")}{(isNullable? "": "not")} null{(column.default_constraint_name == null? "": $" constraint [{column.default_constraint_name}] default {column.default_constraint_definition}")}";
// })
// ),
// pKey == null? "": $"\tconstraint [{pKey.primary_key}] primary key {pKey.index_type_desc} ( [{pKey.name}] asc )",
// ")"
// });
//}
public static string CreateTable(SysTable table, List <SysColumn> columns)
{
var pKey = columns.Find(c => c.primary_key != null);
var cmd = new List <string>
{
$"IF (OBJECT_ID('{table.qualified_name}') IS NULL)",
"BEGIN",
$"\tCREATE TABLE {table.qualified_name} (",
string.Join(",\n", columns.Select(column => "\t\t" + column.column_definition)),
};
if (pKey != null)
{
cmd.Add($"\t\tCONSTRAINT [{pKey.primary_key}] PRIMARY KEY {pKey.index_type_desc} ( [{pKey.name}] ASC )");
}
if (table.history_table != null)
{
var rowStart = columns.Find(c => c.generated_always_type == "1").name;
var rowEnd = columns.Find(c => c.generated_always_type == "2").name;
cmd[cmd.Count() - 1] = string.Join("", cmd.Last().Append(','));
cmd.Add($"\t\tPERIOD FOR SYSTEM_TIME ([{rowStart}], [{rowEnd}])");
cmd.Add("\t)");
cmd.Add($"WITH (SYSTEM_VERSIONING = ON ( HISTORY_TABLE = {table.history_table} ))");
}
else
{
cmd.Add("\t)");
}
cmd.Add("END");
cmd.Add(BatchSeperator);
return(string.Join("\n", cmd));
}
public FamilyEditViewModel(Family family)
{
SysTable = new SysTable();
SysTable.Name = "taxonomy_family";
base.TableName = "taxonomy_family";
ID = family.ID;
CurrentID = family.ParentID;
Name = family.Name;
Authority = family.Authority;
TypeGenusID = family.TypeGenusID;
TypeGenusName = family.TypeGenus.Name;
SuprafamilyRankCode = family.SuprafamilyRankCode;
SuprafamilyRankName = family.SuprafamilyRankName;
AlternateName = family.AlternateName;
SubFamilyName = family.SubFamilyName;
TribeName = family.TribeName;
SubTribeName = family.SubTribeName;
Note = family.Note;
CreatedByCooperatorID = family.CreatedByCooperatorID;
CreatedByCooperatorName = family.CreatedByCooperatorName;
CreatedDate = family.CreatedDate;
ModifiedByCooperatorID = family.ModifiedByCooperatorID;
ModifiedByCooperatorName = family.ModifiedByCooperatorName;
ModifiedDate = family.ModifiedDate;
Genera = family.Genera;
SubFamilies = family.SubFamilies;
SubTribes = family.SubTribes;
}
private void AddData(DataDictionary dict, SysTable table, RowCollection rows)
{
if (!dict.ContainsKey(table.qualified_name))
{
dict.Add(table.qualified_name, rows);
}
}
private void btSaveGrid_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
try
{
SysTable stb = new SysTable();
foreach (CDTGridColumn col in gvMain.Columns)
{
if (!col.isExCol)
{
stb.UpdateColWidth(col.MasterRow, col.Width);
}
stb.UpdateColIndex(col.MasterRow, col.VisibleIndex);
stb.UpdateColVisible(col.MasterRow, col.Visible ? 1 : 0);
}
for (int i = 0; i < this._frmDesigner._gcDetail.Count; i++)
{
GridControl gr = this._frmDesigner._gcDetail[i];
GridView gv = gr.MainView as GridView;
foreach (CDTGridColumn col in gv.Columns)
{
if (!col.isExCol)
{
stb.UpdateColWidth(col.MasterRow, col.Width);
}
stb.UpdateColIndex(col.MasterRow, col.VisibleIndex);
stb.UpdateColVisible(col.MasterRow, col.Visible ? 1 : 0);
}
}
}
catch { }
}
private bool IsTargetEmpty(SysTable table)
{
return(Helpers
.ExecuteCommand <bool>(
new SqlCommand($"select case when not exists (select top(1) * from {table.qualified_name}) then cast(1 as bit) else cast(0 as bit) end"),
targetConn)
.FirstOrDefault());
}
public static string DropTable(SysTable table)
{
return(string.Join("\n", new string[]
{
$"IF EXISTS (SELECT object_id FROM sys.tables WHERE object_id = OBJECT_ID('{table.qualified_name}'))",
$"\tDROP TABLE {table.qualified_name}\n",
}));
}
public FamilyEditViewModel()
{
SysTable = new SysTable();
SysTable.Name = "taxonomy_family";
base.TableName = "taxonomy_family";
Genera = new List <Genus>().AsEnumerable();
SubFamilies = new List <Family>().AsEnumerable();
SubTribes = new List <Family>().AsEnumerable();
Citations = new List <Citation>().AsEnumerable();
}
public static string DropSystemVersioning(SysTable table)
{
return(string.Join("\n", new string[]
{
SetSystemVersioning(table, false),
$"ALTER TABLE {table.qualified_name}",
$"DROP PERIOD FOR SYSTEM_TIME",
BatchSeperator
}));
}
private RowCollection GetTableData(SysTable table)
{
if (usingSourceFile)
{
return(dbData.Seed.GetValueOrDefault(table.qualified_name));
}
var rows = Helpers.ExecuteCommandToDictionary(new SqlCommand($"select * from {table.qualified_name}"), sourceConn);
return(Query.ConvertRows(rows));
}
public static string SeedIf(SysTable table, IEnumerable <SysColumn> columns, RowCollection rows)
{
var tableName = table.qualified_name;
return(string.Join("\n", new string[] {
$"IF NOT EXISTS (SELECT * FROM {tableName})",
"BEGIN",
DistributeSeed(tableName, columns, rows),
"END\n"
}));
}
private void btSaveGrid_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
SysTable stb = new SysTable();
foreach (CDTGridColumn col in gvMain.Columns)
{
if (!col.isExCol)
{
stb.UpdateColWidth(col.MasterRow, col.Width);
}
stb.UpdateColIndex(col.MasterRow, col.VisibleIndex);
stb.UpdateColVisible(col.MasterRow, col.Visible ? 1 : 0);
}
}
// alter
public static string AddSystemVersioning(SysTable table)
{
return(string.Join("\n", new string[]
{
$"ALTER TABLE {table.qualified_name}",
$"ADD",
$"\tSysStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN",
$"\t\tCONSTRAINT [DF_{table.name}_SysStartTime] DEFAULT SYSUTCDATETIME(),",
$"\tSysEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN",
$"\t\tCONSTRAINT [DF_{table.name}_SysEndTime] DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),",
$"\tPERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)",
BatchSeperator,
SetSystemVersioning(table, true)
}));
}
private void SeedTable(StringBuilder builder, SysTable table, List <SysColumn> columns, RowCollection rows)
{
var hasIdentity = table.has_identity != null;
builder.Append($"-- {table.qualified_name} --\n\n");
if (hasIdentity)
{
builder.Append(Query.ToggleIdInsert(table.qualified_name, "ON"));
}
builder.Append(Query.SeedIf(table, columns, rows));
if (hasIdentity)
{
builder.Append(Query.ToggleIdInsert(table.qualified_name, "OFF"));
}
builder.Append(Query.BatchSeperator);
}
public static string UpdateIf(SysTable table, IEnumerable <SysColumn> columns, IDictionary <string, string> data)
{
var pKey = columns.ToList().Find(c => c.primary_key != null);
var pKeyColumn = pKey.name;
var pKeyValue = data[pKeyColumn];
return(string.Join("\n", new string[]
{
$"IF NOT EXISTS (SELECT {pKeyColumn} FROM {table.qualified_name} WHERE {pKeyColumn} = {pKeyValue})",
$"\tINSERT INTO {table.qualified_name} ({string.Join(", ", columns.Select(c => c.name))})",
$"\tVALUES ({string.Join(", ", columns.Select(c => data[c.name]))})",
"ELSE",
$"\tUPDATE {table.qualified_name} SET",
$"\t{string.Join(", ", columns.Where(c => c.primary_key == null).Select(c => $"{c.name} = {data[c.name]}"))} WHERE",
$"\t{pKeyColumn} = {data[pKeyColumn]}\n"
}));
}
public static string SetSystemVersioning(SysTable table, bool onOff)
{
if (onOff)
{
return(string.Join("\n", new string[]
{
$"ALTER TABLE {table.qualified_name}",
$"SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = {table.history_table}))\n"
}));
}
else
{
return(string.Join("\n", new string[]
{
$"ALTER TABLE {table.qualified_name}",
$"SET (SYSTEM_VERSIONING = OFF)\n"
}));
}
}
//执行生成代码
private void btnRun_Click(object sender, RoutedEventArgs e)
{
string basePath = AppDomain.CurrentDomain.BaseDirectory;
ComboBoxItem itemType = T4Template.SelectedItem as ComboBoxItem;
if (null == itemType)
{
return;
}
string type = itemType.Content.ToString();
foreach (var item in tables.SelectedItems)
{
SysTable table = item as SysTable;
if (null != table)
{
IGenerator gen = GeneratorFactory.GetInstance(type, table.name, txtNamespace.Text, txtPrefix.Text);
gen.SaveToCodeFile();
}
}
}
void btSaveGrid_ItemPress(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
bool admin = bool.Parse(Config.GetValue("Admin").ToString());
if (!admin)
{
return;
}
try
{
SysTable stb = new SysTable();
foreach (CDTGridColumn col in gvMain.Columns)
{
if (!col.isExCol)
{
stb.UpdateColWidth(col.MasterRow, col.Width);
}
stb.UpdateColIndex(col.MasterRow, col.VisibleIndex);
stb.UpdateColVisible(col.MasterRow, col.Visible ? 1 : 0);
}
for (int i = 0; i < this._frmDesigner._gcDetail.Count; i++)
{
GridControl gr = this._frmDesigner._gcDetail[i];
GridView gv = gr.MainView as GridView;
foreach (CDTGridColumn col in gv.Columns)
{
if (!col.isExCol)
{
stb.UpdateColWidth(col.MasterRow, col.Width);
}
stb.UpdateColIndex(col.MasterRow, col.VisibleIndex);
stb.UpdateColVisible(col.MasterRow, col.Visible ? 1 : 0);
}
}
}
catch { }
}
protected static SDatabase System()
{
return(SysTable.SysTables(new SDatabase()));
}
public SysTable GetSysTable(int id)
{
SysTable sysTable = new SysTable();
return(GetSysTables().Where(x => x.ID == id).FirstOrDefault());
}
public DatabaseImp( string dirName )
{
Name = dirName;
Log = new Log( Name );
Sys = GetSchema( "sys", true, null );
{
SysString = OpenFile( FileType.System, 0 );
SysStringReader = new BinaryReader( SysString );
SysStringWriter = new BinaryWriter( SysString );
IndexFileInfo ii = new IndexFileInfo();
ii.KeyCount = 1;
ii.Types = new DataType[] { DataType.String };
var f = OpenFile( FileType.System, 1 );
SysStringIndex = new IndexFile( f, ii, this, -1 );
}
{
SysBinary = OpenFile( FileType.System, 2 );
SysBinaryReader = new BinaryReader( SysBinary );
SysBinaryWriter = new BinaryWriter( SysBinary );
IndexFileInfo ii = new IndexFileInfo();
ii.KeyCount = 1;
ii.Types = new DataType[] { DataType.Binary };
var f = OpenFile( FileType.System, 3 );
SysBinaryIndex = new IndexFile( f, ii, this, -2 );
}
var cb = new ColBuilder();
cb.Add( "Name", DataType.String );
SysSchema = NewSysTable( 1, "Schema", cb.Get() );
cb.Add( "Schema", DataType.Int );
cb.Add( "Name", DataType.String );
cb.Add( "IsView", DataType.Tinyint );
cb.Add( "Definition", DataType.String );
SysTable = NewSysTable( 2, "Table", cb.Get() );
cb.Add( "Table", DataType.Int );
cb.Add( "Name", DataType.String );
cb.Add( "Type", DataType.Int );
SysColumn = NewSysTable( 3, "Column", cb.Get() );
cb.Add( "Table", DataType.Int );
cb.Add( "Name", DataType.String );
cb.Add( "Modified", DataType.Tinyint );
SysIndex = NewSysTable( 4, "Index", cb.Get() );
cb.Add( "Table", DataType.Int );
cb.Add( "Index", DataType.Int );
cb.Add( "ColId", DataType.Int );
SysIndexCol = NewSysTable( 5, "IndexCol", cb.Get() );
SysColumn.OpenIndexes( IndexInfo.Single( 1, 1 ) );
SysColumnIndex = SysColumn.FindIndex( 1 );
SysIndexCol.OpenIndexes( IndexInfo.Single( 2, 1) );
SysIndexColIndex = SysIndexCol.FindIndex( 1 );
SysSchema.OpenIndexes( IndexInfo.Single( 3, 1 ) );
SysSchemaByName = SysSchema.FindIndex( 1 );
SysTable.OpenIndexes( IndexInfo.Single( 4, 2 ) );
SysTableByName = SysTable.FindIndex( 2 );
if ( SysSchema.RowCount == 0 )
{
IsNew = true;
Sql( "CREATE SCHEMA sys" ); // Note these are created in TableId order.
Sql( "CREATE TABLE sys.Schema( Name string )" );
Sql( "CREATE TABLE sys.Table( Schema int, Name string, IsView tinyint, Definition string )" );
Sql( "CREATE TABLE sys.Column( Table int, Name string, Type int )" );
Sql( "CREATE TABLE sys.Index( Table int, Name string, Modified tinyint )" );
Sql( "CREATE TABLE sys.IndexCol( Table int, Index int, ColId int )" );
Sql( "CREATE INDEX ColumnByTable on sys.Column(Table)" );
Sql( "CREATE INDEX IndexColByTable on sys.IndexCol(Table)" );
Sql( "CREATE INDEX SchemaByName on sys.Schema(Name)" );
Sql( "CREATE INDEX TableByName on sys.Table(Name)" );
Normal = true;
Sql( "CREATE TABLE sys.Function( Schema int, Name string, Definition string )" );
Sql( "CREATE INDEX FunctionByName on sys.Function(Name)" );
Sql( "CREATE TABLE sys.Procedure( Schema int, Name string, Definition string )" );
Sql( "CREATE INDEX ProcedureByName on sys.Procedure(Name)" );
}
RollbackOrCommit();
Normal = true;
} // end DatabaseImp
