Merge pull request 'version/0.1.4' (#9) from version/0.1.4 into main

Reviewed-on: #9

docker-compose.yml

@@ -1,6 +1,6 @@
 services:
   app:
-    image: chiko/77th_eventcalendarntfy:v0.1.3
+    image: chiko/77th_eventcalendarntfy:v0.1.4
     build: .
     volumes:
       - ./data/db:/opt/app/data/db

docker/Crontab

@@ -2,4 +2,3 @@ SHELL=/bin/bash
 MAILTO=""
 0 8 * * * root . /etc/cron-env.sh && /opt/app/run-task.sh --today >> /proc/1/fd/1 2>&1
 */15 * * * * root . /etc/cron-env.sh && /opt/app/run-task.sh >> /proc/1/fd/1 2>&1
-* * * * * root echo "cron test ran at $(date)" >> /proc/1/fd/1 2>&1

docker/docker-entrypoint.sh

@@ -7,6 +7,7 @@ chmod +x /etc/cron-env.sh
 # Write the Env Vars into a file for cron. happens during runtime of the container and not build.
 # List your environment variables here
 env_vars=(
+  NODE_ENV
   TZ
   DB_FILEPATH
   DB_FILENAME

package.json

@@ -1,5 +1,5 @@
 {
-  "version": "0.1.3",
+  "version": "0.1.4",
   "name": "77th_eventcalendarnotification",
   "module": "./src/app.ts",
   "type": "module",
@@ -16,7 +16,7 @@
     "typescript-eslint": "^8.46.2"
   },
   "scripts": {
-    "prod": "NODE_ENV=production bun run ./src/app.ts",
+    "start": "bun run ./src/app.ts",
     "dev": "NODE_ENV=development bun ./src/app.ts",
     "db:init": "bun run ./run/db_init.ts",
     "db:deleteall": "bun run ./run/db_event_deleteall.ts",

run/db_migration_v0.1.3.ts

@@ -0,0 +1,39 @@
+import db from "../src/sql";
+
+const run_migration = db.transaction(() => {
+  // SQL 1: Insert a new user
+  db.run(`DELETE FROM events
+    WHERE rowid NOT IN (
+      SELECT MIN(rowid)
+      FROM events
+      GROUP BY uid
+    );`);
+
+  // SQL 2: Update product stock
+  db.run(`CREATE TABLE events_new (
+    "event_uid" INTEGER PRIMARY KEY,
+    "uid" TEXT NOT NULL UNIQUE,
+    "title" TEXT NOT NULL,
+    "date_at" DATETIME NOT NULL,
+    "time_start" TEXT NOT NULL,
+    "time_end" TEXT NOT NULL,
+    "posted_by" TEXT NOT NULL,
+    "location" TEXT NOT NULL,
+    "event_type" TEXT NOT NULL,
+    "link" TEXT NOT NULL,
+    "description" TEXT NOT NULL,
+    "timezone" TEXT NOT NULL,
+    "notification" TEXT NOT NULL,
+    "deleteDate" INTEGER NULL
+  );`);
+
+  // SQL 3: Log the transaction
+  db.run(`INSERT INTO events_new (event_uid, uid, title, date_at, time_start, time_end, posted_by, location, event_type, link, description, timezone, notification, deleteDate)
+    SELECT event_uid, uid, title, date_at, time_start, time_end, posted_by, location, event_type, link, description, timezone, notification, deleteDate FROM events;
+  `);
+  db.run(`DROP TABLE events;
+    ALTER TABLE events_new RENAME TO events;`);
+});
+
+// Run the transaction
+run_migration();

sql/events/events_create_unique_index_uid.sql

@@ -0,0 +1 @@
+CREATE UNIQUE INDEX idx_events_uid ON events(uid);

sql/events/events_delete_duplicate_rows.sql

@@ -0,0 +1,6 @@
+DELETE FROM events
+WHERE rowid NOT IN (
+  SELECT MIN(rowid)
+  FROM events
+  GROUP BY uid
+);

sql/events/events_find_duplicate_uid.sql

@@ -0,0 +1,4 @@
+SELECT uid, COUNT(*) AS count
+FROM events
+GROUP BY uid
+HAVING COUNT(*) > 1;

sql/sql_migration_v0.1.3.sql

@@ -0,0 +1,29 @@
+DELETE FROM events
+WHERE rowid NOT IN (
+  SELECT MIN(rowid)
+  FROM events
+  GROUP BY uid
+);
+
+CREATE TABLE events_new (
+  "event_uid" INTEGER PRIMARY KEY,
+  "uid" TEXT NOT NULL UNIQUE,
+  "title" TEXT NOT NULL,
+  "date_at" DATETIME NOT NULL,
+  "time_start" TEXT NOT NULL,
+  "time_end" TEXT NOT NULL,
+  "posted_by" TEXT NOT NULL,
+  "location" TEXT NOT NULL,
+  "event_type" TEXT NOT NULL,
+  "link" TEXT NOT NULL,
+  "description" TEXT NOT NULL,
+  "timezone" TEXT NOT NULL,
+  "notification" TEXT NOT NULL,
+  "deleteDate" INTEGER NULL
+);
+
+INSERT INTO events_new (event_uid, uid, title, date_at, time_start, time_end, posted_by, location, event_type, link, description, timezone, notification, deleteDate)
+SELECT event_uid, uid, title, date_at, time_start, time_end, posted_by, location, event_type, link, description, timezone, notification, deleteDate FROM events;
+
+DROP TABLE events;
+ALTER TABLE events_new RENAME TO events;

src/component/event/events.ts

@@ -43,8 +43,8 @@ export class Event implements TEventEntity {
 
   static createTable (db: Database): void {
     const query = db.query(`CREATE TABLE IF NOT EXISTS "events" (
-      "event_uid" INTEGER NOT NULL,
-      "uid" TEXT NOT NULL,
+	    "event_uid" INTEGER PRIMARY KEY,
+      "uid" TEXT NOT NULL UNIQUE,
       "title" TEXT NOT NULL,
       "date_at" DATETIME NOT NULL,
       "time_start" TEXT NOT NULL,
@@ -56,10 +56,8 @@ export class Event implements TEventEntity {
       "description" TEXT NOT NULL,
       "timezone" TEXT NOT NULL,
       "notification" TEXT NOT NULL,
-      "deleteDate" INTEGER NULL,
-      PRIMARY KEY ("event_uid")
-    );
-    CREATE UNIQUE INDEX "sqlite_autoindex_events_1" ON "events" ("uid");`);
+      "deleteDate" INTEGER NULL
+    );`);
     query.run(); 
   }
 
@@ -220,7 +218,7 @@ export class Event implements TEventEntity {
     const body = [
       `Title: ${this.title}`,
       `Date: ${this.date_at}`,
-      `Time: ${this.get_time_start()}${ TimeDiff && TimeDiff == "00:00" ? ` (Optime ${TimeDiff})` : "" }`,
+      `Time: ${this.get_time_start()} (OP Time${ TimeDiff != "00:00" ? ` ${TimeDiff}` : "" })`,
       `Type: ${ TEventType[ this.event_type ] }`,
       `Location: ${this.location}`,
       `By: ${this.posted_by}`,

src/sql.ts

@@ -8,6 +8,8 @@ console.log(db_filepath);
 
 export const db = new Database(db_filepath);
 
+export default db;
+
 export function init () {
   Event.createTable(db);
 }