I put up a new song today. It was 90% done a year or two ago, but I came across it today and decided to finish it and publish. The photo is from the ruins of Zama in Mexico.

Every time I go to build a SQL*Loader control file, I find out that it can do things I didn't consider and can't do things that seemed obvious that it should.  Somewhere between removing tufts of hair with fists and jumping up and down with joy, things finally get done.  I find the lack of examples and documentation to be particularly frustrating.  Or maybe I'm awful at Google.  With peer assistance and Internetting, I finally got things to do my bidding.  Anyway, I thought I'd put down some learnings in wordings to help prevent some headaches for others and myself when I forget things.

Background

So my task was to get a delimited, hierarchical file loaded into a set of tables.  I wanted to avoid just dumping everything into a table and running a complicated stored procedure over the top of it, and the file was way to big to map into memory.  And I didn't want to make a complicated process to break it into smaller chunks for tracking and time purposes.  I'll mock up some completely contrived examples for the problem and solution explanation.

Example Data

Mind that this data is made up and simpler than my actual probably, but it is structured in a way that is on purpose and will illustrate the point without being excessively complex.

There are records for each person as indicated by the first field PERSON.  Under each person are groupings of records that tie to the person:  PERSON_PHONE, and PERSON_ADDRESS.  Notice that there are more than one of these records.  In theory you would be able to have any number of phone numbers or addresses for each person, and you can have any number of people.

PERSON|123|Robert||Robertson|
PERSON_PHONE|123|1115557777|CELLULAR
PERSON_PHONE|123|1115557772|WORK
PERSON_ADDRESS|123|555 Robin Ln||St. Robert|CA|88888|HOME
PERSON_ADDRESS|123|222 Bobbypin Circle|Suite 211|St. Robert|CA|88888|WORK
PERSON|123|William|W|Williams|Jr.
PERSON_PHONE|123|1115556666|HOME
PERSON_PHONE|123|1115556622|WORK
PERSON_ADDRESS|123|212 Wiley St. E||St. Robert|CA|88888-8888|WORK
PERSON_ADDRESS|123|444 Willie St|Apt 222|St. Robert|CA|88888|HOME

Data Model

These are the made-up tables for demonstration.  Again, this is totally contrived, so please just ignore that I am being design and syntax-lazy.

CREATE TABLE PERSON (
    PERSON_ID NUMBER NOT NULL PRIMARY KEY,
    FILE_ID NUMBER,
    FIRST_NAME VARCHAR2,
    MIDDLE_NAME VARCHAR2,
    LAST_NAME VARCHAR2,
);

CREATE TABLE PERSON_PHONE (
    PERSON_PHONE_ID NUMBER NOT NULL PRIMARY KEY,
    PERSON_ID NUMBER FOREIGN KEY REFERENCES PERSON (PERSON_ID),
    FILE_ID NUMBER,
    PHONE_NUMBER VARCHAR2,
    PHONE_TYPE VARCHAR2
);

CREATE TABLE PERSON_ADDRESS (
    PERSON_ADDRESS_ID NUMBER NOT NULL PRIMARY KEY,
    PERSON_ID NUMBER FOREIGN KEY REFERENCES PERSON (PERSON_ID),
    FILE_ID NUMBER,
    STREET VARCHAR2,
    SUITE VARCHAR2,
    CITY VARCHAR2,
    STATE VARCHAR2,
    ZIP VARCHAR2
);

Failed First Attempt

Disclaimer:  This does not work.

The first thing I tried was to use sequences.  Bang-pow, done.  Shut down everything and go home.

Control File:

LOAD DATA
CHARACTERSET WE8MSWIN1252

APPEND

INTO TABLE PERSON
WHERE (1) = 'PERSON'
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
    RECORD_TYPE FILLER,
    FILE_ID,
    FIRST_NAME,
    MIDDLE_NAME,
    LAST_NAME,
    PERSON_ID "PERSON_SEQUENCE.NEXTVAL"
)

INTO TABLE PERSON_PHONE
WHERE (1) = 'PERSON_PHONE'
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
    RECORD_TYPE FILLER,
    FILE_ID,
    PHONE_NUMBER,
    PHONE_TYPE,
    PERSON_PHONE_ID "PERSON_PHONE_SEQUENCE.NEXTVAL",
    PERSON_ID "PERSON.CURRVAL"
)

