hostbbq-monitoring/server/src/migrations/202211190031_cascade_delete.sql

ALTER TABLE HealthCheckConfig RENAME TO HealthCheckConfig_OLD;

CREATE TABLE HealthCheckConfig(
  ID INTEGER PRIMARY KEY AUTOINCREMENT,
  ServerID INTEGER NOT NULL,
  Type TEXT NOT NULL,
  Title TEXT NOT NULL,
  CONSTRAINT FK_HealthCheckConfig_Server
    FOREIGN KEY(ServerID)
    REFERENCES Server(ID)
    ON DELETE CASCADE,
  UNIQUE(ServerID, Type, Title)
);

INSERT INTO HealthCheckConfig SELECT * FROM HealthCheckConfig_OLD;
DROP TABLE HealthCheckConfig_OLD;

ALTER TABLE HealthCheckParams RENAME TO HealthCheckParams_OLD;
CREATE TABLE HealthCheckParams(
  ID INTEGER PRIMARY KEY AUTOINCREMENT,
  ConfigID INTEGER NOT NULL,
  Type TEXT NOT NULL,
  Key TEXT NOT NULL,
  Value TEXT NOT NULL,
  CONSTRAINT FK_HealthCheckParams_HealthCheckConfig 
    FOREIGN KEY(ConfigID) 
    REFERENCES HealthCheckConfig(ID)
    ON DELETE CASCADE
);
INSERT INTO HealthCheckParams SELECT * FROM HealthCheckParams_OLD;
DROP TABLE HealthCheckParams_OLD;

ALTER TABLE HealthCheckDataEntry RENAME TO HealthCheckDataEntry_OLD;
CREATE TABLE HealthCheckDataEntry(
  ID INTEGER PRIMARY KEY AUTOINCREMENT,
  ConfigID INTEGER NOT NULL,
  Timestamp INTEGER NOT NULL,
  Status INTEGER NOT NULL,
  Message TEXT,
  CONSTRAINT FK_HealthCheckDataEntry_HealthCheckConfig
    FOREIGN KEY(ConfigID) 
    REFERENCES HealthCheckConfig(ID)
    ON DELETE CASCADE,
  UNIQUE(ConfigID, Timestamp)
);
INSERT INTO HealthCheckDataEntry SELECT * FROM HealthCheckDataEntry_OLD;
DROP TABLE HealthCheckDataEntry_OLD;

ALTER TABLE ServerConfig RENAME TO ServerConfig_OLD;
CREATE TABLE ServerConfig (
  ID INTEGER PRIMARY KEY AUTOINCREMENT,
  ServerID INTEGER NOT NULL,
  Key TEXT NOT NULL,
  Value TEXT NOT NULL,
  CONSTRAINT FK_ServerConfig_Server
    FOREIGN KEY(ServerID) 
    REFERENCES Server(ID)
    ON DELETE CASCADE,
  UNIQUE(ServerID, Key)
);
INSERT INTO ServerConfig SELECT * FROM ServerConfig_OLD;
DROP TABLE ServerConfig_OLD;

ALTER TABLE ServerDataEntry RENAME TO ServerDataEntry_OLD;
CREATE TABLE ServerDataEntry (
  ID INTEGER PRIMARY KEY AUTOINCREMENT,
  ServerID INTEGER NOT NULL,
  Timestamp INTEGER NOT NULL,
  CONSTRAINT FK_ServerDataEntry_Server
    FOREIGN KEY(ServerID)
    REFERENCES Server(ID)
    ON DELETE CASCADE,
  UNIQUE(ServerID, Timestamp)
);
INSERT INTO ServerDataEntry SELECT * FROM ServerDataEntry_OLD;
DROP TABLE ServerDataEntry_OLD;

ALTER TABLE ServerDataValue RENAME TO ServerDataValue_OLD;
CREATE TABLE ServerDataValue (
  ID INTEGER PRIMARY KEY AUTOINCREMENT,
  EntryID INTEGER NOT NULL,
  Type Text NOT NULL,
  Key TEXT NOT NULL,
  Value REAL NOT NULL,
  CONSTRAINT FK_ServerDataValue_ServerDataEntry
    FOREIGN KEY(EntryID)
    REFERENCES ServerDataEntry(ID)
    ON DELETE CASCADE,
  UNIQUE(EntryID, Type, Key)
);
INSERT INTO ServerDataValue SELECT * FROM ServerDataValue_OLD;
DROP TABLE ServerDataValue_OLD;