public async Task <Quote> Create(Quote quote, Author author, Language language)
{
await using var connection = ConfigurationExtensions.GetConnection(_configuration);
await using var transaction = connection.BeginTransaction();
try
{
var quoteHash = HashGenerator.Sha256(quote.Text);
var quoteIds = await GetQuoteIdsByHash(quoteHash);
if (quoteIds != default)
{
quote.Id = quoteIds.Id;
quote.Uuid = quoteIds.Uuid;
return(quote);
}
var quoteUuid = IdGenerator.Instance.Next();
var quoteText = quote.Text;
var authorId = author.Id;
var languageId = language.Id;
var addedAt = DateTime.UtcNow;
var insertQuoteSql = $@"
INSERT INTO {QuoteSchema.Table}
(
{QuoteSchema.Columns.Uuid},
{QuoteSchema.Columns.Text},
{QuoteSchema.Columns.Hash},
{QuoteSchema.Columns.AuthorId},
{QuoteSchema.Columns.LanguageId},
{QuoteSchema.Columns.AddedAt}
)
VALUES
(
@{nameof(quoteUuid)}, @{nameof(quoteText)}, @{nameof(quoteHash)},
@{nameof(authorId)}, @{nameof(languageId)}, @{nameof(addedAt)}
);
SELECT {QuoteSchema.Columns.Id} FROM {QuoteSchema.Table} WHERE {QuoteSchema.Columns.Uuid} = @{nameof(quoteUuid)};";
var quoteId = await connection.QueryFirstAsync <int>(
insertQuoteSql,
new { quoteUuid, quoteText, quoteHash, authorId, languageId, addedAt },
transaction);
transaction.Commit();
quote.Id = quoteId;
quote.Uuid = quoteUuid;
return(quote);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
transaction.Rollback();
return(null);
}
}
public async Task <IEnumerable <Language> > GetLanguages()
{
await using var connection = ConfigurationExtensions.GetConnection(_configuration);
var sql = $@"
SELECT {LanguageSchema.Table}.{LanguageSchema.Columns.Id} AS Id,
{LanguageSchema.Table}.{LanguageSchema.Columns.Code} AS Code,
{LanguageSchema.Table}.{LanguageSchema.Columns.Name} AS Description
FROM {LanguageSchema.Table} AS {LanguageSchema.Table}
GROUP BY {LanguageSchema.Table}.{LanguageSchema.Columns.Id}";
return(await connection.QueryAsync <Language>(sql));
}
private async Task <Quote> GetQuoteIdsByHash(string hash)
{
await using var connection = ConfigurationExtensions.GetConnection(_configuration);
var sql = $@"
SELECT {QuoteSchema.Table}.{QuoteSchema.Columns.Id},
{QuoteSchema.Table}.{QuoteSchema.Columns.Uuid}
FROM {QuoteSchema.Table} AS {QuoteSchema.Table}
WHERE {QuoteSchema.Table}.{QuoteSchema.Columns.Hash} = @{nameof(hash)}
LIMIT 1";
return(await connection.QueryFirstOrDefaultAsync <Quote>(sql, new { hash }));
}
public async Task <Language> GetLanguage(string code)
{
await using var connection = ConfigurationExtensions.GetConnection(_configuration);
var sql = $@"
SELECT {LanguageSchema.Table}.{LanguageSchema.Columns.Id} AS Id,
{LanguageSchema.Table}.{LanguageSchema.Columns.Code} AS Code,
{LanguageSchema.Table}.{LanguageSchema.Columns.Name} AS Description
FROM {LanguageSchema.Table} AS {LanguageSchema.Table}
WHERE LOWER({LanguageSchema.Table}.{LanguageSchema.Columns.Code}) = LOWER(@{nameof(code)})
LIMIT 1;";
return(await connection.QueryFirstOrDefaultAsync <Language>(sql, code));
}
public async Task <Author> Get(string name)
{
await using var connection = ConfigurationExtensions.GetConnection(_configuration);
var sql = $@"
SELECT {AuthorSchema.Table}.{AuthorSchema.Columns.Id} AS Id,
{AuthorSchema.Table}.{AuthorSchema.Columns.Uuid} AS Uuid,
{AuthorNameSchema.Table}.{AuthorNameSchema.Columns.Name} AS Name
FROM {AuthorSchema.Table} AS {AuthorSchema.Table}
INNER JOIN {AuthorNameSchema.Table} AS {AuthorNameSchema.Table}
ON {AuthorNameSchema.Table}.{AuthorNameSchema.Columns.AuthorId} = {AuthorSchema.Table}.{AuthorSchema.Columns.Id}
WHERE {AuthorNameSchema.Table}.{AuthorNameSchema.Columns.Name} = @{nameof(name)}
LIMIT 1";
return(await connection.QueryFirstOrDefaultAsync <Author>(sql, new { name }));
}
public async Task <IEnumerable <Author> > SearchByName(string name, int page, int count)
{
await using var connection = ConfigurationExtensions.GetConnection(_configuration);
var offset = page * count;
var sql = $@"
SELECT {AuthorSchema.Table}.{AuthorSchema.Columns.Id} AS Id,
{AuthorSchema.Table}.{AuthorSchema.Columns.Uuid} AS Uuid,
{AuthorNameSchema.Table}.{AuthorNameSchema.Columns.Name} AS Name
FROM {AuthorSchema.Table} AS {AuthorSchema.Table}
INNER JOIN {AuthorNameSchema.Table} AS {AuthorNameSchema.Table}
ON {AuthorNameSchema.Table}.{AuthorNameSchema.Columns.AuthorId} = {AuthorSchema.Table}.{AuthorSchema.Columns.Id}
WHERE {AuthorNameSchema.Table}.{AuthorNameSchema.Columns.Name} LIKE CONCAT('%',@{nameof(name)},'%')
ORDER BY {AuthorNameSchema.Table}.{AuthorNameSchema.Columns.Name} ASC
LIMIT @{nameof(count)} OFFSET @{nameof(offset)}";
return(await connection.QueryAsync <Author>(sql, new { name, count, offset }));
}
public async Task <Quote> GetRandom(string languageCode)
{
await using var connection = ConfigurationExtensions.GetConnection(_configuration);
var languageIdSql = $@"
SELECT @languageId := {LanguageSchema.Table}.{LanguageSchema.Columns.Id}
FROM {LanguageSchema.Table} AS {LanguageSchema.Table}
WHERE {LanguageSchema.Table}.{LanguageSchema.Columns.Code} = @{nameof(languageCode)};";
var minMaxIdSql = $@"
SELECT @min := MIN({QuoteSchema.Table}.{QuoteSchema.Columns.Id}),
@max := MAX({QuoteSchema.Table}.{QuoteSchema.Columns.Id})
FROM {QuoteSchema.Table} AS {QuoteSchema.Table}
WHERE {QuoteSchema.Table}.{QuoteSchema.Columns.LanguageId} = @languageId;";
var sql = $@"
SELECT {QuoteSchema.Table}.{QuoteSchema.Columns.Id} AS Id,
{QuoteSchema.Table}.{QuoteSchema.Columns.Uuid} AS Uuid,
{QuoteSchema.Table}.{QuoteSchema.Columns.Text} AS Text,
{AuthorNameSchema.Table}.{AuthorNameSchema.Columns.Name} AS Author
FROM {QuoteSchema.Table} AS {QuoteSchema.Table}
LEFT JOIN {AuthorNameSchema.Table} AS {AuthorNameSchema.Table}
ON {QuoteSchema.Table}.{QuoteSchema.Columns.AuthorId} = {AuthorNameSchema.Table}.{AuthorNameSchema.Columns.AuthorId}
AND {QuoteSchema.Table}.{QuoteSchema.Columns.LanguageId} = {AuthorNameSchema.Table}.{AuthorNameSchema.Columns.LanguageId}
JOIN (
SELECT {QuoteSchema.Columns.Id}
FROM (
SELECT {QuoteSchema.Columns.Id}
FROM (SELECT @min + (@max - @min + 1 - 50) * RAND() AS START FROM DUAL) AS INIT
JOIN {QuoteSchema.Table} AS Y
WHERE Y.{QuoteSchema.Columns.Id} > INIT.START
ORDER BY Y.{QuoteSchema.Columns.Id}
LIMIT 50 -- Inflated to deal with gaps
) AS Z ORDER BY RAND()
LIMIT 1 -- number of rows desired (change to 1 if looking for a single row)
) R
ON {QuoteSchema.Table}.{QuoteSchema.Columns.Id} = R.{QuoteSchema.Columns.Id}
AND {QuoteSchema.Table}.{QuoteSchema.Columns.LanguageId}=@languageId;";
var languageId = await connection.QueryFirstAsync <int>(languageIdSql, new { languageCode });
var(min, max) = await connection.QueryFirstAsync <(int min, int max)>(minMaxIdSql, new { languageId });
return(await connection.QueryFirstAsync <Quote>(sql, new { min, max, languageId }));
}
public async Task <Quote> GetById(string uuid)
{
await using var connection = ConfigurationExtensions.GetConnection(_configuration);
var sql = $@"
SELECT {QuoteSchema.Table}.{QuoteSchema.Columns.Id} AS Id,
{QuoteSchema.Table}.{QuoteSchema.Columns.Uuid} AS Uuid,
{QuoteSchema.Table}.{QuoteSchema.Columns.Text} AS Text,
{AuthorNameSchema.Table}.{AuthorNameSchema.Columns.Name} AS Author
FROM {QuoteSchema.Table} AS {QuoteSchema.Table}
INNER JOIN {LanguageSchema.Table} AS {LanguageSchema.Table}
ON {QuoteSchema.Table}.{QuoteSchema.Columns.LanguageId} = {LanguageSchema.Table}.{LanguageSchema.Columns.Id}
INNER JOIN {AuthorSchema.Table} AS {AuthorSchema.Table}
ON {QuoteSchema.Table}.{QuoteSchema.Columns.AuthorId} = {AuthorSchema.Table}.{AuthorSchema.Columns.Id}
INNER JOIN {AuthorNameSchema.Table} AS {AuthorNameSchema.Table}
ON {AuthorSchema.Table}.{AuthorSchema.Columns.Id} = {AuthorNameSchema.Table}.{AuthorNameSchema.Columns.AuthorId}
AND {QuoteSchema.Table}.{QuoteSchema.Columns.LanguageId} = {AuthorNameSchema.Table}.{AuthorNameSchema.Columns.LanguageId}
WHERE {QuoteSchema.Table}.{QuoteSchema.Columns.Uuid} = @{nameof(uuid)}";
return(await connection.QueryFirstOrDefaultAsync <Quote>(sql, new { uuid }));
}
public async Task <QuotesStatistics> GetStatistics()
{
await using var connection = ConfigurationExtensions.GetConnection(_configuration);
var sql = $@"
SELECT {LanguageSchema.Table}.{LanguageSchema.Columns.Code} AS code,
COUNT({QuoteSchema.Table}.{QuoteSchema.Columns.LanguageId}) AS count
FROM {QuoteSchema.Table} AS {QuoteSchema.Table}
LEFT JOIN {LanguageSchema.Table} AS {LanguageSchema.Table}
ON {QuoteSchema.Table}.{QuoteSchema.Columns.LanguageId} = {LanguageSchema.Table}.{LanguageSchema.Columns.Id}
GROUP BY {QuoteSchema.Table}.{QuoteSchema.Columns.LanguageId}
ORDER BY Count DESC";
var statistics = (await connection.QueryAsync <(string code, int count)>(sql)).ToList();
var result = new QuotesStatistics
{
Languages = statistics.ToDictionary(x => x.code, x => x.count),
Total = statistics.Sum(x => x.count)
};
return(result);
}
public async Task <Author> Create(string name, Language language)
{
await using var connection = ConfigurationExtensions.GetConnection(_configuration);
await using var transaction = connection.BeginTransaction();
try
{
var authorUuid = IdGenerator.Instance.Next();
var insertAuthorSql = $@"
INSERT INTO {AuthorSchema.Table} ({AuthorSchema.Columns.Uuid})
VALUES (@{nameof(authorUuid)});
SELECT LAST_INSERT_ID();";
var authorId = await connection.QueryFirstAsync <int>(insertAuthorSql, new { authorUuid }, transaction);
var languageId = language.Id;
var insertAuthorNameSql = $@"
INSERT INTO {AuthorNameSchema.Table}
(
{AuthorNameSchema.Columns.Name},
{AuthorNameSchema.Columns.AuthorId},
{AuthorNameSchema.Columns.LanguageId}
)
VALUES (@{nameof(name)}, @{nameof(authorId)}, @{nameof(languageId)})";
await connection.ExecuteAsync(insertAuthorNameSql, new { name, authorId, languageId }, transaction);
transaction.Commit();
return(new Author {
Id = authorId, Uuid = authorUuid, Name = name
});
}
catch (Exception)
{
transaction.Rollback();
return(null);
}
}
public async Task <IEnumerable <Quote> > GetByAuthor(string languageCode, string authorUuid, int page, int count)
{
await using var connection = ConfigurationExtensions.GetConnection(_configuration);
var offset = page * count;
var sql = $@"
SELECT {QuoteSchema.Table}.{QuoteSchema.Columns.Id} AS Id,
{QuoteSchema.Table}.{QuoteSchema.Columns.Uuid} AS Uuid,
{QuoteSchema.Table}.{QuoteSchema.Columns.Text} AS Text,
{AuthorNameSchema.Table}.{AuthorNameSchema.Columns.Name} AS Author
FROM {QuoteSchema.Table} AS {QuoteSchema.Table}
INNER JOIN {LanguageSchema.Table} AS {LanguageSchema.Table}
ON {QuoteSchema.Table}.{QuoteSchema.Columns.LanguageId} = {LanguageSchema.Table}.{LanguageSchema.Columns.Id}
INNER JOIN {AuthorSchema.Table} AS {AuthorSchema.Table}
ON {QuoteSchema.Table}.{QuoteSchema.Columns.AuthorId} = {AuthorSchema.Table}.{AuthorSchema.Columns.Id}
INNER JOIN {AuthorNameSchema.Table} AS {AuthorNameSchema.Table}
ON {AuthorSchema.Table}.{AuthorSchema.Columns.Id} = {AuthorNameSchema.Table}.{AuthorNameSchema.Columns.AuthorId}
AND {QuoteSchema.Table}.{QuoteSchema.Columns.LanguageId} = {AuthorNameSchema.Table}.{AuthorNameSchema.Columns.LanguageId}
WHERE {AuthorSchema.Table}.{AuthorSchema.Columns.Uuid} = @{nameof(authorUuid)}
AND {LanguageSchema.Table}.{LanguageSchema.Columns.Code} = @{nameof(languageCode)}
LIMIT @{nameof(count)} OFFSET @{nameof(offset)}";
return(await connection.QueryAsync <Quote>(sql, new { languageCode, authorUuid, count, offset }));
}