Talk:Bklein7 Week 14

select propertytype.value from propertytype inner join dbreferencetype on
   (propertytype.dbreferencetype_property_hjid = dbreferencetype.hjid)
   where dbreferencetype.type = 'EnsemblBacteria' and propertytype.type = 'gene ID' order by propertytype.value;

select propertytype.value from propertytype inner join dbreferencetype on
   (propertytype.dbreferencetype_property_hjid = dbreferencetype.hjid)
   where dbreferencetype.type = 'EnsemblBacteria' and propertytype.type = 'gene ID'
   and propertytype.value ~ 'BP[0-9][0-9][0-9][0-9](A|B)' order by propertytype.value;

# Bordetella pertussis
bordetellapertussis_level_amount=1

bordetellapertussis_element_level0=uniprot/entry/gene/name&type&ORF

bordetellapertussis_query_level0=select count(*) from genenametype where type = 'ORF'; 

bordetellapertussis_table_name_level0=ORF

#
# wizard.properties
#

   @Override
    public TableManager getSystemTableManagerCustomizations(TableManager tableManager,
            TableManager primarySystemTableManager, Date version) throws SQLException, InvalidParameterException {
        // Start with the default OrderedLocusNames behavior.
        TableManager result = super.getSystemTableManagerCustomizations(tableManager, primarySystemTableManager,
                version);

        String sqlQuery = "select dbreferencetype.entrytype_dbreference_hjid as hjid, propertytype.value from propertytype inner join dbreferencetype on " +
                "(propertytype.dbreferencetype_property_hjid = dbreferencetype.hjid) " +
                "where dbreferencetype.type = 'EnsemblBacteria' and propertytype.type = 'gene ID' " +
                "and propertytype.value ~ 'BP[0-9][0-9][0-9][0-9](A|B)' order by propertytype.value";

        Connection c = ConnectionManager.getRelationalDBConnection();
        PreparedStatement ps;
        ResultSet rs;
        try {
            // Query, iterate, add to table manager.
            ps = c.prepareStatement(sqlQuery);
            rs = ps.executeQuery();
            while (rs.next()) {
                String hjid = Long.valueOf(rs.getLong("hjid")).toString();
                String id = rs.getString("value");
                result.submit("OrderedLocusNames", QueryType.insert, new Object[][] {
                    { "ID", id },
                    { "Species", "|" + getSpeciesName() + "|" },
                    { "Date", version },
                    { "UID", hjid }
                });
            }
        } catch(SQLException sqlexc) {
            logSQLException(sqlexc, sqlQuery);
        }

        return result;
    }

    private void logSQLException(SQLException sqlexc, String sqlQuery) {
        LOG.error("Exception trying to execute query: " + sqlQuery);
        while (sqlexc != null) {
            LOG.error("Error code: [" + sqlexc.getErrorCode() + "]");
            LOG.error("Error message: [" + sqlexc.getMessage() + "]");
            LOG.error("Error SQL State: [" + sqlexc.getSQLState() + "]");
            sqlexc = sqlexc.getNextException();
        }
    }

    private static final Log LOG = LogFactory.getLog(BordetellaPertussisUniProtSpeciesProfile.class);

}