Automatically generate SQL INSERT statements with data that respect database constraints

Question

Here is a simple situation. (My apologies my SQL is quite rusty.)

(I am using MySQL and Java but it should not matter too much.)

Let's say I have two tables.

Here is a simple SQL statement:

A:

insert into patient (patient_id) values (16)

Now there is another table person, which has a person_id and that has to be constrained to match patient_id.

B:

CONSTRAINT `person_id_for_patient` FOREIGN KEY (`patient_id`) REFERENCES `person` (`person_id`) ON UPDATE CASCADE)

What I am looking for is some pre-built tool, solution, etc., that might allow one to expand an insert statement such as A in order to satisfy constraints automatically.

In other words, that would automatically expand A into:

insert into person (person_id) values (16)
insert into patient (patient_id) values (16)

Any ideas?

Thank you Misha

Answer 1

Consider these 2 solutions to handle your business logic requirements:

• Strongly suggest creating a stored procedure to insert into both the person and the patient tables.

CREATE PROCEDURE CreatePerson(IN newID int)
BEGIN
   insert into person (person_id) values (newID);
   insert into patient (patient_id) values (newID);
   --as many statements as you need. perhaps wrap in a TRANSACTION.
END;

This will help control the business logic. You'll have to enforce that all code NOT directly insert into the person using adhoc SQL. How easy/hard this is depends on your environment.

• Create a trigger for after insert on the person table to automatically insert into patient. Creating triggers in MySQL. Triggers will definitely solve your dev problem here, but generally aren't overall a great solution, as you tend to 'forget' that they're there. Later, when you are investigating a defect/problem in your database, you may overlook the hidden logic in the trigger. Read more opinions on triggers.

Either way, with either of these two, you'll get the creation of a record into the patient after person.

The question then becomes - does this business rule need enforcing the other way around? What happens when a process tried to write into the patient table? Does it require that a match exist in the person table?

Answer 2

Thanks guys for very helpful replies.

EDIT: Here is an improved version, JDBC compliant I believe:

import java.sql.*;
import java.util.*;
import java.util.Map.Entry;

class NotAllowedToInsertIntoTableException extends Exception {
    String table;
    public NotAllowedToInsertIntoTableException(String table) {
        super(table);
        this.table = table;
    }
}

class UnhandledCaseException extends Exception {
    public UnhandledCaseException() {
        super();
    }
}

class TableColumn {
    String table;
    String column;
    public TableColumn(String table, String column) {
        this.table = table;
        this.column = column;
    }
    public String toString() {
        return "\"" + table + "\".\"" + column + "\"";
    }
    public boolean equals(Object obj) {
        if (obj instanceof TableColumn) {
            TableColumn tableColumn = (TableColumn)obj;
            return ((table.equals(tableColumn.table)) && (column.equals(tableColumn.column)));
        } else {
            return false;
        }
    }
    public int hashCode() {
        int hashCode = 1;
        hashCode = 31*hashCode + (table==null ? 0 : table.hashCode());
        hashCode = 31*hashCode + (column==null ? 0 : column.hashCode());
        return hashCode;
    }       
}

class KeyReference extends TableColumn {
    public KeyReference(String table, String column) {
        super(table, column);
    }
    public KeyReference(TableColumn tableColumn) {
        this(tableColumn.table, tableColumn.column);
    }
}

class ImportedKey {
    TableColumn primary;
    TableColumn foreign;
    public ImportedKey(ResultSet rs) throws Exception {
        primary = new TableColumn(rs.getString("PKTABLE_NAME"), rs.getString("PKCOLUMN_NAME"));
        foreign = new TableColumn(rs.getString("FKTABLE_NAME"), rs.getString("FKCOLUMN_NAME"));
    }
    public String toString() {
        return "primary " + primary.toString() + ", foreign " + foreign.toString();
    }
    public boolean equals(Object obj) {
        if (obj instanceof ImportedKey) {
            ImportedKey importedKey = (ImportedKey)obj;
            return ((primary == importedKey.primary) && (foreign == importedKey.foreign));
        } else {
            return false;
        }
    }
    public int hashCode() {
        int hashCode = 1;
        hashCode = 31*hashCode + (primary==null ? 0 : primary.hashCode());
        hashCode = 31*hashCode + (foreign==null ? 0 : foreign.hashCode());
        return hashCode;
    }       
}

