Mercurial > gemma
changeset 1572:056a86b24be2
Made bottleneck primary key an int. Attention: This may break something!
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 12 Dec 2018 19:21:02 +0100 |
parents | b3f2d43f43e4 |
children | 761e8744e1f3 |
files | pkg/controllers/surveys.go pkg/imports/bn.go pkg/imports/sr.go pkg/models/sr.go schema/gemma.sql |
diffstat | 5 files changed, 73 insertions(+), 26 deletions(-) [+] |
line wrap: on
line diff
--- a/pkg/controllers/surveys.go Wed Dec 12 17:21:11 2018 +0100 +++ b/pkg/controllers/surveys.go Wed Dec 12 19:21:02 2018 +0100 @@ -23,10 +23,13 @@ ) const ( - listSurveysSQL = `SELECT s.bottleneck_id, - s.date_info::text -FROM waterway.bottlenecks b, waterway.sounding_results s -WHERE b.objnam=$1 AND s.bottleneck_id = b.bottleneck_id;` + listSurveysSQL = ` +SELECT + s.bottleneck_id, + s.date_info::text +FROM waterway.bottlenecks b JOIN waterway.sounding_results s +ON b.id = s.bottleneck_id +WHERE b.objnam=$1` ) func listSurveys(
--- a/pkg/imports/bn.go Wed Dec 12 17:21:11 2018 +0100 +++ b/pkg/imports/bn.go Wed Dec 12 19:21:02 2018 +0100 @@ -32,7 +32,12 @@ const BNJobKind JobKind = "bn" -const insertSQL = `INSERT INTO waterway.bottlenecks ( +const ( + hasBottleneckSQL = ` +SELECT true FROM waterway.bottlenecks WHERE bottleneck_id = $1` + + insertSQL = ` +INSERT INTO waterway.bottlenecks ( bottleneck_id, fk_g_fid, objnam, @@ -60,7 +65,9 @@ $11, $12, $13 -) ON CONFLICT (bottleneck_id) DO NOTHING` +) +RETURNING id` +) type bnJobCreator struct{} @@ -156,9 +163,35 @@ } defer tx.Rollback() + hasStmt, err := tx.PrepareContext(ctx, hasBottleneckSQL) + if err != nil { + return nil, err + } + defer hasStmt.Close() + insertStmt, err := tx.PrepareContext(ctx, insertSQL) + if err != nil { + return nil, err + } + defer insertStmt.Close() + + var nids []int64 + start := time.Now() - for i := range bns { - bn := bns[i] + +nextBN: + for _, bn := range bns { + + var found bool + err := hasStmt.QueryRowContext(ctx, bn.Bottleneck_id).Scan(&found) + switch { + case err == sql.ErrNoRows: + // This is good. + case err != nil: + return nil, err + case found: + continue nextBN + } + rb, lb := splitRBLB(bn.Rb_lb) var limiting, country string @@ -171,7 +204,10 @@ country = string(*bn.Responsible_country) } - tx.Exec(insertSQL, + var nid int64 + + err = insertStmt.QueryRowContext( + ctx, bn.Bottleneck_id, bn.Fk_g_fid, bn.OBJNAM, @@ -184,24 +220,31 @@ limiting, bn.Date_Info, bn.Source, - ) - feedback.Info("Insert \"%s\" into database", bn.OBJNAM) - //TODO: Track for potential later removal? Bottlenecks have an string PK, track wants int64. - // if err = track(ctx, tx, importID, "waterway.bottlenecks", bn.Bottleneck_id); err != nil { - // return nil, err - // } + ).Scan(&nid) + if err != nil { + return nil, err + } + nids = append(nids, nid) + feedback.Info("Inserted '%s'into database", bn.OBJNAM) + if err := track(ctx, tx, importID, "waterway.bottlenecks", nid); err != nil { + return nil, err + } + } + if len(nids) == 0 { + feedback.Error("No new bottlenecks found") + return nil, errors.New("No new bottlenecks found") } - feedback.Info("Storing %d bottlenecks took %s", len(bns), time.Since(start)) + feedback.Info("Storing %d bottlenecks took %s", len(nids), time.Since(start)) if err = tx.Commit(); err == nil { feedback.Info("Import of bottlenecks was successful") } - // TODO: needs to be filled. + // TODO: needs to be filled more useful. summary := struct { - BottleneckCount int `json:"bottleneckCount"` + Bottlenecks []int64 `json:"bottlenecks"` }{ - BottleneckCount: len(bns), + Bottlenecks: nids, } return &summary, err }
--- a/pkg/imports/sr.go Wed Dec 12 17:21:11 2018 +0100 +++ b/pkg/imports/sr.go Wed Dec 12 19:21:02 2018 +0100 @@ -114,7 +114,7 @@ point_cloud, area ) VALUES ( - (SELECT bottleneck_id from waterway.bottlenecks where objnam = $1), + (SELECT id from waterway.bottlenecks where objnam = $1), $2::date, $3, ST_Transform(ST_GeomFromWKB($4, $6::integer), 4326)::geography,
--- a/pkg/models/sr.go Wed Dec 12 17:21:11 2018 +0100 +++ b/pkg/models/sr.go Wed Dec 12 19:21:02 2018 +0100 @@ -46,7 +46,7 @@ checkBottleneckDateUniqueSQL = ` SELECT true FROM waterway.sounding_results sr JOIN - waterway.bottlenecks bn ON sr.bottleneck_id = bn.bottleneck_id + waterway.bottlenecks bn ON sr.bottleneck_id = bn.id WHERE bn.objnam = $1 AND sr.date_info = $2` )
--- a/schema/gemma.sql Wed Dec 12 17:21:11 2018 +0100 +++ b/schema/gemma.sql Wed Dec 12 19:21:02 2018 +0100 @@ -402,7 +402,8 @@ -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL) CREATE TABLE bottlenecks ( - bottleneck_id varchar PRIMARY KEY, + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + bottleneck_id varchar UNIQUE NOT NULL, fk_g_fid isrs NOT NULL REFERENCES gauges, -- XXX: DRC references "ch. 3.1.1", which does not exist in document. objnam varchar, @@ -431,7 +432,7 @@ FOR EACH ROW EXECUTE PROCEDURE update_date_info() CREATE TABLE bottlenecks_riverbed_materials ( - bottleneck_id varchar REFERENCES bottlenecks, + bottleneck_id int REFERENCES bottlenecks(id), riverbed varchar REFERENCES riverbed_materials, -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3 PRIMARY KEY (bottleneck_id, riverbed) @@ -439,7 +440,7 @@ CREATE TABLE sounding_results ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, - bottleneck_id varchar NOT NULL REFERENCES bottlenecks, + bottleneck_id int NOT NULL REFERENCES bottlenecks(id), date_info date NOT NULL, UNIQUE (bottleneck_id, date_info), area geography(POLYGON, 4326) NOT NULL, @@ -476,7 +477,7 @@ CREATE TABLE fairway_availability ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, position_code char(2) REFERENCES position_codes, - bottleneck_id varchar NOT NULL REFERENCES bottlenecks, + bottleneck_id int NOT NULL REFERENCES bottlenecks(id), surdat date NOT NULL, UNIQUE (bottleneck_id, surdat), -- additional_data xml -- Currently not relevant for GEMMA @@ -539,7 +540,7 @@ FROM waterway.bottlenecks bn LEFT JOIN ( SELECT bottleneck_id, max(date_info) AS current FROM waterway.sounding_results - GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.bottleneck_id + GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id ORDER BY objnam CREATE TABLE import_configuration (