DB in UT

DB in test case

DbConfig

public class DbConfig {
   private String userName = "";
   private String password = "";
   private String dbHost = "";
   private String dbName = "";
   private String dbPort = "";
   
   private DbConfig() {}
   
   public DbConfig(String userName, String password, String dbHost, int dbPort, String dbName) {
      this.userName = userName;
      this.password = password;
      this.dbHost = dbHost;
      this.dbName = dbName;
      this.dbPort = "" + dbPort;
   }
   
   public String userName() {
      return userName;
   }
   
   public String password() {
      return password;
   }
   
   public String dbHost() {
      return dbHost;
   }
   
   public String dbName() {
      return dbName;
   }
   
   public String dbPort() {
      return dbPort != null ? dbPort : "5432";
   }
   
   public static DbConfig defaultConfig() {
      return new DbConfig();
   }
}

Code

private static EmbeddedPostgres db;

 
@BeforeClass
public static void prepareDb() throws Exception {
    db = EmbeddedPostgres.start();
    DbUtil.createTestSchema();
    DbUtil.loadTestData();
}

DbUtil

package com.telenav.rawlog.metadata.util;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

import com.opentable.db.postgres.embedded.EmbeddedPostgres;
import com.telenav.rawlog.metadata.DbConfig;

public class DbUtils {

    public final static String userName = "postgres";
    public final static String password = "postgres";
    public final static String dbName = "postgres";
    public final static String dbHost = "localhost";

    public static void createTestSchema(DbConfig config) {
        String fileName = DbUtils.class.getClassLoader().getResource("db/createSchema.sql").getFile();

        runSqlFile(fileName, config);
    }

    public static void loadTestData(DbConfig config) {
        runSqlFile(DbUtils.class.getClassLoader().getResource("db/insertData.sql").getFile(), config);
    }

    private static void runSqlFile(String fileName, DbConfig config) {
        Connection conn = getConnection(config);
        Statement st = null;

        try {
            st = conn.createStatement();

            BufferedReader reader = null;

            try {
                reader = new BufferedReader(new InputStreamReader(new FileInputStream(fileName)));

                String line = null;

                while ((line = reader.readLine()) != null) {
                    if (line.endsWith(";")) {
                        try {
                            st.execute(line);
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                }
            } catch (Exception e) {
                throw new RuntimeException("Error reading file", e);
            } finally {
                if (st != null) {
                    try {
                        st.close();
                    } catch (Exception ex) {
                    }
                }

                try {
                    reader.close();
                } catch (Exception e) {
                    // ignore
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(DbConfig config) {
        try {
            Class.forName("org.postgresql.Driver");

            String url = "jdbc:postgresql://" + config.dbHost() + ":" + config.dbPort() + "/" + config.dbName() + "?user=" + config.userName() + "&password=" + config.password();
            return DriverManager.getConnection(url);
        } catch (Exception e) {
            throw new RuntimeException("Could not get database connection.", e);
        }
    }
}

 

CreateSchema.sql

SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET row_security = off;

CREATE SCHEMA raw_log AUTHORIZATION postgres;
    
CREATE SEQUENCE raw_log.log_file_id_seq INCREMENT 1 START 6 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1;

ALTER SEQUENCE raw_log.log_file_id_seq OWNER TO postgres;
    
CREATE SEQUENCE raw_log.meta_data_id_seq INCREMENT 1 START 6 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1;

ALTER SEQUENCE raw_log.meta_data_id_seq OWNER TO postgres;
    
CREATE TABLE raw_log.log_file (id integer NOT NULL DEFAULT nextval('raw_log.log_file_id_seq'::regclass), app_id character varying(64) COLLATE pg_catalog."default" DEFAULT NULL::character varying, file_path character varying(1024) COLLATE pg_catalog."default" DEFAULT NULL::character varying, update_time timestamp without time zone, total_lines integer, CONSTRAINT log_file_pkey PRIMARY KEY (id), CONSTRAINT log_file_path_id_key UNIQUE (file_path)) WITH (OIDS = FALSE) TABLESPACE pg_default;

ALTER TABLE raw_log.log_file OWNER to postgres;

CREATE INDEX log_file_app_id_idx ON raw_log.log_file USING btree (app_id COLLATE pg_catalog."default" ASC NULLS LAST) TABLESPACE pg_default;

CREATE INDEX log_file_path_idx ON raw_log.log_file USING btree (file_path COLLATE pg_catalog."default" ASC NULLS LAST) TABLESPACE pg_default;

CREATE TABLE raw_log.meta_data (id integer NOT NULL DEFAULT nextval('raw_log.meta_data_id_seq'::regclass), user_id character varying(128) COLLATE pg_catalog."default" DEFAULT NULL::character varying, device_id character varying(128) COLLATE pg_catalog."default" DEFAULT NULL::character varying, update_time timestamp without time zone, occurrences integer, log_file_id integer, CONSTRAINT meta_data_log_file_jk FOREIGN KEY (log_file_id) REFERENCES raw_log.log_file (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION) WITH (OIDS = FALSE) TABLESPACE pg_default;

ALTER TABLE raw_log.meta_data OWNER to postgres;

CREATE INDEX device_id_idx ON raw_log.meta_data USING btree (device_id COLLATE pg_catalog."default" ASC NULLS LAST) TABLESPACE pg_default;

CREATE INDEX user_id_idx ON raw_log.meta_data USING btree (user_id COLLATE pg_catalog."default" ASC NULLS LAST) TABLESPACE pg_default;

InsertData.sql

DELETE FROM raw_log.meta_data;
DELETE FROM raw_log.log_file;

INSERT INTO raw_log.log_file (id, app_id, file_path, total_lines) values (1, 'denali_usage_logs', 's3://td-titan-w2/rawlog/logshed_denali_usage_logs_2019-08-01-18-40-GMT_ec2s-logshedcollector-11-240000.gz', 161);

INSERT INTO raw_log.meta_data (id, user_id, device_id, occurrences, log_file_id) VALUES (1, 'E0S4I9WVJJZJ872M9MYVF80AV', 'f481afa3c1eb5a0471f0b8e0a3407948374e967fb0a95817c96e22a0bab3aa9e', 9, 1);

SELECT pg_catalog.setval('raw_log.log_file_id_seq', 6, true);

SELECT pg_catalog.setval('raw_log.meta_data_id_seq', 6, true);