public ModelReader GetSchema()
        {
            var  connector = GetDBConnector(_connection);
            int  siteId    = connector.GetSiteId(_siteName);
            bool replaceUrls;

            using (var cmd = connector.CreateDbCommand($"SELECT {SqlQuerySyntaxHelper.Top(connector.DatabaseType, "1")} REPLACE_URLS FROM SITE WHERE SITE_ID = @siteId"))
            {
                cmd.Parameters.AddWithValue("@siteId", siteId);
                replaceUrls = (bool)connector.GetRealScalarData(cmd);
            }

            var attributes = GetAttributes(connector, siteId);
            var contents   = GetContents(connector, siteId, attributes);

            var model = new ModelReader();

            model.Schema.DBType      = connector.DatabaseType;
            model.Schema.ReplaceUrls = replaceUrls;
            model.Schema.SiteName    = _siteName;
            model.Attributes.AddRange(attributes);
            model.Contents.AddRange(contents);

            return(model);
        }
Exemple #2
0
        /// <summary>
        /// Проверяет текст запроса на корректность
        /// </summary>
        internal static bool IsQueryQueryCorrect(string userQuery, out string errorMessage)
        {
            errorMessage = null;
            using (QPConfiguration.OutOfTransaction())
            {
                try
                {
                    var viewName          = $"uq_v_test_{DateTime.Now.Ticks}";
                    var schema            = SqlQuerySyntaxHelper.DbSchemaName(QPContext.DatabaseType);
                    var createTestViewSql = $"CREATE VIEW {schema}.{viewName} AS {userQuery}";
                    using (var connect = QPContext.CreateDbConnection())
                    {
                        connect.Open();
                        Common.ExecuteSql(connect, createTestViewSql);
                        Common.DropView(connect, viewName);
                    }

                    return(true);
                }
                catch (SqlException ex)
                {
                    errorMessage = ex.ErrorsToString();
                    return(false);
                }
                catch (NpgsqlException ex)
                {
                    errorMessage = ex.Message;
                    return(false);
                }
            }
        }
Exemple #3
0
        private static string ParseBooleanParam(ArticleSearchQueryParam p)
        {
            Ensure.NotNull(p);
            Ensure.That(p.SearchType == ArticleFieldSearchType.Boolean);

            if (string.IsNullOrWhiteSpace(p.FieldColumn))
            {
                throw new ArgumentException("FieldColumn");
            }

            // параметры не пустые и их не меньше 2х (используем 1й, 2й - остальные отбрасываем)
            if (p.QueryParams == null || p.QueryParams.Length < 2)
            {
                throw new ArgumentException();
            }

            // первый параметр должен быть bool
            if (!(p.QueryParams[0] is bool))
            {
                throw new InvalidCastException();
            }

            // второй параметр должен быть bool
            if (!(p.QueryParams[1] is bool))
            {
                throw new InvalidCastException();
            }

            var dbType    = QPContext.DatabaseType;
            var fieldName = SqlQuerySyntaxHelper.EscapeEntityName(dbType, p.FieldColumn.ToLower());

            return((bool)p.QueryParams[0]
                ? $"({GetTableAlias(p)}.{fieldName} IS NULL)"
                : $"({GetTableAlias(p)}.{fieldName} = {((bool)p.QueryParams[1] ? 1 : 0)})");
        }
Exemple #4
0
        public List <AbstractItemData> GetByIds(int siteId, bool isArchive, IEnumerable <int> itemIds, IDbTransaction transaction = null)
        {
            _logger.LogDebug($"getByIds. siteId: {siteId}, isArchive: {isArchive}, itemIds: {SerializeData(itemIds)}");

            if (itemIds == null || !itemIds.Any())
            {
                throw new ArgumentNullException("itemIds");
            }
            if (itemIds.Any(x => x <= 0))
            {
                throw new ArgumentException("itemId <= 0");
            }

            var idList = SqlQuerySyntaxHelper.IdList(_uow.DatabaseType, "@ItemIds", "itemIds");
            var query  = GetCmdGetAbstractItemByIds(siteId, isArchive, idList);

            List <AbstractItemData> result;

            if (_uow.DatabaseType == DatabaseType.SqlServer)
            {
                result = _uow.Connection.Query <AbstractItemData>(query,
                                                                  new { ItemIds = SqlQuerySyntaxHelper.IdsToDataTable(itemIds).AsTableValuedParameter("Ids") },
                                                                  transaction)
                         .ToList();
            }
            else
            {
                result = _uow.Connection.Query <AbstractItemData>(query, new { ItemIds = itemIds }, transaction).ToList();
            }

            _logger.LogDebug($"getByIds. count: {result.Count}, result: {SerializeData(result)}");
            return(result);
        }
        public async Task <CustomActionInfo> GetCustomActionByAlias(string alias)
        {
            if (String.IsNullOrWhiteSpace(alias))
            {
                throw new ArgumentException(nameof(alias));
            }

            DbConnection connection = _customer.DatabaseType == DatabaseType.Postgres
                ? (DbConnection) new NpgsqlConnection(_customer.ConnectionString)
                : new SqlConnection(_customer.ConnectionString);

            using (connection)
            {
                await connection.OpenAsync();

                var actionInfo = await connection.QuerySingleAsync <CustomActionInfo>($@"
                    SELECT {SqlQuerySyntaxHelper.Top(_customer.DatabaseType, "1")}
                        ba.CODE AS {nameof(CustomActionInfo.ActionCode)},
	                    et.CODE AS {nameof(CustomActionInfo.EntityTypeCode)}
                    FROM dbo.CUSTOM_ACTION AS ca
                    INNER JOIN {SqlQuerySyntaxHelper.DbSchemaName(_customer.DatabaseType)}.BACKEND_ACTION AS ba ON ca.ACTION_ID = ba.ID
                    INNER JOIN {SqlQuerySyntaxHelper.DbSchemaName(_customer.DatabaseType)}.ENTITY_TYPE AS et ON ba.ENTITY_TYPE_ID = et.ID
                    WHERE ca.ALIAS = @{nameof(alias)} {SqlQuerySyntaxHelper.Limit(_customer.DatabaseType, "1")}",
                                                                                      new { alias });

                return(actionInfo);
            }
        }
