7bb146fd81b91f05e7048f40c539a407e1ec6db3
[debian/jabref.git] / src / java / net / sf / jabref / sql / SQLUtil.java
1 /*  Copyright (C) 2003-2011 JabRef contributors.
2     This program is free software; you can redistribute it and/or modify
3     it under the terms of the GNU General public static License as published by
4     the Free Software Foundation; either version 2 of the License, or
5     (at your option) any later version.
6
7     This program is distributed in the hope that it will be useful,
8     but WITHOUT ANY WARRANTY; without even the implied warranty of
9     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
10     GNU General public static License for more details.
11
12     You should have received a copy of the GNU General public static License along
13     with this program; if not, write to the Free Software Foundation, Inc.,
14     51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
15  */
16 package net.sf.jabref.sql;
17
18 import java.io.PrintStream;
19 import java.sql.Connection;
20 import java.sql.ResultSet;
21 import java.sql.SQLException;
22 import java.sql.SQLWarning;
23 import java.sql.Statement;
24 import java.util.ArrayList;
25 import java.util.Arrays;
26 import java.util.List;
27 import java.util.ListIterator;
28
29 import net.sf.jabref.BibtexFields;
30 import net.sf.jabref.Globals;
31
32 /**
33  * 
34  * @author pattonlk
35  * 
36  *         Reestructured by ifsteinm. Jan 20th Now it is possible to export more
37  *         than one jabref database. BD creation, insertions and queries where
38  *         reformulated to accomodate the changes. The changes include a
39  *         refactory on import/export to SQL module, creating many other classes
40  *         making them more readable This class just support Exporters and
41  *         Importers
42  */
43
44 public class SQLUtil {
45
46         private static final ArrayList<String> reservedDBWords = new ArrayList<String>(
47                         Arrays.asList("key"));
48
49         private static ArrayList<String> allFields = null;
50
51         private SQLUtil() {
52         }
53
54         /**
55          * loop through entry types to get required, optional, general and utility
56          * fields for this type.
57          */
58         public static void refreshFields() {
59                 if (allFields == null) {
60                         allFields = new ArrayList<String>();
61                 } else {
62                         allFields.clear();
63                 }
64                 uniqueInsert(allFields, BibtexFields.getAllFieldNames());
65         }
66
67         /**
68          * 
69          * @return All existent fields for a bibtex entry
70          */
71         public static ArrayList<String> getAllFields() {
72                 if (allFields == null)
73                         refreshFields();
74                 return allFields;
75         }
76
77         /**
78          * 
79          * @return Create a common separated field names
80          */
81         public static String getFieldStr() {
82                 // create comma separated list of field names
83                 String fieldstr = "";
84                 String field = "";
85                 for (int i = 0; i < getAllFields().size(); i++) {
86                         field = allFields.get(i);
87                         if (i > 0)
88                                 fieldstr = fieldstr + ", ";
89                         if (reservedDBWords.contains(field))
90                                 field += "_";
91                         fieldstr = fieldstr + field;
92                 }
93                 return fieldstr;
94         }
95
96         /**
97          * Inserts the elements of a String array into an ArrayList making sure not
98          * to duplicate entries in the ArrayList
99          * 
100          * @param list
101          *            The ArrayList containing unique entries
102          * @param array
103          *            The String array to be inserted into the ArrayList
104          * @return The updated ArrayList with new unique entries
105          */
106         private static ArrayList<String> uniqueInsert(ArrayList<String> list,
107                         String[] array) {
108                 if (array != null) {
109                         for (int i = 0; i < array.length; i++) {
110                                 if (!list.contains(array[i]))
111                                         list.add(array[i]);
112                         }
113                 }
114                 return list;
115         }
116
117         /**
118          * Generates DML specifying table columns and their datatypes. The output of
119          * this routine should be used within a CREATE TABLE statement.
120          * 
121          * @param fields
122          *            Contains unique field names
123          * @param datatype
124          *            Specifies the SQL data type that the fields should take on.
125          * @return The SQL code to be included in a CREATE TABLE statement.
126          */
127         public static String fieldsAsCols(ArrayList<String> fields, String datatype) {
128                 String str = "";
129                 String field = "";
130                 ListIterator<String> li = fields.listIterator();
131                 while (li.hasNext()) {
132                         field = li.next();
133                         if (reservedDBWords.contains(field))
134                                 field = field + "_";
135                         str = str + field + datatype;
136                         if (li.hasNext())
137                                 str = str + ", ";
138                 }
139                 return str;
140         }
141         
142         /**
143          * 
144          * @param allFields
145          *            All existent fields for a given entry type
146          * @param reqFields
147          *            list containing required fields for an entry type
148          * @param optFields
149          *            list containing optional fields for an entry type
150          * @param utiFields
151          *            list containing utility fields for an entry type
152          * @param origList
153          *            original list with the correct size filled with the default
154          *            values for each field
155          * @return origList changing the values of the fields that appear on
156          *         reqFields, optFields, utiFields set to 'req', 'opt' and 'uti'
157          *         respectively
158          */
159         public static ArrayList<String> setFieldRequirement(
160                         ArrayList<String> allFields, List<String> reqFields,
161                         List<String> optFields, List<String> utiFields,
162                         ArrayList<String> origList) {
163
164                 String currentField = null;
165                 for (int i = 0; i < allFields.size(); i++) {
166                         currentField = allFields.get(i);
167                         if (reqFields.contains(currentField))
168                                 origList.set(i, "req");
169                         else if (optFields.contains(currentField))
170                                 origList.set(i, "opt");
171                         else if (utiFields.contains(currentField))
172                                 origList.set(i, "uti");
173                 }
174                 return origList;
175         }
176
177         /**
178          * Return a message raised from a SQLException
179          * 
180          * @param ex
181          *            The SQLException raised
182          */
183         public static String getExceptionMessage(Exception ex) {
184                 String msg = null;
185                 if (ex.getMessage() == null) {
186                         msg = ex.toString();
187                 } else {
188                         msg = ex.getMessage();
189                 }
190                 return msg;
191         }
192
193         /**
194          * return a ResultSet with the result of a "SELECT *" query for a given
195          * table
196          * 
197          * @param conn
198          *            Connection to the database
199          * @param tableName
200          *            String containing the name of the table you want to get the
201          *            results.
202          * @return a ResultSet with the query result returned from the DB
203          * @throws SQLException
204          */
205         public static ResultSet queryAllFromTable(Connection conn, String tableName)
206                         throws SQLException {
207                 String query = "SELECT * FROM " + tableName + ";";
208                 Statement res = (Statement) processQueryWithResults(conn, query);
209                 return res.getResultSet();
210         }
211
212         /**
213          * Utility method for processing DML with proper output
214          * 
215          * @param out
216          *            The output (PrintStream or Connection) object to which the DML
217          *            should be sent
218          * @param dml
219          *            The DML statements to be processed
220          */
221         public static void processQuery(Object out, String dml) throws SQLException {
222                 if (out instanceof PrintStream) {
223                         PrintStream fout = (PrintStream) out;
224                         fout.println(dml);
225                 }
226                 if (out instanceof Connection) {
227                         Connection conn = (Connection) out;
228                         executeQuery(conn, dml);
229                 }
230         }
231
232         /**
233          * Utility method for processing DML with proper output
234          * 
235          * @param out
236          *            The output (PrintStream or Connection) object to which the DML
237          *            should be sent
238          * @param query
239          *            The DML statements to be processed
240          * @return the result of the statement
241          */
242         public static Object processQueryWithResults(Object out, String query)
243                         throws SQLException {
244                 if (out instanceof PrintStream) {// TODO: how to handle the PrintStream
245                                                                                         // case?
246                         PrintStream fout = (PrintStream) out;
247                         fout.println(query);
248                         return fout;
249                 }
250                 if (out instanceof Connection) {
251                         Connection conn = (Connection) out;
252                         return executeQueryWithResults(conn, query);
253                 }
254                 return null;
255         }
256
257         /**
258          * This routine returns the JDBC url corresponding to the DBStrings input.
259          * 
260          * @param dbStrings
261          *            The DBStrings to use to make the connection
262          * @return The JDBC url corresponding to the input DBStrings
263          */
264         public static String createJDBCurl(DBStrings dbStrings, boolean withDBName) {
265                 String url = "";
266                 url = "jdbc:" + dbStrings.getServerType().toLowerCase() + "://"
267                                 + dbStrings.getServerHostname()
268                                 + (withDBName ? "/" + dbStrings.getDatabase() : "");
269                 return url;
270         }
271
272         /**
273          * Process a query and returns only the first result of a result set as a
274          * String. To be used when it is certain that only one String (single cell)
275          * will be returned from the DB
276          * 
277          * @param out
278          *            The output (PrintStream or Connection) object to which the DML
279          *            should be sent
280          * @param query
281          *            The query statements to be processed
282          * @return String with the result returned from the database
283          * @throws SQLException
284          */
285         public static String processQueryWithSingleResult(Connection conn,
286                         String query) throws SQLException {
287                 ResultSet rs = ((Statement) executeQueryWithResults(conn, query))
288                                 .getResultSet();
289                 rs.next();
290                 String result = rs.getString(1);
291                 rs.getStatement().close();
292                 return result;
293         }
294
295         /**
296          * Utility method for executing DML
297          * 
298          * @param conn
299          *            The DML Connection object that will execute the SQL
300          * @param qry
301          *            The DML statements to be executed
302          */
303         public static void executeQuery(Connection conn, String qry)
304                         throws SQLException {
305                 Statement stmnt = conn.createStatement();
306                 stmnt.execute(qry);
307                 SQLWarning warn = stmnt.getWarnings();
308                 if (warn != null) {
309                         System.err.println(warn.toString());
310                 }
311                 stmnt.close();
312         }
313
314         /**
315          * Utility method for executing DML
316          * 
317          * @param conn
318          *            The DML Connection object that will execute the SQL
319          * @param qry
320          *            The DML statements to be executed
321          */
322         public static Statement executeQueryWithResults(Connection conn, String qry)
323                         throws SQLException {
324                 Statement stmnt = conn.createStatement();
325                 stmnt.executeQuery(qry);
326                 SQLWarning warn = stmnt.getWarnings();
327                 if (warn != null) {
328
329                         System.err.println(warn.toString());
330                 }
331                 return stmnt;
332         }
333         
334         
335 }