Context Navigation

← Previous Change
Next Change →

Changeset 408 for branches

Timestamp:

06/11/10 19:05:19 (20 months ago)

Author:

bturner

Message:

Convert all db calls to follow canonical pattern of resource usage.

Location:

branches/ds_pool/src/org/bridgedb/rdb

Files:

: 5 modified

SimpleGdb.java (modified) (2 diffs)
SimpleGdbFactory.java (modified) (1 diff)
SimpleGdbImpl2.java (modified) (2 diffs)
SimpleGdbImpl3.java (modified) (2 diffs)
SimpleGdbImplCommon.java (modified) (2 diffs)

Legend:

: Unmodified
: Added
: Removed

branches/ds_pool/src/org/bridgedb/rdb/SimpleGdb.java

r405	r408
18	18
19	19	import java.sql.Connection;
20		~~import java.sql.DriverManager;~~
21		~~import java.sql.PreparedStatement;~~
22	20	import java.sql.ResultSet;
23	21	import java.sql.SQLException;
24
	22	import java.sql.Statement;
	23
	24	import org.apache.commons.logging.Log;
	25	import org.apache.commons.logging.LogFactory;
25	26	import org.bridgedb.DataSource;
26	27	import org.bridgedb.IDMapperException;
27	28
28	29	/**
29		* SimpleGdb is the main implementation of the Gdb interface,
30		* for dealing with single SQL-based pgdb's.
31		* It's responsible for creating and querying a single
	30	* SimpleGdb is the main implementation of the Gdb interface, for dealing with
	31	* single SQL-based pgdb's. It's responsible for creating and querying a single
32	32	* pgdb relational database through the JDBC interface.
33	33	* <p>
34		* It wraps SQL statements in methods,
35		* so the rest of the apps don't need to know the
36		* details of the Database schema.
37		* <p>
38		* It delegates dealing with the differences between
39		* various RDBMS's (Derby, Hsqldb etc.)
40		* to a DBConnector instance.
41		* A correct DBConnector instance needs to be
42		* passed to the constructor of SimpleGdb.
43		* <p>
44		* In the PathVisio GUI environment, use GdbManager
45		* to create and connect one or two centralized Gdb's.
46		* This will also automatically
47		* find the right DBConnector from the preferences.
48		* <p>
49		* In a head-less or test environment, you can bypass GdbManager
50		* and use SimpleGdb directly
51		* to create or connect to one or more pgdb's of any type.
	34	* It wraps SQL statements in methods, so the rest of the apps don't need to
	35	* know the details of the Database schema.
	36	* <p>
	37	* It delegates dealing with the differences between various RDBMS's (Derby,
	38	* Hsqldb etc.) to a DBConnector instance. A correct DBConnector instance needs
	39	* to be passed to the constructor of SimpleGdb.
	40	* <p>
	41	* In the PathVisio GUI environment, use GdbManager to create and connect one or
	42	* two centralized Gdb's. This will also automatically find the right
	43	* DBConnector from the preferences.
	44	* <p>
	45	* In a head-less or test environment, you can bypass GdbManager and use
	46	* SimpleGdb directly to create or connect to one or more pgdb's of any type.
52	47	*/
53	48	public abstract class SimpleGdb extends IDMapperRdb
54	49	{
55	50
	51	private static final Log LOG = LogFactory.getLog(SimpleGdb.class);
	52
56	53	private JdbcConnectionPool pool;
57	54
58
59		private final String connectionString;
60		/**
61		* Create IDMapper based on an existing SQL connection.
62		* @param con Existing SQL Connection.
63		*/
64		SimpleGdb(String dbName, String connectionString)
65		{
66		this.connectionString = connectionString;
67		this.dbName = dbName;
68
69		try
70		{
71		// there's something a bit weird that this class does not know
72		// it's underlying driver...
73		String driverClassName = null;
74		pool = new JdbcConnectionPool(dbName, driverClassName, connectionString);
	55	private boolean connected;
	56
	57	protected final String dbName;
	58
	59	/**
	60	* Create IDMapper based on an existing SQL connection.
	61	*
	62	* @param data_
	63	* .con Existing SQL Connection.
	64	*/
	65	SimpleGdb(String theDbName,
	66	String connectionString)
	67	{
	68
	69	dbName = theDbName;
	70
	71	try
	72	{
	73	// there's something a bit weird that this class does not know
	74	// it's underlying driver...
	75	String driverClassName = null;
	76	pool = new JdbcConnectionPool(dbName,
	77	driverClassName,
	78	connectionString);
	79	connected = true;
75	80	}
76	81	catch (IDMapperException e)
…	…
78	83	throw new RuntimeException(e);
79	84	}
80		}
81
82		private boolean singleConnection = true;
83		private boolean neverCloseConnection = true;
84
85		/**
86		* helper class that handles the life cycle of a connection, query and resultset.
87		* <p>
88		* The sql for a query is passed in at construction time.
89		* Before each query, call init(). This will lead to lazy initialization of the
90		* connection and preparedstatement objects, if necessary. Set the query parameters
91		* using setString(int, String). Get the resultSet using
92		* Do not close the resultset! This will be closed for you when you call cleanup().
93		* Always call cleanup() in a finally block.
94		* <p>
95		* The advantages of using QueryLifeCycle are:
96		* <ul>
97		* <li>guarantee to close preparedstatement, resultset and connection if necessary.
98		* <li>in case of connection pooling, preparedstatement and connection are kept together as long
99		* as possible.
100		* <li>lazy initialization of prepared statement
101		* <li>always uses preparedstatement, so safe from SQL injection.
102		* </ul>
103		* <p>
104		* This class is not static because it needs SimpleGdb.getConnection().
105		*/
106		final class QueryLifeCycle
107		{
108		/**
109		* Initialize with given SQL string, but don't create PreparedStatement yet.
110		* Valid to call before database connection is created.
111		* @param aSql SQL query
112		*/
113		public QueryLifeCycle(String aSql)
114		{
115		sql = aSql;
116		}
117
118		private Connection con = null;
119		private ResultSet rs = null;
120		private PreparedStatement pst = null;
121		private final String sql;
122		private boolean inited = false;
123
124		public static final int QUERY_TIMEOUT = 20; //seconds
125		public static final int NO_LIMIT = 0;
126		public static final int NO_TIMEOUT = 0;
127
128		public void init(int limit) throws SQLException
129		{
130		init();
131		pst.setQueryTimeout(QUERY_TIMEOUT);
132		if(limit > NO_LIMIT)
133		{
134		pst.setMaxRows(limit);
135		}
136		}
137
138		/**
139		* Initialize connection and PreparedStatement lazily.
140		* <p>
141		* @throws SQLException when a PreparedStatement could not be created
142		*/
143		public void init() throws SQLException
144		{
145		if (inited) throw new IllegalStateException("Must call cleanup() between two init() calls");
146		try
147		{
148		if (con == null) con = getConnection();
149		if (pst == null)
150		{
151		pst = con.prepareStatement(sql);
152		}
153		}
154		finally { inited = true; }
155		}
156
157		public void setString (int index, String val) throws SQLException
158		{
159		if (!inited) throw new IllegalStateException("Must call init() before setString()");
160		pst.setString(index, val);
161		}
162
163		public ResultSet executeQuery() throws SQLException
164		{
165		if (!inited) throw new IllegalStateException("Must call init() before executeQuery()");
166		rs = pst.executeQuery();
167		return rs;
168		}
169
170		/**
171		* Clean up resultset. If keepConnection is false, preparedstatement
172		* and connection are cached. If keepConnection is true, they are closed as well.
173		* The later is useful when using connection pooling.
174		* <p>
175		* Always call this in a finally block!
176		* */
177		public void cleanup()
178		{
179		if (!inited) throw new IllegalStateException("Must call init() before cleanup()");
180		inited = false;
181		if (rs != null) try { rs.close(); } catch (SQLException ignore) {}
182		if (neverCloseConnection) return;
183		if (pst != null) try { pst.close(); } catch (SQLException ignore) {}
184		pst = null;
185		if (con != null) try { con.close(); } catch (SQLException ignore) {}
186		con = null;
187		}
188		}
189
190		private Connection con = null;
191
192		synchronized public Connection getConnection() throws SQLException
193		{
194		/*
195		// if singleConnection is true, each call to getConnection() will return the same object.
196		// if singleConnection is false, each call to getConneciton() will lead to a new connection object being created.
197		if (!singleConnection \|\| con == null)
198		{
199		con = DriverManager.getConnection(connectionString);
200		con.setReadOnly(true);
201		}
202		return con;
203		*/
204		return pool.getConnection();
205		}
206
207		/**
208		* The {@link Connection} to the Gene Database.
209		*/
210
211		//private Connection con = null;
212
213		/** {@inheritDoc} */
214		final public boolean isConnected() {
215		//return con != null;
216		return true;
217		}
218
219		protected final String dbName;
220
221		/** {@inheritDoc} */
222		@Override final public String getDbName() { return dbName; }
223
224		/** {@inheritDoc} */
225		final public void close() throws IDMapperException
226		{
227		// try
228		// {
229		// con.close();
230		// }
231		// catch (SQLException ex)
232		// {
233		// throw new IDMapperException (ex);
234		// }
235		// con = null;
236		}
237
238		public static final int NO_LIMIT = 0;
239		public static final int NO_TIMEOUT = 0;
240		public static final int QUERY_TIMEOUT = 5; //seconds
241
242		/**
243		@return number of rows in gene table.
244		@throws IDMapperException on failure
245		*/
246		final public int getGeneCount() throws IDMapperException
247		{
248		int result = 0;
249		try
250		{
251		ResultSet r = getConnection().createStatement().executeQuery("SELECT COUNT(*) FROM " + "datanode");
252		r.next();
253		result = r.getInt (1);
254		r.close();
255		}
256		catch (SQLException e)
257		{
258		throw new IDMapperException (e);
259		}
260		return result;
261		}
262
263		/**
264		* @param ds DataSource to count identifiers for.
265		@return number of identifiers table for the given datasource
266		@throws IDMapperException on failure
267		*/
268		final public int getGeneCount(DataSource ds) throws IDMapperException
269		{
270		int result = 0;
271		try
272		{
273		ResultSet r = getConnection().createStatement().executeQuery(
274		"SELECT COUNT(*) FROM datanode WHERE code = '" + ds.getSystemCode() + "'");
275		r.next();
276		result = r.getInt (1);
277		r.close();
278		}
279		catch (SQLException e)
280		{
281		throw new IDMapperException (e);
282		}
283		return result;
284		}
285
	85	}
	86
	87	public Connection getConnection() throws SQLException
	88	{
	89
	90	return pool.getConnection();
	91	}
	92
	93	/** {@inheritDoc} */
	94	@Override
	95	final public String getDbName()
	96	{
	97	return dbName;
	98	}
	99
	100	/**
	101	* @return number of rows in gene table.
	102	* @throws IDMapperException
	103	* on failure
	104	*/
	105	final public int getGeneCount() throws IDMapperException
	106	{
	107	int result = 0;
	108
	109	ResultSet r = null;
	110	Statement s = null;
	111	Connection c = null;
	112	try
	113	{
	114	c = getConnection();
	115	s = c.createStatement();
	116	r = s.executeQuery("SELECT COUNT(*) FROM "
	117	+ "datanode");
	118	r.next();
	119	result = r.getInt(1);
	120	}
	121	catch (SQLException e)
	122	{
	123	throw new IDMapperException(e);
	124	}
	125	finally
	126	{
	127	closeSilently(r, s, c);
	128	}
	129	return result;
	130	}
	131
	132	/**
	133	* @param ds
	134	* DataSource to count identifiers for.
	135	* @return number of identifiers table for the given datasource
	136	* @throws IDMapperException
	137	* on failure
	138	*/
	139	final public int getGeneCount(DataSource ds) throws IDMapperException
	140	{
	141	int result = 0;
	142
	143	ResultSet r = null;
	144	Statement s = null;
	145	Connection c = null;
	146
	147	try
	148	{
	149	c = getConnection();
	150	s = c.createStatement();
	151	r = s.executeQuery("SELECT COUNT(*) FROM datanode WHERE code = '"
	152	+ ds.getSystemCode() + "'");
	153
	154	r.next();
	155	result = r.getInt(1);
	156	}
	157	catch (SQLException e)
	158	{
	159	throw new IDMapperException(e);
	160	}
	161	finally
	162	{
	163	closeSilently(r, s, c);
	164	}
	165
	166	return result;
	167	}
	168
	169	protected void closeSilently(Connection c)
	170	{
	171	if (c != null)
	172	{
	173	try
	174	{
	175	c.close();
	176	}
	177	catch (SQLException e)
	178	{
	179	LOG.error("Error closing result set: " + e, e);
	180	}
	181	}
	182	}
	183
	184	protected void closeSilently(Statement s)
	185	{
	186	if (s != null)
	187	{
	188	try
	189	{
	190	s.close();
	191	}
	192	catch (SQLException e)
	193	{
	194	LOG.error("Error closing statement: " + e, e);
	195	}
	196	}
	197	}
	198
	199	protected void closeSilently(ResultSet rs)
	200	{
	201	if (rs != null)
	202	{
	203	try
	204	{
	205	rs.close();
	206	}
	207	catch (SQLException e)
	208	{
	209	LOG.error("Error closing conection: " + e, e);
	210	}
	211	}
	212	}
	213
	214	public void closeSilently(ResultSet r,
	215	Statement s,
	216	Connection c)
	217	{
	218
	219	closeSilently(r);
	220
	221	closeSilently(s);
	222
	223	closeSilently(c);
	224
	225	}
	226
	227	@Override
	228	public void close() throws IDMapperException
	229	{
	230	if (pool != null)
	231	{
	232
	233	pool.shutdownDriver();
	234
	235	}
	236	connected = false;
	237	}
	238
	239	@Override
	240	public boolean isConnected()
	241	{
	242
	243	return connected;
	244	}
	245
	246
286	247	}

