Dmitry
4ed1839893
Some checks are pending
Backend CI / test (push) Waiting to run
Backend CI / build (push) Blocked by required conditions
Web App CI / test (push) Waiting to run
Web App CI / build (push) Blocked by required conditions
Deploy Mnemo Cards / Deploy Backend (push) Waiting to run
Deploy Mnemo Cards / Deploy Web App (push) Blocked by required conditions
Deploy Mnemo Cards / Final Verification (push) Blocked by required conditions
426 lines
13 KiB
Dart
426 lines
13 KiB
Dart
import 'package:drift/drift.dart';
|
||
import 'package:drift_postgres/drift_postgres.dart';
|
||
import 'package:postgres/postgres.dart' as pg;
|
||
import 'dart:io';
|
||
import 'dart:convert';
|
||
|
||
// Импорт конвертеров (нужен для генерации кода)
|
||
import 'converters.dart';
|
||
|
||
// Импорт всех таблиц
|
||
import 'tables/users.dart';
|
||
import 'tables/auth.dart';
|
||
import 'tables/packs.dart';
|
||
import 'tables/relations.dart';
|
||
import 'tables/subscriptions.dart';
|
||
import 'tables/payments.dart';
|
||
import 'tables/tests.dart';
|
||
import 'tables/tasks.dart';
|
||
import 'tables/promo_codes.dart';
|
||
import 'tables/discounts.dart';
|
||
import 'tables/statistics.dart';
|
||
import 'tables/telegram.dart';
|
||
import 'tables/achievements.dart';
|
||
import 'tables/word_statistics.dart';
|
||
import 'tables/audit.dart';
|
||
|
||
// Импорт DAOs
|
||
import 'daos/user_dao.dart';
|
||
import 'daos/pack_dao.dart';
|
||
import 'daos/test_dao.dart';
|
||
import 'daos/payment_dao.dart';
|
||
import 'daos/subscription_dao.dart';
|
||
import 'daos/task_dao.dart';
|
||
import 'daos/promo_code_dao.dart';
|
||
import 'daos/discount_dao.dart';
|
||
import 'daos/statistics_dao.dart';
|
||
import 'daos/achievement_dao.dart';
|
||
import 'daos/word_statistics_dao.dart';
|
||
import 'daos/audit_dao.dart';
|
||
|
||
// Сгенерированный код будет здесь
|
||
part 'database.g.dart';
|
||
|
||
@DriftDatabase(
|
||
tables: [
|
||
// User tables
|
||
Users,
|
||
UserDatas,
|
||
|
||
// Auth tables
|
||
Tokens,
|
||
RefreshTokens,
|
||
TelegramAuthCodes,
|
||
|
||
// Pack tables
|
||
CardPacks,
|
||
GameCards,
|
||
VoiceModels,
|
||
|
||
// Relations
|
||
UserPacks,
|
||
PreviewCards,
|
||
CardPackCards,
|
||
CardVoices,
|
||
|
||
// Subscription tables
|
||
SubscriptionPlans,
|
||
UserSubscriptions,
|
||
|
||
// Payment tables
|
||
Payments,
|
||
|
||
// Test tables
|
||
Tests,
|
||
TestQuestions,
|
||
TestPackRelations,
|
||
TestStatistics,
|
||
|
||
// Task tables
|
||
Tasks,
|
||
UserTasks,
|
||
UserTaskProgresses,
|
||
UserTaskResults,
|
||
|
||
// Promo code tables
|
||
PromoCodesCampaigns,
|
||
PromoCodes,
|
||
|
||
// Discount tables
|
||
DiscountCampaigns,
|
||
Discounts,
|
||
DiscountUserDatas,
|
||
|
||
// Statistics tables
|
||
StudySessions,
|
||
WordStatistics,
|
||
|
||
// Achievements tables
|
||
UserAchievements,
|
||
|
||
// Telegram tables
|
||
ShareRequests,
|
||
|
||
// Audit tables
|
||
AuditLogs,
|
||
],
|
||
daos: [
|
||
UserDao,
|
||
PackDao,
|
||
TestDao,
|
||
PaymentDao,
|
||
SubscriptionDao,
|
||
TaskDao,
|
||
PromoCodeDao,
|
||
DiscountDao,
|
||
StatisticsDao,
|
||
AchievementDao,
|
||
WordStatisticsDao,
|
||
AuditDao,
|
||
],
|
||
)
|
||
class AppDatabase extends _$AppDatabase {
|
||
AppDatabase(super.e);
|
||
|
||
@override
|
||
int get schemaVersion => 3;
|
||
|
||
/// Factory для подключения к PostgreSQL
|
||
static AppDatabase connect({
|
||
required String host,
|
||
required int port,
|
||
required String database,
|
||
required String username,
|
||
required String password,
|
||
bool useSsl = false,
|
||
}) {
|
||
final endpoint = pg.Endpoint(
|
||
host: host,
|
||
port: port,
|
||
database: database,
|
||
username: username,
|
||
password: password,
|
||
);
|
||
|
||
final connection = PgDatabase(
|
||
endpoint: endpoint,
|
||
settings: pg.ConnectionSettings(
|
||
sslMode: useSsl ? pg.SslMode.require : pg.SslMode.disable,
|
||
connectTimeout: const Duration(seconds: 10),
|
||
),
|
||
);
|
||
|
||
return AppDatabase(connection);
|
||
}
|
||
|
||
/// Factory для подключения из environment variables
|
||
static AppDatabase fromEnvironment() {
|
||
return connect(
|
||
host: Platform.environment['DB_HOST'] ?? 'localhost',
|
||
port: int.parse(Platform.environment['DB_PORT'] ?? '5432'),
|
||
database: Platform.environment['DB_NAME'] ?? 'mnemo_cards_dev',
|
||
username: Platform.environment['DB_USER'] ?? 'mnemo_user',
|
||
password: Platform.environment['DB_PASSWORD'] ?? '',
|
||
useSsl: Platform.environment['DB_SSL_MODE'] == 'require',
|
||
);
|
||
}
|
||
|
||
@override
|
||
MigrationStrategy get migration => MigrationStrategy(
|
||
onCreate: (Migrator m) async {
|
||
print('Creating database schema...');
|
||
await m.createAll();
|
||
print('Database schema created successfully');
|
||
|
||
// Создать индексы для оптимизации
|
||
await _createIndexes();
|
||
},
|
||
onUpgrade: (Migrator m, int from, int to) async {
|
||
print('Migrating database from version $from to $to');
|
||
|
||
// Миграция с версии 1 на 2: обновление структуры тестов
|
||
if (from < 2) {
|
||
await _migrateToV2(m);
|
||
}
|
||
|
||
// Миграция с версии 2 на 3: добавление telegram в users
|
||
if (from < 3) {
|
||
await _migrateToV3(m);
|
||
}
|
||
},
|
||
beforeOpen: (details) async {
|
||
print('Opening database connection...');
|
||
|
||
// Проверка подключения
|
||
final result = await customSelect('SELECT 1 as test').getSingle();
|
||
print('Database connection successful: ${result.data}');
|
||
|
||
// Включить foreign key constraints
|
||
await customStatement('SET CONSTRAINTS ALL IMMEDIATE');
|
||
},
|
||
);
|
||
|
||
/// Создание индексов для оптимизации запросов
|
||
Future<void> _createIndexes() async {
|
||
print('Creating indexes...');
|
||
|
||
// Users indexes
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_users_email ON users(email) WHERE email IS NOT NULL',
|
||
);
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_users_telegram ON users(telegram) WHERE telegram IS NOT NULL',
|
||
);
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_users_admin ON users(admin) WHERE admin = TRUE',
|
||
);
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_users_not_deleted ON users(is_deleted) WHERE is_deleted = FALSE',
|
||
);
|
||
|
||
// UserDatas indexes
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_user_datas_user_id ON user_datas(user_id)',
|
||
);
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_user_datas_last_online ON user_datas(last_time_online DESC)',
|
||
);
|
||
|
||
// Auth indexes
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_tokens_user_id ON tokens(user_id)',
|
||
);
|
||
// Индекс на expires без предиката (NOW() не может быть использована в предикате индекса)
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_tokens_expires ON tokens(expires)',
|
||
);
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_refresh_tokens_user_id ON refresh_tokens(user_id)',
|
||
);
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_refresh_tokens_jti ON refresh_tokens(jti)',
|
||
);
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_refresh_tokens_not_blacklisted ON refresh_tokens(is_blacklisted) WHERE is_blacklisted = FALSE',
|
||
);
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_telegram_codes_code ON telegram_auth_codes(code)',
|
||
);
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_telegram_codes_not_used ON telegram_auth_codes(is_used) WHERE is_used = FALSE',
|
||
);
|
||
|
||
// CardPacks indexes
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_packs_enabled ON card_packs(enabled) WHERE enabled = TRUE',
|
||
);
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_packs_order ON card_packs("order")',
|
||
);
|
||
|
||
// GameCards indexes
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_cards_original ON game_cards(original)',
|
||
);
|
||
|
||
// UserPacks indexes
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_user_packs_user_id ON user_packs(user_id)',
|
||
);
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_user_packs_pack_id ON user_packs(pack_id)',
|
||
);
|
||
|
||
// Payments indexes
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_payments_user_id ON payments(user_id)',
|
||
);
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_payments_status ON payments(status)',
|
||
);
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_payments_created ON payments(date DESC)',
|
||
);
|
||
|
||
// Subscriptions indexes
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_subscriptions_user_id ON user_subscriptions(user_id)',
|
||
);
|
||
// Индекс на finish без предиката (NOW() не может быть использована в предикате индекса)
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_subscriptions_finish ON user_subscriptions(finish)',
|
||
);
|
||
|
||
// Tests indexes
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_test_questions_test_id ON test_questions(test_id)',
|
||
);
|
||
|
||
// StudySessions indexes
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON study_sessions(user_id)',
|
||
);
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_sessions_started ON study_sessions(start_time DESC)',
|
||
);
|
||
|
||
// PromoCodes indexes
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_promo_codes_code ON promo_codes(code)',
|
||
);
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_promo_codes_campaign_id ON promo_codes(campaign_id)',
|
||
);
|
||
|
||
print('Indexes created successfully');
|
||
}
|
||
|
||
/// Миграция с версии 1 на версию 2
|
||
/// Обновление структуры тестов: добавление новых колонок и очистка старых данных
|
||
Future<void> _migrateToV2(Migrator m) async {
|
||
print(
|
||
'Starting migration to v2: updating test questions and statistics...',
|
||
);
|
||
|
||
try {
|
||
// 1. Добавляем новые колонки в test_questions
|
||
print('Adding new columns to test_questions...');
|
||
await customStatement(
|
||
'ALTER TABLE test_questions '
|
||
'ADD COLUMN IF NOT EXISTS order_index INTEGER DEFAULT 0, '
|
||
'ADD COLUMN IF NOT EXISTS word TEXT DEFAULT \'\', '
|
||
'ADD COLUMN IF NOT EXISTS answer TEXT DEFAULT \'\', '
|
||
'ADD COLUMN IF NOT EXISTS options TEXT DEFAULT \'[]\', '
|
||
'ADD COLUMN IF NOT EXISTS ui_data TEXT DEFAULT \'{}\'',
|
||
);
|
||
|
||
// 2. Удаляем все старые вопросы (soft delete)
|
||
print('Soft-deleting all existing test questions...');
|
||
final now = PgDateTime(DateTime.now());
|
||
await (update(
|
||
testQuestions,
|
||
)..where((tq) => tq.isDeleted.equals(false))).write(
|
||
TestQuestionsCompanion(
|
||
isDeleted: const Value(true),
|
||
deletedAt: Value(now),
|
||
updatedAt: Value(now),
|
||
),
|
||
);
|
||
print(
|
||
'All old questions marked as deleted. Create new questions via admin panel.',
|
||
);
|
||
|
||
// 3. Удаляем старую колонку body
|
||
print('Dropping old body column...');
|
||
await customStatement(
|
||
'ALTER TABLE test_questions DROP COLUMN IF EXISTS body',
|
||
);
|
||
|
||
// 4. Добавляем новые колонки в test_statistics
|
||
print('Adding new columns to test_statistics...');
|
||
await customStatement(
|
||
'ALTER TABLE test_statistics '
|
||
'ADD COLUMN IF NOT EXISTS total_questions INTEGER DEFAULT 0, '
|
||
'ADD COLUMN IF NOT EXISTS correct_answers INTEGER DEFAULT 0, '
|
||
'ADD COLUMN IF NOT EXISTS incorrect_answers INTEGER DEFAULT 0, '
|
||
'ADD COLUMN IF NOT EXISTS skipped_answers INTEGER DEFAULT 0, '
|
||
'ADD COLUMN IF NOT EXISTS score_percentage REAL DEFAULT 0.0, '
|
||
'ADD COLUMN IF NOT EXISTS time_spent_seconds INTEGER, '
|
||
'ADD COLUMN IF NOT EXISTS question_results TEXT DEFAULT \'[]\', '
|
||
'ADD COLUMN IF NOT EXISTS metadata TEXT DEFAULT \'{}\'',
|
||
);
|
||
|
||
// 5. Удаляем всю старую статистику (она будет создаваться заново)
|
||
print('Deleting old test statistics...');
|
||
await (delete(testStatistics)).go();
|
||
print(
|
||
'All old statistics deleted. New statistics will be collected automatically.',
|
||
);
|
||
|
||
// 6. Удаляем старую колонку results
|
||
print('Dropping old results column...');
|
||
await customStatement(
|
||
'ALTER TABLE test_statistics DROP COLUMN IF EXISTS results',
|
||
);
|
||
|
||
print('Migration to v2 completed successfully!');
|
||
} catch (e, stackTrace) {
|
||
print('Error during migration to v2: $e');
|
||
print('Stack trace: $stackTrace');
|
||
rethrow;
|
||
}
|
||
}
|
||
|
||
/// Миграция с версии 2 на версию 3
|
||
/// Добавление поля telegram в users и перенос старых telegram-логинов из email
|
||
Future<void> _migrateToV3(Migrator m) async {
|
||
print('Starting migration to v3: adding telegram to users...');
|
||
|
||
try {
|
||
await customStatement(
|
||
'ALTER TABLE users ADD COLUMN IF NOT EXISTS telegram TEXT',
|
||
);
|
||
|
||
// Раньше Telegram-username сохранялся в email. Переносим "похожие на username"
|
||
// значения в telegram и очищаем email.
|
||
await customStatement(
|
||
'UPDATE users '
|
||
'SET telegram = email, email = NULL '
|
||
'WHERE (telegram IS NULL OR telegram = \'\') '
|
||
'AND email IS NOT NULL AND email != \'\' '
|
||
'AND POSITION(\'@\' IN email) = 0',
|
||
);
|
||
|
||
// Индексы на existing db
|
||
await customStatement(
|
||
'CREATE INDEX IF NOT EXISTS idx_users_telegram ON users(telegram) WHERE telegram IS NOT NULL',
|
||
);
|
||
|
||
print('Migration to v3 completed successfully!');
|
||
} catch (e, stackTrace) {
|
||
print('Error during migration to v3: $e');
|
||
print('Stack trace: $stackTrace');
|
||
rethrow;
|
||
}
|
||
}
|
||
}
|