class InsertStatement {
    String table;
    Map<String, Object> columnValues = new HashMap<String, Object>();
    public InsertStatement(String table) {
        this.table = table;
    }
    public void add(String column, Object value) {
        columnValues.put(column, value);
    }
    public String toSql(Map<KeyReference, Object> keys) throws Exception {
        String columns = null;
        String values = null;
        for (Map.Entry<String, Object> columnValue: columnValues.entrySet()) {
            String column = columnValue.getKey();
            if (columns == null) columns = column; else columns += ", " + column;
            Object value = columnValue.getValue();
            String string = null;
            if (value instanceof String) {
                string = (String) value;
            } else if (value instanceof KeyReference) {
                KeyReference keyReference = (KeyReference) value;
                if (keys.containsKey(keyReference) == false ||
                    (keys.get(keyReference) instanceof String) == false) {
                    throw new UnhandledCaseException();
                } 
                string = (String) keys.get(keyReference);
            } else {
                throw new UnhandledCaseException();
            }
            if (values == null) values = string; else values += ", " + string;
        }
        if (columns != null && values != null) {
            return "insert into " + table + " (" + columns + ") values (" + values + ")";
        } else {
            return "insert into " + table + " () values ()";
        }
    }
}

class InsertStatementList {
    List<InsertStatement> insertStatements = new Vector<InsertStatement>();
    public InsertStatementList() {
    }
    public InsertStatementList(InsertStatementList insertStatementList) {
        this.insertStatements.addAll(insertStatementList.insertStatements);
    }
    public int getTableIndex(String table) {
        int idx = 0;
        for (InsertStatement is: insertStatements) {
            if (is.table.equals(table)) return idx;
            idx++;
        }
        return -1;
    } 
    public void add(String table, String column, Object value) {
        int idx = getTableIndex(table);
        InsertStatement is = null;
        if (idx != -1) {
            is = insertStatements.get(idx);
        } else {
            is = new InsertStatement(table);
        }
        is.add(column, value);
        if (idx != -1) {
            insertStatements.set(idx, is);
        } else {
            insertStatements.add(is);
        }
    }
    public void add(TableColumn tableColumn, Object value) {
        add(tableColumn.table, tableColumn.column, value);
    }
}

public class test {

    public static final String tablesIntoWhichWeAreAllowedToInsert[] = {
        "patient", "person" 
    };

    public static boolean isDebugEnabled() {
        return true;
    }
    public static final void debug(String string) {
        System.out.println(string);
    }

