public override string RenameRelationQuery(NoDbTable table, NoDbRelation oldRelation, NoDbRelation newRelation)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("EXEC sp_rename N'{2}.{0}', N'{1}';", Escape(oldRelation.Name), Escape(newRelation.Name));
return(stringBuilder.ToString());
}
public override string RenameIndexQuery(NoDbTable table, NoDbIndex oldIndex, NoDbIndex newIndex)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("EXEC sp_rename N'{0}.{1}', N'{2}', 'INDEX';", table.Detail.GetTableDbName(), oldIndex.Name, newIndex.Name);
return(stringBuilder.ToString());
}
public override string CreateIndexQuery(NoDbTable table, NoDbIndex index)
{
string schema = GetSchema(table);
StringBuilder stringBuilder = new StringBuilder();
if (index.IsPrimaryKey)
{
stringBuilder.AppendFormat("ALTER TABLE {3}.{1} ADD CONSTRAINT {0} PRIMARY KEY ({2});",
Escape(index.Name),
Escape(table.Detail.GetTableDbName()),
string.Join(", ", index.Columns.Select(x => Escape(x.ColumnName))),
schema
);
}
else
{
stringBuilder.AppendFormat("CREATE {3} INDEX {0} ON {4}.{1} ({2});",
Escape(index.Name),
Escape(table.Detail.GetTableDbName()),
string.Join(", ", index.Columns.Select(x => Escape(x.ColumnName) + " " + x.Sort)),
(index.IsUnique ? "UNIQUE" : ""),
schema
);
}
return(stringBuilder.ToString());
}
public override string RenameColumnQuery(NoDbTable table, NoDbColumn oldColumn, NoDbColumn newColumn)
{
string schema = GetSchema(table);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("ALTER TABLE {3}.{0} RENAME COLUMN {1} TO {2};", Escape(table.Detail.GetTableDbName()), Escape(oldColumn.Name), Escape(newColumn.Name), schema);
return(stringBuilder.ToString());
}
public override string DropTableQuery(NoDbTable table)
{
string schema = GetSchema(table);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("IF OBJECT_ID('{1}.{0}', 'U') IS NOT NULL\nDROP TABLE {1}.{0};", Escape(table.Detail.GetTableDbName()), schema);
return(stringBuilder.ToString());
}
public override string DropColumnQuery(NoDbTable table, NoDbColumn column)
{
string schema = GetSchema(table);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("ALTER TABLE {2}.{0} DROP COLUMN {1};", Escape(table.Detail.GetTableDbName()), Escape(column.Name), schema);
return(stringBuilder.ToString());
}
public override string RenameIndexQuery(NoDbTable table, NoDbIndex oldIndex, NoDbIndex newIndex)
{
string schema = GetSchema(table);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("ALTER INDEX {2}.{0} RENAME TO {1};", Escape(oldIndex.Name), Escape(newIndex.Name), schema);
return(stringBuilder.ToString());
}
public override string RenameRelationQuery(NoDbTable table, NoDbRelation oldRelation, NoDbRelation newRelation)
{
string schema = GetSchema(table);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("ALTER TABLE {3}.{0} RENAME CONSTRAINT {1} TO {2}", Escape(table.Detail.GetTableDbName()), Escape(oldRelation.Name), Escape(newRelation.Name), schema);
return(stringBuilder.ToString());
}
public override string TableConstraintPrimaryKeyQuery(NoDbTable table)
{
StringBuilder stringBuilder = new StringBuilder();
var pkColumns = table.GetPkColumns();
stringBuilder.AppendFormat("CONSTRAINT pk_{0} PRIMARY KEY ({1})", table.Hash, string.Join(", ", pkColumns.Select(x => Escape(x.Name))));
return(stringBuilder.ToString());
}
public override string DropTableQuery(NoDbTable table)
{
string schema = GetSchema(table);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("DROP TABLE IF EXISTS {1}.{0};", Escape(table.Detail.GetTableDbName()), schema);
return(stringBuilder.ToString());
}
public override string RenameColumnQuery(NoDbTable table, NoDbColumn oldColumn, NoDbColumn newColumn)
{
string schema = GetSchema(table);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("EXEC sp_rename '{3}.{0}.{1}', '{2}', 'COLUMN';", table.Detail.GetTableDbName(), oldColumn.Name, newColumn.Name, schema);
return(stringBuilder.ToString());
}
public override string DeleteRelationQuery(NoDbTable table, NoDbRelation relation)
{
string schema = GetSchema(table);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("ALTER TABLE {2}.{0} DROP CONSTRAINT {1};", Escape(table.Detail.GetTableDbName()), Escape(relation.Name), schema);
return(stringBuilder.ToString());
}
public override string RenameRelationQuery(NoDbTable table, NoDbRelation oldRelation, NoDbRelation newRelation)
{
string schema = GetSchema(table);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendFormat(DeleteRelationQuery(table, oldRelation) + "\n");
stringBuilder.AppendFormat(CreateRelationQuery(table, newRelation));
return(stringBuilder.ToString());
}
public NoDbSearchItem GetDefaultSearchItem(NoDbTable table, List <NoDbSearchItem> currentItems)
{
var defaultItem = new NoDbSearchItem
{
RepositoryMethod = "Search" + (currentItems?.Count > 0 ? "_" + currentItems?.Count : "")
};
foreach (var column in table.ColumnsWithRelated())
{
defaultItem.AllColumns.Add(new NoDbSearchColumn
{
Name = column.Name,
DisplayInCreate = true,
DisplayInEdit = true,
DisplayInDetail = true
});
defaultItem.DisplayedColumns.Add(new NoDbSearchGridColumn
{
ColumnName = column.Name
});
if (NoDbHelper.IsDate(column.DataType))
{
defaultItem.Columns.Add(new NoDbSearchFilterColumn
{
ColumnName = column.Name,
Title = column.Name + ">",
Sign = NoDbSearchSign.Greater
});
defaultItem.Columns.Add(new NoDbSearchFilterColumn
{
ColumnName = column.Name,
Title = column.Name + "<=",
Sign = NoDbSearchSign.LessEq
});
continue;
}
if (column.DataType == NoDbDataType.STRING)
{
defaultItem.Columns.Add(new NoDbSearchFilterColumn
{
ColumnName = column.Name,
Sign = NoDbSearchSign.Contain
});
continue;
}
defaultItem.Columns.Add(new NoDbSearchFilterColumn
{
ColumnName = column.Name,
Sign = NoDbSearchSign.Equal
});
}
return(defaultItem);
}
public override string UpdateColumnQuery(NoDbTable table, NoDbColumn column)
{
string schema = GetSchema(table);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("ALTER TABLE {0}.{1} " +
"ALTER COLUMN {2} TYPE {3} --USING ({2}::integer) \n" +
",ALTER COLUMN {2} {4} NOT NULL;", schema, Escape(table.Detail.GetTableDbName()),
Escape(column.Name), ColumnDataType(column), column.Required ? "SET" : "DROP");
return(stringBuilder.ToString());
}
private void NewTableButton_Click(object sender, RoutedEventArgs e)
{
var newTableWindow = new SubWindows.NewTable((string name, string template) =>
{
_selectedTable = _noDbService.TableService.New(name, template);
BindTables();
return(true);
});
newTableWindow.ShowDialog();
refreshStaticManagerSolutions();
}
public void UpdateTable(NoDbTable updatedTable)
{
if (string.IsNullOrWhiteSpace(updatedTable.Detail.TitleColumn))
{
throw new Exception("Please enter TitleColumn field!");
}
var index = Tables.FindIndex(x => x.Hash == updatedTable.Hash);
var originalTable = Tables[index];
_noDbService.RevisionService.SaveRevision(originalTable, updatedTable);
Tables[index] = updatedTable;
WriteToFile();
}
public override string CreateRelationQuery(NoDbTable table, NoDbRelation relation)
{
string schema = GetSchema(table);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("ALTER TABLE {2}.{0} ADD CONSTRAINT {1}\n", Escape(table.Detail.GetTableDbName()), Escape(relation.Name), schema);
stringBuilder.AppendFormat("FOREIGN KEY ({0}) REFERENCES {3}.{1} ({2})\n",
string.Join(",", relation.Items.Select(x => Escape(x.ColumnName))),
Escape(relation.ForeignTable),
string.Join(",", relation.Items.Select(x => Escape(x.ForeignColumn))),
schema
);
stringBuilder.AppendFormat("ON DELETE {0}\nON UPDATE {1};", ToRule(relation.DeleteRule), ToRule(relation.UpdateRule));
return(stringBuilder.ToString());
}
public override string DropIndexQuery(NoDbTable table, NoDbIndex index)
{
string schema = GetSchema(table);
StringBuilder stringBuilder = new StringBuilder();
if (index.IsPrimaryKey)
{
stringBuilder.AppendFormat("ALTER TABLE {2}.{0} DROP CONSTRAINT {1};", Escape(table.Detail.GetTableDbName()), Escape(index.Name), schema);
}
else
{
stringBuilder.AppendFormat("DROP INDEX {1}.{0};", Escape(index.Name), schema);
}
return(stringBuilder.ToString());
}
private void XTables_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
if (xTables.SelectedItem == null)
{
_selectedTable = null;
ClearAllRelatedWithTable();
StaticManager.SelectedTable = null;
return;
}
_selectedTable = (xTables.SelectedItem as NoDbTable).JsonClone();
xColumns.ItemsSource = _selectedTable.Columns;
xTableDetail.SelectedObject = _selectedTable.Detail;
xColumnsGrid.IsEnabled = true;
StaticManager.SelectedTable = _selectedTable;
}
public override string AddColumnQuery(NoDbTable table, NoDbColumn column)
{
var schema = GetSchema(table);
StringBuilder stringBuilder = new StringBuilder();
if (column.Required)
{
stringBuilder.AppendFormat("ALTER TABLE {2}.{0} ADD {1};\n", Escape(table.Detail.GetTableDbName()), ColumnQuery(column), schema);
column.Required = false;
stringBuilder.AppendFormat("--ALTER TABLE {2}.{0} ADD {1};\n", Escape(table.Detail.GetTableDbName()), ColumnQuery(column), schema);
stringBuilder.AppendFormat("--UPDATE {0}.{1} SET {2} = 0;\n", schema, Escape(table.Detail.GetTableDbName()), Escape(column.Name));
column.Required = true;
stringBuilder.AppendFormat("--" + UpdateColumnQuery(table, column).Replace("\n", "\n--"));
}
else
{
stringBuilder.AppendFormat("ALTER TABLE {2}.{0} ADD {1};", Escape(table.Detail.GetTableDbName()), ColumnQuery(column), schema);
}
return(stringBuilder.ToString());
}
public override string CreateTableQuery(NoDbTable table)
{
string schema = GetSchema(table);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("CREATE TABLE {1}.{0}(\n", Escape(table.Detail.GetTableDbName()), schema);
stringBuilder.AppendFormat("\t{0}\n", string.Join(",\n\t", table.ColumnsWithRelated().Select(x => ColumnQuery(x))));
stringBuilder.Append("\n);\n");
foreach (var item in table.Indices)
{
stringBuilder.Append(CreateIndexQuery(table, item) + "\n");
}
foreach (var item in table.Relations)
{
stringBuilder.Append(CreateRelationQuery(table, item));
}
return(stringBuilder.ToString());
}
public abstract string DropTableQuery(NoDbTable table);
public abstract string DropIndexQuery(NoDbTable table, NoDbIndex index);
public abstract string RenameColumnQuery(NoDbTable table, NoDbColumn oldColumn, NoDbColumn newColumn);
public string GetSchema(NoDbTable table)
{
return(table.Detail.Schema ?? "dbo");
}
public abstract string UpdateColumnQuery(NoDbTable table, NoDbColumn column);
public abstract string TableConstraintPrimaryKeyQuery(NoDbTable table);
public abstract string RenameRelationQuery(NoDbTable table, NoDbRelation oldRelation, NoDbRelation newRelation);
public abstract string RenameIndexQuery(NoDbTable table, NoDbIndex oldIndex, NoDbIndex newIndex);