SuperAppsRT/InitDb.sql at main · xr3ed/SuperAppsRT · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
    "MigrationId" TEXT NOT NULL CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY,
    "ProductVersion" TEXT NOT NULL
);

BEGIN TRANSACTION;
INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20250504124435_AddJenisIuranIdToIuran', '9.0.4');

ALTER TABLE "Iuran" ADD "JenisIuranId" INTEGER NULL;

CREATE INDEX "IX_Iuran_JenisIuranId" ON "Iuran" ("JenisIuranId");

CREATE TABLE "ef_temp_Iuran" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Iuran" PRIMARY KEY AUTOINCREMENT,
    "Bulan" INTEGER NOT NULL,
    "Catatan" TEXT NULL,
    "JenisIuranId" INTEGER NULL,
    "JumlahBayar" TEXT NOT NULL,
    "JumlahTagihan" TEXT NOT NULL,
    "KartuKeluargaId" INTEGER NOT NULL,
    "KonfigurasiIuranId" INTEGER NOT NULL,
    "MetodePembayaran" TEXT NULL,
    "ReferensiPembayaran" TEXT NULL,
    "StatusPembayaran" TEXT NOT NULL,
    "Tahun" INTEGER NOT NULL,
    "TanggalBayar" TEXT NULL,
    "TanggalDibuat" TEXT NOT NULL,
    "TanggalDiubah" TEXT NULL,
    CONSTRAINT "FK_Iuran_JenisIuran_JenisIuranId" FOREIGN KEY ("JenisIuranId") REFERENCES "JenisIuran" ("ID") ON DELETE RESTRICT,
    CONSTRAINT "FK_Iuran_KartuKeluarga_KartuKeluargaId" FOREIGN KEY ("KartuKeluargaId") REFERENCES "KartuKeluarga" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_Iuran_KonfigurasiIuran_KonfigurasiIuranId" FOREIGN KEY ("KonfigurasiIuranId") REFERENCES "KonfigurasiIuran" ("Id") ON DELETE RESTRICT
);

INSERT INTO "ef_temp_Iuran" ("Id", "Bulan", "Catatan", "JenisIuranId", "JumlahBayar", "JumlahTagihan", "KartuKeluargaId", "KonfigurasiIuranId", "MetodePembayaran", "ReferensiPembayaran", "StatusPembayaran", "Tahun", "TanggalBayar", "TanggalDibuat", "TanggalDiubah")
SELECT "Id", "Bulan", "Catatan", "JenisIuranId", "JumlahBayar", "JumlahTagihan", "KartuKeluargaId", "KonfigurasiIuranId", "MetodePembayaran", "ReferensiPembayaran", "StatusPembayaran", "Tahun", "TanggalBayar", "TanggalDibuat", "TanggalDiubah"
FROM "Iuran";

COMMIT;

PRAGMA foreign_keys = 0;

BEGIN TRANSACTION;
DROP TABLE "Iuran";

ALTER TABLE "ef_temp_Iuran" RENAME TO "Iuran";

COMMIT;

PRAGMA foreign_keys = 1;

BEGIN TRANSACTION;
CREATE INDEX "IX_Iuran_JenisIuranId" ON "Iuran" ("JenisIuranId");

CREATE UNIQUE INDEX "IX_Iuran_KartuKeluargaId_KonfigurasiIuranId_Bulan_Tahun" ON "Iuran" ("KartuKeluargaId", "KonfigurasiIuranId", "Bulan", "Tahun");

CREATE INDEX "IX_Iuran_KonfigurasiIuranId" ON "Iuran" ("KonfigurasiIuranId");

COMMIT;

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20250504150132_AddJenisIuranIdColumn', '9.0.4');

BEGIN TRANSACTION;
ALTER TABLE "JenisIuran" RENAME COLUMN "ID" TO "Id";

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20250504150610_RenameJenisIuranIDToId', '9.0.4');

CREATE TABLE "KKIuranPengecualian" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_KKIuranPengecualian" PRIMARY KEY AUTOINCREMENT,
    "KartuKeluargaId" INTEGER NOT NULL,
    "KonfigurasiIuranId" INTEGER NOT NULL,
    "IsDikecualikan" INTEGER NOT NULL DEFAULT 1,
    "AlasanPengecualian" TEXT NULL,
    "TanggalDibuat" TEXT NOT NULL,
    "TanggalDiubah" TEXT NULL,
    CONSTRAINT "FK_KKIuranPengecualian_KartuKeluarga_KartuKeluargaId" FOREIGN KEY ("KartuKeluargaId") REFERENCES "KartuKeluarga" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_KKIuranPengecualian_KonfigurasiIuran_KonfigurasiIuranId" FOREIGN KEY ("KonfigurasiIuranId") REFERENCES "KonfigurasiIuran" ("Id") ON DELETE CASCADE
);

CREATE UNIQUE INDEX "IX_KKIuranPengecualian_KartuKeluargaId_KonfigurasiIuranId" ON "KKIuranPengecualian" ("KartuKeluargaId", "KonfigurasiIuranId");

CREATE INDEX "IX_KKIuranPengecualian_KonfigurasiIuranId" ON "KKIuranPengecualian" ("KonfigurasiIuranId");

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20250504194430_AddKKIuranPengecualianTable', '9.0.4');

COMMIT;