Bug report #10478

Non-geometry Spatialite view shows the last record duplicated

Added by cgsbob - about 6 years ago. Updated over 1 year ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider
Affected QGIS version:2.2.0 Regression?:No
Operating System:Windows Easy fix?:No
Pull Request or Patch supplied:No Resolution:end of life
Crashes QGIS or corrupts data:No Copied to github as #:18890

Description

I created a nonspatial Spatialite database using the SQL commands below. When I look at the Attribute of the view, I see the last record of the view duplicated.

CREATE TABLE Sample_Data (
        fid INTEGER PRIMARY KEY AUTOINCREMENT,
        creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        revision_date TIMESTAMP,
        official_name TEXT,
        sample_id TEXT,
        geol_map_unit TEXT,
        top_depth REAL  NOT NULL,
        bottom_depth REAL,
        lith_type TEXT  NOT NULL,
        layer_number INTEGER,
        sample_equip TEXT,
        liner TEXT,
        sampler_dimension_source TEXT,
        remarks TEXT,
        oname_sampid TEXT,
        UNIQUE (official_name,sample_id)
);
CREATE TABLE Sample_Data (
        fid INTEGER PRIMARY KEY AUTOINCREMENT,
        creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        revision_date TIMESTAMP,
        official_name TEXT,
        sample_id TEXT,
        geol_map_unit TEXT,
        top_depth REAL  NOT NULL,
        bottom_depth REAL,
        lith_type TEXT  NOT NULL,
        layer_number INTEGER,
        sample_equip TEXT,
        liner TEXT,
        sampler_dimension_source TEXT,
        remarks TEXT,
        oname_sampid TEXT,
        UNIQUE (official_name,sample_id)
);
CREATE TABLE "Soil_Indices_Lab" (
        fid                     INTEGER PRIMARY KEY AUTOINCREMENT,
        creation_date                   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        revision_date                   TIMESTAMP,
        official_name                   TEXT,
        sample_id                       TEXT,
        "parameter_name"                TEXT    NOT NULL,
        "parameter_value"               REAL,
        "parameter_units"               TEXT,
        remarks                         TEXT,
        oname_sampid                    TEXT,
        UNIQUE (official_name,sample_id,parameter_name),
        FOREIGN KEY(official_name,sample_id) REFERENCES Sample_Data(official_name,sample_id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE VIEW "sd_soils2" AS
SELECT
    "Soil_Indices_Lab"."official_name" AS official_name,
    "Soil_Indices_Lab"."sample_id" AS sample_id,
    "Soil_Indices_Lab"."parameter_name" AS parameter_name,
    "Soil_Indices_Lab"."parameter_value" AS parameter_value,
    "Soil_Indices_Lab"."parameter_units" AS parameter_units,
    "Soil_Indices_Lab"."remarks" AS remarks
FROM "Sample_Data" 
JOIN "Soil_Indices_Lab" USING ("official_name", "sample_id");
CREATE TRIGGER del_sd_soils2 instead of delete on sd_soils2
begin
   delete from "Soil_Indices_Lab" 
      where
         official_name=old.official_name
      and
         sample_id=old.sample_id
      and
         parameter_name=old.parameter_name
      and
         parameter_value=old.parameter_value;
end;
CREATE TRIGGER in_sd_soils2 instead of insert on "sd_soils2" 
begin
   insert into "Soil_Indices_Lab" ("official_name","sample_id","parameter_name","parameter_value","parameter_units","remarks")
      values(new.official_name, new.sample_id, new.parameter_name, new.parameter_value, new.parameter_units, new.remarks);
end;
CREATE TRIGGER up_sd_soils2 instead of update on "sd_soils2" 
begin
   update "Soil_Indices_Lab" 
      set
         "parameter_name"=new.parameter_name,
         "parameter_value"=new.parameter_value,
         "parameter_units"=new.parameter_units,
         "remarks"=new.remarks
      where
         official_name=old.official_name
      and
         sample_id=old.sample_id
      and
         parameter_name=old.parameter_name
      and
         parameter_value=old.parameter_value;
end;

INSERT INTO "Sample_Data" VALUES(1,NULL,NULL,'2EI_5432b1','1',NULL,3.0,4.0,'ML-CL',1,'MCS','N','S',NULL,'2EI_5432b1|1');
INSERT INTO "Sample_Data" VALUES(2,NULL,NULL,'2EI_5432b1','2',NULL,6.0,7.0,'SC-SM',2,'MCS','N','S',NULL,'2EI_5432b1|2');
INSERT INTO "Soil_Indices_Lab" VALUES(5,'2014-06-05 22:01:39',NULL,'2EI_5432b1','1','LL',1.414,'PCT','test1','2EI_5432b1|1');
INSERT INTO "Soil_Indices_Lab" VALUES(7,'2014-06-05 22:06:22',NULL,'2EI_5432b1','2','DD',5.0,'PCT','test2','2EI_5432b1|2');

select * from sd_soils2;
official_name|sample_id|parameter_name|parameter_value|parameter_units|remarks
2EI_5432b1|1|LL|1.414|PCT|test1
2EI_5432b1|2|DD|5.0|PCT|test2

image001.jpg - spatialite parent and child table with writeable view (17.4 KB) cgsbob -, 2014-06-05 05:43 PM

History

#1 Updated by Jürgen Fischer about 6 years ago

  • Target version changed from Version 2.2 to Future Release - Lower Priority

#2 Updated by Giovanni Manghi over 3 years ago

  • Easy fix? set to No
  • Regression? set to No

#3 Updated by Giovanni Manghi over 1 year ago

  • Resolution set to end of life
  • Status changed from Open to Closed

Also available in: Atom PDF