📄 sqlgenerator.cs
字号:
//------------------------------------------------------------------------------
// <copyright company="Telligent Systems">
// Copyright (c) Telligent Systems Corporation. All rights reserved.
// </copyright>
//------------------------------------------------------------------------------
using System;
using System.Text;
using CommunityServer.Blogs.Components;
using CommunityServer.Components;
using CommunityServer.Galleries.Components;
namespace CommunityServer.Data
{
/// <summary>
/// Helper class for generating dynamic SQL.
/// </summary>
public class SqlGenerator
{
private SqlGenerator()
{
}
#region GetSearchFullTextSQL(). Added by backend@gmail.com
public static string GetSearchFullTextSQL(SearchQuery query, SearchTerms terms, int settingsID, ApplicationType appType)
{
string searchSQL = "SELECT DISTINCT PostID, P.SectionID, Weight=0, P.PostDate FROM cs_Posts P, cs_Sections F WHERE {0} {1} AND F.SectionID = P.SectionID AND P.SettingsID = {2} AND F.ApplicationType = {3} AND F.IsSearchable = 1 {4} ORDER BY PostDate DESC";
string pattern = "(CONTAINS(P.*, '{0}'))";
string[] clauses = new string[5];
clauses[0] = BuildKeywordClauses(pattern, terms);
clauses[1] = BuildSectionAndUserClauses(query);
if (clauses[0] == null && clauses[1] == null)
// Can't search nothing.
throw new CSException(CSExceptionType.SearchNoResults, "Search nothing");
if (clauses[0] != null && clauses[1] != null)
{
clauses[0] += " AND ";
}
clauses[2] = settingsID.ToString();
clauses[3] = ((int)appType).ToString();
clauses[4] = BuildGroupClauses(query);
return string.Format(searchSQL, clauses);
}
#endregion
#region GetSearchSimpleSQL(). Added by backend@gmail.com
public static string GetSearchSimpleSQL(SearchQuery query, SearchTerms terms, int settingsID, ApplicationType appType)
{
string searchSQL = "SELECT DISTINCT PostID, P.SectionID, Weight=0, P.PostDate FROM cs_Posts P, cs_Sections F WHERE {0} {1} AND F.SectionID = P.SectionID AND P.SettingsID = {2} AND F.ApplicationType = {3} AND F.IsSearchable = 1 {4} ORDER BY PostDate DESC";
string pattern = "(Subject like '%{0}%' OR Body like '%{0}%')";
string[] clauses = new string[5];
clauses[0] = BuildKeywordClauses(pattern, terms);
clauses[1] = BuildSectionAndUserClauses(query);
if (clauses[0] == null && clauses[1] == null)
// Can't search nothing.
throw new CSException(CSExceptionType.SearchNoResults, "Search nothing");
if (clauses[0] != null && clauses[1] != null)
{
clauses[0] += " AND ";
}
clauses[2] = settingsID.ToString();
clauses[3] = ((int)appType).ToString();
clauses[4] = BuildGroupClauses(query);
return string.Format(searchSQL, clauses);
}
#endregion
#region GetSearchCSIndexerSQL
/// <summary>
/// Builds the baseline query for searching against the searchbarrel table.
/// </summary>
public static string GetSearchCSIndexerSQL(SearchQuery query, SearchTerms terms, int settingsID, ApplicationType appType)//, out string searchSQL, out string recordCount)
{
bool hasORterms = false;
string searchSQL = "SELECT DISTINCT B0.PostID, B0.SectionID, Weight = ({0}), P.PostDate FROM {1}, cs_Posts P, cs_Sections F WHERE {2} {3} {4} AND F.SectionID = P.SectionID AND P.SettingsID = {5} AND F.ApplicationType = {7} AND F.IsSearchable = 1 {6} ORDER BY Weight DESC, PostDate DESC";
string orSQL = "(";
string[] clauses = new string[8];
// OR clause in search terms
//
if (terms.Or.Length > 1)
{
hasORterms = true;
for (int i = 0; i < terms.Or.Length; i++)
{
string barrel = "B0.WordHash = {0}";
orSQL += string.Format(barrel, terms.Or[i]);
if ((i+1) < terms.Or.Length)
{
orSQL += " OR ";
}
else
{
clauses[2] += orSQL += ") AND ";
}
}
}
// AND clause in search terms
//
if (terms.And.Length > 0)
{
for (int i = 0; i < terms.And.Length; i++)
{
string barrel = "B{0}";
// Build the clauses
if (hasORterms)
clauses[2] += string.Format(barrel, (i+1)) + ".WordHash = " + terms.And[i];
else
clauses[2] += string.Format(barrel, i) + ".WordHash = " + terms.And[i];
if ((i+1) < terms.And.Length)
{
clauses[2] += " AND ";
}
}
clauses[2] += " AND ";
}
// Modified by backend@gmail.com
clauses[4] = BuildSectionAndUserClauses(query);
// Main Loop for AND clause in search terms
//
int loopCount = terms.And.Length;
if (hasORterms)
loopCount ++;
if (loopCount > 0)
{
for (int i = 0; i < loopCount; i++)
{
string barrel = "B{0}";
clauses[0] += string.Format(barrel, i) + ".Weight";
clauses[1] += "cs_SearchBarrel " + string.Format(barrel, i);
clauses[3] += string.Format(barrel, i) + ".PostID = P.PostID";
if ((i+1) < loopCount)
{
clauses[0] += " + ";
clauses[1] += ", ";
clauses[3] += " AND ";
}
}
}
else
{
clauses[0] = "0";
clauses[1] = "cs_SearchBarrel B0";
clauses[3] = "B0.PostID = P.PostID";
}
clauses[5] = settingsID.ToString();
clauses[7] = ((int)appType).ToString();
// Added by backend@gmail.com
clauses[6] = BuildGroupClauses(query);
return string.Format(searchSQL, clauses);
}
#endregion
#region SearchText
/// <summary>
/// Builds the baseline query for searching against the searchbarrel table.
/// </summary>
// public static string SearchText(SearchQuery query, SearchTerms terms, int settingsID, ApplicationType appType)//, out string searchSQL, out string recordCount)
// {
// bool hasORterms = false;
// string searchSQL = "SELECT DISTINCT B0.PostID, B0.SectionID, Weight = ({0}), P.PostDate FROM {1}, cs_Posts P, cs_Sections F WHERE {2} {3} {4} AND F.SectionID = P.SectionID AND P.SettingsID = {5} AND F.ApplicationType = {7} AND F.IsSearchable = 1 {6} ORDER BY Weight DESC, PostDate DESC";
// //recordCount = "SELECT TotalRecords = COUNT(DISTINCT B0.PostID) FROM {1}, cs_Posts P {6} WHERE {2} {3} AND {4} AND P.SettingsID = {5} {7}";
// string orSQL = "(";
// string[] clauses = new string[8];
//
// // OR clause in search terms
// //
// if (terms.Or.Length > 1)
// {
// hasORterms = true;
//
// for (int i = 0; i < terms.Or.Length; i++)
// {
// string barrel = "B0.WordHash = {0}";
//
// orSQL += string.Format(barrel, terms.Or[i]);
//
// if ((i+1) < terms.Or.Length)
// {
// orSQL += " OR ";
// }
// else
// {
// clauses[2] += orSQL += ") AND ";
// }
//
// }
// }
//
// // AND clause in search terms
// //
// if (terms.And.Length > 0)
// {
//
// for (int i = 0; i < terms.And.Length; i++)
// {
// string barrel = "B{0}";
//
// // Build the clauses
// if (hasORterms)
// clauses[2] += string.Format(barrel, (i+1)) + ".WordHash = " + terms.And[i];
// else
// clauses[2] += string.Format(barrel, i) + ".WordHash = " + terms.And[i];
//
// if ((i+1) < terms.And.Length)
// {
// clauses[2] += " AND ";
// }
//
// }
//
// clauses[2] += " AND ";
// }
//
// if(query.SectionsToSearch != null || query.SectionsToFilter != null || query.UsersToSearch != null)
// {
// StringBuilder sb = new StringBuilder();
//
//
// // FORUMS
// //
// if (query.SectionsToSearch != null && query.SectionsToSearch.Length > 0)
// {
// sb.Append(" AND B0.SectionID IN (");
// for (int i = 0; i < query.SectionsToSearch.Length; i++)
// {
// sb.Append(query.SectionsToSearch[i]);
// // Build the clauses
// // clauses[4] += barrel + query.SectionsToSearch[i];
//
// if ((i+1) < query.SectionsToSearch.Length)
// {
// sb.Append(",");
// }
// }
//
// sb.Append(")");
// }
//
// if(query.SectionsToFilter != null && query.SectionsToFilter.Count > 0)
// {
//
// sb.Append(" AND B0.SectionID IN (");
// for(int i =0; i <query.SectionsToFilter.Count; i++)
// {
// sb.Append(((Section)query.SectionsToFilter[i]).SectionID.ToString() );
// if ((i+1) < query.SectionsToFilter.Count)
// {
// sb.Append(",");
// }
// }
// sb.Append(")");
// }
//
// // Users
// //
// if (query.UsersToSearch != null && query.UsersToSearch.Length > 0 && query.UsersToSearch[0] != "")
// {
// string usersSearch = string.Empty;
//
// sb.Append(" AND P.UserID IN (");
// for (int i = 0; i < query.UsersToSearch.Length; i++)
// {
// string barrel = "P.UserID = ";
// sb.Append(query.UsersToSearch[i]);
//
// // Build the clauses
// usersSearch += barrel + query.UsersToSearch[i];
//
// if ((i+1) < query.UsersToSearch.Length)
// {
// sb.Append(",");
// }
//
//
// }
// sb.Append(")");
//
// }
// clauses[4] = sb.ToString();
//
// }
//
// // Main Loop for AND clause in search terms
// //
// int loopCount = terms.And.Length;
//
// if (hasORterms)
// loopCount ++;
//
// if (loopCount > 0)
// {
// for (int i = 0; i < loopCount; i++)
// {
// string barrel = "B{0}";
//
// clauses[0] += string.Format(barrel, i) + ".Weight";
// clauses[1] += "cs_SearchBarrel " + string.Format(barrel, i);
// clauses[3] += string.Format(barrel, i) + ".PostID = P.PostID";
//
// if ((i+1) < loopCount)
// {
// clauses[0] += " + ";
// clauses[1] += ", ";
// clauses[3] += " AND ";
// }
// }
// }
// else
// {
// clauses[0] = "0";
// clauses[1] = "cs_SearchBarrel B0";
// clauses[3] = "B0.PostID = P.PostID";
// }
//
// clauses[5] = settingsID.ToString();
// clauses[7] = ((int)appType).ToString();
//
// if(query.GroupToSearch != null && query.GroupToSearch.Length > 0)
// {
// clauses[6] = " AND F.SectionID = P.SectionID AND (";
//
// for(int i = 0; i < query.GroupToSearch.Length; i++)
// {
// if(i != 0)
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -