/// <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)); }