    public static void insertIntoDatabase(Connection conn,
                                          Map<TableColumn, Object> paramMap,
                                          Map<TableColumn, Object> given) throws Exception
    {
        InsertStatementList insertStatements = new InsertStatementList();

        // Make a copy of the map
        Map<TableColumn, Object> map = new HashMap<TableColumn, Object>();
        map.putAll(paramMap);

        // Add map elements
        for (Entry<TableColumn, Object> mapElement: map.entrySet()) {
            TableColumn tableColumn = mapElement.getKey();
            Object value = mapElement.getValue();
            insertStatements.add(tableColumn.table, tableColumn.column, value);
        }

        // Try to satisfy foreign key constraints
        DatabaseMetaData dmd = conn.getMetaData();
        boolean satisfiedConstraints = false;
        do {

            debug("---");

            //
            // What imported keys do we need?
            //

            // Avoid ConcurrentModificationException
            InsertStatementList savedInsertStatements = new InsertStatementList(insertStatements);

            List<ImportedKey> importedKeys = new Vector<ImportedKey>();
            for (InsertStatement is: savedInsertStatements.insertStatements) {          

                // Find not nullable columns
                List<TableColumn> notNullable = new Vector<TableColumn>();
                ResultSet rs = dmd.getColumns(null, null, is.table, "");
                debug("Not nullable columns:");
                while (rs.next()) {
                    if (rs.getString("IS_NULLABLE").equals("NO")) {
                        TableColumn tableColumn = new TableColumn(rs.getString("TABLE_NAME"), rs.getString("COLUMN_NAME"));
                        notNullable.add(tableColumn);
                        debug("\t" + tableColumn.toString());
                    }

                    // If we have uuid column and none in insert
                    // http://wiki.openmrs.org/display/archive/UUIDs
                    if (rs.getString("COLUMN_NAME").equals("uuid") &&
                        is.columnValues.containsValue("uuid") == false) {
                        insertStatements.add(is.table, "uuid", "uuid()");               
                    }
                }

                // Find imported keys ...
                rs = dmd.getImportedKeys(null, null, is.table);
                debug("Imported keys:");
                while (rs.next()) {

                    ImportedKey importedKey = new ImportedKey(rs);

                    // ... if in given, just add
                    if (given.containsKey(importedKey.primary)) {
                        insertStatements.add(importedKey.foreign, given.get(importedKey.primary));
                    } 
                    // ... if not in map and not nullable need to insert table                  
                    else if (map.containsKey(importedKey.primary) == false && 
                            notNullable.contains(importedKey.foreign) == true) {
                        importedKeys.add(importedKey);
                        debug("\t" + importedKey.toString());
                    }
                }
            }

            //
            // Try to add tables
            //

            for (ImportedKey ik: importedKeys) {
                TableColumn primary = ik.primary;
                if (primary.column.equals(primary.table + "_id") == false) {
                    throw new UnhandledCaseException();
                }
                if (Arrays.asList(tablesIntoWhichWeAreAllowedToInsert).contains(primary.table) == false) {
                    throw new NotAllowedToInsertIntoTableException(primary.table);  
                }

                // Add to map for reference
                map.put(primary, null);

                // Add to insert statement for table
                insertStatements.add(ik.foreign.table, ik.foreign.column, new KeyReference(ik.primary));

                // Add to top of insert statements
                insertStatements.insertStatements.add(0, new InsertStatement(primary.table));
            }

            // 
            // Done?
            //

            if (importedKeys.isEmpty()) {
                satisfiedConstraints = true;
            }
        } while (satisfiedConstraints == false);

        //
        // Insert into database
        //

        debug("* --- *");

        // Keys
        Map<KeyReference, Object> keys = new HashMap<KeyReference, Object>();

        for (InsertStatement is: insertStatements.insertStatements) {
            Statement s = conn.createStatement();
            String sql = is.toSql(keys);
            debug(sql);

            s.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
            ResultSet rs = s.getGeneratedKeys();
            if (!rs.next()) {
                throw new UnhandledCaseException();
            }

            keys.put(new KeyReference(is.table, is.table + "_id"), rs.getString(1));

            rs.close();
            s.close();
        }
    }

    public static void main (String[] args)
    {
        Connection conn = null;

        try
        {
            String userName = "openmrs_user";
            String password = "Iw65GkNPQVOP";
            String url = "jdbc:mysql://localhost:3306/openmrs";
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(url, userName, password);
            conn.setAutoCommit(false);            

            // Test data
            Map<TableColumn, Object> map = new HashMap<TableColumn, Object>();
            map.put(new TableColumn("patient_identifier", "identifier"), "1234");
            map.put(new TableColumn("person_name", "given_name"), "'Bob'");

            // Given columns
            Map<TableColumn, Object> given = new HashMap<TableColumn, Object>();
            given.put(new TableColumn("users", "user_id"), "1");
            given.put(new TableColumn("patient_identifier_type", "patient_identifier_type_id"), "1");
            given.put(new TableColumn("location", "location_id"), "1");
            given.put(new TableColumn("tribe", "tribe_id"), "1");

            insertIntoDatabase(conn, map, given);
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            if (conn != null)
            {
                try
                {
                    conn.rollback();
                    conn.close();
                }
                catch (Exception e) { /* ignore close errors */ }
            }
        }
    }
}

I have whittled something together that uses Connector/J mysql-connector-java-5.1.8.jar

import java.sql.*;
import java.util.*;

class TableColumn {
    String table;
    String column;
    public TableColumn(String table, String column) {
        this.table = table; this.column = column;
    }
    public String toString() {
        return table+"."+column;
    }
    public boolean equals(Object obj) {
        if (obj instanceof TableColumn) {
            TableColumn tableColumnObj = (TableColumn)obj;
            return ((table.equals(tableColumnObj.table)) && (column.equals(tableColumnObj.column)));
        } else {
            return false;
        }
    }   
    public int hashCode() {
        int hashCode = 1;
        hashCode = 31*hashCode + (table==null ? 0 : table.hashCode());
        hashCode = 31*hashCode + (column==null ? 0 : column.hashCode());
        return hashCode;
    }
}   

