From 8e96605c2a4301a7c8e7bb5b1a2a4bc0b26daa34 Mon Sep 17 00:00:00 2001 From: shentao999 Date: Wed, 10 Apr 2019 14:38:03 +0800 Subject: Change database to postgreSQL9.5 Change-Id: I859d0ae4173d41e1055d88518aac0f9a7f50d01a Issue-ID: USECASEUI-236 Signed-off-by: zhangab --- standalone/src/main/assembly/Dockerfile | 47 ++++--- standalone/src/main/assembly/bin/run.sh | 34 ++--- .../src/main/assembly/resources/bin/initDB.sh | 50 ++++--- .../resources/dbscripts/postgres/uui_create_db.sql | 26 ++++ .../dbscripts/postgres/uui_create_table.sql | 149 +++++++++++++++++++++ 5 files changed, 238 insertions(+), 68 deletions(-) create mode 100644 standalone/src/main/assembly/resources/dbscripts/postgres/uui_create_db.sql create mode 100644 standalone/src/main/assembly/resources/dbscripts/postgres/uui_create_table.sql diff --git a/standalone/src/main/assembly/Dockerfile b/standalone/src/main/assembly/Dockerfile index 4d5cbe65..9d80e0ee 100644 --- a/standalone/src/main/assembly/Dockerfile +++ b/standalone/src/main/assembly/Dockerfile @@ -1,47 +1,52 @@ FROM ubuntu:16.04 - MAINTAINER "Lu Ji" +# Expose the Usecase-UI backend port and postgreSQL port EXPOSE 8082 -EXPOSE 3306 - -ARG HTTP_PROXY=${HTTP_PROXY} -ARG HTTPS_PROXY=${HTTPS_PROXY} +EXPOSE 5432 -ENV http_proxy $HTTP_PROXY -ENV https_proxy $HTTPS_PROXY +# set env parameters +ENV PG_HOME=/etc/postgresql \ + PG_VERSION=9.5 \ + PG_VAR_LIB=/var/lib/postgresql \ + PG_USR_LIB=/usr/lib/postgresql \ + PG_LOGDIR=/var/log/postgresql -RUN echo "mysql-server mysql-server/root_password password root" | debconf-set-selections -RUN echo "mysql-server mysql-server/root_password_again password root" | debconf-set-selections +# Install PostgreSQL 9.5 +RUN wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add - && \ + echo 'deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main' > /etc/apt/sources.list.d/pgdg.list && \ + apt-get -y -q install postgresql-${PG_VERSION} postgresql-client-${PG_VERSION} postgresql-contrib-${PG_VERSION} +# Update the Ubuntu and install tools RUN apt-get update && \ apt-get install -y openjdk-8-jdk && \ apt-get install -y gcc && \ - apt-get install -y libmysqlclient-dev && \ - apt-get install -y mysql-server && \ - apt-get install -y mysql-client && \ apt-get install -y curl && \ + apt-get install -y vim && \ apt-get install -y build-essential && \ apt-get install -y libssl-dev && \ apt-get install -y libffi-dev -#configure the JDK +# Adjust PostgreSQL configuration +RUN echo "host all all 0.0.0.0/0 md5" >> ${PG_HOME}/${PG_VERSION}/main/pg_hba.conf +RUN echo "listen_addresses='*'" >> ${PG_HOME}/${PG_VERSION}/main/postgresql.conf + +# Add VOLUMEs to allow backup of config, logs and databases +VOLUME ["${PG_HOME}", "${PG_VAR_LIB}", "${PG_LOGDIR}"] + +# Set the default command to run postgreSQL when starting the container +CMD ["${PG_USR_LIB}/${PG_VERSION}/bin/postgres", "-D", "${PG_VAR_LIB}/${PG_VERSION}/main", "-c", "config_file=${PG_HOME}/${PG_VERSION}/main/postgresql.conf"] + +#Configure Java SDK RUN sed -i 's|#networkaddress.cache.ttl=-1|networkaddress.cache.ttl=10|' /usr/lib/jvm/java-8-openjdk-amd64/jre/lib/security/java.security ENV JAVA_HOME /usr/lib/jvm/java-8-openjdk-amd64 ENV PATH $PATH:/usr/lib/jvm/java-8-openjdk-amd64/jre/bin:/usr/lib/jvm/java-8-openjdk-amd64/bin ENV CLASSPATH .:${JAVA_HOME}/lib:${JRE_HOME}/lib ENV JRE_HOME ${JAVA_HOME}/jre -#add uui related resources to the docker image +#Add Usecase-UI server related resources to the docker image RUN mkdir /home/uui WORKDIR /home/uui ADD usecase-ui-server-*-linux64.tar.gz /home/uui/ -#init db -#RUN chmod 755 /home/uui/resources/bin/*.sh -#RUN /home/uui/resources/bin/initDB.sh root root 3306 127.0.0.1 - -#RUN chmod 755 /home/uui/bin/*.sh - -#ENTRYPOINT /home/uui/resources/bin/initDB.sh root root 3306 127.0.0.1 ENTRYPOINT /home/uui/bin/run.sh diff --git a/standalone/src/main/assembly/bin/run.sh b/standalone/src/main/assembly/bin/run.sh index 95850849..66e0749b 100644 --- a/standalone/src/main/assembly/bin/run.sh +++ b/standalone/src/main/assembly/bin/run.sh @@ -15,31 +15,25 @@ # limitations under the License. # -DIRNAME=`dirname $0` -RUNHOME=`cd $DIRNAME/; pwd` -echo @RUNHOME@ $RUNHOME +main_path="/home/uui" +echo @main_path@ $main_path -echo "Starting mysql" -service mysql start +echo "Starting postgreSQL..." +service postgresql start sleep 10 -echo "grep initDB status" -ps -fe | grep initDB.sh -SCRIPT="/home/uui/resources/bin/initDB.sh" -chmod 755 $SCRIPT -$SCRIPT root root 3306 127.0.0.1 +echo "running usecase-ui database init script..." +dbScript="$main_path/resources/bin/initDB.sh" +chmod 755 $dbScript +$dbScript postgres postgres 127.0.0.1 5432 postgres -echo @JAVA_HOME@ $JAVA_HOME -JAVA="$JAVA_HOME/bin/java" -echo @JAVA@ $JAVA -main_path=$RUNHOME/../ -cd $main_path +JAVA_PATH="$JAVA_HOME/bin/java" JAVA_OPTS="-Xms50m -Xmx128m" -#port=9500 -#JAVA_OPTS="$JAVA_OPTS -Xdebug -Xnoagent -Djava.compiler=NONE -Xrunjdwp:transport=dt_socket,address=$port,server=y,suspend=n" +echo @JAVA_PATH@ $JAVA_PATH echo @JAVA_OPTS@ $JAVA_OPTS -class_path="$main_path/:$main_path/usecase-ui-server.jar" -echo @class_path@ $class_path +jar_path="$main_path/usecase-ui-server.jar" +echo @jar_path@ $jar_path -"$JAVA" $JAVA_OPTS -classpath "$class_path" -jar "$main_path/usecase-ui-server.jar" +echo "Starting usecase-ui-server..." +$JAVA_PATH $JAVA_OPTS -classpath $jar_path -jar $jar_path diff --git a/standalone/src/main/assembly/resources/bin/initDB.sh b/standalone/src/main/assembly/resources/bin/initDB.sh index 06d2bfc8..a7ee1eed 100644 --- a/standalone/src/main/assembly/resources/bin/initDB.sh +++ b/standalone/src/main/assembly/resources/bin/initDB.sh @@ -15,36 +15,32 @@ # limitations under the License. # -DIRNAME=`dirname $0` -HOME=`cd $DIRNAME/; pwd` +echo "setting database init parameters" +main_path="/home/uui" user=$1 password=$2 -port=$3 -host=$4 -echo "start create usecase-ui db" -sql_path=$HOME/../ -mysql -u$user -p$password -P$port -h$host <$sql_path/dbscripts/mysql/usecase-ui-createdb.sql +host=$3 +port=$4 +dbname=$5 + +echo "start create usecase-ui database..." +dbscripts_path="$main_path/resources/dbscripts/postgres" +psql "host=$host port=$port user=$user password=$password dbname=$dbname" -f $dbscripts_path/uui_create_db.sql sql_result=$? -if [ $sql_result != 0 ] ; then - echo "failed to create usecase-ui database" +if [ $sql_result!=0 ] then + echo "failed to create usecase-ui database!" exit 1 +else + echo "usecase-ui database created successfully!" fi -fileFlag=*createobj.sql -location=$sql_path/dbscripts/mysql -fileName="" -for i in `ls $location` -do - if [[ $i == ${fileFlag} ]];then - fileName=${i}; - echo "start create table:${fileName}" - mysql -u$user -p$password -P$port -h$host <$sql_path/dbscripts/mysql/$fileName - sql_result=$? - if [ $sql_result != 0 ] ; then - echo "failed to init usecase-ui table:${fileName}" - exit 1 - fi - fi -done -echo "init usecase-ui database success!" -exit 0 +echo "start create usecase-ui tables..." +psql "host=$host port=$port user=$user password=$password dbname=$dbname" -f $dbscripts_path/uui_create_table.sql +sql_result=$? +if [ $sql_result!=0 ] then + echo "failed to create usecase-ui table!" + exit 1 +else + echo "usecase-ui tables created successfully!" +fi +exit 0 diff --git a/standalone/src/main/assembly/resources/dbscripts/postgres/uui_create_db.sql b/standalone/src/main/assembly/resources/dbscripts/postgres/uui_create_db.sql new file mode 100644 index 00000000..49e9e593 --- /dev/null +++ b/standalone/src/main/assembly/resources/dbscripts/postgres/uui_create_db.sql @@ -0,0 +1,26 @@ +-- +-- Copyright (C) 2019 CMCC, Inc. and others. All rights reserved. +-- +-- Licensed under the Apache License, Version 2.0 (the "License"); +-- you may not use this file except in compliance with the License. +-- You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +-- create uui user +create user "uui" with password 'uui' nocreatedb; + +-- create uui database +drop database if exists uui; +create database uui with owner uui template template0 encoding 'UTF8'; + +-- grant uui create uui user +grant all privileges on database uui to uui; +grant all privileges on database uui to postgres; diff --git a/standalone/src/main/assembly/resources/dbscripts/postgres/uui_create_table.sql b/standalone/src/main/assembly/resources/dbscripts/postgres/uui_create_table.sql new file mode 100644 index 00000000..a62d0835 --- /dev/null +++ b/standalone/src/main/assembly/resources/dbscripts/postgres/uui_create_table.sql @@ -0,0 +1,149 @@ +-- +-- Copyright (C) 2019 CMCC, Inc. and others. All rights reserved. +-- +-- Licensed under the Apache License, Version 2.0 (the "License"); +-- you may not use this file except in compliance with the License. +-- You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +-- ---------------------------- +-- Table structure for "alarms_additionalinformation" +-- ---------------------------- +DROP TABLE alarms_additionalinformation; +CREATE TABLE alarms_additionalinformation( + id serial, + "header_id" varchar(50) NOT NULL, + "name" varchar(100) DEFAULT NULL, + "value" varchar(100) DEFAULT NULL, + "source_id" varchar(100) DEFAULT NULL, + "start_epoch_microsec" varchar(100) DEFAULT NULL, + "last_epoch_microsec" varchar(100) DEFAULT NULL, + CONSTRAINT alarms_additionalinformation_pk PRIMARY KEY (id) +) WITH ( OIDS = FALSE); + +-- ---------------------------- +-- Table structure for "alarms_commoneventheader" +-- ---------------------------- +DROP TABLE alarms_commoneventheader; +CREATE TABLE alarms_commoneventheader ( + "id" varchar(50) NOT NULL, + "version" varchar(100) DEFAULT NULL, + "event_name" varchar(100) DEFAULT NULL, + "domain" varchar(100) DEFAULT NULL, + "event_id" varchar(100) DEFAULT NULL, + "event_type" varchar(100) DEFAULT NULL, + "nfc_naming_code" varchar(100) DEFAULT NULL, + "nf_naming_code" varchar(100) DEFAULT NULL, + "source_id" varchar(100) DEFAULT NULL, + "source_name" varchar(100) DEFAULT NULL, + "reporting_entity_id" varchar(100) DEFAULT NULL, + "reporting_entity_name" varchar(100) DEFAULT NULL, + "priority" varchar(50) DEFAULT NULL, + "start_epoch_microsec" varchar(100) DEFAULT NULL, + "last_epoch_microsec" varchar(100) DEFAULT NULL, + "start_epoch_microsec_cleared" varchar(100) DEFAULT NULL, + "last_epoch_microsec_cleared" varchar(100) DEFAULT NULL, + "sequence" varchar(100) DEFAULT NULL, + "fault_fields_version" varchar(100) DEFAULT NULL, + "event_servrity" varchar(100) DEFAULT NULL, + "event_source_type" varchar(100) DEFAULT NULL, + "event_category" varchar(100) DEFAULT NULL, + "alarm_condition" varchar(100) DEFAULT NULL, + "specific_problem" varchar(100) DEFAULT NULL, + "vf_status" varchar(100) DEFAULT NULL, + "alarm_interfacea" varchar(100) DEFAULT NULL, + "status" varchar(50) DEFAULT NULL, + CONSTRAINT alarms_commoneventheader_pk PRIMARY KEY (id) +) WITH ( OIDS = FALSE ); + +-- ---------------------------- +-- Table structure for "performance_additionalinformation" +-- ---------------------------- +DROP TABLE performance_additionalinformation; +CREATE TABLE performance_additionalinformation ( + id serial, + "header_id" varchar(50) NOT NULL, + "name" varchar(100) DEFAULT NULL, + "value" varchar(100) DEFAULT NULL, + "source_id" varchar(100) DEFAULT NULL, + "start_epoch_microsec" varchar(100) DEFAULT NULL, + "last_epoch_microsec" varchar(100) DEFAULT NULL, + CONSTRAINT performance_additionalinformation_pk PRIMARY KEY (id) +) WITH ( OIDS = FALSE ); + +-- ---------------------------- +-- Table structure for "performance_commoneventheader" +-- ---------------------------- +DROP TABLE performance_commoneventheader; +CREATE TABLE performance_commoneventheader ( + "id" varchar(50) NOT NULL, + "version" varchar(100) DEFAULT NULL, + "event_name" varchar(100) DEFAULT NULL, + "domain" varchar(100) DEFAULT NULL, + "event_id" varchar(100) DEFAULT NULL, + "event_type" varchar(100) DEFAULT NULL, + "nfc_naming_code" varchar(100) DEFAULT NULL, + "nf_namingcode" varchar(100) DEFAULT NULL, + "source_id" varchar(100) DEFAULT NULL, + "source_name" varchar(100) DEFAULT NULL, + "reporting_entity_id" varchar(100) DEFAULT NULL, + "reporting_entity_name" varchar(100) DEFAULT NULL, + "priority" varchar(50) DEFAULT NULL, + "start_epoch_microsec" varchar(100) DEFAULT NULL, + "last_epoch_microsec" varchar(100) DEFAULT NULL, + "sequence" varchar(100) DEFAULT NULL, + "measurements_for_vf_scaling_version" varchar(100) DEFAULT NULL, + "measurement_interval" varchar(100) DEFAULT NULL, + CONSTRAINT performance_commoneventheader_pk PRIMARY KEY (id) +) WITH (OIDS = FALSE); + +-- ---------------------------- +-- Table structure for service_instances +-- ---------------------------- +DROP TABLE service_instances; +CREATE TABLE service_instances ( + "id" varchar(50) NOT NULL, + "service_instance_id" varchar(100) NOT NULL, + "customer_id" varchar(50) NOT NULL, + "service_type" varchar(50) NOT NULL, + "usecase_type" varchar(50) NOT NULL, + "parent_service_instance_id" varchar(100) DEFAULT NULL, + "uuid" varchar(100), + "invariant_uuid" varchar(100), + CONSTRAINT service_instances_pk PRIMARY KEY (service_instance_id) +); + +-- ---------------------------- +-- Table structure for service_instance_operations +-- ---------------------------- +DROP TABLE service_instance_operations; +CREATE TABLE service_instance_operations ( + "service_instance_id" varchar(100) NOT NULL, + "operation_id" varchar(100) NOT NULL, + "operation_type" varchar(50) NOT NULL, + "operation_progress" varchar(50) NOT NULL, + "operation_result" varchar(100) DEFAULT NULL, + "start_time" varchar(100) NOT NULL, + "end_time" varchar(100), + CONSTRAINT service_instance_operations_pk PRIMARY KEY (service_instance_id, operation_id) +); + +-- ---------------------------- +-- Table structure for sort_master +-- ---------------------------- +DROP TABLE sort_master; +CREATE TABLE sort_master ( + "sort_type" varchar(50) NOT NULL, + "sort_code" varchar(10) NOT NULL, + "sort_value" varchar(100) NOT NULL, + "language" varchar(50) NOT NULL, + CONSTRAINT sort_master_pk PRIMARY KEY (sort_type, sort_code, language) +); -- cgit 1.2.3-korg