001/*
002 * Copyright (c) 2004-2010 The Regents of the University of California.
003 * All rights reserved.
004 *
005 * '$Author: jianwu $'
006 * '$Date: 2010-12-09 23:11:45 +0000 (Thu, 09 Dec 2010) $' 
007 * '$Revision: 26470 $'
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.ecoinformatics.seek.dataquery;
031
032import java.sql.Connection;
033import java.sql.ResultSet;
034import java.sql.SQLException;
035import java.sql.Statement;
036
037import org.apache.commons.logging.Log;
038import org.apache.commons.logging.LogFactory;
039import org.kepler.util.sql.DatabaseFactory;
040
041/**
042 * This class will check if a given table name already existed in db. This class
043 * has tigh relationship to hsql. If we use different sql engine This class need
044 * to re-implemented.
045 * 
046 * @author Jing Tao
047 * 
048 */
049
050public class DBTableExistenceChecker {
051
052        // this table name should NOT be easy to be duplidate
053        // Make this table name weild to make sure it is hard to duplicate
054        private static final String TABLENAME = "KEPLERTEXTTABLES";
055        private static final String TABLENAMEFIELD = "TABLENAME";
056        private static final String URLFIELD = "URL";
057
058        private static Log log;
059        private static boolean isDebugging;
060
061        static {
062                log = LogFactory.getLog("org.ecoinformatics.seek.dataquery");
063                isDebugging = log.isDebugEnabled();
064        }
065
066        /**
067         * Check if a given url existed. Url is a key for table
068         * 
069         * @param url
070         *            String
071         * @throws SQLException
072         * @throws ClassNotFoundException
073         * @return boolean
074         */
075        public boolean isURLExisted(String url) throws SQLException,
076                        ClassNotFoundException {
077                return isGivenStringExisted(URLFIELD, url);
078        }
079
080        /**
081         * Check if a given table name existed
082         * 
083         * @param tableName
084         *            String
085         * @throws SQLException
086         * @throws ClassNotFoundException
087         * @return boolean
088         */
089        public boolean isTableNameExisted(String tableName) throws SQLException,
090                        ClassNotFoundException {
091                return isGivenStringExisted(TABLENAMEFIELD, tableName);
092        }
093
094        /*
095         * A method will check if a given string existed in the table
096         */
097        private boolean isGivenStringExisted(String fieldName, String givenString)
098                        throws SQLException, ClassNotFoundException {
099                boolean existed = true;
100                String sql = DBTablesGenerator.SELECT + DBTablesGenerator.SPACE
101                                + fieldName + DBTablesGenerator.SPACE + DBTablesGenerator.FROM
102                                + DBTablesGenerator.SPACE + TABLENAME + DBTablesGenerator.SPACE
103                                + DBTablesGenerator.WHERE + DBTablesGenerator.SPACE + fieldName
104                                + DBTablesGenerator.SPACE + DBTablesGenerator.LIKE
105                                + DBTablesGenerator.SPACE + "'" + givenString + "'"
106                                + DBTablesGenerator.SEMICOLON;
107                if (isDebugging) {
108                        log.debug("The sql for checking table if it existed is " + sql);
109                }
110                Connection conn = DatabaseFactory.getDBConnection();
111                Statement st = conn.createStatement();
112                ResultSet rs = st.executeQuery(sql);
113                if (rs.next()) {
114                        existed = true;
115                } else {
116                        existed = false;
117                }
118                rs.close();
119                st.close();
120                conn.close();
121                return existed;
122        }// isTalbeExisted
123
124        /**
125         * Method will store a gnerated table info (table name and url) into a
126         * persistant table
127         * 
128         * @param tableName
129         *            String
130         * @param url
131         *            String
132         * @throws SQLException
133         * @throws ClassNotFoundException
134         */
135        public void storeTableRecord(String tableName, String url)
136                        throws SQLException, ClassNotFoundException {
137                String sql = DBTablesGenerator.INSERT + DBTablesGenerator.SPACE
138                                + TABLENAME + DBTablesGenerator.SPACE
139                                + DBTablesGenerator.LEFTPARENTH + TABLENAMEFIELD
140                                + DBTablesGenerator.COMMA + URLFIELD
141                                + DBTablesGenerator.RIGHTPARENTH + DBTablesGenerator.SPACE
142                                + DBTablesGenerator.VALUES + DBTablesGenerator.SPACE
143                                + DBTablesGenerator.LEFTPARENTH + "'" + tableName + "'"
144                                + DBTablesGenerator.COMMA + "'" + url + "'"
145                                + DBTablesGenerator.RIGHTPARENTH + DBTablesGenerator.SEMICOLON;
146                if (isDebugging) {
147                        log.debug("The sql to insert table record into storing table is "
148                                        + sql);
149                }
150                Connection conn = DatabaseFactory.getDBConnection();
151                Statement st = conn.createStatement();
152                st.execute(sql);
153                st.close();
154                conn.close();
155        }// storeTableRecord
156
157        public void deleteRecord(String tableName, String url) throws SQLException,
158                        ClassNotFoundException {
159                String sql = DBTablesGenerator.DELETE + DBTablesGenerator.SPACE
160                                + DBTablesGenerator.FROM + DBTablesGenerator.SPACE + TABLENAME
161                                + DBTablesGenerator.SPACE + DBTablesGenerator.WHERE
162                                + DBTablesGenerator.SPACE + DBTablesGenerator.LEFTPARENTH
163                                + TABLENAMEFIELD + DBTablesGenerator.SPACE
164                                + DBTablesGenerator.LIKE + DBTablesGenerator.SPACE + "'"
165                                + tableName + "'" + DBTablesGenerator.SPACE
166                                + DBTablesGenerator.AND + DBTablesGenerator.SPACE + URLFIELD
167                                + DBTablesGenerator.SPACE + DBTablesGenerator.LIKE
168                                + DBTablesGenerator.SPACE + "'" + url + "'"
169                                + DBTablesGenerator.RIGHTPARENTH + DBTablesGenerator.SEMICOLON;
170                if (isDebugging) {
171                        log.debug("delete record from table is " + sql);
172                }
173                Connection conn = DatabaseFactory.getDBConnection();
174                Statement st = conn.createStatement();
175                st.execute(sql);
176                st.close();
177                conn.close();
178        }
179
180        /**
181         * This method will return a tableName for a given URL in the storing table
182         * 
183         * @param url
184         *            String
185         * @return String
186         */
187        public String getTableName(String url) throws SQLException,
188                        ClassNotFoundException {
189                String tableName = null;
190                if (url == null) {
191                        if (isDebugging) {
192                                log
193                                                .debug("The table for given url " + url + " is "
194                                                                + tableName);
195                        }
196                        return tableName;
197                }
198                String sql = DBTablesGenerator.SELECT + DBTablesGenerator.SPACE
199                                + TABLENAMEFIELD + DBTablesGenerator.SPACE
200                                + DBTablesGenerator.FROM + DBTablesGenerator.SPACE + TABLENAME
201                                + DBTablesGenerator.SPACE + DBTablesGenerator.WHERE
202                                + DBTablesGenerator.SPACE + URLFIELD + DBTablesGenerator.SPACE
203                                + DBTablesGenerator.LIKE + DBTablesGenerator.SPACE + "'" + url
204                                + "'" + DBTablesGenerator.SEMICOLON;
205                if (isDebugging) {
206                        log.debug("The sql to get table name from url is " + sql);
207                }
208                Connection conn = DatabaseFactory.getDBConnection();
209                Statement st = conn.createStatement();
210                ResultSet rs = st.executeQuery(sql);
211                if (rs.next()) {
212                        tableName = rs.getString(1);
213                }
214                if (isDebugging) {
215                        log.debug("The table for given url " + url + " is " + tableName);
216                }
217                rs.close();
218                conn.close();
219                return tableName;
220        }
221
222}// DBTableExistenceChecker