Mercurial > gemma

changeset 4749:fd9f171b87e4

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Generally disallow the same section name for different countries This avoids the situation that a waterway admin is informed about the duplicate name only on trying to accept the import of a new section. Such an import will now end up failed.
author Tom Gottfried <tom@intevation.de>
date Fri, 18 Oct 2019 15:45:48 +0200
parents 47922c1a088d
children 67a5de15490b
files pkg/pgxutils/errors.go schema/gemma.sql schema/updates/1311/01.improve_section_uniqueness.sql schema/version.sql
diffstat 4 files changed, 20 insertions(+), 1 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/pgxutils/errors.go	Fri Oct 18 15:34:34 2019 +0200
+++ b/pkg/pgxutils/errors.go	Fri Oct 18 15:45:48 2019 +0200
@@ -26,6 +26,7 @@
 	foreignKeyViolation      = "23503"
 	uniqueViolation          = "23505"
 	checkViolation           = "23514"
+	exclusionViolation       = "23P01"
 	violatesRowLevelSecurity = "42501"
 	duplicateObject          = "42710"
 	noDataFound              = "P0002"
@@ -119,6 +120,19 @@
 				}
 			}
 		}
+	case exclusionViolation:
+		switch err.SchemaName {
+		case "waterway":
+			switch err.TableName {
+			case "sections":
+				switch err.ConstraintName {
+				case "sections_name_country_excl":
+					m = "A section with that name already exists for another country"
+					c = http.StatusConflict
+					return
+				}
+			}
+		}
 	case checkViolation:
 		switch err.SchemaName {
 		case "waterway":
--- a/schema/gemma.sql	Fri Oct 18 15:34:34 2019 +0200
+++ b/schema/gemma.sql	Fri Oct 18 15:45:48 2019 +0200
@@ -628,6 +628,9 @@
         date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
         source_organization varchar NOT NULL,
         staging_done boolean NOT NULL DEFAULT false,
+        -- Disallow the same name for different countries
+        EXCLUDE USING GiST (name WITH =, country WITH <>),
+        -- Allow the same name one time in and outside staging area, each
         UNIQUE(name, staging_done)
     )
     CREATE TRIGGER sections_date_info
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1311/01.improve_section_uniqueness.sql	Fri Oct 18 15:45:48 2019 +0200
@@ -0,0 +1,2 @@
+ALTER TABLE waterway.sections
+    ADD EXCLUDE USING GiST (name WITH =, country WITH <>)
--- a/schema/version.sql	Fri Oct 18 15:34:34 2019 +0200
+++ b/schema/version.sql	Fri Oct 18 15:45:48 2019 +0200
@@ -1,1 +1,1 @@
-INSERT INTO gemma_schema_version(version) VALUES (1310);
+INSERT INTO gemma_schema_version(version) VALUES (1311);