# HG changeset patch # User Ingo Weinzierl # Date 1339575120 0 # Node ID cc8fc6b296492cfbb1e49a25d7fc9bf95468ec4d # Parent a41f279a66e210219c6240cdf43734e181dc6dc9 Store sq relations into database after parsing. flys-backend/trunk@4647 c6561f87-3c4e-4783-a992-168aeb5c3f6f diff -r a41f279a66e2 -r cc8fc6b29649 flys-backend/ChangeLog --- a/flys-backend/ChangeLog Wed Jun 13 06:22:04 2012 +0000 +++ b/flys-backend/ChangeLog Wed Jun 13 08:12:00 2012 +0000 @@ -1,3 +1,23 @@ +2012-06-13 Ingo Weinzierl + + * doc/schema/oracle-minfo.sql, + doc/schema/oracle-drop-minfo.sql: Added missing river_id column to + sq_relation table and reordered the drop statements. + + * src/main/java/de/intevation/flys/model/SQRelationValue.java, + src/main/java/de/intevation/flys/model/SQRelation.java: Added missing + constructors and fixed some minor bugs that occured during import test. + + * src/main/java/de/intevation/flys/importer/ImportSQRelationValue.java, + src/main/java/de/intevation/flys/importer/ImportSQRelation.java: + Implemented code to store sq relations and values into db. + + * src/main/java/de/intevation/flys/importer/parsers/SQRelationParser.java: + Override parse() to retrieve the filename. + + * src/main/java/de/intevation/flys/importer/ImportRiver.java: Implemented + code to store sq relations into db. + 2012-06-13 Ingo Weinzierl * src/main/java/de/intevation/flys/importer/ImportSQRelationValue.java, diff -r a41f279a66e2 -r cc8fc6b29649 flys-backend/doc/schema/oracle-drop-minfo.sql --- a/flys-backend/doc/schema/oracle-drop-minfo.sql Wed Jun 13 06:22:04 2012 +0000 +++ b/flys-backend/doc/schema/oracle-drop-minfo.sql Wed Jun 13 08:12:00 2012 +0000 @@ -40,6 +40,7 @@ ALTER TABLE waterlevel_difference_column DROP CONSTRAINT fk_wdc_difference_id; ALTER TABLE waterlevel_difference_values DROP CONSTRAINT fk_wdv_column_id; ALTER TABLE sq_relation DROP CONSTRAINT fk_sqr_tinterval_id; +ALTER TABLE sq_relation DROP CONSTRAINT fk_sqr_river_id; ALTER TABLE sq_relation_value DROP CONSTRAINT fk_sqr_id; DROP TABLE bed_height_type; @@ -68,8 +69,8 @@ DROP TABLE waterlevel_difference; DROP TABLE waterlevel_difference_column; DROP TABLE waterlevel_difference_values; +DROP TABLE sq_relation_value; DROP TABLE sq_relation; -DROP TABLE sq_relation_value; DROP SEQUENCE BED_HEIGHT_TYPE_SEQ; DROP SEQUENCE LOCATION_SYSTEM_SEQ; diff -r a41f279a66e2 -r cc8fc6b29649 flys-backend/doc/schema/oracle-minfo.sql --- a/flys-backend/doc/schema/oracle-minfo.sql Wed Jun 13 06:22:04 2012 +0000 +++ b/flys-backend/doc/schema/oracle-minfo.sql Wed Jun 13 08:12:00 2012 +0000 @@ -356,9 +356,11 @@ CREATE TABLE sq_relation ( id NUMBER(38,0) NOT NULL, + river_id NUMBER(38,0) NOT NULL, time_interval_id NUMBER(38,0) NOT NULL, description VARCHAR(256), PRIMARY KEY (id), + CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) ); diff -r a41f279a66e2 -r cc8fc6b29649 flys-backend/src/main/java/de/intevation/flys/importer/ImportRiver.java --- a/flys-backend/src/main/java/de/intevation/flys/importer/ImportRiver.java Wed Jun 13 06:22:04 2012 +0000 +++ b/flys-backend/src/main/java/de/intevation/flys/importer/ImportRiver.java Wed Jun 13 08:12:00 2012 +0000 @@ -530,7 +530,7 @@ protected void parseSQRelation() throws IOException { if (Config.INSTANCE.skipSQRelation()) { - log.info("skip sq relation"); + log.info("skip parsing sq relation"); return; } @@ -1228,7 +1228,24 @@ if (!Config.INSTANCE.skipSQRelation()) { log.info("store sq relations"); - // TODO + River river = getPeer(); + + int count = 0; + + for (ImportSQRelation sqRelation: sqRelations) { + try { + sqRelation.storeDependencies(river); + count++; + } + catch (SQLException sqle) { + log.error("Error while storing sq relation.", sqle); + } + catch (ConstraintViolationException cve) { + log.error("Error while storing sq relation.", cve); + } + } + + log.info("stored " + count + " sq relations."); } } diff -r a41f279a66e2 -r cc8fc6b29649 flys-backend/src/main/java/de/intevation/flys/importer/ImportSQRelation.java --- a/flys-backend/src/main/java/de/intevation/flys/importer/ImportSQRelation.java Wed Jun 13 06:22:04 2012 +0000 +++ b/flys-backend/src/main/java/de/intevation/flys/importer/ImportSQRelation.java Wed Jun 13 08:12:00 2012 +0000 @@ -6,10 +6,13 @@ import org.apache.log4j.Logger; +import org.hibernate.Query; +import org.hibernate.Session; import org.hibernate.exception.ConstraintViolationException; import de.intevation.flys.model.River; import de.intevation.flys.model.SQRelation; +import de.intevation.flys.model.TimeInterval; public class ImportSQRelation { @@ -19,6 +22,8 @@ private ImportTimeInterval timeInterval; + private String description; + private List values; private SQRelation peer; @@ -33,21 +38,76 @@ throws SQLException, ConstraintViolationException { log.info("store dependencies"); - log.warn("TODO: IMPLEMENT 'storeDependencies()'"); + + SQRelation peer = getPeer(river); + + timeInterval.getPeer(); + + int count = 0; + + for (ImportSQRelationValue value: values) { + try { + value.storeDependencies(peer); + count++; + } + catch (SQLException sqle) { + log.warn("Unable to store sq relation value.", sqle); + } + catch (ConstraintViolationException cve) { + log.warn("Unable to store sq relation value.", cve); + } + } + + log.info("stored " + count + " sq relation values."); } - public SQRelation getPeer() { + public SQRelation getPeer(River river) { log.debug("getPeer()"); if (peer == null) { - // TODO + TimeInterval timeInter = timeInterval.getPeer(); + + if (timeInter == null) { + log.warn("Cannot determine sq relation without time interval"); + return null; + } + + Session session = ImporterSession.getInstance().getDatabaseSession(); + + Query query = session.createQuery( + "FROM SQRelation WHERE river=:river AND timeInterval=:timeInter" + ); + + query.setParameter("river", river); + query.setParameter("timeInter", timeInter); + + List sq = query.list(); + + if (sq.isEmpty()) { + log.info("create new SQ relation '" + description + "'"); + + peer = new SQRelation( + river, + timeInter, + description + ); + session.save(peer); + } + else { + peer = sq.get(0); + } } return peer; } + public void setDescription(String description) { + this.description = description; + } + + public void setTimeInterval(ImportTimeInterval timeInterval) { this.timeInterval = timeInterval; } diff -r a41f279a66e2 -r cc8fc6b29649 flys-backend/src/main/java/de/intevation/flys/importer/ImportSQRelationValue.java --- a/flys-backend/src/main/java/de/intevation/flys/importer/ImportSQRelationValue.java Wed Jun 13 06:22:04 2012 +0000 +++ b/flys-backend/src/main/java/de/intevation/flys/importer/ImportSQRelationValue.java Wed Jun 13 08:12:00 2012 +0000 @@ -1,9 +1,12 @@ package de.intevation.flys.importer; import java.sql.SQLException; +import java.util.List; import org.apache.log4j.Logger; +import org.hibernate.Query; +import org.hibernate.Session; import org.hibernate.exception.ConstraintViolationException; import de.intevation.flys.model.SQRelation; @@ -46,16 +49,46 @@ public void storeDependencies(SQRelation owner) throws SQLException, ConstraintViolationException { - log.info("store dependencies"); - log.warn("TODO: IMPLEMENT 'storeDependencies()'"); + getPeer(owner); } - public SQRelationValue getPeer() { - log.debug("getPeer()"); + public SQRelationValue getPeer(SQRelation owner) { + if (peer == null) { + Session session = ImporterSession.getInstance().getDatabaseSession(); - if (peer == null) { - // TODO + Query query = session.createQuery( + "from SQRelationValue " + + " where sqRelation=:owner " + + " and parameter=:parameter" + + " and fraction=:fraction" + + " and function=:function" + + " and km=:km"); + + query.setParameter("owner", owner); + query.setString("parameter", parameter); + query.setString("fraction", fraction); + query.setString("function", function); + query.setDouble("km", km); + + List values = query.list(); + + if (values.isEmpty()) { + peer = new SQRelationValue( + owner, + parameter, + fraction, + function, + km, + a, + b + ); + + session.save(peer); + } + else { + peer = values.get(0); + } } return peer; diff -r a41f279a66e2 -r cc8fc6b29649 flys-backend/src/main/java/de/intevation/flys/importer/parsers/SQRelationParser.java --- a/flys-backend/src/main/java/de/intevation/flys/importer/parsers/SQRelationParser.java Wed Jun 13 06:22:04 2012 +0000 +++ b/flys-backend/src/main/java/de/intevation/flys/importer/parsers/SQRelationParser.java Wed Jun 13 08:12:00 2012 +0000 @@ -1,5 +1,7 @@ package de.intevation.flys.importer.parsers; +import java.io.File; +import java.io.IOException; import java.text.NumberFormat; import java.text.ParseException; import java.util.ArrayList; @@ -30,6 +32,8 @@ private ImportSQRelation current; + private String currentDescription; + public SQRelationParser() { relations = new ArrayList(); @@ -41,6 +45,13 @@ } @Override + public void parse(File file) throws IOException { + this.currentDescription = file.getName(); + super.parse(file); + } + + + @Override protected void reset() { current = new ImportSQRelation(); } @@ -48,7 +59,10 @@ @Override protected void finish() { - relations.add(current); + if (current != null) { + current.setDescription(currentDescription); + relations.add(current); + } } diff -r a41f279a66e2 -r cc8fc6b29649 flys-backend/src/main/java/de/intevation/flys/model/SQRelation.java --- a/flys-backend/src/main/java/de/intevation/flys/model/SQRelation.java Wed Jun 13 06:22:04 2012 +0000 +++ b/flys-backend/src/main/java/de/intevation/flys/model/SQRelation.java Wed Jun 13 08:12:00 2012 +0000 @@ -11,6 +11,7 @@ import javax.persistence.Column; import javax.persistence.SequenceGenerator; import javax.persistence.OneToMany; +import javax.persistence.OneToOne; import javax.persistence.JoinColumn; import javax.persistence.GenerationType; @@ -21,6 +22,8 @@ private Integer id; + private River river; + private TimeInterval timeInterval; private String description; @@ -28,6 +31,17 @@ private List values; + protected SQRelation() { + } + + + public SQRelation(River river, TimeInterval timeInterval, String desc) { + this.river = river; + this.timeInterval = timeInterval; + this.description = desc; + } + + @Id @SequenceGenerator( name = "SEQUENCE_SQ_ID_SEQ", @@ -46,6 +60,17 @@ } + @OneToOne + @JoinColumn(name = "river_id") + public River getRiver() { + return river; + } + + public void setRiver(River river) { + this.river = river; + } + + @Column(name = "description") public String getDescription() { return description; @@ -56,7 +81,8 @@ } - @Column(name = "time_interval") + @OneToOne + @JoinColumn(name = "time_interval_id") public TimeInterval getTimeInterval() { return timeInterval; } diff -r a41f279a66e2 -r cc8fc6b29649 flys-backend/src/main/java/de/intevation/flys/model/SQRelationValue.java --- a/flys-backend/src/main/java/de/intevation/flys/model/SQRelationValue.java Wed Jun 13 06:22:04 2012 +0000 +++ b/flys-backend/src/main/java/de/intevation/flys/model/SQRelationValue.java Wed Jun 13 08:12:00 2012 +0000 @@ -6,6 +6,7 @@ import javax.persistence.Id; import javax.persistence.Table; import javax.persistence.GeneratedValue; +import javax.persistence.JoinColumn; import javax.persistence.Column; import javax.persistence.SequenceGenerator; import javax.persistence.OneToOne; @@ -29,6 +30,29 @@ private double b; + protected SQRelationValue() { + } + + + public SQRelationValue( + SQRelation sqRelation, + String parameter, + String fraction, + String function, + double km, + double a, + double b + ) { + this.sqRelation = sqRelation; + this.parameter = parameter; + this.fraction = fraction; + this.function = function; + this.km = km; + this.a = a; + this.b = b; + } + + @Id @SequenceGenerator( name = "SEQUENCE_SQ_VALUE_ID_SEQ", @@ -47,13 +71,13 @@ } - //@Column(name = "sq_relation_id") @OneToOne - public SQRelation getSQRelation() { + @JoinColumn(name = "sq_relation_id") + public SQRelation getSqRelation() { return sqRelation; } - public void setSQRelation(SQRelation sqRelation) { + public void setSqRelation(SQRelation sqRelation) { this.sqRelation = sqRelation; }