/// <summary>
/// This is used to generate a delete query that uses a sub-query to select the data, it is required because there's a very particular syntax that
/// needs to be used to work for all servers: MySql, SQLCE and MSSQL
/// </summary>
/// <returns></returns>
/// <remarks>
/// See: http://issues.umbraco.org/issue/U4-3876
/// </remarks>
public static Sql GetDeleteSubquery(this ISqlSyntaxProvider sqlProvider, string tableName, string columnName, Sql subQuery)
{
return(new Sql(string.Format(@"DELETE FROM {0} WHERE {1} IN (SELECT {1} FROM ({2}) x)",
sqlProvider.GetQuotedTableName(tableName),
sqlProvider.GetQuotedColumnName(columnName),
subQuery.SQL), subQuery.Arguments));
}
public static Sql From <T>(this Sql sql, ISqlSyntaxProvider sqlSyntax)
{
var type = typeof(T);
var tableName = type.GetTableName();
return(sql.From(sqlSyntax.GetQuotedTableName(tableName)));
}
public static Sql.SqlJoinClause RightJoin <T>(this Sql sql, ISqlSyntaxProvider sqlSyntax)
{
var type = typeof(T);
var tableName = type.GetTableName();
return(sql.RightJoin(sqlSyntax.GetQuotedTableName(tableName)));
}
public static Sql From <T>(this Sql sql, ISqlSyntaxProvider sqlSyntax)
{
var type = typeof(T);
var tableNameAttribute = type.FirstAttribute <TableNameAttribute>();
string tableName = tableNameAttribute == null ? string.Empty : tableNameAttribute.Value;
return(sql.From(sqlSyntax.GetQuotedTableName(tableName)));
}
public static void TruncateTable(this IDatabase db, ISqlSyntaxProvider sqlSyntax, string tableName)
{
var sql = new Sql(string.Format(
sqlSyntax.TruncateTable,
sqlSyntax.GetQuotedTableName(tableName)));
db.Execute(sql);
}
private static string[] GetFieldNames <T>(ISqlSyntaxProvider sqlSyntax, params Expression <Func <T, object> >[] fields)
{
if (fields.Length == 0)
{
return(new[] { string.Format("{0}.*", sqlSyntax.GetQuotedTableName(typeof(T).GetTableName())) });
}
return(fields.Select(field => GetFieldName(field, sqlSyntax)).ToArray());
}
private static string GetFieldName <T>(Expression <Func <T, object> > fieldSelector, ISqlSyntaxProvider sqlSyntax)
{
var field = ExpressionHelper.FindProperty(fieldSelector) as PropertyInfo;
var fieldName = field.GetColumnName();
var type = typeof(T);
var tableName = type.GetTableName();
return(sqlSyntax.GetQuotedTableName(tableName) + "." + sqlSyntax.GetQuotedColumnName(fieldName));
}
/// <summary>
/// Gets a quoted table and field name.
/// </summary>
/// <typeparam name="TDto">The type of the DTO.</typeparam>
/// <param name="sqlSyntax">An <see cref="ISqlSyntaxProvider"/>.</param>
/// <param name="fieldSelector">An expression specifying the field.</param>
/// <param name="tableAlias">An optional table alias.</param>
/// <returns></returns>
public static string GetFieldName <TDto>(this ISqlSyntaxProvider sqlSyntax, Expression <Func <TDto, object> > fieldSelector, string tableAlias = null)
{
var field = ExpressionHelper.FindProperty(fieldSelector).Item1 as PropertyInfo;
var fieldName = field.GetColumnName();
var type = typeof(TDto);
var tableName = tableAlias ?? type.GetTableName();
return(sqlSyntax.GetQuotedTableName(tableName) + "." + sqlSyntax.GetQuotedColumnName(fieldName));
}
internal virtual string GetColumnName(ISqlSyntaxProvider sqlSyntax, Type dtoType, PropertyInfo dtoProperty)
{
var tableNameAttribute = dtoType.FirstAttribute <TableNameAttribute>();
var tableName = tableNameAttribute.Value;
var columnAttribute = dtoProperty.FirstAttribute <ColumnAttribute>();
var columnName = columnAttribute.Name;
var columnMap = sqlSyntax.GetQuotedTableName(tableName) + "." + sqlSyntax.GetQuotedColumnName(columnName);
return(columnMap);
}
public static Sql On <TLeft, TRight>(this Sql.SqlJoinClause sql, ISqlSyntaxProvider sqlSyntax, Expression <Func <TLeft, object> > leftMember,
Expression <Func <TRight, object> > rightMember, params object[] args)
{
var leftType = typeof(TLeft);
var rightType = typeof(TRight);
var leftTableName = leftType.FirstAttribute <TableNameAttribute>().Value;
var rightTableName = rightType.FirstAttribute <TableNameAttribute>().Value;
var left = ExpressionHelper.FindProperty(leftMember) as PropertyInfo;
var right = ExpressionHelper.FindProperty(rightMember) as PropertyInfo;
var leftColumnName = left.FirstAttribute <ColumnAttribute>().Name;
var rightColumnName = right.FirstAttribute <ColumnAttribute>().Name;
string onClause = string.Format("{0}.{1} = {2}.{3}",
sqlSyntax.GetQuotedTableName(leftTableName),
sqlSyntax.GetQuotedColumnName(leftColumnName),
sqlSyntax.GetQuotedTableName(rightTableName),
sqlSyntax.GetQuotedColumnName(rightColumnName));
return(sql.On(onClause));
}
/// <summary>
/// This is used to generate a delete query that uses a sub-query to select the data, it is required because there's a
/// very particular syntax that
/// needs to be used to work for all servers
/// </summary>
/// <returns></returns>
/// <remarks>
/// See: http://issues.umbraco.org/issue/U4-3876
/// </remarks>
public static Sql GetDeleteSubquery(this ISqlSyntaxProvider sqlProvider, string tableName, string columnName, Sql subQuery, WhereInType whereInType = WhereInType.In) =>
// TODO: This is no longer necessary since this used to be a specific requirement for MySql!
// Now we can do a Delete<T> + sub query, see RelationRepository.DeleteByParent for example
new Sql(
string.Format(
whereInType == WhereInType.In
? @"DELETE FROM {0} WHERE {1} IN (SELECT {1} FROM ({2}) x)"
: @"DELETE FROM {0} WHERE {1} NOT IN (SELECT {1} FROM ({2}) x)",
sqlProvider.GetQuotedTableName(tableName),
sqlProvider.GetQuotedColumnName(columnName),
subQuery.SQL), subQuery.Arguments);
internal virtual string GetColumnName(Type dtoType, PropertyInfo dtoProperty)
{
var tableNameAttribute = dtoType.FirstAttribute <TableNameAttribute>();
string tableName = tableNameAttribute.Value;
var columnAttribute = dtoProperty.FirstAttribute <ColumnAttribute>();
string columnName = columnAttribute.Name;
string columnMap = string.Format("{0}.{1}",
_sqlSyntax.GetQuotedTableName(tableName),
_sqlSyntax.GetQuotedColumnName(columnName));
return(columnMap);
}
public static Sql OrderByDescending <TColumn>(this Sql sql, Expression <Func <TColumn, object> > columnMember, ISqlSyntaxProvider sqlSyntax)
{
var column = ExpressionHelper.FindProperty(columnMember) as PropertyInfo;
var columnName = column.FirstAttribute <ColumnAttribute>().Name;
var type = typeof(TColumn);
var tableNameAttribute = type.FirstAttribute <TableNameAttribute>();
string tableName = tableNameAttribute == null ? string.Empty : tableNameAttribute.Value;
var syntax = string.Format("{0}.{1} DESC",
sqlSyntax.GetQuotedTableName(tableName),
sqlSyntax.GetQuotedColumnName(columnName));
return(sql.OrderBy(syntax));
}
public static Sql OrderByDescending <TColumn>(this Sql sql, Expression <Func <TColumn, object> > columnMember, ISqlSyntaxProvider sqlSyntax)
{
var column = ExpressionHelper.FindProperty(columnMember) as PropertyInfo;
var columnName = column.GetColumnName();
var type = typeof(TColumn);
var tableName = type.GetTableName();
//need to ensure the order by is in brackets, see: https://github.com/toptensoftware/PetaPoco/issues/177
var syntax = string.Format("({0}.{1}) DESC",
sqlSyntax.GetQuotedTableName(tableName),
sqlSyntax.GetQuotedColumnName(columnName));
return(sql.OrderBy(syntax));
}
public static Sql OrderBy <TColumn>(this Sql sql, Expression <Func <TColumn, object> > columnMember, ISqlSyntaxProvider sqlSyntax)
{
var column = ExpressionHelper.FindProperty(columnMember) as PropertyInfo;
var columnName = column.FirstAttribute <ColumnAttribute>().Name;
var type = typeof(TColumn);
var tableNameAttribute = type.FirstAttribute <TableNameAttribute>();
string tableName = tableNameAttribute == null ? string.Empty : tableNameAttribute.Value;
//need to ensure the order by is in brackets, see: https://github.com/toptensoftware/PetaPoco/issues/177
var syntax = string.Format("({0}.{1})",
sqlSyntax.GetQuotedTableName(tableName),
sqlSyntax.GetQuotedColumnName(columnName));
return(sql.OrderBy(syntax));
}
/// <summary>
/// Checks the cmsContentXml table to see if the number of entries for media matches the number of media entities
/// </summary>
/// <returns></returns>
/// <remarks>
/// Further to just counting the data integrity, this also checks if the XML stored in the cmsContentXml table contains the correct
/// GUID identifier. In older versions of Umbraco, the GUID is not persisted to the content cache, that will cause problems in newer
/// versions of Umbraco, so the xml table would need a rebuild.
/// </remarks>
private HealthCheckStatus CheckMedia()
{
var total = _services.MediaService.CountNotTrashed();
var mediaObjectType = Guid.Parse(Constants.ObjectTypes.Media);
//count entries
var countTotalSubQuery = new Sql()
.Select("Count(*)")
.From <ContentXmlDto>(_sqlSyntax)
.InnerJoin <NodeDto>(_sqlSyntax)
.On <ContentXmlDto, NodeDto>(_sqlSyntax, left => left.NodeId, right => right.NodeId)
.Where <NodeDto>(dto => dto.NodeObjectType == mediaObjectType);
var totalXml = _database.ExecuteScalar <int>(countTotalSubQuery);
//count invalid entries
var countNonGuidQuery = new Sql()
.Select("Count(*)")
.From <ContentXmlDto>(_sqlSyntax)
.InnerJoin <NodeDto>(_sqlSyntax)
.On <ContentXmlDto, NodeDto>(_sqlSyntax, left => left.NodeId, right => right.NodeId)
.Where <NodeDto>(dto => dto.NodeObjectType == mediaObjectType)
.Where(string.Format("{0}.{1} NOT LIKE '% key=\"%'", _sqlSyntax.GetQuotedTableName("cmsContentXml"), _sqlSyntax.GetQuotedColumnName("xml")));
var totalNonGuidXml = _database.ExecuteScalar <int>(countNonGuidQuery);
var hasError = false;
var actions = new List <HealthCheckAction>();
if (totalXml != total || totalNonGuidXml > 0)
{
//if the counts don't match
actions.Add(new HealthCheckAction(CheckMediaXmlTableAction, Id));
hasError = true;
}
return(new HealthCheckStatus(_textService.Localize("healthcheck/xmlDataIntegrityCheckMedia",
new[] { totalXml.ToString(), total.ToString(), totalNonGuidXml.ToString() }))
{
ResultType = hasError == false
? StatusResultType.Success
: StatusResultType.Error,
Actions = actions
});
}
// fixme: TSource is used for nothing
protected void DefineMap <TSource, TTarget>(string sourceName, string targetName)
{
if (_sqlSyntax == null)
{
throw new InvalidOperationException("Do not define maps outside of DefineMaps.");
}
var targetType = typeof(TTarget);
// TODO ensure that sourceName is a valid sourceType property (but, slow?)
var tableNameAttribute = targetType.FirstAttribute <TableNameAttribute>();
if (tableNameAttribute == null)
{
throw new InvalidOperationException($"Type {targetType.FullName} is not marked with a TableName attribute.");
}
var tableName = tableNameAttribute.Value;
// TODO maybe get all properties once and then index them
var targetProperty = targetType.GetProperty(targetName);
if (targetProperty == null)
{
throw new InvalidOperationException($"Type {targetType.FullName} does not have a property named {targetName}.");
}
var columnAttribute = targetProperty.FirstAttribute <ColumnAttribute>();
if (columnAttribute == null)
{
throw new InvalidOperationException($"Property {targetType.FullName}.{targetName} is not marked with a Column attribute.");
}
var columnName = columnAttribute.Name;
var columnMap = _sqlSyntax.GetQuotedTableName(tableName) + "." + _sqlSyntax.GetQuotedColumnName(columnName);
var mapperMaps = _maps.GetOrAdd(GetType(), type => new ConcurrentDictionary <string, string>());
mapperMaps[sourceName] = columnMap;
}
/// <summary>
/// Returns the quotes tableName.columnName combo
/// </summary>
/// <param name="sql"></param>
/// <param name="tableName"></param>
/// <param name="columnName"></param>
/// <returns></returns>
public static string GetQuotedColumn(this ISqlSyntaxProvider sql, string tableName, string columnName)
{
return(sql.GetQuotedTableName(tableName) + "." + sql.GetQuotedColumnName(columnName));
}
public void CreateTable(bool overwrite, Type modelType)
{
var tableDefinition = DefinitionFactory.GetTableDefinition(modelType);
var tableName = tableDefinition.Name;
string createSql = _syntaxProvider.Format(tableDefinition);
string createPrimaryKeySql = _syntaxProvider.FormatPrimaryKey(tableDefinition);
var foreignSql = _syntaxProvider.Format(tableDefinition.ForeignKeys);
var indexSql = _syntaxProvider.Format(tableDefinition.Indexes);
var tableExist = _db.TableExist(tableName);
if (overwrite && tableExist)
{
_db.DropTable(tableName);
tableExist = false;
}
if (tableExist == false)
{
using (var transaction = _db.GetTransaction())
{
//Execute the Create Table sql
int created = _db.Execute(new Sql(createSql));
_logger.Info <Database>(string.Format("Create Table sql {0}:\n {1}", created, createSql));
//If any statements exists for the primary key execute them here
if (!string.IsNullOrEmpty(createPrimaryKeySql))
{
int createdPk = _db.Execute(new Sql(createPrimaryKeySql));
_logger.Info <Database>(string.Format("Primary Key sql {0}:\n {1}", createdPk, createPrimaryKeySql));
}
//Turn on identity insert if db provider is not mysql
if (_syntaxProvider.SupportsIdentityInsert() && tableDefinition.Columns.Any(x => x.IsIdentity))
{
_db.Execute(new Sql(string.Format("SET IDENTITY_INSERT {0} ON ", _syntaxProvider.GetQuotedTableName(tableName))));
}
//Call the NewTable-event to trigger the insert of base/default data
//OnNewTable(tableName, _db, e, _logger);
_baseDataCreation.InitializeBaseData(tableName);
//Turn off identity insert if db provider is not mysql
if (_syntaxProvider.SupportsIdentityInsert() && tableDefinition.Columns.Any(x => x.IsIdentity))
{
_db.Execute(new Sql(string.Format("SET IDENTITY_INSERT {0} OFF;", _syntaxProvider.GetQuotedTableName(tableName))));
}
//Special case for MySql
if (_syntaxProvider is MySqlSyntaxProvider && tableName.Equals("umbracoUser"))
{
_db.Update <UserDto>("SET id = @IdAfter WHERE id = @IdBefore AND userLogin = @Login", new { IdAfter = 0, IdBefore = 1, Login = "******" });
}
//Loop through index statements and execute sql
foreach (var sql in indexSql)
{
int createdIndex = _db.Execute(new Sql(sql));
_logger.Info <Database>(string.Format("Create Index sql {0}:\n {1}", createdIndex, sql));
}
//Loop through foreignkey statements and execute sql
foreach (var sql in foreignSql)
{
int createdFk = _db.Execute(new Sql(sql));
_logger.Info <Database>(string.Format("Create Foreign Key sql {0}:\n {1}", createdFk, sql));
}
transaction.Complete();
}
}
_logger.Info <Database>(string.Format("New table '{0}' was created", tableName));
}
