427 lines
14 KiB
Dart
427 lines
14 KiB
Dart
// Adatbázis-séma füstpróba — emulátor nélkül, gépen fut:
|
|
// flutter test test/app_database_schema_test.dart
|
|
//
|
|
// Mit fog meg:
|
|
// * _onCreate szintaxishibák (pl. hiányzó vessző a DDL-ben) — a friss
|
|
// telepítés tesztje azonnal elszáll rajta
|
|
// * hiányzó táblák/oszlopok friss telepítés után
|
|
// * a v1 → v4 migráció helyessége: oszlopok, uuid-backfill,
|
|
// updated_at-backfill, supabase_id → uuid öröklés, árva-takarítás
|
|
// * uuid-egyediség (unique index tényleg él)
|
|
// * FK CASCADE tényleg működik (a pragma bekapcsolásával)
|
|
// * migráció-idempotencia (régi verziószámozású eszköz esete) —
|
|
// ez a teszt a _tryExec védelmet igényli a _migrateToV4-ben!
|
|
|
|
import 'dart:io';
|
|
|
|
import 'package:flutter_test/flutter_test.dart';
|
|
import 'package:path/path.dart' as p;
|
|
import 'package:sqflite/sqflite.dart';
|
|
import 'package:sqflite_common_ffi/sqflite_ffi.dart';
|
|
import 'package:terepi_seged/services/app_database.dart';
|
|
|
|
void main() {
|
|
sqfliteFfiInit();
|
|
final factory = databaseFactoryFfi;
|
|
|
|
// ── Segédek ────────────────────────────────────────────────────────
|
|
|
|
Future<Set<String>> tableNames(Database db) async =>
|
|
(await db.rawQuery("SELECT name FROM sqlite_master WHERE type = 'table'"))
|
|
.map((r) => r['name'] as String)
|
|
.toSet();
|
|
|
|
Future<Set<String>> columnNames(Database db, String table) async =>
|
|
(await db.rawQuery('PRAGMA table_info($table)'))
|
|
.map((r) => r['name'] as String)
|
|
.toSet();
|
|
|
|
Future<int> count(Database db, String sql,
|
|
[List<Object?> args = const []]) async =>
|
|
Sqflite.firstIntValue(await db.rawQuery(sql, args)) ?? 0;
|
|
|
|
// A szinkronhoz KRITIKUS oszlopok táblánként (részhalmaz-ellenőrzés:
|
|
// a tényleges séma tartalmazhat többet, kevesebbet nem).
|
|
const requiredColumns = <String, Set<String>>{
|
|
'projects': {
|
|
'id',
|
|
'uuid',
|
|
'name',
|
|
'status',
|
|
'is_local_only',
|
|
'created_at',
|
|
'updated_at',
|
|
'deleted_at',
|
|
'sync_status',
|
|
'last_pulled_at',
|
|
},
|
|
'measured_points': {
|
|
'id',
|
|
'uuid',
|
|
'project_id',
|
|
'name',
|
|
'timestamp',
|
|
'created_by',
|
|
'device_id',
|
|
'updated_at',
|
|
'deleted_at',
|
|
'sync_status',
|
|
},
|
|
'tracks': {
|
|
'id',
|
|
'uuid',
|
|
'project_id',
|
|
'name',
|
|
'start_time',
|
|
'sync_status',
|
|
'supabase_id',
|
|
'created_by',
|
|
'device_id',
|
|
'updated_at',
|
|
'deleted_at',
|
|
},
|
|
'track_points': {
|
|
'id',
|
|
'uuid',
|
|
'track_id',
|
|
'latitude',
|
|
'longitude',
|
|
'timestamp',
|
|
'sync_status',
|
|
},
|
|
'note_items': {
|
|
'id',
|
|
'uuid',
|
|
'project_id',
|
|
'type',
|
|
'points_json',
|
|
'created_at',
|
|
'created_by',
|
|
'updated_at',
|
|
'deleted_at',
|
|
'sync_status',
|
|
},
|
|
'stakeout_points': {
|
|
'id',
|
|
'uuid',
|
|
'project_id',
|
|
'line_id',
|
|
'station',
|
|
'name',
|
|
'plan_eov_y',
|
|
'plan_eov_x',
|
|
'plan_lat',
|
|
'plan_lon',
|
|
'status',
|
|
'is_offset',
|
|
'parent_uuid',
|
|
'device_id',
|
|
'created_at',
|
|
'updated_at',
|
|
'deleted_at',
|
|
'sync_status',
|
|
},
|
|
};
|
|
|
|
Future<void> assertSchema(Database db) async {
|
|
final tables = await tableNames(db);
|
|
for (final entry in requiredColumns.entries) {
|
|
expect(tables, contains(entry.key),
|
|
reason: 'Hiányzó tábla: ${entry.key}');
|
|
final cols = await columnNames(db, entry.key);
|
|
final missing = entry.value.difference(cols);
|
|
expect(missing, isEmpty,
|
|
reason: 'Hiányzó oszlop(ok) a(z) ${entry.key} táblában: $missing');
|
|
}
|
|
}
|
|
|
|
// ═══════════════════════════════════════════════════════════════════
|
|
// 1. FRISS TELEPÍTÉS
|
|
// ═══════════════════════════════════════════════════════════════════
|
|
|
|
test('friss telepítés: _onCreate hibátlanul lefut, séma teljes', () async {
|
|
final db = await factory.openDatabase(
|
|
inMemoryDatabasePath,
|
|
options: OpenDatabaseOptions(
|
|
version: 4,
|
|
onConfigure: (db) => db.execute('PRAGMA foreign_keys = ON'),
|
|
onCreate: (db, v) => AppDatabase.instance.createSchema(db),
|
|
),
|
|
);
|
|
addTearDown(db.close);
|
|
|
|
await assertSchema(db);
|
|
|
|
// Alapértelmezett projekt létrejött.
|
|
expect(
|
|
await count(db, "SELECT COUNT(*) FROM projects WHERE is_default = 1"),
|
|
1);
|
|
|
|
// FK CASCADE tényleg él: projekt törlése viszi a pontját.
|
|
final pid = await db.insert('projects', {
|
|
'uuid': 'proj-cascade-test',
|
|
'name': 'Cascade teszt',
|
|
'created_at': '2026-01-01T00:00:00',
|
|
'updated_at': '2026-01-01T00:00:00',
|
|
});
|
|
await db.insert('measured_points', {
|
|
'uuid': 'mp-cascade-test',
|
|
'project_id': pid,
|
|
'name': 'P1',
|
|
'timestamp': '2026-01-01T00:00:00',
|
|
});
|
|
await db.delete('projects', where: 'id = ?', whereArgs: [pid]);
|
|
expect(
|
|
await count(db,
|
|
'SELECT COUNT(*) FROM measured_points WHERE project_id = ?', [pid]),
|
|
0,
|
|
reason: 'A FK CASCADE nem működik — hiányzik a PRAGMA?');
|
|
|
|
// uuid-egyediség: duplikátum beszúrása hibát dob.
|
|
await db.insert('measured_points', {
|
|
'uuid': 'dup-uuid',
|
|
'project_id': 1,
|
|
'name': 'A',
|
|
'timestamp': '2026-01-01T00:00:00',
|
|
});
|
|
expect(
|
|
() => db.insert('measured_points', {
|
|
'uuid': 'dup-uuid',
|
|
'project_id': 1,
|
|
'name': 'B',
|
|
'timestamp': '2026-01-01T00:00:00',
|
|
}),
|
|
throwsA(isA<DatabaseException>()),
|
|
reason: 'A uuid unique megkötés nem él.',
|
|
);
|
|
});
|
|
|
|
// ═══════════════════════════════════════════════════════════════════
|
|
// 2. MIGRÁCIÓ v1 → v4
|
|
// ═══════════════════════════════════════════════════════════════════
|
|
|
|
test('migráció v1 → 4: oszlopok, backfill, árva-takarítás', () async {
|
|
final dir = Directory.systemTemp.createTempSync('ts_db_test');
|
|
final path = p.join(dir.path, 'migr.db');
|
|
addTearDown(() => dir.deleteSync(recursive: true));
|
|
|
|
// v1-es (örökölt) séma + magvak.
|
|
var db = await factory.openDatabase(
|
|
path,
|
|
options: OpenDatabaseOptions(
|
|
version: 1,
|
|
onCreate: (db, v) => _createLegacyV1(db),
|
|
),
|
|
);
|
|
await db.insert('projects', {
|
|
'uuid': 'p-1',
|
|
'name': 'Régi projekt',
|
|
'created_at': '2025-01-01T10:00:00',
|
|
'updated_at': '2025-01-01T10:00:00',
|
|
});
|
|
await db.insert('measured_points', {
|
|
'project_id': 1,
|
|
'name': '101',
|
|
'timestamp': '2025-01-02T08:30:00',
|
|
});
|
|
await db.insert('tracks', {
|
|
'project_id': 1,
|
|
'name': 'T1',
|
|
'start_time': '2025-01-02T09:00:00',
|
|
'supabase_id': 'sb-uuid-orokolt-ertek',
|
|
});
|
|
await db.insert('track_points', {
|
|
'track_id': 1,
|
|
'latitude': 47.0,
|
|
'longitude': 19.0,
|
|
'timestamp': '2025-01-02T09:00:01',
|
|
});
|
|
// ÁRVA track-pont (nem létező trackre) — a takarításnak vinnie kell.
|
|
await db.insert('track_points', {
|
|
'track_id': 999,
|
|
'latitude': 47.0,
|
|
'longitude': 19.0,
|
|
'timestamp': '2025-01-02T09:00:02',
|
|
});
|
|
await db.insert('note_items', {
|
|
'project_id': 1,
|
|
'type': 'point',
|
|
'points_json': '[]',
|
|
'created_at': '2025-01-03T12:00:00',
|
|
});
|
|
await db.close();
|
|
|
|
// Újranyitás v4-gyel → a VALÓDI _onUpgrade fut.
|
|
db = await factory.openDatabase(
|
|
path,
|
|
options: OpenDatabaseOptions(
|
|
version: 4,
|
|
onConfigure: (db) => db.execute('PRAGMA foreign_keys = ON'),
|
|
onUpgrade: (db, oldV, newV) =>
|
|
AppDatabase.instance.upgradeSchema(db, oldV, newV),
|
|
),
|
|
);
|
|
addTearDown(db.close);
|
|
|
|
await assertSchema(db);
|
|
|
|
// uuid-backfill: sehol nincs NULL uuid.
|
|
for (final t in [
|
|
'projects',
|
|
'measured_points',
|
|
'tracks',
|
|
'track_points',
|
|
'note_items'
|
|
]) {
|
|
expect(await count(db, 'SELECT COUNT(*) FROM $t WHERE uuid IS NULL'), 0,
|
|
reason: 'Backfill kimaradt: $t.uuid NULL sorokat tartalmaz.');
|
|
}
|
|
|
|
// updated_at-backfill a mérés időpontjából.
|
|
final mp = (await db.query('measured_points', limit: 1)).first;
|
|
expect(mp['updated_at'], '2025-01-02T08:30:00');
|
|
|
|
// A track a supabase_id-jét örökli uuid-ként.
|
|
final tr = (await db.query('tracks', limit: 1)).first;
|
|
expect(tr['uuid'], 'sb-uuid-orokolt-ertek');
|
|
|
|
// Árva-takarítás.
|
|
expect(
|
|
await count(
|
|
db, 'SELECT COUNT(*) FROM track_points WHERE track_id = 999'),
|
|
0,
|
|
reason: 'Az árva track_points sorok nem törlődtek.');
|
|
});
|
|
|
|
// ═══════════════════════════════════════════════════════════════════
|
|
// 3. MIGRÁCIÓ-IDEMPOTENCIA (régi verziószámozású eszköz esete)
|
|
// ═══════════════════════════════════════════════════════════════════
|
|
|
|
test(
|
|
'migráció idempotens: már meglévő oszlopokra futtatva sem dob hibát '
|
|
'(ehhez kell a _tryExec védelem a _migrateToV4-ben!)', () async {
|
|
final db = await factory.openDatabase(
|
|
inMemoryDatabasePath,
|
|
options: OpenDatabaseOptions(
|
|
version: 4,
|
|
onCreate: (db, v) => AppDatabase.instance.createSchema(db),
|
|
),
|
|
);
|
|
addTearDown(db.close);
|
|
|
|
// A séma már v4 — a 3→4 migráció újrafuttatása duplicate column-ba
|
|
// futna védelem nélkül.
|
|
await expectLater(
|
|
AppDatabase.instance.upgradeSchema(db, 3, 4),
|
|
completes,
|
|
reason: 'A _migrateToV4 nem idempotens — hiányzik a _tryExec?',
|
|
);
|
|
});
|
|
}
|
|
|
|
// ═════════════════════════════════════════════════════════════════════
|
|
// Örökölt v1-es séma — csak a migráció által érintett táblák, minimális
|
|
// (de a migrációhoz elegendő) oszlopkészlettel.
|
|
// ═════════════════════════════════════════════════════════════════════
|
|
|
|
Future<void> _createLegacyV1(Database db) async {
|
|
await db.execute('''
|
|
CREATE TABLE projects (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
uuid TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
client TEXT NOT NULL DEFAULT '',
|
|
description TEXT NOT NULL DEFAULT '',
|
|
crs TEXT NOT NULL DEFAULT 'eov',
|
|
color TEXT NOT NULL DEFAULT '#185FA5',
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
is_default INTEGER NOT NULL DEFAULT 0,
|
|
is_local_only INTEGER NOT NULL DEFAULT 1,
|
|
last_synced_at TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
)
|
|
''');
|
|
await db.execute('''
|
|
CREATE TABLE measured_points (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
project_id INTEGER NOT NULL,
|
|
name TEXT NOT NULL,
|
|
eov_y REAL, eov_x REAL, eov_z REAL,
|
|
latitude REAL, longitude REAL, altitude REAL,
|
|
accuracy REAL, fix_quality INTEGER,
|
|
timestamp TEXT NOT NULL,
|
|
note TEXT NOT NULL DEFAULT ''
|
|
)
|
|
''');
|
|
await db.execute('''
|
|
CREATE TABLE tracks (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
project_id INTEGER,
|
|
name TEXT NOT NULL,
|
|
start_time TEXT NOT NULL,
|
|
end_time TEXT,
|
|
status TEXT NOT NULL DEFAULT 'recording',
|
|
source TEXT NOT NULL DEFAULT 'Telefon GPS',
|
|
distance_m REAL NOT NULL DEFAULT 0,
|
|
point_count INTEGER NOT NULL DEFAULT 0,
|
|
is_local_only INTEGER NOT NULL DEFAULT 0,
|
|
sync_status TEXT NOT NULL DEFAULT 'pending',
|
|
supabase_id TEXT
|
|
)
|
|
''');
|
|
await db.execute('''
|
|
CREATE TABLE track_points (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
track_id INTEGER NOT NULL,
|
|
latitude REAL NOT NULL,
|
|
longitude REAL NOT NULL,
|
|
altitude REAL, accuracy REAL, speed REAL, heading REAL,
|
|
timestamp TEXT NOT NULL
|
|
)
|
|
''');
|
|
await db.execute('''
|
|
CREATE TABLE note_items (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
project_id INTEGER,
|
|
type TEXT NOT NULL,
|
|
points_json TEXT NOT NULL,
|
|
color TEXT NOT NULL DEFAULT '#185FA5',
|
|
opacity REAL NOT NULL DEFAULT 0.5,
|
|
stroke_width REAL NOT NULL DEFAULT 3.0,
|
|
stroke_color TEXT NOT NULL DEFAULT '#FFD700',
|
|
label TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL
|
|
)
|
|
''');
|
|
await db.execute('''
|
|
CREATE TABLE note_item_photos (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
note_item_id INTEGER NOT NULL,
|
|
local_path TEXT NOT NULL,
|
|
created_at TEXT NOT NULL
|
|
)
|
|
''');
|
|
await db.execute('''
|
|
CREATE TABLE note_item_audios (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
note_item_id INTEGER NOT NULL,
|
|
local_path TEXT NOT NULL,
|
|
created_at TEXT NOT NULL
|
|
)
|
|
''');
|
|
await db.execute('''
|
|
CREATE TABLE imported_layers (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
source_type TEXT NOT NULL,
|
|
local_path TEXT NOT NULL,
|
|
storage_path TEXT,
|
|
is_visible INTEGER NOT NULL DEFAULT 1,
|
|
project_id INTEGER,
|
|
imported_at TEXT NOT NULL,
|
|
synced_at TEXT
|
|
)
|
|
''');
|
|
}
|