public ChecklistDB(JbirdDB db,
    ResourceBundle pr) 
{
         
	dbase = db;
	log = dbase.getLogListener();
	progres = pr;
        nformatter.setGroupingUsed(false);
	add_clist_msg = progres.getString("checklists_-_added");
	add_clist_failedmsg = progres.getString("checklists_-_failed_to_add");
	add_clist_already = progres.getString("checklists_-_already_exists");
	add_species_msg = progres.getString("checklist_-_added_species");
	add_species_failedmsg = progres.getString("checklist_-_added_species_failed");
	del_species_msg = progres.getString("checklist_-_deleted_species");
}

 public final int addChecklist(String[] listinfo,
    int parent) throws SQLException 
{
	int rc = approveChecklistName(listinfo[LIST_NAME]);
	if (rc != APPROVED) {
		//log.error(MessageFormat.format(add_clist_already, oba));
		return rc;
	}
	String sql = "SELECT MAX(ListNo) FROM checklistinfo";
	int listnum = dbase.extractIntClose(dbase.sqlQuery(sql)) + 1;
	if (pnewchecklist == null) {
		sql = "INSERT INTO checklistinfo"
			+ " (ListNo, ListName, Description, Source,"
			+ " Contributor, Version, ParentList)"
			+ " VALUES (?, ?, ?, ?, ?, ?, ?)";
		pnewchecklist = dbase.jbirdconn.prepareStatement(sql);
	}
	pnewchecklist.clearParameters();
	pnewchecklist.setInt(1, listnum);
	pnewchecklist.setString(2, JbirdDB.clipToMax(listinfo[LIST_NAME],
					JbirdDB.LIST_NAME_LEN_MAX));
	pnewchecklist.setString(3, JbirdDB.clipToMax(listinfo[LIST_DESCRIPTION],
					JbirdDB.LIST_DESCRIPTION_LEN_MAX));
	pnewchecklist.setString(4, JbirdDB.clipToMax(listinfo[LIST_SOURCE],
					JbirdDB.LIST_SOURCE_LEN_MAX));
	pnewchecklist.setString(5, JbirdDB.clipToMax(listinfo[LIST_CONTRIBUTOR],
					JbirdDB.LIST_CONTRIBUTOR_LEN_MAX));
	pnewchecklist.setString(6, JbirdDB.clipToMax(listinfo[LIST_VERSION],
					JbirdDB.LIST_VERSION_LEN_MAX));
	pnewchecklist.setInt(7, parent);
	rc = pnewchecklist.executeUpdate();
	if (rc < = 0) {
		listnum = -1;
		Object[] oba = new Object[1];
		oba[0] = listinfo[LIST_NAME];
		log.error(MessageFormat.format(add_clist_failedmsg, oba));
	} else {
		Object[] obb = new Object[2];
		obb[0] = listinfo[LIST_NAME];
		obb[1] = nformatter.format(listnum);
		log.trace(MessageFormat.format(add_clist_msg, obb));
	}
	return listnum;
}

Add a new checklist to checklistinfo. Returns the number of the checklist if successful and -1 if not if not successful. Throws SQLException for most problems.

 public final int addSpecies(int checklist,
    int species) 
{
	int retcode = -1;
	try {
	   if (paddtochecklist == null) {
		paddtochecklist = dbase.jbirdconn.prepareStatement(
	   		"INSERT INTO checklist (ListNo, SpecNo) " +
	   		"VALUES ( ?, ? )");
			// parm 1 integer list number
			// parm 2 integer species number
	   }
	   paddtochecklist.clearParameters();
	   paddtochecklist.setInt(1, checklist);
	   paddtochecklist.setInt(2, species);
	   retcode = paddtochecklist.executeUpdate();
	}
	catch (SQLException e1) {
		dbase.unanticipatedError("addSpecies " +
				e1.getMessage());
	}
	return retcode;
}

Add a species to the checklist.

