Как писать сложные SQL запросы, не зная SQL. Пишем на C# и смотрим log

[plastov]

Пробовал использовать кубик Visual Studio + Entity Framework. Но возникли сложности с запуском из ЗП. Поэтому нашел этот способ.

Я использую БД PostreSQL для Windows и Visual Studio.


1) Создаем консольное приложение




2) Через NuGet устанавливаем Microsoft.EntityFrameworkCore.Tools и Npgsql.EntityFrameworkCore.PostgreSQL



Что нужно для MySQL ниже в ссылках.

3) Пишем класс (модель) нашей будущей БД и контекст. В моем случае БД используется для передачи заданий на постинг в ленту

using Microsoft.EntityFrameworkCore;

Console.WriteLine("Hello, World!");

//столбцы БД
public class PostingTask
{
    public int ID { get; set; } //ID - обязательный
    public string Link { get; set; } = null!; //ссылка на пост, не может быть Null (пустым)
    public int LikesNeed { get; set; } = 0; //количество лайков, которые надо поставить, по умолчанию = 0
    public int LikesDelivered { get; set; } = 0; //количество лайков, которые поставили, по умолчанию = 0
    public bool? IsntPostInFeed { get; set; } // если пост не найден в ленте, ставим True. Может быть Null
    public string? Nickname { get; set; } // никнейм, может быть Null
    public DateTime DatetimePost { get; set; } = DateTime.Now.ToUniversalTime(); //время поста. Автоматически ставит текущую время и дату. Позже руками поправим в БД
    public DateTime? DatetimeLastLike { get; set; } //время последнего лайка. Может быть Null
}
//настройки подключения
public class taskContext : DbContext
{
    public DbSet<PostingTask> PostingTasks { get; set; } = null!;
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql(@"Host=localhost;Port=5432;Database=dbPostingTask;Username=super;Password=KdgCRrXZSBIt");
    }
}

4) Создаем БД в Postgres через миграцию. Используем Консоль диспетчера пакетов
Как подключить MySQL ниже в ссылках.

Открываем Вид - Другие окна - Консоль диспетчера пакетов



Пишем команды:
Add-Migration Initial_0 //Initial_0 - Название пакета миграции. Может быть любым. Но каждое новое изменение должно быть с новым названием, можно ставить в конце цифру
Update-Database




Создалась БД



6) Пишем запрос на добавление нового задания в БД

Добавляем конструктор класса с атрибутами (переменными), которые надо записать в БД, и сам запрос. Запускаем, проверяем данные в БД

Console.WriteLine("Hello, World!");

//запрос на добавление данных в БД
using (taskContext db = new taskContext())
{
    PostingTask task = new PostingTask(@"https://link", 120, "Nickname");
    db.PostingTasks.Add(task);
    db.SaveChanges();
}

public class PostingTask
{
    public int ID { get; set; }
    public string Link { get; set; } = null!;
    public int LikesNeed { get; set; } = 0;
    public int LikesDelivered { get; set; } = 0;
    public bool? IsntPostInFeed { get; set; }
    public string? Nickname { get; set; }
    public DateTime DatetimePost { get; set; } = DateTime.Now.ToUniversalTime();
    public DateTime? DatetimeLastLike { get; set; }

    //конструктор для нового задания
    public PostingTask(string link, int likesNeed, string nickname) //отправдяем в БД ссылку, сколько нужно поставить лайков и никнейм
    {
        Link = link;
        LikesNeed = likesNeed;
        Nickname = nickname;
    }
}

Появились данные в БД. Время поста записалось автоматически




7) Настраиваем логгирование операций. Более подробно ниже в ссылках на использованные материалы:

В контекст данных добавляем код

public class taskContext : DbContext
{
    private readonly StreamWriter logStream = new StreamWriter(@"C:\zp\testRequest\testRequest\mylog.txt", true);
    public DbSet<PostingTask> PostingTasks { get; set; } = null!;
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql(@"Host=localhost;Port=5432;Database=dbPostingTask;Username=super;Password=KdgCRrXZSBIt");
        optionsBuilder.LogTo(logStream.WriteLine, new[] { RelationalEventId.CommandExecuted });
    }
    public override void Dispose()
    {
        base.Dispose();
        logStream.Dispose();
    }
}

