public override string ToSql(ExpressionSqlBuilder builder)
{
if (PhysicalColumn == null)
{
throw new Exception("Column \"" + FieldName + "\" is not found");
}
string res = "";
if (builder.FieldsAsInsertedAlias)
{
res += "INSERTED";
}
if (!IsLocalColumn)
{
if (!string.IsNullOrEmpty(TableAlias))
{
if ((StringComparer.InvariantCultureIgnoreCase.Compare(TableAlias, TableClause.Alias) == 0))
{
if (!string.IsNullOrEmpty(res))
{
res += ".";
}
res += builder.EncodeTable(TableAlias);
}
else
{
res += MinTableName(builder); //TableClause.Table.ToSql(builder);
}
}
else
{
if (!string.IsNullOrEmpty(TableClause.Alias))
{
res += builder.EncodeTable(TableClause.Alias);
}
else
{
res += MinTableName(builder);// TableClause.Table.ToSql(builder);
}
}
}
if (!string.IsNullOrEmpty(PhysicalColumn.PhysicalName))
{
if (!string.IsNullOrEmpty(res))
{
res += ".";
}
res += builder.EncodeTable(PhysicalColumn.PhysicalName);
}
else
{//на всякий пожарный, но эта ветка не используется
if (!string.IsNullOrEmpty(res))
{
res += ".";
}
res += builder.EncodeTable(FieldName);
}
return(res);
}
public string ToSql(ExpressionSqlBuilder builder, bool nameOnly = false)
{
string r = "";
if (!nameOnly && !string.IsNullOrEmpty(Schema))
{
r = builder.EncodeTable(Schema) + ".";
}
r += builder.EncodeTable(Name);
return(r);
}
protected string ToPostgreSql(ExpressionSqlBuilder builder)
{
StringBuilder sb = new StringBuilder();
sb.Append("ALTER TABLE ");
sb.Append(Table.ToSql(builder));
sb.Append(" ");
if (AlterType == AlterType.RenameColumn)
{
sb.Append("RENAME COLUMN ");
sb.Append(builder.EncodeTable(OldColumnName));
sb.Append(" TO ").Append(builder.EncodeTable(NewName));
}
if (AlterType == AlterType.RenameTable)
{
sb.Append("RENAME TO ");
sb.Append(builder.EncodeTable(NewName));
}
if (AlterType == AlterType.RenameSchema)
{
sb.Append("SET SCHEMA ");
sb.Append(builder.EncodeTable(NewName));
}
if (AlterType == AlterType.AlterColumn)
{
for (int i = 0; i < AlterColumns.Count; i++)
{
var cd = AlterColumns[i];
if (i > 0)
{
sb.Append(", ");
}
switch (cd.AlterColumn)
{
case AlterColumnType.AddColumn:
sb.Append("ADD COLUMN ").Append(cd.ToSql(builder));
break;
case AlterColumnType.DropColumn:
sb.Append("DROP COLUMN ").Append(builder.EncodeTable(cd.Name));
break;
case AlterColumnType.AlterColumn:
sb.Append("ALTER COLUNN ").Append(builder.EncodeTable(cd.Name));
if (cd.Nullable)
{
sb.Append(" DROP NOT NULL");
}
else
{
sb.Append(" SET NOT NULL");
}
sb.Append(", ALTER COLUNN ").Append(builder.EncodeTable(cd.Name));
sb.Append("ALTER COLUNN ").Append(builder.EncodeTable(cd.Name)).Append(" TYPE ").Append(cd.Type.ToSql(builder));
break;
}
}
}
return(sb.ToString());
}
public override string ToSql(ExpressionSqlBuilder builder)
{
if (Columns.Count > 0)
{
StringBuilder sqlColumns = new StringBuilder();
int i = 0;
foreach (var cs in Columns)
{
if (i != 0)
{
sqlColumns.Append(", ");
}
sqlColumns.Append(cs.ColumnExpression.ToSql(builder));
if (!string.IsNullOrEmpty(cs.Alias))
{
sqlColumns.Append(" as ").Append(builder.EncodeTable(cs.Alias)).Append(" ");
}
i++;
}
sqlColumns.Append(" ");
return(sqlColumns.ToString());
}
else
{
string s = "";
if (!string.IsNullOrEmpty(Prefix))
{
s = Prefix + ".";
}
s = s + "*";
return(s);
}
}
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 override string ToSql(ExpressionSqlBuilder builder)
{
StringBuilder sb = new StringBuilder();
sb.Append(builder.EncodeTable(Name));
if (AlterColumn == AlterColumnType.AddColumn || AlterColumn == AlterColumnType.AlterColumn)
{
sb.Append(" ");
if (builder.DbType == DriverType.PostgreSQL && AutoIncrement)
{
if (Type.Type == DbColumnType.BigInt)
{
sb.Append("bigserial");
}
else
{
sb.Append("serial");
}
}
else
{
sb.Append(Type.ToSql(builder));
}
if (AutoIncrement && builder.DbType == DriverType.SqlServer)
{
sb.Append(" IDENTITY(1,1)");
}
if (Nullable)
{
sb.Append(" NULL");
}
else
{
sb.Append(" NOT NULL");
}
if (PrimaryKey)
{
sb.Append(" PRIMARY KEY");
}
}
return(sb.ToString());
}
private string DoToSql(ExpressionSqlBuilder builder)
{
//добавляем колонки
StringBuilder sqlColumns = new StringBuilder();
List <ColumnClause> ColumnsLocal = GetAllColumns();
int i = 0;
foreach (var cs in ColumnsLocal)
{
if (i != 0)
{
sqlColumns.Append(", ");
}
sqlColumns.Append(cs.ToSql(builder));
//if (!string.IsNullOrEmpty(cs.InternalDbAlias)) sqlColumns.Append(" as ").Append(BaseExpressionFactory.TableSqlCodeEscape(cs.InternalDbAlias)).Append(" ");
i++;
}
StringBuilder sqlTables = new StringBuilder();
if (Tables.Count > 0)
{
sqlTables.Append("from ");
}
i = 0;
foreach (TableClause st in Tables)
{
if (i != 0)
{
switch (st.Join)
{
case JoinType.Cross:
sqlTables.Append(" CROSS JOIN ");
break;
case JoinType.Full:
sqlTables.Append(" FULL JOIN ");
break;
case JoinType.Left:
sqlTables.Append(" LEFT JOIN ");
break;
case JoinType.Right:
sqlTables.Append(" RIGHT JOIN ");
break;
case JoinType.Inner:
sqlTables.Append(" INNER JOIN ");
break;
}
}
sqlTables.Append(st.ToSql(builder));
//if (!string.IsNullOrEmpty(st.Alias)) sqlTables.Append(" as ").Append(BaseExpressionFactory.TableSqlCodeEscape(st.Alias)).Append(" ");
if (i != 0 && st.Join != JoinType.Cross)
{
sqlTables.Append(" ON ").Append(st.OnExpression.ToSql(builder));
}
sqlTables.Append(" ");
i++;
}
//sqlSb.Append(" ");
string sqlWhere = "";
if (WhereExpr != null)
{
sqlWhere = " where ";
sqlWhere += WhereExpr.ToSql(builder);
}
StringBuilder sqlGroups = new StringBuilder();
if (GroupBys != null && GroupBys.Count > 0)
{
sqlGroups.Append(" group by ");
i = 0;
foreach (GroupByClause groupBy in GroupBys)
{
if (i != 0)
{
sqlGroups.Append(", ");
}
sqlGroups.Append(groupBy.ToSql(builder));
i++;
}
}
string orderby = "";
if (OrderBys != null && OrderBys.Count > 0)
{
foreach (var ob in OrderBys)
{
if (!string.IsNullOrEmpty(orderby))
{
orderby += ", ";
}
orderby += ob.ToSql(builder);
}
if (!string.IsNullOrEmpty(orderby))
{
orderby = " ORDER BY " + orderby;
}
}
string sql = "";
//вместе с ограничениями
if (builder.DbType == DriverType.SqlServer)
{
if (SkipRecords == 0)
{
return(string.Format("select {6}{5}{0} {1} {2} {3} {4}", sqlColumns.ToString(), sqlTables.ToString(), sqlWhere, sqlGroups.ToString(), orderby,
Distinct ? "distinct " : "",
LimitRecords > 0 ? "top " + LimitRecords.ToString() + " " : ""));
}
if (string.IsNullOrEmpty(orderby))
{
orderby = "ORDER BY (SELECT null)";
}
StringBuilder aliasesCol = new StringBuilder();
for (int i2 = 0; i2 < ColumnsLocal.Count; i2++)
{
if (i2 != 0)
{
aliasesCol.Append(", ");
}
aliasesCol.Append(builder.EncodeTable(ColumnsLocal[i2].InternalDbAlias));
}
sql = string.Format(@"
select {0} from
(
SELECT {6} {2}, Row_Number() OVER ({1}) as ""num19376194i9"" {3} {4} {5}
)as t2 where ", aliasesCol.ToString(), orderby, sqlColumns.ToString(), sqlTables.ToString(), sqlWhere, sqlGroups.ToString(), Distinct ? " distinct " : "");
sql += "\"num19376194i9\" > " + SkipRecords.ToString();
if (LimitRecords >= 0)
{
sql += " and \"num19376194i9\" <= " + (LimitRecords + SkipRecords).ToString();
}
}
if (builder.DbType == DriverType.PostgreSQL)
{
sql = string.Format("select {5} {0} {1} {2} {3} {4}", sqlColumns.ToString(), sqlTables.ToString(), sqlWhere, sqlGroups.ToString(), orderby, Distinct ? " distinct " : "");
if (SkipRecords > 0)
{
sql += " OFFSET " + SkipRecords.ToString();
}
if (LimitRecords >= 0)
{
sql += " LIMIT " + LimitRecords.ToString();
}
}
return(sql);
}
protected string ToMSSql(ExpressionSqlBuilder builder)
{
StringBuilder sb = new StringBuilder();
if (AlterType == AlterType.RenameTable)
{
var p1 = builder.SqlConstant(Table.ToSql(builder));
var p2 = builder.SqlConstant(NewName);
sb.AppendFormat("EXEC sp_rename {0}, {1}", p1, p2).Append(";");
}
if (AlterType == AlterType.RenameSchema)
{
sb.AppendFormat("ALTER SCHEMA {0} TRANSFER ", builder.EncodeTable(NewName)).Append(";");
sb.Append(Table.ToSql(builder));
}
if (AlterType == AlterType.RenameColumn)
{
string s = "";
s += Table.ToSql(builder);
var p1 = builder.SqlConstant(s);
var p2 = builder.SqlConstant(NewName);
sb.AppendFormat("EXEC sp_RENAME {0}, {1}, 'COLUMN'", p1, p2).Append(";");
}
if (AlterType == AlterType.AlterColumn)
{
if (AlterColumns.Count > 0)
{
sb.Append("ALTER TABLE ").Append(Table.ToSql(builder)).Append(" ");
var addCols = AlterColumns.Where(a => a.AlterColumn == AlterColumnType.AddColumn).ToList();
if (addCols.Count > 0)
{
sb.Append(" ADD ");
for (int i = 0; i < addCols.Count; i++)
{
var ac = addCols[i];
if (i > 0)
{
sb.Append(", ");
}
sb.Append(ac.ToSql(builder));
}
}
var dropCols = AlterColumns.Where(a => a.AlterColumn == AlterColumnType.DropColumn).ToList();
if (dropCols.Count > 0)
{
sb.Append(" DROP ");
for (int i = 0; i < dropCols.Count; i++)
{
var ac = dropCols[i];
if (i > 0)
{
sb.Append(", ");
}
sb.Append(ac.ToSql(builder));
}
}
var alterCols = AlterColumns.Where(a => a.AlterColumn == AlterColumnType.AlterColumn).ToList();
if (alterCols.Count > 0)
{
sb.Append(" ALTER COLUMN ");
for (int i = 0; i < alterCols.Count; i++)
{
var ac = alterCols[i];
if (i > 0)
{
sb.Append(", ");
}
sb.Append(ac.ToSql(builder));
}
}
sb.Append(";");
}
}
return(sb.ToString());
}
/// <summary>
/// Используется для:
/// insert into xx ([вот здесь])
/// update xx set [вот здесь]=..
/// select OVER ( ORDER BY [вот здесь] ) from ...
/// </summary>
public string ToSqlShort(ExpressionSqlBuilder builder)
{
return(builder.EncodeTable(PhysicalColumn.PhysicalName));
}
public override string ToSql(ExpressionSqlBuilder builder)
{
StringBuilder sb = new StringBuilder();
if (builder.DbType == DriverType.SqlServer)
{
if (IfNotExists)
{
var p = builder.SqlConstant(IndexName.ToSql(builder, true));
sb.Append(string.Format("IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = {0}) ", p));
}
sb.Append("CREATE");
if (Unique)
{
sb.Append(" UNIQUE");
}
sb.Append(" INDEX");
sb.Append(" ").Append(IndexName.ToSql(builder));
sb.Append(" ON ").Append(OnTable.ToSql(builder));
sb.Append(" (");
for (int i = 0; i < IndexColumns.Count; i++)
{
if (i > 0)
{
sb.Append(", ");
}
var ac = IndexColumns[i];
sb.Append(builder.EncodeTable(ac.Name));
if (ac.Sort == ParserCore.SortType.ASC)
{
sb.Append(" ASC");
}
else
{
sb.Append(" DESC");
}
}
sb.Append(");");
}
if (builder.DbType == DriverType.PostgreSQL)
{
string create = "CREATE";
if (Unique)
{
create += " UNIQUE";
}
create += (" INDEX");
create += " " + IndexName.ToSql(builder);
create += " ON " + OnTable.ToSql(builder);
create += " (";
for (int i = 0; i < IndexColumns.Count; i++)
{
if (i > 0)
{
create += ", ";
}
var ac = IndexColumns[i];
create += builder.EncodeTable(ac.Name);
if (ac.Sort == ParserCore.SortType.ASC)
{
create += " ASC";
}
else
{
create += " DESC";
}
}
create += ");";
if (!IfNotExists)
{
sb.Append(create);
}
else
{
string schema = "public";
if (!string.IsNullOrEmpty(OnTable.Schema))
{
schema = OnTable.Schema;
}
var schemaP = builder.SqlConstant(schema);
var tableP = builder.SqlConstant(OnTable.Name);
var indexP = builder.SqlConstant(IndexName);
string s = string.Format(@"
do
$$
declare
l_count integer;
begin
select count(*)
into l_count
from pg_indexes
where schemaname = {0}
and tablename = {1}
and indexname = {2};
if l_count = 0 then
{3}
end if;
end;
$$;", schemaP, tableP, indexP, create);
sb.Append(s);
}
}
return(sb.ToString());
}