Does not log.

 public final int addSpeciesLog(int checklist,
    int species,
    String listname,
    String speciesname) 
{
	int answer = addSpecies(checklist, species);
	add_species_oba[0] = listname;
	add_species_oba[1] = speciesname;
	add_species_oba[2] = nformatter.format(species);
	if (answer  > 0) {
		log.trace(MessageFormat.format(add_species_msg, add_species_oba));
	} else {
		log.trace(MessageFormat.format(add_species_failedmsg, add_species_oba));
	}
	return answer;
}

 public final int approveChecklistName(String name) throws SQLException 
{
	if (name != null) {
		name = name.trim();
		if (name.length() <  1) { // name blank
			return BAD_NAME;
		}
	} else {	// name null
		return BAD_NAME;
	}
	String sql = "SELECT COUNT(ListNo) FROM checklistinfo"
			+ " WHERE ListName = ?";
	Object[] oba = new Object[1];
	oba[0] = JbirdDB.clipToMax(name, JbirdDB.LIST_NAME_LEN_MAX);
	int[] types = new int[] { java.sql.Types.VARCHAR };
	int count = dbase.singleIntQuery(sql, oba, types);
	if (count  > 0) {
		oba[0] = name;
		log.error(MessageFormat.format(add_clist_already, oba));
		return EXISTS_ALREADY;
	}
	return APPROVED;
}

Returns APPROVED, ALREADY_EXISTS, BAD_NAME. BAD_NAME is returned if name is null, length zero, or whitespace .

 public final int changeChecklistParents(int oldparent,
    int newparent) throws SQLException 
{
		String sql = "UPDATE checklistinfo"
			+ " SET ParentList = ? "
			+ " WHERE ParentList = ?";
		Object[] parmvals = new Object[2];
		parmvals[0] = new Integer(newparent);
		parmvals[1] = new Integer(oldparent);
		int[] parmtypes = new int[] { java.sql.Types.INTEGER, 
						java.sql.Types.INTEGER };
		return dbase.sqlUpdate(sql, parmvals, parmtypes);
}

Change parent of checklists that have old checklist as their parent to newparent. This method does not log changnes. It is up to the calling method to log the transaction.

 public final boolean checklistContains(int checklist,
    int species) 
{
	boolean answer = false;
	ResultSet rs = null;
	try {
		if (pchecklistcontains == null) {
			pchecklistcontains = 
				dbase.jbirdconn.prepareStatement(
	   			"SELECT COUNT(SpecNo)"
				+ " FROM checklist"
				+ " WHERE ListNo = ? AND SpecNo = ?");
				// parm 1 integer list number
				// parm 2 integer species number
		} // pchecklistcontains
		pchecklistcontains.clearParameters();
		pchecklistcontains.setInt(1, checklist);
		pchecklistcontains.setInt(2, species);
		rs = pchecklistcontains.executeQuery();
		rs.first();
		int count = rs.getInt(1);
		if (count  > 0) {
			answer = true;
		} // count
	} // try
	catch (SQLException e) {
		dbase.unanticipatedError("checklistContains " +
				e.getMessage());
		log.exception(e);
	} // catch
	finally {
		if (rs != null) {
			try {
				rs.close();
			} // try
			catch (SQLException e1) {
			} // catch
		} // rs
	} // finally
	return answer;
}

Return true if the checklist contains the species.

 public final int checklistDelete(int checklist,
    int species) throws SQLException 
{
 	String sqlstmt = new StringBuffer(70)
			.append("DELETE FROM checklist WHERE "
			  + "ListNo = ")
			.append(nformatter.format(checklist))
			.append(" AND SpecNo = ")
			.append(nformatter.format(species))
			.toString();
	int rc = 0;
	try {
	    rc = dbase.sqlUpdate(sqlstmt);
	}
	catch (SQLException e) {	// log and throw again
	    dbase.unanticipatedError("checklistDelete " + 
				e.getMessage());
	    throw(e);
	}
	return rc;
}