Запускаем на выполнение, смотрим созданный mylog.txt:

info: 13.10.2022 13:23:06.998 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (120ms) [Parameters=[@p0='?' (DbType = DateTime), @p1='?' (DbType = DateTime), @p2='?' (DbType = Boolean), @p3='?' (DbType = Int32), @p4='?' (DbType = Int32), @p5='?', @p6='?'], CommandType='Text', CommandTimeout='30']
      INSERT INTO "PostingTasks" ("DatetimeLastLike", "DatetimePost", "IsntPostInFeed", "LikesDelivered", "LikesNeed", "Link", "Nickname")
      VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6)
      RETURNING "ID";

Вот наш запрос:

INSERT INTO "PostingTasks" ("DatetimeLastLike", "DatetimePost", "IsntPostInFeed", "LikesDelivered", "LikesNeed", "Link", "Nickname")
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6)
RETURNING "ID";

8 ) Добавляем кубик в ПМ и исправляем ошибки запроса

Как подключить Postgres к ЗП - https://zennolab.com/discussion/threads/postgresql-subd-i-zennoposter-podkljuchenie-cherez-odbc.43320/

Немного редактируем наш запрос и добавляем в кубик.

INSERT INTO "PostingTasks" ("LikesNeed", "Link", "Nickname")
VALUES ({-Variable.likesNeed-}, '{-Variable.link-}', '{-Variable.nickname-}');

Запускаем кубик, получаем ошибку - LikesDelivered не должен быть Null.
При запуске из VS, он сам подставлял нужные данные. Из кубика мы их не отправляли. Здесь варианты - отправлять из кубика, или сделать по умолчанию 0 в столбце БД, или сделать новую миграцию в VS (LikesDelivered может быть Null) и обновить.

Я добавлю для LikesDelivered значение по умолчанию 0. Заодно значение по умолчанию для DatetimePost - CURRENT_TIMESTAMP (это значения я нашел в др запросах, дальше будет видно). Если не поставить CURRENT_TIMESTAMP, в ячейке будет -infinity
Из кубика можно отправлять значение DEFAULT, будет подставляться значение по умолчанию.




Снова запускаем, смотрим данные в БД. Все ок.


Удаляем строку с -infinity чтобы не выдавала ошибку при вычислении дальше.


9) Пишем сложный запрос

Нам надо получить одну любую ссылку на пост, который не старше 15 минут; количество поставленных лайков меньше чем надо поставить, нет метки что поста нет в ленте. И если эта ссылка найдена, то в LikesDelivered записываем столько же лайков как и в LikesNeed (как будто мы их собираемся поставить)

Пишем запрос на C# и смотрим лог. Также выводим на консоль полученную ссылку, чтобы убедиться что результат правильный. Можно нагенерить разных ссылок, и посмотреть какие будут выдаваться в результат.

//получить задание из БД согласно условиям
using (taskContext db = new taskContext())
{
    PostingTask? task = db.PostingTasks.FirstOrDefault(p => (DateTime.Now.ToUniversalTime() - p.DatetimePost).TotalMinutes < 15
                                                        && p.LikesNeed - p.LikesDelivered > 0
                                                        && p.IsntPostInFeed != true);
    if (task != null)
    {
        task.LikesDelivered = task.LikesNeed;
        db.SaveChanges();
        Console.WriteLine(task.Link);
    }
}

Смотрим лог:

