Standing in the middle of life…

Chrissie_Hynde_2013… with my plans behind me.  Just this morning, I realized that was the lyric in the pretenders song, Middle of the Road.  For the longest time, I thought that the words were “Standing in the middle of life with my pants behind me.”  I was never really sure what that meant.  It does conjure up a few images and seemed like the kind of thing Chrissie Hynde would write, however.

It is a good line to start this post in a way.  In my last post, I was exploring some of the initial tests for the Spring Boot middleware server that I am building for my EKG Field Monitor system.  The tests that I wrote about last time were primarily unit tests.  They were unit tests because they focused specifically on testing single classes or a few classes and mocked out back end interactions (or avoided them altogether.)  I did have one test in there for a service that could really be considered to be an integration test.

I have heard, in the past, the comment “I don’t write unit tests because I have everything covered in my integration tests.”  That may be true.  However, it misses a pretty important point of writing unit tests.  That is that when you write a test first, drives the way that you write your code to satisfy the test.  Here is an interesting post that talks about the value of unit testing and provides some good responses to why people don’t value unit tests.

At any rate, I skipped over a pretty important concept and one that I have touched on briefly in previous posts.  That concept is dependency injection.  Despite my “plan” of being very pragmatic about things, I may have gotten a little ahead of myself.  I guess you could say that I may have been standing in the middle of the road with my pants behind me.  I’ll do my best to get them back on.

If you are unfamiliar with dependency injection you should read up on it.  It is core to Spring.  Let’s take a look at the testGetStrips test we had last time and see if we can take it a bit further in making it an integration test and set the stage for interacting with a database.  (This might be considered a “functional” test to some degree, or maybe even a “deep integration test”.)

@Test
public void testGetStrips() {
    Monitor m = new Monitor();
    m.setID(UUID.randomUUID());
    List<Strip> strips = stripService.getStrips(m);
    assertTrue(strips.size()==5);
}

For this test, I am creating a monitor object and setting the ID to some random value.  For this test, since we are not going to be mocking out the data, we really don’t want a random UUID.  We want to use the ID of a monitor that we know has data in our database.  That’s great…  except we don’t have a database yet.  So, let’s create one!

For Heroku apps, Postgres is a nice database option because it is easily available at the free level.  Postgres is also a nice option because if you are developing on a Mac, you can use a local version of Postgres that can be run as a simple OSX app.

Screen Shot 2016-05-02 at 8.39.22 AM

Once we have Postgres up and running, we have a couple things we need to do.  First, we need to include the Postgres and JDBC dependencies in our app pom file:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
    <version>1.3.3.RELEASE</version>
</dependency>
<dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>9.4-1203-jdbc42</version> </dependency>

We need to specify connection details for the database in our application.properties file

#spring.datasource.url=${JDBC_DATABASE_URL}
spring.datasource.url=jdbc:postgresql://localhost/test
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.maxActive=10
spring.datasource.maxIdle=5
spring.datasource.minIdle=2
spring.datasource.initialSize=5
spring.datasource.removeAbandoned=true

Note that I am leaving the first commented out line in this file.  That will become more important when we get our app running on Heroku.  For now, I am going to set the database URL to my local machine.

So, for the above test to pass, we are going to need to make sure that we have data in the database to support the test.  That raises two questions.  First, how do we get the data into the database?  Secondly, how do we clean up the database?  The last question is probably not a big deal for a query…  but it will be once we write a test that inserts data!

To accomplish this, I used an approach that is outlined in this tutorial.  That approach uses the @SQL annotation to run scripts.  An example can be seen below…

@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = EKGFMApplication.class)
@WebAppConfiguration
@Sql(executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD, scripts = "classpath:createDatabase.sql")
@Sql(executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD, scripts = "classpath:dropDatabase.sql")
public class ITStripServiceTest {

    @Autowired
    StripService stripService;

    @Test
    public void testITStripServiceCreate() {
        Monitor m = new Monitor();
        m.setID(UUID.randomUUID());
        Strip s = stripService.createStrip(m);
        assertNotNull(s);
    }

I created two scripts as the tutorial suggests.  The first one creates the database schema, and the second one drops all the tables and sequences.  I included the drop statements at the beginning of the create script as well just be sure that everything is cleaned up prior to creating data gain.

drop table if exists public."sample" cascade;
drop table if exists public."sampleset" cascade;
drop table if exists public."strip" cascade;
drop table if exists public."monitor" cascade;
drop sequence if exists public.monitor_id_seq cascade;
drop sequence if exists public.strip_id_seq cascade;
drop sequence if exists public.sampleset_id_seq cascade;
drop sequence if exists public.sample_id_seq cascade;

CREATE SEQUENCE public.monitor_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE public.monitor_id_seq
  OWNER TO postgres;

  CREATE SEQUENCE public.strip_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE public.strip_id_seq
  OWNER TO postgres;

  CREATE SEQUENCE public.sampleset_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE public.sampleset_id_seq
  OWNER TO postgres;

  CREATE SEQUENCE public.sample_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE public.sample_id_seq
  OWNER TO postgres;

CREATE TABLE monitor
(
    id INTEGER default nextval('monitor_id_seq'::regclass) NOT NULL UNIQUE,
    uuid VARCHAR(30) NOT NULL
);

CREATE TABLE strip
(
    id INTEGER DEFAULT nextval('strip_id_seq'::regclass) NOT NULL UNIQUE,
    uploaded TIMESTAMP DEFAULT now(),
    monitorid INTEGER NOT NULL,
    CONSTRAINT strip_monitor_id_fk FOREIGN KEY (monitorid) REFERENCES monitor (id)
);

CREATE TABLE sampleset
(
    id INTEGER DEFAULT nextval('sampleset_id_seq'::regclass) NOT NULL UNIQUE,
    stripid INTEGER NOT NULL,
    CONSTRAINT strip_set_id_fk FOREIGN KEY (stripid) REFERENCES strip (id)
);
CREATE UNIQUE INDEX sampleset_id_uindex ON sampleset (id);

CREATE TABLE sample
(
    id INTEGER default nextval('sample_id_seq'::regclass) NOT NULL UNIQUE,
    val INTEGER NOT NULL,
    time INTEGER NOT NULL,
    samplesetid INTEGER NOT NULL,
    CONSTRAINT sample_sampleset_id_fk FOREIGN KEY (samplesetid) REFERENCES sampleset (id)
);

There are several approaches to working with Database in a Spring Boot app.  The most common are using hand-written SQL, the second is JDBC Templates and the third is using JPA (or hibernate, or some other ORM tool.)  My simple app that consists of only 4 tables and a hand full of columns seems to simple for a full JPA implementation.  However, I don’t think that hand-writing all the SQL via raw JDBC is a good practice either.  It is just too easy to open the app to SQL Injection vulnerabilities that way.  That leaves JDBC Templates.  I’ll go over that in my next post.  But now, I have to get “Back on the Chain Gang” so I can have some “Brass in Pocket”.

Advertisements

Author: Miles Porter

Miles Porter is a senior consulting software engineer in the Minneapolis, MN. He is interested in pragmatic software development practices, embedded software, and cloud computing. In his free time he likes to travel with his family, play upright and electric basses and study Taekwondo.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s