class InsertStatement {
    String table;
    Map<String, String> columnValues = new LinkedHashMap<String, String>();
    public String toString(Map<String, String> tableColumnValueMap) {
        String columns = null;
        String values = null;
        for (Map.Entry<String, String> columnValue: columnValues.entrySet()) {
            String column = columnValue.getKey();
            String value = columnValue.getValue();

            if (value.startsWith("!@#$%")) {
                String key = value.substring("!@#$%".length());
                value = tableColumnValueMap.get(key);
                if (value == null) {
                    System.out.println("Cannot find tableColumnValueMap key \"" + key + "\"");
                    System.out.println("tableColumnMap:");
                    for (Map.Entry<String, String> tableColumnValueMapEntry: tableColumnValueMap.entrySet()) {
                        System.out.println("\t\"" + tableColumnValueMapEntry.getKey() +
                            "\" = \"" +
                            tableColumnValueMapEntry.getValue() +
                            "\"");
                    }
                    try { throw new Exception("Unhandled case!!!"); } catch (Exception e) { e.printStackTrace(); }
                    System.exit(1);
                }
            }

            if (columns == null) columns = column; else columns += ", " + column;
            if (values == null) values = value; else values += ", " + value;
        }

        // http://wiki.openmrs.org/display/archive/UUIDs
        if (!table.equals("patient")) {
            String column = "uuid";
            String value = "uuid()";

            if (columns == null) columns = column; else columns += ", " + column;
            if (values == null) values = value; else values += ", " + value;
        }

        return "insert into " + table + " (" + columns + ") values (" + values + ")";
    }
}

public class test {
    // constraintMap<<table, Map<column, referencedTable>>
    static Map<String, Map<String, TableColumn>> constraintMap = new HashMap<String, Map<String, TableColumn>>();

    static String columnsWeDoNotFillInAutomatically[] = { 
        "voided_by", "changed_by", "retired_by", "cause_of_death" 
    };

    static void insertIntoDatabaseHelper(Connection conn,
                                         Map<String, Map<String, String>> columnValueMapByTable,
                                         Map<TableColumn, String> given)
    {
        Vector<InsertStatement> insertStatements = new Vector<InsertStatement>();

        // Output
        System.out.println("columnValueMapByTable:");
        for (Map.Entry<String, Map<String, String>> entry: columnValueMapByTable.entrySet()) {
            String table = (String) entry.getKey();
            System.out.println(table);
            Map<String, String> columnValueMap = (Map<String, String>) entry.getValue();
            if (columnValueMap != null) {
                for (Map.Entry<String, String> columnValue: columnValueMap.entrySet()) {
                    String column = columnValue.getKey();
                    String value = columnValue.getValue();
                    System.out.println("\t" + column + " = \"" + value + "\"");
                }
            }
        }
        System.out.println("");         

        List<String> insertedTables = new Vector<String>();

        for (Map.Entry<String, Map<String, String>> entry: columnValueMapByTable.entrySet()) {
            InsertStatement is = new InsertStatement();
            is.table = (String) entry.getKey();

            Map<String, TableColumn> constraintColumnValueMap = constraintMap.get(is.table);            
            Map<String, String> columnValueMap = entry.getValue();      

            // Create statement
            if (columnValueMap != null) {
                for (Map.Entry<String, String> columnValue: columnValueMap.entrySet()) {
                    String column = columnValue.getKey();
                    String value = "'" + columnValue.getValue() + "'";

                    if (constraintColumnValueMap.containsKey(column)) {
                        System.out.println("Constrained columns should never be present in form data");
                        System.exit(1);
                    }

                    is.columnValues.put(column, value);
                }
            }

            if (constraintColumnValueMap != null) {
                // Find given columns in constrained columns - add those
                Map<String, TableColumn> remainingConstraints = new HashMap<String, TableColumn>();
                for (Map.Entry<String, TableColumn> columnTableColumn: constraintColumnValueMap.entrySet()) {
                    String column = columnTableColumn.getKey();
                    TableColumn tableColumn = columnTableColumn.getValue();
                    if (Arrays.asList(columnsWeDoNotFillInAutomatically).contains(column)) {
                        // don't fill in automatically
                    } else if (given.containsKey(tableColumn)) {
                        String givenValue = given.get(tableColumn);

                        is.columnValues.put(column, givenValue);
                    } else {
                        remainingConstraints.put(column, tableColumn);
                    }
                }               
                constraintColumnValueMap = remainingConstraints;

                // Remove columns named table_id for each table in map
                remainingConstraints = new HashMap<String, TableColumn>();
                for (Map.Entry<String, TableColumn> columnTableColumn: constraintColumnValueMap.entrySet()) {

                    String column = columnTableColumn.getKey();
                    TableColumn tableColumn = columnTableColumn.getValue();
                    if (columnValueMapByTable.containsKey(tableColumn.table) && 
                        tableColumn.column.equals(tableColumn.table + "_id")) {
                        if (!insertedTables.contains(tableColumn.table)) {

                            // Is table included at all
                            if (!columnValueMapByTable.containsKey(tableColumn.table)) {
                                try { throw new Exception("Unhandled case!!!"); } catch (Exception e) { e.printStackTrace(); }
                                System.out.println("tableColumn: " + tableColumn);
                                System.exit(1);                                                         
                            } else {

                                Map<String, Map<String, String>> newMap = new LinkedHashMap<String, Map<String, String>>();
                                for (Map.Entry<String, Map<String, String>> newEntry: columnValueMapByTable.entrySet()) {
                                    String table = newEntry.getKey();
                                    Map<String, String> map = newEntry.getValue();

                                    if (!table.equals(is.table) && !table.equals(tableColumn.table)) {
                                        // preserve order
                                        newMap.put(table, map);
                                    } else if (table.equals(is.table)) {
                                        newMap.put(tableColumn.table, columnValueMapByTable.get(tableColumn.table));
                                        newMap.put(table, map);
                                    }
                                }
                                insertIntoDatabaseHelper(conn, newMap, given);
                                return;                             
                            }

                            System.out.println("insertedTables:");
                            for (String table: insertedTables) {
                                System.out.println("\t" + table);
                            }
                        } else {
                            String value = "!@#$%" + tableColumn.toString();

                            is.columnValues.put(column, value);
                        }
                    } else {
                        remainingConstraints.put(column, tableColumn);                  
                    }
                }               
                constraintColumnValueMap = remainingConstraints;
            }

            // Any constrained columns? If so, add those tables to map (empty) and restart
            // (ordered map)
            if (constraintColumnValueMap != null &&
                !constraintColumnValueMap.isEmpty()) {
                Map<String, Map<String, String>> newMap = new LinkedHashMap<String, Map<String, String>>();
                for (Map.Entry<String, TableColumn> columnTableColumn: constraintColumnValueMap.entrySet()) {
                    String column = columnTableColumn.getKey();
                    TableColumn tableColumn = columnTableColumn.getValue();
                    System.out.println("\tConstraint: " + column + " = \"" + tableColumn.toString() + "\"");

                    if (!newMap.containsKey(tableColumn.table)) {
                        newMap.put(tableColumn.table, null);
                    } else {
                        try { throw new Exception("Unhandled case!!!"); } catch (Exception e) { e.printStackTrace(); }
                        System.exit(1);
                    }
                }                   
                newMap.putAll(columnValueMapByTable);
                insertIntoDatabaseHelper(conn, newMap, given);
                return;
            }

            insertStatements.add(is);
            insertedTables.add(is.table);
        }

        // Execute inserts
        Map<String, String> tableColumnValueMap = new HashMap<String, String>();
        for (InsertStatement is: insertStatements) {
            try {
                Statement s = conn.createStatement();
                String sql = is.toString(tableColumnValueMap);
                System.out.println("Sql: \"" + sql + "\"");
                int rows = s.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
                ResultSet rs = s.getGeneratedKeys();
                if (!rs.next()) {
                    try { throw new Exception("Unhandled case!!!"); } catch (Exception e) { e.printStackTrace(); }
                    System.exit(1);
                }           
                tableColumnValueMap.put(is.table + "." + is.table + "_id", rs.getString("GENERATED_KEY"));
                rs.close();
                s.close();
            } catch (SQLException sqle) {
                sqle.printStackTrace();
                System.exit(1);
            }
        }
    }

