# HG changeset patch # User Raimund Renkert # Date 1461940826 -7200 # Node ID 690b6506e0a2046a896c810f6353fb84229b9029 # Parent 9121d99a471eef8018e8baabc49345249b099d00# Parent 2b746f4e97d6804f1ee5f569f874dea312053de8 merged. diff -r 9121d99a471e -r 690b6506e0a2 db_schema/lada_schema.sql --- a/db_schema/lada_schema.sql Fri Apr 29 16:38:33 2016 +0200 +++ b/db_schema/lada_schema.sql Fri Apr 29 16:40:26 2016 +0200 @@ -399,7 +399,8 @@ probeentnahme_beginn timestamp with time zone, probeentnahme_ende timestamp with time zone, mittelungsdauer bigint, - letzte_aenderung timestamp without time zone DEFAULT now() + letzte_aenderung timestamp without time zone DEFAULT now(), + UNIQUE (mst_id, hauptproben_nr) ); CREATE TRIGGER letzte_aenderung_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); diff -r 9121d99a471e -r 690b6506e0a2 db_schema/setup-db.sh --- a/db_schema/setup-db.sh Fri Apr 29 16:38:33 2016 +0200 +++ b/db_schema/setup-db.sh Fri Apr 29 16:40:26 2016 +0200 @@ -2,23 +2,55 @@ DIR=`dirname $0` ROLE_NAME=${1:-lada} +echo "DROLE_NAME = $ROLE_NAME=" ROLE_PW=${2:-$ROLE_NAME} +echo "ROLE_PW = $ROLE_PW" DB_NAME=${3:-$ROLE_NAME} +echo "DB_NAME = $DB_NAME" -psql --command "CREATE USER $ROLE_NAME PASSWORD '$ROLE_PW';" -createdb -E UTF-8 $DB_NAME +# 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 + DB_CONNECT_STRING="$DB_CONNECT_STRING -p $DB_PORT" +fi +DB_CONNECT_STRING="$DB_CONNECT_STRING -U postgres" +echo "DB_CONNECT_STRING = $DB_CONNECT_STRING" -psql -d $DB_NAME --command \ +if [ `psql $DB_CONNECT_STRING -t --command "SELECT count(*) FROM pg_catalog.pg_user WHERE usename = '$ROLE_NAME'"` -eq 0 ] ; then + echo create user $ROLE_NAME + psql $DB_CONNECT_STRING --command "CREATE USER $ROLE_NAME PASSWORD '$ROLE_PW';" +fi +exit + +if psql -h test-pgsql1-fr.lab.bfs.de -U postgres -l | grep -q "^ $DB_NAME " ; then + echo drop db $DB_NAME + psql $DB_CONNECT_STRING --command "DROP DATABASE $DB_NAME" +fi + +echo create db $DB_NAME +psql $DB_CONNECT_STRING --command \ + "CREATE DATABASE $DB_NAME WITH OWNER = $ROLE_NAME ENCODING = 'UTF8'" +exit + +echo create postgis extention +psql $DB_CONNECT_STRING -d $DB_NAME --command \ "CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public" -psql -d $DB_NAME -f $DIR/stammdaten_schema.sql -psql -d $DB_NAME -f $DIR/lada_schema.sql -psql -d $DB_NAME --command \ +echo create stammdaten schema +psql $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_schema.sql + +echo create lada schema +psql $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_schema.sql +echo set grants +psql $DB_CONNECT_STRING -d $DB_NAME --command \ "GRANT USAGE ON SCHEMA stammdaten, bund, land TO $ROLE_NAME; GRANT USAGE ON ALL SEQUENCES IN SCHEMA stammdaten, bund, land TO $ROLE_NAME; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON ALL TABLES IN SCHEMA stammdaten, bund, land TO $ROLE_NAME;" -psql -d $DB_NAME -f $DIR/stammdaten_data.sql -psql -d $DB_NAME -f $DIR/lada_data.sql +echo import stammdaten +psql $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_data.sql + +echo import lada test data +psql $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_data.sql diff -r 9121d99a471e -r 690b6506e0a2 db_schema/stammdaten_data.sql --- a/db_schema/stammdaten_data.sql Fri Apr 29 16:38:33 2016 +0200 +++ b/db_schema/stammdaten_data.sql Fri Apr 29 16:40:26 2016 +0200 @@ -21997,7 +21997,7 @@ 4 MST und UMW probe SELECT probe.id AS id, probe.hauptproben_nr AS hpNr, datenbasis.datenbasis AS dBasis, probe.netzbetreiber_id AS netzId, probe.mst_id AS mstId, probe.umw_id AS umwId, probenart.probenart AS pArt, to_char(probe.probeentnahme_beginn, 'dd.mm.YYYY hh24:MI') AS peBegin, to_char(probe.probeentnahme_ende, 'dd.mm.YYYY hh24:MI') AS peEnd, ort.ort_id AS ortId, ort.gem_id AS eGemId, verwaltungseinheit.bezeichnung AS eGem, probe_translation.probe_id_alt AS probeId FROM land.probe LEFT JOIN stammdaten.datenbasis ON (probe.datenbasis_id = datenbasis.id) LEFT JOIN stammdaten.probenart ON (probe.probenart_id = probenart.id) LEFT OUTER JOIN land.ortszuordnung ON ( probe.id = ortszuordnung.probe_id AND ortszuordnung.ortszuordnung_typ = 'E' ) LEFT OUTER JOIN stammdaten.ort ON (ortszuordnung.ort_id = ort.id) LEFT OUTER JOIN stammdaten.verwaltungseinheit ON (ort.gem_id = verwaltungseinheit.id) LEFT OUTER JOIN land.probe_translation ON (probe.id = probe_translation.probe_id)WHERE (probe.mst_id = :mstIdFilter OR '' = :mstIdFilter) AND (probe.umw_id = :umwIdFilter OR '' = :umwIdFilter) Abfrage der Proben gefiltert nach Messtellen ID und ID des Umweltbereichs 5 Proben pro Land probe SELECT probe.id AS id, probe.hauptproben_nr AS hpNr, datenbasis.datenbasis AS dBasis, probe.netzbetreiber_id AS netzId, probe.mst_id AS mstId, probe.umw_id AS umwId, probenart.probenart AS pArt, to_char(probe.probeentnahme_beginn, 'dd.mm.YYYY hh24:MI') AS peBegin, to_char(probe.probeentnahme_ende, 'dd.mm.YYYY hh24:MI') AS peEnd, ort.ort_id AS ortId, ort.gem_id AS eGemId, verwaltungseinheit.bezeichnung AS eGem, probe_translation.probe_id_alt AS probeId FROM land.probe LEFT JOIN stammdaten.datenbasis ON (probe.datenbasis_id = datenbasis.id) LEFT JOIN stammdaten.probenart ON (probe.probenart_id = probenart.id) LEFT OUTER JOIN land.ortszuordnung ON ( probe.id = ortszuordnung.probe_id AND ortszuordnung.ortszuordnung_typ = 'E' ) LEFT OUTER JOIN stammdaten.ort ON (ortszuordnung.ort_id = ort.id) LEFT OUTER JOIN stammdaten.verwaltungseinheit ON (ort.gem_id = verwaltungseinheit.id) LEFT OUTER JOIN land.probe_translation ON (probe.id = probe_translation.probe_id) WHERE (probe.netzbetreiber_id = :netzIdFilter OR '' = :netzIdFilter) Proben gefiltert nach Ländern 7 Proben pro Land und UMW (Multiselect) probe SELECT probe.id AS id, probe.hauptproben_nr AS hpNr, datenbasis.datenbasis AS dBasis, probe.netzbetreiber_id AS netzId, probe.mst_id AS mstId, probe.umw_id AS umwId, probenart.probenart AS pArt, to_char(probe.probeentnahme_beginn, 'dd.mm.YYYY hh24:MI') AS peBegin, to_char(probe.probeentnahme_ende, 'dd.mm.YYYY hh24:MI') AS peEnd, ort.ort_id AS ortId, ort.gem_id AS eGemId, verwaltungseinheit.bezeichnung AS eGem, probe_translation.probe_id_alt AS probeId FROM land.probe LEFT JOIN stammdaten.datenbasis ON (probe.datenbasis_id = datenbasis.id) LEFT JOIN stammdaten.probenart ON (probe.probenart_id = probenart.id) LEFT OUTER JOIN land.ortszuordnung ON ( probe.id = ortszuordnung.probe_id AND ortszuordnung.ortszuordnung_typ = 'E' ) LEFT OUTER JOIN stammdaten.ort ON (ortszuordnung.ort_id = ort.id) LEFT OUTER JOIN stammdaten.verwaltungseinheit ON (ort.gem_id = verwaltungseinheit.id) LEFT OUTER JOIN land.probe_translation ON (probe.id = probe_translation.probe_id) WHERE (probe.netzbetreiber_id = :netzIdFilter OR '' =:netzIdFilter) AND (probe.umw_id similar to (:umwIdFilter) OR '' = :umwIdFilter) Abfrage aller Proben gefiltert pro Land und Umweltbereich (mit Mehrfachauswahl) -15 kein Filter messung SELECT m.id, p.id as probe_id, p.hauptproben_nr as hauptprobenNr, m.nebenproben_nr as nebenprobenNr, s.status_wert as statusWert, s.status_stufe from land.probe p join land.messung m on p.id = m.probe_id join land.status_protokoll s on m.status = s.id +15 kein Filter messung SELECT m.id, p.id as probe_id, p.hauptproben_nr as hauptprobenNr, m.nebenproben_nr as nebenprobenNr, s.status_wert as statusWert, s.status_stufe from land.probe p join land.messung m on p.id = m.probe_id join land.status_protokoll s on m.status = s.id kein Filter \. @@ -36950,7 +36950,6 @@ 145 15 statusStufe Stufe 100 FALSE 4 146 15 probeId Probe Id 100 FALSE 0 147 15 hauptprobenNr Hauptproben Nr 100 FALSE 1 - \. diff -r 9121d99a471e -r 690b6506e0a2 src/main/webapp/WEB-INF/classes/log4j.properties --- a/src/main/webapp/WEB-INF/classes/log4j.properties Fri Apr 29 16:38:33 2016 +0200 +++ b/src/main/webapp/WEB-INF/classes/log4j.properties Fri Apr 29 16:40:26 2016 +0200 @@ -3,7 +3,7 @@ log4j.logger.org.openid4java=WARN log4j.logger.org.hibernate=ERROR log4j.appender.lada=org.apache.log4j.RollingFileAppender -log4j.appender.lada.File=/usr/src/lada-server/log/lada-server.log +log4j.appender.lada.File=/var/log/wildfly/lada-server.log log4j.appender.lada.MaxFileSize=20MB log4j.appender.lada.MaxBackupIndex=5 log4j.appender.lada.layout=org.apache.log4j.PatternLayout