private void PrepareColumns()
{
if (Columns == null || Columns.Count == 0)
{
List <ColumnClause> lst = new List <ColumnClause>();
for (int i = 0; i < Tables.Count; i++)
{
TableClause st = Tables[i];
var tlist = st.Table.TableColumns;
foreach (var c in tlist)
{
ColumnClause cs = new ColumnClause();
FieldExpr fe = new FieldExpr();
fe.Bind(st, c.Name);
cs.ColumnExpression = fe;
lst.Add(cs);
}
}
Columns.Replace(lst);
}
for (int i = 0; i < Columns.Count; i++)
{
ColumnClause cs = Columns[i];
cs.Prepare();
}
}
private string MinTableName(ExpressionSqlBuilder builder)
{
string res;
TableClause tc1 = this.TableClause;
if (!(tc1.Table is TableDesc))
{
throw new Exception("Can not find alias table " + tc1.Table.ToStr());
}
ITableSource item = this.FindParentTableSource();
var arr = item.GetTables();
if (arr.Length == 1 && arr[0] == tc1)
{
return("");
}
while (item != null)
{
res = DoMinTableName(builder, item, tc1);
if (res != null)
{
return(res);
}
var t = item.FindParentTableSource();
if (t == item)
{
throw new Exception("internal parser error (132)");
}
item = t;
}
throw new Exception("Table clause not found in expression");
}
public static string GenerateTemplate(ITableSchema schema, SqlScriptType type, bool ifExists)
{
TableName tableName = schema.TableName;
TableClause script = new TableClause(schema);
switch (type)
{
case SqlScriptType.INSERT:
return(script.INSERT(schema.Columns));
case SqlScriptType.SELECT:
return(script.SELECT(schema.Columns));
case SqlScriptType.UPDATE:
return(script.UPDATE(schema.Columns));
case SqlScriptType.INSERT_OR_UPDATE:
return(script.INSERT_OR_UPDATE(schema.Columns));
case SqlScriptType.DELETE:
return(new Dependency(tableName.DatabaseName).DELETE(tableName)
+ script.DELETE(schema.Columns));
case SqlScriptType.DROP:
return(new Dependency(tableName.DatabaseName).DROP_TABLE(tableName, ifExists)
+ script.DROP_TABLE(ifExists));
}
return(null);
}
private void PrepareTables()
{
//подготовка таблиц
for (int i = 0; i < Tables.Count; i++)
{
TableClause st = Tables[i];
st.Prepare();
}
}
public void Bind(TableClause tableClause, string fieldName)
{
Column ci = tableClause.Table.ByName(fieldName);
if (ci == null)
{
throw new Exception("Column " + fieldName + " is not found");
}
FieldName = fieldName;
PhysicalColumn = ci;
TableClause = tableClause;
Init(ci.SimpleType, tableClause.Table.CoordinateSystem);
IsBinded = true;
}
public string Generate(Modifier modifier, ClassTableName ctname)
{
//must run it first to form Dictionary
Fields();
string comment = @"//
// Machine Generated Code
//
";
if (nonvalized.Count > 0)
{
this.code.AddUsing("Tie");
comment += "using Tie;";
}
this.clss.AddConstructor();
var cons = this.clss.AddConstructor();
cons.Params.Add <DataRow>("row");
cons.BaseArgs = new Arguments("row");
SQL_CREATE_TABLE_STRING = TableClause.GenerateCREATE_TABLE(metaTable);
if (option.HasTableAttribute)
{
var attr = clss.AddAttribute <TableAttribute>();
GetTableAttribute(attr, metaTable, ctname);
}
PrimaryConstructor();
DPObjectId();
PrimaryKeys();
IdentitiyKeys();
FillAndCollect();
ConstStringColumnNames();
clss.AddUtilsMethod(UtilsThisMethod.Copy | UtilsThisMethod.Clone | UtilsThisMethod.Compare);
return(code.ToString());
}
public override IExplore Expolore(DelegateExpessionExplorer del)
{
List <TableClause> Tables2 = new List <TableClause>();
Tables.ForEach(a =>
{
TableClause g2 = (TableClause)a.Expolore(del);
if (g2 != null)
{
Tables2.Add(g2);
}
});
Tables.Replace(Tables2);
List <ColumnClause> Columns2 = new List <ColumnClause>();
Columns.ForEach(a =>
{
ColumnClause c = (ColumnClause)a.Expolore(del);
if (c != null)
{
Columns2.Add(c);
}
}
);
Columns.Replace(Columns2);
if (WhereExpr != null)
{
WhereExpr = (Expression)WhereExpr.Expolore(del);
}
if (GroupBys != null)
{
List <GroupByClause> GroupBys2 = new List <GroupByClause>();
GroupBys.ForEach(a =>
{
GroupByClause g2 = (GroupByClause)a.Expolore(del);
if (g2 != null)
{
GroupBys2.Add(g2);
}
});
GroupBys.Replace(GroupBys2);
}
if (Having != null)
{
Having = (Expression)Having.Expolore(del);
}
if (OrderBys != null)
{
List <OrderByClause> OrderBys2 = new List <OrderByClause>();
OrderBys.ForEach(a =>
{
OrderByClause g2 = (OrderByClause)a.Expolore(del);
if (g2 != null)
{
OrderBys2.Add(g2);
}
});
OrderBys.Replace(OrderBys2);
}
if (ExtSelects != null && ExtSelects.Count > 0)
{
var ExtSelects2 = new TokenList <ExtSelectClause>(this);
foreach (var e in ExtSelects)
{
var t = (ExtSelectClause)e.Expolore(del);
if (t != null)
{
ExtSelects2.Add(t);
}
}
ExtSelects = ExtSelects2;
}
return(base.Expolore(del));
}
public string Compare()
{
TableSchema schema1 = new TableSchema(tableName1);
TableSchema schema2 = new TableSchema(tableName2);
StringBuilder builder = new StringBuilder();
TableClause script = new TableClause(schema1);
foreach (ColumnSchema column in schema1.Columns)
{
if (schema2.Columns.Where(c => c.ColumnName == column.ColumnName).Count() == 0)
{
builder.AppendLine(script.ADD_COLUMN(column));
}
else if (schema2.Columns.Where(c =>
c.ColumnName.Equals(column.ColumnName)
&& (c.CType != column.CType
|| c.Nullable != column.Nullable
|| c.Precision != column.Precision
|| c.Scale != column.Scale
|| c.IsIdentity != column.IsIdentity
|| c.IsComputed != column.IsComputed
))
.Count() != 0)
{
builder.AppendLine(script.ALTER_COLUMN(column));
}
}
var pk1 = schema1.PrimaryKeys;
var pk2 = schema2.PrimaryKeys;
if (pk2.Keys.Length == 0)
{
if (pk1.Keys.Length > 0)
{
builder.AppendLine(script.ADD_PRIMARY_KEY(pk1));
builder.AppendLine(TableClause.GO);
}
}
else
{
if (pk1.Keys.Length > 0 && !Equals(pk1.Keys, pk2.Keys))
{
builder.AppendLine(script.DROP_PRIMARY_KEY(pk1));
builder.AppendLine(script.ADD_PRIMARY_KEY(pk1));
builder.AppendLine(TableClause.GO);
}
}
var fk1 = schema1.ForeignKeys;
var fk2 = schema2.ForeignKeys;
if (fk2.Keys.Length == 0)
{
if (fk1.Keys.Length > 0)
{
foreach (var fk in fk1.Keys)
{
builder.AppendLine(script.ADD_FOREIGN_KEY(fk)).AppendLine(TableClause.GO);
}
}
}
else
{
if (fk1.Keys.Length > 0)
{
foreach (var k1 in fk1.Keys)
{
if (fk2.Keys.Where(k2 => k2.Constraint_Name.Equals(k1.Constraint_Name)).Count() == 0)
{
builder.AppendLine(script.DROP_FOREIGN_KEY(k1)).AppendLine(TableClause.GO);
builder.AppendLine(script.ADD_FOREIGN_KEY(k1)).AppendLine(TableClause.GO);
}
}
}
}
return builder.ToString();
}
private static string DoMinTableName(ExpressionSqlBuilder builder, ITableSource parent, TableClause tc1)
{
TableDesc td1 = (TableDesc)tc1.Table;
var arr = parent.GetTables();
bool find = false;
bool findDubName = false;
bool findDubSchema = false;
foreach (var tc in arr)
{
if (tc == tc1)
{
find = true;
}
else
if (string.IsNullOrEmpty(tc.Alias) &&
(tc.Table is TableDesc)
)
{
TableDesc td = (TableDesc)tc.Table;
if (td.PhysicalTableName.ToLower() == td1.PhysicalTableName.ToLower())
{
findDubName = true;
if (td.PhysicalSchema.ToLower() == td1.PhysicalSchema.ToLower())
{
findDubSchema = true;
}
}
}
}
if (!find)
{
return(null);
}
if (!findDubName)
{
return(builder.EncodeTable(td1.PhysicalTableName));
}
if (!findDubSchema)
{
return(builder.EncodeTable(td1.PhysicalSchema) + "." + builder.EncodeTable(td1.PhysicalTableName));
}
throw new Exception("Not unique table " + builder.EncodeTable(td1.PhysicalSchema) + "." + builder.EncodeTable(td1.PhysicalTableName));
}
public static int GenerateRows(StreamWriter writer, TableSchema schema, Locator where, bool hasIfExists)
{
TableName tableName = schema.TableName;
string sql = string.Format("SELECT * FROM {0}", tableName);
if (where != null)
sql = string.Format("SELECT * FROM {0} WHERE {1}", tableName, where);
SqlCmd cmd = new SqlCmd(tableName.Provider, sql);
TableClause script = new TableClause(schema);
int count = 0;
cmd.Read(
reader =>
{
DataTable schema1 = reader.GetSchemaTable();
string[] columns = schema1.AsEnumerable().Select(row => row.Field<string>("ColumnName")).ToArray();
object[] values = new object[columns.Length];
while (reader.HasRows)
{
while (reader.Read())
{
reader.GetValues(values);
if (hasIfExists)
writer.WriteLine(script.IF_NOT_EXISTS_INSERT(columns, values));
else
writer.WriteLine(script.INSERT(columns, values));
count++;
if (count % 5000 == 0)
writer.WriteLine(TableClause.GO);
}
reader.NextResult();
}
});
if (count != 0)
writer.WriteLine(TableClause.GO);
return count;
}
private static string GenerateRows(TableSchema schema, TableReader reader)
{
var table = reader.Table;
TableClause script = new TableClause(schema);
StringBuilder builder = new StringBuilder();
foreach (DataRow row in table.Rows)
builder.Append(script.INSERT(row)).AppendLine();
if (table.Rows.Count > 0)
builder.AppendLine(TableClause.GO);
return builder.ToString();
}
public static string GenerateTemplate(TableSchema schema, SqlScriptType type)
{
TableName tableName = schema.TableName;
TableClause script = new TableClause(schema);
switch (type)
{
case SqlScriptType.INSERT:
return script.INSERT(schema.Columns);
case SqlScriptType.SELECT:
return script.SELECT(schema.Columns);
case SqlScriptType.UPDATE:
return script.UPDATE(schema.Columns);
case SqlScriptType.INSERT_OR_UPDATE:
return script.INSERT_OR_UPDATE(schema.Columns);
case SqlScriptType.DELETE:
return new Dependency(tableName.DatabaseName).DELETE(tableName)
+ script.DELETE(schema.Columns);
}
return null;
}
public string Compare()
{
TableSchema schema1 = new TableSchema(tableName1);
TableSchema schema2 = new TableSchema(tableName2);
StringBuilder builder = new StringBuilder();
TableClause script = new TableClause(schema2);
foreach (ColumnSchema column in schema1.Columns)
{
if (schema2.Columns.Where(c => IgnoreCaseEquals(c.ColumnName, column.ColumnName)).Count() == 0)
{
builder.AppendLine(script.ADD_COLUMN(column));
}
else if (schema2.Columns.Where(c => ColumnNotEqual(c, column)).Count() != 0)
{
builder.AppendLine(script.ALTER_COLUMN(column));
}
}
var pk1 = schema1.PrimaryKeys;
var pk2 = schema2.PrimaryKeys;
if (pk2.Keys.Length == 0)
{
if (pk1.Keys.Length > 0)
{
builder.AppendLine(script.ADD_PRIMARY_KEY(pk1));
builder.AppendLine(SqlScript.GO);
}
}
else
{
if (pk1.Keys.Length > 0 && !Equals(pk1.Keys, pk2.Keys))
{
builder.AppendLine(script.DROP_PRIMARY_KEY(pk1));
builder.AppendLine(script.ADD_PRIMARY_KEY(pk1));
builder.AppendLine(SqlScript.GO);
}
}
var fk1 = schema1.ForeignKeys;
var fk2 = schema2.ForeignKeys;
if (fk2.Keys.Length == 0)
{
if (fk1.Keys.Length > 0)
{
foreach (var fk in fk1.Keys)
{
builder.AppendLine(script.ADD_FOREIGN_KEY(fk)).AppendLine(SqlScript.GO);
}
}
}
else
{
if (fk1.Keys.Length > 0)
{
foreach (var k1 in fk1.Keys)
{
if (fk2.Keys.Where(k2 => k2.Constraint_Name.Equals(k1.Constraint_Name)).Count() == 0)
{
builder.AppendLine(script.DROP_FOREIGN_KEY(k1)).AppendLine(SqlScript.GO);
builder.AppendLine(script.ADD_FOREIGN_KEY(k1)).AppendLine(SqlScript.GO);
}
}
}
}
return(builder.ToString());
}
