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.service.AdminSqlConnectionService;
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 TableDAO implements ITableDAO
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 PRIMARY_KEY = "PRIMARY KEY";
60 private static final String FOREIGN_KEY = "FOREIGN";
61 private static final String INDEX_KEY = "INDEX";
62 private static final String UNIQUE_KEY = "UNIQUE";
63 private static final String DROP = " DROP ";
64 private static final String RENAME_TO = " RENAME TO ";
65 private static final String WHERE = " WHERE ";
66 private static final String SPACE = " ";
67 private static final String USER = "-u";
68 private static final String PASSWORD = "-p";
69
70
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_SELECT = "SELECT ";
81 private static final String SQL_QUERY_SHOW_DATABASE_TABLES = "SHOW TABLES ";
82 private static final String SQL_QUERY_SHOW_DATABASE_TABLES_BY_POOL = "SHOW TABLES FROM ";
83 private static final String SQL_QUERY_SELECT_A_TABLE_ON_DATABASE = "SELECT TABLE_NAME, COLUMN_NAME, COLUMN_KEY FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ? ";
84 private static final String SQL_QUERY_CREATE_TABLE = " CREATE TABLE ";
85 private static final String SQL_QUERY_DELETE_TABLE = " DROP TABLE ";
86 private static final String SQL_QUERY_MODIFY_TABLE_NAME = " ALTER TABLE ";
87 private static final String SQL_QUERY_SELECT_A_TABLE_ON_DATABASE_1 = "DESC ";
88
89
90
91
92
93
94
95
96 public Table load( String strPoolName, String strTableName, Plugin plugin )
97 {
98 Table table = new Table( );
99 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_A_TABLE_ON_DATABASE_1 + strTableName, plugin );
100 daoUtil.executeQuery( );
101
102 if ( daoUtil.next( ) )
103 {
104 table.setTableName( strTableName );
105 table.setFieldName( daoUtil.getString( 2 ) );
106 table.setFieldNull( daoUtil.getString( 3 ) );
107 table.setPrimaryKey( daoUtil.getString( 4 ) );
108 }
109
110 daoUtil.free( );
111
112 return table;
113 }
114
115
116
117
118
119
120
121
122 public void insert( String strPoolName, Table table, Field field, Plugin plugin )
123 {
124
125 String strFieldName = field.getFieldName( );
126 field.setLabelTypeValue( findFieldTypeLabelbyId( field.getIdTypeValue( ), plugin, strPoolName ) );
127
128 String strLabelType = field.getLabelTypeValue( );
129 String strFieldLabelType = strLabelType + "(" + field.getLengthTypeValue( ) + ")";
130 field.setLabelNullValue( findFieldNullLabelbyId( field.getIdNullValue( ), plugin, strPoolName ) );
131
132 String strLabelNullValue = field.getLabelNullValue( );
133 String strFieldLabelNullValue = "";
134
135 if ( strLabelNullValue.equals( "YES" ) )
136 {
137 strFieldLabelNullValue = NULL_VALUE;
138 }
139 else
140 {
141 strFieldLabelNullValue = NOT + NULL_VALUE;
142 }
143
144 int nIdKeyValue = field.getIdKeyValue( );
145 field.setLabelKeyValue( findFieldKeyLabelbyId( nIdKeyValue, plugin, strPoolName ) );
146
147 String strFieldLabelKeyValue = field.getLabelKeyValue( );
148 String strKey;
149
150 if ( nIdKeyValue == 1 )
151 {
152 strKey = "";
153 }
154 else
155 {
156 strKey = PRIMARY_KEY;
157 }
158
159 String strFieldLabelDefaultValue = field.getDefaultValue( );
160 String strDEFAULT = "";
161
162 if ( strFieldLabelDefaultValue.equals( "" ) )
163 {
164 strDEFAULT = "";
165 }
166 else
167 {
168 strDEFAULT = "DEFAULT";
169 strFieldLabelDefaultValue = " '" + strFieldLabelDefaultValue + "' ";
170 }
171 String strTableName = table.getTableName( );
172 System.out.println(SQL_QUERY_CREATE_TABLE + strTableName + "(" + strFieldName + " " +
173 strFieldLabelType + " " + strKey + " " + strFieldLabelNullValue + " " + strDEFAULT +
174 strFieldLabelDefaultValue + ")");
175
176 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_CREATE_TABLE + strTableName + "(" + strFieldName + " " +
177 strFieldLabelType + " " + strKey + " " + strFieldLabelNullValue + " " + strDEFAULT +
178 strFieldLabelDefaultValue + ")", plugin );
179
180 daoUtil.executeUpdate( );
181 daoUtil.free( );
182 }
183
184
185
186
187
188
189 public void store( String strTableNameToModify, Table table )
190 {
191 String strTableName = table.getTableName( );
192 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_MODIFY_TABLE_NAME + strTableName + RENAME_TO + strTableNameToModify );
193 daoUtil.executeUpdate( );
194 daoUtil.free( );
195 }
196
197
198
199
200
201
202 public void delete( String strTableName, Plugin plugin )
203 {
204 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_TABLE + strTableName, plugin );
205 daoUtil.executeUpdate( );
206 daoUtil.free( );
207 }
208
209
210
211
212
213
214 public List<Table> selectTableList( Plugin plugin )
215 {
216 List<Table> tableList = new ArrayList<Table>( );
217 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SHOW_DATABASE_TABLES, plugin );
218 daoUtil.executeQuery( );
219
220 while ( daoUtil.next( ) )
221 {
222 Table table = new Table( );
223 table.setTableName( daoUtil.getString( 1 ) );
224 tableList.add( table );
225 }
226
227 daoUtil.free( );
228
229 return tableList;
230 }
231
232
233
234
235
236
237
238 public List<Table> selectTableListByPool( String strPoolName, Plugin plugin )
239 {
240 plugin.setConnectionService( AdminSqlConnectionService.getInstance( ).getConnectionService( strPoolName ) );
241
242 List<Table> tableList = new ArrayList<Table>( );
243 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SHOW_DATABASE_TABLES, plugin );
244 daoUtil.executeQuery( );
245
246 while ( daoUtil.next( ) )
247 {
248 Table table = new Table( );
249 table.setTableName( daoUtil.getString( 1 ) );
250 tableList.add( table );
251 }
252
253 daoUtil.free( );
254
255 return tableList;
256 }
257
258
259
260
261
262
263 public ReferenceList selectFieldTypeList( Plugin plugin )
264 {
265 ReferenceList fieldtypeList = new ReferenceList( );
266 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_FIELD_TYPE, plugin );
267
268 daoUtil.executeQuery( );
269
270 while ( daoUtil.next( ) )
271 {
272 ReferenceItem item = new ReferenceItem( );
273 item.setCode( daoUtil.getString( 1 ) );
274 item.setName( daoUtil.getString( 2 ) );
275 fieldtypeList.add( item );
276 }
277
278 daoUtil.free( );
279
280 return fieldtypeList;
281 }
282
283
284
285
286
287
288
289 public ReferenceList selectFieldKeyList( Plugin plugin )
290 {
291 ReferenceList fieldkeyList = new ReferenceList( );
292 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_FIELD_KEY, plugin );
293
294 daoUtil.executeQuery( );
295
296 while ( daoUtil.next( ) )
297 {
298 ReferenceItem item = new ReferenceItem( );
299 item.setCode( daoUtil.getString( 1 ) );
300 item.setName( daoUtil.getString( 2 ) );
301 fieldkeyList.add( item );
302 }
303
304 daoUtil.free( );
305
306 return fieldkeyList;
307 }
308
309
310
311
312
313
314
315 public ReferenceList selectFieldNullList( Plugin plugin )
316 {
317 ReferenceList fieldnullList = new ReferenceList( );
318 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_FIELD_NULL, plugin );
319
320 daoUtil.executeQuery( );
321
322 while ( daoUtil.next( ) )
323 {
324 ReferenceItem item = new ReferenceItem( );
325 item.setCode( daoUtil.getString( 1 ) );
326 item.setName( daoUtil.getString( 2 ) );
327 fieldnullList.add( item );
328 }
329
330 daoUtil.free( );
331
332 return fieldnullList;
333 }
334
335
336
337
338
339
340
341
342 public int findFieldTypeIdbyLabel( String strLabelType, Plugin plugin, String strPoolName )
343 {
344 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_FIELD_TYPE_ID, getDefaultPool( plugin ) );
345 daoUtil.setString( 1, strLabelType );
346 daoUtil.executeQuery( );
347
348 int nIdFieldType = 0;
349
350 if ( daoUtil.next( ) )
351 {
352 nIdFieldType = daoUtil.getInt( 1 );
353 }
354
355 daoUtil.free( );
356 plugin = changePool( strPoolName );
357
358 return nIdFieldType;
359 }
360
361
362
363
364
365
366
367
368 public String findFieldTypeLabelbyId( int nId, Plugin plugin, String strPoolName )
369 {
370 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_FIELD_TYPE_LABEL_BY_ID, getDefaultPool( plugin ) );
371 daoUtil.setInt( 1, nId );
372 daoUtil.executeQuery( );
373
374 String strFieldTypeLabel = "";
375
376 if ( daoUtil.next( ) )
377 {
378 strFieldTypeLabel = daoUtil.getString( 1 );
379 }
380
381 daoUtil.free( );
382 plugin = changePool( strPoolName );
383
384 return strFieldTypeLabel;
385 }
386
387
388
389
390
391
392
393
394 public int findFieldNullIdbyLabel( String strLabelNull, Plugin plugin, String strPoolName )
395 {
396 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_FIELD_NULL_ID, getDefaultPool( plugin ) );
397 daoUtil.setString( 1, strLabelNull );
398 daoUtil.executeQuery( );
399
400 int nIdFieldNull = 0;
401
402 if ( daoUtil.next( ) )
403 {
404 nIdFieldNull = daoUtil.getInt( 1 );
405 }
406
407 daoUtil.free( );
408 plugin = changePool( strPoolName );
409
410 return nIdFieldNull;
411 }
412
413
414
415
416
417
418
419
420 public String findFieldNullLabelbyId( int nId, Plugin plugin, String strPoolName )
421 {
422 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_FIELD_NULL_LABEL_BY_ID, getDefaultPool( plugin ) );
423 daoUtil.setInt( 1, nId );
424 daoUtil.executeQuery( );
425
426 String strFieldNullLabel = "";
427
428 if ( daoUtil.next( ) )
429 {
430 strFieldNullLabel = daoUtil.getString( 1 );
431 }
432
433 daoUtil.free( );
434 plugin = changePool( strPoolName );
435
436 return strFieldNullLabel;
437 }
438
439
440
441
442
443
444
445
446 public int findFieldKeyIdbyLabel( String strLabelKey, Plugin plugin, String strPoolName )
447 {
448 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_FIELD_KEY_ID, getDefaultPool( plugin ) );
449 daoUtil.setString( 1, strLabelKey );
450 daoUtil.executeQuery( );
451
452 int nIdFieldKey = 0;
453
454 if ( daoUtil.next( ) )
455 {
456 nIdFieldKey = daoUtil.getInt( 1 );
457 }
458
459 daoUtil.free( );
460 plugin = changePool( strPoolName );
461
462 return nIdFieldKey;
463 }
464
465
466
467
468
469
470
471
472 public String findFieldKeyLabelbyId( int nId, Plugin plugin, String strPoolName )
473 {
474 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_FIELD_KEY_LABEL_BY_ID, getDefaultPool( plugin ) );
475 daoUtil.setInt( 1, nId );
476 daoUtil.executeQuery( );
477
478 String strFieldKeyLabel = "";
479
480 if ( daoUtil.next( ) )
481 {
482 strFieldKeyLabel = daoUtil.getString( 1 );
483 }
484
485 daoUtil.free( );
486 plugin = changePool( strPoolName );
487
488 return strFieldKeyLabel;
489 }
490
491 private Plugin changePool( String strPoolName )
492 {
493 Plugin plugin = PluginService.getPlugin( "adminsql" );
494
495
496 PluginConnectionService connectionService = new PluginConnectionService( strPoolName );
497 connectionService.setPool( strPoolName );
498 plugin.setConnectionService( connectionService );
499
500 return plugin;
501 }
502
503 private Plugin getDefaultPool( Plugin plugin )
504 {
505 PluginConnectionService connectionService = new PluginConnectionService( plugin.getDbPoolName( ) );
506 connectionService.setPool( "adminsql" );
507 plugin.setConnectionService( connectionService );
508
509 return plugin;
510 }
511 }