/// <summary>
/// 获取post预览列表
/// </summary>
/// <param name="customSQL">自定义SQL</param>
/// <param name="treatedTags">经处理过的标签</param>
/// <param name="site">图站</param>
/// <param name="column">按此列排序</param>
/// <param name="ratingArray">分级</param>
/// <returns></returns>
public static DataTable SelectPostPreviewList(string customSQL, string treatedTags, Site site, PostOrderBy column, Rating[] ratingArray)
{
string selectColumns = string.Format("file_md5,{0}", column.ToString());
string ratingIN = string.Join(",", ratingArray.Select(s => string.Format("'{0}'", s.ToString().Substring(0, 1).ToLower())));
string tagsMATCH = string.IsNullOrWhiteSpace(treatedTags) ? string.Empty : string.Format(" AND id IN (SELECT docid FROM posts_fts4 WHERE tags MATCH '{0}')", treatedTags);
if (string.IsNullOrWhiteSpace(customSQL) == false)
{
customSQL = string.Format(" AND ({0})", string.Join(" AND ", customSQL.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)));
}
string commandText = string.Format("SELECT {0} FROM posts WHERE rating IN ({1}){2}{3}", selectColumns, ratingIN, tagsMATCH, customSQL);
SQLiteCommand command = SQLiteHelper.CreateCommand(DBConn.GetConn(site), commandText);
try
{
DataTable dt = SQLiteHelper.ExecuteDataTable(command);
return(dt);
}
catch
{
return(null);
}
}
/// <summary>
/// 获取偏好MD5列表
/// </summary>
/// <param name="mode">模式</param>
/// <param name="preferenceArray">需获取的偏好</param>
/// <returns></returns>
public static DataTable SelectPreferMD5(Mode mode, params Prefer[] selectPreferArray)
{
string commandText = string.Format("SELECT file_md5 FROM {0} WHERE prefer IN ({1})", mode.ToString(), string.Join(",", selectPreferArray.Select(s => s.ToString("d"))));
SQLiteCommand command = SQLiteHelper.CreateCommand(DBConn.prefer, commandText);
return(SQLiteHelper.ExecuteDataTable(command));
}
/// <summary>
/// 获取MD5的偏好
/// </summary>
/// <param name="mode">模式</param>
/// <param name="file_md5"></param>
/// <returns></returns>
public static DataTable SelectMD5Prefer(Mode mode, string file_md5)
{
string commandText = string.Format("SELECT prefer FROM {0} WHERE file_md5 = @file_md5", mode.ToString());
SQLiteParameter param = new SQLiteParameter("@file_md5", file_md5);
SQLiteCommand command = SQLiteHelper.CreateCommand(DBConn.prefer, commandText, param);
return(SQLiteHelper.ExecuteDataTable(command));
}
/// <summary>
/// 获取Pool中posts预览列表
/// </summary>
/// <param name="pool_id">pool的id</param>
/// <param name="site">图站</param>
/// <returns></returns>
public static DataTable SelectPoolPostsPreviewList(int pool_id, Site site)
{
string commandText = string.Format("SELECT file_md5,sequence FROM pool_posts WHERE pool_id=@pool_id");
SQLiteParameter param = new SQLiteParameter("@pool_id", pool_id);
SQLiteCommand command = SQLiteHelper.CreateCommand(DBConn.GetConn(site), commandText, param);
return(SQLiteHelper.ExecuteDataTable(command));
}
/// <summary>
/// 获取Pool详情
/// </summary>
/// <param name="id"></param>
/// <param name="site">图站</param>
/// <returns></returns>
public static DataTable SelectPoolDetail(int id, Site site)
{
string commandText = "SELECT * FROM pools WHERE id=@id";
SQLiteParameter param = new SQLiteParameter("@id", id);
SQLiteCommand command = SQLiteHelper.CreateCommand(DBConn.GetConn(site), commandText, param);
return(SQLiteHelper.ExecuteDataTable(command));
}
/// <summary>
/// 获取post详情
/// </summary>
/// <param name="file_md5"></param>
/// <param name="site"></param>
/// <returns></returns>
public static DataTable SelectPostDetail(string file_md5, Site site)
{
string commandText = "SELECT * FROM posts WHERE file_md5 = @file_md5";
SQLiteParameter param = new SQLiteParameter("@file_md5", file_md5);
SQLiteCommand command = SQLiteHelper.CreateCommand(DBConn.GetConn(site), commandText, param);
return(SQLiteHelper.ExecuteDataTable(command));
}
/// <summary>
/// 获取预览图
/// </summary>
/// <param name="md5Group">按首位分组的md5</param>
/// <returns></returns>
public static DataTable SelectPreviewImage(IGrouping <string, string> md5Group)
{
string md5IN = string.Join(",", md5Group.Select(s => string.Format("'{0}'", s)));
string commandText = string.Format("SELECT file_md5,preview FROM _{0} WHERE file_md5 IN ({1})", md5Group.Key, md5IN);
SQLiteCommand command = SQLiteHelper.CreateCommand(DBConn.preview, commandText);
return(SQLiteHelper.ExecuteDataTable(command));
}
/// <summary>
/// 为图片详情查询某图片的标签
/// </summary>
/// <param name="multiCRC32">多个CRC32标签</param>
/// <returns></returns>
public static DataTable SelectTagsForDetail(string multiCRC32, Site site)
{
string tagCRC32IN = string.Join(",", multiCRC32.Split(' ').Select(s => string.Format("'{0}'", s)));
string commandText = string.Format("SELECT name,type,post_count FROM tags WHERE crc32 IN ({0}) ORDER BY type,name", tagCRC32IN);
SQLiteCommand command = SQLiteHelper.CreateCommand(DBConn.GetConn(site), commandText);
return(SQLiteHelper.ExecuteDataTable(command));
}
/// <summary>
/// 获取Pool预览列表
/// </summary>
/// <param name="inputName">用户输入的名称</param>
/// <param name="site">图站</param>
/// <param name="column">按此列排序</param>
/// <param name="ratingArray">分级</param>
/// <returns></returns>
public static DataTable SelectPoolPreviewList(string inputName, Site site, PoolOrderBy column, Rating[] ratingArray)
{
string selectColumns = string.Format("id,name,file_md5,{0}", column.ToString());
string ratingIN = string.Join(",", ratingArray.Select(s => string.Format("'{0}'", s.ToString().Substring(0, 1).ToLower())));
string commandText = string.Format("SELECT {0} FROM pools WHERE name LIKE ('%'||@inputName||'%') AND rating IN ({1})", selectColumns, ratingIN);
SQLiteParameter param = new SQLiteParameter("@inputName", inputName);
SQLiteCommand command = SQLiteHelper.CreateCommand(DBConn.GetConn(site), commandText, param);
return(SQLiteHelper.ExecuteDataTable(command));
}
/// <summary>
/// 为推荐查询匹配图片标签
/// </summary>
/// <param name="incompleteTag">不完整的标签</param>
/// <param name="limit">返回数量</param>
/// <returns></returns>
public static DataTable SelectTagsForRecommend(string incompleteTag, Site site, int limit = 5)
{
//搜索 （标签*） 和 （*_标签*）
string commandText = @"SELECT name,type,post_count FROM tags WHERE name LIKE (@incompleteTag||'%') OR name LIKE ('%\_'||@incompleteTag||'%') ESCAPE '\' ORDER BY post_count DESC LIMIT @limit";
SQLiteParameter[] paramArray =
{
new SQLiteParameter("@incompleteTag", incompleteTag),
new SQLiteParameter("@limit", limit),
};
SQLiteCommand command = SQLiteHelper.CreateCommand(DBConn.GetConn(site), commandText, paramArray);
return(SQLiteHelper.ExecuteDataTable(command));
}