Exemple #6
0
        public void ResetFreezing(int[] productIds)
        {
            if (string.IsNullOrEmpty(_freezeFieldName))
            {
                return;
            }
            int userId = _userProvider.GetUserId();

            List <string> freezeFieldQueries  = new List <string>();
            var           extensionFreezeMeta = GetExtensionsFreezeMeta();
            var           dbConnector         = GetConnector();


            string idsQueryPart = SqlQuerySyntaxHelper.IdList(dbConnector.DatabaseType, "@Ids", "Ids");

            foreach (ExtentionFreezeMetaRow row in extensionFreezeMeta)
            {
                freezeFieldQueries.Add($@"SELECT {row.ContentId} AS ContentId, CONTENT_ITEM_ID AS Id, {row.AttributeId} AS FieldId  
						FROM content_{row.ContentId}_united
						WHERE visible = 1 AND archive = 0
								AND {row.AttributeName} in (
									SELECT Id 
									FROM {idsQueryPart})"                                    );
            }

            var productFreezeMeta = GetProductFreezeMeta();

            foreach (ProductFreezeMetaRow row in productFreezeMeta)
            {
                freezeFieldQueries.Add($@"SELECT {row.ContentId} AS ContentId, CONTENT_ITEM_ID AS Id, {row.AttributeId} AS FieldId 
						FROM content_{row.ContentId}_united
						WHERE visible = 1 AND archive = 0 
								AND CONTENT_ITEM_ID in (
									SELECT Id 
									FROM {idsQueryPart})"                                    );
            }
            var dbCommand = dbConnector.CreateDbCommand(string.Join("\nunion all\n", freezeFieldQueries));

            dbCommand.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@Ids", productIds, dbConnector.DatabaseType));

            var freezeDatesData = dbConnector.GetRealData(dbCommand);
            var products        = freezeDatesData.AsEnumerable()
                                  .Select(Converter.ToModelFromDataRow <ProductDescriptor>)
                                  .ToArray();

            foreach (var g in products.GroupBy(p => new { p.ContentId, p.FieldId }))
            {
                var extentionContentId = g.Key.ContentId;
                var fieldIds           = new[] { g.Key.FieldId };

                var values = g.Select(p => new Dictionary <string, string>
                {
                    { FieldName.ContentItemId, p.Id.ToString() },
                    { _freezeFieldName, string.Empty }
                }).ToArray();

                dbConnector.ImportToContent(extentionContentId, values, userId, fieldIds);
            }
        }
Exemple #7
0
        private string GetRegionLinkIdQuery(int siteId)
        {
            string query =
                $@"SELECT {SqlQuerySyntaxHelper.Top(_uow.DatabaseType, "1")} |QPAbstractItem.Regions| FROM |QPAbstractItem| WHERE |QPAbstractItem.Regions|
                IS NOT NULL {SqlQuerySyntaxHelper.Limit(_uow.DatabaseType, "1")}";

            return(_netNameQueryAnalyzer.PrepareQuery(query, siteId, false, true));
        }
Exemple #8
0
        private List <DataRow> GetArticlesForExport(IEnumerable <ExportSettings.FieldSetting> fieldsToExpand)
        {
            var dbType = QPContext.DatabaseType;
            var sb     = new StringBuilder();

            if (_settings.FieldNames.Any())
            {
                foreach (var s in _settings.FieldNames)
                {
                    sb.AppendFormat(", {0}", s);
                }
            }

            var ns = SqlQuerySyntaxHelper.DbSchemaName(dbType);

            foreach (var field in fieldsToExpand)
            {
                if (field.ExcludeFromSQLRequest)
                {
                    sb.AppendFormat(", NULL as {0}", field.Alias);
                }
                else if (field.ExactType == FieldExactTypes.O2MRelation)
                {
                    var fieldName = string.Join(" + '; ' + ", GetParts(field));
                    sb.Append($", {fieldName} as {SqlQuerySyntaxHelper.EscapeEntityName(dbType, field.Alias)}");
                }
                else if (field.ExactType == FieldExactTypes.M2ORelation && field.Related.Any())
                {
                    var related            = field.Related.First(f => !f.IsRelation).Id;
                    var contentItemIdField = dbType == DatabaseType.Postgres ? "content_item_id::integer" : "content_item_id";

                    sb.Append($", {ns}.qp_m2o_titles(base.{contentItemIdField}, {related}, {field.RelatedAttributeId}, 255) as {SqlQuerySyntaxHelper.EscapeEntityName(dbType, field.Alias)}");
                }
                else
                {
                    var contentCondition = field.FromExtension
                        ? $"{SqlQuerySyntaxHelper.EscapeEntityName(dbType, $"ex{field.ContentName}")}.content_item_id"
                        : "base.content_item_id";

                    if (dbType == DatabaseType.Postgres)
                    {
                        contentCondition += "::integer";
                    }
                    sb.Append($", {ns}.qp_link_titles({field.LinkId}, {contentCondition}, {field.RelatedAttributeId}, 255) as {SqlQuerySyntaxHelper.EscapeEntityName(dbType, field.Alias)}");
                }
            }

            var stepLength = Math.Min(_itemsPerStep, _ids.Length - StartFrom + 1);
            var stepIds    = new int[stepLength];

            Array.Copy(_ids, StartFrom - 1, stepIds, 0, stepLength);

            var orderBy = string.IsNullOrEmpty(_settings.OrderByField) ? IdentifierFieldName : _settings.OrderByField;
            var archive = _settings.IsArchive ? "1": "0";
            var filter  = $"base.content_item_id in ({string.Join(",", stepIds)}) and base.archive = {archive}";

            return(ArticleRepository.GetArticlesForExport(_contentId, _settings.ExtensionsStr, sb.ToString(), filter, 1, _itemsPerStep, orderBy, fieldsToExpand));
        }
Exemple #9
0
        public static T[] GetFieldValues <T>(DBConnector localdbConnector, int contentId, string fieldName, int[] ids = null, bool isAsync = false)
        {
            var asyncString = isAsync ? "_async" : string.Empty;
            var idsString   = ids != null ? $"where content_item_id in ({IdsStr(ids)})" : string.Empty;
            var fn          = SqlQuerySyntaxHelper.FieldName(localdbConnector.DatabaseType, fieldName);
            var sql         = $"select {fn} from content_{contentId}{asyncString} {idsString} order by content_item_id asc";

            return(localdbConnector.GetRealData(sql).Select().Select(row => ConvertHelpers.ChangeType <T>(row[fieldName])).ToArray());
        }
Exemple #10
0
        public static void UpdateUnsentNotifications(DbConnection connection, IEnumerable <int> ids)
        {
            var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
            var query  = $@"UPDATE EXTERNAL_NOTIFICATION_QUEUE SET
				TRIES = TRIES + 1,
				MODIFIED = {SqlQuerySyntaxHelper.Now(dbType)}
			WHERE ID IN (SELECT Id FROM {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")})"            ;

            ExecuteIdsQuery(connection, query, ids);
        }
Exemple #11
0
        private static void ExecuteIdsQuery(DbConnection connection, string query, IEnumerable <int> ids)
        {
            using (var cmd = DbCommandFactory.Create(query, connection))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, DatabaseTypeHelper.ResolveDatabaseType(connection)));

                cmd.ExecuteNonQuery();
            }
        }
Exemple #12
0
            private string GetCommonQuery(string method, DatabaseType dbType)
            {
                int authorizationCoontentId = GetContentId(SettingsTitles.API_AUTHORIZATION_CONTENT_ID);
                int tokensCoontentId        = GetContentId(SettingsTitles.HIGHLOAD_API_USERS_CONTENT_ID);
                var fieldName = method == "GET" ? "Read" : "Write";
                var field     = SqlQuerySyntaxHelper.EscapeEntityName(dbType, fieldName);
                var qp        = SqlQuerySyntaxHelper.EscapeEntityName(dbType, "Qp User");
                var token     = SqlQuerySyntaxHelper.EscapeEntityName(dbType, "Token User");

                return(string.Format(CommonQueryTemplate, authorizationCoontentId, tokensCoontentId, field, qp, token));
            }
        public static void DeleteSentNotifications(DbConnection connection)
        {
            var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
            var query  = $"DELETE FROM SYSTEM_NOTIFICATION_QUEUE WHERE SENT = {SqlQuerySyntaxHelper.ToBoolSql(dbType, true)}";

            using (var cmd = DbCommandFactory.Create(query, connection))
            {
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
            }
        }
        public static void UpdateUnsentNotifications(DbConnection connection, IEnumerable <int> ids, string lastExceptionMessage = null)
        {
            var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
            var query  = $@"UPDATE SYSTEM_NOTIFICATION_QUEUE SET
				TRIES = TRIES + 1,
                LastExceptionMessage = @lastExceptionMessage,
				MODIFIED = {SqlQuerySyntaxHelper.Now(dbType)}
			WHERE ID IN (SELECT Id FROM {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")})"            ;

            ExecuteIdsQuery(connection, query, ids, lastExceptionMessage);
        }
        public static void UpdateSentNotifications(DbConnection connection, IEnumerable <int> ids)
        {
            var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
            var query  = $@"UPDATE SYSTEM_NOTIFICATION_QUEUE SET
				SENT = {SqlQuerySyntaxHelper.ToBoolSql(dbType, true)},
                LastExceptionMessage = NULL,
				MODIFIED = {SqlQuerySyntaxHelper.Now(dbType)}
			WHERE ID IN (SELECT Id FROM {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")})"            ;

            ExecuteIdsQuery(connection, query, ids);
        }
 public AuthenticationService(DBConnector connector)
 {
     _connector     = connector;
     _clearSIDQuery = $@"UPDATE sessions_log SET sid = NULL {
             SqlQuerySyntaxHelper.Output(_connector.DatabaseType, new[] { "USER_ID", "SESSION_ID" })
         } WHERE sid = @sid {SqlQuerySyntaxHelper.Returning(_connector.DatabaseType, new[] { "USER_ID", "SESSION_ID" })}";
     _tokenQuery    = $@"select UserId, ExpirationDate
             from access_token
             where Token = @token and Application = @application and {
                     SqlQuerySyntaxHelper.Now(_connector.DatabaseType)
                  } < ExpirationDate";
 }
        private static void ExecuteIdsQuery(DbConnection connection, string query, IEnumerable <int> ids, string lastExceptionMessage = null)
        {
            using (var cmd = DbCommandFactory.Create(query, connection))
            {
                var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, dbType));
                cmd.Parameters.AddWithValue("@lastExceptionMessage", lastExceptionMessage);

                cmd.ExecuteNonQuery();
            }
        }
        public static void InsertNotifications(DbConnection connection, string notificationsXml)
        {
            var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
            var query  = dbType == DatabaseType.Postgres ? PgInsertNotificationsQuery : SqlInsertNotificationsQuery;

            using (var cmd = DbCommandFactory.Create(query, connection))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@notifications", notificationsXml, dbType));
                cmd.ExecuteNonQuery();
            }
        }
Exemple #19
0
        private static IEnumerable <string> GetParts(ExportSettings.FieldSetting field)
        {
            var parts              = new List <string>();
            var dbType             = QPContext.DatabaseType;
            var ns                 = SqlQuerySyntaxHelper.DbSchemaName(dbType);
            var contentItemIdField = dbType == DatabaseType.Postgres ? "content_item_id::integer" : "content_item_id";

            if (field.Related == null || !field.Related.Any())
            {
                parts.Add($"{SqlQuerySyntaxHelper.CastToString(dbType, field.TableAlias)}.content_item_id");
            }
            else
            {
                foreach (var f in field.Related)
                {
                    switch (f.ExactType)
                    {
                    case FieldExactTypes.M2MRelation:

                        parts.Add($"{ns}.qp_link_titles({f.LinkId.Value}, {field.TableAlias}.{contentItemIdField}, {f.RelatedAttributeId}, 255)");
                        break;

                    case FieldExactTypes.O2MRelation:
                        parts.Add($"{SqlQuerySyntaxHelper.CastToString(dbType, $"{f.TableAlias}.{SqlQuerySyntaxHelper.EscapeEntityName(dbType, f.RelatedAttributeName)}")}");
                        break;

                    case FieldExactTypes.M2ORelation:

                        parts.Add($"{ns}.qp_m2o_titles(base.{contentItemIdField}, {f.Id}, {field.RelatedAttributeId}, 255)");
                        break;

                    default:
                        parts.Add(new[]
                        {
                            FieldExactTypes.Date,
                            FieldExactTypes.DateTime,
                            FieldExactTypes.Time,
                            FieldExactTypes.Textbox,
                            FieldExactTypes.VisualEdit,
                            FieldExactTypes.Numeric,
                            FieldExactTypes.Classifier
                        }.Contains(f.ExactType)
                                ? $"coalesce({SqlQuerySyntaxHelper.CastToString(dbType, $"{field.TableAlias}.{SqlQuerySyntaxHelper.EscapeEntityName(dbType, f.Name)}")}, '')"
                                : $"coalesce( {field.TableAlias}.{SqlQuerySyntaxHelper.EscapeEntityName(dbType, f.Name)}, '')");
                        break;
                    }
                }
            }

            return(parts);
        }
Exemple #20
0
        private static string GetExtensions(IEnumerable <Content> extensionContents)
        {
            var sb = new StringBuilder();

            foreach (var content in extensionContents)
            {
                var name      = content.Fields.Single(f => f.Aggregated).Name;
                var dbType    = QPContext.DatabaseType;
                var tableName = SqlQuerySyntaxHelper.EscapeEntityName(dbType, $"ex{content.Name}");
                sb.Append($" LEFT JOIN CONTENT_{content.Id}_united {tableName} ON {tableName}.{SqlQuerySyntaxHelper.EscapeEntityName(dbType, name)} = base.CONTENT_ITEM_ID ");
            }

            return(sb.ToString());
        }
Exemple #21
0
        /// <summary>
        /// Возвращает информацию о столбцах запроса
        /// </summary>
        internal static IEnumerable <UserQueryColumn> GetQuerySchema(string userQuery)
        {
            using (var scope = new QPConnectionScope())
            {
                var viewName          = $"uq_v_test_{DateTime.Now.Ticks}";
                var schema            = SqlQuerySyntaxHelper.DbSchemaName(QPContext.DatabaseType);
                var createTestViewSql = $"CREATE VIEW {schema}.{viewName} AS {userQuery}";
                RunCreateViewDdl(createTestViewSql);

                var dttU = Common.GetViewColumnUsage(scope.DbConnection, viewName);
                DropView(viewName);

                return(DataTableToUserQueryColumns(dttU));
            }
        }
Exemple #22
0
        public void AddFormToContent_ThrowsException_ValidateConstraintForDataConflict()
        {
            var titleName  = DbConnector.FieldName(Global.SiteId, ContentName, "Title");
            var numberName = DbConnector.FieldName(Global.SiteId, ContentName, "Number");
            var fn         = SqlQuerySyntaxHelper.FieldName(DbConnector.DatabaseType, "Title");
            var cmd        = DbConnector.CreateDbCommand($"select content_item_id from content_{ContentId}_united where {fn} <> 'Name2'");
            var id         = (int)(decimal)DbConnector.GetRealScalarData(cmd);
            var article1   = new Hashtable
            {
                [titleName]  = "Name2",
                [numberName] = "9,5"
            };

            Assert.That(() => { id = DbConnector.AddFormToContent(Global.SiteId, ContentName, "Published", ref article1, id); }, Throws.Exception.TypeOf <QpInvalidAttributeException>().And.Message.Contains("Unique constraint violation"), "Duplicate of test data should violate rules");
        }
Exemple #23
0
        public string TranslateSortExpression(string sortExpression, DatabaseType dbType)
        {
            if (string.IsNullOrEmpty(sortExpression))
            {
                sortExpression = $"{SqlQuerySyntaxHelper.EscapeEntityName(dbType, "ORDER")} ASC";
            }

            var replaces = new Dictionary <string, string>
            {
                { "LastModifiedByUser", "LOGIN" },
                { "ActionTypeName", "ACTION_TYPE_NAME" },
                { "EntityTypeName", "ENTITY_TYPE_NAME" }
            };

            return(TranslateHelper.TranslateSortExpression(sortExpression, replaces));
        }
Exemple #24
0
        public void MassUpdate_ThrowsException_ValidateConstraintForDataConflict()
        {
            var values   = new List <Dictionary <string, string> >();
            var fn       = SqlQuerySyntaxHelper.FieldName(DbConnector.DatabaseType, "Title");
            var cmd      = DbConnector.CreateDbCommand($"select content_item_id from content_{ContentId}_united where {fn} <> 'Name2'");
            var id       = (decimal)DbConnector.GetRealScalarData(cmd);
            var article1 = new Dictionary <string, string>
            {
                [FieldName.ContentItemId] = id.ToString(CultureInfo.InvariantCulture),
                ["Title"]  = "Name2",
                ["Number"] = "9,5"
            };

            values.Add(article1);
            Assert.That(() => DbConnector.MassUpdate(ContentId, values, 1), Throws.Exception.TypeOf <QpInvalidAttributeException>().And.Message.Contains("for content articles"), "Duplicate of test data should violate rules");
        }
Exemple #25
0
        protected override string GetQuery()
        {
            var limitsContentId  = SettingsService.GetSetting(SettingsTitles.HIGHLOAD_API_LIMITS_CONTENT_ID);
            var methodsContentId = SettingsService.GetSetting(SettingsTitles.HIGHLOAD_API_METHODS_CONTENT_ID);
            var usersContentId   = SettingsService.GetSetting(SettingsTitles.HIGHLOAD_API_USERS_CONTENT_ID);


            if (string.IsNullOrEmpty(limitsContentId) || string.IsNullOrEmpty(methodsContentId) || string.IsNullOrEmpty(usersContentId))
            {
                return(null);
            }

            var user = SqlQuerySyntaxHelper.EscapeEntityName(_dbType, "User");

            return(string.Format(QueryTemplate, limitsContentId, methodsContentId, usersContentId, user));
        }
Exemple #26
0
        private string[] GetFieldNames(IEnumerable <Content> extensionContents)
        {
            var result = new List <string>();
            var dbType = QPContext.DatabaseType;
            var fields = GetBaseFields().Select(s => $"base.{SqlQuerySyntaxHelper.EscapeEntityName(dbType, s.Name)}");

            result.AddRange(fields);

            foreach (var content in extensionContents)
            {
                var template = $"{Escape(dbType, "ex{0}")}.{Escape(dbType, "{1}")} {Escape(dbType, "{0}.{1}")}";
                fields = GetExtensionFields(content, template, dbType);
                result.AddRange(fields);
            }

            return(result.ToArray());
        }
Exemple #27
0
        public int[] GetFrozenProductIds(int[] productIds)
        {
            if (string.IsNullOrEmpty(_freezeFieldName))
            {
                return(new int[0]);
            }

            List <string> freezeFieldQueries  = new List <string>();
            var           extensionFreezeMeta = GetExtensionsFreezeMeta();
            var           dbConnector         = GetConnector();

            string idsQueryPart = SqlQuerySyntaxHelper.IdList(dbConnector.DatabaseType, "@Ids", "Ids");

            foreach (ExtentionFreezeMetaRow row in extensionFreezeMeta)
            {
                freezeFieldQueries.Add($@"SELECT {row.AttributeName} Id FROM content_{row.ContentId}_united
						WHERE visible = 1 AND archive = 0
								AND {_freezeFieldName} >= @date 
								AND {row.AttributeName} in (
									SELECT Id 
									FROM {idsQueryPart})"                                    );
            }

            var productFreezeMeta = GetProductFreezeMeta();

            foreach (ProductFreezeMetaRow row in productFreezeMeta)
            {
                freezeFieldQueries.Add($@"SELECT CONTENT_ITEM_ID Id FROM content_{row.ContentId}_united
						WHERE visible = 1 AND archive = 0 
								AND {_freezeFieldName} >= @date 
								AND CONTENT_ITEM_ID in (
									SELECT Id 
									FROM {idsQueryPart})"                                    );
            }

            var dbCommand = dbConnector.CreateDbCommand(string.Join("\nunion all\n", freezeFieldQueries));

            dbCommand.Parameters.AddWithValue("@date", DateTime.Now);
            dbCommand.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@Ids", productIds, dbConnector.DatabaseType));

            var freezeDatesData = dbConnector.GetRealData(dbCommand);
            var ids             = GetIds(freezeDatesData);

            return(ids);
        }
Exemple #28
0
        public static Dictionary <int, int[]> GetContentIds(IEnumerable <int> ids, Customer customer)
        {
            var dbConnector = new DBConnector(customer.ConnectionString, customer.DatabaseType);
            var idList      = SqlQuerySyntaxHelper.IdList(customer.DatabaseType, "@Ids", "Ids");
            var dbCommand   = dbConnector.CreateDbCommand($@"SELECT CONTENT_ID, CONTENT_ITEM_ID 
					FROM CONTENT_ITEM 
					{SqlQuerySyntaxHelper.WithNoLock(customer.DatabaseType)} 
				WHERE CONTENT_ITEM_ID IN (SELECT ID FROM {idList})"                );

            dbCommand.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@Ids", ids, customer.DatabaseType));

            var dt = dbConnector.GetRealData(dbCommand);

            return(dt
                   .AsEnumerable()
                   .GroupBy(x => (int)(decimal)x["CONTENT_ID"])
                   .ToDictionary(x => x.Key, x => x.Select(y => (int)(decimal)y["CONTENT_ITEM_ID"]).ToArray()));
        }
        private string GetSqlCommandSelect(int contentId)
        {
            string select = null;

            if (!string.IsNullOrEmpty(Fields))
            {
                var orderBy      = GetSqlCommandOrderBy();
                var orderByAttrs = string.IsNullOrEmpty(orderBy)
                    ? new string[] { }
                    : orderBy
                .Split(',')
                .Select(n => n.Trim())
                .Select(n => CRegex.Replace(AscRegex.Replace(DescRegex.Replace(n, ""), ""), ""))
                .Select(n => n.Trim().Replace("[", "").Replace("]", ""))
                .ToArray();

                var attrs = new HashSet <string>(
                    DbConnector.GetContentAttributeObjects(contentId)
                    .Select(n => n.Name.ToLowerInvariant())
                    .Union(new[] { "content_item_id", "archive", "visible", "created", "modified", "last_modified_by" })
                    );

                select = string.Join(", ", Fields
                                     .Split(',')
                                     .Select(n => n.Trim().Replace("[", "").Replace("]", ""))
                                     .Union(orderByAttrs, StringComparer.InvariantCultureIgnoreCase)
                                     .Where(n => attrs.Contains(n.ToLowerInvariant()))
                                     .Select(n => SqlQuerySyntaxHelper.FieldName(DbConnector.DatabaseType, n))
                                     .ToArray()
                                     );
            }

            if (string.IsNullOrEmpty(select))
            {
                select = "c.*";
            }

            if (UseSecurity && !FilterRecords)
            {
                select += ", IsNull(pi.permission_level, 0) as current_permission_level ";
            }

            return(select);
        }
Exemple #30
0
        public IDictionary <int, AbstractItemExtensionCollection> GetAbstractItemExtensionlessData(IEnumerable <int> ids,
                                                                                                   ContentPersistentData baseContent,
                                                                                                   bool isStage, IDbTransaction transaction = null)
        {
            var idListTable = SqlQuerySyntaxHelper.IdList(UnitOfWork.DatabaseType, "@ids", "ids");
            var withNoLock  = SqlQuerySyntaxHelper.WithNoLock(UnitOfWork.DatabaseType);

            string extFieldsQuery =
                $@"SELECT * FROM {baseContent.GetTableName(isStage)} ai {withNoLock}
                    INNER JOIN {idListTable} on Id = ai.Content_item_id";

            using (var command = UnitOfWork.Connection.CreateCommand())
            {
                command.CommandText = extFieldsQuery;
                command.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@Ids", ids, UnitOfWork.DatabaseType));
                command.Transaction = transaction;
                return(LoadAbstractItemExtension(command));
            }
        }