info: 13.10.2022 15:53:15.377 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (84ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT p."ID", p."DatetimeLastLike", p."DatetimePost", p."IsntPostInFeed", p."LikesDelivered", p."LikesNeed", p."Link", p."Nickname"
      FROM "PostingTasks" AS p
      WHERE (((date_part('epoch', now()::timestamp::timestamptz - p."DatetimePost") / 60.0) < 15.0) AND ((p."LikesNeed" - p."LikesDelivered") > 0)) AND ((p."IsntPostInFeed" <> TRUE) OR ((p."IsntPostInFeed" IS NULL)))
      LIMIT 1
info: 13.10.2022 15:53:15.612 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (14ms) [Parameters=[@p1='?' (DbType = Int32), @p0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      UPDATE "PostingTasks" SET "LikesDelivered" = @p0
      WHERE "ID" = @p1;

В нем два запроса. Если ссылка не найдена, будет только один запрос SELECT

      SELECT p."ID", p."DatetimeLastLike", p."DatetimePost", p."IsntPostInFeed", p."LikesDelivered", p."LikesNeed", p."Link", p."Nickname"
      FROM "PostingTasks" AS p
      WHERE (((date_part('epoch', now()::timestamp::timestamptz - p."DatetimePost") / 60.0) < 15.0) AND ((p."LikesNeed" - p."LikesDelivered") > 0)) AND ((p."IsntPostInFeed" <> TRUE) OR ((p."IsntPostInFeed" IS NULL)))
      LIMIT 1

      UPDATE "PostingTasks" SET "LikesDelivered" = @p0
      WHERE "ID" = @p1;

Можно применить его так: Если переменная не пустая, заполняем LikesDelivered и идем по алгоритму дальше. Если пустая, то делаем паузу и снова проверяем БД.



Код полностью

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

Console.WriteLine("Hello, World!");

////запрос на добавление данных
//using (taskContext db = new taskContext())
//{
//    PostingTask task = new PostingTask(@"https://link", 120, "Nickname");
//    db.PostingTasks.Add(task);
//    db.SaveChanges();
//}

//получить задание из БД согласно условиям
using (taskContext db = new taskContext())
{
    PostingTask? task = db.PostingTasks.FirstOrDefault(p => (DateTime.Now.ToUniversalTime() - p.DatetimePost).TotalMinutes < 15
                                                        && p.LikesNeed - p.LikesDelivered > 0
                                                        && p.IsntPostInFeed != true);
    if (task != null)
    {
        task.LikesDelivered = task.LikesNeed;
        db.SaveChanges();
        Console.WriteLine(task.Link);
    }
}

public class PostingTask
{
    public int ID { get; set; }
    public string Link { get; set; } = null!;
    public int LikesNeed { get; set; } = 0;
    public int LikesDelivered { get; set; } = 0;
    public bool? IsntPostInFeed { get; set; }
    public string? Nickname { get; set; }
    public DateTime DatetimePost { get; set; } = DateTime.Now.ToUniversalTime();
    public DateTime? DatetimeLastLike { get; set; }

    //конструктор для нового задания
    public PostingTask(string link, int likesNeed, string nickname) //отправдяем в БД ссылку, сколько нужно поставить лайков и никнейм
    {
        Link = link;
        LikesNeed = likesNeed;
        Nickname = nickname;
    }
}

public class taskContext : DbContext
{
    private readonly StreamWriter logStream = new StreamWriter(@"C:\zp\testRequest\testRequest\mylog.txt", true);
    public DbSet<PostingTask> PostingTasks { get; set; } = null!;
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql(@"Host=localhost;Port=5432;Database=dbPostingTask;Username=super;Password=KdgCRrXZSBIt");
        optionsBuilder.LogTo(logStream.WriteLine, new[] { RelationalEventId.CommandExecuted });
    }
    public override void Dispose()
    {
        base.Dispose();
        logStream.Dispose();
    }
}

Ссылки на использованные материалы:
1) БД PostreSQL для Windows - https://postgrespro.ru/windows
2) Более подробная инфа по подключению БД через EF - https://metanit.com/sharp/efcore/7.3.php
Как подключить MySQL - https://metanit.com/sharp/efcore/7.2.php
3) Инфа по настройке логгирования - https://metanit.com/sharp/entityframeworkcore/2.16.php
4) Как подключить Postgres к ЗП - https://zennolab.com/discussion/threads/postgresql-subd-i-zennoposter-podkljuchenie-cherez-odbc.43320/
5) Список команд - https://metanit.com/sharp/efcore/5.1.php

 

[sw_sw]

Почему PostreSQL ?

 

[plastov]

Почему PostreSQL ?

Мне массивы нужны были, поэтому выбрал PostreSQL. А так еще плюсом что на винду ставится без бубнов, инфы много.


Как подключить MySQL - https://metanit.com/sharp/efcore/7.2.php

 

[Yuriy Zymlex]

Почему PostreSQL ?

А что в нём плохого?

Создаем консольное приложение

Да, на текущий момент это лучший вариант, чем подключение к шаблону.

 

[sw_sw]

А что в нём плохого?

Да, на текущий момент это лучший вариант, чем подключение к шаблону.

Ничего, просто поинтересовался..