INTO TABLE PERSON_ADDRESS
WHERE (1) = 'PERSON_ADDRESS'
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
    RECORD_TYPE FILLER,
    FILE_ID,
    STREET,
    SUITE,
    CITY,
    STATE,
    ZIP,
    PERSON_ADDRESS_ID "PERSON_ADDRESS_SEQUENCE.NEXTVAL",
    PERSON_ID "PERSON.CURRVAL"
)

This did not work for a number of reasons.  I'll list them off.

1. Every time the SQL*Loader reads a record, it has some sort of positional index thing that dictates the position in the file to read.  When the first field of each record is read, the default behavior is to take a NEXT.  I don't claim to completely understand why it doesn't start at the beginning, but I suspect it's something to do with the WHEN clause eating position 1.
2. The WHEN clause doesn't care about the terminator.  Notice how the "FIELDS TERMINATED BY" part comes after the match?  Yeah.  Because all the tables start with PERSON, all of them matched for the PERSON table.  Ooops.
3. The SQL*Loader behavior does each "INTO TABLE" block through for the entire file before going to the next.  And it commits between.  This means that all the matches for PERSON are done first, then all the PERSON_PHONE, then PERSON_ADDRESS.  As a result, using a sequence CURRVAL is not going to work.  What happens is all the PERSON records get loaded first.  Then the sequence is sitting on the most recent insert record.  When the PERSON_PHONE records or PERSON_ADDRESS records come along, all of them get the PERSON_ID of the last inserted PERSON record.  Not cool.

Successful Attempt

To say that this was attempt 2 would be a blatant lie.  It would be more like attempt 12.  Anyway, this is where I landed to achieve level 9001.

Luckily, I had complete control over the data file spec (I put it together from an even uglier file) and over the data model (at least enough to add a column).  I could bake the hierarchy into the file and use some queries.  Since my process created the hierarchy, I could trust it.  I did try some nonsense using key values--for example, including the name in each phone and address record--but that would not guarantee a unique result, so I had to toss it out with the moldy peaches.

New Data File:

PERSON|123|1|Robert||Robertson|
PERSON_PHONE|123|1|1115557777|CELLULAR
PERSON_PHONE|123|1|1115557772|WORK
PERSON_ADDRESS|123|1|555 Robin Ln||St. Robert|CA|88888|HOME
PERSON_ADDRESS|123|1|222 Bobbypin Circle|Suite 211|St. Robert|CA|88888|WORK
PERSON|123|2|William|W|Williams|Jr.
PERSON_PHONE|123|2|1115556666|HOME
PERSON_PHONE|123|2|1115556622|WORK
PERSON_ADDRESS|123|2|212 Wiley St. E||St. Robert|CA|88888-8888|WORK
PERSON_ADDRESS|123|2|444 Willie St|Apt 222|St. Robert|CA|88888|HOME

Data Model:

CREATE TABLE PERSON (
    PERSON_ID NUMBER NOT NULL PRIMARY KEY,
    FILE_ID NUMBER,
    SEQUENCE_ID NUMBER,
    FIRST_NAME VARCHAR2,
    MIDDLE_NAME VARCHAR2,
    LAST_NAME VARCHAR2,
);

CREATE TABLE PERSON_PHONE (
    PERSON_PHONE_ID NUMBER NOT NULL PRIMARY KEY,
    PERSON_ID NUMBER FOREIGN KEY REFERENCES PERSON (PERSON_ID),
    FILE_ID NUMBER,
    PHONE_NUMBER VARCHAR2,
    PHONE_TYPE VARCHAR2
);

CREATE TABLE PERSON_ADDRESS (
    PERSON_ADDRESS_ID NUMBER NOT NULL PRIMARY KEY,
    PERSON_ID NUMBER FOREIGN KEY REFERENCES PERSON (PERSON_ID),
    FILE_ID NUMBER,
    STREET VARCHAR2,
    SUITE VARCHAR2,
    CITY VARCHAR2,
    STATE VARCHAR2,
    ZIP VARCHAR2
);

New Control File:

LOAD DATA
CHARACTERSET WE8MSWIN1252

APPEND

INTO TABLE PERSON
WHERE (1) = 'PERSON' AND (1) != 'PERSON_'
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
    RECORD_TYPE FILLER POSITION(1),
    FILE_ID,
    SEQUENCE_ID,
    FIRST_NAME,
    MIDDLE_NAME,
    LAST_NAME,
    PERSON_ID "PERSON_SEQUENCE.NEXTVAL"
)

