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.updatercatalog.business;
35
36 import fr.paris.lutece.portal.service.plugin.Plugin;
37 import fr.paris.lutece.util.sql.DAOUtil;
38
39 import java.util.ArrayList;
40 import java.util.List;
41
42
43
44
45
46 public final class CatalogDAO implements ICatalogDAO
47 {
48
49 private static final String SQL_QUERY_NEW_PK = "SELECT max( id_catalog ) FROM updatercatalog_catalog";
50 private static final String SQL_QUERY_SELECT = "SELECT id_catalog, catalog_locale, catalog_name, catalog_description, output_filename, url_repository FROM updatercatalog_catalog WHERE id_catalog = ?";
51 private static final String SQL_QUERY_INSERT = "INSERT INTO updatercatalog_catalog ( id_catalog, catalog_locale, catalog_name, catalog_description, output_filename, url_repository ) VALUES ( ?, ?, ?, ?, ?, ? ) ";
52 private static final String SQL_QUERY_DELETE = "DELETE FROM updatercatalog_catalog WHERE id_catalog = ? ";
53 private static final String SQL_QUERY_UPDATE = "UPDATE updatercatalog_catalog SET id_catalog = ?, catalog_locale = ?, catalog_name = ?, catalog_description = ?, output_filename = ?, url_repository = ? WHERE id_catalog = ?";
54 private static final String SQL_QUERY_SELECTALL = "SELECT id_catalog, catalog_locale, catalog_name, catalog_description, output_filename, url_repository FROM updatercatalog_catalog";
55 private static final String SQL_QUERY_SELECT_PLUGIN_ENTRIES = "SELECT b.id_release , a.plugin_name, a.plugin_description, a.plugin_author, " +
56 "b.plugin_version, b.url_download, a.url_homepage, b.core_version_min , b.core_version_max " +
57 "FROM updatercatalog_plugin a, updatercatalog_plugin_release b, updatercatalog_catalog_plugin_release c, updatercatalog_catalog d " +
58 "WHERE a.plugin_name = b.plugin_name AND d.catalog_locale = a.plugin_locale " +
59 "AND b.id_release = c.id_release AND c.id_catalog = d.id_catalog AND d.id_catalog = ?";
60 private static final String SQL_QUERY_INSERT_RELEASE = "INSERT INTO updatercatalog_catalog_plugin_release ( id_catalog , id_release ) VALUES ( ? , ? ) ";
61 private static final String SQL_QUERY_DELETE_RELEASE = "DELETE FROM updatercatalog_catalog_plugin_release WHERE id_catalog = ? AND id_release = ? ";
62 private static final String SQL_QUERY_SELECT_UPGRADES = "SELECT version_from , critical_upgrade , url_download FROM updatercatalog_plugin_upgrade WHERE id_release = ? ";
63
64
65
66
67
68
69 public int newPrimaryKey( Plugin plugin )
70 {
71 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
72 daoUtil.executeQuery( );
73
74 int nKey;
75
76 if ( !daoUtil.next( ) )
77 {
78
79 nKey = 1;
80 }
81
82 nKey = daoUtil.getInt( 1 ) + 1;
83 daoUtil.free( );
84
85 return nKey;
86 }
87
88
89
90
91
92
93 public void insert( Catalog catalog, Plugin plugin )
94 {
95 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
96
97 catalog.setId( newPrimaryKey( plugin ) );
98
99 daoUtil.setInt( 1, catalog.getId( ) );
100 daoUtil.setString( 2, catalog.getLocale( ) );
101 daoUtil.setString( 3, catalog.getName( ) );
102 daoUtil.setString( 4, catalog.getDescription( ) );
103 daoUtil.setString( 5, catalog.getOutputFilename( ) );
104 daoUtil.setString( 6, catalog.getUrlRepository( ) );
105
106 daoUtil.executeUpdate( );
107 daoUtil.free( );
108 }
109
110
111
112
113
114
115
116 public Catalog load( int nId, Plugin plugin )
117 {
118 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
119 daoUtil.setInt( 1, nId );
120 daoUtil.executeQuery( );
121
122 Catalog catalog = null;
123
124 if ( daoUtil.next( ) )
125 {
126 catalog = new Catalog( );
127
128 catalog.setId( daoUtil.getInt( 1 ) );
129 catalog.setLocale( daoUtil.getString( 2 ) );
130 catalog.setName( daoUtil.getString( 3 ) );
131 catalog.setDescription( daoUtil.getString( 4 ) );
132 catalog.setOutputFilename( daoUtil.getString( 5 ) );
133 catalog.setUrlRepository( daoUtil.getString( 6 ) );
134 }
135
136 daoUtil.free( );
137
138 return catalog;
139 }
140
141
142
143
144
145
146 public void delete( int nCatalogId, Plugin plugin )
147 {
148 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
149 daoUtil.setInt( 1, nCatalogId );
150 daoUtil.executeUpdate( );
151 daoUtil.free( );
152 }
153
154
155
156
157
158
159 public void store( Catalog catalog, Plugin plugin )
160 {
161 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
162
163 daoUtil.setInt( 1, catalog.getId( ) );
164 daoUtil.setString( 2, catalog.getLocale( ) );
165 daoUtil.setString( 3, catalog.getName( ) );
166 daoUtil.setString( 4, catalog.getDescription( ) );
167 daoUtil.setString( 5, catalog.getOutputFilename( ) );
168 daoUtil.setString( 6, catalog.getUrlRepository( ) );
169 daoUtil.setInt( 7, catalog.getId( ) );
170
171 daoUtil.executeUpdate( );
172 daoUtil.free( );
173 }
174
175
176
177
178
179
180 public List<Catalog> selectCatalogsList( Plugin plugin )
181 {
182 List<Catalog> catalogList = new ArrayList<Catalog>( );
183 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
184 daoUtil.executeQuery( );
185
186 while ( daoUtil.next( ) )
187 {
188 Catalog catalog = new Catalog( );
189
190 catalog.setId( daoUtil.getInt( 1 ) );
191 catalog.setLocale( daoUtil.getString( 2 ) );
192 catalog.setName( daoUtil.getString( 3 ) );
193 catalog.setDescription( daoUtil.getString( 4 ) );
194 catalog.setOutputFilename( daoUtil.getString( 5 ) );
195 catalog.setUrlRepository( daoUtil.getString( 6 ) );
196
197 catalogList.add( catalog );
198 }
199
200 daoUtil.free( );
201
202 return catalogList;
203 }
204
205
206
207
208
209
210
211 public List<CatalogPluginEntry> selectPluginsEntries( int nCatalogId, Plugin plugin )
212 {
213 List<CatalogPluginEntry> list = new ArrayList<CatalogPluginEntry>( );
214 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PLUGIN_ENTRIES, plugin );
215 daoUtil.setInt( 1, nCatalogId );
216 daoUtil.executeQuery( );
217
218 while ( daoUtil.next( ) )
219 {
220 CatalogPluginEntry entry = new CatalogPluginEntry( );
221
222 entry.setReleaseId( daoUtil.getInt( 1 ) );
223 entry.setPluginName( daoUtil.getString( 2 ) );
224 entry.setPluginDescription( daoUtil.getString( 3 ) );
225 entry.setPluginAuthor( daoUtil.getString( 4 ) );
226 entry.setPluginVersion( daoUtil.getString( 5 ) );
227 entry.setUrlDownload( daoUtil.getString( 6 ) );
228 entry.setUrlHomepage( daoUtil.getString( 7 ) );
229 entry.setCoreVersionMin( daoUtil.getString( 8 ) );
230 entry.setCoreVersionMax( daoUtil.getString( 9 ) );
231
232
233 loadUpgrades( entry, plugin );
234
235 list.add( entry );
236 }
237
238 daoUtil.free( );
239
240 return list;
241 }
242
243
244
245
246
247
248 private void loadUpgrades( CatalogPluginEntry entry, Plugin plugin )
249 {
250 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_UPGRADES, plugin );
251 daoUtil.setInt( 1, entry.getReleaseId( ) );
252 daoUtil.executeQuery( );
253
254 while ( daoUtil.next( ) )
255 {
256 ReleaseUpgrade upgrade = new ReleaseUpgrade( );
257
258 upgrade.setVersionFrom( daoUtil.getString( 1 ) );
259 upgrade.setCritical( daoUtil.getInt( 2 ) );
260 upgrade.setUrlDownload( daoUtil.getString( 3 ) );
261
262 entry.addUpgrade( upgrade );
263 }
264
265 daoUtil.free( );
266 }
267
268
269
270
271
272
273
274 public void addRelease( int nCatalogId, int nReleaseId, Plugin plugin )
275 {
276 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_RELEASE, plugin );
277
278 daoUtil.setInt( 1, nCatalogId );
279 daoUtil.setInt( 2, nReleaseId );
280
281 daoUtil.executeUpdate( );
282 daoUtil.free( );
283 }
284
285
286
287
288
289
290
291 public void removeRelease( int nCatalogId, int nReleaseId, Plugin plugin )
292 {
293 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_RELEASE, plugin );
294
295 daoUtil.setInt( 1, nCatalogId );
296 daoUtil.setInt( 2, nReleaseId );
297
298 daoUtil.executeUpdate( );
299 daoUtil.free( );
300 }
301 }