示例#1
0
        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);
            }
        }
示例#2
0
        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));
        }
示例#3
0
        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 }));
        }
示例#4
0
        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));
        }
示例#5
0
        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 }));
        }
示例#6
0
        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 }));
        }
示例#7
0
        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 }));
        }
示例#8
0
        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 }));
        }
示例#9
0
        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);
        }
示例#10
0
        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);
            }
        }
示例#11
0
        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 }));
        }