SQLite.Net Extensions с Many-To-One не работает должным образом

Question

Моя таблица будет содержать много повторяющихся строк, таких как домены. Для минимизации размера базы данных я хочу сохранить только уникальные домены в другой таблице и использовать идентификатор домена в основной таблице.

Все время я делал это вручную, но недавно я обнаружил, что SQLite может делать это автоматически.

Сейчас я пытаюсь использовать отношения "многие к одному" с ключом "ИНОСТРАННЫЙ", но безуспешно. Может я что то не так делаю.

Пример кода

Таблицы классов:

public class Domains
{
    public Domains() { }
    public Domains(string domain) { this.Domain = domain; }

    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    [Unique, MaxLength(64)]
    public string Domain { get; set; }
}

public class Statistics
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public int Timestamp { get; set; }

    [ForeignKey(typeof(Domains))]
    public int DomainId { get; set; }

    public int Status { get; set; }

    [ManyToOne(CascadeOperations = CascadeOperation.All)]
    public Domains Domain { get; set; }
}

Основной код:

static void Main(string[] args)
{
    var dbFile = "stats.db";
    var domains = new[] { "stackru.com", "superuser.com", "serverfault.com", "google.com", "microsoft.com" };
    var statList = new List<Statistics>();

    var sqlBase = new SQLiteConnection(dbFile);
    sqlBase.Execute("PRAGMA foreign_keys = ON");
    sqlBase.CreateTable<Domains>();
    sqlBase.CreateTable<Statistics>();

    Console.WriteLine(SQLite3.LibVersionNumber());

    var runTimestamp = (int)(DateTime.UtcNow - new DateTime(1970, 1, 1)).TotalSeconds;
    foreach (var domain in domains)
    {
        HttpWebResponse resp = null;
        var status = -1;
        try
        {
            resp = (HttpWebResponse)WebRequest.Create("http://" + domain).GetResponse();
        }
        catch { };
        status = (int)resp.StatusCode;

        var stat = new Statistics();
        stat.Domain = new Domains(domain);
        stat.Status = status;
        stat.Timestamp = runTimestamp;

        statList.Add(stat);
    }

    sqlBase.InsertOrIgnoreAllWithChildren(statList); // Modification "INSERT" with "OR IGNORE"

    Console.WriteLine(@"Table ""Domains""");
    foreach (var table in sqlBase.Table<Domains>())
    {
        Console.WriteLine("Id: {0}\tDomain: {1}", table.Id, table.Domain);
    }
    Console.WriteLine();

    Console.WriteLine(@"Table ""Statistics""");
    foreach (var table in sqlBase.Table<Statistics>())
    {
        Console.WriteLine("Id: {0}\tDomain Id: {1}", table.Id, table.DomainId);
    }

    Console.WriteLine();
    Console.WriteLine("Press any key to exit...");
    Console.ReadKey();
}

После первого запуска это выглядит хорошо.

Но после второго запуска, когда домены повторяются - расширения sqlite вставляют неверный идентификатор домена

Где я ошибаюсь?

Answer 1

Моя ошибка, функция "Внешний ключ" не работает, как я думал. Поэтому я использую представление с объединением двух таблиц и триггеров для редактирования.

И это работает так, как мне нужно.

SQLite.Net и SQLite.Net.Extensions не поддерживают представления и триггеры, поэтому я использую только SQLite.Net и Execute-функцию для создания представления и триггеров.

CREATE VIEW IF NOT EXISTS 'StatisticsView' AS 
  SELECT Stat.Id, Stat.Timestamp, Dom.Domain, Stat.Status FROM Statistics AS Stat 
    INNER JOIN Domains Dom ON Stat.DomainId = Dom.Id

CREATE TRIGGER IF NOT EXISTS 'StatisticsViewInsert'
INSTEAD OF INSERT ON 'StatisticsView'
BEGIN
  INSERT OR IGNORE INTO Domains(Domain) VALUES(NEW.Domain);
  INSERT INTO Statistics(Timestamp, Status, DomainId) VALUES (NEW.Timestamp, NEW.Status, (SELECT Id FROM Domains WHERE Domain = NEW.Domain));
END

CREATE TRIGGER IF NOT EXISTS 'StatisticsViewUpdate' 
INSTEAD OF UPDATE ON 'StatisticsView' 
BEGIN
  INSERT OR IGNORE INTO Domains(Domain) VALUES(NEW.Domain);
  UPDATE Statistics SET Status = NEW.Status, Timestamp = NEW.Timestamp, DomainId = (SELECT Id FROM Domains WHERE Domain = NEW.Domain) WHERE Id = OLD.Id;
END

CREATE TRIGGER IF NOT EXISTS 'StatisticsViewDelete' 
INSTEAD OF DELETE ON 'StatisticsView' 
BEGIN
  DELETE FROM Domains WHERE (Domain = OLD.Domain AND (SELECT COUNT(Id) FROM Statistics WHERE DomainId = (SELECT Id FROM Domains WHERE Domain = OLD.Domain)) < 2);
  DELETE FROM Statistics WHERE Id=OLD.Id;
END

Таблицы классов:

public class Domains
{
    public Domains() { }
    public Domains(string domain) { this.Domain = domain; }

    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    [Unique, MaxLength(64)]
    public string Domain { get; set; }
}

public class Statistics
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public int Timestamp { get; set; }
    public int DomainId { get; set; }
    public int Status { get; set; }
}

// Virtual Table
public class StatisticsView
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public int Timestamp { get; set; }
    public string Domain { get; set; }
    public int Status { get; set; }
}       

Основной код:

