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