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.business;
35
36 import fr.paris.lutece.plugins.adminsql.util.AdminSqlUtil;
37 import fr.paris.lutece.portal.service.database.PluginConnectionService;
38 import fr.paris.lutece.portal.service.plugin.Plugin;
39 import fr.paris.lutece.portal.service.plugin.PluginService;
40 import fr.paris.lutece.util.ReferenceItem;
41 import fr.paris.lutece.util.ReferenceList;
42 import fr.paris.lutece.util.sql.DAOUtil;
43
44
45 import java.util.ArrayList;
46 import java.util.List;
47
48
49
50
51
52 public class FieldDAO implements IFieldDAO
53 {
54
55 private static final String NULL_VALUE = "NULL";
56 private static final String NOT = "NOT ";
57 private static final String CHANGE = "CHANGE";
58 private static final String ADD = " ADD ";
59 private static final String ADD_KEY = " ,ADD ";
60 private static final String PRIMARY_KEY = "PRIMARY KEY";
61 private static final String FOREIGN_KEY = "FOREIGN KEY";
62 private static final String INDEX_KEY = "INDEX";
63 private static final String UNIQUE_KEY = "UNIQUE";
64 private static final String DROP = " DROP ";
65 private static final String DROP_KEY = " ,DROP ";
66 private static final String POOL_ADMINSQL = "adminsql";
67
68
69 private static final String SQL_QUERY_SHOW_TABLE_FIELDS_STRUCTURE = " SHOW COLUMNS FROM ";
70 private static final String SQL_QUERY_SELECT_A_FIELD_ON_TABLE = "DESC ";
71 private static final String SQL_QUERY_SELECTALL_FIELD_TYPE = " SELECT id_field_type, label_field_type FROM adminsql_field_type";
72 private static final String SQL_QUERY_SELECTALL_FIELD_KEY = "SELECT id_field_key, label_field_key FROM adminsql_field_key";
73 private static final String SQL_QUERY_SELECTALL_FIELD_NULL = "SELECT id_field_null, label_field_null FROM adminsql_field_null";
74 private static final String SQL_QUERY_FIND_FIELD_TYPE_ID = " SELECT id_field_type FROM adminsql_field_type WHERE label_field_type= ?";
75 private static final String SQL_QUERY_FIND_FIELD_TYPE_LABEL_BY_ID = " SELECT label_field_type FROM adminsql_field_type WHERE id_field_type= ?";
76 private static final String SQL_QUERY_FIND_FIELD_NULL_ID = " SELECT id_field_null FROM adminsql_field_null WHERE label_field_null= ?";
77 private static final String SQL_QUERY_FIND_FIELD_NULL_LABEL_BY_ID = " SELECT label_field_null FROM adminsql_field_null WHERE id_field_null= ?";
78 private static final String SQL_QUERY_FIND_FIELD_KEY_ID = " SELECT id_field_key FROM adminsql_field_key WHERE label_field_key= ?";
79 private static final String SQL_QUERY_FIND_FIELD_KEY_LABEL_BY_ID = " SELECT label_field_key FROM adminsql_field_key WHERE id_field_key= ?";
80 private static final String SQL_QUERY_UPDATE = " ALTER TABLE ";
81 private static final String SQL_QUERY_DELETE_FIELD = " ALTER TABLE ";
82
83
84
85
86
87
88
89
90
91 public Field load( String strPoolName, String strTableName, String strFieldName, Plugin plugin )
92 {
93 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_A_FIELD_ON_TABLE + strTableName + " " + strFieldName, plugin );
94 daoUtil.executeQuery( );
95
96 Field field = new Field( );
97 String[] tabString = { "", "" };
98
99 if ( daoUtil.next( ) )
100 {
101 field.setFieldName( daoUtil.getString( 1 ) );
102 field.setTypeValue( daoUtil.getString( 2 ) );
103
104 String strFieldType = field.getTypeValue( );
105
106 if ( strFieldType.equals( "date" ) || strFieldType.equals( "datetime" ) || strFieldType.equals( "time" ) ||
107 strFieldType.equals( "timestamp" ) || strFieldType.equals( "text" ) ||
108 strFieldType.equals( "tinytext" ) || strFieldType.equals( "longtext" ) ||
109 strFieldType.equals( "mediumtext" ) || strFieldType.equals( "blob" ) ||
110 strFieldType.equals( "tinyblob" ) || strFieldType.equals( "longblob" ) ||
111 strFieldType.equals( "mediumblob" ) )
112 {
113 field.setLabelTypeValue( strFieldType );
114 field.setLengthTypeValue( "" );
115 field.setIdTypeValue( findFieldTypeIdbyLabel( strFieldType, plugin, strPoolName ) );
116 }
117 else
118 {
119 tabString = AdminSqlUtil.getFieldDetails( field.getTypeValue( ) );
120 field.setLabelTypeValue( tabString[0] );
121 field.setLengthTypeValue( tabString[1] );
122 field.setIdTypeValue( findFieldTypeIdbyLabel( tabString[0], plugin, strPoolName ) );
123 }
124
125 field.setLabelNullValue( daoUtil.getString( 3 ) );
126
127 int nFieldIdNull = findFieldNullIdbyLabel( daoUtil.getString( 3 ), plugin, strPoolName );
128 field.setIdNullValue( nFieldIdNull );
129
130 String strLabelKeyValue = daoUtil.getString( 4 );
131
132 if ( strLabelKeyValue.equals( "PRI" ) )
133 {
134 strLabelKeyValue = "PRIMAIRE";
135 }
136
137 int nFieldIdKey = findFieldKeyIdbyLabel( strLabelKeyValue, plugin, strPoolName );
138 field.setIdKeyValue( nFieldIdKey );
139
140 if ( daoUtil.getString( 5 ) == null )
141 {
142 field.setDefaultValue( NULL_VALUE );
143 }
144 else
145 {
146 field.setDefaultValue( daoUtil.getString( 5 ) );
147 }
148 }
149
150 daoUtil.free( );
151
152 return field;
153 }
154
155
156
157
158
159
160
161
162
163
164 public void store( String strPoolName, String strTableName, Field field, String strFieldNameToModify,
165 int nIdOldFieldKey, Plugin plugin )
166 {
167 String strFieldName = field.getFieldName( );
168 field.setLabelTypeValue( findFieldTypeLabelbyId( field.getIdTypeValue( ), plugin, strPoolName ) );
169
170 String strFieldType = field.getLabelTypeValue( );
171 String strFieldLabelType = "";
172 String strLengthTypeValue = field.getLengthTypeValue( );
173
174 if ( strLengthTypeValue.equals( "" ) || ( strLengthTypeValue == null ) )
175 {
176 strFieldLabelType = strFieldType;
177 }
178 else if ( strFieldType.equals( "year" ) )
179 {
180 strFieldLabelType = strFieldType + "(" + 4 + ")";
181 }
182 else
183 {
184 strFieldLabelType = strFieldType + "(" + field.getLengthTypeValue( ) + ")";
185 }
186
187 field.setLabelNullValue( findFieldNullLabelbyId( field.getIdNullValue(), plugin, strPoolName ) );
188
189 String strLabelNullValue = field.getLabelNullValue( );
190 String strFieldLabelNullValue = "";
191
192 if ( strLabelNullValue.equals( "YES" ) )
193 {
194 strFieldLabelNullValue = NULL_VALUE;
195 }
196 else
197 {
198 strFieldLabelNullValue = NOT + NULL_VALUE;
199 }
200
201 int nIdKeyValue = field.getIdKeyValue( );
202 String strKey = "";
203 String strNewPrimaryKey = "";
204
205 if ( ( nIdOldFieldKey == 1 ) && ( nIdKeyValue == 2 ) )
206 {
207 strKey = ADD_KEY + PRIMARY_KEY;
208 strNewPrimaryKey = "(" + strFieldName + ")";
209 }
210 else if ( ( nIdOldFieldKey == 2 ) && ( nIdKeyValue == 1 ) )
211 {
212 strKey = DROP_KEY + PRIMARY_KEY;
213 }
214
215 String strFieldLabelDefaultValue = field.getDefaultValue( );
216 String strDEFAULT;
217 String strFieldLabelDefaultValueFinal;
218
219 if ( strFieldLabelDefaultValue.equals( "" ) )
220 {
221 strDEFAULT = "";
222 strFieldLabelDefaultValueFinal = "";
223 }
224 else
225 {
226 strDEFAULT = "DEFAULT";
227 strFieldLabelDefaultValueFinal = " '" + strFieldLabelDefaultValue + "'";
228 }
229
230 String strAlterQuery = SQL_QUERY_UPDATE + " " + strTableName + " " + CHANGE + " " + strFieldNameToModify + " " +
231 strFieldName + " " + strFieldLabelType + " " + " " + strFieldLabelNullValue + " " + strDEFAULT +
232 strFieldLabelDefaultValueFinal + " " + strKey + strNewPrimaryKey;
233 DAOUtil daoUtil = new DAOUtil( strAlterQuery, plugin );
234 daoUtil.executeUpdate( );
235 daoUtil.free( );
236 }
237
238
239
240
241
242
243
244
245 public void insert( String strPoolName, String strTableName, Field field, Plugin plugin )
246 {
247 String strFieldName = field.getFieldName( );
248 field.setLabelTypeValue( findFieldTypeLabelbyId( field.getIdTypeValue( ), plugin, strPoolName ) );
249
250 String strFieldLabelType = "";
251 String strFieldType = field.getLabelTypeValue( );
252 String strLengthTypeValue = field.getLengthTypeValue( );
253
254 if ( strLengthTypeValue.equals( "" ) || ( strLengthTypeValue == null ) )
255 {
256 strFieldLabelType = strFieldType;
257 }
258 else if ( strFieldType.equals( "YEAR" ) )
259 {
260 strFieldLabelType = strFieldType + "(" + 4 + ")";
261 }
262 else
263 {
264 strFieldLabelType = strFieldType + "(" + field.getLengthTypeValue( ) + ")";
265 }
266
267 field.setLabelNullValue( findFieldNullLabelbyId( field.getIdNullValue( ), plugin, strPoolName ) );
268
269 String strLabelNullValue = field.getLabelNullValue( );
270 String strFieldLabelNullValue = "";
271
272 if ( strLabelNullValue.equals( "YES" ) )
273 {
274 strFieldLabelNullValue = NULL_VALUE;
275 }
276 else
277 {
278 strFieldLabelNullValue = NOT + NULL_VALUE;
279 }
280
281 int nIdKeyValue = field.getIdKeyValue( );
282 field.setLabelKeyValue( findFieldKeyLabelbyId( nIdKeyValue, plugin, strPoolName ) );
283
284 String strFieldLabelKeyValue = field.getLabelKeyValue( );
285 String strKey;
286
287 if ( nIdKeyValue == 1 )
288 {
289 strKey = "";
290 }
291 else
292 {
293 strKey = PRIMARY_KEY;
294 }
295
296 String strFieldLabelDefaultValue = field.getDefaultValue( );
297 String strDEFAULT = "";
298
299 if ( strFieldLabelDefaultValue.equals( "" ) )
300 {
301 strDEFAULT = "";
302 }
303 else
304 {
305 strDEFAULT = "DEFAULT";
306 strFieldLabelDefaultValue = " '" + strFieldLabelDefaultValue + "' ";
307 }
308
309 int nAddFieldEndOfTable = field.getFieldEndOfTable( );
310 int nAddFieldBeginningOfTable = field.getFieldBeginningOfTable( );
311 int nAddFieldAfterAField = field.getFieldAfterAField( );
312 int nPlaceOfField = field.getPlaceOfField( );
313 String strFIRST = "";
314
315 if ( nPlaceOfField == 2 )
316 {
317 strFIRST = "FIRST";
318 }
319 else
320 {
321 strFIRST = "";
322 }
323
324 String strAfterTheField = "";
325 String strAFTER = "";
326
327 if ( nPlaceOfField == 3 )
328 {
329 strAFTER = "AFTER";
330 strAfterTheField = field.getAfterTheField( );
331 }
332 else
333 {
334 strAFTER = "";
335 }
336
337 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE + strTableName + " " + ADD + " " + strFieldName + " " +
338 strFieldLabelType + " " + strKey + " " + strFieldLabelNullValue + " " + strDEFAULT +
339 strFieldLabelDefaultValue + strFIRST + strAFTER + " " + strAfterTheField, plugin );
340 daoUtil.executeUpdate( );
341 daoUtil.free( );
342 }
343
344
345
346
347
348
349
350 public void delete( String strTableName, String strFieldName, Plugin plugin )
351 {
352 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_FIELD + strTableName + DROP + strFieldName );
353 daoUtil.executeUpdate( );
354 daoUtil.free( );
355 }
356
357
358
359
360
361
362
363
364 public List<Field> selectFieldList( String strPoolName, String strTableName, Plugin plugin )
365 {
366 List<Field> fieldList = new ArrayList<Field>( );
367 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SHOW_TABLE_FIELDS_STRUCTURE + strTableName, changePool( strPoolName ) );
368 daoUtil.executeQuery( );
369
370 while ( daoUtil.next( ) )
371 {
372 Field field = new Field( );
373 field.setFieldName( daoUtil.getString( 1 ) );
374 field.setTypeValue( daoUtil.getString( 2 ) );
375 field.setLabelNullValue( daoUtil.getString( 3 ) );
376
377 int nFieldIdNull = findFieldNullIdbyLabel( daoUtil.getString( 3 ), plugin, strPoolName );
378 field.setIdNullValue( nFieldIdNull );
379 field.setLabelKeyValue( daoUtil.getString( 4 ) );
380
381 int nFieldIdKey = findFieldKeyIdbyLabel( daoUtil.getString( 4 ), plugin, strPoolName );
382 field.setIdKeyValue( nFieldIdKey );
383
384 if ( daoUtil.getString( 5 ) == null )
385 {
386 field.setDefaultValue( NULL_VALUE );
387 }
388 else
389 {
390 field.setDefaultValue( daoUtil.getString( 5 ) );
391 }
392
393 fieldList.add( field );
394 }
395
396 daoUtil.free( );
397
398 return fieldList;
399 }
400
401
402
403
404
405
406
407 public ReferenceList selectFieldTypeList( String strPoolName, Plugin plugin )
408 {
409 ReferenceList fieldtypeList = new ReferenceList( );
410 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_FIELD_TYPE, getDefaultPool( plugin ) );
411
412 daoUtil.executeQuery( );
413
414 while ( daoUtil.next( ) )
415 {
416 ReferenceItem item = new ReferenceItem( );
417 item.setCode( daoUtil.getString( 1 ) );
418 item.setName( daoUtil.getString( 2 ) );
419 fieldtypeList.add( item );
420 }
421
422 daoUtil.free( );
423 plugin = changePool( strPoolName );
424
425 return fieldtypeList;
426 }
427
428
429
430
431
432
433
434 public ReferenceList selectFieldKeyList( String strPoolName, Plugin plugin )
435 {
436 ReferenceList fieldkeyList = new ReferenceList( );
437 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_FIELD_KEY, getDefaultPool( plugin ) );
438
439 daoUtil.executeQuery( );
440
441 while ( daoUtil.next( ) )
442 {
443 ReferenceItem item = new ReferenceItem( );
444 item.setCode( daoUtil.getString( 1 ) );
445 item.setName( daoUtil.getString( 2 ) );
446 fieldkeyList.add( item );
447 }
448
449 daoUtil.free( );
450 plugin = changePool( strPoolName );
451
452 return fieldkeyList;
453 }
454
455
456
457
458
459
460
461 public ReferenceList selectFieldNullList( String strPoolName, Plugin plugin )
462 {
463 ReferenceList fieldnullList = new ReferenceList( );
464 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_FIELD_NULL, getDefaultPool( plugin ) );
465
466 daoUtil.executeQuery( );
467
468 while ( daoUtil.next( ) )
469 {
470 ReferenceItem item = new ReferenceItem( );
471 item.setCode( daoUtil.getString( 1 ) );
472 item.setName( daoUtil.getString( 2 ) );
473 fieldnullList.add( item );
474 }
475
476 daoUtil.free( );
477
478 return fieldnullList;
479 }
480
481
482
483
484
485
486
487
488 public int findFieldTypeIdbyLabel( String strLabelType, Plugin plugin, String strPoolName )
489 {
490 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_FIELD_TYPE_ID, getDefaultPool( plugin ) );
491 daoUtil.setString( 1, strLabelType );
492 daoUtil.executeQuery( );
493
494 int nIdFieldType = 0;
495
496 if ( daoUtil.next( ) )
497 {
498 nIdFieldType = daoUtil.getInt( 1 );
499 }
500
501 daoUtil.free( );
502 plugin = changePool( strPoolName );
503
504 return nIdFieldType;
505 }
506
507
508
509
510
511
512
513
514 public String findFieldTypeLabelbyId( int nId, Plugin plugin, String strPoolName )
515 {
516 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_FIELD_TYPE_LABEL_BY_ID, getDefaultPool( plugin ) );
517 daoUtil.setInt( 1, nId );
518 daoUtil.executeQuery( );
519
520 String strFieldTypeLabel = "";
521
522 if ( daoUtil.next( ) )
523 {
524 strFieldTypeLabel = daoUtil.getString( 1 );
525 }
526
527 daoUtil.free( );
528 plugin = changePool( strPoolName );
529
530 return strFieldTypeLabel;
531 }
532
533
534
535
536
537
538
539
540 public int findFieldNullIdbyLabel( String strLabelNull, Plugin plugin, String strPoolName )
541 {
542 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_FIELD_NULL_ID, getDefaultPool( plugin ) );
543 daoUtil.setString( 1, strLabelNull );
544 daoUtil.executeQuery( );
545
546 int nIdFieldNull = 0;
547
548 if ( daoUtil.next( ) )
549 {
550 nIdFieldNull = daoUtil.getInt( 1 );
551 }
552
553 daoUtil.free( );
554 plugin = changePool( strPoolName );
555
556 return nIdFieldNull;
557 }
558
559
560
561
562
563
564
565
566 public String findFieldNullLabelbyId( int nId, Plugin plugin, String strPoolName )
567 {
568 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_FIELD_NULL_LABEL_BY_ID, getDefaultPool( plugin ) );
569 daoUtil.setInt( 1, nId );
570 daoUtil.executeQuery( );
571
572 String strFieldNullLabel = "";
573
574 if ( daoUtil.next( ) )
575 {
576 strFieldNullLabel = daoUtil.getString( 1 );
577 }
578
579 daoUtil.free( );
580 plugin = changePool( strPoolName );
581
582 return strFieldNullLabel;
583 }
584
585
586
587
588
589
590
591
592 public int findFieldKeyIdbyLabel( String strLabelKey, Plugin plugin, String strPoolName )
593 {
594 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_FIELD_KEY_ID, getDefaultPool( plugin ) );
595 daoUtil.setString( 1, strLabelKey );
596 daoUtil.executeQuery( );
597
598 int nIdFieldKey = 0;
599
600 if ( daoUtil.next( ) )
601 {
602 nIdFieldKey = daoUtil.getInt( 1 );
603 }
604
605 daoUtil.free( );
606 plugin = changePool( strPoolName );
607
608 return nIdFieldKey;
609 }
610
611
612
613
614
615
616
617
618 public String findFieldKeyLabelbyId( int nId, Plugin plugin, String strPoolName )
619 {
620 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_FIELD_KEY_LABEL_BY_ID, getDefaultPool( plugin ) );
621 daoUtil.setInt( 1, nId );
622 daoUtil.executeQuery( );
623
624 String strFieldKeyLabel = "";
625
626 if ( daoUtil.next( ) )
627 {
628 strFieldKeyLabel = daoUtil.getString( 1 );
629 }
630
631 daoUtil.free( );
632 plugin = changePool( strPoolName );
633
634 return strFieldKeyLabel;
635 }
636
637 private Plugin changePool( String strPoolName )
638 {
639 Plugin plugin = PluginService.getPlugin( "adminsql" );
640
641 PluginConnectionService connectionService = new PluginConnectionService( strPoolName );
642 connectionService.setPool( strPoolName );
643 plugin.setConnectionService( connectionService );
644
645 return plugin;
646 }
647
648 private Plugin getDefaultPool( Plugin plugin )
649 {
650 PluginConnectionService connectionService = new PluginConnectionService( plugin.getDbPoolName( ) );
651 connectionService.setPool( "adminsql" );
652 plugin.setConnectionService( connectionService );
653
654 return plugin;
655 }
656 }