001/*
002 * Copyright (c) 2010 The Regents of the University of California.
003 * All rights reserved.
004 *
005 * '$Author: jianwu $'
006 * '$Date: 2010-12-09 22:47:22 +0000 (Thu, 09 Dec 2010) $' 
007 * '$Revision: 26468 $'
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
030/* Lidar jobs monitoring database processing. 
031 */
032
033package org.geon;
034
035import java.io.BufferedReader;
036import java.io.FileReader;
037import java.io.FileWriter;
038import java.io.PrintWriter;
039import java.sql.Connection;
040import java.sql.DriverManager;
041import java.sql.ResultSet;
042import java.sql.Statement;
043import java.util.HashMap;
044import java.util.Iterator;
045import java.util.Map;
046import java.util.StringTokenizer;
047import java.util.Vector;
048
049import javax.servlet.http.HttpServletRequest;
050
051//////////////////////////////////////////////////////////////////////////
052//// ExecutionThread
053/**
054 * Thread for executing the Lidar processing.
055 * 
056 * @author Efrat Jaeger
057 */
058public class LidarJobDB {
059
060        private static final String LIDARJOBS = "LIDAR.LIDARJOBS";
061        private static final String JOBSTATUS = "LIDAR.JOBSTATUS";
062        private static final String DATASETS = "LIDAR.DATASETS";
063        private static final String JOBCLASSIFICATIONS = "LIDAR.JOBCLASSIFICATIONS";
064        private static final String JOBPROCESSINGS = "LIDAR.JOBPROCESSINGS";
065        private static final String JOBDESCRIPTION = "LIDAR.JOBDESCRIPTION";
066        private static final String LIDARACCESSLIST = "LIDAR.LIDARACCESSLIST";
067        private static final String PENDINGACCESSLIST = "LIDAR.PENDINGACCESSLIST";
068        private static final String COMMENT_CHAR = "#";
069        private static final String algs[] = { "elev", "slope", "aspect", "pcurv" };
070        private static final String formats[] = { "view", "arc", "ascii", "tiff" };
071
072        public LidarJobDB(String configFile) {
073                setProperties(configFile);
074        }
075
076        public LidarJobDB(String configFile, String submissionDate) {
077                this.submissionDate = submissionDate;
078                setProperties(configFile);
079        }
080
081        private String dbclassname;
082        private String dburl;
083        private String username;
084        private String password;
085        private Map propsMap = new HashMap();
086        private Connection con;
087        private String submissionDate;
088
089        public void setProperties(String configFile) {
090                try {
091                        BufferedReader br = new BufferedReader(new FileReader(configFile));
092                        String line = br.readLine();
093                        while (line != null) {
094                                line = line.trim();
095                                if (!(line.startsWith(COMMENT_CHAR) || line.equals(""))) {
096                                        StringTokenizer st = new StringTokenizer(line, "=");
097                                        propsMap.put(st.nextToken(), st.nextToken());
098                                }
099                                line = br.readLine();
100                        }
101                        DBsetupvars();
102                } catch (Exception ex) {
103                        ex.printStackTrace();
104                }
105        }
106
107        public void DBsetupvars() {
108                dbclassname = (String) propsMap.get("mdbc.classname");
109                dburl = (String) propsMap.get("mdbc.url");
110                username = (String) propsMap.get("mdbc.username");
111                password = (String) propsMap.get("mdbc.password");
112        }
113
114        public void createNewEntry(HttpServletRequest request) {
115                String jobId = (String) request.getParameter("id");
116                if (jobId == null || jobId.equals("")) {
117                        System.out.println("ERROR! Job id cannot be null");
118                        return;
119                }
120                try {
121                        connect();
122                        createNewJobEntry(request);
123                        createNewJobClassificationsEntry(request);
124                        createNewJobProcessingsEntry(request);
125                        createNewJobStatusEntry(jobId);
126                        disconnect();
127                } catch (Exception ex) {
128                        try {
129                                disconnect();
130                                PrintWriter pw = new PrintWriter(new FileWriter(
131                                                "/tmp/dbLog.txt", true));
132                                ex.printStackTrace(pw);
133                                pw.close();
134                        } catch (Exception e) {
135                                ex.printStackTrace();
136                        }
137                }
138        }
139
140        /**
141         * create a new job entry in the lidar jobs table.
142         * 
143         * @param request
144         * @throws Exception
145         */
146        private void createNewJobEntry(HttpServletRequest request) throws Exception {
147                String jobId = (String) request.getParameter("id");
148
149                // use username instead of email.
150                String userId = request.getParameter("username"); // TODO: would need to
151                                                                                                                        // be userID!!!!
152
153                String srid = request.getParameter("srid");
154                String numRows = request.getParameter("numRows");
155
156                String MinX = request.getParameter("MinX");
157                String MaxX = request.getParameter("MaxX");
158                String MinY = request.getParameter("MinY");
159                String MaxY = request.getParameter("MaxY");
160
161                String res = request.getParameter("resolution");
162                String dmin = request.getParameter("dmin");
163                String tension = request.getParameter("spline_tension");
164                String smooth = request.getParameter("spline_smoothing");
165
166                if (res == null || res.equals(""))
167                        res = "6";
168                if (dmin == null || dmin.equals(""))
169                        dmin = "1";
170                if (tension == null || tension.equals(""))
171                        tension = "40";
172                if (smooth == null || smooth.equals(""))
173                        smooth = "0.1";
174
175                Statement stmt = con.createStatement();
176                System.out.println("INSERT INTO " + LIDARJOBS + " VALUES('" + jobId
177                                + "', '" + userId + "', '" + submissionDate + "', '" + srid
178                                + "', '" + MinX + "', '" + MaxX + "', '" + MinY + "', '" + MaxY
179                                + "', '" + res + "', '" + dmin + "', '" + tension + "', '"
180                                + smooth + "', NULL, NULL, NULL,'" + numRows + "')");
181                stmt.execute("INSERT INTO " + LIDARJOBS + " VALUES('" + jobId + "', '"
182                                + userId + "', '" + submissionDate + "', '" + srid + "', '"
183                                + MinX + "', '" + MaxX + "', '" + MinY + "', '" + MaxY + "', '"
184                                + res + "', '" + dmin + "', '" + tension + "', '" + smooth
185                                + "', NULL, NULL, NULL,'" + numRows + "')");
186                stmt.close();
187
188        }
189
190        /**
191         * Insert all the job's classification attributes.
192         * 
193         * @param request
194         * @throws Exception
195         */
196        private void createNewJobClassificationsEntry(HttpServletRequest request)
197                        throws Exception {
198                String jobId = (String) request.getParameter("id");
199                String[] classification = request.getParameterValues("c");
200
201                Statement stmt = con.createStatement();
202
203                if (classification != null) {
204                        for (int i = 0; i < classification.length; i++) {
205                                System.out.println("INSERT INTO " + JOBCLASSIFICATIONS
206                                                + " VALUES('" + jobId + "', '" + classification[i]
207                                                + "')");
208                                stmt.execute("INSERT INTO " + JOBCLASSIFICATIONS + " VALUES('"
209                                                + jobId + "', '" + classification[i] + "')");
210                        }
211                }
212                stmt.close();
213        }
214
215        /**
216         * Insert all of the job's selected processings.
217         * 
218         * @param request
219         * @throws Exception
220         */
221        private void createNewJobProcessingsEntry(HttpServletRequest request)
222                        throws Exception {
223                String jobId = (String) request.getParameter("id");
224
225                Statement stmt = con.createStatement();
226                for (int i = 0; i < algs.length; i++) {
227                        for (int j = 0; j < formats.length; j++) {
228                                String type = algs[i] + formats[j];
229                                String typeVal = request.getParameter(type);
230                                System.out.println(type + "=" + typeVal);
231                                if (typeVal != null && !typeVal.equals("")) {
232                                        System.out.println("INSERT INTO " + JOBPROCESSINGS
233                                                        + " VALUES('" + jobId + "', '" + type + "')");
234                                        stmt.execute("INSERT INTO " + JOBPROCESSINGS + " VALUES('"
235                                                        + jobId + "', '" + type + "')");
236                                }
237                        }
238                }
239                stmt.close();
240        }
241
242        /**
243         * Create an initial job status entry
244         * 
245         * @param jobId
246         * @throws Exception
247         */
248        public void createNewJobStatusEntry(String jobId) throws Exception {
249                Statement stmt = con.createStatement();
250                System.out.println("INSERT INTO " + JOBSTATUS + " VALUES('" + jobId
251                                + "', 'submitted', '')");
252                stmt.execute("INSERT INTO " + JOBSTATUS + " VALUES('" + jobId
253                                + "', 'submitted', '')");
254                stmt.close();
255        }
256
257        public void updateJobEntry(String jobId, Map fieldValues) {
258
259                if (fieldValues.size() > 0) { // Otherwise there is nothing to update.
260                        String updateQuery = "UPDATE " + LIDARJOBS + " SET";
261                        Iterator keys = fieldValues.keySet().iterator();
262                        while (keys.hasNext()) {
263                                String key = (String) keys.next();
264                                String value = (String) fieldValues.get(key);
265                                updateQuery += " " + key + " = '" + value + "',";
266                        }
267                        // remove last comma.
268                        updateQuery = updateQuery.substring(0, updateQuery.length() - 1);
269                        updateQuery += " WHERE JOBID = '" + jobId + "'";
270                        System.out.println(updateQuery);
271                        try {
272                                connect();
273                                Statement stmt = con.createStatement();
274                                stmt.executeUpdate(updateQuery);
275                                stmt.close();
276                                disconnect();
277                        } catch (Exception ex) {
278                                try {
279                                        disconnect();
280                                        PrintWriter pw = new PrintWriter(new FileWriter(
281                                                        "/tmp/dbLog.txt", true));
282                                        ex.printStackTrace(pw);
283                                        pw.close();
284                                } catch (Exception e) {
285                                        ex.printStackTrace();
286                                }
287                        }
288                }
289        }
290
291        public void setJobStatus(String jobId, String jobStatus, String description) {
292                try {
293                        connect();
294                        Statement stmt = con.createStatement();
295                        ResultSet rs = stmt.executeQuery("SELECT * FROM " + JOBSTATUS
296                                        + " WHERE JOBID = '" + jobId + "'");
297                        boolean exists = false;
298                        while (rs.next()) {
299                                exists = true;
300                                break;
301                        }
302                        rs.close();
303                        if (exists) { // update
304                                System.out.println("UPDATE " + JOBSTATUS + " SET STATUS = '"
305                                                + jobStatus + "', DESCRIPTION = '" + description
306                                                + "' WHERE JOBID = '" + jobId + "'");
307                                stmt.execute("UPDATE " + JOBSTATUS + " SET STATUS = '"
308                                                + jobStatus + "', DESCRIPTION = '" + description
309                                                + "' WHERE JOBID = '" + jobId + "'");
310                        } else { // insert
311                                System.out.println("INSERT INTO " + JOBSTATUS + " VALUES('"
312                                                + jobId + "', '" + jobStatus + "', '" + description
313                                                + "'");
314                                stmt.execute("INSERT INTO " + JOBSTATUS + " VALUES('" + jobId
315                                                + "', '" + jobStatus + "', '" + description + "')");
316                        }
317                        stmt.close();
318                        disconnect();
319                } catch (Exception ex) {
320                        disconnect();
321                        ex.printStackTrace();
322                }
323        }
324
325        /**
326         * Enter a job description
327         * 
328         */
329        public void setJobDescription(String jobId, String title, String description) {
330                try {
331                        connect();
332                        Statement stmt = con.createStatement();
333                        System.out.println("INSERT INTO " + JOBDESCRIPTION + " VALUES('"
334                                        + jobId + "', '" + title + "', '" + description + "')");
335                        stmt.execute("INSERT INTO " + JOBDESCRIPTION + " VALUES('" + jobId
336                                        + "', '" + title + "', '" + description + "')");
337                        stmt.close();
338                        disconnect();
339                } catch (Exception ex) {
340                        disconnect();
341                        ex.printStackTrace();
342                }
343        }
344
345        /**
346         * Get a job description
347         * 
348         */
349        public LidarJobConfig getJobDescription(String jobId) {
350                LidarJobConfig jobConfig = new LidarJobConfig(jobId);
351                String title = "";
352                String description = "";
353                try {
354                        connect();
355                        Statement stmt = con.createStatement();
356                        ResultSet rs = stmt.executeQuery("SELECT TITLE, DESCRIPTION FROM "
357                                        + JOBDESCRIPTION + " WHERE JOBID = '" + jobId + "'");
358                        while (rs.next()) {
359                                title = rs.getString(1);
360                                description = rs.getString(2);
361                                break;
362                        }
363                        rs.close();
364                        stmt.close();
365                        jobConfig.setJobDescription(title, description);
366                        disconnect();
367                        return jobConfig;
368                } catch (Exception ex) {
369                        try {
370                                disconnect();
371                                PrintWriter pw = new PrintWriter(new FileWriter(
372                                                "/tmp/dbLog.txt", true));
373                                ex.printStackTrace(pw);
374                                pw.close();
375                                return jobConfig;
376                        } catch (Exception e) {
377                                ex.printStackTrace();
378                                return jobConfig;
379                        }
380                }
381        }
382
383        /**
384         * Get all of a user's job entries
385         * 
386         * @param userId
387         *       */
388        public LidarJobConfig[] getUserJobs(String userId) {
389                LidarJobConfig[] userJobs = null;
390                try {
391                        connect();
392                        Statement stmt = con.createStatement();
393                        ResultSet rs = stmt.executeQuery("SELECT JOBID, SUBMISSIONDATE "
394                                        + "FROM " + LIDARJOBS + " WHERE USERID = '" + userId + "'");
395                        Vector jobConfigs = new Vector();
396                        while (rs.next()) {
397                                String jobId = rs.getString(1);
398                                String submissionDate = rs.getString(2);
399                                String title = "";
400                                String description = "";
401                                String status = "";
402                                Statement stmt1 = con.createStatement();
403                                ResultSet rsStat = stmt1
404                                                .executeQuery("SELECT TITLE, DESCRIPTION FROM "
405                                                                + JOBDESCRIPTION + " WHERE JOBID = '" + jobId
406                                                                + "'");
407                                while (rsStat.next()) {
408                                        title = rsStat.getString(1);
409                                        description = rsStat.getString(2);
410                                        break;
411                                }
412                                rsStat.close();
413                                rsStat = stmt1.executeQuery("SELECT STATUS FROM " + JOBSTATUS
414                                                + " WHERE JOBID = '" + jobId + "'");
415                                while (rsStat.next()) {
416                                        status = rsStat.getString(1);
417                                        break;
418                                }
419                                rsStat.close();
420                                stmt1.close();
421                                LidarJobConfig jobConfig = new LidarJobConfig(jobId);
422                                jobConfig.setUserId(userId);
423                                jobConfig.setSubmissionDate(submissionDate);
424                                jobConfig.setJobDescription(title, description);
425                                jobConfig.setJobStatus(status);
426                                jobConfigs.add(jobConfig);
427                        }
428                        rs.close();
429                        stmt.close();
430                        if (jobConfigs.size() > 0) {
431                                userJobs = new LidarJobConfig[jobConfigs.size()];
432                                jobConfigs.toArray(userJobs);
433                        }
434                        disconnect();
435                        return userJobs;
436                } catch (Exception ex) {
437                        try {
438                                disconnect();
439                                PrintWriter pw = new PrintWriter(new FileWriter(
440                                                "/tmp/dbLog.txt", true));
441                                ex.printStackTrace(pw);
442                                pw.close();
443                                return userJobs;
444                        } catch (Exception e) {
445                                ex.printStackTrace();
446                                return userJobs;
447                        }
448                }
449        }
450
451        public LidarJobConfig getJobConfig(String jobId) {
452                LidarJobConfig jobConfig = new LidarJobConfig(jobId);
453                try {
454                        connect();
455                        Statement stmt = con.createStatement();
456                        ResultSet rs = stmt.executeQuery("SELECT * " + "FROM " + LIDARJOBS
457                                        + " WHERE JOBID = '" + jobId + "'");
458                        boolean exists = false;
459                        String srid = "";
460                        while (rs.next()) {
461                                String userId = rs.getString("USERID");
462                                jobConfig.setUserId(userId);
463                                String submissionDate = rs.getString("SUBMISSIONDATE");
464                                jobConfig.setSubmissionDate(submissionDate);
465                                srid = rs.getString("SRID");
466                                jobConfig.setSrid(srid);
467                                String xmin = rs.getString("XMIN");
468                                String xmax = rs.getString("XMAX");
469                                String ymin = rs.getString("YMIN");
470                                String ymax = rs.getString("YMAX");
471                                jobConfig.setSpatial(xmin, xmax, ymin, ymax);
472                                String numRows = rs.getString("NUMROWS");
473                                jobConfig.setNumRows(numRows);
474                                String res = rs.getString("RES");
475                                String dmin = rs.getString("DMIN");
476                                String tension = rs.getString("TENSION");
477                                String smooth = rs.getString("SMOOTH");
478                                jobConfig.setAlgAtts(res, dmin, tension, smooth);
479                                String queryTime = rs.getString("QUERYTIME");
480                                String processTime = rs.getString("PROCESSTIME");
481                                String completionDate = rs.getString("COMPLETIONDATE");
482                                jobConfig.setTimings(queryTime, processTime, completionDate);
483                                exists = true;
484                                break;
485                        }
486                        rs.close();
487                        if (!exists) {
488                                System.out.println("No entry for job id " + jobId
489                                                + " in the lidar job archival!");
490                                return null;
491                        }
492                        // get job classifications
493                        rs = stmt.executeQuery("SELECT ATTRIBUTE " + "FROM "
494                                        + JOBCLASSIFICATIONS + " WHERE JOBID = '" + jobId + "'");
495                        Vector cVec = new Vector();
496                        while (rs.next()) {
497                                String c = rs.getString(1);
498                                cVec.add(c);
499                        }
500                        rs.close();
501                        String[] classifications = new String[cVec.size()];
502                        cVec.toArray(classifications);
503                        jobConfig.setClassifications(classifications);
504
505                        // get job processings
506                        rs = stmt.executeQuery("SELECT ALGORITHM " + "FROM "
507                                        + JOBPROCESSINGS + " WHERE JOBID = '" + jobId + "'");
508                        cVec = new Vector();
509                        while (rs.next()) {
510                                String c = rs.getString(1);
511                                cVec.add(c);
512                        }
513                        rs.close();
514                        String[] processings = new String[cVec.size()];
515                        cVec.toArray(processings);
516                        jobConfig.setProcessings(processings);
517                        System.out.println(processings.length);
518                        // get job status
519                        rs = stmt.executeQuery("SELECT STATUS " + "FROM " + JOBSTATUS
520                                        + " WHERE JOBID = '" + jobId + "'");
521                        while (rs.next()) {
522                                String status = rs.getString(1);
523                                jobConfig.setJobStatus(status);
524                                break;
525                        }
526                        rs.close();
527                        if (srid.equals("") || srid == null) {
528                                System.out.println("No dataset entry for job id " + jobId
529                                                + " in the lidar job archival!");
530                                jobConfig.setJobDatasetPath("", "");
531                        } else {
532                                rs = stmt.executeQuery("SELECT URL, PORTLETURI " + "FROM "
533                                                + DATASETS + " WHERE SRID = '" + srid + "'");
534                                String portletUri = "";
535                                String portletUrl = "";
536                                while (rs.next()) {
537                                        portletUrl = rs.getString(1);
538                                        if (portletUrl == null)
539                                                portletUrl = "";
540                                        portletUri = rs.getString(2);
541                                        if (portletUri == null)
542                                                portletUri = "";
543                                        jobConfig.setJobDatasetPath(portletUri, portletUrl);
544                                        break;
545                                }
546                        }
547                        rs.close();
548                        stmt.close();
549                        disconnect();
550                        return jobConfig;
551                } catch (Exception ex) {
552                        try {
553                                disconnect();
554                                PrintWriter pw = new PrintWriter(new FileWriter(
555                                                "/tmp/dbLog.txt", true));
556                                ex.printStackTrace(pw);
557                                pw.close();
558                                return null;
559                        } catch (Exception e) {
560                                ex.printStackTrace();
561                                return null;
562                        }
563                }
564        }
565
566        public String getJobStatus(String jobId) {
567                try {
568                        connect();
569                        Statement stmt = con.createStatement();
570                        ResultSet rs = stmt.executeQuery("SELECT STATUS FROM " + JOBSTATUS
571                                        + " WHERE JOBID = '" + jobId + "'");
572                        String jobStatus = "";
573                        while (rs.next()) {
574                                jobStatus = rs.getString(1);
575                                break; // Each job should have a single entry.
576                        }
577                        stmt.close();
578                        rs.close();
579                        disconnect();
580                        return jobStatus;
581                } catch (Exception ex) {
582                        disconnect();
583                        ex.printStackTrace();
584                        return "";
585                }
586        }
587
588        /**
589         * Add or update user to access/pending access list.
590         * 
591         */
592        public boolean addUser(String tableName, String username, String firstName,
593                        String lastName, String org, String email) {
594                boolean hasAccess = false;
595                try {
596                        connect();
597                        Statement stmt = con.createStatement();
598                        ResultSet rs = stmt.executeQuery("SELECT * FROM LIDAR." + tableName
599                                        + " WHERE USERNAME = '" + username.trim().toLowerCase()
600                                        + "'");
601                        while (rs.next()) {
602                                System.out.println("user has access");
603                                hasAccess = true;
604                                break;
605                        }
606                        rs.close();
607                        if (hasAccess) {
608                                // update
609                                System.out.println("UPDATE LIDAR." + tableName
610                                                + " SET EMAIL = '" + email + "', FIRSTNAME = '"
611                                                + firstName + "', LASTNAME = '" + lastName
612                                                + "', ORGANIZATION = '" + org + "'  WHERE USERNAME = '"
613                                                + username.trim().toLowerCase() + "'");
614                                stmt.execute("UPDATE LIDAR." + tableName + " SET EMAIL = '"
615                                                + email + "', FIRSTNAME = '" + firstName
616                                                + "', LASTNAME = '" + lastName + "', ORGANIZATION = '"
617                                                + org + "'  WHERE USERNAME = '"
618                                                + username.trim().toLowerCase() + "'");
619                        } else {
620                                // insert
621                                System.out.println("INSERT INTO LIDAR." + tableName
622                                                + " VALUES('" + username.trim().toLowerCase() + "', '"
623                                                + email + "', '" + firstName + "', '" + lastName
624                                                + "', '" + org + "')");
625                                stmt.execute("INSERT INTO LIDAR." + tableName + " VALUES('"
626                                                + username.trim().toLowerCase() + "', '" + email
627                                                + "', '" + firstName + "', '" + lastName + "', '" + org
628                                                + "')");
629                        }
630                        stmt.close();
631                        disconnect();
632                        return true;
633                } catch (Exception ex) {
634                        disconnect();
635                        ex.printStackTrace();
636                        return false;
637                }
638        }
639
640        /**
641         * Get existing/pending users list.
642         * 
643         */
644        public String getUsers(String tableName) {
645                String users = "";
646                try {
647                        connect();
648                        Statement stmt = con.createStatement();
649                        ResultSet rs = stmt
650                                        .executeQuery("SELECT * FROM LIDAR." + tableName);
651                        while (rs.next()) {
652                                users += rs.getString(1) + " " + rs.getString(2) + " "
653                                                + rs.getString(3) + " " + rs.getString(4) + " "
654                                                + rs.getString(5) + "<br>\n";
655                        }
656                        stmt.close();
657                        rs.close();
658                        disconnect();
659                        return users;
660                } catch (Exception ex) {
661                        disconnect();
662                        ex.printStackTrace();
663                        return "unable to query " + tableName;
664                }
665        }
666
667        /**
668         * Verify whether a user has access to run lidar jobs.
669         * 
670         */
671        public boolean verifyUser(String username) {
672                boolean hasAccess = false;
673                try {
674                        connect();
675                        Statement stmt = con.createStatement();
676                        System.out.println("SELECT * FROM " + LIDARACCESSLIST
677                                        + " WHERE USERNAME = '" + username.trim().toLowerCase()
678                                        + "'");
679                        ResultSet rs = stmt.executeQuery("SELECT * FROM " + LIDARACCESSLIST
680                                        + " WHERE USERNAME = '" + username.trim().toLowerCase()
681                                        + "'");
682                        while (rs.next()) {
683                                System.out.println("user has access");
684                                hasAccess = true;
685                                break;
686                        }
687                        stmt.close();
688                        rs.close();
689                        disconnect();
690                        return hasAccess;
691                } catch (Exception ex) {
692                        disconnect();
693                        ex.printStackTrace();
694                        return hasAccess;
695                }
696        }
697
698        /**
699         * Remove a pending user from the pending access list once approved.
700         * 
701         */
702        public boolean removePendingUser(String username) {
703                try {
704                        connect();
705                        Statement stmt = con.createStatement();
706                        stmt
707                                        .execute("DELETE FROM " + PENDINGACCESSLIST
708                                                        + " WHERE USERNAME='"
709                                                        + username.trim().toLowerCase() + "'");
710                        stmt.close();
711                        disconnect();
712                        return true;
713                } catch (Exception ex) {
714                        disconnect();
715                        ex.printStackTrace();
716                        return false;
717                }
718        }
719
720        /**
721         * Delete a job entry
722         * 
723         */
724        public void deleteJob(String jobId) {
725                try {
726                        connect();
727                        Statement stmt = con.createStatement();
728                        stmt.execute("DELETE FROM " + LIDARJOBS + " WHERE jobid='" + jobId
729                                        + "'");
730                        stmt.execute("DELETE FROM " + JOBSTATUS + " WHERE jobid='" + jobId
731                                        + "'");
732                        stmt.execute("DELETE FROM " + JOBPROCESSINGS + " WHERE jobid='"
733                                        + jobId + "'");
734                        stmt.execute("DELETE FROM " + JOBCLASSIFICATIONS + " WHERE jobid='"
735                                        + jobId + "'");
736                        stmt.close();
737                        disconnect();
738                } catch (Exception ex) {
739                        try {
740                                PrintWriter pw = new PrintWriter(new FileWriter(
741                                                "/tmp/dbLog.txt", true));
742                                ex.printStackTrace(pw);
743                                pw.close();
744                        } catch (Exception e) {
745                                ex.printStackTrace();
746                        }
747                }
748        }
749
750        private void connect() throws Exception {
751                Class.forName(dbclassname).newInstance();
752                con = DriverManager.getConnection(dburl, username, password);
753        }
754
755        private void disconnect() {
756                try {
757                        con.close();
758                } catch (Exception ex) {
759                        con = null;
760                }
761        }
762}