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