/// <summary> /// 创建搜索缓存 /// </summary> /// <param name="cacheinfo">搜索缓存信息</param> /// <returns>搜索缓存id</returns> public int CreateSearchCache(SearchCacheInfo cacheinfo) { IDataParameter[] prams = { DbHelper.MakeInParam("@keywords",(DbType)SqlDbType.NVarChar,255,cacheinfo.Keywords), DbHelper.MakeInParam("@searchstring",(DbType)SqlDbType.NVarChar,255,cacheinfo.Searchstring), DbHelper.MakeInParam("@ip",(DbType)SqlDbType.VarChar,15,cacheinfo.Ip), DbHelper.MakeInParam("@uid",(DbType)SqlDbType.Int,4,cacheinfo.Uid), DbHelper.MakeInParam("@groupid",(DbType)SqlDbType.Int,4,cacheinfo.Groupid), DbHelper.MakeInParam("@postdatetime", (DbType)SqlDbType.DateTime, 8, DateTime.Parse(cacheinfo.Postdatetime)), DbHelper.MakeInParam("@expiration",(DbType)SqlDbType.VarChar,19,cacheinfo.Expiration), DbHelper.MakeInParam("@topics",(DbType)SqlDbType.Int,4,cacheinfo.Topics), DbHelper.MakeInParam("@tids",(DbType)SqlDbType.Text,0,cacheinfo.Tids) }; return Utils.StrToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}createsearchcache", BaseConfigs.GetTablePrefix), prams).ToString(), -1); }
/// <summary> /// 根据指定条件进行搜索 /// </summary> /// <param name="posttableid">帖子表id</param> /// <param name="userid">用户id</param> /// <param name="usergroupid">用户组id</param> /// <param name="keyword">关键字</param> /// <param name="posterid">发帖者id</param> /// <param name="type">搜索类型</param> /// <param name="searchforumid">搜索版块id</param> /// <param name="keywordtype">关键字类型</param> /// <param name="searchtime">搜索时间</param> /// <param name="searchtimetype">搜索时间类型</param> /// <param name="resultorder">结果排序方式</param> /// <param name="resultordertype">结果类型类型</param> /// <returns>如果成功则返回searchid, 否则返回-1</returns> public int Search(int posttableid, int userid, int usergroupid, string keyword, int posterid, string type, string searchforumid, int keywordtype, int searchtime, int searchtimetype, int resultorder, int resultordertype) { // 超过30分钟的缓存纪录将被删除 DatabaseProvider.GetInstance().DeleteExpriedSearchCache(); string sql = string.Empty; StringBuilder strTids = new StringBuilder(); SearchType searchType = SearchType.TopicTitle; switch (keywordtype) { case 0: searchType = SearchType.PostTitle; if (type == "digest") { searchType = SearchType.DigestTopic; } break; case 1: searchType = SearchType.PostContent; break; case 2: searchType = SearchType.SpacePostTitle; break; case 3: searchType = SearchType.AlbumTitle; break; case 8: searchType = SearchType.ByPoster; break; } switch (searchType) { case SearchType.All: break; case SearchType.DigestTopic: sql = GetSearchTopicTitleSQL(posterid, searchforumid, resultorder, resultordertype, 1, keyword); break; case SearchType.TopicTitle: sql = GetSearchTopicTitleSQL(posterid, searchforumid, resultorder, resultordertype, 0, keyword); break; case SearchType.PostTitle: sql = GetSearchTopicTitleSQL(posterid, searchforumid, resultorder, resultordertype, 0, keyword); break; case SearchType.PostContent: sql = GetSearchPostContentSQL(posterid, searchforumid, resultorder, resultordertype, searchtime, searchtimetype, posttableid, new StringBuilder(keyword)); break; case SearchType.SpacePostTitle: sql = GetSearchSpacePostTitleSQL(posterid, resultorder, resultordertype, searchtime, searchtimetype, keyword); break; case SearchType.AlbumTitle: sql = GetSearchAlbumTitleSQL(posterid, resultorder, resultordertype, searchtime, searchtimetype, keyword); break; case SearchType.ByPoster: sql = GetSearchByPosterSQL(posterid, posttableid); break; default: sql = GetSearchTopicTitleSQL(posterid, searchforumid, resultorder, resultordertype, 0, keyword); break; } #region /* // 关键词与作者至少有一个条件不为空 if (keyword.Equals(""))//按作者搜索 { if (type == "digest") { strSQL.AppendFormat("SELECT [tid] FROM [{0}topics] WHERE [digest]>0 AND [posterid]=", BaseConfigs.GetTablePrefix); strSQL.Append(posterid); } else if (type == "post") { strSQL.AppendFormat("SELECT [pid] FROM [{0}posts{1}] WHERE [posterid]=", BaseConfigs.GetTablePrefix, posttableid); strSQL.Append(posterid); } else { strSQL.AppendFormat("SELECT [tid] FROM [{0}topics] WHERE [posterid]=", BaseConfigs.GetTablePrefix); strSQL.Append(posterid); } //所属板块判断 if (!searchforumid.Equals("")) { strSQL.Append(" AND [fid] IN ("); strSQL.Append(searchforumid); strSQL.Append(")"); } strSQL.Append(" ORDER BY "); switch (resultorder) { case 1: strSQL.Append("[tid]"); break; case 2: strSQL.Append("[replies]"); break; case 3: strSQL.Append("[views]"); break; default: strSQL.Append("[postdatetime]"); break; } if (resultordertype == 1) { strSQL.Append(" ASC"); } else { strSQL.Append(" DESC"); } } else { // 过滤危险字符 keyword = Regex.Replace(keyword, "--|;|'|\"", "", RegexOptions.Compiled | RegexOptions.Multiline); StringBuilder strKeyWord = new StringBuilder(keyword); // 替换转义字符 strKeyWord.Replace("'", "''"); strKeyWord.Replace("%", "[%]"); strKeyWord.Replace("_", "[_]"); strKeyWord.Replace("[", "[[]"); // 将SQL查询条件循序指定为"forumid, posterid, 搜索时间范围, 关键词" if (keywordtype == 0) { strSQL.AppendFormat("SELECT [tid] FROM [{0}topics] WHERE", BaseConfigs.GetTablePrefix); if (!searchforumid.Equals("")) { strSQL.Append(" [fid] IN ("); strSQL.Append(searchforumid); strSQL.Append(") AND "); } if (posterid != -1) { strSQL.Append("[posterid]="); strSQL.Append(posterid); strSQL.Append(" AND "); } if (searchtime != 0) { strSQL.Append("[postdatetime]"); if (searchtimetype == 1) { strSQL.Append("<"); } else { strSQL.Append(">'"); } strSQL.Append(DateTime.Now.AddDays(searchtime * -1).ToString("yyyy-MM-dd 00:00:00")); strSQL.Append("'AND "); } string[] keywordlist = Utils.SplitString(strKeyWord.ToString(), " "); strKeyWord = new StringBuilder(); for (int i = 0; i < keywordlist.Length; i++) { strKeyWord.Append(" OR [title] "); strKeyWord.Append("LIKE '%"); strKeyWord.Append(keywordlist[i]); strKeyWord.Append("%' "); } strSQL.Append(strKeyWord.ToString().Substring(3)); strSQL.Append("ORDER BY "); switch (resultorder) { case 1: strSQL.Append("[tid]"); break; case 2: strSQL.Append("[replies]"); break; case 3: strSQL.Append("[views]"); break; default: strSQL.Append("[lastpost]"); break; } if (resultordertype == 1) { strSQL.Append(" ASC"); } else { strSQL.Append(" DESC"); } } else { string orderfield = "lastpost"; switch (resultorder) { case 1: orderfield = "tid"; break; case 2: orderfield = "replies"; break; case 3: orderfield = "views"; break; default: orderfield = "lastpost"; break; } strSQL.AppendFormat("SELECT DISTINCT [{0}posts{1}].[tid],[{0}topics].[{2}] FROM [{0}posts{1}] LEFT JOIN [{0}topics] ON [{0}topics].[tid]=[{0}posts{1}].[tid] WHERE ", BaseConfigs.GetTablePrefix, posttableid, orderfield); if (!searchforumid.Equals("")) { strSQL.AppendFormat("[{0}posts{1}].[fid] IN (", BaseConfigs.GetTablePrefix, posttableid); strSQL.Append(searchforumid); strSQL.Append(") AND "); } if (posterid != -1) { strSQL.AppendFormat("[{0}posts{1}].[posterid]=", BaseConfigs.GetTablePrefix, posttableid); strSQL.Append(posterid); strSQL.Append(" AND "); } if (searchtime != 0) { strSQL.AppendFormat("[{0}posts{1}].[postdatetime]", BaseConfigs.GetTablePrefix, posttableid); if (searchtimetype == 1) { strSQL.Append("<"); } else { strSQL.Append(">'"); } strSQL.Append(DateTime.Now.AddDays(searchtime).ToString("yyyy-MM-dd 00:00:00")); strSQL.Append("'AND "); } string[] keywordlist = Utils.SplitString(strKeyWord.ToString(), " "); strKeyWord = new StringBuilder(); for (int i = 0; i < keywordlist.Length; i++) { strKeyWord.Append(" OR "); if (GeneralConfigs.GetConfig().Fulltextsearch == 1) { strKeyWord.AppendFormat("CONTAINS(message, '\"*", BaseConfigs.GetTablePrefix, posttableid); strKeyWord.Append(keywordlist[i]); strKeyWord.Append("*\"') "); } else { strKeyWord.AppendFormat("[{0}posts{1}].[message] LIKE '%", BaseConfigs.GetTablePrefix, posttableid); strKeyWord.Append(keywordlist[i]); strKeyWord.Append("%' "); } } strSQL.Append(strKeyWord.ToString().Substring(3)); strSQL.AppendFormat("ORDER BY [{0}topics].", BaseConfigs.GetTablePrefix); switch (resultorder) { case 1: strSQL.Append("[tid]"); break; case 2: strSQL.Append("[replies]"); break; case 3: strSQL.Append("[views]"); break; default: strSQL.Append("[lastpost]"); break; } if (resultordertype == 1) { strSQL.Append(" ASC"); } else { strSQL.Append(" DESC"); } } } */ #endregion if (sql == string.Empty) { return -1; } IDataParameter[] prams2 = { DbHelper.MakeInParam("@searchstring",(DbType)SqlDbType.VarChar,255, sql), DbHelper.MakeInParam("@uid",(DbType)SqlDbType.Int,4,userid), DbHelper.MakeInParam("@groupid",(DbType)SqlDbType.Int,4,usergroupid) }; int searchid = Utils.StrToInt(DbHelper.ExecuteScalar(CommandType.Text, string.Format(@"SELECT TOP 1 [searchid] FROM [{0}searchcaches] WHERE [searchstring]=@searchstring AND [groupid]=@groupid", BaseConfigs.GetTablePrefix), prams2), -1); if (searchid > -1) { return searchid; } IDataReader reader; try { reader = DbHelper.ExecuteReader(CommandType.Text, sql); } catch { ConfirmFullTextEnable(); reader = DbHelper.ExecuteReader(CommandType.Text, sql); } int rowcount = 0; if (reader != null) { switch (searchType) { case SearchType.All: case SearchType.DigestTopic: case SearchType.TopicTitle: case SearchType.PostTitle: case SearchType.PostContent: strTids.Append("<ForumTopics>"); break; case SearchType.SpacePostTitle: strTids.Append("<SpacePosts>"); break; case SearchType.AlbumTitle: strTids.Append("<Albums>"); break; case SearchType.ByPoster: strTids = GetSearchByPosterResult(reader); SearchCacheInfo cacheinfo = new SearchCacheInfo(); cacheinfo.Keywords = keyword; cacheinfo.Searchstring = sql; cacheinfo.Postdatetime = Utils.GetDateTime(); cacheinfo.Topics = rowcount; cacheinfo.Tids = strTids.ToString(); cacheinfo.Uid = userid; cacheinfo.Groupid = usergroupid; cacheinfo.Ip = DNTRequest.GetIP(); cacheinfo.Expiration = Utils.GetDateTime(); reader.Close(); return CreateSearchCache(cacheinfo); } while (reader.Read()) { strTids.Append(reader[0].ToString()); strTids.Append(","); rowcount++; } if (rowcount > 0) { strTids.Remove(strTids.Length - 1, 1); switch (searchType) { case SearchType.All: case SearchType.DigestTopic: case SearchType.TopicTitle: case SearchType.PostTitle: case SearchType.PostContent: strTids.Append("</ForumTopics>"); break; case SearchType.SpacePostTitle: strTids.Append("</SpacePosts>"); break; case SearchType.AlbumTitle: strTids.Append("</Albums>"); break; } SearchCacheInfo cacheinfo = new SearchCacheInfo(); cacheinfo.Keywords = keyword; cacheinfo.Searchstring = sql; cacheinfo.Postdatetime = Utils.GetDateTime(); cacheinfo.Topics = rowcount; cacheinfo.Tids = strTids.ToString(); cacheinfo.Uid = userid; cacheinfo.Groupid = usergroupid; cacheinfo.Ip = DNTRequest.GetIP(); cacheinfo.Expiration = Utils.GetDateTime(); reader.Close(); return CreateSearchCache(cacheinfo); } reader.Close(); } return -1; }
/// <summary> /// 创建搜索缓存 /// </summary> /// <param name="cacheinfo">搜索缓存信息</param> /// <returns>搜索缓存id</returns> public int CreateSearchCache(SearchCacheInfo cacheInfo) { DbParameter[] parms = { DbHelper.MakeInParam("@keywords",(DbType)NpgsqlDbType.Varchar,255,cacheInfo.Keywords), DbHelper.MakeInParam("@searchstring",(DbType)NpgsqlDbType.Varchar,255,cacheInfo.Searchstring), DbHelper.MakeInParam("@ip",(DbType)NpgsqlDbType.Varchar,15,cacheInfo.Ip), DbHelper.MakeInParam("@uid",(DbType)NpgsqlDbType.Integer,4,cacheInfo.Uid), DbHelper.MakeInParam("@groupid",(DbType)NpgsqlDbType.Integer,4,cacheInfo.Groupid), DbHelper.MakeInParam("@postdatetime", (DbType)NpgsqlDbType.Timestamp, 8, DateTime.Parse(cacheInfo.Postdatetime)), DbHelper.MakeInParam("@expiration",(DbType)NpgsqlDbType.Varchar,19,cacheInfo.Expiration), DbHelper.MakeInParam("@topics",(DbType)NpgsqlDbType.Integer,4,cacheInfo.Topics), DbHelper.MakeInParam("@tids",(DbType)NpgsqlDbType.Text,0,cacheInfo.Tids) }; return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}createsearchcache", BaseConfigs.GetTablePrefix), parms), -1); }
/// <summary> /// 搜索 /// </summary> /// <param name="spaceenabled">空间是否开启</param> /// <param name="albumenable">相册是否开启</param> /// <param name="posttableid">帖子表id</param> /// <param name="userid">用户id</param> /// <param name="usergroupid">用户组id</param> /// <param name="keyword">关键字</param> /// <param name="posterid">发帖者id</param> /// <param name="searchType">搜索类型</param> /// <param name="searchforumid">搜索版块id</param> /// <param name="searchtime">搜索时间</param> /// <param name="searchtimetype">搜索时间类型</param> /// <param name="resultorder">结果排序方式</param> /// <param name="resultordertype">结果类型类型</param> /// <returns>如果成功则返回searchid, 否则返回-1</returns> public int Search(bool spaceEnabled, bool albumEnabled, int postTableId, int userId, int userGroupId, string keyWord, int posterId, SearchType searchType, string searchForumId, int searchTime, int searchTimeType, int resultOrder, int resultOrderType) { // 超过30分钟的缓存纪录将被删除 DatabaseProvider.GetInstance().DeleteExpriedSearchCache(); string sql = string.Empty; StringBuilder strTids = new StringBuilder(); switch (searchType) { case SearchType.TopicTitle: sql = GetSearchTopicTitleSQL(posterId, searchForumId, resultOrder, resultOrderType, searchTime, searchTimeType, 0, keyWord, postTableId); break; case SearchType.DigestTopic: sql = GetSearchTopicTitleSQL(posterId, searchForumId, resultOrder, resultOrderType, searchTime, searchTimeType, 1, keyWord, postTableId); break; case SearchType.ByPoster: sql = GetSearchByPosterSQL(spaceEnabled, albumEnabled, posterId, postTableId); break; case SearchType.PostContent: sql = GetSearchPostContentSQL(posterId, searchForumId, resultOrder, resultOrderType, searchTime, searchTimeType, postTableId, new StringBuilder(keyWord)); break; case SearchType.SpacePostTitle: sql = GetSearchSpacePostTitleSQL(posterId, resultOrder, resultOrderType, searchTime, searchTimeType, keyWord); break; case SearchType.AlbumTitle: sql = GetSearchAlbumTitleSQL(posterId, resultOrder, resultOrderType, searchTime, searchTimeType, keyWord); break; default: sql = GetSearchTopicTitleSQL(posterId, searchForumId, resultOrder, resultOrderType, searchTime, searchTimeType, 0, keyWord, postTableId); break; } if (Utils.StrIsNullOrEmpty(sql)) return -1; DbParameter[] prams2 = { DbHelper.MakeInParam("@searchstring",(DbType)SqlDbType.VarChar,255, sql), DbHelper.MakeInParam("@uid",(DbType)SqlDbType.Int,4,userId), DbHelper.MakeInParam("@groupid",(DbType)SqlDbType.Int,4,userGroupId) }; int searchid = Utils.StrToInt(DbHelper.ExecuteScalar(CommandType.Text, string.Format(@"SELECT TOP 1 [searchid] FROM [{0}searchcaches] WHERE [searchstring]=@searchstring AND [groupid]=@groupid", BaseConfigs.GetTablePrefix), prams2), -1); if (searchid > -1) return searchid; IDataReader reader; try { reader = DbHelper.ExecuteReader(CommandType.Text, sql); } catch { ConfirmFullTextEnable(); reader = DbHelper.ExecuteReader(CommandType.Text, sql); } int rowcount = 0; if (reader != null) { switch (searchType) { case SearchType.DigestTopic: case SearchType.TopicTitle: case SearchType.PostContent: strTids.Append("<ForumTopics>"); break; case SearchType.SpacePostTitle: strTids.Append("<SpacePosts>"); break; case SearchType.AlbumTitle: strTids.Append("<Albums>"); break; case SearchType.ByPoster: strTids = GetSearchByPosterResult(reader); SearchCacheInfo cacheinfo = new SearchCacheInfo(); cacheinfo.Keywords = keyWord; cacheinfo.Searchstring = sql; cacheinfo.Postdatetime = Utils.GetDateTime(); cacheinfo.Topics = rowcount; cacheinfo.Tids = strTids.ToString(); cacheinfo.Uid = userId; cacheinfo.Groupid = userGroupId; cacheinfo.Ip = DNTRequest.GetIP(); cacheinfo.Expiration = Utils.GetDateTime(); reader.Close(); return CreateSearchCache(cacheinfo); } while (reader.Read()) { strTids.Append(reader[0].ToString()); strTids.Append(","); rowcount++; } if (rowcount > 0) { strTids.Remove(strTids.Length - 1, 1); switch (searchType) { case SearchType.DigestTopic: case SearchType.TopicTitle: case SearchType.PostContent: strTids.Append("</ForumTopics>"); break; case SearchType.SpacePostTitle: strTids.Append("</SpacePosts>"); break; case SearchType.AlbumTitle: strTids.Append("</Albums>"); break; } SearchCacheInfo cacheinfo = new SearchCacheInfo(); cacheinfo.Keywords = keyWord; cacheinfo.Searchstring = sql; cacheinfo.Postdatetime = Utils.GetDateTime(); cacheinfo.Topics = rowcount; cacheinfo.Tids = strTids.ToString(); cacheinfo.Uid = userId; cacheinfo.Groupid = userGroupId; cacheinfo.Ip = DNTRequest.GetIP(); cacheinfo.Expiration = Utils.GetDateTime(); reader.Close(); return CreateSearchCache(cacheinfo); } reader.Close(); } return -1; }
/// <summary> /// 创建搜索缓存 /// </summary> /// <param name="cacheinfo">搜索缓存信息</param> /// <returns>搜索缓存id</returns> public static int CreateSearchCache(SearchCacheInfo cacheinfo) { return DatabaseProvider.GetInstance().CreateSearchCache(cacheinfo); }