1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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
63
64 public class AdminSqlJspBean extends PluginAdminPageJspBean
65 {
66
67 public static final String RIGHT_MANAGE_ADMIN_SQL = "ADMIN_SQL_MANAGEMENT";
68
69
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
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
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
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
160 private static final String MESSAGE_SQL_ERROR_IN_QUERY = "adminsql.message.sqlErrorInQuery";
161
162
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
174 private static final String PROPERTY_DATABASE_TYPE_LIST = "adminsql.type.database.list";
175
176
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
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
207
208
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
227
228
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
246
247
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
268
269
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
287
288
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
387
388
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
404
405
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
575
576
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
597
598
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
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
669
670
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
692
693
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
719
720
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
737
738
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
755
756
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( ) );
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
784
785
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
812
813
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
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
894
895
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
922
923
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
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
1007
1008
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
1027
1028
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
1046
1047
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
1106
1107
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
1129
1130
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
1196
1197
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
1229
1230
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
1283
1284
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
1316
1317
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 }