139 lines
4.9 KiB
C#
139 lines
4.9 KiB
C#
using System;
|
|
using Microsoft.Data.Sqlite;
|
|
using System.IO;
|
|
|
|
namespace ReSync
|
|
{
|
|
public class DataBaseConnector
|
|
{
|
|
private string _dbPath;
|
|
|
|
public DataBaseConnector(string dbPath)
|
|
{
|
|
_dbPath = dbPath;
|
|
InitializeDatabase();
|
|
}
|
|
|
|
private void InitializeDatabase()
|
|
{
|
|
// Ensure database file exists
|
|
if (!File.Exists(_dbPath))
|
|
{
|
|
Directory.CreateDirectory(Path.GetDirectoryName(_dbPath) ?? "");
|
|
File.Create(_dbPath).Dispose();
|
|
}
|
|
|
|
// Open connection and apply schema (CREATE TABLE IF NOT EXISTS ...)
|
|
using (var connection = new SqliteConnection($"Data Source={_dbPath}"))
|
|
{
|
|
connection.Open();
|
|
|
|
// Ensure foreign key support is enabled
|
|
using (var pragma = connection.CreateCommand())
|
|
{
|
|
pragma.CommandText = "PRAGMA foreign_keys = ON;";
|
|
pragma.ExecuteNonQuery();
|
|
}
|
|
|
|
var command = connection.CreateCommand();
|
|
command.CommandText = @"
|
|
-- Devices: phones or other sources
|
|
CREATE TABLE IF NOT EXISTS device (
|
|
id INTEGER PRIMARY KEY,
|
|
uuid TEXT UNIQUE,
|
|
name TEXT,
|
|
type TEXT,
|
|
last_seen INTEGER,
|
|
metadata TEXT
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_device_uuid ON device(uuid);
|
|
|
|
-- Media: canonical original files detected (one row per unique original hash)
|
|
CREATE TABLE IF NOT EXISTS media (
|
|
id INTEGER PRIMARY KEY,
|
|
original_hash TEXT NOT NULL,
|
|
original_path TEXT,
|
|
storage_type TEXT,
|
|
file_name TEXT,
|
|
mime_type TEXT,
|
|
size INTEGER,
|
|
width INTEGER,
|
|
height INTEGER,
|
|
duration REAL,
|
|
created_at INTEGER,
|
|
modified_at INTEGER,
|
|
source_device_id INTEGER,
|
|
note TEXT,
|
|
UNIQUE(original_hash)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_media_hash ON media(original_hash);
|
|
CREATE INDEX IF NOT EXISTS idx_media_device ON media(source_device_id);
|
|
|
|
-- Encoded: one row per encoded variant (phone-optimized copy)
|
|
CREATE TABLE IF NOT EXISTS encoded (
|
|
id INTEGER PRIMARY KEY,
|
|
media_id INTEGER NOT NULL,
|
|
encoded_hash TEXT NOT NULL,
|
|
encoded_path TEXT NOT NULL,
|
|
codec TEXT,
|
|
preset TEXT,
|
|
crf INTEGER,
|
|
size INTEGER,
|
|
width INTEGER,
|
|
height INTEGER,
|
|
duration REAL,
|
|
created_at INTEGER,
|
|
encoder_version TEXT,
|
|
notes TEXT,
|
|
FOREIGN KEY(media_id) REFERENCES media(id) ON DELETE CASCADE,
|
|
UNIQUE(media_id, encoded_hash)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_encoded_media ON encoded(media_id);
|
|
CREATE INDEX IF NOT EXISTS idx_encoded_hash ON encoded(encoded_hash);
|
|
|
|
-- Sync jobs: track sync runs and basic results
|
|
CREATE TABLE IF NOT EXISTS sync_job (
|
|
id INTEGER PRIMARY KEY,
|
|
started_at INTEGER,
|
|
finished_at INTEGER,
|
|
initiated_by TEXT,
|
|
phone_device_id INTEGER,
|
|
files_found INTEGER,
|
|
files_backed_up INTEGER,
|
|
files_encoded INTEGER,
|
|
errors INTEGER,
|
|
notes TEXT,
|
|
FOREIGN KEY(phone_device_id) REFERENCES device(id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_sync_started ON sync_job(started_at);
|
|
|
|
-- Duplicate groups: optional helper for UI to store grouped duplicates
|
|
CREATE TABLE IF NOT EXISTS duplicate_group (
|
|
id INTEGER PRIMARY KEY,
|
|
media_hash TEXT NOT NULL,
|
|
created_at INTEGER
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_dupgroup_hash ON duplicate_group(media_hash);
|
|
|
|
-- Log: operation log for auditing and debugging
|
|
CREATE TABLE IF NOT EXISTS log (
|
|
id INTEGER PRIMARY KEY,
|
|
ts INTEGER,
|
|
level TEXT,
|
|
source TEXT,
|
|
message TEXT
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_log_ts ON log(ts);
|
|
";
|
|
|
|
command.ExecuteNonQuery();
|
|
}
|
|
}
|
|
}
|
|
} |