protected new void ReplaceColumn <T>(string tableName, string currentName, string newName)
{
if (DatabaseType.IsSqlCe())
{
AddColumn <T>(tableName, newName, out var sqls);
Execute.Sql($"UPDATE {SqlSyntax.GetQuotedTableName(tableName)} SET {SqlSyntax.GetQuotedColumnName(newName)}={SqlSyntax.GetQuotedColumnName(currentName)}").Do();
foreach (var sql in sqls)
{
Execute.Sql(sql).Do();
}
Delete.Column(currentName).FromTable(tableName).Do();
}
else
{
// TODO: Adding column just to get the SQL to execute after the rename
AddColumn <T>(tableName, newName, out var sqls);
Delete.Column(currentName).FromTable(tableName).Do();
Execute.Sql(SqlSyntax.FormatColumnRename(tableName, currentName, newName)).Do();
foreach (var sql in sqls)
{
Execute.Sql(sql).Do();
}
}
}
public override string ToString()
{
var deleteItems = new List <string>();
if (IsAllRows)
{
deleteItems.Add(string.Format(SqlSyntax.DeleteData, SqlSyntax.GetQuotedTableName(TableName), "1 = 1"));
}
else
{
foreach (var row in Rows)
{
var whereClauses = new List <string>();
foreach (KeyValuePair <string, object> item in row)
{
whereClauses.Add(string.Format("{0} {1} {2}",
SqlSyntax.GetQuotedColumnName(item.Key),
item.Value == null ? "IS" : "=",
GetQuotedValue(item.Value)));
}
deleteItems.Add(string.Format(SqlSyntax.DeleteData,
SqlSyntax.GetQuotedTableName(TableName),
String.Join(" AND ", whereClauses.ToArray())));
}
}
return(String.Join("; ", deleteItems.ToArray()));
}
protected override Sql GetBaseQuery(bool isCount)
{
var sqlx = string.Format("LEFT OUTER JOIN {0} {1} ON ({1}.{2}={0}.{2} AND {1}.{3}=1)",
SqlSyntax.GetQuotedTableName("cmsDocument"),
SqlSyntax.GetQuotedTableName("cmsDocument2"),
SqlSyntax.GetQuotedColumnName("nodeId"),
SqlSyntax.GetQuotedColumnName("published"));
var sql = new Sql();
sql.Select(isCount ? "COUNT(*)" : "*")
.From <DocumentDto>()
.InnerJoin <ContentVersionDto>()
.On <DocumentDto, ContentVersionDto>(left => left.VersionId, right => right.VersionId)
.InnerJoin <ContentDto>()
.On <ContentVersionDto, ContentDto>(left => left.NodeId, right => right.NodeId)
.InnerJoin <NodeDto>()
.On <ContentDto, NodeDto>(left => left.NodeId, right => right.NodeId)
// cannot do this because PetaPoco does not know how to alias the table
//.LeftOuterJoin<DocumentPublishedReadOnlyDto>()
//.On<DocumentDto, DocumentPublishedReadOnlyDto>(left => left.NodeId, right => right.NodeId)
// so have to rely on writing our own SQL
.Append(sqlx /*, new { @published = true }*/)
.Where <NodeDto>(x => x.NodeObjectType == NodeObjectTypeId);
return(sql);
}
private string GetMySql()
{
return(string.Format(SqlSyntax.DeleteConstraint,
SqlSyntax.GetQuotedTableName(Constraint.TableName),
Constraint.IsPrimaryKeyConstraint ? "PRIMARY KEY" : "FOREIGN KEY",
""));
}
public override string ToString()
{
// Test for MySQL primary key situation.
if (CurrentDatabaseProvider == DatabaseProviders.MySql)
{
if (Constraint.IsPrimaryKeyConstraint)
{
return(string.Format(SqlSyntax.DeleteConstraint,
SqlSyntax.GetQuotedTableName(Constraint.TableName),
"PRIMARY KEY",
""));
}
else
{
return(string.Format(SqlSyntax.DeleteConstraint,
SqlSyntax.GetQuotedTableName(Constraint.TableName),
"FOREIGN KEY",
""));
}
}
else
{
return(string.Format(SqlSyntax.DeleteConstraint,
SqlSyntax.GetQuotedTableName(Constraint.TableName),
SqlSyntax.GetQuotedName(Constraint.ConstraintName)));
}
}
public override string ToString()
{
if (IsExpressionSupported() == false)
{
return(string.Empty);
}
if (ForeignKey.ForeignTable == null)
{
throw new ArgumentNullException("Table name not specified, ensure you have appended the OnTable extension. Format should be Delete.ForeignKey(KeyName).OnTable(TableName)");
}
if (CurrentDatabaseProvider == DatabaseProviders.MySql)
{
//MySql naming "convention" for foreignkeys, which aren't explicitly named
if (string.IsNullOrEmpty(ForeignKey.Name))
{
ForeignKey.Name = string.Format("{0}_ibfk_1", ForeignKey.ForeignTable.ToLower());
}
return(string.Format(SqlSyntax.DeleteConstraint,
SqlSyntax.GetQuotedTableName(ForeignKey.ForeignTable),
"FOREIGN KEY",
SqlSyntax.GetQuotedName(ForeignKey.Name)));
}
if (string.IsNullOrEmpty(ForeignKey.Name))
{
ForeignKey.Name = string.Format("FK_{0}_{1}_{2}", ForeignKey.ForeignTable, ForeignKey.PrimaryTable, ForeignKey.PrimaryColumns.First());
}
return(string.Format(SqlSyntax.DeleteConstraint,
SqlSyntax.GetQuotedTableName(ForeignKey.ForeignTable),
SqlSyntax.GetQuotedName(ForeignKey.Name)));
}
public override string ToString()
{
if (IsExpressionSupported() == false)
return string.Empty;
var updateItems = new List<string>();
var whereClauses = new List<string>();
foreach (var item in Set)
{
updateItems.Add(string.Format("{0} = {1}",
SqlSyntax.GetQuotedColumnName(item.Key),
GetQuotedValue(item.Value)));
}
if (IsAllRows)
{
whereClauses.Add("1 = 1");
}
else
{
foreach (var item in Where)
{
whereClauses.Add(string.Format("{0} {1} {2}",
SqlSyntax.GetQuotedColumnName(item.Key),
item.Value == null ? "IS" : "=",
GetQuotedValue(item.Value)));
}
}
return string.Format(SqlSyntax.UpdateData,
SqlSyntax.GetQuotedTableName(TableName),
string.Join(", ", updateItems.ToArray()),
string.Join(" AND ", whereClauses.ToArray()));
}
protected override string GetSql() =>
// NOTE Should probably investigate if Deleting a Default Constraint is different from deleting a 'regular' constraint
string.Format(
SqlSyntax.DeleteConstraint,
SqlSyntax.GetQuotedTableName(TableName),
SqlSyntax.GetQuotedName(ConstraintName));
protected override string GetSql()
{
var constraintType = Constraint.IsPrimaryKeyConstraint ? "PRIMARY KEY" : "UNIQUE";
if (Constraint.IsPrimaryKeyConstraint && SqlSyntax.SupportsClustered())
{
constraintType += Constraint.IsPrimaryKeyClustered ? " CLUSTERED" : " NONCLUSTERED";
}
if (Constraint.IsNonUniqueConstraint)
{
constraintType = string.Empty;
}
var columns = new string[Constraint.Columns.Count];
for (var i = 0; i < Constraint.Columns.Count; i++)
{
columns[i] = SqlSyntax.GetQuotedColumnName(Constraint.Columns.ElementAt(i));
}
return(string.Format(
SqlSyntax.CreateConstraint,
SqlSyntax.GetQuotedTableName(Constraint.TableName),
SqlSyntax.GetQuotedName(Constraint.ConstraintName),
constraintType,
string.Join(", ", columns)));
}
protected override string GetSql() =>
HasDefaultConstraint
? string.Format(
SqlSyntax.DeleteDefaultConstraint,
SqlSyntax.GetQuotedTableName(TableName),
SqlSyntax.GetQuotedColumnName(ColumnName),
SqlSyntax.GetQuotedName(ConstraintName))
: string.Empty;
protected virtual string GetFieldName(PocoData pocoData, string name, string alias)
{
var column = pocoData.Columns.FirstOrDefault(x => x.Value.MemberInfoData.Name == name);
var tableName = SqlSyntax.GetQuotedTableName(alias ?? pocoData.TableInfo.TableName);
var columnName = SqlSyntax.GetQuotedColumnName(column.Value.ColumnName);
return(tableName + "." + columnName);
}
protected virtual string GetFieldName(Database.PocoData pocoData, string name)
{
var column = pocoData.Columns.FirstOrDefault(x => x.Value.PropertyInfo.Name == name);
return(string.Format("{0}.{1}",
SqlSyntax.GetQuotedTableName(pocoData.TableInfo.TableName),
SqlSyntax.GetQuotedColumnName(column.Value.ColumnName)));
}
public override string ToString()
{
//NOTE Should probably investigate if Deleting a Default Constraint is different from deleting a 'regular' constraint
return(string.Format(SqlSyntax.DeleteConstraint,
SqlSyntax.GetQuotedTableName(TableName),
SqlSyntax.GetQuotedName(ConstraintName)));
}
protected override string GetSql()
{
return(DatabaseType.IsMySql()
? GetMySql()
: string.Format(SqlSyntax.DeleteConstraint,
SqlSyntax.GetQuotedTableName(Constraint.TableName),
SqlSyntax.GetQuotedName(Constraint.ConstraintName)));
}
public override string ToString()
{
if (IsExpressionSupported() == false)
{
return(string.Empty);
}
var sb = new StringBuilder();
if (EnabledIdentityInsert && SqlSyntax.SupportsIdentityInsert())
{
sb.AppendLine(string.Format("SET IDENTITY_INSERT {0} ON;", SqlSyntax.GetQuotedTableName(TableName)));
if (CurrentDatabaseProvider == DatabaseProviders.SqlServer || CurrentDatabaseProvider == DatabaseProviders.SqlServerCE)
{
sb.AppendLine("GO");
}
}
try
{
foreach (var item in Rows)
{
var cols = "";
var vals = "";
foreach (var keyVal in item)
{
cols += SqlSyntax.GetQuotedColumnName(keyVal.Key) + ",";
vals += GetQuotedValue(keyVal.Value) + ",";
}
cols = cols.TrimEnd(',');
vals = vals.TrimEnd(',');
var sql = string.Format(SqlSyntax.InsertData,
SqlSyntax.GetQuotedTableName(TableName),
cols, vals);
sb.AppendLine(string.Format("{0};", sql));
if (CurrentDatabaseProvider == DatabaseProviders.SqlServer || CurrentDatabaseProvider == DatabaseProviders.SqlServerCE)
{
sb.AppendLine("GO");
}
}
}
finally
{
if (EnabledIdentityInsert && SqlSyntax.SupportsIdentityInsert())
{
sb.AppendLine(string.Format("SET IDENTITY_INSERT {0} OFF;", SqlSyntax.GetQuotedTableName(TableName)));
if (CurrentDatabaseProvider == DatabaseProviders.SqlServer || CurrentDatabaseProvider == DatabaseProviders.SqlServerCE)
{
sb.AppendLine("GO");
}
}
}
return(sb.ToString());
}
/// <inheritdoc />
public void ClearCurrent(string source, string context, string action)
{
var sql = new Sql($@"UPDATE {SqlSyntax.GetQuotedTableName(ConsentDto.TableName)}
SET {SqlSyntax.GetQuotedColumnName("current")} = @0
WHERE {SqlSyntax.GetQuotedColumnName("source")} = @1 AND {SqlSyntax.GetQuotedColumnName("context")} = @2 AND {SqlSyntax.GetQuotedColumnName("action")} = @3
AND {SqlSyntax.GetQuotedColumnName("current")} <> @0 ", false, source, context, action);
Database.Execute(sql);
}
protected void ReplaceColumn <T>(string tableName, string currentName, string newName)
{
AddColumn <T>(tableName, newName, out var sqls);
Execute.Sql($"UPDATE {SqlSyntax.GetQuotedTableName(tableName)} SET {SqlSyntax.GetQuotedColumnName(newName)}={SqlSyntax.GetQuotedColumnName(currentName)}").Do();
foreach (var sql in sqls)
{
Execute.Sql(sql).Do();
}
Delete.Column(currentName).FromTable(tableName).Do();
}
protected override string GetSql()
{
var stmts = new StringBuilder();
foreach (var columnName in ColumnNames)
{
stmts.AppendFormat(SqlSyntax.DropColumn, SqlSyntax.GetQuotedTableName(TableName), SqlSyntax.GetQuotedColumnName(columnName));
AppendStatementSeparator(stmts);
}
return(stmts.ToString());
}
protected override string GetSql()
{
if (string.IsNullOrEmpty(Column.TableName))
{
Column.TableName = TableName;
}
return(string.Format(SqlSyntax.AddColumn,
SqlSyntax.GetQuotedTableName(Column.TableName),
SqlSyntax.Format(Column)));
}
protected void AlterColumn <T>(string tableName, string columnName)
{
var table = DefinitionFactory.GetTableDefinition(typeof(T), SqlSyntax);
var column = table.Columns.First(x => x.Name == columnName);
SqlSyntax.Format(column, SqlSyntax.GetQuotedTableName(tableName), out var sqls);
foreach (var sql in sqls)
{
Execute.Sql(sql).Do();
}
}
private void AddColumn(TableDefinition table, string tableName, string columnName)
{
if (ColumnExists(tableName, columnName))
{
return;
}
var column = table.Columns.First(x => x.Name == columnName);
var createSql = SqlSyntax.Format(column);
Execute.Sql(string.Format(SqlSyntax.AddColumn, SqlSyntax.GetQuotedTableName(tableName), createSql)).Do();
}
public IEnumerable <IRedirectUrl> SearchUrls(string searchTerm, long pageIndex, int pageSize, out long total)
{
var sql = GetBaseQuery(false)
.Where(string.Format("{0}.{1} LIKE @url", SqlSyntax.GetQuotedTableName("umbracoRedirectUrl"), SqlSyntax.GetQuotedColumnName("Url")), new { url = "%" + searchTerm.Trim().ToLowerInvariant() + "%" })
.OrderByDescending <RedirectUrlDto>(x => x.CreateDateUtc);
var result = Database.Page <RedirectUrlDto>(pageIndex + 1, pageSize, sql);
total = Convert.ToInt32(result.TotalItems);
var rules = result.Items.Select(Map);
return(rules);
}
public IEnumerable <IRedirectUrl> GetAllUrls(int rootContentId, long pageIndex, int pageSize, out long total)
{
var sql = GetBaseQuery(false)
.Where(string.Format("{0}.{1} LIKE @path", SqlSyntax.GetQuotedTableName("umbracoNode"), SqlSyntax.GetQuotedColumnName("path")), new { path = "%," + rootContentId + ",%" })
.OrderByDescending <RedirectUrlDto>(x => x.CreateDateUtc);
var result = Database.Page <RedirectUrlDto>(pageIndex + 1, pageSize, sql);
total = Convert.ToInt32(result.TotalItems);
var rules = result.Items.Select(Map);
return(rules);
}
private void AddColumn(TableDefinition table, string tableName, string columnName, out IEnumerable <string> sqls)
{
if (ColumnExists(tableName, columnName))
{
sqls = Enumerable.Empty <string>();
return;
}
var column = table.Columns.First(x => x.Name == columnName);
var createSql = SqlSyntax.Format(column, SqlSyntax.GetQuotedTableName(tableName), out sqls);
Execute.Sql(string.Format(SqlSyntax.AddColumn, SqlSyntax.GetQuotedTableName(tableName), createSql)).Do();
}
private string GetPagedResultsByQueryWhere()
{
if (_pagedResultsByQueryWhere == null)
{
_pagedResultsByQueryWhere = " AND ("
+ $"({SqlSyntax.GetQuotedTableName("umbracoNode")}.{SqlSyntax.GetQuotedColumnName("text")} LIKE @0)"
+ " OR "
+ $"({SqlSyntax.GetQuotedTableName("cmsMember")}.{SqlSyntax.GetQuotedColumnName("LoginName")} LIKE @0)"
+ ")";
}
return(_pagedResultsByQueryWhere);
}
public override void Migrate()
{
// cannot delete the column without this, of course
Delete.KeysAndIndexes().Do();
Delete.Column("id").FromTable("umbracoUser").Do();
var table = DefinitionFactory.GetTableDefinition(typeof(UserDto), SqlSyntax);
var column = table.Columns.First(x => x.Name == "id");
var create = SqlSyntax.Format(column); // returns [id] INTEGER NOT NULL IDENTITY(1060,1)
Database.Execute($"ALTER TABLE {SqlSyntax.GetQuotedTableName("umbracoUser")} ADD " + create);
}
private string GetMySql()
{
// MySql naming "convention" for foreignkeys, which aren't explicitly named
if (string.IsNullOrEmpty(ForeignKey.Name))
{
ForeignKey.Name = $"{ForeignKey.ForeignTable.ToLower()}_ibfk_1";
}
return(string.Format(SqlSyntax.DeleteConstraint,
SqlSyntax.GetQuotedTableName(ForeignKey.ForeignTable),
"FOREIGN KEY",
SqlSyntax.GetQuotedName(ForeignKey.Name)));
}
public override string ToString()
{
//string columnNameFormat = string.Format("{0} {1}",
// SqlSyntaxContext.SqlSyntaxProvider.GetQuotedColumnName(Column.Name),
// SqlSyntaxContext.SqlSyntaxProvider.Format(Column));
return(string.Format(SqlSyntax.AlterColumn,
SqlSyntax.GetQuotedTableName(TableName),
SqlSyntax.Format(Column)));
//return string.Format(SqlSyntaxContext.SqlSyntaxProvider.AlterColumn,
// SqlSyntaxContext.SqlSyntaxProvider.GetQuotedTableName(TableName),
// SqlSyntaxContext.SqlSyntaxProvider.GetQuotedColumnName(Column.Name));
}
protected override string GetSql()
{
var stmts = new StringBuilder();
if (EnabledIdentityInsert && SqlSyntax.SupportsIdentityInsert())
{
stmts.AppendLine($"SET IDENTITY_INSERT {SqlSyntax.GetQuotedTableName(TableName)} ON");
AppendStatementSeparator(stmts);
}
try
{
foreach (var item in Rows)
{
var cols = new StringBuilder();
var vals = new StringBuilder();
var first = true;
foreach (var keyVal in item)
{
if (first)
{
first = false;
}
else
{
cols.Append(",");
vals.Append(",");
}
cols.Append(SqlSyntax.GetQuotedColumnName(keyVal.Key));
vals.Append(GetQuotedValue(keyVal.Value));
}
var sql = string.Format(SqlSyntax.InsertData, SqlSyntax.GetQuotedTableName(TableName), cols, vals);
stmts.Append(sql);
AppendStatementSeparator(stmts);
}
}
finally
{
if (EnabledIdentityInsert && SqlSyntax.SupportsIdentityInsert())
{
stmts.AppendLine($"SET IDENTITY_INSERT {SqlSyntax.GetQuotedTableName(TableName)} OFF");
AppendStatementSeparator(stmts);
}
}
return(stmts.ToString());
}
protected override string GetSql()
{
if (ForeignKey.ForeignTable == null)
{
throw new ArgumentNullException("Table name not specified, ensure you have appended the OnTable extension. Format should be Delete.ForeignKey(KeyName).OnTable(TableName)");
}
if (string.IsNullOrEmpty(ForeignKey.Name))
{
ForeignKey.Name = $"FK_{ForeignKey.ForeignTable}_{ForeignKey.PrimaryTable}_{ForeignKey.PrimaryColumns.First()}";
}
return(string.Format(SqlSyntax.DeleteConstraint,
SqlSyntax.GetQuotedTableName(ForeignKey.ForeignTable),
SqlSyntax.GetQuotedName(ForeignKey.Name)));
}
