352 lines
8.2 KiB
Markdown
352 lines
8.2 KiB
Markdown
# 🗄️ База данных
|
||
|
||
Описание работы с 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 | Последнее обновление |
|
||
|
||
**Индексы:**
|
||
```sql
|
||
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:**
|
||
```sql
|
||
FOREIGN KEY (session_id) REFERENCES chat_sessions(id) ON DELETE CASCADE
|
||
```
|
||
|
||
**Индексы:**
|
||
```sql
|
||
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
|
||
|
||
```csharp
|
||
public class ChatBotDbContext : DbContext
|
||
{
|
||
public DbSet<ChatSessionEntity> ChatSessions { get; set; }
|
||
public DbSet<ChatMessageEntity> ChatMessages { get; set; }
|
||
}
|
||
```
|
||
|
||
### Конфигурация моделей
|
||
|
||
```csharp
|
||
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 });
|
||
});
|
||
}
|
||
```
|
||
|
||
### Миграции
|
||
|
||
#### Создание миграции
|
||
|
||
```bash
|
||
dotnet ef migrations add InitialCreate --project ChatBot
|
||
```
|
||
|
||
#### Применение миграций
|
||
|
||
```bash
|
||
# Вручную
|
||
dotnet ef database update --project ChatBot
|
||
|
||
# Автоматически при запуске (DatabaseInitializationService)
|
||
```
|
||
|
||
#### Откат миграции
|
||
|
||
```bash
|
||
dotnet ef database update PreviousMigration --project ChatBot
|
||
```
|
||
|
||
#### Удаление последней миграции
|
||
|
||
```bash
|
||
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
|
||
|
||
```csharp
|
||
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
|
||
|
||
```csharp
|
||
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
|
||
|
||
```csharp
|
||
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
|
||
|
||
```csharp
|
||
// Загрузка с сообщениями
|
||
var session = await _context.ChatSessions
|
||
.Include(s => s.Messages)
|
||
.FirstOrDefaultAsync(s => s.ChatId == chatId);
|
||
```
|
||
|
||
### Projections
|
||
|
||
```csharp
|
||
// Только нужные поля
|
||
var sessionInfo = await _context.ChatSessions
|
||
.Where(s => s.ChatId == chatId)
|
||
.Select(s => new { s.SessionId, s.Model })
|
||
.FirstOrDefaultAsync();
|
||
```
|
||
|
||
### AsNoTracking
|
||
|
||
```csharp
|
||
// Read-only запросы
|
||
var sessions = await _context.ChatSessions
|
||
.AsNoTracking()
|
||
.ToListAsync();
|
||
```
|
||
|
||
## 🔧 Обслуживание БД
|
||
|
||
### Vacuum (очистка)
|
||
|
||
```sql
|
||
VACUUM ANALYZE chat_sessions;
|
||
VACUUM ANALYZE chat_messages;
|
||
```
|
||
|
||
### Статистика
|
||
|
||
```sql
|
||
SELECT
|
||
schemaname,
|
||
tablename,
|
||
n_live_tup,
|
||
n_dead_tup
|
||
FROM pg_stat_user_tables
|
||
WHERE tablename IN ('chat_sessions', 'chat_messages');
|
||
```
|
||
|
||
### Размер таблиц
|
||
|
||
```sql
|
||
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 автоматически параметризует запросы:
|
||
|
||
```csharp
|
||
// ✅ Безопасно
|
||
var session = await _context.ChatSessions
|
||
.Where(s => s.ChatId == chatId)
|
||
.FirstOrDefaultAsync();
|
||
```
|
||
|
||
### Права пользователя БД
|
||
|
||
```sql
|
||
-- Создание пользователя
|
||
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
|
||
|
||
```sql
|
||
SELECT count(*)
|
||
FROM pg_stat_activity
|
||
WHERE datname = 'chatbot';
|
||
```
|
||
|
||
### Long Running Queries
|
||
|
||
```sql
|
||
SELECT
|
||
pid,
|
||
now() - pg_stat_activity.query_start AS duration,
|
||
query
|
||
FROM pg_stat_activity
|
||
WHERE state = 'active'
|
||
ORDER BY duration DESC;
|
||
```
|
||
|
||
### Locks
|
||
|
||
```sql
|
||
SELECT * FROM pg_locks
|
||
WHERE NOT granted;
|
||
```
|
||
|
||
## 🔄 Backup & Restore
|
||
|
||
### Backup
|
||
|
||
```bash
|
||
# Полный 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
|
||
|
||
```bash
|
||
# Восстановление
|
||
psql -U chatbot chatbot < backup.sql
|
||
```
|
||
|
||
## 📚 См. также
|
||
|
||
- [Модели данных](./data-models.md)
|
||
- [Конфигурация](../configuration.md)
|
||
- [Установка](../installation.md)
|