# HG changeset patch # User Tom Gottfried # Date 1490625071 -7200 # Node ID 46aa15c8cfb262c76a67b624094d5ebdde62d961 # Parent f2e574204e38895bfba971d67007d16fd6fba9c5# Parent 212fe0cfd69489d2ff63155fb3c5ca21e285fc1e Merged branch release-2.5 into default. diff -r 212fe0cfd694 -r 46aa15c8cfb2 .hgtags --- a/.hgtags Fri Mar 24 11:59:19 2017 +0100 +++ b/.hgtags Mon Mar 27 16:31:11 2017 +0200 @@ -40,3 +40,4 @@ effef57f3f7e1587cae44b5907cf624105ced0db 2.3.0 fb90e4d8bd0d09fb7bc638e67b4f482ba830e3b7 2.4.0 204fb75b0c06e2f760dfe37d2fb0845ec31b68f1 2.4.1 +1f47e9fa10c4cd2319f96be262c0265168bb86a0 2.5 diff -r 212fe0cfd694 -r 46aa15c8cfb2 Dockerfile --- a/Dockerfile Fri Mar 24 11:59:19 2017 +0100 +++ b/Dockerfile Mon Mar 27 16:31:11 2017 +0200 @@ -76,7 +76,7 @@ # # Build and deploy LADA-server # -ENV LADA_VERSION 2.5 +ENV LADA_VERSION 2.6-SNAPSHOT RUN mvn clean compile package && \ mv target/lada-server-$LADA_VERSION.war \ $JBOSS_HOME/standalone/deployments && \ diff -r 212fe0cfd694 -r 46aa15c8cfb2 db_schema/Dockerfile --- a/db_schema/Dockerfile Fri Mar 24 11:59:19 2017 +0100 +++ b/db_schema/Dockerfile Mon Mar 27 16:31:11 2017 +0200 @@ -1,4 +1,4 @@ -# Docker file for postgresql 9.4 on debain +# Docker file for the LADA database on Debian # # build with e.g. `docker build --force-rm=true -t koala/lada_db .', # then run with e.g. @@ -28,24 +28,52 @@ # Install packages # RUN apt-get update && \ - apt-get install -y postgresql-9.4-postgis-2.1 postgis curl unzip + apt-get install -y curl unzip make gcc +RUN echo "deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main" \ + >> /etc/apt/sources.list +RUN curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add - +RUN apt-get update && \ + apt-get install -y --no-install-recommends \ + postgresql-9.5-postgis-2.3 postgresql-9.5-postgis-scripts postgis + +# +# Add context as working directory +# +ADD . /opt/lada_sql/ +WORKDIR /opt/lada_sql/ + +# +# Set environment variables +# +ENV PGCONF /etc/postgresql/9.5/main/postgresql.conf +ENV PGDATA /var/lib/postgresql/9.5/main # # Use user postgres to run the next commands # USER postgres -RUN /etc/init.d/postgresql start && \ - psql --command "CREATE USER admin WITH SUPERUSER PASSWORD 'secret';" +# XXX: Seems to fail on initdb issued by package installation +# (due to /usr/sbin/policy-rc.d ?). +# See also https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=739276 +RUN mkdir /var/run/postgresql/9.5-main.pg_stat_tmp # # Adjust PostgreSQL configuration so that remote connections to the # database are possible. # RUN echo "host all all 0.0.0.0/0 md5" >> \ - /etc/postgresql/9.4/main/pg_hba.conf + /etc/postgresql/9.5/main/pg_hba.conf +RUN echo "listen_addresses='*'" >> $PGCONF -RUN echo "listen_addresses='*'" >> /etc/postgresql/9.4/main/postgresql.conf +# +# Configure logging collector +# (because we use postgres directly in CMD, +# the usual collection from stderr does not work) +# +RUN echo "logging_collector = on" >> $PGCONF +RUN echo "log_directory = '/var/log/postgresql'" >> $PGCONF +RUN echo "log_filename = 'postgresql-9.5-main.log'" >> $PGCONF # # Expose the PostgreSQL port @@ -59,15 +87,12 @@ # 'FATAL: the database system is starting up'. # It's because of the -w # -ADD . /opt/lada_sql/ -WORKDIR /opt/lada_sql/ - -RUN /usr/lib/postgresql/9.4/bin/pg_ctl start -wD /etc/postgresql/9.4/main/ && \ - /opt/lada_sql/setup-db.sh +RUN /usr/lib/postgresql/9.5/bin/pg_ctl start -wo "--config_file=$PGCONF" && \ + /opt/lada_sql/setup-db.sh && \ + /usr/lib/postgresql/9.5/bin/pg_ctl stop # # Set the default command to run when starting the container # -CMD ["/usr/lib/postgresql/9.4/bin/postgres", "-D", \ - "/var/lib/postgresql/9.4/main", "-c", \ - "config_file=/etc/postgresql/9.4/main/postgresql.conf"] +CMD ["/usr/lib/postgresql/9.5/bin/postgres", \ + "--config_file=/etc/postgresql/9.5/main/postgresql.conf"] diff -r 212fe0cfd694 -r 46aa15c8cfb2 db_schema/audit.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/db_schema/audit.sql Mon Mar 27 16:31:11 2017 +0200 @@ -0,0 +1,213 @@ +-- Based on https://github.com/xdimedrolx/audit-trigger/ +-- +-- which is licensed by "The PostgreSQL License", effectively equivalent to the BSD +-- license. + +SET search_path TO land; +CREATE TABLE audit_trail( + id bigserial primary key, + table_name varchar(50) not null, + tstamp TIMESTAMP WITHOUT TIME ZONE NOT NULL, + action varchar(1) NOT NULL CHECK (action IN ('I','D','U', 'T')), + object_id integer not null, + row_data JSONB, + changed_fields JSONB +); + +CREATE OR REPLACE FUNCTION jsonb_delete_left(a jsonb, b jsonb) + RETURNS jsonb AS + $BODY$ + SELECT COALESCE( + ( + SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}') + FROM jsonb_each(a) + WHERE NOT ('{' || to_json(key) || ':' || value || '}')::jsonb <@ b + ) + , '{}')::jsonb; + $BODY$ +LANGUAGE sql IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_delete_left(jsonb, jsonb) IS 'delete matching pairs in second argument from first argument'; +DROP OPERATOR IF EXISTS - (jsonb, jsonb); +CREATE OPERATOR - ( PROCEDURE = jsonb_delete_left, LEFTARG = jsonb, RIGHTARG = jsonb); +COMMENT ON OPERATOR - (jsonb, jsonb) IS 'delete matching pairs from left operand'; + + +CREATE OR REPLACE FUNCTION jsonb_delete_left(a jsonb, b text[]) + RETURNS jsonb AS + $BODY$ + SELECT COALESCE( + ( + SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}') + FROM jsonb_each(a) + WHERE key <> ALL(b) + ) + , '{}')::jsonb; + $BODY$ +LANGUAGE sql IMMUTABLE STRICT; +COMMENT ON FUNCTION jsonb_delete_left(jsonb, text[]) IS 'delete keys in second argument from first argument'; +DROP OPERATOR IF EXISTS - (jsonb, text[]); +CREATE OPERATOR - ( PROCEDURE = jsonb_delete_left, LEFTARG = jsonb, RIGHTARG = text[]); +COMMENT ON OPERATOR - (jsonb, text[]) IS 'delete keys from left operand'; + + +CREATE OR REPLACE FUNCTION if_modified_func() RETURNS TRIGGER AS $body$ +DECLARE + audit_row land.audit_trail; + include_values boolean; + log_diffs boolean; + h_old jsonb; + h_new jsonb; + excluded_cols text[] = ARRAY[]::text[]; +BEGIN + IF TG_WHEN <> 'AFTER' THEN + RAISE EXCEPTION 'land.if_modified_func() may only run as an AFTER trigger'; + END IF; + + -- Do nothing on delete. + IF (TG_OP = 'DELETE') THEN + RETURN NULL; + END IF; + + audit_row = ROW( + nextval('land.audit_trail_id_seq'), -- id + TG_TABLE_NAME::varchar, -- table_name + current_timestamp, -- tstamp + substring(TG_OP,1,1), -- action + NEW.id, -- object_id + NULL, NULL -- row_data, changed_fields + ); + + IF TG_ARGV[1] IS NOT NULL THEN + excluded_cols = TG_ARGV[1]::text[]; + END IF; + + IF (TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW') THEN + audit_row.row_data = row_to_json(OLD)::JSONB; + audit_row.changed_fields = row_to_json(NEW)::JSONB - audit_row.row_data - excluded_cols; + IF audit_row.changed_fields = '{}'::jsonb THEN + -- All changed fields are ignored. Skip this update. + RETURN NULL; + END IF; + ELSIF (TG_OP = 'INSERT' AND TG_LEVEL = 'ROW') THEN + audit_row.row_data = row_to_json(NEW)::JSONB; + audit_row.changed_fields = jsonb_strip_nulls(row_to_json(NEW)::JSONB - excluded_cols); + ELSE + RAISE EXCEPTION '[land.if_modified_func] - Trigger func added as trigger for unhandled case: %, %',TG_OP, TG_LEVEL; + RETURN NULL; + END IF; + INSERT INTO land.audit_trail VALUES (audit_row.*); + RETURN NULL; +END; +$body$ +LANGUAGE plpgsql +SECURITY DEFINER +SET search_path = land, public; + +CREATE OR REPLACE FUNCTION audit_table( + target_table regclass, + audit_rows boolean, + audit_query_text boolean, + ignored_cols text[] +) RETURNS void AS +$body$ +DECLARE + stm_targets text = 'INSERT OR UPDATE OR DELETE OR TRUNCATE'; + _q_txt text; + _ignored_cols_snip text = ''; +BEGIN + EXECUTE 'DROP TRIGGER IF EXISTS audit_trigger_row ON ' || quote_ident(target_table::TEXT); + EXECUTE 'DROP TRIGGER IF EXISTS audit_trigger_stm ON ' || quote_ident(target_table::TEXT); + + IF audit_rows THEN + IF array_length(ignored_cols,1) > 0 THEN + _ignored_cols_snip = ', ' || quote_literal(ignored_cols); + END IF; + _q_txt = 'CREATE TRIGGER audit_trigger_row AFTER INSERT OR UPDATE OR DELETE ON ' || + quote_ident(target_table::TEXT) || + ' FOR EACH ROW EXECUTE PROCEDURE land.if_modified_func(' || + quote_literal(audit_query_text) || _ignored_cols_snip || ');'; + RAISE NOTICE '%',_q_txt; + EXECUTE _q_txt; + stm_targets = 'TRUNCATE'; + ELSE + END IF; + + _q_txt = 'CREATE TRIGGER audit_trigger_stm AFTER ' || stm_targets || ' ON ' || + target_table || + ' FOR EACH STATEMENT EXECUTE PROCEDURE land.if_modified_func('|| + quote_literal(audit_query_text) || ');'; + RAISE NOTICE '%',_q_txt; + EXECUTE _q_txt; + +END; +$body$ +language 'plpgsql'; + +COMMENT ON FUNCTION audit_table(regclass, boolean, boolean, text[]) IS $body$ +Add auditing support to a table. + +Arguments: + target_table: Table name, schema qualified if not on search_path + audit_rows: Record each row change, or only audit at a statement level + audit_query_text: Record the text of the client query that triggered the audit event? + ignored_cols: Columns to exclude from update diffs, ignore updates that change only ignored cols. +$body$; + +-- Pg doesn't allow variadic calls with 0 params, so provide a wrapper +CREATE OR REPLACE FUNCTION audit_table(target_table regclass, audit_rows boolean, audit_query_text boolean) RETURNS void AS $body$ +SELECT audit_table($1, $2, $3, ARRAY[]::text[]); +$body$ LANGUAGE SQL; + +-- And provide a convenience call wrapper for the simplest case +-- of row-level logging with no excluded cols and query logging enabled. +-- +CREATE OR REPLACE FUNCTION audit_table(target_table regclass) RETURNS void AS $body$ +SELECT audit_table($1, BOOLEAN 't', BOOLEAN 't'); +$body$ LANGUAGE 'sql'; + +COMMENT ON FUNCTION audit_table(regclass) IS $body$ +Add auditing support to the given table. Row-level changes will be logged with full client query text. No cols are ignored. +$body$; + +CREATE INDEX probe_id_ndx ON audit_trail(cast("row_data"->>'probe_id' AS int)); +CREATE INDEX messung_id_ndx ON audit_trail(cast("row_data"->>'messung_id' AS int)); + +-- View for probe audit trail +CREATE OR REPLACE VIEW audit_trail_probe AS +SELECT + id, + table_name, + action, + object_id, + tstamp, + cast(row_data ->> 'messungs_id' AS integer) AS messungs_id, + coalesce(cast(row_data ->> 'probe_id' AS integer), + (SELECT probe_id FROM messung WHERE id = cast( + row_data ->> 'messungs_id' AS integer))) AS probe_id, + row_data, + changed_fields +FROM audit_trail; + + +-- View for messung audit trail +CREATE OR REPLACE VIEW audit_trail_messung AS +SELECT audit_trail.id, + audit_trail.table_name, + audit_trail.tstamp, + audit_trail.action, + audit_trail.object_id, + audit_trail.row_data, + audit_trail.changed_fields, + cast(row_data ->> 'messungs_id' AS int) AS messungs_id +FROM audit_trail; + + +SELECT audit_table('probe', true, false, '{id, tree_modified, letzte_aenderung}'::text[]); +SELECT audit_table('messung', true, false, '{id, probe_id, tree_modified, letzte_aenderung, status}'::text[]); +SELECT audit_table('messwert', true, false, '{id, messungs_id, tree_modified, letzte_aenderung}'::text[]); +SELECT audit_table('kommentar_p', true, false, '{id, probe_id, tree_modified, letzte_aenderung}'::text[]); +SELECT audit_table('kommentar_m', true, false, '{id, messungs_id, tree_modified, letzte_aenderung}'::text[]); +SELECT audit_table('zusatz_wert', true, false, '{id, probe_id, tree_modified, letzte_aenderung}'::text[]); +SELECT audit_table('ortszuordnung', true, false, '{id, probe_id, tree_modified, letzte_aenderung}'::text[]); + +SET search_path TO public; diff -r 212fe0cfd694 -r 46aa15c8cfb2 db_schema/setup-db.sh --- a/db_schema/setup-db.sh Fri Mar 24 11:59:19 2017 +0100 +++ b/db_schema/setup-db.sh Mon Mar 27 16:31:11 2017 +0200 @@ -32,6 +32,9 @@ DB_NAME=${3:-$ROLE_NAME} echo "DB_NAME = $DB_NAME" +# Stop on error any execution of SQL via psql +DB_CONNECT_STRING="-v ON_ERROR_STOP=on " + # if variable DB_SRV and otional DB_PORT is set a remote database connection will be used if [ -n "$DB_SRV" ] ; then DB_CONNECT_STRING="-h $DB_SRV" ; fi if [ -n "$DB_SRV" -a -n "$DB_PORT" ] ; then @@ -63,6 +66,10 @@ echo create lada schema psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_schema.sql + +echo create audit-trail table/trigger/views +psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/audit.sql + echo set grants psql $DB_CONNECT_STRING -d $DB_NAME --command \ "GRANT USAGE ON SCHEMA stammdaten, land TO $ROLE_NAME; diff -r 212fe0cfd694 -r 46aa15c8cfb2 pom.xml --- a/pom.xml Fri Mar 24 11:59:19 2017 +0100 +++ b/pom.xml Mon Mar 27 16:31:11 2017 +0200 @@ -369,5 +369,5 @@ http://www.hibernatespatial.org/repository - 2.5 + 2.6-SNAPSHOT diff -r 212fe0cfd694 -r 46aa15c8cfb2 src/main/java/de/intevation/lada/model/land/AuditTrail.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src/main/java/de/intevation/lada/model/land/AuditTrail.java Mon Mar 27 16:31:11 2017 +0200 @@ -0,0 +1,109 @@ +package de.intevation.lada.model.land; + +import java.io.Serializable; +import java.sql.Timestamp; + +import javax.json.JsonObject; +import javax.persistence.Column; +import javax.persistence.Entity; +import javax.persistence.Id; +import javax.persistence.Table; + +import org.hibernate.annotations.Type; +import org.hibernate.annotations.TypeDef; +import org.hibernate.annotations.TypeDefs; + +import de.intevation.lada.util.data.JsonObjectType; + +/** + * The persistent class for the audit_trail database table. + * + */ +@Entity +@Table(name="audit_trail") +@TypeDefs({ @TypeDef(name = "JsonObject", typeClass = JsonObjectType.class) }) +public class AuditTrail implements Serializable { + private static final long serialVersionUID = 1L; + + @Id + private Long id; + + private String action; + + @Column(name="action_tstamp_clk") + private Timestamp actionTstampClk; + + @Column(name="changed_fields") + @Type(type="JsonObject") + private JsonObject changedFields; + + @Column(name="object_id") + private Integer objectId; + + @Column(name="row_data") + @Type(type="JsonObject") + private JsonObject rowData; + + @Column(name="table_name") + private String tableName; + + public AuditTrail() { + } + + public Long getId() { + return this.id; + } + + public void setId(Long id) { + this.id = id; + } + + public String getAction() { + return this.action; + } + + public void setAction(String action) { + this.action = action; + } + + public Timestamp getActionTstampClk() { + return this.actionTstampClk; + } + + public void setActionTstampClk(Timestamp actionTstampClk) { + this.actionTstampClk = actionTstampClk; + } + + public JsonObject getChangedFields() { + return this.changedFields; + } + + public void setChangedFields(JsonObject changedFields) { + this.changedFields = changedFields; + } + + public Integer getObjectId() { + return this.objectId; + } + + public void setObjectId(Integer objectId) { + this.objectId = objectId; + } + + public JsonObject getRowData() { + return this.rowData; + } + + public void setRowData(JsonObject rowData) { + this.rowData = rowData; + } + + public String getTableName() { + return this.tableName; + } + + public void setTableName(String tableName) { + this.tableName = tableName; + } + +} diff -r 212fe0cfd694 -r 46aa15c8cfb2 src/main/java/de/intevation/lada/model/land/AuditTrailMessung.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src/main/java/de/intevation/lada/model/land/AuditTrailMessung.java Mon Mar 27 16:31:11 2017 +0200 @@ -0,0 +1,122 @@ +package de.intevation.lada.model.land; + +import java.io.Serializable; +import java.sql.Timestamp; + +import javax.persistence.Column; +import javax.persistence.Entity; +import javax.persistence.Id; +import javax.persistence.Table; + +import org.hibernate.annotations.Type; +import org.hibernate.annotations.TypeDef; +import org.hibernate.annotations.TypeDefs; + +import com.fasterxml.jackson.databind.JsonNode; + +import de.intevation.lada.util.data.JsonObjectType; + + +/** + * The persistent class for the audit_trail_messung database table. + * + */ +@Entity +@Table(name="audit_trail_messung") +@TypeDefs({ @TypeDef(name = "JsonObject", typeClass = JsonObjectType.class) }) +public class AuditTrailMessung implements Serializable { + private static final long serialVersionUID = 1L; + + @Id + private Long id; + + private String action; + + @Column(name="tstamp") + private Timestamp tstamp; + + @Column(name="changed_fields") + @Type(type="JsonObject") + private JsonNode changedFields; + + @Column(name="messungs_id") + private String messungsId; + + @Column(name="object_id") + private Integer objectId; + + @Column(name="row_data") + @Type(type="JsonObject") + private JsonNode rowData; + + @Column(name="table_name") + private String tableName; + + public AuditTrailMessung() { + } + + public String getAction() { + return this.action; + } + + public void setAction(String action) { + this.action = action; + } + + public Timestamp getTstamp() { + return this.tstamp; + } + + public void setTstamp(Timestamp tstamp) { + this.tstamp = tstamp; + } + + public JsonNode getChangedFields() { + return this.changedFields; + } + + public void setChangedFields(JsonNode changedFields) { + this.changedFields = changedFields; + } + + public Long getId() { + return this.id; + } + + public void setId(Long id) { + this.id = id; + } + + public String getMessungsId() { + return this.messungsId; + } + + public void setMessungsId(String messungsId) { + this.messungsId = messungsId; + } + + public Integer getObjectId() { + return this.objectId; + } + + public void setObjectId(Integer objectId) { + this.objectId = objectId; + } + + public JsonNode getRowData() { + return this.rowData; + } + + public void setRowData(JsonNode rowData) { + this.rowData = rowData; + } + + public String getTableName() { + return this.tableName; + } + + public void setTableName(String tableName) { + this.tableName = tableName; + } + +} diff -r 212fe0cfd694 -r 46aa15c8cfb2 src/main/java/de/intevation/lada/model/land/AuditTrailProbe.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src/main/java/de/intevation/lada/model/land/AuditTrailProbe.java Mon Mar 27 16:31:11 2017 +0200 @@ -0,0 +1,126 @@ +package de.intevation.lada.model.land; + +import java.io.Serializable; +import java.sql.Timestamp; + +import javax.json.JsonObject; +import javax.persistence.Column; +import javax.persistence.Entity; +import javax.persistence.Id; +import javax.persistence.Table; + +import org.hibernate.annotations.Type; +import org.hibernate.annotations.TypeDef; +import org.hibernate.annotations.TypeDefs; + +import com.fasterxml.jackson.databind.JsonNode; + +import de.intevation.lada.util.data.JsonObjectType; + +/** + * The persistent class for the audit_trail_probe database table. + * + */ +@Entity +@Table(name="audit_trail_probe") +@TypeDefs({ @TypeDef(name = "JsonObject", typeClass = JsonObjectType.class) }) +public class AuditTrailProbe implements Serializable { + private static final long serialVersionUID = 1L; + + @Id + private Long id; + + @Column(name="changed_fields") + @Type(type="JsonObject") + private JsonNode changedFields; + + @Column(name="row_data") + @Type(type="JsonObject") + private JsonNode rowData; + + @Column(name="tstamp") + private Timestamp tstamp; + + @Column(name="action") + private String action; + + @Column(name="messungs_id") + private Integer messungsId; + + @Column(name="object_id") + private Integer objectId; + + @Column(name="probe_id") + private Integer probeId; + + @Column(name="table_name") + private String tableName; + + public AuditTrailProbe() { + } + + public JsonNode getChangedFields() { + return this.changedFields; + } + + public void setChangedFields(JsonNode changedFields) { + this.changedFields = changedFields; + } + + public JsonNode getRowData() { + return rowData; + } + + public void setRowData(JsonNode rowData) { + this.rowData = rowData; + } + + public Timestamp getTstamp() { + return tstamp; + } + + public void setTstamp(Timestamp tstamp) { + this.tstamp = tstamp; + } + + public String getAction() { + return action; + } + + public void setAction(String action) { + this.action = action; + } + + public Integer getMessungsId() { + return this.messungsId; + } + + public void setMessungsId(Integer messungsId) { + this.messungsId = messungsId; + } + + public Integer getObjectId() { + return this.objectId; + } + + public void setObjectId(Integer objectId) { + this.objectId = objectId; + } + + public Integer getProbeId() { + return this.probeId; + } + + public void setProbeId(Integer probeId) { + this.probeId = probeId; + } + + public String getTableName() { + return this.tableName; + } + + public void setTableName(String tableName) { + this.tableName = tableName; + } + +} diff -r 212fe0cfd694 -r 46aa15c8cfb2 src/main/java/de/intevation/lada/model/land/Messprogramm.java --- a/src/main/java/de/intevation/lada/model/land/Messprogramm.java Fri Mar 24 11:59:19 2017 +0100 +++ b/src/main/java/de/intevation/lada/model/land/Messprogramm.java Mon Mar 27 16:31:11 2017 +0200 @@ -64,7 +64,7 @@ @Column(name="labor_mst_id") private String laborMstId; - @Column(name="letzte_aenderung") + @Column(name="letzte_aenderung", insertable=false) private Timestamp letzteAenderung; @Column(name="media_desk") diff -r 212fe0cfd694 -r 46aa15c8cfb2 src/main/java/de/intevation/lada/model/land/MessprogrammMmt.java --- a/src/main/java/de/intevation/lada/model/land/MessprogrammMmt.java Fri Mar 24 11:59:19 2017 +0100 +++ b/src/main/java/de/intevation/lada/model/land/MessprogrammMmt.java Mon Mar 27 16:31:11 2017 +0200 @@ -29,7 +29,7 @@ @GeneratedValue(strategy=GenerationType.IDENTITY) private Integer id; - @Column(name="letzte_aenderung") + @Column(name="letzte_aenderung", insertable=false) private Timestamp letzteAenderung; @Type(type="IntegerArray") diff -r 212fe0cfd694 -r 46aa15c8cfb2 src/main/java/de/intevation/lada/model/land/Messung.java --- a/src/main/java/de/intevation/lada/model/land/Messung.java Fri Mar 24 11:59:19 2017 +0100 +++ b/src/main/java/de/intevation/lada/model/land/Messung.java Mon Mar 27 16:31:11 2017 +0200 @@ -38,7 +38,7 @@ @Column(name="id_alt") private Integer idAlt; - @Column(name="letzte_aenderung") + @Column(name="letzte_aenderung", insertable=false) private Timestamp letzteAenderung; private Integer messdauer; diff -r 212fe0cfd694 -r 46aa15c8cfb2 src/main/java/de/intevation/lada/model/land/Messwert.java --- a/src/main/java/de/intevation/lada/model/land/Messwert.java Fri Mar 24 11:59:19 2017 +0100 +++ b/src/main/java/de/intevation/lada/model/land/Messwert.java Mon Mar 27 16:31:11 2017 +0200 @@ -27,7 +27,7 @@ private Boolean grenzwertueberschreitung; - @Column(name="letzte_aenderung") + @Column(name="letzte_aenderung", insertable=false) private Timestamp letzteAenderung; @Column(name="meh_id") diff -r 212fe0cfd694 -r 46aa15c8cfb2 src/main/java/de/intevation/lada/model/land/Ortszuordnung.java --- a/src/main/java/de/intevation/lada/model/land/Ortszuordnung.java Fri Mar 24 11:59:19 2017 +0100 +++ b/src/main/java/de/intevation/lada/model/land/Ortszuordnung.java Mon Mar 27 16:31:11 2017 +0200 @@ -25,7 +25,7 @@ @GeneratedValue(strategy=GenerationType.IDENTITY) private Integer id; - @Column(name="letzte_aenderung") + @Column(name="letzte_aenderung", insertable=false) private Timestamp letzteAenderung; @Column(name="ort_id") diff -r 212fe0cfd694 -r 46aa15c8cfb2 src/main/java/de/intevation/lada/model/land/OrtszuordnungMp.java --- a/src/main/java/de/intevation/lada/model/land/OrtszuordnungMp.java Fri Mar 24 11:59:19 2017 +0100 +++ b/src/main/java/de/intevation/lada/model/land/OrtszuordnungMp.java Mon Mar 27 16:31:11 2017 +0200 @@ -32,7 +32,7 @@ @GeneratedValue(strategy=GenerationType.IDENTITY) private Integer id; - @Column(name="letzte_aenderung") + @Column(name="letzte_aenderung", insertable=false) private Timestamp letzteAenderung; @Column(name="messprogramm_id") diff -r 212fe0cfd694 -r 46aa15c8cfb2 src/main/java/de/intevation/lada/model/land/Probe.java --- a/src/main/java/de/intevation/lada/model/land/Probe.java Fri Mar 24 11:59:19 2017 +0100 +++ b/src/main/java/de/intevation/lada/model/land/Probe.java Mon Mar 27 16:31:11 2017 +0200 @@ -44,7 +44,7 @@ @Column(name="labor_mst_id") private String laborMstId; - @Column(name="letzte_aenderung") + @Column(name="letzte_aenderung", insertable=false) private Timestamp letzteAenderung; private String media; diff -r 212fe0cfd694 -r 46aa15c8cfb2 src/main/java/de/intevation/lada/model/land/ZusatzWert.java --- a/src/main/java/de/intevation/lada/model/land/ZusatzWert.java Fri Mar 24 11:59:19 2017 +0100 +++ b/src/main/java/de/intevation/lada/model/land/ZusatzWert.java Mon Mar 27 16:31:11 2017 +0200 @@ -27,7 +27,7 @@ @GeneratedValue(strategy=GenerationType.IDENTITY) private Integer id; - @Column(name="letzte_aenderung") + @Column(name="letzte_aenderung", insertable=false) private Timestamp letzteAenderung; private Float messfehler; diff -r 212fe0cfd694 -r 46aa15c8cfb2 src/main/java/de/intevation/lada/rest/AuditTrailService.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src/main/java/de/intevation/lada/rest/AuditTrailService.java Mon Mar 27 16:31:11 2017 +0200 @@ -0,0 +1,391 @@ +/* Copyright (C) 2013 by Bundesamt fuer Strahlenschutz + * Software engineering by Intevation GmbH + * + * This file is Free Software under the GNU GPL (v>=3) + * and comes with ABSOLUTELY NO WARRANTY! Check out + * the documentation coming with IMIS-Labordaten-Application for details. + */ +package de.intevation.lada.rest; + +import java.text.DateFormat; +import java.text.ParseException; +import java.text.SimpleDateFormat; +import java.util.Date; +import java.util.HashMap; +import java.util.Iterator; +import java.util.List; +import java.util.Map; + +import javax.annotation.PostConstruct; +import javax.enterprise.context.RequestScoped; +import javax.inject.Inject; +import javax.persistence.EntityManager; +import javax.servlet.http.HttpServletRequest; +import javax.ws.rs.GET; +import javax.ws.rs.Path; +import javax.ws.rs.PathParam; +import javax.ws.rs.Produces; +import javax.ws.rs.core.Context; +import javax.ws.rs.core.MediaType; + +import org.apache.log4j.Logger; + +import com.fasterxml.jackson.databind.ObjectMapper; +import com.fasterxml.jackson.databind.node.ArrayNode; +import com.fasterxml.jackson.databind.node.ObjectNode; + +import de.intevation.lada.model.land.AuditTrailMessung; +import de.intevation.lada.model.land.AuditTrailProbe; +import de.intevation.lada.model.land.Messung; +import de.intevation.lada.model.land.Probe; +import de.intevation.lada.util.annotation.AuthorizationConfig; +import de.intevation.lada.util.annotation.RepositoryConfig; +import de.intevation.lada.util.auth.Authorization; +import de.intevation.lada.util.auth.AuthorizationType; +import de.intevation.lada.util.data.QueryBuilder; +import de.intevation.lada.util.data.Repository; +import de.intevation.lada.util.data.RepositoryType; + +/** + * REST service for AuditTrail. + *

