001: /*
002: * SalomeTMF is a Test Management Framework
003: * Copyright (C) 2005 France Telecom R&D
004: *
005: * This library is free software; you can redistribute it and/or
006: * modify it under the terms of the GNU Lesser General Public
007: * License as published by the Free Software Foundation; either
008: * version 2 of the License, or (at your option) any later version.
009: *
010: * This library is distributed in the hope that it will be useful,
011: * but WITHOUT ANY WARRANTY; without even the implied warranty of
012: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
013: * Lesser General Public License for more details.
014: *
015: * You should have received a copy of the GNU Lesser General Public
016: * License along with this library; if not, write to the Free Software
017: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
018: *
019: * @author Marche Mikael
020: *
021: * Contact: mikael.marche@rd.francetelecom.com
022: */
023:
024: package org.objectweb.salome_tmf.databaseSQL;
025:
026: import java.sql.PreparedStatement;
027: import java.sql.ResultSet;
028: import java.util.Vector;
029:
030: import org.objectweb.salome_tmf.api.Api;
031: import org.objectweb.salome_tmf.api.ApiConstants;
032: import org.objectweb.salome_tmf.api.Util;
033: import org.objectweb.salome_tmf.api.data.GroupWrapper;
034: import org.objectweb.salome_tmf.api.data.UrlAttachementWrapper;
035: import org.objectweb.salome_tmf.api.data.UserWrapper;
036: import org.objectweb.salome_tmf.api.sql.ISQLGroup;
037:
038: public class SQLGroup implements ISQLGroup {
039:
040: /**
041: * Insert a group for the project idProject
042: * @param idProject
043: * @param name of the group
044: * @param description of the group
045: * @param perm of the group in the project
046: * @return the id of the group
047: * @exception
048: * no permission needed
049: */
050: public int insert(int idProject, String name, String description,
051: int perm) throws Exception {
052: int id = -1;
053: int transNumber = -1;
054: if (idProject < 1) {
055: throw new Exception("[SQLGroup->insert] Project have no id");
056: }
057: try {
058: transNumber = SQLEngine.beginTransaction(0,
059: ApiConstants.INSERT_GROUP);
060:
061: PreparedStatement prep = SQLEngine
062: .getSQLAddQuery("addGroup"); //ok
063: prep.setInt(1, idProject);
064: prep.setString(2, name);
065: prep.setString(3, description);
066: prep.setInt(4, perm);
067: SQLEngine.runAddQuery(prep);
068:
069: id = getID(idProject, name);
070:
071: SQLEngine.commitTrans(transNumber);
072: } catch (Exception e) {
073: Util.log("[SQLGroup->insert]" + e);
074: if (Api.isDEBUG()) {
075: e.printStackTrace();
076: }
077: SQLEngine.rollBackTrans(transNumber);
078: throw e;
079: }
080: return id;
081: }
082:
083: /**
084: * Insert an user idUser in the group idGroup
085: * @param
086: * @param
087: * @exception
088: * no permission needed
089: */
090: public void insertUser(int idGroup, int idUser) throws Exception {
091: int transNumber = -1;
092: if (idUser < 1) {
093: throw new Exception(
094: "[SQLGroup->insertUser] user have no id");
095: }
096: if (idGroup < 1) {
097: throw new Exception(
098: "[SQLGroup->insertUser] group have no id");
099: }
100: try {
101: transNumber = SQLEngine.beginTransaction(0,
102: ApiConstants.INSERT_USER_INTO_GROUP);
103:
104: PreparedStatement prep = SQLEngine
105: .getSQLAddQuery("addUserToGroup"); //ok
106: prep.setInt(1, idUser);
107: prep.setInt(2, idGroup);
108: SQLEngine.runAddQuery(prep);
109:
110: SQLEngine.commitTrans(transNumber);
111:
112: } catch (Exception e) {
113: Util.log("[SQLGroup->insertUser]" + e);
114: if (Api.isDEBUG()) {
115: e.printStackTrace();
116: }
117: SQLEngine.rollBackTrans(transNumber);
118: throw e;
119: }
120:
121: }
122:
123: /**
124: * delete the group idGroup and all reference about user in group
125: * @param idGroup
126: * @exception
127: * no permission needed
128: */
129: public void delete(int idGroup) throws Exception {
130: if (idGroup < 1) {
131: throw new Exception(
132: "[SQLGroup->delete] entry data are not valid");
133: }
134: int transNumber = -1;
135: try {
136: transNumber = SQLEngine.beginTransaction(0,
137: ApiConstants.DELETE_GROUP);
138:
139: deleteUserGroup(idGroup);
140:
141: PreparedStatement prep = SQLEngine
142: .getSQLDeleteQuery("deleteGroup"); //ok
143: prep.setInt(1, idGroup);
144: SQLEngine.runDeleteQuery(prep);
145:
146: SQLEngine.commitTrans(transNumber);
147: } catch (Exception e) {
148: Util.log("[SQLGroup->delete]" + e);
149: if (Api.isDEBUG()) {
150: e.printStackTrace();
151: }
152: SQLEngine.rollBackTrans(transNumber);
153: throw e;
154: }
155: }
156:
157: /**
158: * update the permission of the group idGroup by perm
159: * @param idGroup
160: * @param perm
161: * @exception
162: * no permission needed
163: */
164: public void updatePermission(int idGroup, int perm)
165: throws Exception {
166: if (idGroup < 1) {
167: throw new Exception(
168: "[SQLGroup->updatePermission] entry data are not valid");
169: }
170: int transNumber = -1;
171: try {
172: transNumber = SQLEngine.beginTransaction(0,
173: ApiConstants.UPDATE_PERMISSION);
174:
175: PreparedStatement prep = SQLEngine
176: .getSQLUpdateQuery("updateAllowGroupByID"); //ok
177: prep.setInt(1, perm);
178: prep.setInt(2, idGroup);
179: SQLEngine.runUpdateQuery(prep);
180:
181: SQLEngine.commitTrans(transNumber);
182: } catch (Exception e) {
183: Util.log("[SQLGroup->updatePermission]" + e);
184: if (Api.isDEBUG()) {
185: e.printStackTrace();
186: }
187: SQLEngine.rollBackTrans(transNumber);
188: throw e;
189: }
190: }
191:
192: /**
193: * update the name and the description of the group idGroup
194: * @param idGroup
195: * @param name
196: * @param description
197: * @exception
198: * no permission needed
199: */
200: public void updateGroup(int idGroup, String name, String description)
201: throws Exception {
202: if (idGroup < 1) {
203: throw new Exception(
204: "[SQLGroup->updateGroup] entry data are not valid");
205: }
206: int transNumber = -1;
207: try {
208: transNumber = SQLEngine.beginTransaction(0,
209: ApiConstants.UPDATE_GROUP);
210: PreparedStatement prep = SQLEngine
211: .getSQLUpdateQuery("updateGroupByID"); //ok
212: prep.setString(1, name);
213: prep.setString(2, description);
214: prep.setInt(3, idGroup);
215: SQLEngine.runUpdateQuery(prep);
216:
217: SQLEngine.commitTrans(transNumber);
218: } catch (Exception e) {
219: Util.log("[SQLGroup->updateGroup]" + e);
220: if (Api.isDEBUG()) {
221: e.printStackTrace();
222: }
223: SQLEngine.rollBackTrans(transNumber);
224: throw e;
225: }
226: }
227:
228: /**
229: * Update the description of idUser in the group idGroup
230: * @param idGroup
231: * @param idUser
232: * @param description
233: * @exception
234: * no permission needed
235: */
236: public void updateUserDescInGroup(int idGroup, int idUser,
237: String description) throws Exception {
238: if (idGroup < 1 || idUser < 1) {
239: throw new Exception(
240: "[SQLGroup->updateUserDescInGroup] entry data are not valid");
241: }
242: int transNumber = -1;
243: try {
244: transNumber = SQLEngine.beginTransaction(0,
245: ApiConstants.UPDATE_GROUP);
246:
247: PreparedStatement prep = SQLEngine
248: .getSQLUpdateQuery("updateUserDescInGroupe"); //ok
249: prep.setString(1, description);
250: prep.setInt(2, idUser);
251: prep.setInt(3, idGroup);
252: SQLEngine.runUpdateQuery(prep);
253:
254: SQLEngine.commitTrans(transNumber);
255:
256: } catch (Exception e) {
257: Util.log("[SQLGroup->updateUserDescInGroup]" + e);
258: if (Api.isDEBUG()) {
259: e.printStackTrace();
260: }
261: SQLEngine.rollBackTrans(transNumber);
262: throw e;
263: }
264: }
265:
266: void updatePermissionForGroup(int idProject, String groupName,
267: int permission) throws Exception {
268: if (idProject < 1) {
269: throw new Exception(
270: "[SQLGroup->updatePermissionForGroup] entry data are not valid");
271: }
272: int transNumber = -1;
273: try {
274: transNumber = SQLEngine.beginTransaction(0,
275: ApiConstants.UPDATE_PERMISSION);
276:
277: PreparedStatement prep = SQLEngine
278: .getSQLUpdateQuery("updateAllowGroup2"); //ok
279: prep.setInt(1, permission);
280: prep.setInt(2, idProject);
281: prep.setString(3, groupName);
282: SQLEngine.runUpdateQuery(prep);
283:
284: SQLEngine.commitTrans(transNumber);
285: } catch (Exception e) {
286: Util.log("[SQLGroup->updatePermissionForGroup]" + e);
287: if (Api.isDEBUG()) {
288: e.printStackTrace();
289: }
290: SQLEngine.rollBackTrans(transNumber);
291: throw e;
292: }
293: }
294:
295: /**
296: * Delete idUser in the group idGroup
297: * @param idGroup
298: * @param idUser
299: * @exception
300: * no permission needed
301: */
302: public void deleteUserInGroup(int idGroup, int idUser)
303: throws Exception {
304: if (idGroup < 1 || idUser < 1) {
305: throw new Exception(
306: "[SQLGroup->deleteUserInGroup] entry data are not valid");
307: }
308: int transNumber = -1;
309: try {
310: transNumber = SQLEngine.beginTransaction(0,
311: ApiConstants.DELETE_USER_FROM_GROUP);
312:
313: PreparedStatement prep = SQLEngine
314: .getSQLDeleteQuery("deleteUserFromGroup"); //ok
315: prep.setInt(1, idUser);
316: prep.setInt(2, idGroup);
317: SQLEngine.runDeleteQuery(prep);
318:
319: SQLEngine.commitTrans(transNumber);
320: } catch (Exception e) {
321: Util.log("[SQLGroup->deleteUserInGroup]" + e);
322: if (Api.isDEBUG()) {
323: e.printStackTrace();
324: }
325: SQLEngine.rollBackTrans(transNumber);
326: throw e;
327: }
328: }
329:
330: /**
331: * Delete all reference of group in user - group mapping
332: * @param idGroup
333: * @throws Exception
334: */
335: public void deleteUserGroup(int idGroup) throws Exception {
336: if (idGroup < 1) {
337: throw new Exception(
338: "[SQLGroup->deleteUserGroup] entry data are not valid");
339: }
340: int transNumber = -1;
341: try {
342: transNumber = SQLEngine.beginTransaction(0,
343: ApiConstants.DELETE_USER_FROM_GROUP);
344:
345: PreparedStatement prep = SQLEngine
346: .getSQLDeleteQuery("deleteGroupUsers"); //ok
347: prep.setInt(1, idGroup);
348: SQLEngine.runDeleteQuery(prep);
349:
350: SQLEngine.commitTrans(transNumber);
351: } catch (Exception e) {
352: Util.log("[SQLGroup->deleteUserGroup]" + e);
353: if (Api.isDEBUG()) {
354: e.printStackTrace();
355: }
356: SQLEngine.rollBackTrans(transNumber);
357: throw e;
358: }
359: }
360:
361: /**
362: * get the id of an group groupName in project idProject
363: * @param idProject
364: * @param groupName
365: * @exception
366: * no permission needed
367: */
368: public int getID(int idProject, String groupName) throws Exception {
369: if (idProject < 1) {
370: throw new Exception(
371: "[SQLGroup->getID] entry data are not valid");
372: }
373: int idGroup = -1;
374: PreparedStatement prep = SQLEngine
375: .getSQLSelectQuery("selectIdGroup"); //ok
376: prep.setInt(1, idProject);
377: prep.setString(2, groupName);
378: ResultSet DS = SQLEngine.runSelectQuery(prep);
379:
380: if (DS.next()) {
381: idGroup = DS.getInt("id_groupe");
382: }
383: return idGroup;
384: }
385:
386: /**
387: * Get an vector of GroupWrapper representing all groups in project idProject
388: * @param idProject
389: * @exception
390: * no permission needed
391: */
392: public GroupWrapper[] getGroupWrapperInProject(int idProject)
393: throws Exception {
394: if (idProject < 1) {
395: throw new Exception(
396: "[SQLGroup->getGroupWrapperInProject] entry data are not valid");
397: }
398: Vector result = new Vector();
399: PreparedStatement prep = SQLEngine
400: .getSQLSelectQuery("selectGroupInfo"); //ok
401: prep.setInt(1, idProject);
402: ResultSet stmtRes = SQLEngine.runSelectQuery(prep);
403:
404: while (stmtRes.next()) {
405: GroupWrapper pGroupWrapper = new GroupWrapper();
406: pGroupWrapper.setIdProject(idProject);
407: pGroupWrapper.setIdBDD(stmtRes.getInt("id_groupe"));
408: pGroupWrapper.setName(stmtRes.getString("nom_groupe"));
409: pGroupWrapper.setDescription(stmtRes
410: .getString("desc_groupe"));
411: pGroupWrapper.setPermission(stmtRes.getInt("permission"));
412: result.add(pGroupWrapper);
413: }
414: GroupWrapper[] gwArray = new GroupWrapper[result.size()];
415: for (int i = 0; i < result.size(); i++) {
416: gwArray[i] = (GroupWrapper) result.get(i);
417: }
418: return gwArray;
419: }
420:
421: /**
422: * Get an vector of GroupWrapper representing all groups where userLogin is
423: * @param idProject
424: * @param userLogin
425: * @exception
426: * no permission needed
427: */
428: public GroupWrapper[] getGroupsForUser(int idProject,
429: String userLogin) throws Exception {
430: if (idProject < 1) {
431: throw new Exception(
432: "[SQLGroup->getGroupsForUser] entry data are not valid");
433: }
434: Vector result = new Vector();
435: PreparedStatement prep = SQLEngine
436: .getSQLSelectQuery("selectGroupsForUser"); // ok
437: prep.setInt(1, idProject);
438: prep.setString(2, userLogin);
439: ResultSet stmtRes = SQLEngine.runSelectQuery(prep);
440:
441: while (stmtRes.next()) {
442: GroupWrapper pGroupWrapper = new GroupWrapper();
443: pGroupWrapper.setIdProject(idProject);
444: pGroupWrapper.setIdBDD(stmtRes.getInt("id_groupe"));
445: pGroupWrapper.setName(stmtRes.getString("nom_groupe"));
446: pGroupWrapper.setDescription(stmtRes
447: .getString("desc_groupe"));
448: pGroupWrapper.setPermission(stmtRes.getInt("permission"));
449: result.add(pGroupWrapper);
450: }
451: GroupWrapper[] gwArray = new GroupWrapper[result.size()];
452: for (int i = 0; i < result.size(); i++) {
453: gwArray[i] = (GroupWrapper) result.get(i);
454: }
455: return gwArray;
456: }
457:
458: /**
459: * Get an vector of UserWrapper representing all users in group idGroup
460: * @param idGroup
461: * @exception
462: * no permission needed
463: */
464: public UserWrapper[] getUserWrappersInGroup(int idGroup)
465: throws Exception {
466: if (idGroup < 1) {
467: throw new Exception(
468: "[SQLGroup->getUserWrappersInGroup] entry data are not valid");
469: }
470: Vector result = new Vector();
471: PreparedStatement prep = SQLEngine
472: .getSQLSelectQuery("selectUserGroupeInfo"); //ok
473: prep.setInt(1, idGroup);
474: ResultSet stmtRes = SQLEngine.runSelectQuery(prep);
475:
476: while (stmtRes.next()) {
477: UserWrapper pUserWrapper = new UserWrapper();
478: pUserWrapper.setIdBDD(stmtRes
479: .getInt("PERSONNE_id_personne"));
480: pUserWrapper.setLogin(stmtRes.getString("login_personne"));
481: pUserWrapper.setName(stmtRes.getString("nom_personne"));
482: pUserWrapper
483: .setPrenom(stmtRes.getString("prenom_personne"));
484: pUserWrapper.setDescription(stmtRes
485: .getString("desc_personne"));
486: pUserWrapper.setEmail(stmtRes.getString("email_personne"));
487: pUserWrapper.setTel(stmtRes.getString("tel_personne"));
488: pUserWrapper.setCreateDate(stmtRes
489: .getDate("date_creation_personne"));
490: pUserWrapper.setCreateTime(stmtRes.getTime(
491: "heure_creation_personne").getTime());
492: pUserWrapper.setPassword(stmtRes.getString("mot_de_passe"));
493: result.add(pUserWrapper);
494: }
495: UserWrapper[] uwArray = new UserWrapper[result.size()];
496: for (int i = 0; i < result.size(); i++) {
497: uwArray[i] = (UserWrapper) result.get(i);
498: }
499: return uwArray;
500: }
501: }
|