1   /*
2    *  DBHelper.java
3    *
4    *  Copyright (c) 1998-2005, The University of Sheffield.
5    *
6    *  This file is part of GATE (see http://gate.ac.uk/), and is free
7    *  software, licenced under the GNU Library General Public License,
8    *  Version 2, June 1991 (in the distribution as file licence.html,
9    *  and also available at http://gate.ac.uk/gate/licence.html).
10   *
11   *  Marin Dimitrov, 18/Sep/2001
12   *
13   *  $Id: DBHelper.java,v 1.37 2005/01/11 13:51:36 ian Exp $
14   */
15  
16  package gate.persist;
17  
18  import java.sql.*;
19  import java.util.HashMap;
20  
21  import gate.Gate;
22  
23  public class DBHelper {
24  
25    /** class name of the Oracle jdbc driver */
26    private static final String jdbcOracleDriverName = "oracle.jdbc.driver.OracleDriver";
27    private static final String jdbcPostgresDriverName = "org.postgresql.Driver";
28  //  private static final String jdbcSapDBDriverName = "com.sap.dbtech.jdbc.DriverSapDB";
29  
30    public static final int CHINK_SIZE_SMALL = 30;
31    public static final int CHINK_SIZE_MEDIUM = 60;
32    public static final int CHINK_SIZE_LARGE = 100;
33    //WARNING!
34    //DO NOT EDIT THESE CONSTANTS WITHOUT
35    //SYNCHRONIZING WITH ERROR.SPC PL/SQL PACKAGE
36    //note that while Oracle returns negative error numbers
37    //the SQLException::getErrorCode() returns positive ones
38    //
39  
40    /** user defined error codes in Oracle start with -21000 */
41    public static final int X_ORACLE_START = 20100;
42  
43    /**  this should be thrown if an attempt to create a group with duplicated name is made */
44    public static final int X_ORACLE_DUPLICATE_GROUP_NAME =      X_ORACLE_START + 1 ;
45  
46    /** see above */
47    public static final int X_ORACLE_DUPLICATE_USER_NAME =       X_ORACLE_START + 2 ;
48  
49    /** no such user failure upon login */
50    public static final int X_ORACLE_INVALID_USER_NAME =         X_ORACLE_START + 3 ;
51  
52    /** - */
53    public static final int X_ORACLE_INVALID_USER_PASS =         X_ORACLE_START + 4 ;
54  
55    /** invalid group id supplied for operation requiring such specifier */
56    public static final int X_ORACLE_INVALID_USER_GROUP =        X_ORACLE_START + 5 ;
57  
58    /** access to LR by id fails - no such resource */
59    public static final int X_ORACLE_INVALID_LR =                X_ORACLE_START + 6 ;
60  
61    /** attempt to access resource in mode that does not exist */
62    public static final int X_ORACLE_INVALID_ACCESS_MODE =       X_ORACLE_START + 7 ;
63  
64    /** huh? */
65    public static final int X_ORACLE_INVALID_ARGUMENT =          X_ORACLE_START + 8 ;
66  
67    /** this should not be in use anymore */
68    public static final int X_ORACLE_NOT_IMPLEMENTED =           X_ORACLE_START + 9 ;
69  
70    /** attempt to delete a group that owns resources is made */
71    public static final int X_ORACLE_GROUP_OWNS_RESOURCES =      X_ORACLE_START + 10 ;
72  
73    /** attempt to delete a user that owns resources is made */
74    public static final int X_ORACLE_USER_OWNS_RESOURCES =       X_ORACLE_START + 11 ;
75  
76    /** huh? */
77    public static final int X_ORACLE_INCOMPLETE_DATA  =          X_ORACLE_START + 12 ;
78  
79    /** attempt to access resources by type is made, but no such type exists */
80    public static final int X_ORACLE_INVALID_LR_TYPE  =          X_ORACLE_START + 13 ;
81  
82    /** this is obsolete now? */
83    public static final int X_ORACLE_INVALID_ANNOTATION_TYPE =   X_ORACLE_START + 14 ;
84  
85    /** attempt to create a feature with invalid value type is made
86     *  since value types are automatically assigned in the java code, this errror
87     *  should indicate that the java code was changed but no changes were made to the
88     *  relevant pl/sql code
89     *  */
90    public static final int X_ORACLE_INVALID_FEATURE_TYPE =      X_ORACLE_START + 15 ;
91  
92    /**
93     * not supported content type - we support only character/binary/empty content
94     * since there are no many other options this error shoudkl indicate that the
95     * java code was not synced with the pl/sql one
96     *
97     *  */
98    public static final int X_ORACLE_INVALID_CONTENT_TYPE =      X_ORACLE_START + 16 ;
99  
100   /** attempt to remove annotation that does not exist is made */
101   public static final int X_ORACLE_INVALID_ANNOTATION =        X_ORACLE_START + 17 ;
102 
103   /** attempt to perform an operation that requres more privileged is made */
104   public static final int X_ORACLE_INSUFFICIENT_PRIVILEGES =   X_ORACLE_START + 18 ;
105 
106   /** attempt to remove annotation set that does not exist is made */
107   public static final int X_ORACLE_INVALID_ANNOTATION_SET  =   X_ORACLE_START + 19 ;
108 
109   public static final int TRUE = 1;
110   public static final int FALSE = 0;
111 
112   /** character content (may make difference for the database) */
113   public static final int CHARACTER_CONTENT = 1;
114 
115   /** binary content (may make difference for the database) */
116   public static final int BINARY_CONTENT = 2;
117 
118   /** document has no content*/
119   public static final int EMPTY_CONTENT = 3;
120 
121   /** LR classes supported at present */
122   public static final String DOCUMENT_CLASS = "gate.corpora.DatabaseDocumentImpl";
123   /** LR classes supported at present */
124   public static final String CORPUS_CLASS =  "gate.corpora.DatabaseCorpusImpl";
125 
126   /** key in T_PARAMETER that defines a unique id for the data store */
127   public static final String  DB_PARAMETER_GUID = "DB_GUID";
128 
129   //dummy key
130   //hopefully no one will create a feature with such key
131   /** dummy feature key, do not use it */
132   public static final String DUMMY_FEATURE_KEY =  "--NO--SUCH--KEY--";
133   /** dummy encoding type, do not use it */
134   public static final String DUMMY_ENCODING =  "-!-";
135 
136   /** used internaly, may change in the future */
137   public static final int READ_ACCESS = 0;
138   /** used internaly, may change in the future */
139   public static final int WRITE_ACCESS = 1;
140 
141   //dummy ID
142   /** huh? */
143   public static final Long DUMMY_ID;
144 
145 
146   //!!! WARNING !!!
147   // these 4 constants should *always* be synchronzied with the ones in the
148   // related SQL packages/scripts [for Oracle - security.spc]
149   // i.e. if u don't have a serious reason do *not* change anything
150 
151   /** used to store corpus' features */
152   protected static final int FEATURE_OWNER_CORPUS  = 1;
153   /** used to store document's features */
154   protected static final int FEATURE_OWNER_DOCUMENT  = 2;
155   /** used to store annotation's features */
156   protected static final int FEATURE_OWNER_ANNOTATION  = 3;
157 
158   /** feature value is null  */
159   public static final int VALUE_TYPE_NULL              = 100;
160   /** feature value is int  */
161   public static final int VALUE_TYPE_INTEGER           = 101;
162   /** feature value is long */
163   public static final int VALUE_TYPE_LONG              = 102;
164   /** feature value is boolean */
165   public static final int VALUE_TYPE_BOOLEAN           = 103;
166   /** feature value is string less than 4000 bytes */
167   public static final int VALUE_TYPE_STRING            = 104;
168   /** feature value is binary */
169   public static final int VALUE_TYPE_BINARY            = 105;
170   /** feature value is float */
171   public static final int VALUE_TYPE_FLOAT             = 106;
172   /** feature value is array of ints */
173   public static final int VALUE_TYPE_INTEGER_ARR       = 107;
174   /** feature value is array of longs */
175   public static final int VALUE_TYPE_LONG_ARR          = 108;
176   /** feature value is array of bools */
177   public static final int VALUE_TYPE_BOOLEAN_ARR       = 109;
178   /** feature value is array of strings */
179   public static final int VALUE_TYPE_STRING_ARR        = 110;
180   /** feature value is array of binary values */
181   public static final int VALUE_TYPE_BINARY_ARR        = 111;
182   /** feature value is array of floats */
183   public static final int VALUE_TYPE_FLOAT_ARR         = 112;
184   /** feature value is array of floats */
185   public static final int VALUE_TYPE_EMPTY_ARR         = 113;
186 
187   /** Oracle database type */
188   public static final int ORACLE_DB = 101;
189   /** PostgreSQL database type */
190   public static final int POSTGRES_DB = 102;
191 
192   private static final boolean DEBUG = false;
193 
194   private static boolean  driversLoaded;
195   private static HashMap pools;
196 
197   /** size (in elements) of the jdbc connection pool (if any) */
198   private static final int POOL_SIZE = 20;
199 
200   static {
201     DUMMY_ID = new Long(Long.MIN_VALUE);
202     driversLoaded = false;
203     pools = new HashMap();
204   }
205 
206 
207   protected DBHelper() {
208 
209     //no way
210     //contains only static methods
211   }
212 
213   /** --- */
214   private static synchronized void loadDrivers()
215     throws ClassNotFoundException {
216 
217     if (!driversLoaded) {
218       Class.forName(jdbcOracleDriverName);
219       Class.forName(jdbcPostgresDriverName);
220 //      Class.forName(jdbcSapDBDriverName);
221 
222       driversLoaded = true;
223     }
224   }
225 
226 
227   /**
228    *  closes a result set
229    *  note that Oracle jdbc classes do not have finalize() implementations so if
230    *  they're not closed leaks may occur
231    */
232   public static void cleanup(ResultSet rs)
233     throws PersistenceException {
234 
235     try {
236       if (rs!=null)
237         rs.close();
238     }
239     catch(SQLException sqle) {
240       throw new PersistenceException("an SQL exception occured ["+ sqle.getMessage()+"]");
241     }
242   }
243 
244   /**
245    *  closes a statement
246    *  note that Oracle jdbc classes do not have finalize() implementations so if
247    *  they're not closed leaks may occur
248    */
249   public static void cleanup(Statement stmt)
250     throws PersistenceException {
251     try {
252       if (stmt!=null)
253         stmt.close();
254     }
255     catch(SQLException sqle) {
256       throw new PersistenceException("an SQL exception occured ["+ sqle.getMessage()+"]");
257     }
258   }
259 
260   /**
261    *  connects to DB
262    */
263   public static Connection connect(String connectURL)
264     throws SQLException,ClassNotFoundException{
265 
266     loadDrivers();
267     Connection conn = DriverManager.getConnection(connectURL);
268 
269     if (DEBUG) {
270       DatabaseMetaData meta = conn.getMetaData();
271       gate.util.Err.println(
272             "JDBC driver name=["+meta.getDriverName() +
273             "] version=["+ meta.getDriverVersion() +"]");
274     }
275 
276     return conn;
277   }
278 
279   /**
280    *  connects to DB
281    */
282   public static Connection connect(String connectURL, String user, String pass)
283     throws SQLException,ClassNotFoundException{
284 
285     loadDrivers();
286     Connection conn = DriverManager.getConnection(connectURL, user, pass);
287 
288     if (DEBUG) {
289       DatabaseMetaData meta = conn.getMetaData();
290       gate.util.Err.println(
291             "JDBC driver name=["+meta.getDriverName() +
292             "] version=["+ meta.getDriverVersion() +"]");
293     }
294 
295     return conn;
296   }
297 
298   /**
299    * disconnects from DB, may return connection to pool if such exists
300    *
301    * any uncommited transactions are rolled back
302    */
303   public static void disconnect(Connection conn)
304     throws PersistenceException{
305 
306     //2. close the JDBC connection
307     try {
308       //rollback uncommited transactions
309       conn.rollback();
310       conn.close();
311     }
312     catch (SQLException sqle) {
313       throw new PersistenceException("cannot close JDBC connection, DB error is ["+
314                                       sqle.getMessage() +"]");
315     }
316   }
317 
318   /**
319    *  connects to DB
320    * gets connection from pool if such exists
321    */
322   public static Connection connect(String connectURL,boolean usePool)
323     throws SQLException,ClassNotFoundException{
324 
325     if (false == usePool) {
326       return connect(connectURL);
327     }
328     else {
329       ConnectionPool currPool = null;
330 
331       synchronized(pools) {
332         if (false == pools.containsKey(connectURL)) {
333           currPool = new ConnectionPool(POOL_SIZE, connectURL);
334           pools.put(connectURL, currPool);
335         }
336         else {
337           currPool = (ConnectionPool) pools.get(connectURL);
338         }
339       }
340 
341       return currPool.get();
342     }
343   }
344 
345   /**
346    * disconnects from DB, may return connection to pool if such exists
347    *
348    * any uncommited transactions are rolled back
349    */
350   public static void disconnect(Connection conn, boolean usePool)
351     throws PersistenceException{
352 
353     if (false == usePool) {
354       disconnect(conn);
355     }
356     else {
357       String jdbcURL = null;
358 
359       try {
360         jdbcURL = conn.getMetaData().getURL();
361         conn.rollback();
362       }
363       catch(SQLException sqle) {
364         throw new PersistenceException(sqle);
365       }
366 
367       ConnectionPool currPool = (ConnectionPool) pools.get(jdbcURL);
368       currPool.put(conn);
369     }
370   }
371 
372   public static String getSchemaPrefix(String jdbcURL) {
373 
374     if (jdbcURL.startsWith("jdbc:oracle")) {
375       return Gate.DB_OWNER+".";
376     }
377     else if (jdbcURL.startsWith("jdbc:postgres")) {
378       return "";
379     }
380     else {
381       throw new IllegalArgumentException();
382     }
383   }
384 
385   public static int getDatabaseType(String jdbcURL) {
386 
387     if (jdbcURL.startsWith("jdbc:oracle")) {
388       return DBHelper.ORACLE_DB;
389     }
390     else if (jdbcURL.startsWith("jdbc:postgres")) {
391       return DBHelper.POSTGRES_DB;
392     }
393     else {
394       throw new IllegalArgumentException();
395     }
396   }
397 
398 }
399