8.2 KiB
8.2 KiB
🗄️ База данных
Описание работы с PostgreSQL в ChatBot.
📊 Схема базы данных
Таблицы
chat_sessions
Хранит информацию о сессиях чатов.
| Колонка | Тип | Constraints | Описание |
|---|---|---|---|
| id | SERIAL | PRIMARY KEY | Auto-increment ID |
| session_id | VARCHAR(50) | UNIQUE, NOT NULL | Уникальный идентификатор |
| chat_id | BIGINT | NOT NULL, INDEXED | Telegram chat ID |
| chat_type | VARCHAR(20) | NOT NULL | Тип чата |
| chat_title | VARCHAR(200) | NULL | Название чата |
| model | VARCHAR(100) | NULL | AI модель |
| created_at | TIMESTAMP | NOT NULL | Дата создания |
| last_updated_at | TIMESTAMP | NOT NULL | Последнее обновление |
Индексы:
CREATE UNIQUE INDEX idx_chat_sessions_session_id ON chat_sessions(session_id);
CREATE INDEX idx_chat_sessions_chat_id ON chat_sessions(chat_id);
chat_messages
Хранит историю сообщений.
| Колонка | Тип | Constraints | Описание |
|---|---|---|---|
| id | SERIAL | PRIMARY KEY | Auto-increment ID |
| session_id | INTEGER | FK, NOT NULL | Ссылка на сессию |
| content | VARCHAR(10000) | NOT NULL | Текст сообщения |
| role | VARCHAR(20) | NOT NULL | user/assistant/system |
| message_order | INTEGER | NOT NULL | Порядок в диалоге |
| created_at | TIMESTAMP | NOT NULL | Время создания |
Foreign Keys:
FOREIGN KEY (session_id) REFERENCES chat_sessions(id) ON DELETE CASCADE
Индексы:
CREATE INDEX idx_chat_messages_session_id ON chat_messages(session_id);
CREATE INDEX idx_chat_messages_created_at ON chat_messages(created_at);
CREATE INDEX idx_chat_messages_session_order ON chat_messages(session_id, message_order);
🔄 Entity Framework Core
DbContext
public class ChatBotDbContext : DbContext
{
public DbSet<ChatSessionEntity> ChatSessions { get; set; }
public DbSet<ChatMessageEntity> ChatMessages { get; set; }
}
Конфигурация моделей
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
// ChatSessionEntity
modelBuilder.Entity<ChatSessionEntity>(entity =>
{
entity.HasKey(e => e.Id);
entity.Property(e => e.SessionId).IsRequired().HasMaxLength(50);
entity.HasIndex(e => e.SessionId).IsUnique();
entity.HasIndex(e => e.ChatId);
entity.HasMany(e => e.Messages)
.WithOne(e => e.Session)
.HasForeignKey(e => e.SessionId)
.OnDelete(DeleteBehavior.Cascade);
});
// ChatMessageEntity
modelBuilder.Entity<ChatMessageEntity>(entity =>
{
entity.HasKey(e => e.Id);
entity.Property(e => e.Content).IsRequired().HasMaxLength(10000);
entity.HasIndex(e => e.SessionId);
entity.HasIndex(e => new { e.SessionId, e.MessageOrder });
});
}
Миграции
Создание миграции
dotnet ef migrations add InitialCreate --project ChatBot
Применение миграций
# Вручную
dotnet ef database update --project ChatBot
# Автоматически при запуске (DatabaseInitializationService)
Откат миграции
dotnet ef database update PreviousMigration --project ChatBot
Удаление последней миграции
dotnet ef migrations remove --project ChatBot
🔌 Подключение к БД
Connection String
Host={host};Port={port};Database={name};Username={user};Password={password}
Пример:
Host=localhost;Port=5432;Database=chatbot;Username=chatbot;Password=secret
Конфигурация в Program.cs
builder.Services.AddDbContext<ChatBotDbContext>(
(serviceProvider, options) =>
{
var dbSettings = serviceProvider
.GetRequiredService<IOptions<DatabaseSettings>>()
.Value;
options.UseNpgsql(
dbSettings.ConnectionString,
npgsqlOptions =>
{
npgsqlOptions.CommandTimeout(dbSettings.CommandTimeout);
}
);
}
);
Connection Pooling
Npgsql автоматически использует connection pooling:
Max Pool Size=100
Min Pool Size=1
Connection Lifetime=300
Connection Idle Lifetime=300
📝 Repository Pattern
Interface
public interface IChatSessionRepository
{
Task<ChatSessionEntity?> GetByChatIdAsync(long chatId);
Task<ChatSessionEntity> CreateAsync(ChatSessionEntity session);
Task UpdateAsync(ChatSessionEntity session);
Task DeleteAsync(int id);
Task<List<ChatSessionEntity>> GetAllAsync();
}
Implementation
public class ChatSessionRepository : IChatSessionRepository
{
private readonly ChatBotDbContext _context;
public async Task<ChatSessionEntity?> GetByChatIdAsync(long chatId)
{
return await _context.ChatSessions
.Include(s => s.Messages)
.FirstOrDefaultAsync(s => s.ChatId == chatId);
}
public async Task<ChatSessionEntity> CreateAsync(ChatSessionEntity session)
{
_context.ChatSessions.Add(session);
await _context.SaveChangesAsync();
return session;
}
}
🚀 Оптимизация запросов
Eager Loading
// Загрузка с сообщениями
var session = await _context.ChatSessions
.Include(s => s.Messages)
.FirstOrDefaultAsync(s => s.ChatId == chatId);
Projections
// Только нужные поля
var sessionInfo = await _context.ChatSessions
.Where(s => s.ChatId == chatId)
.Select(s => new { s.SessionId, s.Model })
.FirstOrDefaultAsync();
AsNoTracking
// Read-only запросы
var sessions = await _context.ChatSessions
.AsNoTracking()
.ToListAsync();
🔧 Обслуживание БД
Vacuum (очистка)
VACUUM ANALYZE chat_sessions;
VACUUM ANALYZE chat_messages;
Статистика
SELECT
schemaname,
tablename,
n_live_tup,
n_dead_tup
FROM pg_stat_user_tables
WHERE tablename IN ('chat_sessions', 'chat_messages');
Размер таблиц
SELECT
tablename,
pg_size_pretty(pg_total_relation_size(tablename::regclass)) as size
FROM pg_tables
WHERE schemaname = 'public';
🛡️ Безопасность
SQL Injection Prevention
Entity Framework Core автоматически параметризует запросы:
// ✅ Безопасно
var session = await _context.ChatSessions
.Where(s => s.ChatId == chatId)
.FirstOrDefaultAsync();
Права пользователя БД
-- Создание пользователя
CREATE USER chatbot WITH PASSWORD 'secure_password';
-- Выдача прав
GRANT CONNECT ON DATABASE chatbot TO chatbot;
GRANT USAGE ON SCHEMA public TO chatbot;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO chatbot;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO chatbot;
📊 Мониторинг
Active Connections
SELECT count(*)
FROM pg_stat_activity
WHERE datname = 'chatbot';
Long Running Queries
SELECT
pid,
now() - pg_stat_activity.query_start AS duration,
query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;
Locks
SELECT * FROM pg_locks
WHERE NOT granted;
🔄 Backup & Restore
Backup
# Полный backup
pg_dump -U chatbot chatbot > backup.sql
# Только схема
pg_dump -U chatbot --schema-only chatbot > schema.sql
# Только данные
pg_dump -U chatbot --data-only chatbot > data.sql
Restore
# Восстановление
psql -U chatbot chatbot < backup.sql