Also add date_added and date_modified fields to group table.

Signed-off-by: DL6ER <dl6er@dl6er.de>

2 files changed, 10 insertions, 2 deletions

diff --git a/advanced/Scripts/database_migration/gravity-db.sh b/advanced/Scripts/database_migration/gravity-db.sh
index 6dc88bf3..bc650d3f 100644
--- a/advanced/Scripts/database_migration/gravity-db.sh
+++ b/advanced/Scripts/database_migration/gravity-db.sh
@@ -75,8 +75,9 @@ upgrade_gravityDB(){
 	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=7
+		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
index 8f79bde8..0e58408e 100644
--- a/advanced/Scripts/database_migration/gravity/7_to_8.sql
+++ b/advanced/Scripts/database_migration/gravity/7_to_8.sql
@@ -11,10 +11,17 @@ 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
 );
 
-INSERT OR IGNORE INTO "group" SELECT * FROM "group__";
+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;
+
+INSERT OR IGNORE INTO "group" (id,enabled,name,description) SELECT id,enabled,name,description FROM "group__";
 
 DROP TABLE "group__";