Delete a species from a checklist.

 public final int checklistDeleteAll(int listno) throws SQLException 
{
	String sql = "DELETE FROM checklist WHERE ListNo = ?";
	Object[] parmval = new Object[1];
	parmval[0] = new Integer(listno);
	int[] parmtype = new int[] { java.sql.Types.INTEGER };
	return dbase.sqlUpdate(sql, parmval, parmtype);
}

Remove all species from the checklist named. Return the number of species removed.

 public final int checklistDeleteLog(int checklist,
    int species,
    String listname,
    String speciesname) throws SQLException 
{
	int answer = checklistDelete(checklist, species);
	if (answer  > 0) {
		del_species_oba[0] = listname;
		del_species_oba[1] = speciesname;
		del_species_oba[2] = nformatter.format(species);
		log.trace(MessageFormat.format(del_species_msg, del_species_oba));
	}	// no logging of else because that might be OK
	return answer;
}

Delete species from checklist and log deletion. No log entry is made if nothing is deleted.

 public final  void close() 
{
	dbase.closePreparedStatement(paddtochecklist);
	dbase.closePreparedStatement(pchecklistcontains);
	dbase.closePreparedStatement(pnewchecklist);
	dbase.closePreparedStatement(pchildcount);
	dbase.closePreparedStatement(pchildnumbers);
	dbase.closePreparedStatement(pinfo);
	paddtochecklist = null;
	pchecklistcontains = null;
	pnewchecklist = null;
	pchildcount = null;
	pchildnumbers = null;
	pinfo = null;
}

Closes prepared statements in object. Required. Call before abandoning object. Not a substitute for nullify.

 public final synchronized Object[] getChecklistInfo(int listno) throws SQLException 
{
	if (pinfo == null) {
		pinfo = dbase.jbirdconn.prepareStatement(INFO_QUERY);
	}
	pinfo.clearParameters();
	pinfo.setInt(1, listno);
	Object[] answer = null;
	ResultSet rs = pinfo.executeQuery();
	try {
	   rs.beforeFirst();
	   if(rs.next()) {
		answer = new Object[LIST_LENGTH];
		answer[LIST_NAME] = rs.getString("ListName");
		answer[LIST_DESCRIPTION] = rs.getString("Description");
		answer[LIST_SOURCE] = rs.getString("Source");
		answer[LIST_CONTRIBUTOR] = rs.getString("Contributor");
		answer[LIST_VERSION] = rs.getString("Version");
		int parent = rs.getInt("ParentList");
		if (rs.wasNull()) {
			parent = 0;
		}
		answer[LIST_PARENT] = new Integer(parent);
	   } else {		// no info on 
		Object[] oba = new Object[1];
		oba[0] = nformatter.format(listno);
		log.warning(MessageFormat.format(
			progres.getString("no_checklist"), oba));
	   }
        }
        catch(SQLException e) {
		dbase.unanticipatedError("getChecklistinfo: " + 
				  e.getMessage());
		throw(e);
	}
	finally {
	   if (rs != null) {
		try {
			rs.close();
		} catch (SQLException sqle) {
			log.exception(sqle);
		}
	   }
	}
	return answer;
}

 public final String[] getChecklistNames(int[] clnos) throws SQLException 
{
				// generate comma delimited set of observers
	String sclnos = MyStringArray.join(", ", 
			    MyStringArray.formatIntArray(clnos, nformatter));
				// build query and get result set
	String query = new StringBuffer()
			.append("SELECT ListNo, ListName FROM checklistinfo where " +
			        "ListNo IN ( ")
			.append(sclnos)
			.append(" )")
			.toString();
	ResultSet resultset = dbase.sqlQuery(query);
				// extract answer
	String[] answer = new String[clnos.length];
	int i;
	try {
		resultset.beforeFirst();
		while (resultset.next()) {
			int thisno = resultset.getInt(1);
			i = 0;
			while (i <  clnos.length && thisno != clnos[i] ) {
				i++;
			}
			if (i <  clnos.length) {
				answer[i] = resultset.getString(2);
			}
		}
	}
	catch (SQLException e) {
		throw (e);
	}
	finally {
		try {
			resultset.close();
		}
		catch (SQLException e1) {
		    dbase.unanticipatedError("getChecklistNames: closing ResultSet " + 
				e1.getMessage());
		}
	}
			// fill missing values with empty strings
	for(i = 0; i <  clnos.length; i++) {
		if (answer[i] == null) {
			answer[i] = "";
		}
	}
	return answer;
}

