Merge pull request #3049 from pi-hole/tweak/unique_group_name

Group table enhancements

2 files changed, 43 insertions, 0 deletions

diff --git a/advanced/Scripts/database_migration/gravity-db.sh b/advanced/Scripts/database_migration/gravity-db.sh
index 28054643..bc650d3f 100644
--- a/advanced/Scripts/database_migration/gravity-db.sh
+++ b/advanced/Scripts/database_migration/gravity-db.sh
@@ -72,4 +72,12 @@ upgrade_gravityDB(){
 		sqlite3 "${database}" < "${scriptPath}/6_to_7.sql"
 		version=7
 	fi
+	if [[ "$version" == "7" ]]; then
+		# This migration script recreated the group table
+		# to ensure uniqueness on the group name
+		# We also add date_added and date_modified columns
+		echo -e "  ${INFO} Upgrading gravity database from version 7 to 8"
+		sqlite3 "${database}" < "${scriptPath}/7_to_8.sql"
+		version=8
+	fi
 }
diff --git a/advanced/Scripts/database_migration/gravity/7_to_8.sql b/advanced/Scripts/database_migration/gravity/7_to_8.sql
new file mode 100644
index 00000000..412475da
--- /dev/null
+++ b/advanced/Scripts/database_migration/gravity/7_to_8.sql
@@ -0,0 +1,35 @@
+.timeout 30000
+
+PRAGMA FOREIGN_KEYS=OFF;
+
+BEGIN TRANSACTION;
+
+ALTER TABLE "group" RENAME TO "group__";
+
+CREATE TABLE "group"
+(
+	id INTEGER PRIMARY KEY AUTOINCREMENT,
+	enabled BOOLEAN NOT NULL DEFAULT 1,
+	name TEXT UNIQUE NOT NULL,
+	date_added INTEGER NOT NULL DEFAULT (cast(strftime('%s', 'now') as int)),
+	date_modified INTEGER NOT NULL DEFAULT (cast(strftime('%s', 'now') as int)),
+	description TEXT
+);
+
+CREATE TRIGGER tr_group_update AFTER UPDATE ON "group"
+    BEGIN
+      UPDATE "group" SET date_modified = (cast(strftime('%s', 'now') as int)) WHERE id = NEW.id;
+    END;
+
+CREATE TRIGGER tr_group_zero AFTER DELETE ON "group"
+    BEGIN
+      INSERT OR IGNORE INTO "group" (id,enabled,name) VALUES (0,1,'Unassociated');
+    END;
+
+INSERT OR IGNORE INTO "group" (id,enabled,name,description) SELECT id,enabled,name,description FROM "group__";
+
+DROP TABLE "group__";
+
+UPDATE info SET value = 8 WHERE property = 'version';
+
+COMMIT;