public override string ToSql(ExpressionSqlBuilder builder)
{
string s = "";
if (builder.DbType == DriverType.SqlServer)
{
if (IfExists)
{
s = string.Format("IF EXISTS (SELECT name FROM sysindexes WHERE name = {0}) DROP INDEX {1}",
builder.SqlConstant(IndexName.ToSql(builder, true)),
IndexName.ToSql(builder));
}
else
{
s = "DROP INDEX " + IndexName.ToSql(builder, true);
}
}
if (builder.DbType == DriverType.PostgreSQL)
{
s = "DROP INDEX ";
if (IfExists)
{
s += "IF EXISTS ";
}
s += IndexName.ToSql(builder);
}
return(s);
}
public override string ToSql(ExpressionSqlBuilder builder)
{
string s = "";
if (builder.DbType == DriverType.SqlServer)
{
if (IfExists)
{
s = string.Format("IF OBJECT_ID({0}, 'U') IS NOT NULL DROP TABLE {1}", builder.SqlConstant(Table.ToSql(builder)), Table.ToSql(builder));
}
else
{
s = "DROP TABLE " + Table.ToSql(builder);
}
}
if (builder.DbType == DriverType.PostgreSQL)
{
s = "DROP TABLE ";
if (IfExists)
{
s += "IF EXISTS ";
}
s += Table.ToSql(builder);
}
return(s);
}
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());
}
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());
}