changeset 927:690b6506e0a2

merged.
author Raimund Renkert <raimund.renkert@intevation.de>
date Fri, 29 Apr 2016 16:40:26 +0200 (2016-04-29)
parents 9121d99a471e (current diff) 2b746f4e97d6 (diff)
children 6ab17d8b5558 b0fcb034fdb0
files db_schema/lada_schema.sql
diffstat 4 files changed, 44 insertions(+), 12 deletions(-) [+]
line wrap: on
line diff
--- 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();
--- 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
--- 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
-
 \.
 
 
--- 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
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)