static void Main(string[] args)
{
    var dbFile = "stats.db";
    var domains = new[] { "stackru.com", "superuser.com", "serverfault.com", "google.com", "microsoft.com" };
    var statList = new List<StatisticsView>();

    var sqlBase = new SQLiteConnection(dbFile);
    sqlBase.CreateTable<Domains>();
    sqlBase.CreateTable<Statistics>();
    sqlBase.Execute("CREATE VIEW IF NOT EXISTS 'StatisticsView' AS SELECT Stat.Id, Stat.Timestamp, Dom.Domain, Stat.Status FROM Statistics AS Stat INNER JOIN Domains Dom ON Stat.DomainId=Dom.Id;");
    sqlBase.Execute("CREATE TRIGGER IF NOT EXISTS 'StatisticsViewInsert' INSTEAD OF INSERT ON 'StatisticsView' BEGIN INSERT OR IGNORE INTO Domains(Domain) VALUES(NEW.Domain); INSERT INTO Statistics(Timestamp, Status, DomainId) VALUES (NEW.Timestamp, NEW.Status, (SELECT Id FROM Domains WHERE Domain=NEW.Domain)); END");
    sqlBase.Execute("CREATE TRIGGER IF NOT EXISTS 'StatisticsViewUpdate' INSTEAD OF UPDATE ON 'StatisticsView' BEGIN INSERT OR IGNORE INTO Domains(Domain) VALUES(NEW.Domain); UPDATE Statistics SET Status=NEW.Status, Timestamp=NEW.Timestamp, DomainId=(SELECT Id FROM Domains WHERE Domain=NEW.Domain) WHERE Id=OLD.Id; END");
    sqlBase.Execute("CREATE TRIGGER IF NOT EXISTS 'StatisticsViewDelete' INSTEAD OF DELETE ON 'StatisticsView' BEGIN DELETE FROM Domains WHERE (Domain = OLD.Domain AND (SELECT COUNT(Id) FROM Statistics WHERE DomainId=(SELECT Id FROM Domains WHERE Domain=OLD.Domain)) < 2); DELETE FROM Statistics WHERE Id=OLD.Id; END");

    Console.WriteLine(SQLite3.LibVersionNumber());

    var runTimestamp = (int)(DateTime.UtcNow - new DateTime(1970, 1, 1)).TotalSeconds;
    foreach (var domain in domains)
    {
        HttpWebResponse resp = null;
        var status = -1;
        try
        {
            resp = (HttpWebResponse)WebRequest.Create("http://" + domain).GetResponse();
        }
        catch { };
        status = (int)resp.StatusCode;

        var stat = new StatisticsView();
        stat.Domain = domain;
        stat.Status = status;
        stat.Timestamp = runTimestamp;

        statList.Add(stat);
    }

    sqlBase.InsertAll(statList);

    Console.WriteLine(@"Table ""Domains""");
    foreach (var table in sqlBase.Table<Domains>())
    {
        Console.WriteLine("Id: {0}\tDomain: {1}", table.Id, table.Domain);
    }
    Console.WriteLine();

    Console.WriteLine(@"Table ""Statistics""");
    foreach (var table in sqlBase.Table<Statistics>())
    {
        Console.WriteLine("Id: {0}\tDomain Id: {1}", table.Id, table.DomainId);
    }

    Console.WriteLine();
    Console.WriteLine("Press any key to exit...");
    Console.ReadKey();
}

После второго запуска:

Answer 2

Ты можешь использовать Domain как первичный (и внешний) ключ, так и ваш текущий код будет работать как положено:

public class Domains
{
    public Domains() { }
    public Domains(string domain) { this.Domain = domain; }

    [PrimaryKey, MaxLength(64)]
    public string Domain { get; set; }
}

public class Statistics
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public int Timestamp { get; set; }

    [ForeignKey(typeof(Domains))]
    public String DomainId { get; set; }

    public int Status { get; set; }

    [ManyToOne(CascadeOperations = CascadeOperation.All)]
    public Domains Domain { get; set; }
}

Answer 3

В своем коде вы создаете новый Domains сущности каждый раз, когда вы пытаетесь сохранить новую статистику.

Для SQLite-Net Extensions необходим первичный ключ ссылочного объекта, чтобы назначить внешний ключ. Кажется, что ваш InsertOrIgnoreAllWithChildren назначает 10 всем вашим Domains объекты, даже когда они не вставляются.

Что вам нужно сделать, это получить ваши текущие домены, чтобы получить правильный первичный ключ.

Попробуйте что-то вроде этого:

var dbFile = "stats.db";
var statList = new List<Statistics>();

var sqlBase = new SQLiteConnection(dbFile);
sqlBase.Execute("PRAGMA foreign_keys = ON");
sqlBase.CreateTable<Domains>();
sqlBase.CreateTable<Statistics>();

// Fetch existing domains from database
var domains = sqlBase.Table<Domains>().toList();

if (domains.isEmpty()) {
    // Insert domains into database if they don't exist
    var domainNames = new[] { "stackru.com", "superuser.com", "serverfault.com", "google.com", "microsoft.com" };
    domains = domainNames.Select(domainName => new Domain(domainName));
    sqlBase.InsertAll(domains);
}

var runTimestamp = (int)(DateTime.UtcNow - new DateTime(1970, 1, 1)).TotalSeconds;
foreach (var domain in domains)
{
    HttpWebResponse resp = null;
    var status = -1;
    try
    {
        resp = (HttpWebResponse)WebRequest.Create("http://" + domain.domain).GetResponse();
    }
    catch { };
    status = (int)resp.StatusCode;

    var stat = new Statistics();
    stat.Domain = domain; // Assign the existing domain object
    stat.Status = status;
    stat.Timestamp = runTimestamp;

    statList.Add(stat);
}

// Insert only Statistics (Domains already exist), and assign foreign keys
sqlBase.InsertAllWithChildren(statList);