Talk:Bklein7 Week 14 - Revision history

Bklein7: added code update

2015-12-14T01:58:47Z

added code update

Bklein7: fixed code formatting

2015-12-14T00:41:17Z

fixed code formatting

Bklein7: added further code

2015-12-14T00:39:19Z

added further code

Bklein7: added new sql query block

2015-12-10T23:50:23Z

added new sql query block

Bklein7: made the query able to be copied easily

2015-12-10T23:37:31Z

made the query able to be copied easily

Bklein7: added sql query for reference

2015-12-10T23:37:11Z

added sql query for reference

New page

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;

← Older revision		Revision as of 01:58, 14 December 2015
Line 20:		Line 20:
	# wizard.properties		# 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);
		+
		+	}

@@ Line 8: / Line 8: @@
      and propertytype.value ~ 'BP[0-9][0-9][0-9][0-9](A|B)' order by propertytype.value;
-# Bordetella pertussis
+ # Bordetella pertussis
-bordetellapertussis_level_amount=1
+ bordetellapertussis_level_amount=1
-−
-bordetellapertussis_element_level0=uniprot/entry/gene/name&type&ORF
+ bordetellapertussis_element_level0=uniprot/entry/gene/name&type&ORF
-−
-bordetellapertussis_query_level0=select count(*) from genenametype where type = 'ORF';
+ bordetellapertussis_query_level0=select count(*) from genenametype where type = 'ORF';
-−
-bordetellapertussis_table_name_level0=ORF
+ bordetellapertussis_table_name_level0=ORF
-−
-−
+ #
-# wizard.properties
+ # wizard.properties
-−
+ #

← Older revision		Revision as of 00:39, 14 December 2015
Line 7:		Line 7:
	where dbreferencetype.type = 'EnsemblBacteria' and propertytype.type = 'gene ID'		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;		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
		+	#

← Older revision		Revision as of 23:50, 10 December 2015
Line 2:		Line 2:
	(propertytype.dbreferencetype_property_hjid = dbreferencetype.hjid)		(propertytype.dbreferencetype_property_hjid = dbreferencetype.hjid)
	where dbreferencetype.type = 'EnsemblBacteria' and propertytype.type = 'gene ID' order by propertytype.value;		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;

@@ Line 1: / Line 1: @@
-select propertytype.value from propertytype inner join dbreferencetype on
+ select propertytype.value from propertytype inner join dbreferencetype on
-   (propertytype.dbreferencetype_property_hjid = dbreferencetype.hjid)
+    (propertytype.dbreferencetype_property_hjid = dbreferencetype.hjid)
-   where dbreferencetype.type = 'EnsemblBacteria' and propertytype.type = 'gene ID' order by propertytype.value;
+    where dbreferencetype.type = 'EnsemblBacteria' and propertytype.type = 'gene ID' order by propertytype.value;