private string generateUpdateBody(Table table, List<Column> keyColumns, List<Column> updateColumns)
{
CodeWriter writer = new CodeWriter();
writer.WriteLineFormat("UPDATE [{0}].[{1}]", table.Schema.Name, table.Name);
writer.WriteLine("SET");
writer.PushIdent();
for (int i = 0; i < updateColumns.Count; i++)
{
Column column = updateColumns[i];
string and = i < updateColumns.Count - 1 ? "," : "";
string cond = string.Format("[{0}] = @{0} {1}", column.Name, and).Trim();
writer.WriteLine(cond);
}
writer.PopIdent();
writer.WriteLine("WHERE");
writer.PushIdent();
for (int i = 0; i < keyColumns.Count; i++)
{
Column column = keyColumns[i];
string and = i < keyColumns.Count - 1 ? "AND" : "";
string cond = string.Format("[{0}] = @{0} {1}", column.Name, and).Trim();
writer.WriteLine(cond);
}
writer.PopIdent();
return writer.Code;
}
private void buildColumnsNode(Table table, TreeNode parentNode)
{
foreach (Column col in table.Columns)
{
string s = col.Name + " [" + col.DataType.FullName + "]";
TreeNode node = new TreeNode(s, 3, 3);
node.Tag = col;
parentNode.Nodes.Add(node);
}
}
public override string GenerateSqlCreate(Table table)
{
CodeWriter writer = new CodeWriter();
writer.WriteLineFormat("create table [{0}].[{1}](", table.Schema.Name, table.Name);
writer.PushIdent();
int colCount = table.Columns.Count;
int conCount = table.Constraints.Count;
for (int i = 0; i < colCount; i++)
{
Column col = table.Columns[i];
DataType dt = TypeMap == null ? col.DataType : TypeMap.MapDataType(col.DataType);
string nullDef = col.IsNullable? "null": "not null";
string autoIncrementDef = col.IsAutoIncremented ? "identity(1, 1)" : "";
string colDef = string.Format("[{0}] {1} {2} {3}", col.Name, dt.FullName, nullDef, autoIncrementDef).Trim();
string comma = (i < colCount - 1 || conCount > 0) ? "," : "";
writer.WriteLineFormat("{0}{1}", colDef, comma);
}
for (int i = 0; i < conCount; i++)
{
TableConstraint con = table.Constraints[i];
string conDef = null;
if (con.Type == ConstraintType.PrimaryKey)
{
string colDef = StringUtils.GenerateCommaSeparatedString(con.Columns, "[", "]");
conDef = string.Format("constraint [{0}] primary key({1})", con.Name, colDef);
}
else if (con.Type == ConstraintType.Unique)
{
string colDef = StringUtils.GenerateCommaSeparatedString(con.Columns, "[", "]");
conDef = string.Format("constraint [{0}] unique({1})", con.Name, colDef);
}
else if (con.Type == ConstraintType.Check)
{
CheckConstraint chk = (CheckConstraint)con;
conDef = string.Format("constraint [{0}] check {1}", chk.Name, chk.Clause);
}
else if (con.Type == ConstraintType.ForeignKey)
{
ForeignKeyConstraint fk = (ForeignKeyConstraint)con;
string colDef = StringUtils.GenerateCommaSeparatedString(fk.Columns, "[", "]");
string refColDef = StringUtils.GenerateCommaSeparatedString(fk.ReferencedColumns, "[", "]");
conDef = string.Format("constraint [{0}] foreign key({1}) references [{2}].[{3}]({4}) on update {5} on delete {6}",
fk.Name, colDef, con.Table.Schema.Name, fk.ReferencedTable, refColDef, fk.UpdateRule, fk.DeleteRule);
}
string comma = i < conCount - 1 ? "," : "";
writer.WriteLineFormat("{0}{1}", conDef, comma);
}
writer.PopIdent();
writer.Write(")");
return writer.Code;
}
public override string GenerateSqlCreate(Table table)
{
CodeWriter writer = new CodeWriter();
writer.WriteLine("DELIMITER $$");
writer.WriteLineFormat("create table `{0}`.`{1}`(", table.Schema.Name, table.Name);
writer.PushIdent();
int colCount = table.Columns.Count;
int conCount = table.Constraints.Where(c => c.Type != ConstraintType.Check).Count();
for (int i = 0; i < colCount; i++)
{
Column col = table.Columns[i];
DataType dt = TypeMap == null ? col.DataType : TypeMap.MapDataType(col.DataType);
string nullDef = col.IsNullable? "null": "not null";
string autoIncrementDef = col.IsAutoIncremented ? "auto_increment" : "";
string colDef = string.Format("`{0}` {1} {2} {3}", col.Name, dt.FullName, nullDef, autoIncrementDef).Trim();
string comma = (i < colCount - 1 || conCount > 0) ? "," : "";
writer.WriteLineFormat("{0}{1}", colDef, comma);
}
for (int i = 0; i < conCount; i++)
{
TableConstraint con = table.Constraints[i];
string conDef = null;
if (con.Type == ConstraintType.PrimaryKey)
{
string colDef = StringUtils.GenerateCommaSeparatedString(con.Columns, "`", "`");
conDef = string.Format("constraint `{0}` primary key({1})", con.Name, colDef);
}
else if (con.Type == ConstraintType.Unique)
{
string colDef = StringUtils.GenerateCommaSeparatedString(con.Columns, "`", "`");
conDef = string.Format("constraint `{0}` unique({1})", con.Name, colDef);
}
else if (con.Type == ConstraintType.ForeignKey)
{
ForeignKeyConstraint fk = (ForeignKeyConstraint)con;
string colDef = StringUtils.GenerateCommaSeparatedString(fk.Columns, "`", "`");
string refColDef = StringUtils.GenerateCommaSeparatedString(fk.ReferencedColumns, "`", "`");
conDef = string.Format("constraint `{0}` foreign key({1}) references `{2}`.`{3}`({4}) on update {5} on delete {6}",
fk.Name, colDef, con.Table.Schema.Name, fk.ReferencedTable, refColDef, fk.UpdateRule, fk.DeleteRule);
}
string comma = i < conCount - 1 ? "," : "";
writer.WriteLineFormat("{0}{1}", conDef, comma);
}
writer.PopIdent();
writer.Write(")$$");
return writer.Code;
}
private string generateInsertBody(Table table, List<Column> insertableColumns)
{
List<string> colNames = new List<string>();
foreach (Column col in insertableColumns)
{
colNames.Add(col.Name);
}
CodeWriter writer = new CodeWriter();
writer.WriteLineFormat("INSERT INTO [{0}].[{1}]", table.Schema.Name, table.Name);
writer.WriteLineFormat("({0})", StringUtils.GenerateCommaSeparatedString(colNames, "[", "]"));
writer.WriteLine("VALUES");
writer.WriteLineFormat("({0});", StringUtils.GenerateCommaSeparatedString(colNames, "@", ""));
writer.WriteLine("RETURN SCOPE_IDENTITY();");
return writer.Code;
}
private void buildConstraintsNode(Table table, TreeNode parentNode)
{
foreach (TableConstraint con in table.Constraints)
{
string s = con.Name + " [" + con.Type + "]";
TreeNode node = new TreeNode(s, 4, 4);
node.Tag = con;
TreeNode conColumns = new TreeNode("Columns", 1, 1);
TreeNode conDetails = new TreeNode("Details", 1, 1);
node.Nodes.Add(conColumns);
node.Nodes.Add(conDetails);
buildConstraintColumnsNode(con, conColumns);
buildConstraintDetailsNode(con, conDetails);
parentNode.Nodes.Add(node);
}
}
public abstract string GenerateDelete(Table table);
public abstract string GenerateUpdate(Table table);
public abstract string GenerateGet(Table table);
public abstract string GenerateInsert(Table table);
public string generateDeleteBody(Table table, List<Column> keyColumns)
{
CodeWriter writer = new CodeWriter();
writer.WriteLineFormat("DELETE FROM [{0}].[{1}]", table.Schema.Name, table.Name);
writer.WriteLine("WHERE");
writer.PushIdent();
for (int i = 0; i < keyColumns.Count; i++)
{
Column column = keyColumns[i];
string and = i < keyColumns.Count - 1 ? "and" : "";
string cond = string.Format("[{0}] = @{0} {1}", column.Name, and).Trim();
writer.WriteLine(cond);
}
writer.PopIdent();
return writer.Code;
}
public override string GenerateDelete(Table table)
{
CodeWriter writer = new CodeWriter();
foreach (TableConstraint constraint in table.Constraints)
{
if (constraint.Type == ConstraintType.PrimaryKey || constraint.Type == ConstraintType.Unique)
{
StoredProcedure sp = new StoredProcedure();
sp.Parameters = new List<Parameter>();
sp.Schema = table.Schema;
List<string> colNames = constraint.Columns;
string sepStrings = StringUtils.GenerateSeparatedString(colNames, "_AND_", "", "");
sp.Name = string.Format("HXF_{0}_DELETE_BY_{1}", table.Name, sepStrings);
IEnumerable<Column> keyColumns = table.Columns.Where<Column>(c => colNames.Contains(c.Name));
string body = generateDeleteBody(table, keyColumns.ToList<Column>());
foreach (Column column in keyColumns)
{
Parameter p = new Parameter();
p.StoredProcedure = sp;
p.Name = "@" + column.Name;
//p.Direction = ParameterDirection.In;
p.Mode = "IN";
p.DataType = TypeMap != null ? TypeMap.MapDataType(column.DataType) : column.DataType;
sp.Parameters.Add(p);
sp.Definition = body;
}
SqlServerDatabaseGenerator gen = new SqlServerDatabaseGenerator(TypeMap);
string s = gen.GenerateStoredProcedureCreate(sp).Trim();
writer.WriteLineFormat("-- {0} --", sp.Name);
writer.WriteLine(GenerateDropIfExists(sp.Name));
writer.WriteLine();
writer.WriteLine(s);
writer.WriteLine("GO");
writer.WriteLine();
}
}
return writer.Code;
}
public override string GenerateUpdate(Table table)
{
CodeWriter writer = new CodeWriter();
foreach (TableConstraint constraint in table.Constraints)
{
if (constraint.Type == ConstraintType.PrimaryKey || constraint.Type == ConstraintType.Unique)
{
StoredProcedure sp = new StoredProcedure();
sp.Parameters = new List<Parameter>();
sp.Schema = table.Schema;
List<string> colNames = constraint.Columns;
string sepStrings = StringUtils.GenerateSeparatedString(colNames, "_AND_", "", "");
sp.Name = string.Format("HXF_{0}_UPDATE_BY_{1}", table.Name, sepStrings);
// columns that are part of key constraint is used in where clause
IEnumerable<Column> keyColumns = table.Columns.Where<Column>(c => colNames.Contains(c.Name));
// columns that can be updated
IEnumerable<Column> updatableColumns = table.Columns.Where<Column>(c => !(c.IsComputed || c.IsAutoIncremented));
// columns that will be part of update statement
IEnumerable<Column> updateColumns = updatableColumns.Except(keyColumns);
if (updateColumns.Count() > 0)
{
string body = generateUpdateBody(table, keyColumns.ToList<Column>(), updateColumns.ToList<Column>());
// create parameters for columns used in the stored procedure
foreach (Column column in keyColumns.Union(updatableColumns))
{
Parameter p = new Parameter();
p.StoredProcedure = sp;
p.Name = "@" + column.Name;
//p.Direction = ParameterDirection.In;
p.Mode = "IN";
p.DataType = TypeMap != null ? TypeMap.MapDataType(column.DataType) : column.DataType;
sp.Parameters.Add(p);
sp.Definition = body;
}
SqlServerDatabaseGenerator gen = new SqlServerDatabaseGenerator(TypeMap);
string s = gen.GenerateStoredProcedureCreate(sp).Trim();
writer.WriteLineFormat("-- {0} --", sp.Name);
writer.WriteLine(GenerateDropIfExists(sp.Name));
writer.WriteLine();
writer.WriteLine(s);
writer.WriteLine("GO");
writer.WriteLine();
}
}
}
return writer.Code;
}
public override string GenerateGetAll(Table table)
{
CodeWriter writer = new CodeWriter();
StoredProcedure sp = new StoredProcedure();
sp.Parameters = new List<Parameter>();
sp.Schema = table.Schema;
sp.Name = string.Format("HXF_{0}_GET_ALL", table.Name);
string body = string.Format("SELECT * from [{0}].[{1}]", table.Schema.Name, table.Name);
sp.Definition = body;
string s = new SqlServerDatabaseGenerator(this.TypeMap).GenerateStoredProcedureCreate(sp).Trim();
writer.WriteLineFormat("-- {0} --", sp.Name);
writer.WriteLine(GenerateDropIfExists(sp.Name));
writer.WriteLine();
writer.WriteLine(s);
return writer.Code;
}
public override string GenerateInsert(Table table)
{
CodeWriter writer = new CodeWriter();
StoredProcedure sp = new StoredProcedure();
sp.Schema = table.Schema;
sp.Name = string.Format("HXF_{0}_INSERT", table.Name);
IEnumerable<Column> insertableColumns = table.Columns.Where<Column>(c => !(c.IsComputed || c.IsAutoIncremented));
List<Parameter> parameters = new List<Parameter>();
int i = 0;
foreach (Column col in insertableColumns)
{
Parameter p = new Parameter();
p.Name = "@" + col.Name;
p.Position = i++;
p.DataType = col.DataType.Clone();
p.Mode = "IN";
parameters.Add(p);
}
sp.Parameters = parameters;
sp.Definition = generateInsertBody(table, insertableColumns.ToList<Column>());
string s = new SqlServerDatabaseGenerator(TypeMap).GenerateStoredProcedureCreate(sp).Trim();
writer.WriteLineFormat("-- {0} --", sp.Name);
writer.WriteLine(GenerateDropIfExists(sp.Name));
writer.WriteLine();
writer.WriteLineFormat(s);
return writer.Code;
}
public abstract string GenerateSqlCreate(Table table);