|
1 package com.quadcap.app.bugdb;
2
3
40
41 import java.io.ByteArrayInputStream  ;
42 import java.io.ByteArrayOutputStream ;
43 import java.io.InputStream ;
44 import java.io.PrintWriter ;
45
46 import java.util.Enumeration ;
47 import java.util.Hashtable ;
48 import java.util.Properties ;
49 import java.util.Vector ;
50
51
52 import java.sql.DriverManager ;
53 import java.sql.SQLException ;
54
55 import java.net.URLDecoder ;
56
57 import java.sql.Connection ;
58 import java.sql.PreparedStatement ;
59 import java.sql.ResultSet ;
60 import java.sql.ResultSetMetaData ;
61 import java.sql.Statement ;
62 import java.sql.SQLException ;
63
64 import javax.servlet.ServletException ;
65 import javax.servlet.http.HttpServletRequest ;
66 import javax.servlet.http.HttpSession ;
67 import javax.servlet.http.HttpSessionBindingEvent ;
68 import javax.servlet.http.HttpSessionBindingListener ;
69 import javax.servlet.http.HttpSessionContext ;
70
71
78 public class BugSession implements HttpSessionBindingListener {
79 Connection conn;
80
81
82 String me = "nobody";
83
84 String my_person_type = null;
85 boolean isAdmin = false;
86 boolean isManager = false;
87 boolean isDeveloper = false;
88 boolean isUser = false;
89
90 static Object lock = new Object ();
91
92 Vector debug_history = new Vector ();
93
94 PreparedStatement getComponentOwner;
95 PreparedStatement insertBugHist;
96 PreparedStatement insertPerson;
97 PreparedStatement getBugHist;
98 PreparedStatement getBug;
99 PreparedStatement getQuery;
100 PreparedStatement saveQuery;
101 PreparedStatement getUser;
102 PreparedStatement updateUser;
103 PreparedStatement deleteUser;
104 PreparedStatement getProject;
105 PreparedStatement newProject;
106 PreparedStatement deleteProject;
107 PreparedStatement updateProject;
108
109 final String driverName = "com.quadcap.jdbc.JdbcDriver";
110 final String dbUrl = "jdbc:qed:bugdb;create=true";
111 final String dbUser = "bugdb";
112 final String dbPass = "bugdb";
113
114
117 public BugSession() {
118 try {
121 Class.forName(driverName);
122 getConnection();
123 prepareStatements();
124 checkSchema();
125 } catch (Exception e) {
126 e.printStackTrace(System.out);
127 throw new RuntimeException (e.toString());
128 }
129 }
130
131
136 public Connection getConnection() throws SQLException {
137 if (conn == null) {
138 Properties props = new Properties ();
139 props.put("user", dbUser);
140 props.put("password", dbPass);
141 props.put("create", "true");
142 this.conn = DriverManager.getConnection(dbUrl, props);
143 }
145 return conn;
146 }
147
148
154 public void valueBound (HttpSessionBindingEvent event) {
155 HttpSession session = event.getSession();
156 session.setMaxInactiveInterval(6 * 3600); }
158
159
165 public void valueUnbound (HttpSessionBindingEvent event) {
166 logout(null);
167 try {
168 conn.close();
169 } catch (Exception e) {}
170 }
171
172
173
178 private final void prepareStatements() throws SQLException {
179 getComponentOwner = conn.prepareStatement(
180 "select owner from component where component = ?");
181
182 insertBugHist = conn.prepareStatement(
183 "insert into bug_history (bug_id, hist_pos, submitter," +
184 " abstract, description, comments, state, priority, type," +
185 " owner, component, modtime) values(?,?,?,?,?,?,?,?,?,?,?,?)");
186
187 insertPerson = conn.prepareStatement(
188 "insert into person(name,password,email,person_type) " +
189 "values(?,?,?,?,?)");
190
191 getBugHist = conn.prepareStatement("select hist_size from bug where" +
192 " bug_id = ? for update",
193 ResultSet.TYPE_SCROLL_SENSITIVE,
194 ResultSet.CONCUR_UPDATABLE);
195 getBug = conn.prepareStatement("select * from bug_history " +
196 "where bug_id = ? " +
197 "order by hist_pos desc");
198 getQuery = conn.prepareStatement("select query from queries " +
199 "where owner = ? and name = ?");
200 saveQuery = conn.prepareStatement("insert into queries values(?,?,?)");
201 getUser = conn.prepareStatement("select * from person where name=?");
202 updateUser = conn.prepareStatement(
203 "select * from person where name=? for update",
204 ResultSet.TYPE_SCROLL_SENSITIVE,
205 ResultSet.CONCUR_UPDATABLE);
206 deleteUser = conn.prepareStatement("delete from person where name=?");
207 getProject = conn.prepareStatement(
208 "select * from component where component=?");
209 newProject = conn.prepareStatement(
210 "insert into component values(?,?)");
211 updateProject = conn.prepareStatement(
212 "select * from component where component=? for update",
213 ResultSet.TYPE_SCROLL_SENSITIVE,
214 ResultSet.CONCUR_UPDATABLE);
215 deleteProject = conn.prepareStatement(
216 "delete from component where component=?");
217 }
218
219
225 private final void checkSchema() throws SQLException {
226 synchronized (lock) {
227 Statement s = conn.createStatement();
228 ResultSet rs = null;
229 try {
230 rs = s.executeQuery("select * from singleton");
231 } catch (SQLException e) {
232 Loader loader = new Loader();
233 loader.setConnection(conn);
234 ClassLoader c = this.getClass().getClassLoader();
235 InputStream is = c.getResourceAsStream(
236 "com/quadcap/app/bugdb/bugdb.sql");
237 loader.loadStream(is);
238 } finally {
239 if (rs != null) rs.close();
240 s.close();
241 }
242 }
243 }
244
245
250 public boolean isUser() { return isUser; }
251
252
257 public boolean isDeveloper() { return isDeveloper; }
258
259
264 public boolean isManager() { return isManager; }
265
266
271 public boolean isAdmin() { return isAdmin; }
272
273
274
281 public String getQuery(String name) throws ServletException {
282 try {
283 getQuery.clearParameters();
284 getQuery.setString(1, me);
285 getQuery.setString(2, name);
286 ResultSet rs = getQuery.executeQuery();
287 try {
288 if (rs.next()) {
289 return rs.getString(1);
290 } else {
291 return "";
292 }
293 } finally {
294 rs.close();
295 }
296 } catch (SQLException ex) {
297 throw new ServletException (ex.toString());
298 }
299 }
300
301
308 public void saveQuery(String name, String query) throws ServletException {
309 try {
310 saveQuery.clearParameters();
311 saveQuery.setString(1, name);
312 saveQuery.setString(2, query);
313 saveQuery.setString(3, me);
314 saveQuery.executeUpdate();
315 } catch (SQLException ex) {
316 throw new ServletException (ex.toString());
317 }
318 }
319
320
326 public boolean logout(Properties props) {
327 me = "nobody";
328 isAdmin = isManager = isDeveloper = isUser = false;
329 if (conn != null) {
330 try {
331 conn.close();
332 } catch (Throwable t) {}
333 conn = null;
334 }
335 return true;
336 }
337
338
349 public boolean login(Properties props) throws SQLException {
350 String user = getString(props, "username");
351 String pass = getString(props, "password");
352 getConnection();
353 Statement s = conn.createStatement();
354 try {
355 ResultSet rs = s.executeQuery(
356 "select * from person where name = '" + user +
357 "' and password = '" + pass + "'");
358 try {
359 if (rs.next()) {
360 me = user;
361 my_person_type = rs.getString("person_type");
362 isAdmin = my_person_type.equals("Administrator");
363 isManager = isAdmin || my_person_type.equals("Manager");
364 isDeveloper = isManager || my_person_type.equals("Developer");
365 isUser = isDeveloper || my_person_type.equals("User");
366 return true;
367 }
368 } finally {
369 rs.close();
370 }
371 } finally {
372 s.close();
373 }
374 return false;
375 }
376
377
383 int getNextId(String type) throws SQLException {
384 Statement s = conn.createStatement(
385 ResultSet.TYPE_SCROLL_SENSITIVE,
386 ResultSet.CONCUR_UPDATABLE);
387 try {
388 int ret = 0;
389 ResultSet rs = s.executeQuery(
390 "select ivalue from singleton where name = '" + type + "'");
391 try {
392 if (rs.next()) {
393 ret = rs.getInt(1);
394 rs.updateInt(1, ret + 1);
395 rs.updateRow();
396 }
397 } finally {
398 rs.close();
399 }
400 return ret;
401 } finally {
402 s.close();
403 }
404 }
405
406
414 int getBugHistSize(int bug_id) throws SQLException {
415 getBugHist.clearParameters();
416 getBugHist.setInt(1, bug_id);
417 ResultSet rs = getBugHist.executeQuery();
418 try {
419 if (rs.next()) {
420 int ret = rs.getInt(1) + 1;
421 rs.updateInt(1, ret);
422 rs.updateRow();
423 return ret;
424 } else {
425 throw new SQLException ("no bug: " + bug_id);
426 }
427 } finally {
428 rs.close();
429 }
430 }
431
432
451 public boolean addUser(Properties props)
452 throws SQLException , ServletException
453 {
454 String p1 = getString(props, "password");
455 String p2 = getString(props, "password2");
456 if (!p1.equals(p2)) {
457 throw new ServletException (
458 "Password doesn't match confirm password");
459 }
460 insertPerson.clearParameters();
461 insertPerson.setString(1, getString(props, "name"));
462 insertPerson.setString(2, getString(props, "password"));
463 insertPerson.setString(3, getString(props, "email"));
464
465 String person_type = "User";
466 if (isAdmin) {
467 String t = getString(props, "person_type");
468 if (t != null && t.length() > 0) person_type = t;
469 }
470 insertPerson.setString(4, person_type);
471
472 return 1 == insertPerson.executeUpdate();
473 }
474
475
492 public boolean updateUser(Properties p) throws SQLException {
493 updateUser.clearParameters();
494 updateUser.setString(1, p.getProperty("name"));
495 ResultSet rs = updateUser.executeQuery();
496 try {
497 if (rs.next()) {
498 if (!p.getProperty("password").equals("password")) {
499 rs.updateString("password", p.getProperty("password"));
500 }
501 rs.updateString("email", p.getProperty("email"));
502 rs.updateString("person_type", p.getProperty("person_type"));
503 rs.updateRow();
504 return true;
505 } else {
506 return false;
507 }
508 } finally {
509 rs.close();
510 }
511 }
512
513
518 public boolean deleteUser(Properties p) throws SQLException {
519 deleteUser.clearParameters();
520 deleteUser.setString(1, p.getProperty("name"));
521 return deleteUser.executeUpdate() == 1;
522 }
523
524
531 public String listOptions(String type)
532 throws ServletException
533 {
534 return listOptions(type, null);
535 }
536
537
546 public String listOptions(String type, String sel)
547 throws ServletException
548 {
549 return listOptions(type, type, sel);
550 }
551
552
562 public String listOptions(String table, String type, String sel)
563 throws ServletException
564 {
565 return listOptions(table, table, type, sel);
566 }
567
568
578 public String listOptions(String seltype, String table,
579 String type, String sel)
580 throws ServletException
581 {
582 try {
583 Statement s = conn.createStatement();
584 StringBuffer sb = new StringBuffer ("\n<select name=\"");
585 sb.append(seltype);
586 sb.append("\">");
587 try {
588 ResultSet rs = s.executeQuery("select " + type +
589 " from " + table);
590 try {
591 while (rs.next()) {
592 String t = rs.getString(1);
593 sb.append("\n <option value=\"");
594 sb.append(t);
595 sb.append('"');
596 if (sel != null && t.equals(sel)) {
597 sb.append(" selected");
598 }
599 sb.append(">");
600 sb.append(t);
601 sb.append("</option>");
602 }
603 } finally {
604 rs.close();
605 }
606 sb.append("</select>");
607 return sb.toString();
608 } finally {
609 s.close();
610 }
611 } catch (SQLException e) {
612 throw new ServletException (e.toString());
613 }
614 }
615
616
623 public String listUsers(String type, String sel) throws ServletException {
624 return listOptions(type, "person", "name", sel);
625 }
626
627
635 public String getComponentOwner(String component)
636 throws SQLException , ServletException
637 {
638 synchronized (getComponentOwner) {
639 getComponentOwner.clearParameters();
640 getComponentOwner.setString(1, component);
641 ResultSet rs = getComponentOwner.executeQuery();
642 try {
643 if (rs.next()) {
644 return rs.getString(1);
645 } else {
646 return "nobody";
647 }
648 } finally {
649 rs.close();
650 }
651 }
652 }
653
654
663 static int getInt(Properties props, String name) throws ServletException {
664 try {
665 return Integer.parseInt(props.getProperty(name));
666 } catch (Throwable e) {
667 throw new ServletException ("Missing or malformed parameter: " +
668 name);
669 }
670 }
671
672
680 static String getString(Properties props, String name) {
681 String s = props.getProperty(name);
682 if (s == null) return "";
683 return s;
684 }
685
686
695 public boolean addBug(Properties props) throws ServletException {
696 if (!isUser()) throw new ServletException ("not logged in");
697 try {
698 Statement s = conn.createStatement();
699 try {
700 int bug_id = getNextId("bug");
701 int ret = s.executeUpdate(
702 "insert into bug(bug_id, hist_size) values(" +
703 bug_id + ",0)");
704 if (ret != 1) {
705 throw new ServletException ("add bug failed");
706 }
707
708 String component = getString(props, "component");
709 String owner = getComponentOwner(component);
710
711 insertBugHist.clearParameters();
712 insertBugHist.setInt(1, bug_id); insertBugHist.setInt(2, 0); insertBugHist.setString(3, me); insertBugHist.setString(4, getString(props, "abstract"));
716 insertBugHist.setString(5, getString(props, "description"));
717 insertBugHist.setString(6, getString(props, "comments"));
718 insertBugHist.setString(7, "Active"); insertBugHist.setString(8, getString(props, "priority"));
720 insertBugHist.setString(9, getString(props, "type"));
721 insertBugHist.setString(10, owner); insertBugHist.setString(11, component); insertBugHist.setTimestamp(12, now()); ret = insertBugHist.executeUpdate();
725 if (ret != 1) {
726 throw new ServletException ("add bug history failed, ret = " + ret);
727 }
728 return true;
729 } finally {
730 s.close();
731 }
732 } catch (SQLException e) {
733 throw new ServletException (e.toString());
734 }
735 }
736
737
746 public boolean updateBug(Properties props) throws ServletException {
747 if (!isUser()) throw new ServletException ("not logged in");
748 try {
749 int bug_id = getInt(props, "bug_id");
750 int hist_pos = getBugHistSize(bug_id);
751
752 String component = getString(props, "component");
753 String owner = getComponentOwner(component);
754
755 insertBugHist.clearParameters();
756 insertBugHist.setInt(1, bug_id); insertBugHist.setInt(2, hist_pos); insertBugHist.setString(3, me); insertBugHist.setString(4, getString(props, "abstract"));
760 insertBugHist.setString(5, getString(props, "description"));
761 insertBugHist.setString(6, getString(props, "comments"));
762 insertBugHist.setString(7, getString(props, "state"));
763 insertBugHist.setString(8, getString(props, "priority"));
764 insertBugHist.setString(9, getString(props, "type"));
765 insertBugHist.setString(10, getString(props, "owner"));
766 insertBugHist.setString(11, getString(props, "component"));
767 insertBugHist.setTimestamp(12, now()); if (insertBugHist.executeUpdate() != 1) {
769 throw new ServletException ("update bug history failed");
770 }
771 return true;
772 } catch (SQLException e) {
773 throw new ServletException (e.toString());
774 }
775 }
776
777
782 static final java.sql.Timestamp now() {
783 return new java.sql.Timestamp (System.currentTimeMillis());
784 }
785
786
792 public final String urlDecode(String s) {
793 try {
794 if (s == null) return "";
795 return java.net.URLDecoder.decode(s);
796 } catch (Throwable e) {
797 return s;
798 }
799 }
800
801
813 public Vector searchBugs(String query, String ob) throws ServletException {
814 Vector v = new Vector ();
815 try {
816 Statement s = conn.createStatement();
817 if (query == null) query = "";
818 query = query.trim();
819 if (query.length() > 0) {
820 query = "and " + query;
821 }
822 if (ob != null) {
823 query = query + " order by " + ob;
824 }
825 StringBuffer sb = new StringBuffer ();
826 sb.append("select * from bug natural join bug_history where ");
827 sb.append("(hist_size = hist_pos) " + query);
828 ResultSet rs = s.executeQuery(sb.toString());
829 ResultSetMetaData rm = rs.getMetaData();
830 try {
831 while (rs.next()) {
832 Properties p = new Properties ();
833 for (int i = 1; i <= rm.getColumnCount(); i++) {
834 String name = rm.getColumnLabel(i);
835 Object obj = rs.getObject(i);
836 String val = (obj == null) ? "" : obj.toString();
837 p.put(name.toLowerCase(), val);
838 }
839 v.addElement(p);
840 }
841 } finally {
842 rs.close();
843 }
844 } catch (SQLException e) {
845 throw new ServletException (e.toString());
846 } catch (Throwable t) {
847 t.printStackTrace(System.out);
848 }
849 return v;
850 }
851
852
862 final Hashtable getProps(ResultSet rs) throws SQLException {
863 Hashtable p = new Hashtable ();
864 ResultSetMetaData rm = rs.getMetaData();
865 for (int i = 1; i <= rm.getColumnCount(); i++) {
866 String name = rm.getColumnLabel(i);
867 String val = String.valueOf(rs.getObject(i));
868 p.put(name.toLowerCase(), val);
869 }
870 return p;
871 }
872
873
881 public Vector getUsers() throws SQLException {
882 Vector v = new Vector ();
883 Statement s = conn.createStatement();
884 try {
885 ResultSet rs = s.executeQuery("select * from person");
886 try {
887 while (rs.next()) {
888 Hashtable t = getProps(rs);
889 t.remove("password");
890 v.addElement(t);
891 }
892 } finally {
893 rs.close();
894 }
895 } finally {
896 s.close();
897 }
898 return v;
899 }
900
901
910 public Vector getProjects() throws SQLException {
911 Vector v = new Vector ();
912 Statement s = conn.createStatement();
913 try {
914 ResultSet rs = s.executeQuery("select * from component");
915 try {
916 while (rs.next()) {
917 Hashtable t = getProps(rs);
918 v.addElement(t);
919 }
920 } finally {
921 rs.close();
922 }
923 } finally {
924 s.close();
925 }
926 return v;
927 }
928
929
936 public Hashtable getUser(String user) throws SQLException {
937 getUser.clearParameters();
938 getUser.setString(1, user);
939 ResultSet rs = getUser.executeQuery();
940 try {
941 if (rs.next()) {
942 Hashtable t = getProps(rs);
943 t.remove("password");
944 return t;
945 } else {
946 return null;
947 }
948 } finally {
949 rs.close();
950 }
951 }
952
953
960 public Hashtable getProject(String user) throws SQLException {
961 getProject.clearParameters();
962 getProject.setString(1, user);
963 ResultSet rs = getProject.executeQuery();
964 try {
965 if (rs.next()) {
966 Hashtable t = getProps(rs);
967 return t;
968 } else {
969 return null;
970 }
971 } finally {
972 rs.close();
973 }
974 }
975
976
987 public boolean newProject(Properties p) throws SQLException {
988 newProject.clearParameters();
989 newProject.setString(1, p.getProperty("component"));
990 newProject.setString(2, p.getProperty("owner"));
991 return 1 == newProject.executeUpdate();
992 }
993
994
1004 public boolean updateProject(Properties p) throws SQLException {
1005 updateProject.clearParameters();
1006 updateProject.setString(1, p.getProperty("component"));
1007 ResultSet rs = updateProject.executeQuery();
1008 try {
1009 if (rs.next()) {
1010 rs.updateString("owner", p.getProperty("owner"));
1011 rs.updateRow();
1012 return true;
1013 } else {
1014 return false;
1015 }
1016 } finally {
1017 rs.close();
1018 }
1019 }
1020
1021
1026 public boolean deleteProject(Properties p) throws SQLException {
1027 deleteProject.clearParameters();
1028 deleteProject.setString(1, p.getProperty("component"));
1029 return deleteProject.executeUpdate() == 1;
1030 }
1031
1032
1036 static Hashtable nonDeltas = new Hashtable ();
1037 static {
1038 nonDeltas.put("modtime", "");
1039 nonDeltas.put("comments", "");
1040 }
1041
1042
1052 Hashtable diffProps(Hashtable a, Hashtable b) {
1053 Hashtable t = new Hashtable ();
1054 Enumeration e = a.keys();
1055 while (e.hasMoreElements()) {
1056 String key = (String )e.nextElement();
1057 if (key.equals("history")) continue;
1058 if (key.equals("hist_pos")) continue;
1059 String aval = (String )a.get(key);
1060 String bval = (String )b.get(key);
1061 if (!aval.equals(bval)) {
1062 if (nonDeltas.get(key) != null) {
1063 t.put(key, bval);
1064 } else {
1065 t.put(key, "Changed from " + bval + " to " + aval);
1066 }
1067 }
1068 }
1069 return t;
1070 }
1071
1072
1084 public Hashtable getBug(String bug_id) throws ServletException {
1085 try {
1086 getBug.clearParameters();
1087 getBug.setInt(1, Integer.parseInt(bug_id));
1088 ResultSet rs = getBug.executeQuery();
1089 try {
1090 Hashtable ret = null;
1091 Vector hist = new Vector ();
1092 Hashtable last = null;
1093 String description = "";
1094 while (rs.next()) {
1095 Hashtable props = getProps(rs);
1096 description = rs.getString("Description");
1097 if (ret == null) {
1098 ret = props;
1099 ret.put("history", hist);
1100 } else {
1101 hist.addElement(diffProps(last, props));
1102 }
1103 last = props;
1104 }
1105 if (ret == null) ret = new Hashtable ();
1108 ret.put("description", description);
1109 return ret;
1110 } finally {
1111 rs.close();
1112 }
1113 } catch (SQLException e) {
1114 throw new ServletException (e.toString());
1115 }
1116 }
1117
1118
1125 public String htmlEncode(String s) {
1126 try {
1127 ByteArrayOutputStream bos = new ByteArrayOutputStream ();
1128 PrintWriter pw = new PrintWriter (bos);
1129 HtmlWriter w = new HtmlWriter(pw);
1130 for (int i = 0; i < s.length(); i++) {
1131 w.write(s.charAt(i));
1132 }
1133 w.flush();
1134 return bos.toString();
1135 } catch (Exception e) {
1136 return s;
1137 }
1138 }
1139
1140
1148 public void handleRequest(HttpServletRequest req)
1149 throws ServletException , SQLException
1150 {
1151 String action = req.getParameter("action");
1152 boolean commit = false;
1153 try {
1154 if (action != null) {
1155 Properties p = getRequestProperties(req);
1156 if (action.equals("login")) {
1157 commit = login(p);
1158 } else if (action.equals("logout")) {
1159 commit = logout(p);
1160 } else if (!isUser()) {
1161 throw new ServletException ("not logged in");
1162 } else if (action.equals("newbug")) {
1163 commit = addBug(p);
1164 } else if (action.equals("modify")) {
1165 commit = updateBug(p);
1166 } else if (action.equals("register")) {
1167 commit =addUser(p);
1168 } else if (action.equals("updateUser")) {
1169 commit = updateUser(p);
1170 } else if (action.equals("deleteUser")) {
1171 commit = deleteUser(p);
1172 } else if (action.equals("newUser")) {
1173 commit = addUser(p);
1174 } else if (action.equals("newProject")) {
1175 commit = newProject(p);
1176 } else if (action.equals("updateProject")) {
1177 commit = updateProject(p);
1178 } else if (action.equals("deleteProject")) {
1179 commit = deleteProject(p);
1180 } else if (action.equals("noop")) {
1181 } else {
1182 throw new ServletException ("Bad action: " + action);
1183 }
1184 } else if (!isUser()) {
1185 throw new ServletException ("not logged in");
1186 }
1187 } finally {
1188 }
1194 }
1195
1196
1202 public Properties getRequestProperties(HttpServletRequest req) {
1203 Properties p = new Properties ();
1204 Enumeration e = req.getParameterNames();
1205 while (e.hasMoreElements()) {
1206 String name = (String )e.nextElement();
1207 String val = req.getParameter(name);
1208 try {
1209 val = URLDecoder.decode(val);
1210 } catch (Throwable ex) {}
1211 p.put(name, val);
1212 }
1213 return p;
1214 }
1215}
1216 |
Popular Tags |
Java API By Example, From Geeks To Geeks.