public List <string> CharactersInSeries(string seriesName, MySqlConnection conn)
{
seriesName = TagParser.EscapeApostrophe(seriesName);
MySqlCommand cmd;
MySqlDataReader rdr;
string characterQuery =
$"SELECT t.name " +
$"FROM tags AS t, series_tags AS st, series AS s " +
$"WHERE t.id = st.tag_id AND st.series_id = s.id AND s.name LIKE '{seriesName}'";
List <string> characters = new List <string>();
try
{
cmd = new MySqlCommand(characterQuery, conn);
using (rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
characters.Add((string)rdr[0]);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message + Environment.NewLine + e.StackTrace);
}
return(characters);
}
public string SeriesWithCharacter(string charName, MySqlConnection conn)
{
MySqlCommand cmd;
MySqlDataReader rdr;
charName = TagParser.EscapeApostrophe(charName);
string seriesQuery =
$"SELECT s.name " +
$"FROM tags AS t, series_tags AS st, series AS s " +
$"WHERE t.id = st.tag_id AND st.series_id = s.id AND t.name LIKE '{charName}'";
string series = "";
try
{
cmd = new MySqlCommand(seriesQuery, conn);
using (rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
series = (string)rdr[0];
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message + Environment.NewLine + e.StackTrace);
}
return(series);
}
public List <TagData> LookupTagData(List <string> tags, MySqlConnection conn)
{
string escaped;
string dataQuery;
string last = tags[tags.Count - 1];
StringBuilder sb = new StringBuilder();
foreach (string s in tags)
{
escaped = TagParser.EscapeApostrophe(s);
if (s == last)
{
sb.Append($"'{escaped}'");
}
else
{
sb.Append($"'{escaped}',");
}
}
dataQuery = $"SELECT name, id, post_count FROM series WHERE name IN ({sb})";
return(LookupTagData(dataQuery, conn));
}
protected List <int> GetLinkIdsForTag(string tagName, MySqlConnection conn)
{
tagName = TagParser.EscapeApostrophe(tagName);
string linkIdQuery =
$"SELECT tl.link_id " +
$"FROM tags AS t, tag_links AS tl " +
$"WHERE t.id = tl.tag_id AND t.name = '{tagName}'";
MySqlCommand cmd;
MySqlDataReader rdr;
List <int> linkIds = new List <int>();
try
{
cmd = new MySqlCommand(linkIdQuery, conn);
cmd.CommandTimeout = TimeOut;
using (rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
linkIds.Add((int)rdr[0]);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message + Environment.NewLine + e.StackTrace);
}
return(linkIds);
}
public new bool HasExactMatch(string searchTerm, MySqlConnection conn, out string matched)
{
searchTerm = TagParser.EscapeApostrophe(searchTerm);
string tagQuery = $"SELECT name from series WHERE name LIKE '{searchTerm}'";
return(base.HasExactMatch(tagQuery, conn, out matched));
}
public new List <string> LookupTags(string searchTerm, MySqlConnection conn)
{
searchTerm = TagParser.EscapeApostrophe(searchTerm);
string tagQuery = $"SELECT name from series WHERE name LIKE '{searchTerm}'";
return(base.LookupTags(tagQuery, conn));
}
public new int LookupTagIdByName(string tagName, MySqlConnection conn)
{
tagName = TagParser.EscapeApostrophe(tagName);
string tagQuery = $"SELECT id from tags WHERE name = '{tagName}'";
return(base.LookupTagIdByName(tagQuery, conn));
}
public PostData LookupPreviousPost(string searchTerm, int postId, MySqlConnection conn)
{
searchTerm = TagParser.EscapeApostrophe(searchTerm);
int previousLinkId = GetPreviousLinkId(searchTerm, postId, conn);
PostData pd = GetPostForQuery(BuildPostQuery(_postQuery, searchTerm, previousLinkId), conn);
pd.PostIndex = GetIndexForPostId(searchTerm, previousLinkId, conn);
return(pd);
}
public PostData LookupRandomPost(string searchTerm, MySqlConnection conn)
{
searchTerm = TagParser.EscapeApostrophe(searchTerm);
string linkIdQuery =
$"SELECT tl.link_id " +
$"FROM tags AS t, tag_links AS tl " +
$"WHERE t.id = tl.tag_id AND t.name = '{searchTerm}'";
int randomId = GetRandomLinkIdForQuery(linkIdQuery, conn);
return(GetPostForQuery(BuildPostQuery(_postQuery, searchTerm, randomId), conn));
}
public PostData LookupRandomPost(string searchTerm, MySqlConnection conn)
{
searchTerm = TagParser.EscapeApostrophe(searchTerm);
string postQuery =
$"SELECT t.id, t.name, l.url, s.name, l.id, s.post_count " +
$"FROM links AS l, tags AS t, tag_links AS tl, series_tags AS st, series AS s " +
$"WHERE l.id = tl.link_id AND t.id = tl.tag_id AND t.id = st.tag_id AND s.id = st.series_id AND s.name = '&' AND l.id = %";
string linkIdQuery =
$"SELECT tl.link_id " +
$"FROM tags AS t, series AS s, series_tags AS st, tag_links AS tl " +
$"WHERE s.id = st.series_id AND st.tag_id = t.id AND t.id = tl.tag_id AND s.name = '{searchTerm}'";
return(GetPostForQuery(BuildPostQuery(postQuery, searchTerm, GetRandomLinkIdForQuery(linkIdQuery, conn)), conn));
}
private PostData RandomCollabPost(string[] searchTerms, MySqlConnection conn)
{
Random rand = new Random();
List <int> commonLinkIds = GetCommonLinkIds(searchTerms, conn).ToList();
commonLinkIds.Sort();
string searchTermEscaped = TagParser.EscapeApostrophe(searchTerms[0]);
int randomId = commonLinkIds[rand.Next(0, commonLinkIds.Count())];
string postQuery = BuildPostQuery(_postQuery, searchTermEscaped, randomId);
PostData pd = GetPostForQuery(postQuery, conn);
pd.PostIndex = commonLinkIds.IndexOf(randomId);
pd.PostCount = commonLinkIds.Count();
return(pd);
}
private PostData PreviousCollabPost(string[] searchTerms, int currentId, MySqlConnection conn)
{
List <int> commonLinkIds = GetCommonLinkIds(searchTerms, conn).ToList();
commonLinkIds.Sort();
int idIndex = commonLinkIds.IndexOf(currentId);
int previousIndex = idIndex == 0 ? idIndex : idIndex - 1;
string postQuery = BuildPostQuery(_postQuery, TagParser.EscapeApostrophe(searchTerms[0]), commonLinkIds[previousIndex]);
PostData pd = GetPostForQuery(postQuery, conn);
AdditionalPostData apd = BuildAdditionalPostData(searchTerms, conn);
pd.PostIndex = previousIndex;
pd.PostCount = commonLinkIds.Count();
pd.AdditionalData = apd;
return(pd);
}