internal override SqlStatement VisitUpdate(SqlUpdate update) {
bool saveMakeUnique = this.makeUnique;
this.makeUnique = false;
bool saveUseMappedNames = this.useMappedNames;
this.useMappedNames = true;
SqlStatement stmt = base.VisitUpdate(update);
this.makeUnique = saveMakeUnique;
this.useMappedNames = saveUseMappedNames;
return stmt;
}
protected override SqlExpression VisitUpdate(SqlUpdate expression)
{
_writer.WriteStartUpdate();
Visit(expression.Table);
_writer.WriteStartSet();
foreach (var value in expression.Values)
{
Visit(value.Column);
Visit(value.Value);
}
if (expression.Predicate != null)
{
_writer.WriteStartWhere();
Visit(expression.Predicate);
}
return expression;
}
/// <summary>
/// Visits the specified <see cref="SqlUpdate"/>.
/// </summary>
/// <param name="expression">
/// The expression to visit.
/// </param>
public virtual void Visit(SqlUpdate expression)
{
}
private SqlStatement VisitUpdate(Expression item, LambdaExpression check, LambdaExpression resultSelector) {
if (item == null) {
throw Error.ArgumentNull("item");
}
MetaTable metaTable = this.services.Model.GetTable(item.Type);
Expression source = this.services.Context.GetTable(metaTable.RowType.Type).Expression;
Type rowType = metaTable.RowType.Type;
bool saveAllowDeferred = this.allowDeferred;
this.allowDeferred = false;
try {
Expression seq = source;
// construct identity predicate based on supplied item
ParameterExpression p = Expression.Parameter(rowType, "p");
LambdaExpression idPredicate = Expression.Lambda(Expression.Equal(p, item), p);
// combine predicate and check expression into single find predicate
LambdaExpression findPredicate = idPredicate;
if (check != null) {
findPredicate = Expression.Lambda(Expression.And(Expression.Invoke(findPredicate, p), Expression.Invoke(check, p)), p);
}
seq = Expression.Call(typeof(Enumerable), "Where", new Type[] { rowType }, seq, findPredicate);
// source 'query' is based on table + find predicate
SqlSelect ss = new RetypeCheckClause().VisitSelect(this.VisitSequence(seq));
// construct update assignments from 'item' info
List<SqlAssign> assignments = new List<SqlAssign>();
ConstantExpression conItem = item as ConstantExpression;
if (conItem == null) {
throw Error.UpdateItemMustBeConstant();
}
if (conItem.Value == null) {
throw Error.ArgumentNull("item");
}
// get changes from data services to construct update command
Type entityType = conItem.Value.GetType();
MetaType metaType = this.services.Model.GetMetaType(entityType);
ITable table = this.services.Context.GetTable(metaType.InheritanceRoot.Type);
foreach (ModifiedMemberInfo mmi in table.GetModifiedMembers(conItem.Value)) {
MetaDataMember mdm = metaType.GetDataMember(mmi.Member);
assignments.Add(
new SqlAssign(
sql.Member(ss.Selection, mmi.Member),
new SqlValue(mdm.Type, this.typeProvider.From(mdm.Type), mmi.CurrentValue, true, source),
source
));
}
SqlUpdate upd = new SqlUpdate(ss, assignments, source);
if (resultSelector == null) {
return upd;
}
SqlSelect select = null;
// build select to return result
seq = source;
seq = Expression.Call(typeof(Enumerable), "Where", new Type[] { rowType }, seq, idPredicate);
seq = Expression.Call(typeof(Enumerable), "Select", new Type[] { rowType, resultSelector.Body.Type }, seq, resultSelector);
select = this.VisitSequence(seq);
select.Where = sql.AndAccumulate(
sql.Binary(SqlNodeType.GT, this.GetRowCountExpression(), sql.ValueFromObject(0, false, this.dominatingExpression)),
select.Where
);
// combine update & select into statement block
SqlBlock block = new SqlBlock(source);
block.Statements.Add(upd);
block.Statements.Add(select);
return block;
}
finally {
this.allowDeferred = saveAllowDeferred;
}
}
internal virtual SqlStatement VisitUpdate(SqlUpdate update) {
update.Select = this.VisitSequence(update.Select);
for (int i = 0, n = update.Assignments.Count; i < n; i++) {
update.Assignments[i] = (SqlAssign)this.Visit(update.Assignments[i]);
}
return update;
}
protected virtual SqlExpression VisitUpdate(SqlUpdate expr)
{
return expr;
}
protected virtual void ExecuteDelete()
{
var isActiveDeletedRow = Row as IIsActiveDeletedRow;
var isDeletedRow = Row as IIsDeletedRow;
var deleteLogRow = Row as IDeleteLogRow;
var idField = Row.IdField;
var id = idField.ConvertValue(Request.EntityId, CultureInfo.InvariantCulture);
if (isActiveDeletedRow == null && isDeletedRow == null && deleteLogRow == null)
{
var delete = new SqlDelete(Row.Table)
.WhereEqual(idField, id);
InvokeDeleteAction(() =>
{
if (delete.Execute(Connection) != 1)
{
throw DataValidation.EntityNotFoundError(Row, id, Localizer);
}
});
}
else
{
if (isDeletedRow != null || isActiveDeletedRow != null)
{
var update = new SqlUpdate(Row.Table)
.WhereEqual(idField, id)
.Where(ServiceQueryHelper.GetNotDeletedCriteria(Row));
if (isActiveDeletedRow != null)
{
update.Set(isActiveDeletedRow.IsActiveField, -1);
}
else if (isDeletedRow != null)
{
update.Set(isDeletedRow.IsDeletedField, true);
}
if (deleteLogRow != null)
{
update.Set(deleteLogRow.DeleteDateField, DateTimeField.ToDateTimeKind(DateTime.Now,
deleteLogRow.DeleteDateField.DateTimeKind))
.Set(deleteLogRow.DeleteUserIdField, User?.GetIdentifier().TryParseID());
}
else if (Row is IUpdateLogRow updateLogRow)
{
update.Set(updateLogRow.UpdateDateField, DateTimeField.ToDateTimeKind(DateTime.Now,
updateLogRow.UpdateDateField.DateTimeKind))
.Set(updateLogRow.UpdateUserIdField, User?.GetIdentifier().TryParseID());
}
InvokeDeleteAction(() =>
{
if (update.Execute(Connection) != 1)
{
throw DataValidation.EntityNotFoundError(Row, id, Localizer);
}
});
}
else //if (deleteLogRow != null)
{
var update = new SqlUpdate(Row.Table)
.Set(deleteLogRow.DeleteDateField, DateTimeField.ToDateTimeKind(DateTime.Now,
deleteLogRow.DeleteDateField.DateTimeKind))
.Set(deleteLogRow.DeleteUserIdField, User?.GetIdentifier().TryParseID())
.WhereEqual(idField, id)
.Where(new Criteria(deleteLogRow.DeleteUserIdField).IsNull());
InvokeDeleteAction(() =>
{
if (update.Execute(Connection) != 1)
{
throw DataValidation.EntityNotFoundError(Row, id, Localizer);
}
});
}
}
InvalidateCacheOnCommit();
}
public void Add(SqlUpdate statement)
{
AddCommandWithParameters(statement.ToString(), statement.Params);
}
public void MergeDublicate(int fromContactID, int toContactID)
{
var fromContact = GetByID(fromContactID);
var toContact = GetByID(toContactID);
if (fromContact == null || toContact == null)
{
throw new ArgumentException();
}
using (var tx = DbManager.BeginTransaction())
{
ISqlInstruction q = Update("crm_task")
.Set("contact_id", toContactID)
.Where(Exp.Eq("contact_id", fromContactID));
DbManager.ExecuteNonQuery(q);
// crm_entity_contact
q = new SqlQuery("crm_entity_contact l")
.From("crm_entity_contact r")
.Select("l.entity_id", "l.entity_type", "l.contact_id")
.Where(Exp.EqColumns("l.entity_id", "r.entity_id") & Exp.EqColumns("l.entity_type", "r.entity_type"))
.Where("l.contact_id", fromContactID)
.Where("r.contact_id", toContactID);
DbManager.ExecuteList(q)
.ForEach(row =>
DbManager.ExecuteNonQuery(new SqlDelete("crm_entity_contact").Where("entity_id", row[0]).Where("entity_type", row[1]).Where("contact_id", row[2]))
);
q = new SqlUpdate("crm_entity_contact")
.Set("contact_id", toContactID)
.Where("contact_id", fromContactID);
DbManager.ExecuteNonQuery(q);
// crm_deal
q = Update("crm_deal")
.Set("contact_id", toContactID)
.Where("contact_id", fromContactID);
DbManager.ExecuteNonQuery(q);
// crm_relationship_event
q = Update("crm_relationship_event")
.Set("contact_id", toContactID)
.Where("contact_id", fromContactID);
DbManager.ExecuteNonQuery(q);
// crm_entity_tag
q = new SqlQuery("crm_entity_tag l")
.Select("l.tag_id")
.From("crm_entity_tag r")
.Where(Exp.EqColumns("l.tag_id", "r.tag_id") & Exp.EqColumns("l.entity_type", "r.entity_type"))
.Where("l.entity_id", fromContactID)
.Where("r.entity_id", toContactID);
var dublicateTagsID = DbManager.ExecuteList(q).ConvertAll(row => row[0]);
q = new SqlDelete("crm_entity_tag").Where(Exp.Eq("entity_id", fromContactID) & Exp.Eq("entity_type", (int)EntityType.Contact) & Exp.In("tag_id", dublicateTagsID));
DbManager.ExecuteNonQuery(q);
q = new SqlUpdate("crm_entity_tag").Set("entity_id", toContactID).Where("entity_id", fromContactID).Where("entity_type", (int)EntityType.Contact);
DbManager.ExecuteNonQuery(q);
// crm_field_value
q = Query("crm_field_value l")
.From("crm_field_value r")
.Select("l.field_id")
.Where(Exp.EqColumns("l.tenant_id", "r.tenant_id") & Exp.EqColumns("l.field_id", "r.field_id") & Exp.EqColumns("l.entity_type", "r.entity_type"))
.Where("l.entity_id", fromContactID)
.Where("r.entity_id", toContactID);
var dublicateCustomFieldValueID = DbManager.ExecuteList(q).ConvertAll(row => row[0]);
q = Delete("crm_field_value")
.Where("entity_id", fromContactID)
.Where(Exp.In("entity_type", new[] { (int)EntityType.Contact, (int)EntityType.Person, (int)EntityType.Company }))
.Where(Exp.In("field_id", dublicateCustomFieldValueID));
DbManager.ExecuteNonQuery(q);
q = Update("crm_field_value")
.Set("entity_id", toContactID)
.Where("entity_id", fromContactID)
.Where("entity_type", (int)EntityType.Contact);
DbManager.ExecuteNonQuery(q);
// crm_contact_info
q = Query("crm_contact_info l")
.From("crm_contact_info r")
.Select("l.id")
.Where(Exp.EqColumns("l.tenant_id", "r.tenant_id"))
.Where(Exp.EqColumns("l.type", "r.type"))
.Where(Exp.EqColumns("l.is_primary", "r.is_primary"))
.Where(Exp.EqColumns("l.category", "r.category"))
.Where(Exp.EqColumns("l.data", "r.data"))
.Where("l.contact_id", fromContactID)
.Where("r.contact_id", toContactID);
var dublicateContactInfoID = DbManager.ExecuteList(q).ConvertAll(row => row[0]);
q = Delete("crm_contact_info")
.Where("contact_id", fromContactID)
.Where(Exp.In("id", dublicateContactInfoID));
DbManager.ExecuteNonQuery(q);
q = Update("crm_contact_info")
.Set("contact_id", toContactID)
.Where("contact_id", fromContactID);
DbManager.ExecuteNonQuery(q);
MergeContactInfo(fromContact, toContact);
// crm_contact
if ((fromContact is Company) && (toContact is Company))
{
q = Update("crm_contact")
.Set("company_id", toContactID)
.Where("company_id", fromContactID);
DbManager.ExecuteNonQuery(q);
}
q = Delete("crm_contact").Where("id", fromContactID);
DbManager.ExecuteNonQuery(q);
tx.Commit();
}
CoreContext.AuthorizationManager.RemoveAllAces(fromContact);
}
public void UpdateCommand(SqlUpdate sqlUpdate, T item)
{
InvokeAction(_mappedColumn.Where(x => !x.Key.IsIdentity), (name, value) => sqlUpdate.Set(name, value), item);
}
public static void AddResource(string cultureTitle, string resType, DateTime date, ResWord word, bool isConsole, string authorLogin, bool updateIfExist = true)
{
using (var db = DbManager.FromHttpContext(Dbid))
{
var resData = db.ExecuteScalar <string>(GetQuery(ResDataTable, cultureTitle, word));
var resReserve = db.ExecuteScalar <string>(GetQuery(ResReserveTable, cultureTitle, word));
//нет ключа
if (string.IsNullOrEmpty(resData))
{
//добавляем в основную таблицу
db.ExecuteNonQuery(Insert(ResDataTable, cultureTitle, word)
.InColumnValue("resourceType", resType)
.InColumnValue("timechanges", date)
.InColumnValue("flag", 2)
.InColumnValue("authorLogin", authorLogin));
//добавляем в резервную таблицу
if (isConsole)
{
db.ExecuteNonQuery(Insert(ResReserveTable, cultureTitle, word));
}
}
else
{
if (cultureTitle == "Neutral" && isConsole)
{
updateIfExist = db.ExecuteScalar <int>(new SqlQuery(ResDataTable)
.SelectCount()
.Where("fileID", word.ResFile.FileID)
.Where(!Exp.Eq("cultureTitle", cultureTitle))
.Where("title", word.Title)) == 0;
}
var isChangeResData = resData != word.ValueFrom;
var isChangeResReserve = resReserve != word.ValueFrom;
if (!updateIfExist)
{
return;
}
//при работе с консолью изменилось по сравнению с res_data и res_reserve, либо при работе с сайтом изменилось по сравнению с res_reserve
if ((isConsole && isChangeResData && isChangeResReserve) || !isConsole)
{
// изменилась нейтральная культура - выставлен флаг у всех ключей из выбранного файла с выбранным title
if (cultureTitle == "Neutral")
{
var update = new SqlUpdate(ResDataTable)
.Set("flag", 3)
.Where("fileID", word.ResFile.FileID)
.Where("title", word.Title);
db.ExecuteNonQuery(update);
}
// изменилась не нейтральная культура
db.ExecuteNonQuery(Insert(ResDataTable, cultureTitle, word)
.InColumnValue("resourceType", resType)
.InColumnValue("timechanges", date)
.InColumnValue("flag", 2)
.InColumnValue("authorLogin", authorLogin));
if (isConsole)
{
db.ExecuteNonQuery(Update(ResReserveTable, cultureTitle, word));
}
}
else if (isChangeResData)
{
db.ExecuteNonQuery(Update(ResReserveTable, cultureTitle, word));
}
}
}
}
// Method for updating chain flags, date and length.
public void UpdateChain(IDaoFactory daoFactory, string chainId, FolderType folder, uint?userFolderId, int mailboxId,
int tenant, string user)
{
if (string.IsNullOrEmpty(chainId))
{
return;
}
var db = daoFactory.DbManager;
var engine = new EngineFactory(tenant, user, Log);
var daoMailInfo = daoFactory.CreateMailInfoDao(tenant, user);
const string m_alias = "m";
const string utm_alias = "ufm";
var getChainQuery = new SqlQuery(MailTable.TABLE_NAME.Alias(m_alias))
.SelectCount()
.SelectMax(MailTable.Columns.DateSent.Prefix(m_alias))
.SelectMax(MailTable.Columns.Unread.Prefix(m_alias))
.SelectMax(MailTable.Columns.AttachCount.Prefix(m_alias))
.SelectMax(MailTable.Columns.Importance.Prefix(m_alias))
.Where(MailTable.Columns.Tenant.Prefix(m_alias), tenant)
.Where(MailTable.Columns.User.Prefix(m_alias), user)
.Where(MailTable.Columns.IsRemoved.Prefix(m_alias), 0)
.Where(MailTable.Columns.ChainId.Prefix(m_alias), chainId)
.Where(MailTable.Columns.MailboxId.Prefix(m_alias), mailboxId)
.Where(MailTable.Columns.Folder.Prefix(m_alias), (int)folder);
if (userFolderId.HasValue)
{
getChainQuery
.InnerJoin(UserFoldertXMailTable.TABLE_NAME.Alias(utm_alias),
Exp.EqColumns(MailTable.Columns.Id.Prefix(m_alias),
UserFoldertXMailTable.Columns.MailId.Prefix(utm_alias)))
.Where(UserFoldertXMailTable.Columns.Tenant.Prefix(utm_alias), tenant)
.Where(UserFoldertXMailTable.Columns.User.Prefix(utm_alias), user)
.Where(UserFoldertXMailTable.Columns.FolderId.Prefix(utm_alias), userFolderId.Value);
}
var chainInfo = db.ExecuteList(getChainQuery)
.ConvertAll(x => new
{
length = Convert.ToInt32(x[0]),
date = Convert.ToDateTime(x[1]),
unread = Convert.ToBoolean(x[2]),
attach_count = Convert.ToInt32(x[3]),
importance = Convert.ToBoolean(x[4])
})
.FirstOrDefault();
if (chainInfo == null)
{
throw new InvalidDataException("Conversation is absent in MAIL_MAIL");
}
var daoChain = daoFactory.CreateChainDao(tenant, user);
var storedChainInfo = daoChain.GetChains(SimpleConversationsExp.CreateBuilder(tenant, user)
.SetMailboxId(mailboxId)
.SetChainId(chainId)
.SetFolder((int)folder)
.Build());
var chainUnreadFlag = storedChainInfo.Any() && Convert.ToBoolean(storedChainInfo.First().Unread);
if (0 == chainInfo.length)
{
var result = daoChain.Delete(SimpleConversationsExp.CreateBuilder(tenant, user)
.SetFolder((int)folder)
.SetMailboxId(mailboxId)
.SetChainId(chainId)
.Build());
Log.DebugFormat(
"UpdateChain() row deleted from chain table tenant='{0}', user_id='{1}', id_mailbox='{2}', folder='{3}', chain_id='{4}' result={5}",
tenant, user, mailboxId, folder, chainId, result);
var unreadConvDiff = chainUnreadFlag ? -1 : (int?)null;
engine.FolderEngine.ChangeFolderCounters(daoFactory, folder, userFolderId,
unreadConvDiff: unreadConvDiff, totalConvDiff: -1);
}
else
{
var updateMailQuery = new SqlUpdate(MailTable.TABLE_NAME)
.Where(MailTable.Columns.Tenant, tenant)
.Where(MailTable.Columns.User, user)
.Where(MailTable.Columns.IsRemoved, 0)
.Where(MailTable.Columns.ChainId, chainId)
.Where(MailTable.Columns.MailboxId, mailboxId)
.Where(MailTable.Columns.Folder, (int)folder)
// Folder condition important because chain has different dates in different folders(Ex: Sent and Inbox).
.Set(MailTable.Columns.ChainDate, chainInfo.date);
db.ExecuteNonQuery(updateMailQuery);
daoMailInfo.SetFieldValue(
SimpleMessagesExp.CreateBuilder(tenant, user)
.SetChainId(chainId)
.SetMailboxId(mailboxId)
.SetFolder((int)folder)
.Build(),
MailTable.Columns.ChainDate,
chainInfo.date);
var tags = GetChainTags(daoFactory, chainId, folder, mailboxId, tenant, user);
var chain = new Chain
{
Id = chainId,
Tenant = tenant,
User = user,
MailboxId = mailboxId,
Folder = folder,
Length = chainInfo.length,
Unread = chainInfo.unread,
HasAttachments = chainInfo.attach_count > 0,
Importance = chainInfo.importance,
Tags = tags
};
var result = daoChain.SaveChain(chain);
if (result <= 0)
{
throw new InvalidOperationException(string.Format("Invalid insert into {0}", ChainTable.TABLE_NAME));
}
Log.DebugFormat(
"UpdateChain() row inserted to chain table tenant='{0}', user_id='{1}', id_mailbox='{2}', folder='{3}', chain_id='{4}'",
tenant, user, mailboxId, folder, chainId);
var unreadConvDiff = (int?)null;
var totalConvDiff = (int?)null;
if (!storedChainInfo.Any())
{
totalConvDiff = 1;
unreadConvDiff = chainInfo.unread ? 1 : (int?)null;
}
else
{
if (chainUnreadFlag != chainInfo.unread)
{
unreadConvDiff = chainInfo.unread ? 1 : -1;
}
}
engine.FolderEngine.ChangeFolderCounters(daoFactory, folder, userFolderId,
unreadConvDiff: unreadConvDiff, totalConvDiff: totalConvDiff);
}
}
public static SqlUpdate ApplyFilter(this SqlUpdate query, MailFilter filter)
{
return(ApplyFilter(query, filter, false));
}
public Tenant SaveTenant(Tenant t)
{
if (t == null)
{
throw new ArgumentNullException("tenant");
}
using (var db = GetDb())
using (var tx = db.BeginTransaction())
{
if (!string.IsNullOrEmpty(t.MappedDomain))
{
var baseUrl = TenantUtil.GetBaseDomain(t.HostedRegion);
if (baseUrl != null && t.MappedDomain.EndsWith("." + baseUrl, StringComparison.InvariantCultureIgnoreCase))
{
ValidateDomain(db, t.MappedDomain.Substring(0, t.MappedDomain.Length - baseUrl.Length - 1), t.TenantId, false);
}
else
{
ValidateDomain(db, t.MappedDomain, t.TenantId, false);
}
}
if (t.TenantId == Tenant.DEFAULT_TENANT)
{
var q = new SqlQuery("tenants_version")
.Select("id")
.Where(Exp.Eq("default_version", 1) | Exp.Eq("id", 0))
.OrderBy(1, false)
.SetMaxResults(1);
t.Version = db.ExecuteScalar <int>(q);
var i = new SqlInsert("tenants_tenants", true)
.InColumnValue("id", t.TenantId)
.InColumnValue("alias", t.TenantAlias.ToLowerInvariant())
.InColumnValue("mappeddomain", !string.IsNullOrEmpty(t.MappedDomain) ? t.MappedDomain.ToLowerInvariant() : null)
.InColumnValue("version", t.Version)
.InColumnValue("version_changed", t.VersionChanged)
.InColumnValue("name", t.Name ?? t.TenantAlias)
.InColumnValue("language", t.Language)
.InColumnValue("timezone", t.TimeZone.Id)
.InColumnValue("owner_id", t.OwnerId.ToString())
.InColumnValue("trusteddomains", t.GetTrustedDomains())
.InColumnValue("trusteddomainsenabled", (int)t.TrustedDomainsType)
.InColumnValue("creationdatetime", t.CreatedDateTime)
.InColumnValue("status", (int)t.Status)
.InColumnValue("statuschanged", t.StatusChangeDate)
.InColumnValue("payment_id", t.PaymentId)
.InColumnValue("last_modified", t.LastModified = DateTime.UtcNow)
.InColumnValue("industry", (int)t.Industry)
.Identity <int>(0, t.TenantId, true);
t.TenantId = db.ExecuteScalar <int>(i);
}
else
{
var u = new SqlUpdate("tenants_tenants")
.Set("alias", t.TenantAlias.ToLowerInvariant())
.Set("mappeddomain", !string.IsNullOrEmpty(t.MappedDomain) ? t.MappedDomain.ToLowerInvariant() : null)
.Set("version", t.Version)
.Set("version_changed", t.VersionChanged)
.Set("name", t.Name ?? t.TenantAlias)
.Set("language", t.Language)
.Set("timezone", t.TimeZone.Id)
.Set("owner_id", t.OwnerId.ToString())
.Set("trusteddomains", t.GetTrustedDomains())
.Set("trusteddomainsenabled", (int)t.TrustedDomainsType)
.Set("creationdatetime", t.CreatedDateTime)
.Set("status", (int)t.Status)
.Set("statuschanged", t.StatusChangeDate)
.Set("payment_id", t.PaymentId)
.Set("last_modified", t.LastModified = DateTime.UtcNow)
.Set("industry", (int)t.Industry)
.Where("id", t.TenantId);
db.ExecuteNonQuery(u);
}
if (string.IsNullOrEmpty(t.PartnerId) && string.IsNullOrEmpty(t.AffiliateId))
{
var d = new SqlDelete("tenants_partners").Where("tenant_id", t.TenantId);
db.ExecuteNonQuery(d);
}
else
{
var i = new SqlInsert("tenants_partners", true)
.InColumnValue("tenant_id", t.TenantId)
.InColumnValue("partner_id", t.PartnerId)
.InColumnValue("affiliate_id", t.AffiliateId);
db.ExecuteNonQuery(i);
}
tx.Commit();
}
//CalculateTenantDomain(t);
return(t);
}
/// <summary>
/// Visits the specified <see cref="SqlUpdate"/>.
/// </summary>
/// <param name="expression">
/// The expression to visit.
/// </param>
public virtual void Visit(SqlUpdate expression)
{
}
public static void UpdateDataFromInternalDatabase(string hostname, MailServerInfo mailServer)
{
using (var dbManager = GetDb())
using (var transaction = dbManager.BeginTransaction())
{
var insertQuery = new SqlInsert("mail_mailbox_provider")
.InColumnValue("id", 0)
.InColumnValue("name", hostname)
.Identity(0, 0, true);
var providerId = dbManager.ExecuteScalar <int>(insertQuery);
insertQuery = new SqlInsert("mail_mailbox_server")
.InColumnValue("id", 0)
.InColumnValue("id_provider", providerId)
.InColumnValue("type", "smtp")
.InColumnValue("hostname", hostname)
.InColumnValue("port", 587)
.InColumnValue("socket_type", "STARTTLS")
.InColumnValue("username", "%EMAILADDRESS%")
.InColumnValue("authentication", "")
.InColumnValue("is_user_data", false)
.Identity(0, 0, true);
var smtpServerId = dbManager.ExecuteScalar <int>(insertQuery);
insertQuery = new SqlInsert("mail_mailbox_server")
.InColumnValue("id", 0)
.InColumnValue("id_provider", providerId)
.InColumnValue("type", "imap")
.InColumnValue("hostname", hostname)
.InColumnValue("port", 143)
.InColumnValue("socket_type", "STARTTLS")
.InColumnValue("username", "%EMAILADDRESS%")
.InColumnValue("authentication", "")
.InColumnValue("is_user_data", false)
.Identity(0, 0, true);
var imapServerId = dbManager.ExecuteScalar <int>(insertQuery);
var selectQuery = new SqlQuery("mail_server_server")
.Select("id, smtp_settings_id, imap_settings_id")
.SetMaxResults(1);
var mailServerData = dbManager
.ExecuteList(selectQuery)
.Select(r => new[] { Convert.ToInt32(r[0]), Convert.ToInt32(r[1]), Convert.ToInt32(r[2]) })
.FirstOrDefault();
var connectionString = Newtonsoft.Json.JsonConvert.SerializeObject(mailServer);
insertQuery = new SqlInsert("mail_server_server")
.InColumnValue("id", 0)
.InColumnValue("mx_record", hostname)
.InColumnValue("connection_string", connectionString)
.InColumnValue("server_type", 2)
.InColumnValue("smtp_settings_id", smtpServerId)
.InColumnValue("imap_settings_id", imapServerId);
dbManager.ExecuteNonQuery(insertQuery);
if (mailServerData != null)
{
var deleteQuery = new SqlDelete("mail_server_server")
.Where(Exp.Eq("id", mailServerData[0]));
dbManager.ExecuteNonQuery(deleteQuery);
selectQuery = new SqlQuery("mail_mailbox_server")
.Select("id_provider")
.Where(Exp.Eq("id", mailServerData[1]))
.SetMaxResults(1);
providerId = dbManager
.ExecuteList(selectQuery)
.Select(r => Convert.ToInt32(r[0]))
.FirstOrDefault();
deleteQuery = new SqlDelete("mail_mailbox_provider")
.Where(Exp.Eq("id", providerId));
dbManager.ExecuteNonQuery(deleteQuery);
deleteQuery = new SqlDelete("mail_mailbox_server")
.Where(Exp.In("id", new[] { mailServerData[1], mailServerData[2] }));
dbManager.ExecuteNonQuery(deleteQuery);
selectQuery = new SqlQuery("mail_mailbox")
.Select("id")
.Where(Exp.Eq("id_smtp_server", mailServerData[1]))
.Where(Exp.Eq("id_in_server", mailServerData[2]));
var mailboxId = dbManager
.ExecuteList(selectQuery)
.Select(r => Convert.ToInt32(r[0]))
.ToArray();
var updateQuery = new SqlUpdate("mail_mailbox")
.Set("id_smtp_server", smtpServerId)
.Set("id_in_server", imapServerId)
.Where(Exp.In("id", mailboxId));
dbManager.ExecuteNonQuery(updateQuery);
}
transaction.Commit();
}
}
public void TableIsNotSetTest3()
{
SqlUpdate update = SqlDml.Update();
Assert.Throws <SqlCompilerException>(() => sqlDriver.Compile(update));
}
/// <summary>
/// Logs the error to SQL
/// If the rollup conditions are met, then the matching error will have a DuplicateCount += @DuplicateCount (usually 1, unless in retry) rather than a distinct new row for the error
/// </summary>
/// <param name="error">The error to log</param>
protected override void LogError(Error error)
{
using (var c = GetConnection())
{
if (RollupThreshold.HasValue && error.ErrorHash.HasValue)
{
var queryParams = new Serenity.Data.DynamicParameters(new
{
error.DuplicateCount,
error.ErrorHash,
ApplicationName = error.ApplicationName.Truncate(50),
minDate = DateTime.UtcNow.Add(RollupThreshold.Value.Negate())
});
if (isSqlServer)
{
queryParams.Add("@newGUID", dbType: DbType.Guid, direction: ParameterDirection.Output);
var count = c.Execute(@"
Update Exceptions
Set DuplicateCount = DuplicateCount + @DuplicateCount,
@newGUID = GUID
Where Id In (Select Top 1 Id
From Exceptions
Where ErrorHash = @ErrorHash
And ApplicationName = @ApplicationName
And DeletionDate Is Null
And CreationDate >= @minDate)", queryParams);
// if we found an error that's a duplicate, jump out
if (count > 0)
{
error.GUID = queryParams.Get <Guid>("@newGUID");
return;
}
}
else
{
var count = new SqlUpdate("Exceptions")
.Set("DuplicateCount", "DuplicateCount + @DuplicateCount")
.Where(new Criteria("[Id]").In(new Criteria("(" +
new SqlQuery()
.Dialect(c.GetDialect())
.Select("[Id]")
.From("Exceptions")
.Take(1)
.Where(hashMatch)) + ")"))
.Execute(c, ExpectedRows.Ignore);
// if we found an exception that's a duplicate, jump out
if (count > 0)
{
error.GUID = c.Query <Guid>(new SqlQuery()
.Dialect(c.GetDialect())
.From("Exceptions")
.Select("GUID")
.Take(1)
.Where(hashMatch)).First();
return;
}
}
error.FullJson = error.ToJson();
c.Execute(@"
Insert Into Exceptions ([GUID], [ApplicationName], [MachineName], [CreationDate], [Type], [IsProtected], [Host], [Url], [HTTPMethod], [IPAddress], [Source], [Message], [Detail], [StatusCode], [SQL], [FullJson], [ErrorHash], [DuplicateCount])
Values (@GUID, @ApplicationName, @MachineName, @CreationDate, @Type, @IsProtected, @Host, @Url, @HTTPMethod, @IPAddress, @Source, @Message, @Detail, @StatusCode, @SQL, @FullJson, @ErrorHash, @DuplicateCount)",
new
{
error.GUID,
ApplicationName = error.ApplicationName.Truncate(50),
MachineName = error.MachineName.Truncate(50),
error.CreationDate,
Type = error.Type.Truncate(100),
error.IsProtected,
Host = error.Host.Truncate(100),
Url = error.Url.Truncate(500),
HTTPMethod = error.HTTPMethod.Truncate(10), // this feels silly, but you never know when someone will up and go crazy with HTTP 1.2!
error.IPAddress,
Source = error.Source.Truncate(100),
Message = error.Message.Truncate(1000),
error.Detail,
error.StatusCode,
error.SQL,
error.FullJson,
error.ErrorHash,
error.DuplicateCount
});
}
}
}
public void Values_IsNotNullWhenFirstCalled()
{
var update = new SqlUpdate("Users");
Assert.NotNull(update.Set);
}
internal override SqlStatement VisitUpdate(SqlUpdate sup) {
bool saveTop = this.topLevel;
this.topLevel = false;
base.VisitUpdate(sup);
this.topLevel = saveTop;
return sup;
}
public void ExpressionType_ReturnsUpdate()
{
var query = new SqlUpdate("User");
Assert.Equal(SqlExpressionType.Update, query.ExpressionType);
}
internal override SqlStatement VisitUpdate(SqlUpdate sup) {
Scope save = this.CurrentScope;
this.CurrentScope = new Scope(sup.Select, this.CurrentScope.ContainingScope);
base.VisitUpdate(sup);
this.CurrentScope = save;
return sup;
}
private bool AddFakeVersionColumnUpdate(PersistRequestBuilderContext context, SqlUpdate update, SqlTableRef filteredTable)
{
foreach (var column in context.Type.GetVersionColumns())
{
var columnExpression = filteredTable[column.Name];
if (columnExpression.IsNullReference())
{
continue;
}
int index = GetFieldIndex(context.Type, column);
if (index < 0 || !context.Task.AvailableFields[index])
{
continue;
}
update.Values.Add(columnExpression, columnExpression);
return(true);
}
return(false);
}
protected override SqlExpression VisitUpdate(SqlUpdate expr)
{
return expr;
}
public TUndeleteResponse Process(IUnitOfWork unitOfWork, UndeleteRequest request)
{
if (unitOfWork == null)
{
throw new ArgumentNullException("unitOfWork");
}
ValidatePermissions();
UnitOfWork = unitOfWork;
Request = request;
Response = new TUndeleteResponse();
if (request.EntityId == null)
{
throw DataValidation.RequiredError("EntityId");
}
Row = new TRow();
var isDeletedRow = Row as IIsActiveDeletedRow;
var deleteLogRow = Row as IDeleteLogRow;
if (isDeletedRow == null && deleteLogRow == null)
{
throw new NotImplementedException();
}
var idField = (Field)Row.IdField;
var id = idField.ConvertValue(Request.EntityId, CultureInfo.InvariantCulture);
LoadEntity();
ValidateRequest();
if ((isDeletedRow != null && isDeletedRow.IsActiveField[Row] > 0) ||
(deleteLogRow != null && ((Field)deleteLogRow.DeleteUserIdField).IsNull(Row)))
{
Response.WasNotDeleted = true;
}
else
{
OnBeforeUndelete();
var update = new SqlUpdate(Row.Table)
.WhereEqual(idField, id);
if (isDeletedRow != null)
{
update.Set(isDeletedRow.IsActiveField, 1)
.WhereEqual(isDeletedRow.IsActiveField, -1);
}
if (deleteLogRow != null)
{
update.Set((Field)deleteLogRow.DeleteUserIdField, null)
.Set(deleteLogRow.DeleteDateField, null);
if (isDeletedRow == null)
{
update.Where(((Field)deleteLogRow.DeleteUserIdField).IsNotNull());
}
}
if (update.Execute(Connection) != 1)
{
throw DataValidation.EntityNotFoundError(Row, id);
}
InvalidateCacheOnCommit();
OnAfterUndelete();
DoAudit();
}
OnReturn();
return(Response);
}
internal override SqlStatement VisitUpdate(SqlUpdate su) {
SqlSelect ss = (SqlSelect)this.Visit(su.Select);
List<SqlAssign> assignments = new List<SqlAssign>(su.Assignments.Count);
foreach (SqlAssign sa in su.Assignments) {
assignments.Add((SqlAssign)this.Visit(sa));
}
return new SqlUpdate(ss, assignments, su.SourceExpression);
}
public virtual int UpdateProviderInfo(int linkId, string customerTitle, AuthData newAuthData, FolderType folderType, Guid?userId = null)
{
var authData = new AuthData();
if (newAuthData != null && !newAuthData.IsEmpty())
{
var querySelect = new SqlQuery(TableTitle)
.Select("provider", "url", "user_name", "password")
.Where("tenant_id", TenantID)
.Where("id", linkId);
object[] input;
try
{
using (var db = GetDb())
{
input = db.ExecuteList(querySelect).Single();
}
}
catch (Exception e)
{
Global.Logger.Error(string.Format("UpdateProviderInfo: linkId = {0} , user = {1}", linkId, SecurityContext.CurrentAccount.ID), e);
throw;
}
var providerKey = (string)input[0];
ProviderTypes key;
if (!Enum.TryParse(providerKey, true, out key))
{
throw new ArgumentException("Unrecognize ProviderType");
}
authData = new AuthData(
!string.IsNullOrEmpty(newAuthData.Url) ? newAuthData.Url : (string)input[1],
(string)input[2],
!string.IsNullOrEmpty(newAuthData.Password) ? newAuthData.Password : DecryptPassword(input[3] as string),
newAuthData.Token);
if (!string.IsNullOrEmpty(newAuthData.Token))
{
authData = GetEncodedAccesToken(authData, key);
}
if (!CheckProviderInfo(ToProviderInfo(0, providerKey, customerTitle, authData, SecurityContext.CurrentAccount.ID.ToString(), folderType, TenantUtil.DateTimeToUtc(TenantUtil.DateTimeNow()))))
{
throw new UnauthorizedAccessException(string.Format(FilesCommonResource.ErrorMassage_SecurityException_Auth, providerKey));
}
}
var queryUpdate = new SqlUpdate(TableTitle)
.Where("id", linkId)
.Where("tenant_id", TenantID);
if (!string.IsNullOrEmpty(customerTitle))
{
queryUpdate
.Set("customer_title", customerTitle);
}
if (folderType != FolderType.DEFAULT)
{
queryUpdate
.Set("folder_type", (int)folderType);
}
if (userId.HasValue)
{
queryUpdate
.Set("user_id", userId.Value.ToString());
}
if (!authData.IsEmpty())
{
queryUpdate
.Set("user_name", authData.Login ?? "")
.Set("password", EncryptPassword(authData.Password))
.Set("token", EncryptPassword(authData.Token ?? ""))
.Set("url", authData.Url ?? "");
}
using (var db = GetDb())
{
return(db.ExecuteNonQuery(queryUpdate) == 1 ? linkId : default(int));
}
}
internal override SqlStatement VisitUpdate(SqlUpdate su) {
sb.Append("UPDATE ");
this.suppressedAliases.Add(su.Select.From);
this.Visit(su.Select.From);
this.NewLine();
sb.Append("SET ");
for (int i = 0, n = su.Assignments.Count; i < n; i++) {
if (i > 0) sb.Append(", ");
SqlAssign sa = su.Assignments[i];
this.Visit(sa.LValue);
sb.Append(" = ");
this.Visit(sa.RValue);
}
if (su.Select.Where != null) {
this.NewLine();
sb.Append("WHERE ");
this.Visit(su.Select.Where);
}
this.suppressedAliases.Remove(su.Select.From);
return su;
}
/// <summary>
/// Converts the specified <see cref="SqlUpdate"/> expression tree to an <see cref="IDbCommand"/>
/// instance for the specified <paramref name="connection" />.
/// </summary>
/// <param name="sql">
/// The <see cref="SqlUpdate"/> to convert to an <see cref="IDbCommand"/>.
/// </param>
/// <param name="connection">
/// The <see cref="IDbConnection"/> for which to create an <see cref="IDbCommand"/>.
/// </param>
/// <returns>
/// An <see cref="IDbCommand"/> for the specified <paramref name="connection"/> that reprents
/// the specified SQL expression tree.
/// </returns>
/// <remarks>
/// Any <see cref="SqlParameter"/> members of the <see cref="SqlUpdate"/> expression are
/// automatically converted to <see cref="IDbDataParameter"/> instances and attached to
/// the returned <see cref="IDbCommand"/>.
/// </remarks>
public static IDbCommand CreateCommand(this IDbConnection connection, SqlUpdate sql)
{
return connection.CreateCommandInternal(sql);
}
