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.Date;
027: import java.sql.PreparedStatement;
028: import java.sql.ResultSet;
029: import java.sql.Time;
030: import java.util.Vector;
031:
032: import org.objectweb.salome_tmf.api.Api;
033: import org.objectweb.salome_tmf.api.ApiConstants;
034: import org.objectweb.salome_tmf.api.Util;
035: import org.objectweb.salome_tmf.api.data.CampaignWrapper;
036: import org.objectweb.salome_tmf.api.data.ExecutionWrapper;
037: import org.objectweb.salome_tmf.api.data.FamilyWrapper;
038: import org.objectweb.salome_tmf.api.data.GroupWrapper;
039: import org.objectweb.salome_tmf.api.data.ProjectWrapper;
040: import org.objectweb.salome_tmf.api.data.SuiteWrapper;
041: import org.objectweb.salome_tmf.api.data.TestWrapper;
042: import org.objectweb.salome_tmf.api.data.UserWrapper;
043: import org.objectweb.salome_tmf.api.sql.ISQLPersonne;
044:
045: public class SQLPersonne implements ISQLPersonne {
046:
047: /**
048: * Insert a user to the datadase
049: * @param login
050: * @param name
051: * @param firstName
052: * @param desc
053: * @param email
054: * @param tel
055: * @param pwd
056: * @return
057: * @throws Exception
058: * no permission needed
059: */
060: public int insert(String login, String name, String firstName,
061: String desc, String email, String tel, String pwd,
062: boolean crypth) throws Exception {
063: int id = -1;
064: int transNumber = -1;
065: try {
066: transNumber = SQLEngine.beginTransaction(0,
067: ApiConstants.INSERT_USER);
068: PreparedStatement prep = SQLEngine
069: .getSQLAddQuery("addPerson"); //ok
070: Date dateActuelle = Util.getCurrentDate();
071: Time heureActuelle = Util.getCurrentTime();
072: prep.setString(1, login);
073: prep.setString(2, name);
074: prep.setString(3, firstName);
075: prep.setString(4, desc);
076: prep.setString(5, email);
077: prep.setString(6, tel);
078: prep.setDate(7, dateActuelle);
079: prep.setTime(8, heureActuelle);
080: if (crypth) {
081: pwd = org.objectweb.salome_tmf.api.MD5paswd
082: .getEncodedPassword(pwd);
083: }
084: prep.setString(9, pwd);
085: SQLEngine.runAddQuery(prep);
086: id = getID(login);
087:
088: SQLEngine.commitTrans(transNumber);
089: } catch (Exception e) {
090: Util.log("[SQLPersonne->insert]" + e);
091: if (Api.isDEBUG()) {
092: e.printStackTrace();
093: }
094: SQLEngine.rollBackTrans(transNumber);
095: throw e;
096: }
097: return id;
098: }
099:
100: /**
101: * Update information about user identified by idUser
102: * @param idUser
103: * @param newLogin
104: * @param newName
105: * @param newFirstName
106: * @param newDesc
107: * @param newEmail
108: * @param newTel
109: * @throws Exception
110: * no permission needed
111: */
112: public void update(int idUser, String newLogin, String newName,
113: String newFirstName, String newDesc, String newEmail,
114: String newTel) throws Exception {
115: int transNumber = -1;
116: if (idUser < 1) {
117: throw new Exception(
118: "[SQLPersonne->update] entry data are not valid");
119: }
120: try {
121: transNumber = SQLEngine.beginTransaction(0,
122: ApiConstants.UPDATE_USER);
123:
124: PreparedStatement prep = SQLEngine
125: .getSQLUpdateQuery("updatePerson"); //ok
126: prep.setString(1, newLogin);
127: prep.setString(2, newName);
128: prep.setString(3, newFirstName);
129: prep.setString(4, newDesc);
130: prep.setString(5, newEmail);
131: prep.setString(6, newTel);
132: prep.setInt(7, idUser);
133: SQLEngine.runUpdateQuery(prep);
134:
135: SQLEngine.commitTrans(transNumber);
136: } catch (Exception e) {
137: Util.log("[SQLPersonne->update]" + e);
138: if (Api.isDEBUG()) {
139: e.printStackTrace();
140: }
141: SQLEngine.rollBackTrans(transNumber);
142: throw e;
143: }
144: }
145:
146: /**
147: * Update the password for User userLogin with newPassword
148: * @param userLogin
149: * @param newPassword
150: * @return the new crypted password
151: * @throws Exception
152: * no permission needed
153: */
154: public String updatePassword(String userLogin, String newPassword,
155: boolean crypth) throws Exception {
156: int transNumber = -1;
157: try {
158: transNumber = SQLEngine.beginTransaction(0,
159: ApiConstants.UPDATE_PASSWORD);
160:
161: PreparedStatement prep = SQLEngine
162: .getSQLUpdateQuery("updatePassword"); //ok
163: if (crypth) {
164: newPassword = org.objectweb.salome_tmf.api.MD5paswd
165: .getEncodedPassword(newPassword);
166: }
167: prep.setString(1, newPassword);
168: prep.setString(2, userLogin);
169: SQLEngine.runUpdateQuery(prep);
170:
171: SQLEngine.commitTrans(transNumber);
172: } catch (Exception e) {
173: Util.log("[SQLPersonne->updatePassword]" + e);
174: if (Api.isDEBUG()) {
175: e.printStackTrace();
176: }
177: SQLEngine.rollBackTrans(transNumber);
178: throw e;
179: }
180: return newPassword;
181: }
182:
183: /**
184: * Delete an user in the database
185: * the clean all reference about user in project and group
186: * if user is the unique admin of an project, the project is deleted
187: * @param userLogin
188: * @throws Exception
189: * no permission needed
190: */
191: public void deleteByLogin(String userLogin) throws Exception {
192: deleteById(getID(userLogin));
193: /*int transNumber = -1;
194: try {
195: transNumber = SQLEngine.beginTransaction(ApiConstants.DELETE_USER);
196:
197: int id = getID(userLogin);
198: PreparedStatement prep = SQLEngine.getSQLDeleteQuery("deleteUserByLogin"); //ok
199: prep.setString(1, userLogin);
200: SQLEngine.runDeleteQuery(prep);
201:
202: cleanUserReference(id);
203:
204: SQLEngine.commitTrans(transNumber);
205: } catch (Exception e ){
206: Util.log("[SQLPersonne->delete]" + e);
207: if (Api.isDEBUG()){
208: e.printStackTrace();
209: }
210: SQLEngine.rollBackTrans(transNumber);
211: throw e;
212: }*/
213: }
214:
215: /**
216: * Delete an user in the database
217: * the clean all reference about user in project and group
218: * if user is the unique admin of an project, the project is deleted
219: * @param idUser
220: * @throws Exception
221: * no permission needed
222: */
223: public void deleteById(int idUser) throws Exception {
224: if (idUser < 1) {
225: throw new Exception(
226: "[SQLPersonne->delete] entry data are not valid");
227: }
228: int transNumber = -1;
229: try {
230: transNumber = SQLEngine.beginTransaction(0,
231: ApiConstants.DELETE_USER);
232:
233: cleanUserReference(idUser);
234:
235: PreparedStatement prep = SQLEngine
236: .getSQLDeleteQuery("deleteUserByID"); //ok
237: prep.setInt(1, idUser);
238: SQLEngine.runDeleteQuery(prep);
239:
240: /* Suppression des config */
241: try {
242: if (Api.getLockMeth() == 0) {
243: prep = SQLEngine
244: .getSQLCommonQuery("lockCONFIGWRITE");
245: SQLEngine.runSelectQuery(prep);
246: }
247: SQLObjectFactory.getInstanceOfISQLConfig()
248: .deleteAllUserConf(idUser);
249: } catch (Exception e1) {
250: if (Api.isDEBUG()) {
251: e1.printStackTrace();
252: }
253: /* WARNING */
254: }
255:
256: SQLEngine.commitTrans(transNumber);
257: } catch (Exception e) {
258: Util.log("[SQLPersonne->delete]" + e);
259: if (Api.isDEBUG()) {
260: e.printStackTrace();
261: }
262: SQLEngine.rollBackTrans(transNumber);
263: throw e;
264: }
265: }
266:
267: /**
268: * Delete user reference in project
269: * @param idUser
270: * @param projectName
271: * @throws Exception
272: */
273: public void deleteInProject(int idUser, String projectName)
274: throws Exception {
275: if (idUser < 1) {
276: throw new Exception(
277: "[SQLPersonne->cleanUserReference] entry data are not valid");
278: }
279: boolean projectDeleted = false;
280: int transNumber = -1;
281: try {
282: transNumber = SQLEngine.beginTransaction(0,
283: ApiConstants.DELETE_USER);
284: ProjectWrapper pProjectWrapper = (ProjectWrapper) SQLObjectFactory
285: .getInstanceOfISQLProject().getProject(projectName);
286: int idProjet = pProjectWrapper.getIdBDD();
287: if (idProjet < 1) {
288: throw new Exception(
289: "[SQLPersonne->cleanUserReference] entry data are not valid");
290: }
291: UserWrapper adminProjet = null;
292: UserWrapper user2del = getUserById(idUser);
293: UserWrapper[] projectAdmins = SQLObjectFactory
294: .getInstanceOfISQLProject().getAdminsOfProject(
295: projectName);
296: if (projectAdmins.length == 0) {
297: SQLObjectFactory.getInstanceOfISQLProject().delete(
298: idProjet, projectName);
299: projectDeleted = true;
300: } else {
301: projectDeleted = true;
302: int i = 0;
303: while (projectDeleted && i < projectAdmins.length) {
304: adminProjet = projectAdmins[i];
305: if (adminProjet.getIdBDD() != idUser) {
306: projectDeleted = false;
307: }
308: i++;
309: }
310: }
311:
312: GroupWrapper[] userGroupInProject = SQLObjectFactory
313: .getInstanceOfISQLGroup().getGroupsForUser(
314: idProjet, user2del.getLogin());
315: for (int j = 0; j < userGroupInProject.length; j++) {
316: GroupWrapper pGroupWrapper = userGroupInProject[j];
317: SQLObjectFactory.getInstanceOfISQLGroup()
318: .deleteUserInGroup(pGroupWrapper.getIdBDD(),
319: idUser);
320: }
321: if (!projectDeleted) {
322: FamilyWrapper[] projectFamilies = SQLObjectFactory
323: .getInstanceOfISQLProject().getFamily(idProjet);
324: for (int k = 0; k < projectFamilies.length; k++) {
325: FamilyWrapper pFamilyWrapper = projectFamilies[k];
326: int idFamily = pFamilyWrapper.getIdBDD();
327: SuiteWrapper[] projectSuites = SQLObjectFactory
328: .getInstanceOfISQLFamily().getTestList(
329: idFamily);
330: for (int l = 0; l < projectSuites.length; l++) {
331: SuiteWrapper pSuiteWrapper = projectSuites[l];
332: int idSuite = pSuiteWrapper.getIdBDD();
333: SQLObjectFactory.getInstanceOfISQLTest()
334: .updateUserRef(idSuite, idUser,
335: adminProjet.getIdBDD());
336: }
337: }
338: CampaignWrapper[] projectCamps = SQLObjectFactory
339: .getInstanceOfISQLProject().getPrjectCampaigns(
340: idProjet);
341: for (int k = 0; k < projectCamps.length; k++) {
342: CampaignWrapper pCampaignWrapper = projectCamps[k];
343: int idCamp = pCampaignWrapper.getIdBDD();
344: SQLObjectFactory.getInstanceOfISQLCampaign()
345: .updateUserRef(idCamp, idUser,
346: adminProjet.getIdBDD());
347: SQLObjectFactory.getInstanceOfISQLCampaign()
348: .updateTestAssignationRef(idCamp, idUser,
349: adminProjet.getIdBDD());
350: SQLObjectFactory.getInstanceOfISQLExecution()
351: .updateUserRef(idCamp, idUser,
352: adminProjet.getIdBDD());
353: ExecutionWrapper[] campagneExec = SQLObjectFactory
354: .getInstanceOfISQLCampaign().getExecutions(
355: idCamp);
356: for (int l = 0; l < campagneExec.length; l++) {
357: ExecutionWrapper pExecutionWrapper = campagneExec[l];
358: int idExec = pExecutionWrapper.getIdBDD();
359: SQLObjectFactory
360: .getInstanceOfISQLExecutionResult()
361: .updateUserRef(idExec, idUser,
362: adminProjet.getIdBDD());
363: }
364:
365: }
366: }
367: SQLEngine.commitTrans(transNumber);
368: } catch (Exception e) {
369: Util.log("[SQLPersonne->deleteInProject]" + e);
370: if (Api.isDEBUG()) {
371: e.printStackTrace();
372: }
373: SQLEngine.rollBackTrans(transNumber);
374: throw e;
375: }
376: }
377:
378: void cleanUserReference(int idUser) throws Exception {
379: if (idUser < 1) {
380: throw new Exception(
381: "[SQLPersonne->cleanUserReference] entry data are not valid");
382: }
383: ProjectWrapper[] allProject = SQLObjectFactory
384: .getInstanceOfISQLProject().getAllProjects();
385: boolean projectDeleted = false;
386: UserWrapper user2del = getUserById(idUser);
387: UserWrapper adminProjet = null;
388: for (int i = 0; i < allProject.length; i++) {
389: ProjectWrapper pProjectWrapper = allProject[i];
390: int idProjet = pProjectWrapper.getIdBDD();
391: String projName = pProjectWrapper.getName();
392: UserWrapper[] projectAdmins = SQLObjectFactory
393: .getInstanceOfISQLProject().getAdminsOfProject(
394: projName);
395: if (projectAdmins.length == 0) {
396: SQLObjectFactory.getInstanceOfISQLProject().delete(
397: idProjet, projName);
398: projectDeleted = true;
399: } else {
400: adminProjet = projectAdmins[0];
401: projectDeleted = false;
402: }
403: GroupWrapper[] userGroupInProject = SQLObjectFactory
404: .getInstanceOfISQLGroup().getGroupsForUser(
405: idProjet, user2del.getLogin());
406: for (int j = 0; j < userGroupInProject.length; j++) {
407: GroupWrapper pGroupWrapper = userGroupInProject[j];
408: SQLObjectFactory.getInstanceOfISQLGroup()
409: .deleteUserInGroup(pGroupWrapper.getIdBDD(),
410: idUser);
411: }
412: if (!projectDeleted) {
413: FamilyWrapper[] projectFamilies = SQLObjectFactory
414: .getInstanceOfISQLProject().getFamily(idProjet);
415: for (int k = 0; k < projectFamilies.length; k++) {
416: FamilyWrapper pFamilyWrapper = projectFamilies[k];
417: int idFamily = pFamilyWrapper.getIdBDD();
418: SuiteWrapper[] projectSuites = SQLObjectFactory
419: .getInstanceOfISQLFamily().getTestList(
420: idFamily);
421: for (int l = 0; l < projectSuites.length; l++) {
422: SuiteWrapper pSuiteWrapper = projectSuites[l];
423: int idSuite = pSuiteWrapper.getIdBDD();
424: SQLObjectFactory.getInstanceOfISQLTest()
425: .updateUserRef(idSuite, idUser,
426: adminProjet.getIdBDD());
427: }
428: }
429: CampaignWrapper[] projectCamps = SQLObjectFactory
430: .getInstanceOfISQLProject().getPrjectCampaigns(
431: idProjet);
432: for (int k = 0; k < projectCamps.length; k++) {
433: CampaignWrapper pCampaignWrapper = projectCamps[k];
434: int idCamp = pCampaignWrapper.getIdBDD();
435: SQLObjectFactory.getInstanceOfISQLCampaign()
436: .updateUserRef(idCamp, idUser,
437: adminProjet.getIdBDD());
438: SQLObjectFactory.getInstanceOfISQLCampaign()
439: .updateTestAssignationRef(idCamp, idUser,
440: adminProjet.getIdBDD());
441: SQLObjectFactory.getInstanceOfISQLExecution()
442: .updateUserRef(idCamp, idUser,
443: adminProjet.getIdBDD());
444: ExecutionWrapper[] campagneExec = SQLObjectFactory
445: .getInstanceOfISQLCampaign().getExecutions(
446: idCamp);
447: for (int l = 0; l < campagneExec.length; l++) {
448: ExecutionWrapper pExecutionWrapper = campagneExec[l];
449: int idExec = pExecutionWrapper.getIdBDD();
450: SQLObjectFactory
451: .getInstanceOfISQLExecutionResult()
452: .updateUserRef(idExec, idUser,
453: adminProjet.getIdBDD());
454: }
455: }
456: }
457: }
458: }
459:
460: /**
461: * Get the permission of an user in a projet idProject
462: * @param idProject
463: * @param userLogin
464: * @return
465: * @throws Exception
466: */
467: public int getPermissionOfUser(int idProject, String userLogin)
468: throws Exception {
469: if (idProject < 1) {
470: throw new Exception(
471: "[SQLPersonne->getPermissionOfUser] entry data are not valid");
472: }
473: GroupWrapper[] groupe_List = SQLObjectFactory
474: .getInstanceOfISQLGroup().getGroupsForUser(idProject,
475: userLogin);
476: int res = -1;
477: if ((groupe_List.length != 0)) {
478: res = 0;
479: for (int i = 0; i < groupe_List.length; i++) {
480: GroupWrapper pGroupWrapper = groupe_List[i];
481: res |= pGroupWrapper.getPermission();
482: }
483: }
484: return res;
485: }
486:
487: /**
488: * Get The Id of a person using login
489: * @param login
490: * @return
491: * @throws Exception
492: */
493: public int getID(String login) throws Exception {
494: int idPerson = -1;
495: PreparedStatement prep = SQLEngine
496: .getSQLSelectQuery("selectPersByLogin"); //ok
497: prep.setString(1, login);
498: ResultSet stmtRes = SQLEngine.runSelectQuery(prep);
499:
500: if (stmtRes.next()) {
501: idPerson = stmtRes.getInt("id_personne");
502: }
503: return idPerson;
504: }
505:
506: /**
507: * Get the login of the user identified by idUser
508: * @param idUser
509: * @return
510: * @throws Exception
511: */
512: public String getLogin(int idUser) throws Exception {
513: if (idUser < 1) {
514: throw new Exception(
515: "[SQLPersonne->getLogin] entry data are not valid");
516: }
517: return getUserById(idUser).getLogin();
518: }
519:
520: /**
521: * Get the name of the user identified by idUser
522: * @param idUser
523: * @return
524: * @throws Exception
525: */
526: public String getName(int idUser) throws Exception {
527: if (idUser < 1) {
528: throw new Exception(
529: "[SQLPersonne->getName] entry data are not valid");
530: }
531: return getUserById(idUser).getName();
532: }
533:
534: /**
535: * Get the last and the fist name of the user identified by idUser
536: * @param idUser
537: * @return
538: * @throws Exception
539: */
540: public String getTwoName(int idUser) throws Exception {
541: if (idUser < 1) {
542: throw new Exception(
543: "[SQLPersonne->getTwoName] entry data are not valid");
544: }
545: UserWrapper pUserWrapper = getUserById(idUser);
546: return pUserWrapper.getName() + " " + pUserWrapper.getPrenom();
547: }
548:
549: /**
550: * Get an UserWrapper of the user identified by login
551: * @param login
552: * @return
553: * @throws Exception
554: */
555: public UserWrapper getUserByLogin(String login) throws Exception {
556: UserWrapper pUserWrapper = null;
557: PreparedStatement prep = SQLEngine
558: .getSQLSelectQuery("selectPersByLogin"); //ok
559: prep.setString(1, login);
560: ResultSet stmtRes = SQLEngine.runSelectQuery(prep);
561: if (stmtRes.next()) {
562: pUserWrapper = new UserWrapper();
563: pUserWrapper.setIdBDD(stmtRes.getInt("id_personne"));
564: pUserWrapper.setLogin(stmtRes.getString("login_personne"));
565: pUserWrapper.setName(stmtRes.getString("nom_personne"));
566: pUserWrapper
567: .setPrenom(stmtRes.getString("prenom_personne"));
568: pUserWrapper.setDescription(stmtRes
569: .getString("desc_personne"));
570: pUserWrapper.setEmail(stmtRes.getString("email_personne"));
571: pUserWrapper.setTel(stmtRes.getString("tel_personne"));
572: try {
573: pUserWrapper.setCreateDate(stmtRes
574: .getDate("date_creation_personne"));
575: } catch (Exception e) {
576: pUserWrapper.setCreateDate(Util.getCurrentDate());
577: }
578: pUserWrapper.setCreateTime(stmtRes.getTime(
579: "heure_creation_personne").getTime());
580: pUserWrapper.setPassword(stmtRes.getString("mot_de_passe"));
581: }
582: return pUserWrapper;
583: }
584:
585: /**
586: * Get an UserWrapper of the user identified by idUser
587: * @param idUser
588: * @return
589: * @throws Exception
590: */
591: public UserWrapper getUserById(int idUser) throws Exception {
592: if (idUser < 1) {
593: throw new Exception(
594: "[SQLPersonne->getUser] entry data are not valid");
595: }
596: UserWrapper pUserWrapper = null;
597: PreparedStatement prep = SQLEngine
598: .getSQLSelectQuery("selectPersByID"); //ok
599: prep.setInt(1, idUser);
600: ResultSet stmtRes = SQLEngine.runSelectQuery(prep);
601: if (stmtRes.next()) {
602: pUserWrapper = new UserWrapper();
603: pUserWrapper.setIdBDD(stmtRes.getInt("id_personne"));
604: pUserWrapper.setLogin(stmtRes.getString("login_personne"));
605: pUserWrapper.setName(stmtRes.getString("nom_personne"));
606: pUserWrapper
607: .setPrenom(stmtRes.getString("prenom_personne"));
608: pUserWrapper.setDescription(stmtRes
609: .getString("desc_personne"));
610: pUserWrapper.setEmail(stmtRes.getString("email_personne"));
611: pUserWrapper.setTel(stmtRes.getString("tel_personne"));
612: try {
613: pUserWrapper.setCreateDate(stmtRes
614: .getDate("date_creation_personne"));
615: } catch (Exception e) {
616: pUserWrapper.setCreateDate(Util.getCurrentDate());
617: }
618: pUserWrapper.setCreateTime(stmtRes.getTime(
619: "heure_creation_personne").getTime());
620: pUserWrapper.setPassword(stmtRes.getString("mot_de_passe"));
621: }
622: return pUserWrapper;
623: }
624: }
|