INTO TABLE PERSON_PHONE
WHERE (1) = 'PERSON_PHONE'
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
    RECORD_TYPE FILLER POSITION(1),
    SEQUENCE_ID BOUNDFILLER,
    FILE_ID,
    PHONE_NUMBER,
    PHONE_TYPE,
    PERSON_PHONE_ID "PERSON_PHONE_SEQUENCE.NEXTVAL",
    PERSON_ID "(SELECT PERSON_ID FROM PERSON WHERE FILE_ID = :FILE_ID AND SEQUENCE_ID = :SEQUENCE_ID)"
)

INTO TABLE PERSON_ADDRESS
WHERE (1) = 'PERSON_ADDRESS'
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
    RECORD_TYPE FILLER POSITION(1),
    SEQUENCE_ID BOUNDFILLER,
    FILE_ID,
    STREET,
    SUITE,
    CITY,
    STATE,
    ZIP,
    PERSON_ADDRESS_ID "PERSON_ADDRESS_SEQUENCE.NEXTVAL",
    PERSON_ID "(SELECT PERSON_ID FROM PERSON WHERE FILE_ID = :FILE_ID AND SEQUENCE_ID = :SEQUENCE_ID)"
)

 Notice a few things.

1. The first field in each table has a POSITION(1) flag on it to fix the problem of grabbing a NEXT instead of the first column.
2. The PERSON table now has an AND in the WHEN clause for not matching to other tables.
3. Now there is a SQL query using the sequence number for referencing the right row.
   1. The SEQUENCE_ID in the address and phone tables don't have a column, so we had to flag it is a FILLER, but not just any filler; a BOUNDFILLER.  This allows the column to be available in the query for PERSON_ID.  Otherwise no dice.
   2. The select query is in parenthesis.  If you don't do that, you can't use a select query.  This is because you are only supposed to access functions in the control file.  Apparently putting a select query in parenthesis makes it a function.

So there it is.  Peace at last.

I was supposed to be putting things into boxes and moving those boxes from one place to another; however, I was more motivated to create this.  I like to think of it as a song about exploring the unknown.

 http://www.intentsdesign.com/music/Into%20the%20Darkness.mp3

Here's a song I put together a while ago.  It still needs some mixing and mastering.  But it's abandoned at present.

http://www.intentsdesign.com/music/Meet%20the%20Earth.mp3

We went to Hawaii.  Here are some pictures.

Somebody's lawn near "our" beach.

"Our" beach in Kailua.

Dancers at the Polynesian Cultural Center (representing Tahiti).

A view from the Polynesian Cultural Center boat ride.

A spider we saw on our nature hike on the Makapu'u Point Lighthouse trail.

The view from the top of the Makapu'u Point Lighthouse trail.

Another view from the top of the Makapu'u Point Lighthouse trail.

Some random person floating in mid air over the Makapu'U area.

The Makapu'u Point Lighthouse trail.

The dolphin show at Sea Life Park.

A crazy tree in a park.

Sunset on Waikiki beach.

A bird we saw on the Manoa Falls trail.

A beach near the Halona Blow Hole.

The Manoa Falls trail.

A plant at the Lyon Arboretum

Our submarine pilot.

Manoa Falls.

I have a lot more pictures than these, but...this'll do for now.

Looking for how to do a decent mock test on a DAO layer using JPA-style hibernate, I found nothing that perfectly fit what we were doing, so I thought I'd put down an example and help add to the world of knowledge.

The point here is that all of the code is executed right up to actually trying to make a database call.  The query itself is mocked up, and so there is no external dependency.  This is useful if--for some reason or another--the team that controls your automated builds puts up a firewall between your build server and your database. Also useful if you can't rely on the data in your database to be stable.  Not so useful if you are getting failures calling out to the database, but you can always write non-mock tests for that purpose.  That's easier anyway.

I don't feel especially like explaining all this since I'm lazy.  That, and the links at the bottom include some decent explanations.  So have a look over there, and then hopefully the example will speak for itself.

Note that all of this code is simplified from real, working examples, but I haven't actually compiled it--hopefully it's good.  Anyway, onto some classes.

SillyTable.java

package com.intents.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;@SuppressWarnings("serial")

