001/*
002 * Copyright (c) 2010 The Regents of the University of California.
003 * All rights reserved.
004 *
005 * '$Author: welker $'
006 * '$Date: 2010-05-06 05:21:26 +0000 (Thu, 06 May 2010) $' 
007 * '$Revision: 24234 $'
008 * 
009 * Permission is hereby granted, without written agreement and without
010 * license or royalty fees, to use, copy, modify, and distribute this
011 * software and its documentation for any purpose, provided that the above
012 * copyright notice and the following two paragraphs appear in all copies
013 * of this software.
014 *
015 * IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY
016 * FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES
017 * ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF
018 * THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF
019 * SUCH DAMAGE.
020 *
021 * THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
022 * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
023 * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE
024 * PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF
025 * CALIFORNIA HAS NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, UPDATES,
026 * ENHANCEMENTS, OR MODIFICATIONS.
027 *
028 */
029
030package org.kepler.objectmanager.library;
031
032import java.sql.Connection;
033import java.sql.PreparedStatement;
034import java.sql.ResultSet;
035import java.sql.SQLException;
036import java.sql.Statement;
037import java.util.Vector;
038
039import org.apache.commons.logging.Log;
040import org.apache.commons.logging.LogFactory;
041import org.kepler.util.sql.DatabaseFactory;
042
043public class LibSearch {
044        private static final Log log = LogFactory.getLog(LibSearch.class.getName());
045        private static final boolean isDebugging = log.isDebugEnabled();
046
047        /**
048         * the name of the table in the database
049         */
050        public static final String LIB_SEARCH_TABLE_NAME = "LIBRARY_SEARCH";
051
052        /**
053         * Map of integers for the different types of strings that are stored in the
054         * CACHE_SEARCH table.
055         */
056        public static final int TYPE_NAME = 1;
057        public static final int TYPE_CLASSNAME = 2;
058        public static final int TYPE_ONTCLASSNAME = 3;
059        public static final int TYPE_ONTOLOGY = 4;
060        public static final int TYPE_FOLDERNAME = 5;
061        public static final int TYPE_KARNAME = 6;
062        public static final int TYPE_LOCALREPO = 7;
063
064        private Connection _conn;
065        private Statement _stmt;
066        private PreparedStatement _insertPrepStmt;
067        private PreparedStatement _deletePrepStmt;
068        private PreparedStatement _deleteAllPrepStmt;
069
070        /**
071         * A constructor that creates a new connection to the database.
072         * 
073         * @throws Exception
074         */
075        public LibSearch() throws Exception {
076                if (isDebugging)
077                        log.debug("new CacheSearch()");
078                try {
079                        initialize(DatabaseFactory.getDBConnection());
080                } catch (Exception e) {
081                        e.printStackTrace();
082                        throw new Exception("Error obtaining database connection: "
083                                        + e.getMessage());
084                }
085
086        }
087
088        /**
089         * A constructor that is given a connection to the database.
090         * 
091         * @param conn
092         */
093        public LibSearch(Connection conn) {
094                initialize(conn);
095        }
096
097        /**
098         * Initialize the instance.
099         * 
100         * @param conn
101         */
102        public void initialize(Connection conn) {
103                if (isDebugging)
104                        log.debug("initialize(" + conn.toString() + ")");
105                _conn = conn;
106
107                try {
108                        // By creating the statement and keeping it around
109                        // make sure to close your resultsets to save memory
110                        _stmt = _conn.createStatement();
111                        _insertPrepStmt = _conn.prepareStatement("insert into "
112                                        + LIB_SEARCH_TABLE_NAME
113                                        + " (sid, type, liid, searchstring) values ( ?, ?, ?, ? )");
114                        _deletePrepStmt = _conn.prepareStatement("delete from "
115                                        + LIB_SEARCH_TABLE_NAME + " where liid = ?");
116                        _deleteAllPrepStmt = _conn.prepareStatement("delete from "
117                                        + LIB_SEARCH_TABLE_NAME);
118
119                } catch (SQLException e) {
120                        e.printStackTrace();
121                }
122        }
123
124        /**
125         * Search all search string types in the index for a specific string.
126         * 
127         * @param value
128         * @return Vector<Integer> of Library Index IDs that match
129         */
130        public Vector<Integer> search(String value) {
131                // Read the configuration
132                LibSearchConfiguration lsc = new LibSearchConfiguration();
133                return search(value, lsc.getSearchTypes());
134        }
135
136        /**
137         * Search a specific search string type in the index for the given value.
138         * 
139         * @param value
140         * @param type
141         * @return Vector<Integer> of Library Index IDs that match
142         */
143        public Vector<Integer> search(String value, int type) {
144                Vector<Integer> types = new Vector<Integer>(1);
145                types.add(new Integer(type));
146                return search(value, types);
147        }
148
149        /**
150         * Return all LIID values that match the given string and search types.
151         * 
152         * @param value
153         * @param types
154         * @return Vector<Integer> that are the Library Index IDs that match
155         */
156        public Vector<Integer> search(String value, Vector<Integer> types) {
157                Vector<Integer> liids = new Vector<Integer>();
158                try {
159                        String query = "SELECT liid FROM " + LIB_SEARCH_TABLE_NAME
160                                        + " WHERE searchstring like '%" + value.toLowerCase()
161                                        + "%'";
162                        if (types.size() > 0) {
163                                query += " and ( type = " + types.elementAt(0);
164                                for (int i = 1; i < types.size(); i++) {
165                                        query += " or type = " + types.elementAt(i);
166                                }
167                                query += " ) ";
168                        }
169                        if (isDebugging)
170                                log.debug(query);
171
172                        ResultSet rs = _stmt.executeQuery(query);
173                        if (rs != null) {
174                                while (rs.next()) {
175                                        int liid = rs.getInt(1);
176                                        liids.add(new Integer(liid));
177                                }
178                                rs.close();
179                        }
180                } catch (Exception sqle) {
181                        sqle.printStackTrace();
182                }
183                return liids;
184        }
185
186        /**
187         * Delete everything from the database table.
188         */
189        public void clear() throws SQLException {
190                _deleteAllPrepStmt.executeUpdate();
191
192                String resetAutoInc = "ALTER TABLE " + LIB_SEARCH_TABLE_NAME
193                                + " ALTER COLUMN SID RESTART WITH 1";
194                _stmt.execute(resetAutoInc);
195        }
196
197        /**
198         * Remove all of the entries from the table that match the given KeplerLSID.
199         * 
200         * @param lsid
201         * @throws SQLException
202         */
203        public void remove(int liid) throws SQLException {
204                _deletePrepStmt.setInt(1, liid);
205                _deletePrepStmt.executeUpdate();
206                _deletePrepStmt.clearParameters();
207        }
208
209        /**
210         * Count how many rows there are in the LIBRARY_SEARCH table.
211         * 
212         * @return
213         */
214        public int countItems() {
215                int count = 0;
216                try {
217                        String cntQuery = "SELECT count(SID) from " + LIB_SEARCH_TABLE_NAME;
218                        ResultSet rs = _stmt.executeQuery(cntQuery);
219                        if (rs == null)
220                                return count;
221                        if (rs.next()) {
222                                count = rs.getInt(1);
223                        }
224                        rs.close();
225                } catch (SQLException sqle) {
226                        sqle.printStackTrace();
227                }
228                return count;
229
230        }
231
232        /**
233         * Convenience method to reduce code redundancy.
234         * 
235         * @param type
236         * @param lsid
237         * @param searchString
238         * @throws Exception
239         */
240        public void insertRow(int type, int liid, String searchString)
241                        throws SQLException {
242                if (isDebugging)
243                        log.debug("insertRow(" + type + "," + liid + "," + searchString
244                                        + ")");
245
246                _insertPrepStmt.setNull(1, java.sql.Types.INTEGER);
247                _insertPrepStmt.setInt(2, type);
248                _insertPrepStmt.setInt(3, liid);
249                _insertPrepStmt.setString(4, searchString.toLowerCase());
250                try {
251                        _insertPrepStmt.executeUpdate();
252                } catch (SQLException sqle) {
253                        // if (isDebugging) log.debug(sqle.getMessage());
254                        if (sqle.getErrorCode() == -104) {
255                                // the insert violated the unique key (LIID,SEARCHSTRING)
256                                // so we're just going to ignore the error since this
257                                // search string already exists for this LIID
258                                // if (isDebugging) log.debug("Ignoring duplicate insert");
259                        } else {
260                                throw sqle;
261                        }
262                } finally {
263                        _insertPrepStmt.clearParameters();
264                }
265
266        }
267}