View Javadoc
1   /*
2    * Copyright (c) 2002-2017, Mairie de Paris
3    * All rights reserved.
4    *
5    * Redistribution and use in source and binary forms, with or without
6    * modification, are permitted provided that the following conditions
7    * are met:
8    *
9    *  1. Redistributions of source code must retain the above copyright notice
10   *     and the following disclaimer.
11   *
12   *  2. Redistributions in binary form must reproduce the above copyright notice
13   *     and the following disclaimer in the documentation and/or other materials
14   *     provided with the distribution.
15   *
16   *  3. Neither the name of 'Mairie de Paris' nor 'Lutece' nor the names of its
17   *     contributors may be used to endorse or promote products derived from
18   *     this software without specific prior written permission.
19   *
20   * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
21   * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
22   * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
23   * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR CONTRIBUTORS BE
24   * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
25   * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
26   * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
27   * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
28   * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
29   * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
30   * POSSIBILITY OF SUCH DAMAGE.
31   *
32   * License 1.0
33   */
34  package fr.paris.lutece.plugins.adminsql.web;
35  
36  import java.util.ArrayList;
37  import java.util.HashMap;
38  import java.util.List;
39  import java.util.Map;
40  
41  import javax.servlet.http.HttpServletRequest;
42  
43  import fr.paris.lutece.plugins.adminsql.business.DataHome;
44  import fr.paris.lutece.plugins.adminsql.business.Field;
45  import fr.paris.lutece.plugins.adminsql.business.FieldHome;
46  import fr.paris.lutece.plugins.adminsql.business.FieldValues;
47  import fr.paris.lutece.plugins.adminsql.business.Table;
48  import fr.paris.lutece.plugins.adminsql.business.TableHome;
49  import fr.paris.lutece.plugins.adminsql.service.AdminSqlConnectionService;
50  import fr.paris.lutece.portal.service.database.AppConnectionService;
51  import fr.paris.lutece.portal.service.message.AdminMessage;
52  import fr.paris.lutece.portal.service.message.AdminMessageService;
53  import fr.paris.lutece.portal.service.template.AppTemplateService;
54  import fr.paris.lutece.portal.service.util.AppException;
55  import fr.paris.lutece.portal.web.admin.PluginAdminPageJspBean;
56  import fr.paris.lutece.util.ReferenceList;
57  import fr.paris.lutece.util.html.HtmlTemplate;
58  import fr.paris.lutece.util.url.UrlItem;
59  
60  
61  /**
62   * Management of the sql server
63   */
64  public class AdminSqlJspBean extends PluginAdminPageJspBean
65  {
66      //Rights
67      public static final String RIGHT_MANAGE_ADMIN_SQL = "ADMIN_SQL_MANAGEMENT";
68  
69      //Messages
70      private static final String MESSAGE_TITLE_INVALID_CHARACTERS = "portal.site.message.invalidCharactersInTitleName";
71      private static final String MESSAGE_ERROR_FIELD_NAME = "adminsql.create_field.errorFieldName";
72      private static final String MESSAGE_ERROR_FIELD_TYPE_LENGTH = "adminsql.create_field.errorFieldTypeLength";
73      private static final String MESSAGE_ERROR_TABLE_NAME = "adminsql.create_table.errorTableName";
74      private static final String MESSAGE_ERROR_NULL_AND_PRIMARY_KEY = "adminsql.create_field.errorNullAndPrimaryKey";
75  
76      //Properties
77      private static final String PROPERTY_PAGE_TITLE_POOL_LIST = "adminsql.manage_databases.pageTitle";
78      private static final String PROPERTY_PAGE_TITLE_TABLE_LIST = "adminsql.manage_tables.pageTitle";
79      private static final String PROPERTY_PAGE_TITLE_MANAGE_FIELDS_STRUCTURE = "adminsql.manage_fields_structure.pageTitle";
80      private static final String PROPERTY_PAGE_TITLE_DATA_TABLE = "adminsql.manage_data.pageTitle";
81      private static final String PROPERTY_PAGE_TITLE_CREATE_DATA = "adminsql.create_data.pageTitle";
82      private static final String PROPERTY_PAGE_TITLE_MODIFY_DATA = "adminsql.modify_data.pageTitle";
83      private static final String PROPERTY_PAGE_TITLE_MODIFY_FIELD_STRUCTURE = "adminsql.modify_field_structure.pageTitle";
84      private static final String PROPERTY_PAGE_TITLE_CREATE_FIELD = "adminsql.create_field.pageTitle";
85      private static final String PROPERTY_CONFIRM_DELETE_FIELD = "adminsql.delete_field.confirm_delete_field";
86      private static final String PROPERTY_CONFIRM_MODIFY_FIELD = "adminsql.modify_field.confirm_modify_field";
87      private static final String PROPERTY_PAGE_TITLE_CREATE_TABLE = "adminsql.create_table.pageTitle";
88      private static final String PROPERTY_CONFIRM_DELETE_TABLE = "adminsql.delete_table.confirm_delete_table";
89      private static final String PROPERTY_PAGE_TITLE_MODIFY_TABLE_NAME = "adminsql.modify_table_name.pageTitle";
90      private static final String PROPERTY_PAGE_TITLE_FONCTIONNALITIES_LIST = "adminsql.choose_fonctionnality.pageTitle";
91      private static final String PROPERTY_PAGE_TITLE_USER_REQUEST = "adminsql.user_request.pageTitle";
92      private static final String PROPERTY_CONFIRM_DELETE_DATA = "adminsql.delete_data.confirm_delete_data";
93  
94      //Parameters
95      private static final String PARAMETER_POOL_NAME = "pool_name";
96      private static final String PARAMETER_TABLE_NAME = "table_name";
97      private static final String PARAMETER_TABLE_NAME_TO_MODIFY = "tableNameToModify";
98      private static final String PARAMETER_TABLE_COMMENT = "table_comment";
99      private static final String PARAMETER_DATA_ID = "data_id";
100     private static final String PARAMETER_FIELD_NAME = "field_name";
101     private static final String PARAMETER_LABEL_TYPE_VALUE = "type_value_id";
102     private static final String PARAMETER_NULL_VALUE = "null_value";
103     private static final String PARAMETER_FIELD_LABEL_TYPE = "field_label_type";
104     private static final String PARAMETER_FIELD_LENGTH_TYPE = "field_length_type";
105     private static final String PARAMETER_FIELD_DEFAULT_VALUE = "field_default_value";
106     private static final String PARAMETER_FIELD_NULL = "field_null_value";
107     private static final String PARAMETER_FIELD_COMMENT = "field_comment_value";
108     private static final String PARAMETER_FIELD_KEY = "field_key_value";
109     private static final String PARAMETER_FIELD_TO_MODIFY = "fieldToModify";
110     private static final String PARAMETER_ID_OLD_FIELD_KEY = "idOldFieldKey";
111     private static final String PARAMETER_ID_FIELD_TYPE = "field_type_id";
112     private static final String PARAMETER_ID_FIELD_NULL = "field_null_id";
113     private static final String PARAMETER_ID_FIELD_KEY = "field_key_id";
114     private static final String PARAMETER_FIELDS_NUMBER_ADDING = "fields_number_adding";
115     private static final String PARAMETER_PLACE_OF_FIELD = "place_of_new_field";
116     private static final String PARAMETER_AFTER_THE_FIELD = "choose_fields";
117     private static final String PARAMETER_NUMBER_FIELDS_TABLE = "number_fields_table";
118     private static final String PARAMETER_DATA = "data";
119     private static final String PARAMETER_CONCAT_FIELDNAME_DATA = "concat_fielname_data";
120     private static final String PARAMETER_USER_REQUEST = "user_request";
121     private static final String PARAMETER_DB_TYPE_NAME = "db_type_name";
122     private static final String PARAMETER_IDENTIFIANT_USER = "identifiant";
123     private static final String PARAMETER_PASSWORD_USER = "password";
124     private static final String PARAMETER_ROW_TO_MODIFY = "row_to_modify";
125     private static final String PARAMETER_CHECKBOX_STRUCTURE = "checkbox_structure";
126     private static final String PARAMETER_CHECKBOX_DATA = "checkbox_data";
127     private static final String PARAMETER_CHECKBOX_DATABASE = "checkbox_database";
128     private static final String PARAMETER_ERROR_MESSAGE = "error_mess";
129 
130     //Markers
131     private static final String MARK_POOL_LIST = "pool_list";
132     private static final String MARK_TABLE_LIST = "table_list";
133     private static final String MARK_DATA_LIST = "data_list";
134     private static final String MARK_FIELDS_TABLE = "field_list";
135     private static final String MARK_DATA_LIST_TO_MODIFY = "data_to_modify_list";
136     private static final String MARK_PRIMARY_KEY_LIST = "primary_key_list";
137     private static final String MARK_POOL_NAME = "pool_name";
138     private static final String MARK_TABLE = "table";
139     private static final String MARK_FIELD = "field";
140     private static final String MARK_FIELD_TYPE_REFERENCE_LIST = "field_type_list";
141     private static final String MARK_FIELD_KEY_REFERENCE_LIST = "field_key_list";
142     private static final String MARK_FIELD_NULL_REFERENCE_LIST = "field_null_list";
143     private static final String MARK_FIELD_NAME_REFERENCE_LIST = "field_name_list";
144     private static final String MARK_NEW_FIELD = "place_of_new_field";
145     private static final String MARK_AFTER_THE_FIELD = "choose_fields";
146     private static final String MARK_NUMBER_FIELDS_TABLE = "number_fields_table";
147     private static final String MARK_CONCAT_PRIMARY_KEY_AND_VALUE = "concat_primary_key_and_value";
148     private static final String MARK_CONCAT_FIELDNAME_DATA = "concat_fielname_data";
149     private static final String MARK_USER_REQUEST = "user_request";
150     private static final String MARK_FIELDS_NAMES = "list_fields_names";
151     private static final String MARK_DATABASE_TYPE_LIST = "list_database_type";
152     private static final String MARK_DB_TYPE_NAME = "db_type_name";
153     private static final String MARK_SQL_ERROR = "sql_error";
154     private static final String MARK_DUMP_SCRIPT = "dump_script";
155     private static final String MARK_INPUT_CONCAT_FIELD_NAME_AND_DATA = "input_concat_field_name_data";
156     private static final String MARK_DUMP_COMMANDS = "dump_commands";
157     private static final String MARK_APPEXCEPTION_ERROR = "error_sql";
158 
159     //Error messages
160     private static final String MESSAGE_SQL_ERROR_IN_QUERY = "adminsql.message.sqlErrorInQuery";
161 
162     //Jsps
163     private static final String JSP_URL_MANAGE_FIELDS_STRUCTURES = "ManageFieldsStructure.jsp";
164     private static final String JSP_URL_MANAGE_TABLES = "ManageTables.jsp";
165     private static final String JSP_DO_DELETE_FIELD_STRUCTURE = "jsp/admin/plugins/adminsql/DoDeleteFieldStructure.jsp";
166     private static final String JSP_DO_DELETE_TABLE = "jsp/admin/plugins/adminsql/DoDeleteTable.jsp";
167     private static final String JSP_DO_MODIFY_FIELD_STRUCTURE = "jsp/admin/plugins/adminsql/DoMofifyFieldStructure.jsp";
168     private static final String JSP_URL_MANAGE_DATA_TABLE = "ManageData.jsp";
169     private static final String JSP_URL_MANAGE_DATA_TABLE_AFTER_MODIF = "ManageData.jsp";
170     private static final String JSP_DO_DELETE_DATA = "jsp/admin/plugins/adminsql/DoDeleteData.jsp";
171     private static final String JSP_CREATE_TABLE = "jsp/admin/plugins/adminsql/CreateTable.jsp";
172 
173     // Properties file definition
174     private static final String PROPERTY_DATABASE_TYPE_LIST = "adminsql.type.database.list";
175 
176     //Urls
177     private static final String URL_CREATION_FIELD = "jsp/admin/plugins/adminsql/CreateFieldStructure.jsp";
178     private static final String URL_MODIFY_FIELD = "jsp/admin/plugins/adminsql/ModifyFieldStructure.jsp";
179     private static final String URL_CREATE_DATA = "jsp/admin/plugins/adminsql/CreateData.jsp";
180     private static final String URL_MODIFY_DATA = "jsp/admin/plugins/adminsql/ModifyData.jsp";
181     private static final String URL_USER_REQUEST = "jsp/admin/plugins/adminsql/UserRequest.jsp";
182     private static final String URL_DO_USER_REQUEST_ERROR = "jsp/admin/plugins/adminsql/DoUserRequestError.jsp";
183 
184 
185     //Templates
186     private String TEMPLATE_MANAGE_POOL_SQL = "admin/plugins/adminsql/manage_databases.html";
187     private String TEMPLATE_MANAGE_TABLE_SQL = "admin/plugins/adminsql/manage_tables.html";
188     private String TEMPLATE_MANAGE_DATA_SQL = "admin/plugins/adminsql/manage_data.html";
189     private String TEMPLATE_CREATE_DATA = "admin/plugins/adminsql/create_data.html";
190     private String TEMPLATE_MANAGE_STRUCTURE_SQL = "admin/plugins/adminsql/manage_fields_structure.html";
191     private String TEMPLATE_MODIFY_DATA = "admin/plugins/adminsql/modify_data.html";
192     private String TEMPLATE_MODIFY_STRUCTURE = "admin/plugins/adminsql/modify_field_structure.html";
193     private String TEMPLATE_CREATE_STRUCTURE = "admin/plugins/adminsql/create_field.html";
194     private String TEMPLATE_CREATE_TABLE = "admin/plugins/adminsql/create_table.html";
195     private String TEMPLATE_MODIFY_TABLE_NAME = "admin/plugins/adminsql/modify_table_name.html";
196     private String TEMPLATE_CHOOSE_FONCTIONNALITIES_LIST = "admin/plugins/adminsql/fonctionnalities_list_on_database.html";
197     private String TEMPLATE_USER_REQUEST = "admin/plugins/adminsql/user_request.html";
198     private String TEMPLATE_RESULT_OF_USER_REQUEST = "admin/plugins/adminsql/result_of_user_request.html";
199     private String TEMPLATE_EXPORT_DATABASE = "admin/plugins/adminsql/export_database.html";
200     private String TEMPLATE_SQL_ERROR_PAGE = "admin/plugins/adminsql/sql_error_page.html";
201     private String TEMPLATE_PARAMETER_TO_EXPORT_DATABASE = "admin/plugins/adminsql/parameter_to_export_db.html";
202     private String TEMPLATE_DUMP_DATABASE = "admin/plugins/adminsql/dump_database.html";
203     private String TEMPLATE_USER_REQUEST_ERROR = "admin/plugins/adminsql/error_user_request.html";
204 
205     /**
206      * List of databases
207      * @param request HttpServletRequest
208      * @return the list of databases page
209      */
210     public String getManageDatabases( HttpServletRequest request )
211     {
212         setPageTitleProperty( PROPERTY_PAGE_TITLE_POOL_LIST );
213 
214         ReferenceList listPools = new ReferenceList(  );
215         AppConnectionService.getPoolList( listPools );
216 
217         HashMap model = new HashMap(  );
218         model.put( MARK_POOL_LIST, listPools );
219 
220         HtmlTemplate template = AppTemplateService.getTemplate( TEMPLATE_MANAGE_POOL_SQL, getLocale(  ), model );
221 
222         return getAdminPage( template.getHtml(  ) );
223     }
224 
225     /**
226      * List of fonctionnalities on the pool
227      * @param request HttpServletRequest
228      * @return list of fonctionnalities
229      */
230     public String getManageFonctionnalitiesList( HttpServletRequest request )
231     {
232         setPageTitleProperty( PROPERTY_PAGE_TITLE_FONCTIONNALITIES_LIST );
233 
234         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
235         HashMap model = new HashMap(  );
236         model.put( MARK_POOL_NAME, strPoolName );
237 
238         HtmlTemplate template = AppTemplateService.getTemplate( TEMPLATE_CHOOSE_FONCTIONNALITIES_LIST, getLocale(  ),
239                 model );
240 
241         return getAdminPage( template.getHtml(  ) );
242     }
243 
244     /**
245      * List all tables of a database
246      * @param request HttpServletRequest
247      * @return List of all tables of the databases
248      */
249     public String getManageTables( HttpServletRequest request )
250     {
251         setPageTitleProperty( PROPERTY_PAGE_TITLE_TABLE_LIST );
252 
253         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
254 
255         List<Table> listTables = TableHome.findAllByPool( strPoolName, getPlugin(  ) );
256 
257         Map<String, Object> model = new HashMap<String, Object>(  );
258         model.put( MARK_TABLE_LIST, listTables );
259         model.put( MARK_POOL_NAME, strPoolName );
260 
261         HtmlTemplate template = AppTemplateService.getTemplate( TEMPLATE_MANAGE_TABLE_SQL, getLocale(  ), model );
262 
263         return getAdminPage( template.getHtml(  ) );
264     }
265 
266     /**
267      * User can stamp his request
268      * @param request HttpServletRequest
269      * @return user request page
270      */
271     public String getUserRequest( HttpServletRequest request )
272     {
273         setPageTitleProperty( PROPERTY_PAGE_TITLE_USER_REQUEST );
274 
275         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
276 
277         Map<String, Object> model = new HashMap<String, Object>(  );
278         model.put( MARK_POOL_NAME, strPoolName );
279 
280         HtmlTemplate template = AppTemplateService.getTemplate( TEMPLATE_USER_REQUEST, getLocale(  ), model );
281 
282         return getAdminPage( template.getHtml(  ) );
283     }
284 
285     /**
286      * User can see the result of his request
287      * @param request HttpServletRequest
288      * @return result of the request
289      */
290     public String doUserRequest( HttpServletRequest request )
291     {
292         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
293         String strUserRequest = request.getParameter( PARAMETER_USER_REQUEST );
294         List<List<String>> listData = new ArrayList<List<String>>(  );
295         List<String> listFieldsNames = new ArrayList<String>(  );
296         strUserRequest = strUserRequest.trim(  );
297 
298         String strSqlRequest = strUserRequest;
299         strSqlRequest = strUserRequest.toLowerCase(  );
300 
301         if ( strSqlRequest.contains( "select" ) )
302         {
303             strSqlRequest = strSqlRequest.substring( 0, 6 );
304 
305             if ( strSqlRequest.contains( "select" ) )
306             {
307                 try
308                 {
309                     listData = DataHome.findAllInUserRequest( strPoolName, strUserRequest,
310                             AdminSqlConnectionService.getConnectionService( strPoolName ) );
311                     listFieldsNames = DataHome.findFieldsNames( strUserRequest,
312                             AdminSqlConnectionService.getConnectionService( strPoolName ), strPoolName );
313                 }
314                 catch ( AppException e )
315                 {
316                     String strErrorMessage = ( e.getCause(  ) != null ) ? e.getCause(  ).getMessage(  ) : "";
317                     Map<String, Object> model = new HashMap<String, Object>(  );
318                     model.put( MARK_POOL_NAME, strPoolName );
319                     model.put( MARK_APPEXCEPTION_ERROR, strErrorMessage );
320                     model.put( MARK_USER_REQUEST, strUserRequest );
321 
322                     HtmlTemplate template = AppTemplateService.getTemplate( TEMPLATE_USER_REQUEST_ERROR, getLocale(  ),
323                             model );
324 
325                     return getAdminPage( template.getHtml(  ) );
326                 }
327             }
328         }
329         else
330         {
331             try
332             {
333                 DataHome.createWithUserRequest( strUserRequest,
334                     AdminSqlConnectionService.getConnectionService( strPoolName ), strPoolName );
335             }
336             catch ( AppException e )
337             {
338                 String strErrorMessage = ( e.getCause(  ) != null ) ? e.getCause(  ).getMessage(  ) : "";
339                 Map<String, Object> model = new HashMap<String, Object>(  );
340                 model.put( MARK_POOL_NAME, strPoolName );
341                 model.put( MARK_APPEXCEPTION_ERROR, strErrorMessage );
342                 model.put( MARK_USER_REQUEST, strUserRequest );
343 
344                 HtmlTemplate template = AppTemplateService.getTemplate( TEMPLATE_USER_REQUEST_ERROR, getLocale(  ),
345                         model );
346 
347                 return getAdminPage( template.getHtml(  ) );
348             }
349         }
350 
351         if ( listData.equals( null ) )
352         {
353             UrlItem url = new UrlItem( JSP_URL_MANAGE_TABLES );
354             url.addParameter( PARAMETER_POOL_NAME, strPoolName );
355 
356             return url.getUrl(  );
357         }
358         else
359         {
360             HashMap<String, Object> model = new HashMap<String, Object>(  );
361             model.put( MARK_POOL_NAME, strPoolName );
362             model.put( MARK_USER_REQUEST, strUserRequest );
363             model.put( MARK_DATA_LIST, listData );
364             model.put( MARK_FIELDS_NAMES, listFieldsNames );
365 
366             HtmlTemplate template = AppTemplateService.getTemplate( TEMPLATE_RESULT_OF_USER_REQUEST, getLocale(  ),
367                     model );
368 
369             return getAdminPage( template.getHtml(  ) );
370         }
371     }
372 
373     public String getUserRequestError( HttpServletRequest request )
374     {
375         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
376         String strErrorMessage = request.getParameter( PARAMETER_ERROR_MESSAGE );
377 
378         UrlItem url = new UrlItem( URL_USER_REQUEST );
379         url.addParameter( PARAMETER_POOL_NAME, strPoolName );
380 
381         return AdminMessageService.getMessageUrl( request, MESSAGE_SQL_ERROR_IN_QUERY, strErrorMessage, url.getUrl(  ),
382             AdminMessage.TYPE_STOP );
383     }
384 
385     /**
386      * User can choose the options of the dump
387      * @param request HttpServletRequest
388      * @return the dump option page
389      */
390     public String getExportDatabase( HttpServletRequest request )
391     {
392         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
393         HashMap<String, Object> model = new HashMap<String, Object>(  );
394         model.put( MARK_POOL_NAME, strPoolName );
395 
396         HtmlTemplate template = AppTemplateService.getTemplate( TEMPLATE_PARAMETER_TO_EXPORT_DATABASE, getLocale(  ),
397                 model );
398 
399         return getAdminPage( template.getHtml(  ) );
400     }
401 
402     /**
403      * User can see the dump of a database on a page
404      * @param request HttpServletRequest
405      * @return dump database page
406      */
407     public String doExportDatabase( HttpServletRequest request )
408     {
409         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
410         String strCheckboxStructure = request.getParameter( PARAMETER_CHECKBOX_STRUCTURE );
411         String strCheckboxData = request.getParameter( PARAMETER_CHECKBOX_DATA );
412         String strCheckboxDatabase = request.getParameter( PARAMETER_CHECKBOX_DATABASE );
413         String strFieldNameList = "";
414         String strPKFieldName = "";
415         String strKey = "";
416         List<String> DumpCommandslist = new ArrayList(  );
417         List<String> listRows = new ArrayList(  );
418         StringBuffer stDumpCommands = new StringBuffer(  );
419         List<Table> tableList = new ArrayList<Table>(  );
420         List<Field> fieldList = new ArrayList<Field>(  );
421         List<List<String>> listData = new ArrayList<List<String>>(  );
422         tableList = TableHome.findAllByPool( strPoolName, getPlugin(  ) );
423         stDumpCommands.append( "--\n-- Base de donn�es : " + "`" + strPoolName + "`" + "\n--\n\n" );
424         stDumpCommands.append( "-- -------------------------------\n\n" );
425 
426         if ( ( strCheckboxDatabase != null ) && strCheckboxDatabase.equals( "on" ) )
427         {
428             stDumpCommands.append( "DROP DATABASE IF EXISTS " + strPoolName + ";\n" + "CREATE DATABASE " + strPoolName +
429                 ";\n" + "USE " + strPoolName + ";\n\n" );
430         }
431 
432         for ( Table table : tableList )
433         {
434             String strTableName = table.getTableName(  );
435             String strDataRow = "";
436             String strInserDataLine = "";
437             String strStructureLine = "";
438             String strFieldsStructuresLines = "";
439 
440             if ( ( strCheckboxStructure != null ) && strCheckboxStructure.equals( "on" ) )
441             {
442                 stDumpCommands.append( "--\n-- Structure de la table " + "`" + strTableName + "`" + "\n--\n\n" );
443                 stDumpCommands.append( "DROP TABLE IF EXISTS " + "`" + strTableName + "`;\n" );
444                 stDumpCommands.append( "CREATE TABLE " + "`" + strTableName + "`(\n" );
445                 fieldList = FieldHome.findAll( strPoolName, strTableName, getPlugin(  ) );
446 
447                 for ( Field field : fieldList )
448                 {
449                     String strFieldName = field.getFieldName(  );
450                     strFieldNameList += ( "`" + strFieldName + "`," );
451 
452                     String strType = field.getTypeValue(  );
453                     String strNull = field.getLabelNullValue(  );
454 
455                     if ( strNull.equals( "NO" ) )
456                     {
457                         strNull = "NOT NULL";
458                     }
459                     else
460                     {
461                         strNull = "";
462                     }
463 
464                     String strDefault = field.getDefaultValue(  );
465 
466                     if ( strDefault.equals( "" ) )
467                     {
468                         strDefault = "";
469                     }
470                     else
471                     {
472                         if ( field.getTypeValue(  ).equals( "text" ) )
473                         {
474                             strDefault = "";
475                         }
476                         else if ( field.getDefaultValue(  ).equals( "NULL" ) ||
477                                 field.getDefaultValue(  ).equals( "CURRENT_TIMESTAMP" ) )
478                         {
479                             strDefault = "default " + field.getDefaultValue(  );
480                         }
481                         else
482                         {
483                             strDefault = "default " + "'" + field.getDefaultValue(  ) + "'";
484                         }
485                     }
486 
487                     strKey = field.getLabelKeyValue(  );
488 
489                     if ( strKey.equals( "PRI" ) )
490                     {
491                         strPKFieldName += ( "`" + field.getFieldName(  ) + "`" + "," );
492                     }
493 
494                     strStructureLine = "`" + strFieldName + "` " + strType + " " + strNull + " " + strDefault + ",";
495                     strFieldsStructuresLines += ( strStructureLine + "\n" );
496                 }
497 
498                 if ( strPKFieldName != "" )
499                 {
500                     strPKFieldName = strPKFieldName.substring( 0, strPKFieldName.length(  ) - 1 );
501                     strFieldsStructuresLines += ( "PRIMARY KEY (" + strPKFieldName + ")\n);" );
502                 }
503                 else
504                 {
505                     strFieldsStructuresLines = strFieldsStructuresLines.substring( 0,
506                             strFieldsStructuresLines.length(  ) - 2 ) + ");";
507                 }
508 
509                 strPKFieldName = "";
510                 stDumpCommands.append( strFieldsStructuresLines + "\n\n" );
511                 strFieldNameList = "(" + strFieldNameList.substring( 0, strFieldNameList.length(  ) - 1 ) + ")";
512             }
513 
514             if ( ( strCheckboxData != null ) && strCheckboxData.equals( "on" ) )
515             {
516                 if ( !( ( strCheckboxStructure != null ) && strCheckboxStructure.equals( "on" ) ) )
517                 {
518                     fieldList = FieldHome.findAll( strPoolName, strTableName, getPlugin(  ) );
519 
520                     for ( Field field : fieldList )
521                     {
522                         String strFieldName = field.getFieldName(  );
523                         strFieldNameList += ( "`" + strFieldName + "`," );
524                     }
525 
526                     strFieldNameList = "(" + strFieldNameList.substring( 0, strFieldNameList.length(  ) - 1 ) + ")";
527                 }
528 
529                 listData = DataHome.findAll( strTableName, AdminSqlConnectionService.getConnectionService( strPoolName ) );
530 
531                 if ( listData.isEmpty(  ) )
532                 {
533                     stDumpCommands.append( "--\n-- Pas de donnees dans " + "`" + strTableName + "`" + "\n--\n\n" ); //
534                 }
535                 else
536                 {
537                     stDumpCommands.append( "--\n-- Contenu de la table " + "`" + strTableName + "`" + "\n--\n\n" );
538 
539                     for ( List<String> listRow : listData )
540                     {
541                         for ( String strData : listRow )
542                         {
543                             if ( strData.contains( "'" ) )
544                             {
545                                 strData = strData.replaceAll( "'", "''" );
546                             }
547 
548                             strDataRow += ( "'" + strData + "'" + "," );
549                         }
550 
551                         strInserDataLine = "(" + strDataRow.substring( 0, strDataRow.length(  ) - 1 ) + ");";
552                         stDumpCommands.append( "INSERT INTO " + "`" + strTableName + "`" + strFieldNameList +
553                             " VALUES " + strInserDataLine + "\n" );
554                         strDataRow = "";
555                     }
556 
557                     stDumpCommands.append( "\n" );
558                 }
559 
560                 strFieldNameList = "";
561             }
562         }
563 
564         HashMap model = new HashMap(  );
565         model.put( MARK_POOL_NAME, strPoolName );
566         model.put( MARK_DUMP_COMMANDS, stDumpCommands );
567 
568         HtmlTemplate template = AppTemplateService.getTemplate( TEMPLATE_DUMP_DATABASE, getLocale(  ), model );
569 
570         return getAdminPage( template.getHtml(  ) );
571     }
572 
573     /**
574      * Creation of a table in a database
575      * @param request HttpServletRequest
576      * @return table creation page
577      */
578     public String getCreateTable( HttpServletRequest request )
579     {
580         setPageTitleProperty( PROPERTY_PAGE_TITLE_CREATE_TABLE );
581 
582         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
583 
584         HashMap model = new HashMap(  );
585         model.put( MARK_POOL_NAME, strPoolName );
586         model.put( MARK_FIELD_TYPE_REFERENCE_LIST, FieldHome.findFieldType( strPoolName, getPlugin(  ) ) );
587         model.put( MARK_FIELD_KEY_REFERENCE_LIST, FieldHome.findFieldKey( strPoolName, getPlugin(  ) ) );
588         model.put( MARK_FIELD_NULL_REFERENCE_LIST, FieldHome.findFieldNull( strPoolName, getPlugin(  ) ) );
589 
590         HtmlTemplate template = AppTemplateService.getTemplate( TEMPLATE_CREATE_TABLE, getLocale(  ), model );
591 
592         return getAdminPage( template.getHtml(  ) );
593     }
594 
595     /**
596      * Process the creation of the table
597      * @param request HttpServletRequest
598      * @return the manage tables page
599      */
600     public String doCreateTable( HttpServletRequest request )
601     {
602         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
603         String strTableName = request.getParameter( PARAMETER_TABLE_NAME );
604         String strTableComment = request.getParameter( PARAMETER_TABLE_COMMENT );
605 
606         String strFieldName = request.getParameter( PARAMETER_FIELD_NAME );
607         String strFieldLengthType = request.getParameter( PARAMETER_FIELD_LENGTH_TYPE );
608         String strFieldDefault = request.getParameter( PARAMETER_FIELD_DEFAULT_VALUE );
609 
610         if ( strTableName.equals( null ) || strTableName.equals( "" ) )
611         {
612             return AdminMessageService.getMessageUrl( request, MESSAGE_ERROR_TABLE_NAME, AdminMessage.TYPE_STOP );
613         }
614 
615         if ( strFieldName.equals( null ) || strFieldName.equals( "" ) )
616         {
617             return AdminMessageService.getMessageUrl( request, MESSAGE_ERROR_FIELD_NAME, AdminMessage.TYPE_STOP );
618         }
619 
620         if ( ( strFieldLengthType == null ) || strFieldLengthType.equals( "" ) )
621         {
622             return AdminMessageService.getMessageUrl( request, MESSAGE_ERROR_FIELD_TYPE_LENGTH, AdminMessage.TYPE_STOP );
623         }
624 
625         //Combos
626         int nIdFieldType = Integer.parseInt( request.getParameter( PARAMETER_ID_FIELD_TYPE ) );
627         int nIdFieldNull = 1;
628         int nIdFieldKey = Integer.parseInt( request.getParameter( PARAMETER_ID_FIELD_KEY ) );
629 
630         Table table = new Table(  );
631         table.setTableName( strTableName );
632         table.setPrimaryKey( strFieldName );
633         table.setComment( strTableComment );
634 
635         Field field = new Field(  );
636         field.setFieldName( strFieldName );
637         field.setIdTypeValue( nIdFieldType );
638         field.setLengthTypeValue( strFieldLengthType );
639         field.setIdNullValue( nIdFieldNull );
640         field.setIdKeyValue( nIdFieldKey );
641         field.setDefaultValue( strFieldDefault );
642 
643         try
644         {
645             TableHome.create( strPoolName, table, field, getPlugin(  ) );
646         }
647         catch ( AppException e )
648         {
649             UrlItem url = new UrlItem( JSP_CREATE_TABLE );
650 
651             url.addParameter( PARAMETER_POOL_NAME, strPoolName );
652             url.addParameter( PARAMETER_TABLE_NAME, strTableName );
653 
654             Object[] messageArgs = { ( e.getCause(  ) != null ) ? e.getCause(  ).getMessage(  ) : "" };
655 
656             return AdminMessageService.getMessageUrl( request, MESSAGE_SQL_ERROR_IN_QUERY, messageArgs, url.getUrl(  ),
657                 AdminMessage.TYPE_STOP );
658         }
659 
660         UrlItem url = new UrlItem( JSP_URL_MANAGE_FIELDS_STRUCTURES );
661         url.addParameter( PARAMETER_POOL_NAME, strPoolName );
662         url.addParameter( PARAMETER_TABLE_NAME, strTableName );
663 
664         return url.getUrl(  );
665     }
666 
667     /**
668      * Modification of a table name of a database
669      * @param request HttpServletRequest
670      * @return the modification of the table name page
671      */
672     public String getModifyTableName( HttpServletRequest request )
673     {
674         setPageTitleProperty( PROPERTY_PAGE_TITLE_MODIFY_TABLE_NAME );
675 
676         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
677         String strTableName = request.getParameter( PARAMETER_TABLE_NAME );
678 
679         Table table = TableHome.findByPrimaryKey( strPoolName, strTableName, getPlugin(  ) );
680 
681         HashMap model = new HashMap(  );
682         model.put( MARK_POOL_NAME, strPoolName );
683         model.put( MARK_TABLE, table );
684 
685         HtmlTemplate template = AppTemplateService.getTemplate( TEMPLATE_MODIFY_TABLE_NAME, getLocale(  ), model );
686 
687         return getAdminPage( template.getHtml(  ) );
688     }
689 
690     /**
691      * Process the modification of the table name
692      * @param request HttpServletRequest
693      * @return the manage tables page
694      */
695     public String doModifyTableName( HttpServletRequest request )
696     {
697         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
698         String strTableNameToModify = request.getParameter( PARAMETER_TABLE_NAME_TO_MODIFY );
699         String strTableName = request.getParameter( PARAMETER_TABLE_NAME );
700 
701         if ( strTableName.equals( null ) || strTableName.equals( "" ) )
702         {
703             return AdminMessageService.getMessageUrl( request, MESSAGE_ERROR_TABLE_NAME, AdminMessage.TYPE_STOP );
704         }
705 
706         Table table = new Table(  );
707         table.setTableName( strTableName );
708 
709         TableHome.update( strTableNameToModify, table );
710 
711         UrlItem url = new UrlItem( JSP_URL_MANAGE_TABLES );
712         url.addParameter( PARAMETER_POOL_NAME, strPoolName );
713 
714         return url.getUrl(  );
715     }
716 
717     /**
718      * Confirmation of the table deleting
719      * @param request HttpServletRequest
720      * @return a confirm message to delete
721      */
722     public String getDeleteTable( HttpServletRequest request )
723     {
724         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
725         String strTableName = request.getParameter( PARAMETER_TABLE_NAME );
726         String strUrl = JSP_DO_DELETE_TABLE + "?" + PARAMETER_POOL_NAME + "=" + strPoolName + "&" +
727             PARAMETER_TABLE_NAME + "=" + strTableName;
728         String strMessageKey = PROPERTY_CONFIRM_DELETE_TABLE;
729         String strAdminMessageUrl = AdminMessageService.getMessageUrl( request, strMessageKey, strUrl, "",
730                 AdminMessage.TYPE_CONFIRMATION );
731 
732         return strAdminMessageUrl;
733     }
734 
735     /**
736      * Process the deleting of the table on a database
737      * @param request HttpServletRequest
738      * @return the management of the tables
739      */
740     public String doDeleteTable( HttpServletRequest request )
741     {
742         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
743         String strTableName = request.getParameter( PARAMETER_TABLE_NAME );
744 
745         TableHome.remove( strTableName, getPlugin(  ) );
746 
747         UrlItem url = new UrlItem( JSP_URL_MANAGE_TABLES );
748         url.addParameter( PARAMETER_POOL_NAME, strPoolName );
749 
750         return url.getUrl(  );
751     }
752 
753     /**
754      * Management of the fields structure of a table
755      * @param request HttpServletRequest
756      * @return the manage fields structure page
757      */
758     public String getManageFieldsStructure( HttpServletRequest request )
759     {
760         setPageTitleProperty( PROPERTY_PAGE_TITLE_MANAGE_FIELDS_STRUCTURE );
761 
762         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
763         String strTableName = request.getParameter( PARAMETER_TABLE_NAME );
764         String strPlaceOfField = request.getParameter( PARAMETER_PLACE_OF_FIELD );
765         String strAfterTheField = request.getParameter( PARAMETER_AFTER_THE_FIELD );
766 
767         Table table = TableHome.findByPrimaryKey( strPoolName, strTableName, getPlugin(  ) ); //TODO COnnection service
768         List<Field> listFields = FieldHome.findAll( strPoolName, strTableName, getPlugin(  ) );
769 
770         HashMap model = new HashMap(  );
771         model.put( MARK_FIELDS_TABLE, listFields );
772         model.put( MARK_POOL_NAME, strPoolName );
773         model.put( MARK_TABLE, table );
774         model.put( MARK_NEW_FIELD, strPlaceOfField );
775         model.put( MARK_AFTER_THE_FIELD, strAfterTheField );
776 
777         HtmlTemplate template = AppTemplateService.getTemplate( TEMPLATE_MANAGE_STRUCTURE_SQL, getLocale(  ), model );
778 
779         return getAdminPage( template.getHtml(  ) );
780     }
781 
782     /**
783      * Creation of a field in a table
784      * @param request HttpServletRequest
785      * @return the field creating page
786      */
787     public String getCreateFieldStructure( HttpServletRequest request )
788     {
789         setPageTitleProperty( PROPERTY_PAGE_TITLE_CREATE_FIELD );
790 
791         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
792         String strTableName = request.getParameter( PARAMETER_TABLE_NAME );
793         String strPlaceOfField = request.getParameter( PARAMETER_PLACE_OF_FIELD );
794         String strAfterTheField = request.getParameter( PARAMETER_AFTER_THE_FIELD );
795         Table table = TableHome.findByPrimaryKey( strPoolName, strTableName, getPlugin(  ) );
796         HashMap model = new HashMap(  );
797         model.put( MARK_POOL_NAME, strPoolName );
798         model.put( MARK_TABLE, table );
799         model.put( MARK_FIELD_TYPE_REFERENCE_LIST, FieldHome.findFieldType( strPoolName, getPlugin(  ) ) );
800         model.put( MARK_FIELD_KEY_REFERENCE_LIST, FieldHome.findFieldKey( strPoolName, getPlugin(  ) ) );
801         model.put( MARK_FIELD_NULL_REFERENCE_LIST, FieldHome.findFieldNull( strPoolName, getPlugin(  ) ) );
802         model.put( MARK_NEW_FIELD, strPlaceOfField );
803         model.put( MARK_AFTER_THE_FIELD, strAfterTheField );
804 
805         HtmlTemplate template = AppTemplateService.getTemplate( TEMPLATE_CREATE_STRUCTURE, getLocale(  ), model );
806 
807         return getAdminPage( template.getHtml(  ) );
808     }
809 
810     /**
811      * Process the creation of the field
812      * @param request HttpServletRequest
813      * @return the mana
814      */
815     public String doCreateFieldStructure( HttpServletRequest request )
816     {
817         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
818         String strTableName = request.getParameter( PARAMETER_TABLE_NAME );
819         String strFieldName = request.getParameter( PARAMETER_FIELD_NAME );
820         String strFieldLengthType = request.getParameter( PARAMETER_FIELD_LENGTH_TYPE );
821         String strFieldDefault = request.getParameter( PARAMETER_FIELD_DEFAULT_VALUE );
822         String strPlaceOfField = request.getParameter( PARAMETER_PLACE_OF_FIELD );
823         String strAfterTheField = request.getParameter( PARAMETER_AFTER_THE_FIELD );
824 
825         if ( strFieldName.equals( null ) || strFieldName.equals( "" ) )
826         {
827             return AdminMessageService.getMessageUrl( request, MESSAGE_ERROR_TABLE_NAME, AdminMessage.TYPE_STOP );
828         }
829 
830         //Combos
831         int nIdFieldType = Integer.parseInt( request.getParameter( PARAMETER_ID_FIELD_TYPE ) );
832         int nIdFieldNull = Integer.parseInt( request.getParameter( PARAMETER_ID_FIELD_NULL ) );
833         int nIdFieldKey = Integer.parseInt( request.getParameter( PARAMETER_ID_FIELD_KEY ) );
834 
835         if ( ( nIdFieldType == 3 ) || ( nIdFieldType == 4 ) || ( nIdFieldType == 12 ) || ( nIdFieldType == 13 ) ||
836                 ( nIdFieldType == 14 ) || ( nIdFieldType == 15 ) || ( nIdFieldType == 17 ) || ( nIdFieldType == 18 ) ||
837                 ( nIdFieldType == 19 ) || ( nIdFieldType == 20 ) || ( nIdFieldType == 21 ) || ( nIdFieldType == 22 ) ||
838                 ( nIdFieldType == 23 ) )
839         {
840             strFieldLengthType = "";
841         }
842         else if ( ( strFieldLengthType == null ) || strFieldLengthType.equals( "" ) )
843         {
844             return AdminMessageService.getMessageUrl( request, MESSAGE_ERROR_FIELD_TYPE_LENGTH, AdminMessage.TYPE_STOP );
845         }
846 
847         int nPlaceOfField = Integer.parseInt( strPlaceOfField );
848 
849         if ( ( nIdFieldNull == 2 ) && ( nIdFieldKey == 2 ) )
850         {
851             return AdminMessageService.getMessageUrl( request, MESSAGE_ERROR_NULL_AND_PRIMARY_KEY,
852                 AdminMessage.TYPE_STOP );
853         }
854 
855         Field field = new Field(  );
856         field.setFieldName( strFieldName );
857         field.setIdTypeValue( nIdFieldType );
858         field.setLengthTypeValue( strFieldLengthType );
859         field.setIdNullValue( nIdFieldNull );
860         field.setIdKeyValue( nIdFieldKey );
861         field.setDefaultValue( strFieldDefault );
862         field.setPlaceOfField( nPlaceOfField );
863         field.setAfterTheField( strAfterTheField );
864 
865         try
866         {
867             FieldHome.create( strPoolName, strTableName, field, getPlugin(  ) );
868         }
869 
870         catch ( AppException e )
871         {
872             UrlItem url = new UrlItem( URL_CREATION_FIELD );
873 
874             url.addParameter( PARAMETER_POOL_NAME, strPoolName );
875             url.addParameter( PARAMETER_TABLE_NAME, strTableName );
876             url.addParameter( PARAMETER_PLACE_OF_FIELD, strPlaceOfField );
877             url.addParameter( PARAMETER_AFTER_THE_FIELD, strAfterTheField );
878 
879             Object[] messageArgs = { ( e.getCause(  ) != null ) ? e.getCause(  ).getMessage(  ) : "" };
880 
881             return AdminMessageService.getMessageUrl( request, MESSAGE_SQL_ERROR_IN_QUERY, messageArgs, url.getUrl(  ),
882                 AdminMessage.TYPE_STOP );
883         }
884 
885         UrlItem url = new UrlItem( JSP_URL_MANAGE_FIELDS_STRUCTURES );
886         url.addParameter( PARAMETER_POOL_NAME, strPoolName );
887         url.addParameter( PARAMETER_TABLE_NAME, strTableName );
888 
889         return url.getUrl(  );
890     }
891 
892     /**
893      * Modification of a field structure
894      * @param request HttpServletRequest
895      * @return the field modification page
896      */
897     public String getModifyFieldStructure( HttpServletRequest request )
898     {
899         setPageTitleProperty( PROPERTY_PAGE_TITLE_MODIFY_FIELD_STRUCTURE );
900 
901         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
902         String strTableName = request.getParameter( PARAMETER_TABLE_NAME );
903         String strFieldName = request.getParameter( PARAMETER_FIELD_NAME );
904         Table table = TableHome.findByPrimaryKey( strPoolName, strTableName, getPlugin(  ) );
905         Field field = FieldHome.findByPrimaryKey( strPoolName, strTableName, strFieldName, getPlugin(  ) );
906 
907         HashMap model = new HashMap(  );
908         model.put( MARK_POOL_NAME, strPoolName );
909         model.put( MARK_TABLE, table );
910         model.put( MARK_FIELD, field );
911         model.put( MARK_FIELD_TYPE_REFERENCE_LIST, FieldHome.findFieldType( strPoolName, getPlugin(  ) ) );
912         model.put( MARK_FIELD_KEY_REFERENCE_LIST, FieldHome.findFieldKey( strPoolName, getPlugin(  ) ) );
913         model.put( MARK_FIELD_NULL_REFERENCE_LIST, FieldHome.findFieldNull( strPoolName, getPlugin(  ) ) );
914 
915         HtmlTemplate template = AppTemplateService.getTemplate( TEMPLATE_MODIFY_STRUCTURE, getLocale(  ), model );
916 
917         return getAdminPage( template.getHtml(  ) );
918     }
919 
920     /**
921      * Process the modification of the field structure
922      * @param request HttpServletRequest
923      * @return the manage fields structure page
924      */
925     public String doModifyFieldStructure( HttpServletRequest request )
926     {
927         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
928         String strTableName = request.getParameter( PARAMETER_TABLE_NAME );
929         String strFieldName = request.getParameter( PARAMETER_FIELD_NAME );
930         String strFieldLengthType = request.getParameter( PARAMETER_FIELD_LENGTH_TYPE );
931         String strFieldDefault = request.getParameter( PARAMETER_FIELD_DEFAULT_VALUE );
932         String strFieldNameToModify = request.getParameter( PARAMETER_FIELD_TO_MODIFY );
933         String strIdOldFieldKey = request.getParameter( PARAMETER_ID_OLD_FIELD_KEY );
934 
935         int nIdOldFieldKey = Integer.parseInt( strIdOldFieldKey );
936 
937         if ( strFieldName.equals( null ) || strFieldName.equals( "" ) )
938         {
939             return AdminMessageService.getMessageUrl( request, MESSAGE_ERROR_FIELD_NAME, AdminMessage.TYPE_STOP );
940         }
941 
942         //Combos
943         int nIdFieldType = Integer.parseInt( request.getParameter( PARAMETER_ID_FIELD_TYPE ) );
944         int nIdFieldNull = Integer.parseInt( request.getParameter( PARAMETER_ID_FIELD_NULL ) );
945         int nIdFieldKey = Integer.parseInt( request.getParameter( PARAMETER_ID_FIELD_KEY ) );
946 
947         if ( ( nIdFieldType == 3 ) || ( nIdFieldType == 4 ) || ( nIdFieldType == 12 ) || ( nIdFieldType == 13 ) ||
948                 ( nIdFieldType == 14 ) || ( nIdFieldType == 15 ) || ( nIdFieldType == 17 ) || ( nIdFieldType == 18 ) ||
949                 ( nIdFieldType == 19 ) || ( nIdFieldType == 20 ) || ( nIdFieldType == 21 ) || ( nIdFieldType == 22 ) ||
950                 ( nIdFieldType == 23 ) )
951         {
952             strFieldLengthType = "";
953         }
954         else if ( ( strFieldLengthType == null ) || strFieldLengthType.equals( "" ) )
955         {
956             return AdminMessageService.getMessageUrl( request, MESSAGE_ERROR_FIELD_TYPE_LENGTH, AdminMessage.TYPE_STOP );
957         }
958 
959         if ( strFieldDefault == null )
960         {
961             strFieldDefault = "";
962         }
963 
964         if ( ( nIdFieldNull == 2 ) && ( nIdFieldKey == 2 ) )
965         {
966             return AdminMessageService.getMessageUrl( request, MESSAGE_ERROR_NULL_AND_PRIMARY_KEY,
967                 AdminMessage.TYPE_STOP );
968         }
969 
970         Field field = new Field(  );
971 
972         field.setFieldName( strFieldName );
973         field.setIdTypeValue( nIdFieldType );
974         field.setLengthTypeValue( strFieldLengthType );
975         field.setIdKeyValue( nIdFieldKey );
976         field.setIdNullValue( nIdFieldNull );
977         field.setDefaultValue( strFieldDefault );
978 
979         try
980         {
981             FieldHome.update( strPoolName, strTableName, field, strFieldNameToModify, nIdOldFieldKey, getPlugin(  ) );
982         }
983 
984         catch ( AppException e )
985         {
986             UrlItem url = new UrlItem( URL_MODIFY_FIELD );
987 
988             url.addParameter( PARAMETER_POOL_NAME, strPoolName );
989             url.addParameter( PARAMETER_TABLE_NAME, strTableName );
990             url.addParameter( PARAMETER_FIELD_NAME, strFieldName );
991 
992             Object[] messageArgs = { ( e.getCause(  ) != null ) ? e.getCause(  ).getMessage(  ) : "" };
993 
994             return AdminMessageService.getMessageUrl( request, MESSAGE_SQL_ERROR_IN_QUERY, messageArgs, url.getUrl(  ),
995                 AdminMessage.TYPE_STOP );
996         }
997 
998         UrlItem url = new UrlItem( JSP_URL_MANAGE_FIELDS_STRUCTURES );
999         url.addParameter( PARAMETER_POOL_NAME, strPoolName );
1000         url.addParameter( PARAMETER_TABLE_NAME, strTableName );
1001 
1002         return url.getUrl(  );
1003     }
1004 
1005     /**
1006      * Delete a field structure of a database
1007      * @param request HttpServletRequest
1008      * @return the confirmation message to delete the field
1009      */
1010     public String getDeleteFieldStructure( HttpServletRequest request )
1011     {
1012         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
1013         String strTableName = request.getParameter( PARAMETER_TABLE_NAME );
1014         String strFieldName = request.getParameter( PARAMETER_FIELD_NAME );
1015         String strUrl = JSP_DO_DELETE_FIELD_STRUCTURE + "?" + PARAMETER_POOL_NAME + "=" + strPoolName + "&" +
1016             PARAMETER_TABLE_NAME + "=" + strTableName + "&" + PARAMETER_FIELD_NAME + "=" + strFieldName;
1017         String strMessageKey = PROPERTY_CONFIRM_DELETE_FIELD;
1018 
1019         String strAdminMessageUrl = AdminMessageService.getMessageUrl( request, strMessageKey, strUrl, "",
1020                 AdminMessage.TYPE_CONFIRMATION );
1021 
1022         return strAdminMessageUrl;
1023     }
1024 
1025     /**
1026      * Confirmation of a deling field structure
1027      * @param request HttpServletRequest
1028      * @return the manage fields structure page
1029      */
1030     public String doDeleteFieldStructure( HttpServletRequest request )
1031     {
1032         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
1033         String strTableName = request.getParameter( PARAMETER_TABLE_NAME );
1034         String strFieldName = request.getParameter( PARAMETER_FIELD_NAME );
1035         FieldHome.remove( strTableName, strFieldName, getPlugin(  ) );
1036 
1037         UrlItem url = new UrlItem( JSP_URL_MANAGE_FIELDS_STRUCTURES );
1038         url.addParameter( PARAMETER_POOL_NAME, strPoolName );
1039         url.addParameter( PARAMETER_TABLE_NAME, strTableName );
1040 
1041         return url.getUrl(  );
1042     }
1043 
1044     /**
1045      * List all data's table of a database
1046      * @return List of all data's table of a databases
1047      * @param request HttpServetRequest
1048      */
1049     public String getManageDataTable( HttpServletRequest request )
1050     {
1051         setPageTitleProperty( PROPERTY_PAGE_TITLE_DATA_TABLE );
1052 
1053         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
1054         String strTableName = request.getParameter( PARAMETER_TABLE_NAME );
1055         Table table = TableHome.findByPrimaryKey( strPoolName, strTableName, getPlugin(  ) );
1056         List<Field> listFields = FieldHome.findAll( strPoolName, strTableName, getPlugin(  ) );
1057         List<List<String>> listData = DataHome.findAll( strTableName,
1058                 AdminSqlConnectionService.getConnectionService( strPoolName ) );
1059         List<List<String>> listNewData = new ArrayList(  );
1060         List<String> listRows = new ArrayList(  );
1061         List<String> listInputs = new ArrayList(  );
1062 
1063         int nNumberLine = listData.size(  );
1064         String strData = "";
1065         int nNumberFields = listFields.size(  );
1066         String strConcatFieldNameAndData = "";
1067         int nNumberData = 0;
1068         int nNumberOfRow = 0;
1069         int n;
1070         String strInputConcatFieldNameAndData = "";
1071 
1072         for ( n = 0; n < nNumberLine; n++ )
1073         {
1074             strConcatFieldNameAndData = "";
1075             listRows = listData.get( n );
1076 
1077             for ( Field field : listFields )
1078             {
1079                 String strFieldName = field.getFieldName(  );
1080                 strData = listRows.get( listFields.indexOf( field ) );
1081                 strConcatFieldNameAndData = strConcatFieldNameAndData + "&" + strFieldName + "=" + strData;
1082                 strInputConcatFieldNameAndData = "<input name=" + field.getFieldName(  ) + " type=hidden value=" +
1083                     strData + ">";
1084             }
1085 
1086             listInputs.add( strInputConcatFieldNameAndData );
1087             listRows.add( strConcatFieldNameAndData );
1088             listNewData.add( listRows );
1089         }
1090 
1091         HashMap model = new HashMap(  );
1092         model.put( MARK_FIELDS_TABLE, listFields );
1093         model.put( MARK_CONCAT_FIELDNAME_DATA, strConcatFieldNameAndData );
1094         model.put( MARK_INPUT_CONCAT_FIELD_NAME_AND_DATA, strInputConcatFieldNameAndData );
1095         model.put( MARK_DATA_LIST, listData );
1096         model.put( MARK_POOL_NAME, strPoolName );
1097         model.put( MARK_TABLE, table );
1098 
1099         HtmlTemplate template = AppTemplateService.getTemplate( TEMPLATE_MANAGE_DATA_SQL, getLocale(  ), model );
1100 
1101         return getAdminPage( template.getHtml(  ) );
1102     }
1103 
1104     /**
1105      * Data creation page of a table
1106      * @param request HttpServetRequest
1107      * @return data creation page of a table
1108      */
1109     public String getCreateData( HttpServletRequest request )
1110     {
1111         setPageTitleProperty( PROPERTY_PAGE_TITLE_CREATE_DATA );
1112 
1113         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
1114         String strTableName = request.getParameter( PARAMETER_TABLE_NAME );
1115         Table table = TableHome.findByPrimaryKey( strPoolName, strTableName, getPlugin(  ) );
1116         List<Field> listFields = FieldHome.findAll( strPoolName, strTableName, getPlugin(  ) );
1117         HashMap model = new HashMap(  );
1118         model.put( MARK_FIELDS_TABLE, listFields );
1119         model.put( MARK_POOL_NAME, strPoolName );
1120         model.put( MARK_TABLE, table );
1121 
1122         HtmlTemplate template = AppTemplateService.getTemplate( TEMPLATE_CREATE_DATA, getLocale(  ), model );
1123 
1124         return getAdminPage( template.getHtml(  ) );
1125     }
1126 
1127     /**
1128      * Process the creation of data
1129      * @param request HttpServletRequest
1130      * @return the manage data page
1131      */
1132     public String doCreateData( HttpServletRequest request )
1133     {
1134         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
1135         String strTableName = request.getParameter( PARAMETER_TABLE_NAME );
1136         List<Field> listFields = FieldHome.findAll( strPoolName, strTableName, getPlugin(  ) );
1137         List<String> listFieldsNames = new ArrayList(  );
1138         List<String> listData = new ArrayList(  );
1139         int nIndexOfData=0;
1140         String strDefaultValue="";
1141         String strData = "";
1142 
1143         for ( Field field : listFields )
1144         {
1145             listFieldsNames.add( field.getFieldName(  ) );
1146             strData = request.getParameter( field.getFieldName(  ) );
1147 
1148             if ( strData != null && strData.length( )>0  )
1149                 
1150             {
1151                 nIndexOfData = listData.indexOf(strData) + 1;
1152                 Field verifFieldDefault = listFields.get(nIndexOfData);
1153                 strDefaultValue=verifFieldDefault.getDefaultValue();
1154                  if ( strDefaultValue != null && strDefaultValue.length( )>0)
1155                  {
1156                      strData = "";
1157                  }
1158                  else
1159                  {
1160                     strData = strDefaultValue;
1161                  }
1162             }
1163             if ( field.getIdTypeValue(  ) == 1 )
1164             {
1165                 strData = "\"" + strData + "\"";
1166             }
1167             listData.add( strData );
1168         }
1169         try
1170         {
1171             DataHome.create( strPoolName, strTableName, listData, listFieldsNames,
1172                 AdminSqlConnectionService.getConnectionService( strPoolName ) );
1173         }
1174         catch ( AppException e )
1175         {
1176             UrlItem url = new UrlItem( URL_CREATE_DATA );
1177 
1178             url.addParameter( PARAMETER_POOL_NAME, strPoolName );
1179             url.addParameter( PARAMETER_TABLE_NAME, strTableName );
1180 
1181             Object[] messageArgs = { ( e.getCause(  ) != null ) ? e.getCause(  ).getMessage(  ) : "" };
1182 
1183             return AdminMessageService.getMessageUrl( request, MESSAGE_SQL_ERROR_IN_QUERY, messageArgs, url.getUrl(  ),
1184                 AdminMessage.TYPE_STOP );
1185         }
1186 
1187         UrlItem url = new UrlItem( JSP_URL_MANAGE_DATA_TABLE );
1188         url.addParameter( PARAMETER_POOL_NAME, strPoolName );
1189         url.addParameter( PARAMETER_TABLE_NAME, strTableName );
1190 
1191         return url.getUrl(  );
1192     }
1193 
1194     /**
1195      * Modification of a data in a table
1196      * @param request HttpServletRequest
1197      * @return the modification data page
1198      */
1199     public String getModifyData( HttpServletRequest request )
1200     {
1201         setPageTitleProperty( PROPERTY_PAGE_TITLE_MODIFY_DATA );
1202 
1203         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
1204         String strTableName = request.getParameter( PARAMETER_TABLE_NAME );
1205         Table table = TableHome.findByPrimaryKey( strPoolName, strTableName, getPlugin(  ) );
1206         List<Field> listFields = FieldHome.findAll( strPoolName, strTableName, getPlugin(  ) );
1207         List<String> listData = new ArrayList(  );
1208         String strData = "";
1209 
1210         for ( Field field : listFields )
1211         {
1212             strData = request.getParameter( field.getFieldName(  ) );
1213             listData.add( strData );
1214         }
1215 
1216         HashMap model = new HashMap(  );
1217         model.put( MARK_DATA_LIST, listData );
1218         model.put( MARK_FIELDS_TABLE, listFields );
1219         model.put( MARK_POOL_NAME, strPoolName );
1220         model.put( MARK_TABLE, table );
1221 
1222         HtmlTemplate template = AppTemplateService.getTemplate( TEMPLATE_MODIFY_DATA, getLocale(  ), model );
1223 
1224         return getAdminPage( template.getHtml(  ) );
1225     }
1226 
1227     /**
1228      * Process the modification of a data in a table
1229      * @param request HttpServletRequest
1230      * @return the manage data page
1231      */
1232     public String doModifyData( HttpServletRequest request )
1233     {
1234         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
1235         String strTableName = request.getParameter( PARAMETER_TABLE_NAME );
1236 
1237         List<Field> listFields = FieldHome.findAll( strPoolName, strTableName, getPlugin(  ) );
1238         List<String> listConcatFieldNameAndData = new ArrayList(  );
1239         List<String> listConcatFieldNameAndOldData = new ArrayList(  );
1240         List<FieldValues> listFieldValues = new ArrayList<FieldValues>(  );
1241 
1242         for ( Field field : listFields )
1243         {
1244             String strFieldNameUpdate = field.getFieldName(  ) + "_update";
1245             String strDataUpdate = request.getParameter( strFieldNameUpdate );
1246             String strFieldName = field.getFieldName(  );
1247             String strData = request.getParameter( strFieldName );
1248 
1249             FieldValues fieldValues = new FieldValues(  );
1250             fieldValues.setFieldName( strFieldName );
1251             fieldValues.setOldValue( strData );
1252             fieldValues.setNewValue( strDataUpdate );
1253 
1254             listFieldValues.add( fieldValues );
1255         }
1256 
1257         try
1258         {
1259             DataHome.update( strPoolName, strTableName, listFieldValues,
1260                 AdminSqlConnectionService.getConnectionService( strPoolName ) );
1261         }
1262         catch ( AppException e )
1263         {
1264             UrlItem url = new UrlItem( JSP_URL_MANAGE_DATA_TABLE );
1265             url.addParameter( PARAMETER_POOL_NAME, strPoolName );
1266             url.addParameter( PARAMETER_TABLE_NAME, strTableName );
1267 
1268             Object[] messageArgs = { ( e.getCause(  ) != null ) ? e.getCause(  ).getMessage(  ) : "" };
1269 
1270             return AdminMessageService.getMessageUrl( request, MESSAGE_SQL_ERROR_IN_QUERY, messageArgs, url.getUrl(  ),
1271                 AdminMessage.TYPE_STOP );
1272         }
1273 
1274         UrlItem url = new UrlItem( JSP_URL_MANAGE_DATA_TABLE_AFTER_MODIF );
1275         url.addParameter( PARAMETER_POOL_NAME, strPoolName );
1276         url.addParameter( PARAMETER_TABLE_NAME, strTableName );
1277 
1278         return url.getUrl(  );
1279     }
1280 
1281     /**
1282      * Delete a data in a table
1283      * @param request HttpServletRequest
1284      * @return the confirmation message to deleting a row
1285      */
1286     public String getDeleteData( HttpServletRequest request )
1287     {
1288         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
1289         String strTableName = request.getParameter( PARAMETER_TABLE_NAME );
1290         List<Field> listFields = FieldHome.findAll( strPoolName, strTableName, getPlugin(  ) );
1291         String strData = "";
1292         String strConcatFieldNameAndData = "";
1293         String strFieldName = "";
1294 
1295         for ( Field field : listFields )
1296         {
1297             strFieldName = field.getFieldName(  );
1298             strData = request.getParameter( field.getFieldName(  ) );
1299             strConcatFieldNameAndData = strConcatFieldNameAndData + "&" + strFieldName + "=" + strData;
1300         }
1301 
1302         strConcatFieldNameAndData = strConcatFieldNameAndData.substring( 1 );
1303 
1304         String strUrl = JSP_DO_DELETE_DATA + "?" + PARAMETER_POOL_NAME + "=" + strPoolName + "&" +
1305             PARAMETER_TABLE_NAME + "=" + strTableName + "&" + strConcatFieldNameAndData;
1306         String strMessageKey = PROPERTY_CONFIRM_DELETE_DATA;
1307 
1308         String strAdminMessageUrl = AdminMessageService.getMessageUrl( request, strMessageKey, strUrl, "",
1309                 AdminMessage.TYPE_CONFIRMATION );
1310 
1311         return strAdminMessageUrl;
1312     }
1313 
1314     /**
1315      * Process the deleting of a data
1316      * @param request HttpServletRequest
1317      * @return th e manage data page
1318      */
1319     public String doDeleteData( HttpServletRequest request )
1320     {
1321         String strPoolName = request.getParameter( PARAMETER_POOL_NAME );
1322         String strTableName = request.getParameter( PARAMETER_TABLE_NAME );
1323         List<String> listFieldsNames = new ArrayList(  );
1324         List<String> listConcatFieldNameAndData = new ArrayList(  );
1325         List<Field> listFields = FieldHome.findAll( strPoolName, strTableName, getPlugin(  ) );
1326         String strData = "";
1327         String strConcatFieldNameAndData = "";
1328         String strFieldName = "";
1329 
1330         for ( Field field : listFields )
1331         {
1332             strFieldName = field.getFieldName(  );
1333             strData = request.getParameter( field.getFieldName(  ) );
1334             listConcatFieldNameAndData.add( strFieldName + "='" + strData + "'" );
1335         }
1336 
1337         DataHome.remove( strPoolName, strTableName, listConcatFieldNameAndData,
1338             AdminSqlConnectionService.getConnectionService( strPoolName ) );
1339 
1340         UrlItem url = new UrlItem( JSP_URL_MANAGE_DATA_TABLE );
1341         url.addParameter( PARAMETER_POOL_NAME, strPoolName );
1342         url.addParameter( PARAMETER_TABLE_NAME, strTableName );
1343 
1344         return url.getUrl(  );
1345     }
1346 }