public App FindAppByPath(string appPath)
{
var(cmd, reader) =
ExecuteReader("select Id,Name,Path,Color from App where Path = @path", ("path", appPath));
using (cmd)
using (reader)
{
return(reader.Read() ? AppMapper(reader) : null);
}
}
public IObservable <byte[]> GetAppIcon(App app)
{
var lazy = new Lazy <Task <byte[]> >(async() =>
{
var(cmd, reader) = ExecuteReader("select Icon from App where Id = @appId", ("appId", app.Id));
using (cmd)
using (reader)
{
return(reader.Read() ? await GetBytes(reader, 0) : null);
}
}, true);
return(Observable.FromAsync(() => lazy.Value));
}
protected virtual void OnExecuteReader(string query, string exception)
{
ExecuteReader?.Invoke(this, new DataBaseManagerEventArgs()
{
Query = query, Exception = exception
});
}
public static IEnumerable <IDataRecord> GetEuFinsDocumentsPeriods(int siteLangId)
{
var sequence = ExecuteReader.Yield(
connectionString: connPG,
functionName: "get_eufins_documents_date_of_effect",
parameterBag: new { _site_lang_id = siteLangId }
);
return(sequence);
}
public static IEnumerable <IDataRecord> GetNewEuFinsDocuments(int siteLangId, int userId, DateTime startDate, DateTime endDate)
{
var sequence = ExecuteReader.Yield(
connectionString: connPG,
functionName: "get_new_eufins_docs",
parameterBag: new
{
_site_lang_id = siteLangId,
_start_date = startDate,
_end_date = endDate,
_user_id = userId
}
);
return(sequence);
}
public static IEnumerable <Team> GetAll(long leagueId)
=> ExecuteReader(
"SELECT * FROM teams WHERE league_id=@leagueId",
FromReader,
("leagueId", leagueId));
/// <summary>
/// 特定のハッシュ値の画像を含むツイートのうち、表示可能かつ最も古いやつの(tweet_id, media_id)
/// </summary>
/// <param name="dcthash"></param>
/// <param name="login_user_id"></param>
/// <returns>(tweet_id, media_id) 見つからなければnull</returns>
public async Task <(long tweet_id, long media_id)?> HashtoTweet(long?dcthash, long?login_user_id)
{
if (dcthash == null)
{
return(null);
}
//外側にORDER BYがないので
//ハッシュ完全一致→距離1→距離2,3くらい の優先度になる(たぶん)
using (MySqlCommand cmd = new MySqlCommand(@"
(SELECT o.tweet_id, m.media_id
FROM media m
JOIN tweet_media USING (media_id)
JOIN tweet o USING (tweet_id)
JOIN user ou USING (user_id)
WHERE m.dcthash = @dcthash
AND (ou.isprotected = 0 OR ou.user_id = @login_user_id OR EXISTS (SELECT * FROM friend WHERE user_id = @login_user_id AND friend_id = ou.user_id))
ORDER BY o.created_at LIMIT 1
) UNION ALL (
SELECT o.tweet_id, m.media_id
FROM media m
JOIN tweet_media USING (media_id)
JOIN tweet o USING (tweet_id)
JOIN user ou USING (user_id)
WHERE m.dcthash IN (@0,@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21,@22,@23,@24,@25,@26,@27,@28,@29,@30,@31,@32,@33,@34,@35,@36,@37,@38,@39,@40,@41,@42,@43,@44,@45,@46,@47,@48,@49,@50,@51,@52,@53,@54,@55,@56,@57,@58,@59,@60,@61,@62,@63)
AND (ou.isprotected = 0 OR ou.user_id = @login_user_id OR EXISTS (SELECT * FROM friend WHERE user_id = @login_user_id AND friend_id = ou.user_id))
ORDER BY o.created_at LIMIT 1
) UNION ALL (
SELECT o.tweet_id, m.media_id
FROM dcthashpairslim p
JOIN media m ON p.hash_large = m.dcthash
JOIN tweet_media USING (media_id)
JOIN tweet o USING (tweet_id)
JOIN user ou USING (user_id)
WHERE p.hash_small IN (@dcthash,@0,@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21,@22,@23,@24,@25,@26,@27,@28,@29,@30,@31,@32,@33,@34,@35,@36,@37,@38,@39,@40,@41,@42,@43,@44,@45,@46,@47,@48,@49,@50,@51,@52,@53,@54,@55,@56,@57,@58,@59,@60,@61,@62,@63)
AND (ou.isprotected = 0 OR ou.user_id = @login_user_id OR EXISTS (SELECT * FROM friend WHERE user_id = @login_user_id AND friend_id = o.user_id))
ORDER BY o.created_at LIMIT 1
) UNION ALL (
SELECT o.tweet_id, m.media_id
FROM dcthashpairslim p
JOIN media m ON p.hash_small = m.dcthash
JOIN tweet_media USING (media_id)
JOIN tweet o USING (tweet_id)
JOIN user ou USING (user_id)
WHERE p.hash_large IN (@dcthash,@0,@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21,@22,@23,@24,@25,@26,@27,@28,@29,@30,@31,@32,@33,@34,@35,@36,@37,@38,@39,@40,@41,@42,@43,@44,@45,@46,@47,@48,@49,@50,@51,@52,@53,@54,@55,@56,@57,@58,@59,@60,@61,@62,@63)
AND (ou.isprotected = 0 OR ou.user_id = @login_user_id OR EXISTS (SELECT * FROM friend WHERE user_id = @login_user_id AND friend_id = o.user_id))
ORDER BY o.created_at LIMIT 1
);"))
{
cmd.Parameters.Add("@dcthash", MySqlDbType.Int64).Value = dcthash;
for (int i = 0; i < 64; i++)
{
cmd.Parameters.Add("@" + i.ToString(), MySqlDbType.Int64).Value = dcthash ^ (1L << i);
}
cmd.Parameters.Add("@login_user_id", MySqlDbType.Int64).Value = login_user_id;
(long, long)? ret = null;
await ExecuteReader(cmd, (r) => ret = (r.GetInt64(0), r.GetInt64(1))).ConfigureAwait(false);
return(ret);
}
}
