From 878327ccd0de1794ca5d19f82ef53687b7e19160 Mon Sep 17 00:00:00 2001
From: Vitaly Takmazov
Date: Sat, 5 Oct 2024 09:24:51 +0300
Subject: db: Add `created_at` field to `emails` table

---
 src/main/resources/db/migration/V1.50__email_created_at.sql | 1 +
 src/main/resources/schema-h2.sql                            | 3 ++-
 src/main/resources/schema-mysql.sql                         | 1 +
 src/main/resources/schema-sqlite.sql                        | 1 +
 src/main/resources/schema-sqlserver.sql                     | 1 +
 5 files changed, 6 insertions(+), 1 deletion(-)
 create mode 100644 src/main/resources/db/migration/V1.50__email_created_at.sql

(limited to 'src/main/resources')

diff --git a/src/main/resources/db/migration/V1.50__email_created_at.sql b/src/main/resources/db/migration/V1.50__email_created_at.sql
new file mode 100644
index 00000000..aa5b3f4b
--- /dev/null
+++ b/src/main/resources/db/migration/V1.50__email_created_at.sql
@@ -0,0 +1 @@
+ALTER TABLE emails ADD COLUMN created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
diff --git a/src/main/resources/schema-h2.sql b/src/main/resources/schema-h2.sql
index 5fd7fff8..dc705727 100644
--- a/src/main/resources/schema-h2.sql
+++ b/src/main/resources/schema-h2.sql
@@ -233,7 +233,8 @@ ALTER TABLE "PUBLIC"."USERSINFO" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_BB6" PRIMAR
 CREATE MEMORY TABLE "PUBLIC"."EMAILS"(
     "USER_ID" INTEGER NOT NULL,
     "EMAIL" VARCHAR_IGNORECASE(255) NOT NULL,
-    "SUBSCR_HOUR" TINYINT DEFAULT NULL
+    "SUBSCR_HOUR" TINYINT DEFAULT NULL,
+    "CREATED_AT" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
 );
 ALTER TABLE "PUBLIC"."EMAILS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_7A" PRIMARY KEY("EMAIL");
 CREATE MEMORY TABLE "PUBLIC"."LOGINS"(
diff --git a/src/main/resources/schema-mysql.sql b/src/main/resources/schema-mysql.sql
index 0e035d5e..813b9e3d 100644
--- a/src/main/resources/schema-mysql.sql
+++ b/src/main/resources/schema-mysql.sql
@@ -72,6 +72,7 @@ CREATE TABLE `emails` (
   `user_id` int(10) unsigned NOT NULL,
   `email` char(128) NOT NULL,
   `subscr_hour` tinyint(4) DEFAULT NULL,
+  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
   KEY `email` (`email`) USING HASH
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
diff --git a/src/main/resources/schema-sqlite.sql b/src/main/resources/schema-sqlite.sql
index 74ba28b6..54160c80 100644
--- a/src/main/resources/schema-sqlite.sql
+++ b/src/main/resources/schema-sqlite.sql
@@ -32,6 +32,7 @@ CREATE TABLE emails (
     user_id INTEGER NOT NULL,
     email character varying(128) NOT NULL,
     subscr_hour smallint,
+    created_at DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
 );
 CREATE TABLE facebook (
diff --git a/src/main/resources/schema-sqlserver.sql b/src/main/resources/schema-sqlserver.sql
index 947e5403..9ff21ed2 100644
--- a/src/main/resources/schema-sqlserver.sql
+++ b/src/main/resources/schema-sqlserver.sql
@@ -56,6 +56,7 @@ CREATE TABLE emails (
     user_id bigint NOT NULL,
     email character varying(128) NOT NULL,
     subscr_hour smallint,
+    created_at datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
 );
 CREATE TABLE facebook (
-- 
cgit v1.2.3