From e63f206d994658995505ce322d644fba0b807d5b Mon Sep 17 00:00:00 2001
From: Even Rouault <even.rouault@spatialys.com>
Date: Sun, 21 Apr 2019 21:51:43 +0200
Subject: Database: make conversion & helmert_transformation updatable views

- Transform conversion as a view, and when inserting into it, actually
  insert into 3 tables: conversion_table, conversion_method and conversion_param,
  so that method and parameter names are not repeated each time.
- Similarly for helmert_tranformation, insert into helmert_transformation_tabl
  and coordinate_operation_method.

This reduces the db size from 6 344 704 bytes to 5 853 184 bytes, without
significant slowdown for queries.
---
 data/sql/conversion_triggers.sql | 4 ++--
 1 file changed, 2 insertions(+), 2 deletions(-)

(limited to 'data/sql/conversion_triggers.sql')

diff --git a/data/sql/conversion_triggers.sql b/data/sql/conversion_triggers.sql
index 86eb1afd..001a8174 100644
--- a/data/sql/conversion_triggers.sql
+++ b/data/sql/conversion_triggers.sql
@@ -1,8 +1,8 @@
 --- This file has been generated by scripts/build_db.py. DO NOT EDIT !
 
 CREATE TRIGGER conversion_method_check_insert_trigger
-BEFORE INSERT ON conversion
-FOR EACH ROW BEGIN
+INSTEAD OF INSERT ON conversion
+BEGIN
 
     SELECT RAISE(ABORT, 'insert on conversion violates constraint: bad parameters for Lambert Conic Conformal (2SP)')
         WHERE NEW.deprecated != 1 AND NEW.method_auth_name = 'EPSG' AND NEW.method_code = '9802' AND (NEW.method_name != 'Lambert Conic Conformal (2SP)' OR NEW.param1_auth_name != 'EPSG' OR NEW.param1_code != '8821' OR NEW.param1_name != 'Latitude of false origin' OR NEW.param1_value IS NULL OR NEW.param1_uom_auth_name IS NULL OR NEW.param1_uom_code IS NULL OR (SELECT type FROM unit_of_measure WHERE auth_name = NEW.param1_uom_auth_name AND code = NEW.param1_uom_code) != 'angle' OR NEW.param2_auth_name != 'EPSG' OR NEW.param2_code != '8822' OR NEW.param2_name != 'Longitude of false origin' OR NEW.param2_value IS NULL OR NEW.param2_uom_auth_name IS NULL OR NEW.param2_uom_code IS NULL OR (SELECT type FROM unit_of_measure WHERE auth_name = NEW.param2_uom_auth_name AND code = NEW.param2_uom_code) != 'angle' OR NEW.param3_auth_name != 'EPSG' OR NEW.param3_code != '8823' OR NEW.param3_name != 'Latitude of 1st standard parallel' OR NEW.param3_value IS NULL OR NEW.param3_uom_auth_name IS NULL OR NEW.param3_uom_code IS NULL OR (SELECT type FROM unit_of_measure WHERE auth_name = NEW.param3_uom_auth_name AND code = NEW.param3_uom_code) != 'angle' OR NEW.param4_auth_name != 'EPSG' OR NEW.param4_code != '8824' OR NEW.param4_name != 'Latitude of 2nd standard parallel' OR NEW.param4_value IS NULL OR NEW.param4_uom_auth_name IS NULL OR NEW.param4_uom_code IS NULL OR (SELECT type FROM unit_of_measure WHERE auth_name = NEW.param4_uom_auth_name AND code = NEW.param4_uom_code) != 'angle' OR NEW.param5_auth_name != 'EPSG' OR NEW.param5_code != '8826' OR NEW.param5_name != 'Easting at false origin' OR NEW.param5_value IS NULL OR NEW.param5_uom_auth_name IS NULL OR NEW.param5_uom_code IS NULL OR (SELECT type FROM unit_of_measure WHERE auth_name = NEW.param5_uom_auth_name AND code = NEW.param5_uom_code) != 'length' OR NEW.param6_auth_name != 'EPSG' OR NEW.param6_code != '8827' OR NEW.param6_name != 'Northing at false origin' OR NEW.param6_value IS NULL OR NEW.param6_uom_auth_name IS NULL OR NEW.param6_uom_code IS NULL OR (SELECT type FROM unit_of_measure WHERE auth_name = NEW.param6_uom_auth_name AND code = NEW.param6_uom_code) != 'length' OR NEW.param7_auth_name IS NOT NULL OR NEW.param7_code IS NOT NULL OR NEW.param7_name IS NOT NULL OR NEW.param7_value IS NOT NULL OR NEW.param7_uom_auth_name IS NOT NULL OR NEW.param7_uom_code IS NOT NULL);
-- 
cgit v1.2.3