+ * The services produce data in the application/json media type. + * All HTTP methods use the authorization module to determine if the user is + * allowed to perform the requested action. + * A typical response holds information about the action performed and the data. + * 

+ * 
+ * {
+ *  "success": [boolean];
+ *  "message": [string],
+ *  "data":[{
+ *      "id": [number],
+ *      "identifier: [string]
+ *      "audit": [array]
+ *  }],
+ * }
+ * 
+ *
+ * + * @author Raimund Renkert + */ +@Path("rest/audit") +@RequestScoped +public class AuditTrailService { + + /** + * Class to store tablename and value field for foreign key mappings. + */ + private class TableMapper { + public String mappingTable; + public String valueField; + + public TableMapper( + String mappingTable, + String valueField + ) { + this.mappingTable = mappingTable; + this.valueField = valueField; + } + } + + @Inject Logger logger; + /** + * The data repository granting read/write access. + */ + @Inject + @RepositoryConfig(type=RepositoryType.RO) + private Repository repository; + + /** + * The authorization module. + */ + @Inject + @AuthorizationConfig(type=AuthorizationType.HEADER) + private Authorization authorization; + + /** + * Map foreign key to their associated table and the display value. + */ + private Map mappings; + + /** + * Initialize the object with key <-> table mappings. + */ + @PostConstruct + public void initialize() { + mappings = new HashMap(); + mappings.put("messgroesse_id", + new TableMapper("messgroesse", "messgroesse")); + mappings.put("meh_id", + new TableMapper("mess_einheit", "einheit")); + mappings.put("ort_id", + new TableMapper("ort", "ort_id")); + mappings.put("datenbasis_id", + new TableMapper("datenbasis", "datenbasis")); + mappings.put("ba_id", + new TableMapper("betriebsart", "name")); + mappings.put("mpl_id", + new TableMapper("messprogramm_kategorie", "code")); + mappings.put("probenart_id", + new TableMapper("probenart", "probenart")); + mappings.put("probe_nehmer_id", + new TableMapper("probenehmer", "prn_id")); + mappings.put("probeentnahme_beginn", + new TableMapper("date", "dd.MM.yy HH:mm")); + mappings.put("probeentnahme_ende", + new TableMapper("date", "dd.MM.yy HH:mm")); + } + + /** + * Service to generate audit trail for probe objects. + */ + @GET + @Path("/probe/{id}") + @Produces(MediaType.APPLICATION_JSON) + public String getProbe( + @Context HttpServletRequest request, + @PathParam("id") String id + ) { + if (id == null || "".equals(id)) { + String ret = "{\"success\": false," + + "\"message\":698,\"data\":null}"; + return ret; + } + + Integer pId = null; + String ret = "{\"success\": false," + + "\"message\":600,\"data\":null}"; + try { + pId = Integer.valueOf(id); + } + catch(NumberFormatException nfe) { + return ret; + } + // Get the plain probe object to have the hauptproben_nr. + Probe probe = repository.getByIdPlain(Probe.class, pId, "land"); + if (probe == null) { + return ret; + } + + // Get all entries for the probe and its sub objects. + QueryBuilder builder = + new QueryBuilder( + repository.entityManager("land"), + AuditTrailProbe.class); + builder.and("objectId", id); + builder.and("tableName", "probe"); + builder.or("probeId", id); + builder.orderBy("tstamp", true); + List audit = + repository.filterPlain(builder.getQuery(), "land"); + + // Create an empty JsonObject + ObjectMapper mapper = new ObjectMapper(); + ObjectNode responseNode = mapper.createObjectNode(); + responseNode.put("success", true); + responseNode.put("message", 200); + ObjectNode auditJson = responseNode.putObject("data"); + ArrayNode entries = auditJson.putArray("audit"); + auditJson.put("id", probe.getId()); + auditJson.put("identifier", probe.getHauptprobenNr()); + for (AuditTrailProbe a : audit) { + entries.add(createEntry(a, mapper)); + } + return responseNode.toString(); + } + + /** + * Create a JSON object for an AuditTrailProbe entry. + * + * @param audit The table entry + * @param mapper JSON object mapper + */ + private ObjectNode createEntry(AuditTrailProbe audit, ObjectMapper mapper) { + ObjectNode node = mapper.createObjectNode(); + node.put("timestamp", audit.getTstamp().getTime()); + node.put("type", audit.getTableName()); + node.put("action", audit.getAction()); + ObjectNode data = (ObjectNode)audit.getChangedFields(); + data = translateValues(data); + node.putPOJO("changedFields", data); + if ("kommentar_p".equals(audit.getTableName())) { + node.put("identifier", audit.getRowData().get("datum").toString()); + } + if ("zusatz_wert".equals(audit.getTableName())) { + node.put("identifier", audit.getRowData().get("pzs_id").toString()); + } + if ("ortszuordnung".equals(audit.getTableName())) { + String value = translateId( + "ort", + "ort_id", + audit.getRowData().get("ort_id").toString(), + "id", + "stamm"); + node.put("identifier", value); + } + if ("messung".equals(audit.getTableName())) { + logger.debug("npr: " + audit.getRowData()); + node.put("identifier", + audit.getRowData() + .get("nebenproben_nr").toString().replaceAll("\"", "")); + } + if (audit.getMessungsId() != null) { + Messung m = repository.getByIdPlain( + Messung.class, audit.getMessungsId(), "land"); + ObjectNode identifier = node.putObject("identifier"); + identifier.put("messung", m.getNebenprobenNr()); + if ("kommentar_m".equals(audit.getTableName())) { + identifier.put("identifier", + audit.getRowData().get("datum").toString()); + } + if ("messwert".equals(audit.getTableName())) { + String value = translateId( + "messgroesse", + "messgroesse", + audit.getRowData().get("messgroesse_id").toString(), + "id", + "stamm"); + identifier.put("identifier", value); + } + } + return node; + } + + /** + * Service to generate audit trail for messung objects. + */ + @GET + @Path("/messung/{id}") + @Produces(MediaType.APPLICATION_JSON) + public String getMessung( + @Context HttpServletRequest request, + @PathParam("id") String id + ) { + if (id == null || "".equals(id)) { + String ret = "{\"success\": false," + + "\"message\":698,\"data\":null}"; + return ret; + } + + Integer mId = null; + String ret = "{\"success\": false," + + "\"message\":600,\"data\":null}"; + try { + mId = Integer.valueOf(id); + } + catch(NumberFormatException nfe) { + return ret; + } + Messung messung = repository.getByIdPlain(Messung.class, mId, "land"); + if (messung == null) { + return ret; + } + + QueryBuilder builder = + new QueryBuilder( + repository.entityManager("land"), + AuditTrailMessung.class); + builder.and("objectId", mId); + builder.and("tableName", "messung"); + builder.or("messungsId", mId); + builder.orderBy("tstamp", true); + List audit = + repository.filterPlain(builder.getQuery(), "land"); + + // Create an empty JsonObject + ObjectMapper mapper = new ObjectMapper(); + ObjectNode responseNode = mapper.createObjectNode(); + responseNode.put("success", true); + responseNode.put("message", 200); + ObjectNode auditJson = responseNode.putObject("data"); + ArrayNode entries = auditJson.putArray("audit"); + auditJson.put("id", messung.getId()); + auditJson.put("identifier", messung.getNebenprobenNr()); + for (AuditTrailMessung a : audit) { + entries.add(createEntry(a, mapper)); + } + return responseNode.toString(); + } + + /** + * Create a JSON object for an AuditTrailMessung entry. + * + * @param audit The table entry + * @param mapper JSON object mapper + */ + private ObjectNode createEntry(AuditTrailMessung audit, ObjectMapper mapper) { + ObjectNode node = mapper.createObjectNode(); + node.put("timestamp", audit.getTstamp().getTime()); + node.put("type", audit.getTableName()); + node.put("action", audit.getAction()); + ObjectNode data = (ObjectNode)audit.getChangedFields(); + node.putPOJO("changedFields", data); + if ("kommentar_m".equals(audit.getTableName())) { + node.put("identifier", audit.getRowData().get("datum").toString()); + } + if ("messwert".equals(audit.getTableName())) { + String value = translateId( + "messgroesse", + "messgroesse", + audit.getRowData().get("messgroesse_id").toString(), + "id", + "stamm"); + node.put("identifier", value); + } + return node; + } + + /** + * Translate a foreign key into the associated value. + */ + private String translateId( + String table, + String field, + String id, + String idField, + String source + ) { + EntityManager manager = repository.entityManager(source); + String sql = "SELECT " + field + " FROM " + table + + " WHERE " + idField + " = " + id + ";"; + javax.persistence.Query query = manager.createNativeQuery(sql); + List result = query.getResultList(); + return result.get(0); + } + + private Long formatDate(String format, String date) { + DateFormat inFormat = new SimpleDateFormat("yyyy-MM-dd'T'hh:mm:ssXXX"); + try { + return inFormat.parse(date).getTime(); + } catch (ParseException e) { + return 0L; + } + } + + /** + * Translate all known foreign keys + */ + private ObjectNode translateValues(ObjectNode node) { + for (Iterator i = node.fieldNames(); i.hasNext();) { + String key = i.next(); + if (mappings.containsKey(key)) { + TableMapper m = mappings.get(key); + if (m.mappingTable.equals("date")) { + Long value = formatDate(m.valueField, node.get(key).asText()); + node.put(key, value); + } + else { + String value = translateId( + m.mappingTable, + m.valueField, + node.get(key).asText(), + "id", + "stamm"); + node.put(key, value); + } + } + } + return node; + } +} diff -r 212fe0cfd694 -r 46aa15c8cfb2 src/main/java/de/intevation/lada/util/auth/HeaderAuthorization.java --- a/src/main/java/de/intevation/lada/util/auth/HeaderAuthorization.java Fri Mar 24 11:59:19 2017 +0100 +++ b/src/main/java/de/intevation/lada/util/auth/HeaderAuthorization.java Mon Mar 27 16:31:11 2017 +0200 @@ -137,9 +137,8 @@ return data; } Authorizer authorizer = authorizers.get(clazz); - //This is a hack... Allows wildcard for unknown classes. if (authorizer == null) { - return data; + return new Response(false, 699, null); } return authorizer.filter(data, userInfo, clazz); } @@ -242,9 +241,8 @@ Class clazz ) { Authorizer authorizer = authorizers.get(clazz); - //This is a hack... Allows wildcard for unknown classes. if (authorizer == null) { - return true; + return false; } return authorizer.isAuthorized(data, RequestMethod.GET, userInfo, clazz); } @@ -263,9 +261,8 @@ Class clazz ) { Authorizer authorizer = authorizers.get(clazz); - //This is a hack... Allows wildcard for unknown classes. if (authorizer == null) { - return true; + return false; } return authorizer.isAuthorized(data, RequestMethod.POST, userInfo, clazz); } diff -r 212fe0cfd694 -r 46aa15c8cfb2 src/main/java/de/intevation/lada/util/data/JsonObjectType.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src/main/java/de/intevation/lada/util/data/JsonObjectType.java Mon Mar 27 16:31:11 2017 +0200 @@ -0,0 +1,207 @@ +/* Copyright (C) 2013 by Bundesamt fuer Strahlenschutz + * Software engineering by Intevation GmbH + * + * This file is Free Software under the GNU GPL (v>=3) + * and comes with ABSOLUTELY NO WARRANTY! Check out + * the documentation coming with IMIS-Labordaten-Application for details. + */ +package de.intevation.lada.util.data; + +import java.io.IOException; +import java.io.Serializable; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Types; + +import org.hibernate.HibernateException; +import org.hibernate.engine.spi.SessionImplementor; +import org.hibernate.usertype.UserType; + +import com.fasterxml.jackson.databind.JsonNode; +import com.fasterxml.jackson.databind.ObjectMapper; + +/** + * Implementation for a new data type in the postgresql/postgis jdbc driver. + * + * @author Raimund Renkert + */ +public class JsonObjectType implements UserType { + /** + * Reconstruct an object from the cacheable representation. At the very + * least this method should perform a deep copy if the type is mutable. + * (optional operation) + * + * @param cached + * the object to be cached + * @param owner + * the owner of the cached object + * @return a reconstructed object from the cachable representation + * @throws HibernateException + */ + @Override + public Object assemble(Serializable cached, Object owner) throws HibernateException { + return this.deepCopy(cached); + } + + /** + * Return a deep copy of the persistent state, stopping at entities and st + * collections. It is not necessary to copy immutable objects, or null + * values, in which case it is safe to simple return the argument. + * + * @param value + * the object to be cloned, which may be null + * + * @return object a copy + * @throws HibernateException + */ + @Override + public Object deepCopy(Object value) throws HibernateException { + return value; + } + + /** + * Transform the object into its cacheable representation. At the very least + * this method should perform a deep copy if the type is mutable. That may + * not be enough for some implementations, however; for example, + * associations must be cached as identifier values. (optional operation) + * + * @param value + * the object to be cached + * @return a cachable representation of the object + * @throws HibernateException + */ + @Override + public Serializable disassemble(Object value) throws HibernateException { + return (String) this.deepCopy(value); + } + + /** + * Compare two instances of the class mapped by this type for persistence + * "equality". Equality of the persistence state. + * + * @param x + * @param y + * @return boolean + * @throws HibernateException + */ + @Override + public boolean equals(Object x, Object y) throws HibernateException { + if (x == null) { + return y == null; + } + return x.equals(y); + } + + /** + * Get a hashcode for the instance, consistent with persistence "equality". + */ + @Override + public int hashCode(Object x) throws HibernateException { + return x.hashCode(); + } + + /** + * Are objects of this type mutable? + * + * @return boolean + */ + @Override + public boolean isMutable() { + return true; + } + + /** + * Retrieve an instance of the mapped class from a JDBC resultset. + * Implementors should handle possibility of null values. + * + * @param rs + * a JDBC result set + * @param names + * the column names + * @param session + * @param owner + * the containing entity + * @return + * @throws HibernateException + * @throws SQLException + */ + @Override + public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor session, Object owner) throws HibernateException, SQLException { + ObjectMapper mapper = new ObjectMapper(); + JsonNode node = null; + try { + node = mapper.readTree(rs.getString(names[0])); + return node; + } catch (IOException e) { + e.printStackTrace(); + } + return mapper.createObjectNode(); + } + + /** + * Write an instance of the mapped class to a prepared statement. + * Implementors should handle possibility of null values. A multi-column + * type should be written to parameters starting from index + * + * @param st + * a JDBC prepared statement + * @param value + * the object to write + * @param index + * statement parameter index + * @param session + * @throws HibernateException + * @throws SQLException + */ + @Override + public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session) throws HibernateException, SQLException { + if (value == null) { + st.setNull(index, Types.OTHER); + return; + } + st.setObject(index, value, Types.OTHER); + } + + /** + * During merge, replace the existing (target) values in the entity we are + * merging to with a new (original) value from the detched entity we are + * merging. For immutable objects, or null values, it is safe to return a + * copy of the first parameter. For the objects with component values, it + * might make sense to recursively replace component values + * + * @param original + * the value from the detched entity being merged + * @param target + * the value in the managed entity + * @param owner + * @return the value to be merged + * @throws HibernateException + */ + @Override + public Object replace(Object original, Object target, Object owner) throws HibernateException { + return original; + } + + /** + * The class returned by nullSafeGet() + * + * @return Class + */ + @Override + public Class returnedClass() { + return String.class; + } + + /** + * Returns the SQL type codes for the columns mapped by this type. The codes + * are defined on java.sql.Types + * + * @return int[] the typecodes + * @see java.sql.Types + */ + @Override + public int[] sqlTypes() { + return new int[] { Types.JAVA_OBJECT }; + } +} diff -r 212fe0cfd694 -r 46aa15c8cfb2 src/main/java/de/intevation/lada/util/data/LadaPostgisDialect.java --- a/src/main/java/de/intevation/lada/util/data/LadaPostgisDialect.java Fri Mar 24 11:59:19 2017 +0100 +++ b/src/main/java/de/intevation/lada/util/data/LadaPostgisDialect.java Mon Mar 27 16:31:11 2017 +0200 @@ -23,5 +23,6 @@ public LadaPostgisDialect() { super(); this.registerColumnType(Types.ARRAY, "integer[]"); + this.registerColumnType(Types.JAVA_OBJECT, "json"); } }