Return names of the checklists passed in the argument.

 public final synchronized int getChildCount(int listnum) throws SQLException 
{
	if (pchildcount == null) {
		pchildcount = dbase.jbirdconn.prepareStatement(CHILDCOUNT_QUERY);
	}
	pchildcount.clearParameters();
	pchildcount.setInt(1, listnum);
	ResultSet rs = pchildcount.executeQuery();
	int answer = dbase.extractInt(rs);
	rs.close();
	return answer;
}

Count the number of checklists that reference the argument as their parent. Used by ChecklistNode to implement TreeNode.

 public final synchronized int[] getChildNumbers(int listno) throws SQLException 
{
	if (pchildnumbers == null) {
		pchildnumbers = dbase.jbirdconn.prepareStatement(CHILD_NUMBERS_QUERY);
	}
	int[] answer = new int[getChildCount(listno)];
	pchildnumbers.clearParameters();
	pchildnumbers.setInt(1, listno);
	ResultSet rs = pchildnumbers.executeQuery();
	try {
		int at = 0;
		while(rs.next()) {
			answer[at] = rs.getInt(1);
			at ++;
		}
	}
	catch (SQLException sqle) {
		throw (sqle);
	}
	finally {
		rs.close();
	}
	return answer;
}

Return primary keys of children of the given checklist.

 public final LogListener getLogListener() 
{
	return log;
}

Return the LogListener that is being used to log events.

 public final int getParentListNum(int listno) throws SQLException 
{
	String sql = "SELECT ParentList FROM checklistinfo"
			+ " WHERE ListNo = ?";
	Object[] parmval = new Object[1];
	parmval[0] = new Integer(listno);
	int[] parmtype = new int[] { java.sql.Types.INTEGER };
	return dbase.singleIntQuery(sql, parmval, parmtype);
}

Return the primary key of the parent of the given list number.

 public final int[] getParentListNums(int[] listnos) throws SQLException 
{
	String sql = new StringBuffer()
		.append("SELECT DISTINCT ParentList FROM checklistinfo"
			+ " WHERE ListNo IN (")
		.append(dbase.joinIntArray(", ", listnos))
		.append(")")
		.toString();
	ResultSet rs = dbase.sqlQuery(sql);
	int[] answer = null;
	try {
		rs.last();
		int rowcount = rs.getRow();
		rs.beforeFirst();
		answer = new int[rowcount];
		int idx = 0;
		while (rs.next()) {
			answer[idx] = rs.getInt(1);
			if (rs.wasNull()) {
				answer = new int[] {0};
				break;
			}
			if (answer[idx] == 0) {
				answer = new int[] {0};
				break;
			}
			idx ++;
		}
	} catch (SQLException sqle) {
		throw(sqle);
	} finally {
		dbase.closeSqlQuery(rs);
	}
	return answer;
}

Return the set of checklists that contains parents of the set of checklists in the argument. If any one of the the parents is zero or NULL, an int array of lenth 1 that contains the value zero is returned.

 public final synchronized String getTreeName(int listnum) throws SQLException 
{
	String answer = "";
	Object[] info = getChecklistInfo(listnum);
	if (info != null) {
		answer = new StringBuffer(128)
					.append((String)info[LIST_NAME])
					.append(" - ")
					.append((String)info[LIST_DESCRIPTION])
					.toString();
	}
	return answer;
}