    // map - TableColumn, value to insert
    // given - TableColumn, value which are known (like user_id)
    public static void insertIntoDatabase(Connection conn,
                                          Map<TableColumn, String> map,
                                          Map<TableColumn, String> given) 
    {
        Map<String, Map<String, String>> columnValueMapByTable = new HashMap<String, Map<String, String>>();

        // Find all columns that are in a single table
        for (Map.Entry<TableColumn, String> entry: map.entrySet()) {
             Map<String, String> columnValueMap = null;

            // Does table exist?
            TableColumn tableColumn = (TableColumn) entry.getKey();
            if (columnValueMapByTable.containsKey(tableColumn.table)) {             
                columnValueMap = (Map<String, String>) columnValueMapByTable.get(tableColumn.table);
            } else {                
                columnValueMap = new HashMap<String, String>();
            }
            columnValueMap.put(tableColumn.column, (String) entry.getValue());
            columnValueMapByTable.put(tableColumn.table, columnValueMap);
        }

        // Helper
        insertIntoDatabaseHelper(conn, columnValueMapByTable, given);
    }

    public static void main (String[] args)
    {
        Connection conn = null;
        boolean exception = false;

        try
        {
            String userName = "openmrs_user";
            String password = "Iw65GkNPQVOP";
            String url = "jdbc:mysql://localhost:3306/information_schema";
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(url, userName, password);

            // find all constraints
            Statement s = conn.createStatement();
            s.executeQuery("select table_name, column_name, referenced_table_name, referenced_column_name " +
                "   from key_column_usage " +
                "   where table_schema = 'openmrs' and referenced_table_schema != ''");
            ResultSet rs = s.getResultSet();
            while (rs.next()) {
                String table = rs.getString("table_name");
                String column = rs.getString("column_name");
                Map<String, TableColumn> columnValueMap = null;
                if (constraintMap.containsKey(table)) {             
                    columnValueMap = constraintMap.get(table);
                } else {                
                    columnValueMap = new HashMap<String, TableColumn>();
                }
                TableColumn referencedColumn = new TableColumn(rs.getString("referenced_table_name"), rs.getString("referenced_column_name"));
                columnValueMap.put(column, referencedColumn);
                constraintMap.put(table, columnValueMap);
            }
            rs.close();
            s.close();

            // output
            System.out.println("Constraints: ");
            for (Map.Entry<String, Map<String, TableColumn>> entry: constraintMap.entrySet()) {
                String table = entry.getKey();
                System.out.println(table);

                Map<String, TableColumn> columnValueMap = entry.getValue();
                for (Map.Entry<String, TableColumn> newEntry: columnValueMap.entrySet()) {
                    System.out.println("\t" + newEntry.getKey() + ": " + newEntry.getValue().toString());
                }
            }
            System.out.println("");
        }
        catch (Exception e)
        {
            e.printStackTrace();
            exception = true;
        }
        finally
        {
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (Exception e) { /* ignore close errors */ }
            }
        }

        if (exception) System.exit(1); 

        try
        {
            String userName = "openmrs_user";
            String password = "Iw65GkNPQVOP";
            String url = "jdbc:mysql://localhost:3306/openmrs";
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(url, userName, password);
            conn.setAutoCommit(false);            

            // Test data
            Map<TableColumn, String> map = new HashMap<TableColumn, String>();
            map.put(new TableColumn("patient_identifier", "identifier"), "1234");
            map.put(new TableColumn("person_name", "given_name"), "Bob");

            // Given columns
            Map<TableColumn, String> given = new HashMap<TableColumn, String>();
            given.put(new TableColumn("users", "user_id"), "1");
            given.put(new TableColumn("patient_identifier_type", "patient_identifier_type_id"), "1");
            given.put(new TableColumn("location", "location_id"), "1");
            given.put(new TableColumn("tribe", "tribe_id"), "1");

            insertIntoDatabase(conn, map, given);
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            if (conn != null)
            {
                try
                {
                    conn.rollback();
                    conn.close();
                }
                catch (Exception e) { /* ignore close errors */ }
            }
        }
    }
}