branches/ds_pool/src/org/bridgedb/rdb/SimpleGdbFactory.java

r405	r408
77	77	finally
78	78	{
	79
79	80	if (r != null) try { r.close(); } catch (SQLException ignore) {}
80	81	if (stmt != null) try { stmt.close(); } catch (SQLException ignore) {}

branches/ds_pool/src/org/bridgedb/rdb/SimpleGdbImpl2.java

r405	r408
18	18
19	19	import java.sql.Connection;
	20	import java.sql.PreparedStatement;
20	21	import java.sql.ResultSet;
21	22	import java.sql.SQLException;
	23	import java.sql.Statement;
22	24	import java.util.HashMap;
23	25	import java.util.HashSet;
…	…
32	34	/** {@inheritDoc} */
33	35	class SimpleGdbImpl2 extends SimpleGdbImplCommon
34		{
35		private static final int GDB_COMPAT_VERSION = 2; //Preferred schema version
36
37		private final SimpleGdb.QueryLifeCycle qBackpage = new SimpleGdb.QueryLifeCycle(
38		"SELECT backpageText FROM datanode " +
39		" WHERE id = ? AND code = ?"
40		);
41
42		/**
43		* get Backpage info. In Schema v2, this was not stored in
44		* the attribute table but as a separate column, so this is treated
45		* as a special case. This method is called by <pre>getAttribute (ref, "Backpage")</pre>
46		* @param ref the entity to get backpage info for.
47		* @return Backpage info as string
48		* @throws IDMapperException when database is unavailable
49		*/
50		private String getBpInfo(Xref ref) throws IDMapperException
51		{
52		final QueryLifeCycle pst = qBackpage;
53		synchronized (pst)
54		{
55		try {
56		pst.init();
57		pst.setString (1, ref.getId());
58		pst.setString (2, ref.getDataSource().getSystemCode());
59		ResultSet r = pst.executeQuery();
60		String result = null;
61		if (r.next())
62		{
63		result = r.getString(1);
64		}
65		return result;
66		} catch (SQLException e) { throw new IDMapperException (e); } //Gene not found
67		finally {pst.cleanup(); }
68		}
69		}
70
71		/**
72		* Opens a connection to the Gene Database located in the given file.
73		* A new instance of this class is created automatically.
74		* @param dbName The file containing the Gene Database.
75		* @param data_.con An existing SQL Connector.
76		* @param props PROP_RECREATE if you want to create a new database (possibly overwriting an existing one)
77		* or PROP_NONE if you want to connect read-only
78		* @throws IDMapperException when the database could not be created or connected to
79		*/
80		public SimpleGdbImpl2(String dbName, String connectionString) throws IDMapperException
81		{
82		super (dbName, connectionString);
83
84		if(dbName == null) throw new NullPointerException();
85		checkSchemaVersion();
86		}
87
88		/**
89		* look at the info table of the current database to determine the schema version.
90		* @throws IDMapperException when looking up the schema version failed
91		*/
92		private void checkSchemaVersion() throws IDMapperException
93		{
94		int version = 0;
95		try
96		{
97		ResultSet r = getConnection().createStatement().executeQuery("SELECT schemaversion FROM info");
98		if(r.next()) version = r.getInt(1);
99		}
100		catch (SQLException e)
101		{
102		//Ignore, older db's don't even have schema version
103		}
104		if(version != GDB_COMPAT_VERSION)
105		{
106		throw new IDMapperException ("Implementation and schema version mismatch");
107		}
108		}
109
110		private static final Map<String, String> ATTRIBUTES_FROM_BACKPAGE;
111
112		static
113		{
114		ATTRIBUTES_FROM_BACKPAGE = new HashMap<String, String>();
115		ATTRIBUTES_FROM_BACKPAGE.put ("Chromosome", "<TH>Chr:<TH>([^<]*)<");
116		ATTRIBUTES_FROM_BACKPAGE.put ("Description", "<TH>Description:<TH>([^<]*)<");
117		ATTRIBUTES_FROM_BACKPAGE.put ("Synonyms", "<TH>Synonyms:<TH>([^<]*)<");
118		ATTRIBUTES_FROM_BACKPAGE.put ("Symbol", "<TH>(?:Gene Symbol\|Metabolite):<TH>([^<]*)<");
119		ATTRIBUTES_FROM_BACKPAGE.put ("BrutoFormula", "<TH>Bruto Formula:<TH>([^<]*)<");
120		}
121
122		/** {@inheritDoc} */
123		public Set<String> getAttributes(Xref ref, String attrname)
124		throws IDMapperException
125		{
126		Set<String> result = new HashSet<String>();
127		final QueryLifeCycle pst = qAttribute;
128
129		if (ATTRIBUTES_FROM_BACKPAGE.containsKey(attrname))
130		{
131		String bpInfo = getBpInfo(ref);
132		if (bpInfo != null)
133		{
134		Pattern pat = Pattern.compile(ATTRIBUTES_FROM_BACKPAGE.get (attrname));
135		Matcher matcher = pat.matcher(bpInfo);
136		if (matcher.find())
137		{
138		result.add (matcher.group(1));
139		}
140		}
141		}
142
143		synchronized (pst)
144		{
145		try {
146		pst.init();
147		pst.setString (1, ref.getId());
148		pst.setString (2, ref.getDataSource().getSystemCode());
149		pst.setString (3, attrname);
150		ResultSet r = pst.executeQuery();
151		if (r.next())
152		{
153		result.add (r.getString(1));
154		}
155		return result;
156		} catch (SQLException e) { throw new IDMapperException ("Xref:" + ref + ", Attribute: " + attrname, e); } // Database unavailable
157		finally {pst.cleanup(); }
158		}
159		}
160
161		/** {@inheritDoc} */
162		public Map<String, Set<String>> getAttributes(Xref ref)
163		throws IDMapperException
164		{
165		Map<String, Set<String>> result = new HashMap<String, Set<String>>();
166		final QueryLifeCycle pst = qAllAttributes;
167
168		String bpInfo = getBpInfo(ref);
169		if (bpInfo != null)
170		{
171		for (String attrname : ATTRIBUTES_FROM_BACKPAGE.keySet())
172		{
173		Pattern pat = Pattern.compile(ATTRIBUTES_FROM_BACKPAGE.get (attrname));
174		Matcher matcher = pat.matcher(bpInfo);
175		if (matcher.find())
176		{
177		Set<String> attrSet = new HashSet<String>();
178		attrSet.add (matcher.group(1));
179		result.put (attrname, attrSet);
180		}
181		}
182		}
183
184		synchronized (pst)
185		{
186		try {
187		pst.init();
188		pst.setString (1, ref.getId());
189		pst.setString (2, ref.getDataSource().getSystemCode());
190		ResultSet r = pst.executeQuery();
191		if (r.next())
192		{
193		String key = r.getString(1);
194		String value = r.getString(2);
195		if (result.containsKey (key))
196		{
197		result.get(key).add (value);
198		}
199		else
200		{
201		Set<String> valueSet = new HashSet<String>();
202		valueSet.add (value);
203		result.put (key, valueSet);
204		}
205		}
206		return result;
207		} catch (SQLException e) { throw new IDMapperException ("Xref:" + ref, e); } // Database unavailable
208		finally {pst.cleanup(); }
209		}
210		}
	36	{
	37	private static final int GDB_COMPAT_VERSION = 2; // Preferred schema version
	38
	39	private final String qBackpage = "SELECT backpageText FROM datanode "
	40	+ " WHERE id = ? AND code = ?";
	41
	42	/**
	43	* get Backpage info. In Schema v2, this was not stored in the attribute
	44	* table but as a separate column, so this is treated as a special case.
	45	* This method is called by
	46	*
	47	* <pre>
	48	* getAttribute(ref, "Backpage")
	49	* </pre>
	50	*
	51	* @param ref
	52	* the entity to get backpage info for.
	53	* @return Backpage info as string
	54	* @throws IDMapperException
	55	* when database is unavailable
	56	*/
	57	private String getBpInfo(Xref ref) throws IDMapperException
	58	{
	59	String result = null;
	60	Connection c = null;
	61	PreparedStatement pst = null;
	62	ResultSet r = null;
	63
	64	try
	65	{
	66	c = getConnection();
	67	pst = c.prepareStatement(qBackpage);
	68	pst.setString(1, ref.getId());
	69	pst.setString(2, ref.getDataSource().getSystemCode());
	70	r = pst.executeQuery();
	71
	72	if (r.next())
	73	{
	74	result = r.getString(1);
	75	}
	76
	77	}
	78	catch (SQLException e)
	79	{
	80	// Gene not found
	81	throw new IDMapperException(e);
	82	}
	83	finally
	84	{
	85
	86	closeSilently(r, pst, c);
	87	}
	88
	89	return result;
	90
	91	}
	92
	93	/**
	94	* Opens a connection to the Gene Database located in the given file. A new
	95	* instance of this class is created automatically.
	96	*
	97	* @param dbName
	98	* The file containing the Gene Database.
	99	* @param data_
	100	* .con An existing SQL Connector.
	101	* @param props
	102	* PROP_RECREATE if you want to create a new database (possibly
	103	* overwriting an existing one) or PROP_NONE if you want to
	104	* connect read-only
	105	* @throws IDMapperException
	106	* when the database could not be created or connected to
	107	*/
	108	public SimpleGdbImpl2(String dbName,
	109	String connectionString) throws IDMapperException
	110	{
	111	super(dbName, connectionString);
	112
	113	if (dbName == null)
	114	throw new NullPointerException();
	115	checkSchemaVersion();
	116	}
	117
	118	/**
	119	* look at the info table of the current database to determine the schema
	120	* version.
	121	*
	122	* @throws IDMapperException
	123	* when looking up the schema version failed
	124	*/
	125	private void checkSchemaVersion() throws IDMapperException
	126	{
	127	int version = 0;
	128	Connection c = null;
	129	Statement s = null;
	130	ResultSet r = null;
	131	try
	132	{
	133	c = getConnection();
	134	s = c.createStatement();
	135
	136	r = s.executeQuery("SELECT schemaversion FROM info");
	137	if (r.next())
	138	version = r.getInt(1);
	139	}
	140	catch (SQLException e)
	141	{
	142	// Ignore, older db's don't even have schema version
	143	}
	144	finally
	145	{
	146	closeSilently(r, s, c);
	147	}
	148
	149	if (version != GDB_COMPAT_VERSION)
	150	{
	151	throw new IDMapperException("Implementation and schema version mismatch");
	152	}
	153	}
	154
	155	private static final Map<String, String> ATTRIBUTES_FROM_BACKPAGE;
	156
	157	static
	158	{
	159	ATTRIBUTES_FROM_BACKPAGE = new HashMap<String, String>();
	160	ATTRIBUTES_FROM_BACKPAGE.put("Chromosome", "<TH>Chr:<TH>([^<]*)<");
	161	ATTRIBUTES_FROM_BACKPAGE.put("Description",
	162	"<TH>Description:<TH>([^<]*)<");
	163	ATTRIBUTES_FROM_BACKPAGE.put("Synonyms", "<TH>Synonyms:<TH>([^<]*)<");
	164	ATTRIBUTES_FROM_BACKPAGE.put("Symbol",
	165	"<TH>(?:Gene Symbol\|Metabolite):<TH>([^<]*)<");
	166	ATTRIBUTES_FROM_BACKPAGE.put("BrutoFormula",
	167	"<TH>Bruto Formula:<TH>([^<]*)<");
	168	}
	169
	170	/** {@inheritDoc} */
	171	public Set<String> getAttributes(Xref ref,
	172	String attrname) throws IDMapperException
	173	{
	174	Set<String> result = new HashSet<String>();
	175
	176	if (ATTRIBUTES_FROM_BACKPAGE.containsKey(attrname))
	177	{
	178	String bpInfo = getBpInfo(ref);
	179	if (bpInfo != null)
	180	{
	181	Pattern pat = Pattern.compile(ATTRIBUTES_FROM_BACKPAGE.get(attrname));
	182	Matcher matcher = pat.matcher(bpInfo);
	183	if (matcher.find())
	184	{
	185	result.add(matcher.group(1));
	186	}
	187	}
	188	}
	189
	190	Connection c = null;
	191	PreparedStatement pst = null;
	192	ResultSet r = null;
	193	try
	194	{
	195	c = getConnection();
	196	pst = c.prepareStatement(qAttribute);
	197
	198	pst.setString(1, ref.getId());
	199	pst.setString(2, ref.getDataSource().getSystemCode());
	200	pst.setString(3, attrname);
	201	r = pst.executeQuery();
	202	if (r.next())
	203	{
	204	result.add(r.getString(1));
	205	}
	206	}
	207	catch (SQLException e)
	208	{
	209	throw new IDMapperException("Xref:" + ref + ", Attribute: "
	210	+ attrname, e);
	211	} // Database unavailable
	212	finally
	213	{
	214	closeSilently(r, pst, c);
	215	}
	216	return result;
	217	}
	218
	219	/** {@inheritDoc} */
	220	public Map<String, Set<String>> getAttributes(Xref ref) throws IDMapperException
	221	{
	222	Map<String, Set<String>> result = new HashMap<String, Set<String>>();
	223
	224	String bpInfo = getBpInfo(ref);
	225	if (bpInfo != null)
	226	{
	227	for (String attrname : ATTRIBUTES_FROM_BACKPAGE.keySet())
	228	{
	229	Pattern pat = Pattern.compile(ATTRIBUTES_FROM_BACKPAGE.get(attrname));
	230	Matcher matcher = pat.matcher(bpInfo);
	231	if (matcher.find())
	232	{
	233	Set<String> attrSet = new HashSet<String>();
	234	attrSet.add(matcher.group(1));
	235	result.put(attrname, attrSet);
	236	}
	237	}
	238	}
	239	Connection c = null;
	240	PreparedStatement pst = null;
	241	ResultSet r = null;
	242	try
	243	{
	244	c = getConnection();
	245	pst = c.prepareStatement(qAllAttributes);
	246
	247	pst.setString(1, ref.getId());
	248	pst.setString(2, ref.getDataSource().getSystemCode());
	249	r = pst.executeQuery();
	250	if (r.next())
	251	{
	252	String key = r.getString(1);
	253	String value = r.getString(2);
	254	if (result.containsKey(key))
	255	{
	256	result.get(key).add(value);
	257	}
	258	else
	259	{
	260	Set<String> valueSet = new HashSet<String>();
	261	valueSet.add(value);
	262	result.put(key, valueSet);
	263	}
	264	}
	265
	266	}
	267	catch (SQLException e)
	268	{
	269	throw new IDMapperException("Xref:" + ref, e);
	270	} // Database unavailable
	271	finally
	272	{
	273	closeSilently(r, pst, c);
	274	}
	275	return result;
	276	}
211	277	}

branches/ds_pool/src/org/bridgedb/rdb/SimpleGdbImpl3.java

r405	r408
18	18
19	19	import java.sql.Connection;
	20	import java.sql.PreparedStatement;
20	21	import java.sql.ResultSet;
21	22	import java.sql.SQLException;
	23	import java.sql.Statement;
22	24	import java.util.HashMap;
23	25	import java.util.HashSet;
…	…
30	32	/** {@inheritDoc} */
31	33	class SimpleGdbImpl3 extends SimpleGdbImplCommon
32		{
33		private static final int GDB_COMPAT_VERSION = 3; //Preferred schema version
34
35		/**
36		* Opens a connection to the Gene Database located in the given file.
37		* A new instance of this class is created automatically.
38		* @param dbName The file containing the Gene Database.
39		* @param data_.con An existing java SQL connection
40		* @param props PROP_RECREATE if you want to create a new database (possibly overwriting an existing one)
41		* or PROP_NONE if you want to connect read-only
42		* @throws IDMapperException when the database could not be created or connected to
43		*/
44		public SimpleGdbImpl3(String dbName, String connectionString) throws IDMapperException
45		{
46		super(dbName, connectionString);
47		checkSchemaVersion();
48		}
49
50		/**
51		* look at the info table of the current database to determine the schema version.
52		* @throws IDMapperException when looking up the schema version failed
53		*/
54		private void checkSchemaVersion() throws IDMapperException
55		{
56		int version = 0;
57		try
58		{
59		ResultSet r = getConnection().createStatement().executeQuery("SELECT schemaversion FROM info");
60		if(r.next()) version = r.getInt(1);
61		}
62		catch (SQLException e)
63		{
64		//Ignore, older db's don't even have schema version
65		}
66		if(version != GDB_COMPAT_VERSION)
67		{
68		throw new IDMapperException ("Implementation and schema version mismatch");
69		}
70		}
71
72		/** {@inheritDoc} */
73		public Set<String> getAttributes(Xref ref, String attrname)
74		throws IDMapperException
75		{
76		Set<String> result = new HashSet<String>();
77		final QueryLifeCycle pst = qAttribute;
78		synchronized (pst)
79		{
80		try {
81		pst.init();
82		pst.setString (1, ref.getId());
83		pst.setString (2, ref.getDataSource().getSystemCode());
84		pst.setString (3, attrname);
85		ResultSet r = pst.executeQuery();
86		if (r.next())
87		{
88		result.add (r.getString(1));
89		}
90		return result;
91		} catch (SQLException e) { throw new IDMapperException (e); } // Database unavailable
92		finally {pst.cleanup(); }
93		}
94		}
95
96		/** {@inheritDoc} */
97		public Map<String, Set<String>> getAttributes(Xref ref)
98		throws IDMapperException
99		{
100		Map<String, Set<String>> result = new HashMap<String, Set<String>>();
101		final QueryLifeCycle pst = qAllAttributes;
102		synchronized (pst)
103		{
104		try {
105		pst.init();
106		pst.setString (1, ref.getId());
107		pst.setString (2, ref.getDataSource().getSystemCode());
108		ResultSet r = pst.executeQuery();
109		while (r.next())
110		{
111		String key = r.getString(1);
112		String value = r.getString(2);
113		if (result.containsKey (key))
114		{
115		result.get(key).add (value);
116		}
117		else
118		{
119		Set<String> valueSet = new HashSet<String>();
120		valueSet.add (value);
121		result.put (key, valueSet);
122		}
123		}
124		return result;
125		} catch (SQLException e) { throw new IDMapperException ("Xref:" + ref, e); } // Database unavailable
126		finally {pst.cleanup(); }
127		}
128		}
	34	{
	35	private static final int GDB_COMPAT_VERSION = 3; // Preferred schema version
	36
	37	/**
	38	* Opens a connection to the Gene Database located in the given file. A new
	39	* instance of this class is created automatically.
	40	*
	41	* @param dbName
	42	* The file containing the Gene Database.
	43	* @param data_
	44	* .con An existing java SQL connection
	45	* @param props
	46	* PROP_RECREATE if you want to create a new database (possibly
	47	* overwriting an existing one) or PROP_NONE if you want to
	48	* connect read-only
	49	* @throws IDMapperException
	50	* when the database could not be created or connected to
	51	*/
	52	public SimpleGdbImpl3(String dbName,
	53	String connectionString) throws IDMapperException
	54	{
	55	super(dbName, connectionString);
	56	checkSchemaVersion();
	57	}
	58
	59	/**
	60	* look at the info table of the current database to determine the schema
	61	* version.
	62	*
	63	* @throws IDMapperException
	64	* when looking up the schema version failed
	65	*/
	66	private void checkSchemaVersion() throws IDMapperException
	67	{
	68	int version = 0;
	69	Connection c = null;
	70	Statement s = null;
	71	ResultSet r = null;
	72	try
	73	{
	74	c = getConnection();
	75	s = c.createStatement();
	76
	77	r = s.executeQuery("SELECT schemaversion FROM info");
	78	if (r.next())
	79	version = r.getInt(1);
	80	}
	81	catch (SQLException e)
	82	{
	83	// Ignore, older db's don't even have schema version
	84	}
	85	finally
	86	{
	87	closeSilently(r, s, c);
	88	}
	89	if (version != GDB_COMPAT_VERSION)
	90	{
	91	throw new IDMapperException("Implementation and schema version mismatch");
	92	}
	93	}
	94
	95	/** {@inheritDoc} */
	96	public Set<String> getAttributes(Xref ref,
	97	String attrname) throws IDMapperException
	98	{
	99	Set<String> result = new HashSet<String>();
	100
	101	Connection c = null;
	102	PreparedStatement pst = null;
	103	ResultSet r = null;
	104	try
	105	{
	106	c = getConnection();
	107	pst = c.prepareStatement(qAttribute);
	108	pst.setString(1, ref.getId());
	109	pst.setString(2, ref.getDataSource().getSystemCode());
	110	pst.setString(3, attrname);
	111	r = pst.executeQuery();
	112	if (r.next())
	113	{
	114	result.add(r.getString(1));
	115	}
	116
	117	}
	118	catch (SQLException e)
	119	{
	120	throw new IDMapperException(e);
	121	} // Database unavailable
	122	finally
	123	{
	124	closeSilently(r, pst, c);
	125	}
	126	return result;
	127	}
	128
	129	/** {@inheritDoc} */
	130	public Map<String, Set<String>> getAttributes(Xref ref) throws IDMapperException
	131	{
	132	Map<String, Set<String>> result = new HashMap<String, Set<String>>();
	133
	134	Connection c = null;
	135	PreparedStatement pst = null;
	136	ResultSet r = null;
	137	try
	138	{
	139	c = getConnection();
	140	pst = c.prepareStatement(qAllAttributes);
	141	pst.setString(1, ref.getId());
	142	pst.setString(2, ref.getDataSource().getSystemCode());
	143	r = pst.executeQuery();
	144	while (r.next())
	145	{
	146	String key = r.getString(1);
	147	String value = r.getString(2);
	148	if (result.containsKey(key))
	149	{
	150	result.get(key).add(value);
	151	}
	152	else
	153	{
	154	Set<String> valueSet = new HashSet<String>();
	155	valueSet.add(value);
	156	result.put(key, valueSet);
	157	}
	158	}
	159
	160	}
	161	catch (SQLException e)
	162	{
	163	throw new IDMapperException("Xref:" + ref, e);
	164	} // Database unavailable
	165	finally
	166	{
	167	closeSilently(r, pst, c);
	168	}
	169	return result;
	170	}
129	171	}

branches/ds_pool/src/org/bridgedb/rdb/SimpleGdbImplCommon.java

r318	r408
1	1	package org.bridgedb.rdb;
2	2
	3	import java.sql.Connection;
	4	import java.sql.PreparedStatement;
3	5	import java.sql.ResultSet;
4	6	import java.sql.ResultSetMetaData;
…	…
19	21
20	22	/**
21		* Some methods and constants that are shared between SimpleGdbImpl2 and SimpleGdbImpl3
	23	* Some methods and constants that are shared between SimpleGdbImpl2 and
	24	* SimpleGdbImpl3
22	25	*/
23	26	public abstract class SimpleGdbImplCommon extends SimpleGdb
24	27	{
25		SimpleGdbImplCommon(String dbName, String connectionString) throws IDMapperException
26		{
27		super(dbName, connectionString);
28		caps = new SimpleGdbCapabilities();
29		}
30
31		final SimpleGdb.QueryLifeCycle qDatasources = new SimpleGdb.QueryLifeCycle(
32		"SELECT codeRight FROM link GROUP BY codeRight"
33		);
34		final SimpleGdb.QueryLifeCycle qInfo = new SimpleGdb.QueryLifeCycle(
35		"SELECT * FROM info"
36		);
37		final SimpleGdb.QueryLifeCycle qXrefExists = new SimpleGdb.QueryLifeCycle(
38		"SELECT id FROM " + "datanode" + " WHERE " +
39		"id = ? AND code = ?"
40		);
41		final SimpleGdb.QueryLifeCycle qAttribute = new SimpleGdb.QueryLifeCycle(
42		"SELECT attrvalue FROM attribute " +
43		" WHERE id = ? AND code = ? AND attrname = ?"
44		);
45		final SimpleGdb.QueryLifeCycle qAllAttributes = new SimpleGdb.QueryLifeCycle(
46		"SELECT attrname, attrvalue FROM attribute " +
47		" WHERE id = ? AND code = ?"
48		);
49		final SimpleGdb.QueryLifeCycle qAttributesSet = new SimpleGdb.QueryLifeCycle(
50		"SELECT attrname FROM attribute GROUP BY attrname"
51		);
52		final SimpleGdb.QueryLifeCycle qCrossRefs = new SimpleGdb.QueryLifeCycle (
53		"SELECT dest.idRight, dest.codeRight FROM link AS src JOIN link AS dest " +
54		"ON src.idLeft = dest.idLeft and src.codeLeft = dest.codeLeft " +
55		"WHERE src.idRight = ? AND src.codeRight = ?"
56		);
57		final SimpleGdb.QueryLifeCycle qCrossRefsWithCode = new SimpleGdb.QueryLifeCycle (
58		"SELECT dest.idRight, dest.codeRight FROM link AS src JOIN link AS dest " +
59		"ON src.idLeft = dest.idLeft and src.codeLeft = dest.codeLeft " +
60		"WHERE src.idRight = ? AND src.codeRight = ? AND dest.codeRight = ?"
61		);
62		final SimpleGdb.QueryLifeCycle qRefsByAttribute = new SimpleGdb.QueryLifeCycle (
63		"SELECT datanode.id, datanode.code FROM datanode " +
64		" LEFT JOIN attribute ON attribute.code = datanode.code AND attribute.id = datanode.id " +
65		"WHERE attrName = ? AND attrValue = ?"
66		);
67		final SimpleGdb.QueryLifeCycle qFreeSearch = new SimpleGdb.QueryLifeCycle (
68		"SELECT id, code FROM datanode WHERE " +
69		"LOWER(ID) LIKE ?"
70		);
71		final SimpleGdb.QueryLifeCycle qAttributeSearch = new SimpleGdb.QueryLifeCycle (
72		"SELECT id, code, attrvalue FROM attribute WHERE " +
73		"attrname = 'Symbol' AND LOWER(attrvalue) LIKE ?"
74		);
75		final SimpleGdb.QueryLifeCycle qIdSearchWithAttributes = new SimpleGdb.QueryLifeCycle (
76		"SELECT id, code, attrvalue FROM attribute WHERE " +
77		"attrname = 'Symbol' AND LOWER(ID) LIKE ?"
78		);
79
80		/** {@inheritDoc} */
81		public boolean xrefExists(Xref xref) throws IDMapperException
82		{
83		final QueryLifeCycle pst = qXrefExists;
84		synchronized (pst) {
85		try
86		{
87		pst.init();
88		pst.setString(1, xref.getId());
89		pst.setString(2, xref.getDataSource().getSystemCode());
90		ResultSet r = pst.executeQuery();
91
92		while(r.next())
93		{
94		return true;
95		}
96		}
97		catch (SQLException e)
98		{
99		throw new IDMapperException (e);
100		}
101		finally {pst.cleanup(); }
102		return false;
103		}
104		}
105
106		/**
107		* Read the info table and return as properties.
108		* @return a map where keys are column names and values are the fields in the first row.
109		* @throws IDMapperException when the database became unavailable
110		*/
111		Map<String, String> getInfo() throws IDMapperException
112		{
113		Map<String, String> result = new HashMap<String, String>();
114		final QueryLifeCycle pst = qInfo;
115		synchronized (pst) {
116		try
117		{
118		pst.init();
119		ResultSet rs = pst.executeQuery();
120
121		if (rs.next())
122		{
123		ResultSetMetaData rsmd = rs.getMetaData();
124		for (int i = 1; i <= rsmd.getColumnCount(); ++i)
125		{
126		String key = rsmd.getColumnName(i);
127		String val = rs.getString(i);
128		result.put (key, val);
129		}
130		}
131		}
132		catch (SQLException ex)
133		{
134		throw new IDMapperException (ex);
135		}
136
137		return result;
138		}
139		}
140
141
142		/** {@inheritDoc} */
143		public Set<Xref> mapID (Xref idc, DataSource... resultDs) throws IDMapperException
144		{
145		final QueryLifeCycle pst = resultDs.length != 1 ? qCrossRefs : qCrossRefsWithCode;
146		Set<Xref> refs = new HashSet<Xref>();
147
148		if (idc.getDataSource() == null) return refs;
149		synchronized (pst) {
150		try
151		{
152		pst.init();
153		pst.setString(1, idc.getId());
154		pst.setString(2, idc.getDataSource().getSystemCode());
155		if (resultDs.length == 1) pst.setString(3, resultDs[0].getSystemCode());
156
157		Set<DataSource> dsFilter = new HashSet<DataSource>(Arrays.asList(resultDs));
158
159		ResultSet rs = pst.executeQuery();
160		while (rs.next())
161		{
162		DataSource ds = DataSource.getBySystemCode(rs.getString(2));
163		if (resultDs.length == 0 \|\| dsFilter.contains(ds))
164		{
165		refs.add (new Xref (rs.getString(1), ds));
166		}
167		}
168		}
169		catch (SQLException e)
170		{
171		throw new IDMapperException (e);
172		}
173		finally {pst.cleanup(); }
174
175		return refs;
176		}
177		}
178
179		/** {@inheritDoc} */
180		public List<Xref> getCrossRefsByAttribute(String attrName, String attrValue) throws IDMapperException {
181		// Logger.log.trace("Fetching cross references by attribute: " + attrName + " = " + attrValue);
182		List<Xref> refs = new ArrayList<Xref>();
183
184		final QueryLifeCycle pst = qRefsByAttribute;
185		synchronized (pst) {
186		try {
187		pst.init();
188		pst.setString(1, attrName);
189		pst.setString(2, attrValue);
190		ResultSet r = pst.executeQuery();
191		while(r.next()) {
192		Xref ref = new Xref(r.getString(1), DataSource.getBySystemCode(r.getString(2)));
193		refs.add(ref);
194		}
195		} catch(SQLException e) {
196		throw new IDMapperException (e);
197		}
198		finally {pst.cleanup(); }
199		// Logger.log.trace("End fetching cross references by attribute");
200		return refs;
201		}
202		}
203
204		/** {@inheritDoc} */
205		public Set<Xref> freeSearch (String text, int limit) throws IDMapperException
206		{
207		Set<Xref> result = new HashSet<Xref>();
208		final QueryLifeCycle pst = qFreeSearch;
209		synchronized (pst) {
210		try {
211		pst.init(limit);
212		pst.setString(1, "%" + text.toLowerCase() + "%");
213		ResultSet r = pst.executeQuery();
214		while(r.next()) {
215		String id = r.getString(1);
216		DataSource ds = DataSource.getBySystemCode(r.getString(2));
217		Xref ref = new Xref (id, ds);
218		result.add (ref);
219		}
220		}
221		catch (SQLException e)
222		{
223		throw new IDMapperException(e);
224		}
225		finally {pst.cleanup(); }
226		return result;
227		}
228		}
229
230		/**
231		* @return a list of data sources present in this database.
232		@throws IDMapperException when the database is unavailable
233		*/
234		private Set<DataSource> getDataSources() throws IDMapperException
235		{
236		Set<DataSource> result = new HashSet<DataSource>();
237		final QueryLifeCycle pst = qDatasources;
238		synchronized (pst) {
239		try
240		{
241		pst.init();
242		ResultSet rs = pst.executeQuery();
243		while (rs.next())
244		{
245		DataSource ds = DataSource.getBySystemCode(rs.getString(1));
246		result.add (ds);
247		}
248		}
249		catch (SQLException ignore)
250		{
251		throw new IDMapperException(ignore);
252		}
253		finally {pst.cleanup(); }
254		return result;
255		}
256		}
257
258		private final IDMapperCapabilities caps;
259
260		class SimpleGdbCapabilities extends AbstractIDMapperCapabilities
261		{
262		/** default constructor.
263		* @throws IDMapperException when database is not available */
264		public SimpleGdbCapabilities() throws IDMapperException
265		{
266		super (SimpleGdbImplCommon.this.getDataSources(), true,
267		SimpleGdbImplCommon.this.getInfo());
268		}
269		}
270
271		/**
272		* @return the capabilities of this gene database
273		*/
274		public IDMapperCapabilities getCapabilities()
275		{
276		return caps;
277		}
278
279		/**
280		*
281		* @return true
282		*/
283		public boolean isFreeAttributeSearchSupported()
284		{
285		return true;
286		}
287
288		/**
289		* free text search for matching symbols.
290		* @return references that match the query
291		* @param query The text to search for
292		* @param attrType the attribute to look for, e.g. 'Symbol' or 'Description'.
293		* @param limit The number of results to limit the search to
294		* @throws IDMapperException if the mapping service is (temporarily) unavailable
295		*/
296		public Map<Xref, String> freeAttributeSearch (String query, String attrType, int limit) throws IDMapperException
297		{
298		Map<Xref, String> result = new HashMap<Xref, String>();
299		final QueryLifeCycle pst = (MATCH_ID.equals (attrType)) ?
300		qIdSearchWithAttributes : qAttributeSearch;
301		synchronized (pst) {
302		try {
303		pst.init(limit);
304		pst.setString(1, "%" + query.toLowerCase() + "%");
305		ResultSet r = pst.executeQuery();
306
307		while(r.next())
308		{
309		String id = r.getString("id");
310		String code = r.getString("code");
311		String symbol = r.getString("attrValue");
312		result.put(new Xref (id, DataSource.getBySystemCode(code)), symbol);
313		}
314		} catch (SQLException e) {
315		throw new IDMapperException (e);
316		}
317		finally {pst.cleanup(); }
318		return result;
319		}
320		}
321
322		/** {@inheritDoc} */
323		public Set<String> getAttributeSet() throws IDMapperException
324		{
325		Set<String> result = new HashSet<String>();
326		final QueryLifeCycle pst = qAttributesSet;
327		synchronized (pst) {
328		try
329		{
330		pst.init();
331		ResultSet rs = pst.executeQuery();
332		while (rs.next())
333		{
334		result.add (rs.getString(1));
335		}
336		}
337		catch (SQLException ignore)
338		{
339		throw new IDMapperException(ignore);
340		}
341		finally {pst.cleanup(); }
342		return result;
343		}
344		}
345
	28	SimpleGdbImplCommon(String dbName,
	29	String connectionString) throws IDMapperException
	30	{
	31	super(dbName, connectionString);
	32	caps = new SimpleGdbCapabilities();
	33	}
	34
	35	private final static String qDatasources = "SELECT codeRight FROM link GROUP BY codeRight";
	36
	37	private final static String qInfo = "SELECT * FROM info";
	38
	39	private final static String qXrefExists = "SELECT id FROM "
	40	+ "datanode" + " WHERE "
	41	+ "id = ? AND code = ?";
	42
	43	protected final static String qAttribute = "SELECT attrvalue FROM attribute "
	44	+ " WHERE id = ? AND code = ? AND attrname = ?";
	45
	46	protected final static String qAllAttributes = "SELECT attrname, attrvalue FROM attribute "
	47	+ " WHERE id = ? AND code = ?";
	48
	49	private final static String qAttributesSet = "SELECT attrname FROM attribute GROUP BY attrname";
	50
	51	private final static String qCrossRefs = "SELECT dest.idRight, dest.codeRight FROM link AS src JOIN link AS dest "
	52	+ "ON src.idLeft = dest.idLeft and src.codeLeft = dest.codeLeft "
	53	+ "WHERE src.idRight = ? AND src.codeRight = ?";
	54
	55	private final static String qCrossRefsWithCode = "SELECT dest.idRight, dest.codeRight FROM link AS src JOIN link AS dest "
	56	+ "ON src.idLeft = dest.idLeft and src.codeLeft = dest.codeLeft "
	57	+ "WHERE src.idRight = ? AND src.codeRight = ? AND dest.codeRight = ?";
	58
	59	private final static String qRefsByAttribute = "SELECT datanode.id, datanode.code FROM datanode "
	60	+ " LEFT JOIN attribute ON attribute.code = datanode.code AND attribute.id = datanode.id "
	61	+ "WHERE attrName = ? AND attrValue = ?";
	62
	63	private final static String qFreeSearch = "SELECT id, code FROM datanode WHERE "
	64	+ "LOWER(ID) LIKE ?";
	65
	66	private final static String qAttributeSearch = "SELECT id, code, attrvalue FROM attribute WHERE "
	67	+ "attrname = 'Symbol' AND LOWER(attrvalue) LIKE ?";
	68
	69	private final static String qIdSearchWithAttributes = "SELECT id, code, attrvalue FROM attribute WHERE "
	70	+ "attrname = 'Symbol' AND LOWER(ID) LIKE ?";
	71
	72	/** {@inheritDoc} */
	73	public boolean xrefExists(Xref xref) throws IDMapperException
	74	{
	75	Connection c = null;
	76	PreparedStatement pst = null;
	77	ResultSet r = null;
	78	boolean exists = false;
	79	try
	80	{
	81	c = getConnection();
	82	pst = c.prepareStatement(qXrefExists);
	83
	84	pst.setString(1, xref.getId());
	85	pst.setString(2, xref.getDataSource().getSystemCode());
	86	r = pst.executeQuery();
	87
	88	exists = r.next();
	89
	90	}
	91	catch (SQLException e)
	92	{
	93	throw new IDMapperException(e);
	94	}
	95	finally
	96	{
	97	closeSilently(r, pst, c);
	98	}
	99	return exists;
	100	}
	101
	102	/**
	103	* Read the info table and return as properties.
	104	*
	105	* @return a map where keys are column names and values are the fields in
	106	* the first row.
	107	* @throws IDMapperException
	108	* when the database became unavailable
	109	*/
	110	Map<String, String> getInfo() throws IDMapperException
	111	{
	112	Map<String, String> result = new HashMap<String, String>();
	113
	114	Connection c = null;
	115	PreparedStatement pst = null;
	116	ResultSet r = null;
	117	try
	118	{
	119	c = getConnection();
	120	pst = c.prepareStatement(qInfo);
	121
	122	r = pst.executeQuery();
	123
	124	if (r.next())
	125	{
	126	ResultSetMetaData rsmd = r.getMetaData();
	127	for (int i = 1; i <= rsmd.getColumnCount(); ++i)
	128	{
	129	String key = rsmd.getColumnName(i);
	130	String val = r.getString(i);
	131	result.put(key, val);
	132	}
	133	}
	134	}
	135	catch (SQLException ex)
	136	{
	137	throw new IDMapperException(ex);
	138	}
	139	finally
	140	{
	141	closeSilently(r, pst, c);
	142	}
	143
	144	return result;
	145
	146	}
	147
	148	/** {@inheritDoc} */
	149	public Set<Xref> mapID(Xref idc,
	150	DataSource... resultDs) throws IDMapperException
	151	{
	152	String query = resultDs.length != 1 ? qCrossRefs : qCrossRefsWithCode;
	153	Set<Xref> refs = new HashSet<Xref>();
	154
	155	if (idc.getDataSource() != null)
	156	{
	157
	158	Connection c = null;
	159	PreparedStatement pst = null;
	160	ResultSet rs = null;
	161	try
	162	{
	163	c = getConnection();
	164	pst =c.prepareStatement(query);
	165
	166	pst.setString(1, idc.getId());
	167	pst.setString(2, idc.getDataSource().getSystemCode());
	168	if (resultDs.length == 1)
	169	pst.setString(3, resultDs[0].getSystemCode());
	170
	171	Set<DataSource> dsFilter = new HashSet<DataSource>(Arrays.asList(resultDs));
	172
	173	rs = pst.executeQuery();
	174	while (rs.next())
	175	{
	176	DataSource ds = DataSource.getBySystemCode(rs.getString(2));
	177	if (resultDs.length == 0 \|\| dsFilter.contains(ds))
	178	{
	179	refs.add(new Xref(rs.getString(1), ds));
	180	}
	181	}
	182	}
	183	catch (SQLException e)
	184	{
	185	throw new IDMapperException(e);
	186	}
	187	finally
	188	{
	189	closeSilently(rs, pst, c);
	190	}
	191	}
	192	return refs;
	193
	194	}
	195
	196	/** {@inheritDoc} */
	197	public List<Xref> getCrossRefsByAttribute(String attrName,
	198	String attrValue) throws IDMapperException
	199	{
	200	// Logger.log.trace("Fetching cross references by attribute: " +
	201	// attrName + " = " + attrValue);
	202	List<Xref> refs = new ArrayList<Xref>();
	203
	204	Connection c = null;
	205	PreparedStatement pst = null;
	206	ResultSet r = null;
	207
	208	try
	209	{
	210	c = getConnection();
	211	pst = c.prepareStatement(qRefsByAttribute);
	212
	213	pst.setString(1, attrName);
	214	pst.setString(2, attrValue);
	215	r = pst.executeQuery();
	216	while (r.next())
	217	{
	218	Xref ref = new Xref(r.getString(1),
	219	DataSource.getBySystemCode(r.getString(2)));
	220	refs.add(ref);
	221	}
	222	}
	223	catch (SQLException e)
	224	{
	225	throw new IDMapperException(e);
	226	}
	227	finally
	228	{
	229	closeSilently(r, pst, c);
	230	}
	231	// Logger.log.trace("End fetching cross references by attribute");
	232	return refs;
	233
	234	}
	235
	236	/** {@inheritDoc} */
	237	public Set<Xref> freeSearch(String text,
	238	int limit) throws IDMapperException
	239	{
	240	Set<Xref> result = new HashSet<Xref>();
	241
	242	Connection c = null;
	243	PreparedStatement pst = null;
	244	ResultSet r = null;
	245
	246	try
	247	{
	248	c = getConnection();
	249	pst = c.prepareStatement(qFreeSearch);
	250	pst.setString(1, "%" + text.toLowerCase() + "%");
	251	r = pst.executeQuery();
	252	while (r.next())
	253	{
	254	String id = r.getString(1);
	255	DataSource ds = DataSource.getBySystemCode(r.getString(2));
	256	Xref ref = new Xref(id, ds);
	257	result.add(ref);
	258	}
	259	}
	260	catch (SQLException e)
	261	{
	262	throw new IDMapperException(e);
	263	}
	264	finally
	265	{
	266	closeSilently(r, pst, c);
	267	}
	268	return result;
	269
	270	}
	271
	272	/**
	273	* @return a list of data sources present in this database.
	274	* @throws IDMapperException
	275	* when the database is unavailable
	276	*/
	277	private Set<DataSource> getDataSources() throws IDMapperException
	278	{
	279	Set<DataSource> result = new HashSet<DataSource>();
	280
	281	Connection c = null;
	282	PreparedStatement pst = null;
	283	ResultSet rs = null;
	284
	285	try
	286	{
	287	c = getConnection();
	288	pst = c.prepareStatement(qDatasources);
	289	rs = pst.executeQuery();
	290	while (rs.next())
	291	{
	292	DataSource ds = DataSource.getBySystemCode(rs.getString(1));
	293	result.add(ds);
	294	}
	295	}
	296	catch (SQLException e)
	297	{
	298	throw new IDMapperException(e);
	299	}
	300	finally
	301	{
	302	closeSilently(rs, pst, c);
	303	}
	304	return result;
	305
	306	}
	307
	308	private final IDMapperCapabilities caps;
	309
	310	class SimpleGdbCapabilities extends AbstractIDMapperCapabilities
	311	{
	312	/**
	313	* default constructor.
	314	*
	315	* @throws IDMapperException
	316	* when database is not available
	317	*/
	318	public SimpleGdbCapabilities() throws IDMapperException
	319	{
	320	super(SimpleGdbImplCommon.this.getDataSources(),
	321	true,
	322	SimpleGdbImplCommon.this.getInfo());
	323	}
	324	}
	325
	326	/**
	327	* @return the capabilities of this gene database
	328	*/
	329	public IDMapperCapabilities getCapabilities()
	330	{
	331	return caps;
	332	}
	333
	334	/**
	335	*
	336	* @return true
	337	*/
	338	public boolean isFreeAttributeSearchSupported()
	339	{
	340	return true;
	341	}
	342
	343	/**
	344	* free text search for matching symbols.
	345	*
	346	* @return references that match the query
	347	* @param query
	348	* The text to search for
	349	* @param attrType
	350	* the attribute to look for, e.g. 'Symbol' or 'Description'.
	351	* @param limit
	352	* The number of results to limit the search to
	353	* @throws IDMapperException
	354	* if the mapping service is (temporarily) unavailable
	355	*/
	356	public Map<Xref, String> freeAttributeSearch(String query,
	357	String attrType,
	358	int limit) throws IDMapperException
	359	{
	360	Map<Xref, String> result = new HashMap<Xref, String>();
	361	String querySql = (MATCH_ID.equals(attrType)) ? qIdSearchWithAttributes
	362	: qAttributeSearch;
	363
	364	Connection c = null;
	365	PreparedStatement pst = null;
	366	ResultSet r = null;
	367
	368	try
	369	{
	370	c = getConnection();
	371	pst = c.prepareStatement(querySql);
	372	pst.setString(1, "%" + query.toLowerCase() + "%");
	373	r = pst.executeQuery();
	374
	375	while (r.next())
	376	{
	377	String id = r.getString("id");
	378	String code = r.getString("code");
	379	String symbol = r.getString("attrValue");
	380	result.put(new Xref(id, DataSource.getBySystemCode(code)),
	381	symbol);
	382	}
	383	}
	384	catch (SQLException e)
	385	{
	386	throw new IDMapperException(e);
	387	}
	388	finally
	389	{
	390	closeSilently(r, pst, c);
	391	}
	392	return result;
	393
	394	}
	395
	396	/** {@inheritDoc} */
	397	public Set<String> getAttributeSet() throws IDMapperException
	398	{
	399	Set<String> result = new HashSet<String>();
	400
	401	Connection c = null;
	402	PreparedStatement pst = null;
	403	ResultSet rs = null;
	404
	405	try
	406	{
	407	c = getConnection();
	408	pst = c.prepareStatement(qAttributesSet);
	409	rs = pst.executeQuery();
	410	while (rs.next())
	411	{
	412	result.add(rs.getString(1));
	413	}
	414	}
	415	catch (SQLException ignore)
	416	{
	417	throw new IDMapperException(ignore);
	418	}
	419	finally
	420	{
	421	closeSilently(rs, pst, c);
	422	}
	423	return result;
	424	}
	425
346	426	}

Context Navigation

Changeset 408 for branches

Legend:

branches/ds_pool/src/org/bridgedb/rdb/SimpleGdb.java

branches/ds_pool/src/org/bridgedb/rdb/SimpleGdbFactory.java

branches/ds_pool/src/org/bridgedb/rdb/SimpleGdbImpl2.java

branches/ds_pool/src/org/bridgedb/rdb/SimpleGdbImpl3.java

branches/ds_pool/src/org/bridgedb/rdb/SimpleGdbImplCommon.java

Download in other formats: