360 lines
12 KiB
C#
360 lines
12 KiB
C#
namespace MTG_CollectionVerwaltung.Data
|
|
{
|
|
using Microsoft.EntityFrameworkCore;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.ComponentModel.DataAnnotations;
|
|
|
|
namespace MTGCollection
|
|
{
|
|
public class MTGContext : DbContext
|
|
{
|
|
public MTGContext(DbContextOptions<MTGContext> options)
|
|
: base(options)
|
|
{
|
|
var conn = Database.GetDbConnection();
|
|
conn.Open();
|
|
|
|
// SQLite Funktion registrieren
|
|
var sqliteConn = (Microsoft.Data.Sqlite.SqliteConnection)conn;
|
|
sqliteConn.CreateFunction<string, string, int, int>("levenshtein", LevenshteinMinWordDistanceByLength);
|
|
}
|
|
|
|
public DbSet<Card> Cards { get; set; }
|
|
public DbSet<CardPrinting> CardPrintings { get; set; }
|
|
public DbSet<CardType> CardTypes { get; set; }
|
|
public DbSet<MTGType> Types { get; set; }
|
|
public DbSet<Set> Sets { get; set; }
|
|
public DbSet<CardCollection> CardCollections { get; set; }
|
|
public DbSet<Supertype> Supertypes { get; set; }
|
|
public DbSet<CardPrintingFts> CardPrintingsFts { get; set; }
|
|
|
|
protected override void OnModelCreating(ModelBuilder modelBuilder)
|
|
{
|
|
// CardPrinting: zusammengesetzter Schlüssel
|
|
modelBuilder.Entity<CardPrinting>()
|
|
.HasIndex(cp => new { cp.CardId, cp.Lang })
|
|
.IsUnique(); // falls du weiterhin Eindeutigkeit auf CardId+Lang willst
|
|
|
|
modelBuilder.Entity<CardPrinting>()
|
|
.HasOne(cp => cp.Card)
|
|
.WithMany(c => c.Printings)
|
|
.HasForeignKey(cp => cp.CardId);
|
|
|
|
// CardType: zusammengesetzter Schlüssel
|
|
modelBuilder.Entity<CardType>()
|
|
.HasKey(ct => new { ct.CardId, ct.TypeId });
|
|
|
|
modelBuilder.Entity<CardType>()
|
|
.HasOne(ct => ct.Card)
|
|
.WithMany(c => c.CardTypes)
|
|
.HasForeignKey(ct => ct.CardId);
|
|
|
|
modelBuilder.Entity<CardType>()
|
|
.HasOne(ct => ct.Type)
|
|
.WithMany()
|
|
.HasForeignKey(ct => ct.TypeId);
|
|
|
|
// CardCollection: zusammengesetzter Schlüssel
|
|
modelBuilder.Entity<CardCollection>()
|
|
.HasKey(cc => new { cc.CardId, cc.UserId });
|
|
|
|
modelBuilder.Entity<CardCollection>()
|
|
.HasOne(cc => cc.Card)
|
|
.WithMany(c => c.Collections)
|
|
.HasForeignKey(cc => cc.CardId);
|
|
|
|
// Card -> Set
|
|
modelBuilder.Entity<Card>()
|
|
.HasOne(c => c.Set)
|
|
.WithMany(s => s.Cards)
|
|
.HasForeignKey(c => c.SetCode)
|
|
.HasPrincipalKey(s => s.SetCode);
|
|
|
|
// Card -> Supertype
|
|
modelBuilder.Entity<Card>()
|
|
.HasOne(c => c.SuperType)
|
|
.WithMany()
|
|
.HasForeignKey(c => c.SuperTypeId);
|
|
|
|
// Indizes
|
|
modelBuilder.Entity<Card>()
|
|
.HasIndex(c => c.CardName);
|
|
|
|
modelBuilder.Entity<CardCollection>()
|
|
.HasIndex(cc => cc.UserId);
|
|
|
|
modelBuilder.Entity<Card>()
|
|
.HasIndex(c => new { c.CollectorNumber, c.SetCode });
|
|
|
|
modelBuilder.Entity<CardPrinting>()
|
|
.HasIndex(c => c.Name);
|
|
|
|
modelBuilder.Entity<CardPrinting>()
|
|
.HasIndex(c => c.OracleText);
|
|
|
|
// Default Values
|
|
modelBuilder.Entity<Card>()
|
|
.Property(c => c.Foil)
|
|
.HasDefaultValue(false);
|
|
|
|
modelBuilder.Entity<Card>()
|
|
.Property(c => c.NonFoil)
|
|
.HasDefaultValue(false);
|
|
|
|
//Remove CaseSensitivity
|
|
modelBuilder.Entity<CardPrinting>()
|
|
.Property(c => c.Name)
|
|
.UseCollation("NOCASE");
|
|
|
|
modelBuilder.Entity<CardPrinting>()
|
|
.Property(c => c.OracleText)
|
|
.UseCollation("NOCASE");
|
|
|
|
modelBuilder.Entity<MTGType>()
|
|
.Property(c => c.TypeEn)
|
|
.UseCollation("NOCASE");
|
|
|
|
modelBuilder.Entity<MTGType>()
|
|
.Property(c => c.TypeDe)
|
|
.UseCollation("NOCASE");
|
|
|
|
modelBuilder.Entity<Supertype>()
|
|
.Property(c => c.TypeEn)
|
|
.UseCollation("NOCASE");
|
|
|
|
modelBuilder.Entity<Supertype>()
|
|
.Property(c => c.TypeDe)
|
|
.UseCollation("NOCASE");
|
|
|
|
modelBuilder.Entity<CardPrintingFts>(entity =>
|
|
{
|
|
entity.HasNoKey(); // Keyless, weil FTS rowid nicht als PK nutzbar
|
|
entity.ToView(null); // keine Migration/View erzeugen
|
|
entity.Property(e => e.Name);
|
|
entity.Property(e => e.OracleText);
|
|
entity.Property(e => e.FlavorText);
|
|
});
|
|
}
|
|
|
|
public static int LevenshteinMinWordDistanceByLength(string s, string t, int tolerance = 2)
|
|
{
|
|
if (string.IsNullOrEmpty(s)) return t?.Length ?? 0;
|
|
if (string.IsNullOrEmpty(t)) return s.Length;
|
|
|
|
if (s.Contains(t)) return 0;
|
|
|
|
var wordsS = s.Split(new[] { ' ', '-', '_', '`', '\'', ',' }, StringSplitOptions.RemoveEmptyEntries);
|
|
var wordsT = t.Split(new[] { ' ', '-', '_', '`', '\'', ',' }, StringSplitOptions.RemoveEmptyEntries);
|
|
|
|
int minDistance = int.MaxValue;
|
|
|
|
foreach (var ws in wordsS)
|
|
{
|
|
// nur Wörter in sinnvollem Längenbereich vergleichen
|
|
if (Math.Abs(ws.Length - t.Length) <= tolerance)
|
|
{
|
|
foreach (var wt in wordsT)
|
|
{
|
|
if (Math.Abs(ws.Length - wt.Length) <= tolerance)
|
|
{
|
|
int dist = Levenshtein(ws, wt);
|
|
if (dist < minDistance)
|
|
minDistance = dist;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
return minDistance;
|
|
}
|
|
|
|
|
|
public static int Levenshtein(string s, string t)
|
|
{
|
|
if (string.IsNullOrEmpty(s)) return t?.Length ?? 0;
|
|
if (string.IsNullOrEmpty(t)) return s.Length;
|
|
|
|
int[,] d = new int[s.Length + 1, t.Length + 1];
|
|
|
|
for (int i = 0; i <= s.Length; i++) d[i, 0] = i;
|
|
for (int j = 0; j <= t.Length; j++) d[0, j] = j;
|
|
|
|
for (int i = 1; i <= s.Length; i++)
|
|
{
|
|
for (int j = 1; j <= t.Length; j++)
|
|
{
|
|
int cost = (s[i - 1] == t[j - 1]) ? 0 : 1;
|
|
d[i, j] = Math.Min(
|
|
Math.Min(d[i - 1, j] + 1, d[i, j - 1] + 1),
|
|
d[i - 1, j - 1] + cost);
|
|
}
|
|
}
|
|
return d[s.Length, t.Length];
|
|
}
|
|
|
|
public void EnsureFtsInitialized()
|
|
{
|
|
using var conn = (Microsoft.Data.Sqlite.SqliteConnection)Database.GetDbConnection();
|
|
conn.Open();
|
|
|
|
// Prüfen, ob FTS-Tabelle schon existiert
|
|
using var checkCmd = conn.CreateCommand();
|
|
checkCmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='CardPrintingsFTS';";
|
|
var exists = checkCmd.ExecuteScalar() != null;
|
|
|
|
if (!exists)
|
|
{
|
|
// FTS-Tabelle erstellen
|
|
using var createCmd = conn.CreateCommand();
|
|
createCmd.CommandText = @"
|
|
CREATE VIRTUAL TABLE CardPrintingsFTS
|
|
USING fts5(
|
|
Name,
|
|
OracleText,
|
|
FlavorText,
|
|
content='CardPrintings',
|
|
content_rowid='Id',
|
|
tokenize='trigram'
|
|
);
|
|
";
|
|
createCmd.ExecuteNonQuery();
|
|
|
|
}
|
|
}
|
|
|
|
|
|
public void RebuildCardPrintingsFts()
|
|
{
|
|
using var conn = (Microsoft.Data.Sqlite.SqliteConnection)Database.GetDbConnection();
|
|
conn.Open();
|
|
|
|
using var cmd = conn.CreateCommand();
|
|
cmd.CommandText = @"INSERT INTO CardPrintingsFTS(CardPrintingsFTS) VALUES('rebuild');";
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
public class Card
|
|
{
|
|
[Key]
|
|
public string Id { get; set; } = string.Empty;
|
|
|
|
[Required, MaxLength(10)]
|
|
public string SetCode { get; set; } = string.Empty;
|
|
|
|
[Required, MaxLength(100)]
|
|
public string CardName { get; set; } = string.Empty;
|
|
|
|
public string? CollectorNumber { get; set; }
|
|
public string? ManaCost { get; set; }
|
|
public bool Foil { get; set; }
|
|
public bool NonFoil { get; set; }
|
|
public string? Rarity { get; set; }
|
|
public string? Power { get; set; }
|
|
public string? Toughness { get; set; }
|
|
public int SuperTypeId { get; set; }
|
|
public int Cmc { get; set; }
|
|
public string? OtherFace { get; set; }
|
|
|
|
public Set Set { get; set; }
|
|
public Supertype SuperType { get; set; }
|
|
public ICollection<CardPrinting> Printings { get; set; } = new List<CardPrinting>();
|
|
public ICollection<CardType> CardTypes { get; set; } = new List<CardType>();
|
|
public ICollection<CardCollection> Collections { get; set; } = new List<CardCollection>();
|
|
}
|
|
|
|
public class CardPrinting
|
|
{
|
|
[Key]
|
|
public int Id { get; set; }
|
|
|
|
[Required, MaxLength(40)]
|
|
public string CardId { get; set; }
|
|
|
|
[Required, MaxLength(5)]
|
|
public string Lang { get; set; }
|
|
|
|
[Required, MaxLength(100)]
|
|
public string Name { get; set; }
|
|
public string? OracleText { get; set; }
|
|
public string? FlavorText { get; set; }
|
|
public string? ImageUriSmall { get; set; }
|
|
public string? ImageUriNormal { get; set; }
|
|
public string? ImageUriLarge { get; set; }
|
|
|
|
public Card Card { get; set; }
|
|
|
|
}
|
|
|
|
[Keyless]
|
|
public class CardPrintingFts
|
|
{
|
|
public string Name { get; set; }
|
|
public string? OracleText { get; set; }
|
|
public string? FlavorText { get; set; }
|
|
}
|
|
|
|
public class CardType
|
|
{
|
|
[Required]
|
|
public string CardId { get; set; }
|
|
|
|
[Required]
|
|
public int TypeId { get; set; }
|
|
|
|
public Card Card { get; set; }
|
|
public MTGType Type { get; set; }
|
|
}
|
|
|
|
public class MTGType
|
|
{
|
|
[Key]
|
|
public int TypeId { get; set; }
|
|
public string TypeEn { get; set; } = "";
|
|
public string TypeDe { get; set; } = "";
|
|
}
|
|
|
|
public class Supertype
|
|
{
|
|
[Key]
|
|
public int TypeId { get; set; }
|
|
public string TypeEn { get; set; } = "";
|
|
public string TypeDe { get; set; } = "";
|
|
}
|
|
|
|
public class Set
|
|
{
|
|
[Key, MaxLength(10)]
|
|
public string SetCode { get; set; }
|
|
|
|
[Required, MaxLength(50)]
|
|
public string SetName { get; set; }
|
|
|
|
public string SetIcon { get; set; }
|
|
public string SetType { get; set; }
|
|
public int CardCount { get; set; }
|
|
public DateTime ReleaseDate { get; set; }
|
|
|
|
public ICollection<Card> Cards { get; set; } = new List<Card>();
|
|
}
|
|
|
|
public class CardCollection
|
|
{
|
|
[Required]
|
|
public string CardId { get; set; }
|
|
|
|
[Required]
|
|
public string UserId { get; set; }
|
|
|
|
public int Count { get; set; }
|
|
|
|
public Card Card { get; set; }
|
|
}
|
|
}
|
|
}
|