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); }