Fix bug where it wasn't possible to create more than one response record on SQLite and Postgres (#8664)

13 files changed, 16 insertions, 6 deletions

diff --git a/CHANGELOG.md b/CHANGELOG.md
index 0fa12ca8c..9317d115b 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -13,6 +13,7 @@
 - Fix bug where renamed options were removed from the config on installto.sh (update.sh) run (#8643)
 - Fix favicon rewrite rule in .htaccess (#8654)
 - Fix various PHP 8.2 warnings
+- Fix bug where it wasn't possible to create more than one response record on SQLite and Postgres (#8664)
 
 ## Release 1.6.0
 
diff --git a/SQL/mssql.initial.sql b/SQL/mssql.initial.sql
index f7a301761..b49a1eae7 100644
--- a/SQL/mssql.initial.sql
+++ b/SQL/mssql.initial.sql
@@ -486,6 +486,6 @@ CREATE TRIGGER [contact_delete_member] ON [dbo].[contacts]
     WHERE [contact_id] IN (SELECT [contact_id] FROM deleted)

 GO

 

-INSERT INTO [dbo].[system] ([name], [value]) VALUES ('roundcube-version', '2021100300')

+INSERT INTO [dbo].[system] ([name], [value]) VALUES ('roundcube-version', '2022081200')

 GO

 
\ No newline at end of file
diff --git a/SQL/mssql/2022081200.sql b/SQL/mssql/2022081200.sql
new file mode 100644
index 000000000..105e39efb
--- /dev/null
+++ b/SQL/mssql/2022081200.sql
@@ -0,0 +1 @@
+-- SQLite/Postgres only
\ No newline at end of file
diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql
index 90961b7e3..f829cb304 100644
--- a/SQL/mysql.initial.sql
+++ b/SQL/mysql.initial.sql
@@ -260,4 +260,4 @@ CREATE TABLE `system` (
 
 SET FOREIGN_KEY_CHECKS=1;
 
-INSERT INTO `system` (`name`, `value`) VALUES ('roundcube-version', '2021100300');
+INSERT INTO `system` (`name`, `value`) VALUES ('roundcube-version', '2022081200');
diff --git a/SQL/mysql/2022081200.sql b/SQL/mysql/2022081200.sql
new file mode 100644
index 000000000..105e39efb
--- /dev/null
+++ b/SQL/mysql/2022081200.sql
@@ -0,0 +1 @@
+-- SQLite/Postgres only
\ No newline at end of file
diff --git a/SQL/oracle.initial.sql b/SQL/oracle.initial.sql
index 60446dd44..989681cf4 100644
--- a/SQL/oracle.initial.sql
+++ b/SQL/oracle.initial.sql
@@ -285,4 +285,4 @@ CREATE TABLE "system" (
     "value" long
 );
 
-INSERT INTO "system" ("name", "value") VALUES ('roundcube-version', '2021100300');
+INSERT INTO "system" ("name", "value") VALUES ('roundcube-version', '2022081200');
diff --git a/SQL/oracle/2022081200.sql b/SQL/oracle/2022081200.sql
new file mode 100644
index 000000000..105e39efb
--- /dev/null
+++ b/SQL/oracle/2022081200.sql
@@ -0,0 +1 @@
+-- SQLite/Postgres only
\ No newline at end of file
diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql
index 7db7e976c..209f0066c 100644
--- a/SQL/postgres.initial.sql
+++ b/SQL/postgres.initial.sql
@@ -375,4 +375,4 @@ CREATE TABLE "system" (
     value text
 );
 
-INSERT INTO "system" (name, value) VALUES ('roundcube-version', '2021100300');
+INSERT INTO "system" (name, value) VALUES ('roundcube-version', '2022081200');
diff --git a/SQL/postgres/2018122300.sql b/SQL/postgres/2018122300.sql
index b7c100156..1a7d895b1 100644
--- a/SQL/postgres/2018122300.sql
+++ b/SQL/postgres/2018122300.sql
@@ -1,4 +1,5 @@
-ALTER TABLE "filestore" ADD COLUMN context varchar(32) NOT NULL;
+ALTER TABLE "filestore" ADD COLUMN context varchar(32);
 UPDATE "filestore" SET context = 'enigma';
+ALTER TABLE "filestore" ALTER COLUMN context SET NOT NULL;
 ALTER TABLE "filestore" DROP CONSTRAINT "filestore_user_id_filename";
 ALTER TABLE "filestore" ADD CONSTRAINT "filestore_user_id_context_filename" UNIQUE (user_id, context, filename);
diff --git a/SQL/postgres/2020091000.sql b/SQL/postgres/2020091000.sql
index d27fd32b1..fa618b3b7 100644
--- a/SQL/postgres/2020091000.sql
+++ b/SQL/postgres/2020091000.sql
@@ -1,3 +1,4 @@
+
 CREATE SEQUENCE collected_addresses_seq
     START WITH 1
     INCREMENT BY 1
diff --git a/SQL/postgres/2022081200.sql b/SQL/postgres/2022081200.sql
new file mode 100644
index 000000000..fee9438e7
--- /dev/null
+++ b/SQL/postgres/2022081200.sql
@@ -0,0 +1,2 @@
+DROP INDEX responses_user_id_idx;
+CREATE INDEX responses_user_id_idx ON responses (user_id, del);
diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql
index 5c317f5e0..dd8f2f500 100644
--- a/SQL/sqlite.initial.sql
+++ b/SQL/sqlite.initial.sql
@@ -260,4 +260,4 @@ CREATE TABLE system (
   value text NOT NULL
 );
 
-INSERT INTO system (name, value) VALUES ('roundcube-version', '2021100300');
+INSERT INTO system (name, value) VALUES ('roundcube-version', '2022081200');
diff --git a/SQL/sqlite/2022081200.sql b/SQL/sqlite/2022081200.sql
new file mode 100644
index 000000000..d379fc558
--- /dev/null
+++ b/SQL/sqlite/2022081200.sql
@@ -0,0 +1,2 @@
+DROP INDEX ix_responses_user_id;
+CREATE INDEX ix_responses_user_id ON responses(user_id, del);