Return the name of a checklist that is suitable for display in a JTree.

 public final  void nullify() 
{
		progres = null;
		nformatter = null;
		dbase = null;
		log = null;
        	add_clist_msg = null;
        	add_clist_failedmsg = null;
        	add_clist_already = null;
        	add_species_msg = null;
        	add_species_oba = null;
        	del_species_oba = null;
        	add_species_failedmsg = null;
        	del_species_msg = null;
}

Optional. Sets fields to null. Does not call close.

 public final int removeChecklist(int listno) throws SQLException 
{
	return removeChecklist(listno, getParentListNum(listno));
}

Remove a checklist from the database. Checklists that are children of the targets will have their parents changed from the target to the parent of the target. Well, not implmented yet. Parents are set to zero.

 public final int removeChecklist(int listno,
    int newparent) throws SQLException 
{
	Object[] info = getChecklistInfo(listno);
	String listname = (String)info[LIST_NAME];
	int delspec = 0;
	int rc = 0;
	dbase.jbirdconn.setAutoCommit(false);
	try {
			// reassign parents
		int changecount = changeChecklistParents(listno, newparent);
			// delete species
		delspec = checklistDeleteAll(listno);
			// delete info
		String sql = "DELETE FROM checklistinfo WHERE ListNo = ?";
		Object[] parmval = new Object[1];
		parmval[0] = new Integer(listno);
		int[] parmtype = new int[] { java.sql.Types.INTEGER };
		rc = dbase.sqlUpdate(sql, parmval, parmtype);
			// commit
		dbase.jbirdconn.commit();
			// log
		Object[] oba = new Object[] { listname, 
					nformatter.format(delspec),
					nformatter.format(changecount),
					nformatter.format(newparent) };
		log.trace(MessageFormat.format(
			progres.getString("remove_checklist"), oba));
	} catch (SQLException sqle) {	// rely on catcher to log
		dbase.jbirdconn.rollback();
		throw(sqle);
	} 
	finally {
		dbase.jbirdconn.setAutoCommit(true);
	}
	return rc;
}

Remove a checklist from the database. Checklists in the db that reference the target as their parent will have their parents reassigned to be newparent.

The three transactions (i.e., replace parents, delete species, and delete metadata) are performed as a unit. If one fails, the database is rolled back so that none of the changes are effected.

Returns the number of records deleted from table checklistinfo.

 public final int removeChecklists(int[] listnos) throws SQLException 
{
	int answer = 0;
	for (int idx = 0; idx <  listnos.length; idx ++) {
		answer += removeChecklist(listnos[idx]);
	}
	return answer;
}

 public final int updateChecklist(int listno,
    Object[] data) throws SQLException 
{
	String sql = "UPDATE checklistinfo SET ListName = ?,"
			+ " Description = ?,"
			+ " Source = ?,"
			+ " Contributor = ?,"
			+ " Version = ?,"
			+ " ParentList = ?"
			+ " WHERE ListNo = ?";
	Object[] oba = new Object[LIST_LENGTH + 1];
	oba[0] = data[LIST_NAME];
	oba[1] = data[LIST_DESCRIPTION];
	oba[2] = data[LIST_SOURCE];
	oba[3] = data[LIST_CONTRIBUTOR];
	oba[4] = data[LIST_VERSION];
	oba[5] = data[LIST_PARENT];
	oba[6] = new Integer(listno);
	int[] types = new int[] { java.sql.Types.VARCHAR,
				java.sql.Types.VARCHAR,
				java.sql.Types.VARCHAR,
				java.sql.Types.VARCHAR,
				java.sql.Types.VARCHAR,
				java.sql.Types.INTEGER,
				java.sql.Types.INTEGER
	};
	int answer = dbase.sqlUpdate(sql, oba, types);
	if (answer  > 0) {
		oba = new Object[] { data[LIST_NAME],
					nformatter.format(listno) };
		log.trace(MessageFormat.format(
			progres.getString("checklist_modified"), oba));
	}
	return answer;
}