@Entity
@NamedQueries({
    @NamedQuery(name = "SillyTable.findByPrimaryKey", query = "select st from SillyTable st where st.primaryKey = :primaryKey")
})
@Table(name = "SILLY_TABLE")
public class SillyTable {
    @Id
    @GeneratedValue(generator = "primaryKeySeq")
    @SequenceGenerator(name = "primaryKeySeq", sequenceName = "PRIMARY_KEY_SEQ")
    @Column(name = "PRIMARY_KEY", nullable = false)
    private Long primaryKey;
}

SillyTableDao.java

package com.intents.dao;

import com.intents.model.SillyTable;

public interface SillyTableDao {
    String FIND_BY_PRIMARY_KEY_QUERY = "SillyTable.findByPrimaryKey";
    String PRIMARY_KEY_PARAMETER = "primaryKey";

    SillyTable findByPrimaryKey(Long primaryKey) throws Exception;
}

SillyTableDaoImpl.java

package com.intents.dao.impl;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import org.springframework.stereotype.Repository;
import com.intents.dao.SillyTableDao;
import com.intents.model.SillyTable;

@Repository
public class SillyTableDaoImpl implements SillyTableDao {
    
    @PersistenceContext(unitName = "framework")
    private EntityManager entityManager;
    
    public void setEntityManager(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    public EntityManager getEntityManager() {
        return entityManager;
    }

    @Override
    public SillyTable findByPrimaryKey(final Long primaryKey) throws Exception {
        SillyTable result = null;
        Query query;
        try {
            query = getEntityManager().createNamedQuery(FIND_BY_PRIMARY_KEY_QUERY);
            query.setParameter(PRIMARY_KEY_PARAMETER, primaryKey);
            result = (SillyTable) query.getSingleResult();
        } catch (Exception e) {
            throw new Exception("Failed lookup! ZMOG!", e);
        }
        return result;
    }
}

SillyTableDaoImplMockTest.java

package com.intents.dao.impl;

import static org.junit.Assert.assertSame;
import static org.mockito.Matchers.any;
import static org.mockito.Mockito.when;
import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.Query;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.mockito.Mock;
import org.mockito.runners.MockitoJUnitRunner;
import org.springframework.test.annotation.Rollback;
import org.springframework.transaction.annotation.Transactional;
import com.intents.dao.impl.SillyTableDaoImpl;
import com.intents.model.SillyTable;

@RunWith(MockitoJUnitRunner.class)
@Transactional
public class SillyTableDaoImplMockTest  {
    private static final String FIND_BY_PRIMARY_KEY_QUERY = "SillyTable.findByPrimaryKey";
    private static final Long PRIMARY_KEY = 9001L;

    @Mock
    EntityManager entityManager;

    @Mock
    Query query;

    SillyTableDaoImpl sillyTableDao = new SillyTableDaoImpl();

    @Before
    public void setup() {
        when(entityManager.find((Class<?>) any(), any())).thenReturn(null);
        sillyTableDao.setEntityManager(entityManager);
    }

    @Test
    @Rollback(true)
    public void testFindByPrimaryKeyReturnsResult() {
        SillyTable expectedSillyTable = new SillyTable();
        when(entityManager.createNamedQuery(FIND_BY_PRIMARY_KEY_QUERY)).thenReturn(query);
        when(query.getSingleResult()).thenReturn(expectedSillyTable);
        SillyTable returnedSillyTable = sillyTableDao.findByPrimaryKey(PRIMARY_KEY);
        assertSame(expectedSillyTable, returnedSillyTable);
    }

    @Test(expected = Exception.class)
    @Rollback(true)
    public void testFindByPrimaryKeyNoResultsThrowsException() throws Exception {
        when(entityManager.createNamedQuery(FIND_BY_PRIMARY_KEY_QUERY)).thenReturn(query);
        when(query.getSingleResult()).thenThrow(new NoResultException());
        sillyTableDao.findByPrimaryKey(PRIMARY_KEY);
    }
}

References Used

http://bitbybitblog.com/unit-testing-data-access-components/

http://www.luckyryan.com/2013/06/28/unit-testing-with-mockito/

http://api.nhindirect.org/java/site/direct-msg-monitor/1.1.3/xref-test/org/nhindirect/monitor/dao/impl/AggregationDAOImpl_confirmAggregationTest.html